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