[Home] [Help]
PACKAGE BODY: APPS.ASO_IBY_FINANCING_PVT
Source
1 PACKAGE BODY aso_iby_financing_pvt AS
2 /* $Header: asovibyb.pls 120.1 2005/06/29 12:41:51 appldev ship $ */
3 -- Start of Comments
4 -- Package name : ASO_IBY_FINANCING_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 g_pkg_name CONSTANT VARCHAR2 (30) := 'ASO_IBY_FINANCE_PVT';
11 g_file_name CONSTANT VARCHAR2 (12) := 'asovibyb.pls';
12 g_login_id NUMBER := fnd_global.conc_login_id;
13 g_financing_rejected CONSTANT VARCHAR2 (30) := 'REJECTED';
14 g_financing_canceled CONSTANT VARCHAR2 (30) := 'CANCELED';
15 g_financing_approved CONSTANT VARCHAR2 (30) := 'APPROVED';
16 g_financing_pending CONSTANT VARCHAR2 (30) := 'PENDING';
17
18 PROCEDURE update_status (
19 p_api_version IN NUMBER,
20 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
21 p_commit IN VARCHAR2 := fnd_api.g_false,
22 p_validation_level IN NUMBER := fnd_api.g_miss_num,
23 p_tangible_id IN NUMBER,
24 p_credit_app_id IN NUMBER,
25 p_new_status_category IN VARCHAR2,
26 p_new_status IN VARCHAR2,
27 p_last_update_date IN DATE := fnd_api.g_miss_date,
28 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
29 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
30 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
31 ) IS
32 l_api_version NUMBER := 1.0;
33 l_api_name VARCHAR2 (50) := 'Update_Status';
34 l_related_obj_rec aso_quote_pub.related_obj_rec_type;
35 l_related_obj_id NUMBER;
36 l_control_rec aso_quote_pub.control_rec_type;
37 l_first_time_pending BOOLEAN := FALSE;
38 l_current_status_code VARCHAR2 (30);
39 l_quote_status_id NUMBER;
40 l_quote_header_id NUMBER := p_tangible_id;
41 l_credit_app_id NUMBER;
42 l_qte_source_code VARCHAR2 (240);
43 l_qte_resource_id NUMBER;
44 l_qte_header_rec aso_quote_pub.qte_header_rec_type
45 := aso_quote_pub.g_miss_qte_header_rec;
46 lx_qte_header_rec aso_quote_pub.qte_header_rec_type;
47 lx_qte_line_tbl aso_quote_pub.qte_line_tbl_type;
48 lx_qte_line_dtl_tbl aso_quote_pub.qte_line_dtl_tbl_type;
49 lx_hd_price_attr_tbl aso_quote_pub.price_attributes_tbl_type;
50 lx_hd_payment_tbl aso_quote_pub.payment_tbl_type;
51 lx_hd_shipment_rec aso_quote_pub.shipment_rec_type;
52 lx_hd_shipment_tbl aso_quote_pub.shipment_tbl_type;
53 lx_hd_freight_charge_tbl aso_quote_pub.freight_charge_tbl_type;
54 lx_hd_tax_detail_tbl aso_quote_pub.tax_detail_tbl_type;
55 lx_line_attr_ext_tbl aso_quote_pub.line_attribs_ext_tbl_type;
56 lx_line_rltship_tbl aso_quote_pub.line_rltship_tbl_type;
57 lx_price_adjustment_tbl aso_quote_pub.price_adj_tbl_type;
58 lx_price_adj_attr_tbl aso_quote_pub.price_adj_attr_tbl_type;
59 lx_price_adj_rltship_tbl aso_quote_pub.price_adj_rltship_tbl_type;
60 lx_ln_price_attr_tbl aso_quote_pub.price_attributes_tbl_type;
61 lx_ln_payment_tbl aso_quote_pub.payment_tbl_type;
62 lx_ln_shipment_tbl aso_quote_pub.shipment_tbl_type;
63 lx_ln_freight_charge_tbl aso_quote_pub.freight_charge_tbl_type;
64 lx_ln_tax_detail_tbl aso_quote_pub.tax_detail_tbl_type;
65
66 CURSOR c_financing_id (
67 lc_qte_header_id NUMBER
68 ) IS
69 SELECT object_id, last_update_date, related_object_id
70 FROM aso_quote_related_objects
71 WHERE relationship_type_code = 'THIRDPARTY_FINANCING'
72 AND object_type_code = 'CREDIT_APPLICATION'
73 AND quote_object_id = lc_qte_header_id;
74
75 CURSOR c_quote (
76 lc_quote_id NUMBER
77 ) IS
78 SELECT qh.quote_source_code, qh.last_update_date, qh.resource_id,
79 qh.quote_status_id, qs.status_code
80 FROM aso_quote_headers_all qh, aso_quote_statuses_b qs
81 WHERE qh.quote_header_id = lc_quote_id
82 AND qh.quote_status_id = qs.quote_status_id;
83
84 CURSOR c_qte_status_id (
85 lc_status_code VARCHAR2
86 ) IS
87 SELECT quote_status_id
88 FROM aso_quote_statuses_b
89 WHERE status_code = lc_status_code;
90 BEGIN
91 -- Standard Start of API savepoint
92 SAVEPOINT update_status_pvt;
93
94 -- Standard call to check for call compatibility.
95 IF NOT fnd_api.compatible_api_call (
96 l_api_version,
97 p_api_version,
98 l_api_name,
99 g_pkg_name
100 )
101 THEN
102 RAISE fnd_api.g_exc_unexpected_error;
103 END IF;
104
105 -- Initialize message list if p_init_msg_list is set to TRUE.
106 IF fnd_api.to_boolean (
107 p_init_msg_list
108 )
109 THEN
110 fnd_msg_pub.initialize;
111 END IF;
112
113 -- Initialize API return status to SUCCESS
114 x_return_status := fnd_api.g_ret_sts_success;
115
116 --
117 -- API body
118 --
119
120 -- ******************************************************************
121 -- Validate Environment
122 -- ******************************************************************
123 IF fnd_global.user_id IS NULL
124 THEN
125 IF fnd_msg_pub.check_msg_level (
126 fnd_msg_pub.g_msg_lvl_error
127 )
128 THEN
129 fnd_message.set_name (
130 ' + appShortName +',
131 'UT_CANNOT_GET_PROFILE_VALUE'
132 );
133 fnd_message.set_token (
134 'PROFILE',
135 'USER_ID',
136 FALSE
137 );
138 fnd_msg_pub.ADD;
139 END IF;
140
141 RAISE fnd_api.g_exc_error;
142 END IF;
143
144 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
145 aso_debug_pub.ADD (
146 'Update_Status Begin',
147 1,
148 'Y'
149 );
150 aso_debug_pub.ADD (
151 'Update_Status: P_API_VERSION '|| p_api_version,
152 1,
153 'Y'
154 );
155 aso_debug_pub.ADD (
156 'Update_Status: P_INIT_MSG_LIST '|| p_init_msg_list,
157 1,
158 'Y'
159 );
160 aso_debug_pub.ADD (
161 'Update_Status: P_COMMIT '|| p_commit,
162 1,
163 'Y'
164 );
165 aso_debug_pub.ADD (
166 'Update_Status: p_validation_level '|| p_validation_level,
167 1,
168 'Y'
169 );
170 aso_debug_pub.ADD (
171 'Update_Status: p_tangible_id '|| p_tangible_id,
172 1,
173 'Y'
174 );
175 aso_debug_pub.ADD (
176 'Update_Status: p_credit_app_id '|| p_credit_app_id,
177 1,
178 'Y'
179 );
180 aso_debug_pub.ADD (
181 'Update_Status: p_new_status_category '|| p_new_status_category,
182 1,
183 'Y'
184 );
185 aso_debug_pub.ADD (
186 'Update_Status: p_new_status '|| p_new_status,
187 1,
188 'Y'
189 );
190 aso_debug_pub.ADD (
191 'Update_Status: p_last_update_date '
192 || TO_CHAR (
193 p_last_update_date,
194 'DD-MON-YY HH24:MI:SS'
195 ),
196 1,
197 'Y'
198 );
199 END IF;
200 OPEN c_financing_id (
201 l_quote_header_id
202 );
203 FETCH c_financing_id INTO l_credit_app_id,
204 l_related_obj_rec.last_update_date,
205 l_related_obj_id;
206
207 IF c_financing_id%NOTFOUND
208 THEN
209 CLOSE c_financing_id;
210
211 IF p_new_status_category = g_financing_pending
212 THEN
213
214 -- if it is the first called to change to financing pending, a relationship
215 -- between quote header and credit app is created and quote status is changed.
216 l_first_time_pending := TRUE;
217 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
218 aso_debug_pub.ADD (
219 'Update_Status: The first time that it is called to change quote to financing pending',
220 1,
221 'Y'
222 );
223 END IF;
224 ELSE
225 IF fnd_msg_pub.check_msg_level (
226 fnd_msg_pub.g_msg_lvl_error
227 )
228 THEN
229 fnd_message.set_name (
230 'ASO',
231 'ASO_API_FINANCING_NOT_ATTACHED'
232 );
233 fnd_msg_pub.ADD;
234 END IF;
235
236 RAISE fnd_api.g_exc_error;
237 END IF;
238 ELSE
239 CLOSE c_financing_id;
240 END IF;
241
242 OPEN c_quote (
243 l_quote_header_id
244 );
245 FETCH c_quote INTO l_qte_source_code,
246 l_qte_header_rec.last_update_date,
247 l_qte_resource_id,
248 l_quote_status_id,
249 l_current_status_code;
250 CLOSE c_quote;
251 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
252 aso_debug_pub.ADD (
253 'Update_Status: getting information for quote '|| l_quote_header_id,
254 1,
255 'Y'
256 );
257 aso_debug_pub.ADD (
258 'Update_Status: quote current status '|| l_current_status_code,
259 1,
260 'Y'
261 );
262 aso_debug_pub.ADD (
263 'Update_Status: quote last update date '
264 || TO_CHAR (
265 l_qte_header_rec.last_update_date,
266 'DD-MON-YY HH24:MI:SS'
267 ),
268 1,
269 'Y'
270 );
271 END IF;
272
273 /* if it asks quote status change to financing pending from any other valid statuses,
274 * p_last_update_date must be passed in as the same value in quote header record.
275 */
276 IF p_new_status_category = g_financing_pending
277 AND l_current_status_code <> 'FINANCING PENDING'
278 THEN
279 IF p_last_update_date = fnd_api.g_miss_date
280 OR p_last_update_date IS NULL
281 THEN
282 IF fnd_msg_pub.check_msg_level (
283 fnd_msg_pub.g_msg_lvl_error
284 )
285 THEN
286 fnd_message.set_name (
287 'ASO',
288 'API_INVALID_ID'
289 );
290 fnd_message.set_token (
291 'COLUMN',
292 'LAST_UPDATE_DATE',
293 FALSE
294 );
295 fnd_msg_pub.ADD;
296 END IF;
297
298 RAISE fnd_api.g_exc_error;
299 ELSIF p_last_update_date <> l_qte_header_rec.last_update_date
300 THEN
301 IF fnd_msg_pub.check_msg_level (
302 fnd_msg_pub.g_msg_lvl_error
303 )
304 THEN
305 fnd_message.set_name (
306 'ASO',
307 'ASO_API_RECORD_CHANGED'
308 );
309 fnd_message.set_token (
310 'INFO',
311 'quote',
312 FALSE
313 );
314 fnd_msg_pub.ADD;
315 END IF;
316
317 RAISE fnd_api.g_exc_error;
318 END IF;
319 END IF;
320
321 IF p_new_status_category = g_financing_approved
322 THEN
323 OPEN c_qte_status_id (
324 'FINANCING APPROVED'
325 );
326 FETCH c_qte_status_id INTO l_qte_header_rec.quote_status_id;
327 CLOSE c_qte_status_id;
328 ELSIF p_new_status_category = g_financing_canceled
329 THEN
330 OPEN c_qte_status_id (
331 'FINANCING CANCELED'
332 );
333 FETCH c_qte_status_id INTO l_qte_header_rec.quote_status_id;
334 CLOSE c_qte_status_id;
335 ELSIF p_new_status_category = g_financing_rejected
336 THEN
337 OPEN c_qte_status_id (
338 'FINANCING REJECTED'
339 );
340 FETCH c_qte_status_id INTO l_qte_header_rec.quote_status_id;
341 CLOSE c_qte_status_id;
342 ELSIF p_new_status_category = g_financing_pending
343 THEN
344 -- creating related object if it is the first time, otherwise updating it.
345
346 l_related_obj_rec.quote_object_type_code := 'HEADER';
347 l_related_obj_rec.quote_object_id := l_quote_header_id;
348 l_related_obj_rec.object_type_code := 'CREDIT_APPLICATION';
349 l_related_obj_rec.object_id := p_credit_app_id;
350 l_related_obj_rec.relationship_type_code := 'THIRDPARTY_FINANCING';
351 l_related_obj_rec.reciprocal_flag := 'N';
352
353 IF l_first_time_pending
354 THEN
355 aso_related_obj_pvt.create_related_obj (
356 p_api_version_number => 1.0,
357 p_init_msg_list => p_init_msg_list,
358 p_commit => p_commit,
359 p_validation_level => fnd_api.g_valid_level_none,
360 p_related_obj_rec => l_related_obj_rec,
361 x_related_object_id => l_related_obj_id,
362 x_return_status => x_return_status,
363 x_msg_count => x_msg_count,
364 x_msg_data => x_msg_data
365 );
366 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
367 aso_debug_pub.ADD (
368 'Update_Status: after Create_related_obj return_status: '
369 || x_return_status,
370 1,
371 'Y'
372 );
373 END IF;
374
375 IF x_return_status <> fnd_api.g_ret_sts_success
376 THEN
377 IF fnd_msg_pub.check_msg_level (
378 fnd_msg_pub.g_msg_lvl_error
379 )
380 THEN
381 fnd_message.set_name (
382 'ASO',
383 'ASO_API_ERROR_IN_CREATE_RLTN'
384 );
385 fnd_message.set_token (
386 'COLUMN',
387 l_related_obj_rec.relationship_type_code,
388 FALSE
389 );
390 fnd_msg_pub.ADD;
391 END IF;
392
393 RAISE fnd_api.g_exc_error;
394 END IF;
395 ELSIF l_credit_app_id <> p_credit_app_id
396 THEN
397 l_related_obj_rec.related_object_id := l_related_obj_id;
398 aso_related_obj_pvt.update_related_obj (
399 p_api_version_number => 1.0,
400 p_init_msg_list => p_init_msg_list,
401 p_commit => p_commit,
402 p_validation_level => fnd_api.g_valid_level_none,
403 p_related_obj_rec => l_related_obj_rec,
404 x_return_status => x_return_status,
405 x_msg_count => x_msg_count,
406 x_msg_data => x_msg_data
407 );
408 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
409 aso_debug_pub.ADD (
410 'Update_Status: after Update_related_obj return_status: '
411 || x_return_status,
412 1,
413 'Y'
414 );
415 END IF;
416
417 IF x_return_status <> fnd_api.g_ret_sts_success
418 THEN
419 IF fnd_msg_pub.check_msg_level (
420 fnd_msg_pub.g_msg_lvl_error
421 )
422 THEN
423 fnd_message.set_name (
424 'ASO',
425 'ASO_API_ERROR_IN_UPDATE_RLTN'
426 );
427 fnd_message.set_token (
428 'COLUMN',
429 l_related_obj_rec.relationship_type_code,
430 FALSE
431 );
432 fnd_msg_pub.ADD;
433 END IF;
434
435 RAISE fnd_api.g_exc_error;
436 END IF;
437 END IF;
438
439 OPEN c_qte_status_id (
440 'FINANCING PENDING'
441 );
442 FETCH c_qte_status_id INTO l_qte_header_rec.quote_status_id;
443 CLOSE c_qte_status_id;
444 ELSE
445 IF fnd_msg_pub.check_msg_level (
446 fnd_msg_pub.g_msg_lvl_error
447 )
448 THEN
449 fnd_message.set_name (
450 'ASO',
451 'API_INVALID_ID'
452 );
453 fnd_message.set_token (
454 'COLUMN',
455 'NEW STATUS CATEGORY',
456 FALSE
457 );
458 fnd_msg_pub.ADD;
459 END IF;
460
461 RAISE fnd_api.g_exc_error;
462 END IF;
463
464 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
465 aso_debug_pub.ADD (
466 'Update_Status: Updating quote status to '
467 || l_qte_header_rec.quote_status_id,
468 1,
469 'Y'
470 );
471 END IF;
472
473
474 -- we should do nothing if the passing status is the same as current status.
475 -- If we pass in same quote status, the update_quote will not throw exception.
476 -- However, it may pop up the quote version.
477
478 IF l_qte_header_rec.quote_status_id <> l_quote_status_id
479 THEN
480 l_qte_header_rec.quote_header_id := l_quote_header_id;
481 l_control_rec := aso_quote_pub.g_miss_control_rec;
482 l_control_rec.auto_version_flag := fnd_api.g_true;
483 aso_quote_pub.update_quote (
484 p_api_version_number => 1.0,
485 p_init_msg_list => fnd_api.g_false,
486 p_commit => fnd_api.g_false,
487 p_qte_header_rec => l_qte_header_rec,
488 x_qte_header_rec => lx_qte_header_rec,
489 x_qte_line_tbl => lx_qte_line_tbl,
490 x_qte_line_dtl_tbl => lx_qte_line_dtl_tbl,
491 x_hd_price_attributes_tbl => lx_hd_price_attr_tbl,
492 x_hd_payment_tbl => lx_hd_payment_tbl,
493 x_hd_shipment_tbl => lx_hd_shipment_tbl,
494 x_hd_freight_charge_tbl => lx_hd_freight_charge_tbl,
495 x_hd_tax_detail_tbl => lx_hd_tax_detail_tbl,
496 x_line_attr_ext_tbl => lx_line_attr_ext_tbl,
497 x_line_rltship_tbl => lx_line_rltship_tbl,
498 x_price_adjustment_tbl => lx_price_adjustment_tbl,
499 x_price_adj_attr_tbl => lx_price_adj_attr_tbl,
500 x_price_adj_rltship_tbl => lx_price_adj_rltship_tbl,
501 x_ln_price_attributes_tbl => lx_ln_price_attr_tbl,
502 x_ln_payment_tbl => lx_ln_payment_tbl,
503 x_ln_shipment_tbl => lx_ln_shipment_tbl,
504 x_ln_freight_charge_tbl => lx_ln_freight_charge_tbl,
505 x_ln_tax_detail_tbl => lx_ln_tax_detail_tbl,
506 x_return_status => x_return_status,
507 x_msg_count => x_msg_count,
508 x_msg_data => x_msg_data
509 );
510 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
511 aso_debug_pub.ADD (
512 'Update_Status: after Update_Quote return_status: '|| x_return_status,
513 1,
514 'Y'
515 );
516 END IF;
517
518 IF x_return_status <> fnd_api.g_ret_sts_success
519 THEN
520 IF fnd_msg_pub.check_msg_level (
521 fnd_msg_pub.g_msg_lvl_error
522 )
523 THEN
524 fnd_message.set_name (
525 'ASO',
526 'ASO_API_ERROR_IN_UPDATE_QUOTE'
527 );
528 fnd_msg_pub.ADD;
529 END IF;
530
531 RAISE fnd_api.g_exc_error;
532 END IF;
533 END IF;
534
535 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
536 aso_debug_pub.ADD (
537 'Update_Status End',
538 1,
539 'Y'
540 );
541 END IF;
542
543 --
544 -- End of API body.
545 --
546
547 -- Standard check for p_commit
548 IF fnd_api.to_boolean (
549 p_commit
550 )
551 THEN
552 COMMIT WORK;
553 END IF;
554
555 -- Debug Message
556 aso_utility_pvt.debug_message (
557 fnd_msg_pub.g_msg_lvl_debug_low,
558 'Public API: ' || l_api_name || 'end'
559 );
560 -- Standard call to get message count and if count is 1, get message info.
561 fnd_msg_pub.count_and_get (
562 p_count => x_msg_count,
563 p_data => x_msg_data
564 );
565 EXCEPTION
566 WHEN fnd_api.g_exc_error
567 THEN
568 aso_utility_pvt.handle_exceptions (
569 p_api_name => l_api_name,
570 p_pkg_name => g_pkg_name,
571 p_exception_level => fnd_msg_pub.g_msg_lvl_error,
572 p_package_type => aso_utility_pvt.g_pvt,
573 x_msg_count => x_msg_count,
574 x_msg_data => x_msg_data,
575 x_return_status => x_return_status
576 );
577 WHEN fnd_api.g_exc_unexpected_error
578 THEN
579 aso_utility_pvt.handle_exceptions (
580 p_api_name => l_api_name,
581 p_pkg_name => g_pkg_name,
582 p_exception_level => fnd_msg_pub.g_msg_lvl_unexp_error,
583 p_package_type => aso_utility_pvt.g_pvt,
584 x_msg_count => x_msg_count,
585 x_msg_data => x_msg_data,
586 x_return_status => x_return_status
587 );
588 WHEN OTHERS
589 THEN
590 aso_utility_pvt.handle_exceptions (
591 p_api_name => l_api_name,
592 p_pkg_name => g_pkg_name,
593 p_exception_level => aso_utility_pvt.g_exc_others,
594 p_package_type => aso_utility_pvt.g_pvt,
595 x_msg_count => x_msg_count,
596 x_msg_data => x_msg_data,
597 x_return_status => x_return_status
598 );
599 END update_status;
600 END aso_iby_financing_pvt;