[Home] [Help]
PACKAGE BODY: APPS.OKL_LSQ_PVT
Source
1 PACKAGE BODY OKL_LSQ_PVT AS
2 /* $Header: OKLSLSQB.pls 120.3.12010000.3 2008/11/27 04:53:15 kkorrapo ship $ */
3
4 -------------------------
5 -- PROCEDURE add_language
6 -------------------------
7 PROCEDURE add_language IS
8
9 BEGIN
10
11 DELETE FROM OKL_LEASE_QUOTES_TL T
12 WHERE NOT EXISTS (SELECT NULL FROM OKL_LEASE_QUOTES_B B WHERE B.ID =T.ID);
13
14 UPDATE OKL_LEASE_QUOTES_TL T
15 SET (SHORT_DESCRIPTION,
16 DESCRIPTION,
17 COMMENTS) =
18 (SELECT
19 B.SHORT_DESCRIPTION,
20 B.DESCRIPTION,
21 B.COMMENTS
22 FROM
23 OKL_LEASE_QUOTES_TL B
24 WHERE
25 B.ID = T.ID
30 FROM
26 AND B.LANGUAGE = T.SOURCE_LANG)
27 WHERE (T.ID, T.LANGUAGE) IN (SELECT
28 SUBT.ID,
29 SUBT.LANGUAGE
31 OKL_LEASE_QUOTES_TL SUBB,
32 OKL_LEASE_QUOTES_TL SUBT
33 WHERE
34 SUBB.ID = SUBT.ID
35 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
36 AND (SUBB.SHORT_DESCRIPTION <> SUBT.SHORT_DESCRIPTION
37 OR (SUBB.DESCRIPTION <> SUBT.DESCRIPTION)
38 OR (SUBB.COMMENTS <> SUBT.COMMENTS)
39 OR (SUBB.SHORT_DESCRIPTION IS NULL AND SUBT.SHORT_DESCRIPTION IS NOT NULL)
40 OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
41 OR (SUBB.COMMENTS IS NULL AND SUBT.COMMENTS IS NOT NULL)
42 )
43 );
44
45 INSERT INTO OKL_LEASE_QUOTES_TL (
46 ID,
47 LANGUAGE,
48 SOURCE_LANG,
49 SFWT_FLAG,
50 CREATED_BY,
51 CREATION_DATE,
52 LAST_UPDATED_BY,
53 LAST_UPDATE_DATE,
54 LAST_UPDATE_LOGIN,
55 SHORT_DESCRIPTION,
56 DESCRIPTION,
57 COMMENTS)
58 SELECT
59 B.ID,
60 L.LANGUAGE_CODE,
61 B.SOURCE_LANG,
62 B.SFWT_FLAG,
63 B.CREATED_BY,
64 B.CREATION_DATE,
65 B.LAST_UPDATED_BY,
66 B.LAST_UPDATE_DATE,
67 B.LAST_UPDATE_LOGIN,
68 B.SHORT_DESCRIPTION,
69 B.DESCRIPTION,
70 B.COMMENTS
71 FROM OKL_LEASE_QUOTES_TL B, FND_LANGUAGES L
72 WHERE L.INSTALLED_FLAG IN ('I', 'B')
73 AND B.LANGUAGE = USERENV('LANG')
74 AND NOT EXISTS (
75 SELECT NULL
76 FROM OKL_LEASE_QUOTES_TL T
77 WHERE T.ID = B.ID
78 AND T.LANGUAGE = L.LANGUAGE_CODE
79 );
80
81 END add_language;
82
83
84 -----------------------------
85 -- FUNCTION null_out_defaults
86 -----------------------------
87 FUNCTION null_out_defaults (p_lsqv_rec IN lsqv_rec_type) RETURN lsqv_rec_type IS
88
89 l_lsqv_rec lsqv_rec_type;
90
91 BEGIN
92
93 l_lsqv_rec := p_lsqv_rec;
94
95 -- Not applicable to ID and OBJECT_VERSION_NUMBER
96
97 IF l_lsqv_rec.attribute_category = FND_API.G_MISS_CHAR THEN
98 l_lsqv_rec.attribute_category := NULL;
99 END IF;
100 IF l_lsqv_rec.attribute1 = FND_API.G_MISS_CHAR THEN
101 l_lsqv_rec.attribute1 := NULL;
102 END IF;
103 IF l_lsqv_rec.attribute2 = FND_API.G_MISS_CHAR THEN
104 l_lsqv_rec.attribute2 := NULL;
105 END IF;
106 IF l_lsqv_rec.attribute3 = FND_API.G_MISS_CHAR THEN
107 l_lsqv_rec.attribute3 := NULL;
108 END IF;
109 IF l_lsqv_rec.attribute4 = FND_API.G_MISS_CHAR THEN
110 l_lsqv_rec.attribute4 := NULL;
111 END IF;
112 IF l_lsqv_rec.attribute5 = FND_API.G_MISS_CHAR THEN
113 l_lsqv_rec.attribute5 := NULL;
114 END IF;
115 IF l_lsqv_rec.attribute6 = FND_API.G_MISS_CHAR THEN
116 l_lsqv_rec.attribute6 := NULL;
117 END IF;
118 IF l_lsqv_rec.attribute7 = FND_API.G_MISS_CHAR THEN
119 l_lsqv_rec.attribute7 := NULL;
120 END IF;
121 IF l_lsqv_rec.attribute8 = FND_API.G_MISS_CHAR THEN
122 l_lsqv_rec.attribute8 := NULL;
123 END IF;
124 IF l_lsqv_rec.attribute9 = FND_API.G_MISS_CHAR THEN
125 l_lsqv_rec.attribute9 := NULL;
126 END IF;
127 IF l_lsqv_rec.attribute10 = FND_API.G_MISS_CHAR THEN
128 l_lsqv_rec.attribute10 := NULL;
129 END IF;
130 IF l_lsqv_rec.attribute11 = FND_API.G_MISS_CHAR THEN
131 l_lsqv_rec.attribute11 := NULL;
132 END IF;
133 IF l_lsqv_rec.attribute12 = FND_API.G_MISS_CHAR THEN
134 l_lsqv_rec.attribute12 := NULL;
135 END IF;
136 IF l_lsqv_rec.attribute13 = FND_API.G_MISS_CHAR THEN
137 l_lsqv_rec.attribute13 := NULL;
138 END IF;
139 IF l_lsqv_rec.attribute14 = FND_API.G_MISS_CHAR THEN
140 l_lsqv_rec.attribute14 := NULL;
141 END IF;
142 IF l_lsqv_rec.attribute15 = FND_API.G_MISS_CHAR THEN
143 l_lsqv_rec.attribute15 := NULL;
144 END IF;
145 IF l_lsqv_rec.reference_number = FND_API.G_MISS_CHAR THEN
146 l_lsqv_rec.reference_number := NULL;
147 END IF;
148 IF l_lsqv_rec.status = FND_API.G_MISS_CHAR THEN
149 l_lsqv_rec.status := NULL;
150 END IF;
151 IF l_lsqv_rec.parent_object_code = FND_API.G_MISS_CHAR THEN
152 l_lsqv_rec.parent_object_code := NULL;
153 END IF;
154 IF l_lsqv_rec.parent_object_id = FND_API.G_MISS_NUM THEN
155 l_lsqv_rec.parent_object_id := NULL;
156 END IF;
157 IF l_lsqv_rec.valid_from = FND_API.G_MISS_DATE THEN
158 l_lsqv_rec.valid_from := NULL;
159 END IF;
160 IF l_lsqv_rec.valid_to = FND_API.G_MISS_DATE THEN
161 l_lsqv_rec.valid_to := NULL;
162 END IF;
163 IF l_lsqv_rec.customer_bookclass = FND_API.G_MISS_CHAR THEN
164 l_lsqv_rec.customer_bookclass := NULL;
165 END IF;
166 IF l_lsqv_rec.customer_taxowner = FND_API.G_MISS_CHAR THEN
167 l_lsqv_rec.customer_taxowner := NULL;
168 END IF;
169 IF l_lsqv_rec.expected_start_date = FND_API.G_MISS_DATE THEN
173 l_lsqv_rec.expected_funding_date := NULL;
170 l_lsqv_rec.expected_start_date := NULL;
171 END IF;
172 IF l_lsqv_rec.expected_funding_date = FND_API.G_MISS_DATE THEN
174 END IF;
175 IF l_lsqv_rec.expected_delivery_date = FND_API.G_MISS_DATE THEN
176 l_lsqv_rec.expected_delivery_date := NULL;
177 END IF;
178 IF l_lsqv_rec.pricing_method = FND_API.G_MISS_CHAR THEN
179 l_lsqv_rec.pricing_method := NULL;
180 END IF;
181 IF l_lsqv_rec.term = FND_API.G_MISS_NUM THEN
182 l_lsqv_rec.term := NULL;
183 END IF;
184 IF l_lsqv_rec.product_id = FND_API.G_MISS_NUM THEN
185 l_lsqv_rec.product_id := NULL;
186 END IF;
187 IF l_lsqv_rec.end_of_term_option_id = FND_API.G_MISS_NUM THEN
188 l_lsqv_rec.end_of_term_option_id := NULL;
189 END IF;
190 IF l_lsqv_rec.structured_pricing = FND_API.G_MISS_CHAR THEN
191 l_lsqv_rec.structured_pricing := NULL;
192 END IF;
193 IF l_lsqv_rec.line_level_pricing = FND_API.G_MISS_CHAR THEN
194 l_lsqv_rec.line_level_pricing := NULL;
195 END IF;
196 IF l_lsqv_rec.rate_template_id = FND_API.G_MISS_NUM THEN
197 l_lsqv_rec.rate_template_id := NULL;
198 END IF;
199 IF l_lsqv_rec.rate_card_id = FND_API.G_MISS_NUM THEN
200 l_lsqv_rec.rate_card_id := NULL;
201 END IF;
202 IF l_lsqv_rec.lease_rate_factor = FND_API.G_MISS_NUM THEN
203 l_lsqv_rec.lease_rate_factor := NULL;
204 END IF;
205 IF l_lsqv_rec.target_rate_type = FND_API.G_MISS_CHAR THEN
206 l_lsqv_rec.target_rate_type := NULL;
207 END IF;
208 IF l_lsqv_rec.target_rate = FND_API.G_MISS_NUM THEN
209 l_lsqv_rec.target_rate := NULL;
210 END IF;
211 IF l_lsqv_rec.target_amount = FND_API.G_MISS_NUM THEN
212 l_lsqv_rec.target_amount := NULL;
213 END IF;
214 IF l_lsqv_rec.target_frequency = FND_API.G_MISS_CHAR THEN
215 l_lsqv_rec.target_frequency := NULL;
216 END IF;
217 IF l_lsqv_rec.target_arrears_yn = FND_API.G_MISS_CHAR THEN
218 l_lsqv_rec.target_arrears_yn := NULL;
219 END IF;
220 IF l_lsqv_rec.target_periods = FND_API.G_MISS_NUM THEN
221 l_lsqv_rec.target_periods := NULL;
222 END IF;
223 IF l_lsqv_rec.iir = FND_API.G_MISS_NUM THEN
224 l_lsqv_rec.iir := NULL;
225 END IF;
226 IF l_lsqv_rec.booking_yield = FND_API.G_MISS_NUM THEN
227 l_lsqv_rec.booking_yield := NULL;
228 END IF;
229 IF l_lsqv_rec.pirr = FND_API.G_MISS_NUM THEN
230 l_lsqv_rec.pirr := NULL;
231 END IF;
232 IF l_lsqv_rec.airr = FND_API.G_MISS_NUM THEN
233 l_lsqv_rec.airr := NULL;
234 END IF;
235 IF l_lsqv_rec.sub_iir = FND_API.G_MISS_NUM THEN
236 l_lsqv_rec.sub_iir := NULL;
237 END IF;
238 IF l_lsqv_rec.sub_booking_yield = FND_API.G_MISS_NUM THEN
239 l_lsqv_rec.sub_booking_yield := NULL;
240 END IF;
241 IF l_lsqv_rec.sub_pirr = FND_API.G_MISS_NUM THEN
242 l_lsqv_rec.sub_pirr := NULL;
243 END IF;
244 IF l_lsqv_rec.sub_airr = FND_API.G_MISS_NUM THEN
245 l_lsqv_rec.sub_airr := NULL;
246 END IF;
247 IF l_lsqv_rec.usage_category = FND_API.G_MISS_CHAR THEN
248 l_lsqv_rec.usage_category := NULL;
249 END IF;
250 IF l_lsqv_rec.usage_industry_class = FND_API.G_MISS_CHAR THEN
251 l_lsqv_rec.usage_industry_class := NULL;
252 END IF;
253 IF l_lsqv_rec.usage_industry_code = FND_API.G_MISS_CHAR THEN
254 l_lsqv_rec.usage_industry_code := NULL;
255 END IF;
256 IF l_lsqv_rec.usage_amount = FND_API.G_MISS_NUM THEN
257 l_lsqv_rec.usage_amount := NULL;
258 END IF;
259 IF l_lsqv_rec.usage_location_id = FND_API.G_MISS_NUM THEN
260 l_lsqv_rec.usage_location_id := NULL;
261 END IF;
262 IF l_lsqv_rec.property_tax_applicable = FND_API.G_MISS_CHAR THEN
263 l_lsqv_rec.property_tax_applicable := NULL;
264 END IF;
265 IF l_lsqv_rec.property_tax_billing_type = FND_API.G_MISS_CHAR THEN
266 l_lsqv_rec.property_tax_billing_type := NULL;
267 END IF;
268 IF l_lsqv_rec.upfront_tax_treatment = FND_API.G_MISS_CHAR THEN
269 l_lsqv_rec.upfront_tax_treatment := NULL;
270 END IF;
271 IF l_lsqv_rec.upfront_tax_stream_type = FND_API.G_MISS_NUM THEN
272 l_lsqv_rec.upfront_tax_stream_type := NULL;
273 END IF;
274 IF l_lsqv_rec.transfer_of_title = FND_API.G_MISS_CHAR THEN
275 l_lsqv_rec.transfer_of_title := NULL;
276 END IF;
277 IF l_lsqv_rec.age_of_equipment = FND_API.G_MISS_NUM THEN
278 l_lsqv_rec.age_of_equipment := NULL;
279 END IF;
280 IF l_lsqv_rec.purchase_of_lease = FND_API.G_MISS_CHAR THEN
281 l_lsqv_rec.purchase_of_lease := NULL;
282 END IF;
283 IF l_lsqv_rec.sale_and_lease_back = FND_API.G_MISS_CHAR THEN
284 l_lsqv_rec.sale_and_lease_back := NULL;
285 END IF;
286 IF l_lsqv_rec.interest_disclosed = FND_API.G_MISS_CHAR THEN
287 l_lsqv_rec.interest_disclosed := NULL;
288 END IF;
289 IF l_lsqv_rec.primary_quote = FND_API.G_MISS_CHAR THEN
290 l_lsqv_rec.primary_quote := NULL;
291 END IF;
292 --Bug # 5647107
293 IF l_lsqv_rec.legal_entity_id = FND_API.G_MISS_NUM THEN
294 l_lsqv_rec.legal_entity_id := NULL;
295 END IF;
296 --Bug # 5647107
297 -- Bug 5908845. eBTax Enhancement Project
298 IF l_lsqv_rec.line_intended_use = FND_API.G_MISS_CHAR THEN
299 l_lsqv_rec.line_intended_use := NULL;
300 END IF;
301 -- End Bug 5908845. eBTax Enhancement Project
302 IF l_lsqv_rec.short_description = FND_API.G_MISS_CHAR THEN
303 l_lsqv_rec.short_description := NULL;
304 END IF;
305 IF l_lsqv_rec.description = FND_API.G_MISS_CHAR THEN
309 l_lsqv_rec.comments := NULL;
306 l_lsqv_rec.description := NULL;
307 END IF;
308 IF l_lsqv_rec.comments = FND_API.G_MISS_CHAR THEN
310 END IF;
311
312 RETURN l_lsqv_rec;
313
314 END null_out_defaults;
315
316
317 -------------------
318 -- FUNCTION get_rec
319 -------------------
320 FUNCTION get_rec (p_id IN NUMBER
321 ,x_return_status OUT NOCOPY VARCHAR2) RETURN lsqv_rec_type IS
322
323 l_lsqv_rec lsqv_rec_type;
324 l_prog_name VARCHAR2(61);
325
326 BEGIN
327
328 l_prog_name := G_PKG_NAME||'.get_rec';
329
330 SELECT
331 id
332 ,object_version_number
333 ,attribute_category
334 ,attribute1
335 ,attribute2
336 ,attribute3
337 ,attribute4
338 ,attribute5
339 ,attribute6
340 ,attribute7
341 ,attribute8
342 ,attribute9
343 ,attribute10
344 ,attribute11
345 ,attribute12
346 ,attribute13
347 ,attribute14
348 ,attribute15
349 ,reference_number
350 ,status
351 ,parent_object_code
352 ,parent_object_id
353 ,valid_from
354 ,valid_to
355 ,customer_bookclass
356 ,customer_taxowner
357 ,expected_start_date
358 ,expected_funding_date
359 ,expected_delivery_date
360 ,pricing_method
361 ,term
362 ,product_id
363 ,end_of_term_option_id
364 ,structured_pricing
365 ,line_level_pricing
366 ,rate_template_id
367 ,rate_card_id
368 ,lease_rate_factor
369 ,target_rate_type
370 ,target_rate
371 ,target_amount
372 ,target_frequency
373 ,target_arrears_yn
374 ,target_periods
375 ,iir
376 ,booking_yield
377 ,pirr
378 ,airr
379 ,sub_iir
380 ,sub_booking_yield
381 ,sub_pirr
382 ,sub_airr
383 ,usage_category
384 ,usage_industry_class
385 ,usage_industry_code
386 ,usage_amount
387 ,usage_location_id
388 ,property_tax_applicable
389 ,property_tax_billing_type
390 ,upfront_tax_treatment
391 ,upfront_tax_stream_type
392 ,transfer_of_title
393 ,age_of_equipment
394 ,purchase_of_lease
395 ,sale_and_lease_back
396 ,interest_disclosed
397 ,primary_quote
398 ,legal_entity_id
399 -- Bug 5908845. eBTax Enhancement Project
400 ,line_intended_use
401 -- End Bug 5908845. eBTax Enhancement Project
402 ,short_description
403 ,description
404 ,comments
405 INTO
406 l_lsqv_rec.id
407 ,l_lsqv_rec.object_version_number
408 ,l_lsqv_rec.attribute_category
409 ,l_lsqv_rec.attribute1
410 ,l_lsqv_rec.attribute2
411 ,l_lsqv_rec.attribute3
412 ,l_lsqv_rec.attribute4
413 ,l_lsqv_rec.attribute5
414 ,l_lsqv_rec.attribute6
415 ,l_lsqv_rec.attribute7
416 ,l_lsqv_rec.attribute8
417 ,l_lsqv_rec.attribute9
418 ,l_lsqv_rec.attribute10
419 ,l_lsqv_rec.attribute11
420 ,l_lsqv_rec.attribute12
421 ,l_lsqv_rec.attribute13
422 ,l_lsqv_rec.attribute14
423 ,l_lsqv_rec.attribute15
424 ,l_lsqv_rec.reference_number
425 ,l_lsqv_rec.status
426 ,l_lsqv_rec.parent_object_code
427 ,l_lsqv_rec.parent_object_id
428 ,l_lsqv_rec.valid_from
429 ,l_lsqv_rec.valid_to
430 ,l_lsqv_rec.customer_bookclass
431 ,l_lsqv_rec.customer_taxowner
432 ,l_lsqv_rec.expected_start_date
433 ,l_lsqv_rec.expected_funding_date
434 ,l_lsqv_rec.expected_delivery_date
435 ,l_lsqv_rec.pricing_method
436 ,l_lsqv_rec.term
437 ,l_lsqv_rec.product_id
438 ,l_lsqv_rec.end_of_term_option_id
439 ,l_lsqv_rec.structured_pricing
440 ,l_lsqv_rec.line_level_pricing
441 ,l_lsqv_rec.rate_template_id
442 ,l_lsqv_rec.rate_card_id
443 ,l_lsqv_rec.lease_rate_factor
444 ,l_lsqv_rec.target_rate_type
445 ,l_lsqv_rec.target_rate
446 ,l_lsqv_rec.target_amount
447 ,l_lsqv_rec.target_frequency
448 ,l_lsqv_rec.target_arrears_yn
449 ,l_lsqv_rec.target_periods
450 ,l_lsqv_rec.iir
451 ,l_lsqv_rec.booking_yield
452 ,l_lsqv_rec.pirr
453 ,l_lsqv_rec.airr
454 ,l_lsqv_rec.sub_iir
455 ,l_lsqv_rec.sub_booking_yield
456 ,l_lsqv_rec.sub_pirr
457 ,l_lsqv_rec.sub_airr
458 ,l_lsqv_rec.usage_category
459 ,l_lsqv_rec.usage_industry_class
460 ,l_lsqv_rec.usage_industry_code
461 ,l_lsqv_rec.usage_amount
462 ,l_lsqv_rec.usage_location_id
463 ,l_lsqv_rec.property_tax_applicable
464 ,l_lsqv_rec.property_tax_billing_type
465 ,l_lsqv_rec.upfront_tax_treatment
466 ,l_lsqv_rec.upfront_tax_stream_type
467 ,l_lsqv_rec.transfer_of_title
468 ,l_lsqv_rec.age_of_equipment
469 ,l_lsqv_rec.purchase_of_lease
470 ,l_lsqv_rec.sale_and_lease_back
471 ,l_lsqv_rec.interest_disclosed
472 ,l_lsqv_rec.primary_quote
473 ,l_lsqv_rec.legal_entity_id
474 -- Bug 5908845. eBTax Enhancement Project
475 ,l_lsqv_rec.line_intended_use
479 ,l_lsqv_rec.comments
476 -- End Bug 5908845. eBTax Enhancement Project
477 ,l_lsqv_rec.short_description
478 ,l_lsqv_rec.description
480 FROM okl_lease_quotes_v
481 WHERE id = p_id;
482
483 x_return_status := G_RET_STS_SUCCESS;
484 RETURN l_lsqv_rec;
485
486 EXCEPTION
487
488 WHEN OTHERS THEN
489
490 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
491 p_msg_name => G_DB_ERROR,
492 p_token1 => G_PROG_NAME_TOKEN,
493 p_token1_value => l_prog_name,
494 p_token2 => G_SQLCODE_TOKEN,
495 p_token2_value => sqlcode,
496 p_token3 => G_SQLERRM_TOKEN,
497 p_token3_value => sqlerrm);
498
499 x_return_status := G_RET_STS_UNEXP_ERROR;
500
501 END get_rec;
502
503
504 ------------------------
505 -- PROCEDURE validate_id
506 ------------------------
507 PROCEDURE validate_id (x_return_status OUT NOCOPY VARCHAR2, p_id IN NUMBER) IS
508 BEGIN
509 IF p_id IS NULL THEN
510 OKL_API.set_message(p_app_name => G_APP_NAME,
511 p_msg_name => G_COL_ERROR,
512 p_token1 => G_COL_NAME_TOKEN,
513 p_token1_value => 'id',
514 p_token2 => G_PKG_NAME_TOKEN,
515 p_token2_value => G_PKG_NAME);
516 RAISE OKL_API.G_EXCEPTION_ERROR;
517 END IF;
518 x_return_status := G_RET_STS_SUCCESS;
519 END validate_id;
520
521
522 -------------------------------------------
523 -- PROCEDURE validate_object_version_number
524 -------------------------------------------
525 PROCEDURE validate_object_version_number (x_return_status OUT NOCOPY VARCHAR2, p_object_version_number IN NUMBER) IS
526 BEGIN
527 IF p_object_version_number IS NULL THEN
528 OKL_API.set_message(p_app_name => G_APP_NAME,
529 p_msg_name => G_COL_ERROR,
530 p_token1 => G_COL_NAME_TOKEN,
531 p_token1_value => 'object_version_number',
532 p_token2 => G_PKG_NAME_TOKEN,
533 p_token2_value => G_PKG_NAME);
534 RAISE OKL_API.G_EXCEPTION_ERROR;
535 END IF;
536 x_return_status := G_RET_STS_SUCCESS;
537 END validate_object_version_number;
538
539
540 --------------------------------------
541 -- PROCEDURE validate_reference_number
542 --------------------------------------
543 PROCEDURE validate_reference_number (x_return_status OUT NOCOPY VARCHAR2, p_reference_number IN VARCHAR2) IS
544 BEGIN
545 IF p_reference_number IS NULL THEN
546 OKL_API.set_message(p_app_name => G_APP_NAME,
547 p_msg_name => G_COL_ERROR,
548 p_token1 => G_COL_NAME_TOKEN,
549 p_token1_value => 'reference_number',
550 p_token2 => G_PKG_NAME_TOKEN,
551 p_token2_value => G_PKG_NAME);
552 RAISE OKL_API.G_EXCEPTION_ERROR;
553 END IF;
554
555 --Bug 7022258-Added by kkorrapo
556 IF (okl_util.validate_seq_num('OKL_LSQ_REF_SEQ','OKL_LEASE_QUOTES_B','REFERENCE_NUMBER',p_reference_number) = 'N') THEN
557 RAISE okl_api.g_exception_error;
558 END IF;
559 --Bug 7022258--Addition end
560
561 x_return_status := G_RET_STS_SUCCESS;
562 END validate_reference_number;
563
564
565 -----------------------------------------
566 -- PROCEDURE validate_status
567 -----------------------------------------
568 PROCEDURE validate_status (x_return_status OUT NOCOPY VARCHAR2, p_status IN VARCHAR2) IS
569 BEGIN
570 IF p_status IS NULL THEN
571 OKL_API.set_message(p_app_name => G_APP_NAME,
572 p_msg_name => G_COL_ERROR,
573 p_token1 => G_COL_NAME_TOKEN,
574 p_token1_value => 'status',
575 p_token2 => G_PKG_NAME_TOKEN,
576 p_token2_value => G_PKG_NAME);
577 RAISE OKL_API.G_EXCEPTION_ERROR;
578 END IF;
579 x_return_status := G_RET_STS_SUCCESS;
580 END validate_status;
581
582
583 -----------------------------------------
584 -- PROCEDURE validate_parent_object_code
585 -----------------------------------------
586 PROCEDURE validate_parent_object_code (x_return_status OUT NOCOPY VARCHAR2, p_parent_object_code IN VARCHAR2) IS
587 BEGIN
588 IF p_parent_object_code IS NULL THEN
589 OKL_API.set_message(p_app_name => G_APP_NAME,
590 p_msg_name => G_COL_ERROR,
591 p_token1 => G_COL_NAME_TOKEN,
592 p_token1_value => 'parent_object_code',
593 p_token2 => G_PKG_NAME_TOKEN,
594 p_token2_value => G_PKG_NAME);
595 RAISE OKL_API.G_EXCEPTION_ERROR;
596 END IF;
597 x_return_status := G_RET_STS_SUCCESS;
598 END validate_parent_object_code;
599
600
601 -----------------------------------------
602 -- PROCEDURE validate_parent_object_id
603 -----------------------------------------
604 PROCEDURE validate_parent_object_id (x_return_status OUT NOCOPY VARCHAR2, p_parent_object_id IN NUMBER) IS
605 BEGIN
606 IF p_parent_object_id IS NULL THEN
607 OKL_API.set_message(p_app_name => G_APP_NAME,
611 p_token2 => G_PKG_NAME_TOKEN,
608 p_msg_name => G_COL_ERROR,
609 p_token1 => G_COL_NAME_TOKEN,
610 p_token1_value => 'parent_object_id',
612 p_token2_value => G_PKG_NAME);
613 RAISE OKL_API.G_EXCEPTION_ERROR;
614 END IF;
615 x_return_status := G_RET_STS_SUCCESS;
616 END validate_parent_object_id;
617
618
619 -----------------------------------------
620 -- PROCEDURE validate_expected_start_date
621 -----------------------------------------
622 PROCEDURE validate_expected_start_date (x_return_status OUT NOCOPY VARCHAR2, p_expected_start_date IN DATE) IS
623 BEGIN
624 IF p_expected_start_date IS NULL THEN
625 OKL_API.set_message(p_app_name => G_APP_NAME,
626 p_msg_name => G_COL_ERROR,
627 p_token1 => G_COL_NAME_TOKEN,
628 p_token1_value => 'expected_start_date',
629 p_token2 => G_PKG_NAME_TOKEN,
630 p_token2_value => G_PKG_NAME);
631 RAISE OKL_API.G_EXCEPTION_ERROR;
632 END IF;
633 x_return_status := G_RET_STS_SUCCESS;
634 END validate_expected_start_date;
635
636
637 -----------------------------------------
638 -- PROCEDURE validate_pricing_method
639 -----------------------------------------
640 PROCEDURE validate_pricing_method (x_return_status OUT NOCOPY VARCHAR2, p_pricing_method IN VARCHAR2) IS
641 BEGIN
642 IF p_pricing_method IS NULL THEN
643 OKL_API.set_message(p_app_name => G_APP_NAME,
644 p_msg_name => G_COL_ERROR,
645 p_token1 => G_COL_NAME_TOKEN,
646 p_token1_value => 'pricing_method',
647 p_token2 => G_PKG_NAME_TOKEN,
648 p_token2_value => G_PKG_NAME);
649 RAISE OKL_API.G_EXCEPTION_ERROR;
650 END IF;
651 x_return_status := G_RET_STS_SUCCESS;
652 END validate_pricing_method;
653
654
655 -----------------------------------------
656 -- PROCEDURE validate_term
657 -----------------------------------------
658 PROCEDURE validate_term (x_return_status OUT NOCOPY VARCHAR2, p_term IN NUMBER) IS
659 BEGIN
660 IF p_term IS NULL THEN
661 OKL_API.set_message(p_app_name => G_APP_NAME,
662 p_msg_name => G_COL_ERROR,
663 p_token1 => G_COL_NAME_TOKEN,
664 p_token1_value => 'term',
665 p_token2 => G_PKG_NAME_TOKEN,
666 p_token2_value => G_PKG_NAME);
667 RAISE OKL_API.G_EXCEPTION_ERROR;
668 END IF;
669 x_return_status := G_RET_STS_SUCCESS;
670 END validate_term;
671
672
673 -----------------------------------------
674 -- PROCEDURE validate_product_id
675 -----------------------------------------
676 PROCEDURE validate_product_id (x_return_status OUT NOCOPY VARCHAR2, p_product_id IN NUMBER) IS
677 BEGIN
678 IF p_product_id IS NULL THEN
679 OKL_API.set_message(p_app_name => G_APP_NAME,
680 p_msg_name => G_COL_ERROR,
681 p_token1 => G_COL_NAME_TOKEN,
682 p_token1_value => 'product_id',
683 p_token2 => G_PKG_NAME_TOKEN,
684 p_token2_value => G_PKG_NAME);
685 RAISE OKL_API.G_EXCEPTION_ERROR;
686 END IF;
687 x_return_status := G_RET_STS_SUCCESS;
688 END validate_product_id;
689
690
691 -----------------------------------------
692 -- PROCEDURE validate_end_of_term_option_id
693 -----------------------------------------
694 PROCEDURE validate_end_of_term_option_id (x_return_status OUT NOCOPY VARCHAR2, p_end_of_term_option_id IN NUMBER) IS
695 BEGIN
696 IF p_end_of_term_option_id IS NULL THEN
697 OKL_API.set_message(p_app_name => G_APP_NAME,
698 p_msg_name => G_COL_ERROR,
699 p_token1 => G_COL_NAME_TOKEN,
700 p_token1_value => 'end_of_term_option_id',
701 p_token2 => G_PKG_NAME_TOKEN,
702 p_token2_value => G_PKG_NAME);
703 RAISE OKL_API.G_EXCEPTION_ERROR;
704 END IF;
705 x_return_status := G_RET_STS_SUCCESS;
706 END validate_end_of_term_option_id;
707
708 --Added Bug 5647107 ssdeshpa start
709 -----------------------------------------
710 -- PROCEDURE validate_legal_entity_id
711 -----------------------------------------
712 PROCEDURE validate_legal_entity_id (x_return_status OUT NOCOPY VARCHAR2, p_legal_entity_id IN NUMBER) IS
713 l_return_val NUMBER(1);
714 BEGIN
715 l_return_val := NVL((OKL_LEGAL_ENTITY_UTIL.check_le_id_exists(p_legal_entity_id)),0);
716 IF (p_legal_entity_id IS NOT NULL AND l_return_val <> 1) THEN
717 OKL_API.set_message(p_app_name => G_APP_NAME,
718 p_msg_name => G_COL_ERROR,
719 p_token1 => G_COL_NAME_TOKEN,
720 p_token1_value => 'legal_entity_id',
721 p_token2 => G_PKG_NAME_TOKEN,
722 p_token2_value => G_PKG_NAME);
723 RAISE OKL_API.G_EXCEPTION_ERROR;
724 END IF;
725 x_return_status := G_RET_STS_SUCCESS;
726 END validate_legal_entity_id;
727
728 --Added Bug 5647107 ssdeshpa start
729
733 FUNCTION validate_attributes (p_lsqv_rec IN lsqv_rec_type, p_mode IN VARCHAR2) RETURN VARCHAR2 IS --Bug 7596781
730 -------------------------------
731 -- FUNCTION validate_attributes
732 -------------------------------
734
735 l_return_status VARCHAR2(1);
736
737 BEGIN
738
739 validate_id (l_return_status, p_lsqv_rec.id);
740 validate_object_version_number (l_return_status, p_lsqv_rec.object_version_number);
741 IF(p_mode = 'CREATE') THEN --Bug 7596781
742 validate_reference_number (l_return_status, p_lsqv_rec.reference_number);
743 END IF; --Bug 7596781
744 validate_status (l_return_status, p_lsqv_rec.status);
745 validate_parent_object_code (l_return_status, p_lsqv_rec.parent_object_code);
746 validate_parent_object_id (l_return_status, p_lsqv_rec.parent_object_id);
747 validate_pricing_method (l_return_status, p_lsqv_rec.pricing_method);
748 validate_term (l_return_status, p_lsqv_rec.term);
749 validate_product_id (l_return_status, p_lsqv_rec.product_id);
750 validate_end_of_term_option_id (l_return_status, p_lsqv_rec.end_of_term_option_id);
751 validate_legal_entity_id(l_return_status,p_lsqv_rec.legal_entity_id);
752 RETURN l_return_status;
753
754 END validate_attributes;
755
756 ----------------------------
757 -- PROCEDURE validate_record
758 ----------------------------
759 FUNCTION validate_record (p_lsqv_rec IN lsqv_rec_type) RETURN VARCHAR2 IS
760 l_return_status VARCHAR2(1);
761 BEGIN
762 RETURN G_RET_STS_SUCCESS;
763 END validate_record;
764
765
766 -----------------------------
767 -- PROECDURE migrate (V -> B)
768 -----------------------------
769 PROCEDURE migrate (p_from IN lsqv_rec_type, p_to IN OUT NOCOPY lsq_rec_type) IS
770
771 BEGIN
772
773 p_to.id := p_from.id;
774 p_to.object_version_number := p_from.object_version_number;
775 p_to.attribute_category := p_from.attribute_category;
776 p_to.attribute1 := p_from.attribute1;
777 p_to.attribute2 := p_from.attribute2;
778 p_to.attribute3 := p_from.attribute3;
779 p_to.attribute4 := p_from.attribute4;
780 p_to.attribute5 := p_from.attribute5;
781 p_to.attribute6 := p_from.attribute6;
782 p_to.attribute7 := p_from.attribute7;
783 p_to.attribute8 := p_from.attribute8;
784 p_to.attribute9 := p_from.attribute9;
785 p_to.attribute10 := p_from.attribute10;
786 p_to.attribute11 := p_from.attribute11;
787 p_to.attribute12 := p_from.attribute12;
788 p_to.attribute13 := p_from.attribute13;
789 p_to.attribute14 := p_from.attribute14;
790 p_to.attribute15 := p_from.attribute15;
791 p_to.reference_number := p_from.reference_number;
792 p_to.status := p_from.status;
793 p_to.parent_object_code := p_from.parent_object_code;
794 p_to.parent_object_id := p_from.parent_object_id;
795 p_to.valid_from := p_from.valid_from;
796 p_to.valid_to := p_from.valid_to;
797 p_to.customer_bookclass := p_from.customer_bookclass;
798 p_to.customer_taxowner := p_from.customer_taxowner;
799 p_to.expected_start_date := p_from.expected_start_date;
800 p_to.expected_funding_date := p_from.expected_funding_date;
801 p_to.expected_delivery_date := p_from.expected_delivery_date;
802 p_to.pricing_method := p_from.pricing_method;
803 p_to.term := p_from.term;
804 p_to.product_id := p_from.product_id;
805 p_to.end_of_term_option_id := p_from.end_of_term_option_id;
806 p_to.structured_pricing := p_from.structured_pricing;
807 p_to.line_level_pricing := p_from.line_level_pricing;
808 p_to.rate_template_id := p_from.rate_template_id;
809 p_to.rate_card_id := p_from.rate_card_id;
810 p_to.lease_rate_factor := p_from.lease_rate_factor;
811 p_to.target_rate_type := p_from.target_rate_type;
812 p_to.target_rate := p_from.target_rate;
813 p_to.target_amount := p_from.target_amount;
814 p_to.target_frequency := p_from.target_frequency;
815 p_to.target_arrears_yn := p_from.target_arrears_yn;
816 p_to.target_periods := p_from.target_periods;
817 p_to.iir := p_from.iir;
818 p_to.booking_yield := p_from.booking_yield;
819 p_to.pirr := p_from.pirr;
820 p_to.airr := p_from.airr;
821 p_to.sub_iir := p_from.sub_iir;
822 p_to.sub_booking_yield := p_from.sub_booking_yield;
823 p_to.sub_pirr := p_from.sub_pirr;
824 p_to.sub_airr := p_from.sub_airr;
825 p_to.usage_category := p_from.usage_category;
826 p_to.usage_industry_class := p_from.usage_industry_class;
827 p_to.usage_industry_code := p_from.usage_industry_code;
828 p_to.usage_amount := p_from.usage_amount;
829 p_to.usage_location_id := p_from.usage_location_id;
830 p_to.property_tax_applicable := p_from.property_tax_applicable;
834 p_to.transfer_of_title := p_from.transfer_of_title;
831 p_to.property_tax_billing_type := p_from.property_tax_billing_type;
832 p_to.upfront_tax_treatment := p_from.upfront_tax_treatment;
833 p_to.upfront_tax_stream_type := p_from.upfront_tax_stream_type;
835 p_to.age_of_equipment := p_from.age_of_equipment;
836 p_to.purchase_of_lease := p_from.purchase_of_lease;
837 p_to.sale_and_lease_back := p_from.sale_and_lease_back;
838 p_to.interest_disclosed := p_from.interest_disclosed;
839 p_to.primary_quote := p_from.primary_quote;
840 p_to.legal_entity_id := p_from.legal_entity_id;
841 -- Bug 5908845. eBTax Enhancement Project
842 p_to.line_intended_use := p_from.line_intended_use;
843 -- End Bug 5908845. eBTax Enhancement Project
844 END migrate;
845
846
847 -----------------------------
848 -- PROCEDURE migrate (V -> TL)
849 -----------------------------
850 PROCEDURE migrate (p_from IN lsqv_rec_type, p_to IN OUT NOCOPY lsqtl_rec_type) IS
851 BEGIN
852 p_to.id := p_from.id;
853 p_to.short_description := p_from.short_description;
854 p_to.description := p_from.description;
855 p_to.comments := p_from.comments;
856 END migrate;
857
858
859 ---------------------------
860 -- PROCEDURE insert_row (B)
861 ---------------------------
862 PROCEDURE insert_row (x_return_status OUT NOCOPY VARCHAR2, p_lsq_rec IN lsq_rec_type) IS
863
864 l_prog_name VARCHAR2(61);
865
866 BEGIN
867
868 l_prog_name := G_PKG_NAME||'.insert_row (B)';
869
870 INSERT INTO okl_lease_quotes_b (
871 id
872 ,object_version_number
873 ,attribute_category
874 ,attribute1
875 ,attribute2
876 ,attribute3
877 ,attribute4
878 ,attribute5
879 ,attribute6
880 ,attribute7
881 ,attribute8
882 ,attribute9
883 ,attribute10
884 ,attribute11
885 ,attribute12
886 ,attribute13
887 ,attribute14
888 ,attribute15
889 ,created_by
890 ,creation_date
891 ,last_updated_by
892 ,last_update_date
893 ,last_update_login
894 ,reference_number
895 ,status
896 ,parent_object_code
897 ,parent_object_id
898 ,valid_from
899 ,valid_to
900 ,customer_bookclass
901 ,customer_taxowner
902 ,expected_start_date
903 ,expected_funding_date
904 ,expected_delivery_date
905 ,pricing_method
906 ,term
907 ,product_id
908 ,end_of_term_option_id
909 ,structured_pricing
910 ,line_level_pricing
911 ,rate_template_id
912 ,rate_card_id
913 ,lease_rate_factor
914 ,target_rate_type
915 ,target_rate
916 ,target_amount
917 ,target_frequency
918 ,target_arrears_yn
919 ,target_periods
920 ,iir
921 ,booking_yield
922 ,pirr
923 ,airr
924 ,sub_iir
925 ,sub_booking_yield
926 ,sub_pirr
927 ,sub_airr
928 ,usage_category
929 ,usage_industry_class
930 ,usage_industry_code
931 ,usage_amount
932 ,usage_location_id
933 ,property_tax_applicable
934 ,property_tax_billing_type
935 ,upfront_tax_treatment
936 ,upfront_tax_stream_type
937 ,transfer_of_title
938 ,age_of_equipment
939 ,purchase_of_lease
940 ,sale_and_lease_back
941 ,interest_disclosed
942 ,primary_quote
943 ,legal_entity_id
944 --Bug 5908845. eBTax Enhancement Project
945 ,line_intended_use
946 -- End Bug 5908845. eBTax Enhancement Project
947 )
948 VALUES
949 (
950 p_lsq_rec.id
951 ,p_lsq_rec.object_version_number
952 ,p_lsq_rec.attribute_category
953 ,p_lsq_rec.attribute1
954 ,p_lsq_rec.attribute2
955 ,p_lsq_rec.attribute3
956 ,p_lsq_rec.attribute4
957 ,p_lsq_rec.attribute5
958 ,p_lsq_rec.attribute6
959 ,p_lsq_rec.attribute7
960 ,p_lsq_rec.attribute8
961 ,p_lsq_rec.attribute9
962 ,p_lsq_rec.attribute10
963 ,p_lsq_rec.attribute11
964 ,p_lsq_rec.attribute12
965 ,p_lsq_rec.attribute13
966 ,p_lsq_rec.attribute14
967 ,p_lsq_rec.attribute15
968 ,G_USER_ID
969 ,SYSDATE
970 ,G_USER_ID
971 ,SYSDATE
972 ,G_LOGIN_ID
973 ,p_lsq_rec.reference_number
974 ,p_lsq_rec.status
975 ,p_lsq_rec.parent_object_code
976 ,p_lsq_rec.parent_object_id
977 ,p_lsq_rec.valid_from
978 ,p_lsq_rec.valid_to
979 ,p_lsq_rec.customer_bookclass
980 ,p_lsq_rec.customer_taxowner
981 ,p_lsq_rec.expected_start_date
982 ,p_lsq_rec.expected_funding_date
983 ,p_lsq_rec.expected_delivery_date
984 ,p_lsq_rec.pricing_method
985 ,p_lsq_rec.term
986 ,p_lsq_rec.product_id
987 ,p_lsq_rec.end_of_term_option_id
988 ,p_lsq_rec.structured_pricing
989 ,p_lsq_rec.line_level_pricing
990 ,p_lsq_rec.rate_template_id
991 ,p_lsq_rec.rate_card_id
992 ,p_lsq_rec.lease_rate_factor
993 ,p_lsq_rec.target_rate_type
994 ,p_lsq_rec.target_rate
995 ,p_lsq_rec.target_amount
996 ,p_lsq_rec.target_frequency
997 ,p_lsq_rec.target_arrears_yn
1001 ,p_lsq_rec.pirr
998 ,p_lsq_rec.target_periods
999 ,p_lsq_rec.iir
1000 ,p_lsq_rec.booking_yield
1002 ,p_lsq_rec.airr
1003 ,p_lsq_rec.sub_iir
1004 ,p_lsq_rec.sub_booking_yield
1005 ,p_lsq_rec.sub_pirr
1006 ,p_lsq_rec.sub_airr
1007 ,p_lsq_rec.usage_category
1008 ,p_lsq_rec.usage_industry_class
1009 ,p_lsq_rec.usage_industry_code
1010 ,p_lsq_rec.usage_amount
1011 ,p_lsq_rec.usage_location_id
1012 ,p_lsq_rec.property_tax_applicable
1013 ,p_lsq_rec.property_tax_billing_type
1014 ,p_lsq_rec.upfront_tax_treatment
1015 ,p_lsq_rec.upfront_tax_stream_type
1016 ,p_lsq_rec.transfer_of_title
1017 ,p_lsq_rec.age_of_equipment
1018 ,p_lsq_rec.purchase_of_lease
1019 ,p_lsq_rec.sale_and_lease_back
1020 ,p_lsq_rec.interest_disclosed
1021 ,p_lsq_rec.primary_quote
1022 ,p_lsq_rec.legal_entity_id
1023 -- Bug 5908845. eBTax Enhancement Project
1024 ,p_lsq_rec.line_intended_use
1025 -- End Bug 5908845. eBTax Enhancement Project
1026 );
1027
1028 x_return_status := G_RET_STS_SUCCESS;
1029
1030 EXCEPTION
1031
1032 WHEN OTHERS THEN
1033
1034 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1035 p_msg_name => G_DB_ERROR,
1036 p_token1 => G_PROG_NAME_TOKEN,
1037 p_token1_value => l_prog_name,
1038 p_token2 => G_SQLCODE_TOKEN,
1039 p_token2_value => sqlcode,
1040 p_token3 => G_SQLERRM_TOKEN,
1041 p_token3_value => sqlerrm);
1042
1043 x_return_status := G_RET_STS_UNEXP_ERROR;
1044
1045 END insert_row;
1046
1047
1048 ----------------------------
1049 -- PROCEDURE insert_row (TL)
1050 ----------------------------
1051 PROCEDURE insert_row (x_return_status OUT NOCOPY VARCHAR2, p_lsqtl_rec IN lsqtl_rec_type) IS
1052
1053 CURSOR get_languages IS
1054 SELECT language_code
1055 FROM FND_LANGUAGES
1056 WHERE INSTALLED_FLAG IN ('I', 'B');
1057
1058 l_sfwt_flag VARCHAR2(1);
1059
1060 l_prog_name VARCHAR2(61);
1061
1062 BEGIN
1063
1064 l_prog_name := G_PKG_NAME||'.insert_row (TL)';
1065
1066 FOR l_lang_rec IN get_languages LOOP
1067
1068 IF l_lang_rec.language_code = USERENV('LANG') THEN
1069 l_sfwt_flag := 'N';
1070 ELSE
1071 l_sfwt_flag := 'Y';
1072 END IF;
1073
1074 INSERT INTO OKL_LEASE_QUOTES_TL (
1075 id
1076 ,language
1077 ,source_lang
1078 ,sfwt_flag
1079 ,created_by
1080 ,creation_date
1081 ,last_updated_by
1082 ,last_update_date
1083 ,last_update_login
1084 ,short_description
1085 ,description
1086 ,comments)
1087 VALUES (
1088 p_lsqtl_rec.id
1089 ,l_lang_rec.language_code
1090 ,USERENV('LANG')
1091 ,l_sfwt_flag
1092 ,G_USER_ID
1093 ,SYSDATE
1094 ,G_USER_ID
1095 ,SYSDATE
1096 ,G_LOGIN_ID
1097 ,p_lsqtl_rec.short_description
1098 ,p_lsqtl_rec.description
1099 ,p_lsqtl_rec.comments);
1100
1101 END LOOP;
1102
1103 x_return_status := G_RET_STS_SUCCESS;
1104
1105 EXCEPTION
1106
1107 WHEN OTHERS THEN
1108
1109 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1110 p_msg_name => G_DB_ERROR,
1111 p_token1 => G_PROG_NAME_TOKEN,
1112 p_token1_value => l_prog_name,
1113 p_token2 => G_SQLCODE_TOKEN,
1114 p_token2_value => sqlcode,
1115 p_token3 => G_SQLERRM_TOKEN,
1116 p_token3_value => sqlerrm);
1117
1118 x_return_status := G_RET_STS_UNEXP_ERROR;
1119
1120 END insert_row;
1121
1122
1123 ---------------------------
1124 -- PROCEDURE insert_row (V)
1125 ---------------------------
1126 PROCEDURE insert_row (
1127 x_return_status OUT NOCOPY VARCHAR2,
1128 p_lsqv_rec IN lsqv_rec_type,
1129 x_lsqv_rec OUT NOCOPY lsqv_rec_type) IS
1130
1131 l_return_status VARCHAR2(1);
1132
1133 l_lsqv_rec lsqv_rec_type;
1134 l_lsq_rec lsq_rec_type;
1135 l_lsqtl_rec lsqtl_rec_type;
1136
1137 l_prog_name VARCHAR2(61);
1138
1139 BEGIN
1140
1141 l_prog_name := G_PKG_NAME||'.insert_row (V)';
1142
1143 l_lsqv_rec := null_out_defaults (p_lsqv_rec);
1144
1145 SELECT okl_lsq_seq.nextval INTO l_lsqv_rec.ID FROM DUAL;
1146
1147 l_lsqv_rec.OBJECT_VERSION_NUMBER := 1;
1148
1149 l_return_status := validate_attributes(l_lsqv_rec,'CREATE'); --Bug 7596781
1150
1151 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1152 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1153 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1154 RAISE OKL_API.G_EXCEPTION_ERROR;
1155 END IF;
1156
1157 l_return_status := validate_record(l_lsqv_rec);
1158
1159 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1160 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1164
1161 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1162 RAISE OKL_API.G_EXCEPTION_ERROR;
1163 END IF;
1165 migrate (l_lsqv_rec, l_lsq_rec);
1166 migrate (l_lsqv_rec, l_lsqtl_rec);
1167
1168 insert_row (x_return_status => l_return_status, p_lsq_rec => l_lsq_rec);
1169
1170 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1171 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1172 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1173 RAISE OKL_API.G_EXCEPTION_ERROR;
1174 END IF;
1175
1176 insert_row (x_return_status => l_return_status, p_lsqtl_rec => l_lsqtl_rec);
1177
1178 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1179 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1180 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1181 RAISE OKL_API.G_EXCEPTION_ERROR;
1182 END IF;
1183
1184 x_lsqv_rec := l_lsqv_rec;
1185 x_return_status := l_return_status;
1186
1187 EXCEPTION
1188
1189 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1190
1191 x_return_status := G_RET_STS_ERROR;
1192
1193 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1194
1195 x_return_status := G_RET_STS_UNEXP_ERROR;
1196
1197 WHEN OTHERS THEN
1198
1199 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1200 p_msg_name => G_DB_ERROR,
1201 p_token1 => G_PROG_NAME_TOKEN,
1202 p_token1_value => l_prog_name,
1203 p_token2 => G_SQLCODE_TOKEN,
1204 p_token2_value => sqlcode,
1205 p_token3 => G_SQLERRM_TOKEN,
1206 p_token3_value => sqlerrm);
1207
1208 x_return_status := G_RET_STS_UNEXP_ERROR;
1209
1210 END insert_row;
1211
1212
1213 -----------------------------
1214 -- PROCEDURE insert_row (REC)
1215 -----------------------------
1216 PROCEDURE insert_row(
1217 p_api_version IN NUMBER,
1218 p_init_msg_list IN VARCHAR2,
1219 x_return_status OUT NOCOPY VARCHAR2,
1220 x_msg_count OUT NOCOPY NUMBER,
1221 x_msg_data OUT NOCOPY VARCHAR2,
1222 p_lsqv_rec IN lsqv_rec_type,
1223 x_lsqv_rec OUT NOCOPY lsqv_rec_type) IS
1224
1225 l_return_status VARCHAR2(1);
1226
1227 l_prog_name VARCHAR2(61);
1228
1229 BEGIN
1230
1231 l_prog_name := G_PKG_NAME||'.insert_row (REC)';
1232
1233 IF p_init_msg_list = G_TRUE THEN
1234 FND_MSG_PUB.initialize;
1235 END IF;
1236
1237 insert_row (x_return_status => l_return_status,
1238 p_lsqv_rec => p_lsqv_rec,
1239 x_lsqv_rec => x_lsqv_rec);
1240
1241 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1242 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1243 ELSIF l_return_status = G_RET_STS_ERROR THEN
1244 RAISE OKL_API.G_EXCEPTION_ERROR;
1245 END IF;
1246
1247 x_return_status := l_return_status;
1248
1249 EXCEPTION
1250
1251 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1252
1253 x_return_status := G_RET_STS_ERROR;
1254
1255 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1256
1257 x_return_status := G_RET_STS_UNEXP_ERROR;
1258
1259 WHEN OTHERS THEN
1260
1261 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1262 p_msg_name => G_DB_ERROR,
1263 p_token1 => G_PROG_NAME_TOKEN,
1264 p_token1_value => l_prog_name,
1265 p_token2 => G_SQLCODE_TOKEN,
1266 p_token2_value => sqlcode,
1267 p_token3 => G_SQLERRM_TOKEN,
1268 p_token3_value => sqlerrm);
1269
1270 x_return_status := G_RET_STS_UNEXP_ERROR;
1271
1272 END insert_row;
1273
1274
1275 -----------------------------
1276 -- PROCEDURE insert_row (TBL)
1277 -----------------------------
1278 PROCEDURE insert_row(
1279 p_api_version IN NUMBER,
1280 p_init_msg_list IN VARCHAR2,
1281 x_return_status OUT NOCOPY VARCHAR2,
1282 x_msg_count OUT NOCOPY NUMBER,
1283 x_msg_data OUT NOCOPY VARCHAR2,
1284 p_lsqv_tbl IN lsqv_tbl_type,
1285 x_lsqv_tbl OUT NOCOPY lsqv_tbl_type) IS
1286
1287 l_return_status VARCHAR2(1);
1288 i BINARY_INTEGER;
1289
1290 l_prog_name VARCHAR2(61);
1291
1292 BEGIN
1293
1294 l_prog_name := G_PKG_NAME||'.insert_row (TBL)';
1295
1296 IF p_init_msg_list = G_TRUE THEN
1297 FND_MSG_PUB.initialize;
1298 END IF;
1299
1300 IF (p_lsqv_tbl.COUNT > 0) THEN
1301 i := p_lsqv_tbl.FIRST;
1302 LOOP
1303 IF p_lsqv_tbl.EXISTS(i) THEN
1304
1305 insert_row (x_return_status => l_return_status,
1306 p_lsqv_rec => p_lsqv_tbl(i),
1307 x_lsqv_rec => x_lsqv_tbl(i));
1308
1309 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1310 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1311 ELSIF l_return_status = G_RET_STS_ERROR THEN
1312 RAISE OKL_API.G_EXCEPTION_ERROR;
1313 END IF;
1314
1315 EXIT WHEN (i = p_lsqv_tbl.LAST);
1316 i := p_lsqv_tbl.NEXT(i);
1317
1321
1318 END IF;
1319
1320 END LOOP;
1322 ELSE
1323
1324 l_return_status := G_RET_STS_SUCCESS;
1325
1326 END IF;
1327
1328 x_return_status := l_return_status;
1329
1330 EXCEPTION
1331
1332 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1333
1334 x_return_status := G_RET_STS_ERROR;
1335
1336 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1337
1338 x_return_status := G_RET_STS_UNEXP_ERROR;
1339
1340 WHEN OTHERS THEN
1341
1342 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1343 p_msg_name => G_DB_ERROR,
1344 p_token1 => G_PROG_NAME_TOKEN,
1345 p_token1_value => l_prog_name,
1346 p_token2 => G_SQLCODE_TOKEN,
1347 p_token2_value => sqlcode,
1348 p_token3 => G_SQLERRM_TOKEN,
1349 p_token3_value => sqlerrm);
1350
1351 x_return_status := G_RET_STS_UNEXP_ERROR;
1352
1353 END insert_row;
1354
1355
1356 ---------------------
1357 -- PROCEDURE lock_row
1358 ---------------------
1359 PROCEDURE lock_row (x_return_status OUT NOCOPY VARCHAR2, p_lsq_rec IN lsq_rec_type) IS
1360
1361 E_Resource_Busy EXCEPTION;
1362
1363 PRAGMA EXCEPTION_INIT (E_Resource_Busy, -00054);
1364
1365 CURSOR lock_csr IS
1366 SELECT OBJECT_VERSION_NUMBER
1367 FROM OKL_LEASE_QUOTES_B
1368 WHERE ID = p_lsq_rec.id
1369 AND OBJECT_VERSION_NUMBER = p_lsq_rec.object_version_number
1370 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1371
1372 CURSOR lchk_csr IS
1373 SELECT OBJECT_VERSION_NUMBER
1374 FROM OKL_LEASE_QUOTES_B
1375 WHERE ID = p_lsq_rec.id;
1376
1377 l_object_version_number NUMBER;
1378 lc_object_version_number NUMBER;
1379
1380 l_prog_name VARCHAR2(61);
1381
1382 BEGIN
1383
1384 l_prog_name := G_PKG_NAME||'.lock_row';
1385
1386 BEGIN
1387 OPEN lock_csr;
1388 FETCH lock_csr INTO l_object_version_number;
1389 CLOSE lock_csr;
1390 EXCEPTION
1391 WHEN E_Resource_Busy THEN
1392
1393 IF (lock_csr%ISOPEN) THEN
1394 CLOSE lock_csr;
1395 END IF;
1396 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1397 p_msg_name => G_OVN_ERROR2,
1398 p_token1 => G_PROG_NAME_TOKEN,
1399 p_token1_value => l_prog_name);
1400 RAISE OKL_API.G_EXCEPTION_ERROR;
1401 END;
1402
1403 IF l_object_version_number IS NULL THEN
1404
1405 OPEN lchk_csr;
1406 FETCH lchk_csr INTO lc_object_version_number;
1407 CLOSE lchk_csr;
1408
1409 IF lc_object_version_number IS NULL THEN
1410
1411 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1412 p_msg_name => G_OVN_ERROR3,
1413 p_token1 => G_PROG_NAME_TOKEN,
1414 p_token1_value => l_prog_name);
1415
1416 ELSIF lc_object_version_number <> p_lsq_rec.object_version_number THEN
1417
1418 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1419 p_msg_name => G_OVN_ERROR,
1420 p_token1 => G_PROG_NAME_TOKEN,
1421 p_token1_value => l_prog_name);
1422
1423 END IF;
1424
1425 RAISE OKL_API.G_EXCEPTION_ERROR;
1426
1427 END IF;
1428
1429 x_return_status := G_RET_STS_SUCCESS;
1430
1431 EXCEPTION
1432
1433 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1434
1435 x_return_status := G_RET_STS_ERROR;
1436
1437 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1438
1439 x_return_status := G_RET_STS_UNEXP_ERROR;
1440
1441 WHEN OTHERS THEN
1442
1443 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1444 p_msg_name => G_DB_ERROR,
1445 p_token1 => G_PROG_NAME_TOKEN,
1446 p_token1_value => l_prog_name,
1447 p_token2 => G_SQLCODE_TOKEN,
1448 p_token2_value => sqlcode,
1449 p_token3 => G_SQLERRM_TOKEN,
1450 p_token3_value => sqlerrm);
1451
1452 x_return_status := G_RET_STS_UNEXP_ERROR;
1453
1454 END lock_row;
1455
1456
1457 ---------------------------
1458 -- PROCEDURE update_row (B)
1459 ---------------------------
1460 PROCEDURE update_row(x_return_status OUT NOCOPY VARCHAR2, p_lsq_rec IN lsq_rec_type) IS
1461
1462 l_return_status VARCHAR2(1);
1463
1464 l_prog_name VARCHAR2(61);
1465
1466 BEGIN
1467
1468 l_prog_name := G_PKG_NAME||'.update_row (B)';
1469
1470 lock_row (x_return_status => l_return_status, p_lsq_rec => p_lsq_rec);
1471
1472 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1473 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1474 ELSIF l_return_status = G_RET_STS_ERROR THEN
1475 RAISE OKL_API.G_EXCEPTION_ERROR;
1476 END IF;
1477
1478 UPDATE okl_lease_quotes_b
1479 SET
1480 object_version_number = p_lsq_rec.object_version_number+1
1481 ,attribute_category = p_lsq_rec.attribute_category
1482 ,attribute1 = p_lsq_rec.attribute1
1483 ,attribute2 = p_lsq_rec.attribute2
1487 ,attribute6 = p_lsq_rec.attribute6
1484 ,attribute3 = p_lsq_rec.attribute3
1485 ,attribute4 = p_lsq_rec.attribute4
1486 ,attribute5 = p_lsq_rec.attribute5
1488 ,attribute7 = p_lsq_rec.attribute7
1489 ,attribute8 = p_lsq_rec.attribute8
1490 ,attribute9 = p_lsq_rec.attribute9
1491 ,attribute10 = p_lsq_rec.attribute10
1492 ,attribute11 = p_lsq_rec.attribute11
1493 ,attribute12 = p_lsq_rec.attribute12
1494 ,attribute13 = p_lsq_rec.attribute13
1495 ,attribute14 = p_lsq_rec.attribute14
1496 ,attribute15 = p_lsq_rec.attribute15
1497 ,reference_number = p_lsq_rec.reference_number
1498 ,status = p_lsq_rec.status
1499 ,parent_object_code = p_lsq_rec.parent_object_code
1500 ,parent_object_id = p_lsq_rec.parent_object_id
1501 ,valid_from = p_lsq_rec.valid_from
1502 ,valid_to = p_lsq_rec.valid_to
1503 ,customer_bookclass = p_lsq_rec.customer_bookclass
1504 ,customer_taxowner = p_lsq_rec.customer_taxowner
1505 ,expected_start_date = p_lsq_rec.expected_start_date
1506 ,expected_funding_date = p_lsq_rec.expected_funding_date
1507 ,expected_delivery_date = p_lsq_rec.expected_delivery_date
1508 ,pricing_method = p_lsq_rec.pricing_method
1509 ,term = p_lsq_rec.term
1510 ,product_id = p_lsq_rec.product_id
1511 ,end_of_term_option_id = p_lsq_rec.end_of_term_option_id
1512 ,structured_pricing = p_lsq_rec.structured_pricing
1513 ,line_level_pricing = p_lsq_rec.line_level_pricing
1514 ,rate_template_id = p_lsq_rec.rate_template_id
1515 ,rate_card_id = p_lsq_rec.rate_card_id
1516 ,lease_rate_factor = p_lsq_rec.lease_rate_factor
1517 ,target_rate_type = p_lsq_rec.target_rate_type
1518 ,target_rate = p_lsq_rec.target_rate
1519 ,target_amount = p_lsq_rec.target_amount
1520 ,target_frequency = p_lsq_rec.target_frequency
1521 ,target_arrears_yn = p_lsq_rec.target_arrears_yn
1522 ,target_periods = p_lsq_rec.target_periods
1523 ,iir = p_lsq_rec.iir
1524 ,booking_yield = p_lsq_rec.booking_yield
1525 ,pirr = p_lsq_rec.pirr
1526 ,airr = p_lsq_rec.airr
1527 ,sub_iir = p_lsq_rec.sub_iir
1528 ,sub_booking_yield = p_lsq_rec.sub_booking_yield
1529 ,sub_pirr = p_lsq_rec.sub_pirr
1530 ,sub_airr = p_lsq_rec.sub_airr
1531 ,usage_category = p_lsq_rec.usage_category
1532 ,usage_industry_class = p_lsq_rec.usage_industry_class
1533 ,usage_industry_code = p_lsq_rec.usage_industry_code
1534 ,usage_amount = p_lsq_rec.usage_amount
1535 ,usage_location_id = p_lsq_rec.usage_location_id
1536 ,property_tax_applicable = p_lsq_rec.property_tax_applicable
1537 ,property_tax_billing_type = p_lsq_rec.property_tax_billing_type
1538 ,upfront_tax_treatment = p_lsq_rec.upfront_tax_treatment
1539 ,upfront_tax_stream_type = p_lsq_rec.upfront_tax_stream_type
1540 ,transfer_of_title = p_lsq_rec.transfer_of_title
1541 ,age_of_equipment = p_lsq_rec.age_of_equipment
1542 ,purchase_of_lease = p_lsq_rec.purchase_of_lease
1543 ,sale_and_lease_back = p_lsq_rec.sale_and_lease_back
1544 ,interest_disclosed = p_lsq_rec.interest_disclosed
1545 ,primary_quote = p_lsq_rec.primary_quote
1546 ,legal_entity_id = p_lsq_rec.legal_entity_id
1547 -- Bug 5908845. eBTax Enhancement Project
1548 ,line_intended_use = p_lsq_rec.line_intended_use
1549 -- End Bug 5908845. eBTax Enhancement Project
1550 WHERE id = p_lsq_rec.id;
1551
1552 x_return_status := l_return_status;
1553
1554 EXCEPTION
1555
1556 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1557 x_return_status := G_RET_STS_ERROR;
1558
1559 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1560 x_return_status := G_RET_STS_UNEXP_ERROR;
1561
1562 WHEN OTHERS THEN
1563 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1564 p_msg_name => G_DB_ERROR,
1565 p_token1 => G_PROG_NAME_TOKEN,
1566 p_token1_value => l_prog_name,
1567 p_token2 => G_SQLCODE_TOKEN,
1568 p_token2_value => sqlcode,
1569 p_token3 => G_SQLERRM_TOKEN,
1570 p_token3_value => sqlerrm);
1571
1572 x_return_status := G_RET_STS_UNEXP_ERROR;
1573
1574 END update_row;
1575
1576
1577 ----------------------------
1578 -- PROCEDURE update_row (TL)
1579 ----------------------------
1580 PROCEDURE update_row(x_return_status OUT NOCOPY VARCHAR2, p_lsqtl_rec IN lsqtl_rec_type) IS
1581
1582 l_prog_name VARCHAR2(61);
1583
1584 BEGIN
1585
1586 l_prog_name := G_PKG_NAME||'.update_row (TL)';
1587
1588 UPDATE OKL_LEASE_QUOTES_TL
1589 SET
1590 source_lang = USERENV('LANG')
1591 ,sfwt_flag = 'Y'
1592 ,last_updated_by = G_USER_ID
1593 ,last_update_date = SYSDATE
1594 ,last_update_login = G_LOGIN_ID
1595 ,short_description = p_lsqtl_rec.short_description
1596 ,description = p_lsqtl_rec.description
1597 ,comments = p_lsqtl_rec.comments
1598 WHERE ID = p_lsqtl_rec.id;
1599
1600 UPDATE OKL_LEASE_QUOTES_TL
1601 SET SFWT_FLAG = 'N'
1602 WHERE ID = p_lsqtl_rec.id
1603 AND SOURCE_LANG = LANGUAGE;
1604
1605 x_return_status := G_RET_STS_SUCCESS;
1606
1607 EXCEPTION
1608
1609 WHEN OTHERS THEN
1610
1611 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1612 p_msg_name => G_DB_ERROR,
1616 p_token2_value => sqlcode,
1613 p_token1 => G_PROG_NAME_TOKEN,
1614 p_token1_value => l_prog_name,
1615 p_token2 => G_SQLCODE_TOKEN,
1617 p_token3 => G_SQLERRM_TOKEN,
1618 p_token3_value => sqlerrm);
1619
1620 x_return_status := G_RET_STS_UNEXP_ERROR;
1621
1622 END update_row;
1623
1624
1625 ---------------------------
1626 -- PROCEDURE update_row (V)
1627 ---------------------------
1628 PROCEDURE update_row (
1629 x_return_status OUT NOCOPY VARCHAR2,
1630 p_lsqv_rec IN lsqv_rec_type,
1631 x_lsqv_rec OUT NOCOPY lsqv_rec_type) IS
1632
1633 l_prog_name VARCHAR2(61);
1634
1635 l_return_status VARCHAR2(1);
1636 l_lsqv_rec lsqv_rec_type;
1637 l_lsq_rec lsq_rec_type;
1638 l_lsqtl_rec lsqtl_rec_type;
1639
1640 ----------------------
1641 -- populate_new_record
1642 ----------------------
1643 FUNCTION populate_new_record (p_lsqv_rec IN lsqv_rec_type,
1644 x_lsqv_rec OUT NOCOPY lsqv_rec_type) RETURN VARCHAR2 IS
1645
1646 l_prog_name VARCHAR2(61);
1647 l_return_status VARCHAR2(1);
1648 l_db_lsqv_rec lsqv_rec_type;
1649
1650 BEGIN
1651
1652 l_prog_name := G_PKG_NAME||'.populate_new_record';
1653
1654 x_lsqv_rec := p_lsqv_rec;
1655 l_db_lsqv_rec := get_rec (p_lsqv_rec.id, l_return_status);
1656
1657 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1658 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1659 ELSIF l_return_status = G_RET_STS_ERROR THEN
1660 RAISE OKL_API.G_EXCEPTION_ERROR;
1661 END IF;
1662
1663 IF x_lsqv_rec.attribute_category = FND_API.G_MISS_CHAR THEN
1664 x_lsqv_rec.attribute_category := l_db_lsqv_rec.attribute_category;
1665 END IF;
1666 IF x_lsqv_rec.attribute1 = FND_API.G_MISS_CHAR THEN
1667 x_lsqv_rec.attribute1 := l_db_lsqv_rec.attribute1;
1668 END IF;
1669 IF x_lsqv_rec.attribute2 = FND_API.G_MISS_CHAR THEN
1670 x_lsqv_rec.attribute2 := l_db_lsqv_rec.attribute2;
1671 END IF;
1672 IF x_lsqv_rec.attribute3 = FND_API.G_MISS_CHAR THEN
1673 x_lsqv_rec.attribute3 := l_db_lsqv_rec.attribute3;
1674 END IF;
1675 IF x_lsqv_rec.attribute4 = FND_API.G_MISS_CHAR THEN
1676 x_lsqv_rec.attribute4 := l_db_lsqv_rec.attribute4;
1677 END IF;
1678 IF x_lsqv_rec.attribute5 = FND_API.G_MISS_CHAR THEN
1679 x_lsqv_rec.attribute5 := l_db_lsqv_rec.attribute5;
1680 END IF;
1681 IF x_lsqv_rec.attribute6 = FND_API.G_MISS_CHAR THEN
1682 x_lsqv_rec.attribute6 := l_db_lsqv_rec.attribute6;
1683 END IF;
1684 IF x_lsqv_rec.attribute7 = FND_API.G_MISS_CHAR THEN
1685 x_lsqv_rec.attribute7 := l_db_lsqv_rec.attribute7;
1686 END IF;
1687 IF x_lsqv_rec.attribute8 = FND_API.G_MISS_CHAR THEN
1688 x_lsqv_rec.attribute8 := l_db_lsqv_rec.attribute8;
1689 END IF;
1690 IF x_lsqv_rec.attribute9 = FND_API.G_MISS_CHAR THEN
1691 x_lsqv_rec.attribute9 := l_db_lsqv_rec.attribute9;
1692 END IF;
1693 IF x_lsqv_rec.attribute10 = FND_API.G_MISS_CHAR THEN
1694 x_lsqv_rec.attribute10 := l_db_lsqv_rec.attribute10;
1695 END IF;
1696 IF x_lsqv_rec.attribute11 = FND_API.G_MISS_CHAR THEN
1697 x_lsqv_rec.attribute11 := l_db_lsqv_rec.attribute11;
1698 END IF;
1699 IF x_lsqv_rec.attribute12 = FND_API.G_MISS_CHAR THEN
1700 x_lsqv_rec.attribute12 := l_db_lsqv_rec.attribute12;
1701 END IF;
1702 IF x_lsqv_rec.attribute13 = FND_API.G_MISS_CHAR THEN
1703 x_lsqv_rec.attribute13 := l_db_lsqv_rec.attribute13;
1704 END IF;
1705 IF x_lsqv_rec.attribute14 = FND_API.G_MISS_CHAR THEN
1706 x_lsqv_rec.attribute14 := l_db_lsqv_rec.attribute14;
1707 END IF;
1708 IF x_lsqv_rec.attribute15 = FND_API.G_MISS_CHAR THEN
1709 x_lsqv_rec.attribute15 := l_db_lsqv_rec.attribute15;
1710 END IF;
1711 IF x_lsqv_rec.reference_number = FND_API.G_MISS_CHAR THEN
1712 x_lsqv_rec.reference_number := l_db_lsqv_rec.reference_number;
1713 END IF;
1714 IF x_lsqv_rec.status = FND_API.G_MISS_CHAR THEN
1715 x_lsqv_rec.status := l_db_lsqv_rec.status;
1716 END IF;
1717 IF x_lsqv_rec.parent_object_code = FND_API.G_MISS_CHAR THEN
1718 x_lsqv_rec.parent_object_code := l_db_lsqv_rec.parent_object_code;
1719 END IF;
1720 IF x_lsqv_rec.parent_object_id = FND_API.G_MISS_NUM THEN
1721 x_lsqv_rec.parent_object_id := l_db_lsqv_rec.parent_object_id;
1722 END IF;
1723 IF x_lsqv_rec.valid_from = FND_API.G_MISS_DATE THEN
1724 x_lsqv_rec.valid_from := l_db_lsqv_rec.valid_from;
1725 END IF;
1726 IF x_lsqv_rec.valid_to = FND_API.G_MISS_DATE THEN
1727 x_lsqv_rec.valid_to := l_db_lsqv_rec.valid_to;
1728 END IF;
1729 IF x_lsqv_rec.customer_bookclass = FND_API.G_MISS_CHAR THEN
1730 x_lsqv_rec.customer_bookclass := l_db_lsqv_rec.customer_bookclass;
1731 END IF;
1732 IF x_lsqv_rec.customer_taxowner = FND_API.G_MISS_CHAR THEN
1733 x_lsqv_rec.customer_taxowner := l_db_lsqv_rec.customer_taxowner;
1734 END IF;
1735 IF x_lsqv_rec.expected_start_date = FND_API.G_MISS_DATE THEN
1736 x_lsqv_rec.expected_start_date := l_db_lsqv_rec.expected_start_date;
1737 END IF;
1738 IF x_lsqv_rec.expected_funding_date = FND_API.G_MISS_DATE THEN
1739 x_lsqv_rec.expected_funding_date := l_db_lsqv_rec.expected_funding_date;
1740 END IF;
1741 IF x_lsqv_rec.expected_delivery_date = FND_API.G_MISS_DATE THEN
1742 x_lsqv_rec.expected_delivery_date := l_db_lsqv_rec.expected_delivery_date;
1743 END IF;
1744 IF x_lsqv_rec.pricing_method = FND_API.G_MISS_CHAR THEN
1745 x_lsqv_rec.pricing_method := l_db_lsqv_rec.pricing_method;
1746 END IF;
1747 IF x_lsqv_rec.term = FND_API.G_MISS_NUM THEN
1748 x_lsqv_rec.term := l_db_lsqv_rec.term;
1749 END IF;
1750 IF x_lsqv_rec.product_id = FND_API.G_MISS_NUM THEN
1751 x_lsqv_rec.product_id := l_db_lsqv_rec.product_id;
1752 END IF;
1753 IF x_lsqv_rec.end_of_term_option_id = FND_API.G_MISS_NUM THEN
1754 x_lsqv_rec.end_of_term_option_id := l_db_lsqv_rec.end_of_term_option_id;
1755 END IF;
1756 IF x_lsqv_rec.structured_pricing = FND_API.G_MISS_CHAR THEN
1757 x_lsqv_rec.structured_pricing := l_db_lsqv_rec.structured_pricing;
1758 END IF;
1759 IF x_lsqv_rec.line_level_pricing = FND_API.G_MISS_CHAR THEN
1760 x_lsqv_rec.line_level_pricing := l_db_lsqv_rec.line_level_pricing;
1761 END IF;
1762 IF x_lsqv_rec.rate_template_id = FND_API.G_MISS_NUM THEN
1763 x_lsqv_rec.rate_template_id := l_db_lsqv_rec.rate_template_id;
1764 END IF;
1765 IF x_lsqv_rec.rate_card_id = FND_API.G_MISS_NUM THEN
1766 x_lsqv_rec.rate_card_id := l_db_lsqv_rec.rate_card_id;
1767 END IF;
1768 IF x_lsqv_rec.lease_rate_factor = FND_API.G_MISS_NUM THEN
1769 x_lsqv_rec.lease_rate_factor := l_db_lsqv_rec.lease_rate_factor;
1770 END IF;
1771 IF x_lsqv_rec.target_rate_type = FND_API.G_MISS_CHAR THEN
1772 x_lsqv_rec.target_rate_type := l_db_lsqv_rec.target_rate_type;
1773 END IF;
1774 IF x_lsqv_rec.target_rate = FND_API.G_MISS_NUM THEN
1775 x_lsqv_rec.target_rate := l_db_lsqv_rec.target_rate;
1776 END IF;
1777 IF x_lsqv_rec.target_amount = FND_API.G_MISS_NUM THEN
1778 x_lsqv_rec.target_amount := l_db_lsqv_rec.target_amount;
1779 END IF;
1780 IF x_lsqv_rec.target_frequency = FND_API.G_MISS_CHAR THEN
1781 x_lsqv_rec.target_frequency := l_db_lsqv_rec.target_frequency;
1782 END IF;
1783 IF x_lsqv_rec.target_arrears_yn = FND_API.G_MISS_CHAR THEN
1784 x_lsqv_rec.target_arrears_yn := l_db_lsqv_rec.target_arrears_yn;
1785 END IF;
1786 IF x_lsqv_rec.target_periods = FND_API.G_MISS_NUM THEN
1787 x_lsqv_rec.target_periods := l_db_lsqv_rec.target_periods;
1788 END IF;
1789 IF x_lsqv_rec.iir = FND_API.G_MISS_NUM THEN
1790 x_lsqv_rec.iir := l_db_lsqv_rec.iir;
1791 END IF;
1792 IF x_lsqv_rec.booking_yield = FND_API.G_MISS_NUM THEN
1793 x_lsqv_rec.booking_yield := l_db_lsqv_rec.booking_yield;
1794 END IF;
1795 IF x_lsqv_rec.pirr = FND_API.G_MISS_NUM THEN
1796 x_lsqv_rec.pirr := l_db_lsqv_rec.pirr;
1797 END IF;
1798 IF x_lsqv_rec.airr = FND_API.G_MISS_NUM THEN
1799 x_lsqv_rec.airr := l_db_lsqv_rec.airr;
1800 END IF;
1801 IF x_lsqv_rec.sub_iir = FND_API.G_MISS_NUM THEN
1802 x_lsqv_rec.sub_iir := l_db_lsqv_rec.sub_iir;
1803 END IF;
1804 IF x_lsqv_rec.sub_booking_yield = FND_API.G_MISS_NUM THEN
1805 x_lsqv_rec.sub_booking_yield := l_db_lsqv_rec.sub_booking_yield;
1806 END IF;
1807 IF x_lsqv_rec.sub_pirr = FND_API.G_MISS_NUM THEN
1808 x_lsqv_rec.sub_pirr := l_db_lsqv_rec.sub_pirr;
1809 END IF;
1810 IF x_lsqv_rec.sub_airr = FND_API.G_MISS_NUM THEN
1811 x_lsqv_rec.sub_airr := l_db_lsqv_rec.sub_airr;
1812 END IF;
1813 IF x_lsqv_rec.usage_category = FND_API.G_MISS_CHAR THEN
1814 x_lsqv_rec.usage_category := l_db_lsqv_rec.usage_category;
1815 END IF;
1816 IF x_lsqv_rec.usage_industry_class = FND_API.G_MISS_CHAR THEN
1817 x_lsqv_rec.usage_industry_class := l_db_lsqv_rec.usage_industry_class;
1818 END IF;
1819 IF x_lsqv_rec.usage_industry_code = FND_API.G_MISS_CHAR THEN
1820 x_lsqv_rec.usage_industry_code := l_db_lsqv_rec.usage_industry_code;
1821 END IF;
1822 IF x_lsqv_rec.usage_amount = FND_API.G_MISS_NUM THEN
1823 x_lsqv_rec.usage_amount := l_db_lsqv_rec.usage_amount;
1824 END IF;
1825 IF x_lsqv_rec.usage_location_id = FND_API.G_MISS_NUM THEN
1826 x_lsqv_rec.usage_location_id := l_db_lsqv_rec.usage_location_id;
1827 END IF;
1828 IF x_lsqv_rec.property_tax_applicable = FND_API.G_MISS_CHAR THEN
1829 x_lsqv_rec.property_tax_applicable := l_db_lsqv_rec.property_tax_applicable;
1830 END IF;
1831 IF x_lsqv_rec.property_tax_billing_type = FND_API.G_MISS_CHAR THEN
1832 x_lsqv_rec.property_tax_billing_type := l_db_lsqv_rec.property_tax_billing_type;
1833 END IF;
1834 IF x_lsqv_rec.upfront_tax_treatment = FND_API.G_MISS_CHAR THEN
1835 x_lsqv_rec.upfront_tax_treatment := l_db_lsqv_rec.upfront_tax_treatment;
1836 END IF;
1837 IF x_lsqv_rec.upfront_tax_stream_type = FND_API.G_MISS_NUM THEN
1838 x_lsqv_rec.upfront_tax_stream_type := l_db_lsqv_rec.upfront_tax_stream_type;
1839 END IF;
1840 IF x_lsqv_rec.transfer_of_title = FND_API.G_MISS_CHAR THEN
1841 x_lsqv_rec.transfer_of_title := l_db_lsqv_rec.transfer_of_title;
1842 END IF;
1843 IF x_lsqv_rec.age_of_equipment = FND_API.G_MISS_NUM THEN
1844 x_lsqv_rec.age_of_equipment := l_db_lsqv_rec.age_of_equipment;
1845 END IF;
1846 IF x_lsqv_rec.purchase_of_lease = FND_API.G_MISS_CHAR THEN
1847 x_lsqv_rec.purchase_of_lease := l_db_lsqv_rec.purchase_of_lease;
1848 END IF;
1849 IF x_lsqv_rec.sale_and_lease_back = FND_API.G_MISS_CHAR THEN
1850 x_lsqv_rec.sale_and_lease_back := l_db_lsqv_rec.sale_and_lease_back;
1851 END IF;
1852 IF x_lsqv_rec.interest_disclosed = FND_API.G_MISS_CHAR THEN
1853 x_lsqv_rec.interest_disclosed := l_db_lsqv_rec.interest_disclosed;
1854 END IF;
1855 IF x_lsqv_rec.primary_quote = FND_API.G_MISS_CHAR THEN
1856 x_lsqv_rec.primary_quote := l_db_lsqv_rec.primary_quote;
1857 END IF;
1858 --Bug # 5647107
1859 IF x_lsqv_rec.legal_entity_id = FND_API.G_MISS_NUM THEN
1860 x_lsqv_rec.legal_entity_id := l_db_lsqv_rec.legal_entity_id;
1861 END IF;
1862 --Bug # 5647107
1863 -- Bug 5908845. eBTax Enhancement Project
1864 IF x_lsqv_rec.line_intended_use = FND_API.G_MISS_CHAR THEN
1865 x_lsqv_rec.line_intended_use := l_db_lsqv_rec.line_intended_use;
1866 END IF;
1867 -- End Bug 5908845. eBTax Enhancement Project
1868 IF x_lsqv_rec.short_description = FND_API.G_MISS_CHAR THEN
1869 x_lsqv_rec.short_description := l_db_lsqv_rec.short_description;
1870 END IF;
1871 IF x_lsqv_rec.description = FND_API.G_MISS_CHAR THEN
1872 x_lsqv_rec.description := l_db_lsqv_rec.description;
1873 END IF;
1874 IF x_lsqv_rec.comments = FND_API.G_MISS_CHAR THEN
1875 x_lsqv_rec.comments := l_db_lsqv_rec.comments;
1876 END IF;
1877
1878 RETURN l_return_status;
1879
1880 EXCEPTION
1881
1882 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1883
1884 x_return_status := G_RET_STS_ERROR;
1885
1886 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1887
1888 x_return_status := G_RET_STS_UNEXP_ERROR;
1889
1890 WHEN OTHERS THEN
1891
1892 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1893 p_msg_name => G_DB_ERROR,
1894 p_token1 => G_PROG_NAME_TOKEN,
1895 p_token1_value => l_prog_name,
1896 p_token2 => G_SQLCODE_TOKEN,
1897 p_token2_value => sqlcode,
1898 p_token3 => G_SQLERRM_TOKEN,
1899 p_token3_value => sqlerrm);
1900
1901 x_return_status := G_RET_STS_UNEXP_ERROR;
1902
1903 END populate_new_record;
1904
1905 BEGIN
1906
1907 l_prog_name := G_PKG_NAME||'.update_row (V)';
1908
1909 l_return_status := populate_new_record (p_lsqv_rec, l_lsqv_rec);
1910
1911 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1912 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1913 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1914 RAISE OKL_API.G_EXCEPTION_ERROR;
1915 END IF;
1916
1917 l_return_status := validate_attributes (l_lsqv_rec,'UPDATE'); --Bug 7596781
1918
1919 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1920 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1921 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1922 RAISE OKL_API.G_EXCEPTION_ERROR;
1923 END IF;
1924
1925 l_return_status := validate_record (l_lsqv_rec);
1926
1927 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1928 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1929 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1930 RAISE OKL_API.G_EXCEPTION_ERROR;
1931 END IF;
1932
1933 migrate (l_lsqv_rec, l_lsq_rec);
1934 migrate (l_lsqv_rec, l_lsqtl_rec);
1935
1936 update_row (x_return_status => l_return_status, p_lsq_rec => l_lsq_rec);
1937
1938 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1939 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1940 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1941 RAISE OKL_API.G_EXCEPTION_ERROR;
1942 END IF;
1943
1944 update_row (x_return_status => l_return_status, p_lsqtl_rec => l_lsqtl_rec);
1945
1946 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1947 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1948 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1949 RAISE OKL_API.G_EXCEPTION_ERROR;
1950 END IF;
1951
1952 x_return_status := l_return_status;
1953 x_lsqv_rec := l_lsqv_rec;
1954
1955 EXCEPTION
1956
1957 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1958
1959 x_return_status := G_RET_STS_ERROR;
1960
1961 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1962
1963 x_return_status := G_RET_STS_UNEXP_ERROR;
1964
1965 WHEN OTHERS THEN
1966
1967 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1968 p_msg_name => G_DB_ERROR,
1969 p_token1 => G_PROG_NAME_TOKEN,
1970 p_token1_value => l_prog_name,
1971 p_token2 => G_SQLCODE_TOKEN,
1972 p_token2_value => sqlcode,
1973 p_token3 => G_SQLERRM_TOKEN,
1974 p_token3_value => sqlerrm);
1975
1976 x_return_status := G_RET_STS_UNEXP_ERROR;
1977
1978 END update_row;
1979
1980
1981 -----------------------------
1982 -- PROCEDURE update_row (REC)
1983 -----------------------------
1984 PROCEDURE update_row(
1985 p_api_version IN NUMBER,
1986 p_init_msg_list IN VARCHAR2,
1987 x_return_status OUT NOCOPY VARCHAR2,
1988 x_msg_count OUT NOCOPY NUMBER,
1989 x_msg_data OUT NOCOPY VARCHAR2,
1990 p_lsqv_rec IN lsqv_rec_type,
1991 x_lsqv_rec OUT NOCOPY lsqv_rec_type) IS
1992
1993 l_return_status VARCHAR2(1);
1994
1995 l_prog_name VARCHAR2(61);
1996
1997 BEGIN
1998
1999 l_prog_name := G_PKG_NAME||'.update_row (REC)';
2000
2001 IF p_init_msg_list = G_TRUE THEN
2002 FND_MSG_PUB.initialize;
2003 END IF;
2004
2005 update_row (x_return_status => l_return_status,
2006 p_lsqv_rec => p_lsqv_rec,
2007 x_lsqv_rec => x_lsqv_rec);
2008
2009 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2010 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2011 ELSIF l_return_status = G_RET_STS_ERROR THEN
2012 RAISE OKL_API.G_EXCEPTION_ERROR;
2013 END IF;
2014
2015 x_return_status := l_return_status;
2016
2017 EXCEPTION
2018
2019 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2023 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2020
2021 x_return_status := G_RET_STS_ERROR;
2022
2024
2025 x_return_status := G_RET_STS_UNEXP_ERROR;
2026
2027 WHEN OTHERS THEN
2028
2029 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2030 p_msg_name => G_DB_ERROR,
2031 p_token1 => G_PROG_NAME_TOKEN,
2032 p_token1_value => l_prog_name,
2033 p_token2 => G_SQLCODE_TOKEN,
2034 p_token2_value => sqlcode,
2035 p_token3 => G_SQLERRM_TOKEN,
2036 p_token3_value => sqlerrm);
2037
2038 x_return_status := G_RET_STS_UNEXP_ERROR;
2039
2040 END update_row;
2041
2042
2043 -----------------------------
2044 -- PROCEDURE update_row (TBL)
2045 -----------------------------
2046 PROCEDURE update_row(
2047 p_api_version IN NUMBER,
2048 p_init_msg_list IN VARCHAR2,
2049 x_return_status OUT NOCOPY VARCHAR2,
2050 x_msg_count OUT NOCOPY NUMBER,
2051 x_msg_data OUT NOCOPY VARCHAR2,
2052 p_lsqv_tbl IN lsqv_tbl_type,
2053 x_lsqv_tbl OUT NOCOPY lsqv_tbl_type) IS
2054
2055 l_return_status VARCHAR2(1);
2056 i BINARY_INTEGER;
2057 l_prog_name VARCHAR2(61);
2058
2059 BEGIN
2060
2061 l_prog_name := G_PKG_NAME||'.update_row (TBL)';
2062
2063 IF p_init_msg_list = G_TRUE THEN
2064 FND_MSG_PUB.initialize;
2065 END IF;
2066
2067 x_lsqv_tbl := p_lsqv_tbl;
2068
2069 IF (p_lsqv_tbl.COUNT > 0) THEN
2070
2071 i := p_lsqv_tbl.FIRST;
2072
2073 LOOP
2074
2075 IF p_lsqv_tbl.EXISTS(i) THEN
2076 update_row (x_return_status => l_return_status,
2077 p_lsqv_rec => p_lsqv_tbl(i),
2078 x_lsqv_rec => x_lsqv_tbl(i));
2079
2080 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2081 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2082 ELSIF l_return_status = G_RET_STS_ERROR THEN
2083 RAISE OKL_API.G_EXCEPTION_ERROR;
2084 END IF;
2085
2086 EXIT WHEN (i = p_lsqv_tbl.LAST);
2087 i := p_lsqv_tbl.NEXT(i);
2088
2089 END IF;
2090
2091 END LOOP;
2092
2093 ELSE
2094
2095 l_return_status := G_RET_STS_SUCCESS;
2096
2097 END IF;
2098
2099 x_return_status := l_return_status;
2100
2101 EXCEPTION
2102
2103 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2104
2105 x_return_status := G_RET_STS_ERROR;
2106
2107 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2108
2109 x_return_status := G_RET_STS_UNEXP_ERROR;
2110
2111 WHEN OTHERS THEN
2112
2113 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2114 p_msg_name => G_DB_ERROR,
2115 p_token1 => G_PROG_NAME_TOKEN,
2116 p_token1_value => l_prog_name,
2117 p_token2 => G_SQLCODE_TOKEN,
2118 p_token2_value => sqlcode,
2119 p_token3 => G_SQLERRM_TOKEN,
2120 p_token3_value => sqlerrm);
2121
2122 x_return_status := G_RET_STS_UNEXP_ERROR;
2123
2124 END update_row;
2125
2126
2127 -----------------
2128 -- delete_row (V)
2129 -----------------
2130 PROCEDURE delete_row(
2131 x_return_status OUT NOCOPY VARCHAR2,
2132 p_id IN NUMBER) IS
2133
2134 l_prog_name VARCHAR2(61);
2135
2136 BEGIN
2137
2138 l_prog_name := G_PKG_NAME||'.delete_row (V)';
2139
2140 DELETE FROM OKL_LEASE_QUOTES_B WHERE id = p_id;
2141 DELETE FROM OKL_LEASE_QUOTES_TL WHERE id = p_id;
2142
2143 x_return_status := G_RET_STS_SUCCESS;
2144
2145 EXCEPTION
2146
2147 WHEN OTHERS THEN
2148
2149 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2150 p_msg_name => G_DB_ERROR,
2151 p_token1 => G_PROG_NAME_TOKEN,
2152 p_token1_value => l_prog_name,
2153 p_token2 => G_SQLCODE_TOKEN,
2154 p_token2_value => sqlcode,
2155 p_token3 => G_SQLERRM_TOKEN,
2156 p_token3_value => sqlerrm);
2157
2158 x_return_status := G_RET_STS_UNEXP_ERROR;
2159
2160 END delete_row;
2161
2162
2163 -----------------------------
2164 -- PROCEDURE delete_row (REC)
2165 -----------------------------
2166 PROCEDURE delete_row(
2167 p_api_version IN NUMBER,
2168 p_init_msg_list IN VARCHAR2,
2169 x_return_status OUT NOCOPY VARCHAR2,
2170 x_msg_count OUT NOCOPY NUMBER,
2171 x_msg_data OUT NOCOPY VARCHAR2,
2172 p_lsqv_rec IN lsqv_rec_type) IS
2173
2174 l_return_status VARCHAR2(1);
2175
2176 l_prog_name VARCHAR2(61);
2177
2178 BEGIN
2179
2180 l_prog_name := G_PKG_NAME||'.delete_row (REC)';
2181
2182 IF p_init_msg_list = G_TRUE THEN
2183 FND_MSG_PUB.initialize;
2184 END IF;
2185
2186 delete_row (x_return_status => l_return_status,
2187 p_id => p_lsqv_rec.id);
2188
2189 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2190 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2191 ELSIF l_return_status = G_RET_STS_ERROR THEN
2192 RAISE OKL_API.G_EXCEPTION_ERROR;
2193 END IF;
2194
2195 x_return_status := l_return_status;
2196
2197 EXCEPTION
2198
2199 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2200
2201 x_return_status := G_RET_STS_ERROR;
2202
2203 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2204
2205 x_return_status := G_RET_STS_UNEXP_ERROR;
2206
2207 WHEN OTHERS THEN
2208
2209 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2210 p_msg_name => G_DB_ERROR,
2211 p_token1 => G_PROG_NAME_TOKEN,
2212 p_token1_value => l_prog_name,
2213 p_token2 => G_SQLCODE_TOKEN,
2214 p_token2_value => sqlcode,
2215 p_token3 => G_SQLERRM_TOKEN,
2216 p_token3_value => sqlerrm);
2217
2218 x_return_status := G_RET_STS_UNEXP_ERROR;
2219
2220 END delete_row;
2221
2222
2223 -------------------
2224 -- delete_row (TBL)
2225 -------------------
2226 PROCEDURE delete_row(
2227 p_api_version IN NUMBER,
2228 p_init_msg_list IN VARCHAR2,
2229 x_return_status OUT NOCOPY VARCHAR2,
2230 x_msg_count OUT NOCOPY NUMBER,
2231 x_msg_data OUT NOCOPY VARCHAR2,
2232 p_lsqv_tbl IN lsqv_tbl_type) IS
2233
2234 l_return_status VARCHAR2(1);
2235 i BINARY_INTEGER;
2236
2237 l_prog_name VARCHAR2(61);
2238
2239 BEGIN
2240
2241 l_prog_name := G_PKG_NAME||'.delete_row (TBL)';
2242
2243 IF p_init_msg_list = G_TRUE THEN
2244 FND_MSG_PUB.initialize;
2245 END IF;
2246
2247 IF (p_lsqv_tbl.COUNT > 0) THEN
2248
2249 i := p_lsqv_tbl.FIRST;
2250
2251 LOOP
2252
2253 IF p_lsqv_tbl.EXISTS(i) THEN
2254
2255 delete_row (x_return_status => l_return_status,
2256 p_id => p_lsqv_tbl(i).id);
2257
2258 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2259 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2260 ELSIF l_return_status = G_RET_STS_ERROR THEN
2261 RAISE OKL_API.G_EXCEPTION_ERROR;
2262 END IF;
2263
2264 EXIT WHEN (i = p_lsqv_tbl.LAST);
2265 i := p_lsqv_tbl.NEXT(i);
2266
2267 END IF;
2268
2269 END LOOP;
2270
2271 ELSE
2272
2273 l_return_status := G_RET_STS_SUCCESS;
2274
2275 END IF;
2276
2277 x_return_status := l_return_status;
2278
2279 EXCEPTION
2280
2281 WHEN OTHERS THEN
2282
2283 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2284 p_msg_name => G_DB_ERROR,
2285 p_token1 => G_PROG_NAME_TOKEN,
2286 p_token1_value => l_prog_name,
2287 p_token2 => G_SQLCODE_TOKEN,
2288 p_token2_value => sqlcode,
2289 p_token3 => G_SQLERRM_TOKEN,
2290 p_token3_value => sqlerrm);
2291
2292 x_return_status := G_RET_STS_UNEXP_ERROR;
2293
2294 END delete_row;
2295
2296
2297 END OKL_LSQ_PVT;