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