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