[Home] [Help]
PACKAGE BODY: APPS.AR_CMGT_REVIEW_CONC
Source
1 PACKAGE BODY AR_CMGT_REVIEW_CONC AS
2 /* $Header: ARCMPRCB.pls 120.6.12010000.2 2009/06/17 23:33:41 rravikir ship $ */
3
4 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
5
6 PROCEDURE debug (
7 p_message_name IN VARCHAR2 ) IS
8 BEGIN
9 ar_cmgt_util.debug (p_message_name, 'ar.cmgt.plsql.AR_CMGT_REVIEW_CONC' );
10 END;
11
12 PROCEDURE populate_temp_table (
13 p_party_id IN NUMBER,
14 p_cust_account_id IN NUMBER,
15 p_site_use_id IN NUMBER,
16 p_check_list_id IN NUMBER,
17 p_review_cycle IN VARCHAR2,
18 p_next_review_date IN DATE,
19 p_last_review_date IN DATE,
20 p_review_type IN VARCHAR2,
21 p_credit_classification IN VARCHAR2,
22 p_currency_code IN VARCHAR2 ) IS
23
24 -- This procedure will populate global temporary table which will
25 -- be used by Eligibility Reports
26 BEGIN
27
28 IF pg_debug = 'Y' THEN
29 debug ('AR_CMGT_REVIEW_CONC.populate_temp_table(+)' );
30 END IF;
31
32 INSERT INTO OCM_CREDIT_REVIEW_GT (
33 party_id,
34 cust_account_id,
35 site_use_id,
36 check_list_id,
37 review_cycle,
38 next_credit_review_date,
39 last_credit_review_date,
40 review_type,
41 credit_classification,
42 currency_code )
43 VALUES
44 (p_party_id,
45 p_cust_account_id,
46 p_site_use_id,
47 p_check_list_id,
48 p_review_cycle,
49 p_next_review_date,
50 p_last_review_date,
51 p_review_type,
52 p_credit_classification,
53 p_currency_code);
54 IF pg_debug = 'Y' THEN
55 debug ('AR_CMGT_REVIEW_CONC.populate_temp_table(-)' );
56 END IF;
57 EXCEPTION
58 WHEN OTHERS THEN
59 RAISE;
60 END;
61 /*========================================================================
62 | PRIVATE PROCEDURE submit_preview_report
63 |
64 | DESCRIPTION
65 | This procedure submits the periodic cycle review report.
66 |
67 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
68 | ar_cmgt_review_conc.submit_preview_report()
69 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
70 | NONE
71 | PARAMETERS
72 | p_currency_code IN Currency code
73 |
74 | RETURNS : NONE
75 |
76 | KNOWN ISSUES
77 |
78 | NOTES
79 | This concurrent request for periodic cycle review report submitted
80 | before the periodic review records are processed.
81 | MODIFICATION HISTORY
82 | Date Author Description of Changes
83 | 11-OCT-02 S.Nambiar Created
84 |
85 +===========================================================================*/
86
87
88 PROCEDURE submit_preview_report(
89 p_party_id IN NUMBER,
90 p_cust_account_id IN NUMBER,
91 p_currency_code IN VARCHAR2,
92 p_credit_classification IN VARCHAR2,
93 p_profile_class_id IN VARCHAR2,
94 p_check_list_id IN NUMBER,
95 p_review_cycle IN VARCHAR2,
96 p_check_list_match_rule IN VARCHAR2,
97 p_review_cycle_as_of_date IN VARCHAR2,
98 p_cust_level IN VARCHAR2
99 ) IS
100
101 m_request_id NUMBER;
102 l_request_id NUMBER;
103 l_options_ok BOOLEAN;
104
105 BEGIN
106 /* IF PG_DEBUG in ('Y', 'C') THEN
107 --arp_util.debug('ar_cmgt_review_conc.submit_preview_report (+)');
108 END IF; */
109
110 l_request_id := fnd_global.conc_request_id;
111
112 l_options_ok := FND_REQUEST.SET_OPTIONS (
113 implicit => 'NO'
114 , protected => 'YES'
115 , language => ''
116 , territory => '');
117 IF (l_options_ok)
118 THEN
119
120 m_request_id := FND_REQUEST.SUBMIT_REQUEST(
121 application => 'AR'
122 , program => 'ARCMPRPT'
123 , description => ''
124 , start_time => ''
125 , sub_request => FALSE
126 , argument1 => p_review_cycle
127 , argument2 => fnd_date.date_to_canonical(p_review_cycle_as_of_date)
128 , argument3 => p_currency_code
129 , argument4 => p_cust_level
130 , argument5 => fnd_number.number_to_canonical(p_check_list_id)
131 , argument6 => p_check_list_match_rule
132 , argument7 => fnd_number.number_to_canonical(p_party_id)
133 , argument8 => p_cust_account_id
134 , argument9 => p_credit_classification
135 , argument10 => fnd_number.number_to_canonical(p_profile_class_id)
136 , argument11 => chr(0)
137 , argument12 => ''
138 , argument13 => ''
139 , argument14 => ''
140 , argument15 => ''
141 , argument16 => ''
142 , argument17 => ''
143 , argument18 => ''
144 , argument19 => ''
145 , argument20 => ''
146 , argument21 => ''
147 , argument22 => ''
148 , argument23 => ''
149 , argument24 => ''
150 , argument25 => ''
151 , argument26 => ''
152 , argument27 => ''
153 , argument28 => ''
154 , argument29 => ''
155 , argument30 => ''
156 , argument31 => ''
157 , argument32 => ''
158 , argument33 => ''
159 , argument34 => ''
160 , argument35 => ''
161 , argument36 => ''
162 , argument37 => ''
163 , argument38 => ''
164 , argument39 => ''
165 , argument40 => ''
166 , argument41 => ''
167 , argument42 => ''
168 , argument43 => ''
169 , argument44 => ''
170 , argument45 => ''
171 , argument46 => ''
172 , argument47 => ''
173 , argument48 => ''
174 , argument49 => ''
175 , argument50 => ''
176 , argument51 => ''
177 , argument52 => ''
178 , argument53 => ''
179 , argument54 => ''
180 , argument55 => ''
181 , argument56 => ''
182 , argument57 => ''
183 , argument58 => ''
184 , argument59 => ''
185 , argument60 => ''
186 , argument61 => ''
187 , argument62 => ''
188 , argument63 => ''
189 , argument64 => ''
190 , argument65 => ''
191 , argument66 => ''
192 , argument67 => ''
193 , argument68 => ''
194 , argument69 => ''
195 , argument70 => ''
196 , argument71 => ''
197 , argument72 => ''
198 , argument73 => ''
199 , argument74 => ''
200 , argument75 => ''
201 , argument76 => ''
202 , argument77 => ''
203 , argument78 => ''
204 , argument79 => ''
205 , argument80 => ''
206 , argument81 => ''
207 , argument82 => ''
208 , argument83 => ''
209 , argument84 => ''
210 , argument85 => ''
211 , argument86 => ''
212 , argument87 => ''
213 , argument88 => ''
214 , argument89 => ''
215 , argument90 => ''
216 , argument91 => ''
217 , argument92 => ''
218 , argument93 => ''
219 , argument94 => ''
220 , argument95 => ''
221 , argument96 => ''
222 , argument97 => ''
223 , argument98 => ''
224 , argument99 => ''
225 , argument100 => '');
226
227 END IF;
228 /*IF PG_DEBUG in ('Y', 'C') THEN
229 --arp_util.debug('ar_cmgt_review_conc.submit_preview_report (+)');
230 END IF; */
231
232 EXCEPTION
233 WHEN OTHERS THEN
234 IF PG_DEBUG in ('Y', 'C') THEN
235 raise;
236 --arp_util.debug('EXCEPTION :ar_cmgt_review_conc.submit_preview_report '||SQLERRM);
237 END IF;
238 END submit_preview_report;
239
240
241 --------------------------------------------------------------------------------------------------------------------
242 --K.Joshi (IDC) Periodic Credit Review Enhancement - Bug 3824304
243 -- Added the following new parameters:
244 -- 1) review_cycle_as_of_date
245 -- 2) cust_level
246 -- 3) account_number
247 -- Removed the following new parameters
248 -- 1) review_match_rule
249 -- 2) Party Number
250 --Changes Start-----------------------------------------------------------------------------------------------------
251
252
253 PROCEDURE periodic_review(
254 errbuf IN OUT NOCOPY VARCHAR2,
255 retcode IN OUT NOCOPY VARCHAR2,
256 p_review_cycle IN VARCHAR2,
257 p_review_cycle_as_of_date IN VARCHAR2,
258 p_currency_code hz_cust_profile_amts.currency_code%type,
259 p_cust_level IN VARCHAR2,
260 p_check_list_id IN VARCHAR2,
261 p_party_id IN NUMBER,
262 p_cust_account_id IN NUMBER,
263 p_credit_classification IN VARCHAR2,
264 p_profile_class_id IN VARCHAR2,
265 p_processing_option IN VARCHAR2 )
266 IS
267 --Changes End-----------------------------------------------------------------------------------------------------
268
269
270 --Declare Local variables
271 l_review_cycle VARCHAR2(30);
272 l_review_match_rule VARCHAR2(30);
273 l_check_list_id ar_cmgt_check_lists.check_list_id%TYPE;
274 l_match_prev_cf_checklist VARCHAR2(80);
275 l_profile_class_id NUMBER(15);
276 l_credit_classification VARCHAR2(30);
277 l_processing_option VARCHAR2(30);
278 l_credit_request_id NUMBER;
279 l_request_id NUMBER;
280 l_return_status VARCHAR2(1);
281 l_msg_count NUMBER;
282 l_msg_index NUMBER;
283 l_msg_data VARCHAR2(2000);
284 l_party_id hz_parties.party_id%TYPE;
285 l_found_flag VARCHAR2(1) := 'N';
286 l_create_request_flag VARCHAR2(1) := 'N';
287 l_case_folder_count NUMBER;
288 l_review_type VARCHAR2(30);
289 l_currency_code hz_cust_profile_amts.currency_code%type;
290 l_review_cycle_as_of_date hz_customer_profiles.LAST_CREDIT_REVIEW_DATE %type;
291 l_next_review_date hz_customer_profiles.NEXT_CREDIT_REVIEW_DATE%type;
292 l_cust_level VARCHAR2(30);
293 l_cust_account_id VARCHAR2(360);
294 l_last_review_date_for_curr ar_cmgt_credit_requests.application_date%type;
295 l_results VARCHAR2(1);
296 l_exist_trx_currency ar_cmgt_credit_requests.trx_currency%type;
297
298 Type review_row IS RECORD
299 (
300 party_id hz_parties.party_id%type,
301 cust_account_id hz_customer_profiles.cust_account_id%type,
302 site_use_id hz_customer_profiles.site_use_id%type,
303 review_cycle hz_customer_profiles.review_cycle%type,
304 next_review_date hz_customer_profiles.NEXT_CREDIT_REVIEW_DATE%type ,
305 last_review_date hz_customer_profiles.LAST_CREDIT_REVIEW_DATE %type,
306 review_type ar_cmgt_check_lists.review_type%type,
307 credit_classification ar_cmgt_check_lists.credit_classification%type,
308 profile_id hz_customer_profiles.CUST_ACCOUNT_PROFILE_ID%type,
309 cp_credit_classification ar_cmgt_check_lists.credit_classification%type
310 );
311
312 --Changes End------------------------------------------------------------------------------------------------------
313
314
315 Type cur_typ is REF CURSOR;
316 review_cur cur_typ;
317 review_rec review_row;
318 review_cur_str VARCHAR2(12000);
319
320 review_cur_str1 VARCHAR2(4000);
321 review_cur_str2 VARCHAR2(4000);
322 review_cur_str3 VARCHAR2(4000);
323 review_cur_str4 VARCHAR2(4000);
324
325
326
327
328 BEGIN
329 IF pg_debug = 'Y' THEN
330 debug ('AR_CMGT_REVIEW_CONC.periodic_review(+)' );
331 debug (' Review Cycle ' || p_review_cycle );
332 debug (' Review Cycle As of date ' || p_review_cycle_as_of_date );
333 debug (' Currency Code ' || p_currency_code );
334 debug (' Cust Level ' || p_cust_level );
335 debug (' Check List Id ' || p_check_list_id );
336 debug (' Party Id ' || p_party_id );
337 debug (' Cust Account Id ' || p_cust_account_id );
338 debug (' Credit Classification ' || p_credit_classification );
339 debug (' Profile Class Id ' || p_profile_class_id );
340 END IF;
341
342 l_cust_level := p_cust_level;
343
344 IF ( l_cust_level = 'PARTY' ) THEN
345 review_cur_str1 := 'SELECT party.party_id party_id,
346 -99 cust_account_id ,
347 -99 site_use_id ,
348 hcp.review_cycle review_cycle,
349 hcp.NEXT_CREDIT_REVIEW_DATE next_review_date,
350 NVL(hcp.LAST_CREDIT_REVIEW_DATE, party.creation_date) last_review_date,
351 checklist.review_type review_type,
352 checklist.credit_classification credit_classification,
353 hcp.CUST_ACCOUNT_PROFILE_ID profile_id,
354 hcp.credit_classification cp_credit_classification
355 FROM hz_customer_profiles hcp,
356 ar_cmgt_check_lists checklist,
357 hz_parties party
358 WHERE hcp.party_id = NVL(:bnd_party_id ,hcp.party_id)
359 AND :bnd_cust_account_id IS NULL
360 AND hcp.cust_account_id = -1
361 AND hcp.site_use_id IS NULL
362 AND party.party_id = hcp.party_id
363 AND party.status = '||''''||'A'||''''||'
364 AND party.party_type = '||''''||'ORGANIZATION'||''''||'
365 AND nvl(hcp.credit_classification, -99) = NVL(:bnd_credit_classification,
366 nvl(hcp.credit_classification,-99))
367 AND nvl(hcp.profile_class_id,-99) = NVL(:bnd_profile_class_id,nvl(hcp.profile_class_id,-99))
368 AND checklist.check_list_id = :bnd_check_list_id
369 AND nvl(hcp.review_cycle,-99) = NVL(:bnd_review_cycle,nvl(hcp.review_cycle,-99))
370 AND hcp.status ='||'''A''';
371 END IF;
372 IF ( l_cust_level = 'ACCT' ) THEN
373 review_cur_str2 := 'SELECT party.party_id party_id,
374 hcp.cust_account_id cust_account_id,
375 -99 site_use_id,
376 hcp.review_cycle review_cycle,
377 hcp.NEXT_CREDIT_REVIEW_DATE next_review_date,
378 NVL(hcp.LAST_CREDIT_REVIEW_DATE, cust.creation_date) last_review_date,
379 checklist.review_type review_type,
380 checklist.credit_classification credit_classification,
381 hcp.CUST_ACCOUNT_PROFILE_ID profile_id,
382 hcp.credit_classification cp_credit_classification
383 FROM hz_parties party,
384 hz_customer_profiles hcp,
385 ar_cmgt_check_lists checklist,
386 hz_cust_accounts cust
387 WHERE hcp.party_id = NVL(:bnd_party_id ,hcp.party_id)
388 AND party.party_id = hcp.party_id
389 AND hcp.CUST_ACCOUNT_ID = NVL(:bnd_cust_account_id ,hcp.CUST_ACCOUNT_ID)
390 AND hcp.cust_account_id <> -1
391 and hcp.cust_account_id = cust.cust_account_id
392 and cust.status = '||''''||'A'||''''||'
393 AND hcp.site_use_id IS NULL
394 AND party.party_type = '||''''||'ORGANIZATION'||''''||'
395 AND nvl(hcp.credit_classification, -99) = NVL(:bnd_credit_classification,
396 nvl(hcp.credit_classification,-99))
397 AND nvl(hcp.profile_class_id,-99) = NVL(:bnd_profile_class_id,nvl(hcp.profile_class_id,-99))
398 AND checklist.check_list_id = :bnd_check_list_id
399 AND nvl(hcp.review_cycle,-99) = NVL(:bnd_review_cycle,nvl(hcp.review_cycle,-99))
400 AND hcp.status ='||'''A''';
401 END IF;
402 IF (l_cust_level = 'SITE' ) THEN
403 review_cur_str3 := 'SELECT party.party_id party_id,
404 hcp.cust_account_id cust_account_id,
405 hcp.site_use_id site_use_id,
406 hcp.review_cycle review_cycle,
407 hcp.NEXT_CREDIT_REVIEW_DATE next_review_date,
408 NVL(hcp.LAST_CREDIT_REVIEW_DATE, uses.creation_date) last_review_date,
409 checklist.review_type review_type,
410 checklist.credit_classification credit_classification,
411 hcp.CUST_ACCOUNT_PROFILE_ID profile_id,
412 hcp.credit_classification cp_credit_classification
413 FROM hz_parties party,
414 HZ_CUST_SITE_USES_ALL uses ,
415 hz_customer_profiles hcp,
416 ar_cmgt_check_lists checklist
417 WHERE hcp.party_id = NVL(:bnd_party_id,hcp.party_id)
418 AND hcp.CUST_ACCOUNT_ID = NVL(:bnd_cust_account_id ,hcp.CUST_ACCOUNT_ID)
419 AND party.party_id = hcp.party_id
420 AND hcp.cust_account_id <> -1
421 AND hcp.site_use_id IS NOT NULL
422 AND hcp.SITE_USE_ID = uses.SITE_USE_ID
423 AND uses.site_use_code = '||''''||'BILL_TO'||''''||'
424 and uses.status = '||''''||'A'||''''||'
425 AND party.party_type = '||''''||'ORGANIZATION'||''''||'
426 AND nvl(hcp.credit_classification, -99) = NVL(:bnd_credit_classification,
427 nvl(hcp.credit_classification,-99))
428 AND nvl(hcp.profile_class_id,-99) = NVL(:bnd_profile_class_id,nvl(hcp.profile_class_id,-99))
429 AND checklist.check_list_id = :bnd_check_list_id
430 AND nvl(hcp.review_cycle,-99) = NVL(:bnd_review_cycle,nvl(hcp.review_cycle,-99))
431 AND hcp.status ='||'''A''';
432 END IF;
433 IF (l_cust_level = 'ALL') THEN
434 review_cur_str4 := 'SELECT hcp.party_id party_id,
435 DECODE(hcp.cust_account_id,-1,-99,hcp.cust_account_id) cust_account_id,
436 NVL(hcp.site_use_id,-99) site_use_id,
437 hcp.review_cycle review_cycle,
438 hcp.NEXT_CREDIT_REVIEW_DATE next_review_date,
439 NVL(NVL(hcp.LAST_CREDIT_REVIEW_DATE, uses.creation_date), party.creation_date) last_review_date,
440 checklist.review_type,
441 checklist.credit_classification,
442 hcp.CUST_ACCOUNT_PROFILE_ID profile_id,
443 hcp.credit_classification cp_credit_classification
444 FROM hz_customer_profiles hcp,
445 ar_cmgt_check_lists checklist,
446 hz_parties party,
447 HZ_CUST_SITE_USES_ALL uses
448 WHERE party.party_id = hcp.party_id
449 AND :bnd_party_id IS NULL
450 AND :bnd_cust_account_id IS NULL
451 AND party.party_type = '||''''||'ORGANIZATION'||''''||'
452 AND nvl(hcp.credit_classification, -99) = NVL(:bnd_credit_classification,
453 nvl(hcp.credit_classification,-99))
454 AND hcp.profile_class_id = NVL(:bnd_profile_class_id,hcp.profile_class_id)
455 AND checklist.check_list_id = :bnd_check_list_id
456 AND nvl(hcp.review_cycle,-99) = NVL(:bnd_review_cycle,nvl(hcp.review_cycle,-99))
457 AND hcp.site_use_id = uses.site_use_id(+)
458 and uses.site_use_code(+) = '||''''||'BILL_TO'||''''||'
459 AND hcp.status ='||'''A''';
460
461 END IF;
462
463
464 IF (l_cust_level = 'PARTY') THEN
465 review_cur_str := review_cur_str1;
466 END IF;
467
468 IF (l_cust_level = 'ACCT') THEN
469 review_cur_str := review_cur_str2;
470 END IF;
471
472 IF (l_cust_level = 'SITE') THEN
473 review_cur_str := review_cur_str3;
474 END IF;
475
476 IF (l_cust_level = 'ALL') THEN
477 review_cur_str := review_cur_str4;
478
479 END IF;
480
481
482 l_review_cycle := p_review_cycle ;
483 l_check_list_id := FND_NUMBER.CANONICAL_TO_NUMBER(p_check_list_id) ;
484 l_party_id := p_party_id ;
485 l_profile_class_id := FND_NUMBER.CANONICAL_TO_NUMBER(p_profile_class_id);
486 l_credit_classification := p_credit_classification ;
487 l_processing_option := p_processing_option;
488 l_request_id := fnd_global.conc_request_id;
489 l_review_cycle_as_of_date :=
490 trunc(nvl(fnd_date.canonical_to_date(p_review_cycle_as_of_date), sysdate)) ;
491 l_cust_level := p_cust_level;
492 l_cust_account_id := p_cust_account_id;
493 l_currency_code := p_currency_code;
494
495 l_return_status := FND_API.G_RET_STS_SUCCESS;
496
497
498 IF (l_party_id IS NULL)
499 AND (l_cust_account_id IS NOT NULL) THEN
500 SELECT party_id
501 INTO l_party_id
502 FROM HZ_CUST_ACCOUNTS
503 WHERE CUST_ACCOUNT_ID = l_cust_account_id;
504 END IF;
505
506 IF NVL(l_processing_option,'NONE') in ('PROCESS_REVIEWS','BOTH', 'REPORT_ONLY')
507 THEN
508 IF(l_cust_level = 'SITE' OR l_cust_level = 'ACCT' OR l_cust_level = 'PARTY'
509 OR l_cust_level = 'ALL' )
510 THEN
511 OPEN review_cur FOR review_cur_str USING
512 l_party_id,
513 l_cust_account_id,
514 l_credit_classification,
515 l_profile_class_id,
516 l_check_list_id,
517 l_review_cycle;
518 END IF;
519 LOOP
520 FETCH review_cur INTO review_rec;
521 EXIT WHEN review_cur%NOTFOUND;
522 IF pg_debug = 'Y' THEN
523 debug ('Inside Loop' );
524 debug ('Party Id ' || review_rec.party_id );
525 debug ('Cust Account Id ' || review_rec.cust_account_id );
526 debug ('Site Use Id ' || review_rec.site_use_id );
527 END IF;
528 l_found_flag := 'N';
529 l_create_request_flag := 'N';
530
531 -- Now check additional Conditions
532 IF review_rec.review_cycle IS NOT NULL
533 THEN
534 IF review_rec.review_cycle = 'YEARLY' THEN
535 l_next_review_date := trunc(review_rec.last_review_date) + 365;
536 l_last_review_date_for_curr := trunc(l_review_cycle_as_of_date) - 365;
537 ELSIF review_rec.review_cycle = 'HALF_YEARLY' THEN
538 l_next_review_date := trunc(review_rec.last_review_date) + 180;
539 l_last_review_date_for_curr := trunc(l_review_cycle_as_of_date) - 180;
540 ELSIF review_rec.review_cycle = 'QUARTERLY' THEN
541 l_next_review_date := trunc(review_rec.last_review_date) + 90;
542 l_last_review_date_for_curr := trunc(l_review_cycle_as_of_date) - 90;
543 ELSIF review_rec.review_cycle = 'MONTHLY' THEN
544 l_next_review_date := trunc(review_rec.last_review_date) + 30;
545 l_last_review_date_for_curr := trunc(l_review_cycle_as_of_date) - 30;
546 ELSIF review_rec.review_cycle = 'WEEKLY' THEN
547 l_next_review_date := trunc(review_rec.last_review_date) + 7;
548 l_last_review_date_for_curr := trunc(l_review_cycle_as_of_date) - 7;
549 ELSE
550 l_next_review_date := trunc(review_rec.last_review_date) + 1;
551 l_last_review_date_for_curr := trunc(l_review_cycle_as_of_date) - 1;
552 END IF;
553 END IF;
554 IF review_rec.next_review_date IS NOT NULL AND
555 review_rec.next_review_date <= l_review_cycle_as_of_date
556 THEN
557 IF pg_debug = 'Y' THEN
558 debug ('Ist Condition True' );
559 END IF;
560 l_create_request_flag := 'Y';
561 END IF;
562
563 IF review_rec.next_review_date IS NULL AND
564 review_rec.last_review_date IS NOT NULL AND
565 review_rec.review_cycle IS NOT NULL
566 THEN
567 IF pg_debug = 'Y' THEN
568 debug ('2nd Condition True' );
569 END IF;
570 IF l_next_review_date <= l_review_cycle_as_of_date
571 THEN
572 IF pg_debug = 'Y' THEN
573 debug ('2nd Condition True with create_request_flag = Y' );
574 END IF;
575 l_create_request_flag := 'Y';
576 END IF;
577 END IF;
578
579 IF review_rec.next_review_date IS NULL AND
580 review_rec.last_review_date IS NULL AND
581 review_rec.review_cycle IS NOT NULL
582 THEN
583 IF pg_debug = 'Y' THEN
584 debug ('3rd Condition True' );
585 END IF;
586 l_create_request_flag := 'Y';
587 END IF;
588
589 --arp_util.debug('create request flag :'||l_create_request_flag);
590
591 -- Now check if there
592 IF NVL(l_create_request_flag,'N') = 'N'
593 THEN
594 IF review_rec.review_cycle IS NOT NULL
595 THEN
596 BEGIN
597 SELECT trx_currency
598 INTO l_exist_trx_currency
599 FROM ar_cmgt_credit_requests
600 WHERE party_id = review_rec.party_id
601 AND cust_account_id = review_rec.cust_account_id
602 AND site_use_id = review_rec.site_use_id
603 AND source_name = 'AR_PERIODIC_REVIEW'
604 -- AND trx_currency = l_currency_code -- Bug 5149880
605 AND trunc(application_date) between l_last_review_date_for_curr
606 and l_review_cycle_as_of_date;
607
608 -- bug 5159880
609 IF l_exist_trx_currency = l_currency_code
610 THEN
611 IF pg_debug = 'Y' THEN
612 debug ('Currency Matches' );
613 END IF;
614 l_create_request_flag :='N';
615 ELSE
616 IF pg_debug = 'Y' THEN
617 debug ('Currency Does not Match' );
618 END IF;
619 l_create_request_flag :='Y';
620 END IF;
621 -- end 5149880
622
623 EXCEPTION
624 WHEN NO_DATA_FOUND THEN
625 -- bug 5149880
626 -- Checking this cindition to make sure it didn't pick
627 -- unwanted records for which next review details are
628 -- in future as entered in UI. If any of the values are not null
629 -- then the condition would have executed earlier.
630
631 IF ( review_rec.next_review_date IS NOT NULL OR
632 review_rec.last_review_date IS NOT NULL OR
633 review_rec.review_cycle IS NOT NULL)
634 THEN
635 IF pg_debug = 'Y' THEN
636 debug ('No Period Review Exists and No request will be created' );
637 END IF;
638 l_create_request_flag :='N';
639 ELSE
640 IF pg_debug = 'Y' THEN
641 debug ('No Period Review Exists and request will be created' );
642 END IF;
643 l_create_request_flag :='Y';
644 END IF;
645 WHEN OTHERS THEN
646 l_create_request_flag :='N';
647 END;
648 END IF;
649 END IF;
650
651 IF NVL(l_create_request_flag,'N') = 'Y'
652 THEN
653 IF NVL(l_processing_option,'NONE') in ('PROCESS_REVIEWS','BOTH')
654 THEN
655 IF pg_debug = 'Y' THEN
656 debug ('Inside Creating Request' );
657 debug ('Employee Id ' || FND_GLOBAL.employee_id);
658 debug ('Review Type ' || review_rec.review_type);
659 debug ('Classification ' || review_rec.credit_classification);
660 END IF;
661 AR_CMGT_CREDIT_REQUEST_API.create_credit_request
662 (p_api_version => 1.0,
663 p_init_msg_list => FND_API.G_TRUE,
664 p_commit => FND_API.G_FALSE,
665 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
666 x_return_status => l_return_status,
667 x_msg_count => l_msg_count,
668 x_msg_data => l_msg_data,
669 p_application_number => NULL,
670 p_application_date => trunc(sysdate),
671 p_requestor_type => 'EMPLOYEE',
672 p_requestor_id => FND_GLOBAL.employee_id,
673 p_review_type => review_rec.review_type,
674 p_review_cycle => review_rec.review_cycle,
675 p_credit_classification => review_rec.credit_classification,
676 p_requested_amount => NULL,
677 p_requested_currency => l_currency_code,
678 p_trx_amount => NULL,
679 p_trx_currency => l_currency_code,
680 p_credit_type => 'TRADE',
681 p_term_length => NULL,
682 p_credit_check_rule_id => NULL,
683 p_credit_request_status => 'SUBMIT',
684 p_party_id => review_rec.party_id,
685 p_cust_account_id => review_rec.cust_account_id,
686 p_cust_acct_site_id => NULL,
687 p_site_use_id => review_rec.site_use_id,
688 p_contact_party_id => NULL,
689 p_notes => NULL,
690 p_source_org_id => NULL,
691 p_source_user_id => NULL,
692 p_source_resp_id => NULL,
693 p_source_appln_id => NULL,
694 p_source_security_group_id => NULL,
695 p_source_name => 'AR_PERIODIC_REVIEW',
696 p_source_column1 => NULL,
697 p_source_column2 => NULL,
698 p_source_column3 => NULL,
699 p_credit_request_id => l_credit_request_id
700 );
701 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
702 EXIT;
703 END IF;
704 IF pg_debug = 'Y' THEN
705 debug ('Credit Request Created' );
706 END IF;
707 END IF;
708 IF NVL(l_processing_option,'NONE') in ('BOTH', 'REPORT_ONLY' )
709 THEN
710 IF pg_debug = 'Y' THEN
711 debug ('Populate Global Temp Table' );
712 END IF;
713 -- calculate the next review date for report purpose
714 IF review_rec.review_cycle = 'YEARLY' THEN
715 l_next_review_date := trunc(l_review_cycle_as_of_date) + 365;
716 ELSIF review_rec.review_cycle = 'HALF_YEARLY' THEN
717 l_next_review_date := trunc(l_review_cycle_as_of_date) + 180;
718 ELSIF review_rec.review_cycle = 'QUARTERLY' THEN
719 l_next_review_date := trunc(l_review_cycle_as_of_date) + 90;
720 ELSIF review_rec.review_cycle = 'MONTHLY' THEN
721 l_next_review_date := trunc(l_review_cycle_as_of_date) + 30;
722 ELSIF review_rec.review_cycle = 'WEEKLY' THEN
723 l_next_review_date := trunc(l_review_cycle_as_of_date) + 7;
724 ELSE
725 l_next_review_date := trunc(l_review_cycle_as_of_date) + 1;
726 END IF;
727 populate_temp_table (
728 p_party_id => review_rec.party_id,
729 p_cust_account_id => review_rec.cust_account_id,
730 p_site_use_id => review_rec.site_use_id,
731 p_check_list_id => l_check_list_id,
732 p_review_cycle => review_rec.review_cycle,
733 p_next_review_date => l_next_review_date,
734 p_last_review_date => review_rec.last_review_date,
735 p_review_type => review_rec.review_type,
736 p_credit_classification => review_rec.cp_credit_classification,
737 p_currency_code => l_currency_code );
738 END IF; -- if p_from_report
739 END IF; -- if p_create_request_flag = 'Y'
740
741
742 IF NVL(l_processing_option,'NONE') in ('PROCESS_REVIEWS','BOTH')
743 THEN
744 IF l_create_request_flag = 'Y'
745 THEN
746 BEGIN
747 IF pg_debug = 'Y' THEN
748 debug ('Updating HZ table' );
749 END IF;
750 UPDATE HZ_CUSTOMER_PROFILES
751 SET NEXT_CREDIT_REVIEW_DATE =
752 DECODE(review_cycle,
753 'YEARLY', (trunc(sysdate) + 365),
754 'HALF_YEARLY', (trunc(sysdate) + 180),
755 'QUARTERLY', (trunc(sysdate) + 90),
756 'MONTHLY', (trunc(sysdate) + 30),
757 'WEEKLY', (trunc(sysdate) + 7),
758 (trunc(sysdate) + 1)),
759 LAST_CREDIT_REVIEW_DATE = trunc(SYSDATE),
760 LAST_UPDATED_BY = fnd_global.user_id,
761 LAST_UPDATE_DATE = sysdate,
762 last_update_login = fnd_global.login_id
763 WHERE HZ_CUSTOMER_PROFILES.CUST_ACCOUNT_PROFILE_ID = review_rec.profile_id;
764 END;
765 END IF;
766 END IF;
767 END LOOP;
768 END IF; --If review only, or both
769
770 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
771 IF NVL(l_msg_count,0) > 0 Then
772 IF l_msg_count > 1 Then
773 FOR l_count IN 1..l_msg_count LOOP
774 l_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,
775 FND_API.G_FALSE);
776 --arp_util.debug(to_char(l_count)||' : '||l_msg_data);
777 END LOOP;
778 END IF; -- l_msg_count
779 END IF; -- NVL(l_msg_count,0)
780
781 IF l_return_status <> FND_API.G_RET_STS_SUCCESS Then
782 FND_MSG_PUB.Get (FND_MSG_PUB.G_FIRST, FND_API.G_TRUE,l_msg_data, l_msg_index);
783 FND_MESSAGE.Set_Encoded (l_msg_data);
784 app_exception.raise_exception;
785 END IF;
786 END IF;
787 IF pg_debug = 'Y' THEN
788 debug ('AR_CMGT_REVIEW_CONC.periodic_review(-)' );
789 END IF;
790 END;
791
792 END AR_CMGT_REVIEW_CONC;