[Home] [Help]
PACKAGE BODY: APPS.HZP_CPROF_PKG
Source
1 PACKAGE BODY hzp_cprof_pkg as
2 /* $Header: ARHCPRFB.pls 120.15 2005/06/16 21:09:29 jhuang ship $ */
3 --
4 --
5 FUNCTION INIT_SWITCH
6 ( p_date IN DATE,
7 p_switch IN VARCHAR2 DEFAULT 'NULL_GMISS')
8 RETURN DATE
9 IS
10 res_date date;
11 BEGIN
12 IF p_switch = 'NULL_GMISS' THEN
13 IF p_date IS NULL THEN
14 res_date := FND_API.G_MISS_DATE;
15 ELSE
16 res_date := p_date;
17 END IF;
18 ELSIF p_switch = 'GMISS_NULL' THEN
19 IF p_date = FND_API.G_MISS_DATE THEN
20 res_date := NULL;
21 ELSE
22 res_date := p_date;
23 END IF;
24 ELSE
25 res_date := TO_DATE('31/12/1800','DD/MM/RRRR');
26 END IF;
27 RETURN res_date;
28 END;
29
30 FUNCTION INIT_SWITCH
31 ( p_char IN VARCHAR2,
32 p_switch IN VARCHAR2 DEFAULT 'NULL_GMISS')
33 RETURN VARCHAR2
34 IS
35 res_char varchar2(2000);
36 BEGIN
37 IF p_switch = 'NULL_GMISS' THEN
38 IF p_char IS NULL THEN
39 return FND_API.G_MISS_CHAR;
40 ELSE
41 return p_char;
42 END IF;
43 ELSIF p_switch = 'GMISS_NULL' THEN
44 IF p_char = FND_API.G_MISS_CHAR THEN
45 return NULL;
46 ELSE
47 return p_char;
48 END IF;
49 ELSE
50 return ('INCORRECT_P_SWITCH');
51 END IF;
52 END;
53
54 FUNCTION INIT_SWITCH
55 ( p_num IN NUMBER,
56 p_switch IN VARCHAR2 DEFAULT 'NULL_GMISS')
57 RETURN NUMBER
58 IS
59 BEGIN
60 IF p_switch = 'NULL_GMISS' THEN
61 IF p_num IS NULL THEN
62 return FND_API.G_MISS_NUM;
63 ELSE
64 return p_num;
65 END IF;
66 ELSIF p_switch = 'GMISS_NULL' THEN
67 IF p_num = FND_API.G_MISS_NUM THEN
68 return NULL;
69 ELSE
70 return p_num;
71 END IF;
72 ELSE
73 return ('9999999999');
74 END IF;
75 END;
76
77 PROCEDURE check_unique ( p_customer_id in number,
78 p_site_use_id in number
79 )
80 IS
81 --
82 l_profile_count number;
83 --
84 BEGIN
85 --
86 IF ( p_site_use_id is null ) THEN
87 --
88 select count(1)
89 into l_profile_count
90 from hz_customer_profiles cp
91 where cp.cust_account_id = p_customer_id
92 and cp.site_use_id is null;
93 --
94 ELSE
95 /* bug3578108 : Added condition to pickup only bill to,
96 statement and dunning profile */
97 select count(1)
98 into l_profile_count
99 from hz_customer_profiles cp,
100 hz_cust_site_uses su
101 where cp.cust_account_id = p_customer_id
102 and cp.site_use_id = p_site_use_id
103 and su.site_use_id = cp.site_use_id
104 and su.site_use_code in ('BILL_TO','DUN','STMTS') ;
105 --
106 END IF;
107 IF ( l_profile_count >= 1 ) THEN
108 fnd_message.set_name('AR','AR_CUST_ONE_PROFILE_ALLOWED');
109 app_exception.raise_exception;
110 END IF;
111 --
112 --
113 END check_unique;
114 --
115 --
116
117 PROCEDURE update_customer_alt_names(p_rowid in varchar2,
118 p_standard_terms in number,
119 p_customer_id in number,
120 p_site_use_id in number
121 )
122 IS
123 l_standard_terms number := null ;
124
125 CURSOR c1 is
126 select standard_terms
127 from hz_customer_profiles
128 where rowid = p_rowid;
129 BEGIN
130 --
131 --
132 -- if ( nvl ( fnd_profile.value('AR_ALT_NAME_SEARCH') , 'N' ) = 'Y' ) then
133 --
134 OPEN c1;
135 FETCH c1 INTO l_standard_terms;
136 IF c1%FOUND THEN
137 --
138 IF (
139 ( l_standard_terms IS NULL AND p_standard_terms IS NOT NULL )
140 OR ( l_standard_terms IS NOT NULL and p_standard_terms IS NULL )
141 OR ( l_standard_terms <> p_standard_terms )
142 )
143 THEN
144 --
145 arp_cust_alt_match_pkg.update_pay_term_id ( p_customer_id,
146 p_site_use_id , p_standard_terms );
147 --
148 END IF;
149 --
150 END IF;
151 CLOSE c1;
152 --
153 -- end if;
154 --
155 --
156 EXCEPTION
157 WHEN OTHERS THEN
158 arp_standard.debug('EXCEPTION: hzp_cprof_pkg.update_customer_alt_names');
159 END update_customer_alt_names;
160
161 --
162 --
163 --
164 PROCEDURE Insert_Row(
165 X_Customer_Profile_Id IN OUT NOCOPY NUMBER,
166 X_Auto_Rec_Incl_Disputed_Flag VARCHAR2,
167 X_Collector_Id NUMBER,
168 X_Created_By NUMBER,
169 X_Creation_Date DATE,
170 X_Credit_Balance_Statements VARCHAR2,
171 X_Credit_Checking VARCHAR2,
172 X_Credit_Hold VARCHAR2,
173 X_Customer_Id NUMBER,
174 X_Discount_Terms VARCHAR2,
175 X_Dunning_Letters VARCHAR2,
176 X_Interest_Charges VARCHAR2,
177 X_Last_Updated_By NUMBER,
178 X_Last_Update_Date DATE,
179 X_Statements VARCHAR2,
180 X_Status VARCHAR2,
181 X_Tolerance NUMBER,
182 X_Tax_Printing_Option VARCHAR2,
183 X_Account_Status VARCHAR2,
184 X_Autocash_Hierarchy_Id NUMBER,
185 X_Credit_Rating VARCHAR2,
186 X_Customer_Profile_Class_Id NUMBER,
187 X_Discount_Grace_Days NUMBER,
188 X_Dunning_Letter_Set_Id NUMBER,
189 X_Interest_Period_Days NUMBER,
190 X_Last_Update_Login NUMBER,
191 X_Override_Terms VARCHAR2,
192 X_Payment_Grace_Days NUMBER,
193 X_Percent_Collectable NUMBER,
194 X_Risk_Code VARCHAR2,
195 X_Site_Use_Id NUMBER,
196 X_Standard_Terms NUMBER,
197 X_Statement_Cycle_Id NUMBER,
198 X_Attribute_Category VARCHAR2,
199 X_Attribute1 VARCHAR2,
200 X_Attribute2 VARCHAR2,
201 X_Attribute3 VARCHAR2,
202 X_Attribute4 VARCHAR2,
203 X_Attribute5 VARCHAR2,
204 X_Attribute6 VARCHAR2,
205 X_Attribute7 VARCHAR2,
206 X_Attribute8 VARCHAR2,
207 X_Attribute9 VARCHAR2,
208 X_Attribute10 VARCHAR2,
209 X_Attribute11 VARCHAR2,
210 X_Attribute12 VARCHAR2,
211 X_Attribute13 VARCHAR2,
212 X_Attribute14 VARCHAR2,
213 X_Attribute15 VARCHAR2,
214 X_Charge_On_Fin_Charge_Flag VARCHAR2,
215 X_Grouping_Rule_Id NUMBER,
216 X_Cons_Inv_Flag VARCHAR2,
217 X_Cons_Inv_Type VARCHAR2,
218 X_Clearing_Days NUMBER,
219 X_Jgzz_attribute_Category VARCHAR2,
220 X_Jgzz_attribute1 VARCHAR2,
221 X_Jgzz_attribute2 VARCHAR2,
222 X_Jgzz_attribute3 VARCHAR2,
223 X_Jgzz_attribute4 VARCHAR2,
224 X_Jgzz_attribute5 VARCHAR2,
225 X_Jgzz_attribute6 VARCHAR2,
226 X_Jgzz_attribute7 VARCHAR2,
227 X_Jgzz_attribute8 VARCHAR2,
228 X_Jgzz_attribute9 VARCHAR2,
229 X_Jgzz_attribute10 VARCHAR2,
230 X_Jgzz_attribute11 VARCHAR2,
231 X_Jgzz_attribute12 VARCHAR2,
232 X_Jgzz_attribute13 VARCHAR2,
233 X_Jgzz_attribute14 VARCHAR2,
234 X_Jgzz_attribute15 VARCHAR2,
235 X_global_attribute_category VARCHAR2,
236 X_global_attribute1 VARCHAR2,
237 X_global_attribute2 VARCHAR2,
238 X_global_attribute3 VARCHAR2,
239 X_global_attribute4 VARCHAR2,
240 X_global_attribute5 VARCHAR2,
241 X_global_attribute6 VARCHAR2,
242 X_global_attribute7 VARCHAR2,
243 X_global_attribute8 VARCHAR2,
244 X_global_attribute9 VARCHAR2,
245 X_global_attribute10 VARCHAR2,
246 X_global_attribute11 VARCHAR2,
247 X_global_attribute12 VARCHAR2,
248 X_global_attribute13 VARCHAR2,
249 X_global_attribute14 VARCHAR2,
250 X_global_attribute15 VARCHAR2,
251 X_global_attribute16 VARCHAR2,
252 X_global_attribute17 VARCHAR2,
253 X_global_attribute18 VARCHAR2,
254 X_global_attribute19 VARCHAR2,
255 X_global_attribute20 VARCHAR2,
256 X_lockbox_matching_option VARCHAR2,
257 X_autocash_hierarchy_id_adr NUMBER,
258 --{ BUG 2310474
259 X_Review_Cycle VARCHAR2 DEFAULT NULL,
260 X_Credit_Classification VARCHAR2 DEFAULT NULL,
261 X_Next_Credit_Review_Date DATE DEFAULT NULL,
262 X_Last_Credit_Review_Date DATE DEFAULT NULL,
263 X_Credit_Analyst_id NUMBER DEFAULT NULL,
264 X_Party_id NUMBER DEFAULT NULL,
265 --}
266 x_return_status out NOCOPY varchar2,
267 x_msg_count out NOCOPY number,
268 x_msg_data out NOCOPY varchar2
269 )
270 IS
271 -- suse_rec hz_customer_accounts_pub.acct_site_uses_rec_type;
272 -- prof_rec hz_customer_accounts_pub.cust_profile_rec_type;
273 suse_rec hz_cust_account_site_v2pub.cust_site_use_rec_type;
274 prof_rec hz_customer_profile_v2pub.customer_profile_rec_type;
275
276 i_site_use_id NUMBER;
277 tmp_var VARCHAR2(2000);
278 i NUMBER;
279 tmp_var1 VARCHAR2(2000);
280 l_cust_account_profile_id NUMBER;
281 BEGIN
282
283 hzp_cprof_pkg.check_unique (
284 p_customer_id => x_customer_id,
285 p_site_use_id => x_site_use_id
286 );
287
288 --
289 -- The form may have already allocated on value to customer_profile_id
290 -- in order bt create profile ammounts from a prfile class
291 --
292 IF ( x_customer_profile_id is null ) THEN
293 --
294 SELECT hz_customer_profiles_s.nextval
295 INTO x_customer_profile_id
296 FROM dual;
297 --
298 END IF;
299 --
300 --
301 IF (X_Credit_Hold = 'Y') THEN
302 arh_cprof1_pkg.check_credit_hold(
303 p_customer_id => X_Customer_Id,
304 p_site_use_id => X_Site_Use_Id,
305 p_credit_hold => X_Credit_Hold);
306 END IF;
307 --
308 --
309 prof_rec.cust_account_profile_id := X_Customer_Profile_Id;
310 prof_rec.cust_account_id := X_Customer_Id;
311 prof_rec.status := x_status;
312 prof_rec.collector_id := x_collector_id;
313 --{2310474
314 prof_rec.credit_analyst_id := X_credit_analyst_id;
315 prof_rec.next_credit_review_date := x_next_credit_review_date;
316 --}
317 prof_rec.credit_checking := x_credit_checking;
318 prof_rec.tolerance := x_tolerance;
319 prof_rec.discount_terms := x_discount_terms;
320 prof_rec.dunning_letters := x_dunning_letters;
321 prof_rec.interest_charges := x_interest_charges;
322 prof_rec.send_statements := x_statements;
323 prof_rec.credit_balance_statements := x_credit_balance_statements;
324 prof_rec.credit_hold := x_credit_hold;
325 prof_rec.profile_class_id := X_Customer_Profile_Class_Id;
326 prof_rec.site_use_id := X_Site_Use_Id;
327 prof_rec.credit_rating := x_credit_rating;
328 prof_rec.risk_code := x_risk_code;
329 prof_rec.standard_terms := x_standard_terms;
330 prof_rec.override_terms := x_override_terms;
331 prof_rec.dunning_letter_set_id := x_dunning_letter_set_id;
332 prof_rec.interest_period_days := x_interest_period_days;
333 prof_rec.payment_grace_days := x_payment_grace_days;
334 prof_rec.discount_grace_days := x_discount_grace_days;
335 prof_rec.statement_cycle_id := x_statement_cycle_id;
336 prof_rec.account_status := x_account_status;
337 prof_rec.percent_collectable := x_percent_collectable;
338 prof_rec.autocash_hierarchy_id := x_autocash_hierarchy_id;
339 prof_rec.attribute_category := x_attribute_category;
340 prof_rec.attribute1 := x_attribute1;
341 prof_rec.attribute2 := x_attribute2;
342 prof_rec.attribute3 := x_attribute3;
343 prof_rec.attribute4 := x_attribute4;
344 prof_rec.attribute5 := x_attribute5;
345 prof_rec.attribute6 := x_attribute6;
346 prof_rec.attribute7 := x_attribute7;
347 prof_rec.attribute8 := x_attribute8;
348 prof_rec.attribute9 := x_attribute9;
349 prof_rec.attribute10 := x_attribute10;
350 prof_rec.attribute11 := x_attribute11;
351 prof_rec.attribute12 := x_attribute12;
352 prof_rec.attribute13 := x_attribute13;
353 prof_rec.attribute14 := x_attribute14;
354 prof_rec.attribute15 := x_attribute15;
355 prof_rec.auto_rec_incl_disputed_flag := x_auto_rec_incl_disputed_flag;
356 prof_rec.tax_printing_option := x_tax_printing_option;
357 prof_rec.charge_on_finance_charge_flag := x_charge_on_fin_charge_flag;
358 prof_rec.grouping_rule_id := x_grouping_rule_id;
359 prof_rec.clearing_days := x_clearing_days;
360 prof_rec.jgzz_attribute_category := x_jgzz_attribute_category;
361 prof_rec.jgzz_attribute1 := x_jgzz_attribute1;
362 prof_rec.jgzz_attribute2 := x_jgzz_attribute2;
363 prof_rec.jgzz_attribute3 := x_jgzz_attribute3;
364 prof_rec.jgzz_attribute4 := x_jgzz_attribute4;
365 prof_rec.jgzz_attribute5 := x_jgzz_attribute5;
366 prof_rec.jgzz_attribute6 := x_jgzz_attribute6;
367 prof_rec.jgzz_attribute7 := x_jgzz_attribute7;
368 prof_rec.jgzz_attribute8 := x_jgzz_attribute8;
369 prof_rec.jgzz_attribute9 := x_jgzz_attribute9;
370 prof_rec.jgzz_attribute10 := x_jgzz_attribute10;
371 prof_rec.jgzz_attribute11 := x_jgzz_attribute11;
372 prof_rec.jgzz_attribute12 := x_jgzz_attribute12;
373 prof_rec.jgzz_attribute13 := x_jgzz_attribute13;
374 prof_rec.jgzz_attribute14 := x_jgzz_attribute14;
375 prof_rec.jgzz_attribute15 := x_jgzz_attribute15;
376 prof_rec.global_attribute1 := x_global_attribute1;
377 prof_rec.global_attribute2 := x_global_attribute2;
378 prof_rec.global_attribute3 := x_global_attribute3;
379 prof_rec.global_attribute4 := x_global_attribute4;
380 prof_rec.global_attribute5 := x_global_attribute5;
381 prof_rec.global_attribute6 := x_global_attribute6;
382 prof_rec.global_attribute7 := x_global_attribute7;
383 prof_rec.global_attribute8 := x_global_attribute8;
384 prof_rec.global_attribute9 := x_global_attribute9;
385 prof_rec.global_attribute10 := x_global_attribute10;
386 prof_rec.global_attribute11 := x_global_attribute11;
387 prof_rec.global_attribute12 := x_global_attribute12;
388 prof_rec.global_attribute13 := x_global_attribute13;
389 prof_rec.global_attribute14 := x_global_attribute14;
390 prof_rec.global_attribute15 := x_global_attribute15;
391 prof_rec.global_attribute16 := x_global_attribute16;
392 prof_rec.global_attribute17 := x_global_attribute17;
393 prof_rec.global_attribute18 := x_global_attribute18;
394 prof_rec.global_attribute19 := x_global_attribute19;
395 prof_rec.global_attribute20 := x_global_attribute20;
396 prof_rec.global_attribute_category := x_global_attribute_category;
397 prof_rec.cons_inv_flag := x_cons_inv_flag;
398 prof_rec.cons_inv_type := x_cons_inv_type;
399 prof_rec.autocash_hierarchy_id_for_adr := X_autocash_hierarchy_id_adr;
400 prof_rec.lockbox_matching_option := x_lockbox_matching_option ;
401 --{2310474
402 prof_rec.review_cycle := x_review_cycle;
403 prof_rec.credit_classification := x_credit_classification;
404 prof_rec.last_credit_review_date := x_last_credit_review_date;
405 prof_rec.party_id := x_party_id;
406 --}
407
408 /* Commented for Bug No : 2134837
409 HZ_CUST_ACCOUNT_SITE_V2PUB.create_cust_site_use (
410 p_cust_site_use_rec => suse_rec,
411 p_customer_profile_rec => prof_rec,
412 p_create_profile => fnd_api.g_true,
413 p_create_profile_amt => fnd_api.g_false,
414 x_site_use_id => i_site_use_id,
415 x_return_status => x_return_status,
416 x_msg_count => x_msg_count,
417 x_msg_data => x_msg_data
418 );
419 */
420 prof_rec.created_by_module := 'TCA_FORM_WRAPPER';
421 HZ_CUSTOMER_PROFILE_V2PUB.create_customer_profile (
422 p_customer_profile_rec => prof_rec,
423 p_create_profile_amt => fnd_api.g_false,
424 x_cust_account_profile_id => l_cust_account_profile_id,
425 x_return_status => x_return_status,
426 x_msg_count => x_msg_count,
427 x_msg_data => x_msg_data
428 );
429
430 IF x_msg_count > 1 THEN
431 FOR i IN 1..x_msg_count LOOP
432 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
433 tmp_var1 := tmp_var1 || ' '|| tmp_var;
434 END LOOP;
435 x_msg_data := tmp_var1;
436 END IF;
437
438 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
439 return;
440 END IF;
441
442 END Insert_Row;
443
444
445 PROCEDURE insert_row(x_customer_id NUMBER,
446 x_site_use_id NUMBER,
447 x_customer_profile_class_id NUMBER,
448 x_party_id NUMBER DEFAULT NULL,
449 x_last_credit_review_date DATE DEFAULT NULL,
450 x_next_credit_review_date DATE DEFAULT NULL )
451 IS
452 --
453 --
454 l_customer_profile_id number;
455 x_cust_prof_rec HZ_CUSTOMER_PROFILE_V2PUB.customer_profile_rec_type;
456 --
457 CURSOR cu_class IS
458 SELECT *
459 FROM hz_cust_profile_classes
460 WHERE profile_class_id = x_customer_profile_class_id;
461
462 lrec hz_cust_profile_classes%ROWTYPE;
463 x_return_status VARCHAR2(10);
464 x_msg_count NUMBER;
465 x_msg_data VARCHAR2(2000);
466 tmp_var VARCHAR2(2000);
467 i number;
468 tmp_var1 VARCHAR2(2000);
469
470 BEGIN
471 --
472 OPEN cu_class;
473 FETCH cu_class INTO lrec;
474 CLOSE cu_class;
475 --
476 SELECT hz_customer_profiles_s.NEXTVAL
477 INTO l_customer_profile_id
478 FROM DUAL;
479 --
480 x_cust_prof_rec.cust_account_profile_id := l_customer_profile_id;
481 x_cust_prof_rec.cust_account_id := x_customer_id;
482 x_cust_prof_rec.site_use_id := x_site_use_id;
483 x_cust_prof_rec.collector_id := lrec.collector_id;
484 x_cust_prof_rec.credit_checking := lrec.credit_checking;
485 x_cust_prof_rec.tolerance := lrec.tolerance;
486 x_cust_prof_rec.tax_printing_option := lrec.tax_printing_option;
487 x_cust_prof_rec.discount_terms := lrec.discount_terms;
488 x_cust_prof_rec.dunning_letters := lrec.dunning_letters;
489 x_cust_prof_rec.interest_charges := lrec.interest_charges;
490 x_cust_prof_rec.send_statements := lrec.statements;
491 x_cust_prof_rec.credit_balance_statements:= lrec.credit_balance_statements;
492 x_cust_prof_rec.credit_hold := 'N';
493 x_cust_prof_rec.profile_class_id := lrec.profile_class_id;
494 x_cust_prof_rec.standard_terms := lrec.standard_terms;
495 x_cust_prof_rec.override_terms := lrec.override_terms;
496 x_cust_prof_rec.dunning_letter_set_id := lrec.dunning_letter_set_id;
497 x_cust_prof_rec.interest_period_days := lrec.interest_period_days;
498 x_cust_prof_rec.payment_grace_days := lrec.payment_grace_days;
499 x_cust_prof_rec.discount_grace_days := lrec.discount_grace_days;
500 x_cust_prof_rec.statement_cycle_id := lrec.statement_cycle_id;
501 x_cust_prof_rec.attribute_category := lrec.attribute_category;
502 x_cust_prof_rec.attribute1 := lrec.attribute1;
503 x_cust_prof_rec.attribute2 := lrec.attribute2;
504 x_cust_prof_rec.attribute3 := lrec.attribute3;
505 x_cust_prof_rec.attribute4 := lrec.attribute4;
506 x_cust_prof_rec.attribute5 := lrec.attribute5;
507 x_cust_prof_rec.attribute6 := lrec.attribute6;
508 x_cust_prof_rec.attribute7 := lrec.attribute7;
509 x_cust_prof_rec.attribute8 := lrec.attribute8;
510 x_cust_prof_rec.attribute9 := lrec.attribute9;
511 x_cust_prof_rec.attribute10 := lrec.attribute10;
512 x_cust_prof_rec.attribute11 := lrec.attribute11;
513 x_cust_prof_rec.attribute12 := lrec.attribute12;
514 x_cust_prof_rec.attribute13 := lrec.attribute13;
515 x_cust_prof_rec.attribute14 := lrec.attribute14;
516 x_cust_prof_rec.attribute15 := lrec.attribute15;
517 x_cust_prof_rec.jgzz_attribute_category := lrec.jgzz_attribute_category;
518 x_cust_prof_rec.jgzz_attribute1 := lrec.jgzz_attribute1;
519 x_cust_prof_rec.jgzz_attribute2 := lrec.jgzz_attribute2;
520 x_cust_prof_rec.jgzz_attribute3 := lrec.jgzz_attribute3;
521 x_cust_prof_rec.jgzz_attribute4 := lrec.jgzz_attribute4;
522 x_cust_prof_rec.jgzz_attribute5 := lrec.jgzz_attribute5;
523 x_cust_prof_rec.jgzz_attribute6 := lrec.jgzz_attribute6;
524 x_cust_prof_rec.jgzz_attribute7 := lrec.jgzz_attribute7;
525 x_cust_prof_rec.jgzz_attribute8 := lrec.jgzz_attribute8;
526 x_cust_prof_rec.jgzz_attribute9 := lrec.jgzz_attribute9;
527 x_cust_prof_rec.jgzz_attribute10 := lrec.jgzz_attribute10;
528 x_cust_prof_rec.jgzz_attribute11 := lrec.jgzz_attribute11;
529 x_cust_prof_rec.jgzz_attribute12 := lrec.jgzz_attribute12;
530 x_cust_prof_rec.jgzz_attribute13 := lrec.jgzz_attribute13;
531 x_cust_prof_rec.jgzz_attribute14 := lrec.jgzz_attribute14;
532 x_cust_prof_rec.jgzz_attribute15 := lrec.jgzz_attribute15;
533 x_cust_prof_rec.status := 'A';
534 x_cust_prof_rec.auto_rec_incl_disputed_flag := lrec.auto_rec_incl_disputed_flag;
535 x_cust_prof_rec.autocash_hierarchy_id := lrec.autocash_hierarchy_id;
536 x_cust_prof_rec.charge_on_finance_charge_flag := lrec.charge_on_finance_charge_flag;
537 x_cust_prof_rec.grouping_rule_id := lrec.grouping_rule_id;
538 x_cust_prof_rec.cons_inv_flag := lrec.cons_inv_flag;
539 x_cust_prof_rec.cons_inv_type := lrec.cons_inv_type;
540 x_cust_prof_rec.lockbox_matching_option := lrec.lockbox_matching_option;
541 x_cust_prof_rec.autocash_hierarchy_id_for_adr := lrec.autocash_hierarchy_id_for_adr;
542 x_cust_prof_rec.created_by_module := 'TCA_FORM_WRAPPER';
543 --{2310474
544 x_cust_prof_rec.review_cycle := lrec.review_cycle;
545 x_cust_prof_rec.credit_classification := lrec.credit_classification;
546 x_cust_prof_rec.credit_analyst_id := lrec.credit_analyst_id;
547 x_cust_prof_rec.next_credit_review_date := x_next_credit_review_date;
548 x_cust_prof_rec.last_credit_review_date := x_last_credit_review_date;
549 x_cust_prof_rec.party_id := x_party_id;
550 --}
551
552 HZ_CUSTOMER_PROFILE_V2PUB.create_customer_profile (
553 p_customer_profile_rec => x_cust_prof_rec,
554 x_cust_account_profile_id => l_customer_profile_id,
555 x_return_status => x_return_status,
556 x_msg_count => x_msg_count,
557 x_msg_data => x_msg_data );
558
559 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
560 IF x_msg_count > 1 THEN
561 FOR i IN 1..x_msg_count LOOP
562 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
563 tmp_var1 := tmp_var1 || ' '|| tmp_var;
564 END LOOP;
565 x_msg_data := tmp_var1;
566 END IF;
567 END IF;
568
569 END insert_row;
570 --
571 --
572 --
573 --
574 --
575 --
576 PROCEDURE Update_Row(X_Rowid VARCHAR2,
577 X_Customer_Profile_Id NUMBER,
578 X_Auto_Rec_Incl_Disputed_Flag VARCHAR2,
579 X_Collector_Id NUMBER,
580 X_Credit_Balance_Statements VARCHAR2,
581 X_Credit_Checking VARCHAR2,
582 X_Credit_Hold VARCHAR2,
583 X_Customer_Id NUMBER,
584 X_Discount_Terms VARCHAR2,
585 X_Dunning_Letters VARCHAR2,
586 X_Interest_Charges VARCHAR2,
587 X_Last_Updated_By NUMBER,
588 X_Last_Update_Date DATE,
589 X_Statements VARCHAR2,
590 X_Status VARCHAR2,
591 X_Tolerance NUMBER,
592 X_Tax_Printing_Option VARCHAR2,
593 X_Account_Status VARCHAR2,
594 X_Autocash_Hierarchy_Id NUMBER,
595 X_Credit_Rating VARCHAR2,
596 X_Customer_Profile_Class_Id NUMBER,
597 X_Discount_Grace_Days NUMBER,
598 X_Dunning_Letter_Set_Id NUMBER,
599 X_Interest_Period_Days NUMBER,
600 X_Last_Update_Login NUMBER,
601 X_Override_Terms VARCHAR2,
602 X_Payment_Grace_Days NUMBER,
603 X_Percent_Collectable NUMBER,
604 X_Risk_Code VARCHAR2,
605 X_Site_Use_Id NUMBER,
606 X_Standard_Terms NUMBER,
607 X_Statement_Cycle_Id NUMBER,
608 X_Attribute_Category VARCHAR2,
609 X_Attribute1 VARCHAR2,
610 X_Attribute2 VARCHAR2,
611 X_Attribute3 VARCHAR2,
612 X_Attribute4 VARCHAR2,
613 X_Attribute5 VARCHAR2,
614 X_Attribute6 VARCHAR2,
615 X_Attribute7 VARCHAR2,
616 X_Attribute8 VARCHAR2,
617 X_Attribute9 VARCHAR2,
618 X_Attribute10 VARCHAR2,
619 X_Attribute11 VARCHAR2,
620 X_Attribute12 VARCHAR2,
621 X_Attribute13 VARCHAR2,
622 X_Attribute14 VARCHAR2,
623 X_Attribute15 VARCHAR2,
624 X_Charge_On_Fin_Charge_Flag VARCHAR2,
625 X_Grouping_Rule_Id NUMBER,
626 X_Cons_Inv_Flag VARCHAR2,
627 X_Cons_Inv_Type VARCHAR2,
628 X_Clearing_Days NUMBER,
629 X_Jgzz_attribute_Category VARCHAR2,
630 X_Jgzz_attribute1 VARCHAR2,
631 X_Jgzz_attribute2 VARCHAR2,
632 X_Jgzz_attribute3 VARCHAR2,
633 X_Jgzz_attribute4 VARCHAR2,
634 X_Jgzz_attribute5 VARCHAR2,
635 X_Jgzz_attribute6 VARCHAR2,
636 X_Jgzz_attribute7 VARCHAR2,
637 X_Jgzz_attribute8 VARCHAR2,
638 X_Jgzz_attribute9 VARCHAR2,
639 X_Jgzz_attribute10 VARCHAR2,
640 X_Jgzz_attribute11 VARCHAR2,
641 X_Jgzz_attribute12 VARCHAR2,
642 X_Jgzz_attribute13 VARCHAR2,
643 X_Jgzz_attribute14 VARCHAR2,
644 X_Jgzz_attribute15 VARCHAR2,
645 X_global_attribute_category VARCHAR2,
646 X_global_attribute1 VARCHAR2,
647 X_global_attribute2 VARCHAR2,
648 X_global_attribute3 VARCHAR2,
649 X_global_attribute4 VARCHAR2,
650 X_global_attribute5 VARCHAR2,
651 X_global_attribute6 VARCHAR2,
652 X_global_attribute7 VARCHAR2,
653 X_global_attribute8 VARCHAR2,
654 X_global_attribute9 VARCHAR2,
655 X_global_attribute10 VARCHAR2,
656 X_global_attribute11 VARCHAR2,
657 X_global_attribute12 VARCHAR2,
658 X_global_attribute13 VARCHAR2,
659 X_global_attribute14 VARCHAR2,
660 X_global_attribute15 VARCHAR2,
661 X_global_attribute16 VARCHAR2,
662 X_global_attribute17 VARCHAR2,
663 X_global_attribute18 VARCHAR2,
664 X_global_attribute19 VARCHAR2,
665 X_global_attribute20 VARCHAR2,
666 X_lockbox_matching_option VARCHAR2,
667 X_autocash_hierarchy_id_adr NUMBER,
668 --{2310474
669 X_party_id NUMBER DEFAULT NULL,
670 X_review_cycle VARCHAR2 DEFAULT NULL,
671 X_credit_classification VARCHAR2 DEFAULT NULL,
672 X_credit_analyst_id NUMBER DEFAULT NULL,
673 X_last_credit_review_date DATE DEFAULT NULL,
674 X_next_credit_review_date DATE DEFAULT NULL,
675 --}
676 X_object_version IN NUMBER DEFAULT -1
677 ) IS
678 l_object_version NUMBER;
679 l_rowid ROWID;
680 x_msg_count NUMBER;
681 x_msg_data VARCHAR2(2000);
682 x_return_status VARCHAR2(10);
683 tmp_var VARCHAR2(2000);
684 i number;
685 tmp_var1 VARCHAR2(2000);
686 l_cp_rec HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
687 Exc_g EXCEPTION;
688 BEGIN
689 l_object_version := x_object_version;
690
691 IF l_object_version = -1 THEN
692
693 SELECT OBJECT_VERSION_NUMBER
694 INTO l_object_version
695 FROM HZ_CUSTOMER_PROFILES
696 WHERE ROWID = X_rowid;
697
698 IF (SQL%NOTFOUND) THEN
699 FND_MESSAGE.SET_NAME('AR','HZ_API_NO_RECORD');
700 FND_MESSAGE.SET_TOKEN('RECORD','HZ_CUSTOMER_PROFILES');
701 FND_MESSAGE.SET_TOKEN('ID',X_Customer_Profile_Id);
702 FND_MSG_PUB.ADD;
703
704 FND_MSG_PUB.Count_And_Get(
705 p_encoded => FND_API.G_FALSE,
706 p_count => x_msg_count,
707 p_data => x_msg_data );
708
709 IF x_msg_count > 1 THEN
710 FOR i IN 1..x_msg_count LOOP
711 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
712 tmp_var1 := tmp_var1 || ' '|| tmp_var;
713 END LOOP;
714 x_msg_data := SUBSTRB(tmp_var1,1,2000);
715 END IF;
716
717 Raise Exc_g;
718
719 END IF;
720
721 END IF;
722
723
724 arh_cprof1_pkg.check_credit_hold ( p_customer_id => x_customer_id,
725 p_site_use_id => x_site_use_id,
726 p_credit_hold => x_credit_hold
727 );
728 --
729 hzp_cprof_pkg.update_customer_alt_names( X_Rowid , X_Standard_Terms ,
730 X_Customer_Id , X_Site_Use_Id ) ;
731 --
732 l_cp_rec.cust_account_profile_id := INIT_SWITCH(X_Customer_Profile_Id);
733 l_cp_rec.auto_rec_incl_disputed_flag := INIT_SWITCH(X_Auto_Rec_Incl_Disputed_Flag);
734 l_cp_rec.collector_id := INIT_SWITCH(X_Collector_Id);
735 l_cp_rec.credit_balance_statements := INIT_SWITCH( X_Credit_Balance_Statements);
736 l_cp_rec.credit_checking := INIT_SWITCH( X_Credit_Checking);
737 l_cp_rec.credit_hold := INIT_SWITCH( X_Credit_Hold);
738 l_cp_rec.cust_account_id := INIT_SWITCH( X_Customer_Id);
739 l_cp_rec.discount_terms := INIT_SWITCH( X_Discount_Terms);
740 l_cp_rec.dunning_letters := INIT_SWITCH( X_Dunning_Letters);
741 l_cp_rec.interest_charges := INIT_SWITCH( X_Interest_Charges);
742 -- l_cp_rec.last_updated_by := INIT_SWITCH( X_Last_Updated_By);
743 -- l_cp_rec.last_update_date := INIT_SWITCH( X_Last_Update_Date);
744 l_cp_rec.send_statements := INIT_SWITCH( X_Statements);
745 l_cp_rec.status := INIT_SWITCH( X_Status);
746 l_cp_rec.tolerance := INIT_SWITCH( X_Tolerance);
747 l_cp_rec.tax_printing_option := INIT_SWITCH( X_Tax_Printing_Option);
748 l_cp_rec.account_status := INIT_SWITCH( X_Account_Status);
749 l_cp_rec.autocash_hierarchy_id := INIT_SWITCH( X_Autocash_Hierarchy_Id);
750 l_cp_rec.credit_rating := INIT_SWITCH( X_Credit_Rating);
751 l_cp_rec.profile_class_id := INIT_SWITCH( X_Customer_Profile_Class_Id);
752 l_cp_rec.discount_grace_days := INIT_SWITCH( X_Discount_Grace_Days);
753 l_cp_rec.dunning_letter_set_id := INIT_SWITCH( X_Dunning_Letter_Set_Id);
754 l_cp_rec.interest_period_days := INIT_SWITCH( X_Interest_Period_Days);
755 -- l_cp_rec.last_update_login := INIT_SWITCH( X_Last_Update_Login);
756 l_cp_rec.override_terms := INIT_SWITCH( X_Override_Terms);
757 l_cp_rec.payment_grace_days := INIT_SWITCH( X_Payment_Grace_Days);
758 l_cp_rec.percent_collectable := INIT_SWITCH( X_Percent_Collectable);
759 l_cp_rec.risk_code := INIT_SWITCH( X_Risk_Code);
760 l_cp_rec.site_use_id := INIT_SWITCH( X_Site_Use_Id);
761 l_cp_rec.standard_terms := INIT_SWITCH( X_Standard_Terms);
762 l_cp_rec.statement_cycle_id := INIT_SWITCH( X_Statement_Cycle_Id);
763 l_cp_rec.attribute_category := INIT_SWITCH( X_Attribute_Category);
764 l_cp_rec.attribute1 := INIT_SWITCH( X_Attribute1);
765 l_cp_rec.attribute2 := INIT_SWITCH( X_Attribute2);
766 l_cp_rec.attribute3 := INIT_SWITCH( X_Attribute3);
767 l_cp_rec.attribute4 := INIT_SWITCH( X_Attribute4);
768 l_cp_rec.attribute5 := INIT_SWITCH( X_Attribute5);
769 l_cp_rec.attribute6 := INIT_SWITCH( X_Attribute6);
770 l_cp_rec.attribute7 := INIT_SWITCH( X_Attribute7);
771 l_cp_rec.attribute8 := INIT_SWITCH( X_Attribute8);
772 l_cp_rec.attribute9 := INIT_SWITCH( X_Attribute9);
773 l_cp_rec.attribute10 := INIT_SWITCH( X_Attribute10);
774 l_cp_rec.attribute11 := INIT_SWITCH( X_Attribute11);
775 l_cp_rec.attribute12 := INIT_SWITCH( X_Attribute12);
776 l_cp_rec.attribute13 := INIT_SWITCH( X_Attribute13);
777 l_cp_rec.attribute14 := INIT_SWITCH( X_Attribute14);
778 l_cp_rec.attribute15 := INIT_SWITCH( X_Attribute15);
779 l_cp_rec.charge_on_finance_charge_flag := INIT_SWITCH( X_Charge_On_Fin_Charge_Flag);
780 l_cp_rec.grouping_rule_id := INIT_SWITCH( X_Grouping_Rule_Id);
781 l_cp_rec.cons_inv_flag := INIT_SWITCH( X_Cons_Inv_Flag);
782 l_cp_rec.cons_inv_type := INIT_SWITCH( X_Cons_Inv_Type);
783 l_cp_rec.clearing_days := INIT_SWITCH( X_Clearing_Days);
784 l_cp_rec.jgzz_attribute_category := INIT_SWITCH( X_Jgzz_attribute_Category);
785 l_cp_rec.jgzz_attribute1 := INIT_SWITCH( X_Jgzz_attribute1);
786 l_cp_rec.jgzz_attribute2 := INIT_SWITCH( X_Jgzz_attribute2);
787 l_cp_rec.jgzz_attribute3 := INIT_SWITCH( X_Jgzz_attribute3);
788 l_cp_rec.jgzz_attribute4 := INIT_SWITCH( X_Jgzz_attribute4);
789 l_cp_rec.jgzz_attribute5 := INIT_SWITCH( X_Jgzz_attribute5);
790 l_cp_rec.jgzz_attribute6 := INIT_SWITCH( X_Jgzz_attribute6);
791 l_cp_rec.jgzz_attribute7 := INIT_SWITCH( X_Jgzz_attribute7);
792 l_cp_rec.jgzz_attribute8 := INIT_SWITCH( X_Jgzz_attribute8);
793 l_cp_rec.jgzz_attribute9 := INIT_SWITCH( X_Jgzz_attribute9);
794 l_cp_rec.jgzz_attribute10 := INIT_SWITCH( X_Jgzz_attribute10);
795 l_cp_rec.jgzz_attribute11 := INIT_SWITCH( X_Jgzz_attribute11);
796 l_cp_rec.jgzz_attribute12 := INIT_SWITCH( X_Jgzz_attribute12);
797 l_cp_rec.jgzz_attribute13 := INIT_SWITCH( X_Jgzz_attribute13);
798 l_cp_rec.jgzz_attribute14 := INIT_SWITCH( X_Jgzz_attribute14);
799 l_cp_rec.jgzz_attribute15 := INIT_SWITCH( X_Jgzz_attribute15);
800 l_cp_rec.global_attribute_category := INIT_SWITCH( X_global_attribute_category);
801 l_cp_rec.global_attribute1 := INIT_SWITCH( X_global_attribute1);
802 l_cp_rec.global_attribute2 := INIT_SWITCH( X_global_attribute2);
803 l_cp_rec.global_attribute3 := INIT_SWITCH( X_global_attribute3);
804 l_cp_rec.global_attribute4 := INIT_SWITCH( X_global_attribute4);
805 l_cp_rec.global_attribute5 := INIT_SWITCH( X_global_attribute5);
806 l_cp_rec.global_attribute6 := INIT_SWITCH( X_global_attribute6);
807 l_cp_rec.global_attribute7 := INIT_SWITCH( X_global_attribute7);
808 l_cp_rec.global_attribute8 := INIT_SWITCH( X_global_attribute8);
809 l_cp_rec.global_attribute9 := INIT_SWITCH( X_global_attribute9);
810 l_cp_rec.global_attribute10 := INIT_SWITCH( X_global_attribute10);
811 l_cp_rec.global_attribute11 := INIT_SWITCH( X_global_attribute11);
812 l_cp_rec.global_attribute12 := INIT_SWITCH( X_global_attribute12);
813 l_cp_rec.global_attribute13 := INIT_SWITCH( X_global_attribute13);
814 l_cp_rec.global_attribute14 := INIT_SWITCH( X_global_attribute14);
815 l_cp_rec.global_attribute15 := INIT_SWITCH( X_global_attribute15);
816 l_cp_rec.global_attribute16 := INIT_SWITCH( X_global_attribute16);
817 l_cp_rec.global_attribute17 := INIT_SWITCH( X_global_attribute17);
818 l_cp_rec.global_attribute18 := INIT_SWITCH( X_global_attribute18);
819 l_cp_rec.global_attribute19 := INIT_SWITCH( X_global_attribute19);
820 l_cp_rec.global_attribute20 := INIT_SWITCH( X_global_attribute20);
821 l_cp_rec.lockbox_matching_option := INIT_SWITCH( X_lockbox_matching_option);
822 l_cp_rec.autocash_hierarchy_id_for_adr := INIT_SWITCH( X_autocash_hierarchy_id_adr);
823 l_cp_rec.created_by_module := 'TCA_FORM_WRAPPER';
824 --{2310474
825 l_cp_rec.party_id := X_party_id;
826 l_cp_rec.review_cycle := INIT_SWITCH( X_review_cycle);
827 l_cp_rec.credit_classification := INIT_SWITCH( X_credit_classification);
828 l_cp_rec.credit_analyst_id := INIT_SWITCH( X_credit_analyst_id);
829 l_cp_rec.next_credit_review_date := INIT_SWITCH( X_next_credit_review_date);
830 l_cp_rec.last_credit_review_date := INIT_SWITCH( X_last_credit_review_date);
831 --}
832
833 HZ_CUSTOMER_PROFILE_V2PUB.update_customer_profile (
834 p_customer_profile_rec => l_cp_rec,
835 p_object_version_number => l_object_version,
836 x_return_status => x_return_status,
837 x_msg_count => x_msg_count,
838 x_msg_data => x_msg_data );
839
840 IF x_msg_count > 1 THEN
841 FOR i IN 1..x_msg_count LOOP
842 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
843 tmp_var1 := tmp_var1 || ' '|| tmp_var;
844 END LOOP;
845 x_msg_data := tmp_var1;
846 END IF;
847
848 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
849 RAISE Exc_g;
850 END IF;
851
852 EXCEPTION
853 WHEN Exc_g THEN
854 RAISE_APPLICATION_ERROR(-20000,x_msg_data);
855 END Update_Row;
856 --
857 --
858 --
859 --
860 -- PROCEDURE
861 -- create_profile_from_class
862 --
863 -- DESCRIPTION
864 -- This procedure creates a customer profile from the customr_profile_class
865 -- It is designed to be called from the cust_prof|addr_prof blocks of the
866 -- enter customer form.
867 --
868 -- It returns all the profiles attributes to the form ans sliently
869 -- creates the rows in ar_customer_profile_amounts;
870 --
871 -- It is assume that the calling forms has no uncomitted rows for the
872 -- table hz_customer_profile_amounts.
873 --
874 -- SCOPE - PUBLIC
875 --
876 -- EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
877 --
878 -- ARGUMENTS : IN:
879 --
880 -- OUT:
881 -- None
882 --
883 -- RETURNS : NONE
884 --
885 -- NOTES
886 --
887 -- MODIFICATION HISTORY - Created by Kevin Hudson
888 --
889 -- Marijo Erickson 7/25/01 Bug 1591948: Remove the unnecessary fetching of
890 -- the "global attribute" fields to
891 -- accommodate globalization logic.
892 -- Marijo Erickson 1/29/02 Bug 2046191: revised to delete all prior currency
893 -- amounts to accurately reflect the
894 -- current profile class currencies
895 -- and their respective amounts.
896 -- Herve Yu 13/08/03 Bug 3046535: profile_amount can be either kept
897 -- or replaced
898 PROCEDURE create_profile_from_class
899 ( x_customer_profile_class_id in number,
900 x_customer_profile_id in out NOCOPY number,
901 x_customer_id in out NOCOPY number,
902 x_site_use_id in number,
903 x_collector_id out NOCOPY number,
904 x_collector_name out NOCOPY varchar2,
905 x_credit_checking out NOCOPY varchar2,
906 x_tolerance out NOCOPY number,
907 x_interest_charges out NOCOPY varchar2,
908 x_charge_on_fin_charge_flag out NOCOPY varchar2,
909 x_interest_period_days out NOCOPY number,
910 x_discount_terms out NOCOPY varchar2,
911 x_discount_grace_days out NOCOPY number,
912 x_statements out NOCOPY varchar2,
913 x_statement_cycle_id out NOCOPY number,
914 x_statement_cycle_name out NOCOPY varchar2,
915 x_credit_balance_statements out NOCOPY varchar2,
916 x_standard_terms out NOCOPY number,
917 x_standard_terms_name out NOCOPY varchar2,
918 x_override_terms out NOCOPY varchar2,
919 x_payment_grace_days out NOCOPY number,
920 x_dunning_letters out NOCOPY varchar2,
921 x_dunning_letter_set_id out NOCOPY number,
922 x_dunning_letter_set_name out NOCOPY varchar2,
923 x_autocash_hierarchy_id out NOCOPY number,
924 x_autocash_hierarchy_name out NOCOPY varchar2,
925 x_auto_rec_incl_disputed_flag out NOCOPY varchar2,
926 x_tax_printing_option out NOCOPY varchar2,
927 x_grouping_rule_id out NOCOPY number,
928 x_grouping_rule_name out NOCOPY varchar2,
929 x_cons_inv_flag out NOCOPY varchar2,
930 x_cons_inv_type out NOCOPY varchar2,
931 x_attribute_category out NOCOPY varchar2,
932 x_attribute1 out NOCOPY varchar2,
933 x_attribute2 out NOCOPY varchar2,
934 x_attribute3 out NOCOPY varchar2,
935 x_attribute4 out NOCOPY varchar2,
936 x_attribute5 out NOCOPY varchar2,
937 x_attribute6 out NOCOPY varchar2,
938 x_attribute7 out NOCOPY varchar2,
939 x_attribute8 out NOCOPY varchar2,
940 x_attribute9 out NOCOPY varchar2,
941 x_attribute10 out NOCOPY varchar2,
942 x_attribute11 out NOCOPY varchar2,
943 x_attribute12 out NOCOPY varchar2,
944 x_attribute13 out NOCOPY varchar2,
945 x_attribute14 out NOCOPY varchar2,
946 x_attribute15 out NOCOPY varchar2,
947 x_jgzz_attribute_category out NOCOPY varchar2,
948 x_jgzz_attribute1 out NOCOPY varchar2,
949 x_jgzz_attribute2 out NOCOPY varchar2,
950 x_jgzz_attribute3 out NOCOPY varchar2,
951 x_jgzz_attribute4 out NOCOPY varchar2,
952 x_jgzz_attribute5 out NOCOPY varchar2,
953 x_jgzz_attribute6 out NOCOPY varchar2,
954 x_jgzz_attribute7 out NOCOPY varchar2,
955 x_jgzz_attribute8 out NOCOPY varchar2,
956 x_jgzz_attribute9 out NOCOPY varchar2,
957 x_jgzz_attribute10 out NOCOPY varchar2,
958 x_jgzz_attribute11 out NOCOPY varchar2,
959 x_jgzz_attribute12 out NOCOPY varchar2,
960 x_jgzz_attribute13 out NOCOPY varchar2,
961 x_jgzz_attribute14 out NOCOPY varchar2,
962 x_jgzz_attribute15 out NOCOPY varchar2,
963 x_lockbox_matching_option out NOCOPY varchar2,
964 x_lockbox_matching_name out NOCOPY varchar2,
965 x_autocash_hierarchy_id_adr out NOCOPY number,
966 x_autocash_hierarchy_name_adr out NOCOPY varchar2,
967 x_return_status out NOCOPY varchar2,
968 x_msg_count out NOCOPY number,
969 x_msg_data out NOCOPY varchar2,
970 --{BUG#3046535
971 p_keep_replace IN VARCHAR2 DEFAULT 'KEEP'
972 --}
973 ) is
974
975 --
976 -- prof_amt_rec hz_customer_accounts_pub.cust_prof_amt_rec_type;
977 prof_amt_rec hz_customer_profile_v2pub.cust_profile_amt_rec_type;
978 x_cust_acct_profile_amt_id NUMBER;
979 old_customer_profile_id NUMBER; --Bug Fix#1: 3107081
980 tmp_var VARCHAR2(2000);
981 i NUMBER;
982 tmp_var1 VARCHAR2(2000);
983 CURSOR c_prof_class IS
984 SELECT collector_id,
985 collector_name,
986 credit_checking,
987 tolerance,
988 interest_charges,
989 charge_on_finance_charge_flag,
990 interest_period_days,
991 discount_terms,
992 discount_grace_days,
993 statements,
994 statement_cycle_id,
995 statement_cycle_name,
996 credit_balance_statements,
997 standard_terms,
998 standard_terms_name,
999 override_terms,
1000 payment_grace_days,
1001 dunning_letters,
1002 dunning_letter_set_id,
1003 dunning_letter_set_name,
1004 autocash_hierarchy_id,
1005 autocash_hierarchy_name,
1006 auto_rec_incl_disputed_flag,
1007 tax_printing_option,
1008 grouping_rule_id,
1009 grouping_rule_name,
1010 cons_inv_flag,
1011 cons_inv_type,
1012 attribute_category,
1013 attribute1,
1014 attribute2,
1015 attribute3,
1016 attribute4,
1017 attribute5,
1018 attribute6,
1019 attribute7,
1020 attribute8,
1021 attribute9,
1022 attribute10,
1023 attribute11,
1024 attribute12,
1025 attribute13,
1026 attribute14,
1027 attribute15,
1028 jgzz_attribute_category,
1029 jgzz_attribute1,
1030 jgzz_attribute2,
1031 jgzz_attribute3,
1032 jgzz_attribute4,
1033 jgzz_attribute5,
1034 jgzz_attribute6,
1035 jgzz_attribute7,
1036 jgzz_attribute8,
1037 jgzz_attribute9,
1038 jgzz_attribute10,
1039 jgzz_attribute11,
1040 jgzz_attribute12,
1041 jgzz_attribute13,
1042 jgzz_attribute14,
1043 jgzz_attribute15,
1044 lockbox_matching_option,
1045 lockbox_matching_option_name,
1046 autocash_hierarchy_id_for_adr,
1047 autocash_hierarchy_name_adr
1048 FROM AR_CUSTOMER_PROFILE_CLASSES_V
1049 -- from AR_CUST_PROF_CLASSES_TEST_V
1050 WHERE customer_profile_class_id = x_customer_profile_class_id;
1051
1052
1053 CURSOR c_prof_class_amts IS
1054 SELECT
1055 hz_cust_profile_amts_s.nextval,
1056 x_customer_profile_id,
1057 currency_code,
1058 trx_credit_limit,
1059 overall_credit_limit,
1060 min_dunning_amount,
1061 min_dunning_invoice_amount,
1062 max_interest_charge,
1063 min_statement_amount,
1064 auto_rec_min_receipt_amount,
1065 interest_rate,
1066 attribute_category,
1067 attribute1,
1068 attribute2,
1069 attribute3,
1070 attribute4,
1071 attribute5,
1072 attribute6,
1073 attribute7,
1074 attribute8,
1075 attribute9,
1076 attribute10,
1077 attribute11,
1078 attribute12,
1079 attribute13,
1080 attribute14,
1081 attribute15,
1082 min_fc_balance_amount,
1083 min_fc_invoice_amount,
1084 x_customer_id,
1085 x_site_use_id,
1086 jgzz_attribute_category,
1087 jgzz_attribute1,
1088 jgzz_attribute2,
1089 jgzz_attribute3,
1090 jgzz_attribute4,
1091 jgzz_attribute5,
1092 jgzz_attribute6,
1093 jgzz_attribute7,
1094 jgzz_attribute8,
1095 jgzz_attribute9,
1096 jgzz_attribute10,
1097 jgzz_attribute11,
1098 jgzz_attribute12,
1099 jgzz_attribute13,
1100 jgzz_attribute14,
1101 jgzz_attribute15
1102 FROM hz_cust_prof_class_amts
1103 WHERE profile_class_id = x_customer_profile_class_id;
1104
1105 --
1106
1107 CURSOR c_get_profile_amt_id(l_profile_class_id NUMBER, l_currency_code VARCHAR2) IS
1108 SELECT profile_class_amount_id
1109 FROM hz_cust_prof_class_amts
1110 WHERE profile_class_id = l_profile_class_id
1111 AND currency_code = l_currency_code;
1112
1113 l_profile_class_amount_id NUMBER;
1114 l_msg_count NUMBER;
1115 l_msg_data VARCHAR2(1000);
1116
1117
1118 --{bug#4084126
1119 CURSOR c_prof_class_id(p_customer_profile_id IN NUMBER)
1120 IS
1121 SELECT profile_class_id
1122 FROM hz_customer_profiles
1123 WHERE cust_account_profile_id = p_customer_profile_id;
1124
1125 l_curr_profile_class_id NUMBER;
1126 --}
1127
1128
1129
1130 BEGIN
1131 --
1132 --
1133 OPEN c_prof_class;
1134 FETCH c_prof_class
1135 INTO x_collector_id,
1136 x_collector_name,
1137 x_credit_checking,
1138 x_tolerance,
1139 x_interest_charges,
1140 x_charge_on_fin_charge_flag,
1141 x_interest_period_days,
1142 x_discount_terms,
1143 x_discount_grace_days,
1144 x_statements,
1145 x_statement_cycle_id,
1146 x_statement_cycle_name,
1147 x_credit_balance_statements,
1148 x_standard_terms,
1149 x_standard_terms_name,
1150 x_override_terms,
1151 x_payment_grace_days,
1152 x_dunning_letters,
1153 x_dunning_letter_set_id,
1154 x_dunning_letter_set_name,
1155 x_autocash_hierarchy_id,
1156 x_autocash_hierarchy_name,
1157 x_auto_rec_incl_disputed_flag,
1158 x_tax_printing_option,
1159 x_grouping_rule_id,
1160 x_grouping_rule_name,
1161 x_cons_inv_flag,
1162 x_cons_inv_type,
1163 x_attribute_category,
1164 x_attribute1,
1165 x_attribute2,
1166 x_attribute3,
1167 x_attribute4,
1168 x_attribute5,
1169 x_attribute6,
1170 x_attribute7,
1171 x_attribute8,
1172 x_attribute9,
1173 x_attribute10,
1174 x_attribute11,
1175 x_attribute12,
1176 x_attribute13,
1177 x_attribute14,
1178 x_attribute15,
1179 x_jgzz_attribute_category,
1180 x_jgzz_attribute1,
1181 x_jgzz_attribute2,
1182 x_jgzz_attribute3,
1183 x_jgzz_attribute4,
1184 x_jgzz_attribute5,
1185 x_jgzz_attribute6,
1186 x_jgzz_attribute7,
1187 x_jgzz_attribute8,
1188 x_jgzz_attribute9,
1189 x_jgzz_attribute10,
1190 x_jgzz_attribute11,
1191 x_jgzz_attribute12,
1192 x_jgzz_attribute13,
1193 x_jgzz_attribute14,
1194 x_jgzz_attribute15,
1195 x_lockbox_matching_option,
1196 x_lockbox_matching_name,
1197 x_autocash_hierarchy_id_adr,
1198 x_autocash_hierarchy_name_adr;
1199
1200 --
1201 IF (c_prof_class%NOTFOUND) THEN
1202 CLOSE c_prof_class;
1203 RAISE NO_DATA_FOUND;
1204 END IF;
1205 --
1206 CLOSE c_prof_class;
1207 --
1208 -- If the customer_profile_id/customers_id is null we need to
1209 -- generate one so we
1210 -- can insert rows into hz_cust_profile_amts.
1211 -- Customer_id wil be null when inserting a profile .
1212 -- and not null if they are updating an existing profile.
1213 --
1214 --
1215 IF ( x_customer_id IS NULL ) THEN
1216 SELECT hz_cust_accounts_s.NEXTVAL INTO x_customer_id FROM DUAL;
1217 END IF;
1218 --
1219 --
1220 IF (x_customer_profile_id is null ) THEN
1221 SELECT hz_customer_profiles_s.NEXTVAL INTO x_customer_profile_id FROM DUAL;
1222 END IF;
1223 --
1224 --
1225 --
1226 --
1227 -- Delete only the profile_amounts that match the currency that is
1228 -- present in the profile_amounts of the new customer profile class.
1229 --
1230 -- Bug 2046191: revised to delete all prior currency amounts to
1231 -- accurately reflect the current profile class currencies
1232 -- and their respective amounts.
1233 --
1234 --{BUG#3046535
1235 /********** Bug Fix Begin#2 : 3107081 ***********************/
1236 /**** Get the Site Level Customer Profile Id if a profile already exists ****/
1237
1238 BEGIN
1239 IF x_site_use_id IS NOT NULL THEN
1240 SELECT DISTINCT cust_account_profile_id
1241 INTO old_customer_profile_id
1242 FROM HZ_CUST_PROFILE_AMTS
1243 WHERE cust_account_id = x_customer_id
1244 AND site_use_id = x_site_use_id;
1245 END IF;
1246 EXCEPTION
1247 WHEN NO_DATA_FOUND THEN
1248 NULL;
1249 END;
1250
1251
1252 /**** In the WHERE Clause of the DELETE Statements present in the IF and ELSE parts,
1253 * Use old_customer_profile_id for Site Level Profile
1254 * Use x_customer_profile_id for Account Level Profile ****/
1255
1256 IF p_keep_replace = 'REPLACE' THEN
1257 DELETE FROM hz_cust_profile_amts
1258 WHERE cust_account_profile_id =
1259 NVL(old_customer_profile_id,x_customer_profile_id);
1260 ELSE
1261 DELETE FROM hz_cust_profile_amts
1262 WHERE cust_account_profile_id =
1263 NVL(old_customer_profile_id,x_customer_profile_id)
1264 AND currency_code in ( select currency_code
1265 from hz_cust_prof_class_amts
1266 where profile_class_id = x_customer_profile_class_id
1267 );
1268 END IF;
1269
1270
1271 --{bug#4084126
1272 IF x_customer_profile_id IS NOT NULL AND x_customer_profile_class_id IS NOT NULL THEN
1273 OPEN c_prof_class_id(x_customer_profile_id);
1274 FETCH c_prof_class_id INTO l_curr_profile_class_id;
1275 IF c_prof_class_id%FOUND THEN
1276 IF l_curr_profile_class_id <> x_customer_profile_class_id THEN
1277 UPDATE hz_customer_profiles
1278 SET profile_class_id = x_customer_profile_class_id
1279 WHERE cust_account_profile_id = x_customer_profile_id;
1280 END IF;
1281 END IF;
1282 END IF;
1283 --}
1284
1285
1286
1287
1288 /********** Bug Fix End#2 : 3107081 *************************/
1289
1290 /********** Commented out the following piece of code *******/
1291 /********** Bug Fix Begin#3 : 3107081 *************************
1292 IF p_keep_replace = 'REPLACE' THEN
1293 DELETE FROM hz_cust_profile_amts
1294 WHERE cust_account_profile_id = x_customer_profile_id;
1295
1296 ELSE
1297 DELETE FROM hz_cust_profile_amts
1298 WHERE cust_account_profile_id = x_customer_profile_id
1299 AND currency_code in ( select currency_code
1300 from hz_cust_prof_class_amts
1301 where profile_class_id = x_customer_profile_class_id
1302 );
1303 END IF;
1304 ********** Bug Fix End#3 : 3107081 *************************/
1305 --}
1306 --
1307 -- copy profile amount records from class to customer profile
1308 --
1309 OPEN c_prof_class_amts;
1310 LOOP
1311 FETCH c_prof_class_amts INTO
1312 prof_amt_rec.cust_acct_profile_amt_id,
1313 prof_amt_rec.cust_account_profile_id,
1314 prof_amt_rec.currency_code,
1315 prof_amt_rec.trx_credit_limit,
1316 prof_amt_rec.overall_credit_limit,
1317 prof_amt_rec.min_dunning_amount,
1318 prof_amt_rec.min_dunning_invoice_amount,
1319 prof_amt_rec.max_interest_charge,
1320 prof_amt_rec.min_statement_amount,
1321 prof_amt_rec.auto_rec_min_receipt_amount,
1322 prof_amt_rec.interest_rate,
1323 prof_amt_rec.attribute_category,
1324 prof_amt_rec.attribute1,
1325 prof_amt_rec.attribute2,
1326 prof_amt_rec.attribute3,
1327 prof_amt_rec.attribute4,
1328 prof_amt_rec.attribute5,
1329 prof_amt_rec.attribute6,
1330 prof_amt_rec.attribute7,
1331 prof_amt_rec.attribute8,
1332 prof_amt_rec.attribute9,
1333 prof_amt_rec.attribute10,
1334 prof_amt_rec.attribute11,
1335 prof_amt_rec.attribute12,
1336 prof_amt_rec.attribute13,
1337 prof_amt_rec.attribute14,
1338 prof_amt_rec.attribute15,
1339 prof_amt_rec.min_fc_balance_amount,
1340 prof_amt_rec.min_fc_invoice_amount,
1341 prof_amt_rec.cust_account_id,
1342 prof_amt_rec.site_use_id,
1343 prof_amt_rec.jgzz_attribute_category,
1344 prof_amt_rec.jgzz_attribute1,
1345 prof_amt_rec.jgzz_attribute2,
1346 prof_amt_rec.jgzz_attribute3,
1347 prof_amt_rec.jgzz_attribute4,
1348 prof_amt_rec.jgzz_attribute5,
1349 prof_amt_rec.jgzz_attribute6,
1350 prof_amt_rec.jgzz_attribute7,
1351 prof_amt_rec.jgzz_attribute8,
1352 prof_amt_rec.jgzz_attribute9,
1353 prof_amt_rec.jgzz_attribute10,
1354 prof_amt_rec.jgzz_attribute11,
1355 prof_amt_rec.jgzz_attribute12,
1356 prof_amt_rec.jgzz_attribute13,
1357 prof_amt_rec.jgzz_attribute14,
1358 prof_amt_rec.jgzz_attribute15;
1359 EXIT WHEN c_prof_class_amts%NOTFOUND;
1360
1361 prof_amt_rec.created_by_module := 'TCA_FORM_WRAPPER';
1362 HZ_CUSTOMER_PROFILE_V2PUB.create_cust_profile_amt (
1363 p_check_foreign_key => FND_API.G_FALSE,
1364 p_cust_profile_amt_rec => prof_amt_rec,
1365 x_cust_acct_profile_amt_id => x_cust_acct_profile_amt_id,
1366 x_return_status => x_return_status,
1367 x_msg_count => x_msg_count,
1368 x_msg_data => x_msg_data
1369 );
1370
1371 -- added as per bug 2219199 CASCADE PROFILE CLASS AMOUNTS WITH THE CREDIT USAGES - MULTI CURRENCY SETUP
1372 if (x_return_status = 'S') then
1373 open c_get_profile_amt_id(x_customer_profile_class_id, prof_amt_rec.currency_code);
1374 fetch c_get_profile_amt_id into l_profile_class_amount_id;
1375 close c_get_profile_amt_id;
1376 if (l_profile_class_amount_id is not null) then
1377 HZ_CREDIT_USAGES_CASCADE_PKG.cascade_credit_usage_rules (
1378 x_cust_acct_profile_amt_id,
1379 prof_amt_rec.cust_account_profile_id,
1380 l_profile_class_amount_id,
1381 x_customer_profile_class_id,
1382 x_return_status,
1383 l_msg_count,
1384 l_msg_data );
1385 end if;
1386 end if;
1387
1388
1389 END LOOP;
1390
1391 IF x_msg_count > 1 THEN
1392 FOR i IN 1..x_msg_count LOOP
1393 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1394 tmp_var1 := tmp_var1 || ' '|| tmp_var;
1395 END LOOP;
1396 x_msg_data := tmp_var1;
1397 END IF;
1398
1399 CLOSE c_prof_class_amts;
1400 --
1401 --
1402 END create_profile_from_class;
1403 --
1404 --
1405 --
1406
1407 -- Bug 2310474 Overload method
1408 PROCEDURE create_profile_from_class
1409 ( x_customer_profile_class_id in number,
1410 x_customer_profile_id in out NOCOPY number,
1411 x_customer_id in out NOCOPY number,
1412 x_site_use_id in number,
1413 x_collector_id out NOCOPY number,
1414 x_collector_name out NOCOPY varchar2,
1415 x_credit_checking out NOCOPY varchar2,
1416 x_tolerance out NOCOPY number,
1417 x_interest_charges out NOCOPY varchar2,
1418 x_charge_on_fin_charge_flag out NOCOPY varchar2,
1419 x_interest_period_days out NOCOPY number,
1420 x_discount_terms out NOCOPY varchar2,
1421 x_discount_grace_days out NOCOPY number,
1422 x_statements out NOCOPY varchar2,
1423 x_statement_cycle_id out NOCOPY number,
1424 x_statement_cycle_name out NOCOPY varchar2,
1425 x_credit_balance_statements out NOCOPY varchar2,
1426 x_standard_terms out NOCOPY number,
1427 x_standard_terms_name out NOCOPY varchar2,
1428 x_override_terms out NOCOPY varchar2,
1429 x_payment_grace_days out NOCOPY number,
1430 x_dunning_letters out NOCOPY varchar2,
1431 x_dunning_letter_set_id out NOCOPY number,
1432 x_dunning_letter_set_name out NOCOPY varchar2,
1433 x_autocash_hierarchy_id out NOCOPY number,
1434 x_autocash_hierarchy_name out NOCOPY varchar2,
1435 x_auto_rec_incl_disputed_flag out NOCOPY varchar2,
1436 x_tax_printing_option out NOCOPY varchar2,
1437 x_grouping_rule_id out NOCOPY number,
1438 x_grouping_rule_name out NOCOPY varchar2,
1439 x_cons_inv_flag out NOCOPY varchar2,
1440 x_cons_inv_type out NOCOPY varchar2,
1441 x_attribute_category out NOCOPY varchar2,
1442 x_attribute1 out NOCOPY varchar2,
1443 x_attribute2 out NOCOPY varchar2,
1444 x_attribute3 out NOCOPY varchar2,
1445 x_attribute4 out NOCOPY varchar2,
1446 x_attribute5 out NOCOPY varchar2,
1447 x_attribute6 out NOCOPY varchar2,
1448 x_attribute7 out NOCOPY varchar2,
1449 x_attribute8 out NOCOPY varchar2,
1450 x_attribute9 out NOCOPY varchar2,
1451 x_attribute10 out NOCOPY varchar2,
1452 x_attribute11 out NOCOPY varchar2,
1453 x_attribute12 out NOCOPY varchar2,
1454 x_attribute13 out NOCOPY varchar2,
1455 x_attribute14 out NOCOPY varchar2,
1456 x_attribute15 out NOCOPY varchar2,
1457 x_jgzz_attribute_category out NOCOPY varchar2,
1458 x_jgzz_attribute1 out NOCOPY varchar2,
1459 x_jgzz_attribute2 out NOCOPY varchar2,
1460 x_jgzz_attribute3 out NOCOPY varchar2,
1461 x_jgzz_attribute4 out NOCOPY varchar2,
1462 x_jgzz_attribute5 out NOCOPY varchar2,
1463 x_jgzz_attribute6 out NOCOPY varchar2,
1464 x_jgzz_attribute7 out NOCOPY varchar2,
1465 x_jgzz_attribute8 out NOCOPY varchar2,
1466 x_jgzz_attribute9 out NOCOPY varchar2,
1467 x_jgzz_attribute10 out NOCOPY varchar2,
1468 x_jgzz_attribute11 out NOCOPY varchar2,
1469 x_jgzz_attribute12 out NOCOPY varchar2,
1470 x_jgzz_attribute13 out NOCOPY varchar2,
1471 x_jgzz_attribute14 out NOCOPY varchar2,
1472 x_jgzz_attribute15 out NOCOPY varchar2,
1473 x_lockbox_matching_option out NOCOPY varchar2,
1474 x_lockbox_matching_name out NOCOPY varchar2,
1475 x_autocash_hierarchy_id_adr out NOCOPY number,
1476 x_autocash_hierarchy_name_adr out NOCOPY varchar2,
1477 --{2310474
1478 x_review_cycle out NOCOPY varchar2,
1479 x_credit_classification out NOCOPY varchar2,
1480 x_credit_classification_m out NOCOPY varchar2,
1481 x_review_cycle_name out NOCOPY varchar2,
1482 x_credit_analyst_id out NOCOPY number,
1483 x_credit_analyst_name out NOCOPY varchar2,
1484 --}
1485 x_return_status out NOCOPY varchar2,
1486 x_msg_count out NOCOPY number,
1487 x_msg_data out NOCOPY varchar2,
1488 --{Bug#3046535
1489 p_keep_replace IN VARCHAR2 DEFAULT 'KEEP'
1490 --}
1491 )
1492 IS
1493 prof_amt_rec hz_customer_profile_v2pub.cust_profile_amt_rec_type;
1494 x_cust_acct_profile_amt_id NUMBER;
1495 old_customer_profile_id NUMBER; --Bug Fix#4: 3107081
1496 tmp_var VARCHAR2(2000);
1497 i NUMBER;
1498 tmp_var1 VARCHAR2(2000);
1499
1500 CURSOR c_prof_class IS
1501 SELECT collector_id,
1502 collector_name,
1503 credit_checking,
1504 tolerance,
1505 interest_charges,
1506 charge_on_finance_charge_flag,
1507 interest_period_days,
1508 discount_terms,
1509 discount_grace_days,
1510 statements,
1511 statement_cycle_id,
1512 statement_cycle_name,
1513 credit_balance_statements,
1514 standard_terms,
1515 standard_terms_name,
1516 override_terms,
1517 payment_grace_days,
1518 dunning_letters,
1519 dunning_letter_set_id,
1520 dunning_letter_set_name,
1521 autocash_hierarchy_id,
1522 autocash_hierarchy_name,
1523 auto_rec_incl_disputed_flag,
1524 tax_printing_option,
1525 grouping_rule_id,
1526 grouping_rule_name,
1527 cons_inv_flag,
1528 cons_inv_type,
1529 attribute_category,
1530 attribute1,
1531 attribute2,
1532 attribute3,
1533 attribute4,
1534 attribute5,
1535 attribute6,
1536 attribute7,
1537 attribute8,
1538 attribute9,
1539 attribute10,
1540 attribute11,
1541 attribute12,
1542 attribute13,
1543 attribute14,
1544 attribute15,
1545 jgzz_attribute_category,
1546 jgzz_attribute1,
1547 jgzz_attribute2,
1548 jgzz_attribute3,
1549 jgzz_attribute4,
1550 jgzz_attribute5,
1551 jgzz_attribute6,
1552 jgzz_attribute7,
1553 jgzz_attribute8,
1554 jgzz_attribute9,
1555 jgzz_attribute10,
1556 jgzz_attribute11,
1557 jgzz_attribute12,
1558 jgzz_attribute13,
1559 jgzz_attribute14,
1560 jgzz_attribute15,
1561 lockbox_matching_option,
1562 lockbox_matching_option_name,
1563 autocash_hierarchy_id_for_adr,
1564 autocash_hierarchy_name_adr,
1565 --{2310474
1566 review_cycle,
1567 credit_classification,
1568 credit_classification_meaning,
1569 review_cycle_name,
1570 credit_analyst_id,
1571 credit_analyst_name
1572 --}
1573 FROM AR_CUSTOMER_PROFILE_CLASSES_V
1574 WHERE customer_profile_class_id = x_customer_profile_class_id;
1575
1576
1577 CURSOR c_prof_class_amts IS
1578 SELECT
1579 hz_cust_profile_amts_s.nextval,
1580 x_customer_profile_id,
1581 currency_code,
1582 trx_credit_limit,
1583 overall_credit_limit,
1584 min_dunning_amount,
1585 min_dunning_invoice_amount,
1586 max_interest_charge,
1587 min_statement_amount,
1588 auto_rec_min_receipt_amount,
1589 interest_rate,
1590 attribute_category,
1591 attribute1,
1592 attribute2,
1593 attribute3,
1594 attribute4,
1595 attribute5,
1596 attribute6,
1597 attribute7,
1598 attribute8,
1599 attribute9,
1600 attribute10,
1601 attribute11,
1602 attribute12,
1603 attribute13,
1604 attribute14,
1605 attribute15,
1606 min_fc_balance_amount,
1607 min_fc_invoice_amount,
1608 x_customer_id,
1609 x_site_use_id,
1610 jgzz_attribute_category,
1611 jgzz_attribute1,
1612 jgzz_attribute2,
1613 jgzz_attribute3,
1614 jgzz_attribute4,
1615 jgzz_attribute5,
1616 jgzz_attribute6,
1617 jgzz_attribute7,
1618 jgzz_attribute8,
1619 jgzz_attribute9,
1620 jgzz_attribute10,
1621 jgzz_attribute11,
1622 jgzz_attribute12,
1623 jgzz_attribute13,
1624 jgzz_attribute14,
1625 jgzz_attribute15
1626 FROM hz_cust_prof_class_amts
1627 WHERE profile_class_id = x_customer_profile_class_id;
1628
1629 CURSOR c_get_profile_amt_id(l_profile_class_id NUMBER, l_currency_code VARCHAR2) IS
1630 SELECT profile_class_amount_id
1631 FROM hz_cust_prof_class_amts
1632 WHERE profile_class_id = l_profile_class_id
1633 AND currency_code = l_currency_code;
1634 l_profile_class_amount_id NUMBER;
1635 l_msg_count NUMBER;
1636 l_msg_data VARCHAR2(1000);
1637
1638 --{bug#4084126
1639 CURSOR c_prof_class_id(p_customer_profile_id IN NUMBER)
1640 IS
1641 SELECT profile_class_id
1642 FROM hz_customer_profiles
1643 WHERE cust_account_profile_id = p_customer_profile_id;
1644
1645 l_curr_profile_class_id NUMBER;
1646 --}
1647
1648
1649
1650
1651 BEGIN
1652 OPEN c_prof_class;
1653 FETCH c_prof_class
1654 INTO x_collector_id,
1655 x_collector_name,
1656 x_credit_checking,
1657 x_tolerance,
1658 x_interest_charges,
1659 x_charge_on_fin_charge_flag,
1660 x_interest_period_days,
1661 x_discount_terms,
1662 x_discount_grace_days,
1663 x_statements,
1664 x_statement_cycle_id,
1665 x_statement_cycle_name,
1666 x_credit_balance_statements,
1667 x_standard_terms,
1668 x_standard_terms_name,
1669 x_override_terms,
1670 x_payment_grace_days,
1671 x_dunning_letters,
1672 x_dunning_letter_set_id,
1673 x_dunning_letter_set_name,
1674 x_autocash_hierarchy_id,
1675 x_autocash_hierarchy_name,
1676 x_auto_rec_incl_disputed_flag,
1677 x_tax_printing_option,
1678 x_grouping_rule_id,
1679 x_grouping_rule_name,
1680 x_cons_inv_flag,
1681 x_cons_inv_type,
1682 x_attribute_category,
1683 x_attribute1,
1684 x_attribute2,
1685 x_attribute3,
1686 x_attribute4,
1687 x_attribute5,
1688 x_attribute6,
1689 x_attribute7,
1690 x_attribute8,
1691 x_attribute9,
1692 x_attribute10,
1693 x_attribute11,
1694 x_attribute12,
1695 x_attribute13,
1696 x_attribute14,
1697 x_attribute15,
1698 x_jgzz_attribute_category,
1699 x_jgzz_attribute1,
1700 x_jgzz_attribute2,
1701 x_jgzz_attribute3,
1702 x_jgzz_attribute4,
1703 x_jgzz_attribute5,
1704 x_jgzz_attribute6,
1705 x_jgzz_attribute7,
1706 x_jgzz_attribute8,
1707 x_jgzz_attribute9,
1708 x_jgzz_attribute10,
1709 x_jgzz_attribute11,
1710 x_jgzz_attribute12,
1711 x_jgzz_attribute13,
1712 x_jgzz_attribute14,
1713 x_jgzz_attribute15,
1714 x_lockbox_matching_option,
1715 x_lockbox_matching_name,
1716 x_autocash_hierarchy_id_adr,
1717 x_autocash_hierarchy_name_adr,
1718 --{2310474
1719 x_review_cycle,
1720 x_credit_classification,
1721 x_credit_classification_m,
1722 x_review_cycle_name,
1723 x_credit_analyst_id,
1724 x_credit_analyst_name;
1725 --}
1726
1727 --
1728 IF (c_prof_class%NOTFOUND) THEN
1729 CLOSE c_prof_class;
1730 RAISE NO_DATA_FOUND;
1731 END IF;
1732 --
1733 CLOSE c_prof_class;
1734 --
1735 -- If the customer_profile_id/customers_id is null we need to
1736 -- generate one so we
1737 -- can insert rows into hz_cust_profile_amts.
1738 -- Customer_id wil be null when inserting a profile .
1739 -- and not null if they are updating an existing profile.
1740 --
1741 --
1742 IF ( x_customer_id IS NULL ) THEN
1743 SELECT hz_cust_accounts_s.NEXTVAL INTO x_customer_id FROM DUAL;
1744 END IF;
1745 --
1746 --
1747 IF (x_customer_profile_id is null ) THEN
1748 SELECT hz_customer_profiles_s.NEXTVAL INTO x_customer_profile_id FROM DUAL;
1749 END IF;
1750 --
1751 --
1752 --
1753 --
1754 -- Delete only the profile_amounts that match the currency that is
1755 -- present in the profile_amounts of the new customer profile class.
1756 --
1757 -- Bug 2046191: revised to delete all prior currency amounts to
1758 -- accurately reflect the current profile class currencies
1759 -- and their respective amounts.
1760 --
1761
1762 --{BUG#3046535
1763
1764 /********** Bug Fix Begin#5 : 3107081 *************************/
1765 /**** Get the Site Level Customer Profile Id if a profile already exists ****/
1766
1767 BEGIN
1768 IF x_site_use_id IS NOT NULL THEN
1769 SELECT DISTINCT cust_account_profile_id
1770 INTO old_customer_profile_id
1771 FROM HZ_CUST_PROFILE_AMTS
1772 WHERE cust_account_id = x_customer_id
1773 AND site_use_id = x_site_use_id;
1774 END IF;
1775 EXCEPTION
1776 WHEN NO_DATA_FOUND THEN
1777 NULL;
1778 END;
1779
1780 /**** In the WHERE Clause of the DELETE Statements present in the IF and ELSE parts,
1781 * Use old_customer_profile_id for Site Level Profile
1782 * Use x_customer_profile_id for Account Level Profile ****/
1783
1784 IF p_keep_replace = 'REPLACE' THEN
1785 DELETE FROM hz_cust_profile_amts
1786 WHERE cust_account_profile_id =
1787 NVL(old_customer_profile_id,x_customer_profile_id);
1788 ELSE
1789 DELETE FROM hz_cust_profile_amts
1790 WHERE cust_account_profile_id =
1791 NVL(old_customer_profile_id,x_customer_profile_id)
1792 AND currency_code in ( select currency_code
1793 from hz_cust_prof_class_amts
1794 where profile_class_id = x_customer_profile_class_id
1795 );
1796 END IF;
1797
1798 --{bug#4084126
1799 IF x_customer_profile_id IS NOT NULL AND x_customer_profile_class_id IS NOT NULL THEN
1800 OPEN c_prof_class_id(x_customer_profile_id);
1801 FETCH c_prof_class_id INTO l_curr_profile_class_id;
1802 IF c_prof_class_id%FOUND THEN
1803 IF l_curr_profile_class_id <> x_customer_profile_class_id THEN
1804 UPDATE hz_customer_profiles
1805 SET profile_class_id = x_customer_profile_class_id
1806 WHERE cust_account_profile_id = x_customer_profile_id;
1807 END IF;
1808 END IF;
1809 END IF;
1810 --}
1811
1812
1813
1814 /********** Bug Fix End#5 : 3107081 *************************/
1815
1816 /********** Commented out the following piece of code *******/
1817 /********** Bug Fix Begin#6 : 3107081 *************************
1818 IF p_keep_replace = 'REPLACE' THEN
1819 DELETE FROM hz_cust_profile_amts
1820 WHERE cust_account_profile_id = x_customer_profile_id;
1821
1822 ELSE
1823 DELETE FROM hz_cust_profile_amts
1824 WHERE cust_account_profile_id = x_customer_profile_id
1825 AND currency_code in ( select currency_code
1826 from hz_cust_prof_class_amts
1827 where profile_class_id = x_customer_profile_class_id
1828 );
1829 END IF;
1830 ********** Bug Fix End#6 : 3107081 *************************/
1831 --}
1832
1833 --
1834 -- copy profile amount records from class to customer profile
1835 --
1836 OPEN c_prof_class_amts;
1837 LOOP
1838 FETCH c_prof_class_amts INTO
1839 prof_amt_rec.cust_acct_profile_amt_id,
1840 prof_amt_rec.cust_account_profile_id,
1841 prof_amt_rec.currency_code,
1842 prof_amt_rec.trx_credit_limit,
1843 prof_amt_rec.overall_credit_limit,
1844 prof_amt_rec.min_dunning_amount,
1845 prof_amt_rec.min_dunning_invoice_amount,
1846 prof_amt_rec.max_interest_charge,
1847 prof_amt_rec.min_statement_amount,
1848 prof_amt_rec.auto_rec_min_receipt_amount,
1849 prof_amt_rec.interest_rate,
1850 prof_amt_rec.attribute_category,
1851 prof_amt_rec.attribute1,
1852 prof_amt_rec.attribute2,
1853 prof_amt_rec.attribute3,
1854 prof_amt_rec.attribute4,
1855 prof_amt_rec.attribute5,
1856 prof_amt_rec.attribute6,
1857 prof_amt_rec.attribute7,
1858 prof_amt_rec.attribute8,
1859 prof_amt_rec.attribute9,
1860 prof_amt_rec.attribute10,
1861 prof_amt_rec.attribute11,
1862 prof_amt_rec.attribute12,
1863 prof_amt_rec.attribute13,
1864 prof_amt_rec.attribute14,
1865 prof_amt_rec.attribute15,
1866 prof_amt_rec.min_fc_balance_amount,
1867 prof_amt_rec.min_fc_invoice_amount,
1868 prof_amt_rec.cust_account_id,
1869 prof_amt_rec.site_use_id,
1870 prof_amt_rec.jgzz_attribute_category,
1871 prof_amt_rec.jgzz_attribute1,
1872 prof_amt_rec.jgzz_attribute2,
1873 prof_amt_rec.jgzz_attribute3,
1874 prof_amt_rec.jgzz_attribute4,
1875 prof_amt_rec.jgzz_attribute5,
1876 prof_amt_rec.jgzz_attribute6,
1877 prof_amt_rec.jgzz_attribute7,
1878 prof_amt_rec.jgzz_attribute8,
1879 prof_amt_rec.jgzz_attribute9,
1880 prof_amt_rec.jgzz_attribute10,
1881 prof_amt_rec.jgzz_attribute11,
1882 prof_amt_rec.jgzz_attribute12,
1883 prof_amt_rec.jgzz_attribute13,
1884 prof_amt_rec.jgzz_attribute14,
1885 prof_amt_rec.jgzz_attribute15;
1886 EXIT WHEN c_prof_class_amts%NOTFOUND;
1887
1888 prof_amt_rec.created_by_module := 'TCA_FORM_WRAPPER';
1889 HZ_CUSTOMER_PROFILE_V2PUB.create_cust_profile_amt (
1890 p_check_foreign_key => FND_API.G_FALSE,
1891 p_cust_profile_amt_rec => prof_amt_rec,
1892 x_cust_acct_profile_amt_id => x_cust_acct_profile_amt_id,
1893 x_return_status => x_return_status,
1894 x_msg_count => x_msg_count,
1895 x_msg_data => x_msg_data
1896 );
1897
1898 -- added as per bug 2219199 CASCADE PROFILE CLASS AMOUNTS WITH THE CREDIT USAGES - MULTI CURRENCY SETUP
1899 if (x_return_status = 'S') then
1900 open c_get_profile_amt_id(x_customer_profile_class_id, prof_amt_rec.currency_code);
1901 fetch c_get_profile_amt_id into l_profile_class_amount_id;
1902 close c_get_profile_amt_id;
1903 if (l_profile_class_amount_id is not null) then
1904 HZ_CREDIT_USAGES_CASCADE_PKG.cascade_credit_usage_rules (
1905 x_cust_acct_profile_amt_id,
1906 prof_amt_rec.cust_account_profile_id,
1907 l_profile_class_amount_id,
1908 x_customer_profile_class_id,
1909 x_return_status,
1910 l_msg_count,
1911 l_msg_data );
1912 end if;
1913 end if;
1914
1915
1916 END LOOP;
1917
1918
1919
1920
1921
1922 IF x_msg_count > 1 THEN
1923 FOR i IN 1..x_msg_count LOOP
1924 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1925 tmp_var1 := tmp_var1 || ' '|| tmp_var;
1926 END LOOP;
1927 x_msg_data := tmp_var1;
1928 END IF;
1929
1930 CLOSE c_prof_class_amts;
1931 --
1932 --
1933 END create_profile_from_class;
1934
1935 /*---------------------------------------------------------------+
1936 | PROCEDURE : CREATE_PROFILE_AMOUNT |
1937 | |
1938 | PARAMETERS : |
1939 | Arguments in the record type |
1940 | Hz_Customer_Profile_V2pub.Cust_Profile_Amt_Rec_Type |
1941 | |
1942 | DESCRIPTION : |
1943 | From the arguments entered contruct the recors type and call |
1944 | Hz_Customer_Profile_V2pub.create_cust_profile_amt |
1945 | |
1946 | HISTORY : |
1947 | 12-DEC-2002 H. Yu Created |
1948 +---------------------------------------------------------------*/
1949 PROCEDURE create_profile_amount
1950 ( p_cust_account_profile_id IN NUMBER,
1951 p_currency_code IN VARCHAR2,
1952 p_trx_credit_limit IN NUMBER,
1953 p_overall_credit_limit IN NUMBER,
1954 p_min_dunning_amount IN NUMBER,
1955 p_min_dunning_invoice_amount IN NUMBER,
1956 p_max_interest_charge IN NUMBER,
1957 p_min_statement_amount IN NUMBER,
1958 p_auto_rec_min_receipt_amount IN NUMBER,
1959 p_interest_rate IN NUMBER,
1960 p_attribute_category IN VARCHAR2 DEFAULT NULL,
1961 p_attribute1 IN VARCHAR2 DEFAULT NULL,
1962 p_attribute2 IN VARCHAR2 DEFAULT NULL,
1963 p_attribute3 IN VARCHAR2 DEFAULT NULL,
1964 p_attribute4 IN VARCHAR2 DEFAULT NULL,
1965 p_attribute5 IN VARCHAR2 DEFAULT NULL,
1966 p_attribute6 IN VARCHAR2 DEFAULT NULL,
1967 p_attribute7 IN VARCHAR2 DEFAULT NULL,
1968 p_attribute8 IN VARCHAR2 DEFAULT NULL,
1969 p_attribute9 IN VARCHAR2 DEFAULT NULL,
1970 p_attribute10 IN VARCHAR2 DEFAULT NULL,
1971 p_attribute11 IN VARCHAR2 DEFAULT NULL,
1972 p_attribute12 IN VARCHAR2 DEFAULT NULL,
1973 p_attribute13 IN VARCHAR2 DEFAULT NULL,
1974 p_attribute14 IN VARCHAR2 DEFAULT NULL,
1975 p_attribute15 IN VARCHAR2 DEFAULT NULL,
1976 p_min_fc_balance_amount IN NUMBER,
1977 p_min_fc_invoice_amount IN NUMBER,
1978 p_cust_account_id IN NUMBER,
1979 p_site_use_id IN NUMBER,
1980 p_expiration_date IN DATE,
1981 p_jgzz_attribute_category IN VARCHAR2 DEFAULT NULL,
1982 p_jgzz_attribute1 IN VARCHAR2 DEFAULT NULL,
1983 p_jgzz_attribute2 IN VARCHAR2 DEFAULT NULL,
1984 p_jgzz_attribute3 IN VARCHAR2 DEFAULT NULL,
1985 p_jgzz_attribute4 IN VARCHAR2 DEFAULT NULL,
1986 p_jgzz_attribute5 IN VARCHAR2 DEFAULT NULL,
1987 p_jgzz_attribute6 IN VARCHAR2 DEFAULT NULL,
1988 p_jgzz_attribute7 IN VARCHAR2 DEFAULT NULL,
1989 p_jgzz_attribute8 IN VARCHAR2 DEFAULT NULL,
1990 p_jgzz_attribute9 IN VARCHAR2 DEFAULT NULL,
1991 p_jgzz_attribute10 IN VARCHAR2 DEFAULT NULL,
1992 p_jgzz_attribute11 IN VARCHAR2 DEFAULT NULL,
1993 p_jgzz_attribute12 IN VARCHAR2 DEFAULT NULL,
1994 p_jgzz_attribute13 IN VARCHAR2 DEFAULT NULL,
1995 p_jgzz_attribute14 IN VARCHAR2 DEFAULT NULL,
1996 p_jgzz_attribute15 IN VARCHAR2 DEFAULT NULL,
1997 p_global_attribute1 IN VARCHAR2 DEFAULT NULL,
1998 p_global_attribute2 IN VARCHAR2 DEFAULT NULL,
1999 p_global_attribute3 IN VARCHAR2 DEFAULT NULL,
2000 p_global_attribute4 IN VARCHAR2 DEFAULT NULL,
2001 p_global_attribute5 IN VARCHAR2 DEFAULT NULL,
2002 p_global_attribute6 IN VARCHAR2 DEFAULT NULL,
2003 p_global_attribute7 IN VARCHAR2 DEFAULT NULL,
2004 p_global_attribute8 IN VARCHAR2 DEFAULT NULL,
2005 p_global_attribute9 IN VARCHAR2 DEFAULT NULL,
2006 p_global_attribute10 IN VARCHAR2 DEFAULT NULL,
2007 p_global_attribute11 IN VARCHAR2 DEFAULT NULL,
2008 p_global_attribute12 IN VARCHAR2 DEFAULT NULL,
2009 p_global_attribute13 IN VARCHAR2 DEFAULT NULL,
2010 p_global_attribute14 IN VARCHAR2 DEFAULT NULL,
2011 p_global_attribute15 IN VARCHAR2 DEFAULT NULL,
2012 p_global_attribute16 IN VARCHAR2 DEFAULT NULL,
2013 p_global_attribute17 IN VARCHAR2 DEFAULT NULL,
2014 p_global_attribute18 IN VARCHAR2 DEFAULT NULL,
2015 p_global_attribute19 IN VARCHAR2 DEFAULT NULL,
2016 p_global_attribute20 IN VARCHAR2 DEFAULT NULL,
2017 p_global_attribute_category IN VARCHAR2 DEFAULT NULL,
2018 p_created_by_module IN VARCHAR2 DEFAULT 'TCA_FORM_WRAPPER',
2019 p_application_id IN NUMBER DEFAULT NULL,
2020 x_cust_acct_profile_amt_id OUT NOCOPY NUMBER,
2021 x_return_status OUT NOCOPY VARCHAR2,
2022 x_msg_count OUT NOCOPY NUMBER,
2023 x_msg_data OUT NOCOPY VARCHAR2 )
2024 IS
2025 l_rec Hz_Customer_Profile_V2pub.Cust_Profile_Amt_Rec_Type;
2026 l_cust_acct_profile_amt_id NUMBER;
2027 tmp_var VARCHAR2(2000);
2028 i NUMBER;
2029 tmp_var1 VARCHAR2(2000);
2030 BEGIN
2031 arp_standard.debug('create_profile_amount (+)');
2032 x_return_status := FND_API.G_RET_STS_SUCCESS;
2033
2034 l_rec.cust_acct_profile_amt_id := x_cust_acct_profile_amt_id;
2035 l_rec.cust_account_profile_id := p_cust_account_profile_id;
2036 l_rec.currency_code := p_currency_code;
2037 l_rec.trx_credit_limit := p_trx_credit_limit;
2038 l_rec.overall_credit_limit := p_overall_credit_limit;
2039 l_rec.min_dunning_amount := p_min_dunning_amount;
2040 l_rec.min_dunning_invoice_amount := p_min_dunning_invoice_amount;
2041 l_rec.max_interest_charge := p_max_interest_charge;
2042 l_rec.min_statement_amount := p_min_statement_amount;
2043 l_rec.auto_rec_min_receipt_amount := p_auto_rec_min_receipt_amount;
2044 l_rec.interest_rate := p_interest_rate;
2045 l_rec.attribute_category := p_attribute_category;
2046 l_rec.attribute1 := p_attribute1;
2047 l_rec.attribute2 := p_attribute2;
2048 l_rec.attribute3 := p_attribute3;
2049 l_rec.attribute4 := p_attribute4;
2050 l_rec.attribute5 := p_attribute5;
2051 l_rec.attribute6 := p_attribute6;
2052 l_rec.attribute7 := p_attribute7;
2053 l_rec.attribute8 := p_attribute8;
2054 l_rec.attribute9 := p_attribute9;
2055 l_rec.attribute10 := p_attribute10;
2056 l_rec.attribute11 := p_attribute11;
2057 l_rec.attribute12 := p_attribute12;
2058 l_rec.attribute13 := p_attribute13;
2059 l_rec.attribute14 := p_attribute14;
2060 l_rec.attribute15 := p_attribute15;
2061 l_rec.min_fc_balance_amount := p_min_fc_balance_amount;
2062 l_rec.min_fc_invoice_amount := p_min_fc_invoice_amount;
2063 l_rec.cust_account_id := p_cust_account_id;
2064 l_rec.site_use_id := p_site_use_id;
2065 l_rec.expiration_date := p_expiration_date;
2066 l_rec.jgzz_attribute_category := p_jgzz_attribute_category;
2067 l_rec.jgzz_attribute1 := p_jgzz_attribute1;
2068 l_rec.jgzz_attribute2 := p_jgzz_attribute2;
2069 l_rec.jgzz_attribute3 := p_jgzz_attribute3;
2070 l_rec.jgzz_attribute4 := p_jgzz_attribute4;
2071 l_rec.jgzz_attribute5 := p_jgzz_attribute5;
2072 l_rec.jgzz_attribute6 := p_jgzz_attribute6;
2073 l_rec.jgzz_attribute7 := p_jgzz_attribute7;
2074 l_rec.jgzz_attribute8 := p_jgzz_attribute8;
2075 l_rec.jgzz_attribute9 := p_jgzz_attribute9;
2076 l_rec.jgzz_attribute10 := p_jgzz_attribute10;
2077 l_rec.jgzz_attribute11 := p_jgzz_attribute11;
2078 l_rec.jgzz_attribute12 := p_jgzz_attribute12;
2079 l_rec.jgzz_attribute13 := p_jgzz_attribute13;
2080 l_rec.jgzz_attribute14 := p_jgzz_attribute14;
2081 l_rec.jgzz_attribute15 := p_jgzz_attribute15;
2082 l_rec.global_attribute1 := p_global_attribute1;
2083 l_rec.global_attribute2 := p_global_attribute2;
2084 l_rec.global_attribute3 := p_global_attribute3;
2085 l_rec.global_attribute4 := p_global_attribute4;
2086 l_rec.global_attribute5 := p_global_attribute5;
2087 l_rec.global_attribute6 := p_global_attribute6;
2088 l_rec.global_attribute7 := p_global_attribute7;
2089 l_rec.global_attribute8 := p_global_attribute8;
2090 l_rec.global_attribute9 := p_global_attribute9;
2091 l_rec.global_attribute10 := p_global_attribute10;
2092 l_rec.global_attribute11 := p_global_attribute11;
2093 l_rec.global_attribute12 := p_global_attribute12;
2094 l_rec.global_attribute13 := p_global_attribute13;
2095 l_rec.global_attribute14 := p_global_attribute14;
2096 l_rec.global_attribute15 := p_global_attribute15;
2097 l_rec.global_attribute16 := p_global_attribute16;
2098 l_rec.global_attribute17 := p_global_attribute17;
2099 l_rec.global_attribute18 := p_global_attribute18;
2100 l_rec.global_attribute19 := p_global_attribute19;
2101 l_rec.global_attribute20 := p_global_attribute20;
2102 l_rec.global_attribute_category := p_global_attribute_category;
2103 l_rec.created_by_module := p_created_by_module;
2104 l_rec.application_id := p_application_id;
2105
2106 HZ_CUSTOMER_PROFILE_V2PUB.create_cust_profile_amt (
2107 p_cust_profile_amt_rec => l_rec,
2108 x_cust_acct_profile_amt_id => x_cust_acct_profile_amt_id,
2109 x_return_status => x_return_status,
2110 x_msg_count => x_msg_count,
2111 x_msg_data => x_msg_data);
2112
2113 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2114 IF x_msg_count > 1 THEN
2115 FOR i IN 1..x_msg_count LOOP
2116 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
2117 tmp_var1 := tmp_var1 || ' '|| tmp_var;
2118 END LOOP;
2119 x_msg_data := tmp_var1;
2120 END IF;
2121 arp_standard.debug(x_msg_data);
2122 END IF;
2123
2124 arp_standard.debug('create_profile_amount(-)');
2125 EXCEPTION
2126 WHEN OTHERS THEN
2127 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2128 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
2129 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
2130 FND_MSG_PUB.ADD;
2131 FND_MSG_PUB.Count_And_Get(
2132 p_encoded => FND_API.G_FALSE,
2133 p_count => x_msg_count,
2134 p_data => x_msg_data );
2135 arp_standard.debug('EXCEPTION: hzp_cprof_pkg.delete_profile_amout'||x_msg_data);
2136 END;
2137
2138 /*---------------------------------------------------------------+
2139 | PROCEDURE : UPDATE_PROFILE_AMOUNT |
2140 | |
2141 | PARAMETERS : |
2142 | Arguments in the record type |
2143 | Hz_Customer_Profile_V2pub.Cust_Profile_Amt_Rec_Type |
2144 | |
2145 | DESCRIPTION : |
2146 | From the arguments entered contruct the recors type and call |
2147 | Hz_Customer_Profile_V2pub.update_cust_profile_amt |
2148 | |
2149 | HISTORY : |
2150 | 12-DEC-2002 H. Yu Created |
2151 +---------------------------------------------------------------*/
2152 PROCEDURE update_profile_amount
2153 ( p_cust_acct_profile_amt_id IN NUMBER,
2154 p_cust_account_profile_id IN NUMBER,
2155 p_currency_code IN VARCHAR2,
2156 p_trx_credit_limit IN NUMBER,
2157 p_overall_credit_limit IN NUMBER,
2158 p_min_dunning_amount IN NUMBER,
2159 p_min_dunning_invoice_amount IN NUMBER,
2160 p_max_interest_charge IN NUMBER,
2161 p_min_statement_amount IN NUMBER,
2162 p_auto_rec_min_receipt_amount IN NUMBER,
2163 p_interest_rate IN NUMBER,
2164 p_attribute_category IN VARCHAR2 DEFAULT NULL,
2165 p_attribute1 IN VARCHAR2 DEFAULT NULL,
2166 p_attribute2 IN VARCHAR2 DEFAULT NULL,
2167 p_attribute3 IN VARCHAR2 DEFAULT NULL,
2168 p_attribute4 IN VARCHAR2 DEFAULT NULL,
2169 p_attribute5 IN VARCHAR2 DEFAULT NULL,
2170 p_attribute6 IN VARCHAR2 DEFAULT NULL,
2171 p_attribute7 IN VARCHAR2 DEFAULT NULL,
2172 p_attribute8 IN VARCHAR2 DEFAULT NULL,
2173 p_attribute9 IN VARCHAR2 DEFAULT NULL,
2174 p_attribute10 IN VARCHAR2 DEFAULT NULL,
2175 p_attribute11 IN VARCHAR2 DEFAULT NULL,
2176 p_attribute12 IN VARCHAR2 DEFAULT NULL,
2177 p_attribute13 IN VARCHAR2 DEFAULT NULL,
2178 p_attribute14 IN VARCHAR2 DEFAULT NULL,
2179 p_attribute15 IN VARCHAR2 DEFAULT NULL,
2180 p_min_fc_balance_amount IN NUMBER,
2181 p_min_fc_invoice_amount IN NUMBER,
2182 p_cust_account_id IN NUMBER,
2183 p_site_use_id IN NUMBER,
2184 p_expiration_date IN DATE,
2185 p_jgzz_attribute_category IN VARCHAR2 DEFAULT NULL,
2186 p_jgzz_attribute1 IN VARCHAR2 DEFAULT NULL,
2187 p_jgzz_attribute2 IN VARCHAR2 DEFAULT NULL,
2188 p_jgzz_attribute3 IN VARCHAR2 DEFAULT NULL,
2189 p_jgzz_attribute4 IN VARCHAR2 DEFAULT NULL,
2190 p_jgzz_attribute5 IN VARCHAR2 DEFAULT NULL,
2191 p_jgzz_attribute6 IN VARCHAR2 DEFAULT NULL,
2192 p_jgzz_attribute7 IN VARCHAR2 DEFAULT NULL,
2193 p_jgzz_attribute8 IN VARCHAR2 DEFAULT NULL,
2194 p_jgzz_attribute9 IN VARCHAR2 DEFAULT NULL,
2195 p_jgzz_attribute10 IN VARCHAR2 DEFAULT NULL,
2196 p_jgzz_attribute11 IN VARCHAR2 DEFAULT NULL,
2197 p_jgzz_attribute12 IN VARCHAR2 DEFAULT NULL,
2198 p_jgzz_attribute13 IN VARCHAR2 DEFAULT NULL,
2199 p_jgzz_attribute14 IN VARCHAR2 DEFAULT NULL,
2200 p_jgzz_attribute15 IN VARCHAR2 DEFAULT NULL,
2201 p_global_attribute1 IN VARCHAR2 DEFAULT NULL,
2202 p_global_attribute2 IN VARCHAR2 DEFAULT NULL,
2203 p_global_attribute3 IN VARCHAR2 DEFAULT NULL,
2204 p_global_attribute4 IN VARCHAR2 DEFAULT NULL,
2205 p_global_attribute5 IN VARCHAR2 DEFAULT NULL,
2206 p_global_attribute6 IN VARCHAR2 DEFAULT NULL,
2207 p_global_attribute7 IN VARCHAR2 DEFAULT NULL,
2208 p_global_attribute8 IN VARCHAR2 DEFAULT NULL,
2209 p_global_attribute9 IN VARCHAR2 DEFAULT NULL,
2210 p_global_attribute10 IN VARCHAR2 DEFAULT NULL,
2211 p_global_attribute11 IN VARCHAR2 DEFAULT NULL,
2212 p_global_attribute12 IN VARCHAR2 DEFAULT NULL,
2213 p_global_attribute13 IN VARCHAR2 DEFAULT NULL,
2214 p_global_attribute14 IN VARCHAR2 DEFAULT NULL,
2215 p_global_attribute15 IN VARCHAR2 DEFAULT NULL,
2216 p_global_attribute16 IN VARCHAR2 DEFAULT NULL,
2217 p_global_attribute17 IN VARCHAR2 DEFAULT NULL,
2218 p_global_attribute18 IN VARCHAR2 DEFAULT NULL,
2219 p_global_attribute19 IN VARCHAR2 DEFAULT NULL,
2220 p_global_attribute20 IN VARCHAR2 DEFAULT NULL,
2221 p_global_attribute_category IN VARCHAR2 DEFAULT NULL,
2222 x_return_status OUT NOCOPY VARCHAR2,
2223 x_msg_count OUT NOCOPY NUMBER,
2224 x_msg_data OUT NOCOPY VARCHAR2,
2225 p_object_version IN NUMBER DEFAULT -1,
2226 X_Last_Update_Date IN DATE)
2227 IS
2228 CURSOR cu_version_prof_amt IS
2229 SELECT ROWID,
2230 OBJECT_VERSION_NUMBER,
2231 LAST_UPDATE_DATE
2232 FROM hz_cust_profile_amts
2233 WHERE cust_acct_profile_amt_id = p_cust_acct_profile_amt_id;
2234
2235 l_rec Hz_Customer_Profile_V2pub.Cust_Profile_Amt_Rec_Type;
2236 l_cust_acct_profile_amt_id NUMBER;
2237 tmp_var VARCHAR2(2000);
2238 i NUMBER;
2239 tmp_var1 VARCHAR2(2000);
2240 l_object_version NUMBER;
2241 l_rowid ROWID;
2242 l_last_update_date DATE;
2243 l_exception EXCEPTION;
2244 tca_exception EXCEPTION;
2245 BEGIN
2246 arp_standard.debug('update_profile_amount (+)');
2247 arp_standard.debug('Object_version_number:'||p_object_version);
2248 arp_standard.debug('last_update_date:'||X_Last_Update_Date );
2249
2250 x_return_status := FND_API.G_RET_STS_SUCCESS;
2251
2252 l_rec.cust_acct_profile_amt_id := INIT_SWITCH(p_cust_acct_profile_amt_id);
2253 l_rec.cust_account_profile_id := INIT_SWITCH(p_cust_account_profile_id);
2254 l_rec.currency_code := INIT_SWITCH(p_currency_code);
2255 l_rec.trx_credit_limit := INIT_SWITCH(p_trx_credit_limit);
2256 l_rec.overall_credit_limit := INIT_SWITCH(p_overall_credit_limit);
2257 l_rec.min_dunning_amount := INIT_SWITCH(p_min_dunning_amount);
2258 l_rec.min_dunning_invoice_amount := INIT_SWITCH(p_min_dunning_invoice_amount);
2259 l_rec.max_interest_charge := INIT_SWITCH(p_max_interest_charge);
2260 l_rec.min_statement_amount := INIT_SWITCH(p_min_statement_amount);
2261 l_rec.auto_rec_min_receipt_amount := INIT_SWITCH(p_auto_rec_min_receipt_amount);
2262 l_rec.interest_rate := INIT_SWITCH(p_interest_rate);
2263 l_rec.attribute_category := INIT_SWITCH(p_attribute_category);
2264 l_rec.attribute1 := INIT_SWITCH(p_attribute1);
2265 l_rec.attribute2 := INIT_SWITCH(p_attribute2);
2266 l_rec.attribute3 := INIT_SWITCH(p_attribute3);
2267 l_rec.attribute4 := INIT_SWITCH(p_attribute4);
2268 l_rec.attribute5 := INIT_SWITCH(p_attribute5);
2269 l_rec.attribute6 := INIT_SWITCH(p_attribute6);
2270 l_rec.attribute7 := INIT_SWITCH(p_attribute7);
2271 l_rec.attribute8 := INIT_SWITCH(p_attribute8);
2272 l_rec.attribute9 := INIT_SWITCH(p_attribute9);
2273 l_rec.attribute10 := INIT_SWITCH(p_attribute10);
2274 l_rec.attribute11 := INIT_SWITCH(p_attribute11);
2275 l_rec.attribute12 := INIT_SWITCH(p_attribute12);
2276 l_rec.attribute13 := INIT_SWITCH(p_attribute13);
2277 l_rec.attribute14 := INIT_SWITCH(p_attribute14);
2278 l_rec.attribute15 := INIT_SWITCH(p_attribute15);
2279 l_rec.min_fc_balance_amount := INIT_SWITCH(p_min_fc_balance_amount);
2280 l_rec.min_fc_invoice_amount := INIT_SWITCH(p_min_fc_invoice_amount);
2281 l_rec.cust_account_id := INIT_SWITCH(p_cust_account_id);
2282 l_rec.site_use_id := INIT_SWITCH(p_site_use_id);
2283 l_rec.expiration_date := INIT_SWITCH(p_expiration_date);
2284 l_rec.jgzz_attribute_category := INIT_SWITCH(p_jgzz_attribute_category);
2285 l_rec.jgzz_attribute1 := INIT_SWITCH(p_jgzz_attribute1);
2286 l_rec.jgzz_attribute2 := INIT_SWITCH(p_jgzz_attribute2);
2287 l_rec.jgzz_attribute3 := INIT_SWITCH(p_jgzz_attribute3);
2288 l_rec.jgzz_attribute4 := INIT_SWITCH(p_jgzz_attribute4);
2289 l_rec.jgzz_attribute5 := INIT_SWITCH(p_jgzz_attribute5);
2290 l_rec.jgzz_attribute6 := INIT_SWITCH(p_jgzz_attribute6);
2291 l_rec.jgzz_attribute7 := INIT_SWITCH(p_jgzz_attribute7);
2292 l_rec.jgzz_attribute8 := INIT_SWITCH(p_jgzz_attribute8);
2293 l_rec.jgzz_attribute9 := INIT_SWITCH(p_jgzz_attribute9);
2294 l_rec.jgzz_attribute10 := INIT_SWITCH(p_jgzz_attribute10);
2295 l_rec.jgzz_attribute11 := INIT_SWITCH(p_jgzz_attribute11);
2296 l_rec.jgzz_attribute12 := INIT_SWITCH(p_jgzz_attribute12);
2297 l_rec.jgzz_attribute13 := INIT_SWITCH(p_jgzz_attribute13);
2298 l_rec.jgzz_attribute14 := INIT_SWITCH(p_jgzz_attribute14);
2299 l_rec.jgzz_attribute15 := INIT_SWITCH(p_jgzz_attribute15);
2300 l_rec.global_attribute1 := INIT_SWITCH(p_global_attribute1);
2301 l_rec.global_attribute2 := INIT_SWITCH(p_global_attribute2);
2302 l_rec.global_attribute3 := INIT_SWITCH(p_global_attribute3);
2303 l_rec.global_attribute4 := INIT_SWITCH(p_global_attribute4);
2304 l_rec.global_attribute5 := INIT_SWITCH(p_global_attribute5);
2305 l_rec.global_attribute6 := INIT_SWITCH(p_global_attribute6);
2306 l_rec.global_attribute7 := INIT_SWITCH(p_global_attribute7);
2307 l_rec.global_attribute8 := INIT_SWITCH(p_global_attribute8);
2308 l_rec.global_attribute9 := INIT_SWITCH(p_global_attribute9);
2309 l_rec.global_attribute10 := INIT_SWITCH(p_global_attribute10);
2310 l_rec.global_attribute11 := INIT_SWITCH(p_global_attribute11);
2311 l_rec.global_attribute12 := INIT_SWITCH(p_global_attribute12);
2312 l_rec.global_attribute13 := INIT_SWITCH(p_global_attribute13);
2313 l_rec.global_attribute14 := INIT_SWITCH(p_global_attribute14);
2314 l_rec.global_attribute15 := INIT_SWITCH(p_global_attribute15);
2315 l_rec.global_attribute16 := INIT_SWITCH(p_global_attribute16);
2316 l_rec.global_attribute17 := INIT_SWITCH(p_global_attribute17);
2317 l_rec.global_attribute18 := INIT_SWITCH(p_global_attribute18);
2318 l_rec.global_attribute19 := INIT_SWITCH(p_global_attribute19);
2319 l_rec.global_attribute20 := INIT_SWITCH(p_global_attribute20);
2320 l_rec.global_attribute_category := INIT_SWITCH(p_global_attribute_category);
2321
2322 l_object_version := p_object_version;
2323
2324 IF (l_object_version = -1 OR l_object_version IS NULL) THEN
2325
2326 OPEN cu_version_prof_amt;
2327 FETCH cu_version_prof_amt INTO l_rowid,
2328 l_object_version,
2329 l_last_update_date;
2330 arp_standard.debug('Last_update_date:'||to_char(l_last_update_date));
2331
2332 IF cu_version_prof_amt%NOTFOUND THEN
2333 FND_MESSAGE.SET_NAME('AR','HZ_API_NO_RECORD');
2334 FND_MESSAGE.SET_TOKEN('RECORD','HZ_CUST_PROFILE_AMTS');
2335 FND_MESSAGE.SET_TOKEN('ID',p_cust_acct_profile_amt_id);
2336 FND_MSG_PUB.ADD;
2337 RAISE l_exception;
2338 END IF;
2339
2340 CLOSE cu_version_prof_amt;
2341
2342 END IF;
2343
2344 arp_standard.debug('x_return_status :'||x_return_status);
2345
2346 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2347
2348 HZ_CUSTOMER_PROFILE_V2PUB.update_cust_profile_amt
2349 ( p_cust_profile_amt_rec => l_rec,
2350 p_object_version_number => l_object_version,
2351 x_return_status => x_return_status,
2352 x_msg_count => x_msg_count,
2353 x_msg_data => x_msg_data);
2354
2355 END IF;
2356
2357 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2358 RAISE tca_exception;
2359 END IF;
2360
2361 arp_standard.debug('update_profile_amount (-)');
2362 EXCEPTION
2363 WHEN l_exception THEN
2364 x_return_status := FND_API.G_RET_STS_ERROR;
2365 fnd_msg_pub.count_and_get(
2366 p_encoded => fnd_api.g_false,
2367 p_count => x_msg_count,
2368 p_data => x_msg_data);
2369 IF x_msg_count > 1 THEN
2370 FOR i IN 1..x_msg_count LOOP
2371 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
2372 tmp_var1 := tmp_var1 || ' '|| tmp_var;
2373 END LOOP;
2374 x_msg_data := tmp_var1;
2375 END IF;
2376 arp_standard.debug('Exception hzp_cprof_pkg.update_profile_amount:'||x_msg_data);
2377
2378 WHEN tca_Exception THEN
2379 IF x_msg_count > 1 THEN
2380 FOR i IN 1..x_msg_count LOOP
2381 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
2382 tmp_var1 := tmp_var1 || ' '|| tmp_var;
2383 END LOOP;
2384 x_msg_data := tmp_var1;
2385 END IF;
2386 arp_standard.debug('Exception hzp_cprof_pkg.update_profile_amount:'||x_msg_data);
2387
2388 WHEN OTHERS THEN
2389 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2390 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
2391 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
2392 FND_MSG_PUB.ADD;
2393 FND_MSG_PUB.Count_And_Get(
2394 p_encoded => FND_API.G_FALSE,
2395 p_count => x_msg_count,
2396 p_data => x_msg_data );
2397 arp_standard.debug('EXCEPTION OTHERS: hzp_cprof_pkg.update_profile_amout'||x_msg_data);
2398 END;
2399
2400
2401 /*---------------------------------------------------------------+
2402 | PROCEDURE : DELETE_PROFILE_AMOUNT |
2403 | |
2404 | PARAMETERS : |
2405 | NUMBER Customer_profile_amt_id |
2406 | |
2407 | DESCRIPTION : |
2408 | Delete a record from Hz_Cust_Profile_Amts |
2409 | |
2410 | HISTORY : |
2411 | 12-DEC-2002 H. Yu Created |
2412 +---------------------------------------------------------------*/
2413 PROCEDURE delete_profile_amount
2414 (p_cust_acct_profile_amt_id IN NUMBER,
2415 x_return_status OUT NOCOPY VARCHAR2,
2416 x_msg_count OUT NOCOPY NUMBER,
2417 x_msg_data OUT NOCOPY VARCHAR2)
2418 IS
2419 BEGIN
2420 arp_standard.debug('delete_profile_amount(+)');
2421
2422 x_return_status := FND_API.G_RET_STS_SUCCESS;
2423
2424 DELETE FROM hz_cust_profile_amts
2425 WHERE cust_acct_profile_amt_id = p_cust_acct_profile_amt_id;
2426
2427 IF (SQL%NOTFOUND) THEN
2428 Raise NO_DATA_FOUND;
2429 END IF;
2430
2431 arp_standard.debug('delete_profile_amount(-)');
2432
2433 EXCEPTION
2434 WHEN NO_DATA_FOUND THEN
2435 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2436 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
2437 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
2438 FND_MSG_PUB.ADD;
2439 FND_MSG_PUB.Count_And_Get(
2440 p_encoded => FND_API.G_FALSE,
2441 p_count => x_msg_count,
2442 p_data => x_msg_data );
2443 arp_standard.debug('EXCEPTION NO_DATA_FOUND: hzp_cprof_pkg.delete_profile_amout');
2444
2445 WHEN OTHERS THEN
2446 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2447 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
2448 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
2449 FND_MSG_PUB.ADD;
2450 FND_MSG_PUB.Count_And_Get(
2451 p_encoded => FND_API.G_FALSE,
2452 p_count => x_msg_count,
2453 p_data => x_msg_data );
2454 arp_standard.debug('EXCEPTION OTHERS: hzp_cprof_pkg.delete_profile_amout'||x_msg_data);
2455 END;
2456
2457 END hzp_cprof_pkg;