[Home] [Help]
PACKAGE BODY: APPS.HZ_CREDIT_USAGES_PKG
Source
1 PACKAGE BODY HZ_CREDIT_USAGES_PKG AS
2 /* $Header: ARHUSAGB.pls 115.10 2004/02/03 22:47:44 bsarkar ship $ */
3
4
5 ----------Globals
6 G_ORG_ID NUMBER:= FND_PROFILE.value('ORG_ID') ;
7
8 ---------------------------
9 -- PROCEDURES AND FUNCTIONS
10 ---------------------------
11 --========================================================================
12 -- FUNCTION : Check_release PUBLIC
13 -- PARAMETERS:
14
15 -- COMMENT : Function returns 'OLD' if AR Credit Management is not
16 -- installed or not active and returns 'NEW' if AR Credit
17 -- Management is installed and active
18 --========================================================================
19 FUNCTION Check_release
20 RETURN VARCHAR2
21 IS
22 l_count NUMBER;
23 l_result VARCHAR2(3);
24
25 BEGIN
26
27 IF AR_CMGT_CREDIT_REQUEST_API.is_Credit_Management_Installed
28 = TRUE
29 THEN
30 l_result := 'NEW';
31 ELSE
32 l_result := 'OLD';
33 END IF;
34
35 RETURN (l_result);
36
37 END Check_release;
38
39 --========================================================================
40 -- PROCEDURE : Insert_row PUBLIC
41 -- PARAMETERS: p_row_id ROWID of the current record
42 -- p_credit_usage_rule_set_id rule set id
43 -- p_credit_usage_id primary key
44 -- p_credit_profile_amt_id credit_profile_amt_id
45 -- p_cust_acct_profile_amt_id cust_acct_profile_amt_id
46 -- p_profile_class_amt_id profile_class_amt_id
47 -- p_creation_date date, when a record was inserted
48 -- p_created_by userid of the person,who inserted
49 -- a record
50 -- p_last_update_date date, when a record was inserted
51 -- p_last_updated_by userid of the person,who inserted
52 -- a record
53 -- p_last_update_login login of the person,who inserted
54 -- a record
55
56 -- COMMENT : Procedure inserts record into the table HZ_CREDIT_USAGES
57 --========================================================================
58 PROCEDURE Insert_row
59 ( p_row_id OUT NOCOPY VARCHAR2
60 , p_credit_usage_rule_set_id NUMBER
61 , p_credit_usage_id NUMBER
62 , p_credit_profile_amt_id NUMBER
63 , p_cust_acct_profile_amt_id NUMBER
64 , p_profile_class_amt_id NUMBER
65 , p_creation_date DATE
66 , p_created_by NUMBER
67 , p_last_update_date DATE
68 , p_last_updated_by NUMBER
69 , p_last_update_login NUMBER
70 , p_attribute_category VARCHAR2
71 , p_attribute1 VARCHAR2
72 , p_attribute2 VARCHAR2
73 , p_attribute3 VARCHAR2
74 , p_attribute4 VARCHAR2
75 , p_attribute5 VARCHAR2
76 , p_attribute6 VARCHAR2
77 , p_attribute7 VARCHAR2
78 , p_attribute8 VARCHAR2
79 , p_attribute9 VARCHAR2
80 , p_attribute10 VARCHAR2
81 , p_attribute11 VARCHAR2
82 , p_attribute12 VARCHAR2
83 , p_attribute13 VARCHAR2
84 , p_attribute14 VARCHAR2
85 , p_attribute15 VARCHAR2
86 )
87 IS
88
89 CURSOR usages_csr IS
90 SELECT
91 rowid
92 FROM
93 HZ_CREDIT_USAGES
94 WHERE credit_usage_id=p_credit_usage_id;
95
96 BEGIN
97
98 INSERT INTO hz_credit_usages
99 ( credit_usage_id
100 , credit_profile_amt_id
101 , cust_acct_profile_amt_id
102 , profile_class_amount_id
103 , credit_usage_rule_set_id
104 , creation_date
105 , created_by
106 , last_update_date
107 , last_updated_by
108 , last_update_login
109 , program_application_id
110 , program_id
111 , program_update_date
112 , request_id
113 , attribute_category
114 , attribute1
115 , attribute2
116 , attribute3
117 , attribute4
118 , attribute5
119 , attribute6
120 , attribute7
121 , attribute8
122 , attribute9
123 , attribute10
124 , attribute11
125 , attribute12
126 , attribute13
127 , attribute14
128 , attribute15
129 )
130 VALUES
131 ( p_credit_usage_id
132 , p_credit_profile_amt_id
133 , p_cust_acct_profile_amt_id
134 , p_profile_class_amt_id
135 , p_credit_usage_rule_set_id
136 , p_creation_date
137 , p_created_by
138 , p_last_update_date
139 , p_last_updated_by
140 , p_last_update_login
141 , null
142 , null
143 , null
144 , null
145 , p_attribute_category
146 , p_attribute1
147 , p_attribute2
148 , p_attribute3
149 , p_attribute4
150 , p_attribute5
151 , p_attribute6
152 , p_attribute7
153 , p_attribute8
154 , p_attribute9
155 , p_attribute10
156 , p_attribute11
157 , p_attribute12
158 , p_attribute13
159 , p_attribute14
160 , p_attribute15
161 );
162
163 OPEN usages_csr;
164 FETCH usages_csr INTO p_row_id;
165 IF (usages_csr%NOTFOUND)
166 THEN
167 CLOSE usages_csr;
168 RAISE NO_DATA_FOUND;
169 END IF;
170 CLOSE usages_csr;
171
172 EXCEPTION
173 WHEN OTHERS THEN
174 IF FND_MSG_PUB.Check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
175 THEN
176 FND_MSG_PUB.Add_exc_msg(G_PKG_NAME,'Insert_row');
177 END IF;
178 RAISE;
179
180 END Insert_row;
181
182 --========================================================================
183 -- PROCEDURE : Delete_row PUBLIC
184 -- PARAMETERS: p_row_id ROWID of the current record
185 -- COMMENT : Procedure deletes record with ROWID=p_row_id from the
186 -- table HZ_CREDIT_USAGES.
187 --========================================================================
188 PROCEDURE Delete_row
189 ( p_row_id VARCHAR2
190 )
191 IS
192 BEGIN
193 DELETE
194 FROM HZ_CREDIT_USAGES
195 WHERE ROWID=p_row_id;
196
197 IF (SQL%NOTFOUND)
198 THEN
199 RAISE NO_DATA_FOUND;
200 END IF;
201 EXCEPTION
202 WHEN OTHERS THEN
203 IF FND_MSG_PUB.Check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
204 THEN
205 FND_MSG_PUB.Add_exc_msg(G_PKG_NAME,'Delete_row');
206 END IF;
207 RAISE;
208
209 END Delete_row;
210
211
212 --========================================================================
213 -- PROCEDURE : Lock_row PUBLIC
214 -- PARAMETERS: p_row_id ROWID of the current record
215 -- p_credit_usage_rule_set_id credit_usage_rule_set_id
216 -- p_credit_profile_amt_id
217 -- p_profile_class_amount_id
218 -- p_cust_acct_profile_amt_id
219 --
220 -- COMMENT : Procedure locks current record in the table HZ_CREDIT_USAGES.
221 --========================================================================
222 PROCEDURE Lock_row
223 ( p_row_id VARCHAR2
224 , p_credit_usage_rule_set_id NUMBER
225 , p_credit_profile_amt_id NUMBER
226 , p_profile_class_amount_id NUMBER
227 , p_cust_acct_profile_amt_id NUMBER
228 )
229 IS
230 CURSOR usage_csr
231 IS
232 SELECT *
233 FROM hz_credit_usages
234 WHERE rowid=CHARTOROWID(p_row_id)
235 FOR UPDATE OF cust_acct_profile_amt_id NOWAIT;
236
237 recinfo usage_csr%ROWTYPE;
238
239 BEGIN
240
241 OPEN usage_csr;
242 FETCH usage_csr INTO recinfo;
243 IF (usage_csr%NOTFOUND)
244 THEN
245 CLOSE usage_csr;
246 FND_MESSAGE.Set_name('FND', 'FORM_RECORD_DELETED');
247 RAISE NO_DATA_FOUND;
248 END IF;
249 CLOSE usage_csr;
250
251 IF ((recinfo.credit_usage_rule_set_id=p_credit_usage_rule_set_id)
252 OR (recinfo.credit_usage_rule_set_id is NULL AND p_credit_usage_rule_set_id is NULL))
253 AND
254 ((NVL(recinfo.credit_profile_amt_id,-1)=p_credit_profile_amt_id)
255 OR (recinfo.credit_profile_amt_id is NULL AND p_credit_profile_amt_id is NULL))
256 AND
257 ((NVL(recinfo.profile_class_amount_id,-1)=p_profile_class_amount_id)
258 OR (recinfo.profile_class_amount_id is NULL AND p_profile_class_amount_id is NULL))
259 AND
260 ((NVL(recinfo.cust_acct_profile_amt_id,-1)=p_cust_acct_profile_amt_id)
261 OR (recinfo.cust_acct_profile_amt_id is NULL AND p_cust_acct_profile_amt_id is NULL))
262 THEN
263 NULL;
264 ELSE
265 FND_MESSAGE.set_name('FND', 'FORM_RECORD_CHANGED');
266 APP_EXCEPTION.raise_exception;
267 END IF;
268
269 END Lock_Row;
270
271
272 ------------------------------------------------------------------------------
273 -- PROCEDURE : Get_Limit_Currency_usages
274 -- COMMENT : REturns
275 -- a) Limit currency
276 -- b) Credit limits
277 -- c) Associated usage rules
281 , p_entity_id IN NUMBER
278 ------------------------------------------------------------------------------
279 PROCEDURE Get_Limit_Currency_usages (
280 p_entity_type IN VARCHAR2
282 , p_trx_curr_code IN VARCHAR2
283 , x_limit_curr_code OUT NOCOPY VARCHAR2
284 , x_trx_limit OUT NOCOPY NUMBER
285 , x_overall_limit OUT NOCOPY NUMBER
286 , x_cust_acct_profile_amt_id OUT NOCOPY NUMBER
287 , x_global_exposure_flag OUT NOCOPY VARCHAR2
288 , x_include_all_flag OUT NOCOPY VARCHAR2
289 , x_usage_curr_tbl OUT NOCOPY HZ_CREDIT_USAGES_PKG.curr_tbl_type
290 , x_excl_curr_list OUT NOCOPY VARCHAR2
291 ) IS
292
293
294
295 CURSOR cust_multi_limit_no_incl_csr IS
296 SELECT cpa.cust_acct_profile_amt_id
297 , cpa.currency_code currency_code
298 , cpa.overall_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
299 overall_limit
300 , cpa.trx_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
301 trx_limit
302 , cp.credit_checking
303 , cu.credit_usage_rule_set_id
304 FROM hz_customer_profiles cp
305 , hz_cust_profile_amts cpa
306 , hz_credit_usages cu
307 , hz_credit_usage_rules cur
308 WHERE cp.cust_account_id = p_entity_id
309 AND cp.site_use_id IS NULL
310 AND cp.cust_account_profile_id = cpa.cust_account_profile_id
311 AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
312 AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
313 AND NVL(TRUNC(cpa.expiration_date) , TRUNC(SYSDATE) )
314 <= TRUNC(SYSDATE)
315 AND NVL (cur.include_all_flag, 'N') = 'N'
316 AND cur.usage_type = 'CURRENCY'
317 AND cur.user_code = p_trx_curr_code
318 AND NVL(cur.exclude_flag,'N') = 'N'
319 ORDER BY cpa.overall_credit_limit ;
320
321
322 cust_multi_limit_no_incl_rec cust_multi_limit_no_incl_csr%ROWTYPE ;
323
324 CURSOR cust_multi_limit_incl_csr IS
325 SELECT cpa.cust_acct_profile_amt_id
326 , cpa.currency_code currency_code
327 , cpa.overall_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
328 overall_limit
329 , cpa.trx_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
330 trx_limit
331 , cp.credit_checking
332 , cu.credit_usage_rule_set_id
333 FROM hz_customer_profiles cp
334 , hz_cust_profile_amts cpa
335 , hz_credit_usages cu
336 , hz_credit_usage_rules cur
337 WHERE cp.cust_account_id = p_entity_id
338 AND cp.site_use_id IS NULL
339 AND cp.cust_account_profile_id = cpa.cust_account_profile_id
340 AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
341 AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
342 AND NVL(TRUNC(cpa.expiration_date), TRUNC(SYSDATE)
343 ) <= TRUNC(SYSDATE)
344 AND NVL (cur.include_all_flag, 'N') = 'Y'
345 AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
346 AND NOT EXISTS ( SELECT 'EXCLUDE'
347 FROM hz_credit_usage_rules cur2
348 WHERE cu.credit_usage_rule_set_id
349 = cur2.credit_usage_rule_set_id
350 AND NVL(cur2.exclude_flag,'N') = 'Y'
351 AND cur2.usage_type = 'CURRENCY'
352 AND cur2.user_code = p_trx_curr_code
353 )
354 ORDER BY cpa.overall_credit_limit ;
355
356
357 cust_multi_limit_incl_rec cust_multi_limit_incl_csr%ROWTYPE ;
358
359
360 CURSOR site_multi_limit_no_incl_csr IS
361 SELECT cpa.cust_acct_profile_amt_id
362 , cpa.currency_code currency_code
363 , cpa.overall_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
364 overall_limit
365 , cpa.trx_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
366 trx_limit
367 , cp.credit_checking
368 , cu.credit_usage_rule_set_id
369 FROM hz_customer_profiles cp
370 , hz_cust_profile_amts cpa
371 , hz_credit_usages cu
372 , hz_credit_usage_rules cur
373 WHERE cp.site_use_id = p_entity_id
374 AND cp.cust_account_profile_id = cpa.cust_account_profile_id
375 AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
376 AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
377 AND NVL(TRUNC(cpa.expiration_date) , TRUNC(SYSDATE) )
378 <= TRUNC(SYSDATE)
379 AND NVL (cur.include_all_flag, 'N') = 'N'
383 ORDER BY cpa.overall_credit_limit ;
380 AND cur.usage_type = 'CURRENCY'
381 AND cur.user_code = p_trx_curr_code
382 AND NVL(cur.exclude_flag,'N') = 'N'
384
385 site_multi_limit_no_incl_rec site_multi_limit_no_incl_csr%ROWTYPE ;
386
387 CURSOR site_multi_limit_incl_csr IS
388 SELECT cpa.cust_acct_profile_amt_id
389 , cpa.currency_code currency_code
390 , cpa.overall_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
391 overall_limit
392 , cpa.trx_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
393 trx_limit
394 , cp.credit_checking
395 , cu.credit_usage_rule_set_id
396 FROM hz_customer_profiles cp
397 , hz_cust_profile_amts cpa
398 , hz_credit_usages cu
399 , hz_credit_usage_rules cur
400 WHERE cp.site_use_id = p_entity_id
401 AND cp.cust_account_profile_id = cpa.cust_account_profile_id
402 AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
403 AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
404 AND NVL(TRUNC(cpa.expiration_date), TRUNC(SYSDATE)
405 ) <= TRUNC(SYSDATE)
406 AND NVL (cur.include_all_flag, 'N') = 'Y'
407 AND NOT EXISTS ( SELECT 'EXCLUDE'
408 FROM hz_credit_usage_rules cur2
409 WHERE cu.credit_usage_rule_set_id
410 = cur2.credit_usage_rule_set_id
411 AND NVL(cur2.exclude_flag,'N') = 'Y'
412 AND cur2.usage_type = 'CURRENCY'
413 AND cur2.user_code = p_trx_curr_code
414 )
415 ORDER BY cpa.overall_credit_limit ;
416
417 site_multi_limit_incl_rec site_multi_limit_incl_csr%ROWTYPE ;
418
419
420 CURSOR party_multi_limit_no_incl_csr IS
421 SELECT cpa.cust_acct_profile_amt_id
422 , cpa.currency_code currency_code
423 , cpa.overall_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
424 overall_limit
425 , cpa.trx_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
426 trx_limit
427 , cp.credit_checking
428 , cu.credit_usage_rule_set_id
429 FROM hz_customer_profiles cp
430 , hz_cust_profile_amts cpa
431 , hz_credit_usages cu
432 , hz_credit_usage_rules cur
433 WHERE cp.cust_account_id = -1
434 AND cp.site_use_id IS NULL
435 AND cp.party_id = p_entity_id
436 AND cp.cust_account_profile_id = cpa.cust_account_profile_id
437 AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
438 AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
439 AND NVL(TRUNC(cpa.expiration_date) , TRUNC(SYSDATE) )
440 <= TRUNC(SYSDATE)
441 AND NVL (cur.include_all_flag, 'N') = 'N'
442 AND cur.usage_type = 'CURRENCY'
443 AND cur.user_code = p_trx_curr_code
444 AND NVL(cur.exclude_flag,'N') = 'N'
445 ORDER BY cpa.overall_credit_limit ;
446
447
448 party_multi_limit_no_incl_rec party_multi_limit_no_incl_csr%ROWTYPE ;
449
450 CURSOR party_multi_limit_incl_csr IS
451 SELECT cpa.cust_acct_profile_amt_id
452 , cpa.currency_code currency_code
453 , cpa.overall_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
454 overall_limit
455 , cpa.trx_credit_limit * ((100 + NVL(cp.tolerance,0))/100)
456 trx_limit
457 , cp.credit_checking
458 , cu.credit_usage_rule_set_id
459 FROM hz_customer_profiles cp
460 , hz_cust_profile_amts cpa
461 , hz_credit_usages cu
462 , hz_credit_usage_rules cur
463 WHERE cp.cust_account_id = -1
464 AND cp.site_use_id IS NULL
465 AND cp.party_id = p_entity_id
466 AND cp.cust_account_profile_id = cpa.cust_account_profile_id
467 AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
468 AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
469 AND NVL(TRUNC(cpa.expiration_date), TRUNC(SYSDATE)
470 ) <= TRUNC(SYSDATE)
471 AND NVL (cur.include_all_flag, 'N') = 'Y'
472 AND NOT EXISTS ( SELECT 'EXCLUDE'
473 FROM hz_credit_usage_rules cur2
474 WHERE cu.credit_usage_rule_set_id
475 = cur2.credit_usage_rule_set_id
476 AND NVL(cur2.exclude_flag,'N') = 'Y'
477 AND cur2.usage_type = 'CURRENCY'
478 AND cur2.user_code = p_trx_curr_code
479 )
480 ORDER BY cpa.overall_credit_limit ;
481
482
483 party_multi_limit_incl_rec party_multi_limit_incl_csr%ROWTYPE ;
484
485
486
487
488 BEGIN
489
490 IF p_entity_type = 'SITE'
491 THEN
492
493 BEGIN
494 OPEN site_multi_limit_no_incl_csr ;
495 FETCH site_multi_limit_no_incl_csr
496 INTO site_multi_limit_no_incl_rec ;
497
498 x_limit_curr_code := site_multi_limit_no_incl_rec.currency_code;
499 x_cust_acct_profile_amt_id :=
500 site_multi_limit_no_incl_rec.cust_acct_profile_amt_id;
501 x_overall_limit := site_multi_limit_no_incl_rec.overall_limit;
505
502 x_trx_limit := site_multi_limit_no_incl_rec.trx_limit;
503
504 CLOSE site_multi_limit_no_incl_csr ;
506
507 IF x_limit_curr_code IS NULL
508 THEN
509 OPEN site_multi_limit_incl_csr ;
510 FETCH site_multi_limit_incl_csr
511 INTO site_multi_limit_incl_rec ;
512
513 x_limit_curr_code := site_multi_limit_incl_rec.currency_code;
514 x_overall_limit := site_multi_limit_incl_rec.overall_limit;
515 x_trx_limit := site_multi_limit_incl_rec.trx_limit;
516 x_cust_acct_profile_amt_id :=
517 site_multi_limit_incl_rec.cust_acct_profile_amt_id;
518
519 CLOSE site_multi_limit_incl_csr ;
520
521 END IF;
522 END;
523
524 ELSIF p_entity_type = 'CUSTOMER'
525 THEN
526 BEGIN
527 --dbms_output.put_line(' into customer');
528 OPEN cust_multi_limit_no_incl_csr ;
529 FETCH cust_multi_limit_no_incl_csr
530 INTO cust_multi_limit_no_incl_rec ;
531
532 x_limit_curr_code := cust_multi_limit_no_incl_rec.currency_code;
533 x_overall_limit := cust_multi_limit_no_incl_rec.overall_limit;
534 x_trx_limit := cust_multi_limit_no_incl_rec.trx_limit;
535 x_cust_acct_profile_amt_id :=
536 cust_multi_limit_no_incl_rec.cust_acct_profile_amt_id;
537
538
539 --dbms_output.put_line(' cust_multi_limit_no_incl_csr ');
540 --dbms_output.put_line(' x_limit_curr_code ==> '
541 -- || x_limit_curr_code );
542 --dbms_output.put_line(' x_cust_acct_profile_amt_id => '
543 -- || x_cust_acct_profile_amt_id );
544
545 CLOSE cust_multi_limit_no_incl_csr ;
546
547 IF x_limit_curr_code IS NULL
548 THEN
549 OPEN cust_multi_limit_incl_csr ;
550 FETCH cust_multi_limit_incl_csr
551 INTO cust_multi_limit_incl_rec ;
552
553 x_limit_curr_code := cust_multi_limit_incl_rec.currency_code;
554 x_overall_limit := cust_multi_limit_incl_rec.overall_limit;
555 x_trx_limit := cust_multi_limit_incl_rec.trx_limit;
556 x_cust_acct_profile_amt_id :=
557 cust_multi_limit_incl_rec.cust_acct_profile_amt_id;
558
559
560 --dbms_output.put_line(' cust_multi_limit_incl_csr ');
561 --dbms_output.put_line(' x_limit_curr_code ==> '
562 -- || x_limit_curr_code );
563 --dbms_output.put_line(' x_cust_acct_profile_amt_id => '
564 -- || x_cust_acct_profile_amt_id );
565
566 CLOSE cust_multi_limit_incl_csr ;
567
568 END IF;
569 END ;
570
571 ELSIF p_entity_type = 'PARTY'
572 THEN
573 BEGIN
574 OPEN party_multi_limit_no_incl_csr ;
575 FETCH party_multi_limit_no_incl_csr
576 INTO party_multi_limit_no_incl_rec ;
577
578 x_limit_curr_code := party_multi_limit_no_incl_rec.currency_code;
579 x_overall_limit := party_multi_limit_no_incl_rec.overall_limit;
580 x_trx_limit := party_multi_limit_no_incl_rec.trx_limit;
581 x_cust_acct_profile_amt_id :=
582 party_multi_limit_no_incl_rec.cust_acct_profile_amt_id;
583
584 CLOSE party_multi_limit_no_incl_csr ;
585
586 IF x_limit_curr_code IS NULL
587 THEN
588 OPEN party_multi_limit_incl_csr ;
589 FETCH party_multi_limit_incl_csr
590 INTO party_multi_limit_incl_rec ;
591
592 x_limit_curr_code := party_multi_limit_incl_rec.currency_code;
593 x_overall_limit := party_multi_limit_incl_rec.overall_limit;
594 x_trx_limit := party_multi_limit_incl_rec.trx_limit;
595 x_cust_acct_profile_amt_id :=
596 party_multi_limit_incl_rec.cust_acct_profile_amt_id;
597
598 CLOSE party_multi_limit_incl_csr ;
599
600 END IF;
601 END ;
602 END IF;
603
607 ,p_limit_curr_code => x_limit_curr_code
604 --dbms_output.put_line ( 'about to call Get_usage_rules ');
605 Get_usage_rules(
606 p_cust_acct_profile_amt_id => x_cust_acct_profile_amt_id
608 , x_global_exposure_flag => x_global_exposure_flag
609 , x_include_all_flag => x_include_all_flag
610 , x_usage_curr_tbl => x_usage_curr_tbl
611 , x_excl_curr_list => x_excl_curr_list
612 );
613
614 --dbms_output.put_line ( 'after Get_usage_rules ');
615 --dbms_output.put_line ( 'x_include_all_flag => '|| x_include_all_flag );
616 --dbms_output.put_line ( 'x_global_exposure_flag => '||
617 -- x_global_exposure_flag );
618 --dbms_output.put_line ( 'x_usage_curr_tbl.count' ||
619 -- x_usage_curr_tbl.count );
620
621 EXCEPTION
622 WHEN OTHERS THEN
623 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
624 THEN
625 FND_MSG_PUB.Add_Exc_Msg
626 ( G_PKG_NAME
627 , 'Get_Limit_Currency_usages'
628 );
629 END IF;
630 RAISE;
631
632
633 END Get_Limit_Currency_usages ;
634
635
636
637 ------------------------------------------------------------------------------
638 -- PROCEDURE : Get_usage_rules
639 -- COMMENT : Returns the Usage currencies associated with a given
640 -- profile amount currency
641 --
642 ------------------------------------------------------------------------------
643 PROCEDURE Get_usage_rules(
644 p_cust_acct_profile_amt_id IN NUMBER
645 ,p_limit_curr_code IN VARCHAR2
646 , x_global_exposure_flag OUT NOCOPY VARCHAR2
647 , x_include_all_flag OUT NOCOPY VARCHAR2
648 , x_usage_curr_tbl OUT NOCOPY HZ_CREDIT_USAGES_PKG.curr_tbl_type
649 , x_excl_curr_list OUT NOCOPY VARCHAR2
650 )
651 IS
652
653 --------------------------------------------------------
654 -- Selecting the usage rule sets
655 --------------------------------------------------------
656 CURSOR usage_rule_set_csr IS
657 SELECT rset.credit_usage_rule_set_id ,
658 rset.global_exposure_flag
659 FROM hz_credit_usages usg
660 , hz_credit_usage_rule_sets_b rset
661 WHERE usg.cust_acct_profile_amt_id = p_cust_acct_profile_amt_id
662 AND rset.credit_usage_rule_set_id = usg.credit_usage_rule_set_id ;
663
664 --------------------------------------------------------
665 -- This cursor identifies if the include all flag --
666 -- is set for this usage rule set . --
667 --------------------------------------------------------
668
669 CURSOR include_all_csr (c_credit_usage_rule_set_id IN NUMBER) IS
670 SELECT 'X'
671 FROM hz_credit_usage_rules
672 WHERE credit_usage_rule_set_id = c_credit_usage_rule_set_id
673 AND usage_type = 'CURRENCY'
674 AND NVL(include_all_flag, 'N') = 'Y';
675
676 --------------------------------------------------------
677 -- This cursor identifies all included usages --
678 -- for this usage rule set . --
679 --------------------------------------------------------
680
681 CURSOR incl_curr_csr (c_credit_usage_rule_set_id IN NUMBER) IS
682 SELECT user_code
683 FROM hz_credit_usage_rules cur
684 WHERE cur.credit_usage_rule_set_id = c_credit_usage_rule_set_id
685 AND cur.usage_type = 'CURRENCY'
686 AND cur.user_code IS NOT NULL
687 AND NVL(cur.exclude_flag,'N') = 'N';
688
689 --------------------------------------------------------
690 -- This cursor identifies all excluded usages --
691 -- for this usage rule set . --
692 --------------------------------------------------------
693
694 CURSOR excl_curr_csr (c_credit_usage_rule_set_id IN NUMBER) IS
695 SELECT user_code
696 FROM hz_credit_usage_rules cur
697 WHERE cur.credit_usage_rule_set_id = c_credit_usage_rule_set_id
698 AND cur.usage_type = 'CURRENCY'
699 AND cur.user_code IS NOT NULL
700 AND NVL(cur.exclude_flag,'N') = 'Y';
701
702
703 include_all_rec include_all_csr%rowtype;
704
705 l_limit_flag VARCHAR2(1) := 'N';
706 i NUMBER := 0;
707 j NUMBER := 1;
708 l_return_status NUMBER;
709 l_arrsize NUMBER;
710
711 l_incl_curr_list VARCHAR2(2000);
712 l_trx_curr_list VARCHAR2(2000);
713 l_seperator VARCHAR2(1) := '#';
714 l_currency VARCHAR2(10);
715
716 l_start NUMBER := 1;
717 l_end NUMBER := 1;
718
719 l_exclude_flag VARCHAR2(1) := 'N' ;
720
721 BEGIN
722
723 l_exclude_flag := 'N' ;
724 x_excl_curr_list := NULL;
725 x_include_all_flag := NULL ;
726 x_usage_curr_tbl.DELETE ;
727 x_global_exposure_flag := 'N' ;
728 --------------------------------------
729 -- select the rule set and the associated rules
730 -------------------------------------------
731
732 FOR rule_set_rec IN usage_rule_set_csr
733 LOOP
737 END IF;
734 IF NVL(rule_set_rec.global_exposure_flag,'N') = 'Y'
735 THEN
736 x_global_exposure_flag := 'Y' ;
738
739 OPEN include_all_csr
740 (rule_set_rec.credit_usage_rule_set_id);
741
742 --- Include ALL
743
744 FETCH include_all_csr
745 INTO include_all_rec;
746
747 IF include_all_csr%FOUND
748 THEN
749 x_include_all_flag := 'Y';
750 ELSE
751 x_include_all_flag := 'N';
752 END IF;
753
754 CLOSE include_all_csr;
755
756 --------------------------------------------------------
757 -- identify the included currencies for each rule set --
758 --------------------------------------------------------
759
760 -- Include directly
761 FOR incl_curr_rec
762 IN incl_curr_csr
763 (rule_set_rec.credit_usage_rule_set_id)
764 LOOP
765 l_incl_curr_list :=
766 l_incl_curr_list || l_seperator || incl_curr_rec.user_code;
767 END LOOP;
768
769 --------------------------------------------------------
770 -- identify the excluded currencies for each rule set --
771 --------------------------------------------------------
772 -- Excluded currency
773
774 FOR excl_curr_rec
775 IN excl_curr_csr
776 (rule_set_rec.credit_usage_rule_set_id)
777 LOOP
778 x_excl_curr_list :=
779 x_excl_curr_list || l_seperator || excl_curr_rec.user_code;
780
781 l_exclude_flag := 'Y' ;
782 END LOOP;
783
784 END LOOP;
785 -- End rule_set_rec
786
787 -------------------------------------------------------
788 -- first include all incl currencies (minus excl) --
789 -------------------------------------------------------
790
791 IF x_include_all_flag = 'N'
792 THEN
793 l_start := 1;
794 l_end := 1;
795
796 LOOP
797 l_start := INSTRB (l_incl_curr_list,l_seperator,l_end,1);
798 l_end := INSTRB (l_incl_curr_list,l_seperator,l_start+1,1);
799
800 IF NVL(l_start,0) = 0
801 THEN
802 EXIT;
803 END IF;
804
805 IF NVL(l_end,0) = 0
806 THEN
807 l_end := LENGTHB (l_incl_curr_list) + 1;
808 END IF;
809
810 l_currency := SUBSTRB ( l_incl_curr_list
811 , ( l_start + 1 )
812 , ( l_end - l_start - 1 )
813 );
814
815 IF NVL(INSTRB (x_excl_curr_list,l_currency,1,1),0) = 0
816 THEN
817 i := i + 1;
818 x_usage_curr_tbl(i).usage_curr_code := l_currency;
819
820 IF l_currency = p_limit_curr_code
821 THEN
822 l_limit_flag := 'Y';
823 END IF;
824
825
826 END IF;
827 END LOOP;
828
829 -------------------------------------
830 -- if the limit currency code is --
831 -- not already included, do it now --
832 -------------------------------------
833 IF l_limit_flag = 'N'
834 THEN
835 i := NVL(i,0) + 1;
836 x_usage_curr_tbl(i).usage_curr_code := p_limit_curr_code;
837 END IF;
838
839
840 END IF; -- Invlude ALL flag check
841
842 IF x_include_all_flag IS NULL
843 THEN
844 x_include_all_flag := 'N' ;
845 x_excl_curr_list := NULL ;
846 END IF;
847
848
849 EXCEPTION
850 WHEN OTHERS THEN
851 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
852 THEN
853 FND_MSG_PUB.Add_Exc_Msg
854 ( G_PKG_NAME
855 , 'get_usage_rules'
856 );
857 END IF;
858 RAISE;
859
860
861 END get_usage_rules ;
862
863
864
865 END HZ_CREDIT_USAGES_PKG;