[Home] [Help]
PACKAGE BODY: APPS.HZ_CUSTOMER_PROFILE_V2PUB
Source
1 PACKAGE BODY HZ_CUSTOMER_PROFILE_V2PUB AS
2 /*$Header: ARH2CFSB.pls 120.35.12020000.2 2013/01/30 10:02:26 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_customer_profile (
22 p_customer_profile_rec IN OUT NOCOPY CUSTOMER_PROFILE_REC_TYPE,
23 p_create_profile_amt IN VARCHAR2,
24 x_cust_account_profile_id OUT NOCOPY NUMBER,
25 x_return_status IN OUT NOCOPY VARCHAR2
26 );
27
28 PROCEDURE do_update_customer_profile (
29 p_customer_profile_rec IN OUT NOCOPY CUSTOMER_PROFILE_REC_TYPE,
30 p_object_version_number IN OUT NOCOPY NUMBER,
31 x_return_status IN OUT NOCOPY VARCHAR2
32 );
33
34 PROCEDURE do_create_cust_profile_amt (
35 p_check_foreign_key IN VARCHAR2,
36 p_cust_profile_amt_rec IN OUT NOCOPY CUST_PROFILE_AMT_REC_TYPE,
37 x_cust_acct_profile_amt_id OUT NOCOPY NUMBER,
38 x_return_status IN OUT NOCOPY VARCHAR2
39 );
40
41 PROCEDURE do_update_cust_profile_amt (
42 p_cust_profile_amt_rec IN OUT NOCOPY CUST_PROFILE_AMT_REC_TYPE,
43 p_object_version_number IN OUT NOCOPY NUMBER,
44 x_return_status IN OUT NOCOPY VARCHAR2
45 );
46
47 FUNCTION compute_next_date
48 ( p_date IN DATE,
49 p_period IN VARCHAR2)
50 RETURN DATE;
51
52 FUNCTION party_id_value
53 ( p_cust_account_id IN NUMBER,
54 p_active IN VARCHAR2 )
55 RETURN NUMBER;
56
57 FUNCTION party_exist
58 ( p_party_id IN NUMBER,
59 p_active IN VARCHAR2)
60 RETURN VARCHAR2;
61
62 --------------------------------------
63 -- private procedures and functions
64 --------------------------------------
65 --2310474{
66 /**
67 * Private Function compute_next_date
68 *
69 * Description
70 * Compute the next review date
71 *
72 * MODIFICATION HISTORY
73 * 04-19-2002 Herve Yu o Created
74 *
75 */
76 FUNCTION compute_next_date
77 ( p_date IN DATE,
78 p_period IN VARCHAR2)
79 RETURN DATE
80 IS
81 l_date DATE;
82 BEGIN
83 IF p_period = 'WEEKLY' THEN
84 l_date := p_date + 7;
85 ELSIF p_period = 'MONTHLY' THEN
86 l_date := add_months(p_date,1);
87 ELSIF p_period = 'QUARTERLY' THEN
88 l_date := add_months(p_date,3);
89 ELSIF p_period = 'HALF_YEARLY' THEN
90 l_date := add_months(p_date,6);
91 ELSIF p_period = 'YEARLY' THEN
92 l_date := add_months(p_date,12);
93 ELSE
94 l_date := p_date;
95 END IF;
96 RETURN l_date;
97 END;
98
99 /**
100 * Private Function Party_id_value
101 *
102 * Description
103 * Provide the party_id for a cust_account_id
104 *
105 * MODIFICATION HISTORY
106 * 04-19-2002 Herve Yu o Created
107 *
108 */
109 FUNCTION party_id_value
110 ( p_cust_account_id IN NUMBER,
111 p_active IN VARCHAR2 )
112 RETURN NUMBER
113 IS
114 CURSOR c1 IS
115 SELECT a.party_id
116 FROM hz_cust_accounts a,
117 hz_parties b
118 WHERE a.cust_account_id = p_cust_account_id
119 AND a.party_id = b.party_id
120 AND b.status = DECODE(p_active,'ALL',b.status,p_active);
121 l_party_id NUMBER;
122 BEGIN
123 OPEN c1;
124 FETCH c1 INTO l_party_id;
125 IF c1%NOTFOUND OR l_party_id IS NULL THEN
126 l_party_id := -99999;
127 END IF;
128 CLOSE c1;
129 RETURN l_party_id;
130 END;
131
132 FUNCTION party_exist
133 ( p_party_id IN NUMBER,
134 p_active IN VARCHAR2)
135 RETURN VARCHAR2
136 IS
137 CURSOR c1 IS
138 SELECT 'Y'
139 FROM hz_parties
140 WHERE party_id = p_party_id
141 AND status = DECODE(p_active,'ALL',status,p_active);
142 lact VARCHAR2(1);
143 ret VARCHAR2(1);
144 BEGIN
145 OPEN c1;
146 FETCH c1 INTO lact;
147 IF c1%NOTFOUND THEN
148 ret := 'N';
149 ELSE
150 ret := 'Y';
151 END IF;
152 CLOSE c1;
153 RETURN ret;
154 END;
155
156 /**
157 * Private Function class_review_cycle
158 *
159 * Description
160 * RETURN the Reveiew_Cycle of a Profile Class
161 *
162 * MODIFICATION HISTORY
163 * 04-19-2002 Herve Yu o Created
164 *
165 */
166 FUNCTION class_review_cycle
167 ( p_cust_prof_class_id IN NUMBER)
168 RETURN VARCHAR2
169 IS
170 CURSOR c1 IS
171 SELECT review_cycle
172 FROM hz_cust_profile_classes
173 WHERE profile_class_id = p_cust_prof_class_id;
174 l_review_cycle VARCHAR2(30);
175 BEGIN
176 OPEN c1;
177 FETCH c1 INTO l_review_cycle;
178 IF c1%NOTFOUND THEN
179 l_review_cycle := 'NO_DATA_FOUND';
180 END IF;
181 CLOSE c1;
182 RETURN l_review_cycle;
183 END;
184
185
186 --}
187
188 /**
189 * PRIVATE PROCEDURE enable_debug
190 *
191 * DESCRIPTION
192 * Turn on debug mode.
193 *
194 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
195 * HZ_UTILITY_V2PUB.enable_debug
196 *
197 * MODIFICATION HISTORY
198 *
199 * 07-23-2001 Jianying Huang o Created.
200 *
201 */
202
203 /*PROCEDURE enable_debug IS
204
205 BEGIN
206
207 G_DEBUG_COUNT := G_DEBUG_COUNT + 1;
208
209 IF G_DEBUG_COUNT = 1 THEN
210 IF FND_PROFILE.value( 'HZ_API_FILE_DEBUG_ON' ) = 'Y' OR
211 FND_PROFILE.value( 'HZ_API_DBMS_DEBUG_ON' ) = 'Y'
212 THEN
213 HZ_UTILITY_V2PUB.enable_debug;
214 G_DEBUG := TRUE;
215 END IF;
216 END IF;
217
218 END enable_debug;
219 */
220
221
222 /**
223 * PRIVATE PROCEDURE disable_debug
224 *
225 * DESCRIPTION
226 * Turn off debug mode.
227 *
228 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
229 * HZ_UTILITY_V2PUB.disable_debug
230 *
231 * MODIFICATION HISTORY
232 *
233 * 07-23-2001 Jianying Huang o Created.
234 *
235 */
236
237 /*PROCEDURE disable_debug IS
238
239 BEGIN
240
241 IF G_DEBUG THEN
242 G_DEBUG_COUNT := G_DEBUG_COUNT - 1;
243
244 IF G_DEBUG_COUNT = 0 THEN
245 HZ_UTILITY_V2PUB.disable_debug;
246 G_DEBUG := FALSE;
247 END IF;
248 END IF;
249
250 END disable_debug;
251 */
252
253 /**
254 * PRIVATE PROCEDURE do_create_customer_profile
255 *
256 * DESCRIPTION
257 * Private procedure to create customer profile.
258 *
259 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
260 * HZ_ACCOUNT_VALIDATE_V2PUB.validate_customer_profile
261 * HZ_CUSTOMER_PROFILES_PKG.Insert_Row
262 * HZ_CUST_PROF_CLASS_AMTS_PKG.Select_Row
263 *
264 * ARGUMENTS
265 * IN:
266 * p_create_profile_amt If it is set to FND_API.G_TRUE, API create customer
267 * profile amounts by copying corresponding data
268 * from customer profile class amounts.
269 * IN/OUT:
270 * p_customer_profile_rec Customer profile record. One customer account
271 * must have a customer profile. One account site
272 * use can optionally have one customer profile.
273 * x_return_status Return status after the call. The status can
274 * be FND_API.G_RET_STS_SUCCESS (success),
275 * FND_API.G_RET_STS_ERROR (error),
276 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
277 * OUT:
278 * x_cust_account_profile_id Customer account profile ID.
279 *
280 * NOTES
281 *
282 * MODIFICATION HISTORY
283 *
284 * 07-23-2001 Jianying Huang o Created.
285 * 07-15-2004 Rajib Ranjan Borah o Bug 3767719. Used variable l_default_profile_class_id
286 * instead of using the hard-coded value 0 to improve
287 * performance. This query and the one in package
288 * HZ_ACCOUNT_VALIDATE_V2PUB.validate_customer_profile
289 * will be parsed only once.
290 */
291
292 PROCEDURE do_create_customer_profile (
293 p_customer_profile_rec IN OUT NOCOPY CUSTOMER_PROFILE_REC_TYPE,
294 p_create_profile_amt IN VARCHAR2,
295 x_cust_account_profile_id OUT NOCOPY NUMBER,
296 x_return_status IN OUT NOCOPY VARCHAR2
297 ) IS
298
299 l_debug_prefix VARCHAR2(30) := ''; --'do_create_customer_profile';
300
301 l_is_first BOOLEAN := TRUE;
302 l_return_status VARCHAR2(1);
303 l_msg_count NUMBER;
304 l_msg_data VARCHAR2(2000);
305
306 l_cust_profile_amt_rec CUST_PROFILE_AMT_REC_TYPE;
307 l_status HZ_CUST_PROFILE_CLASSES.status%TYPE;
308 l_profile_class_name HZ_CUST_PROFILE_CLASSES.name%TYPE;
309 l_profile_class_amount_id NUMBER;
310 l_profile_class_id NUMBER;
311
312 -- 2310474 {
313 l_party_id NUMBER;
314 l_review_cycle VARCHAR2(30);
315 l_last_credit_review_date DATE;
316 -- }
317
318 -- Bug 3767719
319 l_default_profile_class_id NUMBER :=0;
320
321 CURSOR c_profile_class_amts IS
322 SELECT PROFILE_CLASS_AMOUNT_ID
323 FROM HZ_CUST_PROF_CLASS_AMTS
324 WHERE PROFILE_CLASS_ID = p_customer_profile_rec.profile_class_id;
325
326 cursor c_acct_use_profile_dtls IS
327 select cons_bill_level, cons_inv_type
328 from hz_customer_profiles
329 where cust_account_id = p_customer_profile_rec.cust_account_id
330 and site_use_id is NULL
331 and cons_inv_flag = 'Y';
332
333 l_cons_bill_level varchar2(30);
334 l_cons_inv_type varchar2(30);
335 v_action VARCHAR2(10);
336 v_entity_code VARCHAR2(1);
337 v_entity_id NUMBER(15);
338 l_profile_class_rec HZ_CUST_PROFILE_CLASSES%ROWTYPE;
339
340 BEGIN
341
342 -- Debug info.
343 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
344 hz_utility_v2pub.debug(p_message=>'do_create_customer_profile (+)',
345 p_prefix=>l_debug_prefix,
346 p_msg_level=>fnd_log.level_procedure);
347 END IF;
348
349 -- Default profile_class_id.
350 IF p_customer_profile_rec.profile_class_id IS NULL OR
351 p_customer_profile_rec.profile_class_id = FND_API.G_MISS_NUM
352 THEN
353 BEGIN
354 SELECT STATUS, NAME INTO l_status, l_profile_class_name
355 FROM HZ_CUST_PROFILE_CLASSES
356 WHERE PROFILE_CLASS_ID = l_default_profile_class_id; -- Bug 3767719.
357
358 IF l_status = 'I' THEN
359 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INACTIVE_PROFILE_CLASS' );
360 FND_MESSAGE.SET_TOKEN( 'NAME', l_profile_class_name );
361 FND_MSG_PUB.ADD;
362 RAISE FND_API.G_EXC_ERROR;
363 END IF;
364
365 -- Setup profile_class_id.
366 p_customer_profile_rec.profile_class_id := 0;
367
368 EXCEPTION
369 WHEN NO_DATA_FOUND THEN
370 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_DEFAULT_PROF_CLASS' );
371 FND_MSG_PUB.ADD;
372 RAISE FND_API.G_EXC_ERROR;
373 END;
374 END IF;
375
376 -- Debug info.
377 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
378 hz_utility_v2pub.debug(p_message=>'get default active profile class (id = 0)',
379 p_prefix =>l_debug_prefix,
380 p_msg_level=>fnd_log.level_statement);
381 END IF;
382
383
384 --{
385 -- 2310474 a value for party_id associated with the customer account
386 -- Algorithm :
387 -- A) Creation mode
388 -- If party_id is null and cust_account_id is null then
389 -- error
390 -- If party_id is not null and cust_account_id is null then
391 -- party_id must be active
392 -- cust_account_id = -1
393 -- If party_id is null and cust_account_id is not null then
394 -- cust_account_id must be associated with an active party_id
395 -- party_id = this active party_id
396 -- If party_id is not null and cust_account_id is not null then
397 -- party_id must be active
398 -- cust_account_id and party_id must be associated in hz_cust_accounts.
399 --
400 IF ( p_customer_profile_rec.party_id IS NULL OR
401 p_customer_profile_rec.party_id = FND_API.G_MISS_NUM )
402 THEN
403
404 IF ( p_customer_profile_rec.cust_account_id IS NULL OR
405 p_customer_profile_rec.cust_account_id = FND_API.G_MISS_NUM )
406 THEN
407 FND_MESSAGE.SET_NAME('AR','HZ_API_CF_ASS_PTY_OR_ACCT');
408 FND_MSG_PUB.ADD;
409 x_return_status := FND_API.G_RET_STS_ERROR;
410 ELSE
411 l_party_id := party_id_value(p_customer_profile_rec.cust_account_id, 'A');
412 IF l_party_id = -99999 THEN
413 FND_MESSAGE.SET_NAME('AR','HZ_API_NO_A_PTY_ASS_ACCT');
414 FND_MESSAGE.SET_TOKEN('ACCT_ID',p_customer_profile_rec.cust_account_id);
415 FND_MSG_PUB.ADD;
416 x_return_status := FND_API.G_RET_STS_ERROR;
417 END IF;
418 p_customer_profile_rec.party_id := l_party_id;
419 END IF;
420
421 ELSE
422
423 IF party_exist( p_customer_profile_rec.party_id, 'A' ) <> 'Y' THEN
424 FND_MESSAGE.SET_NAME('AR','HZ_API_NO_A_PTY');
425 FND_MESSAGE.SET_TOKEN( 'PARTY_ID', p_customer_profile_rec.party_id);
426 FND_MSG_PUB.ADD;
427 x_return_status := FND_API.G_RET_STS_ERROR;
428 END IF;
429
430 IF ( p_customer_profile_rec.cust_account_id IS NULL OR
431 p_customer_profile_rec.cust_account_id = FND_API.G_MISS_NUM )
432 THEN
433 p_customer_profile_rec.cust_account_id := -1;
434 ELSE
435 IF party_id_value(p_customer_profile_rec.cust_account_id, 'A')
436 <> p_customer_profile_rec.party_id
437 THEN
438 FND_MESSAGE.SET_NAME('AR','HZ_API_ACCT_NOT_ASS_PTY');
439 FND_MESSAGE.SET_TOKEN( 'ACCT_ID', p_customer_profile_rec.cust_account_id );
440 FND_MESSAGE.SET_TOKEN( 'PARTY_ID', p_customer_profile_rec.party_id);
441 FND_MSG_PUB.ADD;
442 x_return_status := FND_API.G_RET_STS_ERROR;
443 END IF;
444 END IF;
445
446 END IF;
447 -- }
448 --------------
449 -- Bug 9188425 -Exception block is added to handle invalid Profile Class Id.
450 BEGIN
451 -- to get the default values from profile class
452 SELECT * INTO l_profile_class_rec
453 FROM HZ_CUST_PROFILE_CLASSES
454 WHERE PROFILE_CLASS_ID = p_customer_profile_rec.profile_class_id;
455
456 EXCEPTION
457 WHEN NO_DATA_FOUND THEN
458 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_RECORD' );
459 FND_MESSAGE.SET_TOKEN( 'RECORD', 'Customer Profile Class' );
460 FND_MESSAGE.SET_TOKEN( 'VALUE',
461 NVL( TO_CHAR( p_customer_profile_rec.profile_class_id ), 'null' ) );
462 FND_MSG_PUB.ADD;
463 RAISE FND_API.G_EXC_ERROR;
464 END;
465 -- if cons_inv_flag is NULL, get it defaulted from profile class
466 -- if it is still NULL, assign it as 'N'
467 if p_customer_profile_rec.cons_inv_flag is NULl then
468 if l_profile_class_rec.cons_inv_flag is NOT NULL then
469 p_customer_profile_rec.cons_inv_flag := l_profile_class_rec.cons_inv_flag;
470 else
471 p_customer_profile_rec.cons_inv_flag := 'N';
472 end if;
473 end if;
474
475 -- if cons_inv_flag is 'N', make cons_bill_level and cons_inv_type to NULL
476 if p_customer_profile_rec.cons_inv_flag = 'N' then
477 p_customer_profile_rec.cons_inv_type := NULL;
478 p_customer_profile_rec.cons_bill_level := NULL;
479 elsif p_customer_profile_rec.cons_inv_flag = 'Y' then
480 -- If site_use_id is NOT NULL, then it is for Site use profile
481 -- For site use profile, passed value for bill level should be NULL
482 -- and it should get defaulted from account use profile. Also,
483 -- if cons_inv_type is NULL , it should get defaulted from account use profile.
484 if p_customer_profile_rec.site_use_id is NOT NULL then
485 /*
486 if p_customer_profile_rec.cons_bill_level is NOT NULL then
487 FND_MESSAGE.SET_NAME('AR', 'HZ_API_SITE_BILL_LVL_NULL');
488 FND_MSG_PUB.ADD;
489 x_return_status := FND_API.G_RET_STS_ERROR;
490 end if;
491 */
492 OPEN c_acct_use_profile_dtls;
493 FETCH c_acct_use_profile_dtls INTO l_cons_bill_level, l_cons_inv_type;
494 IF c_acct_use_profile_dtls%NOTFOUND THEN
495 FND_MESSAGE.SET_NAME('AR', 'HZ_API_ENABLE_ACC_BAL_FWD_BILL');
496 FND_MSG_PUB.ADD;
497 x_return_status := FND_API.G_RET_STS_ERROR;
498 END IF;
499 p_customer_profile_rec.cons_bill_level := l_cons_bill_level;
500 if p_customer_profile_rec.cons_inv_type is NULL then
501 p_customer_profile_rec.cons_inv_type := l_cons_inv_type;
502 end if;
503 CLOSE c_acct_use_profile_dtls;
504 -- Else for account use profile, getbthe value from profile class
505 -- if cons_bill_level or cons_inv_type is passed as NULL.
506 else
507 if p_customer_profile_rec.cons_bill_level is NULL then
508 p_customer_profile_rec.cons_bill_level := l_profile_class_rec.cons_bill_level;
509 end if;
510 if p_customer_profile_rec.cons_inv_type is NULL then
511 p_customer_profile_rec.cons_inv_type := l_profile_class_rec.cons_inv_type;
512 end if;
513 end if;
514 end if;
515
516 -- if standard terms is NULL, get it defaulted from profile class
517 if p_customer_profile_rec.standard_terms is NULL then
518 p_customer_profile_rec.standard_terms := l_profile_class_rec.standard_terms;
519 end if;
520
521 -- if late charge payment term or late charge type or message_text_id is NULL, get it defaulted from profile class
522 if p_customer_profile_rec.late_charge_term_id is NULL then
523 p_customer_profile_rec.late_charge_term_id := l_profile_class_rec.late_charge_term_id;
524 end if;
525 if p_customer_profile_rec.late_charge_type is NULL then
526 p_customer_profile_rec.late_charge_type := l_profile_class_rec.late_charge_type;
527 end if;
528
529 if p_customer_profile_rec.message_text_id is NULL then
530 p_customer_profile_rec.message_text_id := l_profile_class_rec.message_text_id;
531 end if;
532
533 if p_customer_profile_rec.automatch_set_id is NULL then
534 p_customer_profile_rec.automatch_set_id := l_profile_class_rec.automatch_set_id;
535 end if;
536
537
538 -- if p_customer_profile_rec.late_charge_type = 'ADJ' then
539 -- p_customer_profile_rec.late_charge_term_id := NULL;
540 -- p_customer_profile_rec.message_text_id := NULL;
541 -- end if;
542 -----------
543 -- Validate customer profile record
544 HZ_ACCOUNT_VALIDATE_V2PUB.validate_customer_profile (
545 p_create_update_flag => 'C',
546 p_customer_profile_rec => p_customer_profile_rec,
547 p_rowid => NULL,
548 x_return_status => x_return_status );
549
550 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
551 RAISE FND_API.G_EXC_ERROR;
552 END IF;
553
554 -- Added rounding of payment_grace_days to integer for Late charge project.
555 IF p_customer_profile_rec.payment_grace_days is NOT NULL then
556 p_customer_profile_rec.payment_grace_days := ROUND(p_customer_profile_rec.payment_grace_days,0);
557 END IF;
558
559 -- Added rounding of interest_period_days to integer for Late charge project.
560 IF p_customer_profile_rec.interest_period_days is NOT NULL then
561 p_customer_profile_rec.interest_period_days := ROUND(p_customer_profile_rec.interest_period_days,0);
562 END IF;
563
564 -- Add logic for global holds
565
566 IF p_customer_profile_rec.credit_hold = 'Y' THEN
567 v_action := 'APPLY';
568 IF p_customer_profile_rec.site_use_id IS NULL THEN
569 v_entity_code := 'C';
570 v_entity_id := p_customer_profile_rec.cust_account_id;
571 ELSE
572 v_entity_code := 'S';
573 v_entity_id := p_customer_profile_rec.site_use_id;
574 END IF;
575
576 -- Debug info.
577 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
578 hz_utility_v2pub.debug(p_message=>'Before call OE_HOLDS... '||
579 'v_action = '||v_action||' '||
580 'v_entity_code = '||v_entity_code||' '||
581 'v_entity_id = '||v_entity_id,
582 p_prefix=>l_debug_prefix,
583 p_msg_level=>fnd_log.level_statement);
584 END IF;
585
586 BEGIN
587 OE_Holds_PUB.Process_Holds (
588 p_api_version => 1.0,
589 p_init_msg_list => FND_API.G_FALSE,
590 p_hold_entity_code => v_entity_code,
591 p_hold_entity_id => v_entity_id,
592 p_hold_id => 1,
593 p_release_reason_code => 'AR_AUTOMATIC',
594 p_action => v_action,
595 x_return_status => l_return_status,
596 x_msg_count => l_msg_count,
597 x_msg_data => l_msg_data);
598
599 -- Debug info.
600 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
601 hz_utility_v2pub.debug(p_message=>'After call OE_HOLDS... '||
602 'l_return_status = '||l_return_status||' '||
603 'l_msg_count = '||l_msg_count||' '||
604 'l_msg_data = '||l_msg_data,
605 p_prefix=>l_debug_prefix,
606 p_msg_level=>fnd_log.level_statement);
607 END IF;
608 EXCEPTION
609 WHEN OTHERS THEN
610 -- Debug info.
611 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
612 hz_utility_v2pub.debug(p_message=>'Exception raised from OE_HOLDS... '||SQLERRM,
613 p_prefix=>l_debug_prefix,
614 p_msg_level=>fnd_log.level_statement);
615 END IF;
616
617 --Bug14059649
618 --We should remove the code where they are suppressing the error being raise
619 --by OM code. TCA team should always pass on the error message back to the user
620 --/ UI / calling API, so that they will know that an error has occurred in OM
621 -- l_return_status := 'S';
622 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
623
624 END;
625
626 --
627 -- only raise unexpected error
628 --Bug14059649
629 --Added FND_API.G_EXC_ERROR
630 --We should remove the code where they are suppressing the error being raise
631 --by OM code. TCA team should always pass on the error message back to the user
632 --/ UI / calling API, so that they will know that an error has occurred in OM
633 --
634 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
635 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
636 ELSIF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
637 RAISE FND_API.G_EXC_ERROR;
638 END IF;
639 END IF;
640
641 -- Call table-handler.
642 -- Table_handler is taking care of default customer profile to profile class.
643
644 -- Debug info.
645 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
646 hz_utility_v2pub.debug(p_message=>'HZ_CUSTOMER_PROFILES_PKG.Insert_Row (+)',
647 p_prefix=>l_debug_prefix,
648 p_msg_level=>fnd_log.level_procedure);
649 END IF;
650
651 HZ_CUSTOMER_PROFILES_PKG.Insert_Row (
652 X_CUST_ACCOUNT_PROFILE_ID => p_customer_profile_rec.cust_account_profile_id,
653 X_CUST_ACCOUNT_ID => p_customer_profile_rec.cust_account_id,
654 X_STATUS => p_customer_profile_rec.status,
655 X_COLLECTOR_ID => p_customer_profile_rec.collector_id,
656 X_CREDIT_ANALYST_ID => p_customer_profile_rec.credit_analyst_id,
657 X_CREDIT_CHECKING => p_customer_profile_rec.credit_checking,
658 X_NEXT_CREDIT_REVIEW_DATE => p_customer_profile_rec.next_credit_review_date,
659 X_TOLERANCE => p_customer_profile_rec.tolerance,
660 X_DISCOUNT_TERMS => p_customer_profile_rec.discount_terms,
661 X_DUNNING_LETTERS => p_customer_profile_rec.dunning_letters,
662 X_INTEREST_CHARGES => p_customer_profile_rec.interest_charges,
663 X_SEND_STATEMENTS => p_customer_profile_rec.send_statements,
664 X_CREDIT_BALANCE_STATEMENTS => p_customer_profile_rec.credit_balance_statements,
665 X_CREDIT_HOLD => p_customer_profile_rec.credit_hold,
666 X_PROFILE_CLASS_ID => p_customer_profile_rec.profile_class_id,
667 X_SITE_USE_ID => p_customer_profile_rec.site_use_id,
668 X_CREDIT_RATING => p_customer_profile_rec.credit_rating,
669 X_RISK_CODE => p_customer_profile_rec.risk_code,
670 X_STANDARD_TERMS => p_customer_profile_rec.standard_terms,
671 X_OVERRIDE_TERMS => p_customer_profile_rec.override_terms,
672 X_DUNNING_LETTER_SET_ID => p_customer_profile_rec.dunning_letter_set_id,
673 X_INTEREST_PERIOD_DAYS => p_customer_profile_rec.interest_period_days,
674 X_PAYMENT_GRACE_DAYS => p_customer_profile_rec.payment_grace_days,
675 X_DISCOUNT_GRACE_DAYS => p_customer_profile_rec.discount_grace_days,
676 X_STATEMENT_CYCLE_ID => p_customer_profile_rec.statement_cycle_id,
677 X_ACCOUNT_STATUS => p_customer_profile_rec.account_status,
678 X_PERCENT_COLLECTABLE => p_customer_profile_rec.percent_collectable,
679 X_AUTOCASH_HIERARCHY_ID => p_customer_profile_rec.autocash_hierarchy_id,
680 X_ATTRIBUTE_CATEGORY => p_customer_profile_rec.attribute_category,
681 X_ATTRIBUTE1 => p_customer_profile_rec.attribute1,
682 X_ATTRIBUTE2 => p_customer_profile_rec.attribute2,
683 X_ATTRIBUTE3 => p_customer_profile_rec.attribute3,
684 X_ATTRIBUTE4 => p_customer_profile_rec.attribute4,
685 X_ATTRIBUTE5 => p_customer_profile_rec.attribute5,
686 X_ATTRIBUTE6 => p_customer_profile_rec.attribute6,
687 X_ATTRIBUTE7 => p_customer_profile_rec.attribute7,
688 X_ATTRIBUTE8 => p_customer_profile_rec.attribute8,
689 X_ATTRIBUTE9 => p_customer_profile_rec.attribute9,
690 X_ATTRIBUTE10 => p_customer_profile_rec.attribute10,
691 X_ATTRIBUTE11 => p_customer_profile_rec.attribute11,
692 X_ATTRIBUTE12 => p_customer_profile_rec.attribute12,
693 X_ATTRIBUTE13 => p_customer_profile_rec.attribute13,
694 X_ATTRIBUTE14 => p_customer_profile_rec.attribute14,
695 X_ATTRIBUTE15 => p_customer_profile_rec.attribute15,
696 X_AUTO_REC_INCL_DISPUTED_FLAG => p_customer_profile_rec.auto_rec_incl_disputed_flag,
697 X_TAX_PRINTING_OPTION => p_customer_profile_rec.tax_printing_option,
698 X_CHARGE_ON_FINANCE_CHARGE_FG => p_customer_profile_rec.charge_on_finance_charge_flag,
699 X_GROUPING_RULE_ID => p_customer_profile_rec.grouping_rule_id,
700 X_CLEARING_DAYS => p_customer_profile_rec.clearing_days,
701 X_JGZZ_ATTRIBUTE_CATEGORY => p_customer_profile_rec.jgzz_attribute_category,
702 X_JGZZ_ATTRIBUTE1 => p_customer_profile_rec.jgzz_attribute1,
703 X_JGZZ_ATTRIBUTE2 => p_customer_profile_rec.jgzz_attribute2,
704 X_JGZZ_ATTRIBUTE3 => p_customer_profile_rec.jgzz_attribute3,
705 X_JGZZ_ATTRIBUTE4 => p_customer_profile_rec.jgzz_attribute4,
706 X_JGZZ_ATTRIBUTE5 => p_customer_profile_rec.jgzz_attribute5,
707 X_JGZZ_ATTRIBUTE6 => p_customer_profile_rec.jgzz_attribute6,
708 X_JGZZ_ATTRIBUTE7 => p_customer_profile_rec.jgzz_attribute7,
709 X_JGZZ_ATTRIBUTE8 => p_customer_profile_rec.jgzz_attribute8,
710 X_JGZZ_ATTRIBUTE9 => p_customer_profile_rec.jgzz_attribute9,
711 X_JGZZ_ATTRIBUTE10 => p_customer_profile_rec.jgzz_attribute10,
712 X_JGZZ_ATTRIBUTE11 => p_customer_profile_rec.jgzz_attribute11,
713 X_JGZZ_ATTRIBUTE12 => p_customer_profile_rec.jgzz_attribute12,
714 X_JGZZ_ATTRIBUTE13 => p_customer_profile_rec.jgzz_attribute13,
715 X_JGZZ_ATTRIBUTE14 => p_customer_profile_rec.jgzz_attribute14,
716 X_JGZZ_ATTRIBUTE15 => p_customer_profile_rec.jgzz_attribute15,
717 X_GLOBAL_ATTRIBUTE1 => p_customer_profile_rec.global_attribute1,
718 X_GLOBAL_ATTRIBUTE2 => p_customer_profile_rec.global_attribute2,
719 X_GLOBAL_ATTRIBUTE3 => p_customer_profile_rec.global_attribute3,
720 X_GLOBAL_ATTRIBUTE4 => p_customer_profile_rec.global_attribute4,
721 X_GLOBAL_ATTRIBUTE5 => p_customer_profile_rec.global_attribute5,
722 X_GLOBAL_ATTRIBUTE6 => p_customer_profile_rec.global_attribute6,
723 X_GLOBAL_ATTRIBUTE7 => p_customer_profile_rec.global_attribute7,
724 X_GLOBAL_ATTRIBUTE8 => p_customer_profile_rec.global_attribute8,
725 X_GLOBAL_ATTRIBUTE9 => p_customer_profile_rec.global_attribute9,
726 X_GLOBAL_ATTRIBUTE10 => p_customer_profile_rec.global_attribute10,
727 X_GLOBAL_ATTRIBUTE11 => p_customer_profile_rec.global_attribute11,
728 X_GLOBAL_ATTRIBUTE12 => p_customer_profile_rec.global_attribute12,
729 X_GLOBAL_ATTRIBUTE13 => p_customer_profile_rec.global_attribute13,
730 X_GLOBAL_ATTRIBUTE14 => p_customer_profile_rec.global_attribute14,
731 X_GLOBAL_ATTRIBUTE15 => p_customer_profile_rec.global_attribute15,
732 X_GLOBAL_ATTRIBUTE16 => p_customer_profile_rec.global_attribute16,
733 X_GLOBAL_ATTRIBUTE17 => p_customer_profile_rec.global_attribute17,
734 X_GLOBAL_ATTRIBUTE18 => p_customer_profile_rec.global_attribute18,
735 X_GLOBAL_ATTRIBUTE19 => p_customer_profile_rec.global_attribute19,
736 X_GLOBAL_ATTRIBUTE20 => p_customer_profile_rec.global_attribute20,
737 X_GLOBAL_ATTRIBUTE_CATEGORY => p_customer_profile_rec.global_attribute_category,
738 X_CONS_INV_FLAG => p_customer_profile_rec.cons_inv_flag,
739 X_CONS_INV_TYPE => p_customer_profile_rec.cons_inv_type,
740 X_AUTOCASH_HIERARCHY_ID_ADR => p_customer_profile_rec.autocash_hierarchy_id_for_adr,
741 X_LOCKBOX_MATCHING_OPTION => p_customer_profile_rec.lockbox_matching_option,
742 X_OBJECT_VERSION_NUMBER => 1,
743 X_CREATED_BY_MODULE => p_customer_profile_rec.created_by_module,
744 X_APPLICATION_ID => p_customer_profile_rec.application_id,
745 X_REVIEW_CYCLE => p_customer_profile_rec.review_cycle,
746 X_last_credit_review_date => p_customer_profile_rec.last_credit_review_date,
747 X_party_id => p_customer_profile_rec.party_id,
748 X_CREDIT_CLASSIFICATION => p_customer_profile_rec.credit_classification,
749 X_CONS_BILL_LEVEL => p_customer_profile_rec.cons_bill_level,
750 X_LATE_CHARGE_CALCULATION_TRX => p_customer_profile_rec.late_charge_calculation_trx,
751 X_CREDIT_ITEMS_FLAG => p_customer_profile_rec.credit_items_flag,
752 X_DISPUTED_TRANSACTIONS_FLAG => p_customer_profile_rec.disputed_transactions_flag,
753 X_LATE_CHARGE_TYPE => p_customer_profile_rec.late_charge_type,
754 X_LATE_CHARGE_TERM_ID => p_customer_profile_rec.late_charge_term_id,
755 X_INTEREST_CALCULATION_PERIOD => p_customer_profile_rec.interest_calculation_period,
756 X_HOLD_CHARGED_INVOICES_FLAG => p_customer_profile_rec.hold_charged_invoices_flag,
757 X_MESSAGE_TEXT_ID => p_customer_profile_rec.message_text_id,
758 X_MULTIPLE_INTEREST_RATES_FLAG => p_customer_profile_rec.multiple_interest_rates_flag,
759 X_CHARGE_BEGIN_DATE => p_customer_profile_rec.charge_begin_date,
760 X_AUTOMATCH_SET_ID => p_customer_profile_rec.automatch_set_id
761 );
762
763 --raji
764
765 x_cust_account_profile_id := p_customer_profile_rec.cust_account_profile_id;
766
767 -- Debug info.
768 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
769 hz_utility_v2pub.debug(p_message=>'HZ_CUSTOMER_PROFILES_PKG.Insert_Row (-) ' ||
770 'x_cust_account_profile_id = ' || x_cust_account_profile_id,
771 p_prefix=>l_debug_prefix,
772 p_msg_level=>fnd_log.level_procedure);
773 END IF;
774
775 -- Now default in the profile amounts only if
776 -- p_create_profile_amt is TRUE. Otherwise, simply return.
777
778 IF p_create_profile_amt = FND_API.G_FALSE THEN
779 RETURN;
780 END IF;
781
782 BEGIN
783
784 -- could have several records in profile_class_amts for a given
785 -- profile_class_id.
786
787 OPEN c_profile_class_amts;
788 LOOP
789 FETCH c_profile_class_amts INTO l_profile_class_amount_id;
790 EXIT WHEN c_profile_class_amts%NOTFOUND;
791
792 -- Setup profile amount record if it is the first run.
793 IF l_is_first THEN
794 l_cust_profile_amt_rec.cust_account_profile_id := p_customer_profile_rec.cust_account_profile_id;
795 l_cust_profile_amt_rec.cust_account_id := p_customer_profile_rec.cust_account_id;
796 l_cust_profile_amt_rec.site_use_id := p_customer_profile_rec.site_use_id;
797 l_cust_profile_amt_rec.created_by_module := p_customer_profile_rec.created_by_module;
798 l_cust_profile_amt_rec.application_id := p_customer_profile_rec.application_id;
799
800 l_is_first := FALSE;
801 END IF;
802
803 -- Debug info.
804 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
805 hz_utility_v2pub.debug(p_message=>'HZ_CUST_PROF_CLASS_AMTS_PKG.Select_Row (+)',
806 p_prefix=>l_debug_prefix,
807 p_msg_level=>fnd_log.level_procedure);
808 END IF;
809
810 -- Call table-handler.
811 HZ_CUST_PROF_CLASS_AMTS_PKG.Select_Row (
812 X_PROFILE_CLASS_AMOUNT_ID => l_profile_class_amount_id,
813 X_PROFILE_CLASS_ID => l_profile_class_id,
814 X_CURRENCY_CODE => l_cust_profile_amt_rec.currency_code,
815 X_TRX_CREDIT_LIMIT => l_cust_profile_amt_rec.trx_credit_limit,
816 X_OVERALL_CREDIT_LIMIT => l_cust_profile_amt_rec.overall_credit_limit,
817 X_MIN_DUNNING_AMOUNT => l_cust_profile_amt_rec.min_dunning_amount,
818 X_MAX_INTEREST_CHARGE => l_cust_profile_amt_rec.max_interest_charge,
819 X_MIN_STATEMENT_AMOUNT => l_cust_profile_amt_rec.min_statement_amount,
820 X_AUTO_REC_MIN_RECEIPT_AMOUNT => l_cust_profile_amt_rec.auto_rec_min_receipt_amount,
821 X_ATTRIBUTE_CATEGORY => l_cust_profile_amt_rec.attribute_category,
822 X_ATTRIBUTE1 => l_cust_profile_amt_rec.attribute1,
823 X_ATTRIBUTE2 => l_cust_profile_amt_rec.attribute2,
824 X_ATTRIBUTE3 => l_cust_profile_amt_rec.attribute3,
825 X_ATTRIBUTE4 => l_cust_profile_amt_rec.attribute4,
826 X_ATTRIBUTE5 => l_cust_profile_amt_rec.attribute5,
827 X_ATTRIBUTE6 => l_cust_profile_amt_rec.attribute6,
828 X_ATTRIBUTE7 => l_cust_profile_amt_rec.attribute7,
829 X_ATTRIBUTE8 => l_cust_profile_amt_rec.attribute8,
830 X_ATTRIBUTE9 => l_cust_profile_amt_rec.attribute9,
831 X_ATTRIBUTE10 => l_cust_profile_amt_rec.attribute10,
832 X_ATTRIBUTE11 => l_cust_profile_amt_rec.attribute11,
833 X_ATTRIBUTE12 => l_cust_profile_amt_rec.attribute12,
834 X_ATTRIBUTE13 => l_cust_profile_amt_rec.attribute13,
835 X_ATTRIBUTE14 => l_cust_profile_amt_rec.attribute14,
836 X_ATTRIBUTE15 => l_cust_profile_amt_rec.attribute15,
837 X_INTEREST_RATE => l_cust_profile_amt_rec.interest_rate,
838 X_MIN_FC_BALANCE_AMOUNT => l_cust_profile_amt_rec.min_fc_balance_amount,
839 X_MIN_FC_INVOICE_AMOUNT => l_cust_profile_amt_rec.min_fc_invoice_amount,
840 X_MIN_DUNNING_INVOICE_AMOUNT => l_cust_profile_amt_rec.min_dunning_invoice_amount,
841 X_EXPIRATION_DATE => l_cust_profile_amt_rec.expiration_date,
842 X_JGZZ_ATTRIBUTE_CATEGORY => l_cust_profile_amt_rec.jgzz_attribute_category,
843 X_JGZZ_ATTRIBUTE1 => l_cust_profile_amt_rec.jgzz_attribute1,
844 X_JGZZ_ATTRIBUTE2 => l_cust_profile_amt_rec.jgzz_attribute2,
845 X_JGZZ_ATTRIBUTE3 => l_cust_profile_amt_rec.jgzz_attribute3,
846 X_JGZZ_ATTRIBUTE4 => l_cust_profile_amt_rec.jgzz_attribute4,
847 X_JGZZ_ATTRIBUTE5 => l_cust_profile_amt_rec.jgzz_attribute5,
848 X_JGZZ_ATTRIBUTE6 => l_cust_profile_amt_rec.jgzz_attribute6,
849 X_JGZZ_ATTRIBUTE7 => l_cust_profile_amt_rec.jgzz_attribute7,
850 X_JGZZ_ATTRIBUTE8 => l_cust_profile_amt_rec.jgzz_attribute8,
851 X_JGZZ_ATTRIBUTE9 => l_cust_profile_amt_rec.jgzz_attribute9,
852 X_JGZZ_ATTRIBUTE10 => l_cust_profile_amt_rec.jgzz_attribute10,
853 X_JGZZ_ATTRIBUTE11 => l_cust_profile_amt_rec.jgzz_attribute11,
854 X_JGZZ_ATTRIBUTE12 => l_cust_profile_amt_rec.jgzz_attribute12,
855 X_JGZZ_ATTRIBUTE13 => l_cust_profile_amt_rec.jgzz_attribute13,
856 X_JGZZ_ATTRIBUTE14 => l_cust_profile_amt_rec.jgzz_attribute14,
857 X_JGZZ_ATTRIBUTE15 => l_cust_profile_amt_rec.jgzz_attribute15,
858 X_GLOBAL_ATTRIBUTE1 => l_cust_profile_amt_rec.global_attribute1,
859 X_GLOBAL_ATTRIBUTE2 => l_cust_profile_amt_rec.global_attribute2,
860 X_GLOBAL_ATTRIBUTE3 => l_cust_profile_amt_rec.global_attribute3,
861 X_GLOBAL_ATTRIBUTE4 => l_cust_profile_amt_rec.global_attribute4,
862 X_GLOBAL_ATTRIBUTE5 => l_cust_profile_amt_rec.global_attribute5,
863 X_GLOBAL_ATTRIBUTE6 => l_cust_profile_amt_rec.global_attribute6,
864 X_GLOBAL_ATTRIBUTE7 => l_cust_profile_amt_rec.global_attribute7,
865 X_GLOBAL_ATTRIBUTE8 => l_cust_profile_amt_rec.global_attribute8,
866 X_GLOBAL_ATTRIBUTE9 => l_cust_profile_amt_rec.global_attribute9,
867 X_GLOBAL_ATTRIBUTE10 => l_cust_profile_amt_rec.global_attribute10,
868 X_GLOBAL_ATTRIBUTE11 => l_cust_profile_amt_rec.global_attribute11,
869 X_GLOBAL_ATTRIBUTE12 => l_cust_profile_amt_rec.global_attribute12,
870 X_GLOBAL_ATTRIBUTE13 => l_cust_profile_amt_rec.global_attribute13,
871 X_GLOBAL_ATTRIBUTE14 => l_cust_profile_amt_rec.global_attribute14,
872 X_GLOBAL_ATTRIBUTE15 => l_cust_profile_amt_rec.global_attribute15,
873 X_GLOBAL_ATTRIBUTE16 => l_cust_profile_amt_rec.global_attribute16,
874 X_GLOBAL_ATTRIBUTE17 => l_cust_profile_amt_rec.global_attribute17,
875 X_GLOBAL_ATTRIBUTE18 => l_cust_profile_amt_rec.global_attribute18,
876 X_GLOBAL_ATTRIBUTE19 => l_cust_profile_amt_rec.global_attribute19,
877 X_GLOBAL_ATTRIBUTE20 => l_cust_profile_amt_rec.global_attribute20,
878 X_GLOBAL_ATTRIBUTE_CATEGORY => l_cust_profile_amt_rec.global_attribute_category,
879 X_EXCHANGE_RATE_TYPE => l_cust_profile_amt_rec.exchange_rate_type,
880 X_MIN_FC_INVOICE_OVERDUE_TYPE => l_cust_profile_amt_rec.min_fc_invoice_overdue_type,
881 X_MIN_FC_INVOICE_PERCENT => l_cust_profile_amt_rec.min_fc_invoice_percent,
882 X_MIN_FC_BALANCE_OVERDUE_TYPE => l_cust_profile_amt_rec.min_fc_balance_overdue_type,
883 X_MIN_FC_BALANCE_PERCENT => l_cust_profile_amt_rec.min_fc_balance_percent,
884 X_INTEREST_TYPE => l_cust_profile_amt_rec.interest_type,
885 X_INTEREST_FIXED_AMOUNT => l_cust_profile_amt_rec.interest_fixed_amount,
886 X_INTEREST_SCHEDULE_ID => l_cust_profile_amt_rec.interest_schedule_id,
887 X_PENALTY_TYPE => l_cust_profile_amt_rec.penalty_type,
888 X_PENALTY_RATE => l_cust_profile_amt_rec.penalty_rate,
889 X_MIN_INTEREST_CHARGE => l_cust_profile_amt_rec.min_interest_charge,
890 X_PENALTY_FIXED_AMOUNT => l_cust_profile_amt_rec.penalty_fixed_amount,
891 X_PENALTY_SCHEDULE_ID => l_cust_profile_amt_rec.penalty_schedule_id
892 );
893
894 -- Debug info.
895 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
896 hz_utility_v2pub.debug(p_message=>'HZ_CUST_PROF_CLASS_AMTS_PKG.Select_Row (-)',
897 p_prefix=>l_debug_prefix,
898 p_msg_level=>fnd_log.level_procedure);
899 END IF;
900
901 l_cust_profile_amt_rec.cust_acct_profile_amt_id := NULL;
902
903 -- Call public API to create cust profile amount.
904 create_cust_profile_amt (
905 p_cust_profile_amt_rec => l_cust_profile_amt_rec,
906 x_cust_acct_profile_amt_id => l_cust_profile_amt_rec.cust_acct_profile_amt_id,
907 x_return_status => x_return_status,
908 x_msg_count => l_msg_count,
909 x_msg_data => l_msg_data );
910
911 -- Bug#2219199 Procedure call added to cascade profile class amount -- multi currency
912
913 IF x_return_status = 'S' THEN
914 HZ_CREDIT_USAGES_CASCADE_PKG.cascade_credit_usage_rules (
915 l_cust_profile_amt_rec.cust_acct_profile_amt_id,
916 l_cust_profile_amt_rec.cust_account_profile_id,
917 l_profile_class_amount_id,
918 l_profile_class_id,
919 x_return_status,
920 l_msg_count,
921 l_msg_data );
922 END IF;
923
924 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
925 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
926 RAISE FND_API.G_EXC_ERROR;
927 ELSE
928 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
929 END IF;
930 END IF;
931
932 END LOOP;
933 CLOSE c_profile_class_amts;
934
935 EXCEPTION
936 WHEN OTHERS THEN
937 IF c_profile_class_amts%ISOPEN THEN
938 CLOSE c_profile_class_amts;
939 END IF;
940
941 RAISE;
942 END;
943
944 -- Debug info.
945 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
946 hz_utility_v2pub.debug(p_message=>'do_create_customer_profile (-)',
947 p_prefix=>l_debug_prefix,
948 p_msg_level=>fnd_log.level_procedure);
949 END IF;
950
951 END do_create_customer_profile;
952
953
954 /**
955 * PRIVATE PROCEDURE do_update_customer_profile
956 *
957 * DESCRIPTION
958 * Private procedure to update customer profile.
959 *
960 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
961 * HZ_ACCOUNT_VALIDATE_V2PUB.validate_customer_profile
962 * HZ_CUSTOMER_PROFILES_PKG.Update_Row
963 * HZ_CUST_PROF_CLASS_AMTS_PKG.Select_Row
964 *
965 * ARGUMENTS
966 * IN/OUT:
967 * p_customer_profile_rec Customer profile record. One customer account
968 * must have a customer profile. One account site
969 * use can optionally have one customer profile.
970 * p_object_version_number Used for locking the being updated record.
971 * x_return_status Return status after the call. The status can
972 * be FND_API.G_RET_STS_SUCCESS (success),
973 * FND_API.G_RET_STS_ERROR (error),
974 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
975 *
976 * NOTES
977 *
978 * MODIFICATION HISTORY
979 *
980 * 07-23-2001 Jianying Huang o Created.
981 *
982 */
983
984 PROCEDURE do_update_customer_profile (
985 p_customer_profile_rec IN OUT NOCOPY CUSTOMER_PROFILE_REC_TYPE,
986 p_object_version_number IN OUT NOCOPY NUMBER,
987 x_return_status IN OUT NOCOPY VARCHAR2
988 ) IS
989
990 l_debug_prefix VARCHAR2(30) := ''; --'do_update_customer_profile'
991
992 l_rowid ROWID := NULL;
993 l_is_first BOOLEAN := TRUE;
994 l_return_status VARCHAR2(1);
995 l_msg_count NUMBER;
996 l_msg_data VARCHAR2(2000);
997
998 l_cust_profile_amt_rec CUST_PROFILE_AMT_REC_TYPE;
999 l_cust_account_id NUMBER;
1000 l_site_use_id NUMBER;
1001 l_profile_class_amount_id NUMBER;
1002 l_profile_class_id NUMBER;
1003 l_cust_acct_profile_amt_id NUMBER;
1004 l_object_version_number NUMBER;
1005 l_amt_object_version_number NUMBER;
1006 l_created_by_module HZ_CUSTOMER_PROFILES.created_by_module%TYPE;
1007 l_application_id NUMBER;
1008
1009 l_party_id NUMBER;
1010 l_last_credit_review_date DATE;
1011
1012 l_credit_hold VARCHAR2(30); -- Bug 4115750
1013
1014 CURSOR c_profile_class_amts IS
1015 SELECT PROFILE_CLASS_AMOUNT_ID
1016 FROM HZ_CUST_PROF_CLASS_AMTS
1017 WHERE PROFILE_CLASS_ID = p_customer_profile_rec.profile_class_id;
1018
1019 v_action VARCHAR2(10);
1020 v_entity_code VARCHAR2(1);
1021 v_entity_id NUMBER(15);
1022 l_cons_inv_flag VARCHAR2(1);
1023 l_cons_inv_type VARCHAR2(30);
1024 l_cons_bill_level VARCHAR2(30);
1025 l_late_charge_type HZ_CUSTOMER_PROFILES.LATE_CHARGE_TYPE%TYPE;
1026 l_late_charge_term_id HZ_CUSTOMER_PROFILES.LATE_CHARGE_TERM_ID%TYPE;
1027 l_message_text_id HZ_CUSTOMER_PROFILES.MESSAGE_TEXT_ID%TYPE;
1028 l_profile_class_rec HZ_CUST_PROFILE_CLASSES%ROWTYPE;
1029 l_standard_terms NUMBER;
1030 l_automatch_set_id NUMBER;
1031 l_profile_class_changed VARCHAR2(10);
1032
1033 cursor c_acct_use_profile_dtls IS
1034 select cons_bill_level, cons_inv_type
1035 from hz_customer_profiles
1036 where cust_account_id = l_cust_account_id
1037 and site_use_id is NULL
1038 and cons_inv_flag = 'Y';
1039
1040 ll_cons_bill_level varchar2(30);
1041 ll_cons_inv_type varchar2(30);
1042
1043 BEGIN
1044
1045 -- Debug info.
1046 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1047 hz_utility_v2pub.debug(p_message=>'do_update_customer_profile (+)',
1048 p_prefix=>l_debug_prefix,
1049 p_msg_level=>fnd_log.level_procedure);
1050 END IF;
1051
1052 -- Lock record.
1053 BEGIN
1054 SELECT ROWID, OBJECT_VERSION_NUMBER, CUST_ACCOUNT_ID, SITE_USE_ID,
1055 CREATED_BY_MODULE, APPLICATION_ID,PROFILE_CLASS_ID, PARTY_ID,
1056 CREDIT_HOLD , CONS_INV_FLAG, CONS_INV_TYPE, CONS_BILL_LEVEL,
1057 LATE_CHARGE_TYPE, LATE_CHARGE_TERM_ID, MESSAGE_TEXT_ID, STANDARD_TERMS,
1058 AUTOMATCH_SET_ID
1059 INTO l_rowid, l_object_version_number, l_cust_account_id, l_site_use_id,
1060 l_created_by_module, l_application_id,l_profile_class_id, l_party_id,
1061 l_credit_hold, l_cons_inv_flag, l_cons_inv_type, l_cons_bill_level,
1062 l_late_charge_type, l_late_charge_term_id, l_message_text_id, l_standard_terms,
1063 l_automatch_set_id
1064 FROM HZ_CUSTOMER_PROFILES
1065 WHERE CUST_ACCOUNT_PROFILE_ID = p_customer_profile_rec.cust_account_profile_id
1066 FOR UPDATE NOWAIT;
1067
1068 IF NOT (
1069 ( p_object_version_number IS NULL AND l_object_version_number IS NULL ) OR
1070 ( p_object_version_number IS NOT NULL AND
1071 l_object_version_number IS NOT NULL AND
1072 p_object_version_number = l_object_version_number ) )
1073 THEN
1074 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_RECORD_CHANGED' );
1075 FND_MESSAGE.SET_TOKEN( 'TABLE', 'hz_customer_profiles' );
1076 FND_MSG_PUB.ADD;
1077 RAISE FND_API.G_EXC_ERROR;
1078 END IF;
1079
1080 p_object_version_number := NVL( l_object_version_number, 1 ) + 1;
1081
1082 EXCEPTION
1083 WHEN NO_DATA_FOUND THEN
1084 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_RECORD' );
1085 FND_MESSAGE.SET_TOKEN( 'RECORD', 'customer profile' );
1086 FND_MESSAGE.SET_TOKEN( 'VALUE',
1087 NVL( TO_CHAR( p_customer_profile_rec.cust_account_profile_id ), 'null' ) );
1088 FND_MSG_PUB.ADD;
1089 RAISE FND_API.G_EXC_ERROR;
1090 END;
1091
1092 l_profile_class_changed := 'N';
1093 -- Bug 9188425 -Exception block is added to handle invalid Profile Class Id.
1094 BEGIN
1095 -- If the profile_class_id is changed get profile class rec into l_profile_class_rec
1096 IF (p_customer_profile_rec.profile_class_id IS NOT NULL AND
1097 p_customer_profile_rec.profile_class_id <> FND_API.G_MISS_NUM AND
1098 p_customer_profile_rec.profile_class_id <> NVL(l_profile_class_id, FND_API.G_MISS_NUM)) then
1099
1100 l_profile_class_changed := 'Y';
1101
1102 SELECT * INTO l_profile_class_rec
1103 FROM HZ_CUST_PROFILE_CLASSES
1104 WHERE PROFILE_CLASS_ID = p_customer_profile_rec.profile_class_id;
1105
1106 -- While changing the profile class for an existing cusromer profile record
1107 -- it should do the validations based on the new input parameters.
1108 -- If the input parameters are passed NULL or G_MISS, get the value from profile class.
1109 if ( p_customer_profile_rec.cons_inv_flag is NULL OR
1110 p_customer_profile_rec.cons_inv_flag = FND_API.G_MISS_CHAR) then
1111 p_customer_profile_rec.cons_inv_flag := l_profile_class_rec.cons_inv_flag;
1112 end if;
1113 if ( p_customer_profile_rec.cons_bill_level is NULL OR
1114 p_customer_profile_rec.cons_bill_level = FND_API.G_MISS_CHAR) then
1115 p_customer_profile_rec.cons_bill_level := l_profile_class_rec.cons_bill_level;
1116 end if;
1117 if ( p_customer_profile_rec.cons_inv_type is NULL OR
1118 p_customer_profile_rec.cons_inv_type = FND_API.G_MISS_CHAR) then
1119 p_customer_profile_rec.cons_inv_type := l_profile_class_rec.cons_inv_type;
1120 end if;
1121 if ( p_customer_profile_rec.standard_terms is NULL OR
1122 p_customer_profile_rec.standard_terms = FND_API.G_MISS_NUM) then
1123 p_customer_profile_rec.standard_terms := l_profile_class_rec.standard_terms;
1124 end if;
1125 --Bug9151634,9197547
1126 --Assigning Profile Class's value.
1127 if ( p_customer_profile_rec.review_cycle is NULL OR
1128 p_customer_profile_rec.review_cycle = FND_API.G_MISS_CHAR) then
1129 p_customer_profile_rec.review_cycle := l_profile_class_rec.review_cycle;
1130 end if;
1131 END IF;
1132 EXCEPTION
1133 WHEN NO_DATA_FOUND THEN
1134 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_RECORD' );
1135 FND_MESSAGE.SET_TOKEN( 'RECORD', 'Customer Profile Class' );
1136 FND_MESSAGE.SET_TOKEN( 'VALUE',
1137 NVL( TO_CHAR( p_customer_profile_rec.profile_class_id ), 'null' ) );
1138 FND_MSG_PUB.ADD;
1139 RAISE FND_API.G_EXC_ERROR;
1140 END;
1141 -- Value for cons_inv_flag should be Y or N
1142 if p_customer_profile_rec.cons_inv_flag is NULL then
1143 p_customer_profile_rec.cons_inv_flag := l_cons_inv_flag;
1144 elsif p_customer_profile_rec.cons_inv_flag = FND_API.G_MISS_CHAR then
1145 p_customer_profile_rec.cons_inv_flag := 'N';
1146 end if;
1147
1148 -- If user passes NULL in update set value as in database
1149 if p_customer_profile_rec.cons_bill_level is NULL then
1150 p_customer_profile_rec.cons_bill_level := l_cons_bill_level;
1151 end if;
1152
1153 -- if cons_inv_flag is 'N', make cons_bill_level and cons_inv_type to NULL
1154 if p_customer_profile_rec.cons_inv_flag = 'N' then
1155 p_customer_profile_rec.cons_bill_level := FND_API.G_MISS_CHAR;
1156 p_customer_profile_rec.cons_inv_type := FND_API.G_MISS_CHAR;
1157 end if;
1158
1159 -- Bill level is non updatable at site level
1160 /*
1161 if l_site_use_id is NOT NULL AND
1162 p_customer_profile_rec.cons_bill_level <> FND_API.G_MISS_CHAR AND
1163 p_customer_profile_rec.cons_bill_level <> NVL(l_cons_bill_level, FND_API.G_MISS_CHAR) then
1164 FND_MESSAGE.SET_NAME('AR', 'HZ_API_SITE_BILL_LVL_NULL');
1165 FND_MSG_PUB.ADD;
1166 x_return_status := FND_API.G_RET_STS_ERROR;
1167 end if;
1168 */
1169
1170 -- if cons_inv_flag is changed from N to Y for a site level,
1171 -- get the bill level from account level
1172 if (p_customer_profile_rec.cons_inv_flag = 'Y' and
1173 l_cons_inv_flag = 'N' and
1174 l_site_use_id is NOT NULL) then
1175 -- For site use profile, passed value for bill level should be NULL
1176 -- and it should get defaulted from account use profile.
1177 OPEN c_acct_use_profile_dtls;
1178 FETCH c_acct_use_profile_dtls INTO ll_cons_bill_level, ll_cons_inv_type;
1179 IF c_acct_use_profile_dtls%NOTFOUND THEN
1180 FND_MESSAGE.SET_NAME('AR', 'HZ_API_ENABLE_ACC_BAL_FWD_BILL');
1181 FND_MSG_PUB.ADD;
1182 x_return_status := FND_API.G_RET_STS_ERROR;
1183 END IF;
1184 p_customer_profile_rec.cons_bill_level := ll_cons_bill_level;
1185 if ( p_customer_profile_rec.cons_inv_type is NULL OR
1186 p_customer_profile_rec.cons_inv_type = FND_API.G_MISS_CHAR) then
1187 p_customer_profile_rec.cons_inv_type := ll_cons_inv_type;
1188 end if;
1189 CLOSE c_acct_use_profile_dtls;
1190 end if;
1191
1192 -- If user passes NULL in update set value as in database
1193 if p_customer_profile_rec.cons_inv_type is NULL then
1194 p_customer_profile_rec.cons_inv_type := l_cons_inv_type;
1195 end if;
1196
1197 -- Added the below IF condition for Balance Forwarded Billing
1198 -- When cons_inv_flag is changed from 'N' to 'Y' or 'Y' to 'N'
1199 -- and profile class is not changed
1200 -- update the standard_terms of the profile to null
1201 -- Bug 8349169 -In NVL function G_MISS_CHAR changed to 'N', null will consider as 'N'
1202 if p_customer_profile_rec.cons_inv_flag IS NOT NULL AND
1203 p_customer_profile_rec.cons_inv_flag <> FND_API.G_MISS_CHAR AND
1204 p_customer_profile_rec.cons_inv_flag <> NVL(l_cons_inv_flag, 'N') AND
1205 l_profile_class_changed = 'N' then
1206 if (p_customer_profile_rec.standard_terms is NULL OR
1207 p_customer_profile_rec.standard_terms = nvl(l_standard_terms,-999111))
1208 then
1209 --l_standard_terms
1210 p_customer_profile_rec.standard_terms := FND_API.G_MISS_NUM;
1211 end if;
1212 end if;
1213
1214 -- if late charge payment term or late charge type or message_text_id is NULL, get value from database.
1215 if p_customer_profile_rec.late_charge_term_id is NULL then
1216 p_customer_profile_rec.late_charge_term_id := l_late_charge_term_id;
1217 elsif p_customer_profile_rec.late_charge_term_id = FND_API.G_MISS_NUM then
1218 p_customer_profile_rec.late_charge_term_id := l_profile_class_rec.late_charge_term_id;
1219 end if;
1220 if p_customer_profile_rec.late_charge_type is NULL then
1221 p_customer_profile_rec.late_charge_type := l_late_charge_type;
1222 elsif p_customer_profile_rec.late_charge_type = FND_API.G_MISS_CHAR then
1223 p_customer_profile_rec.late_charge_type := l_profile_class_rec.late_charge_type;
1224 end if;
1225 if p_customer_profile_rec.message_text_id is NULL then
1226 p_customer_profile_rec.message_text_id := l_message_text_id;
1227 elsif p_customer_profile_rec.message_text_id = FND_API.G_MISS_NUM then
1228 p_customer_profile_rec.message_text_id := l_profile_class_rec.message_text_id;
1229 end if;
1230
1231 if p_customer_profile_rec.late_charge_type = 'ADJ' then
1232 p_customer_profile_rec.late_charge_term_id := FND_API.G_MISS_NUM;
1233 p_customer_profile_rec.message_text_id := FND_API.G_MISS_NUM;
1234 end if;
1235
1236 -- Validate customer profile record
1237 HZ_ACCOUNT_VALIDATE_V2PUB.validate_customer_profile (
1238 p_create_update_flag => 'U',
1239 p_customer_profile_rec => p_customer_profile_rec,
1240 p_rowid => l_rowid,
1241 x_return_status => x_return_status );
1242
1243 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1244 RAISE FND_API.G_EXC_ERROR;
1245 END IF;
1246
1247 -- Added rounding of payment_grace_days to integer for Late charge project.
1248 IF (p_customer_profile_rec.payment_grace_days is NOT NULL AND
1249 p_customer_profile_rec.payment_grace_days <> FND_API.G_MISS_NUM) then
1250 p_customer_profile_rec.payment_grace_days := ROUND(p_customer_profile_rec.payment_grace_days,0);
1251 END IF;
1252
1253 -- Added rounding of interest_period_days to integer for Late charge project.
1254 IF (p_customer_profile_rec.interest_period_days is NOT NULL AND
1255 p_customer_profile_rec.interest_period_days <> FND_API.G_MISS_NUM) then
1256 p_customer_profile_rec.interest_period_days := ROUND(p_customer_profile_rec.interest_period_days,0);
1257 END IF;
1258
1259 -- Add logic for global holds
1260 IF p_customer_profile_rec.credit_hold is not null THEN --Bug5606895
1261 IF nvl(l_credit_hold,'N') <> nvl(p_customer_profile_rec.credit_hold,'N') THEN
1262 if p_customer_profile_rec.credit_hold = 'Y' THEN
1263 v_action := 'APPLY';
1264 ELSE
1265 v_action := 'RELEASE';
1266 END IF;
1267 IF l_site_use_id IS NULL THEN
1268 v_entity_code := 'C';
1269 v_entity_id := l_cust_account_id;
1270 ELSE
1271 v_entity_code := 'S';
1272 v_entity_id := l_site_use_id;
1273 END IF;
1274
1275 -- Debug info.
1276 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1277 hz_utility_v2pub.debug(p_message=>'Before call OE_HOLDS... '||
1278 'v_action = '||v_action||' '||
1279 'v_entity_code = '||v_entity_code||' '||
1280 'v_entity_id = '||v_entity_id,
1281 p_prefix=>l_debug_prefix,
1282 p_msg_level=>fnd_log.level_statement);
1283 END IF;
1284
1285 BEGIN
1286 OE_Holds_PUB.Process_Holds (
1287 p_api_version => 1.0,
1288 p_init_msg_list => FND_API.G_FALSE,
1289 p_hold_entity_code => v_entity_code,
1290 p_hold_entity_id => v_entity_id,
1291 p_hold_id => 1,
1292 p_release_reason_code => 'AR_AUTOMATIC',
1293 p_action => v_action,
1294 x_return_status => l_return_status,
1295 x_msg_count => l_msg_count,
1296 x_msg_data => l_msg_data);
1297
1298 -- Debug info.
1299 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1300 hz_utility_v2pub.debug(p_message=>'After call OE_HOLDS... '||
1301 'l_return_status = '||l_return_status||' '||
1302 'l_msg_count = '||l_msg_count||' '||
1303 'l_msg_data = '||l_msg_data,
1304 p_prefix=>l_debug_prefix,
1305 p_msg_level=>fnd_log.level_statement);
1306 END IF;
1307 EXCEPTION
1308 WHEN OTHERS THEN
1309 -- Debug info.
1310 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1311 hz_utility_v2pub.debug(p_message=>'Exception raised from OE_HOLDS... '||SQLERRM,
1312 p_prefix=>l_debug_prefix,
1313 p_msg_level=>fnd_log.level_statement);
1314 END IF;
1315
1316 --Bug14059649
1317 --We should remove the code where they are suppressing the error being raise
1318 --by OM code. TCA team should always pass on the error message back to the user
1319 --/ UI / calling API, so that they will know that an error has occurred in OM
1320 -- l_return_status := 'S';
1321 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1322 END;
1323
1324 --
1325 -- only raise unexpected error
1326 --Bug14059649
1327 --Added FND_API.G_EXC_ERROR
1328 --We should remove the code where they are suppressing the error being raise
1329 --by OM code. TCA team should always pass on the error message back to the user
1330 --/ UI / calling API, so that they will know that an error has occurred in OM
1331 --
1332 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1333 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1334 ELSIF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1335 RAISE FND_API.G_EXC_ERROR;
1336 END IF;
1337 END IF;
1338 END IF; -- Bug 5606895
1339 -- Call table-handler.
1340 -- Table_handler is taking care of default customer profile to profile class.
1341
1342 -- Debug info.
1343 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1344 hz_utility_v2pub.debug(p_message=>'HZ_CUSTOMER_PROFILES_PKG.Update_Row (+)',
1345 p_prefix=>l_debug_prefix,
1346 p_msg_level=>fnd_log.level_procedure);
1347 END IF;
1348
1349 HZ_CUSTOMER_PROFILES_PKG.Update_Row (
1350 X_Rowid => l_rowid,
1351 X_CUST_ACCOUNT_PROFILE_ID => p_customer_profile_rec.cust_account_profile_id,
1352 X_CUST_ACCOUNT_ID => p_customer_profile_rec.cust_account_id,
1353 X_STATUS => p_customer_profile_rec.status,
1354 X_COLLECTOR_ID => p_customer_profile_rec.collector_id,
1355 X_CREDIT_ANALYST_ID => p_customer_profile_rec.credit_analyst_id,
1356 X_CREDIT_CHECKING => p_customer_profile_rec.credit_checking,
1357 X_NEXT_CREDIT_REVIEW_DATE => p_customer_profile_rec.next_credit_review_date,
1358 X_TOLERANCE => p_customer_profile_rec.tolerance,
1359 X_DISCOUNT_TERMS => p_customer_profile_rec.discount_terms,
1360 X_DUNNING_LETTERS => p_customer_profile_rec.dunning_letters,
1361 X_INTEREST_CHARGES => p_customer_profile_rec.interest_charges,
1362 X_SEND_STATEMENTS => p_customer_profile_rec.send_statements,
1363 X_CREDIT_BALANCE_STATEMENTS => p_customer_profile_rec.credit_balance_statements,
1364 X_CREDIT_HOLD => p_customer_profile_rec.credit_hold,
1365 X_PROFILE_CLASS_ID => p_customer_profile_rec.profile_class_id,
1366 X_SITE_USE_ID => p_customer_profile_rec.site_use_id,
1367 X_CREDIT_RATING => p_customer_profile_rec.credit_rating,
1368 X_RISK_CODE => p_customer_profile_rec.risk_code,
1369 X_STANDARD_TERMS => p_customer_profile_rec.standard_terms,
1370 X_OVERRIDE_TERMS => p_customer_profile_rec.override_terms,
1371 X_DUNNING_LETTER_SET_ID => p_customer_profile_rec.dunning_letter_set_id,
1372 X_INTEREST_PERIOD_DAYS => p_customer_profile_rec.interest_period_days,
1373 X_PAYMENT_GRACE_DAYS => p_customer_profile_rec.payment_grace_days,
1374 X_DISCOUNT_GRACE_DAYS => p_customer_profile_rec.discount_grace_days,
1375 X_STATEMENT_CYCLE_ID => p_customer_profile_rec.statement_cycle_id,
1376 X_ACCOUNT_STATUS => p_customer_profile_rec.account_status,
1377 X_PERCENT_COLLECTABLE => p_customer_profile_rec.percent_collectable,
1378 X_AUTOCASH_HIERARCHY_ID => p_customer_profile_rec.autocash_hierarchy_id,
1379 X_ATTRIBUTE_CATEGORY => p_customer_profile_rec.attribute_category,
1380 X_ATTRIBUTE1 => p_customer_profile_rec.attribute1,
1381 X_ATTRIBUTE2 => p_customer_profile_rec.attribute2,
1382 X_ATTRIBUTE3 => p_customer_profile_rec.attribute3,
1383 X_ATTRIBUTE4 => p_customer_profile_rec.attribute4,
1384 X_ATTRIBUTE5 => p_customer_profile_rec.attribute5,
1385 X_ATTRIBUTE6 => p_customer_profile_rec.attribute6,
1386 X_ATTRIBUTE7 => p_customer_profile_rec.attribute7,
1387 X_ATTRIBUTE8 => p_customer_profile_rec.attribute8,
1388 X_ATTRIBUTE9 => p_customer_profile_rec.attribute9,
1389 X_ATTRIBUTE10 => p_customer_profile_rec.attribute10,
1390 X_ATTRIBUTE11 => p_customer_profile_rec.attribute11,
1391 X_ATTRIBUTE12 => p_customer_profile_rec.attribute12,
1392 X_ATTRIBUTE13 => p_customer_profile_rec.attribute13,
1393 X_ATTRIBUTE14 => p_customer_profile_rec.attribute14,
1394 X_ATTRIBUTE15 => p_customer_profile_rec.attribute15,
1395 X_AUTO_REC_INCL_DISPUTED_FLAG => p_customer_profile_rec.auto_rec_incl_disputed_flag,
1396 X_TAX_PRINTING_OPTION => p_customer_profile_rec.tax_printing_option,
1397 X_CHARGE_ON_FINANCE_CHARGE_FG => p_customer_profile_rec.charge_on_finance_charge_flag,
1398 X_GROUPING_RULE_ID => p_customer_profile_rec.grouping_rule_id,
1399 X_CLEARING_DAYS => p_customer_profile_rec.clearing_days,
1400 X_JGZZ_ATTRIBUTE_CATEGORY => p_customer_profile_rec.jgzz_attribute_category,
1401 X_JGZZ_ATTRIBUTE1 => p_customer_profile_rec.jgzz_attribute1,
1402 X_JGZZ_ATTRIBUTE2 => p_customer_profile_rec.jgzz_attribute2,
1403 X_JGZZ_ATTRIBUTE3 => p_customer_profile_rec.jgzz_attribute3,
1404 X_JGZZ_ATTRIBUTE4 => p_customer_profile_rec.jgzz_attribute4,
1405 X_JGZZ_ATTRIBUTE5 => p_customer_profile_rec.jgzz_attribute5,
1406 X_JGZZ_ATTRIBUTE6 => p_customer_profile_rec.jgzz_attribute6,
1407 X_JGZZ_ATTRIBUTE7 => p_customer_profile_rec.jgzz_attribute7,
1408 X_JGZZ_ATTRIBUTE8 => p_customer_profile_rec.jgzz_attribute8,
1409 X_JGZZ_ATTRIBUTE9 => p_customer_profile_rec.jgzz_attribute9,
1410 X_JGZZ_ATTRIBUTE10 => p_customer_profile_rec.jgzz_attribute10,
1411 X_JGZZ_ATTRIBUTE11 => p_customer_profile_rec.jgzz_attribute11,
1412 X_JGZZ_ATTRIBUTE12 => p_customer_profile_rec.jgzz_attribute12,
1413 X_JGZZ_ATTRIBUTE13 => p_customer_profile_rec.jgzz_attribute13,
1414 X_JGZZ_ATTRIBUTE14 => p_customer_profile_rec.jgzz_attribute14,
1415 X_JGZZ_ATTRIBUTE15 => p_customer_profile_rec.jgzz_attribute15,
1416 X_GLOBAL_ATTRIBUTE1 => p_customer_profile_rec.global_attribute1,
1417 X_GLOBAL_ATTRIBUTE2 => p_customer_profile_rec.global_attribute2,
1418 X_GLOBAL_ATTRIBUTE3 => p_customer_profile_rec.global_attribute3,
1419 X_GLOBAL_ATTRIBUTE4 => p_customer_profile_rec.global_attribute4,
1420 X_GLOBAL_ATTRIBUTE5 => p_customer_profile_rec.global_attribute5,
1421 X_GLOBAL_ATTRIBUTE6 => p_customer_profile_rec.global_attribute6,
1422 X_GLOBAL_ATTRIBUTE7 => p_customer_profile_rec.global_attribute7,
1423 X_GLOBAL_ATTRIBUTE8 => p_customer_profile_rec.global_attribute8,
1424 X_GLOBAL_ATTRIBUTE9 => p_customer_profile_rec.global_attribute9,
1425 X_GLOBAL_ATTRIBUTE10 => p_customer_profile_rec.global_attribute10,
1426 X_GLOBAL_ATTRIBUTE11 => p_customer_profile_rec.global_attribute11,
1427 X_GLOBAL_ATTRIBUTE12 => p_customer_profile_rec.global_attribute12,
1428 X_GLOBAL_ATTRIBUTE13 => p_customer_profile_rec.global_attribute13,
1429 X_GLOBAL_ATTRIBUTE14 => p_customer_profile_rec.global_attribute14,
1430 X_GLOBAL_ATTRIBUTE15 => p_customer_profile_rec.global_attribute15,
1431 X_GLOBAL_ATTRIBUTE16 => p_customer_profile_rec.global_attribute16,
1432 X_GLOBAL_ATTRIBUTE17 => p_customer_profile_rec.global_attribute17,
1433 X_GLOBAL_ATTRIBUTE18 => p_customer_profile_rec.global_attribute18,
1434 X_GLOBAL_ATTRIBUTE19 => p_customer_profile_rec.global_attribute19,
1435 X_GLOBAL_ATTRIBUTE20 => p_customer_profile_rec.global_attribute20,
1436 X_GLOBAL_ATTRIBUTE_CATEGORY => p_customer_profile_rec.global_attribute_category,
1437 X_CONS_INV_FLAG => p_customer_profile_rec.cons_inv_flag,
1438 X_CONS_INV_TYPE => p_customer_profile_rec.cons_inv_type,
1439 X_AUTOCASH_HIERARCHY_ID_ADR => p_customer_profile_rec.autocash_hierarchy_id_for_adr,
1440 X_LOCKBOX_MATCHING_OPTION => p_customer_profile_rec.lockbox_matching_option,
1441 X_OBJECT_VERSION_NUMBER => p_object_version_number,
1442 X_CREATED_BY_MODULE => p_customer_profile_rec.created_by_module,
1443 X_APPLICATION_ID => p_customer_profile_rec.application_id,
1444 X_review_cycle => p_customer_profile_rec.review_cycle,
1445 X_last_credit_review_date => p_customer_profile_rec.last_credit_review_date,
1446 X_party_id => p_customer_profile_rec.party_id,
1447 X_CREDIT_CLASSIFICATION => p_customer_profile_rec.credit_classification,
1448 X_CONS_BILL_LEVEL => p_customer_profile_rec.cons_bill_level,
1449 X_LATE_CHARGE_CALCULATION_TRX => p_customer_profile_rec.late_charge_calculation_trx,
1450 X_CREDIT_ITEMS_FLAG => p_customer_profile_rec.credit_items_flag,
1451 X_DISPUTED_TRANSACTIONS_FLAG => p_customer_profile_rec.disputed_transactions_flag,
1452 X_LATE_CHARGE_TYPE => p_customer_profile_rec.late_charge_type,
1453 X_LATE_CHARGE_TERM_ID => p_customer_profile_rec.late_charge_term_id,
1454 X_INTEREST_CALCULATION_PERIOD => p_customer_profile_rec.interest_calculation_period,
1455 X_HOLD_CHARGED_INVOICES_FLAG => p_customer_profile_rec.hold_charged_invoices_flag,
1456 X_MESSAGE_TEXT_ID => p_customer_profile_rec.message_text_id,
1457 X_MULTIPLE_INTEREST_RATES_FLAG => p_customer_profile_rec.multiple_interest_rates_flag,
1458 X_CHARGE_BEGIN_DATE => p_customer_profile_rec.charge_begin_date,
1459 X_AUTOMATCH_SET_ID => p_customer_profile_rec.automatch_set_id
1460 );
1461
1462 --raji
1463
1464 -- Debug info.
1465 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1466 hz_utility_v2pub.debug(p_message=>'HZ_CUSTOMER_PROFILES_PKG.Update_Row (-)',
1467 p_prefix=>l_debug_prefix,
1468 p_msg_level=>fnd_log.level_procedure);
1469 END IF;
1470
1471 -- When the bill_level at the account profile is changed from 'ACCOUNT' to 'SITE',
1472 -- the bill_level for all the associated BFB enabled site uses should be
1473 -- updated to 'SITE' and the rest of the BFB attributes can be updated
1474 -- while updating the individual site use records.
1475 if (l_site_use_id is NULL and
1476 p_customer_profile_rec.cons_bill_level = 'SITE' and
1477 l_cons_bill_level = 'ACCOUNT') then
1478 begin
1479 update HZ_CUSTOMER_PROFILES
1480 set cons_bill_level = 'SITE'
1481 where site_use_id is NOT NULL
1482 and cons_inv_flag = 'Y'
1483 and cust_account_id = l_cust_account_id;
1484 exception when NO_DATA_FOUND then
1485 null;
1486 end;
1487 end if;
1488
1489 -- Update profile amt.
1490
1491 -- If profile_class_id has been changed, we have to update/copy the profile
1492 -- amount under new profile class. Please note, profile_class_id cannot be
1493 -- updated to null.
1494
1495 IF (p_customer_profile_rec.profile_class_id IS NOT NULL
1496 AND p_customer_profile_rec.profile_class_id <> l_profile_class_id )
1497 THEN
1498 BEGIN
1499
1500 -- could have several records in profile_class_amts for a given
1501 -- profile_class_id.
1502
1503 OPEN c_profile_class_amts;
1504 LOOP
1505 FETCH c_profile_class_amts INTO l_profile_class_amount_id;
1506 EXIT WHEN c_profile_class_amts%NOTFOUND;
1507
1508 -- Setup profile amount record if it is the first run.
1509 IF l_is_first THEN
1510 -- cust_account_id, site_use_id are non-updateable.
1511 -- Setup profile amount record
1512 l_cust_profile_amt_rec.cust_account_profile_id := p_customer_profile_rec.cust_account_profile_id;
1513 l_cust_profile_amt_rec.cust_account_id := l_cust_account_id;
1514 l_cust_profile_amt_rec.site_use_id := l_site_use_id;
1515
1516 l_is_first := FALSE;
1517 END IF;
1518
1519 -- Debug info.
1520 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1521 hz_utility_v2pub.debug(p_message=>'HZ_CUST_PROF_CLASS_AMTS_PKG.Select_Row (+)',
1522 p_prefix=>l_debug_prefix,
1523 p_msg_level=>fnd_log.level_procedure);
1524 END IF;
1525
1526 -- Call table-handler
1527 HZ_CUST_PROF_CLASS_AMTS_PKG.Select_Row (
1528 X_PROFILE_CLASS_AMOUNT_ID => l_profile_class_amount_id,
1529 X_PROFILE_CLASS_ID => l_profile_class_id,
1530 X_CURRENCY_CODE => l_cust_profile_amt_rec.currency_code,
1531 X_TRX_CREDIT_LIMIT => l_cust_profile_amt_rec.trx_credit_limit,
1532 X_OVERALL_CREDIT_LIMIT => l_cust_profile_amt_rec.overall_credit_limit,
1533 X_MIN_DUNNING_AMOUNT => l_cust_profile_amt_rec.min_dunning_amount,
1534 X_MAX_INTEREST_CHARGE => l_cust_profile_amt_rec.max_interest_charge,
1535 X_MIN_STATEMENT_AMOUNT => l_cust_profile_amt_rec.min_statement_amount,
1536 X_AUTO_REC_MIN_RECEIPT_AMOUNT => l_cust_profile_amt_rec.auto_rec_min_receipt_amount,
1537 X_ATTRIBUTE_CATEGORY => l_cust_profile_amt_rec.attribute_category,
1538 X_ATTRIBUTE1 => l_cust_profile_amt_rec.attribute1,
1539 X_ATTRIBUTE2 => l_cust_profile_amt_rec.attribute2,
1540 X_ATTRIBUTE3 => l_cust_profile_amt_rec.attribute3,
1541 X_ATTRIBUTE4 => l_cust_profile_amt_rec.attribute4,
1542 X_ATTRIBUTE5 => l_cust_profile_amt_rec.attribute5,
1543 X_ATTRIBUTE6 => l_cust_profile_amt_rec.attribute6,
1544 X_ATTRIBUTE7 => l_cust_profile_amt_rec.attribute7,
1545 X_ATTRIBUTE8 => l_cust_profile_amt_rec.attribute8,
1546 X_ATTRIBUTE9 => l_cust_profile_amt_rec.attribute9,
1547 X_ATTRIBUTE10 => l_cust_profile_amt_rec.attribute10,
1548 X_ATTRIBUTE11 => l_cust_profile_amt_rec.attribute11,
1549 X_ATTRIBUTE12 => l_cust_profile_amt_rec.attribute12,
1550 X_ATTRIBUTE13 => l_cust_profile_amt_rec.attribute13,
1551 X_ATTRIBUTE14 => l_cust_profile_amt_rec.attribute14,
1552 X_ATTRIBUTE15 => l_cust_profile_amt_rec.attribute15,
1553 X_INTEREST_RATE => l_cust_profile_amt_rec.interest_rate,
1554 X_MIN_FC_BALANCE_AMOUNT => l_cust_profile_amt_rec.min_fc_balance_amount,
1555 X_MIN_FC_INVOICE_AMOUNT => l_cust_profile_amt_rec.min_fc_invoice_amount,
1556 X_MIN_DUNNING_INVOICE_AMOUNT => l_cust_profile_amt_rec.min_dunning_invoice_amount,
1557 X_EXPIRATION_DATE => l_cust_profile_amt_rec.expiration_date,
1558 X_JGZZ_ATTRIBUTE_CATEGORY => l_cust_profile_amt_rec.jgzz_attribute_category,
1559 X_JGZZ_ATTRIBUTE1 => l_cust_profile_amt_rec.jgzz_attribute1,
1560 X_JGZZ_ATTRIBUTE2 => l_cust_profile_amt_rec.jgzz_attribute2,
1561 X_JGZZ_ATTRIBUTE3 => l_cust_profile_amt_rec.jgzz_attribute3,
1562 X_JGZZ_ATTRIBUTE4 => l_cust_profile_amt_rec.jgzz_attribute4,
1563 X_JGZZ_ATTRIBUTE5 => l_cust_profile_amt_rec.jgzz_attribute5,
1564 X_JGZZ_ATTRIBUTE6 => l_cust_profile_amt_rec.jgzz_attribute6,
1565 X_JGZZ_ATTRIBUTE7 => l_cust_profile_amt_rec.jgzz_attribute7,
1566 X_JGZZ_ATTRIBUTE8 => l_cust_profile_amt_rec.jgzz_attribute8,
1567 X_JGZZ_ATTRIBUTE9 => l_cust_profile_amt_rec.jgzz_attribute9,
1568 X_JGZZ_ATTRIBUTE10 => l_cust_profile_amt_rec.jgzz_attribute10,
1569 X_JGZZ_ATTRIBUTE11 => l_cust_profile_amt_rec.jgzz_attribute11,
1570 X_JGZZ_ATTRIBUTE12 => l_cust_profile_amt_rec.jgzz_attribute12,
1571 X_JGZZ_ATTRIBUTE13 => l_cust_profile_amt_rec.jgzz_attribute13,
1572 X_JGZZ_ATTRIBUTE14 => l_cust_profile_amt_rec.jgzz_attribute14,
1573 X_JGZZ_ATTRIBUTE15 => l_cust_profile_amt_rec.jgzz_attribute15,
1574 X_GLOBAL_ATTRIBUTE1 => l_cust_profile_amt_rec.global_attribute1,
1575 X_GLOBAL_ATTRIBUTE2 => l_cust_profile_amt_rec.global_attribute2,
1576 X_GLOBAL_ATTRIBUTE3 => l_cust_profile_amt_rec.global_attribute3,
1577 X_GLOBAL_ATTRIBUTE4 => l_cust_profile_amt_rec.global_attribute4,
1578 X_GLOBAL_ATTRIBUTE5 => l_cust_profile_amt_rec.global_attribute5,
1579 X_GLOBAL_ATTRIBUTE6 => l_cust_profile_amt_rec.global_attribute6,
1580 X_GLOBAL_ATTRIBUTE7 => l_cust_profile_amt_rec.global_attribute7,
1581 X_GLOBAL_ATTRIBUTE8 => l_cust_profile_amt_rec.global_attribute8,
1582 X_GLOBAL_ATTRIBUTE9 => l_cust_profile_amt_rec.global_attribute9,
1583 X_GLOBAL_ATTRIBUTE10 => l_cust_profile_amt_rec.global_attribute10,
1584 X_GLOBAL_ATTRIBUTE11 => l_cust_profile_amt_rec.global_attribute11,
1585 X_GLOBAL_ATTRIBUTE12 => l_cust_profile_amt_rec.global_attribute12,
1586 X_GLOBAL_ATTRIBUTE13 => l_cust_profile_amt_rec.global_attribute13,
1587 X_GLOBAL_ATTRIBUTE14 => l_cust_profile_amt_rec.global_attribute14,
1588 X_GLOBAL_ATTRIBUTE15 => l_cust_profile_amt_rec.global_attribute15,
1589 X_GLOBAL_ATTRIBUTE16 => l_cust_profile_amt_rec.global_attribute16,
1590 X_GLOBAL_ATTRIBUTE17 => l_cust_profile_amt_rec.global_attribute17,
1591 X_GLOBAL_ATTRIBUTE18 => l_cust_profile_amt_rec.global_attribute18,
1592 X_GLOBAL_ATTRIBUTE19 => l_cust_profile_amt_rec.global_attribute19,
1593 X_GLOBAL_ATTRIBUTE20 => l_cust_profile_amt_rec.global_attribute20,
1594 X_GLOBAL_ATTRIBUTE_CATEGORY => l_cust_profile_amt_rec.global_attribute_category,
1595 X_EXCHANGE_RATE_TYPE => l_cust_profile_amt_rec.exchange_rate_type,
1596 X_MIN_FC_INVOICE_OVERDUE_TYPE => l_cust_profile_amt_rec.min_fc_invoice_overdue_type,
1597 X_MIN_FC_INVOICE_PERCENT => l_cust_profile_amt_rec.min_fc_invoice_percent,
1598 X_MIN_FC_BALANCE_OVERDUE_TYPE => l_cust_profile_amt_rec.min_fc_balance_overdue_type,
1599 X_MIN_FC_BALANCE_PERCENT => l_cust_profile_amt_rec.min_fc_balance_percent,
1600 X_INTEREST_TYPE => l_cust_profile_amt_rec.interest_type,
1601 X_INTEREST_FIXED_AMOUNT => l_cust_profile_amt_rec.interest_fixed_amount,
1602 X_INTEREST_SCHEDULE_ID => l_cust_profile_amt_rec.interest_schedule_id,
1603 X_PENALTY_TYPE => l_cust_profile_amt_rec.penalty_type,
1604 X_PENALTY_RATE => l_cust_profile_amt_rec.penalty_rate,
1605 X_MIN_INTEREST_CHARGE => l_cust_profile_amt_rec.min_interest_charge,
1606 X_PENALTY_FIXED_AMOUNT => l_cust_profile_amt_rec.penalty_fixed_amount,
1607 X_PENALTY_SCHEDULE_ID => l_cust_profile_amt_rec.penalty_schedule_id
1608 );
1609
1610 -- Debug info.
1611 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1612 hz_utility_v2pub.debug(p_message=>'HZ_CUST_PROF_CLASS_AMTS_PKG.Select_Row (-)',
1613 p_prefix=>l_debug_prefix,
1614 p_msg_level=>fnd_log.level_procedure);
1615 END IF;
1616
1617 -- Check if a currency code already exist for this profile.
1618 -- If yes, update that amount. If not, create a new amount.
1619
1620 BEGIN
1621 SELECT CUST_ACCT_PROFILE_AMT_ID, OBJECT_VERSION_NUMBER
1622 INTO l_cust_acct_profile_amt_id, l_amt_object_version_number
1623 FROM HZ_CUST_PROFILE_AMTS
1624 WHERE CUST_ACCOUNT_PROFILE_ID = p_customer_profile_rec.cust_account_profile_id
1625 AND CURRENCY_CODE = l_cust_profile_amt_rec.currency_code;
1626
1627 -- The currency exist for the customer profile. Update it.
1628 l_cust_profile_amt_rec.cust_acct_profile_amt_id := l_cust_acct_profile_amt_id;
1629
1630 -- Call public API to update cust profile amount.
1631 update_cust_profile_amt (
1632 p_cust_profile_amt_rec => l_cust_profile_amt_rec,
1633 p_object_version_number => l_amt_object_version_number,
1634 x_return_status => x_return_status,
1635 x_msg_count => l_msg_count,
1636 x_msg_data => l_msg_data );
1637
1638 hz_credit_usages_cascade_pkg.delete_credit_usages(
1639 l_cust_profile_amt_rec.cust_acct_profile_amt_id
1640 , x_return_status
1641 , l_msg_count
1642 , l_msg_data);
1643
1644 EXCEPTION
1645 WHEN NO_DATA_FOUND THEN
1646 -- The currency does not exist, create new amount
1647 l_cust_profile_amt_rec.cust_acct_profile_amt_id := NULL;
1648
1649 IF p_customer_profile_rec.created_by_module IS NOT NULL
1650 and p_customer_profile_rec.created_by_module <> fnd_api.g_miss_char THEN
1651 l_cust_profile_amt_rec.created_by_module := p_customer_profile_rec.created_by_module;
1652 ELSE
1653 -- if l_created_by_module is null, default to TCA_V2_API
1654 l_cust_profile_amt_rec.created_by_module := nvl(l_created_by_module, 'TCA_V2_API');
1655 END IF;
1656
1657 IF p_customer_profile_rec.application_id IS NOT NULL THEN
1658 l_cust_profile_amt_rec.application_id := p_customer_profile_rec.application_id;
1659 ELSE
1660 l_cust_profile_amt_rec.application_id := l_application_id;
1661 END IF;
1662
1663 -- Call public API to create cust profile amount.
1664 create_cust_profile_amt (
1665 p_cust_profile_amt_rec => l_cust_profile_amt_rec,
1666 x_cust_acct_profile_amt_id => l_cust_profile_amt_rec.cust_acct_profile_amt_id,
1667 x_return_status => x_return_status,
1668 x_msg_count => l_msg_count,
1669 x_msg_data => l_msg_data );
1670 END;
1671
1672 IF x_return_status = 'S' THEN
1673 HZ_CREDIT_USAGES_CASCADE_PKG.cascade_credit_usage_rules (
1674 l_cust_profile_amt_rec.cust_acct_profile_amt_id,
1675 l_cust_profile_amt_rec.cust_account_profile_id,
1676 l_profile_class_amount_id,
1677 l_profile_class_id,
1678 x_return_status,
1679 l_msg_count,
1680 l_msg_data );
1681 END IF;
1682
1683
1684 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1685 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1686 RAISE FND_API.G_EXC_ERROR;
1687 ELSE
1688 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1689 END IF;
1690 END IF;
1691
1692 END LOOP;
1693 CLOSE c_profile_class_amts;
1694
1695 EXCEPTION
1696 WHEN OTHERS THEN
1697 IF c_profile_class_amts%ISOPEN THEN
1698 CLOSE c_profile_class_amts;
1699 END IF;
1700
1701 RAISE;
1702 END;
1703 END IF;
1704
1705 -- Debug info.
1706 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1707 hz_utility_v2pub.debug(p_message=>'do_update_customer_profile (-)',
1708 p_prefix=>l_debug_prefix,
1709 p_msg_level=>fnd_log.level_procedure);
1710 END IF;
1711
1712 END do_update_customer_profile;
1713
1714 /**
1715 * PRIVATE PROCEDURE do_create_cust_profile_amt
1716 *
1717 * DESCRIPTION
1718 * Private procedure to create customer profile amount.
1719 *
1720 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1721 * HZ_ACCOUNT_VALIDATE_V2PUB.validate_cust_profile_amt
1722 * HZ_CUST_PROFILE_AMTS_PKG.Insert_Row
1723 *
1724 * ARGUMENTS
1725 * IN:
1726 * p_check_foreign_key If do foreign key checking on cust_account_id
1727 * and cust_account_profile_id or not. Defaut value
1728 * is FND_API.G_TRUE, which means API will do foreign
1729 * key checking on these 2 columns.
1730 * IN/OUT:
1731 * p_cust_profile_amt_rec Customer profile amount record.
1732 * x_return_status Return status after the call. The status can
1733 * be FND_API.G_RET_STS_SUCCESS (success),
1734 * FND_API.G_RET_STS_ERROR (error),
1735 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1736 * OUT:
1737 * x_cust_acct_profile_amt_id Customer account profile amount ID.
1738 *
1739 * NOTES
1740 *
1741 * MODIFICATION HISTORY
1742 *
1743 * 07-23-2001 Jianying Huang o Created.
1744 *
1745 */
1746
1747 PROCEDURE do_create_cust_profile_amt (
1748 p_check_foreign_key IN VARCHAR2,
1749 p_cust_profile_amt_rec IN OUT NOCOPY CUST_PROFILE_AMT_REC_TYPE,
1750 x_cust_acct_profile_amt_id OUT NOCOPY NUMBER,
1751 x_return_status IN OUT NOCOPY VARCHAR2
1752 ) IS
1753
1754 l_debug_prefix VARCHAR2(30) := ''; --'do_create_cust_profile_amt'
1755 l_site_use_id HZ_CUST_PROFILE_AMTS.SITE_USE_ID%TYPE;
1756 BEGIN
1757
1758 -- Debug info.
1759 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1760 hz_utility_v2pub.debug(p_message=>'do_create_cust_profile_amt (+)' ,
1761 p_prefix=>l_debug_prefix,
1762 p_msg_level=>fnd_log.level_procedure);
1763 END IF;
1764
1765 -- Validate cust profile amt record
1766 HZ_ACCOUNT_VALIDATE_V2PUB.validate_cust_profile_amt (
1767 p_create_update_flag => 'C',
1768 p_check_foreign_key => p_check_foreign_key,
1769 p_cust_profile_amt_rec => p_cust_profile_amt_rec,
1770 p_rowid => NULL,
1771 x_return_status => x_return_status );
1772
1773 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1774 RAISE FND_API.G_EXC_ERROR;
1775 END IF;
1776
1777 -- Added rounding of min_fc_invoice_amount, min_fc_balance_amount, min_interest_charge,
1778 -- min_interest_charge, interest_fixed_amount and penalty_fixed_amount to 2 decimal places for Late charge project.
1779 IF p_cust_profile_amt_rec.min_fc_invoice_amount is NOT NULL then
1780 p_cust_profile_amt_rec.min_fc_invoice_amount := ROUND(p_cust_profile_amt_rec.min_fc_invoice_amount,2);
1781 END IF;
1782 IF p_cust_profile_amt_rec.min_fc_balance_amount is NOT NULL then
1783 p_cust_profile_amt_rec.min_fc_balance_amount := ROUND(p_cust_profile_amt_rec.min_fc_balance_amount,2);
1784 END IF;
1785 IF p_cust_profile_amt_rec.max_interest_charge is NOT NULL then
1786 p_cust_profile_amt_rec.max_interest_charge := ROUND(p_cust_profile_amt_rec.max_interest_charge,2);
1787 END IF;
1788 IF p_cust_profile_amt_rec.min_interest_charge is NOT NULL then
1789 p_cust_profile_amt_rec.min_interest_charge := ROUND(p_cust_profile_amt_rec.min_interest_charge,2);
1790 END IF;
1791 IF p_cust_profile_amt_rec.interest_fixed_amount is NOT NULL then
1792 p_cust_profile_amt_rec.interest_fixed_amount := ROUND(p_cust_profile_amt_rec.interest_fixed_amount,2);
1793 END IF;
1794 IF p_cust_profile_amt_rec.penalty_fixed_amount is NOT NULL then
1795 p_cust_profile_amt_rec.penalty_fixed_amount := ROUND(p_cust_profile_amt_rec.penalty_fixed_amount,2);
1796 END IF;
1797
1798 -- Debug info.
1799 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1800 hz_utility_v2pub.debug(p_message=>'HZ_CUST_PROFILE_AMTS_PKG.Insert_Row (+)',
1801 p_prefix=>l_debug_prefix,
1802 p_msg_level=>fnd_log.level_procedure);
1803 END IF;
1804
1805 -- Bug 6472676
1806 -- Changed do_create_cust_profile_amt Procudure to get SITE_USE_ID value from Customer Profiles if
1807 -- Parameter CUST_PROFILE_AMT_REC containing null value in SITE_USE_ID.
1808
1809 IF p_cust_profile_amt_rec.site_use_id IS NULL OR p_CUST_PROFILE_AMT_REC.site_use_id = FND_API.G_MISS_NUM THEN
1810 BEGIN
1811 SELECT site_use_id
1812 INTO l_site_use_id
1813 FROM hz_customer_profiles
1814 WHERE cust_account_id = p_cust_profile_amt_rec.cust_account_id
1815 AND cust_account_profile_id = p_cust_profile_amt_rec.cust_account_profile_id ;
1816
1817 p_cust_profile_amt_rec.site_use_id := l_site_use_id ;
1818
1819 EXCEPTION
1820 WHEN OTHERS THEN
1821 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1822 END;
1823 END IF;
1824
1825 -- Call table-handler.
1826 HZ_CUST_PROFILE_AMTS_PKG.Insert_Row (
1827 X_CUST_ACCT_PROFILE_AMT_ID => p_cust_profile_amt_rec.cust_acct_profile_amt_id,
1828 X_CUST_ACCOUNT_PROFILE_ID => p_cust_profile_amt_rec.cust_account_profile_id,
1829 X_CURRENCY_CODE => p_cust_profile_amt_rec.currency_code,
1830 X_TRX_CREDIT_LIMIT => p_cust_profile_amt_rec.trx_credit_limit,
1831 X_OVERALL_CREDIT_LIMIT => p_cust_profile_amt_rec.overall_credit_limit,
1832 X_MIN_DUNNING_AMOUNT => p_cust_profile_amt_rec.min_dunning_amount,
1833 X_MIN_DUNNING_INVOICE_AMOUNT => p_cust_profile_amt_rec.min_dunning_invoice_amount,
1834 X_MAX_INTEREST_CHARGE => p_cust_profile_amt_rec.max_interest_charge,
1835 X_MIN_STATEMENT_AMOUNT => p_cust_profile_amt_rec.min_statement_amount,
1836 X_AUTO_REC_MIN_RECEIPT_AMOUNT => p_cust_profile_amt_rec.auto_rec_min_receipt_amount,
1837 X_INTEREST_RATE => p_cust_profile_amt_rec.interest_rate,
1838 X_ATTRIBUTE_CATEGORY => p_cust_profile_amt_rec.attribute_category,
1839 X_ATTRIBUTE1 => p_cust_profile_amt_rec.attribute1,
1840 X_ATTRIBUTE2 => p_cust_profile_amt_rec.attribute2,
1841 X_ATTRIBUTE3 => p_cust_profile_amt_rec.attribute3,
1842 X_ATTRIBUTE4 => p_cust_profile_amt_rec.attribute4,
1843 X_ATTRIBUTE5 => p_cust_profile_amt_rec.attribute5,
1844 X_ATTRIBUTE6 => p_cust_profile_amt_rec.attribute6,
1845 X_ATTRIBUTE7 => p_cust_profile_amt_rec.attribute7,
1846 X_ATTRIBUTE8 => p_cust_profile_amt_rec.attribute8,
1847 X_ATTRIBUTE9 => p_cust_profile_amt_rec.attribute9,
1848 X_ATTRIBUTE10 => p_cust_profile_amt_rec.attribute10,
1849 X_ATTRIBUTE11 => p_cust_profile_amt_rec.attribute11,
1850 X_ATTRIBUTE12 => p_cust_profile_amt_rec.attribute12,
1851 X_ATTRIBUTE13 => p_cust_profile_amt_rec.attribute13,
1852 X_ATTRIBUTE14 => p_cust_profile_amt_rec.attribute14,
1853 X_ATTRIBUTE15 => p_cust_profile_amt_rec.attribute15,
1854 X_MIN_FC_BALANCE_AMOUNT => p_cust_profile_amt_rec.min_fc_balance_amount,
1855 X_MIN_FC_INVOICE_AMOUNT => p_cust_profile_amt_rec.min_fc_invoice_amount,
1856 X_CUST_ACCOUNT_ID => p_cust_profile_amt_rec.cust_account_id,
1857 X_SITE_USE_ID => p_cust_profile_amt_rec.site_use_id,
1858 X_EXPIRATION_DATE => p_cust_profile_amt_rec.expiration_date,
1859 X_JGZZ_ATTRIBUTE_CATEGORY => p_cust_profile_amt_rec.jgzz_attribute_category,
1860 X_JGZZ_ATTRIBUTE1 => p_cust_profile_amt_rec.jgzz_attribute1,
1861 X_JGZZ_ATTRIBUTE2 => p_cust_profile_amt_rec.jgzz_attribute2,
1862 X_JGZZ_ATTRIBUTE3 => p_cust_profile_amt_rec.jgzz_attribute3,
1863 X_JGZZ_ATTRIBUTE4 => p_cust_profile_amt_rec.jgzz_attribute4,
1864 X_JGZZ_ATTRIBUTE5 => p_cust_profile_amt_rec.jgzz_attribute5,
1865 X_JGZZ_ATTRIBUTE6 => p_cust_profile_amt_rec.jgzz_attribute6,
1866 X_JGZZ_ATTRIBUTE7 => p_cust_profile_amt_rec.jgzz_attribute7,
1867 X_JGZZ_ATTRIBUTE8 => p_cust_profile_amt_rec.jgzz_attribute8,
1868 X_JGZZ_ATTRIBUTE9 => p_cust_profile_amt_rec.jgzz_attribute9,
1869 X_JGZZ_ATTRIBUTE10 => p_cust_profile_amt_rec.jgzz_attribute10,
1870 X_JGZZ_ATTRIBUTE11 => p_cust_profile_amt_rec.jgzz_attribute11,
1871 X_JGZZ_ATTRIBUTE12 => p_cust_profile_amt_rec.jgzz_attribute12,
1872 X_JGZZ_ATTRIBUTE13 => p_cust_profile_amt_rec.jgzz_attribute13,
1873 X_JGZZ_ATTRIBUTE14 => p_cust_profile_amt_rec.jgzz_attribute14,
1874 X_JGZZ_ATTRIBUTE15 => p_cust_profile_amt_rec.jgzz_attribute15,
1875 X_GLOBAL_ATTRIBUTE1 => p_cust_profile_amt_rec.global_attribute1,
1876 X_GLOBAL_ATTRIBUTE2 => p_cust_profile_amt_rec.global_attribute2,
1877 X_GLOBAL_ATTRIBUTE3 => p_cust_profile_amt_rec.global_attribute3,
1878 X_GLOBAL_ATTRIBUTE4 => p_cust_profile_amt_rec.global_attribute4,
1879 X_GLOBAL_ATTRIBUTE5 => p_cust_profile_amt_rec.global_attribute5,
1880 X_GLOBAL_ATTRIBUTE6 => p_cust_profile_amt_rec.global_attribute6,
1881 X_GLOBAL_ATTRIBUTE7 => p_cust_profile_amt_rec.global_attribute7,
1882 X_GLOBAL_ATTRIBUTE8 => p_cust_profile_amt_rec.global_attribute8,
1883 X_GLOBAL_ATTRIBUTE9 => p_cust_profile_amt_rec.global_attribute9,
1884 X_GLOBAL_ATTRIBUTE10 => p_cust_profile_amt_rec.global_attribute10,
1885 X_GLOBAL_ATTRIBUTE11 => p_cust_profile_amt_rec.global_attribute11,
1886 X_GLOBAL_ATTRIBUTE12 => p_cust_profile_amt_rec.global_attribute12,
1887 X_GLOBAL_ATTRIBUTE13 => p_cust_profile_amt_rec.global_attribute13,
1888 X_GLOBAL_ATTRIBUTE14 => p_cust_profile_amt_rec.global_attribute14,
1889 X_GLOBAL_ATTRIBUTE15 => p_cust_profile_amt_rec.global_attribute15,
1890 X_GLOBAL_ATTRIBUTE16 => p_cust_profile_amt_rec.global_attribute16,
1891 X_GLOBAL_ATTRIBUTE17 => p_cust_profile_amt_rec.global_attribute17,
1892 X_GLOBAL_ATTRIBUTE18 => p_cust_profile_amt_rec.global_attribute18,
1893 X_GLOBAL_ATTRIBUTE19 => p_cust_profile_amt_rec.global_attribute19,
1894 X_GLOBAL_ATTRIBUTE20 => p_cust_profile_amt_rec.global_attribute20,
1895 X_GLOBAL_ATTRIBUTE_CATEGORY => p_cust_profile_amt_rec.global_attribute_category,
1896 X_OBJECT_VERSION_NUMBER => 1,
1897 X_CREATED_BY_MODULE => p_cust_profile_amt_rec.created_by_module,
1898 X_APPLICATION_ID => p_cust_profile_amt_rec.application_id,
1899 X_EXCHANGE_RATE_TYPE => p_cust_profile_amt_rec.exchange_rate_type,
1900 X_MIN_FC_INVOICE_OVERDUE_TYPE => p_cust_profile_amt_rec.min_fc_invoice_overdue_type,
1901 X_MIN_FC_INVOICE_PERCENT => p_cust_profile_amt_rec.min_fc_invoice_percent,
1902 X_MIN_FC_BALANCE_OVERDUE_TYPE => p_cust_profile_amt_rec.min_fc_balance_overdue_type,
1903 X_MIN_FC_BALANCE_PERCENT => p_cust_profile_amt_rec.min_fc_balance_percent,
1904 X_INTEREST_TYPE => p_cust_profile_amt_rec.interest_type,
1905 X_INTEREST_FIXED_AMOUNT => p_cust_profile_amt_rec.interest_fixed_amount,
1906 X_INTEREST_SCHEDULE_ID => p_cust_profile_amt_rec.interest_schedule_id,
1907 X_PENALTY_TYPE => p_cust_profile_amt_rec.penalty_type,
1908 X_PENALTY_RATE => p_cust_profile_amt_rec.penalty_rate,
1909 X_MIN_INTEREST_CHARGE => p_cust_profile_amt_rec.min_interest_charge,
1910 X_PENALTY_FIXED_AMOUNT => p_cust_profile_amt_rec.penalty_fixed_amount,
1911 X_PENALTY_SCHEDULE_ID => p_cust_profile_amt_rec.penalty_schedule_id
1912 );
1913
1914 x_cust_acct_profile_amt_id := p_cust_profile_amt_rec.cust_acct_profile_amt_id;
1915
1916 -- Debug info.
1917 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1918 hz_utility_v2pub.debug(p_message=>'HZ_CUST_PROFILE_AMTS_PKG.Insert_Row (-) ' ||
1919 'x_cust_acct_profile_amt_id = ' || x_cust_acct_profile_amt_id,
1920 p_prefix=>l_debug_prefix,
1921 p_msg_level=>fnd_log.level_procedure);
1922 END IF;
1923
1924 -- Debug info.
1925 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1926 hz_utility_v2pub.debug(p_message=>'do_create_cust_profile_amt (-)',
1927 p_prefix=>l_debug_prefix,
1928 p_msg_level=>fnd_log.level_procedure);
1929 END IF;
1930
1931 END do_create_cust_profile_amt;
1932
1933 /**
1934 * PRIVATE PROCEDURE do_update_cust_profile_amt
1935 *
1936 * DESCRIPTION
1937 * Private procedure to update customer profile amount.
1938 *
1939 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1940 * HZ_ACCOUNT_VALIDATE_V2PUB.validate_cust_profile_amt
1941 * HZ_CUST_PROFILE_AMTS_PKG.Update_Row
1942 *
1943 * ARGUMENTS
1944 * IN/OUT:
1945 * p_cust_profile_amt_rec Customer profile amount record.
1946 * p_object_version_number Used for locking the being updated record.
1947 * x_return_status Return status after the call. The status can
1948 * be FND_API.G_RET_STS_SUCCESS (success),
1949 * FND_API.G_RET_STS_ERROR (error),
1950 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1951 *
1952 * NOTES
1953 *
1954 * MODIFICATION HISTORY
1955 *
1956 * 07-23-2001 Jianying Huang o Created.
1957 *
1958 */
1959
1960 PROCEDURE do_update_cust_profile_amt (
1961 p_cust_profile_amt_rec IN OUT NOCOPY CUST_PROFILE_AMT_REC_TYPE,
1962 p_object_version_number IN OUT NOCOPY NUMBER,
1963 x_return_status IN OUT NOCOPY VARCHAR2
1964 ) IS
1965
1966 l_debug_prefix VARCHAR2(30) := ''; --'do_update_cust_profile_amt';
1967
1968 l_rowid ROWID := NULL;
1969 l_object_version_number NUMBER;
1970
1971 BEGIN
1972
1973 -- Debug info.
1974 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1975 hz_utility_v2pub.debug(p_message=>'do_update_cust_profile_amt (+)',
1976 p_prefix=>l_debug_prefix,
1977 p_msg_level=>fnd_log.level_procedure);
1978 END IF;
1979
1980 -- Lock record.
1981 BEGIN
1982 SELECT ROWID, OBJECT_VERSION_NUMBER
1983 INTO l_rowid, l_object_version_number
1984 FROM HZ_CUST_PROFILE_AMTS
1985 WHERE CUST_ACCT_PROFILE_AMT_ID = p_cust_profile_amt_rec.cust_acct_profile_amt_id
1986 FOR UPDATE NOWAIT;
1987
1988 IF NOT (
1989 ( p_object_version_number IS NULL AND l_object_version_number IS NULL ) OR
1990 ( p_object_version_number IS NOT NULL AND
1991 l_object_version_number IS NOT NULL AND
1992 p_object_version_number = l_object_version_number ) )
1993 THEN
1994 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_RECORD_CHANGED' );
1995 FND_MESSAGE.SET_TOKEN( 'TABLE', 'hz_cust_profile_amts' );
1996 FND_MSG_PUB.ADD;
1997 RAISE FND_API.G_EXC_ERROR;
1998 END IF;
1999
2000 p_object_version_number := NVL( l_object_version_number, 1 ) + 1;
2001
2002 EXCEPTION
2003 WHEN NO_DATA_FOUND THEN
2004 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_RECORD' );
2005 FND_MESSAGE.SET_TOKEN( 'RECORD', 'customer account profile amount' );
2006 FND_MESSAGE.SET_TOKEN( 'VALUE',
2007 NVL( TO_CHAR( p_cust_profile_amt_rec.cust_acct_profile_amt_id ), 'null' ) );
2008 FND_MSG_PUB.ADD;
2009 RAISE FND_API.G_EXC_ERROR;
2010 END;
2011
2012 -- Validate cust profile amt record
2013 HZ_ACCOUNT_VALIDATE_V2PUB.validate_cust_profile_amt (
2014 p_create_update_flag => 'U',
2015 p_check_foreign_key => NULL,
2016 p_cust_profile_amt_rec => p_cust_profile_amt_rec,
2017 p_rowid => l_rowid,
2018 x_return_status => x_return_status );
2019
2020 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2021 RAISE FND_API.G_EXC_ERROR;
2022 END IF;
2023
2024 -- Added rounding of min_fc_invoice_amount, min_fc_balance_amount, min_interest_charge,
2025 -- min_interest_charge, interest_fixed_amount and penalty_fixed_amount to 2 decimal places for Late charge project.
2026 IF p_cust_profile_amt_rec.min_fc_invoice_amount is NOT NULL then
2027 p_cust_profile_amt_rec.min_fc_invoice_amount := ROUND(p_cust_profile_amt_rec.min_fc_invoice_amount,2);
2028 END IF;
2029 IF p_cust_profile_amt_rec.min_fc_balance_amount is NOT NULL then
2030 p_cust_profile_amt_rec.min_fc_balance_amount := ROUND(p_cust_profile_amt_rec.min_fc_balance_amount,2);
2031 END IF;
2032 IF p_cust_profile_amt_rec.max_interest_charge is NOT NULL then
2033 p_cust_profile_amt_rec.max_interest_charge := ROUND(p_cust_profile_amt_rec.max_interest_charge,2);
2034 END IF;
2035 IF p_cust_profile_amt_rec.min_interest_charge is NOT NULL then
2036 p_cust_profile_amt_rec.min_interest_charge := ROUND(p_cust_profile_amt_rec.min_interest_charge,2);
2037 END IF;
2038 IF p_cust_profile_amt_rec.interest_fixed_amount is NOT NULL then
2039 p_cust_profile_amt_rec.interest_fixed_amount := ROUND(p_cust_profile_amt_rec.interest_fixed_amount,2);
2040 END IF;
2041 IF p_cust_profile_amt_rec.penalty_fixed_amount is NOT NULL then
2042 p_cust_profile_amt_rec.penalty_fixed_amount := ROUND(p_cust_profile_amt_rec.penalty_fixed_amount,2);
2043 END IF;
2044
2045 -- Debug info.
2046 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2047 hz_utility_v2pub.debug(p_message=>'HZ_CUST_PROFILE_AMTS_PKG.Update_Row (+)',
2048 p_prefix=>l_debug_prefix,
2049 p_msg_level=>fnd_log.level_procedure);
2050 END IF;
2051
2052 -- Call table-handler.
2053 HZ_CUST_PROFILE_AMTS_PKG.Update_Row (
2054 X_Rowid => l_rowid,
2055 X_CUST_ACCT_PROFILE_AMT_ID => p_cust_profile_amt_rec.cust_acct_profile_amt_id,
2056 X_CUST_ACCOUNT_PROFILE_ID => p_cust_profile_amt_rec.cust_account_profile_id,
2057 X_CURRENCY_CODE => p_cust_profile_amt_rec.currency_code,
2058 X_TRX_CREDIT_LIMIT => p_cust_profile_amt_rec.trx_credit_limit,
2059 X_OVERALL_CREDIT_LIMIT => p_cust_profile_amt_rec.overall_credit_limit,
2060 X_MIN_DUNNING_AMOUNT => p_cust_profile_amt_rec.min_dunning_amount,
2061 X_MIN_DUNNING_INVOICE_AMOUNT => p_cust_profile_amt_rec.min_dunning_invoice_amount,
2062 X_MAX_INTEREST_CHARGE => p_cust_profile_amt_rec.max_interest_charge,
2063 X_MIN_STATEMENT_AMOUNT => p_cust_profile_amt_rec.min_statement_amount,
2064 X_AUTO_REC_MIN_RECEIPT_AMOUNT => p_cust_profile_amt_rec.auto_rec_min_receipt_amount,
2065 X_INTEREST_RATE => p_cust_profile_amt_rec.interest_rate,
2066 X_ATTRIBUTE_CATEGORY => p_cust_profile_amt_rec.attribute_category,
2067 X_ATTRIBUTE1 => p_cust_profile_amt_rec.attribute1,
2068 X_ATTRIBUTE2 => p_cust_profile_amt_rec.attribute2,
2069 X_ATTRIBUTE3 => p_cust_profile_amt_rec.attribute3,
2070 X_ATTRIBUTE4 => p_cust_profile_amt_rec.attribute4,
2071 X_ATTRIBUTE5 => p_cust_profile_amt_rec.attribute5,
2072 X_ATTRIBUTE6 => p_cust_profile_amt_rec.attribute6,
2073 X_ATTRIBUTE7 => p_cust_profile_amt_rec.attribute7,
2074 X_ATTRIBUTE8 => p_cust_profile_amt_rec.attribute8,
2075 X_ATTRIBUTE9 => p_cust_profile_amt_rec.attribute9,
2076 X_ATTRIBUTE10 => p_cust_profile_amt_rec.attribute10,
2077 X_ATTRIBUTE11 => p_cust_profile_amt_rec.attribute11,
2078 X_ATTRIBUTE12 => p_cust_profile_amt_rec.attribute12,
2079 X_ATTRIBUTE13 => p_cust_profile_amt_rec.attribute13,
2080 X_ATTRIBUTE14 => p_cust_profile_amt_rec.attribute14,
2081 X_ATTRIBUTE15 => p_cust_profile_amt_rec.attribute15,
2082 X_MIN_FC_BALANCE_AMOUNT => p_cust_profile_amt_rec.min_fc_balance_amount,
2083 X_MIN_FC_INVOICE_AMOUNT => p_cust_profile_amt_rec.min_fc_invoice_amount,
2084 X_CUST_ACCOUNT_ID => p_cust_profile_amt_rec.cust_account_id,
2085 X_SITE_USE_ID => p_cust_profile_amt_rec.site_use_id,
2086 X_EXPIRATION_DATE => p_cust_profile_amt_rec.expiration_date,
2087 X_JGZZ_ATTRIBUTE_CATEGORY => p_cust_profile_amt_rec.jgzz_attribute_category,
2088 X_JGZZ_ATTRIBUTE1 => p_cust_profile_amt_rec.jgzz_attribute1,
2089 X_JGZZ_ATTRIBUTE2 => p_cust_profile_amt_rec.jgzz_attribute2,
2090 X_JGZZ_ATTRIBUTE3 => p_cust_profile_amt_rec.jgzz_attribute3,
2091 X_JGZZ_ATTRIBUTE4 => p_cust_profile_amt_rec.jgzz_attribute4,
2092 X_JGZZ_ATTRIBUTE5 => p_cust_profile_amt_rec.jgzz_attribute5,
2093 X_JGZZ_ATTRIBUTE6 => p_cust_profile_amt_rec.jgzz_attribute6,
2094 X_JGZZ_ATTRIBUTE7 => p_cust_profile_amt_rec.jgzz_attribute7,
2095 X_JGZZ_ATTRIBUTE8 => p_cust_profile_amt_rec.jgzz_attribute8,
2096 X_JGZZ_ATTRIBUTE9 => p_cust_profile_amt_rec.jgzz_attribute9,
2097 X_JGZZ_ATTRIBUTE10 => p_cust_profile_amt_rec.jgzz_attribute10,
2098 X_JGZZ_ATTRIBUTE11 => p_cust_profile_amt_rec.jgzz_attribute11,
2099 X_JGZZ_ATTRIBUTE12 => p_cust_profile_amt_rec.jgzz_attribute12,
2100 X_JGZZ_ATTRIBUTE13 => p_cust_profile_amt_rec.jgzz_attribute13,
2101 X_JGZZ_ATTRIBUTE14 => p_cust_profile_amt_rec.jgzz_attribute14,
2102 X_JGZZ_ATTRIBUTE15 => p_cust_profile_amt_rec.jgzz_attribute15,
2103 X_GLOBAL_ATTRIBUTE1 => p_cust_profile_amt_rec.global_attribute1,
2104 X_GLOBAL_ATTRIBUTE2 => p_cust_profile_amt_rec.global_attribute2,
2105 X_GLOBAL_ATTRIBUTE3 => p_cust_profile_amt_rec.global_attribute3,
2106 X_GLOBAL_ATTRIBUTE4 => p_cust_profile_amt_rec.global_attribute4,
2107 X_GLOBAL_ATTRIBUTE5 => p_cust_profile_amt_rec.global_attribute5,
2108 X_GLOBAL_ATTRIBUTE6 => p_cust_profile_amt_rec.global_attribute6,
2109 X_GLOBAL_ATTRIBUTE7 => p_cust_profile_amt_rec.global_attribute7,
2110 X_GLOBAL_ATTRIBUTE8 => p_cust_profile_amt_rec.global_attribute8,
2111 X_GLOBAL_ATTRIBUTE9 => p_cust_profile_amt_rec.global_attribute9,
2112 X_GLOBAL_ATTRIBUTE10 => p_cust_profile_amt_rec.global_attribute10,
2113 X_GLOBAL_ATTRIBUTE11 => p_cust_profile_amt_rec.global_attribute11,
2114 X_GLOBAL_ATTRIBUTE12 => p_cust_profile_amt_rec.global_attribute12,
2115 X_GLOBAL_ATTRIBUTE13 => p_cust_profile_amt_rec.global_attribute13,
2116 X_GLOBAL_ATTRIBUTE14 => p_cust_profile_amt_rec.global_attribute14,
2117 X_GLOBAL_ATTRIBUTE15 => p_cust_profile_amt_rec.global_attribute15,
2118 X_GLOBAL_ATTRIBUTE16 => p_cust_profile_amt_rec.global_attribute16,
2119 X_GLOBAL_ATTRIBUTE17 => p_cust_profile_amt_rec.global_attribute17,
2120 X_GLOBAL_ATTRIBUTE18 => p_cust_profile_amt_rec.global_attribute18,
2121 X_GLOBAL_ATTRIBUTE19 => p_cust_profile_amt_rec.global_attribute19,
2122 X_GLOBAL_ATTRIBUTE20 => p_cust_profile_amt_rec.global_attribute20,
2123 X_GLOBAL_ATTRIBUTE_CATEGORY => p_cust_profile_amt_rec.global_attribute_category,
2124 X_OBJECT_VERSION_NUMBER => p_object_version_number,
2125 X_CREATED_BY_MODULE => p_cust_profile_amt_rec.created_by_module,
2126 X_APPLICATION_ID => p_cust_profile_amt_rec.application_id,
2127 X_EXCHANGE_RATE_TYPE => p_cust_profile_amt_rec.exchange_rate_type,
2128 X_MIN_FC_INVOICE_OVERDUE_TYPE => p_cust_profile_amt_rec.min_fc_invoice_overdue_type,
2129 X_MIN_FC_INVOICE_PERCENT => p_cust_profile_amt_rec.min_fc_invoice_percent,
2130 X_MIN_FC_BALANCE_OVERDUE_TYPE => p_cust_profile_amt_rec.min_fc_balance_overdue_type,
2131 X_MIN_FC_BALANCE_PERCENT => p_cust_profile_amt_rec.min_fc_balance_percent,
2132 X_INTEREST_TYPE => p_cust_profile_amt_rec.interest_type,
2133 X_INTEREST_FIXED_AMOUNT => p_cust_profile_amt_rec.interest_fixed_amount,
2134 X_INTEREST_SCHEDULE_ID => p_cust_profile_amt_rec.interest_schedule_id,
2135 X_PENALTY_TYPE => p_cust_profile_amt_rec.penalty_type,
2136 X_PENALTY_RATE => p_cust_profile_amt_rec.penalty_rate,
2137 X_MIN_INTEREST_CHARGE => p_cust_profile_amt_rec.min_interest_charge,
2138 X_PENALTY_FIXED_AMOUNT => p_cust_profile_amt_rec.penalty_fixed_amount,
2139 X_PENALTY_SCHEDULE_ID => p_cust_profile_amt_rec.penalty_schedule_id
2140 );
2141
2142 -- Debug info.
2143 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2144 hz_utility_v2pub.debug(p_message=>'HZ_CUST_PROFILE_AMTS_PKG.Update_Row (-)',
2145 p_prefix=>l_debug_prefix,
2146 p_msg_level=>fnd_log.level_procedure);
2147 END IF;
2148
2149 -- Debug info.
2150 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2151 hz_utility_v2pub.debug(p_message=>'do_update_cust_profile_amt (-)',
2152 p_prefix=>l_debug_prefix,
2153 p_msg_level=>fnd_log.level_procedure);
2154 END IF;
2155
2156 END do_update_cust_profile_amt;
2157
2158 --------------------------------------
2159 -- public procedures and functions
2160 --------------------------------------
2161 --2310474{
2162 /**
2163 * Function next_review_date_compute
2164 *
2165 * Description
2166 * Return the next_review_date
2167 *
2168 * MODIFICATION HISTORY
2169 * 04-19-2002 Herve Yu o Created
2170 *
2171 * Parameters : Review_Cycle
2172 * Last_review_date
2173 * Next_Review_Date
2174 */
2175 FUNCTION next_review_date_compute
2176 ( p_review_cycle IN VARCHAR2 ,
2177 p_last_review_date IN DATE ,
2178 p_next_review_date IN DATE )
2179 RETURN DATE
2180 IS
2181 l_last_date DATE;
2182 l_date DATE := NULL;
2183 BEGIN
2184
2185 -- Last review date is null next review date is null too
2186 IF p_last_review_date IS NULL THEN
2187 RETURN l_date;
2188 END IF;
2189
2190 -- review period is CUSTOM, API does not change next_review_date
2191 IF p_review_cycle = 'CUSTOM' THEN
2192 l_date := p_next_review_date;
2193 RETURN l_date;
2194 END IF;
2195
2196 -- if none review period provide the next review must be null
2197 IF p_review_cycle IS NULL THEN
2198 RETURN l_date;
2199 END IF;
2200
2201 l_last_date := p_last_review_date;
2202
2203 -- Compute next review date based on the period code
2204 IF l_last_date IS NOT NULL THEN
2205 l_date := compute_next_date( p_date => p_last_review_date,
2206 p_period => p_review_cycle);
2207 -- Next Review must be higher then sysdate
2208 IF l_date < SYSDATE THEN
2209 l_date := compute_next_date( p_date => sysdate,
2210 p_period => p_review_cycle);
2211 END IF;
2212 END IF;
2213
2214 RETURN l_date;
2215
2216 END;
2217
2218 /**
2219 * Function last_review_date_default
2220 *
2221 * Description
2222 * Return the last_review_date
2223 *
2224 * MODIFICATION HISTORY
2225 * 04-19-2002 Herve Yu o Created
2226 *
2227 * In parameter : Review_Cycle
2228 * Last_review_Date
2229 * p_create_update_flag
2230 */
2231 FUNCTION last_review_date_default
2232 ( p_review_cycle IN VARCHAR2 ,
2233 p_last_review_date IN DATE ,
2234 p_create_update_flag IN VARCHAR2)
2235 RETURN DATE
2236 IS
2237 l_last_review_date DATE;
2238 BEGIN
2239 IF p_create_update_flag = 'C' THEN
2240 l_last_review_date := SYSDATE;
2241 ELSIF p_create_update_flag = 'U' THEN
2242 l_last_review_date := SYSDATE;
2243 END IF;
2244 RETURN l_last_review_date;
2245 END;
2246 --}
2247
2248 /**
2249 * PROCEDURE create_customer_profile
2250 *
2251 * DESCRIPTION
2252 * Creates customer profile.
2253 *
2254 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2255 * HZ_BUSINESS_EVENT_V2PVT.create_customer_profile_event
2256 *
2257 * ARGUMENTS
2258 * IN:
2259 * p_init_msg_list Initialize message stack if it is set to
2260 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
2261 * p_customer_profile_rec Customer profile record. One customer account
2262 * must have a customer profile. One account site
2263 * use can optionally have one customer profile.
2264 * p_create_profile_amt If it is set to FND_API.G_TRUE, API create customer
2265 * profile amounts by copying corresponding data
2266 * from customer profile class amounts.
2267 * IN/OUT:
2268 * OUT:
2269 * x_cust_account_profile_id Customer account profile ID.
2270 * x_return_status Return status after the call. The status can
2271 * be FND_API.G_RET_STS_SUCCESS (success),
2272 * FND_API.G_RET_STS_ERROR (error),
2273 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
2274 * x_msg_count Number of messages in message stack.
2275 * x_msg_data Message text if x_msg_count is 1.
2276 *
2277 * NOTES
2278 *
2279 * MODIFICATION HISTORY
2280 *
2281 * 07-23-2001 Jianying Huang o Created.
2282 *
2283 */
2284
2285 PROCEDURE create_customer_profile (
2286 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2287 p_customer_profile_rec IN CUSTOMER_PROFILE_REC_TYPE,
2288 p_create_profile_amt IN VARCHAR2 := FND_API.G_TRUE,
2289 x_cust_account_profile_id OUT NOCOPY NUMBER,
2290 x_return_status OUT NOCOPY VARCHAR2,
2291 x_msg_count OUT NOCOPY NUMBER,
2292 x_msg_data OUT NOCOPY VARCHAR2
2293 ) IS
2294
2295 l_customer_profile_rec CUSTOMER_PROFILE_REC_TYPE := p_customer_profile_rec;
2296 l_debug_prefix VARCHAR2(30) := '';
2297
2298 BEGIN
2299
2300 -- Standard start of API savepoint
2301 SAVEPOINT create_customer_profile;
2302
2303 -- Check if API is called in debug mode. If yes, enable debug.
2304 --enable_debug;
2305
2306 -- Debug info.
2307 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2308 hz_utility_v2pub.debug(p_message=>'create_customer_profile (+)',
2309 p_prefix=>l_debug_prefix,
2310 p_msg_level=>fnd_log.level_procedure);
2311 END IF;
2312
2313 -- Initialize message list if p_init_msg_list is set to TRUE.
2314 IF FND_API.to_Boolean(p_init_msg_list) THEN
2315 FND_MSG_PUB.initialize;
2316 END IF;
2317
2318 -- Initialize API return status to success.
2319 x_return_status := FND_API.G_RET_STS_SUCCESS;
2320
2321 -- Call to business logic.
2322 do_create_customer_profile (
2323 l_customer_profile_rec,
2324 p_create_profile_amt,
2325 x_cust_account_profile_id,
2326 x_return_status );
2327
2328 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2329 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('Y', 'EVENTS_ENABLED')) THEN
2330 -- Invoke business event system.
2331 HZ_BUSINESS_EVENT_V2PVT.create_customer_profile_event (
2332 l_customer_profile_rec,
2333 p_create_profile_amt );
2334 END IF;
2335
2336 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'BO_EVENTS_ENABLED')) THEN
2337 HZ_POPULATE_BOT_PKG.pop_hz_customer_profiles(
2338 p_operation => 'I',
2339 p_cust_account_profile_id => x_cust_account_profile_id);
2340 END IF;
2341 END IF;
2342
2343 -- Standard call to get message count and if count is 1, get message info.
2344 FND_MSG_PUB.Count_And_Get(
2345 p_encoded => FND_API.G_FALSE,
2346 p_count => x_msg_count,
2347 p_data => x_msg_data );
2348
2349 -- Debug info.
2350 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
2351 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2352 p_msg_data=>x_msg_data,
2353 p_msg_type=>'WARNING',
2354 p_msg_level=>fnd_log.level_exception);
2355 END IF;
2356 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2357 hz_utility_v2pub.debug(p_message=>'create_customer_profile (-)',
2358 p_prefix=>l_debug_prefix,
2359 p_msg_level=>fnd_log.level_procedure);
2360 END IF;
2361
2362 -- Check if API is called in debug mode. If yes, disable debug.
2363 --disable_debug;
2364
2365 EXCEPTION
2366 WHEN FND_API.G_EXC_ERROR THEN
2367 ROLLBACK TO create_customer_profile;
2368 x_return_status := FND_API.G_RET_STS_ERROR;
2369
2370 FND_MSG_PUB.Count_And_Get(
2371 p_encoded => FND_API.G_FALSE,
2372 p_count => x_msg_count,
2373 p_data => x_msg_data );
2374
2375 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2376 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2377 p_msg_data=>x_msg_data,
2378 p_msg_type=>'ERROR',
2379 p_msg_level=>fnd_log.level_error);
2380 END IF;
2381 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2382 hz_utility_v2pub.debug(p_message=>'create_customer_profile (-)',
2383 p_prefix=>l_debug_prefix,
2384 p_msg_level=>fnd_log.level_procedure);
2385 END IF;
2386
2387 -- Check if API is called in debug mode. If yes, disable debug.
2388 --disable_debug;
2389
2390 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2391 ROLLBACK TO create_customer_profile;
2392 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2393
2394 FND_MSG_PUB.Count_And_Get(
2395 p_encoded => FND_API.G_FALSE,
2396 p_count => x_msg_count,
2397 p_data => x_msg_data );
2398
2399 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2400 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2401 p_msg_data=>x_msg_data,
2402 p_msg_type=>'UNEXPECTED ERROR',
2403 p_msg_level=>fnd_log.level_error);
2404 END IF;
2405 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2406 hz_utility_v2pub.debug(p_message=>'create_customer_profile (-)',
2407 p_prefix=>l_debug_prefix,
2408 p_msg_level=>fnd_log.level_procedure);
2409 END IF;
2410
2411 -- Check if API is called in debug mode. If yes, disable debug.
2412 --disable_debug;
2413
2414 WHEN OTHERS THEN
2415 ROLLBACK TO create_customer_profile;
2416 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2417
2418 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
2419 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
2420 FND_MSG_PUB.ADD;
2421
2422 FND_MSG_PUB.Count_And_Get(
2423 p_encoded => FND_API.G_FALSE,
2424 p_count => x_msg_count,
2425 p_data => x_msg_data );
2426
2427 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2428 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2429 p_msg_data=>x_msg_data,
2430 p_msg_type=>'SQL ERROR',
2431 p_msg_level=>fnd_log.level_error);
2432 END IF;
2433 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2434 hz_utility_v2pub.debug(p_message=>'create_customer_profile (-)',
2435 p_prefix=>l_debug_prefix,
2436 p_msg_level=>fnd_log.level_procedure);
2437 END IF;
2438
2439 -- Check if API is called in debug mode. If yes, disable debug.
2440 --disable_debug;
2441
2442 END create_customer_profile;
2443
2444 /**
2445 * PROCEDURE update_customer_profile
2446 *
2447 * DESCRIPTION
2448 * Updates customer profile.
2449 *
2450 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2451 * HZ_BUSINESS_EVENT_V2PVT.update_customer_profile_event
2452 *
2453 * ARGUMENTS
2454 * IN:
2455 * p_init_msg_list Initialize message stack if it is set to
2456 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
2457 * p_customer_profile_rec Customer profile record. One customer account
2458 * must have a customer profile. One account site
2459 * use can optionally have one customer profile.
2460 * IN/OUT:
2461 * p_object_version_number Used for locking the being updated record.
2462 * OUT:
2463 * x_return_status Return status after the call. The status can
2464 * be FND_API.G_RET_STS_SUCCESS (success),
2465 * FND_API.G_RET_STS_ERROR (error),
2466 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
2467 * x_msg_count Number of messages in message stack.
2468 * x_msg_data Message text if x_msg_count is 1.
2469 *
2470 * NOTES
2471 *
2472 * MODIFICATION HISTORY
2473 *
2474 * 07-23-2001 Jianying Huang o Created.
2475 *
2476 */
2477
2478 PROCEDURE update_customer_profile (
2479 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2480 p_customer_profile_rec IN CUSTOMER_PROFILE_REC_TYPE,
2481 p_object_version_number IN OUT NOCOPY NUMBER,
2482 x_return_status OUT NOCOPY VARCHAR2,
2483 x_msg_count OUT NOCOPY NUMBER,
2484 x_msg_data OUT NOCOPY VARCHAR2
2485 ) IS
2486
2487 l_customer_profile_rec CUSTOMER_PROFILE_REC_TYPE := p_customer_profile_rec;
2488 l_old_customer_profile_rec CUSTOMER_PROFILE_REC_TYPE ;
2489 l_debug_prefix VARCHAR2(30) := '';
2490
2491 BEGIN
2492
2493 -- Standard start of API savepoint
2494 SAVEPOINT update_customer_profile;
2495
2496 -- Check if API is called in debug mode. If yes, enable debug.
2497 --enable_debug;
2498
2499 -- Debug info.
2500 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2501 hz_utility_v2pub.debug(p_message=>'update_customer_profile (+)',
2502 p_prefix=>l_debug_prefix,
2503 p_msg_level=>fnd_log.level_procedure);
2504 END IF;
2505
2506 -- Initialize message list if p_init_msg_list is set to TRUE.
2507 IF FND_API.to_Boolean(p_init_msg_list) THEN
2508 FND_MSG_PUB.initialize;
2509 END IF;
2510
2511 -- Initialize API return status to success.
2512 x_return_status := FND_API.G_RET_STS_SUCCESS;
2513
2514 --2290537
2515 get_customer_profile_rec (
2516 p_cust_account_profile_id => p_customer_profile_rec.cust_account_profile_id,
2517 x_customer_profile_rec => l_old_customer_profile_rec,
2518 x_return_status => x_return_status,
2519 x_msg_count => x_msg_count,
2520 x_msg_data => x_msg_data);
2521
2522 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2523 RAISE FND_API.G_EXC_ERROR;
2524 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2525 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2526 END IF;
2527
2528 -- Call to business logic.
2529 do_update_customer_profile (
2530 l_customer_profile_rec,
2531 p_object_version_number,
2532 x_return_status );
2533
2534 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2535 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('Y', 'EVENTS_ENABLED')) THEN
2536 -- Invoke business event system.
2537 HZ_BUSINESS_EVENT_V2PVT.update_customer_profile_event (
2538 l_customer_profile_rec , l_old_customer_profile_rec);
2539 END IF;
2540
2541 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'BO_EVENTS_ENABLED')) THEN
2542 HZ_POPULATE_BOT_PKG.pop_hz_customer_profiles(
2543 p_operation => 'U',
2544 p_cust_account_profile_id => l_customer_profile_rec.cust_account_profile_id);
2545 END IF;
2546 END IF;
2547
2548 -- Standard call to get message count and if count is 1, get message info.
2549 FND_MSG_PUB.Count_And_Get(
2550 p_encoded => FND_API.G_FALSE,
2551 p_count => x_msg_count,
2552 p_data => x_msg_data );
2553
2554 IF fnd_log.level_exception>=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=>'WARNING',
2558 p_msg_level=>fnd_log.level_exception);
2559 END IF;
2560 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2561 hz_utility_v2pub.debug(p_message=>'update_customer_profile (-)',
2562 p_prefix=>l_debug_prefix,
2563 p_msg_level=>fnd_log.level_procedure);
2564 END IF;
2565
2566 -- Check if API is called in debug mode. If yes, disable debug.
2567 --disable_debug;
2568
2569 EXCEPTION
2570 WHEN FND_API.G_EXC_ERROR THEN
2571 ROLLBACK TO update_customer_profile;
2572 x_return_status := FND_API.G_RET_STS_ERROR;
2573
2574 FND_MSG_PUB.Count_And_Get(
2575 p_encoded => FND_API.G_FALSE,
2576 p_count => x_msg_count,
2577 p_data => x_msg_data );
2578
2579 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2580 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2581 p_msg_data=>x_msg_data,
2582 p_msg_type=>'ERROR',
2583 p_msg_level=>fnd_log.level_error);
2584 END IF;
2585 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2586 hz_utility_v2pub.debug(p_message=>'update_customer_profile (-)',
2587 p_prefix=>l_debug_prefix,
2588 p_msg_level=>fnd_log.level_procedure);
2589 END IF;
2590
2591 -- Check if API is called in debug mode. If yes, disable debug.
2592 --disable_debug;
2593
2594 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2595 ROLLBACK TO update_customer_profile;
2596 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2597
2598 FND_MSG_PUB.Count_And_Get(
2599 p_encoded => FND_API.G_FALSE,
2600 p_count => x_msg_count,
2601 p_data => x_msg_data );
2602
2603 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2604 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2605 p_msg_data=>x_msg_data,
2606 p_msg_type=>'UNEXPECTED ERROR',
2607 p_msg_level=>fnd_log.level_error);
2608 END IF;
2609 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2610 hz_utility_v2pub.debug(p_message=>'update_customer_profile (-)',
2611 p_prefix=>l_debug_prefix,
2612 p_msg_level=>fnd_log.level_procedure);
2613 END IF;
2614
2615 -- Check if API is called in debug mode. If yes, disable debug.
2616 --disable_debug;
2617
2618 WHEN OTHERS THEN
2619 ROLLBACK TO update_customer_profile;
2620 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2621
2622 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
2623 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
2624 FND_MSG_PUB.ADD;
2625
2626 FND_MSG_PUB.Count_And_Get(
2627 p_encoded => FND_API.G_FALSE,
2628 p_count => x_msg_count,
2629 p_data => x_msg_data );
2630
2631 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2632 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2633 p_msg_data=>x_msg_data,
2634 p_msg_type=>'SQL ERROR',
2635 p_msg_level=>fnd_log.level_error);
2636 END IF;
2637 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2638 hz_utility_v2pub.debug(p_message=>'update_customer_profile (-)',
2639 p_prefix=>l_debug_prefix,
2640 p_msg_level=>fnd_log.level_procedure);
2641 END IF;
2642
2643 -- Check if API is called in debug mode. If yes, disable debug.
2644 --disable_debug;
2645
2646 END update_customer_profile;
2647
2648 /**
2649 * PROCEDURE get_customer_profile_rec
2650 *
2651 * DESCRIPTION
2652 * Gets customer profile record
2653 *
2654 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2655 * HZ_CUSTOMER_PROFILES_PKG.Select_Row
2656 *
2657 * ARGUMENTS
2658 * IN:
2659 * p_init_msg_list Initialize message stack if it is set to
2660 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
2661 * p_cust_account_profile_id Customer account profile id.
2662 * IN/OUT:
2663 * OUT:
2664 * x_customer_profile_rec Returned customer profile record.
2665 * x_return_status Return status after the call. The status can
2666 * be FND_API.G_RET_STS_SUCCESS (success),
2667 * FND_API.G_RET_STS_ERROR (error),
2668 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
2669 * x_msg_count Number of messages in message stack.
2670 * x_msg_data Message text if x_msg_count is 1.
2671 *
2672 * NOTES
2673 *
2674 * MODIFICATION HISTORY
2675 *
2676 * 07-23-2001 Jianying Huang o Created.
2677 *
2678 */
2679
2680 PROCEDURE get_customer_profile_rec (
2681 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2682 p_cust_account_profile_id IN NUMBER,
2683 x_customer_profile_rec OUT NOCOPY CUSTOMER_PROFILE_REC_TYPE,
2684 x_return_status OUT NOCOPY VARCHAR2,
2685 x_msg_count OUT NOCOPY NUMBER,
2686 x_msg_data OUT NOCOPY VARCHAR2
2687 ) IS
2688 l_debug_prefix VARCHAR2(30) := '';
2689 BEGIN
2690
2691 -- Check if API is called in debug mode. If yes, enable debug.
2692 --enable_debug;
2693
2694 -- Debug info.
2695 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2696 hz_utility_v2pub.debug(p_message=>'get_customer_profile_rec (+)',
2697 p_prefix=>l_debug_prefix,
2698 p_msg_level=>fnd_log.level_procedure);
2699 END IF;
2700
2701 -- Initialize message list if p_init_msg_list is set to TRUE.
2702 IF FND_API.to_Boolean(p_init_msg_list) THEN
2703 FND_MSG_PUB.initialize;
2704 END IF;
2705
2706 -- Initialize API return status to success.
2707 x_return_status := FND_API.G_RET_STS_SUCCESS;
2708
2709 -- Check whether primary key has been passed in.
2710 IF p_cust_account_profile_id IS NULL OR
2711 p_cust_account_profile_id = FND_API.G_MISS_NUM THEN
2712 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
2713 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'cust_account_profile_id' );
2714 FND_MSG_PUB.ADD;
2715 RAISE FND_API.G_EXC_ERROR;
2716 END IF;
2717
2718 x_customer_profile_rec.cust_account_profile_id := p_cust_account_profile_id;
2719
2720 -- Debug info.
2721 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2722 hz_utility_v2pub.debug(p_message=>'HZ_CUSTOMER_PROFILES_PKG.Select_Row (+)',
2723 p_prefix=>l_debug_prefix,
2724 p_msg_level=>fnd_log.level_procedure);
2725 END IF;
2726
2727 -- Call table-handler.
2728 HZ_CUSTOMER_PROFILES_PKG.Select_Row (
2729 X_CUST_ACCOUNT_PROFILE_ID => x_customer_profile_rec.cust_account_profile_id,
2730 X_CUST_ACCOUNT_ID => x_customer_profile_rec.cust_account_id,
2731 X_STATUS => x_customer_profile_rec.status,
2732 X_COLLECTOR_ID => x_customer_profile_rec.collector_id,
2733 X_CREDIT_ANALYST_ID => x_customer_profile_rec.credit_analyst_id,
2734 X_CREDIT_CHECKING => x_customer_profile_rec.credit_checking,
2735 X_NEXT_CREDIT_REVIEW_DATE => x_customer_profile_rec.next_credit_review_date,
2736 X_TOLERANCE => x_customer_profile_rec.tolerance,
2737 X_DISCOUNT_TERMS => x_customer_profile_rec.discount_terms,
2738 X_DUNNING_LETTERS => x_customer_profile_rec.dunning_letters,
2739 X_INTEREST_CHARGES => x_customer_profile_rec.interest_charges,
2740 X_SEND_STATEMENTS => x_customer_profile_rec.send_statements,
2741 X_CREDIT_BALANCE_STATEMENTS => x_customer_profile_rec.credit_balance_statements,
2742 X_CREDIT_HOLD => x_customer_profile_rec.credit_hold,
2743 X_PROFILE_CLASS_ID => x_customer_profile_rec.profile_class_id,
2744 X_SITE_USE_ID => x_customer_profile_rec.site_use_id,
2745 X_CREDIT_RATING => x_customer_profile_rec.credit_rating,
2746 X_RISK_CODE => x_customer_profile_rec.risk_code,
2747 X_STANDARD_TERMS => x_customer_profile_rec.standard_terms,
2748 X_OVERRIDE_TERMS => x_customer_profile_rec.override_terms,
2749 X_DUNNING_LETTER_SET_ID => x_customer_profile_rec.dunning_letter_set_id,
2750 X_INTEREST_PERIOD_DAYS => x_customer_profile_rec.interest_period_days,
2751 X_PAYMENT_GRACE_DAYS => x_customer_profile_rec.payment_grace_days,
2752 X_DISCOUNT_GRACE_DAYS => x_customer_profile_rec.discount_grace_days,
2753 X_STATEMENT_CYCLE_ID => x_customer_profile_rec.statement_cycle_id,
2754 X_ACCOUNT_STATUS => x_customer_profile_rec.account_status,
2755 X_PERCENT_COLLECTABLE => x_customer_profile_rec.percent_collectable,
2756 X_AUTOCASH_HIERARCHY_ID => x_customer_profile_rec.autocash_hierarchy_id,
2757 X_ATTRIBUTE_CATEGORY => x_customer_profile_rec.attribute_category,
2758 X_ATTRIBUTE1 => x_customer_profile_rec.attribute1,
2759 X_ATTRIBUTE2 => x_customer_profile_rec.attribute2,
2760 X_ATTRIBUTE3 => x_customer_profile_rec.attribute3,
2761 X_ATTRIBUTE4 => x_customer_profile_rec.attribute4,
2762 X_ATTRIBUTE5 => x_customer_profile_rec.attribute5,
2763 X_ATTRIBUTE6 => x_customer_profile_rec.attribute6,
2764 X_ATTRIBUTE7 => x_customer_profile_rec.attribute7,
2765 X_ATTRIBUTE8 => x_customer_profile_rec.attribute8,
2766 X_ATTRIBUTE9 => x_customer_profile_rec.attribute9,
2767 X_ATTRIBUTE10 => x_customer_profile_rec.attribute10,
2768 X_ATTRIBUTE11 => x_customer_profile_rec.attribute11,
2769 X_ATTRIBUTE12 => x_customer_profile_rec.attribute12,
2770 X_ATTRIBUTE13 => x_customer_profile_rec.attribute13,
2771 X_ATTRIBUTE14 => x_customer_profile_rec.attribute14,
2772 X_ATTRIBUTE15 => x_customer_profile_rec.attribute15,
2773 X_AUTO_REC_INCL_DISPUTED_FLAG => x_customer_profile_rec.auto_rec_incl_disputed_flag,
2774 X_TAX_PRINTING_OPTION => x_customer_profile_rec.tax_printing_option,
2775 X_CHARGE_ON_FINANCE_CHARGE_FG => x_customer_profile_rec.charge_on_finance_charge_flag,
2776 X_GROUPING_RULE_ID => x_customer_profile_rec.grouping_rule_id,
2777 X_CLEARING_DAYS => x_customer_profile_rec.clearing_days,
2778 X_JGZZ_ATTRIBUTE_CATEGORY => x_customer_profile_rec.jgzz_attribute_category,
2779 X_JGZZ_ATTRIBUTE1 => x_customer_profile_rec.jgzz_attribute1,
2780 X_JGZZ_ATTRIBUTE2 => x_customer_profile_rec.jgzz_attribute2,
2781 X_JGZZ_ATTRIBUTE3 => x_customer_profile_rec.jgzz_attribute3,
2782 X_JGZZ_ATTRIBUTE4 => x_customer_profile_rec.jgzz_attribute4,
2783 X_JGZZ_ATTRIBUTE5 => x_customer_profile_rec.jgzz_attribute5,
2784 X_JGZZ_ATTRIBUTE6 => x_customer_profile_rec.jgzz_attribute6,
2785 X_JGZZ_ATTRIBUTE7 => x_customer_profile_rec.jgzz_attribute7,
2786 X_JGZZ_ATTRIBUTE8 => x_customer_profile_rec.jgzz_attribute8,
2787 X_JGZZ_ATTRIBUTE9 => x_customer_profile_rec.jgzz_attribute9,
2788 X_JGZZ_ATTRIBUTE10 => x_customer_profile_rec.jgzz_attribute10,
2789 X_JGZZ_ATTRIBUTE11 => x_customer_profile_rec.jgzz_attribute11,
2790 X_JGZZ_ATTRIBUTE12 => x_customer_profile_rec.jgzz_attribute12,
2791 X_JGZZ_ATTRIBUTE13 => x_customer_profile_rec.jgzz_attribute13,
2792 X_JGZZ_ATTRIBUTE14 => x_customer_profile_rec.jgzz_attribute14,
2793 X_JGZZ_ATTRIBUTE15 => x_customer_profile_rec.jgzz_attribute15,
2794 X_GLOBAL_ATTRIBUTE1 => x_customer_profile_rec.global_attribute1,
2795 X_GLOBAL_ATTRIBUTE2 => x_customer_profile_rec.global_attribute2,
2796 X_GLOBAL_ATTRIBUTE3 => x_customer_profile_rec.global_attribute3,
2797 X_GLOBAL_ATTRIBUTE4 => x_customer_profile_rec.global_attribute4,
2798 X_GLOBAL_ATTRIBUTE5 => x_customer_profile_rec.global_attribute5,
2799 X_GLOBAL_ATTRIBUTE6 => x_customer_profile_rec.global_attribute6,
2800 X_GLOBAL_ATTRIBUTE7 => x_customer_profile_rec.global_attribute7,
2801 X_GLOBAL_ATTRIBUTE8 => x_customer_profile_rec.global_attribute8,
2802 X_GLOBAL_ATTRIBUTE9 => x_customer_profile_rec.global_attribute9,
2803 X_GLOBAL_ATTRIBUTE10 => x_customer_profile_rec.global_attribute10,
2804 X_GLOBAL_ATTRIBUTE11 => x_customer_profile_rec.global_attribute11,
2805 X_GLOBAL_ATTRIBUTE12 => x_customer_profile_rec.global_attribute12,
2806 X_GLOBAL_ATTRIBUTE13 => x_customer_profile_rec.global_attribute13,
2807 X_GLOBAL_ATTRIBUTE14 => x_customer_profile_rec.global_attribute14,
2808 X_GLOBAL_ATTRIBUTE15 => x_customer_profile_rec.global_attribute15,
2809 X_GLOBAL_ATTRIBUTE16 => x_customer_profile_rec.global_attribute16,
2810 X_GLOBAL_ATTRIBUTE17 => x_customer_profile_rec.global_attribute17,
2811 X_GLOBAL_ATTRIBUTE18 => x_customer_profile_rec.global_attribute18,
2812 X_GLOBAL_ATTRIBUTE19 => x_customer_profile_rec.global_attribute19,
2813 X_GLOBAL_ATTRIBUTE20 => x_customer_profile_rec.global_attribute20,
2814 X_GLOBAL_ATTRIBUTE_CATEGORY => x_customer_profile_rec.global_attribute_category,
2815 X_CONS_INV_FLAG => x_customer_profile_rec.cons_inv_flag,
2816 X_CONS_INV_TYPE => x_customer_profile_rec.cons_inv_type,
2817 X_AUTOCASH_HIERARCHY_ID_ADR => x_customer_profile_rec.autocash_hierarchy_id_for_adr,
2818 X_LOCKBOX_MATCHING_OPTION => x_customer_profile_rec.lockbox_matching_option,
2819 X_CREATED_BY_MODULE => x_customer_profile_rec.created_by_module,
2820 X_APPLICATION_ID => x_customer_profile_rec.application_id,
2821 X_review_cycle => x_customer_profile_rec.review_cycle,
2822 X_last_credit_review_date => x_customer_profile_rec.last_credit_review_date,
2823 X_party_id => x_customer_profile_rec.party_id,
2824 X_CREDIT_CLASSIFICATION => x_customer_profile_rec.credit_classification,
2825 X_CONS_BILL_LEVEL => x_customer_profile_rec.cons_bill_level,
2826 X_LATE_CHARGE_CALCULATION_TRX => x_customer_profile_rec.late_charge_calculation_trx,
2827 X_CREDIT_ITEMS_FLAG => x_customer_profile_rec.credit_items_flag,
2828 X_DISPUTED_TRANSACTIONS_FLAG => x_customer_profile_rec.disputed_transactions_flag,
2829 X_LATE_CHARGE_TYPE => x_customer_profile_rec.late_charge_type,
2830 X_LATE_CHARGE_TERM_ID => x_customer_profile_rec.late_charge_term_id,
2831 X_INTEREST_CALCULATION_PERIOD => x_customer_profile_rec.interest_calculation_period,
2832 X_HOLD_CHARGED_INVOICES_FLAG => x_customer_profile_rec.hold_charged_invoices_flag,
2833 X_MESSAGE_TEXT_ID => x_customer_profile_rec.message_text_id,
2834 X_MULTIPLE_INTEREST_RATES_FLAG => x_customer_profile_rec.multiple_interest_rates_flag,
2835 X_CHARGE_BEGIN_DATE => x_customer_profile_rec.charge_begin_date,
2836 X_AUTOMATCH_SET_ID => x_customer_profile_rec.automatch_set_id
2837 );
2838
2839 --raji
2840
2841 -- Debug info.
2842 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2843 hz_utility_v2pub.debug(p_message=>'HZ_CUSTOMER_PROFILES_PKG.Select_Row (-)',
2844 p_prefix=>l_debug_prefix,
2845 p_msg_level=>fnd_log.level_procedure);
2846 END IF;
2847
2848 -- Standard call to get message count and if count is 1, get message info.
2849 FND_MSG_PUB.Count_And_Get(
2850 p_encoded => FND_API.G_FALSE,
2851 p_count => x_msg_count,
2852 p_data => x_msg_data );
2853
2854 -- Debug info.
2855 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
2856 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2857 p_msg_data=>x_msg_data,
2858 p_msg_type=>'WARNING',
2859 p_msg_level=>fnd_log.level_exception);
2860 END IF;
2861 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2862 hz_utility_v2pub.debug(p_message=>'get_customer_profile_rec (-)',
2863 p_prefix=>l_debug_prefix,
2864 p_msg_level=>fnd_log.level_procedure);
2865 END IF;
2866
2867 -- Check if API is called in debug mode. If yes, disable debug.
2868 --disable_debug;
2869
2870 EXCEPTION
2871 WHEN FND_API.G_EXC_ERROR THEN
2872 x_return_status := FND_API.G_RET_STS_ERROR;
2873
2874 FND_MSG_PUB.Count_And_Get(
2875 p_encoded => FND_API.G_FALSE,
2876 p_count => x_msg_count,
2877 p_data => x_msg_data );
2878
2879 -- Debug info.
2880 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2881 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2882 p_msg_data=>x_msg_data,
2883 p_msg_type=>'ERROR',
2884 p_msg_level=>fnd_log.level_error);
2885 END IF;
2886 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2887 hz_utility_v2pub.debug(p_message=>'get_customer_profile_rec (-)',
2888 p_prefix=>l_debug_prefix,
2889 p_msg_level=>fnd_log.level_procedure);
2890 END IF;
2891
2892 -- Check if API is called in debug mode. If yes, disable debug.
2893 --disable_debug;
2894
2895 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2896 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2897
2898 FND_MSG_PUB.Count_And_Get(
2899 p_encoded => FND_API.G_FALSE,
2900 p_count => x_msg_count,
2901 p_data => x_msg_data );
2902
2903 -- Debug info.
2904 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2905 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2906 p_msg_data=>x_msg_data,
2907 p_msg_type=>'UNEXPECTED ERROR',
2908 p_msg_level=>fnd_log.level_error);
2909 END IF;
2910 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2911 hz_utility_v2pub.debug(p_message=>'get_customer_profile_rec (-)',
2912 p_prefix=>l_debug_prefix,
2913 p_msg_level=>fnd_log.level_procedure);
2914 END IF;
2915
2916 -- Check if API is called in debug mode. If yes, disable debug.
2917 --disable_debug;
2918
2919 WHEN OTHERS THEN
2920 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2921
2922 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
2923 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
2924 FND_MSG_PUB.ADD;
2925
2926 FND_MSG_PUB.Count_And_Get(
2927 p_encoded => FND_API.G_FALSE,
2928 p_count => x_msg_count,
2929 p_data => x_msg_data );
2930
2931 -- Debug info.
2932 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2933 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2934 p_msg_data=>x_msg_data,
2935 p_msg_type=>'SQL ERROR',
2936 p_msg_level=>fnd_log.level_error);
2937 END IF;
2938 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2939 hz_utility_v2pub.debug(p_message=>'get_customer_profile_rec (-)',
2940 p_prefix=>l_debug_prefix,
2941 p_msg_level=>fnd_log.level_procedure);
2942 END IF;
2943
2944 -- Check if API is called in debug mode. If yes, disable debug.
2945 --disable_debug;
2946
2947 END get_customer_profile_rec;
2948
2949 /**
2950 * PROCEDURE create_cust_profile_amt
2951 *
2952 * DESCRIPTION
2953 * Creates customer profile amounts.
2954 *
2955 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2956 * HZ_BUSINESS_EVENT_V2PVT.create_cust_profile_amt_event
2957 *
2958 * ARGUMENTS
2959 * IN:
2960 * p_init_msg_list Initialize message stack if it is set to
2961 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
2962 * p_check_foreign_key If do foreign key checking on cust_account_id
2963 * and cust_account_profile_id or not. Defaut value
2964 * is FND_API.G_TRUE, which means API will do foreign
2965 * key checking on these 2 columns.
2966 * p_cust_profile_amt_rec Customer profile amount record.
2967 * IN/OUT:
2968 * OUT:
2969 * x_cust_acct_profile_amt_id Customer account profile amount ID.
2970 * x_return_status Return status after the call. The status can
2971 * be FND_API.G_RET_STS_SUCCESS (success),
2972 * FND_API.G_RET_STS_ERROR (error),
2973 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
2974 * x_msg_count Number of messages in message stack.
2975 * x_msg_data Message text if x_msg_count is 1.
2976 *
2977 * NOTES
2978 *
2979 * MODIFICATION HISTORY
2980 *
2981 * 07-23-2001 Jianying Huang o Created.
2982 *
2983 */
2984
2985
2986 PROCEDURE create_cust_profile_amt (
2987 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2988 p_check_foreign_key IN VARCHAR2 := FND_API.G_TRUE,
2989 p_cust_profile_amt_rec IN CUST_PROFILE_AMT_REC_TYPE,
2990 x_cust_acct_profile_amt_id OUT NOCOPY NUMBER,
2991 x_return_status OUT NOCOPY VARCHAR2,
2992 x_msg_count OUT NOCOPY NUMBER,
2993 x_msg_data OUT NOCOPY VARCHAR2
2994 ) IS
2995
2996 l_cust_profile_amt_rec CUST_PROFILE_AMT_REC_TYPE := p_cust_profile_amt_rec;
2997 l_debug_prefix VARCHAR2(30) := '';
2998
2999 BEGIN
3000
3001 -- Standard start of API savepoint
3002 SAVEPOINT create_cust_profile_amt;
3003
3004 -- Check if API is called in debug mode. If yes, enable debug.
3005 --enable_debug;
3006
3007 -- Debug info.
3008 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3009 hz_utility_v2pub.debug(p_message=>'create_cust_profile_amt (+)',
3010 p_prefix=>l_debug_prefix,
3011 p_msg_level=>fnd_log.level_procedure);
3012 END IF;
3013
3014 -- Initialize message list if p_init_msg_list is set to TRUE.
3015 IF FND_API.to_Boolean(p_init_msg_list) THEN
3016 FND_MSG_PUB.initialize;
3017 END IF;
3018
3019 -- Initialize API return status to success.
3020 x_return_status := FND_API.G_RET_STS_SUCCESS;
3021
3022 -- Call to business logic.
3023 do_create_cust_profile_amt (
3024 p_check_foreign_key,
3025 l_cust_profile_amt_rec,
3026 x_cust_acct_profile_amt_id,
3027 x_return_status );
3028
3029 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
3030 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('Y', 'EVENTS_ENABLED')) THEN
3031 -- Invoke business event system.
3032 HZ_BUSINESS_EVENT_V2PVT.create_cust_profile_amt_event (
3033 l_cust_profile_amt_rec );
3034 END IF;
3035
3036 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'BO_EVENTS_ENABLED')) THEN
3037 HZ_POPULATE_BOT_PKG.pop_hz_cust_profile_amts(
3038 p_operation => 'I',
3039 p_cust_acct_profile_amt_id => x_cust_acct_profile_amt_id);
3040 END IF;
3041 END IF;
3042
3043 -- Standard call to get message count and if count is 1, get message info.
3044 FND_MSG_PUB.Count_And_Get(
3045 p_encoded => FND_API.G_FALSE,
3046 p_count => x_msg_count,
3047 p_data => x_msg_data );
3048
3049 -- Debug info.
3050 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
3051 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
3052 p_msg_data=>x_msg_data,
3053 p_msg_type=>'WARNING',
3054 p_msg_level=>fnd_log.level_exception);
3055 END IF;
3056 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3057 hz_utility_v2pub.debug(p_message=>'create_cust_profile_amt (-)',
3058 p_prefix=>l_debug_prefix,
3059 p_msg_level=>fnd_log.level_procedure);
3060 END IF;
3061
3062 -- Check if API is called in debug mode. If yes, disable debug.
3063 --disable_debug;
3064
3065 EXCEPTION
3066 WHEN FND_API.G_EXC_ERROR THEN
3067 ROLLBACK TO create_cust_profile_amt;
3068 x_return_status := FND_API.G_RET_STS_ERROR;
3069
3070 FND_MSG_PUB.Count_And_Get(
3071 p_encoded => FND_API.G_FALSE,
3072 p_count => x_msg_count,
3073 p_data => x_msg_data );
3074
3075 -- Debug info.
3076 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
3077 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
3078 p_msg_data=>x_msg_data,
3079 p_msg_type=>'ERROR',
3080 p_msg_level=>fnd_log.level_error);
3081 END IF;
3082 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3083 hz_utility_v2pub.debug(p_message=>'create_cust_profile_amt (-)',
3084 p_prefix=>l_debug_prefix,
3085 p_msg_level=>fnd_log.level_procedure);
3086 END IF;
3087
3088 -- Check if API is called in debug mode. If yes, disable debug.
3089 --disable_debug;
3090
3091 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3092 ROLLBACK TO create_cust_profile_amt;
3093 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3094
3095 FND_MSG_PUB.Count_And_Get(
3096 p_encoded => FND_API.G_FALSE,
3097 p_count => x_msg_count,
3098 p_data => x_msg_data );
3099
3100 -- Debug info.
3101 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
3102 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
3103 p_msg_data=>x_msg_data,
3104 p_msg_type=>'UNEXPECTED ERROR',
3105 p_msg_level=>fnd_log.level_error);
3106 END IF;
3107 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3108 hz_utility_v2pub.debug(p_message=>'create_cust_profile_amt (-)',
3109 p_prefix=>l_debug_prefix,
3110 p_msg_level=>fnd_log.level_procedure);
3111 END IF;
3112
3113 -- Check if API is called in debug mode. If yes, disable debug.
3114 --disable_debug;
3115
3116 WHEN OTHERS THEN
3117 ROLLBACK TO create_cust_profile_amt;
3118 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3119
3120 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
3121 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
3122 FND_MSG_PUB.ADD;
3123
3124 FND_MSG_PUB.Count_And_Get(
3125 p_encoded => FND_API.G_FALSE,
3126 p_count => x_msg_count,
3127 p_data => x_msg_data );
3128
3129 -- Debug info.
3130 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
3131 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
3132 p_msg_data=>x_msg_data,
3133 p_msg_type=>'SQL ERROR',
3134 p_msg_level=>fnd_log.level_error);
3135 END IF;
3136 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3137 hz_utility_v2pub.debug(p_message=>'create_cust_profile_amt (-)',
3138 p_prefix=>l_debug_prefix,
3139 p_msg_level=>fnd_log.level_procedure);
3140 END IF;
3141
3142 -- Check if API is called in debug mode. If yes, disable debug.
3143 --disable_debug;
3144
3145 END create_cust_profile_amt;
3146
3147
3148 /**
3149 * PROCEDURE update_cust_profile_amt
3150 *
3151 * DESCRIPTION
3152 * Updates customer profile amounts.
3153 *
3154 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3155 * HZ_BUSINESS_EVENT_V2PVT.update_cust_profile_amt_event
3156 *
3157 * ARGUMENTS
3158 * IN:
3159 * p_init_msg_list Initialize message stack if it is set to
3160 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
3161 * p_cust_profile_amt_rec Customer profile amount record.
3162 * IN/OUT:
3163 * p_object_version_number Used for locking the being updated record.
3164 * OUT:
3165 * x_return_status Return status after the call. The status can
3166 * be FND_API.G_RET_STS_SUCCESS (success),
3167 * FND_API.G_RET_STS_ERROR (error),
3168 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
3169 * x_msg_count Number of messages in message stack.
3170 * x_msg_data Message text if x_msg_count is 1.
3171 *
3172 * NOTES
3173 *
3174 * MODIFICATION HISTORY
3175 *
3176 * 07-23-2001 Jianying Huang o Created.
3177 *
3178 */
3179
3180 PROCEDURE update_cust_profile_amt (
3181 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3182 p_cust_profile_amt_rec IN CUST_PROFILE_AMT_REC_TYPE,
3183 p_object_version_number IN OUT NOCOPY NUMBER,
3184 x_return_status OUT NOCOPY VARCHAR2,
3185 x_msg_count OUT NOCOPY NUMBER,
3186 x_msg_data OUT NOCOPY VARCHAR2
3187 ) IS
3188
3189 l_cust_profile_amt_rec CUST_PROFILE_AMT_REC_TYPE := p_cust_profile_amt_rec;
3190 l_old_cust_profile_amt_rec CUST_PROFILE_AMT_REC_TYPE;
3191 l_debug_prefix VARCHAR2(30) := '';
3192
3193 BEGIN
3194
3195 -- Standard start of API savepoint
3196 SAVEPOINT update_cust_profile_amt;
3197
3198 -- Check if API is called in debug mode. If yes, enable debug.
3199 --enable_debug;
3200
3201 -- Debug info.
3202 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3203 hz_utility_v2pub.debug(p_message=>'update_cust_profile_amt (+)',
3204 p_prefix=>l_debug_prefix,
3205 p_msg_level=>fnd_log.level_procedure);
3206 END IF;
3207
3208 -- Initialize message list if p_init_msg_list is set to TRUE.
3209 IF FND_API.to_Boolean(p_init_msg_list) THEN
3210 FND_MSG_PUB.initialize;
3211 END IF;
3212
3213 -- Initialize API return status to success.
3214 x_return_status := FND_API.G_RET_STS_SUCCESS;
3215
3216 --2290537
3217 get_cust_profile_amt_rec (
3218 p_cust_acct_profile_amt_id => p_cust_profile_amt_rec.cust_acct_profile_amt_id,
3219 x_cust_profile_amt_rec => l_old_cust_profile_amt_rec,
3220 x_return_status => x_return_status,
3221 x_msg_count => x_msg_count,
3222 x_msg_data => x_msg_data);
3223
3224 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3225 RAISE FND_API.G_EXC_ERROR;
3226 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3227 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3228 END IF;
3229
3230 -- Call to business logic.
3231 do_update_cust_profile_amt (
3232 l_cust_profile_amt_rec,
3233 p_object_version_number,
3234 x_return_status );
3235
3236 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
3237 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('Y', 'EVENTS_ENABLED')) THEN
3238 -- Invoke business event system.
3239 HZ_BUSINESS_EVENT_V2PVT.update_cust_profile_amt_event (
3240 l_cust_profile_amt_rec , l_old_cust_profile_amt_rec );
3241 END IF;
3242
3243 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'BO_EVENTS_ENABLED')) THEN
3244 HZ_POPULATE_BOT_PKG.pop_hz_cust_profile_amts(
3245 p_operation => 'U',
3246 p_cust_acct_profile_amt_id => l_cust_profile_amt_rec.cust_acct_profile_amt_id);
3247 END IF;
3248 END IF;
3249
3250 -- Standard call to get message count and if count is 1, get message info.
3251 FND_MSG_PUB.Count_And_Get(
3252 p_encoded => FND_API.G_FALSE,
3253 p_count => x_msg_count,
3254 p_data => x_msg_data );
3255
3256 -- Debug info.
3257 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
3258 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
3259 p_msg_data=>x_msg_data,
3260 p_msg_type=>'WARNING',
3261 p_msg_level=>fnd_log.level_exception);
3262 END IF;
3263 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3264 hz_utility_v2pub.debug(p_message=>'update_cust_profile_amt (-)',
3265 p_prefix=>l_debug_prefix,
3266 p_msg_level=>fnd_log.level_procedure);
3267 END IF;
3268
3269
3270 -- Check if API is called in debug mode. If yes, disable debug.
3271 --disable_debug;
3272
3273 EXCEPTION
3274 WHEN FND_API.G_EXC_ERROR THEN
3275 ROLLBACK TO update_cust_profile_amt;
3276 x_return_status := FND_API.G_RET_STS_ERROR;
3277
3278 FND_MSG_PUB.Count_And_Get(
3279 p_encoded => FND_API.G_FALSE,
3280 p_count => x_msg_count,
3281 p_data => x_msg_data );
3282
3283 -- Debug info.
3284 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
3285 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
3286 p_msg_data=>x_msg_data,
3287 p_msg_type=>'ERROR',
3288 p_msg_level=>fnd_log.level_error);
3289 END IF;
3290 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3291 hz_utility_v2pub.debug(p_message=>'update_cust_profile_amt (-)',
3292 p_prefix=>l_debug_prefix,
3293 p_msg_level=>fnd_log.level_procedure);
3294 END IF;
3295
3296 -- Check if API is called in debug mode. If yes, disable debug.
3297 --disable_debug;
3298
3299 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3300 ROLLBACK TO update_cust_profile_amt;
3301 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3302
3303 FND_MSG_PUB.Count_And_Get(
3304 p_encoded => FND_API.G_FALSE,
3305 p_count => x_msg_count,
3306 p_data => x_msg_data );
3307
3308 -- Debug info.
3309 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
3310 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
3311 p_msg_data=>x_msg_data,
3312 p_msg_type=>'UNEXPECTED ERROR',
3313 p_msg_level=>fnd_log.level_error);
3314 END IF;
3315 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3316 hz_utility_v2pub.debug(p_message=>'update_cust_profile_amt (-)',
3317 p_prefix=>l_debug_prefix,
3318 p_msg_level=>fnd_log.level_procedure);
3319 END IF;
3320
3321 -- Check if API is called in debug mode. If yes, disable debug.
3322 --disable_debug;
3323
3324 WHEN OTHERS THEN
3325 ROLLBACK TO update_cust_profile_amt;
3326 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3327
3328 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
3329 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
3330 FND_MSG_PUB.ADD;
3331
3332 FND_MSG_PUB.Count_And_Get(
3333 p_encoded => FND_API.G_FALSE,
3334 p_count => x_msg_count,
3335 p_data => x_msg_data );
3336
3337 -- Debug info.
3338 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
3339 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
3340 p_msg_data=>x_msg_data,
3341 p_msg_type=>'SQL ERROR',
3342 p_msg_level=>fnd_log.level_error);
3343 END IF;
3344 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3345 hz_utility_v2pub.debug(p_message=>'update_cust_profile_amt (-)',
3346 p_prefix=>l_debug_prefix,
3347 p_msg_level=>fnd_log.level_procedure);
3348 END IF;
3349
3350 -- Check if API is called in debug mode. If yes, disable debug.
3351 --disable_debug;
3352
3353 END update_cust_profile_amt;
3354
3355 /**
3356 * PROCEDURE get_cust_profile_amt_rec
3357 *
3358 * DESCRIPTION
3359 * Gets customer profile amount record
3360 *
3361 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3362 * HZ_CUST_PROFILE_AMTS_PKG.Select_Row
3363 *
3364 * ARGUMENTS
3365 * IN:
3366 * p_init_msg_list Initialize message stack if it is set to
3367 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
3368 * p_cust_acct_profile_amt_id Customer account profile amount id.
3369 * IN/OUT:
3370 * OUT:
3371 * x_cust_profile_amt_rec Returned customer profile amount record.
3372 * x_return_status Return status after the call. The status can
3373 * be FND_API.G_RET_STS_SUCCESS (success),
3374 * FND_API.G_RET_STS_ERROR (error),
3375 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
3376 * x_msg_count Number of messages in message stack.
3377 * x_msg_data Message text if x_msg_count is 1.
3378 *
3379 * NOTES
3380 *
3381 * MODIFICATION HISTORY
3382 *
3383 * 07-23-2001 Jianying Huang o Created.
3384 *
3385 */
3386
3387 PROCEDURE get_cust_profile_amt_rec (
3388 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3389 p_cust_acct_profile_amt_id IN NUMBER,
3390 x_cust_profile_amt_rec OUT NOCOPY CUST_PROFILE_AMT_REC_TYPE,
3391 x_return_status OUT NOCOPY VARCHAR2,
3392 x_msg_count OUT NOCOPY NUMBER,
3393 x_msg_data OUT NOCOPY VARCHAR2
3394 ) IS
3395 l_debug_prefix VARCHAR2(30) := '';
3396 BEGIN
3397
3398 -- Check if API is called in debug mode. If yes, enable debug.
3399 --enable_debug;
3400
3401 -- Debug info.
3402 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3403 hz_utility_v2pub.debug(p_message=>'get_cust_profile_amt_rec (+)',
3404 p_prefix=>l_debug_prefix,
3405 p_msg_level=>fnd_log.level_procedure);
3406 END IF;
3407
3408 -- Initialize message list if p_init_msg_list is set to TRUE.
3409 IF FND_API.to_Boolean(p_init_msg_list) THEN
3410 FND_MSG_PUB.initialize;
3411 END IF;
3412
3413 -- Initialize API return status to success.
3414 x_return_status := FND_API.G_RET_STS_SUCCESS;
3415
3416 -- Check whether primary key has been passed in.
3417 IF p_cust_acct_profile_amt_id IS NULL OR
3418 p_cust_acct_profile_amt_id = FND_API.G_MISS_NUM THEN
3419 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
3420 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'cust_acct_profile_amt_id' );
3421 FND_MSG_PUB.ADD;
3422 RAISE FND_API.G_EXC_ERROR;
3423 END IF;
3424
3425 x_cust_profile_amt_rec.cust_acct_profile_amt_id := p_cust_acct_profile_amt_id;
3426
3427 -- Debug info.
3428 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3429 hz_utility_v2pub.debug(p_message=>'HZ_CUST_PROFILE_AMTS_PKG.Select_Row (+)',
3430 p_prefix=>l_debug_prefix,
3431 p_msg_level=>fnd_log.level_procedure);
3432 END IF;
3433
3434 -- Call table-handler.
3435 HZ_CUST_PROFILE_AMTS_PKG.Select_Row (
3436 X_CUST_ACCT_PROFILE_AMT_ID => x_cust_profile_amt_rec.cust_acct_profile_amt_id,
3437 X_CUST_ACCOUNT_PROFILE_ID => x_cust_profile_amt_rec.cust_account_profile_id,
3438 X_CURRENCY_CODE => x_cust_profile_amt_rec.currency_code,
3439 X_TRX_CREDIT_LIMIT => x_cust_profile_amt_rec.trx_credit_limit,
3440 X_OVERALL_CREDIT_LIMIT => x_cust_profile_amt_rec.overall_credit_limit,
3441 X_MIN_DUNNING_AMOUNT => x_cust_profile_amt_rec.min_dunning_amount,
3442 X_MIN_DUNNING_INVOICE_AMOUNT => x_cust_profile_amt_rec.min_dunning_invoice_amount,
3443 X_MAX_INTEREST_CHARGE => x_cust_profile_amt_rec.max_interest_charge,
3444 X_MIN_STATEMENT_AMOUNT => x_cust_profile_amt_rec.min_statement_amount,
3445 X_AUTO_REC_MIN_RECEIPT_AMOUNT => x_cust_profile_amt_rec.auto_rec_min_receipt_amount,
3446 X_INTEREST_RATE => x_cust_profile_amt_rec.interest_rate,
3447 X_ATTRIBUTE_CATEGORY => x_cust_profile_amt_rec.attribute_category,
3448 X_ATTRIBUTE1 => x_cust_profile_amt_rec.attribute1,
3449 X_ATTRIBUTE2 => x_cust_profile_amt_rec.attribute2,
3450 X_ATTRIBUTE3 => x_cust_profile_amt_rec.attribute3,
3451 X_ATTRIBUTE4 => x_cust_profile_amt_rec.attribute4,
3452 X_ATTRIBUTE5 => x_cust_profile_amt_rec.attribute5,
3453 X_ATTRIBUTE6 => x_cust_profile_amt_rec.attribute6,
3454 X_ATTRIBUTE7 => x_cust_profile_amt_rec.attribute7,
3455 X_ATTRIBUTE8 => x_cust_profile_amt_rec.attribute8,
3456 X_ATTRIBUTE9 => x_cust_profile_amt_rec.attribute9,
3457 X_ATTRIBUTE10 => x_cust_profile_amt_rec.attribute10,
3458 X_ATTRIBUTE11 => x_cust_profile_amt_rec.attribute11,
3459 X_ATTRIBUTE12 => x_cust_profile_amt_rec.attribute12,
3460 X_ATTRIBUTE13 => x_cust_profile_amt_rec.attribute13,
3461 X_ATTRIBUTE14 => x_cust_profile_amt_rec.attribute14,
3462 X_ATTRIBUTE15 => x_cust_profile_amt_rec.attribute15,
3463 X_MIN_FC_BALANCE_AMOUNT => x_cust_profile_amt_rec.min_fc_balance_amount,
3464 X_MIN_FC_INVOICE_AMOUNT => x_cust_profile_amt_rec.min_fc_invoice_amount,
3465 X_CUST_ACCOUNT_ID => x_cust_profile_amt_rec.cust_account_id,
3466 X_SITE_USE_ID => x_cust_profile_amt_rec.site_use_id,
3467 X_EXPIRATION_DATE => x_cust_profile_amt_rec.expiration_date,
3468 X_JGZZ_ATTRIBUTE_CATEGORY => x_cust_profile_amt_rec.jgzz_attribute_category,
3469 X_JGZZ_ATTRIBUTE1 => x_cust_profile_amt_rec.jgzz_attribute1,
3470 X_JGZZ_ATTRIBUTE2 => x_cust_profile_amt_rec.jgzz_attribute2,
3471 X_JGZZ_ATTRIBUTE3 => x_cust_profile_amt_rec.jgzz_attribute3,
3472 X_JGZZ_ATTRIBUTE4 => x_cust_profile_amt_rec.jgzz_attribute4,
3473 X_JGZZ_ATTRIBUTE5 => x_cust_profile_amt_rec.jgzz_attribute5,
3474 X_JGZZ_ATTRIBUTE6 => x_cust_profile_amt_rec.jgzz_attribute6,
3475 X_JGZZ_ATTRIBUTE7 => x_cust_profile_amt_rec.jgzz_attribute7,
3476 X_JGZZ_ATTRIBUTE8 => x_cust_profile_amt_rec.jgzz_attribute8,
3477 X_JGZZ_ATTRIBUTE9 => x_cust_profile_amt_rec.jgzz_attribute9,
3478 X_JGZZ_ATTRIBUTE10 => x_cust_profile_amt_rec.jgzz_attribute10,
3479 X_JGZZ_ATTRIBUTE11 => x_cust_profile_amt_rec.jgzz_attribute11,
3480 X_JGZZ_ATTRIBUTE12 => x_cust_profile_amt_rec.jgzz_attribute12,
3481 X_JGZZ_ATTRIBUTE13 => x_cust_profile_amt_rec.jgzz_attribute13,
3482 X_JGZZ_ATTRIBUTE14 => x_cust_profile_amt_rec.jgzz_attribute14,
3483 X_JGZZ_ATTRIBUTE15 => x_cust_profile_amt_rec.jgzz_attribute15,
3484 X_GLOBAL_ATTRIBUTE1 => x_cust_profile_amt_rec.global_attribute1,
3485 X_GLOBAL_ATTRIBUTE2 => x_cust_profile_amt_rec.global_attribute2,
3486 X_GLOBAL_ATTRIBUTE3 => x_cust_profile_amt_rec.global_attribute3,
3487 X_GLOBAL_ATTRIBUTE4 => x_cust_profile_amt_rec.global_attribute4,
3488 X_GLOBAL_ATTRIBUTE5 => x_cust_profile_amt_rec.global_attribute5,
3489 X_GLOBAL_ATTRIBUTE6 => x_cust_profile_amt_rec.global_attribute6,
3490 X_GLOBAL_ATTRIBUTE7 => x_cust_profile_amt_rec.global_attribute7,
3491 X_GLOBAL_ATTRIBUTE8 => x_cust_profile_amt_rec.global_attribute8,
3492 X_GLOBAL_ATTRIBUTE9 => x_cust_profile_amt_rec.global_attribute9,
3493 X_GLOBAL_ATTRIBUTE10 => x_cust_profile_amt_rec.global_attribute10,
3494 X_GLOBAL_ATTRIBUTE11 => x_cust_profile_amt_rec.global_attribute11,
3495 X_GLOBAL_ATTRIBUTE12 => x_cust_profile_amt_rec.global_attribute12,
3496 X_GLOBAL_ATTRIBUTE13 => x_cust_profile_amt_rec.global_attribute13,
3497 X_GLOBAL_ATTRIBUTE14 => x_cust_profile_amt_rec.global_attribute14,
3498 X_GLOBAL_ATTRIBUTE15 => x_cust_profile_amt_rec.global_attribute15,
3499 X_GLOBAL_ATTRIBUTE16 => x_cust_profile_amt_rec.global_attribute16,
3500 X_GLOBAL_ATTRIBUTE17 => x_cust_profile_amt_rec.global_attribute17,
3501 X_GLOBAL_ATTRIBUTE18 => x_cust_profile_amt_rec.global_attribute18,
3502 X_GLOBAL_ATTRIBUTE19 => x_cust_profile_amt_rec.global_attribute19,
3503 X_GLOBAL_ATTRIBUTE20 => x_cust_profile_amt_rec.global_attribute20,
3504 X_GLOBAL_ATTRIBUTE_CATEGORY => x_cust_profile_amt_rec.global_attribute_category,
3505 X_CREATED_BY_MODULE => x_cust_profile_amt_rec.created_by_module,
3506 X_APPLICATION_ID => x_cust_profile_amt_rec.application_id,
3507 X_EXCHANGE_RATE_TYPE => x_cust_profile_amt_rec.exchange_rate_type,
3508 X_MIN_FC_INVOICE_OVERDUE_TYPE => x_cust_profile_amt_rec.min_fc_invoice_overdue_type,
3509 X_MIN_FC_INVOICE_PERCENT => x_cust_profile_amt_rec.min_fc_invoice_percent,
3510 X_MIN_FC_BALANCE_OVERDUE_TYPE => x_cust_profile_amt_rec.min_fc_balance_overdue_type,
3511 X_MIN_FC_BALANCE_PERCENT => x_cust_profile_amt_rec.min_fc_balance_percent,
3512 X_INTEREST_TYPE => x_cust_profile_amt_rec.interest_type,
3513 X_INTEREST_FIXED_AMOUNT => x_cust_profile_amt_rec.interest_fixed_amount,
3514 X_INTEREST_SCHEDULE_ID => x_cust_profile_amt_rec.interest_schedule_id,
3515 X_PENALTY_TYPE => x_cust_profile_amt_rec.penalty_type,
3516 X_PENALTY_RATE => x_cust_profile_amt_rec.penalty_rate,
3517 X_MIN_INTEREST_CHARGE => x_cust_profile_amt_rec.min_interest_charge,
3518 X_PENALTY_FIXED_AMOUNT => x_cust_profile_amt_rec.penalty_fixed_amount,
3519 X_PENALTY_SCHEDULE_ID => x_cust_profile_amt_rec.penalty_schedule_id
3520 );
3521
3522 -- Debug info.
3523 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3524 hz_utility_v2pub.debug(p_message=>'HZ_CUST_PROFILE_AMTS_PKG.Select_Row (-)',
3525 p_prefix=>l_debug_prefix,
3526 p_msg_level=>fnd_log.level_procedure);
3527 END IF;
3528
3529 -- Standard call to get message count and if count is 1, get message info.
3530 FND_MSG_PUB.Count_And_Get(
3531 p_encoded => FND_API.G_FALSE,
3532 p_count => x_msg_count,
3533 p_data => x_msg_data );
3534
3535 -- Debug info.
3536 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
3537 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
3538 p_msg_data=>x_msg_data,
3539 p_msg_type=>'WARNING',
3540 p_msg_level=>fnd_log.level_exception);
3541 END IF;
3542 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3543 hz_utility_v2pub.debug(p_message=>'get_cust_profile_amt_rec (-)',
3544 p_prefix=>l_debug_prefix,
3545 p_msg_level=>fnd_log.level_procedure);
3546 END IF;
3547
3548 -- Check if API is called in debug mode. If yes, disable debug.
3549 --disable_debug;
3550
3551 EXCEPTION
3552 WHEN FND_API.G_EXC_ERROR THEN
3553 x_return_status := FND_API.G_RET_STS_ERROR;
3554
3555 FND_MSG_PUB.Count_And_Get(
3556 p_encoded => FND_API.G_FALSE,
3557 p_count => x_msg_count,
3558 p_data => x_msg_data );
3559
3560 -- Debug info.
3561 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
3562 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
3563 p_msg_data=>x_msg_data,
3564 p_msg_type=>'ERROR',
3565 p_msg_level=>fnd_log.level_error);
3566 END IF;
3567 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3568 hz_utility_v2pub.debug(p_message=>'get_cust_profile_amt_rec (-)',
3569 p_prefix=>l_debug_prefix,
3570 p_msg_level=>fnd_log.level_procedure);
3571 END IF;
3572
3573 -- Check if API is called in debug mode. If yes, disable debug.
3574 --disable_debug;
3575
3576 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3577 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3578
3579 FND_MSG_PUB.Count_And_Get(
3580 p_encoded => FND_API.G_FALSE,
3581 p_count => x_msg_count,
3582 p_data => x_msg_data );
3583
3584 -- Debug info.
3585 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
3586 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
3587 p_msg_data=>x_msg_data,
3588 p_msg_type=>'UNEXPECTED ERROR',
3589 p_msg_level=>fnd_log.level_error);
3590 END IF;
3591 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3592 hz_utility_v2pub.debug(p_message=>'get_cust_profile_amt_rec (-)',
3593 p_prefix=>l_debug_prefix,
3594 p_msg_level=>fnd_log.level_procedure);
3595 END IF;
3596
3597 -- Check if API is called in debug mode. If yes, disable debug.
3598 --disable_debug;
3599
3600 WHEN OTHERS THEN
3601 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3602
3603 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
3604 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
3605 FND_MSG_PUB.ADD;
3606
3607 FND_MSG_PUB.Count_And_Get(
3608 p_encoded => FND_API.G_FALSE,
3609 p_count => x_msg_count,
3610 p_data => x_msg_data );
3611
3612 -- Debug info.
3613 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
3614 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
3615 p_msg_data=>x_msg_data,
3616 p_msg_type=>'SQL ERROR',
3617 p_msg_level=>fnd_log.level_error);
3618 END IF;
3619 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3620 hz_utility_v2pub.debug(p_message=>'get_cust_profile_amt_rec (-)',
3621 p_prefix=>l_debug_prefix,
3622 p_msg_level=>fnd_log.level_procedure);
3623 END IF;
3624
3625 -- Check if API is called in debug mode. If yes, disable debug.
3626 --disable_debug;
3627
3628 END get_cust_profile_amt_rec;
3629
3630 END HZ_CUSTOMER_PROFILE_V2PUB;