1 PACKAGE BODY AR_CMGT_CREDIT_REQUEST_API AS
2 /*$Header: ARCMCRAB.pls 120.34 2011/02/23 00:35:29 rravikir 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 l_op_enabled Varchar2(1) := 'Y'; -- Added for bug 11800630
103
104 BEGIN
105
106 -- Added for bug 11800630 - Start
107 -- OCM Hook to check whether OCM is installed for an operating unit
108 IF p_source_org_id is not NULL THEN
109 l_op_enabled := AR_CMGT_CREDIT_HOOK_API.credit_check_operating_unit(p_source_org_id => p_source_org_id,
110 p_source_object_id => p_source_column1);
111 END IF;
112
113 IF (l_op_enabled <> 'Y') THEN
114 RETURN; -- Abort the credit request creation.
115 END IF;
116 -- Added for bug 11800630 - End
117
118
119 /*------------------------------------+
120 | Standard start of API savepoint |
121 +------------------------------------*/
122
123 SAVEPOINT CREATE_CREDIT_REQ_PVT;
124
125
126 /*--------------------------------------------------------------+
127 | Initialize message list if p_init_msg_list is set to TRUE |
128 +--------------------------------------------------------------*/
129
130 IF FND_API.to_Boolean( p_init_msg_list )
131 THEN
132 FND_MSG_PUB.initialize;
133 END IF;
134
135 IF pg_debug = 'Y'
136 THEN
137 debug ( 'AR_CMGT_CREDIT_REQUEST_API(+)');
138 debug ( 'Application Number ' || p_application_number );
139 debug ( 'Requestor Type ' || p_requestor_type );
140 debug ( 'Requestor Id ' || p_requestor_id );
141 debug ( 'Review Type ' || p_review_type );
142 debug ( 'Classification ' || p_credit_classification );
143 debug ( 'Party Id ' || p_party_id );
144 debug ( 'Account Id' || p_cust_account_id );
145 debug ( 'Site Use Id' || p_site_use_id );
146 debug ( 'Parent Credit request Id ' || p_parent_credit_request_id );
147 debug ( 'Credit request type' || p_credit_request_type );
148 debug ( 'Recommendation ' || p_reco );
149 debug ( 'Status ' || p_credit_request_status );
150 debug ( 'Source Name' || p_source_name );
151 debug ( 'Source Id' || p_source_column1 );
152 debug ( 'Req Currency ' || p_requested_currency );
153 debug ( 'Trx Currency ' || p_trx_currency );
154 END IF;
155
156 /*-----------------------------------------+
157 | Initialize return status to SUCCESS |
158 +-----------------------------------------*/
159
160 x_return_status := FND_API.G_RET_STS_SUCCESS;
161 l_application_date := p_application_date;
162 l_application_number := p_application_number;
163 l_credit_request_status := p_credit_request_status;
164 l_requestor_type := p_requestor_type;
165 l_requestor_id := p_requestor_id;
166
167 IF p_credit_request_type IS NULL
168 THEN
169 l_credit_request_type := 'CREDIT_APP';
170 ELSE
171 l_credit_request_type := p_credit_request_type;
172 END IF;
173
174 IF l_requestor_type IS NULL -- For Backward compatibility
175 THEN
176 l_requestor_type := 'FND_USER/EMPLOYEE';
177 ELSIF l_requestor_type IS NOT NULL AND
178 l_requestor_type NOT IN ('EMPLOYEE', 'FND_USER')
179 THEN
180 IF pg_debug = 'Y'
181 THEN
182 debug ( 'Invalid Requestor Type ' || l_requestor_type );
183 END IF;
184 FND_MESSAGE.SET_NAME('AR','OCM_INVALID_REQUESTOR_TYPE');
185 FND_MSG_PUB.Add;
186 x_msg_data := 'OCM_INVALID_REQUESTOR_TYPE';
187 x_return_status := FND_API.G_RET_STS_ERROR;
188 return;
189 END IF;
190
191 debug ( 'l_requestor_type ' || l_requestor_type );
192
193 IF l_requestor_type = 'FND_USER/EMPLOYEE'
194 THEN
195 /* Not Sure of the Source since p_request_type is passed as NULL
196 In that case first check the requestor ID, if its passed as -1
197 or NULL then take the FND_GLOBAL.USER_ID and set the request
198 type as FND_USER.
199
200 If the Requestor ID is not NULL or <> -1 then check if the
201 Requestor is an Employee
202 Else check if the Requestor is a FND User
203 */
204
205 IF l_requestor_id IS NULL OR l_requestor_id = -1
206 THEN
207 debug('p_requestor_type is passed as NULL, p_requestor_id is NULL or -1, use FND USER');
208 l_requestor_id := FND_GLOBAL.USER_ID;
209 l_requestor_type := 'FND_USER';
210 debug('p_requestor_type is passed as NULL, l_requestor_id:'||
211 l_requestor_id||' l_requestor_type :'||l_requestor_type);
212 ELSE
213 debug('p_requestor_type is passed as NULL, check both EMPLOYEE and FND_USER');
214 OPEN employee(l_requestor_id);
215 FETCH employee INTO l_char;
216 IF employee%NOTFOUND
217 THEN
218 debug('p_requestor_type is passed as NULL, EMPLOYEE check failed');
219 OPEN cFndUser(l_requestor_id);
220 FETCH cFndUser INTO l_char;
221 IF cFndUser%NOTFOUND
222 THEN
223 debug('p_requestor_type is passed as NULL, FND_USER check failed');
224 x_return_status := FND_API.G_RET_STS_ERROR;
225 ELSE
226 -- The Requestor is a FND User, set the Requestor Type
227 l_requestor_type := 'FND_USER';
228 END IF;
229 CLOSE cFndUser;
230
231 ELSE
232 -- The Requestor is an Employee, set the Requestor Type
233 l_requestor_type := 'EMPLOYEE';
234 END IF;
235 CLOSE employee;
236 END IF;
237 debug('p_requestor_type is passed as NULL, l_requestor_type IS :'||
238 l_requestor_type);
239
240 ELSIF l_requestor_id IS NOT NULL and l_requestor_type = 'EMPLOYEE'
241 THEN
242 --verify if the requestor_id is indeed the
243 --person_id existing in the HR
244 OPEN employee(l_requestor_id);
245 FETCH employee INTO l_char;
246 IF employee%NOTFOUND THEN
247 debug('p_requestor_type is passed, Employee check is failed');
248 x_return_status := FND_API.G_RET_STS_ERROR;
249 END IF;
250 CLOSE employee;
251 ELSIF l_requestor_id IS NOT NULL and l_requestor_type = 'FND_USER'
252 THEN
253 OPEN cFndUser(l_requestor_id);
254 FETCH cFndUser INTO l_char;
255 IF cFndUser%NOTFOUND
256 THEN
257 debug('p_requestor_type is passed, FND USER check is failed');
258 x_return_status := FND_API.G_RET_STS_ERROR;
259 END IF;
260 CLOSE cFndUser;
261 ELSE
262 debug('p_requestor_type is NOT passed OR Request ID is passed as NULL');
263 x_return_status := FND_API.G_RET_STS_ERROR;
264 END IF;
265
266 /*---------------------------------------------+
267 | ========== Start of API Body ========== |
268 +---------------------------------------------*/
269 /* bug4556688: Added code for validating the request for appeal project
270 Validation :1. Check for p_credit_request_type for 'APPEAL','APPEAL_REJECTION'
271 and 'RESUBMISSION'.
272 2. Check whether p_parent_credit_request_id is not null
273 3. The parent credit request must be completed.
274 4. Party Id, Cust Account Id, Site Id, Currency, Credit Classification,
275 Review Type must be same as parent credit request.
276 5. In case of Appeal, Appeal Rejection parent credit request
277 must have 'AUTHORIZE_APPEAL' recommendation.
278 6. The value1 column will contain the Number of days Appeal is authorised.
279 The value2 column will contain the expiration date of Appeal.
280 7. In case of Appeal we need to validate the credit application date
281 must be less than or equal to Appeal expiration date.
282 If any of above is not met then reject the credit request.
283 */
284 BEGIN
285 SELECT 'X'
286 INTO l_char
287 FROM ar_lookups
288 WHERE lookup_type = 'OCM_CREDIT_REQUEST_TYPE'
289 AND lookup_code = l_credit_request_type;
290 EXCEPTION
291 WHEN OTHERS THEN
292 FND_MESSAGE.SET_NAME('AR','OCM_UNKNOWN_CREDIT_REQUEST');
293 FND_MSG_PUB.Add;
294 x_return_status := FND_API.G_RET_STS_ERROR;
295 return;
296 END;
297
298 IF l_char IS NOT NULL
299 THEN
300
301 IF ( l_credit_request_type = 'APPEAL' or
302 l_credit_request_type = 'APPEAL_REJECTION' or
303 l_credit_request_type = 'RESUBMISSION' )
304 THEN
305
306 IF p_parent_credit_request_id is not NULL
307 THEN
308 BEGIN
309 select status
310 into l_parent_cr_status
311 from ar_cmgt_credit_requests
312 where credit_request_id = p_parent_credit_request_id
313 and party_id = p_party_id
314 and nvl(cust_account_id,-99) = nvl(p_cust_account_id,-99)
315 and nvl(site_use_id,-99) = nvl(p_site_use_id,-99)
316 and nvl(trx_currency,limit_currency) = p_trx_currency
317 and credit_classification = p_credit_classification
318 and review_type = p_review_type;
319
320 IF pg_debug = 'Y'
321 THEN
322 debug ( 'Inside Appeal, Parent Credit request Status '||
323 l_parent_cr_status);
324 END IF;
325
326 IF l_parent_cr_status = 'PROCESSED'
327 THEN
328 IF l_credit_request_type in ( 'APPEAL', 'APPEAL_REJECTION')
329 THEN
330
331 BEGIN
332 select recommendation_value1,
333 recommendation_value2
334 into l_value1,
335 l_value2
336 from ar_cmgt_cf_recommends
337 where credit_request_id = p_parent_credit_request_id
338 and credit_recommendation = 'AUTHORIZE_APPEAL'
339 and status = 'I'
340 and rownum = 1;
341
342 IF pg_debug = 'Y'
343 THEN
344 debug ( 'Reco value 1 '||l_value1);
345 debug ( 'Reco value 2 '||l_value2);
346 END IF;
347
348 IF trunc(fnd_date.canonical_to_date(p_application_date))
349 > trunc(fnd_date.canonical_to_date(l_value2))
350 THEN
351 -- reject the application
352 IF pg_debug = 'Y'
353 THEN
354 debug ( 'Appeal request is Out of date range');
355 END IF;
356 FND_MESSAGE.SET_NAME('AR','OCM_APPEAL_EXPIRATION_REQUEST');
357 FND_MSG_PUB.Add;
358 x_return_status := FND_API.G_RET_STS_ERROR;
359 END IF;
360 EXCEPTION
361 WHEN NO_DATA_FOUND THEN
362 -- reject the application
363 FND_MESSAGE.SET_NAME('AR','OCM_NO_RECO_APPEAL_REQUEST');
364 FND_MSG_PUB.Add;
365 x_return_status := FND_API.G_RET_STS_ERROR;
366 WHEN OTHERS THEN
367 -- reject the application
368 FND_MESSAGE.SET_NAME('AR','OCM_NO_RECO_APPEAL_REQUEST');
369 FND_MSG_PUB.Add;
370 x_return_status := FND_API.G_RET_STS_ERROR;
371 END;
372 END IF; -- end APPEAL/APPEAL_REJECTION
373
374 ELSE
375 -- reject the application as parent request is not closed
376 FND_MESSAGE.SET_NAME('AR','OCM_NO_PARENT_APPEAL_REQUEST');
377 FND_MSG_PUB.Add;
378 x_return_status := FND_API.G_RET_STS_ERROR;
379 END IF;
380
381 EXCEPTION
382 WHEN NO_DATA_FOUND THEN
383 -- reject the application
384 FND_MESSAGE.SET_NAME('AR','OCM_NO_PARENT_APPEAL_REQUEST');
385 FND_MSG_PUB.Add;
386 x_return_status := FND_API.G_RET_STS_ERROR;
387 WHEN OTHERS THEN
388 -- reject the application
389 FND_MESSAGE.SET_NAME('AR','OCM_NO_PARENT_APPEAL_REQUEST');
390 FND_MSG_PUB.Add;
391 x_return_status := FND_API.G_RET_STS_ERROR;
392 END;
393
394 ELSE
395 -- reject the application parent credit request id is null
396 FND_MESSAGE.SET_NAME('AR','OCM_NO_PARENT_APPEAL_REQUEST');
397 FND_MSG_PUB.Add;
398 x_return_status := FND_API.G_RET_STS_ERROR;
399 END IF; -- end parent_credit_request_id is not null
400 END IF; -- end APPEAL/REJECTION/RESUBMISSION
401
402 ELSE
403 -- reject the application as request type is unknown
404 FND_MESSAGE.SET_NAME('AR','OCM_UNKNOWN_CREDIT_REQUEST');
405 FND_MSG_PUB.Add;
406 x_return_status := FND_API.G_RET_STS_ERROR;
407 END IF; -- end 'unknown' credit_request_type
408
409
410 -- Bug 4137766, In case request comes from OM
411 -- need to verify update is possible or not.
412 IF p_source_name = 'OM' and x_return_status = FND_API.G_RET_STS_SUCCESS
413 THEN
414 -- Get the credit request Id
415 p_credit_request_rec.credit_request_status := 'SUBMIT';
416
417 BEGIN
418 SELECT credit_request_id
419 INTO l_credit_request_id
420 FROM ar_cmgt_credit_requests
421 WHERE source_name = p_source_name
422 AND source_column1 = p_source_column1
423 AND party_id = p_party_id
424 AND cust_account_id = nvl(p_cust_account_id, -99)
425 AND site_use_id = nvl(p_site_use_id, -99)
426 AND status <> 'PROCESSED';
427
428 l_isupdateable := 'Y';
429 EXCEPTION
430 WHEN NO_DATA_FOUND THEN
431 l_credit_request_id := null;
432 l_isupdateable := 'N';
433 WHEN TOO_MANY_ROWS THEN
434 -- this is for backward compatibitilty
435 -- get the latest credit request id
436 BEGIN
437 SELECT max(credit_request_id)
438 INTO l_credit_request_id
439 FROM ar_cmgt_credit_requests
440 WHERE source_name = p_source_name
441 AND source_column1 = p_source_column1
442 AND party_id = p_party_id
443 AND cust_account_id = nvl(p_cust_account_id, -99)
444 AND site_use_id = nvl(p_site_use_id, -99)
445 AND status <> 'PROCESSED';
446
447 l_isupdateable := 'Y'; -- 7185336
448 EXCEPTION
449 WHEN OTHERS THEN
450 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
451 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
452 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT',
453 'GETTING_MAX_CREDIT_REQUEST_FOR_UPDATE : '||SQLERRM);
454 FND_MSG_PUB.Add;
455 return;
456 END;
457
458 WHEN OTHERS THEN
459 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
460 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
461 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT',
462 'GETTING_CREDIT_REQUEST_FOR_UPDATE : '||SQLERRM);
463 FND_MSG_PUB.Add;
464 return;
465 END;
466
467 BEGIN
468 /* 7185336 -- Originally, this only found case folders
469 where status NOT IN SUBMITTED/CLOSED. However, we
470 need to differentiate between situations where
471 the case folder exists vs ones where it does not to
472 determine if we are attempting to update one that is literally
473 in progress at the time of the call */
474 SELECT case_folder_id, status
475 INTO l_case_folder_id, l_case_folder_status
476 FROM ar_cmgt_case_folders
477 WHERE credit_request_id = l_credit_request_id
478 AND type = 'CASE';
479
480 -- Now upadate the records
481 UPDATE ar_cmgt_case_folders
482 SET STATUS = 'REFRESH',
483 last_updated_by = fnd_global.user_id,
484 last_update_date = sysdate,
485 last_updated = sysdate
486 WHERE case_folder_id = l_case_folder_id
487 AND status NOT IN ('SUBMITTED','CLOSED');
488
489 /* 7185336 - if the case folder is CLOSED or SUBMITTED,
490 then we need a new one. Otherwise, we can wait for
491 the existing one to become available */
492 IF l_case_folder_status IN ('SUBMITTED','CLOSED')
493 THEN
494 -- need a new one
495 l_isupdateable := 'N';
496 ELSE
497 -- we can use the existing request/folder
498 l_isupdateable := 'Y';
499 p_credit_request_rec.credit_request_status := 'IN_PROCESS';
500 END IF;
501 EXCEPTION
502 WHEN NO_DATA_FOUND THEN
503 /* 7185336 - This means there literally was no case folder
504 for this active request. Likely a timing issue */
505 IF l_isupdateable = 'Y'
506 THEN
507 select trx_amount,limit_amount
508 into l_trx_amount,l_requested_amount
509 from ar_cmgt_credit_requests
510 where credit_request_id = l_credit_request_id;
511
512 IF l_trx_amount = p_trx_amount AND
513 l_requested_amount = p_requested_amount
514 THEN
515 debug('sent for process already.Will return back.');
516 p_credit_request_id := l_credit_request_id;
517 return;
518 END IF;
519 END IF;
520 WHEN OTHERS THEN
521 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
522 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
523 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT',
524 'GETTING_CASE_FOLDER_FOR_UPDATE : '||SQLERRM);
525 FND_MSG_PUB.Add;
526 return;
527 END;
528
529 IF l_isupdateable = 'Y'
530 THEN
531 -- Now call Update API to update credit request.
532 -- FOR OM we will be updating only Amount.
533 p_credit_request_rec.credit_request_id := l_credit_request_id;
534 p_credit_request_id := l_credit_request_id;
535 p_credit_request_rec.case_folder_id := l_case_folder_id;
536 p_credit_request_rec.trx_amount := p_trx_amount;
537 p_credit_request_rec.requested_amount := p_requested_amount;
538 p_credit_request_rec.requestor_id := l_requestor_id;
539
540
541 OCM_CREDIT_REQUEST_UPDATE_PUB.UPDATE_CREDIT_REQUEST (
542 p_api_version => p_api_version,
543 p_init_msg_list => p_init_msg_list,
544 p_commit => p_commit,
545 p_validation_level => p_validation_level,
546 x_return_status => x_return_status,
547 x_msg_count => x_msg_count,
548 x_msg_data => x_msg_data,
549 p_credit_request_rec => p_credit_request_rec );
550 return;
551 END IF; -- end of l_isupdateable
552
553 END IF; -- p_source_name = 'OM'
554
555 /*-----------------------------------------+
556 | DEFAULTING |
557 +-----------------------------------------*/
558
559 OPEN system_parameters;
560 FETCH system_parameters INTO l_sys_params_rec;
561 CLOSE system_parameters;
562
563 IF l_application_date IS NULL THEN
564 l_application_date := trunc(sysdate);
565 END IF;
566
567 IF l_application_number IS NULL
568 AND nvl(l_sys_params_rec.auto_application_num_flag, 'N') = 'Y'
569 THEN
570 SELECT AR_CMGT_APPLICATION_NUM_S.NEXTVAL, AR_CMGT_CREDIT_REQUESTS_S.NEXTVAL
571 INTO l_application_number, l_credit_request_id
572 FROM DUAL;
573
574 END IF;
575
576 IF l_credit_request_id IS NULL THEN
577 SELECT AR_CMGT_CREDIT_REQUESTS_S.NEXTVAL
578 INTO l_credit_request_id
579 FROM dual;
580 END IF;
581
582 /*-----------------------------------------+
583 | VALIDATION |
584 +-----------------------------------------*/
585 IF l_requestor_id IS NOT NULL and l_requestor_type = 'EMPLOYEE'
586 THEN
587 --verify if the requestor_id is indeed the
588 --person_id existing in the HR
589 OPEN employee(l_requestor_id);
590 FETCH employee INTO l_char;
591 IF employee%NOTFOUND THEN
592 x_return_status := FND_API.G_RET_STS_ERROR;
593 END IF;
594 CLOSE employee;
595 ELSIF l_requestor_id IS NOT NULL and l_requestor_type =
596 'FND_USER'
597 THEN
598 OPEN cFndUser(l_requestor_id);
599 FETCH cFndUser INTO l_char;
600 IF cFndUser%NOTFOUND
601 THEN
602 x_return_status := FND_API.G_RET_STS_ERROR;
603 END IF;
604 CLOSE cFndUser;
605 ELSE
606 x_return_status := FND_API.G_RET_STS_ERROR;
607 END IF;
608
609 IF p_party_id IS NULL
610 THEN
611 --raise error message
612 FND_MESSAGE.SET_NAME('AR','AR_CMGT_NULL_PARTY_ID');
613 FND_MSG_PUB.Add;
614 x_return_status := FND_API.G_RET_STS_ERROR;
615 END IF;
616
617 IF p_review_type IS NULL
618 THEN
619 --raise error message
620 debug('AR_CMGT_NULL_REVIEW_TYPE');
621 FND_MESSAGE.SET_NAME('AR','AR_CMGT_NULL_REVIEW_TYPE');
622 FND_MSG_PUB.Add;
623 x_return_status := FND_API.G_RET_STS_ERROR;
624 END IF;
625
626 IF p_requested_currency IS NULL
627 THEN
628 --raise error message
629 debug('AR_CMGT_NULL_REQ_CURR');
630 FND_MESSAGE.SET_NAME('AR','AR_CMGT_NULL_REQ_CURR');
631 FND_MSG_PUB.Add;
632 x_return_status := FND_API.G_RET_STS_ERROR;
633 END IF;
634
635 IF p_credit_type IS NULL
636 THEN
637 --raise error
638 debug('AR_CMGT_NULL_CREDIT_TYPE');
639 FND_MESSAGE.SET_NAME('AR','AR_CMGT_NULL_CREDIT_TYPE');
640 FND_MSG_PUB.Add;
641 x_return_status := FND_API.G_RET_STS_ERROR;
642 ELSE
643 --verify the specified value is from lookups
644 BEGIN
645 SELECT lookup_code INTO l_credit_type
646 FROM ar_lookups
647 WHERE lookup_type = 'AR_CMGT_CREDIT_TYPE'
648 AND lookup_code = p_credit_type;
649
650 EXCEPTION
651 WHEN OTHERS THEN
652 debug('AR_CMGT_INVALID_CREDIT_TYPE');
653 FND_MESSAGE.SET_NAME('AR','AR_CMGT_INVALID_CREDIT_TYPE');
654 FND_MSG_PUB.Add;
655 x_return_status := FND_API.G_RET_STS_ERROR;
656 END;
657 END IF;
658
659 IF l_credit_request_status IS NULL
660 THEN
661 l_credit_request_status := 'SUBMIT';
662 ELSE
663 IF l_credit_request_status NOT IN
664 ('SUBMIT','SAVE')
665 THEN
666 --raise error
667 debug('AR_CMGT_INVALID_CR_STATUS');
668 FND_MESSAGE.SET_NAME('AR','AR_CMGT_INVALID_CR_STATUS');
669 FND_MSG_PUB.Add;
670 x_return_status := FND_API.G_RET_STS_ERROR;
671 END IF;
672
673 END IF;
674
675 -- validate score_model_id
676 IF p_score_model_id IS NOT NULL
677 THEN
678 BEGIN
679 SELECT 'X'
680 INTO l_char
681 FROM ar_cmgt_scores
682 WHERE score_model_id = p_score_model_id
683 AND submit_flag = 'Y'
684 AND sysdate between start_date and
685 nvl(end_date,sysdate);
686 EXCEPTION
687 WHEN NO_DATA_FOUND THEN
688 debug('AR_CMGT_SCORE_NAME_INVALID');
689 FND_MESSAGE.SET_NAME('AR','AR_CMGT_SCORE_NAME_INVALID');
690 FND_MSG_PUB.Add;
691 x_return_status := FND_API.G_RET_STS_ERROR;
692 WHEN OTHERS THEN
693 debug('AR_CMGT_SCORE_NAME_INVALID, SQLERRM :'||SQLERRM);
694 x_return_status := FND_API.G_RET_STS_ERROR;
695 END;
696 END IF;
697
698 /*-----------------------------------------+
699 | CREATION |
700 +-----------------------------------------*/
701 /* bug4414414 : Added columns parent_credit_request_id and credit_request_type
702 */
703 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
704 INSERT INTO AR_CMGT_CREDIT_REQUESTS
705 (credit_request_id,
706 last_update_date,
707 last_updated_by,
708 creation_date,
709 created_by,
710 last_update_login,
711 application_number,
712 application_date,
713 requestor_type,
714 requestor_id,
715 review_type,
716 review_cycle,
717 credit_classification,
718 check_list_id,
719 credit_analyst_id,
720 limit_amount,
721 limit_currency,
722 trx_amount,
723 trx_currency,
724 credit_check_rule_id,
725 term_length,
726 credit_type,
727 party_id,
728 cust_account_id,
729 cust_acct_site_id,
730 site_use_id,
731 contact_party_id,
732 case_folder_number,
733 score_model_id,
734 attachment_flag,
735 status,
736 source_name,
737 source_user_id,
738 source_resp_id,
739 source_resp_appln_id,
740 source_security_group_id,
741 source_org_id,
742 source_column1,
743 source_column2,
744 source_column3,
745 notes,
746 request_id,
747 parent_credit_request_id,
748 credit_request_type,
749 RECOMMENDATION_NAME
750 )
751 VALUES
752 (l_credit_request_id,
753 sysdate,
754 fnd_global.user_id,
755 sysdate,
756 fnd_global.user_id,
757 fnd_global.login_id,
758 l_application_number,
759 l_application_date,
760 l_requestor_type,
761 l_requestor_id,
762 p_review_type,
763 p_review_cycle,
764 p_credit_classification,
765 null,
766 null,
767 p_requested_amount,
768 p_requested_currency,
769 p_trx_amount,
770 p_trx_currency,
771 p_credit_check_rule_id,
772 p_term_length,
773 p_credit_type,
774 p_party_id,
775 nvl(p_cust_account_id,-99),
776 nvl(p_cust_acct_site_id,-99),
777 nvl(p_site_use_id,-99),
778 p_contact_party_id,
779 p_case_folder_number,
780 p_score_model_id,
781 null,
782 l_credit_request_status,
783 p_source_name,
784 p_source_user_id,
785 p_source_resp_id,
786 p_source_appln_id,
787 p_source_security_group_id,
788 p_source_org_id,
789 p_source_column1,
790 p_source_column2,
791 p_source_column3,
792 p_notes,
793 fnd_global.conc_request_id,
794 p_parent_credit_request_id,
795 l_credit_request_type,
796 p_reco
797 );
798 -- commit;
799 /*-----------------------------------------+
800 | WORKFLOW CALL |
801 +-----------------------------------------*/
802
803 IF l_credit_request_status = 'SUBMIT'
804 THEN
805 IF pg_debug = 'Y'
806 THEN
807 debug ( 'Workflow Call');
808 END IF;
809 AR_CMGT_WF_ENGINE.START_WORKFLOW
810 (l_credit_request_id ,l_credit_request_status);
811 END IF;
812
813 p_credit_request_id := l_credit_request_id;
814
815 ELSE
816 --error was raised during the validation
817 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
818 p_count => x_msg_count,
819 p_data => x_msg_data
820 );
821 return;
822
823 END IF;
824 IF pg_debug = 'Y'
825 THEN
826 debug ( 'AR_CMGT_CREDIT_REQUEST_API(-)');
827 END IF;
828 EXCEPTION
829 WHEN FND_API.G_EXC_ERROR THEN
830
831 ROLLBACK TO CREATE_CREDIT_REQ_PVT;
832 x_return_status := FND_API.G_RET_STS_ERROR ;
833
834 --Display_Parameters;
835
836 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
837 p_count => x_msg_count,
838 p_data => x_msg_data
839 );
840 debug('FND_API.G_EXC_ERROR, x_msg_data :'||x_msg_data|| ' SQLERRM :'||SQLERRM);
841 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
842
843 ROLLBACK TO CREATE_CREDIT_REQ_PVT;
844 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
845
846 -- Display_Parameters;
847
848 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
849 p_count => x_msg_count,
850 p_data => x_msg_data
851 );
852 debug('FND_API.G_EXC_UNEXPECTED_ERROR, x_msg_data :'||x_msg_data|| ' SQLERRM :'||SQLERRM);
853 WHEN OTHERS THEN
854
855 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
856 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
857 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','CREATE_CREDIT_REQUEST : '||SQLERRM);
858 FND_MSG_PUB.Add;
859
860
861 ROLLBACK TO Create_credit_req_PVT;
862
863
864 -- Display_Parameters;
865 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
866 p_count => x_msg_count,
867 p_data => x_msg_data
868 );
869 debug('FND_API.G_EXC_UNEXPECTED_ERROR, x_msg_data :'||x_msg_data|| ' SQLERRM :'||SQLERRM);
870 END create_credit_request;
871
872 /* Overloaded create_credit_request to handle hold_reasons */
873 PROCEDURE create_credit_request
874 ( p_api_version IN NUMBER,
875 p_init_msg_list IN VARCHAR2 ,
876 p_commit IN VARCHAR2,
877 p_validation_level IN VARCHAR2,
878 x_return_status OUT NOCOPY VARCHAR2,
879 x_msg_count OUT NOCOPY NUMBER,
880 x_msg_data OUT NOCOPY VARCHAR2,
881 p_application_number IN VARCHAR2,
882 p_application_date IN DATE,
883 p_requestor_type IN VARCHAR2,
884 p_requestor_id IN NUMBER, --this happens to be the HR person_id of
885 --the requestor
886 p_review_type IN VARCHAR2,
887 p_credit_classification IN VARCHAR2,
888 p_requested_amount IN NUMBER,
889 p_requested_currency IN VARCHAR2,
890 p_trx_amount IN NUMBER,
891 p_trx_currency IN VARCHAR2,
892 p_credit_type IN VARCHAR2,
893 p_term_length IN NUMBER, --the unit is no of months
894 p_credit_check_rule_id IN NUMBER, --this is the credit check rule from the OM
895 p_credit_request_status IN VARCHAR2, --SAVE or FINISH
896 p_party_id IN NUMBER,
897 p_cust_account_id IN NUMBER,
898 p_cust_acct_site_id IN NUMBER,
899 p_site_use_id IN NUMBER,
900 p_contact_party_id IN NUMBER, --this is the party_id of the pseudo party
901 --created becoz of the contact relationship.
902 p_notes IN VARCHAR2,
903 p_source_org_id IN NUMBER,
904 p_source_user_id IN NUMBER,
905 p_source_resp_id IN NUMBER,
906 p_source_appln_id IN NUMBER,
907 p_source_security_group_id IN NUMBER,
908 p_source_name IN VARCHAR2,
909 p_source_column1 IN VARCHAR2,
910 p_source_column2 IN VARCHAR2,
911 p_source_column3 IN VARCHAR2,
912 p_credit_request_id OUT NOCOPY NUMBER,
913 p_review_cycle IN VARCHAR2 ,
914 p_case_folder_number IN VARCHAR2,
915 p_score_model_id IN NUMBER,
916 p_parent_credit_request_id IN NUMBER DEFAULT NULL,
917 p_credit_request_type IN VARCHAR2 DEFAULT NULL,
918 p_reco IN VARCHAR2 DEFAULT NULL,
919 p_hold_reason_rec IN hold_reason_rec_type
920 ) IS
921
922 l_hold_reason_rec hold_reason_rec_type;
923 l_credit_request_id NUMBER;
924
925 l_op_enabled Varchar2(1) := 'Y'; -- Added for bug 11800630
926
927 BEGIN
928
929 -- Added for bug 11800630 - Start
930 -- OCM Hook to check whether OCM is installed for an operating unit
931 IF p_source_org_id is not NULL THEN
932 l_op_enabled := AR_CMGT_CREDIT_HOOK_API.credit_check_operating_unit(p_source_org_id => p_source_org_id,
933 p_source_object_id => p_source_column1);
934 END IF;
935
936 IF (l_op_enabled <> 'Y') THEN
937 RETURN; -- Abort the credit request creation.
938 END IF;
939 -- Added for bug 11800630 - End
940
941 IF pg_debug = 'Y'
942 THEN
943 debug ( 'Overloaded AR_CMGT_CREDIT_REQUEST_API(+)');
944 END IF;
945
946 /* make normal API call */
947 create_credit_request
948 ( p_api_version,
949 p_init_msg_list,
950 p_commit,
951 p_validation_level,
952 x_return_status,
953 x_msg_count,
954 x_msg_data,
955 p_application_number,
956 p_application_date,
957 p_requestor_type,
958 p_requestor_id,
959 p_review_type,
960 p_credit_classification,
961 p_requested_amount,
962 p_requested_currency,
963 p_trx_amount,
964 p_trx_currency,
965 p_credit_type,
966 p_term_length,
967 p_credit_check_rule_id,
968 p_credit_request_status,
969 p_party_id,
970 p_cust_account_id,
971 p_cust_acct_site_id,
972 p_site_use_id,
973 p_contact_party_id,
974 p_notes,
975 p_source_org_id,
976 p_source_user_id,
977 p_source_resp_id,
978 p_source_appln_id,
979 p_source_security_group_id,
980 p_source_name,
981 p_source_column1,
982 p_source_column2,
983 p_source_column3,
984 p_credit_request_id,
985 p_review_cycle,
986 p_case_folder_number,
987 p_score_model_id,
988 p_parent_credit_request_id,
989 p_credit_request_type,
990 p_reco);
991
992 /* 8869430 - Handle hold_reason_rec here */
993 /* using p_hold_reason_rec and p_credit_request_id */
994 l_hold_reason_rec := p_hold_reason_rec;
995 l_credit_request_id := p_credit_request_id;
996
997 IF pg_debug = 'Y' THEN
998 debug ( 'Credit Request ID ' || l_credit_request_id );
999 END IF;
1000
1001 IF (l_hold_reason_rec.COUNT > 0) THEN
1002 FOR rec in l_hold_reason_rec.FIRST..l_hold_reason_rec.LAST
1003 LOOP
1004 INSERT INTO AR_CMGT_HOLD_DETAILS (HOLD_DETAIL_ID,
1005 CREDIT_REQUEST_ID,
1006 TYPE,
1007 CODE,
1008 CREATED_BY,
1009 CREATION_DATE,
1010 LAST_UPDATED_BY,
1011 LAST_UPDATE_DATE,
1012 LAST_UPDATE_LOGIN)
1013 VALUES (AR_CMGT_HOLD_DTL_S.NEXTVAL,
1014 l_credit_request_id,
1015 'REASON',
1016 l_hold_reason_rec(rec),
1017 fnd_global.user_id,
1018 SYSDATE,
1019 fnd_global.user_id,
1020 SYSDATE,
1021 fnd_global.login_id);
1022
1023 END LOOP;
1024 END IF;
1025
1026 IF pg_debug = 'Y'
1027 THEN
1028 debug ( 'Overloaded AR_CMGT_CREDIT_REQUEST_API(-)');
1029 END IF;
1030 EXCEPTION
1031 WHEN FND_API.G_EXC_ERROR THEN
1032
1033 x_return_status := FND_API.G_RET_STS_ERROR ;
1034
1035 --Display_Parameters;
1036
1037 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1038 p_count => x_msg_count,
1039 p_data => x_msg_data
1040 );
1041 debug('FND_API.G_EXC_ERROR, x_msg_data :'||x_msg_data|| ' SQLERRM :'||SQLERRM);
1042 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1043
1044 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1045
1046 -- Display_Parameters;
1047
1048 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1049 p_count => x_msg_count,
1050 p_data => x_msg_data
1051 );
1052 debug('FND_API.G_EXC_UNEXPECTED_ERROR, x_msg_data :'||x_msg_data|| ' SQLERRM :'||SQLERRM);
1053 WHEN OTHERS THEN
1054
1055 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1056 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
1057 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','CREATE_CREDIT_REQUEST : '||SQLERRM);
1058 FND_MSG_PUB.Add;
1059
1060 -- Display_Parameters;
1061 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1062 p_count => x_msg_count,
1063 p_data => x_msg_data
1064 );
1065 debug('FND_API.G_EXC_UNEXPECTED_ERROR, x_msg_data :'||x_msg_data|| ' SQLERRM :'||SQLERRM);
1066 END create_credit_request;
1067
1068 FUNCTION is_Credit_Management_Installed
1069 RETURN BOOLEAN
1070 IS
1071 CURSOR C1 IS
1072 SELECT 'x'
1073 FROM ar_cmgt_setup_options;
1074 l_return BOOLEAN;
1075 l_char VARCHAR2(1);
1076 BEGIN
1077
1078 OPEN C1;
1079
1080 FETCH C1 into l_char;
1081
1082 IF C1%NOTFOUND THEN
1083 l_return := FALSE;
1084 ELSE
1085 -- since row exists in setup options table, check in checklist table
1086 BEGIN
1087 SELECT 'x'
1088 INTO l_char
1089 FROM ar_cmgt_check_lists
1090 WHERE submit_flag = 'Y';
1091
1092 l_return := TRUE;
1093
1094 EXCEPTION
1095 WHEN NO_DATA_FOUND then
1096 l_return := FALSE;
1097 WHEN TOO_MANY_ROWS then
1098 l_return := TRUE;
1099 END;
1100
1101 END IF;
1102
1103 CLOSE C1;
1104
1105 return(l_return);
1106
1107 END;
1108
1109 FUNCTION get_application_number (
1110 p_credit_request_id IN NUMBER )
1111 RETURN VARCHAR2 IS
1112
1113 CURSOR cApplicationNumber IS
1114 SELECT application_number
1115 FROM ar_cmgt_credit_requests
1116 WHERE credit_request_id = p_credit_request_id;
1117
1118 l_application_number VARCHAR2(30);
1119 BEGIN
1120 OPEN cApplicationNumber;
1121
1122 FETCH cApplicationNumber INTO l_application_number;
1123
1124 IF cApplicationNumber%NOTFOUND
1125 THEN
1126 l_application_number := NULL;
1127 END IF;
1128
1129 CLOSE cApplicationNumber;
1130
1131 return(l_application_number);
1132 END get_application_number;
1133
1134 END AR_CMGT_CREDIT_REQUEST_API;
[Home] [Help]