[Home] [Help]
PACKAGE BODY: APPS.OCM_CREDIT_REQUEST_UPDATE_PUB
Source
1 PACKAGE BODY OCM_CREDIT_REQUEST_UPDATE_PUB AS
2 /*$Header: OCMUPCRB.pls 120.7.12020000.2 2013/03/19 20:59:49 rravikir ship $ */
3
4 pg_debug VARCHAR2(1) := nvl(fnd_profile.value('AFLOG_ENABLED'),'N');
5
6 TYPE ID_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
7
8 PROCEDURE debug (
9 p_message_name IN VARCHAR2 ) IS
10 BEGIN
11 ar_cmgt_util.debug (p_message_name, 'ar.cmgt.plsql.OCM_CREDIT_REQUEST_UPDATE_PUB' );
12 END;
13
14 PROCEDURE UPDATE_CREDIT_REQUEST (
15 p_api_version IN NUMBER,
16 p_init_msg_list IN VARCHAR2 ,
17 p_commit IN VARCHAR2,
18 p_validation_level IN VARCHAR2,
19 x_return_status OUT NOCOPY VARCHAR2,
20 x_msg_count OUT NOCOPY NUMBER,
21 x_msg_data OUT NOCOPY VARCHAR2,
22 p_credit_request_rec IN credit_request_rec ) IS
23
24 l_conc_request_id NUMBER;
25 l_status VARCHAR2(2000);
26 l_credit_request_status ar_cmgt_credit_requests.status%type;
27 l_resultout VARCHAR2(2000);
28 itemtype VARCHAR2(30) := 'ARCMGTAP';
29 itemkey VARCHAR2(30);
30 l_check_flag VARCHAr2(60);
31 BEGIN
32 IF pg_debug = 'Y'
33 THEN
34 debug ( 'OCM_CREDIT_REQUEST_UPDATE_PUB.UPDATE_CREDIT_REQUEST(+)');
35 debug ( 'Trx Amount ' || p_credit_request_rec.trx_amount);
36 debug ( 'Requested Amount ' || p_credit_request_rec.requested_amount);
37 debug ( 'Requestor ID ' || p_credit_request_rec.requestor_id);
38 debug ( 'Case Folder ID ' || p_credit_request_rec.case_folder_id);
39 debug ( 'Credit Request ID ' || p_credit_request_rec.credit_request_id);
40 debug ( 'Credit Request Status ' || p_credit_request_rec.credit_request_status);
41 debug ( 'Credit Classification ' || p_credit_request_rec.credit_classification);
42 debug ( 'review_type ' || p_credit_request_rec.review_type);
43 END IF;
44
45 SAVEPOINT UPDATE_CREDIT_REQ_PVT;
46
47 x_return_status := FND_API.G_RET_STS_SUCCESS;
48 itemkey := p_credit_request_rec.credit_request_id;
49
50 IF FND_API.to_Boolean( p_init_msg_list )
51 THEN
52 FND_MSG_PUB.initialize;
53 END IF;
54
55 IF p_credit_request_rec.credit_request_status IS NULL
56 THEN
57 IF p_credit_request_rec.credit_request_id IS NOT NULL
58 THEN
59 SELECT STATUS
60 INTO l_credit_request_status
61 FROM ar_cmgt_credit_requests
62 WHERE credit_request_id = p_credit_request_rec.credit_request_id;
63 END IF;
64 ElSE
65 l_credit_request_status := p_credit_request_rec.credit_request_status;
66 END IF;
67 IF l_credit_request_status IN ( 'SUBMIT', 'IN_PROCESS' )
68 THEN
69
70 UPDATE ar_cmgt_credit_requests
71 SET trx_amount = nvl(p_credit_request_rec.trx_amount, trx_amount),
72 limit_amount = nvl(p_credit_request_rec.requested_amount, limit_amount),
73 requestor_id = nvl(p_credit_request_rec.requestor_id, requestor_id),
74 last_updated_by = fnd_global.user_id,
75 last_update_date = sysdate
76 WHERE credit_request_id = p_credit_request_rec.credit_request_id;
77
78 -- Need to update the workflow attributes.
79 -- first check whether workflow is initiated
80 BEGIN
81 WF_ENGINE.ItemStatus(
82 itemType => 'ARCMGTAP',
83 itemkey => p_credit_request_rec.credit_request_id,
84 status => l_status,
85 result => l_resultout);
86 IF l_status <> 'COMPLETE'
87 THEN
88 WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
89 itemkey => itemkey,
90 aname => 'REQUESTED_CREDIT_LIMIT',
91 avalue => p_credit_request_rec.requested_amount );
92 WF_ENGINE.SetItemAttrNumber(itemtype => itemtype,
93 itemkey => itemkey,
94 aname => 'REQUESTOR_PERSON_ID',
95 avalue => p_credit_request_rec.requestor_id );
96 END IF;
97 EXCEPTION
98 WHEN OTHERS THEN
99 -- means wf is not yet started
100 NULL;
101 END;
102 -- Submit Refresh case Folder Request
103 IF p_credit_request_rec.case_folder_id IS NOT NULL
104 THEN
105 IF pg_debug = 'Y'
106 THEN
110 p_case_folder_id => p_credit_request_rec.case_folder_id,
107 debug ( 'AR_CMGT_REFRESH_CONC.submit_refresh_request(+)');
108 END IF;
109 AR_CMGT_REFRESH_CONC.submit_refresh_request (
111 p_called_from => 'OCM_UPDATE_CREDIT_REQUEST',
112 p_conc_request_id => l_conc_request_id );
113 IF pg_debug = 'Y'
114 THEN
115 debug ( 'Concurrent Request ID ' || l_conc_request_id);
116 debug ( 'AR_CMGT_REFRESH_CONC.submit_refresh_request(-)');
117 END IF;
118 END IF;
119 ELSIF l_credit_request_status = 'SAVE'
120 THEN
121 -- validate critical values before doing updation
122 IF p_credit_request_rec.requestor_id IS NOT NULL
123 THEN
124 IF nvl(p_credit_request_rec.requestor_type,'EMPLOYEE')='EMPLOYEE'
125 THEN
126 BEGIN
127 SELECT 'x' INTO l_check_flag
128 FROM PER_ALL_PEOPLE_F
129 WHERE sysdate between effective_start_date and effective_end_date
130 and current_employee_flag = 'Y'
131 and person_id = p_credit_request_rec.requestor_id;
132
133 EXCEPTION
134 WHEN NO_DATA_FOUND THEN
135 x_msg_data := 'Invalid Requestor Id';
136 x_return_status := FND_API.G_RET_STS_ERROR;
137 return;
138 WHEN OTHERS THEN
139 x_msg_data := Sqlerrm;
140 x_return_status := FND_API.G_RET_STS_ERROR;
141 return;
142 END;
143 ELSIF p_credit_request_rec.requestor_type = 'FND_USER'
144 THEN
145 BEGIN
146 SELECT 'x' INTO l_check_flag
147 FROM fnd_user
148 WHERE user_id = p_credit_request_rec.requestor_id;
149
150 EXCEPTION
151 WHEN NO_DATA_FOUND THEN
152 x_msg_data := 'Invalid User Id';
153 x_return_status := FND_API.G_RET_STS_ERROR;
154 return;
155 WHEN OTHERS THEN
156 x_msg_data := Sqlerrm;
157 x_return_status := FND_API.G_RET_STS_ERROR;
158 return;
159 END;
160 ELSE
161 x_msg_data := 'Invalid requestor';
162 x_return_status := FND_API.G_RET_STS_ERROR;
163 return;
164
165 END IF;
166 END IF;
167 IF p_credit_request_rec.credit_type IS NOT NULL
168 THEN
169 BEGIN
170 SELECT lookup_code INTO l_check_flag
171 FROM ar_lookups
172 WHERE lookup_type = 'AR_CMGT_CREDIT_TYPE'
173 AND lookup_code = p_credit_request_rec.credit_type;
174
175 EXCEPTION
176 WHEN NO_DATA_FOUND THEN
177 x_msg_data := 'Invalid Credit Type';
178 x_return_status := FND_API.G_RET_STS_ERROR;
179 return;
180 WHEN OTHERS THEN
181 x_msg_data := Sqlerrm;
182 x_return_status := FND_API.G_RET_STS_ERROR;
183 return;
184
185 END;
186 END IF;
187 UPDATE ar_cmgt_credit_requests
188 SET trx_amount = nvl(p_credit_request_rec.trx_amount, trx_amount),
189 limit_amount = nvl(p_credit_request_rec.requested_amount, limit_amount),
190 requestor_id = nvl(p_credit_request_rec.requestor_id, requestor_id),
191 last_updated_by = fnd_global.user_id,
192 last_update_date = sysdate,
193 review_type = nvl(p_credit_request_rec.review_type, review_type),
194 credit_classification = nvl(p_credit_request_rec.credit_classification, credit_classification),
195 limit_currency = nvl( p_credit_request_rec.requested_currency, limit_currency),
196 trx_currency = nvl( p_credit_request_rec.trx_currency, trx_currency),
197 credit_type = nvl( p_credit_request_rec.credit_type, credit_type ),
198 term_length = nvl( p_credit_request_rec.term_length, term_length ),
199 credit_check_rule_id = nvl( p_credit_request_rec.credit_check_rule_id, credit_check_rule_id ),
200 party_id = nvl( p_credit_request_rec.party_id, party_id),
201 cust_account_id = nvl( p_credit_request_rec.cust_account_id, cust_account_id ),
202 cust_acct_site_id = nvl( p_credit_request_rec.cust_acct_site_id, cust_acct_site_id ),
203 site_use_id = nvl( p_credit_request_rec.site_use_id, site_use_id ),
204 contact_party_id = nvl( p_credit_request_rec.contact_party_id, contact_party_id ),
205 notes = nvl( p_credit_request_rec.notes, notes),
206 source_org_id = nvl( p_credit_request_rec.source_org_id, source_org_id),
207 source_user_id = nvl( p_credit_request_rec.source_user_id, source_user_id),
208 source_resp_id = nvl( p_credit_request_rec.source_resp_id, source_resp_id),
209 source_resp_appln_id = nvl( p_credit_request_rec.source_resp_appln_id, source_resp_appln_id),
213 source_column2 = nvl( p_credit_request_rec.source_column2, source_column2),
210 source_security_group_id = nvl( p_credit_request_rec.source_security_group_id, source_security_group_id),
211 source_name = nvl( p_credit_request_rec.source_name, source_name),
212 source_column1 = nvl( p_credit_request_rec.source_column1, source_column1),
214 source_column3 = nvl( p_credit_request_rec.source_column3, source_column3),
215 review_cycle = nvl( p_credit_request_rec.review_cycle,review_cycle),
216 stock_exchange = nvl( p_credit_request_rec.stock_exchange, stock_exchange),
217 current_stock_price = nvl( p_credit_request_rec.current_stock_price, current_stock_price),
218 stock_currency = nvl( p_credit_request_rec.stock_currency,stock_currency),
219 market_capitalization = nvl( p_credit_request_rec.market_capitalization,market_capitalization),
220 market_cap_monetary_unit = nvl( p_credit_request_rec.market_cap_monetary_unit,market_cap_monetary_unit),
221 pending_litigations = nvl( p_credit_request_rec.pending_litigations,pending_litigations),
222 bond_rating = nvl( p_credit_request_rec.bond_rating,bond_rating),
223 legal_entity_name = nvl( p_credit_request_rec.legal_entity_name,legal_entity_name),
224 entity_type = nvl( p_credit_request_rec.entity_type,entity_type),
225 RECOMMENDATION_NAME = nvl( p_credit_request_rec.RECOMMENDATION_NAME,RECOMMENDATION_NAME)
226 WHERE credit_request_id = p_credit_request_rec.credit_request_id
227 AND status IN ('SAVE', 'IN_PROCESS');
228
229 END IF;
230 IF pg_debug = 'Y'
231 THEN
232 debug ( 'OCM_CREDIT_REQUEST_UPDATE_PUB.UPDATE_CREDIT_REQUEST(-)');
233 END IF;
234 EXCEPTION
235 WHEN OTHERS THEN
236 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
237 ROLLBACK TO UPDATE_CREDIT_REQ_PVT;
238 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
239 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','UPDATE_CREDIT_REQUEST : '||SQLERRM);
240 FND_MSG_PUB.Add;
241 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
242 p_count => x_msg_count,
243 p_data => x_msg_data
244 );
245
246 END;
247
248 PROCEDURE GET_CREDIT_REQUEST_REC (
249 p_credit_request_id IN NUMBER,
250 p_return_status OUT NOCOPY VARCHAR2,
251 p_error_msg OUT NOCOPY VARCHAR2,
252 p_credit_request_rec OUT NOCOPY credit_request_rec ) IS
253
254 l_credit_request_id ar_cmgt_credit_requests.credit_request_id%type;
255 l_APPLICATION_NUMBER ar_cmgt_credit_requests.APPLICATION_NUMBER%type;
256 l_APPLICATION_DATE ar_cmgt_credit_requests.application_date%type;
257 l_REQUESTOR_TYPE ar_cmgt_credit_requests.requestor_type%type;
258 l_REQUESTOR_ID ar_cmgt_credit_requests.requestor_id%type;
259 l_REVIEW_TYPE ar_cmgt_credit_requests.review_type%type;
260 l_CREDIT_CLASSIFICATION ar_cmgt_credit_requests.credit_classification%type;
261 l_CHECK_LIST_ID ar_cmgt_credit_requests.check_list_id%type;
262 l_CREDIT_ANALYST_ID ar_cmgt_credit_requests.credit_analyst_id%type;
263 l_LIMIT_AMOUNT ar_cmgt_credit_requests.limit_amount%type;
264 l_LIMIT_CURRENCY ar_cmgt_credit_requests.limit_currency%type;
265 l_TRX_AMOUNT ar_cmgt_credit_requests.trx_amount%type;
266 l_TRX_CURRENCY ar_cmgt_credit_requests.trx_currency%type;
267 l_CREDIT_CHECK_RULE_ID ar_cmgt_credit_requests.credit_check_rule_id%type;
268 l_TERM_LENGTH ar_cmgt_credit_requests.term_length%type;
269 l_CREDIT_TYPE ar_cmgt_credit_requests.credit_type%type;
270 l_PARTY_ID ar_cmgt_credit_requests.party_id%type;
271 l_CUST_ACCOUNT_ID ar_cmgt_credit_requests.cust_account_id%type;
272 l_CUST_ACCT_SITE_ID ar_cmgt_credit_requests.cust_acct_site_id%type;
273 l_SITE_USE_ID ar_cmgt_credit_requests.site_use_id%type;
274 l_CONTACT_PARTY_ID ar_cmgt_credit_requests.contact_party_id%type;
275 l_STOCK_EXCHANGE ar_cmgt_credit_requests.stock_exchange%type;
276 l_CURRENT_STOCK_PRICE ar_cmgt_credit_requests.current_stock_price%type;
277 l_STOCK_CURRENCY ar_cmgt_credit_requests.stock_currency%type;
278 l_MARKET_CAPITALIZATION ar_cmgt_credit_requests.market_capitalization%type;
279 l_MARKET_CAP_MONETARY_UNIT ar_cmgt_credit_requests.market_cap_monetary_unit%type;
280 l_PENDING_LITIGATIONS ar_cmgt_credit_requests.pending_litigations%type;
281 l_BOND_RATING ar_cmgt_credit_requests.bond_rating%type;
282 l_LEGAL_ENTITY_NAME ar_cmgt_credit_requests.legal_entity_name%type;
283 l_ENTITY_TYPE ar_cmgt_credit_requests.entity_type%type;
284 l_CASE_FOLDER_NUMBER ar_cmgt_credit_requests.case_folder_number%type;
285 l_SCORE_MODEL_ID ar_cmgt_credit_requests.score_model_id%type;
286 l_STATUS ar_cmgt_credit_requests.status%type;
287 l_SOURCE_NAME ar_cmgt_credit_requests.source_name%type;
288 l_SOURCE_USER_ID ar_cmgt_credit_requests.source_user_id%type;
289 l_SOURCE_RESP_ID ar_cmgt_credit_requests.source_resp_id%type;
290 l_SOURCE_RESP_APPLN_ID ar_cmgt_credit_requests.source_resp_appln_id%type;
291 l_SOURCE_SECURITY_GROUP_ID ar_cmgt_credit_requests.source_security_group_id%type;
292 l_SOURCE_ORG_ID ar_cmgt_credit_requests.source_org_id%type;
293 l_SOURCE_COLUMN1 ar_cmgt_credit_requests.source_column1%type;
294 l_SOURCE_COLUMN2 ar_cmgt_credit_requests.source_column2%type;
295 l_SOURCE_COLUMN3 ar_cmgt_credit_requests.source_column3%type;
296 l_NOTES ar_cmgt_credit_requests.notes%type;
297 l_REVIEW_CYCLE ar_cmgt_credit_requests.review_cycle%type;
298
299 BEGIN
300 IF pg_debug = 'Y'
301 THEN
305 -- Get credit request rec
302 debug ( 'OCM_CREDIT_REQUEST_UPDATE_PUB.GET_CREDIT_REQUEST_REC(+)');
303 END IF;
304 p_return_status := FND_API.G_RET_STS_SUCCESS;
306 SELECT credit_request_id,
307 APPLICATION_NUMBER,
308 APPLICATION_DATE,
309 REQUESTOR_TYPE,
310 REQUESTOR_ID,
311 REVIEW_TYPE,
312 CREDIT_CLASSIFICATION,
313 CHECK_LIST_ID,
314 CREDIT_ANALYST_ID,
315 LIMIT_AMOUNT,
316 LIMIT_CURRENCY,
317 TRX_AMOUNT,
318 TRX_CURRENCY,
319 CREDIT_CHECK_RULE_ID,
320 TERM_LENGTH,
321 CREDIT_TYPE,
322 PARTY_ID,
323 CUST_ACCOUNT_ID,
324 CUST_ACCT_SITE_ID,
325 SITE_USE_ID,
326 CONTACT_PARTY_ID,
327 STOCK_EXCHANGE,
328 CURRENT_STOCK_PRICE,
329 STOCK_CURRENCY,
330 MARKET_CAPITALIZATION,
331 MARKET_CAP_MONETARY_UNIT,
332 PENDING_LITIGATIONS,
333 BOND_RATING,
334 LEGAL_ENTITY_NAME,
335 ENTITY_TYPE,
336 CASE_FOLDER_NUMBER,
337 SCORE_MODEL_ID,
338 STATUS,
339 SOURCE_NAME,
340 SOURCE_USER_ID,
341 SOURCE_RESP_ID,
342 SOURCE_RESP_APPLN_ID,
343 SOURCE_SECURITY_GROUP_ID,
344 SOURCE_ORG_ID,
345 SOURCE_COLUMN1,
346 SOURCE_COLUMN2,
347 SOURCE_COLUMN3,
348 NOTES,
349 REVIEW_CYCLE
350 INTO l_credit_request_id,
351 l_APPLICATION_NUMBER,
352 l_APPLICATION_DATE,
353 l_REQUESTOR_TYPE,
354 l_REQUESTOR_ID,
355 l_REVIEW_TYPE,
356 l_CREDIT_CLASSIFICATION,
357 l_CHECK_LIST_ID,
358 l_CREDIT_ANALYST_ID,
359 l_LIMIT_AMOUNT,
360 l_LIMIT_CURRENCY,
361 l_TRX_AMOUNT,
362 l_TRX_CURRENCY,
363 l_CREDIT_CHECK_RULE_ID,
364 l_TERM_LENGTH,
365 l_CREDIT_TYPE,
366 l_PARTY_ID,
367 l_CUST_ACCOUNT_ID,
368 l_CUST_ACCT_SITE_ID,
369 l_SITE_USE_ID,
370 l_CONTACT_PARTY_ID,
371 l_STOCK_EXCHANGE,
372 l_CURRENT_STOCK_PRICE,
373 l_STOCK_CURRENCY,
374 l_MARKET_CAPITALIZATION,
375 l_MARKET_CAP_MONETARY_UNIT,
376 l_PENDING_LITIGATIONS,
377 l_BOND_RATING,
378 l_LEGAL_ENTITY_NAME,
379 l_ENTITY_TYPE,
380 l_CASE_FOLDER_NUMBER,
381 l_SCORE_MODEL_ID,
382 l_STATUS,
383 l_SOURCE_NAME,
384 l_SOURCE_USER_ID,
385 l_SOURCE_RESP_ID,
386 l_SOURCE_RESP_APPLN_ID,
387 l_SOURCE_SECURITY_GROUP_ID,
388 l_SOURCE_ORG_ID,
389 l_SOURCE_COLUMN1,
390 l_SOURCE_COLUMN2,
391 l_SOURCE_COLUMN3,
392 l_NOTES,
393 l_REVIEW_CYCLE
394 FROM ar_cmgt_credit_requests
395 WHERE credit_request_id = p_credit_request_id;
396
397 IF pg_debug = 'Y'
398 THEN
399 debug ('OCM_CREDIT_REQUEST_UPDATE_PUB.GET_CREDIT_REQUEST_REC (+)' );
400 debug ('Credit request Id : '|| l_credit_request_id );
401 debug ('APPLICATION_NUMBER '|| l_APPLICATION_NUMBER);
402 debug ('APPLICATION_DATE '|| l_APPLICATION_DATE);
403 debug ('REQUESTOR_TYPE '|| l_REQUESTOR_TYPE);
404 debug ('REQUESTOR_ID ' || l_REQUESTOR_ID);
405 debug ('REVIEW_TYPE '|| l_REVIEW_TYPE);
406 debug ('CREDIT_CLASSIFICATION '|| l_CREDIT_CLASSIFICATION);
407 debug ('CHECK_LIST_ID '|| l_CHECK_LIST_ID);
408 debug ('CREDIT_ANALYST_ID '|| l_CREDIT_ANALYST_ID);
409 debug ('LIMIT_AMOUNT '|| l_LIMIT_AMOUNT);
410 debug ('LIMIT_CURRENCY '|| l_LIMIT_CURRENCY);
411 debug ('TRX_AMOUNT '|| l_TRX_AMOUNT);
412 debug ('TRX_CURRENCY '|| l_TRX_CURRENCY);
413 debug ('CREDIT_CHECK_RULE_ID '|| l_CREDIT_CHECK_RULE_ID);
414 debug ('TERM_LENGTH '|| l_TERM_LENGTH);
415 debug ('CREDIT_TYPE '|| l_CREDIT_TYPE);
416 debug ('PARTY_ID '|| l_PARTY_ID);
417 debug ('CUST_ACCOUNT_ID '|| l_CUST_ACCOUNT_ID);
418 debug ('CUST_ACCT_SITE_ID '|| l_CUST_ACCT_SITE_ID);
419 debug ('SITE_USE_ID '|| l_SITE_USE_ID);
420 debug ('CONTACT_PARTY_ID '|| l_CONTACT_PARTY_ID);
421 debug ('STOCK_EXCHANGE '|| l_STOCK_EXCHANGE);
422 debug ('CURRENT_STOCK_PRICE '|| l_CURRENT_STOCK_PRICE);
423 debug ('STOCK_CURRENCY '|| l_STOCK_CURRENCY);
424 debug ('MARKET_CAPITALIZATION '|| l_MARKET_CAPITALIZATION);
425 debug ('MARKET_CAP_MONETARY_UNIT '|| l_MARKET_CAP_MONETARY_UNIT);
426 debug ('PENDING_LITIGATIONS '|| l_PENDING_LITIGATIONS);
427 debug ('BOND_RATING ' || l_BOND_RATING);
428 debug ('LEGAL ENTITY NAMe '|| l_LEGAL_ENTITY_NAME);
429 debug ('ENTITY_TYPE '|| l_ENTITY_TYPE);
430 debug ('CASE_FOLDER_NUMBER '|| l_CASE_FOLDER_NUMBER);
431 debug ('SCORE_MODEL_ID '|| l_SCORE_MODEl_ID);
432 debug ('STATUS '|| l_STATUS);
433 debug ('SOURCE_NAME '|| l_SOURCE_NAME);
434 debug ('SOURCE_USER_ID '|| l_SOURCE_USER_ID);
435 debug ('SOURCE_RESP_ID '|| l_SOURCE_RESP_ID);
436 debug ('SOURCE_RESP_APPLN_ID '|| l_SOURCE_RESP_APPLN_ID);
437 debug ('SOURCE_SECURITY_GROUP_ID '|| l_SOURCE_SECURITY_GROUP_ID);
438 debug ('SOURCE_ORG_ID '|| l_SOURCE_ORG_ID);
439 debug ('SOURCE_COLUMN1 '|| l_SOURCE_COLUMN1);
440 debug ('SOURCE_COLUMN2 '|| l_SOURCE_COLUMN2);
441 debug ('SOURCE_COLUMN3 ' || l_SOURCE_COLUMN3);
442 debug ('NOTES '|| l_NOTES );
443 debug ('REVIEW_CYCLE '|| l_REVIEW_CYCLE );
444 END IF;
445
446 p_credit_request_rec.credit_request_id := l_credit_request_id;
447 p_credit_request_rec.APPLICATION_NUMBER := l_APPLICATION_NUMBER;
448 p_credit_request_rec.APPLICATION_DATE := l_APPLICATION_DATE;
449 p_credit_request_rec.REQUESTOR_TYPE := l_REQUESTOR_TYPE;
450 p_credit_request_rec.REQUESTOR_ID := l_REQUESTOR_ID;
451 p_credit_request_rec.REVIEW_TYPE := l_REVIEW_TYPE;
452 p_credit_request_rec.CREDIT_CLASSIFICATION := l_CREDIT_CLASSIFICATION;
453 p_credit_request_rec.CHECK_LIST_ID := l_CHECK_LIST_ID;
457 p_credit_request_rec.TRX_AMOUNT := l_TRX_AMOUNT;
454 p_credit_request_rec.CREDIT_ANALYST_ID := l_CREDIT_ANALYST_ID;
455 p_credit_request_rec.REQUESTED_AMOUNT := l_LIMIT_AMOUNT;
456 p_credit_request_rec.REQUESTED_CURRENCY := l_LIMIT_CURRENCY;
458 p_credit_request_rec.TRX_CURRENCY := l_TRX_CURRENCY;
459 p_credit_request_rec.CREDIT_CHECK_RULE_ID := l_CREDIT_CHECK_RULE_ID;
460 p_credit_request_rec.TERM_LENGTH := l_TERM_LENGTH;
461 p_credit_request_rec.CREDIT_TYPE := l_CREDIT_TYPE;
462 p_credit_request_rec.PARTY_ID := l_PARTY_ID;
463 p_credit_request_rec.CUST_ACCOUNT_ID := l_CUST_ACCOUNT_ID;
464 p_credit_request_rec.CUST_ACCT_SITE_ID := l_CUST_ACCT_SITE_ID;
465 p_credit_request_rec.SITE_USE_ID := l_SITE_USE_ID;
466 p_credit_request_rec.CONTACT_PARTY_ID := l_CONTACT_PARTY_ID;
467 p_credit_request_rec.STOCK_EXCHANGE := l_STOCK_EXCHANGE;
468 p_credit_request_rec.CURRENT_STOCK_PRICE := l_CURRENT_STOCK_PRICE;
469 p_credit_request_rec.STOCK_CURRENCY := l_STOCK_CURRENCY;
470 p_credit_request_rec.MARKET_CAPITALIZATION := l_MARKET_CAPITALIZATION;
471 p_credit_request_rec.MARKET_CAP_MONETARY_UNIT := l_MARKET_CAP_MONETARY_UNIT;
472 p_credit_request_rec.PENDING_LITIGATIONS := l_PENDING_LITIGATIONS;
473 p_credit_request_rec.BOND_RATING := l_BOND_RATING;
474 p_credit_request_rec.LEGAL_ENTITY_NAME := l_LEGAL_ENTITY_NAME;
475 p_credit_request_rec.ENTITY_TYPE := l_ENTITY_TYPE;
476 p_credit_request_rec.CASE_FOLDER_NUMBER := l_CASE_FOLDER_NUMBER;
477 p_credit_request_rec.SCORE_MODEL_ID := l_SCORE_MODEL_ID;
478 p_credit_request_rec.SOURCE_NAME := l_SOURCE_NAME;
479 p_credit_request_rec.SOURCE_USER_ID := l_SOURCE_USER_ID;
480 p_credit_request_rec.SOURCE_RESP_ID := l_SOURCE_RESP_ID;
481 p_credit_request_rec.SOURCE_RESP_APPLN_ID := l_SOURCE_RESP_APPLN_ID;
482 p_credit_request_rec.SOURCE_SECURITY_GROUP_ID := l_SOURCE_SECURITY_GROUP_ID;
483 p_credit_request_rec.SOURCE_ORG_ID := l_SOURCE_ORG_ID;
484 p_credit_request_rec.SOURCE_COLUMN1 := l_SOURCE_COLUMN1;
485 p_credit_request_rec.SOURCE_COLUMN2 := l_SOURCE_COLUMN2;
486 p_credit_request_rec.SOURCE_COLUMN3 := l_SOURCE_COLUMN3;
487 p_credit_request_rec.NOTES := l_NOTES;
488 p_credit_request_rec.REVIEW_CYCLE := l_REVIEW_CYCLE;
489
490 IF pg_debug = 'Y'
491 THEN
492 debug ( 'OCM_CREDIT_REQUEST_UPDATE_PUB.GET_CREDIT_REQUEST_REC(-)');
493 END IF;
494 EXCEPTION
495 WHEN NO_DATA_FOUND
496 THEN
497 NULL;
498 WHEN OTHERS
499 THEN
500 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
501 p_error_msg := 'OCM_CREDIT_REQUEST_UPDATE_PUB.GET_CREDIT_REQUEST_REC '|| sqlerrm;
502
503 END;
504
505 PROCEDURE update_credit_request_status (
506 p_api_version IN NUMBER,
507 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_TRUE,
508 p_commit IN VARCHAR2,
509 p_validation_level IN VARCHAR2,
510 x_return_status OUT NOCOPY VARCHAR2,
511 x_msg_count OUT NOCOPY NUMBER,
512 x_msg_data OUT NOCOPY VARCHAR2,
513 p_credit_request_id IN NUMBER,
514 p_credit_request_status IN VARCHAR2 DEFAULT 'SUBMIT') IS
515
516 l_status ar_cmgt_credit_requests.status%type;
517
518 CURSOR cCreditRequests IS
519 SELECT credit_request_id
520 FROM ar_cmgt_credit_requests
521 WHERE parent_credit_request_id = p_credit_request_id
522 AND status = 'SAVE';
523
524 /* 6838491 */
525 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
526 l_msg_count NUMBER;
527 l_msg_data VARCHAR2(2000);
528 l_guar_credit_request_id ar_cmgt_credit_requests.credit_request_id%TYPE;
529 i NUMBER := 0;
530 datapoint_t id_type; -- table
531 guarcredreq_t id_type; -- table
532
533 CURSOR c_guar_list(cp_credit_request_id NUMBER) IS
534 SELECT gd.datapoint_id, -- unique key
535 gd.credit_request_id, gd.review_type, gd.credit_classification,
536 gd.guaranteed_amount, gd.currency, gd.party_id,
537 gd.contact_party_id, gd.notes,
538 pr.source_org_id, pr.source_user_id,
539 pr.source_resp_id, pr.source_resp_appln_id,
540 pr.source_security_group_id,
541 pr.source_name parent_source_name,
542 pr.application_date parent_application_date,
543 pr.requestor_type, pr.requestor_id
544 FROM ar_cmgt_guarantor_data gd,
545 ar_cmgt_credit_requests pr
546 WHERE gd.credit_request_id = cp_credit_request_id
547 AND gd.credit_request_id = pr.credit_request_id
548 AND gd.guarantor_credit_request_id IS NULL; -- prevents duplics
549
550 BEGIN
551 IF pg_debug = 'Y'
552 THEN
553 debug ( 'OCM_CREDIT_REQUEST_UPDATE_PUB.update_credit_request_status(+)');
554 debug ( 'Credit request Id :' || to_char(p_credit_request_id));
555 debug ( 'Status :' || p_credit_request_status);
556 END IF;
557 x_return_status := FND_API.G_RET_STS_SUCCESS;
558
559 SAVEPOINT UPDATE_CREDIT_REQ_STATUS;
560
561 IF FND_API.to_Boolean( p_init_msg_list )
562 THEN
563 FND_MSG_PUB.initialize;
564 END IF;
565
566 -- first check the status in case it was passed
567 IF p_credit_request_status NOT IN ( 'SUBMIT', 'SAVE')
568 THEN
569 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
570 x_msg_data := 'Invalid Status';
571 return;
572 END IF;
573
574 -- check for valid credit request id and status
575 BEGIN
576 SELECT status
580
577 INTO l_status
578 FROM ar_cmgt_credit_requests
579 WHERE credit_request_id = p_credit_request_id;
581 EXCEPTION
582 WHEN NO_DATA_FOUND THEN
583 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
584 x_msg_data := 'Invalid Credit Request Id';
585 return;
586 WHEN OTHERS THEN
587 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
588 x_msg_data := sqlerrm;
589 return;
590 END;
591
592 IF l_status = 'SAVE' and p_credit_request_status = 'SUBMIT'
593 THEN
594 IF pg_debug = 'Y'
595 THEN
596 debug ( 'Updating Status to Submit anc calling WF (+)');
597 END IF;
598 -- submit workflow
599 UPDATE ar_cmgt_credit_requests
600 SET status = 'SUBMIT',
601 last_updated_by = fnd_global.user_id,
602 last_update_date = sysdate
603 WHERE credit_request_id = p_credit_request_id;
604
605 AR_CMGT_WF_ENGINE.START_WORKFLOW
606 (p_credit_request_id ,p_credit_request_status);
607
608 IF pg_debug = 'Y'
609 THEN
610 debug ( 'Updating Status to Submit anc calling WF (-)');
611 END IF;
612 END IF;
613
614 /* 6838491 - OKL has directly referenced some OCM OAF components
615 and that includes the Guarantor pages. So they (OKL) can
616 add Guarantors to Saved CRs. However, OCM submits the
617 child CRs for the Guarantors from the submit button on the UI
618 and this is not available to OKL.
619
620 To rectify, we need to see if there are guarantors for this
621 parent request, and submit child CRs for each guarantor. */
622
623 FOR c_guar IN c_guar_list(p_credit_request_id) LOOP
624
625 IF pg_debug = 'Y'
626 THEN
627 debug ( 'Processing guarantor party_id = ' || c_guar.party_id);
628 END IF;
629
630 AR_CMGT_CREDIT_REQUEST_API.create_credit_request
631 (p_api_version => 1 ,
632 p_init_msg_list => FND_API.G_FALSE,
633 p_commit => FND_API.G_FALSE,
634 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
635 x_return_status => l_return_status ,
636 x_msg_count => l_msg_count ,
637 x_msg_data => l_msg_data ,
638 p_application_number => NULL ,
639 p_application_date => c_guar.parent_application_date,
640 p_requestor_type => NULL ,
641 p_requestor_id => -1 ,
642 p_review_type => c_guar.review_type,
643 p_review_cycle => NULL ,
644 p_credit_classification => c_guar.credit_classification ,
645 p_requested_amount => c_guar.guaranteed_amount ,
646 p_requested_currency => c_guar.currency ,
647 p_trx_amount => c_guar.guaranteed_amount ,
648 p_trx_currency => c_guar.currency ,
649 p_credit_type => 'TRADE' ,
650 p_term_length => 0 ,
651 p_credit_check_rule_id => 0 ,
652 p_credit_request_status => 'SUBMIT',
653 p_party_id => c_guar.party_id ,
654 p_cust_account_id => -99 ,
655 p_cust_acct_site_id => -99 ,
656 p_site_use_id => -99 ,
657 p_contact_party_id => c_guar.contact_party_id,
658 p_notes => c_guar.notes,
659 p_source_org_id => c_guar.source_org_id,
660 p_source_user_id => c_guar.source_user_id,
661 p_source_resp_id => c_guar.source_resp_id,
662 p_source_appln_id => c_guar.source_resp_appln_id,
663 p_source_security_group_id => c_guar.source_security_group_id,
664 p_source_name => c_guar.parent_source_name,
665 p_source_column1 => NULL ,
666 p_source_column2 => NULL ,
667 p_source_column3 => NULL ,
668 p_case_folder_number => NULL ,
669 p_score_model_id => NULL ,
670 p_credit_request_id => l_guar_credit_request_id, --out
671 p_parent_credit_request_id => p_credit_request_id,
672 p_credit_request_type =>'GUARANTOR' ) ;
673
674 IF pg_debug = 'Y'
675 THEN
676 debug ( 'Processed guarantor party_id = ' || c_guar.party_id ||
677 ' credit_request_id = ' || l_guar_credit_request_id);
678 END IF;
679
680 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
681 THEN
682 debug('Creating guarantor credit request failed');
683 x_return_status := l_return_status;
684 x_msg_data := l_msg_data;
685 x_msg_count := l_msg_count;
686 ROLLBACK TO UPDATE_CREDIT_REQ_STATUS;
687 RETURN;
688 END IF;
689
690 /* Store datapoint_id and guarantor_credit_request_id
691 so we can stamp them on ar_cmgt_guarantor_data afterwards */
692 datapoint_t(i) := c_guar.datapoint_id;
693 guarcredreq_t(i) := l_guar_credit_request_id;
694 i := i + 1;
695 END LOOP;
696
697 /* Now update ar_cmgt_guarantor_data to set
698 guarantor_credit_request_id */
699 IF i > 0
700 THEN
701 /* 6956789 - Only execute update if there are rows
702 to process */
703 FORALL x in datapoint_t.FIRST..datapoint_t.LAST
704 UPDATE ar_cmgt_guarantor_data
705 SET guarantor_credit_request_id = guarcredreq_t(x)
706 WHERE datapoint_id = datapoint_t(x);
707 END IF;
708
709 /* End 6838491 */
710
711 -- now submit all the child credit request in case if exists any
712 FOR cCreditRequestsRec IN cCreditRequests
713 LOOP
714 IF pg_debug = 'Y'
715 THEN
716 debug ( 'Submitting child credit requests '|| to_char(cCreditRequestsRec.credit_request_id));
717 END IF;
718
719 UPDATE ar_cmgt_credit_requests
720 SET status = 'SUBMIT',
721 last_updated_by = fnd_global.user_id,
722 last_update_date = sysdate
723 WHERE credit_request_id = cCreditRequestsRec.credit_request_id;
724
725 AR_CMGT_WF_ENGINE.START_WORKFLOW
726 (cCreditRequestsRec.credit_request_id ,p_credit_request_status);
727
728 END LOOP;
729
730 IF pg_debug = 'Y'
731 THEN
732 debug ( 'Submitted All Child Credit requests');
733 END IF;
734 IF pg_debug = 'Y'
735 THEN
736 debug ( 'OCM_CREDIT_REQUEST_UPDATE_PUB.update_credit_request_status(-)');
737 END IF;
738
739 EXCEPTION
740 WHEN OTHERS THEN
741 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
742 x_msg_data := sqlerrm;
743 ROLLBACK TO UPDATE_CREDIT_REQ_STATUS;
744 return;
745 END;
746
747 END OCM_CREDIT_REQUEST_UPDATE_PUB;