[Home] [Help]
PACKAGE BODY: APPS.HZ_CUST_ACCOUNT_SITE_V2PUB
Source
1 PACKAGE BODY HZ_CUST_ACCOUNT_SITE_V2PUB AS
2 /*$Header: ARH2CSSB.pls 120.46.12020000.2 2013/01/30 10:20:22 rgokavar ship $ */
3
4 --------------------------------------
5 -- declaration of private global varibles
6 --------------------------------------
7
8 G_DEBUG_COUNT NUMBER := 0;
9 --G_DEBUG BOOLEAN := FALSE;
10
11
12 -- Code added for BUG 3714636
13 g_message_name VARCHAR2(1) :=NULL;
14
15 --------------------------------------
16 -- declaration of private procedures and functions
17 --------------------------------------
18
19 /*PROCEDURE enable_debug;
20
21 PROCEDURE disable_debug;
22 */
23
24
25 PROCEDURE do_create_cust_acct_site (
26 p_cust_acct_site_rec IN OUT NOCOPY CUST_ACCT_SITE_REC_TYPE,
27 x_cust_acct_site_id OUT NOCOPY NUMBER,
28 x_return_status IN OUT NOCOPY VARCHAR2
29 );
30
31 PROCEDURE do_update_cust_acct_site (
32 p_cust_acct_site_rec IN OUT NOCOPY CUST_ACCT_SITE_REC_TYPE,
33 p_object_version_number IN OUT NOCOPY NUMBER,
34 x_return_status IN OUT NOCOPY VARCHAR2
35 );
36
37 PROCEDURE do_create_cust_site_use (
38 p_cust_site_use_rec IN OUT NOCOPY CUST_SITE_USE_REC_TYPE,
39 p_customer_profile_rec IN OUT NOCOPY HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE,
40 p_create_profile IN VARCHAR2 := FND_API.G_TRUE,
41 p_create_profile_amt IN VARCHAR2 := FND_API.G_TRUE,
42 x_site_use_id OUT NOCOPY NUMBER,
43 x_return_status IN OUT NOCOPY VARCHAR2
44 );
45
46 PROCEDURE do_update_cust_site_use (
47 p_cust_site_use_rec IN OUT NOCOPY CUST_SITE_USE_REC_TYPE,
48 p_object_version_number IN OUT NOCOPY NUMBER,
49 x_return_status IN OUT NOCOPY VARCHAR2
50 );
51
52 PROCEDURE denormalize_site_use_flag (
53 p_cust_acct_site_id IN NUMBER,
54 p_site_use_code IN VARCHAR2,
55 p_flag IN VARCHAR2
56 );
57
58 PROCEDURE do_unset_prim_cust_site_use(
59 p_site_use_code IN varchar2,
60 p_cust_acct_site_id IN number,
61 p_org_id IN number -- TCA SSA Uptake (Bug 3456489)
62 );
63
64 PROCEDURE check_obsolete_columns (
65 p_create_update_flag IN VARCHAR2,
66 p_account_site_rec IN cust_acct_site_rec_type,
67 p_old_account_site_rec IN cust_acct_site_rec_type DEFAULT NULL,
68 x_return_status IN OUT NOCOPY VARCHAR2
69 );
70
71 --------------------------------------
72 -- private procedures and functions
73 --------------------------------------
74
75 /**
76 * PRIVATE PROCEDURE enable_debug
77 *
78 * DESCRIPTION
79 * Turn on debug mode.
80 *
81 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
82 * HZ_UTILITY_V2PUB.enable_debug
83 *
84 * MODIFICATION HISTORY
85 *
86 * 07-23-2001 Jianying Huang o Created.
87 *
88 */
89
90 /*PROCEDURE enable_debug IS
91
92 BEGIN
93
94 G_DEBUG_COUNT := G_DEBUG_COUNT + 1;
95
96 IF G_DEBUG_COUNT = 1 THEN
97 IF FND_PROFILE.value( 'HZ_API_FILE_DEBUG_ON' ) = 'Y' OR
98 FND_PROFILE.value( 'HZ_API_DBMS_DEBUG_ON' ) = 'Y'
99 THEN
100 HZ_UTILITY_V2PUB.enable_debug;
101 G_DEBUG := TRUE;
102 END IF;
103 END IF;
104
105 END enable_debug;
106 */
107
108 /**
109 * PRIVATE PROCEDURE disable_debug
110 *
111 * DESCRIPTION
112 * Turn off debug mode.
113 *
114 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
115 * HZ_UTILITY_V2PUB.disable_debug
116 *
117 * MODIFICATION HISTORY
118 *
119 * 07-23-2001 Jianying Huang o Created.
120 *
121 */
122
123 /*PROCEDURE disable_debug IS
124
125 BEGIN
126
127 IF G_DEBUG THEN
128 G_DEBUG_COUNT := G_DEBUG_COUNT - 1;
129
130 IF G_DEBUG_COUNT = 0 THEN
131 HZ_UTILITY_V2PUB.disable_debug;
132 G_DEBUG := FALSE;
133 END IF;
134 END IF;
135
136 END disable_debug;
137 */
138
139 /**
140 * PRIVATE PROCEDURE do_create_cust_acct_site
141 *
142 * DESCRIPTION
143 * Private procedure to create customer account site.
144 *
145 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
146 * HZ_ACCOUNT_VALIDATE_V2PUB.validate_cust_acct_site
147 * HZ_CUST_ACCT_SITES_PKG.Insert_Row
148 *
149 * ARGUMENTS
150 * IN/OUT:
151 * p_cust_acct_site_rec Customer account site record.
152 * x_return_status Return status after the call. The status can
153 * be FND_API.G_RET_STS_SUCCESS (success),
154 * FND_API.G_RET_STS_ERROR (error),
155 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
156 * OUT:
157 * x_cust_acct_site_id Customer account site ID.
158 *
159 * NOTES
160 *
161 * MODIFICATION HISTORY
162 *
163 * 07-23-2001 Jianying Huang o Created.
164 *
165 * 24-JUN-2004 V.Ravichandran o Bug 3714636.Modified do_create_cust_acct_site() and
166 * to reduce cost of query
167 * which checks whether the message name in
168 * FND_NEW_MESSAGES is 'HZ_INACTIVATE_ACCOUNT_SITE_UI'.
169 * 02-AUG-2004 Rajib Ranjan Borah o Bug 3805019. If status is NULL and the corresponding
170 * status is 'A', then warning HZ_ACCT_SITE_INHERIT_STATUS
171 * will not be displayed.
172 * o Rowid and object_version_number in
173 * HZ_PARTY_SITES need not be read for synchronizing
174 * statuses.
175 * Removed unnecessary variables l_ps_rowid,
176 * l_ps_object_version_number and l_dummy.
177 * 12-MAY-2005 Rajib Ranjan Borah o TCA SSA Uptake (Bug 3456489)
178 */
179
180 PROCEDURE do_create_cust_acct_site (
181 p_cust_acct_site_rec IN OUT NOCOPY CUST_ACCT_SITE_REC_TYPE,
182 x_cust_acct_site_id OUT NOCOPY NUMBER,
183 x_return_status IN OUT NOCOPY VARCHAR2
184 ) IS
185
186 l_debug_prefix VARCHAR2(30) := ''; --'do_create_cust_acct_site';
187
188 l_return_status VARCHAR2(1);
189 l_msg_count NUMBER;
190 l_msg_data VARCHAR2(2000);
191
192 l_location_id NUMBER;
193 l_loc_id NUMBER;
194 l_orig_sys_reference_rec HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE;
195 l_cust_acct_site_orig_sys VARCHAR2(255);
196
197 /* 3456489 Added org_id for Shared Service Uptake */
198 CURSOR check_orig_sys_ref IS
199 select 'Y' from hz_cust_acct_sites_all
200 where orig_system_reference =
201 p_cust_acct_site_rec.orig_system_reference
202 and org_id = p_cust_acct_site_rec.org_id;
203
204
205 l_orig_system_reference varchar2(255) :=p_cust_acct_site_rec.orig_system_reference;
206 l_tmp varchar2(1);
207 /* l_ps_object_version_number number;
208 l_ps_rowid rowid := null; */
209 l_status varchar2(1);
210 -- l_dummy varchar2(1);
211
212 CURSOR c_check_first_site (
213 p_cust_account_id NUMBER,
214 p_org_id NUMBER
215 ) IS
216 SELECT null
217 FROM hz_cust_acct_sites_all
218 WHERE cust_account_id = p_cust_account_id
219 AND org_id = p_org_id
220 AND status NOT IN ('M', 'D')
221 AND ROWNUM = 1;
222
223 CURSOR c_check_profile (
224 p_cust_account_id NUMBER
225 ) IS
226 SELECT credit_hold
227 FROM hz_customer_profiles
228 WHERE cust_account_id = p_cust_account_id;
229
230 BEGIN
231
232 -- Debug info.
233 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
234 hz_utility_v2pub.debug(p_message=>'do_create_cust_acct_site (+)',
235 p_prefix=>l_debug_prefix,
236 p_msg_level=>fnd_log.level_procedure);
237 END IF;
238
239 --
240 --- Before creating account site, inherit status from party site(bug 3299622)
241 --
242 -- Code modified for Bug 3714636 starts here
243 IF(g_message_name is null) THEN
244 BEGIN
245 SELECT 'X' into g_message_name FROM FND_NEW_MESSAGES
246 WHERE message_name = 'HZ_INACTIVATE_ACCOUNT_SITE_UI'
247 AND language_code = userenv('LANG')
248 AND application_id = 222
249 AND rownum =1;
250 EXCEPTION
251 WHEN NO_DATA_FOUND THEN
252 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
253 FND_MESSAGE.SET_TOKEN('RECORD', 'Release Name');
254 FND_MESSAGE.SET_TOKEN('VALUE', 'HZ_INACTIVATE_ACCOUNT_SITE_UI');
255 FND_MSG_PUB.ADD;
256 RAISE FND_API.G_EXC_ERROR;
257 END;
258 END IF;
259 IF g_message_name IS NOT NULL THEN
260 -- Code modified for Bug 3714636 ends here
261 BEGIN
262 SELECT /*ROWID, OBJECT_VERSION_NUMBER,*/status
263 INTO /*l_ps_rowid, l_ps_object_version_number,*/l_status
264 FROM HZ_PARTY_SITES
265 WHERE PARTY_SITE_ID = p_cust_acct_site_rec.party_site_id
266 FOR UPDATE NOWAIT;
267
268 --p_object_version_number := NVL( l_object_version_number, 1 ) + 1;
269 EXCEPTION
270 WHEN NO_DATA_FOUND THEN
271 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_RECORD' );
272 FND_MESSAGE.SET_TOKEN( 'RECORD', 'party site' );
273 FND_MESSAGE.SET_TOKEN( 'VALUE',
274 NVL( TO_CHAR( p_cust_acct_site_rec.party_site_id ), 'null' ) );
275 FND_MSG_PUB.ADD;
276 RAISE FND_API.G_EXC_ERROR;
277 END;
278
279 /*IF p_cust_acct_site_rec.status is NULL OR p_cust_acct_site_rec.status <> l_status THEN --Bug 3370870 */
280 IF NVL(p_cust_acct_site_rec.status,'A') <> l_status THEN -- Bug 3805019
281 FND_MESSAGE.SET_NAME( 'AR', 'HZ_ACCT_SITE_INHERIT_STATUS' );
282 FND_MSG_PUB.ADD;
283 p_cust_acct_site_rec.status := l_status;
284 END IF;
285 END IF;
286 --end of party site account site status synch
287
288 /* 4578854 Added for Shared Service Uptake */
289 BEGIN
290 MO_GLOBAL.validate_orgid_pub_api(p_cust_acct_site_rec.org_id,'N',l_return_status);
291 EXCEPTION
292 WHEN OTHERS
293 THEN
294 RAISE FND_API.G_EXC_ERROR;
295 END;
296
297 if (p_cust_acct_site_rec.orig_system is null or p_cust_acct_site_rec.orig_system = fnd_api.g_miss_char)
298 and (p_cust_acct_site_rec.orig_system_reference is not null and
299 p_cust_acct_site_rec.orig_system_reference <> fnd_api.g_miss_char) then
300 p_cust_acct_site_rec.orig_system := 'UNKNOWN';
301 end if;
302
303 open check_orig_sys_ref;
304 fetch check_orig_sys_ref into l_tmp;
305 if check_orig_sys_ref%FOUND then
306 p_cust_acct_site_rec.orig_system_reference:=l_orig_system_reference||'#@'||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS');
307 end if ;
308 close check_orig_sys_ref;
309
310 -- Validate cust acct site record
311 HZ_ACCOUNT_VALIDATE_V2PUB.validate_cust_acct_site (
312 p_create_update_flag => 'C',
313 p_cust_acct_site_rec => p_cust_acct_site_rec,
314 p_rowid => NULL,
315 x_return_status => x_return_status );
316
317 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
318 RAISE FND_API.G_EXC_ERROR;
319 END IF;
320
321 -- Add for global holds
322 --
323
324 OPEN c_check_first_site(
325 p_cust_acct_site_rec.cust_account_id, p_cust_acct_site_rec.org_id);
326 FETCH c_check_first_site INTO l_tmp;
327 IF c_check_first_site%NOTFOUND THEN
328 -- Debug info.
329 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
330 hz_utility_v2pub.debug(p_message=>'First site created in this org... '||
331 'cust_account_id = '||p_cust_acct_site_rec.cust_account_id||' '||
332 'org_id = '||p_cust_acct_site_rec.org_id,
333 p_prefix=>l_debug_prefix,
334 p_msg_level=>fnd_log.level_statement);
335 END IF;
336
337 OPEN c_check_profile(p_cust_acct_site_rec.cust_account_id);
338 FETCH c_check_profile INTO l_tmp;
339 CLOSE c_check_profile;
340
341 IF l_tmp = 'Y' THEN
342 -- Debug info.
343 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
344 hz_utility_v2pub.debug(p_message=>'Before call OE_HOLDS... '||
345 'cust_account_id = '||p_cust_acct_site_rec.cust_account_id,
346 p_prefix=>l_debug_prefix,
347 p_msg_level=>fnd_log.level_statement);
348 END IF;
349
350 BEGIN
351 l_return_status := FND_API.G_RET_STS_SUCCESS;
352 OE_Holds_PUB.Process_Holds (
353 p_api_version => 1.0,
354 p_init_msg_list => FND_API.G_FALSE,
355 p_hold_entity_code => 'C',
356 p_hold_entity_id => p_cust_acct_site_rec.cust_account_id,
357 p_hold_id => 1,
358 p_release_reason_code => 'AR_AUTOMATIC',
359 p_action => 'APPLY',
360 x_return_status => l_return_status,
361 x_msg_count => l_msg_count,
362 x_msg_data => l_msg_data);
363
364 -- Debug info.
365 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
366 hz_utility_v2pub.debug(p_message=>'After call OE_HOLDS... '||
367 'l_return_status = '||l_return_status||' '||
368 'l_msg_count = '||l_msg_count||' '||
369 'l_msg_data = '||l_msg_data,
370 p_prefix=>l_debug_prefix,
371 p_msg_level=>fnd_log.level_statement);
372 END IF;
373 EXCEPTION
374 WHEN OTHERS THEN
375 -- Debug info.
376 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
377 hz_utility_v2pub.debug(p_message=>'Exception raised from OE_HOLDS... '||SQLERRM,
378 p_prefix=>l_debug_prefix,
379 p_msg_level=>fnd_log.level_statement);
380 END IF;
381 --Bug14059649
382 --We should remove the code where they are suppressing the error being raise
383 --by OM code. TCA team should always pass on the error message back to the user
384 --/ UI / calling API, so that they will know that an error has occurred in OM
385 -- l_return_status := 'S';
386 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
387 END;
388
389 --
390 -- only raise unexpected error
391 --Bug14059649
392 --Added FND_API.G_EXC_ERROR
393 --We should remove the code where they are suppressing the error being raise
394 --by OM code. TCA team should always pass on the error message back to the user
395 --/ UI / calling API, so that they will know that an error has occurred in OM
396 --
397 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
398 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
399 ELSIF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
400 RAISE FND_API.G_EXC_ERROR;
401 END IF;
402 END IF;
403 END IF;
404 CLOSE c_check_first_site;
405
406 -- Debug info.
407 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
408 hz_utility_v2pub.debug(p_message=>'HZ_CUST_ACCT_SITES_PKG.Insert_Row (+)',
409 p_prefix=>l_debug_prefix,
410 p_msg_level=>fnd_log.level_procedure);
411 END IF;
412
413 -- Call table-handler.
414 HZ_CUST_ACCT_SITES_PKG.Insert_Row (
415 X_CUST_ACCT_SITE_ID => p_cust_acct_site_rec.cust_acct_site_id,
416 X_CUST_ACCOUNT_ID => p_cust_acct_site_rec.cust_account_id,
417 X_PARTY_SITE_ID => p_cust_acct_site_rec.party_site_id,
418 X_ATTRIBUTE_CATEGORY => p_cust_acct_site_rec.attribute_category,
419 X_ATTRIBUTE1 => p_cust_acct_site_rec.attribute1,
420 X_ATTRIBUTE2 => p_cust_acct_site_rec.attribute2,
421 X_ATTRIBUTE3 => p_cust_acct_site_rec.attribute3,
422 X_ATTRIBUTE4 => p_cust_acct_site_rec.attribute4,
423 X_ATTRIBUTE5 => p_cust_acct_site_rec.attribute5,
424 X_ATTRIBUTE6 => p_cust_acct_site_rec.attribute6,
425 X_ATTRIBUTE7 => p_cust_acct_site_rec.attribute7,
426 X_ATTRIBUTE8 => p_cust_acct_site_rec.attribute8,
427 X_ATTRIBUTE9 => p_cust_acct_site_rec.attribute9,
428 X_ATTRIBUTE10 => p_cust_acct_site_rec.attribute10,
429 X_ATTRIBUTE11 => p_cust_acct_site_rec.attribute11,
430 X_ATTRIBUTE12 => p_cust_acct_site_rec.attribute12,
431 X_ATTRIBUTE13 => p_cust_acct_site_rec.attribute13,
432 X_ATTRIBUTE14 => p_cust_acct_site_rec.attribute14,
433 X_ATTRIBUTE15 => p_cust_acct_site_rec.attribute15,
434 X_ATTRIBUTE16 => p_cust_acct_site_rec.attribute16,
435 X_ATTRIBUTE17 => p_cust_acct_site_rec.attribute17,
436 X_ATTRIBUTE18 => p_cust_acct_site_rec.attribute18,
437 X_ATTRIBUTE19 => p_cust_acct_site_rec.attribute19,
438 X_ATTRIBUTE20 => p_cust_acct_site_rec.attribute20,
439 X_GLOBAL_ATTRIBUTE_CATEGORY => p_cust_acct_site_rec.global_attribute_category,
440 X_GLOBAL_ATTRIBUTE1 => p_cust_acct_site_rec.global_attribute1,
441 X_GLOBAL_ATTRIBUTE2 => p_cust_acct_site_rec.global_attribute2,
442 X_GLOBAL_ATTRIBUTE3 => p_cust_acct_site_rec.global_attribute3,
443 X_GLOBAL_ATTRIBUTE4 => p_cust_acct_site_rec.global_attribute4,
444 X_GLOBAL_ATTRIBUTE5 => p_cust_acct_site_rec.global_attribute5,
445 X_GLOBAL_ATTRIBUTE6 => p_cust_acct_site_rec.global_attribute6,
446 X_GLOBAL_ATTRIBUTE7 => p_cust_acct_site_rec.global_attribute7,
447 X_GLOBAL_ATTRIBUTE8 => p_cust_acct_site_rec.global_attribute8,
448 X_GLOBAL_ATTRIBUTE9 => p_cust_acct_site_rec.global_attribute9,
449 X_GLOBAL_ATTRIBUTE10 => p_cust_acct_site_rec.global_attribute10,
450 X_GLOBAL_ATTRIBUTE11 => p_cust_acct_site_rec.global_attribute11,
451 X_GLOBAL_ATTRIBUTE12 => p_cust_acct_site_rec.global_attribute12,
452 X_GLOBAL_ATTRIBUTE13 => p_cust_acct_site_rec.global_attribute13,
453 X_GLOBAL_ATTRIBUTE14 => p_cust_acct_site_rec.global_attribute14,
454 X_GLOBAL_ATTRIBUTE15 => p_cust_acct_site_rec.global_attribute15,
455 X_GLOBAL_ATTRIBUTE16 => p_cust_acct_site_rec.global_attribute16,
456 X_GLOBAL_ATTRIBUTE17 => p_cust_acct_site_rec.global_attribute17,
457 X_GLOBAL_ATTRIBUTE18 => p_cust_acct_site_rec.global_attribute18,
458 X_GLOBAL_ATTRIBUTE19 => p_cust_acct_site_rec.global_attribute19,
459 X_GLOBAL_ATTRIBUTE20 => p_cust_acct_site_rec.global_attribute20,
460 X_ORIG_SYSTEM_REFERENCE => p_cust_acct_site_rec.orig_system_reference,
461 X_STATUS => p_cust_acct_site_rec.status,
462 X_CUSTOMER_CATEGORY_CODE => p_cust_acct_site_rec.customer_category_code,
463 X_LANGUAGE => p_cust_acct_site_rec.language,
464 X_KEY_ACCOUNT_FLAG => p_cust_acct_site_rec.key_account_flag,
465 X_TP_HEADER_ID => p_cust_acct_site_rec.tp_header_id,
466 X_ECE_TP_LOCATION_CODE => p_cust_acct_site_rec.ece_tp_location_code,
467 X_PRIMARY_SPECIALIST_ID => p_cust_acct_site_rec.primary_specialist_id,
468 X_SECONDARY_SPECIALIST_ID => p_cust_acct_site_rec.secondary_specialist_id,
469 X_TERRITORY_ID => p_cust_acct_site_rec.territory_id,
470 X_TERRITORY => p_cust_acct_site_rec.territory,
471 X_TRANSLATED_CUSTOMER_NAME => p_cust_acct_site_rec.translated_customer_name,
472 X_OBJECT_VERSION_NUMBER => 1,
473 X_CREATED_BY_MODULE => p_cust_acct_site_rec.created_by_module,
474 X_APPLICATION_ID => p_cust_acct_site_rec.application_id,
475 X_ORG_ID => p_cust_acct_site_rec.org_id -- Bug 3456489
476 );
477
478
479
480 -- Debug info.
481 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
482 hz_utility_v2pub.debug(p_message=>'HZ_CUST_ACCT_SITES_PKG.Insert_Row (-) ' ||
483 'x_cust_acct_site_id = ' || p_cust_acct_site_rec.cust_acct_site_id,
484 p_prefix=>l_debug_prefix,
485 p_msg_level=>fnd_log.level_procedure);
486 END IF;
487
488
489 --if (p_cust_acct_site_rec.orig_system_reference is not null and l_party_rec.orig_system_reference<>fnd_api.g_miss_char )-for two tables has null osr.
490 if (p_cust_acct_site_rec.orig_system is not null and p_cust_acct_site_rec.orig_system <>fnd_api.g_miss_char)
491 then
492 l_orig_sys_reference_rec.orig_system := p_cust_acct_site_rec.orig_system;
493 l_orig_sys_reference_rec.orig_system_reference := l_orig_system_reference;
494 l_orig_sys_reference_rec.owner_table_name := 'HZ_CUST_ACCT_SITES_ALL';
495 l_orig_sys_reference_rec.owner_table_id := p_cust_acct_site_rec.cust_acct_site_id;
496 l_orig_sys_reference_rec.created_by_module := p_cust_acct_site_rec.created_by_module;
497
498 hz_orig_system_ref_pub.create_orig_system_reference(
499 FND_API.G_FALSE,
500 l_orig_sys_reference_rec,
501 x_return_status,
502 l_msg_count,
503 l_msg_data);
504 IF x_return_status <> fnd_api.g_ret_sts_success THEN
505 RAISE FND_API.G_EXC_ERROR;
506 END IF;
507 end if;
508
509 x_cust_acct_site_id := p_cust_acct_site_rec.cust_acct_site_id;
510
511 -- Populate data into tax assignment table.
512
513 SELECT LOC.LOCATION_ID INTO l_location_id
514 FROM HZ_LOCATIONS LOC,
515 HZ_PARTY_SITES PARTY_SITE,
516 HZ_CUST_ACCT_SITES_ALL ACCT_SITE -- Bug 3456489
517 WHERE ACCT_SITE.CUST_ACCT_SITE_ID = p_cust_acct_site_rec.cust_acct_site_id
518 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
519 AND PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID;
520
521 HZ_TAX_ASSIGNMENT_V2PUB.create_loc_assignment (
522 p_location_id => l_location_id,
523 p_created_by_module => p_cust_acct_site_rec.created_by_module,
524 p_application_id => p_cust_acct_site_rec.application_id,
525 x_return_status => x_return_status,
526 x_msg_count => l_msg_count,
527 x_msg_data => l_msg_data,
528 x_loc_id => l_loc_id
529 );
530
531 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
532 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
533 RAISE FND_API.G_EXC_ERROR;
534 ELSE
535 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
536 END IF;
537 END IF;
538
539 -- Debug info.
540 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
541 hz_utility_v2pub.debug(p_message=>'do_create_cust_acct_site (-)',
542 p_prefix=>l_debug_prefix,
543 p_msg_level=>fnd_log.level_procedure);
544 END IF;
545
546 END do_create_cust_acct_site;
547
548 PROCEDURE do_unset_prim_cust_site_use(
549 p_site_use_code IN varchar2,
550 p_cust_acct_site_id IN number,
551 p_org_id IN number -- TCA SSA Uptake (Bug 3456489)
552 ) IS
553 l_cust_acct_id number;
554 l_site_use_id number;
555 l_cust_acct_site_id number;
556
557 CURSOR c_site (l_cust_acct_id VARCHAR2) IS
558 SELECT su.site_use_id,su.cust_acct_site_id
559 FROM hz_cust_accounts a,
560 hz_cust_acct_sites_all cas,
561 hz_cust_site_uses_all su
562 WHERE a.cust_account_id = l_cust_acct_id
563 AND l_cust_acct_id = cas.cust_account_id
564 AND cas.cust_acct_site_id = su.cust_acct_site_id
565 AND su.site_use_code = p_site_use_code
566 AND su.status = 'A'
567 AND su.primary_flag = 'Y'
568 AND cas.org_id = p_org_id
569 AND su.org_id = p_org_id;
570
571 r_site c_site%ROWTYPE;
572 l_cnt NUMBER;
573 l_debug_prefix VARCHAR2(30) := '';
574
575 BEGIN
576
577 BEGIN
578 SELECT cust_account_id into l_cust_acct_id
579 FROM hz_cust_acct_sites_all -- Bug 3456489
580 WHERE cust_acct_site_id = p_cust_acct_site_id;
581 END;
582
583
584 BEGIN
585 -- Modified for fix 3294182.
586 SELECT su.site_use_id,su.cust_acct_site_id
587 INTO l_site_use_id,l_cust_acct_site_id
588 FROM hz_cust_accounts a,
589 hz_cust_acct_sites_all cas, -- Bug 3456489
590 hz_cust_site_uses_all su -- Bug 3456489
591 WHERE a.cust_account_id = l_cust_acct_id
592 AND l_cust_acct_id = cas.cust_account_id
593 AND cas.cust_acct_site_id = su.cust_acct_site_id
594 AND su.site_use_code = p_site_use_code
595 AND su.status = 'A'
596 AND su.primary_flag = 'Y'
597 AND cas.org_id = p_org_id -- TCA SSA Uptake (Bug 3456489)
598 AND su.org_id = p_org_id; -- TCA SSA Uptake (Bug 3456489)
599
600 UPDATE hz_cust_site_uses_all -- Bug 3456489
601 SET primary_flag = 'N',
602 last_updated_by = hz_utility_pub.LAST_UPDATED_BY,
603 last_update_date = hz_utility_pub.LAST_UPDATE_DATE
604 WHERE site_use_id =l_site_use_id;
605
606 denormalize_site_use_flag(l_cust_acct_site_id,p_site_use_code,'Y');
607
608 EXCEPTION
609 WHEN no_data_found THEN
610 NULL;
611 --Bug9218025
612 --Functionally there will be only Active Primary record
613 --In exceptional case bad data (multiple) records might created by system
614 --To handle such scenario making all existing Primary records to non-primary
615 WHEN TOO_MANY_ROWS THEN
616 -- Debug info.
617 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
618 hz_utility_v2pub.debug(p_message=>'do_unset_prim_cust_site_use - TOO_MANY_ROWS Exception',
619 p_prefix=>l_debug_prefix,
620 p_msg_level=>fnd_log.level_procedure);
621 END IF;
622
623 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
624 hz_utility_v2pub.debug(p_message=>'Mulitiple rows found for Cust Acct Id '||l_cust_acct_id||' and Site Use Code '||p_site_use_code,
625 p_prefix=>l_debug_prefix,
626 p_msg_level=>fnd_log.level_procedure);
627 END IF;
628
629
630
631 OPEN c_site(l_cust_acct_id);
632 LOOP
633 FETCH c_site INTO r_site;
634
635 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
636 hz_utility_v2pub.debug(p_message=>'TOO_MANY_ROWS Excp Site Use Id '||r_site.site_use_id,
637 p_prefix=>l_debug_prefix,
638 p_msg_level=>fnd_log.level_procedure);
639 END IF;
640
641
642 UPDATE hz_cust_site_uses_all -- Bug 3456489
643 SET primary_flag = 'N',
644 last_updated_by = hz_utility_pub.LAST_UPDATED_BY,
645 last_update_date = hz_utility_pub.LAST_UPDATE_DATE
646 WHERE site_use_id =r_site.site_use_id;
647
648 denormalize_site_use_flag(r_site.cust_acct_site_id,p_site_use_code,'Y');
649 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
650 hz_utility_v2pub.debug(p_message=>'TOO_MANY_ROWS Excp Denormalization done for Acct Site Id '||r_site.cust_acct_site_id||' and Site use Code '||p_site_use_code,
651 p_prefix=>l_debug_prefix,
652 p_msg_level=>fnd_log.level_procedure);
653 END IF;
654 EXIT WHEN c_site%NOTFOUND;
655 END LOOP;
656 CLOSE c_site;
657
658
659 END;
660 END do_unset_prim_cust_site_use;
661
662
663 /**
664 * PRIVATE PROCEDURE do_update_cust_acct_site
665 *
666 * DESCRIPTION
667 * Private procedure to update customer account site.
668 *
669 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
670 * HZ_ACCOUNT_VALIDATE_V2PUB.validate_cust_acct_site
671 * HZ_CUST_ACCT_SITES_PKG.Update_Row
672 *
673 * ARGUMENTS
674 * IN/OUT:
675 * p_cust_acct_site_rec Customer account site record.
676 * p_object_version_number Used for locking the being updated record.
677 * x_return_status Return status after the call. The status can
678 * be FND_API.G_RET_STS_SUCCESS (success),
679 * FND_API.G_RET_STS_ERROR (error),
680 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
681 *
682 * NOTES
683 *
684 * MODIFICATION HISTORY
685 *
686 * 07-23-2001 Jianying Huang o Created.
687 *
688 * 24-JUN-2004 V.Ravichandran o Bug 3714636.Modified do_update_cust_acct_site()
689 * to reduce cost of query
690 * which checks whether the message name in
691 * FND_NEW_MESSAGES is 'HZ_INACTIVATE_ACCOUNT_SITE_UI'.
692 * 02-AUG-2004 Rajib Ranjan Borah o Bug 3805019.party_site_id can be null during update.
693 * Therefore read the value of party_site_id from the
694 * database instead of using p_cust_acct_site_rec.party_site_id
695 * for synchronizing the status in HZ_PARTY_SITES.
696 * o Moreover the cursor does not pick rowid.
697 * o Removed redundant local variables l_dummy, l_ps_rowid.
698 * 12-MAY-2005 Rajib Ranjan Borah o TCA SSA Uptake (Bug 3456489)
699 */
700
701 PROCEDURE do_update_cust_acct_site (
702 p_cust_acct_site_rec IN OUT NOCOPY CUST_ACCT_SITE_REC_TYPE,
703 p_object_version_number IN OUT NOCOPY NUMBER,
704 x_return_status IN OUT NOCOPY VARCHAR2
705 ) IS
706
707 l_debug_prefix VARCHAR2(30) := ''; --'do_update_cust_acct_site';
708 l_msg_count NUMBER;
709 l_msg_data VARCHAR2(2000);
710 l_rowid ROWID := NULL;
711 l_object_version_number NUMBER;
712 l_location_id NUMBER;
713 l_loc_id NUMBER;
714 l_orig_sys_reference_rec HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE;
715
716
717 /* 3456489 Added org_id for Shared Service Uptake */
718
719 CURSOR check_orig_sys_ref IS
720 select 'Y' from hz_cust_acct_sites_all
721 where orig_system_reference =
722 p_cust_acct_site_rec.orig_system_reference
723 and org_id = p_cust_acct_site_rec.org_id;
724
725 l_orig_system_reference varchar2(255) :=p_cust_acct_site_rec.orig_system_reference;
726 l_tmp varchar2(1);
727 l_status varchar2(1);
728 l_party_site_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE;
729 l_party_site_id HZ_CUST_ACCT_SITES.party_site_id%TYPE;
730 -- l_ps_rowid ROWID := NULL;
731 l_ps_object_version_number NUMBER;
732 -- l_dummy VARCHAR2(1);
733
734 BEGIN
735
736 -- Debug info.
737 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
738 hz_utility_v2pub.debug(p_message=>'do_update_cust_acct_site (+)',
739 p_prefix=>l_debug_prefix,
740 p_msg_level=>fnd_log.level_procedure);
741 END IF;
742
743 -- Lock record.
744
745 /* 3456489 Added org_id for Shared Services Uptake. */
746
747 BEGIN
748 SELECT ROWID, OBJECT_VERSION_NUMBER,status, PARTY_SITE_ID, org_id
749 INTO l_rowid, l_object_version_number,l_status, l_party_site_id,
750 p_cust_acct_site_rec.org_id
751 FROM HZ_CUST_ACCT_SITES
752 WHERE CUST_ACCT_SITE_ID = p_cust_acct_site_rec.cust_acct_site_id
753 FOR UPDATE NOWAIT;
754
755 IF NOT (
756 ( p_object_version_number IS NULL AND l_object_version_number IS NULL ) OR
757 ( p_object_version_number IS NOT NULL AND
758 l_object_version_number IS NOT NULL AND
759 p_object_version_number = l_object_version_number ) )
760 THEN
761 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_RECORD_CHANGED' );
762 FND_MESSAGE.SET_TOKEN( 'TABLE', 'hz_cust_acct_sites' );
763 FND_MSG_PUB.ADD;
764 RAISE FND_API.G_EXC_ERROR;
765 END IF;
766
767 p_object_version_number := NVL( l_object_version_number, 1 ) + 1;
768
769 EXCEPTION
770 WHEN NO_DATA_FOUND THEN
771 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_RECORD' );
772 FND_MESSAGE.SET_TOKEN( 'RECORD', 'customer account site' );
773 FND_MESSAGE.SET_TOKEN( 'VALUE',
774 NVL( TO_CHAR( p_cust_acct_site_rec.cust_acct_site_id ), 'null' ) );
775 FND_MSG_PUB.ADD;
776 RAISE FND_API.G_EXC_ERROR;
777 END;
778
779
780
781 -- Validate cust acct site record
782 HZ_ACCOUNT_VALIDATE_V2PUB.validate_cust_acct_site (
783 p_create_update_flag => 'U',
784 p_cust_acct_site_rec => p_cust_acct_site_rec,
785 p_rowid => l_rowid,
786 x_return_status => x_return_status );
787
788 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
789 RAISE FND_API.G_EXC_ERROR;
790 END IF;
791
792 if (p_cust_acct_site_rec.orig_system is not null
793 and p_cust_acct_site_rec.orig_system <>fnd_api.g_miss_char)
794 and (p_cust_acct_site_rec.orig_system_reference is not null
795 and p_cust_acct_site_rec.orig_system_reference <>fnd_api.g_miss_char)
796 then
797 p_cust_acct_site_rec.orig_system_reference := null;
798 -- In mosr, we have bypassed osr nonupdateable validation
799 -- but we should not update existing osr, set it to null
800 end if;
801
802
803 --
804 --- Check if account site status is changed(Bug 3299622)
805 --
806 IF p_cust_acct_site_rec.status <> l_status THEN
807 -- Code modified for Bug 3714636 starts here
808 IF(g_message_name is null) THEN
809 BEGIN
810 SELECT 'X' into g_message_name FROM FND_NEW_MESSAGES
811 WHERE message_name = 'HZ_INACTIVATE_ACCOUNT_SITE_UI'
812 AND language_code = userenv('LANG')
813 AND application_id = 222
814 AND rownum =1;
815 EXCEPTION
816 WHEN NO_DATA_FOUND THEN
817 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
818 FND_MESSAGE.SET_TOKEN('RECORD', 'Message Name');
819 FND_MESSAGE.SET_TOKEN('VALUE', 'HZ_INACTIVATE_ACCOUNT_SITE_UI');
820 FND_MSG_PUB.ADD;
821 RAISE FND_API.G_EXC_ERROR;
822 END;
823 END IF;
824 IF g_message_name IS NOT NULL THEN
825 -- Code modified for Bug 3714636 ends here
826 BEGIN
827 SELECT /*ROWID,*/ OBJECT_VERSION_NUMBER
828 INTO /*l_ps_rowid,*/ l_ps_object_version_number
829 FROM HZ_PARTY_SITES
830 WHERE PARTY_SITE_ID = l_party_site_id /* Bug 3805019: p_cust_acct_site_rec.party_site_id */
831 FOR UPDATE NOWAIT;
832
833 --p_object_version_number := NVL( l_object_version_number, 1 ) + 1;
834 EXCEPTION
835 WHEN NO_DATA_FOUND THEN
836 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_RECORD' );
837 FND_MESSAGE.SET_TOKEN( 'RECORD', 'party site' );
838 FND_MESSAGE.SET_TOKEN( 'VALUE',
839 NVL( TO_CHAR( p_cust_acct_site_rec.party_site_id ), 'null' ) );
840 FND_MSG_PUB.ADD;
841 RAISE FND_API.G_EXC_ERROR;
842 END;
843 l_party_site_rec.party_site_id := l_party_site_id;
844 l_party_site_rec.status := p_cust_acct_site_rec.status;
845
846 -- Call party site api to synch status with account site status
847 HZ_PARTY_SITE_V2PUB.update_party_site(
848 p_party_site_rec => l_party_site_rec,
849 p_object_version_number => l_ps_object_version_number,
850 x_return_status => x_return_status,
851 x_msg_count => l_msg_count,
852 x_msg_data => l_msg_data) ;
853
854 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
855 RAISE FND_API.G_EXC_ERROR;
856 END IF;
857
858 END IF; -- End of account site status check
859 END IF;
860 -- Debug info.
861 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
862 hz_utility_v2pub.debug(p_message=>'HZ_CUST_ACCT_SITES_PKG.Update_Row (+)',
863 p_prefix=>l_debug_prefix,
864 p_msg_level=>fnd_log.level_procedure);
865 END IF;
866
867 --Bug10332578
868 --X_STATUS is set back to Acct Site Status to handle bad data issues.
869 -- Call table-handler.
870 HZ_CUST_ACCT_SITES_PKG.Update_Row (
871 X_Rowid => l_rowid,
872 X_CUST_ACCT_SITE_ID => p_cust_acct_site_rec.cust_acct_site_id,
873 X_CUST_ACCOUNT_ID => p_cust_acct_site_rec.cust_account_id,
874 X_PARTY_SITE_ID => p_cust_acct_site_rec.party_site_id,
875 X_ATTRIBUTE_CATEGORY => p_cust_acct_site_rec.attribute_category,
876 X_ATTRIBUTE1 => p_cust_acct_site_rec.attribute1,
877 X_ATTRIBUTE2 => p_cust_acct_site_rec.attribute2,
878 X_ATTRIBUTE3 => p_cust_acct_site_rec.attribute3,
879 X_ATTRIBUTE4 => p_cust_acct_site_rec.attribute4,
880 X_ATTRIBUTE5 => p_cust_acct_site_rec.attribute5,
881 X_ATTRIBUTE6 => p_cust_acct_site_rec.attribute6,
882 X_ATTRIBUTE7 => p_cust_acct_site_rec.attribute7,
883 X_ATTRIBUTE8 => p_cust_acct_site_rec.attribute8,
884 X_ATTRIBUTE9 => p_cust_acct_site_rec.attribute9,
885 X_ATTRIBUTE10 => p_cust_acct_site_rec.attribute10,
886 X_ATTRIBUTE11 => p_cust_acct_site_rec.attribute11,
887 X_ATTRIBUTE12 => p_cust_acct_site_rec.attribute12,
888 X_ATTRIBUTE13 => p_cust_acct_site_rec.attribute13,
889 X_ATTRIBUTE14 => p_cust_acct_site_rec.attribute14,
890 X_ATTRIBUTE15 => p_cust_acct_site_rec.attribute15,
891 X_ATTRIBUTE16 => p_cust_acct_site_rec.attribute16,
892 X_ATTRIBUTE17 => p_cust_acct_site_rec.attribute17,
893 X_ATTRIBUTE18 => p_cust_acct_site_rec.attribute18,
894 X_ATTRIBUTE19 => p_cust_acct_site_rec.attribute19,
895 X_ATTRIBUTE20 => p_cust_acct_site_rec.attribute20,
896 X_GLOBAL_ATTRIBUTE_CATEGORY => p_cust_acct_site_rec.global_attribute_category,
897 X_GLOBAL_ATTRIBUTE1 => p_cust_acct_site_rec.global_attribute1,
898 X_GLOBAL_ATTRIBUTE2 => p_cust_acct_site_rec.global_attribute2,
899 X_GLOBAL_ATTRIBUTE3 => p_cust_acct_site_rec.global_attribute3,
900 X_GLOBAL_ATTRIBUTE4 => p_cust_acct_site_rec.global_attribute4,
901 X_GLOBAL_ATTRIBUTE5 => p_cust_acct_site_rec.global_attribute5,
902 X_GLOBAL_ATTRIBUTE6 => p_cust_acct_site_rec.global_attribute6,
903 X_GLOBAL_ATTRIBUTE7 => p_cust_acct_site_rec.global_attribute7,
904 X_GLOBAL_ATTRIBUTE8 => p_cust_acct_site_rec.global_attribute8,
905 X_GLOBAL_ATTRIBUTE9 => p_cust_acct_site_rec.global_attribute9,
906 X_GLOBAL_ATTRIBUTE10 => p_cust_acct_site_rec.global_attribute10,
907 X_GLOBAL_ATTRIBUTE11 => p_cust_acct_site_rec.global_attribute11,
908 X_GLOBAL_ATTRIBUTE12 => p_cust_acct_site_rec.global_attribute12,
909 X_GLOBAL_ATTRIBUTE13 => p_cust_acct_site_rec.global_attribute13,
910 X_GLOBAL_ATTRIBUTE14 => p_cust_acct_site_rec.global_attribute14,
911 X_GLOBAL_ATTRIBUTE15 => p_cust_acct_site_rec.global_attribute15,
912 X_GLOBAL_ATTRIBUTE16 => p_cust_acct_site_rec.global_attribute16,
913 X_GLOBAL_ATTRIBUTE17 => p_cust_acct_site_rec.global_attribute17,
914 X_GLOBAL_ATTRIBUTE18 => p_cust_acct_site_rec.global_attribute18,
915 X_GLOBAL_ATTRIBUTE19 => p_cust_acct_site_rec.global_attribute19,
916 X_GLOBAL_ATTRIBUTE20 => p_cust_acct_site_rec.global_attribute20,
917 X_ORIG_SYSTEM_REFERENCE => p_cust_acct_site_rec.orig_system_reference,
918 -- X_STATUS => null, /*p_cust_acct_site_rec.status (bug 3299622)*/
919 X_STATUS => p_cust_acct_site_rec.status,
920 X_CUSTOMER_CATEGORY_CODE => p_cust_acct_site_rec.customer_category_code,
921 X_LANGUAGE => p_cust_acct_site_rec.language,
922 X_KEY_ACCOUNT_FLAG => p_cust_acct_site_rec.key_account_flag,
923 X_TP_HEADER_ID => p_cust_acct_site_rec.tp_header_id,
924 X_ECE_TP_LOCATION_CODE => p_cust_acct_site_rec.ece_tp_location_code,
925 X_PRIMARY_SPECIALIST_ID => p_cust_acct_site_rec.primary_specialist_id,
926 X_SECONDARY_SPECIALIST_ID => p_cust_acct_site_rec.secondary_specialist_id,
927 X_TERRITORY_ID => p_cust_acct_site_rec.territory_id,
928 X_TERRITORY => p_cust_acct_site_rec.territory,
929 X_TRANSLATED_CUSTOMER_NAME => p_cust_acct_site_rec.translated_customer_name,
930 X_OBJECT_VERSION_NUMBER => p_object_version_number,
931 X_CREATED_BY_MODULE => p_cust_acct_site_rec.created_by_module,
932 X_APPLICATION_ID => p_cust_acct_site_rec.application_id
933 );
934
935 -- Update location should populate the change to tax assignment.
936 -- Bug Fix : 2230802.
937 SELECT LOC.LOCATION_ID INTO l_location_id
938 FROM HZ_LOCATIONS LOC,
939 HZ_PARTY_SITES PARTY_SITE,
940 HZ_CUST_ACCT_SITES_ALL ACCT_SITE -- Bug 3456489
941 WHERE ACCT_SITE.CUST_ACCT_SITE_ID = p_cust_acct_site_rec.cust_acct_site_id
942 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
943 AND PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID;
944
945 HZ_TAX_ASSIGNMENT_V2PUB.update_loc_assignment (
946 p_location_id => l_location_id,
947 p_created_by_module => p_cust_acct_site_rec.created_by_module,
948 p_application_id => p_cust_acct_site_rec.application_id,
949 x_return_status => x_return_status,
950 x_msg_count => l_msg_count,
951 x_msg_data => l_msg_data,
952 x_loc_id => l_loc_id
953 );
954
955 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
956 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
957 RAISE FND_API.G_EXC_ERROR;
958 ELSE
959 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
960 END IF;
961 END IF;
962
963 -- Debug info.
964 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
965 hz_utility_v2pub.debug(p_message=>'HZ_CUST_ACCT_SITES_PKG.Update_Row (-)',
966 p_prefix=>l_debug_prefix,
967 p_msg_level=>fnd_log.level_procedure);
968 END IF;
969
970 -- Debug info.
971 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
972 hz_utility_v2pub.debug(p_message=>'do_update_cust_acct_site (-)',
973 p_prefix=>l_debug_prefix,
974 p_msg_level=>fnd_log.level_procedure);
975 END IF;
976
977 END do_update_cust_acct_site;
978
979 /**
980 * PRIVATE PROCEDURE do_create_cust_site_use
981 *
982 * DESCRIPTION
983 * Private procedure to create customer account site use.
984 *
985 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
986 * HZ_ACCOUNT_VALIDATE_V2PUB.validate_cust_site_use
987 * HZ_CUST_SITE_USES_PKG.Insert_Row
988 *
989 * ARGUMENTS
990 * IN:
991 * p_create_profile If it is set to FND_API.G_TRUE, API create customer
992 * profile based on the customer profile record passed
993 * in.
994 * p_create_profile_amt If it is set to FND_API.G_TRUE, API create customer
995 * profile amounts by copying corresponding data
996 * from customer profile class amounts.
997 * IN/OUT:
998 * p_cust_site_use_rec Customer account site use record.
999 * p_customer_profile_rec Customer profile record. One customer account
1000 * must have a customer profile.
1001 * x_return_status Return status after the call. The status can
1002 * be FND_API.G_RET_STS_SUCCESS (success),
1003 * FND_API.G_RET_STS_ERROR (error),
1004 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1005 * OUT:
1006 * x_site_use_id Customer account site use ID.
1007 *
1008 * NOTES
1009 *
1010 * MODIFICATION HISTORY
1011 *
1012 * 07-23-2001 Jianying Huang o Created.
1013 * 12-22-2003 Rajib Ranjan Borah o Bug 3322154.The status field was not considered in the
1014 * check to find out if a party site use record is also to
1015 * be created.
1016 * 12-MAY-2005 Rajib Ranjan Borah o TCA SSA Uptake (Bug 3456489)
1017 * 26-Sep-2007 Sudhir Gokavarapu o Bug 6315081 [FORWARD PORT BUG 6132727] Modified primary site
1018 * use existance check query for performance issues
1019 */
1020
1021 PROCEDURE do_create_cust_site_use (
1022 p_cust_site_use_rec IN OUT NOCOPY CUST_SITE_USE_REC_TYPE,
1023 p_customer_profile_rec IN OUT NOCOPY HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE,
1024 p_create_profile IN VARCHAR2 := FND_API.G_TRUE,
1025 p_create_profile_amt IN VARCHAR2 := FND_API.G_TRUE,
1026 x_site_use_id OUT NOCOPY NUMBER,
1027 x_return_status IN OUT NOCOPY VARCHAR2
1028 ) IS
1029
1030 l_debug_prefix VARCHAR2(30) := ''; --'do_create_cust_site_use'
1031
1032 l_dummy VARCHAR2(1);
1033 l_message_count NUMBER;
1034 l_msg_count NUMBER;
1035 l_msg_data VARCHAR2(2000);
1036 l_flag VARCHAR2(1);
1037 l_return_status VARCHAR2(1);
1038
1039 l_party_site_use_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_USE_REC_TYPE;
1040 l_party_site_id NUMBER;
1041 l_party_site_use_id NUMBER;
1042 l_cust_account_profile_id NUMBER;
1043 l_bill_to_flag HZ_CUST_ACCT_SITES.bill_to_flag%TYPE;
1044 l_ship_to_flag HZ_CUST_ACCT_SITES.ship_to_flag%TYPE;
1045 l_market_flag HZ_CUST_ACCT_SITES.market_flag%TYPE;
1046 l_orig_sys_reference_rec HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE;
1047
1048 l_cnt number := 1;
1049 l_cust_acct_id number;
1050 -- Bug 8970479 : Cursor to get cust_account_profile_id from table hz_customer_profiles
1051 -- if record already exists for this site with 'DUNNING' or 'STATEMENTS' site_use_id
1052 CURSOR c_check_site_use_id IS
1053 SELECT hcp.cust_account_profile_id
1054 FROM hz_customer_profiles hcp,
1055 hz_cust_site_uses_all hcsu
1056 WHERE hcsu.cust_acct_site_id = p_cust_site_use_rec.cust_acct_site_id
1057 AND hcp.site_use_id = hcsu.site_use_id
1058 AND hcsu.site_use_code IN ('DUN','STMTS');
1059
1060 l_cust_acct_prof_id HZ_CUSTOMER_PROFILES.CUST_ACCOUNT_PROFILE_ID%TYPE ;
1061 BEGIN
1062
1063 -- Debug info.
1064 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1065 hz_utility_v2pub.debug(p_message=>'do_create_cust_site_use (+)',
1066 p_prefix=>l_debug_prefix,
1067 p_msg_level=>fnd_log.level_procedure);
1068 END IF;
1069
1070 /* 3456489 Added for Shared Service Uptake */
1071 IF (p_cust_site_use_rec.org_id is NULL or
1072 p_cust_site_use_rec.org_id = fnd_api.g_miss_num) then
1073 BEGIN
1074 SELECT org_id
1075 INTO p_cust_site_use_rec.org_id
1076 FROM HZ_CUST_ACCT_SITES_ALL
1077 WHERE cust_acct_site_id
1078 = p_cust_site_use_rec.cust_acct_site_id;
1079 EXCEPTION
1080 WHEN NO_DATA_FOUND THEN
1081 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_RECORD' );
1082 FND_MESSAGE.SET_TOKEN( 'RECORD', 'customer account site' );
1083 FND_MESSAGE.SET_TOKEN( 'VALUE',
1084 NVL( TO_CHAR(
1085 p_cust_site_use_rec.cust_acct_site_id ), 'null' ) );
1086 FND_MSG_PUB.ADD;
1087 RAISE FND_API.G_EXC_ERROR;
1088 END;
1089 END IF;
1090
1091 BEGIN
1092 MO_GLOBAL.validate_orgid_pub_api(p_cust_site_use_rec.org_id,'N',l_return_status);
1093 EXCEPTION
1094 WHEN OTHERS
1095 THEN
1096 RAISE FND_API.G_EXC_ERROR;
1097 END;
1098
1099
1100 -- Validate site use record.
1101 HZ_ACCOUNT_VALIDATE_V2PUB.validate_cust_site_use (
1102 p_create_update_flag => 'C',
1103 p_cust_site_use_rec => p_cust_site_use_rec,
1104 p_rowid => NULL,
1105 x_return_status => x_return_status );
1106
1107 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1108 RAISE FND_API.G_EXC_ERROR;
1109 END IF;
1110
1111 -- Create party site use of same type if one does not exist.
1112
1113 l_message_count := FND_MSG_PUB.Count_Msg();
1114
1115 HZ_UTILITY_V2PUB.validate_lookup (
1116 p_column => 'site_use_code',
1117 p_lookup_type => 'PARTY_SITE_USE_CODE',
1118 p_column_value => p_cust_site_use_rec.site_use_code,
1119 x_return_status => x_return_status );
1120
1121 -- Debug info.
1122 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1123 hz_utility_v2pub.debug(p_message=>'check if site_use_code is a valid site_use_code in party level. ' ||
1124 'x_return_status = ' || x_return_status,
1125 p_prefix =>l_debug_prefix,
1126 p_msg_level=>fnd_log.level_statement);
1127 END IF;
1128
1129 -- site_use_code is not in a valid site_use_code in party level.
1130 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1131 FND_MSG_PUB.DELETE_MSG( l_message_count + 1 );
1132 x_return_status := FND_API.G_RET_STS_SUCCESS;
1133 ELSE
1134 -- Create party site use
1135 SELECT PARTY_SITE_ID INTO l_party_site_id
1136 FROM HZ_CUST_ACCT_SITES_ALL -- Bug 3456489
1137 WHERE CUST_ACCT_SITE_ID = p_cust_site_use_rec.cust_acct_site_id;
1138
1139 BEGIN
1140 SELECT 'Y' INTO l_dummy
1141 FROM HZ_PARTY_SITE_USES
1142 WHERE PARTY_SITE_ID = l_party_site_id
1143 AND SITE_USE_TYPE = p_cust_site_use_rec.site_use_code
1144 AND STATUS = 'A'; --Bug 3322154
1145 EXCEPTION
1146 WHEN NO_DATA_FOUND THEN
1147 l_party_site_use_rec.party_site_id := l_party_site_id;
1148 l_party_site_use_rec.site_use_type := p_cust_site_use_rec.site_use_code;
1149 l_party_site_use_rec.created_by_module := p_cust_site_use_rec.created_by_module;
1150 l_party_site_use_rec.application_id := p_cust_site_use_rec.application_id;
1151
1152 HZ_PARTY_SITE_V2PUB.create_party_site_use (
1153 p_party_site_use_rec => l_party_site_use_rec,
1154 x_return_status => x_return_status,
1155 x_msg_count => l_msg_count,
1156 x_msg_data => l_msg_data,
1157 x_party_site_use_id => l_party_site_use_id );
1158
1159 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1160 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1161 RAISE FND_API.G_EXC_ERROR;
1162 ELSE
1163 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1164 END IF;
1165 END IF;
1166 END;
1167 END IF;
1168
1169 -- Debug info.
1170 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1171 hz_utility_v2pub.debug(p_message=>'HZ_CUST_SITE_USES_PKG.Insert_Row (+)',
1172 p_prefix=>l_debug_prefix,
1173 p_msg_level=>fnd_log.level_procedure);
1174 END IF;
1175
1176 -- Bug 2643624.
1177 -- The first active site usage for a combination of particular site and org
1178 -- combination is to be set to primary, and when a new record is entered with
1179 -- this combination with the primary flag set, then the first one must be unset.
1180
1181 IF p_cust_site_use_rec.status IS NULL OR
1182 p_cust_site_use_rec.status = fnd_api.g_miss_char OR
1183 p_cust_site_use_rec.status = 'A'
1184 THEN
1185 IF p_cust_site_use_rec.primary_flag = 'Y' THEN
1186 --we must unset the previous set primary_flag.
1187 do_unset_prim_cust_site_use(p_cust_site_use_rec.site_use_code,
1188 p_cust_site_use_rec.cust_acct_site_id,
1189 p_cust_site_use_rec.org_id);
1190 ELSE
1191
1192 BEGIN
1193 SELECT cust_account_id into l_cust_acct_id
1194 FROM hz_cust_acct_sites_all -- Bug 3456489
1195 WHERE cust_acct_site_id = p_cust_site_use_rec.cust_acct_site_id;
1196 EXCEPTION
1197 WHEN NO_DATA_FOUND THEN
1198 l_cnt := 0;
1199 END;
1200
1201 IF l_cnt <> 0 THEN
1202 BEGIN
1203 /*
1204 -- Bug 6315081 : FORWARD PORT BUG 6132727
1205 -- New Query Below
1206 SELECT count(*) into l_cnt
1207 FROM hz_cust_accounts a,
1208 hz_cust_acct_sites_all cas,
1209 hz_cust_site_uses_all su
1210 WHERE
1211 a.cust_account_id = l_cust_acct_id
1212 and a.cust_account_id = cas.cust_account_id
1213 and cas.cust_acct_site_id = su.cust_acct_site_id
1214 and su.site_use_code = p_cust_site_use_rec.site_use_code
1215 and su.status = 'A'
1216 and su.primary_flag = 'Y'
1217 and cas.org_id = p_cust_site_use_rec.org_id -- TCA SSA Uptake (Bug 3456489)
1218 and su.org_id = p_cust_site_use_rec.org_id; -- TCA SSA Uptake (Bug 3456489) */
1219
1220 SELECT count(*) into l_cnt
1221 FROM hz_cust_acct_sites_all cas,
1222 hz_cust_site_uses_all su
1223 WHERE
1224 cas.cust_account_id = l_cust_acct_id
1225 and cas.status = 'A'
1226 and cas.cust_acct_site_id = su.cust_acct_site_id
1227 and su.site_use_code = p_cust_site_use_rec.site_use_code
1228 and su.status = 'A'
1229 and su.primary_flag = 'Y'
1230 and cas.org_id = p_cust_site_use_rec.org_id -- TCA SSA Uptake (Bug 3456489)
1231 and su.org_id = p_cust_site_use_rec.org_id -- TCA SSA Uptake (Bug 3456489)
1232 and rownum <=1 ;
1233 END;
1234 END IF;
1235
1236 IF l_cnt <= 0 THEN
1237 p_cust_site_use_rec.primary_flag := 'Y';
1238 ELSE
1239 p_cust_site_use_rec.primary_flag := 'N';
1240 END IF;
1241
1242 END IF;
1243 END IF;
1244
1245 -- Call table-handler.
1246 HZ_CUST_SITE_USES_PKG.Insert_Row (
1247 X_SITE_USE_ID => p_cust_site_use_rec.site_use_id,
1248 X_CUST_ACCT_SITE_ID => p_cust_site_use_rec.cust_acct_site_id,
1249 X_SITE_USE_CODE => p_cust_site_use_rec.site_use_code,
1250 X_PRIMARY_FLAG => p_cust_site_use_rec.primary_flag,
1251 X_STATUS => p_cust_site_use_rec.status,
1252 X_LOCATION => p_cust_site_use_rec.location,
1253 X_CONTACT_ID => p_cust_site_use_rec.contact_id,
1254 X_BILL_TO_SITE_USE_ID => p_cust_site_use_rec.bill_to_site_use_id,
1255 X_ORIG_SYSTEM_REFERENCE => p_cust_site_use_rec.orig_system_reference,
1256 X_SIC_CODE => p_cust_site_use_rec.sic_code,
1257 X_PAYMENT_TERM_ID => p_cust_site_use_rec.payment_term_id,
1258 X_GSA_INDICATOR => p_cust_site_use_rec.gsa_indicator,
1259 X_SHIP_PARTIAL => p_cust_site_use_rec.ship_partial,
1260 X_SHIP_VIA => p_cust_site_use_rec.ship_via,
1261 X_FOB_POINT => p_cust_site_use_rec.fob_point,
1262 X_ORDER_TYPE_ID => p_cust_site_use_rec.order_type_id,
1263 X_PRICE_LIST_ID => p_cust_site_use_rec.price_list_id,
1264 X_FREIGHT_TERM => p_cust_site_use_rec.freight_term,
1265 X_WAREHOUSE_ID => p_cust_site_use_rec.warehouse_id,
1266 X_TERRITORY_ID => p_cust_site_use_rec.territory_id,
1267 X_ATTRIBUTE_CATEGORY => p_cust_site_use_rec.attribute_category,
1268 X_ATTRIBUTE1 => p_cust_site_use_rec.attribute1,
1269 X_ATTRIBUTE2 => p_cust_site_use_rec.attribute2,
1270 X_ATTRIBUTE3 => p_cust_site_use_rec.attribute3,
1271 X_ATTRIBUTE4 => p_cust_site_use_rec.attribute4,
1272 X_ATTRIBUTE5 => p_cust_site_use_rec.attribute5,
1273 X_ATTRIBUTE6 => p_cust_site_use_rec.attribute6,
1274 X_ATTRIBUTE7 => p_cust_site_use_rec.attribute7,
1275 X_ATTRIBUTE8 => p_cust_site_use_rec.attribute8,
1276 X_ATTRIBUTE9 => p_cust_site_use_rec.attribute9,
1277 X_ATTRIBUTE10 => p_cust_site_use_rec.attribute10,
1278 X_TAX_REFERENCE => p_cust_site_use_rec.tax_reference,
1279 X_SORT_PRIORITY => p_cust_site_use_rec.sort_priority,
1280 X_TAX_CODE => p_cust_site_use_rec.tax_code,
1281 X_ATTRIBUTE11 => p_cust_site_use_rec.attribute11,
1282 X_ATTRIBUTE12 => p_cust_site_use_rec.attribute12,
1283 X_ATTRIBUTE13 => p_cust_site_use_rec.attribute13,
1284 X_ATTRIBUTE14 => p_cust_site_use_rec.attribute14,
1285 X_ATTRIBUTE15 => p_cust_site_use_rec.attribute15,
1286 X_ATTRIBUTE16 => p_cust_site_use_rec.attribute16,
1287 X_ATTRIBUTE17 => p_cust_site_use_rec.attribute17,
1288 X_ATTRIBUTE18 => p_cust_site_use_rec.attribute18,
1289 X_ATTRIBUTE19 => p_cust_site_use_rec.attribute19,
1290 X_ATTRIBUTE20 => p_cust_site_use_rec.attribute20,
1291 X_ATTRIBUTE21 => p_cust_site_use_rec.attribute21,
1292 X_ATTRIBUTE22 => p_cust_site_use_rec.attribute22,
1293 X_ATTRIBUTE23 => p_cust_site_use_rec.attribute23,
1294 X_ATTRIBUTE24 => p_cust_site_use_rec.attribute24,
1295 X_ATTRIBUTE25 => p_cust_site_use_rec.attribute25,
1296 X_DEMAND_CLASS_CODE => p_cust_site_use_rec.demand_class_code,
1297 X_TAX_HEADER_LEVEL_FLAG => p_cust_site_use_rec.tax_header_level_flag,
1298 X_TAX_ROUNDING_RULE => p_cust_site_use_rec.tax_rounding_rule,
1299 X_GLOBAL_ATTRIBUTE1 => p_cust_site_use_rec.global_attribute1,
1300 X_GLOBAL_ATTRIBUTE2 => p_cust_site_use_rec.global_attribute2,
1301 X_GLOBAL_ATTRIBUTE3 => p_cust_site_use_rec.global_attribute3,
1302 X_GLOBAL_ATTRIBUTE4 => p_cust_site_use_rec.global_attribute4,
1303 X_GLOBAL_ATTRIBUTE5 => p_cust_site_use_rec.global_attribute5,
1304 X_GLOBAL_ATTRIBUTE6 => p_cust_site_use_rec.global_attribute6,
1305 X_GLOBAL_ATTRIBUTE7 => p_cust_site_use_rec.global_attribute7,
1306 X_GLOBAL_ATTRIBUTE8 => p_cust_site_use_rec.global_attribute8,
1307 X_GLOBAL_ATTRIBUTE9 => p_cust_site_use_rec.global_attribute9,
1308 X_GLOBAL_ATTRIBUTE10 => p_cust_site_use_rec.global_attribute10,
1309 X_GLOBAL_ATTRIBUTE11 => p_cust_site_use_rec.global_attribute11,
1310 X_GLOBAL_ATTRIBUTE12 => p_cust_site_use_rec.global_attribute12,
1311 X_GLOBAL_ATTRIBUTE13 => p_cust_site_use_rec.global_attribute13,
1312 X_GLOBAL_ATTRIBUTE14 => p_cust_site_use_rec.global_attribute14,
1313 X_GLOBAL_ATTRIBUTE15 => p_cust_site_use_rec.global_attribute15,
1314 X_GLOBAL_ATTRIBUTE16 => p_cust_site_use_rec.global_attribute16,
1315 X_GLOBAL_ATTRIBUTE17 => p_cust_site_use_rec.global_attribute17,
1316 X_GLOBAL_ATTRIBUTE18 => p_cust_site_use_rec.global_attribute18,
1317 X_GLOBAL_ATTRIBUTE19 => p_cust_site_use_rec.global_attribute19,
1318 X_GLOBAL_ATTRIBUTE20 => p_cust_site_use_rec.global_attribute20,
1319 X_GLOBAL_ATTRIBUTE_CATEGORY => p_cust_site_use_rec.global_attribute_category,
1320 X_PRIMARY_SALESREP_ID => p_cust_site_use_rec.primary_salesrep_id,
1321 X_FINCHRG_RECEIVABLES_TRX_ID => p_cust_site_use_rec.finchrg_receivables_trx_id,
1322 X_DATES_NEGATIVE_TOLERANCE => p_cust_site_use_rec.dates_negative_tolerance,
1323 X_DATES_POSITIVE_TOLERANCE => p_cust_site_use_rec.dates_positive_tolerance,
1324 X_DATE_TYPE_PREFERENCE => p_cust_site_use_rec.date_type_preference,
1325 X_OVER_SHIPMENT_TOLERANCE => p_cust_site_use_rec.over_shipment_tolerance,
1326 X_UNDER_SHIPMENT_TOLERANCE => p_cust_site_use_rec.under_shipment_tolerance,
1327 X_ITEM_CROSS_REF_PREF => p_cust_site_use_rec.item_cross_ref_pref,
1328 X_OVER_RETURN_TOLERANCE => p_cust_site_use_rec.over_return_tolerance,
1329 X_UNDER_RETURN_TOLERANCE => p_cust_site_use_rec.under_return_tolerance,
1330 X_SHIP_SETS_INCLUDE_LINES_FLAG => p_cust_site_use_rec.ship_sets_include_lines_flag,
1331 X_ARRIVALSETS_INCLUDE_LINES_FG => p_cust_site_use_rec.arrivalsets_include_lines_flag,
1332 X_SCHED_DATE_PUSH_FLAG => p_cust_site_use_rec.sched_date_push_flag,
1333 X_INVOICE_QUANTITY_RULE => p_cust_site_use_rec.invoice_quantity_rule,
1334 X_PRICING_EVENT => p_cust_site_use_rec.pricing_event,
1335 X_GL_ID_REC => p_cust_site_use_rec.gl_id_rec,
1336 X_GL_ID_REV => p_cust_site_use_rec.gl_id_rev,
1337 X_GL_ID_TAX => p_cust_site_use_rec.gl_id_tax,
1338 X_GL_ID_FREIGHT => p_cust_site_use_rec.gl_id_freight,
1339 X_GL_ID_CLEARING => p_cust_site_use_rec.gl_id_clearing,
1340 X_GL_ID_UNBILLED => p_cust_site_use_rec.gl_id_unbilled,
1341 X_GL_ID_UNEARNED => p_cust_site_use_rec.gl_id_unearned,
1342 X_GL_ID_UNPAID_REC => p_cust_site_use_rec.gl_id_unpaid_rec,
1343 X_GL_ID_REMITTANCE => p_cust_site_use_rec.gl_id_remittance,
1344 X_GL_ID_FACTOR => p_cust_site_use_rec.gl_id_factor,
1345 X_TAX_CLASSIFICATION => p_cust_site_use_rec.tax_classification,
1346 X_OBJECT_VERSION_NUMBER => 1,
1347 X_CREATED_BY_MODULE => p_cust_site_use_rec.created_by_module,
1348 X_APPLICATION_ID => p_cust_site_use_rec.application_id,
1349 X_ORG_ID => p_cust_site_use_rec.org_id -- Bug 3456489
1350 );
1351
1352 -- Debug info.
1353 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1354 hz_utility_v2pub.debug(p_message=>'HZ_CUST_SITE_USES_PKG.Insert_Row (-) ' ||
1355 'x_site_use_id = ' || p_cust_site_use_rec.site_use_id,
1356 p_prefix=>l_debug_prefix,
1357 p_msg_level=>fnd_log.level_procedure);
1358 END IF;
1359
1360 /*Bug 8970479 :
1361 Updating tables hz_customer_profiles and hz_cust_profile_amts if bill-to business
1362 purpose is created and if these 2 tables contains site_use_id of 'DUNNING' or 'STATEMENTS'
1363 business purposes for current site*/
1364
1365 IF (p_cust_site_use_rec.site_use_code = 'BILL_TO') THEN
1366
1367 OPEN c_check_site_use_id ;
1368 FETCH c_check_site_use_id INTO l_cust_account_profile_id ;
1369
1370 IF (c_check_site_use_id%FOUND) THEN
1371
1372 UPDATE hz_customer_profiles
1373 SET site_use_id = p_cust_site_use_rec.site_use_id
1374 WHERE cust_account_profile_id = l_cust_account_profile_id ;
1375
1376 UPDATE hz_cust_profile_amts
1377 SET site_use_id = p_cust_site_use_rec.site_use_id
1378 WHERE cust_account_profile_id = l_cust_account_profile_id ;
1379
1380 -- Debug info.
1381 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1382 hz_utility_v2pub.debug(p_message=>'Updated hz_customer_profiles and hz_cust_profiles/amts with site_use_id of DUNNING or STATEMENTS' ,
1383 p_prefix =>l_debug_prefix,
1384 p_msg_level=>fnd_log.level_statement);
1385 END IF;
1386 -- Debug info.
1387 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1388 hz_utility_v2pub.debug(p_message=>'Updated Site Use Id '||p_cust_site_use_rec.site_use_id ||' at Profile Id '||l_cust_account_profile_id,
1389 p_prefix =>l_debug_prefix,
1390 p_msg_level=>fnd_log.level_statement);
1391 END IF;
1392
1393 END IF;
1394 CLOSE c_check_site_use_id ;
1395
1396 END IF;
1397
1398 /*Bug 8970479 : END */
1399
1400 if (p_cust_site_use_rec.orig_system_reference is not null and p_cust_site_use_rec.orig_system_reference <>fnd_api.g_miss_char ) then
1401 if (p_cust_site_use_rec.orig_system is null OR p_cust_site_use_rec.orig_system =fnd_api.g_miss_char) then
1402 p_cust_site_use_rec.orig_system := 'UNKNOWN';
1403 end if;
1404 end if;
1405
1406
1407 if (p_cust_site_use_rec.orig_system is not null and p_cust_site_use_rec.orig_system<>fnd_api.g_miss_char ) then
1408
1409 l_orig_sys_reference_rec.orig_system := p_cust_site_use_rec.orig_system;
1410 l_orig_sys_reference_rec.orig_system_reference := p_cust_site_use_rec.orig_system_reference;
1411 l_orig_sys_reference_rec.owner_table_name := 'HZ_CUST_SITE_USES_ALL';
1412 l_orig_sys_reference_rec.owner_table_id := p_cust_site_use_rec.site_use_id;
1413 l_orig_sys_reference_rec.created_by_module := p_cust_site_use_rec.created_by_module;
1414 hz_orig_system_ref_pub.create_orig_system_reference(
1415 FND_API.G_FALSE,
1416 l_orig_sys_reference_rec,
1417 x_return_status,
1418 l_msg_count,
1419 l_msg_data);
1420 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1421 RAISE FND_API.G_EXC_ERROR;
1422 END IF;
1423
1424 end if;
1425
1426 -- If this is a active bill_to or ship_to or market,
1427 -- set the appropriate denormalized flag in hz_cust_acct_sites.
1428
1429 IF p_cust_site_use_rec.site_use_code IN ('BILL_TO', 'SHIP_TO', 'MARKET' ) THEN
1430 IF p_cust_site_use_rec.status = 'A' OR
1431 p_cust_site_use_rec.status IS NULL OR
1432 p_cust_site_use_rec.status = FND_API.G_MISS_CHAR
1433 THEN
1434 IF p_cust_site_use_rec.primary_flag = 'Y' THEN
1435 l_flag := 'P';
1436 ELSE
1437 l_flag := 'Y';
1438 END IF;
1439 ELSE
1440 l_flag := NULL;
1441 END IF;
1442
1443 denormalize_site_use_flag (
1444 p_cust_site_use_rec.cust_acct_site_id,
1445 p_cust_site_use_rec.site_use_code,
1446 l_flag );
1447
1448 END IF;
1449
1450 IF p_create_profile = FND_API.G_TRUE THEN
1451
1452 -- Create the profile for the site use
1453
1454 p_customer_profile_rec.site_use_id := p_cust_site_use_rec.site_use_id;
1455 p_customer_profile_rec.created_by_module := p_cust_site_use_rec.created_by_module;
1456 p_customer_profile_rec.application_id := p_cust_site_use_rec.application_id;
1457
1458 SELECT CUST_ACCOUNT_ID INTO p_customer_profile_rec.cust_account_id
1459 FROM HZ_CUST_ACCT_SITES_ALL -- Bug 3456489
1460 WHERE CUST_ACCT_SITE_ID = p_cust_site_use_rec.cust_acct_site_id;
1461
1462 HZ_CUSTOMER_PROFILE_V2PUB.create_customer_profile (
1463 p_customer_profile_rec => p_customer_profile_rec,
1464 p_create_profile_amt => p_create_profile_amt,
1465 x_return_status => x_return_status,
1466 x_msg_count => l_msg_count,
1467 x_msg_data => l_msg_data,
1468 x_cust_account_profile_id => l_cust_account_profile_id );
1469
1470 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1471 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1472 RAISE FND_API.G_EXC_ERROR;
1473 ELSE
1474 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1475 END IF;
1476 END IF;
1477
1478 END IF;
1479
1480 x_site_use_id := p_cust_site_use_rec.site_use_id;
1481
1482 -- Debug info.
1483 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1484 hz_utility_v2pub.debug(p_message=>'do_create_cust_site_use (-)',
1485 p_prefix=>l_debug_prefix,
1486 p_msg_level=>fnd_log.level_procedure);
1487 END IF;
1488
1489
1490 END do_create_cust_site_use;
1491
1492 /**
1493 * PRIVATE PROCEDURE do_update_cust_site_use
1494 *
1495 * DESCRIPTION
1496 * Private procedure to update customer account site use.
1497 *
1498 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1499 * HZ_ACCOUNT_VALIDATE_V2PUB.validate_cust_site_use
1500 * HZ_CUST_SITE_USES_PKG.Update_Row
1501 *
1502 * ARGUMENTS
1503 * IN/OUT:
1504 * p_cust_site_use_rec Customer account site use record.
1505 * p_object_version_number Used for locking the being updated record.
1506 * x_return_status Return status after the call. The status can
1507 * be FND_API.G_RET_STS_SUCCESS (success),
1508 * FND_API.G_RET_STS_ERROR (error),
1509 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1510 *
1511 * NOTES
1512 *
1513 * MODIFICATION HISTORY
1514 *
1515 * 07-23-2001 Jianying Huang o Created.
1516 * 09-08-2003 Rajib Ranjan Borah o Bug 3085557.Site Use flag was earlier not updated
1517 * if the user had modified the STATUS flag.
1518 * 12-08-2003 Rajib Ranjan Borah o Bug 3294182.Site use flags are now updated for scenarios
1519 * introduced by fix 2643624.
1520 * 12-MAY-2005 Rajib Ranjan Borah o TCA SSA Uptake (Bug 3456489)
1521 * 26-Sep-2007 Sudhir Gokavarapu o Bug 6315081 [FORWARD PORT BUG6132727] Changed do_create_cust_site_use
1522 * and do_update_cust_site_use api.
1523 */
1524
1525 PROCEDURE do_update_cust_site_use (
1526 p_cust_site_use_rec IN OUT NOCOPY CUST_SITE_USE_REC_TYPE,
1527 p_object_version_number IN OUT NOCOPY NUMBER,
1528 x_return_status IN OUT NOCOPY VARCHAR2
1529 ) IS
1530
1531 l_debug_prefix VARCHAR2(30) := ''; --'do_update_cust_site_use'
1532
1533 l_rowid ROWID := NULL;
1534 l_object_version_number NUMBER;
1535 l_flag VARCHAR2(1);
1536 l_denormalize BOOLEAN := FALSE;
1537
1538 l_site_use_code HZ_CUST_SITE_USES.site_use_code%TYPE;
1539 l_cust_acct_site_id NUMBER;
1540 l_primary_flag HZ_CUST_SITE_USES.primary_flag%TYPE;
1541 l_status HZ_CUST_SITE_USES.status%TYPE;
1542 l_orig_sys_reference_rec HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE;
1543
1544 /* Bug Fix:5036975 */
1545 l_party_site_use_id HZ_PARTY_SITE_USES.party_site_use_id%TYPE;
1546 l_party_site_use_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_USE_REC_TYPE;
1547 l_created_by_module HZ_CUST_SITE_USES.created_by_module%TYPE;
1548 l_application_id HZ_CUST_SITE_USES.application_id%TYPE;
1549 l_message_count NUMBER;
1550 l_party_site_id NUMBER;
1551 l_dummy VARCHAR2(1);
1552 l_msg_count NUMBER;
1553 l_msg_data VARCHAR2(2000);
1554 /* Bug Fix : 5036975 */
1555
1556 l_cnt number := 1;
1557 l_cust_acct_id number;
1558 l_minrowid rowid;
1559 --Bug 3294182
1560 l_casid number;
1561
1562
1563 BEGIN
1564
1565 -- Debug info.
1566 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1567 hz_utility_v2pub.debug(p_message=> 'do_update_cust_site_use (+)',
1568 p_prefix=>l_debug_prefix,
1569 p_msg_level=>fnd_log.level_procedure);
1570 END IF;
1571
1572
1573 /* Bug 3456489 Modified for Shared Services Uptake. */
1574
1575 -- Lock record.
1576 BEGIN
1577 SELECT ROWID, OBJECT_VERSION_NUMBER, CUST_ACCT_SITE_ID,
1578 SITE_USE_CODE, PRIMARY_FLAG, STATUS, ORG_ID,CREATED_BY_MODULE,APPLICATION_ID
1579 INTO l_rowid, l_object_version_number,
1580 l_cust_acct_site_id, l_site_use_code, l_primary_flag, l_status,
1581 p_cust_site_use_rec.org_id,l_created_by_module,l_application_id
1582 FROM HZ_CUST_SITE_USES
1583 WHERE SITE_USE_ID = p_cust_site_use_rec.site_use_id
1584 FOR UPDATE NOWAIT;
1585
1586 IF NOT (
1587 ( p_object_version_number IS NULL AND
1588 l_object_version_number IS NULL ) OR
1589 ( p_object_version_number IS NOT NULL AND
1590 l_object_version_number IS NOT NULL AND
1591 p_object_version_number = l_object_version_number ) )
1592 THEN
1593 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_RECORD_CHANGED' );
1594 FND_MESSAGE.SET_TOKEN( 'TABLE', 'hz_cust_site_uses' );
1595 FND_MSG_PUB.ADD;
1596 RAISE FND_API.G_EXC_ERROR;
1597 END IF;
1598
1599 p_object_version_number := NVL( l_object_version_number, 1 ) + 1;
1600
1601 EXCEPTION
1602 WHEN NO_DATA_FOUND THEN
1603 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_RECORD' );
1604 FND_MESSAGE.SET_TOKEN( 'RECORD', 'customer site use' );
1605 FND_MESSAGE.SET_TOKEN( 'VALUE',
1606 NVL( TO_CHAR( p_cust_site_use_rec.site_use_id ), 'null' ) );
1607 FND_MSG_PUB.ADD;
1608 RAISE FND_API.G_EXC_ERROR;
1609 END;
1610
1611 -- Validate cust site use record
1612 HZ_ACCOUNT_VALIDATE_V2PUB.validate_cust_site_use (
1613 p_create_update_flag => 'U',
1614 p_cust_site_use_rec => p_cust_site_use_rec,
1615 p_rowid => l_rowid,
1616 x_return_status => x_return_status );
1617
1618 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1619 RAISE FND_API.G_EXC_ERROR;
1620 END IF;
1621
1622 ---Create party site use of same type if one does not exist.
1623 --Bug No. 5036975
1624 IF p_cust_site_use_rec.status = 'A' THEN
1625 l_message_count := FND_MSG_PUB.Count_Msg();
1626 HZ_UTILITY_V2PUB.validate_lookup (
1627 p_column => 'site_use_code',
1628 p_lookup_type => 'PARTY_SITE_USE_CODE',
1629 p_column_value => p_cust_site_use_rec.site_use_code,
1630 x_return_status => x_return_status );
1631
1632 -- Debug info.
1633 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1634 hz_utility_v2pub.debug(p_message=>'check if site_use_code is a valid site_use_code in party level. ' ||
1635 'x_return_status = ' || x_return_status,
1636 p_prefix =>l_debug_prefix,
1637 p_msg_level=>fnd_log.level_statement);
1638 END IF;
1639
1640 -- site_use_code is not in a valid site_use_code in party level.
1641 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1642 FND_MSG_PUB.DELETE_MSG( l_message_count + 1 );
1643 x_return_status := FND_API.G_RET_STS_SUCCESS;
1644 ELSE
1645 -- Create party site use
1646
1647 SELECT PARTY_SITE_ID INTO l_party_site_id
1648 FROM HZ_CUST_ACCT_SITES_ALL
1649 WHERE CUST_ACCT_SITE_ID = l_cust_acct_site_id;
1650 BEGIN
1651 SELECT 'Y' INTO l_dummy
1652 FROM HZ_PARTY_SITE_USES
1653 WHERE PARTY_SITE_ID = l_party_site_id
1654 AND SITE_USE_TYPE = l_site_use_code
1655 AND STATUS = 'A';
1656 EXCEPTION
1657 WHEN NO_DATA_FOUND THEN
1658
1659 l_party_site_use_rec.party_site_id := l_party_site_id;
1660 l_party_site_use_rec.site_use_type := l_site_use_code;
1661 l_party_site_use_rec.created_by_module := l_created_by_module;
1662 l_party_site_use_rec.application_id := l_application_id;
1663
1664
1665 HZ_PARTY_SITE_V2PUB.create_party_site_use (
1666 p_party_site_use_rec => l_party_site_use_rec,
1667 x_return_status => x_return_status,
1668 x_msg_count => l_msg_count,
1669 x_msg_data => l_msg_data,
1670 x_party_site_use_id => l_party_site_use_id );
1671
1672 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1673 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1674 RAISE FND_API.G_EXC_ERROR;
1675 ELSE
1676 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1677 END IF;
1678 END IF;
1679 END;
1680 END IF;
1681 END IF;
1682
1683 -- Debug info.
1684 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1685 hz_utility_v2pub.debug(p_message=>'HZ_CUST_SITE_USES_PKG.Insert_Row (+)',
1686 p_prefix=>l_debug_prefix,
1687 p_msg_level=>fnd_log.level_procedure);
1688 END IF;
1689 --Bug Fix:5036975
1690
1691 if (p_cust_site_use_rec.orig_system is not null
1692 and p_cust_site_use_rec.orig_system <>fnd_api.g_miss_char)
1693 and (p_cust_site_use_rec.orig_system_reference is not null
1694 and p_cust_site_use_rec.orig_system_reference <>fnd_api.g_miss_char)
1695 then
1696 p_cust_site_use_rec.orig_system_reference := null;
1697 -- In mosr, we have bypassed osr nonupdateable validation
1698 -- but we should not update existing osr, set it to null
1699 end if;
1700
1701 -- Debug info.
1702 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1703 hz_utility_v2pub.debug(p_message=>'HZ_CUST_SITE_USES_PKG.Update_Row (+)',
1704 p_prefix=>l_debug_prefix,
1705 p_msg_level=>fnd_log.level_procedure);
1706 END IF;
1707
1708
1709 -- Bug 2643624.
1710 -- During modification of the primary_flag, if we are setting the primary flag
1711 -- of a particular combination of org and cust_account as primary then the already
1712 -- existing such combination would be unset if already primary.
1713 IF p_cust_site_use_rec.primary_flag = 'N' OR
1714 p_cust_site_use_rec.primary_flag = fnd_api.g_miss_char
1715 THEN
1716 p_cust_site_use_rec.primary_flag := NULL;
1717 END IF;
1718
1719
1720
1721 IF (p_cust_site_use_rec.status IS NULL AND
1722 l_status = 'A') OR
1723 p_cust_site_use_rec.status = 'A'
1724 THEN
1725 IF p_cust_site_use_rec.primary_flag = 'Y' AND
1726 l_primary_flag <> 'Y'
1727 THEN
1728
1729 do_unset_prim_cust_site_use(l_site_use_code,
1730 l_cust_acct_site_id,
1731 p_cust_site_use_rec.org_id);
1732
1733 ELSIF l_primary_flag <> 'Y' THEN
1734
1735
1736 BEGIN
1737 SELECT cust_account_id into l_cust_acct_id
1738 FROM hz_cust_acct_sites_all -- Bug 3456489
1739 WHERE cust_acct_site_id = l_cust_acct_site_id;
1740 EXCEPTION
1741 WHEN NO_DATA_FOUND THEN
1742 l_cnt := 0;
1743 END;
1744
1745 IF l_cnt <> 0 THEN
1746 BEGIN
1747 /*
1748 -- Bug 6315081 : FORWARD PORT BUG 6132727
1749 -- New Query Below
1750 SELECT count(*) into l_cnt
1751 FROM hz_cust_accounts a,
1752 hz_cust_acct_sites_all cas,
1753 hz_cust_site_uses_all su
1754 WHERE
1755 a.cust_account_id = l_cust_acct_id
1756 and a.cust_account_id = cas.cust_account_id
1757 and cas.cust_acct_site_id = su.cust_acct_site_id
1758 and su.site_use_code = l_site_use_code
1759 and su.site_use_id <> p_cust_site_use_rec.site_use_id
1760 and su.status = 'A'
1761 and su.primary_flag = 'Y'
1762 and cas.org_id = p_cust_site_use_rec.org_id -- TCA SSA Uptake (Bug 3456489)
1763 and su.org_id = p_cust_site_use_rec.org_id; -- TCA SSA Uptake (Bug 3456489) */
1764
1765 SELECT count(*) into l_cnt
1766 FROM hz_cust_acct_sites_all cas,
1767 hz_cust_site_uses_all su
1768 WHERE
1769 cas.cust_account_id = l_cust_acct_id
1770 and cas.status = 'A'
1771 and cas.cust_acct_site_id = su.cust_acct_site_id
1772 and su.site_use_code = l_site_use_code
1773 and su.site_use_id <> p_cust_site_use_rec.site_use_id
1774 and su.status = 'A'
1775 and su.primary_flag = 'Y'
1776 and cas.org_id = p_cust_site_use_rec.org_id -- TCA SSA Uptake (Bug 3456489)
1777 and su.org_id = p_cust_site_use_rec.org_id -- TCA SSA Uptake (Bug 3456489)
1778 and rownum <= 1;
1779 END;
1780 END IF;
1781
1782 IF l_cnt <= 0 THEN
1783 p_cust_site_use_rec.primary_flag := 'Y';
1784 ELSE
1785 p_cust_site_use_rec.primary_flag := 'N';
1786 END IF;
1787 END IF;
1788 ELSE
1789 IF l_status = 'A' AND
1790 p_cust_site_use_rec.status = 'I' AND
1791 l_primary_flag = 'Y'
1792 THEN
1793 p_cust_site_use_rec.primary_flag := 'N';
1794
1795 BEGIN
1796 SELECT cust_account_id into l_cust_acct_id
1797 FROM hz_cust_acct_sites_all -- Bug 3456489
1798 WHERE cust_acct_site_id = l_cust_acct_site_id;
1799 EXCEPTION
1800 WHEN NO_DATA_FOUND THEN
1801 l_cnt := 0;
1802 END;
1803
1804 IF l_cnt > 0 THEN
1805 BEGIN
1806 SELECT min(su.rowid),count(*) into l_minrowid,l_cnt
1807 FROM hz_cust_accounts a,
1808 hz_cust_acct_sites_all cas, -- Bug 3456489
1809 hz_cust_site_uses_all su -- Bug 3456489
1810 WHERE
1811 a.cust_account_id = l_cust_acct_id
1812 and a.cust_account_id = cas.cust_account_id
1813 and cas.cust_acct_site_id = su.cust_acct_site_id
1814 and su.site_use_code = l_site_use_code
1815 and su.site_use_id <> p_cust_site_use_rec.site_use_id
1816 and su.status = 'A'
1817 and su.primary_flag = 'N'
1818 and cas.org_id = p_cust_site_use_rec.org_id -- TCA SSA Uptake (Bug 3456489)
1819 and su.org_id = p_cust_site_use_rec.org_id; -- TCA SSA Uptake (Bug 3456489)
1820 END;
1821
1822 IF l_cnt > 0 THEN
1823 BEGIN
1824 UPDATE hz_cust_site_uses_all -- Bug 3456489
1825 SET primary_flag = 'Y',
1826 last_updated_by = hz_utility_pub.LAST_UPDATED_BY,
1827 last_update_date = hz_utility_pub.LAST_UPDATE_DATE
1828 WHERE rowid = l_minrowid;
1829 -- Bug 3294182.
1830 select cust_acct_site_id
1831 into l_casid
1832 from hz_cust_site_uses_all -- Bug 3456489
1833 where rowid=l_minrowid;
1834 denormalize_site_use_flag(
1835 l_casid,
1836 nvl(p_cust_site_use_rec.site_use_code,l_site_use_code),
1837 'P');
1838 END;
1839 END IF;
1840
1841 END IF;
1842 END IF;
1843 END IF;
1844
1845 -- Call table-handler.
1846 HZ_CUST_SITE_USES_PKG.Update_Row (
1847 X_Rowid => l_rowid,
1848 X_SITE_USE_ID => p_cust_site_use_rec.site_use_id,
1849 X_CUST_ACCT_SITE_ID => p_cust_site_use_rec.cust_acct_site_id,
1850 X_SITE_USE_CODE => p_cust_site_use_rec.site_use_code,
1851 X_PRIMARY_FLAG => p_cust_site_use_rec.primary_flag,
1852 X_STATUS => p_cust_site_use_rec.status,
1853 X_LOCATION => p_cust_site_use_rec.location,
1854 X_CONTACT_ID => p_cust_site_use_rec.contact_id,
1855 X_BILL_TO_SITE_USE_ID => p_cust_site_use_rec.bill_to_site_use_id,
1856 X_ORIG_SYSTEM_REFERENCE => p_cust_site_use_rec.orig_system_reference,
1857 X_SIC_CODE => p_cust_site_use_rec.sic_code,
1858 X_PAYMENT_TERM_ID => p_cust_site_use_rec.payment_term_id,
1859 X_GSA_INDICATOR => p_cust_site_use_rec.gsa_indicator,
1860 X_SHIP_PARTIAL => p_cust_site_use_rec.ship_partial,
1861 X_SHIP_VIA => p_cust_site_use_rec.ship_via,
1862 X_FOB_POINT => p_cust_site_use_rec.fob_point,
1863 X_ORDER_TYPE_ID => p_cust_site_use_rec.order_type_id,
1864 X_PRICE_LIST_ID => p_cust_site_use_rec.price_list_id,
1865 X_FREIGHT_TERM => p_cust_site_use_rec.freight_term,
1866 X_WAREHOUSE_ID => p_cust_site_use_rec.warehouse_id,
1867 X_TERRITORY_ID => p_cust_site_use_rec.territory_id,
1868 X_ATTRIBUTE_CATEGORY => p_cust_site_use_rec.attribute_category,
1869 X_ATTRIBUTE1 => p_cust_site_use_rec.attribute1,
1870 X_ATTRIBUTE2 => p_cust_site_use_rec.attribute2,
1871 X_ATTRIBUTE3 => p_cust_site_use_rec.attribute3,
1872 X_ATTRIBUTE4 => p_cust_site_use_rec.attribute4,
1873 X_ATTRIBUTE5 => p_cust_site_use_rec.attribute5,
1874 X_ATTRIBUTE6 => p_cust_site_use_rec.attribute6,
1875 X_ATTRIBUTE7 => p_cust_site_use_rec.attribute7,
1876 X_ATTRIBUTE8 => p_cust_site_use_rec.attribute8,
1877 X_ATTRIBUTE9 => p_cust_site_use_rec.attribute9,
1878 X_ATTRIBUTE10 => p_cust_site_use_rec.attribute10,
1879 X_TAX_REFERENCE => p_cust_site_use_rec.tax_reference,
1880 X_SORT_PRIORITY => p_cust_site_use_rec.sort_priority,
1881 X_TAX_CODE => p_cust_site_use_rec.tax_code,
1882 X_ATTRIBUTE11 => p_cust_site_use_rec.attribute11,
1883 X_ATTRIBUTE12 => p_cust_site_use_rec.attribute12,
1884 X_ATTRIBUTE13 => p_cust_site_use_rec.attribute13,
1885 X_ATTRIBUTE14 => p_cust_site_use_rec.attribute14,
1886 X_ATTRIBUTE15 => p_cust_site_use_rec.attribute15,
1887 X_ATTRIBUTE16 => p_cust_site_use_rec.attribute16,
1888 X_ATTRIBUTE17 => p_cust_site_use_rec.attribute17,
1889 X_ATTRIBUTE18 => p_cust_site_use_rec.attribute18,
1890 X_ATTRIBUTE19 => p_cust_site_use_rec.attribute19,
1891 X_ATTRIBUTE20 => p_cust_site_use_rec.attribute20,
1892 X_ATTRIBUTE21 => p_cust_site_use_rec.attribute21,
1893 X_ATTRIBUTE22 => p_cust_site_use_rec.attribute22,
1894 X_ATTRIBUTE23 => p_cust_site_use_rec.attribute23,
1895 X_ATTRIBUTE24 => p_cust_site_use_rec.attribute24,
1896 X_ATTRIBUTE25 => p_cust_site_use_rec.attribute25,
1897 X_DEMAND_CLASS_CODE => p_cust_site_use_rec.demand_class_code,
1898 X_TAX_HEADER_LEVEL_FLAG => p_cust_site_use_rec.tax_header_level_flag,
1899 X_TAX_ROUNDING_RULE => p_cust_site_use_rec.tax_rounding_rule,
1900 X_GLOBAL_ATTRIBUTE1 => p_cust_site_use_rec.global_attribute1,
1901 X_GLOBAL_ATTRIBUTE2 => p_cust_site_use_rec.global_attribute2,
1902 X_GLOBAL_ATTRIBUTE3 => p_cust_site_use_rec.global_attribute3,
1903 X_GLOBAL_ATTRIBUTE4 => p_cust_site_use_rec.global_attribute4,
1904 X_GLOBAL_ATTRIBUTE5 => p_cust_site_use_rec.global_attribute5,
1905 X_GLOBAL_ATTRIBUTE6 => p_cust_site_use_rec.global_attribute6,
1906 X_GLOBAL_ATTRIBUTE7 => p_cust_site_use_rec.global_attribute7,
1907 X_GLOBAL_ATTRIBUTE8 => p_cust_site_use_rec.global_attribute8,
1908 X_GLOBAL_ATTRIBUTE9 => p_cust_site_use_rec.global_attribute9,
1909 X_GLOBAL_ATTRIBUTE10 => p_cust_site_use_rec.global_attribute10,
1910 X_GLOBAL_ATTRIBUTE11 => p_cust_site_use_rec.global_attribute11,
1911 X_GLOBAL_ATTRIBUTE12 => p_cust_site_use_rec.global_attribute12,
1912 X_GLOBAL_ATTRIBUTE13 => p_cust_site_use_rec.global_attribute13,
1913 X_GLOBAL_ATTRIBUTE14 => p_cust_site_use_rec.global_attribute14,
1914 X_GLOBAL_ATTRIBUTE15 => p_cust_site_use_rec.global_attribute15,
1915 X_GLOBAL_ATTRIBUTE16 => p_cust_site_use_rec.global_attribute16,
1916 X_GLOBAL_ATTRIBUTE17 => p_cust_site_use_rec.global_attribute17,
1917 X_GLOBAL_ATTRIBUTE18 => p_cust_site_use_rec.global_attribute18,
1918 X_GLOBAL_ATTRIBUTE19 => p_cust_site_use_rec.global_attribute19,
1919 X_GLOBAL_ATTRIBUTE20 => p_cust_site_use_rec.global_attribute20,
1920 X_GLOBAL_ATTRIBUTE_CATEGORY => p_cust_site_use_rec.global_attribute_category,
1921 X_PRIMARY_SALESREP_ID => p_cust_site_use_rec.primary_salesrep_id,
1922 X_FINCHRG_RECEIVABLES_TRX_ID => p_cust_site_use_rec.finchrg_receivables_trx_id,
1923 X_DATES_NEGATIVE_TOLERANCE => p_cust_site_use_rec.dates_negative_tolerance,
1924 X_DATES_POSITIVE_TOLERANCE => p_cust_site_use_rec.dates_positive_tolerance,
1925 X_DATE_TYPE_PREFERENCE => p_cust_site_use_rec.date_type_preference,
1926 X_OVER_SHIPMENT_TOLERANCE => p_cust_site_use_rec.over_shipment_tolerance,
1927 X_UNDER_SHIPMENT_TOLERANCE => p_cust_site_use_rec.under_shipment_tolerance,
1928 X_ITEM_CROSS_REF_PREF => p_cust_site_use_rec.item_cross_ref_pref,
1929 X_OVER_RETURN_TOLERANCE => p_cust_site_use_rec.over_return_tolerance,
1930 X_UNDER_RETURN_TOLERANCE => p_cust_site_use_rec.under_return_tolerance,
1931 X_SHIP_SETS_INCLUDE_LINES_FLAG => p_cust_site_use_rec.ship_sets_include_lines_flag,
1932 X_ARRIVALSETS_INCLUDE_LINES_FG => p_cust_site_use_rec.arrivalsets_include_lines_flag,
1933 X_SCHED_DATE_PUSH_FLAG => p_cust_site_use_rec.sched_date_push_flag,
1934 X_INVOICE_QUANTITY_RULE => p_cust_site_use_rec.invoice_quantity_rule,
1935 X_PRICING_EVENT => p_cust_site_use_rec.pricing_event,
1936 X_GL_ID_REC => p_cust_site_use_rec.gl_id_rec,
1937 X_GL_ID_REV => p_cust_site_use_rec.gl_id_rev,
1938 X_GL_ID_TAX => p_cust_site_use_rec.gl_id_tax,
1939 X_GL_ID_FREIGHT => p_cust_site_use_rec.gl_id_freight,
1940 X_GL_ID_CLEARING => p_cust_site_use_rec.gl_id_clearing,
1941 X_GL_ID_UNBILLED => p_cust_site_use_rec.gl_id_unbilled,
1942 X_GL_ID_UNEARNED => p_cust_site_use_rec.gl_id_unearned,
1943 X_GL_ID_UNPAID_REC => p_cust_site_use_rec.gl_id_unpaid_rec,
1944 X_GL_ID_REMITTANCE => p_cust_site_use_rec.gl_id_remittance,
1945 X_GL_ID_FACTOR => p_cust_site_use_rec.gl_id_factor,
1946 X_TAX_CLASSIFICATION => p_cust_site_use_rec.tax_classification,
1947 X_OBJECT_VERSION_NUMBER => p_object_version_number,
1948 X_CREATED_BY_MODULE => p_cust_site_use_rec.created_by_module,
1949 X_APPLICATION_ID => p_cust_site_use_rec.application_id
1950 );
1951
1952 -- Debug info.
1953 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1954 hz_utility_v2pub.debug(p_message=>'HZ_CUST_SITE_USES_PKG.Update_Row (-)',
1955 p_prefix=>l_debug_prefix,
1956 p_msg_level=>fnd_log.level_procedure);
1957 END IF;
1958
1959 -- If this is a active bill_to or ship_to or market,
1960 -- set the appropriate denormalized flag in hz_cust_acct_sites.
1961 -- Please note, primary_flag cannot be updated to NULL.
1962
1963 IF l_site_use_code IN ('BILL_TO', 'SHIP_TO', 'MARKET' )
1964 THEN
1965 IF p_cust_site_use_rec.status = 'A' OR
1966 p_cust_site_use_rec.status IS NULL AND
1967 l_status = 'A'
1968 THEN
1969 IF
1970 (
1971 (
1972 l_primary_flag <> 'Y'
1973 AND
1974 p_cust_site_use_rec.primary_flag = 'Y'
1975 )
1976 OR
1977 --Bug no 3085557
1978 (
1979 nvl(p_cust_site_use_rec.primary_flag,l_primary_flag) = 'Y'
1980 AND
1981 l_status<>'A'
1982 )
1983 )
1984 THEN
1985 l_flag := 'P';
1986 l_denormalize := TRUE;
1987 ELSIF
1988 (
1989 (
1990 l_primary_flag = 'Y'
1991 AND
1992 p_cust_site_use_rec.primary_flag = 'N'
1993 )
1994 OR
1995 --Bug no 3085557
1996 (
1997 nvl(p_cust_site_use_rec.primary_flag,l_primary_flag) = 'N'
1998 AND
1999 l_status<>'A'
2000 )
2001 )
2002 THEN
2003 l_flag := 'Y';
2004 l_denormalize := TRUE;
2005 END IF;
2006 ELSIF p_cust_site_use_rec.status IS NOT NULL THEN
2007 l_flag := NULL;
2008 l_denormalize := TRUE;
2009 END IF;
2010
2011 IF l_denormalize THEN
2012 denormalize_site_use_flag (
2013 --Bugfix 2792589 p_cust_site_use_rec.cust_acct_site_id,
2014 l_cust_acct_site_id,
2015 --Bugfix 2792589 p_cust_site_use_rec.site_use_code,
2016 l_site_use_code,
2017 l_flag );
2018 END IF;
2019 END IF;
2020
2021 -- Debug info.
2022 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2023 hz_utility_v2pub.debug(p_message=>'do_update_cust_site_use (-)',
2024 p_prefix=>l_debug_prefix,
2025 p_msg_level=>fnd_log.level_procedure);
2026 END IF;
2027
2028 END do_update_cust_site_use;
2029
2030 /**
2031 * PRIVATE PROCEDURE denormalize_site_use_flag
2032 *
2033 * DESCRIPTION
2034 * Private procedure to denormalize bill_to_flag, ship_to_flag, market_flag
2035 * in hz_cust_acct_sites.
2036 *
2037 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2038 *
2039 * ARGUMENTS
2040 * IN/OUT:
2041 * p_cust_acct_site_id Customer account site id.
2042 * p_site_use_code Site use code. Can only in (BILL_TO, SHIP_TO, MARKET)
2043 * p_flag Flag used to update account site.
2044 *
2045 * NOTES
2046 *
2047 * MODIFICATION HISTORY
2048 *
2049 * 07-23-2001 Jianying Huang o Created.
2050 * 12-MAY-2005 Rajib Ranjan Borah o TCA SSA Uptake (Bug 3456489)
2051 *
2052 */
2053
2054 PROCEDURE denormalize_site_use_flag (
2055 p_cust_acct_site_id IN NUMBER,
2056 p_site_use_code IN VARCHAR2,
2057 p_flag IN VARCHAR2
2058 ) IS
2059
2060 l_debug_prefix VARCHAR2(30) := ''; --'denormalize_site_use_flag'
2061
2062 BEGIN
2063
2064 -- Debug info.
2065 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2066 hz_utility_v2pub.debug(p_message=>'denormalize_site_use_flag (+)',
2067 p_prefix=>l_debug_prefix,
2068 p_msg_level=>fnd_log.level_procedure);
2069 END IF;
2070
2071
2072 IF p_site_use_code = 'BILL_TO' THEN
2073 UPDATE HZ_CUST_ACCT_SITES_ALL -- Bug 3456489
2074 SET BILL_TO_FLAG = p_flag,
2075 last_updated_by = hz_utility_pub.LAST_UPDATED_BY,
2076 last_update_date = hz_utility_pub.LAST_UPDATE_DATE
2077 WHERE CUST_ACCT_SITE_ID = p_cust_acct_site_id;
2078 ELSIF p_site_use_code = 'SHIP_TO' THEN
2079 UPDATE HZ_CUST_ACCT_SITES_ALL -- Bug 3456489
2080 SET SHIP_TO_FLAG = p_flag,
2081 last_updated_by = hz_utility_pub.LAST_UPDATED_BY,
2082 last_update_date = hz_utility_pub.LAST_UPDATE_DATE
2083 WHERE CUST_ACCT_SITE_ID = p_cust_acct_site_id;
2084 ELSIF p_site_use_code = 'MARKET' THEN
2085 UPDATE HZ_CUST_ACCT_SITES_ALL -- Bug 3456489
2086 SET MARKET_FLAG = p_flag,
2087 last_updated_by = hz_utility_pub.LAST_UPDATED_BY,
2088 last_update_date = hz_utility_pub.LAST_UPDATE_DATE
2089 WHERE CUST_ACCT_SITE_ID = p_cust_acct_site_id;
2090 END IF;
2091
2092 -- Debug info.
2093 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2094 hz_utility_v2pub.debug(p_message=>'denormalize_site_use_flag (-)',
2095 p_prefix=>l_debug_prefix,
2096 p_msg_level=>fnd_log.level_procedure);
2097 END IF;
2098
2099 END denormalize_site_use_flag;
2100
2101 --------------------------------------
2102 -- public procedures and functions
2103 --------------------------------------
2104
2105 /**
2106 * PROCEDURE create_cust_acct_site
2107 *
2108 * DESCRIPTION
2109 * Creates customer account site.
2110 *
2111 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2112 * HZ_BUSINESS_EVENT_V2PVT.create_cust_acct_site_event
2113 *
2114 * ARGUMENTS
2115 * IN:
2116 * p_init_msg_list Initialize message stack if it is set to
2117 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
2118 * p_cust_acct_site_rec Customer account site record.
2119 * IN/OUT:
2120 * OUT:
2121 * x_cust_acct_site_id Customer account site ID.
2122 * x_return_status Return status after the call. The status can
2123 * be FND_API.G_RET_STS_SUCCESS (success),
2124 * FND_API.G_RET_STS_ERROR (error),
2125 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
2126 * x_msg_count Number of messages in message stack.
2127 * x_msg_data Message text if x_msg_count is 1.
2128 *
2129 * NOTES
2130 *
2131 * MODIFICATION HISTORY
2132 *
2133 * 07-23-2001 Jianying Huang o Created.
2134 *
2135 */
2136
2137 PROCEDURE create_cust_acct_site (
2138 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2139 p_cust_acct_site_rec IN CUST_ACCT_SITE_REC_TYPE,
2140 x_cust_acct_site_id OUT NOCOPY NUMBER,
2141 x_return_status OUT NOCOPY VARCHAR2,
2142 x_msg_count OUT NOCOPY NUMBER,
2143 x_msg_data OUT NOCOPY VARCHAR2
2144 ) IS
2145
2146 l_cust_acct_site_rec CUST_ACCT_SITE_REC_TYPE := p_cust_acct_site_rec;
2147 l_debug_prefix VARCHAR2(30) := '';
2148
2149 BEGIN
2150
2151 -- Standard start of API savepoint
2152 SAVEPOINT create_cust_acct_site;
2153
2154 -- Check if API is called in debug mode. If yes, enable debug.
2155 --enable_debug;
2156
2157 -- Debug info.
2158 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2159 hz_utility_v2pub.debug(p_message=>'create_cust_acct_site (+)',
2160 p_prefix=>l_debug_prefix,
2161 p_msg_level=>fnd_log.level_procedure);
2162 END IF;
2163
2164 -- Initialize message list if p_init_msg_list is set to TRUE.
2165 IF FND_API.to_Boolean(p_init_msg_list) THEN
2166 FND_MSG_PUB.initialize;
2167 END IF;
2168
2169 -- Initialize API return status to success.
2170 x_return_status := FND_API.G_RET_STS_SUCCESS;
2171
2172 -- report error on obsolete columns based on profile
2173 IF NVL(FND_PROFILE.VALUE('HZ_API_ERR_ON_OBSOLETE_COLUMN'), 'Y') = 'Y' THEN
2174 check_obsolete_columns (
2175 p_create_update_flag => 'C',
2176 p_account_site_rec => l_cust_acct_site_rec,
2177 x_return_status => x_return_status
2178 );
2179
2180 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2181 RAISE FND_API.G_EXC_ERROR;
2182 END IF;
2183 END IF;
2184
2185 -- Call to business logic.
2186 do_create_cust_acct_site (
2187 l_cust_acct_site_rec,
2188 x_cust_acct_site_id,
2189 x_return_status );
2190
2191 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2192 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'Y')) THEN
2193 -- Invoke business event system.
2194 HZ_BUSINESS_EVENT_V2PVT.create_cust_acct_site_event (
2195 l_cust_acct_site_rec );
2196 END IF;
2197
2198 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'BO_EVENTS_ENABLED')) THEN
2199 -- populate function for integration service
2200 HZ_POPULATE_BOT_PKG.pop_hz_cust_acct_sites_all(
2201 p_operation => 'I',
2202 p_cust_acct_site_id => x_cust_acct_site_id );
2203 END IF;
2204 END IF;
2205
2206 -- Standard call to get message count and if count is 1, get message info.
2207 FND_MSG_PUB.Count_And_Get(
2208 p_encoded => FND_API.G_FALSE,
2209 p_count => x_msg_count,
2210 p_data => x_msg_data );
2211
2212 -- Debug info.
2213 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
2214 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2215 p_msg_data=>x_msg_data,
2216 p_msg_type=>'WARNING',
2217 p_msg_level=>fnd_log.level_exception);
2218 END IF;
2219 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2220 hz_utility_v2pub.debug(p_message=>'create_cust_acct_site (-)',
2221 p_prefix=>l_debug_prefix,
2222 p_msg_level=>fnd_log.level_procedure);
2223 END IF;
2224
2225 -- Check if API is called in debug mode. If yes, disable debug.
2226 --disable_debug;
2227
2228 EXCEPTION
2229 WHEN FND_API.G_EXC_ERROR THEN
2230 ROLLBACK TO create_cust_acct_site;
2231 x_return_status := FND_API.G_RET_STS_ERROR;
2232
2233 FND_MSG_PUB.Count_And_Get(
2234 p_encoded => FND_API.G_FALSE,
2235 p_count => x_msg_count,
2236 p_data => x_msg_data );
2237
2238 -- Debug info.
2239 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2240 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2241 p_msg_data=>x_msg_data,
2242 p_msg_type=>'ERROR',
2243 p_msg_level=>fnd_log.level_error);
2244 END IF;
2245 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2246 hz_utility_v2pub.debug(p_message=>'create_cust_acct_site (-)',
2247 p_prefix=>l_debug_prefix,
2248 p_msg_level=>fnd_log.level_procedure);
2249 END IF;
2250
2251 -- Check if API is called in debug mode. If yes, disable debug.
2252 --disable_debug;
2253
2254 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2255 ROLLBACK TO create_cust_acct_site;
2256 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2257
2258 FND_MSG_PUB.Count_And_Get(
2259 p_encoded => FND_API.G_FALSE,
2260 p_count => x_msg_count,
2261 p_data => x_msg_data );
2262
2263 -- Debug info.
2264 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2265 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2266 p_msg_data=>x_msg_data,
2267 p_msg_type=>'UNEXPECTED ERROR',
2268 p_msg_level=>fnd_log.level_error);
2269 END IF;
2270 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2271 hz_utility_v2pub.debug(p_message=>'create_cust_acct_site (-)',
2272 p_prefix=>l_debug_prefix,
2273 p_msg_level=>fnd_log.level_procedure);
2274 END IF;
2275
2276 -- Check if API is called in debug mode. If yes, disable debug.
2277 --disable_debug;
2278
2279 WHEN OTHERS THEN
2280 ROLLBACK TO create_cust_acct_site;
2281 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2282
2283 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
2284 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
2285 FND_MSG_PUB.ADD;
2286
2287 FND_MSG_PUB.Count_And_Get(
2288 p_encoded => FND_API.G_FALSE,
2289 p_count => x_msg_count,
2290 p_data => x_msg_data );
2291
2292 -- Debug info.
2293 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2294 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2295 p_msg_data=>x_msg_data,
2296 p_msg_type=>'SQL ERROR',
2297 p_msg_level=>fnd_log.level_error);
2298 END IF;
2299 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2300 hz_utility_v2pub.debug(p_message=> 'create_cust_acct_site (-)',
2301 p_prefix=>l_debug_prefix,
2302 p_msg_level=>fnd_log.level_procedure);
2303 END IF;
2304
2305 -- Check if API is called in debug mode. If yes, disable debug.
2306 --disable_debug;
2307
2308 END create_cust_acct_site;
2309
2310 /**
2311 * PROCEDURE update_cust_acct_site
2312 *
2313 * DESCRIPTION
2314 * Updates customer account site.
2315 *
2316 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2317 * HZ_BUSINESS_EVENT_V2PVT.update_cust_acct_site_event
2318 *
2319 * ARGUMENTS
2320 * IN:
2321 * p_init_msg_list Initialize message stack if it is set to
2322 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
2323 * p_cust_acct_site_rec Customer account site record.
2324 * IN/OUT:
2325 * p_object_version_number Used for locking the being updated record.
2326 * OUT:
2327 * x_return_status Return status after the call. The status can
2328 * be FND_API.G_RET_STS_SUCCESS (success),
2329 * FND_API.G_RET_STS_ERROR (error),
2330 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
2331 * x_msg_count Number of messages in message stack.
2332 * x_msg_data Message text if x_msg_count is 1.
2333 *
2334 * NOTES
2335 *
2336 * MODIFICATION HISTORY
2337 *
2338 * 07-23-2001 Jianying Huang o Created.
2339 *
2340 */
2341
2342 PROCEDURE update_cust_acct_site (
2343 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2344 p_cust_acct_site_rec IN CUST_ACCT_SITE_REC_TYPE,
2345 p_object_version_number IN OUT NOCOPY NUMBER,
2346 x_return_status OUT NOCOPY VARCHAR2,
2347 x_msg_count OUT NOCOPY NUMBER,
2348 x_msg_data OUT NOCOPY VARCHAR2
2349 ) IS
2350
2351 l_cust_acct_site_rec CUST_ACCT_SITE_REC_TYPE := p_cust_acct_site_rec;
2352 l_old_cust_acct_site_rec CUST_ACCT_SITE_REC_TYPE;
2353 l_debug_prefix VARCHAR2(30) := '';
2354
2355 BEGIN
2356
2357 -- Standard start of API savepoint
2358 SAVEPOINT update_cust_acct_site;
2359
2360 -- Check if API is called in debug mode. If yes, enable debug.
2361 --enable_debug;
2362
2363 -- Debug info.
2364 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2365 hz_utility_v2pub.debug(p_message=>'update_cust_acct_site (+)',
2366 p_prefix=>l_debug_prefix,
2367 p_msg_level=>fnd_log.level_procedure);
2368 END IF;
2369
2370 -- Initialize message list if p_init_msg_list is set to TRUE.
2371 IF FND_API.to_Boolean(p_init_msg_list) THEN
2372 FND_MSG_PUB.initialize;
2373 END IF;
2374
2375 -- Initialize API return status to success.
2376 x_return_status := FND_API.G_RET_STS_SUCCESS;
2377
2378 IF (p_cust_acct_site_rec.orig_system is not null and p_cust_acct_site_rec.orig_system <>fnd_api.g_miss_char)
2379 and (p_cust_acct_site_rec.orig_system_reference is not null and p_cust_acct_site_rec.orig_system_reference <>fnd_api.g_miss_char)
2380 and (p_cust_acct_site_rec.cust_acct_site_id = FND_API.G_MISS_NUM or p_cust_acct_site_rec.cust_acct_site_id is null) THEN
2381
2382 hz_orig_system_ref_pub.get_owner_table_id
2383 (p_orig_system => p_cust_acct_site_rec.orig_system,
2384 p_orig_system_reference => p_cust_acct_site_rec.orig_system_reference,
2385 p_owner_table_name => 'HZ_CUST_ACCT_SITES_ALL',
2386 x_owner_table_id => l_cust_acct_site_rec.cust_acct_site_id,
2387 x_return_status => x_return_status);
2388 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2389 RAISE FND_API.G_EXC_ERROR;
2390 END IF;
2391
2392 END IF;
2393
2394
2395 --2290537
2396 get_cust_acct_site_rec (
2397 p_cust_acct_site_id => l_cust_acct_site_rec.cust_acct_site_id,
2398 x_cust_acct_site_rec => l_old_cust_acct_site_rec,
2399 x_return_status => x_return_status,
2400 x_msg_count => x_msg_count,
2401 x_msg_data => x_msg_data);
2402
2403 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2404 RAISE FND_API.G_EXC_ERROR;
2405 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2406 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2407 END IF;
2408
2409 -- report error on obsolete columns based on profile
2410 IF NVL(FND_PROFILE.VALUE('HZ_API_ERR_ON_OBSOLETE_COLUMN'), 'Y') = 'Y' THEN
2411 check_obsolete_columns (
2412 p_create_update_flag => 'U',
2413 p_account_site_rec => l_cust_acct_site_rec,
2414 p_old_account_site_rec => l_old_cust_acct_site_rec,
2415 x_return_status => x_return_status
2416 );
2417
2418 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2419 RAISE FND_API.G_EXC_ERROR;
2420 END IF;
2421 END IF;
2422
2423 -- Call to business logic.
2424 do_update_cust_acct_site (
2425 l_cust_acct_site_rec,
2426 p_object_version_number,
2427 x_return_status );
2428
2429 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2430 l_old_cust_acct_site_rec.orig_system := l_cust_acct_site_rec.orig_system;
2431 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'Y')) THEN
2432 -- Invoke business event system.
2433 HZ_BUSINESS_EVENT_V2PVT.update_cust_acct_site_event (
2434 l_cust_acct_site_rec , l_old_cust_acct_site_rec );
2435 END IF;
2436
2437 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'BO_EVENTS_ENABLED')) THEN
2438 -- populate function for integration service
2439 HZ_POPULATE_BOT_PKG.pop_hz_cust_acct_sites_all(
2440 p_operation => 'U',
2441 p_cust_acct_site_id => l_cust_acct_site_rec.cust_acct_site_id );
2442 END IF;
2443 END IF;
2444
2445 -- Standard call to get message count and if count is 1, get message info.
2446 FND_MSG_PUB.Count_And_Get(
2447 p_encoded => FND_API.G_FALSE,
2448 p_count => x_msg_count,
2449 p_data => x_msg_data );
2450
2451 -- Debug info.
2452 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
2453 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2454 p_msg_data=>x_msg_data,
2455 p_msg_type=>'WARNING',
2456 p_msg_level=>fnd_log.level_exception);
2457 END IF;
2458 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2459 hz_utility_v2pub.debug(p_message=>'update_cust_acct_site (-)',
2460 p_prefix=>l_debug_prefix,
2461 p_msg_level=>fnd_log.level_procedure);
2462 END IF;
2463
2464 -- Check if API is called in debug mode. If yes, disable debug.
2465 --disable_debug;
2466
2467 EXCEPTION
2468 WHEN FND_API.G_EXC_ERROR THEN
2469 ROLLBACK TO update_cust_acct_site;
2470 x_return_status := FND_API.G_RET_STS_ERROR;
2471
2472 FND_MSG_PUB.Count_And_Get(
2473 p_encoded => FND_API.G_FALSE,
2474 p_count => x_msg_count,
2475 p_data => x_msg_data );
2476
2477 -- Debug info.
2478 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2479 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2480 p_msg_data=>x_msg_data,
2481 p_msg_type=>'ERROR',
2482 p_msg_level=>fnd_log.level_error);
2483 END IF;
2484 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2485 hz_utility_v2pub.debug(p_message=>'update_cust_acct_site (-)',
2486 p_prefix=>l_debug_prefix,
2487 p_msg_level=>fnd_log.level_procedure);
2488 END IF;
2489
2490 -- Check if API is called in debug mode. If yes, disable debug.
2491 --disable_debug;
2492
2493 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2494 ROLLBACK TO update_cust_acct_site;
2495 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2496
2497 FND_MSG_PUB.Count_And_Get(
2498 p_encoded => FND_API.G_FALSE,
2499 p_count => x_msg_count,
2500 p_data => x_msg_data );
2501
2502 -- Debug info.
2503 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2504 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2505 p_msg_data=>x_msg_data,
2506 p_msg_type=>'UNEXPECTED ERROR',
2507 p_msg_level=>fnd_log.level_error);
2508 END IF;
2509 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2510 hz_utility_v2pub.debug(p_message=>'update_cust_acct_site (-)',
2511 p_prefix=>l_debug_prefix,
2512 p_msg_level=>fnd_log.level_procedure);
2513 END IF;
2514
2515 -- Check if API is called in debug mode. If yes, disable debug.
2516 --disable_debug;
2517
2518 WHEN OTHERS THEN
2519 ROLLBACK TO update_cust_acct_site;
2520 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2521
2522 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
2523 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
2524 FND_MSG_PUB.ADD;
2525
2526 FND_MSG_PUB.Count_And_Get(
2527 p_encoded => FND_API.G_FALSE,
2528 p_count => x_msg_count,
2529 p_data => x_msg_data );
2530
2531 -- Debug info.
2532 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2533 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2534 p_msg_data=>x_msg_data,
2535 p_msg_type=>'SQL ERROR',
2536 p_msg_level=>fnd_log.level_error);
2537 END IF;
2538 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2539 hz_utility_v2pub.debug(p_message=>'update_cust_acct_site (-)',
2540 p_prefix=>l_debug_prefix,
2541 p_msg_level=>fnd_log.level_procedure);
2542 END IF;
2543
2544 -- Check if API is called in debug mode. If yes, disable debug.
2545 --disable_debug;
2546
2547 END update_cust_acct_site;
2548
2549 /**
2550 * PROCEDURE get_cust_acct_site_rec
2551 *
2552 * DESCRIPTION
2553 * Gets customer account site record
2554 *
2555 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2556 * HZ_CUST_ACCT_SITES_PKG.Select_Row
2557 *
2558 * ARGUMENTS
2559 * IN:
2560 * p_init_msg_list Initialize message stack if it is set to
2561 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
2562 * p_cust_acct_site_id Customer account site id.
2563 * IN/OUT:
2564 * OUT:
2565 * x_cust_acct_site_rec Returned customer account site record.
2566 * x_return_status Return status after the call. The status can
2567 * be FND_API.G_RET_STS_SUCCESS (success),
2568 * FND_API.G_RET_STS_ERROR (error),
2569 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
2570 * x_msg_count Number of messages in message stack.
2571 * x_msg_data Message text if x_msg_count is 1.
2572 *
2573 * NOTES
2574 *
2575 * MODIFICATION HISTORY
2576 *
2577 * 07-23-2001 Jianying Huang o Created.
2578 *
2579 */
2580
2581 PROCEDURE get_cust_acct_site_rec (
2582 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2583 p_cust_acct_site_id IN NUMBER,
2584 x_cust_acct_site_rec OUT NOCOPY CUST_ACCT_SITE_REC_TYPE,
2585 x_return_status OUT NOCOPY VARCHAR2,
2586 x_msg_count OUT NOCOPY NUMBER,
2587 x_msg_data OUT NOCOPY VARCHAR2
2588 ) IS
2589 l_debug_prefix VARCHAR2(30) := '';
2590 BEGIN
2591
2592 -- Check if API is called in debug mode. If yes, enable debug.
2593 --enable_debug;
2594
2595 -- Debug info.
2596 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2597 hz_utility_v2pub.debug(p_message=>'get_cust_acct_site_rec (+)',
2598 p_prefix=>l_debug_prefix,
2599 p_msg_level=>fnd_log.level_procedure);
2600 END IF;
2601
2602 -- Initialize message list if p_init_msg_list is set to TRUE.
2603 IF FND_API.to_Boolean(p_init_msg_list) THEN
2604 FND_MSG_PUB.initialize;
2605 END IF;
2606
2607 -- Initialize API return status to success.
2608 x_return_status := FND_API.G_RET_STS_SUCCESS;
2609
2610 -- Check whether primary key has been passed in.
2611 IF p_cust_acct_site_id IS NULL OR
2612 p_cust_acct_site_id = FND_API.G_MISS_NUM THEN
2613 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
2614 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'cust_acct_site_id' );
2615 FND_MSG_PUB.ADD;
2616 RAISE FND_API.G_EXC_ERROR;
2617 END IF;
2618
2619 x_cust_acct_site_rec.cust_acct_site_id := p_cust_acct_site_id;
2620
2621 -- Debug info.
2622 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2623 hz_utility_v2pub.debug(p_message=>'HZ_CUST_ACCT_SITES_PKG.Select_Row (+)',
2624 p_prefix=>l_debug_prefix,
2625 p_msg_level=>fnd_log.level_procedure);
2626 END IF;
2627
2628 -- Call table-handler.
2629 HZ_CUST_ACCT_SITES_PKG.Select_Row (
2630 X_CUST_ACCT_SITE_ID => x_cust_acct_site_rec.cust_acct_site_id,
2631 X_CUST_ACCOUNT_ID => x_cust_acct_site_rec.cust_account_id,
2632 X_PARTY_SITE_ID => x_cust_acct_site_rec.party_site_id,
2633 X_ATTRIBUTE_CATEGORY => x_cust_acct_site_rec.attribute_category,
2634 X_ATTRIBUTE1 => x_cust_acct_site_rec.attribute1,
2635 X_ATTRIBUTE2 => x_cust_acct_site_rec.attribute2,
2636 X_ATTRIBUTE3 => x_cust_acct_site_rec.attribute3,
2637 X_ATTRIBUTE4 => x_cust_acct_site_rec.attribute4,
2638 X_ATTRIBUTE5 => x_cust_acct_site_rec.attribute5,
2639 X_ATTRIBUTE6 => x_cust_acct_site_rec.attribute6,
2640 X_ATTRIBUTE7 => x_cust_acct_site_rec.attribute7,
2641 X_ATTRIBUTE8 => x_cust_acct_site_rec.attribute8,
2642 X_ATTRIBUTE9 => x_cust_acct_site_rec.attribute9,
2643 X_ATTRIBUTE10 => x_cust_acct_site_rec.attribute10,
2644 X_ATTRIBUTE11 => x_cust_acct_site_rec.attribute11,
2645 X_ATTRIBUTE12 => x_cust_acct_site_rec.attribute12,
2646 X_ATTRIBUTE13 => x_cust_acct_site_rec.attribute13,
2647 X_ATTRIBUTE14 => x_cust_acct_site_rec.attribute14,
2648 X_ATTRIBUTE15 => x_cust_acct_site_rec.attribute15,
2649 X_ATTRIBUTE16 => x_cust_acct_site_rec.attribute16,
2650 X_ATTRIBUTE17 => x_cust_acct_site_rec.attribute17,
2651 X_ATTRIBUTE18 => x_cust_acct_site_rec.attribute18,
2652 X_ATTRIBUTE19 => x_cust_acct_site_rec.attribute19,
2653 X_ATTRIBUTE20 => x_cust_acct_site_rec.attribute20,
2654 X_GLOBAL_ATTRIBUTE_CATEGORY => x_cust_acct_site_rec.global_attribute_category,
2655 X_GLOBAL_ATTRIBUTE1 => x_cust_acct_site_rec.global_attribute1,
2656 X_GLOBAL_ATTRIBUTE2 => x_cust_acct_site_rec.global_attribute2,
2657 X_GLOBAL_ATTRIBUTE3 => x_cust_acct_site_rec.global_attribute3,
2658 X_GLOBAL_ATTRIBUTE4 => x_cust_acct_site_rec.global_attribute4,
2659 X_GLOBAL_ATTRIBUTE5 => x_cust_acct_site_rec.global_attribute5,
2660 X_GLOBAL_ATTRIBUTE6 => x_cust_acct_site_rec.global_attribute6,
2661 X_GLOBAL_ATTRIBUTE7 => x_cust_acct_site_rec.global_attribute7,
2662 X_GLOBAL_ATTRIBUTE8 => x_cust_acct_site_rec.global_attribute8,
2663 X_GLOBAL_ATTRIBUTE9 => x_cust_acct_site_rec.global_attribute9,
2664 X_GLOBAL_ATTRIBUTE10 => x_cust_acct_site_rec.global_attribute10,
2665 X_GLOBAL_ATTRIBUTE11 => x_cust_acct_site_rec.global_attribute11,
2666 X_GLOBAL_ATTRIBUTE12 => x_cust_acct_site_rec.global_attribute12,
2667 X_GLOBAL_ATTRIBUTE13 => x_cust_acct_site_rec.global_attribute13,
2668 X_GLOBAL_ATTRIBUTE14 => x_cust_acct_site_rec.global_attribute14,
2669 X_GLOBAL_ATTRIBUTE15 => x_cust_acct_site_rec.global_attribute15,
2670 X_GLOBAL_ATTRIBUTE16 => x_cust_acct_site_rec.global_attribute16,
2671 X_GLOBAL_ATTRIBUTE17 => x_cust_acct_site_rec.global_attribute17,
2672 X_GLOBAL_ATTRIBUTE18 => x_cust_acct_site_rec.global_attribute18,
2673 X_GLOBAL_ATTRIBUTE19 => x_cust_acct_site_rec.global_attribute19,
2674 X_GLOBAL_ATTRIBUTE20 => x_cust_acct_site_rec.global_attribute20,
2675 X_ORIG_SYSTEM_REFERENCE => x_cust_acct_site_rec.orig_system_reference,
2676 X_STATUS => x_cust_acct_site_rec.status,
2677 X_CUSTOMER_CATEGORY_CODE => x_cust_acct_site_rec.customer_category_code,
2678 X_LANGUAGE => x_cust_acct_site_rec.language,
2679 X_KEY_ACCOUNT_FLAG => x_cust_acct_site_rec.key_account_flag,
2680 X_TP_HEADER_ID => x_cust_acct_site_rec.tp_header_id,
2681 X_ECE_TP_LOCATION_CODE => x_cust_acct_site_rec.ece_tp_location_code,
2682 X_PRIMARY_SPECIALIST_ID => x_cust_acct_site_rec.primary_specialist_id,
2683 X_SECONDARY_SPECIALIST_ID => x_cust_acct_site_rec.secondary_specialist_id,
2684 X_TERRITORY_ID => x_cust_acct_site_rec.territory_id,
2685 X_TERRITORY => x_cust_acct_site_rec.territory,
2686 X_TRANSLATED_CUSTOMER_NAME => x_cust_acct_site_rec.translated_customer_name,
2687 X_CREATED_BY_MODULE => x_cust_acct_site_rec.created_by_module,
2688 X_APPLICATION_ID => x_cust_acct_site_rec.application_id,
2689 X_ORG_ID => x_cust_acct_site_rec.org_id -- Bug 3456489
2690 );
2691
2692 -- Debug info.
2693 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2694 hz_utility_v2pub.debug(p_message=> 'HZ_CUST_ACCT_SITES_PKG.Select_Row (-)',
2695 p_prefix=>l_debug_prefix,
2696 p_msg_level=>fnd_log.level_procedure);
2697 END IF;
2698
2699 -- Standard call to get message count and if count is 1, get message info.
2700 FND_MSG_PUB.Count_And_Get(
2701 p_encoded => FND_API.G_FALSE,
2702 p_count => x_msg_count,
2703 p_data => x_msg_data );
2704
2705 -- Debug info.
2706 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
2707 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2708 p_msg_data=>x_msg_data,
2709 p_msg_type=>'WARNING',
2710 p_msg_level=>fnd_log.level_exception);
2711 END IF;
2712 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2713 hz_utility_v2pub.debug(p_message=>'get_cust_acct_site_rec (-)',
2714 p_prefix=>l_debug_prefix,
2715 p_msg_level=>fnd_log.level_procedure);
2716 END IF;
2717
2718 -- Check if API is called in debug mode. If yes, disable debug.
2719 --disable_debug;
2720
2721 EXCEPTION
2722 WHEN FND_API.G_EXC_ERROR THEN
2723 x_return_status := FND_API.G_RET_STS_ERROR;
2724
2725 FND_MSG_PUB.Count_And_Get(
2726 p_encoded => FND_API.G_FALSE,
2727 p_count => x_msg_count,
2728 p_data => x_msg_data );
2729
2730 -- Debug info.
2731 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2732 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2733 p_msg_data=>x_msg_data,
2734 p_msg_type=>'ERROR',
2735 p_msg_level=>fnd_log.level_error);
2736 END IF;
2737 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2738 hz_utility_v2pub.debug(p_message=>'get_cust_acct_site_rec (-)',
2739 p_prefix=>l_debug_prefix,
2740 p_msg_level=>fnd_log.level_procedure);
2741 END IF;
2742
2743 -- Check if API is called in debug mode. If yes, disable debug.
2744 --disable_debug;
2745
2746 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2747 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2748
2749 FND_MSG_PUB.Count_And_Get(
2750 p_encoded => FND_API.G_FALSE,
2751 p_count => x_msg_count,
2752 p_data => x_msg_data );
2753
2754 -- Debug info.
2755 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2756 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2757 p_msg_data=>x_msg_data,
2758 p_msg_type=>'UNEXPECTED ERROR',
2759 p_msg_level=>fnd_log.level_error);
2760 END IF;
2761 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2762 hz_utility_v2pub.debug(p_message=>'get_cust_acct_site_rec (-)',
2763 p_prefix=>l_debug_prefix,
2764 p_msg_level=>fnd_log.level_procedure);
2765 END IF;
2766
2767 -- Check if API is called in debug mode. If yes, disable debug.
2768 --disable_debug;
2769
2770 WHEN OTHERS THEN
2771 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2772
2773 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
2774 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
2775 FND_MSG_PUB.ADD;
2776
2777 FND_MSG_PUB.Count_And_Get(
2778 p_encoded => FND_API.G_FALSE,
2779 p_count => x_msg_count,
2780 p_data => x_msg_data );
2781
2782 -- Debug info.
2783 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2784 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2785 p_msg_data=>x_msg_data,
2786 p_msg_type=>'SQL ERROR',
2787 p_msg_level=>fnd_log.level_error);
2788 END IF;
2789 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2790 hz_utility_v2pub.debug(p_message=>'get_cust_acct_site_rec (-)',
2791 p_prefix=>l_debug_prefix,
2792 p_msg_level=>fnd_log.level_procedure);
2793 END IF;
2794
2795 -- Check if API is called in debug mode. If yes, disable debug.
2796 --disable_debug;
2797
2798 END get_cust_acct_site_rec;
2799
2800 /**
2801 * PROCEDURE create_cust_site_use
2802 *
2803 * DESCRIPTION
2804 * Creates customer account site use.
2805 *
2806 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2807 * HZ_BUSINESS_EVENT_V2PVT.create_cust_site_use_event
2808 *
2809 * ARGUMENTS
2810 * IN:
2811 * p_init_msg_list Initialize message stack if it is set to
2812 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
2813 * p_cust_site_use_rec Customer account site use record.
2814 * p_customer_profile_rec Customer profile record. One customer account
2815 * must have a customer profile.
2816 * p_create_profile If it is set to FND_API.G_TRUE, API create customer
2817 * profile based on the customer profile record passed
2818 * in.
2819 * p_create_profile_amt If it is set to FND_API.G_TRUE, API create customer
2820 * profile amounts by copying corresponding data
2821 * from customer profile class amounts.
2822 * IN/OUT:
2823 * OUT:
2824 * x_site_use_id Customer account site use ID.
2825 * x_return_status Return status after the call. The status can
2826 * be FND_API.G_RET_STS_SUCCESS (success),
2827 * FND_API.G_RET_STS_ERROR (error),
2828 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
2829 * x_msg_count Number of messages in message stack.
2830 * x_msg_data Message text if x_msg_count is 1.
2831 *
2832 * NOTES
2833 *
2834 * MODIFICATION HISTORY
2835 *
2836 * 07-23-2001 Jianying Huang o Created.
2837 *
2838 */
2839
2840 PROCEDURE create_cust_site_use (
2841 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2842 p_cust_site_use_rec IN CUST_SITE_USE_REC_TYPE,
2843 p_customer_profile_rec IN HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE,
2844 p_create_profile IN VARCHAR2 := FND_API.G_TRUE,
2845 p_create_profile_amt IN VARCHAR2 := FND_API.G_TRUE,
2846 x_site_use_id OUT NOCOPY NUMBER,
2847 x_return_status OUT NOCOPY VARCHAR2,
2848 x_msg_count OUT NOCOPY NUMBER,
2849 x_msg_data OUT NOCOPY VARCHAR2
2850 ) IS
2851
2852 l_cust_site_use_rec CUST_SITE_USE_REC_TYPE := p_cust_site_use_rec;
2853 l_customer_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE := p_customer_profile_rec;
2854 l_debug_prefix VARCHAR2(30) := '';
2855
2856 BEGIN
2857
2858 -- Standard start of API savepoint
2859 SAVEPOINT create_cust_site_use;
2860
2861 -- Check if API is called in debug mode. If yes, enable debug.
2862 --enable_debug;
2863
2864 -- Debug info.
2865 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2866 hz_utility_v2pub.debug(p_message=>'create_cust_site_use (+)',
2867 p_prefix=>l_debug_prefix,
2868 p_msg_level=>fnd_log.level_procedure);
2869 END IF;
2870
2871 -- Initialize message list if p_init_msg_list is set to TRUE.
2872 IF FND_API.to_Boolean(p_init_msg_list) THEN
2873 FND_MSG_PUB.initialize;
2874 END IF;
2875
2876 -- Initialize API return status to success.
2877 x_return_status := FND_API.G_RET_STS_SUCCESS;
2878 -- Call to business logic.
2879 do_create_cust_site_use (
2880 l_cust_site_use_rec,
2881 l_customer_profile_rec,
2882 p_create_profile,
2883 p_create_profile_amt,
2884 x_site_use_id,
2885 x_return_status );
2886
2887 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2888 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'Y')) THEN
2889 -- Invoke business event system.
2890 HZ_BUSINESS_EVENT_V2PVT.create_cust_site_use_event (
2891 l_cust_site_use_rec,
2892 l_customer_profile_rec,
2893 p_create_profile,
2894 p_create_profile_amt );
2895 END IF;
2896
2897 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'BO_EVENTS_ENABLED')) THEN
2898 -- populate function for integration service
2899 HZ_POPULATE_BOT_PKG.pop_hz_cust_site_uses_all(
2900 p_operation => 'I',
2901 p_site_use_id => x_site_use_id );
2902 END IF;
2903 END IF;
2904
2905 -- Standard call to get message count and if count is 1, get message info.
2906 FND_MSG_PUB.Count_And_Get(
2907 p_encoded => FND_API.G_FALSE,
2908 p_count => x_msg_count,
2909 p_data => x_msg_data );
2910
2911 -- Debug info.
2912 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
2913 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2914 p_msg_data=>x_msg_data,
2915 p_msg_type=>'WARNING',
2916 p_msg_level=>fnd_log.level_exception);
2917 END IF;
2918 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2919 hz_utility_v2pub.debug(p_message=>'create_cust_site_use (-)',
2920 p_prefix=>l_debug_prefix,
2921 p_msg_level=>fnd_log.level_procedure);
2922 END IF;
2923 -- Check if API is called in debug mode. If yes, disable debug.
2924 --disable_debug;
2925
2926 EXCEPTION
2927 WHEN FND_API.G_EXC_ERROR THEN
2928 ROLLBACK TO create_cust_site_use;
2929 x_return_status := FND_API.G_RET_STS_ERROR;
2930
2931 FND_MSG_PUB.Count_And_Get(
2932 p_encoded => FND_API.G_FALSE,
2933 p_count => x_msg_count,
2934 p_data => x_msg_data );
2935
2936 -- Debug info.
2937 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2938 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2939 p_msg_data=>x_msg_data,
2940 p_msg_type=>'ERROR',
2941 p_msg_level=>fnd_log.level_error);
2942 END IF;
2943 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2944 hz_utility_v2pub.debug(p_message=>'create_cust_site_use (-)',
2945 p_prefix=>l_debug_prefix,
2946 p_msg_level=>fnd_log.level_procedure);
2947 END IF;
2948
2949 -- Check if API is called in debug mode. If yes, disable debug.
2950 --disable_debug;
2951
2952 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2953 ROLLBACK TO create_cust_site_use;
2954 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2955
2956 FND_MSG_PUB.Count_And_Get(
2957 p_encoded => FND_API.G_FALSE,
2958 p_count => x_msg_count,
2959 p_data => x_msg_data );
2960
2961 -- Debug info.
2962 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2963 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2964 p_msg_data=>x_msg_data,
2965 p_msg_type=>'UNEXPECTED ERROR',
2966 p_msg_level=>fnd_log.level_error);
2967 END IF;
2968 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2969 hz_utility_v2pub.debug(p_message=>'create_cust_site_use (-)',
2970 p_prefix=>l_debug_prefix,
2971 p_msg_level=>fnd_log.level_procedure);
2972 END IF;
2973
2974 -- Check if API is called in debug mode. If yes, disable debug.
2975 --disable_debug;
2976
2977 WHEN OTHERS THEN
2978 ROLLBACK TO create_cust_site_use;
2979 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2980
2981 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
2982 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
2983 FND_MSG_PUB.ADD;
2984
2985 FND_MSG_PUB.Count_And_Get(
2986 p_encoded => FND_API.G_FALSE,
2987 p_count => x_msg_count,
2988 p_data => x_msg_data );
2989
2990 -- Debug info.
2991 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2992 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2993 p_msg_data=>x_msg_data,
2994 p_msg_type=>'SQL ERROR',
2995 p_msg_level=>fnd_log.level_error);
2996 END IF;
2997 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2998 hz_utility_v2pub.debug(p_message=>'create_cust_site_use (-)',
2999 p_prefix=>l_debug_prefix,
3000 p_msg_level=>fnd_log.level_procedure);
3001 END IF;
3002
3003 -- Check if API is called in debug mode. If yes, disable debug.
3004 --disable_debug;
3005
3006 END create_cust_site_use;
3007
3008 /**
3009 * PROCEDURE update_cust_site_use
3010 *
3011 * DESCRIPTION
3012 * Updates customer account site use.
3013 *
3014 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3015 * HZ_BUSINESS_EVENT_V2PVT.update_cust_site_use_event
3016 *
3017 * ARGUMENTS
3018 * IN:
3019 * p_init_msg_list Initialize message stack if it is set to
3020 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
3021 * p_cust_site_use_rec Customer account site use record.
3022 * IN/OUT:
3023 * p_object_version_number Used for locking the being updated record.
3024 * OUT:
3025 * x_return_status Return status after the call. The status can
3026 * be FND_API.G_RET_STS_SUCCESS (success),
3027 * FND_API.G_RET_STS_ERROR (error),
3028 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
3029 * x_msg_count Number of messages in message stack.
3030 * x_msg_data Message text if x_msg_count is 1.
3031 *
3032 * NOTES
3033 *
3034 * MODIFICATION HISTORY
3035 *
3036 * 07-23-2001 Jianying Huang o Created.
3037 *
3038 */
3039
3040 PROCEDURE update_cust_site_use (
3041 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3042 p_cust_site_use_rec IN CUST_SITE_USE_REC_TYPE,
3043 p_object_version_number IN OUT NOCOPY NUMBER,
3044 x_return_status OUT NOCOPY VARCHAR2,
3045 x_msg_count OUT NOCOPY NUMBER,
3046 x_msg_data OUT NOCOPY VARCHAR2
3047 ) IS
3048
3049 l_cust_site_use_rec CUST_SITE_USE_REC_TYPE := p_cust_site_use_rec;
3050 l_old_cust_site_use_rec CUST_SITE_USE_REC_TYPE ;
3051 l_old_customer_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
3052 l_debug_prefix VARCHAR2(30) := '';
3053
3054 BEGIN
3055
3056 -- Standard start of API savepoint
3057 SAVEPOINT update_cust_site_use;
3058
3059 -- Check if API is called in debug mode. If yes, enable debug.
3060 --enable_debug;
3061
3062 -- Debug info.
3063 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3064 hz_utility_v2pub.debug(p_message=>'update_cust_site_use (+)',
3065 p_prefix=>l_debug_prefix,
3066 p_msg_level=>fnd_log.level_procedure);
3067 END IF;
3068
3069 -- Initialize message list if p_init_msg_list is set to TRUE.
3070 IF FND_API.to_Boolean(p_init_msg_list) THEN
3071 FND_MSG_PUB.initialize;
3072 END IF;
3073
3074 -- Initialize API return status to success.
3075 x_return_status := FND_API.G_RET_STS_SUCCESS;
3076
3077 IF (p_cust_site_use_rec.orig_system is not null and p_cust_site_use_rec.orig_system <>fnd_api.g_miss_char)
3078 and (p_cust_site_use_rec.orig_system_reference is not null and p_cust_site_use_rec.orig_system_reference <>fnd_api.g_miss_char)
3079 and (p_cust_site_use_rec.site_use_id = FND_API.G_MISS_NUM or p_cust_site_use_rec.site_use_id is null) THEN
3080 hz_orig_system_ref_pub.get_owner_table_id
3081 (p_orig_system => p_cust_site_use_rec.orig_system,
3082 p_orig_system_reference => p_cust_site_use_rec.orig_system_reference,
3083 p_owner_table_name => 'HZ_CUST_SITE_USES_ALL',
3084 x_owner_table_id => l_cust_site_use_rec.site_use_id ,
3085 x_return_status => x_return_status);
3086 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3087 RAISE FND_API.G_EXC_ERROR;
3088 END IF;
3089
3090 END IF;
3091
3092 --2290537
3093 get_cust_site_use_rec (
3094 p_site_use_id => l_cust_site_use_rec.site_use_id,
3095 x_cust_site_use_rec => l_old_cust_site_use_rec,
3096 x_customer_profile_rec => l_old_customer_profile_rec,
3097 x_return_status => x_return_status,
3098 x_msg_count => x_msg_count,
3099 x_msg_data => x_msg_data);
3100
3101 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3102 RAISE FND_API.G_EXC_ERROR;
3103 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3104 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3105 END IF;
3106
3107 -- Call to business logic.
3108 do_update_cust_site_use (
3109 l_cust_site_use_rec,
3110 p_object_version_number,
3111 x_return_status );
3112
3113 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
3114 l_old_cust_site_use_rec.orig_system := l_cust_site_use_rec.orig_system;
3115 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'Y')) THEN
3116 -- Invoke business event system.
3117 HZ_BUSINESS_EVENT_V2PVT.update_cust_site_use_event (
3118 l_cust_site_use_rec , l_old_cust_site_use_rec);
3119 END IF;
3120
3121 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'BO_EVENTS_ENABLED')) THEN
3122 -- populate function for integration service
3123 HZ_POPULATE_BOT_PKG.pop_hz_cust_site_uses_all(
3124 p_operation => 'U',
3125 p_site_use_id => l_cust_site_use_rec.site_use_id );
3126 END IF;
3127 END IF;
3128
3129 -- Standard call to get message count and if count is 1, get message info.
3130 FND_MSG_PUB.Count_And_Get(
3131 p_encoded => FND_API.G_FALSE,
3132 p_count => x_msg_count,
3133 p_data => x_msg_data );
3134
3135 -- Debug info.
3136 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
3137 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
3138 p_msg_data=>x_msg_data,
3139 p_msg_type=>'WARNING',
3140 p_msg_level=>fnd_log.level_exception);
3141 END IF;
3142 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3143 hz_utility_v2pub.debug(p_message=>'update_cust_site_use (-)',
3144 p_prefix=>l_debug_prefix,
3145 p_msg_level=>fnd_log.level_procedure);
3146 END IF;
3147
3148 -- Check if API is called in debug mode. If yes, disable debug.
3149 --disable_debug;
3150
3151 EXCEPTION
3152 WHEN FND_API.G_EXC_ERROR THEN
3153 ROLLBACK TO update_cust_site_use;
3154 x_return_status := FND_API.G_RET_STS_ERROR;
3155
3156 FND_MSG_PUB.Count_And_Get(
3157 p_encoded => FND_API.G_FALSE,
3158 p_count => x_msg_count,
3159 p_data => x_msg_data );
3160
3161 -- Debug info.
3162 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
3163 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
3164 p_msg_data=>x_msg_data,
3165 p_msg_type=>'ERROR',
3166 p_msg_level=>fnd_log.level_error);
3167 END IF;
3168 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3169 hz_utility_v2pub.debug(p_message=>'update_cust_site_use (-)',
3170 p_prefix=>l_debug_prefix,
3171 p_msg_level=>fnd_log.level_procedure);
3172 END IF;
3173
3174 -- Check if API is called in debug mode. If yes, disable debug.
3175 --disable_debug;
3176
3177 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3178 ROLLBACK TO update_cust_site_use;
3179 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3180
3181 FND_MSG_PUB.Count_And_Get(
3182 p_encoded => FND_API.G_FALSE,
3183 p_count => x_msg_count,
3184 p_data => x_msg_data );
3185
3186 -- Debug info.
3187 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
3188 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
3189 p_msg_data=>x_msg_data,
3190 p_msg_type=>'UNEXPECTED ERROR',
3191 p_msg_level=>fnd_log.level_error);
3192 END IF;
3193 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3194 hz_utility_v2pub.debug(p_message=>'update_cust_site_use (-)',
3195 p_prefix=>l_debug_prefix,
3196 p_msg_level=>fnd_log.level_procedure);
3197 END IF;
3198
3199 -- Check if API is called in debug mode. If yes, disable debug.
3200 --disable_debug;
3201
3202 WHEN OTHERS THEN
3203 ROLLBACK TO update_cust_site_use;
3204 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3205
3206 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
3207 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
3208 FND_MSG_PUB.ADD;
3209
3210 FND_MSG_PUB.Count_And_Get(
3211 p_encoded => FND_API.G_FALSE,
3212 p_count => x_msg_count,
3213 p_data => x_msg_data );
3214
3215 -- Debug info.
3216 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
3217 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
3218 p_msg_data=>x_msg_data,
3219 p_msg_type=>'SQL ERROR',
3220 p_msg_level=>fnd_log.level_error);
3221 END IF;
3222 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3223 hz_utility_v2pub.debug(p_message=> 'update_cust_site_use (-)',
3224 p_prefix=>l_debug_prefix,
3225 p_msg_level=>fnd_log.level_procedure);
3226 END IF;
3227
3228 -- Check if API is called in debug mode. If yes, disable debug.
3229 --disable_debug;
3230
3231 END update_cust_site_use;
3232
3233 /**
3234 * PROCEDURE get_cust_site_use_rec
3235 *
3236 * DESCRIPTION
3237 * Gets customer account site use record
3238 *
3239 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3240 * HZ_CUST_SITE_USES_PKG.Select_Row
3241 *
3242 * ARGUMENTS
3243 * IN:
3244 * p_init_msg_list Initialize message stack if it is set to
3245 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
3246 * p_cust_site_use_id Customer account site use id.
3247 * IN/OUT:
3248 * OUT:
3249 * x_cust_site_use_rec Returned customer account site use record.
3250 * x_customer_profile_rec Returned customer profile record.
3251 * x_return_status Return status after the call. The status can
3252 * be FND_API.G_RET_STS_SUCCESS (success),
3253 * FND_API.G_RET_STS_ERROR (error),
3254 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
3255 * x_msg_count Number of messages in message stack.
3256 * x_msg_data Message text if x_msg_count is 1.
3257 *
3258 * NOTES
3259 *
3260 * MODIFICATION HISTORY
3261 *
3262 * 07-23-2001 Jianying Huang o Created.
3263 *
3264 */
3265
3266 PROCEDURE get_cust_site_use_rec (
3267 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3268 p_site_use_id IN NUMBER,
3269 x_cust_site_use_rec OUT NOCOPY CUST_SITE_USE_REC_TYPE,
3270 x_customer_profile_rec OUT NOCOPY HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE,
3271 x_return_status OUT NOCOPY VARCHAR2,
3272 x_msg_count OUT NOCOPY NUMBER,
3273 x_msg_data OUT NOCOPY VARCHAR2
3274 ) IS
3275
3276 l_cust_account_profile_id NUMBER;
3277 l_debug_prefix VARCHAR2(30) := '';
3278
3279 BEGIN
3280
3281 -- Check if API is called in debug mode. If yes, enable debug.
3282 --enable_debug;
3283
3284 -- Debug info.
3285 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3286 hz_utility_v2pub.debug(p_message=>'get_cust_site_use_rec (+)',
3287 p_prefix=>l_debug_prefix,
3288 p_msg_level=>fnd_log.level_procedure);
3289 END IF;
3290
3291 -- Initialize message list if p_init_msg_list is set to TRUE.
3292 IF FND_API.to_Boolean(p_init_msg_list) THEN
3293 FND_MSG_PUB.initialize;
3294 END IF;
3295
3296 -- Initialize API return status to success.
3297 x_return_status := FND_API.G_RET_STS_SUCCESS;
3298
3299 -- Check whether primary key has been passed in.
3300 IF p_site_use_id IS NULL OR
3301 p_site_use_id = FND_API.G_MISS_NUM THEN
3302 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
3303 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'site_use_id' );
3304 FND_MSG_PUB.ADD;
3305 RAISE FND_API.G_EXC_ERROR;
3306 END IF;
3307
3308 x_cust_site_use_rec.site_use_id := p_site_use_id;
3309
3310 -- Debug info.
3311 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3312 hz_utility_v2pub.debug(p_message=>'HZ_CUST_SITE_USES_PKG.Select_Row (+)',
3313 p_prefix=>l_debug_prefix,
3314 p_msg_level=>fnd_log.level_procedure);
3315 END IF;
3316
3317 -- Call table-handler.
3318 HZ_CUST_SITE_USES_PKG.Select_Row (
3319 X_SITE_USE_ID => x_cust_site_use_rec.site_use_id,
3320 X_CUST_ACCT_SITE_ID => x_cust_site_use_rec.cust_acct_site_id,
3321 X_SITE_USE_CODE => x_cust_site_use_rec.site_use_code,
3322 X_PRIMARY_FLAG => x_cust_site_use_rec.primary_flag,
3323 X_STATUS => x_cust_site_use_rec.status,
3324 X_LOCATION => x_cust_site_use_rec.location,
3325 X_BILL_TO_SITE_USE_ID => x_cust_site_use_rec.bill_to_site_use_id,
3326 X_ORIG_SYSTEM_REFERENCE => x_cust_site_use_rec.orig_system_reference,
3327 X_SIC_CODE => x_cust_site_use_rec.sic_code,
3328 X_PAYMENT_TERM_ID => x_cust_site_use_rec.payment_term_id,
3329 X_GSA_INDICATOR => x_cust_site_use_rec.gsa_indicator,
3330 X_SHIP_PARTIAL => x_cust_site_use_rec.ship_partial,
3331 X_SHIP_VIA => x_cust_site_use_rec.ship_via,
3332 X_FOB_POINT => x_cust_site_use_rec.fob_point,
3333 X_ORDER_TYPE_ID => x_cust_site_use_rec.order_type_id,
3334 X_PRICE_LIST_ID => x_cust_site_use_rec.price_list_id,
3335 X_FREIGHT_TERM => x_cust_site_use_rec.freight_term,
3336 X_WAREHOUSE_ID => x_cust_site_use_rec.warehouse_id,
3337 X_TERRITORY_ID => x_cust_site_use_rec.territory_id,
3338 X_ATTRIBUTE_CATEGORY => x_cust_site_use_rec.attribute_category,
3339 X_ATTRIBUTE1 => x_cust_site_use_rec.attribute1,
3340 X_ATTRIBUTE2 => x_cust_site_use_rec.attribute2,
3341 X_ATTRIBUTE3 => x_cust_site_use_rec.attribute3,
3342 X_ATTRIBUTE4 => x_cust_site_use_rec.attribute4,
3343 X_ATTRIBUTE5 => x_cust_site_use_rec.attribute5,
3344 X_ATTRIBUTE6 => x_cust_site_use_rec.attribute6,
3345 X_ATTRIBUTE7 => x_cust_site_use_rec.attribute7,
3346 X_ATTRIBUTE8 => x_cust_site_use_rec.attribute8,
3347 X_ATTRIBUTE9 => x_cust_site_use_rec.attribute9,
3348 X_ATTRIBUTE10 => x_cust_site_use_rec.attribute10,
3349 X_TAX_REFERENCE => x_cust_site_use_rec.tax_reference,
3350 X_SORT_PRIORITY => x_cust_site_use_rec.sort_priority,
3351 X_TAX_CODE => x_cust_site_use_rec.tax_code,
3352 X_ATTRIBUTE11 => x_cust_site_use_rec.attribute11,
3353 X_ATTRIBUTE12 => x_cust_site_use_rec.attribute12,
3354 X_ATTRIBUTE13 => x_cust_site_use_rec.attribute13,
3355 X_ATTRIBUTE14 => x_cust_site_use_rec.attribute14,
3356 X_ATTRIBUTE15 => x_cust_site_use_rec.attribute15,
3357 X_ATTRIBUTE16 => x_cust_site_use_rec.attribute16,
3358 X_ATTRIBUTE17 => x_cust_site_use_rec.attribute17,
3359 X_ATTRIBUTE18 => x_cust_site_use_rec.attribute18,
3360 X_ATTRIBUTE19 => x_cust_site_use_rec.attribute19,
3361 X_ATTRIBUTE20 => x_cust_site_use_rec.attribute20,
3362 X_ATTRIBUTE21 => x_cust_site_use_rec.attribute21,
3363 X_ATTRIBUTE22 => x_cust_site_use_rec.attribute22,
3364 X_ATTRIBUTE23 => x_cust_site_use_rec.attribute23,
3365 X_ATTRIBUTE24 => x_cust_site_use_rec.attribute24,
3366 X_ATTRIBUTE25 => x_cust_site_use_rec.attribute25,
3367 X_DEMAND_CLASS_CODE => x_cust_site_use_rec.demand_class_code,
3368 X_TAX_HEADER_LEVEL_FLAG => x_cust_site_use_rec.tax_header_level_flag,
3369 X_TAX_ROUNDING_RULE => x_cust_site_use_rec.tax_rounding_rule,
3370 X_GLOBAL_ATTRIBUTE1 => x_cust_site_use_rec.global_attribute1,
3371 X_GLOBAL_ATTRIBUTE2 => x_cust_site_use_rec.global_attribute2,
3372 X_GLOBAL_ATTRIBUTE3 => x_cust_site_use_rec.global_attribute3,
3373 X_GLOBAL_ATTRIBUTE4 => x_cust_site_use_rec.global_attribute4,
3374 X_GLOBAL_ATTRIBUTE5 => x_cust_site_use_rec.global_attribute5,
3375 X_GLOBAL_ATTRIBUTE6 => x_cust_site_use_rec.global_attribute6,
3376 X_GLOBAL_ATTRIBUTE7 => x_cust_site_use_rec.global_attribute7,
3377 X_GLOBAL_ATTRIBUTE8 => x_cust_site_use_rec.global_attribute8,
3378 X_GLOBAL_ATTRIBUTE9 => x_cust_site_use_rec.global_attribute9,
3379 X_GLOBAL_ATTRIBUTE10 => x_cust_site_use_rec.global_attribute10,
3380 X_GLOBAL_ATTRIBUTE11 => x_cust_site_use_rec.global_attribute11,
3381 X_GLOBAL_ATTRIBUTE12 => x_cust_site_use_rec.global_attribute12,
3382 X_GLOBAL_ATTRIBUTE13 => x_cust_site_use_rec.global_attribute13,
3383 X_GLOBAL_ATTRIBUTE14 => x_cust_site_use_rec.global_attribute14,
3384 X_GLOBAL_ATTRIBUTE15 => x_cust_site_use_rec.global_attribute15,
3385 X_GLOBAL_ATTRIBUTE16 => x_cust_site_use_rec.global_attribute16,
3386 X_GLOBAL_ATTRIBUTE17 => x_cust_site_use_rec.global_attribute17,
3387 X_GLOBAL_ATTRIBUTE18 => x_cust_site_use_rec.global_attribute18,
3388 X_GLOBAL_ATTRIBUTE19 => x_cust_site_use_rec.global_attribute19,
3389 X_GLOBAL_ATTRIBUTE20 => x_cust_site_use_rec.global_attribute20,
3390 X_GLOBAL_ATTRIBUTE_CATEGORY => x_cust_site_use_rec.global_attribute_category,
3391 X_PRIMARY_SALESREP_ID => x_cust_site_use_rec.primary_salesrep_id,
3392 X_FINCHRG_RECEIVABLES_TRX_ID => x_cust_site_use_rec.finchrg_receivables_trx_id,
3393 X_DATES_NEGATIVE_TOLERANCE => x_cust_site_use_rec.dates_negative_tolerance,
3394 X_DATES_POSITIVE_TOLERANCE => x_cust_site_use_rec.dates_positive_tolerance,
3395 X_DATE_TYPE_PREFERENCE => x_cust_site_use_rec.date_type_preference,
3396 X_OVER_SHIPMENT_TOLERANCE => x_cust_site_use_rec.over_shipment_tolerance,
3397 X_UNDER_SHIPMENT_TOLERANCE => x_cust_site_use_rec.under_shipment_tolerance,
3398 X_ITEM_CROSS_REF_PREF => x_cust_site_use_rec.item_cross_ref_pref,
3399 X_OVER_RETURN_TOLERANCE => x_cust_site_use_rec.over_return_tolerance,
3400 X_UNDER_RETURN_TOLERANCE => x_cust_site_use_rec.under_return_tolerance,
3401 X_SHIP_SETS_INCLUDE_LINES_FLAG => x_cust_site_use_rec.ship_sets_include_lines_flag,
3402 X_ARRIVALSETS_INCLUDE_LINES_FG => x_cust_site_use_rec.arrivalsets_include_lines_flag,
3403 X_SCHED_DATE_PUSH_FLAG => x_cust_site_use_rec.sched_date_push_flag,
3404 X_INVOICE_QUANTITY_RULE => x_cust_site_use_rec.invoice_quantity_rule,
3405 X_PRICING_EVENT => x_cust_site_use_rec.pricing_event,
3406 X_GL_ID_REC => x_cust_site_use_rec.gl_id_rec,
3407 X_GL_ID_REV => x_cust_site_use_rec.gl_id_rev,
3408 X_GL_ID_TAX => x_cust_site_use_rec.gl_id_tax,
3409 X_GL_ID_FREIGHT => x_cust_site_use_rec.gl_id_freight,
3410 X_GL_ID_CLEARING => x_cust_site_use_rec.gl_id_clearing,
3411 X_GL_ID_UNBILLED => x_cust_site_use_rec.gl_id_unbilled,
3412 X_GL_ID_UNEARNED => x_cust_site_use_rec.gl_id_unearned,
3413 X_GL_ID_UNPAID_REC => x_cust_site_use_rec.gl_id_unpaid_rec,
3414 X_GL_ID_REMITTANCE => x_cust_site_use_rec.gl_id_remittance,
3415 X_GL_ID_FACTOR => x_cust_site_use_rec.gl_id_factor,
3416 X_TAX_CLASSIFICATION => x_cust_site_use_rec.tax_classification,
3417 X_CREATED_BY_MODULE => x_cust_site_use_rec.created_by_module,
3418 X_APPLICATION_ID => x_cust_site_use_rec.application_id,
3419 X_ORG_ID => x_cust_site_use_rec.org_id -- Bug 3456489
3420 );
3421
3422 -- Debug info.
3423 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3424 hz_utility_v2pub.debug(p_message=> 'HZ_CUST_SITE_USES_PKG.Select_Row (-)',
3425 p_prefix=>l_debug_prefix,
3426 p_msg_level=>fnd_log.level_procedure);
3427 END IF;
3428
3429 BEGIN
3430 -- Fetch customer profile id.
3431 SELECT CUST_ACCOUNT_PROFILE_ID INTO l_cust_account_profile_id
3432 FROM HZ_CUSTOMER_PROFILES
3433 WHERE SITE_USE_ID = p_site_use_id;
3434
3435 HZ_CUSTOMER_PROFILE_V2PUB.get_customer_profile_rec (
3436 p_cust_account_profile_id => l_cust_account_profile_id,
3437 x_customer_profile_rec => x_customer_profile_rec,
3438 x_return_status => x_return_status,
3439 x_msg_count => x_msg_count,
3440 x_msg_data => x_msg_data );
3441
3442 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3443 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3444 RAISE FND_API.G_EXC_ERROR;
3445 ELSE
3446 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3447 END IF;
3448 END IF;
3449 EXCEPTION
3450 WHEN NO_DATA_FOUND THEN
3451 NULL;
3452 END;
3453
3454 -- Standard call to get message count and if count is 1, get message info.
3455 FND_MSG_PUB.Count_And_Get(
3456 p_encoded => FND_API.G_FALSE,
3457 p_count => x_msg_count,
3458 p_data => x_msg_data );
3459
3460 -- Debug info.
3461 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
3462 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
3463 p_msg_data=>x_msg_data,
3464 p_msg_type=>'WARNING',
3465 p_msg_level=>fnd_log.level_exception);
3466 END IF;
3467 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3468 hz_utility_v2pub.debug(p_message=>'get_cust_site_use_rec (-)',
3469 p_prefix=>l_debug_prefix,
3470 p_msg_level=>fnd_log.level_procedure);
3471 END IF;
3472
3473 -- Check if API is called in debug mode. If yes, disable debug.
3474 --disable_debug;
3475
3476 EXCEPTION
3477 WHEN FND_API.G_EXC_ERROR THEN
3478 x_return_status := FND_API.G_RET_STS_ERROR;
3479
3480 FND_MSG_PUB.Count_And_Get(
3481 p_encoded => FND_API.G_FALSE,
3482 p_count => x_msg_count,
3483 p_data => x_msg_data );
3484
3485 -- Debug info.
3486 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
3487 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
3488 p_msg_data=>x_msg_data,
3489 p_msg_type=>'ERROR',
3490 p_msg_level=>fnd_log.level_error);
3491 END IF;
3492 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3493 hz_utility_v2pub.debug(p_message=>'get_cust_site_use_rec (-)',
3494 p_prefix=>l_debug_prefix,
3495 p_msg_level=>fnd_log.level_procedure);
3496 END IF;
3497
3498 -- Check if API is called in debug mode. If yes, disable debug.
3499 --disable_debug;
3500
3501 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3502 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3503
3504 FND_MSG_PUB.Count_And_Get(
3505 p_encoded => FND_API.G_FALSE,
3506 p_count => x_msg_count,
3507 p_data => x_msg_data );
3508
3509 -- Debug info.
3510 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
3511 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
3512 p_msg_data=>x_msg_data,
3513 p_msg_type=>'UNEXPECTED ERROR',
3514 p_msg_level=>fnd_log.level_error);
3515 END IF;
3516 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3517 hz_utility_v2pub.debug(p_message=>'get_cust_site_use_rec (-)',
3518 p_prefix=>l_debug_prefix,
3519 p_msg_level=>fnd_log.level_procedure);
3520 END IF;
3521
3522 -- Check if API is called in debug mode. If yes, disable debug.
3523 --disable_debug;
3524
3525 WHEN OTHERS THEN
3526 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3527
3528 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
3529 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
3530 FND_MSG_PUB.ADD;
3531
3532 FND_MSG_PUB.Count_And_Get(
3533 p_encoded => FND_API.G_FALSE,
3534 p_count => x_msg_count,
3535 p_data => x_msg_data );
3536
3537 -- Debug info.
3538 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
3539 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
3540 p_msg_data=>x_msg_data,
3541 p_msg_type=>'SQL ERROR',
3542 p_msg_level=>fnd_log.level_error);
3543 END IF;
3544 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3545 hz_utility_v2pub.debug(p_message=> 'get_cust_site_use_rec (-)',
3546 p_prefix=>l_debug_prefix,
3547 p_msg_level=>fnd_log.level_procedure);
3548 END IF;
3549
3550 -- Check if API is called in debug mode. If yes, disable debug.
3551 --disable_debug;
3552
3553 END get_cust_site_use_rec;
3554
3555 /**
3556 * PRIVATE PROCEDURE check_obsolete_columns
3557 *
3558 * DESCRIPTION
3559 * Check if user is using obsolete columns.
3560 *
3561 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3562 *
3563 * MODIFICATION HISTORY
3564 *
3565 * 07-25-2005 Jianying Huang o Created.
3566 *
3567 */
3568
3569 PROCEDURE check_obsolete_columns (
3570 p_create_update_flag IN VARCHAR2,
3571 p_account_site_rec IN cust_acct_site_rec_type,
3572 p_old_account_site_rec IN cust_acct_site_rec_type DEFAULT NULL,
3573 x_return_status IN OUT NOCOPY VARCHAR2
3574 ) IS
3575
3576 BEGIN
3577
3578 -- check language
3579 IF (p_create_update_flag = 'C' AND
3580 p_account_site_rec.language IS NOT NULL AND
3581 p_account_site_rec.language <> FND_API.G_MISS_CHAR) OR
3582 (p_create_update_flag = 'U' AND
3583 p_account_site_rec.language IS NOT NULL AND
3584 p_account_site_rec.language <> p_old_account_site_rec.language)
3585 THEN
3586 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OBSOLETE_COLUMN');
3587 FND_MESSAGE.SET_TOKEN('COLUMN', 'language');
3588 FND_MSG_PUB.ADD;
3589 x_return_status := FND_API.G_RET_STS_ERROR;
3590 END IF;
3591
3592 END check_obsolete_columns;
3593
3594 END HZ_CUST_ACCOUNT_SITE_V2PUB;