[Home] [Help]
PACKAGE BODY: APPS.HZ_MGD_MASS_UPDATE_MEDIATOR
Source
1 PACKAGE BODY HZ_MGD_MASS_UPDATE_MEDIATOR AS
2 /* $Header: ARHCMUMB.pls 120.2 2005/06/30 04:46:50 bdhotkar noship $*/
3 /*+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| ARHCMUMB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Body of the package HZ_MGD_MASS_UPDATE_MEDIATOR |
13 --| |
14 --| PROCEDURE LIST |
15 --| Mass_Update_Usage_Rules |
16 --| Check_Duplicate_ALL |
17 --| |
18 --| HISTORY |
19 --| 05/14/2002 tsimmond Created |
20 --| 11/27/2002 tsimmond Updated Added WHENEVER OSERROR EXIT |
21 --| FAILURE ROLLBACK |
22 --| |
23 --+======================================================================*/
24
25
26 --======================================================================
27 --CONSTANTS
28 --======================================================================
29 --G_PKG_NAME CONSTANT VARCHAR2(30) := 'HZ_MGD_MASS_UPDATE_MEDIATOR' ;
30 G_request_id NUMBER
31 := FND_GLOBAL.CONC_Request_id ;
32 G_program_id NUMBER
33 := FND_GLOBAL.CONC_program_id ;
34 G_program_application_id NUMBER
35 := FND_GLOBAL.PROG_APPL_ID ;
36 G_user_id NUMBER :=
37 NVL(TO_NUMBER(FND_PROFILE.Value('USER_ID')),1) ;
38 G_login_id NUMBER :=
39 NVL(TO_NUMBER(FND_PROFILE.Value('LOGIN_ID')),1) ;
40
41
42 --====================================================================
43 PROCEDURE Get_entity_name
44 ( p_entity_id IN NUMBER
45 , p_entity IN VARCHAR2
46 , p_cust_account_id IN NUMBER
47 , x_party_name OUT NOCOPY VARCHAR2
48 , x_cust_name OUT NOCOPY VARCHAR2
49 , x_site_name OUT NOCOPY VARCHAR2
50 )
51 IS
52 l_cust_name hz_parties.party_name%TYPE;
53 l_cust_num hz_parties.party_number%TYPE;
54 l_site_name hz_cust_site_uses.location%TYPE;
55
56 BEGIN
57
58 HZ_MGD_MASS_UPDATE_REP_GEN.log
59 ( p_priority => HZ_MGD_MASS_UPDATE_REP_GEN.G_LOG_PROCEDURE
60 , p_msg => '>> Get_entity_name ' );
61
62 HZ_MGD_MASS_UPDATE_REP_GEN.Log
63 ( p_priority => HZ_MGD_MASS_UPDATE_REP_GEN.G_LOG_STATEMENT
64 , p_msg => 'entity= '||p_entity
65 || ' entity_id= '||TO_CHAR(p_entity_id));
66
67 ----if entity=PARTY-----------
68 IF p_entity='PARTY'
69 THEN
70 SELECT
71 SUBSTRB(party_name,1,40)
72 INTO
73 x_party_name
74 FROM hz_parties
75 WHERE party_id=p_entity_id;
76
77 x_cust_name:=' ';
78 x_site_name:=' ';
79
80 HZ_MGD_MASS_UPDATE_REP_GEN.Log
81 ( p_priority => HZ_MGD_MASS_UPDATE_REP_GEN.G_LOG_STATEMENT
82 , p_msg => 'party_name= '||x_party_name
83 );
84
85 ----if entity=CUSTOMER-----------
86 ELSIF p_entity='CUSTOMER'
87 THEN
88 SELECT
89 SUBSTRB(hp.party_name,1,30)
90 , hca.account_number
91 INTO
92 l_cust_name
93 , l_cust_num
94 FROM
95 hz_parties hp
96 , hz_cust_accounts hca
97 WHERE hca.party_id=hp.party_id
98 AND hca.cust_account_id=p_entity_id;
99
100 x_party_name:=' ';
101 x_cust_name:=l_cust_name||'('||l_cust_num||')';
102 x_site_name:=' ';
103
104 HZ_MGD_MASS_UPDATE_REP_GEN.Log
105 ( p_priority => HZ_MGD_MASS_UPDATE_REP_GEN.G_LOG_STATEMENT
106 , p_msg => 'customer_name= '||x_cust_name
107 );
108
109 ----if entity=SITE-----------
110 ELSIF p_entity='SITE'
111 THEN
112
113 SELECT
114 location
115 INTO
116 x_site_name
117 FROM
118 hz_cust_site_uses
119 WHERE site_use_id=p_entity_id;
120
121 SELECT
122 SUBSTRB(hp.party_name,1,30)
123 , hca.account_number
124 INTO
125 l_cust_name
126 , l_cust_num
127 FROM
128 hz_parties hp
129 , hz_cust_accounts hca
130 WHERE hca.party_id=hp.party_id
131 AND hca.cust_account_id=p_cust_account_id;
132
133 x_cust_name:=l_cust_name||'('||l_cust_num||')';
134
135 HZ_MGD_MASS_UPDATE_REP_GEN.Log
136 ( p_priority => HZ_MGD_MASS_UPDATE_REP_GEN.G_LOG_STATEMENT
137 , p_msg => 'site_name= '||x_site_name
138 );
139
140 END IF;
141
142
143 HZ_MGD_MASS_UPDATE_REP_GEN.log
144 ( p_priority => HZ_MGD_MASS_UPDATE_REP_GEN.G_LOG_PROCEDURE
145 , p_msg => '<< Get_entity_name ' );
146
147 END Get_entity_name;
148
149 --================================================================
150
151 -- Check duplication for all the profile currencies
152 -- Also extends the logic for ALL_CURRENCIES set up
153
154
155 -------------------------------------------------------------------
156
157 /*-------------------------------------------------------------
158 PROCEDURE: Check_Duplicate_all
159 COMMENTS: This procedure will check the
160 duplication of currencies across the
161 existing assigned rule sets with the
162 profile
163 -----------------------------------------------------------------*/
164 PROCEDURE Check_Duplicate_all
165 ( p_rule_set_id IN NUMBER
166 , p_entity IN VARCHAR2
167 , p_entity_id IN NUMBER
168 , p_cust_account_id IN NUMBER
169 , p_include_all IN VARCHAR2
170 , p_cust_acct_profile_amt_id IN NUMBER
171 , x_duplicate OUT NOCOPY VARCHAR2
172 , x_dupl_curr OUT NOCOPY VARCHAR2
173 )
174 IS
175
176 l_usage_curr VARCHAR2(30) ;
177 l_cust_acct_profile_amt_id NUMBER;
178
179 -------------------------------------------------
180
181 -- CASE1 : Add rule set with no Include all currencies
182
183 -- CASE2: Add rule set with Include all currencies
184
185 -- This case will be checked both for
186 -- already attached rules sets with and without
187 -- Include all currencies
188
189 -- The checking will be peformed for the following
190 -- entities.
191 -- p_entity_id will contain the reference ID
192
193 -- PARTY
194 -- CUSTOMER
195 -- SITE ( p_cust_account_id will also be populated )
196 -- ORGANIZATION
197 -- ITEM
198 -- CLASS
199 -------------------------------------------------
200 ---------------
201 -- RULES CURSOR --
202 -------------------------
203
204 CURSOR SELECT_USAGE_CURR_CSR IS
205 SELECT user_code
206 FROM HZ_CREDIT_USAGE_RULES
207 WHERE credit_usage_rule_set_id = p_rule_set_id
208 AND usage_type = 'CURRENCY'
209 AND NVL (include_all_flag, 'N') = 'N'
210 AND NVL(exclude_flag,'N') = 'N' ;
211
212
213 --------------------
214 -- PARTY ------
215 ----------------------
216
217 CURSOR party_check_case1_no_incl_csr IS
218 SELECT cu.credit_usage_id
219 , substrb(cur.user_code,1,5) curr
220 , cpa.currency_code prof_curr
221 FROM hz_customer_profiles cp
222 , hz_cust_profile_amts cpa
223 , hz_credit_usages cu
224 , hz_credit_usage_rules cur
225 WHERE cp.party_id = p_entity_id
226 AND cp.site_use_id IS NULL
227 AND cp.cust_account_id =-1
228 AND cp.cust_account_profile_id = cpa.cust_account_profile_id
229 AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
230 AND cpa.cust_acct_profile_amt_id <> l_cust_acct_profile_amt_id
231 AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
232 AND NVL(TRUNC(cpa.expiration_date) , TRUNC(SYSDATE) )
233 <= TRUNC(SYSDATE)
234 AND NVL (cur.include_all_flag, 'N') = 'N'
235 AND cur.usage_type = 'CURRENCY'
236 AND cur.user_code IN (
237 SELECT cr2.user_code from
238 hz_credit_usage_rules cr2
239 WHERE cr2.credit_usage_rule_set_id = p_rule_set_id
240 AND cr2.usage_type = 'CURRENCY' and
241 NVL (cr2.include_all_flag, 'N') = 'N'
242 AND NVL(cr2.exclude_flag,'N') = 'N'
243 )
244 AND NVL(cur.exclude_flag,'N') = 'N';
245
246 party_check_case1_no_incl_rec party_check_case1_no_incl_csr%ROWTYPE ;
247
248
249 CURSOR party_check_case1_incl_csr ( p_curr_code IN VARCHAR2 ) IS
250 SELECT cpa.currency_code profile_curr
251 , cu.credit_usage_id
252 FROM hz_customer_profiles cp
253 , hz_cust_profile_amts cpa
254 , hz_credit_usages cu
255 , hz_credit_usage_rules cur
256 WHERE cp.party_id = p_entity_id
257 AND cp.cust_account_id =-1
258 AND cp.site_use_id IS NULL
259 AND cp.cust_account_profile_id = cpa.cust_account_profile_id
260 AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
261 AND cpa.cust_acct_profile_amt_id <> l_cust_acct_profile_amt_id
262 AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
263 AND NVL(TRUNC(cpa.expiration_date), TRUNC(SYSDATE)
264 ) <= TRUNC(SYSDATE)
265 AND NVL (cur.include_all_flag, 'N') = 'Y'
266 AND NOT EXISTS ( SELECT 'EXCLUDE'
267 FROM hz_credit_usage_rules cur2
268 WHERE cu.credit_usage_rule_set_id
269 = cur2.credit_usage_rule_set_id
270 AND NVL(cur2.exclude_flag,'N') = 'Y'
271 AND cur2.usage_type = 'CURRENCY'
272 AND cur2.user_code = p_curr_code
273 );
274
275
276 party_check_case1_incl_rec party_check_case1_incl_csr%ROWTYPE ;
277
278
279 CURSOR party_check_case2_no_incl_csr IS
280 SELECT cu.credit_usage_id
281 , substrb(cur.user_code,1,5) curr
282 , cpa.currency_code prof_curr
283 FROM hz_customer_profiles cp
284 , hz_cust_profile_amts cpa
285 , hz_credit_usages cu
286 , hz_credit_usage_rules cur
287 WHERE cp.party_id = p_entity_id
288 AND cp.cust_account_id =-1
289 AND cp.site_use_id IS NULL
290 AND cp.cust_account_profile_id = cpa.cust_account_profile_id
291 AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
292 AND cpa.cust_acct_profile_amt_id <> l_cust_acct_profile_amt_id
293 AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
294 AND NVL(TRUNC(cpa.expiration_date) , TRUNC(SYSDATE) )
295 <= TRUNC(SYSDATE)
296 AND NVL (cur.include_all_flag, 'N') = 'N'
297 AND cur.usage_type = 'CURRENCY'
298 AND cur.user_code NOT IN (
299 SELECT cr2.user_code from
300 hz_credit_usage_rules cr2
301 WHERE cr2.credit_usage_rule_set_id = p_rule_set_id
302 AND cr2.usage_type = 'CURRENCY' and
303 NVL (cr2.include_all_flag, 'N') = 'N'
304 AND NVL(cr2.exclude_flag,'N') = 'Y'
305 )
306 AND NVL(cur.exclude_flag,'N') = 'N' ;
307
308
309 party_check_case2_no_incl_rec party_check_case2_no_incl_csr%ROWTYPE ;
310
311
312 CURSOR party_check_case2_incl_csr IS
313 SELECT cpa.currency_code profile_curr
314 , cu.credit_usage_id
315 FROM hz_customer_profiles cp
316 , hz_cust_profile_amts cpa
317 , hz_credit_usages cu
318 , hz_credit_usage_rules cur
319 WHERE cp.party_id = p_entity_id
320 AND cp.cust_account_id =-1
321 AND cp.site_use_id IS NULL
322 AND cp.cust_account_profile_id = cpa.cust_account_profile_id
323 AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
324 AND cpa.cust_acct_profile_amt_id <> l_cust_acct_profile_amt_id
325 AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
326 AND NVL(TRUNC(cpa.expiration_date), TRUNC(SYSDATE)
327 ) <= TRUNC(SYSDATE)
328 AND NVL (cur.include_all_flag, 'N') = 'Y' ;
329
330
331 party_check_case2_incl_rec party_check_case2_incl_csr%ROWTYPE ;
332
333
334 --------------------
335 -- SITE ---
336 ---------------------
337
338 CURSOR site_check_case1_no_incl_csr IS
339 SELECT cu.credit_usage_id
340 , substrb(cur.user_code,1,5) curr
341 , cpa.currency_code prof_curr
342 FROM hz_customer_profiles cp
343 , hz_cust_profile_amts cpa
344 , hz_credit_usages cu
345 , hz_credit_usage_rules cur
346 WHERE cp.cust_account_id = p_cust_account_id
347 AND cp.site_use_id = p_entity_id
348 AND cp.cust_account_profile_id = cpa.cust_account_profile_id
349 AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
350 AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
351 AND cpa.cust_acct_profile_amt_id <> l_cust_acct_profile_amt_id
352 AND NVL(TRUNC(cpa.expiration_date) , TRUNC(SYSDATE) )
353 <= TRUNC(SYSDATE)
354 AND NVL (cur.include_all_flag, 'N') = 'N'
355 AND cur.usage_type = 'CURRENCY'
356 AND cur.user_code IN (
357 SELECT cr2.user_code from
358 hz_credit_usage_rules cr2
359 WHERE cr2.credit_usage_rule_set_id = p_rule_set_id
360 AND cr2.usage_type = 'CURRENCY' and
361 NVL (cr2.include_all_flag, 'N') = 'N'
362 AND NVL(cr2.exclude_flag,'N') = 'N'
363 )
364 AND NVL(cur.exclude_flag,'N') = 'N' ;
365
366
367 site_check_case1_no_incl_rec site_check_case1_no_incl_csr%ROWTYPE ;
368
369 CURSOR site_check_case1_incl_csr ( p_curr_code IN VARCHAR2 ) IS
370 SELECT cpa.currency_code profile_curr
371 , cu.credit_usage_id
372 FROM hz_customer_profiles cp
373 , hz_cust_profile_amts cpa
374 , hz_credit_usages cu
375 , hz_credit_usage_rules cur
376 WHERE cp.cust_account_id = p_cust_account_id
377 AND cp.site_use_id = p_entity_id
378 AND cp.cust_account_profile_id = cpa.cust_account_profile_id
379 AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
380 AND cpa.cust_acct_profile_amt_id <> l_cust_acct_profile_amt_id
381 AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
382 AND NVL(TRUNC(cpa.expiration_date), TRUNC(SYSDATE)
383 ) <= TRUNC(SYSDATE)
384 AND NVL (cur.include_all_flag, 'N') = 'Y'
385 AND NOT EXISTS ( SELECT 'EXCLUDE'
386 FROM hz_credit_usage_rules cur2
387 WHERE cu.credit_usage_rule_set_id
388 = cur2.credit_usage_rule_set_id
389 AND NVL(cur2.exclude_flag,'N') = 'Y'
390 AND cur2.usage_type = 'CURRENCY'
391 AND cur2.user_code = p_curr_code );
392
396
393 site_check_case1_incl_rec site_check_case1_incl_csr%ROWTYPE ;
394
395
397 CURSOR site_check_case2_no_incl_csr IS
398 SELECT cu.credit_usage_id
399 , substrb(cur.user_code,1,5) curr
400 , cpa.currency_code prof_curr
401 FROM hz_customer_profiles cp
402 , hz_cust_profile_amts cpa
403 , hz_credit_usages cu
404 , hz_credit_usage_rules cur
405 WHERE cp.cust_account_id = p_cust_account_id
406 AND cp.site_use_id = p_entity_id
407 AND cp.cust_account_profile_id = cpa.cust_account_profile_id
408 AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
409 AND cpa.cust_acct_profile_amt_id <> l_cust_acct_profile_amt_id
410 AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
411 AND NVL(TRUNC(cpa.expiration_date) , TRUNC(SYSDATE) )
412 <= TRUNC(SYSDATE)
413 AND NVL (cur.include_all_flag, 'N') = 'N'
414 AND cur.usage_type = 'CURRENCY'
415 AND cur.user_code NOT IN (
416 SELECT cr2.user_code from
417 hz_credit_usage_rules cr2
418 WHERE cr2.credit_usage_rule_set_id = p_rule_set_id
419 AND cr2.usage_type = 'CURRENCY' and
420 NVL (cr2.include_all_flag, 'N') = 'N'
421 AND NVL(cr2.exclude_flag,'N') = 'Y'
422 )
423 AND NVL(cur.exclude_flag,'N') = 'N' ;
424
425
426 site_check_case2_no_incl_rec site_check_case2_no_incl_csr%ROWTYPE;
427
428 CURSOR site_check_case2_incl_csr IS
429 SELECT cpa.currency_code profile_curr
430 , cu.credit_usage_id
431 FROM hz_customer_profiles cp
432 , hz_cust_profile_amts cpa
433 , hz_credit_usages cu
434 , hz_credit_usage_rules cur
435 WHERE cp.cust_account_id = p_cust_account_id
436 AND cp.site_use_id = p_entity_id
437 AND cp.cust_account_profile_id = cpa.cust_account_profile_id
438 AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
439 AND cpa.cust_acct_profile_amt_id <> l_cust_acct_profile_amt_id
440 AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
441 AND NVL(TRUNC(cpa.expiration_date), TRUNC(SYSDATE)
442 ) <= TRUNC(SYSDATE)
443 AND NVL (cur.include_all_flag, 'N') = 'Y' ;
444
445
446
447 site_check_case2_incl_rec site_check_case2_incl_csr%ROWTYPE ;
448
449
450
451
452
453 --------------------
454 -- CUSTOMER ------
455 ----------------------
456
457 CURSOR cust_check_case1_no_incl_csr IS
458 SELECT cu.credit_usage_id
459 , substrb(cur.user_code,1,5) curr
460 , cpa.currency_code prof_curr
461 FROM hz_customer_profiles cp
462 , hz_cust_profile_amts cpa
463 , hz_credit_usages cu
464 , hz_credit_usage_rules cur
465 WHERE cp.cust_account_id = p_entity_id
466 AND cp.site_use_id IS NULL
467 AND cp.cust_account_profile_id = cpa.cust_account_profile_id
468 AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
469 AND cpa.cust_acct_profile_amt_id <> l_cust_acct_profile_amt_id
470 AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
471 AND NVL(TRUNC(cpa.expiration_date) , TRUNC(SYSDATE) )
472 <= TRUNC(SYSDATE)
473 AND NVL (cur.include_all_flag, 'N') = 'N'
474 AND cur.usage_type = 'CURRENCY'
475 AND cur.user_code IN (
476 SELECT cr2.user_code from
477 hz_credit_usage_rules cr2
478 WHERE cr2.credit_usage_rule_set_id = p_rule_set_id
479 AND cr2.usage_type = 'CURRENCY' and
480 NVL (cr2.include_all_flag, 'N') = 'N'
481 AND NVL(cr2.exclude_flag,'N') = 'N'
482 )
483 AND NVL(cur.exclude_flag,'N') = 'N' ;
484
485 cust_check_case1_no_incl_rec cust_check_case1_no_incl_csr%ROWTYPE ;
486
487
488 CURSOR cust_check_case1_incl_csr ( p_curr_code IN VARCHAR2 ) IS
489 SELECT cpa.currency_code profile_curr
490 , cu.credit_usage_id
491 FROM hz_customer_profiles cp
492 , hz_cust_profile_amts cpa
493 , hz_credit_usages cu
494 , hz_credit_usage_rules cur
495 WHERE cp.cust_account_id = p_entity_id
496 AND cp.site_use_id IS NULL
497 AND cp.cust_account_profile_id = cpa.cust_account_profile_id
498 AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
499 AND cpa.cust_acct_profile_amt_id <> l_cust_acct_profile_amt_id
500 AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
501 AND NVL(TRUNC(cpa.expiration_date), TRUNC(SYSDATE)
502 ) <= TRUNC(SYSDATE)
503 AND NVL (cur.include_all_flag, 'N') = 'Y'
504 AND NOT EXISTS ( SELECT 'EXCLUDE'
505 FROM hz_credit_usage_rules cur2
506 WHERE cu.credit_usage_rule_set_id
507 = cur2.credit_usage_rule_set_id
508 AND NVL(cur2.exclude_flag,'N') = 'Y'
509 AND cur2.usage_type = 'CURRENCY'
510 AND cur2.user_code = p_curr_code
511 );
512
513
514 cust_check_case1_incl_rec cust_check_case1_incl_csr%ROWTYPE ;
515
516
517 CURSOR cust_check_case2_no_incl_csr IS
518 SELECT cu.credit_usage_id
519 , substrb(cur.user_code,1,5) curr
520 , cpa.currency_code prof_curr
521 FROM hz_customer_profiles cp
522 , hz_cust_profile_amts cpa
523 , hz_credit_usages cu
524 , hz_credit_usage_rules cur
525 WHERE cp.cust_account_id = p_entity_id
529 AND cpa.cust_acct_profile_amt_id <> l_cust_acct_profile_amt_id
526 AND cp.site_use_id IS NULL
527 AND cp.cust_account_profile_id = cpa.cust_account_profile_id
528 AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
530 AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
531 AND NVL(TRUNC(cpa.expiration_date) , TRUNC(SYSDATE) )
532 <= TRUNC(SYSDATE)
533 AND NVL (cur.include_all_flag, 'N') = 'N'
534 AND cur.usage_type = 'CURRENCY'
535 AND cur.user_code NOT IN (
536 SELECT cr2.user_code from
537 hz_credit_usage_rules cr2
538 WHERE cr2.credit_usage_rule_set_id = p_rule_set_id
539 AND cr2.usage_type = 'CURRENCY' and
540 NVL (cr2.include_all_flag, 'N') = 'N'
541 AND NVL(cr2.exclude_flag,'N') = 'Y'
542 )
543 AND NVL(cur.exclude_flag,'N') = 'N' ;
544
545
546 cust_check_case2_no_incl_rec cust_check_case2_no_incl_csr%ROWTYPE ;
547
548
549 CURSOR cust_check_case2_incl_csr IS
550 SELECT cpa.currency_code profile_curr
551 , cu.credit_usage_id
552 FROM hz_customer_profiles cp
553 , hz_cust_profile_amts cpa
554 , hz_credit_usages cu
555 , hz_credit_usage_rules cur
556 WHERE cp.cust_account_id = p_entity_id
557 AND cp.site_use_id IS NULL
558 AND cp.cust_account_profile_id = cpa.cust_account_profile_id
559 AND cpa.cust_acct_profile_amt_id = cu.cust_acct_profile_amt_id
560 AND cpa.cust_acct_profile_amt_id <> l_cust_acct_profile_amt_id
561 AND cu.credit_usage_rule_set_id = cur.credit_usage_rule_set_id
562 AND NVL(TRUNC(cpa.expiration_date), TRUNC(SYSDATE)
563 ) <= TRUNC(SYSDATE)
564 AND NVL (cur.include_all_flag, 'N') = 'Y' ;
565
566
567 cust_check_case2_incl_rec cust_check_case2_incl_csr%ROWTYPE ;
568
569
570 BEGIN
571
572 x_duplicate := 'N' ;
573
574 -- using l_cust_acct_profile_amt_id local variable as this
575 -- API will be used for other cascading features within
576 -- Multi curremcy credit checking project and the
577 -- p_cust_acct_profile_amt_id may not be made available
578
579 l_cust_acct_profile_amt_id := p_cust_acct_profile_amt_id;
580
581 IF p_entity = 'SITE'
582 THEN
583 --------------------- BEGIN SITE ----------------------
584 BEGIN
585 OPEN site_check_case1_no_incl_csr ;
586 FETCH site_check_case1_no_incl_csr
587 INTO site_check_case1_no_incl_rec ;
588
589
590 IF site_check_case1_no_incl_csr%NOTFOUND
591 THEN
592 x_duplicate := 'N' ;
593 x_dupl_curr := NULL ;
594 --FND_MESSAGE.DEBUG(' site - 1 no found');
595
596 ELSE
597 x_duplicate := 'Y' ;
598 x_dupl_curr := site_check_case1_no_incl_rec.curr ;
599
600 --DBMS_OUTPUT.PUT_LINE(' site - 1 ');
601 END IF;
602
603 CLOSE site_check_case1_no_incl_csr ;
604
605 IF x_duplicate = 'N'
606 THEN
607 l_usage_curr := NULL ;
608
609 OPEN SELECT_USAGE_CURR_CSR ;
610 LOOP
611 FETCH SELECT_USAGE_CURR_CSR
612 INTO l_usage_curr ;
613
614 IF SELECT_USAGE_CURR_CSR%NOTFOUND
615 THEN
616 l_usage_curr := NULL ;
617 EXIT ;
618 END IF;
619
620 --DBMS_OUTPUT.PUT_LINE(' l_usage_curr = '|| l_usage_curr);
621
622 OPEN site_check_case1_incl_csr ( l_usage_curr );
623 FETCH site_check_case1_incl_csr
624 INTO site_check_case1_incl_rec ;
625
626 IF site_check_case1_incl_csr%NOTFOUND
627 THEN
628 x_duplicate := 'N' ;
629 x_dupl_curr := NULL ;
630
631 --DBMS_OUTPUT.PUT_LINE(' site - 2 no found');
632
633 ELSE
634 x_duplicate := 'Y' ;
635 x_dupl_curr := l_usage_curr ;
636
637 CLOSE site_check_case1_incl_csr ;
638
639 --DBMS_OUTPUT.PUT_LINE(' site - 2 ');
640
641 EXIT ;
642
643 END IF;
644
645 CLOSE site_check_case1_incl_csr ;
646
647 END LOOP;
648 CLOSE SELECT_USAGE_CURR_CSR ;
649 --DBMS_OUTPUT.PUT_LINE(' site - 2 - Out of LOOP ');
650
651 END IF;
652
653 IF NVL(p_include_all,'N') = 'Y'
654 THEN
655 IF x_duplicate = 'N'
656 THEN
657 OPEN site_check_case2_no_incl_csr ;
658 FETCH site_check_case2_no_incl_csr
659 INTO site_check_case2_no_incl_rec ;
660
661
662 IF site_check_case2_no_incl_csr%NOTFOUND
663 THEN
664 x_duplicate := 'N' ;
665 x_dupl_curr := NULL ;
666
667 --DBMS_OUTPUT.PUT_LINE(' site - 3 no found');
668
669 ELSE
670
671 x_duplicate := 'Y' ;
672 x_dupl_curr := site_check_case2_no_incl_rec.curr ;
673
674 --DBMS_OUTPUT.PUT_LINE(' site - 3 ');
675
676 END IF;
677
678 CLOSE site_check_case2_no_incl_csr ;
679
680 END IF;
681
682 IF x_duplicate = 'N'
683 THEN
684 OPEN site_check_case2_incl_csr ;
685 FETCH site_check_case2_incl_csr
686 INTO site_check_case2_incl_rec ;
687
688
689 IF site_check_case2_incl_csr%NOTFOUND
693
690 THEN
691 x_duplicate := 'N' ;
692 x_dupl_curr := NULL;
694 -- DBMS_OUTPUT.PUT_LINE(' site - 4 no found');
695
696 ELSE
697 x_duplicate := 'Y' ;
698 x_dupl_curr := 'ALL Currency' ;
699 -- DBMS_OUTPUT.PUT_LINE(' site - 4 ');
700 END IF;
701
702 CLOSE site_check_case2_incl_csr ;
703
704 END IF;
705 END IF; -- case2
706 END ; -- End Site
707
708 --------------- End SITE --------------------------------
709
710
711 --------------------- BEGIN CUST ----------------------
712
713 ELSIF p_entity = 'CUSTOMER'
714 THEN
715
716 BEGIN
717
718 OPEN CUST_check_case1_no_incl_csr ;
719 FETCH CUST_check_case1_no_incl_csr
720 INTO CUST_check_case1_no_incl_rec ;
721
722
723 IF CUST_check_case1_no_incl_csr%NOTFOUND
724 THEN
725 x_duplicate := 'N' ;
726 x_dupl_curr := NULL ;
727 --FND_MESSAGE.DEBUG(' CUST - 1 no found');
728
729 ELSE
730 x_duplicate := 'Y' ;
731 x_dupl_curr := CUST_check_case1_no_incl_rec.curr ;
732
733 --FND_MESSAGE.DEBUG(' CUST - 1 ');
734 END IF;
735
736 CLOSE CUST_check_case1_no_incl_csr ;
737
738 IF x_duplicate = 'N'
739 THEN
740 l_usage_curr := NULL ;
741
742 OPEN SELECT_USAGE_CURR_CSR ;
743 LOOP
744 FETCH SELECT_USAGE_CURR_CSR
745 INTO l_usage_curr ;
746
747 IF SELECT_USAGE_CURR_CSR%NOTFOUND
748 THEN
749 l_usage_curr := NULL ;
750 EXIT ;
751 END IF;
752
753 --FND_MESSAGE.DEBUG(' l_usage_curr = '|| l_usage_curr);
754
755 OPEN CUST_check_case1_incl_csr ( l_usage_curr );
756 FETCH CUST_check_case1_incl_csr
757 INTO CUST_check_case1_incl_rec ;
758
759 IF CUST_check_case1_incl_csr%NOTFOUND
760 THEN
761 x_duplicate := 'N' ;
762 x_dupl_curr := NULL ;
763
764 --FND_MESSAGE.DEBUG(' CUST - 2 no found');
765
766 ELSE
767
768 x_duplicate := 'Y' ;
769 x_dupl_curr := l_usage_curr ;
770
771 CLOSE CUST_check_case1_incl_csr ;
772
773 --FND_MESSAGE.DEBUG(' CUST - 2 ');
774
775 EXIT ;
776
777 END IF;
778
779 CLOSE CUST_check_case1_incl_csr ;
780
781 END LOOP;
782 CLOSE SELECT_USAGE_CURR_CSR ;
783 --FND_MESSAGE.DEBUG(' CUST - 2 - Out of LOOP ');
784
785 END IF;
786
787 IF NVL(p_include_all,'N') = 'Y'
788 THEN
789 IF x_duplicate = 'N'
790 THEN
791 OPEN CUST_check_case2_no_incl_csr ;
792 FETCH CUST_check_case2_no_incl_csr
793 INTO CUST_check_case2_no_incl_rec ;
794
795
796 IF CUST_check_case2_no_incl_csr%NOTFOUND
797 THEN
798 x_duplicate := 'N' ;
799 x_dupl_curr := NULL ;
800
801 --FND_MESSAGE.DEBUG(' CUST - 3 no found');
802
803 ELSE
804
805 x_duplicate := 'Y' ;
806 x_dupl_curr := CUST_check_case2_no_incl_rec.curr ;
807
808 --FND_MESSAGE.DEBUG(' CUST - 3 ');
809
810 END IF;
811
812 CLOSE CUST_check_case2_no_incl_csr ;
813
814 END IF;
815
816 IF x_duplicate = 'N'
817 THEN
818 OPEN CUST_check_case2_incl_csr ;
819 FETCH CUST_check_case2_incl_csr
820 INTO CUST_check_case2_incl_rec ;
821
822
823 IF CUST_check_case2_incl_csr%NOTFOUND
824 THEN
825 x_duplicate := 'N' ;
826 x_dupl_curr := NULL ;
827
828 --FND_MESSAGE.DEBUG(' CUST - 4 no found');
829
830 ELSE
831 x_duplicate := 'Y' ;
832 x_dupl_curr := 'ALL Currency' ;
833 --FND_MESSAGE.DEBUG(' CUST - 4 ');
834 END IF;
835
836 CLOSE CUST_check_case2_incl_csr ;
837
838 END IF;
839 END IF; -- case2
840
841 END ; -- Customer
842
843 --------------- End CUST --------------------------------
844
845 --------------------- BEGIN PARTY ----------------------
846
847 ELSIF p_entity = 'PARTY'
848 THEN
849
850 BEGIN
851
852 OPEN party_check_case1_no_incl_csr ;
853 FETCH party_check_case1_no_incl_csr
854 INTO party_check_case1_no_incl_rec ;
855
856
857 IF party_check_case1_no_incl_csr%NOTFOUND
858 THEN
859 x_duplicate := 'N' ;
860 x_dupl_curr := NULL ;
861 --FND_MESSAGE.DEBUG(' PARTY - 1 no found');
862
863 ELSE
864 x_duplicate := 'Y' ;
865 x_dupl_curr := party_check_case1_no_incl_rec.curr ;
866
867 --FND_MESSAGE.DEBUG(' PARTY - 1 ');
868 END IF;
869
870 CLOSE party_check_case1_no_incl_csr ;
871
872 IF x_duplicate = 'N'
873 THEN
874 l_usage_curr := NULL ;
875
876 OPEN SELECT_USAGE_CURR_CSR ;
877 LOOP
878 FETCH SELECT_USAGE_CURR_CSR
879 INTO l_usage_curr ;
883 l_usage_curr := NULL ;
880
881 IF SELECT_USAGE_CURR_CSR%NOTFOUND
882 THEN
884 EXIT ;
885 END IF;
886
887 --FND_MESSAGE.DEBUG(' l_usage_curr = '|| l_usage_curr);
888
889 OPEN party_check_case1_incl_csr ( l_usage_curr );
890 FETCH party_check_case1_incl_csr
891 INTO party_check_case1_incl_rec ;
892
893 IF party_check_case1_incl_csr%NOTFOUND
894 THEN
895 x_duplicate := 'N' ;
896 x_dupl_curr := NULL ;
897
898 --FND_MESSAGE.DEBUG(' PARTY - 2 no found');
899
900 ELSE
901
902 x_duplicate := 'Y' ;
903 x_dupl_curr := l_usage_curr ;
904
905 CLOSE party_check_case1_incl_csr ;
906
907 --FND_MESSAGE.DEBUG(' PARTY - 2 ');
908
909 EXIT ;
910
911 END IF;
912
913 CLOSE party_check_case1_incl_csr ;
914
915 END LOOP;
916 CLOSE SELECT_USAGE_CURR_CSR ;
917 --FND_MESSAGE.DEBUG(' PARTY - 2 - Out of LOOP ');
918
919 END IF;
920
921 IF NVL(p_include_all,'N') = 'Y'
922 THEN
923 IF x_duplicate = 'N'
924 THEN
925 OPEN party_check_case2_no_incl_csr ;
926 FETCH party_check_case2_no_incl_csr
927 INTO party_check_case2_no_incl_rec ;
928
929
930 IF party_check_case2_no_incl_csr%NOTFOUND
931 THEN
932 x_duplicate := 'N' ;
933 x_dupl_curr := NULL ;
934
935 --FND_MESSAGE.DEBUG(' PARTY - 3 no found');
936
937 ELSE
938
939 x_duplicate := 'Y' ;
940 x_dupl_curr := party_check_case2_no_incl_rec.curr ;
941
942 --FND_MESSAGE.DEBUG(' PARTY - 3 ');
943
944 END IF;
945
946 CLOSE party_check_case2_no_incl_csr ;
947
948 END IF;
949
950 IF x_duplicate = 'N'
951 THEN
952 OPEN party_check_case2_incl_csr ;
953 FETCH party_check_case2_incl_csr
954 INTO party_check_case2_incl_rec ;
955
956
957 IF party_check_case2_incl_csr%NOTFOUND
958 THEN
959 x_duplicate := 'N' ;
960 x_dupl_curr := NULL ;
961
962 --FND_MESSAGE.DEBUG(' PARTY - 4 no found');
963
964 ELSE
965 x_duplicate := 'Y' ;
966 x_dupl_curr := 'ALL Currency' ;
967 --FND_MESSAGE.DEBUG(' PARTY - 4 ');
968 END IF;
969
970 CLOSE party_check_case2_incl_csr ;
971
972 END IF;
973 END IF; -- case2
974
975 END ; -- Party
976
977 --------------- End Party --------------------------------
978
979
980 ELSE
981 x_duplicate := 'Y' ;
982 x_dupl_curr := 'INVALID ENTITY' ;
983 END IF;
984
985 EXCEPTION
986 WHEN OTHERS THEN
987 RAISE ;
988 END Check_Duplicate_all ;
989
990
991
992 /*-------------------------------------------------------------
993 PROCEDURE: delete_credit_usages
994 COMMENTS: This procedure will accept the
995 cust_acct_profile_amt_id as input and
996 remove records from the multi currency credit checking
997 usages table HZ_CREDIT_USAGES for this ID
998 -----------------------------------------------------------------*/
999 PROCEDURE delete_credit_usages
1000 ( p_cust_acct_profile_amt_id IN NUMBER
1001 , X_return_status OUT NOCOPY VARCHAR2
1002 , X_msg_count OUT NOCOPY NUMBER
1003 , X_msg_data OUT NOCOPY VARCHAR2
1004 ) IS
1005
1006 BEGIN
1007 -- Delete the Rule set assigned for a given
1008 -- profile amt id
1009
1010 HZ_MGD_MASS_UPDATE_REP_GEN.log
1011 ( p_priority => HZ_MGD_MASS_UPDATE_REP_GEN.G_LOG_PROCEDURE
1012 , p_msg => '>> delete_credit_usages' );
1013
1014 X_return_status := 'S' ;
1015
1016 DELETE FROM
1017 HZ_CREDIT_USAGES
1018 WHERE CUST_ACCT_PROFILE_AMT_ID
1019 = p_cust_acct_profile_amt_id ;
1020
1021 HZ_MGD_MASS_UPDATE_REP_GEN.log
1022 ( p_priority => HZ_MGD_MASS_UPDATE_REP_GEN.G_LOG_PROCEDURE
1023 , p_msg => '<< delete_credit_usages' );
1024
1025 EXCEPTION
1026 WHEN OTHERS THEN
1027 X_return_status := 'U' ;
1028 END delete_credit_usages ;
1029
1030
1031 --------------------------------------------------------------------
1032 -- PROCEDURE cascade_credit_usage_rules
1033 -- The procedure accepts two input parameters
1034 -- a) p_cust_acct_profile_amt_id
1035 -- This is the new customer / site profile amount id that is
1036 -- being created
1037 -- b) p_profile_class_amt_id
1038 -- This is the profile class amt id from which the new
1039 -- new customer / site profile amount id is created
1040 -------------------------------------------------------------------
1041 PROCEDURE cascade_credit_usage_rules
1042 ( p_cust_acct_profile_amt_id IN NUMBER
1043 , p_cust_profile_id IN NUMBER
1044 , p_profile_class_amt_id IN NUMBER
1045 , p_profile_class_id IN NUMBER
1046 , X_return_status OUT NOCOPY VARCHAR2
1047 , X_msg_count OUT NOCOPY NUMBER
1048 , X_msg_data OUT NOCOPY VARCHAR2
1049 )
1050 IS
1051
1052 l_entity VARCHAR2(30);
1053 l_entity_id NUMBER;
1054 l_cust_account_id NUMBER;
1055 l_include_all VARCHAR2(1);
1056 l_duplicate VARCHAR2(1);
1060 l_party_name VARCHAR2(50);
1057 l_duplicate_curr VARCHAR2(30);
1058 l_cust_name VARCHAR2(50);
1059 l_site_name VARCHAR2(50);
1061 l_count NUMBER;
1062
1063
1064
1065 CURSOR rule_set_csr IS
1066 SELECT
1067 credit_usage_rule_set_id
1068 FROM HZ_CREDIT_USAGES
1069 WHERE profile_class_amount_id = p_profile_class_amt_id;
1070
1071 RULE_SET_CSR_REC rule_set_csr%ROWTYPE;
1072
1073 l_id NUMBER;
1074 l_cust_account_profile_id NUMBER;
1075
1076 BEGIN
1077
1078 HZ_MGD_MASS_UPDATE_REP_GEN.log
1079 ( p_priority => HZ_MGD_MASS_UPDATE_REP_GEN.G_LOG_PROCEDURE
1080 , p_msg => '>> cascade_credit_usage_rules' );
1081
1082 -- The API will duplicate the rule set assignments
1083 -- made with the profile class limit currency with
1084 -- profile amt currency being created when the
1085 -- profile class amt currency ic used as reference
1086 -- The information is currently stores in the
1087 -- HZ_credit_usages table
1088
1089 -- The logic is to replicate the exact image of the rule sets
1090 -- assignments made with the profile class amount currency
1091 -- so the exiting rule sets assigned with the
1092 -- cust_profile_amt currency is removed first
1093
1094 -- Before the actual cascade , the customer/site profile
1095 -- is verified to check to make sure that the cascade will
1096 -- not cause any duplicates in the currency usage
1097 -- assignments
1098
1099 X_return_status := 'S' ;
1100
1101 ---------check duplicates------------------
1102 FOR rule_set_csr_rec IN rule_set_csr
1103 LOOP
1104 BEGIN
1105 SELECT credit_usage_rule_id
1106 INTO l_id
1107 FROM hz_credit_usage_rules
1108 WHERE credit_usage_rule_set_id
1109 = rule_set_csr_rec.credit_usage_rule_set_id
1110 AND user_code IS NULL;
1111
1112 l_include_all := 'Y' ;
1113
1114 EXCEPTION
1115 when no_data_found
1116 THEN
1117 l_include_all := 'N' ;
1118
1119 WHEN TOO_MANY_ROWS
1120 THEN
1121 l_include_all := 'Y' ;
1122
1123 END ;
1124
1125
1126 BEGIN
1127 SELECT
1128 cust_account_id
1129 , DECODE(site_use_id,NULL,'CUSTOMER','SITE') entity_type
1130 , DECODE(site_use_id,NULL,cust_account_id,site_use_id) entity_id
1131 , cust_account_profile_id
1132 INTO
1133 l_cust_account_id
1134 , l_entity
1135 , l_entity_id
1136 , l_cust_account_profile_id
1137 FROM
1138 hz_cust_profile_amts
1139 WHERE cust_acct_profile_amt_id =
1140 p_cust_acct_profile_amt_id ;
1141
1142 IF l_cust_account_id=-1
1143 THEN
1144
1145 l_entity:='PARTY';
1146
1147 SELECT party_id
1148 INTO
1149 l_entity_id
1150 FROM hz_customer_profiles
1151 WHERE cust_account_profile_id=l_cust_account_profile_id;
1152
1153 END IF;
1154
1155 EXCEPTION
1156 WHEN NO_DATA_FOUND
1157 THEN
1158 l_entity := NULL ;
1159 l_entity_id := NULL ;
1160 L_cust_account_id := NULL;
1161
1162 WHEN TOO_MANY_ROWS
1163 THEN
1164 l_entity := NULL ;
1165 l_entity_id := NULL ;
1166 l_cust_account_id := NULL;
1167
1168 END ;
1169
1170 /*DBMS_OUTPUT.PUT_LINE('Rule set ID = '||
1171 rule_set_csr_rec.credit_usage_rule_set_id );
1172
1173 DBMS_OUTPUT.PUT_LINE('l_entity = '||
1174 l_entity );
1175
1176 DBMS_OUTPUT.PUT_LINE('l_entity_id '||
1177 l_entity_id );
1178
1179 DBMS_OUTPUT.PUT_LINE('l_cust_account_id '||
1180 l_cust_account_id );
1181
1182 DBMS_OUTPUT.PUT_LINE('l_include_all '||
1183 l_include_all);
1184 */
1185
1186 Check_Duplicate_all
1187 ( p_rule_set_id =>
1188 rule_set_csr_rec.credit_usage_rule_set_id
1189 , p_entity => l_entity
1190 , p_entity_id => l_entity_id
1191 , p_cust_account_id => l_cust_account_id
1192 , p_include_all => l_include_all
1193 , p_cust_acct_profile_amt_id => p_cust_acct_profile_amt_id
1194 , x_duplicate => l_duplicate
1195 , x_dupl_curr => l_duplicate_curr
1196 );
1197
1198 HZ_MGD_MASS_UPDATE_REP_GEN.log
1199 ( p_priority => HZ_MGD_MASS_UPDATE_REP_GEN.G_LOG_STATEMENT
1200 , p_msg => ' Check_Duplicate_all returns '||l_duplicate);
1201
1202
1203 IF l_duplicate = 'Y'
1204 THEN
1205 IF l_entity='PARTY'
1206 THEN
1207 Get_entity_name
1208 ( p_entity_id => l_entity_id
1209 , p_entity => l_entity
1210 , p_cust_account_id=>l_cust_account_id
1211 , x_party_name => l_party_name
1212 , x_cust_name => l_cust_name
1213 , x_site_name => l_site_name
1214 );
1215
1216 HZ_MGD_MASS_UPDATE_REP_GEN.Add_Exp_Item
1217 ( p_party => l_party_name
1218 , p_customer => NULL
1219 , p_site => NULL
1220 );
1221
1222 ELSIF l_entity='CUSTOMER'
1223 THEN
1224 Get_entity_name
1225 ( p_entity_id => l_entity_id
1226 , p_entity => l_entity
1227 , p_cust_account_id=>l_cust_account_id
1228 , x_party_name => l_party_name
1229 , x_cust_name => l_cust_name
1230 , x_site_name => l_site_name
1231 );
1235 , p_customer => l_cust_name
1232
1233 HZ_MGD_MASS_UPDATE_REP_GEN.Add_Exp_Item
1234 ( p_party => NULL
1236 , p_site => NULL
1237 );
1238 ELSIF l_entity='SITE'
1239 THEN
1240
1241 Get_entity_name
1242 ( p_entity_id => l_entity_id
1243 , p_entity => l_entity
1244 , p_cust_account_id=>l_cust_account_id
1245 , x_party_name => l_party_name
1246 , x_cust_name => l_cust_name
1247 , x_site_name => l_site_name
1248 );
1249
1250 HZ_MGD_MASS_UPDATE_REP_GEN.Add_Exp_Item
1251 ( p_party => NULL
1252 , p_customer => l_cust_name
1253 , p_site => l_site_name
1254 );
1255 END IF;
1256
1257 EXIT ;
1258 END IF;
1259 END LOOP; -- check duplicates
1260
1261
1262 ---DBMS_OUTPUT.PUT_LINE('l_duplicate = '|| l_duplicate );
1263
1264
1265 IF l_duplicate <> 'Y'
1266 THEN
1267 ---DBMS_OUTPUT.PUT_LINE('Call delete_credit_usages ');
1268
1269 Delete_credit_usages
1270 ( p_cust_acct_profile_amt_id => p_cust_acct_profile_amt_id
1271 , X_return_status => X_return_status
1272 , X_msg_count => X_msg_count
1273 , X_msg_data => X_msg_data
1274 );
1275
1276
1277 END IF;
1278
1279 IF X_return_status = 'S'
1280 AND l_duplicate <> 'Y'
1281 THEN
1282 ---DBMS_OUTPUT.PUT_LINE('Delete SUCCESS, call INSERT ');
1283
1284 INSERT INTO HZ_CREDIT_USAGES
1285 ( CREDIT_USAGE_ID
1286 , CREDIT_PROFILE_AMT_ID
1287 , CUST_ACCT_PROFILE_AMT_ID
1288 , PROFILE_CLASS_AMOUNT_ID
1289 , CREDIT_USAGE_RULE_SET_ID
1290 , CREATION_DATE
1291 , CREATED_BY
1292 , LAST_UPDATE_DATE
1293 , LAST_UPDATED_BY
1294 , LAST_UPDATE_LOGIN
1295 , PROGRAM_APPLICATION_ID
1296 , PROGRAM_ID
1297 , PROGRAM_UPDATE_DATE
1298 , REQUEST_ID
1299 , ATTRIBUTE_CATEGORY
1300 , ATTRIBUTE1
1301 , ATTRIBUTE2
1302 , ATTRIBUTE3
1303 , ATTRIBUTE4
1304 , ATTRIBUTE5
1305 , ATTRIBUTE6
1306 , ATTRIBUTE7
1307 , ATTRIBUTE8
1308 , ATTRIBUTE9
1309 , ATTRIBUTE10
1310 , ATTRIBUTE11
1311 , ATTRIBUTE12
1312 , ATTRIBUTE13
1313 , ATTRIBUTE14
1314 , ATTRIBUTE15
1315 ) SELECT
1316 HZ_CREDIT_USAGES_S.NEXTVAL
1317 , NULL
1318 , p_cust_acct_profile_amt_id
1319 , NULL
1320 , cu.CREDIT_USAGE_RULE_SET_ID
1321 , SYSDATE
1322 , G_user_id
1323 , SYSDATE
1324 , G_user_id
1325 , G_login_id
1326 , G_program_application_id
1327 , G_program_id
1328 , SYSDATE
1329 , G_request_id
1330 , cu.ATTRIBUTE_CATEGORY
1331 , cu.ATTRIBUTE1
1332 , cu.ATTRIBUTE2
1333 , cu.ATTRIBUTE3
1334 , cu.ATTRIBUTE4
1335 , cu.ATTRIBUTE5
1336 , cu.ATTRIBUTE6
1337 , cu.ATTRIBUTE7
1338 , cu.ATTRIBUTE8
1339 , cu.ATTRIBUTE9
1340 , cu.ATTRIBUTE10
1341 , cu.ATTRIBUTE11
1345 , cu.ATTRIBUTE15
1342 , cu.ATTRIBUTE12
1343 , cu.ATTRIBUTE13
1344 , cu.ATTRIBUTE14
1346 FROM
1347 HZ_CREDIT_USAGES cu
1348 WHERE cu.PROFILE_CLASS_AMOUNT_ID = p_profile_class_amt_id ;
1349
1350 END IF;
1351
1352 EXCEPTION
1353 WHEN OTHERS THEN
1354 X_return_status := 'U' ;
1355
1356 HZ_MGD_MASS_UPDATE_REP_GEN.log
1357 ( p_priority => HZ_MGD_MASS_UPDATE_REP_GEN.G_LOG_PROCEDURE
1358 , p_msg => '<< cascade_credit_usage_rules' );
1359
1360 END cascade_credit_usage_rules ;
1361
1362 --========================================================================
1363 -- PROCEDURE : Mass_Update_Usage_Rules PUBLIC
1364 -- PARAMETERS: p_profile_class_id Profile Class ID
1365 -- p_currency_code Currency Code
1366 -- p_profile_class_amount_id
1367 -- x_errbuf error buffer
1368 -- x_retcode 0 success, 1 warning, 2 error
1369 --
1370 -- COMMENT : This is the concurrent program for Mass update credit usages
1371 --
1372 --========================================================================
1373 PROCEDURE Mass_Update_Usage_Rules
1374 ( p_profile_class_id IN NUMBER
1375 , p_currency_code IN VARCHAR2
1376 , p_profile_class_amount_id IN NUMBER
1377 , x_errbuf OUT NOCOPY VARCHAR2
1378 , x_retcode OUT NOCOPY VARCHAR2
1379 )
1380
1381 IS
1382 l_return_status VARCHAR2(1);
1383 l_msg_count NUMBER;
1384 l_msg_data VARCHAR2(2000);
1385 l_cust_count NUMBER;
1386 l_count NUMBER;
1387
1388 CURSOR cust_prof_csr
1389 ( p_profile_class_id NUMBER
1390 , p_currency_code VARCHAR2
1391 )
1392 IS
1393 SELECT cpa.cust_acct_profile_amt_id
1394 , cp.cust_account_profile_id
1395 , NVL(cp.site_use_id, cp.cust_account_id) entity_id
1396 , cp.cust_account_id
1397 , DECODE(cp.site_use_id,NULL,'CUSTOMER','SITE') entity_type
1398 FROM hz_cust_profile_amts cpa
1399 , hz_customer_profiles cp
1400 WHERE cp.cust_account_profile_id = cpa.cust_account_profile_id
1401 AND cp.profile_class_id = p_profile_class_id
1402 AND cpa.currency_code = NVL(p_currency_code,currency_code) ;
1403
1404
1405
1406 BEGIN
1407 HZ_MGD_MASS_UPDATE_REP_GEN.log
1408 ( p_priority => HZ_MGD_MASS_UPDATE_REP_GEN.G_LOG_PROCEDURE
1409 , p_msg => '>> Mass_Update_Usage_Rules' );
1410
1411 HZ_MGD_MASS_UPDATE_REP_GEN.log
1412 ( p_priority => HZ_MGD_MASS_UPDATE_REP_GEN.G_LOG_STATEMENT
1413 , p_msg => ' Starting loop for customer profiles for
1414 p_profile_class_id='||TO_CHAR(p_profile_class_id)
1415 ||'p_currency_code='||p_currency_code
1416 );
1417
1418 ----initilize count
1419 l_cust_count:=0;
1420
1421 --Cascade insert usages for customer/site profiles
1422 FOR cust_prof_csr_rec IN cust_prof_csr( p_profile_class_id=> p_profile_class_id
1423 , p_currency_code => p_currency_code)
1424 LOOP
1425 HZ_MGD_MASS_UPDATE_REP_GEN.log
1426 ( p_priority => HZ_MGD_MASS_UPDATE_REP_GEN.G_LOG_STATEMENT
1427 , p_msg => ' HZ_CREDIT_USAGES_CASCADE_PKG.Cascade_credit_usage_rules for
1428 p_cust_acct_profile_amt_id='||TO_CHAR(cust_prof_csr_rec.cust_acct_profile_amt_id)
1429 ||' p_profile_class_amt_id='||TO_CHAR(p_profile_class_amount_id)
1430 );
1431
1432 ------check if there are any credit check rule set assigned
1433 ------to the profile class
1434 SELECT
1435 COUNT(*)
1436 INTO
1437 l_count
1438 FROM HZ_CREDIT_USAGES
1439 WHERE profile_class_amount_id = p_profile_class_amount_id;
1440
1441 IF l_count=0
1442 THEN
1443 -------cascade delete -------------------
1444 Delete_credit_usages
1445 ( p_cust_acct_profile_amt_id => cust_prof_csr_rec.cust_acct_profile_amt_id
1446 , X_return_status => l_return_status
1447 , X_msg_count => l_msg_count
1448 , X_msg_data => l_msg_data
1449 );
1450
1451 COMMIT;
1452
1453 ELSE
1454 --------continue cascade
1455
1456 Cascade_credit_usage_rules
1457 ( p_cust_acct_profile_amt_id => cust_prof_csr_rec.cust_acct_profile_amt_id
1458 , p_cust_profile_id => NULL
1459 , p_profile_class_amt_id => p_profile_class_amount_id
1460 , p_profile_class_id => NULL
1461 , x_return_status => l_return_status
1462 , x_msg_count => l_msg_count
1463 , x_msg_data => l_msg_data
1464 );
1465
1466 IF l_return_status='S'
1467 THEN
1468 l_cust_count:=l_cust_count+1;
1469 END IF;
1470
1471 COMMIT;
1472
1473 END IF;
1474
1475 END LOOP;
1476
1477 HZ_MGD_MASS_UPDATE_REP_GEN.G_PROF_NUMBER:=l_cust_count;
1478
1479
1480
1481 HZ_MGD_MASS_UPDATE_REP_GEN.log
1482 ( p_priority => HZ_MGD_MASS_UPDATE_REP_GEN.G_LOG_STATEMENT
1483 , p_msg => ' End loop for customer profiles ');
1484
1485 HZ_MGD_MASS_UPDATE_REP_GEN.log
1486 ( p_priority => HZ_MGD_MASS_UPDATE_REP_GEN.G_LOG_PROCEDURE
1487 , p_msg => '<< Mass_Update_Usage_Rules' );
1488
1489
1490 EXCEPTION
1491 WHEN OTHERS THEN
1492 HZ_MGD_MASS_UPDATE_REP_GEN.Log( HZ_MGD_MASS_UPDATE_REP_GEN.G_LOG_EXCEPTION,'SQLERRM '|| SQLERRM) ;
1493
1494 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1495 THEN
1496 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, 'Mass_Update_Usage_Rules');
1497 END IF;
1498
1499 x_retcode := 2;
1500 x_errbuf := SUBSTRB(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE),1,255);
1501 ROLLBACK;
1502 RAISE;
1506 END Mass_Update_Usage_Rules;
1503
1504
1505
1507
1508
1509 END HZ_MGD_MASS_UPDATE_MEDIATOR;