[Home] [Help]
PACKAGE BODY: APPS.OKL_CREDIT_MGNT_PVT
Source
1 PACKAGE BODY OKL_CREDIT_MGNT_PVT AS
2 /* $Header: OKLRCMTB.pls 120.3 2005/10/30 04:32:20 appldev noship $ */
3
4 ------------------------
5 -- submit_credit_request
6 ------------------------
7 PROCEDURE submit_credit_request
8 (p_api_version IN NUMBER
9 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
10 ,x_return_status OUT NOCOPY VARCHAR2
11 ,x_msg_count OUT NOCOPY NUMBER
12 ,x_msg_data OUT NOCOPY VARCHAR2
13 ,p_contract_id IN NUMBER
14 ,p_review_type IN VARCHAR2
15 ,p_credit_classification IN VARCHAR2
16 ,p_requested_amount IN NUMBER
17 ,p_contact_party_id IN NUMBER
18 ,p_notes IN VARCHAR2
19 ,p_chr_rec IN l_chr_rec) IS
20
21 l_cm_installed BOOLEAN;
22 l_request_status VARCHAR2(30);
23 l_application_number VARCHAR2(30);
24 l_k_start_date DATE;
25 l_currency VARCHAR2(15);
26 l_org_id NUMBER;
27 l_party_id NUMBER;
28 l_term NUMBER;
29 l_credit_classification VARCHAR2(30);
30 lx_credit_request_id NUMBER;
31 l_resource_id NUMBER;
32 l_return_status VARCHAR2(1);
33
34 l_interaction_rec jtf_ih_pub.interaction_rec_type;
35 l_activity_rec jtf_ih_pub.activity_rec_type;
36 lx_interaction_id NUMBER;
37 lx_activity_id NUMBER;
38
39 l_crqv_rec okl_crq_pvt.crqv_rec_type;
40 lx_crqv_rec okl_crq_pvt.crqv_rec_type;
41
42 CURSOR c_check_submitted IS
43 SELECT credit_req_number,
44 status
45 FROM okl_credit_requests
46 WHERE quote_id = p_contract_id
47 AND status IN ('SUBMITTED' , 'APPROVED');
48
49 CURSOR c_credit_class IS
50 SELECT CREDIT_CLASSIFICATION_CODE
51 FROM hz_cust_accounts
52 WHERE cust_account_id = p_chr_rec.cust_acct_id;
53
54 BEGIN
55
56 l_cm_installed := AR_CMGT_CREDIT_REQUEST_API.is_Credit_Management_Installed;
57
58 IF NOT l_cm_installed THEN
59
60 OKL_API.set_message(p_app_name => OKL_API.G_APP_NAME,
61 p_msg_name => 'OKL_CM_NOTINSTALLED');
62
63 RAISE G_EXCEPTION_ERROR;
64
65 END IF;
66
67 OPEN c_check_submitted;
68 FETCH c_check_submitted INTO l_application_number, l_request_status;
69 CLOSE c_check_submitted;
70
71 IF l_application_number IS NOT NULL THEN
72
73 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
74 p_msg_name => 'OKL_CR_SUBMITTED',
75 p_token1 => 'REQ_ID',
76 p_token1_value => l_application_number,
77 p_token2 => 'REQ_STATUS',
78 p_token2_value => l_request_status );
79
80 RAISE G_EXCEPTION_ERROR;
81
82 END IF;
83
84 IF p_requested_amount <= 0 THEN
85
86 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
87 p_msg_name => 'OKL_CR_AMT_ZERO');
88
89 RAISE G_EXCEPTION_ERROR;
90
91 END IF;
92
93 SELECT chr.start_date,
94 chr.currency_code,
95 chr.authoring_org_id,
96 cpl.object1_id1,
97 khr.term_duration
98 INTO l_k_start_date,
99 l_currency,
100 l_org_id,
101 l_party_id,
102 l_term
103 FROM okc_k_headers_b chr,
104 okl_k_headers khr,
105 okc_k_party_roles_b cpl
106 WHERE chr.id = p_contract_id
107 AND chr.id = khr.id
108 AND chr.id = cpl.dnz_chr_id
109 AND cpl.rle_code = 'LESSEE';
110
111 SELECT AR_CMGT_APPLICATION_NUM_S.NEXTVAL
112 INTO l_application_number
113 FROM DUAL;
114
115 OPEN c_credit_class;
116 FETCH c_credit_class INTO l_credit_classification;
117 CLOSE c_credit_class;
118
119 ar_cmgt_credit_request_api.create_credit_request
120 ( p_api_version => G_API_VERSION
121 ,p_init_msg_list => OKL_API.G_FALSE
122 ,p_commit => 'Y'
123 ,p_validation_level => ''
124 ,x_return_status => l_return_status
125 ,x_msg_count => x_msg_count
126 ,x_msg_data => x_msg_data
127 ,p_application_number => l_application_number
128 ,p_application_date => TRUNC(SYSDATE)
129 ,p_requestor_type => NULL
130 ,p_requestor_id => fnd_global.employee_id
131 ,p_review_type => 'NEW_CREDIT_LIMIT'
132 ,p_credit_classification => l_credit_classification
133 ,p_requested_amount => p_requested_amount
134 ,p_requested_currency => l_currency
135 ,p_trx_amount => NULL
136 ,p_trx_currency => NULL
137 ,p_credit_type => 'TERM'
138 ,p_term_length => l_term
139 ,p_credit_check_rule_id => NULL
140 ,p_credit_request_status => 'SUBMIT'
141 ,p_party_id => l_party_id
142 ,p_cust_account_id => p_chr_rec.cust_acct_id
143 ,p_cust_acct_site_id => NULL
144 ,p_site_use_id => NULL
145 ,p_contact_party_id => p_contact_party_id
146 ,p_notes => p_notes
147 ,p_source_org_id => l_org_id
148 ,p_source_user_id => fnd_global.USER_ID
149 ,p_source_resp_id => fnd_global.RESP_ID
150 ,p_source_appln_id => 540
151 ,p_source_security_group_id => fnd_global.SECURITY_GROUP_ID
152 ,p_source_name => 'OKL'
153 ,p_source_column1 => ''
154 ,p_source_column2 => ''
155 ,p_source_column3 => ''
156 ,p_credit_request_id => lx_credit_request_id
157 ,p_review_cycle => '');
158
159 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
160 RAISE G_EXCEPTION_ERROR;
161 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
162 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
163 END IF;
164
165 l_crqv_rec.quote_id := p_contract_id;
166 l_crqv_rec.credit_req_number := l_application_number;
167 l_crqv_rec.credit_req_id := lx_credit_request_id;
168 l_crqv_rec.credit_amount := p_requested_amount;
169 l_crqv_rec.requested_by := fnd_global.EMPLOYEE_ID;
170 l_crqv_rec.requested_date := sysdate;
171 l_crqv_rec.approved_by := NULL;
172 l_crqv_rec.approved_date := NULL;
173 l_crqv_rec.status := 'SUBMITTED';
174 l_crqv_rec.credit_khr_id := NULL;
175 l_crqv_rec.currency_code := l_currency;
176 l_crqv_rec.org_id := l_org_id;
177
178 okl_credit_request_pub.insert_credit_request(
179 p_api_version => G_API_VERSION
180 ,p_init_msg_list => OKL_API.G_FALSE
181 ,x_return_status => l_return_status
182 ,x_msg_count => x_msg_count
183 ,x_msg_data => x_msg_data
184 ,p_crqv_rec => l_crqv_rec
185 ,x_crqv_rec => lx_crqv_rec);
186
187 IF l_return_status = G_RET_STS_ERROR THEN
188 RAISE G_EXCEPTION_ERROR;
189 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
190 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
191 END IF;
192
193 l_return_status := okl_cs_lc_contract_pvt.get_resource_id(l_resource_id);
194
195 IF l_return_status = G_RET_STS_ERROR THEN
196 RAISE G_EXCEPTION_ERROR;
197 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
198 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
199 END IF;
200
201 l_interaction_rec.start_date_time := SYSDATE;
202 l_interaction_rec.handler_id := 540;
203 l_interaction_rec.resource_id := l_resource_id;
204 l_interaction_rec.outcome_id := 10;
205 l_interaction_rec.party_id := l_party_id;
206
207 jtf_ih_pub.open_interaction(
208 p_api_version => G_API_VERSION,
209 p_init_msg_list => OKL_API.G_FALSE,
210 p_commit => OKL_API.G_TRUE,
211 p_user_id => fnd_global.USER_ID,
212 p_login_id => fnd_global.LOGIN_ID,
213 x_return_status => l_return_status,
214 x_msg_count => x_msg_count,
215 x_msg_data => x_msg_data,
216 p_interaction_rec => l_interaction_rec,
217 x_interaction_id => lx_interaction_id);
218
219 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
220 RAISE G_EXCEPTION_ERROR;
221 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
222 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
223 END IF;
224
225 l_activity_rec.cust_account_id := p_chr_rec.cust_acct_id;
226 l_activity_rec.cust_org_id := l_org_id;
227 l_activity_rec.start_date_time := SYSDATE;
228 l_activity_rec.action_item_id := 87;
229 l_activity_rec.interaction_id := lx_interaction_id;
230 l_activity_rec.outcome_id := 10;
231 l_activity_rec.action_id := 1;
232
233 jtf_ih_pub.add_activity(
234 p_api_version => G_API_VERSION,
235 p_init_msg_list => OKL_API.G_FALSE,
236 p_commit => OKL_API.G_TRUE,
237 p_user_id => fnd_global.USER_ID,
238 p_login_id => fnd_global.LOGIN_ID,
239 x_return_status => l_return_status,
240 x_msg_count => x_msg_count,
241 x_msg_data => x_msg_data,
242 p_activity_rec => l_activity_rec,
243 x_activity_id => lx_activity_id);
244
245 IF l_return_status = G_RET_STS_ERROR THEN
246 RAISE G_EXCEPTION_ERROR;
247 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
248 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
249 END IF;
250
251 jtf_ih_pub.CLOSE_INTERACTION (
252 p_api_version => G_API_VERSION,
253 p_init_msg_list => OKL_API.G_FALSE,
254 p_commit => OKL_API.G_TRUE,
255 P_RESP_APPL_ID => 540,
256 P_RESP_ID => fnd_global.RESP_ID,
257 p_user_id => fnd_global.USER_ID,
258 p_login_id => fnd_global.LOGIN_ID,
259 x_return_status => l_return_status,
260 x_msg_count => x_msg_count,
261 x_msg_data => x_msg_data,
262 P_INTERACTION_ID => lx_interaction_id);
263
264 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
265 RAISE G_EXCEPTION_ERROR;
266 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
267 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
268 END IF;
269
270 IF l_return_status = OKL_API.G_RET_STS_SUCCESS THEN
271
272 OKL_API.set_message(p_app_name => OKL_API.G_APP_NAME,
273 p_msg_name => 'OKL_CR_SUBMIT',
274 p_token1 => 'REQ_NUM',
275 p_token1_value => l_application_number);
276 END IF;
277
278 x_return_status := l_return_status;
279
280 EXCEPTION
281
282 WHEN G_EXCEPTION_ERROR THEN
283 x_return_status := G_RET_STS_ERROR;
284
285 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
286 x_return_status := G_RET_STS_UNEXP_ERROR;
287
288 WHEN OTHERS THEN
289
290 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
291 p_msg_name => G_UNEXPECTED_ERROR,
292 p_token1 => G_SQLCODE_TOKEN,
293 p_token1_value => SQLCODE,
294 p_token2 => G_SQLERRM_TOKEN,
295 p_token2_value => SQLERRM);
296
297 x_return_status := G_RET_STS_UNEXP_ERROR;
298
299 END submit_credit_request;
300
301
302 -------------------------
303 -- compile_credit_request
304 -------------------------
305 PROCEDURE compile_credit_request
306 (p_api_version IN NUMBER
307 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
308 ,x_return_status OUT NOCOPY VARCHAR2
309 ,x_msg_count OUT NOCOPY NUMBER
310 ,x_msg_data OUT NOCOPY VARCHAR2
311 ,p_contract_id IN NUMBER
312 ,x_chr_rec OUT NOCOPY l_chr_rec
313 ) IS
314
315 l_return_status VARCHAR2(1);
316 l_application_number VARCHAR2(30);
317 l_request_status VARCHAR2(30);
318 l_cm_installed BOOLEAN;
319 l_ctrct_financed_amt NUMBER;
320
321 CURSOR c_check_submitted IS
322 SELECT credit_req_number,
323 status
324 FROM okl_credit_requests
325 WHERE quote_id = p_contract_id
326 AND status IN ('SUBMITTED' , 'APPROVED');
327
328 BEGIN
329
330 l_cm_installed := AR_CMGT_CREDIT_REQUEST_API.is_Credit_Management_Installed;
331
332 IF NOT l_cm_installed THEN
333
334 OKL_API.set_message(p_app_name => OKL_API.G_APP_NAME,
335 p_msg_name => 'OKL_CM_NOTINSTALLED');
336
337 RAISE G_EXCEPTION_ERROR;
338
339 END IF;
340
341 OPEN c_check_submitted;
342 FETCH c_check_submitted INTO l_application_number, l_request_status;
343 CLOSE c_check_submitted;
344
345 IF l_application_number IS NOT NULL THEN
346
347 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
348 p_msg_name => 'OKL_CR_SUBMITTED',
349 p_token1 => 'REQ_ID',
350 p_token1_value => l_application_number,
351 p_token2 => 'REQ_STATUS',
352 p_token2_value => l_request_status );
353
354 RAISE G_EXCEPTION_ERROR;
355
356 END IF;
357
358 okl_execute_formula_pub.execute(p_api_version => G_API_VERSION,
359 p_init_msg_list => OKL_API.G_FALSE,
360 x_return_status => l_return_status,
361 x_msg_count => x_msg_count,
362 x_msg_data => x_msg_data,
363 p_formula_name => 'CONTRACT_FINANCED_AMOUNT',
364 p_contract_id => p_contract_id,
365 p_line_id => null,
366 x_value => l_ctrct_financed_amt);
367
368 IF l_return_status = G_RET_STS_ERROR THEN
369 RAISE G_EXCEPTION_ERROR;
370 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
371 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
372 END IF;
373
374 SELECT chr.id,
375 chr.contract_number,
376 chr.cust_acct_id,
377 chr.currency_code,
378 chr.authoring_org_id,
379 cpl.object1_id1,
380 khr.term_duration
381 INTO x_chr_rec.contract_id,
382 x_chr_rec.contract_number,
383 x_chr_rec.cust_acct_id,
384 x_chr_rec.currency,
385 x_chr_rec.org_id,
386 x_chr_rec.party_id,
387 x_chr_rec.term
388 FROM okc_k_headers_b chr,
389 okl_k_headers khr,
390 okc_k_party_roles_b cpl
391 WHERE chr.id = p_contract_id
392 AND chr.id = khr.id
393 AND chr.id = cpl.dnz_chr_id
394 AND cpl.rle_code = 'LESSEE';
395
396 x_chr_rec.requested_amount := l_ctrct_financed_amt;
397
398 x_return_status := l_return_status;
399
400 EXCEPTION
401
402 WHEN G_EXCEPTION_ERROR THEN
403 x_return_status := G_RET_STS_ERROR;
404
405 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
406 x_return_status := G_RET_STS_UNEXP_ERROR;
407
408 WHEN OTHERS THEN
409
410 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
411 p_msg_name => G_UNEXPECTED_ERROR,
412 p_token1 => G_SQLCODE_TOKEN,
413 p_token1_value => SQLCODE,
414 p_token2 => G_SQLERRM_TOKEN,
415 p_token2_value => SQLERRM);
416
417 x_return_status := G_RET_STS_UNEXP_ERROR;
418
419 END compile_credit_request;
420
421 END OKL_CREDIT_MGNT_PVT;