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