1 PACKAGE BODY AR_CMGT_CREDIT_REQUEST_API AS
2 /*$Header: ARCMCRAB.pls 120.27.12010000.2 2008/11/03 23:42:41 mraymond ship $ */
3
4 /* bug4414414 : Added paramters p_parent_credit_request_id and p_credit_request_type
5 */
6
7 pg_debug VARCHAR2(1) := nvl(fnd_profile.value('AFLOG_ENABLED'),'N');
8
9 PROCEDURE debug (
10 p_message_name IN VARCHAR2 ) IS
11 BEGIN
12 IF pg_debug = 'Y' THEN
13 ar_cmgt_util.debug (p_message_name, 'ar.cmgt.plsql.AR_CMGT_CREDIT_REQUEST_API' );
14 END IF;
15 END;
16
17
18 PROCEDURE create_credit_request
19 ( p_api_version IN NUMBER,
20 p_init_msg_list IN VARCHAR2 ,
21 p_commit IN VARCHAR2,
22 p_validation_level IN VARCHAR2,
23 x_return_status OUT NOCOPY VARCHAR2,
24 x_msg_count OUT NOCOPY NUMBER,
25 x_msg_data OUT NOCOPY VARCHAR2,
26 p_application_number IN VARCHAR2,
27 p_application_date IN DATE,
28 p_requestor_type IN VARCHAR2,
29 p_requestor_id IN NUMBER, --this happens to be the HR person_id of
30 --the requestor
31 p_review_type IN VARCHAR2,
32 p_credit_classification IN VARCHAR2,
33 p_requested_amount IN NUMBER,
34 p_requested_currency IN VARCHAR2,
35 p_trx_amount IN NUMBER,
36 p_trx_currency IN VARCHAR2,
37 p_credit_type IN VARCHAR2,
38 p_term_length IN NUMBER, --the unit is no of months
39 p_credit_check_rule_id IN NUMBER, --this is the credit check rule from the OM
40 p_credit_request_status IN VARCHAR2, --SAVE or FINISH
41 p_party_id IN NUMBER,
42 p_cust_account_id IN NUMBER,
43 p_cust_acct_site_id IN NUMBER,
44 p_site_use_id IN NUMBER,
45 p_contact_party_id IN NUMBER, --this is the party_id of the pseudo party
46 --created becoz of the contact relationship.
47 p_notes IN VARCHAR2,
48 p_source_org_id IN NUMBER,
49 p_source_user_id IN NUMBER,
50 p_source_resp_id IN NUMBER,
51 p_source_appln_id IN NUMBER,
52 p_source_security_group_id IN NUMBER,
53 p_source_name IN VARCHAR2,
54 p_source_column1 IN VARCHAR2,
55 p_source_column2 IN VARCHAR2,
56 p_source_column3 IN VARCHAR2,
57 p_credit_request_id OUT NOCOPY NUMBER,
58 p_review_cycle IN VARCHAR2 ,
59 p_case_folder_number IN VARCHAR2,
60 p_score_model_id IN NUMBER,
61 p_parent_credit_request_id IN NUMBER DEFAULT NULL,
62 p_credit_request_type IN VARCHAR2 DEFAULT NULL,
63 p_reco IN VARCHAR2 DEFAULT NULL
64 ) IS
65 l_sys_params_rec ar_cmgt_setup_options%rowtype;
66 l_application_number VARCHAR2(30);
67 l_credit_request_id NUMBER(15);
68 l_application_date DATE;
69 l_credit_request_status VARCHAR2(10);
70 l_char VARCHAR2(1);
71 l_credit_type ar_cmgt_credit_requests.credit_type%type;
72 l_isupdateable VARCHAR2(1) := 'N';
73 l_case_folder_id ar_cmgt_case_folders.case_folder_id%type;
74 l_case_folder_status ar_cmgt_case_folders.status%type;
75 p_credit_request_rec OCM_CREDIT_REQUEST_UPDATE_PUB.credit_request_rec;
76 l_parent_cr_status VARCHAR2(15);
77 l_value1 VARCHAR2(60);
78 l_value2 VARCHAR2(60);
79 l_credit_request_type ar_cmgt_credit_requests.credit_request_type%type;
80 l_requestor_type ar_cmgt_credit_requests.requestor_type%type;
81 l_trx_amount NUMBER;
82 l_requested_amount NUMBER;
83 l_requestor_id NUMBER;
84
85 CURSOR system_parameters IS
86 SELECT *
87 FROM ar_cmgt_setup_options;
88
89 CURSOR employee(p_person_id IN NUMBER) is
90 SELECT 'x'
91 FROM PER_ALL_PEOPLE_F
92 WHERE sysdate between effective_start_date and effective_end_date
93 and current_employee_flag = 'Y'
94 and person_id = p_person_id;
95
96 CURSOR cFndUser(cp_user_id NUMBER) is
97 SELECT 'x'
98 FROM fnd_user
99 WHERE sysdate between start_date and nvl(end_date, sysdate)
100 and user_id = cp_user_id;
101
102 BEGIN
103
104 /*------------------------------------+
105 | Standard start of API savepoint |
106 +------------------------------------*/
107
108 SAVEPOINT CREATE_CREDIT_REQ_PVT;
109
110
111 /*--------------------------------------------------------------+
112 | Initialize message list if p_init_msg_list is set to TRUE |
113 +--------------------------------------------------------------*/
114
115 IF FND_API.to_Boolean( p_init_msg_list )
116 THEN
117 FND_MSG_PUB.initialize;
118 END IF;
119
120 IF pg_debug = 'Y'
121 THEN
122 debug ( 'AR_CMGT_CREDIT_REQUEST_API(+)');
123 debug ( 'Application Number ' || p_application_number );
124 debug ( 'Requestor Type ' || p_requestor_type );
125 debug ( 'Requestor Id ' || p_requestor_id );
126 debug ( 'Review Type ' || p_review_type );
127 debug ( 'Classification ' || p_credit_classification );
128 debug ( 'Party Id ' || p_party_id );
129 debug ( 'Account Id' || p_cust_account_id );
130 debug ( 'Site Use Id' || p_site_use_id );
131 debug ( 'Parent Credit request Id ' || p_parent_credit_request_id );
132 debug ( 'Credit request type' || p_credit_request_type );
133 debug ( 'Recommendation ' || p_reco );
134 debug ( 'Status ' || p_credit_request_status );
135 debug ( 'Source Name' || p_source_name );
136 debug ( 'Source Id' || p_source_column1 );
137 debug ( 'Req Currency ' || p_requested_currency );
138 debug ( 'Trx Currency ' || p_trx_currency );
139 END IF;
140
141 /*-----------------------------------------+
142 | Initialize return status to SUCCESS |
143 +-----------------------------------------*/
144
145 x_return_status := FND_API.G_RET_STS_SUCCESS;
146 l_application_date := p_application_date;
147 l_application_number := p_application_number;
148 l_credit_request_status := p_credit_request_status;
149 l_requestor_type := p_requestor_type;
150 l_requestor_id := p_requestor_id;
151
152 IF p_credit_request_type IS NULL
153 THEN
154 l_credit_request_type := 'CREDIT_APP';
155 ELSE
156 l_credit_request_type := p_credit_request_type;
157 END IF;
158
159 IF l_requestor_type IS NULL -- For Backward compatibility
160 THEN
161 l_requestor_type := 'FND_USER/EMPLOYEE';
162 ELSIF l_requestor_type IS NOT NULL AND
163 l_requestor_type NOT IN ('EMPLOYEE', 'FND_USER')
164 THEN
165 IF pg_debug = 'Y'
166 THEN
167 debug ( 'Invalid Requestor Type ' || l_requestor_type );
168 END IF;
169 FND_MESSAGE.SET_NAME('AR','OCM_INVALID_REQUESTOR_TYPE');
170 FND_MSG_PUB.Add;
171 x_msg_data := 'OCM_INVALID_REQUESTOR_TYPE';
172 x_return_status := FND_API.G_RET_STS_ERROR;
173 return;
174 END IF;
175
176 debug ( 'l_requestor_type ' || l_requestor_type );
177
178 IF l_requestor_type = 'FND_USER/EMPLOYEE'
179 THEN
180 /* Not Sure of the Source since p_request_type is passed as NULL
181 In that case first check the requestor ID, if its passed as -1
182 or NULL then take the FND_GLOBAL.USER_ID and set the request
183 type as FND_USER.
184
185 If the Requestor ID is not NULL or <> -1 then check if the
186 Requestor is an Employee
187 Else check if the Requestor is a FND User
188 */
189
190 IF l_requestor_id IS NULL OR l_requestor_id = -1
191 THEN
192 debug('p_requestor_type is passed as NULL, p_requestor_id is NULL or -1, use FND USER');
193 l_requestor_id := FND_GLOBAL.USER_ID;
194 l_requestor_type := 'FND_USER';
195 debug('p_requestor_type is passed as NULL, l_requestor_id:'||
196 l_requestor_id||' l_requestor_type :'||l_requestor_type);
197 ELSE
198 debug('p_requestor_type is passed as NULL, check both EMPLOYEE and FND_USER');
199 OPEN employee(l_requestor_id);
200 FETCH employee INTO l_char;
201 IF employee%NOTFOUND
202 THEN
203 debug('p_requestor_type is passed as NULL, EMPLOYEE check failed');
204 OPEN cFndUser(l_requestor_id);
205 FETCH cFndUser INTO l_char;
206 IF cFndUser%NOTFOUND
207 THEN
208 debug('p_requestor_type is passed as NULL, FND_USER check failed');
209 x_return_status := FND_API.G_RET_STS_ERROR;
210 ELSE
211 -- The Requestor is a FND User, set the Requestor Type
212 l_requestor_type := 'FND_USER';
213 END IF;
214 CLOSE cFndUser;
215
216 ELSE
217 -- The Requestor is an Employee, set the Requestor Type
218 l_requestor_type := 'EMPLOYEE';
219 END IF;
220 CLOSE employee;
221 END IF;
222 debug('p_requestor_type is passed as NULL, l_requestor_type IS :'||
223 l_requestor_type);
224
225 ELSIF l_requestor_id IS NOT NULL and l_requestor_type = 'EMPLOYEE'
226 THEN
227 --verify if the requestor_id is indeed the
228 --person_id existing in the HR
229 OPEN employee(l_requestor_id);
230 FETCH employee INTO l_char;
231 IF employee%NOTFOUND THEN
232 debug('p_requestor_type is passed, Employee check is failed');
233 x_return_status := FND_API.G_RET_STS_ERROR;
234 END IF;
235 CLOSE employee;
236 ELSIF l_requestor_id IS NOT NULL and l_requestor_type = 'FND_USER'
237 THEN
238 OPEN cFndUser(l_requestor_id);
239 FETCH cFndUser INTO l_char;
240 IF cFndUser%NOTFOUND
241 THEN
242 debug('p_requestor_type is passed, FND USER check is failed');
243 x_return_status := FND_API.G_RET_STS_ERROR;
244 END IF;
245 CLOSE cFndUser;
246 ELSE
247 debug('p_requestor_type is NOT passed OR Request ID is passed as NULL');
248 x_return_status := FND_API.G_RET_STS_ERROR;
249 END IF;
250
251 /*---------------------------------------------+
252 | ========== Start of API Body ========== |
253 +---------------------------------------------*/
254 /* bug4556688: Added code for validating the request for appeal project
255 Validation :1. Check for p_credit_request_type for 'APPEAL','APPEAL_REJECTION'
256 and 'RESUBMISSION'.
257 2. Check whether p_parent_credit_request_id is not null
258 3. The parent credit request must be completed.
259 4. Party Id, Cust Account Id, Site Id, Currency, Credit Classification,
260 Review Type must be same as parent credit request.
261 5. In case of Appeal, Appeal Rejection parent credit request
262 must have 'AUTHORIZE_APPEAL' recommendation.
263 6. The value1 column will contain the Number of days Appeal is authorised.
264 The value2 column will contain the expiration date of Appeal.
265 7. In case of Appeal we need to validate the credit application date
266 must be less than or equal to Appeal expiration date.
267 If any of above is not met then reject the credit request.
268 */
269 BEGIN
270 SELECT 'X'
271 INTO l_char
272 FROM ar_lookups
273 WHERE lookup_type = 'OCM_CREDIT_REQUEST_TYPE'
274 AND lookup_code = l_credit_request_type;
275 EXCEPTION
276 WHEN OTHERS THEN
277 FND_MESSAGE.SET_NAME('AR','OCM_UNKNOWN_CREDIT_REQUEST');
278 FND_MSG_PUB.Add;
279 x_return_status := FND_API.G_RET_STS_ERROR;
280 return;
281 END;
282
283 IF l_char IS NOT NULL
284 THEN
285
286 IF ( l_credit_request_type = 'APPEAL' or
287 l_credit_request_type = 'APPEAL_REJECTION' or
288 l_credit_request_type = 'RESUBMISSION' )
289 THEN
290
291 IF p_parent_credit_request_id is not NULL
292 THEN
293 BEGIN
294 select status
295 into l_parent_cr_status
296 from ar_cmgt_credit_requests
297 where credit_request_id = p_parent_credit_request_id
298 and party_id = p_party_id
299 and nvl(cust_account_id,-99) = nvl(p_cust_account_id,-99)
300 and nvl(site_use_id,-99) = nvl(p_site_use_id,-99)
301 and nvl(trx_currency,limit_currency) = p_trx_currency
302 and credit_classification = p_credit_classification
303 and review_type = p_review_type;
304
305 IF pg_debug = 'Y'
306 THEN
307 debug ( 'Inside Appeal, Parent Credit request Status '||
308 l_parent_cr_status);
309 END IF;
310
311 IF l_parent_cr_status = 'PROCESSED'
312 THEN
313 IF l_credit_request_type in ( 'APPEAL', 'APPEAL_REJECTION')
314 THEN
315
316 BEGIN
317 select recommendation_value1,
318 recommendation_value2
319 into l_value1,
320 l_value2
321 from ar_cmgt_cf_recommends
322 where credit_request_id = p_parent_credit_request_id
323 and credit_recommendation = 'AUTHORIZE_APPEAL'
324 and status = 'I'
325 and rownum = 1;
326
327 IF pg_debug = 'Y'
328 THEN
329 debug ( 'Reco value 1 '||l_value1);
330 debug ( 'Reco value 2 '||l_value2);
331 END IF;
332
333 IF trunc(fnd_date.canonical_to_date(p_application_date))
334 > trunc(fnd_date.canonical_to_date(l_value2))
335 THEN
336 -- reject the application
337 IF pg_debug = 'Y'
338 THEN
339 debug ( 'Appeal request is Out of date range');
340 END IF;
341 FND_MESSAGE.SET_NAME('AR','OCM_APPEAL_EXPIRATION_REQUEST');
342 FND_MSG_PUB.Add;
343 x_return_status := FND_API.G_RET_STS_ERROR;
344 END IF;
345 EXCEPTION
346 WHEN NO_DATA_FOUND THEN
347 -- reject the application
348 FND_MESSAGE.SET_NAME('AR','OCM_NO_RECO_APPEAL_REQUEST');
349 FND_MSG_PUB.Add;
350 x_return_status := FND_API.G_RET_STS_ERROR;
351 WHEN OTHERS THEN
352 -- reject the application
353 FND_MESSAGE.SET_NAME('AR','OCM_NO_RECO_APPEAL_REQUEST');
354 FND_MSG_PUB.Add;
355 x_return_status := FND_API.G_RET_STS_ERROR;
356 END;
357 END IF; -- end APPEAL/APPEAL_REJECTION
358
359 ELSE
360 -- reject the application as parent request is not closed
361 FND_MESSAGE.SET_NAME('AR','OCM_NO_PARENT_APPEAL_REQUEST');
362 FND_MSG_PUB.Add;
363 x_return_status := FND_API.G_RET_STS_ERROR;
364 END IF;
365
366 EXCEPTION
367 WHEN NO_DATA_FOUND THEN
368 -- reject the application
369 FND_MESSAGE.SET_NAME('AR','OCM_NO_PARENT_APPEAL_REQUEST');
370 FND_MSG_PUB.Add;
371 x_return_status := FND_API.G_RET_STS_ERROR;
372 WHEN OTHERS THEN
373 -- reject the application
374 FND_MESSAGE.SET_NAME('AR','OCM_NO_PARENT_APPEAL_REQUEST');
375 FND_MSG_PUB.Add;
376 x_return_status := FND_API.G_RET_STS_ERROR;
377 END;
378
379 ELSE
380 -- reject the application parent credit request id is null
381 FND_MESSAGE.SET_NAME('AR','OCM_NO_PARENT_APPEAL_REQUEST');
382 FND_MSG_PUB.Add;
383 x_return_status := FND_API.G_RET_STS_ERROR;
384 END IF; -- end parent_credit_request_id is not null
385 END IF; -- end APPEAL/REJECTION/RESUBMISSION
386
387 ELSE
388 -- reject the application as request type is unknown
389 FND_MESSAGE.SET_NAME('AR','OCM_UNKNOWN_CREDIT_REQUEST');
390 FND_MSG_PUB.Add;
391 x_return_status := FND_API.G_RET_STS_ERROR;
392 END IF; -- end 'unknown' credit_request_type
393
394
395 -- Bug 4137766, In case request comes from OM
396 -- need to verify update is possible or not.
397 IF p_source_name = 'OM' and x_return_status = FND_API.G_RET_STS_SUCCESS
398 THEN
399 -- Get the credit request Id
400 p_credit_request_rec.credit_request_status := 'SUBMIT';
401
402 BEGIN
403 SELECT credit_request_id
404 INTO l_credit_request_id
405 FROM ar_cmgt_credit_requests
406 WHERE source_name = p_source_name
407 AND source_column1 = p_source_column1
408 AND party_id = p_party_id
409 AND cust_account_id = nvl(p_cust_account_id, -99)
410 AND site_use_id = nvl(p_site_use_id, -99)
411 AND status <> 'PROCESSED';
412
413 l_isupdateable := 'Y';
414 EXCEPTION
415 WHEN NO_DATA_FOUND THEN
416 l_credit_request_id := null;
417 l_isupdateable := 'N';
418 WHEN TOO_MANY_ROWS THEN
419 -- this is for backward compatibitilty
423 INTO l_credit_request_id
420 -- get the latest credit request id
421 BEGIN
422 SELECT max(credit_request_id)
424 FROM ar_cmgt_credit_requests
425 WHERE source_name = p_source_name
426 AND source_column1 = p_source_column1
427 AND party_id = p_party_id
428 AND cust_account_id = nvl(p_cust_account_id, -99)
429 AND site_use_id = nvl(p_site_use_id, -99)
430 AND status <> 'PROCESSED';
431
432 l_isupdateable := 'Y'; -- 7185336
433 EXCEPTION
434 WHEN OTHERS THEN
435 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
436 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
437 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT',
438 'GETTING_MAX_CREDIT_REQUEST_FOR_UPDATE : '||SQLERRM);
439 FND_MSG_PUB.Add;
440 return;
441 END;
442
443 WHEN OTHERS THEN
444 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
445 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
446 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT',
447 'GETTING_CREDIT_REQUEST_FOR_UPDATE : '||SQLERRM);
448 FND_MSG_PUB.Add;
449 return;
450 END;
451
452 BEGIN
453 /* 7185336 -- Originally, this only found case folders
454 where status NOT IN SUBMITTED/CLOSED. However, we
455 need to differentiate between situations where
456 the case folder exists vs ones where it does not to
457 determine if we are attempting to update one that is literally
458 in progress at the time of the call */
459 SELECT case_folder_id, status
460 INTO l_case_folder_id, l_case_folder_status
461 FROM ar_cmgt_case_folders
462 WHERE credit_request_id = l_credit_request_id
463 AND type = 'CASE';
464
465 -- Now upadate the records
466 UPDATE ar_cmgt_case_folders
467 SET STATUS = 'REFRESH',
468 last_updated_by = fnd_global.user_id,
469 last_update_date = sysdate,
470 last_updated = sysdate
471 WHERE case_folder_id = l_case_folder_id
472 AND status NOT IN ('SUBMITTED','CLOSED');
473
474 /* 7185336 - if the case folder is CLOSED or SUBMITTED,
475 then we need a new one. Otherwise, we can wait for
476 the existing one to become available */
477 IF l_case_folder_status IN ('SUBMITTED','CLOSED')
478 THEN
479 -- need a new one
480 l_isupdateable := 'N';
481 ELSE
482 -- we can use the existing request/folder
483 l_isupdateable := 'Y';
484 p_credit_request_rec.credit_request_status := 'IN_PROCESS';
485 END IF;
486 EXCEPTION
487 WHEN NO_DATA_FOUND THEN
488 /* 7185336 - This means there literally was no case folder
489 for this active request. Likely a timing issue */
490 IF l_isupdateable = 'Y'
491 THEN
492 select trx_amount,limit_amount
493 into l_trx_amount,l_requested_amount
494 from ar_cmgt_credit_requests
495 where credit_request_id = l_credit_request_id;
496
497 IF l_trx_amount = p_trx_amount AND
498 l_requested_amount = p_requested_amount
499 THEN
500 debug('sent for process already.Will return back.');
501 p_credit_request_id := l_credit_request_id;
502 return;
503 END IF;
504 END IF;
505 WHEN OTHERS THEN
506 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
507 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
508 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT',
509 'GETTING_CASE_FOLDER_FOR_UPDATE : '||SQLERRM);
510 FND_MSG_PUB.Add;
511 return;
512 END;
513
514 IF l_isupdateable = 'Y'
515 THEN
516 -- Now call Update API to update credit request.
517 -- FOR OM we will be updating only Amount.
518 p_credit_request_rec.credit_request_id := l_credit_request_id;
519 p_credit_request_id := l_credit_request_id;
520 p_credit_request_rec.case_folder_id := l_case_folder_id;
521 p_credit_request_rec.trx_amount := p_trx_amount;
522 p_credit_request_rec.requested_amount := p_requested_amount;
523 p_credit_request_rec.requestor_id := l_requestor_id;
524
525
526 OCM_CREDIT_REQUEST_UPDATE_PUB.UPDATE_CREDIT_REQUEST (
527 p_api_version => p_api_version,
528 p_init_msg_list => p_init_msg_list,
529 p_commit => p_commit,
533 x_msg_data => x_msg_data,
530 p_validation_level => p_validation_level,
531 x_return_status => x_return_status,
532 x_msg_count => x_msg_count,
534 p_credit_request_rec => p_credit_request_rec );
535 return;
536 END IF; -- end of l_isupdateable
537
538 END IF; -- p_source_name = 'OM'
539
540 /*-----------------------------------------+
541 | DEFAULTING |
542 +-----------------------------------------*/
543
544 OPEN system_parameters;
545 FETCH system_parameters INTO l_sys_params_rec;
546 CLOSE system_parameters;
547
548 IF l_application_date IS NULL THEN
549 l_application_date := trunc(sysdate);
550 END IF;
551
552 IF l_application_number IS NULL
553 AND nvl(l_sys_params_rec.auto_application_num_flag, 'N') = 'Y'
554 THEN
555 SELECT AR_CMGT_APPLICATION_NUM_S.NEXTVAL, AR_CMGT_CREDIT_REQUESTS_S.NEXTVAL
556 INTO l_application_number, l_credit_request_id
557 FROM DUAL;
558
559 END IF;
560
561 IF l_credit_request_id IS NULL THEN
562 SELECT AR_CMGT_CREDIT_REQUESTS_S.NEXTVAL
563 INTO l_credit_request_id
564 FROM dual;
565 END IF;
566
567
568 /*-----------------------------------------+
569 | VALIDATION |
570 +-----------------------------------------*/
571 IF p_requestor_id IS NOT NULL and l_requestor_type = 'EMPLOYEE'
572 THEN
573 --verify if the requestor_id is indeed the
574 --person_id existing in the HR
575 OPEN employee(p_requestor_id);
576 FETCH employee INTO l_char;
577 IF employee%NOTFOUND THEN
578 IF pg_debug = 'Y'
579 THEN
580 debug ( 'Employee Requestor Not Found');
581 END IF;
582 x_return_status := FND_API.G_RET_STS_ERROR;
583 END IF;
584 CLOSE employee;
585 ELSIF p_requestor_id IS NOT NULL and l_requestor_type = 'FND_USER'
586 THEN
587 OPEN cFndUser(p_requestor_id);
588 FETCH cFndUser INTO l_char;
589 IF cFndUser%NOTFOUND THEN
590 IF pg_debug = 'Y'
591 THEN
592 debug ( 'FND User Not Found');
593 END IF;
594 x_return_status := FND_API.G_RET_STS_ERROR;
595 END IF;
596 CLOSE cFndUser;
597 ELSE
598 FND_MESSAGE.SET_NAME('AR','OCM_INVALID_REQUESTOR');
599 FND_MSG_PUB.Add;
600 x_return_status := FND_API.G_RET_STS_ERROR;
601 END IF;
602
603 IF p_party_id IS NULL
604 THEN
605 --raise error message
606 FND_MESSAGE.SET_NAME('AR','AR_CMGT_NULL_PARTY_ID');
607 FND_MSG_PUB.Add;
608 x_return_status := FND_API.G_RET_STS_ERROR;
609 END IF;
610
611 IF p_review_type IS NULL
612 THEN
613 --raise error message
614 debug('AR_CMGT_NULL_REVIEW_TYPE');
615 FND_MESSAGE.SET_NAME('AR','AR_CMGT_NULL_REVIEW_TYPE');
616 FND_MSG_PUB.Add;
617 x_return_status := FND_API.G_RET_STS_ERROR;
618 END IF;
619
620 IF p_requested_currency IS NULL
621 THEN
622 --raise error message
623 debug('AR_CMGT_NULL_REQ_CURR');
624 FND_MESSAGE.SET_NAME('AR','AR_CMGT_NULL_REQ_CURR');
625 FND_MSG_PUB.Add;
626 x_return_status := FND_API.G_RET_STS_ERROR;
627 END IF;
628
629 IF p_credit_type IS NULL
630 THEN
631 --raise error
632 debug('AR_CMGT_NULL_CREDIT_TYPE');
633 FND_MESSAGE.SET_NAME('AR','AR_CMGT_NULL_CREDIT_TYPE');
634 FND_MSG_PUB.Add;
635 x_return_status := FND_API.G_RET_STS_ERROR;
636 ELSE
637 --verify the specified value is from lookups
638 BEGIN
639 SELECT lookup_code INTO l_credit_type
640 FROM ar_lookups
641 WHERE lookup_type = 'AR_CMGT_CREDIT_TYPE'
642 AND lookup_code = p_credit_type;
643
644 EXCEPTION
645 WHEN OTHERS THEN
646 debug('AR_CMGT_INVALID_CREDIT_TYPE');
647 FND_MESSAGE.SET_NAME('AR','AR_CMGT_INVALID_CREDIT_TYPE');
648 FND_MSG_PUB.Add;
649 x_return_status := FND_API.G_RET_STS_ERROR;
650 END;
651 END IF;
652
653 IF l_credit_request_status IS NULL
654 THEN
655 l_credit_request_status := 'SUBMIT';
656 ELSE
657 IF l_credit_request_status NOT IN
658 ('SUBMIT','SAVE')
659 THEN
660 --raise error
661 debug('AR_CMGT_INVALID_CR_STATUS');
662 FND_MESSAGE.SET_NAME('AR','AR_CMGT_INVALID_CR_STATUS');
663 FND_MSG_PUB.Add;
664 x_return_status := FND_API.G_RET_STS_ERROR;
665 END IF;
666
667 END IF;
668
669 -- validate score_model_id
670 IF p_score_model_id IS NOT NULL
671 THEN
672 BEGIN
673 SELECT 'X'
674 INTO l_char
675 FROM ar_cmgt_scores
676 WHERE score_model_id = p_score_model_id
677 AND submit_flag = 'Y'
678 AND sysdate between start_date and
679 nvl(end_date,sysdate);
680 EXCEPTION
681 WHEN NO_DATA_FOUND THEN
682 FND_MESSAGE.SET_NAME('AR','AR_CMGT_SCORE_NAME_INVALID');
683 FND_MSG_PUB.Add;
684 x_return_status := FND_API.G_RET_STS_ERROR;
685 WHEN OTHERS THEN
686 x_return_status := FND_API.G_RET_STS_ERROR;
687 END;
688 END IF;
689
690 /*-----------------------------------------+
691 | CREATION |
692 +-----------------------------------------*/
693 /* bug4414414 : Added columns parent_credit_request_id and credit_request_type
694 */
695 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
696 INSERT INTO AR_CMGT_CREDIT_REQUESTS
697 (credit_request_id,
698 last_update_date,
699 last_updated_by,
700 creation_date,
701 created_by,
702 last_update_login,
703 application_number,
704 application_date,
705 requestor_type,
706 requestor_id,
707 review_type,
708 review_cycle,
709 credit_classification,
710 check_list_id,
711 credit_analyst_id,
712 limit_amount,
713 limit_currency,
714 trx_amount,
715 trx_currency,
716 credit_check_rule_id,
717 term_length,
718 credit_type,
719 party_id,
720 cust_account_id,
721 cust_acct_site_id,
722 site_use_id,
723 contact_party_id,
724 case_folder_number,
725 score_model_id,
726 attachment_flag,
727 status,
728 source_name,
729 source_user_id,
730 source_resp_id,
731 source_resp_appln_id,
732 source_security_group_id,
733 source_org_id,
734 source_column1,
735 source_column2,
736 source_column3,
737 notes,
738 request_id,
739 parent_credit_request_id,
740 credit_request_type,
741 RECOMMENDATION_NAME
742 )
743 VALUES
744 (l_credit_request_id,
745 sysdate,
746 fnd_global.user_id,
747 sysdate,
748 fnd_global.user_id,
749 fnd_global.login_id,
750 l_application_number,
751 l_application_date,
752 l_requestor_type,
753 l_requestor_id,
754 p_review_type,
755 p_review_cycle,
756 p_credit_classification,
757 null,
758 null,
759 p_requested_amount,
760 p_requested_currency,
761 p_trx_amount,
762 p_trx_currency,
763 p_credit_check_rule_id,
764 p_term_length,
765 p_credit_type,
766 p_party_id,
767 nvl(p_cust_account_id,-99),
768 nvl(p_cust_acct_site_id,-99),
769 nvl(p_site_use_id,-99),
770 p_contact_party_id,
771 p_case_folder_number,
772 p_score_model_id,
773 null,
774 l_credit_request_status,
775 p_source_name,
776 p_source_user_id,
777 p_source_resp_id,
778 p_source_appln_id,
779 p_source_security_group_id,
780 p_source_org_id,
781 p_source_column1,
782 p_source_column2,
783 p_source_column3,
784 p_notes,
785 fnd_global.conc_request_id,
786 p_parent_credit_request_id,
787 l_credit_request_type,
788 p_reco
789 );
790 -- commit;
791 /*-----------------------------------------+
792 | WORKFLOW CALL |
793 +-----------------------------------------*/
794
795 IF l_credit_request_status = 'SUBMIT'
796 THEN
797 IF pg_debug = 'Y'
798 THEN
799 debug ( 'Workflow Call');
800 END IF;
801 AR_CMGT_WF_ENGINE.START_WORKFLOW
802 (l_credit_request_id ,l_credit_request_status);
803 END IF;
804
805 p_credit_request_id := l_credit_request_id;
806
807 ELSE
808 --error was raised during the validation
809 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
810 p_count => x_msg_count,
811 p_data => x_msg_data
812 );
813 return;
814
815 END IF;
816 IF pg_debug = 'Y'
817 THEN
818 debug ( 'AR_CMGT_CREDIT_REQUEST_API(-)');
819 END IF;
820 EXCEPTION
821 WHEN FND_API.G_EXC_ERROR THEN
822
823 ROLLBACK TO CREATE_CREDIT_REQ_PVT;
824 x_return_status := FND_API.G_RET_STS_ERROR ;
825
826 --Display_Parameters;
827
828 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
829 p_count => x_msg_count,
830 p_data => x_msg_data
831 );
832 debug('FND_API.G_EXC_ERROR, x_msg_data :'||x_msg_data|| ' SQLERRM :'||SQLERRM);
833 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
834
835 ROLLBACK TO CREATE_CREDIT_REQ_PVT;
836 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
837
838 -- Display_Parameters;
839
840 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
841 p_count => x_msg_count,
842 p_data => x_msg_data
843 );
844 debug('FND_API.G_EXC_UNEXPECTED_ERROR, x_msg_data :'||x_msg_data|| ' SQLERRM :'||SQLERRM);
845 WHEN OTHERS THEN
846
847 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
848 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
849 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','CREATE_CREDIT_REQUEST : '||SQLERRM);
850 FND_MSG_PUB.Add;
851
852
853 ROLLBACK TO Create_credit_req_PVT;
854
855
856 -- Display_Parameters;
857 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
858 p_count => x_msg_count,
859 p_data => x_msg_data
860 );
861 debug('FND_API.G_EXC_UNEXPECTED_ERROR, x_msg_data :'||x_msg_data|| ' SQLERRM :'||SQLERRM);
862 END create_credit_request;
863
864 FUNCTION is_Credit_Management_Installed
865 RETURN BOOLEAN
866 IS
867 CURSOR C1 IS
868 SELECT 'x'
869 FROM ar_cmgt_setup_options;
870 l_return BOOLEAN;
871 l_char VARCHAR2(1);
872
873 BEGIN
874
875 OPEN C1;
876
877 FETCH C1 into l_char;
878
879 IF C1%NOTFOUND THEN
880 l_return := FALSE;
881 ELSE
882 -- since row exists in setup options table, check in checklist table
883 BEGIN
884 SELECT 'x'
885 INTO l_char
886 FROM ar_cmgt_check_lists
887 WHERE submit_flag = 'Y';
888
889 l_return := TRUE;
890
891 EXCEPTION
892 WHEN NO_DATA_FOUND then
893 l_return := FALSE;
894 WHEN TOO_MANY_ROWS then
895 l_return := TRUE;
896 END;
897
898 END IF;
899
900 CLOSE C1;
901
902 return(l_return);
903
904 END;
905
906 FUNCTION get_application_number (
907 p_credit_request_id IN NUMBER )
908 RETURN VARCHAR2 IS
909
910 CURSOR cApplicationNumber IS
911 SELECT application_number
912 FROM ar_cmgt_credit_requests
913 WHERE credit_request_id = p_credit_request_id;
914
915 l_application_number VARCHAR2(30);
916 BEGIN
917 OPEN cApplicationNumber;
918
919 FETCH cApplicationNumber INTO l_application_number;
920
921 IF cApplicationNumber%NOTFOUND
922 THEN
923 l_application_number := NULL;
924 END IF;
925
926 CLOSE cApplicationNumber;
927
928 return(l_application_number);
929 END get_application_number;
930
931 END AR_CMGT_CREDIT_REQUEST_API;
[Home] [Help]