[Home] [Help]
PACKAGE BODY: APPS.HZ_CREDIT_USAGES_CASCADE_PKG
Source
1 PACKAGE BODY HZ_CREDIT_USAGES_CASCADE_PKG AS
2 /* $Header: ARHCRCCB.pls 115.15 2003/03/15 03:51:18 vto noship $ */
3 --======================================================================
4 --CONSTANTS
5 --======================================================================
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'HZ_CREDIT_USAGES_CASCADE_PKG' ;
7
8 G_request_id NUMBER
9 := FND_GLOBAL.CONC_Request_id ;
10 G_program_id NUMBER
11 := FND_GLOBAL.CONC_program_id ;
12 G_program_application_id NUMBER
13 := FND_GLOBAL.PROG_APPL_ID ;
14 G_user_id NUMBER :=
15 NVL(TO_NUMBER(FND_PROFILE.Value('USER_ID')),1) ;
16 G_login_id NUMBER :=
17 NVL(TO_NUMBER(FND_PROFILE.Value('LOGIN_ID')),1) ;
18
19
20
21 ---------------------------
22 -- PROCEDURES AND FUNCTIONS
23 ---------------------------
24 --------------------------------------------------------------------
25 -- PROCEDURE cascade_credit_usage_rules
26 -- The procedure accepts two input parameters
27 -- a) p_cust_acct_profile_amt_id
28 -- This is the new customer / site profile amount id that is
29 -- being created
30 -- b) p_profile_class_amt_id
31 -- This is the profile class amt id from which the new
32 -- new customer / site profile amount id is created
33 --------------------------------------------------------------------
34 PROCEDURE cascade_credit_usage_rules
35 ( p_cust_acct_profile_amt_id IN NUMBER
36 , p_cust_profile_id IN NUMBER
37 , p_profile_class_amt_id IN NUMBER
38 , p_profile_class_id IN NUMBER
39 , X_return_status OUT NOCOPY VARCHAR2
40 , X_msg_count OUT NOCOPY NUMBER
41 , X_msg_data OUT NOCOPY VARCHAR2
42 )
43 IS
44
45 l_entity VARCHAR2(30);
46 l_entity_id NUMBER;
47 l_cust_account_id NUMBER;
48 l_site_use_id NUMBER;
49 l_party_id NUMBER;
50 l_include_all VARCHAR2(1);
51 l_duplicate VARCHAR2(1);
52 l_duplicate_curr VARCHAR2(30);
53
54
55 CURSOR rule_set_csr IS
56 SELECT
57 cu.credit_usage_rule_set_id
58 , cr.global_exposure_flag
59 FROM HZ_CREDIT_USAGES cu
60 , hz_credit_usage_rule_sets_b cr
61 WHERE cu.profile_class_amount_id = p_profile_class_amt_id
62 AND cu.credit_usage_rule_set_id = cr.credit_usage_rule_set_id ;
63
64 RULE_SET_CSR_REC rule_set_csr%ROWTYPE;
65
66 l_id NUMBER;
67 l_global_exposure_flag VARCHAR2(1) ;
68
69 BEGIN
70 -- The API will duplicate the rule set assignments
71 -- made with the profile class limit currency with
72 -- profile amt currency being created when the
73 -- profile class amt currency ic used as reference
74 -- The information is currently stores in the
75 -- HZ_credit_usages table
76
77 -- The logic is to replicate the exact image of the rule sets
78 -- assignments made with the profile class amount currency
79 -- so the exiting rule sets assigned with the
80 -- cust_profile_amt currency is removed first
81
82 -- Before the actual cascade , the customer/site profile
83 -- is verified to check to make sure that the cascade will
84 -- not cause any duplicates in the currency usage
85 -- assignments
86
87 X_return_status := 'S' ;
88
89 BEGIN
90 -- The following SELECT statement should be fixed as part of the
91 -- fix to support cascading usage rules from profile class to party
92 -- level credit profile.
93 -- Note: the cascading procedure is called after the customer profile
94 -- amount is created, and BEFORE the customer profile record is created.
95 SELECT
96 ca.cust_account_id
97 , ca.site_use_id
98 INTO
99 l_cust_account_id
100 , l_site_use_id
101 FROM
102 hz_cust_profile_amts ca
103 WHERE ca.cust_acct_profile_amt_id = p_cust_acct_profile_amt_id;
104
105 --Use the IDs to determine the entity.
106 --Note that a party level profile will have a cust_account_id of -1
107 --since cust_account_id is a NOT NULL column.
108
109 IF l_site_use_id is not NULL
110 THEN
111 l_entity := 'SITE' ;
112 l_entity_id := l_site_use_id ;
113 ELSE
114 IF l_cust_account_id <> -1
115 THEN
116 l_entity := 'CUSTOMER';
117 l_entity_id := l_cust_account_id ;
118 ELSE
119 -- Get party ID and set it as the entity ID
120 BEGIN
121 SELECT
122 cp.cust_account_id
123 , cp.site_use_id
124 , cp.party_id
125 INTO
126 l_cust_account_id
127 , l_site_use_id
128 , l_party_id
129 FROM
130 hz_cust_profile_amts ca
131 , hz_customer_profiles cp
132 WHERE ca.cust_acct_profile_amt_id = p_cust_acct_profile_amt_id
133 AND ca.CUST_ACCOUNT_PROFILE_ID = cp.CUST_ACCOUNT_PROFILE_ID
134 AND cp.cust_account_id = -1;
135 IF l_party_id is not NULL
136 THEN
137 l_entity := 'PARTY' ;
138 l_entity_id := l_party_id ;
139 END IF;
140 EXCEPTION
141 WHEN NO_DATA_FOUND THEN
142 l_entity := NULL;
143 l_entity_id := NULL;
144 l_cust_account_id := NULL;
145 l_site_use_id := NULL;
146 l_party_id := NULL;
147 WHEN TOO_MANY_ROWS THEN
148 l_entity := NULL;
149 l_entity_id := NULL;
150 l_cust_account_id := NULL;
151 l_site_use_id := NULL;
152 l_party_id := NULL;
153 END ;
154 END IF;
155 END IF;
156
157 EXCEPTION
158 WHEN NO_DATA_FOUND THEN
159 l_entity := NULL;
160 l_entity_id := NULL;
161 l_cust_account_id := NULL;
162 l_site_use_id := NULL;
163 l_party_id := NULL;
164
165 WHEN TOO_MANY_ROWS THEN
166 l_entity := NULL;
167 l_entity_id := NULL;
168 l_cust_account_id := NULL;
169 l_site_use_id := NULL;
170 l_party_id := NULL;
171 END ;
172
173 --DBMS_OUTPUT.PUT_LINE('l_entity = '|| l_entity );
174 --DBMS_OUTPUT.PUT_LINE('l_entity_id '|| l_entity_id );
175 --DBMS_OUTPUT.PUT_LINE('l_cust_account_id '|| l_cust_account_id );
176 --DBMS_OUTPUT.PUT_LINE('l_include_all '|| l_include_all);
177
178 FOR rule_set_csr_rec IN rule_set_csr
179 LOOP
180 BEGIN
181 SELECT credit_usage_rule_id
182 INTO l_id
183 FROM hz_credit_usage_rules
184 WHERE credit_usage_rule_set_id
185 = rule_set_csr_rec.credit_usage_rule_set_id
186 AND user_code IS NULL;
187
188 l_include_all := 'Y' ;
189
190 EXCEPTION
191 WHEN NO_DATA_FOUND THEN
192 l_include_all := 'N' ;
193
194 WHEN TOO_MANY_ROWS THEN
195 l_include_all := 'Y' ;
196 END ;
197
198 --DBMS_OUTPUT.PUT_LINE('Rule set ID = '||
199 -- rule_set_csr_rec.credit_usage_rule_set_id );
200
201 IF l_entity = 'PARTY'
202 AND NVL(rule_set_csr_rec.global_exposure_flag,'N')= 'N'
203 THEN
204 l_duplicate := 'Y' ;
205 ELSE
206 l_duplicate := NULL ;
207 HZ_CREDIT_USAGES_CASCADE_PKG.Check_Duplicate_all
208 ( p_rule_set_id =>
209 rule_set_csr_rec.credit_usage_rule_set_id
210 , p_entity => l_entity
211 , p_entity_id => l_entity_id
212 , p_cust_account_id => l_cust_account_id
213 , p_include_all => l_include_all
214 , p_cust_acct_profile_amt_id => p_cust_acct_profile_amt_id
215 , x_duplicate => l_duplicate
216 , x_dupl_curr => l_duplicate_curr
217 );
218 END IF;
219
220 IF l_duplicate = 'Y'
221 THEN
222 EXIT ;
223 END IF;
224 END LOOP; -- check duplicates
225
226 --DBMS_OUTPUT.PUT_LINE('l_duplicate = '|| l_duplicate );
227
228 IF l_duplicate <> 'Y'
229 THEN
230 --DBMS_OUTPUT.PUT_LINE('Call delete_credit_usages ');
231
232 HZ_CREDIT_USAGES_CASCADE_PKG.delete_credit_usages
233 ( p_cust_acct_profile_amt_id => p_cust_acct_profile_amt_id
234 , X_return_status => X_return_status
235 , X_msg_count => X_msg_count
236 , X_msg_data => X_msg_data
237 );
238 END IF;
239
240 IF X_return_status = 'S'
241 AND l_duplicate <> 'Y'
242 THEN
243 --DBMS_OUTPUT.PUT_LINE('Delete SUCCESS, call INSERT ');
244
245 INSERT INTO HZ_CREDIT_USAGES
246 ( CREDIT_USAGE_ID
247 , CREDIT_PROFILE_AMT_ID
248 , CUST_ACCT_PROFILE_AMT_ID
249 , PROFILE_CLASS_AMOUNT_ID
250 , CREDIT_USAGE_RULE_SET_ID
251 , CREATION_DATE
252 , CREATED_BY
253 , LAST_UPDATE_DATE
254 , LAST_UPDATED_BY
255 , LAST_UPDATE_LOGIN
256 , PROGRAM_APPLICATION_ID
257 , PROGRAM_ID
258 , PROGRAM_UPDATE_DATE
259 , REQUEST_ID
260 , ATTRIBUTE_CATEGORY
261 , ATTRIBUTE1
262 , ATTRIBUTE2
263 , ATTRIBUTE3
264 , ATTRIBUTE4
265 , ATTRIBUTE5
266 , ATTRIBUTE6
267 , ATTRIBUTE7
268 , ATTRIBUTE8
269 , ATTRIBUTE9
270 , ATTRIBUTE10
271 , ATTRIBUTE11
272 , ATTRIBUTE12
273 , ATTRIBUTE13
274 , ATTRIBUTE14
275 , ATTRIBUTE15
276 )
277 SELECT
278 HZ_CREDIT_USAGES_S.NEXTVAL
279 , NULL
280 , p_cust_acct_profile_amt_id
281 , NULL
282 , cu.CREDIT_USAGE_RULE_SET_ID
283 , SYSDATE
284 , G_user_id
285 , SYSDATE
286 , G_user_id
287 , G_login_id
288 , G_program_application_id
289 , G_program_id
290 , SYSDATE
291 , G_request_id
292 , cu.ATTRIBUTE_CATEGORY
293 , cu.ATTRIBUTE1
294 , cu.ATTRIBUTE2
295 , cu.ATTRIBUTE3
296 , cu.ATTRIBUTE4
297 , cu.ATTRIBUTE5
298 , cu.ATTRIBUTE6
299 , cu.ATTRIBUTE7
300 , cu.ATTRIBUTE8
301 , cu.ATTRIBUTE9
302 , cu.ATTRIBUTE10
303 , cu.ATTRIBUTE11
304 , cu.ATTRIBUTE12
305 , cu.ATTRIBUTE13
306 , cu.ATTRIBUTE14
307 , cu.ATTRIBUTE15
308 FROM
309 HZ_CREDIT_USAGES cu
310 WHERE cu.PROFILE_CLASS_AMOUNT_ID = p_profile_class_amt_id ;
311
312 END IF;
313
314 EXCEPTION
315 WHEN OTHERS THEN
316 X_return_status := 'U' ;
317 END cascade_credit_usage_rules ;
318
319
320 -----------------------------------------------------------------
321 -- PROCEDURE: delete_credit_usages
322 -- COMMENTS: This procedure will accept the
323 -- cust_acct_profile_amt_id as input and
324 -- remove records from the multi currency credit checking
325 -- usages table HZ_CREDIT_USAGES for this ID
326 -----------------------------------------------------------------
327 PROCEDURE delete_credit_usages
328 ( p_cust_acct_profile_amt_id IN NUMBER
329 , X_return_status OUT NOCOPY VARCHAR2
330 , X_msg_count OUT NOCOPY NUMBER
331 , X_msg_data OUT NOCOPY VARCHAR2
332 ) IS
333
334 BEGIN
335 -- Delete the Rule set assigned for a given
336 -- profile amt id
337
338 X_return_status := 'S' ;
339
340 DELETE FROM
341 HZ_CREDIT_USAGES
342 WHERE CUST_ACCT_PROFILE_AMT_ID = p_cust_acct_profile_amt_id ;
343
344 EXCEPTION
345 WHEN OTHERS THEN
346 X_return_status := 'U' ;
347 END delete_credit_usages ;
348
349
350 -----------------------------------------------------------------
351 -- PROCEDURE: Check_Duplicate_all
352 -- COMMENTS: This procedure will check the
353 -- duplication of currencies across the
354 -- existing assigned rule sets with the
355 -- profile
356 -----------------------------------------------------------------
357 PROCEDURE Check_Duplicate_all
358 ( p_rule_set_id IN NUMBER
359 , p_entity IN VARCHAR2
360 , p_entity_id IN NUMBER
361 , p_cust_account_id IN NUMBER
362 , p_include_all IN VARCHAR2
363 , p_cust_acct_profile_amt_id IN NUMBER
364 , x_duplicate OUT NOCOPY VARCHAR2
365 , x_dupl_curr OUT NOCOPY VARCHAR2
366 )
367 IS
368
369 l_usage_curr VARCHAR2(30) ;
370 l_cust_acct_profile_amt_id NUMBER;
371
372 -------------------------------------------------
373
374 -- CASE1 : Add rule set with no Include all currencies
375
376 -- CASE2: Add rule set with Include all currencies
377
378 -- This case will be checked both for
379 -- already attached rules sets with and without
380 -- Include all currencies
381
382 -- The checking will be peformed for the following
383 -- entities.
384 -- p_entity_id will contain the reference ID
385
386 -- CUSTOMER
387 -- SITE ( p_cust_account_id will also be populated )
388 -- ORGANIZATION
389 -- ITEM
390 -- CLASS
391 -------------------------------------------------
392 ---------------
393 -- RULES CURSOR --
394 -------------------------
395
396 CURSOR SELECT_USAGE_CURR_CSR IS
397 SELECT user_code
398 FROM HZ_CREDIT_USAGE_RULES
399 WHERE credit_usage_rule_set_id = p_rule_set_id
400 AND usage_type = 'CURRENCY'
401 AND NVL (include_all_flag, 'N') = 'N'
402 AND NVL(exclude_flag,'N') = 'N' ;
403
404
405 --------------------
406 -- SITE ---
407 ---------------------
408
409 CURSOR site_check_case1_no_incl_csr IS
410 SELECT cu.credit_usage_id
411 , substrb(cur.user_code,1,5) curr
412 , cpa.currency_code prof_curr
413 FROM hz_customer_profiles cp
414 , hz_cust_profile_amts cpa
415 , hz_credit_usages cu
416 , hz_credit_usage_rules cur
417 WHERE cp.cust_account_id = p_cust_account_id
418 AND cp.site_use_id = p_entity_id
419 AND cp.cust_account_profile_id = cpa.cust_account_profile_id
423 AND NVL(TRUNC(cpa.expiration_date) , TRUNC(SYSDATE) )
420 AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
421 AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
422 AND cpa.cust_acct_profile_amt_id <> l_cust_acct_profile_amt_id
424 <= TRUNC(SYSDATE)
425 AND NVL (cur.include_all_flag, 'N') = 'N'
426 AND cur.usage_type = 'CURRENCY'
427 AND cur.user_code IN (
428 SELECT cr2.user_code from
429 hz_credit_usage_rules cr2
430 WHERE cr2.credit_usage_rule_set_id = p_rule_set_id
431 AND cr2.usage_type = 'CURRENCY' and
432 NVL (cr2.include_all_flag, 'N') = 'N'
433 AND NVL(cr2.exclude_flag,'N') = 'N'
434 )
435 AND NVL(cur.exclude_flag,'N') = 'N' ;
436
437
438 site_check_case1_no_incl_rec site_check_case1_no_incl_csr%ROWTYPE ;
439
440 CURSOR site_check_case1_incl_csr ( p_curr_code IN VARCHAR2 ) IS
441 SELECT cpa.currency_code profile_curr
442 , cu.credit_usage_id
443 FROM hz_customer_profiles cp
444 , hz_cust_profile_amts cpa
445 , hz_credit_usages cu
446 , hz_credit_usage_rules cur
447 WHERE cp.cust_account_id = p_cust_account_id
448 AND cp.site_use_id = p_entity_id
449 AND cp.cust_account_profile_id = cpa.cust_account_profile_id
450 AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
451 AND cpa.cust_acct_profile_amt_id <> l_cust_acct_profile_amt_id
452 AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
453 AND NVL(TRUNC(cpa.expiration_date), TRUNC(SYSDATE)
454 ) <= TRUNC(SYSDATE)
455 AND NVL (cur.include_all_flag, 'N') = 'Y'
456 AND NOT EXISTS ( SELECT 'EXCLUDE'
457 FROM hz_credit_usage_rules cur2
458 WHERE cu.credit_usage_rule_set_id
459 = cur2.credit_usage_rule_set_id
460 AND NVL(cur2.exclude_flag,'N') = 'Y'
461 AND cur2.usage_type = 'CURRENCY'
462 AND cur2.user_code = p_curr_code );
463
464 site_check_case1_incl_rec site_check_case1_incl_csr%ROWTYPE ;
465
466
467
468 CURSOR site_check_case2_no_incl_csr IS
469 SELECT cu.credit_usage_id
470 , substrb(cur.user_code,1,5) curr
471 , cpa.currency_code prof_curr
472 FROM hz_customer_profiles cp
473 , hz_cust_profile_amts cpa
474 , hz_credit_usages cu
475 , hz_credit_usage_rules cur
476 WHERE cp.cust_account_id = p_cust_account_id
477 AND cp.site_use_id = p_entity_id
478 AND cp.cust_account_profile_id = cpa.cust_account_profile_id
479 AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
480 AND cpa.cust_acct_profile_amt_id <> l_cust_acct_profile_amt_id
481 AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
482 AND NVL(TRUNC(cpa.expiration_date) , TRUNC(SYSDATE) )
483 <= TRUNC(SYSDATE)
484 AND NVL (cur.include_all_flag, 'N') = 'N'
485 AND cur.usage_type = 'CURRENCY'
486 AND cur.user_code NOT IN (
487 SELECT cr2.user_code from
488 hz_credit_usage_rules cr2
489 WHERE cr2.credit_usage_rule_set_id = p_rule_set_id
490 AND cr2.usage_type = 'CURRENCY' and
491 NVL (cr2.include_all_flag, 'N') = 'N'
492 AND NVL(cr2.exclude_flag,'N') = 'Y'
493 )
494 AND NVL(cur.exclude_flag,'N') = 'N' ;
495
496
497 site_check_case2_no_incl_rec site_check_case2_no_incl_csr%ROWTYPE;
498
499 CURSOR site_check_case2_incl_csr IS
500 SELECT cpa.currency_code profile_curr
501 , cu.credit_usage_id
502 FROM hz_customer_profiles cp
503 , hz_cust_profile_amts cpa
504 , hz_credit_usages cu
505 , hz_credit_usage_rules cur
506 WHERE cp.cust_account_id = p_cust_account_id
507 AND cp.site_use_id = p_entity_id
508 AND cp.cust_account_profile_id = cpa.cust_account_profile_id
509 AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
510 AND cpa.cust_acct_profile_amt_id <> l_cust_acct_profile_amt_id
511 AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
512 AND NVL(TRUNC(cpa.expiration_date), TRUNC(SYSDATE)
513 ) <= TRUNC(SYSDATE)
514 AND NVL (cur.include_all_flag, 'N') = 'Y' ;
515
516
517
518 site_check_case2_incl_rec site_check_case2_incl_csr%ROWTYPE ;
519
520
521
522
523
524 --------------------
525 -- CUSTOMER ------
526 ----------------------
527
528 CURSOR cust_check_case1_no_incl_csr IS
529 SELECT cu.credit_usage_id
530 , substrb(cur.user_code,1,5) curr
531 , cpa.currency_code prof_curr
532 FROM hz_customer_profiles cp
533 , hz_cust_profile_amts cpa
534 , hz_credit_usages cu
535 , hz_credit_usage_rules cur
536 WHERE cp.cust_account_id = p_entity_id
537 AND cp.site_use_id IS NULL
538 AND cp.cust_account_profile_id = cpa.cust_account_profile_id
539 AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
543 <= TRUNC(SYSDATE)
540 AND cpa.cust_acct_profile_amt_id <> l_cust_acct_profile_amt_id
541 AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
542 AND NVL(TRUNC(cpa.expiration_date) , TRUNC(SYSDATE) )
544 AND NVL (cur.include_all_flag, 'N') = 'N'
545 AND cur.usage_type = 'CURRENCY'
546 AND cur.user_code IN (
547 SELECT cr2.user_code from
548 hz_credit_usage_rules cr2
549 WHERE cr2.credit_usage_rule_set_id = p_rule_set_id
550 AND cr2.usage_type = 'CURRENCY' and
551 NVL (cr2.include_all_flag, 'N') = 'N'
552 AND NVL(cr2.exclude_flag,'N') = 'N'
553 )
554 AND NVL(cur.exclude_flag,'N') = 'N' ;
555
556 cust_check_case1_no_incl_rec cust_check_case1_no_incl_csr%ROWTYPE ;
557
558
559 CURSOR cust_check_case1_incl_csr ( p_curr_code IN VARCHAR2 ) IS
560 SELECT cpa.currency_code profile_curr
561 , cu.credit_usage_id
562 FROM hz_customer_profiles cp
563 , hz_cust_profile_amts cpa
564 , hz_credit_usages cu
565 , hz_credit_usage_rules cur
566 WHERE cp.cust_account_id = p_entity_id
567 AND cp.site_use_id IS NULL
568 AND cp.cust_account_profile_id = cpa.cust_account_profile_id
569 AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
570 AND cpa.cust_acct_profile_amt_id <> l_cust_acct_profile_amt_id
571 AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
572 AND NVL(TRUNC(cpa.expiration_date), TRUNC(SYSDATE)
573 ) <= TRUNC(SYSDATE)
574 AND NVL (cur.include_all_flag, 'N') = 'Y'
575 AND NOT EXISTS ( SELECT 'EXCLUDE'
576 FROM hz_credit_usage_rules cur2
577 WHERE cu.credit_usage_rule_set_id
578 = cur2.credit_usage_rule_set_id
579 AND NVL(cur2.exclude_flag,'N') = 'Y'
580 AND cur2.usage_type = 'CURRENCY'
581 AND cur2.user_code = p_curr_code
582 );
583
584
585 cust_check_case1_incl_rec cust_check_case1_incl_csr%ROWTYPE ;
586
587
588 CURSOR cust_check_case2_no_incl_csr IS
589 SELECT cu.credit_usage_id
590 , substrb(cur.user_code,1,5) curr
591 , cpa.currency_code prof_curr
592 FROM hz_customer_profiles cp
593 , hz_cust_profile_amts cpa
594 , hz_credit_usages cu
595 , hz_credit_usage_rules cur
596 WHERE cp.cust_account_id = p_entity_id
597 AND cp.site_use_id IS NULL
598 AND cp.cust_account_profile_id = cpa.cust_account_profile_id
599 AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
600 AND cpa.cust_acct_profile_amt_id <> l_cust_acct_profile_amt_id
601 AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
602 AND NVL(TRUNC(cpa.expiration_date) , TRUNC(SYSDATE) )
603 <= TRUNC(SYSDATE)
604 AND NVL (cur.include_all_flag, 'N') = 'N'
605 AND cur.usage_type = 'CURRENCY'
606 AND cur.user_code NOT IN (
607 SELECT cr2.user_code from
608 hz_credit_usage_rules cr2
609 WHERE cr2.credit_usage_rule_set_id = p_rule_set_id
610 AND cr2.usage_type = 'CURRENCY' and
611 NVL (cr2.include_all_flag, 'N') = 'N'
612 AND NVL(cr2.exclude_flag,'N') = 'Y'
613 )
614 AND NVL(cur.exclude_flag,'N') = 'N' ;
615
616
617 cust_check_case2_no_incl_rec cust_check_case2_no_incl_csr%ROWTYPE ;
618
619
620 CURSOR cust_check_case2_incl_csr IS
621 SELECT cpa.currency_code profile_curr
622 , cu.credit_usage_id
623 FROM hz_customer_profiles cp
624 , hz_cust_profile_amts cpa
625 , hz_credit_usages cu
626 , hz_credit_usage_rules cur
627 WHERE cp.cust_account_id = p_entity_id
628 AND cp.site_use_id IS NULL
629 AND cp.cust_account_profile_id = cpa.cust_account_profile_id
630 AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
631 AND cpa.cust_acct_profile_amt_id <> l_cust_acct_profile_amt_id
632 AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
633 AND NVL(TRUNC(cpa.expiration_date), TRUNC(SYSDATE)
634 ) <= TRUNC(SYSDATE)
635 AND NVL (cur.include_all_flag, 'N') = 'Y' ;
636
637
638 cust_check_case2_incl_rec cust_check_case2_incl_csr%ROWTYPE ;
639
640 -------------------------- party ------------------------------
641 CURSOR party_check_case1_no_incl_csr IS
642 SELECT cu.credit_usage_id
643 , substrb(cur.user_code,1,5) curr
644 , cpa.currency_code prof_curr
645 FROM hz_customer_profiles cp
646 , hz_cust_profile_amts cpa
647 , hz_credit_usages cu
648 , hz_credit_usage_rules cur
649 WHERE cp.cust_account_id = -1
650 AND cp.site_use_id IS NULL
651 AND cp.party_id = p_entity_id
652 AND cp.cust_account_profile_id = cpa.cust_account_profile_id
653 AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
654 AND cpa.cust_acct_profile_amt_id <> l_cust_acct_profile_amt_id
655 AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
659 AND cur.usage_type = 'CURRENCY'
656 AND NVL(TRUNC(cpa.expiration_date) , TRUNC(SYSDATE) )
657 <= TRUNC(SYSDATE)
658 AND NVL (cur.include_all_flag, 'N') = 'N'
660 AND cur.user_code IN (
661 SELECT cr2.user_code from
662 hz_credit_usage_rules cr2
663 WHERE cr2.credit_usage_rule_set_id = p_rule_set_id
664 AND cr2.usage_type = 'CURRENCY' and
665 NVL (cr2.include_all_flag, 'N') = 'N'
666 AND NVL(cr2.exclude_flag,'N') = 'N'
667 )
668 AND NVL(cur.exclude_flag,'N') = 'N' ;
669
670 party_check_case1_no_incl_rec party_check_case1_no_incl_csr%ROWTYPE ;
671
672
673 CURSOR party_check_case1_incl_csr ( p_curr_code IN VARCHAR2 ) IS
674 SELECT cpa.currency_code profile_curr
675 , cu.credit_usage_id
676 FROM hz_customer_profiles cp
677 , hz_cust_profile_amts cpa
678 , hz_credit_usages cu
679 , hz_credit_usage_rules cur
680 WHERE cp.cust_account_id = -1
681 AND cp.site_use_id IS NULL
682 AND cp.party_id = p_entity_id
683 AND cp.cust_account_profile_id = cpa.cust_account_profile_id
684 AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
685 AND cpa.cust_acct_profile_amt_id <> l_cust_acct_profile_amt_id
686 AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
687 AND NVL(TRUNC(cpa.expiration_date), TRUNC(SYSDATE)
688 ) <= TRUNC(SYSDATE)
689 AND NVL (cur.include_all_flag, 'N') = 'Y'
690 AND NOT EXISTS ( SELECT 'EXCLUDE'
691 FROM hz_credit_usage_rules cur2
692 WHERE cu.credit_usage_rule_set_id
693 = cur2.credit_usage_rule_set_id
694 AND NVL(cur2.exclude_flag,'N') = 'Y'
695 AND cur2.usage_type = 'CURRENCY'
696 AND cur2.user_code = p_curr_code
697 );
698
699
700 party_check_case1_incl_rec party_check_case1_incl_csr%ROWTYPE ;
701
702
703 CURSOR party_check_case2_no_incl_csr IS
704 SELECT cu.credit_usage_id
705 , substrb(cur.user_code,1,5) curr
706 , cpa.currency_code prof_curr
707 FROM hz_customer_profiles cp
708 , hz_cust_profile_amts cpa
709 , hz_credit_usages cu
710 , hz_credit_usage_rules cur
711 WHERE cp.cust_account_id = -1
712 AND cp.site_use_id IS NULL
713 AND cp.party_id = p_entity_id
714 AND cp.cust_account_profile_id = cpa.cust_account_profile_id
715 AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
716 AND cpa.cust_acct_profile_amt_id <> l_cust_acct_profile_amt_id
717 AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
718 AND NVL(TRUNC(cpa.expiration_date) , TRUNC(SYSDATE) )
719 <= TRUNC(SYSDATE)
720 AND NVL (cur.include_all_flag, 'N') = 'N'
721 AND cur.usage_type = 'CURRENCY'
722 AND cur.user_code NOT IN (
723 SELECT cr2.user_code from
724 hz_credit_usage_rules cr2
725 WHERE cr2.credit_usage_rule_set_id = p_rule_set_id
726 AND cr2.usage_type = 'CURRENCY' and
727 NVL (cr2.include_all_flag, 'N') = 'N'
728 AND NVL(cr2.exclude_flag,'N') = 'Y'
729 )
730 AND NVL(cur.exclude_flag,'N') = 'N' ;
731
732
733 party_check_case2_no_incl_rec party_check_case2_no_incl_csr%ROWTYPE ;
734
735
736 CURSOR party_check_case2_incl_csr IS
737 SELECT cpa.currency_code profile_curr
738 , cu.credit_usage_id
739 FROM hz_customer_profiles cp
740 , hz_cust_profile_amts cpa
741 , hz_credit_usages cu
742 , hz_credit_usage_rules cur
743 WHERE cp.cust_account_id = -1
744 AND cp.site_use_id IS NULL
745 AND cp.party_id = p_entity_id
746 AND cp.cust_account_profile_id = cpa.cust_account_profile_id
747 AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
748 AND cpa.cust_acct_profile_amt_id <> l_cust_acct_profile_amt_id
749 AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
750 AND NVL(TRUNC(cpa.expiration_date), TRUNC(SYSDATE)
751 ) <= TRUNC(SYSDATE)
752 AND NVL (cur.include_all_flag, 'N') = 'Y' ;
753
754
755 party_check_case2_incl_rec party_check_case2_incl_csr%ROWTYPE ;
756
757 BEGIN
758
759 x_duplicate := 'N' ;
760
761 -- using l_cust_acct_profile_amt_id local variable as this
762 -- API will be used for other cascading features within
763 -- Multi curremcy credit checking project and the
764 -- p_cust_acct_profile_amt_id may not be made available
765
766 l_cust_acct_profile_amt_id := p_cust_acct_profile_amt_id;
767
768 IF p_entity = 'SITE'
769 THEN
770 --------------------- BEGIN SITE ----------------------
771 BEGIN
772 OPEN site_check_case1_no_incl_csr ;
773 FETCH site_check_case1_no_incl_csr
774 INTO site_check_case1_no_incl_rec ;
775
776
777 IF site_check_case1_no_incl_csr%NOTFOUND
778 THEN
782
779 x_duplicate := 'N' ;
780 x_dupl_curr := NULL ;
781 --FND_MESSAGE.DEBUG(' site - 1 no found');
783 ELSE
784 x_duplicate := 'Y' ;
785 x_dupl_curr := site_check_case1_no_incl_rec.curr ;
786
787 --FND_MESSAGE.DEBUG(' site - 1 ');
788 END IF;
789
790 CLOSE site_check_case1_no_incl_csr ;
791
792 IF x_duplicate = 'N'
793 THEN
794 l_usage_curr := NULL ;
795
796 OPEN SELECT_USAGE_CURR_CSR ;
797 LOOP
798 FETCH SELECT_USAGE_CURR_CSR
799 INTO l_usage_curr ;
800
801 IF SELECT_USAGE_CURR_CSR%NOTFOUND
802 THEN
803 l_usage_curr := NULL ;
804 EXIT ;
805 END IF;
806
807 --FND_MESSAGE.DEBUG(' l_usage_curr = '|| l_usage_curr);
808
809 OPEN site_check_case1_incl_csr ( l_usage_curr );
810 FETCH site_check_case1_incl_csr
811 INTO site_check_case1_incl_rec ;
812
813 IF site_check_case1_incl_csr%NOTFOUND
814 THEN
815 x_duplicate := 'N' ;
816 x_dupl_curr := NULL ;
817
818 --FND_MESSAGE.DEBUG(' site - 2 no found');
819
820 ELSE
821 x_duplicate := 'Y' ;
822 x_dupl_curr := l_usage_curr ;
823
824 CLOSE site_check_case1_incl_csr ;
825
826 --FND_MESSAGE.DEBUG(' site - 2 ');
827
828 EXIT ;
829
830 END IF;
831
832 CLOSE site_check_case1_incl_csr ;
833
834 END LOOP;
835 CLOSE SELECT_USAGE_CURR_CSR ;
836 --FND_MESSAGE.DEBUG(' site - 2 - Out of LOOP ');
837
838 END IF;
839
840 IF NVL(p_include_all,'N') = 'Y'
841 THEN
842 IF x_duplicate = 'N'
843 THEN
844 OPEN site_check_case2_no_incl_csr ;
845 FETCH site_check_case2_no_incl_csr
846 INTO site_check_case2_no_incl_rec ;
847
848
849 IF site_check_case2_no_incl_csr%NOTFOUND
850 THEN
851 x_duplicate := 'N' ;
852 x_dupl_curr := NULL ;
853
854 --FND_MESSAGE.DEBUG(' site - 3 no found');
855
856 ELSE
857
858 x_duplicate := 'Y' ;
859 x_dupl_curr := site_check_case2_no_incl_rec.curr ;
860
861 --FND_MESSAGE.DEBUG(' site - 3 ');
862
863 END IF;
864
865 CLOSE site_check_case2_no_incl_csr ;
866
867 END IF;
868
869 IF x_duplicate = 'N'
870 THEN
871 OPEN site_check_case2_incl_csr ;
872 FETCH site_check_case2_incl_csr
873 INTO site_check_case2_incl_rec ;
874
875
876 IF site_check_case2_incl_csr%NOTFOUND
877 THEN
878 x_duplicate := 'N' ;
879 x_dupl_curr := NULL;
880
881 --FND_MESSAGE.DEBUG(' site - 4 no found');
882
883 ELSE
884 x_duplicate := 'Y' ;
885 x_dupl_curr := 'ALL Currency' ;
886 --FND_MESSAGE.DEBUG(' site - 4 ');
887 END IF;
888
889 CLOSE site_check_case2_incl_csr ;
890
891 END IF;
892 END IF; -- case2
893 END ; -- End Site
894
895 --------------- End SITE --------------------------------
896
897
898 --------------------- BEGIN CUST ----------------------
899
900 ELSIF p_entity = 'CUSTOMER'
901 THEN
902
903 BEGIN
904
905 OPEN CUST_check_case1_no_incl_csr ;
906 FETCH CUST_check_case1_no_incl_csr
907 INTO CUST_check_case1_no_incl_rec ;
908
909
910 IF CUST_check_case1_no_incl_csr%NOTFOUND
911 THEN
912 x_duplicate := 'N' ;
913 x_dupl_curr := NULL ;
914 --FND_MESSAGE.DEBUG(' CUST - 1 no found');
915
916 ELSE
917 x_duplicate := 'Y' ;
918 x_dupl_curr := CUST_check_case1_no_incl_rec.curr ;
919
920 --FND_MESSAGE.DEBUG(' CUST - 1 ');
921 END IF;
922
923 CLOSE CUST_check_case1_no_incl_csr ;
924
925 IF x_duplicate = 'N'
926 THEN
927 l_usage_curr := NULL ;
928
929 OPEN SELECT_USAGE_CURR_CSR ;
930 LOOP
931 FETCH SELECT_USAGE_CURR_CSR
932 INTO l_usage_curr ;
933
934 IF SELECT_USAGE_CURR_CSR%NOTFOUND
935 THEN
936 l_usage_curr := NULL ;
937 EXIT ;
938 END IF;
939
940 --FND_MESSAGE.DEBUG(' l_usage_curr = '|| l_usage_curr);
941
942 OPEN CUST_check_case1_incl_csr ( l_usage_curr );
943 FETCH CUST_check_case1_incl_csr
944 INTO CUST_check_case1_incl_rec ;
945
946 IF CUST_check_case1_incl_csr%NOTFOUND
947 THEN
951 --FND_MESSAGE.DEBUG(' CUST - 2 no found');
948 x_duplicate := 'N' ;
949 x_dupl_curr := NULL ;
950
952
953 ELSE
954
955 x_duplicate := 'Y' ;
956 x_dupl_curr := l_usage_curr ;
957
958 CLOSE CUST_check_case1_incl_csr ;
959
960 --FND_MESSAGE.DEBUG(' CUST - 2 ');
961
962 EXIT ;
963
964 END IF;
965
966 CLOSE CUST_check_case1_incl_csr ;
967
968 END LOOP;
969 CLOSE SELECT_USAGE_CURR_CSR ;
970 --FND_MESSAGE.DEBUG(' CUST - 2 - Out of LOOP ');
971
972 END IF;
973
974 IF NVL(p_include_all,'N') = 'Y'
975 THEN
976 IF x_duplicate = 'N'
977 THEN
978 OPEN CUST_check_case2_no_incl_csr ;
979 FETCH CUST_check_case2_no_incl_csr
980 INTO CUST_check_case2_no_incl_rec ;
981
982
983 IF CUST_check_case2_no_incl_csr%NOTFOUND
984 THEN
985 x_duplicate := 'N' ;
986 x_dupl_curr := NULL ;
987
988 --FND_MESSAGE.DEBUG(' CUST - 3 no found');
989
990 ELSE
991
992 x_duplicate := 'Y' ;
993 x_dupl_curr := CUST_check_case2_no_incl_rec.curr ;
994
995 --FND_MESSAGE.DEBUG(' CUST - 3 ');
996
997 END IF;
998
999 CLOSE CUST_check_case2_no_incl_csr ;
1000
1001 END IF;
1002
1003 IF x_duplicate = 'N'
1004 THEN
1005 OPEN CUST_check_case2_incl_csr ;
1006 FETCH CUST_check_case2_incl_csr
1007 INTO CUST_check_case2_incl_rec ;
1008
1009
1010 IF CUST_check_case2_incl_csr%NOTFOUND
1011 THEN
1012 x_duplicate := 'N' ;
1013 x_dupl_curr := NULL ;
1014
1015 --FND_MESSAGE.DEBUG(' CUST - 4 no found');
1016
1017 ELSE
1018 x_duplicate := 'Y' ;
1019 x_dupl_curr := 'ALL Currency' ;
1020 --FND_MESSAGE.DEBUG(' CUST - 4 ');
1021 END IF;
1022
1023 CLOSE CUST_check_case2_incl_csr ;
1024
1025 END IF;
1026 END IF; -- case2
1027
1028 END ; -- Customer
1029
1030 --------------- End CUST --------------------------------
1031
1032 ------------------- party ---------------------------
1033
1034 ELSIF p_entity = 'PARTY'
1035 THEN
1036
1037 BEGIN
1038
1039 OPEN party_check_case1_no_incl_csr ;
1040 FETCH party_check_case1_no_incl_csr
1041 INTO party_check_case1_no_incl_rec ;
1042
1043
1044 IF party_check_case1_no_incl_csr%NOTFOUND
1045 THEN
1046 x_duplicate := 'N' ;
1047 x_dupl_curr := NULL ;
1048 --FND_MESSAGE.DEBUG(' CUST - 1 no found');
1049
1050 ELSE
1051 x_duplicate := 'Y' ;
1052 x_dupl_curr := party_check_case1_no_incl_rec.curr ;
1053
1054 --FND_MESSAGE.DEBUG(' CUST - 1 ');
1055 END IF;
1056
1057 CLOSE party_check_case1_no_incl_csr ;
1058
1059 IF x_duplicate = 'N'
1060 THEN
1061 l_usage_curr := NULL ;
1062
1063 OPEN SELECT_USAGE_CURR_CSR ;
1064 LOOP
1065 FETCH SELECT_USAGE_CURR_CSR
1066 INTO l_usage_curr ;
1067
1068 IF SELECT_USAGE_CURR_CSR%NOTFOUND
1069 THEN
1070 l_usage_curr := NULL ;
1071 EXIT ;
1072 END IF;
1073
1074 --FND_MESSAGE.DEBUG(' l_usage_curr = '|| l_usage_curr);
1075
1076 OPEN party_check_case1_incl_csr ( l_usage_curr );
1077 FETCH party_check_case1_incl_csr
1078 INTO party_check_case1_incl_rec ;
1079
1080 IF party_check_case1_incl_csr%NOTFOUND
1081 THEN
1082 x_duplicate := 'N' ;
1083 x_dupl_curr := NULL ;
1084
1085 --FND_MESSAGE.DEBUG(' CUST - 2 no found');
1086
1087 ELSE
1088
1089 x_duplicate := 'Y' ;
1090 x_dupl_curr := l_usage_curr ;
1091
1092 CLOSE party_check_case1_incl_csr ;
1093
1094 --FND_MESSAGE.DEBUG(' CUST - 2 ');
1095
1096 EXIT ;
1097
1098 END IF;
1099
1100 CLOSE party_check_case1_incl_csr ;
1101
1102 END LOOP;
1103 CLOSE SELECT_USAGE_CURR_CSR ;
1104 --FND_MESSAGE.DEBUG(' CUST - 2 - Out of LOOP ');
1105
1106 END IF;
1107
1108 IF NVL(p_include_all,'N') = 'Y'
1109 THEN
1110 IF x_duplicate = 'N'
1111 THEN
1112 OPEN party_check_case2_no_incl_csr ;
1113 FETCH party_check_case2_no_incl_csr
1114 INTO party_check_case2_no_incl_rec ;
1115
1116
1117 IF party_check_case2_no_incl_csr%NOTFOUND
1118 THEN
1119 x_duplicate := 'N' ;
1120 x_dupl_curr := NULL ;
1121
1122 --FND_MESSAGE.DEBUG(' CUST - 3 no found');
1123
1124 ELSE
1125
1126 x_duplicate := 'Y' ;
1127 x_dupl_curr := party_check_case2_no_incl_rec.curr ;
1128
1129 --FND_MESSAGE.DEBUG(' CUST - 3 ');
1130
1131 END IF;
1132
1133 CLOSE party_check_case2_no_incl_csr ;
1134
1135 END IF;
1136
1137 IF x_duplicate = 'N'
1138 THEN
1139 OPEN party_check_case2_incl_csr ;
1140 FETCH party_check_case2_incl_csr
1141 INTO party_check_case2_incl_rec ;
1142
1143
1144 IF party_check_case2_incl_csr%NOTFOUND
1145 THEN
1146 x_duplicate := 'N' ;
1147 x_dupl_curr := NULL ;
1148
1149 --FND_MESSAGE.DEBUG(' CUST - 4 no found');
1150
1151 ELSE
1152 x_duplicate := 'Y' ;
1153 x_dupl_curr := 'ALL Currency' ;
1154 --FND_MESSAGE.DEBUG(' CUST - 4 ');
1155 END IF;
1156
1157 CLOSE party_check_case2_incl_csr ;
1158
1159 END IF;
1160 END IF; -- case2
1161
1162 END ;
1163
1164 --------------- End party --------------------------------
1165 ELSE
1166 x_duplicate := 'Y' ;
1167 x_dupl_curr := 'INVALID ENTITY' ;
1168 END IF;
1169
1170 EXCEPTION
1171 WHEN OTHERS THEN
1172 RAISE ;
1173 END Check_Duplicate_all ;
1174
1175
1176
1177
1178 END HZ_CREDIT_USAGES_CASCADE_PKG ;