[Home] [Help]
PACKAGE BODY: APPS.OKL_LEASEAPP_TEMPLATE_PVT
Source
1 PACKAGE BODY OKL_LEASEAPP_TEMPLATE_PVT AS
2 /* $Header: OKLRLATB.pls 120.21.12010000.2 2008/12/20 01:37:21 smereddy ship $ */
3
4 -- bug 4741121 - smadhava - Modified - Start
5 G_PROF_FE_APPROVAL_PROCESS CONSTANT VARCHAR2(30) := 'OKL_SO_APPROVAL_PROCESS';
6 G_WF_EVT_LAT_PENDING CONSTANT wf_events.name%TYPE DEFAULT 'oracle.apps.okl.sales.leaseapplication.template_approved';
7 G_WF_LAT_VERSION_ID CONSTANT VARCHAR2(50) := 'VERSION_ID';
8 -- bug 4741121 - smadhava - Modified - Start
9
10 -------------------------------------------------------------------------------
11 -- FUNCTION check_unique_combination
12 -------------------------------------------------------------------------------
13 -- Start of comments
14 --
15 -- Procedure Name : check_unique_combination
16 -- Description : Check unique combination of fields Lease Application Type,
17 -- : Credit Classification,Credit Classification, Industry,
18 -- : Contract Template and Contract Template while creation and
19 -- : updation of Lease application Templates
20 --
21 -- Business Rules :
22 --
23 -- Parameters :
24 -- Version : 1.0
25 -- History : 20-MAY-2005 SKGAUTAM created
26 -- End of comments
27 FUNCTION check_unique_combination(p_latv_rec IN latv_rec_type,
28 p_lavv_rec IN lavv_rec_type)
29 RETURN VARCHAR2 IS
30
31 -- Variables Declarations
32 l_api_version CONSTANT NUMBER DEFAULT 1.0;
33 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'CHECK_UNIQUE_COMBINATION';
34 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
35 l_check_tmplt_comb VARCHAR2(1);
36 CURSOR c_chk_tmlt_cmbntn IS
37 SELECT 'x'
38 FROM Okl_Leaseapp_Templates LATV,
39 OKL_LEASEAPP_TEMPL_VERSIONS_V LAVV
40 WHERE LATV.ID = LAVV.LEASEAPP_TEMPLATE_ID
41 AND LATV.CREDIT_REVIEW_PURPOSE = p_latv_rec.CREDIT_REVIEW_PURPOSE
42 AND LATV.CUST_CREDIT_CLASSIFICATION = p_latv_rec.CUST_CREDIT_CLASSIFICATION
43 -- Bug 5149659 udhenuko : Start - Uncommenting industry values check
44 --Commented the industry values for checking uniqueness due to issue with data model
45 --industry values are stired at header and it will be same always accross versions
46 --for a given template
47 AND NVL(LATV.INDUSTRY_CODE, OKL_API.G_MISS_CHAR) = NVL(p_latv_rec.INDUSTRY_CODE, OKL_API.G_MISS_CHAR)
48 AND NVL(LATV.INDUSTRY_CLASS, OKL_API.G_MISS_CHAR) = NVL(p_latv_rec.INDUSTRY_CLASS, OKL_API.G_MISS_CHAR)
49 -- Bug 5149659 udhenuko : End
50 AND NVL(LAVV.CONTRACT_TEMPLATE_ID, OKL_API.G_MISS_NUM) = NVL(p_lavv_rec.CONTRACT_TEMPLATE_ID, OKL_API.G_MISS_NUM)
51 AND NVL(LAVV.CHECKLIST_ID, OKL_API.G_MISS_NUM) = NVL(p_lavv_rec.CHECKLIST_ID, OKL_API.G_MISS_NUM)
52 AND LAVV.VERSION_STATUS = 'ACTIVE'
53 AND LAVV.ID <> nvl(p_lavv_rec.id,-99999);
54 BEGIN
55 -- check for unique Lease Application Template
56 OPEN c_chk_tmlt_cmbntn;
57 FETCH c_chk_tmlt_cmbntn INTO l_check_tmplt_comb;
58 CLOSE c_chk_tmlt_cmbntn;
59
60 IF l_check_tmplt_comb = 'x' THEN
61 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
62 p_msg_name => 'OKL_SO_LSEAPP_TMPLT_NOT_UNIQUE');
63 -- notify caller of an error
64 l_return_status := OKL_API.G_RET_STS_ERROR;
65 END IF;
66 RETURN(l_return_status);
67 EXCEPTION
68 -- other appropriate handlers
69 WHEN OTHERS THEN
70 -- store SQL error message on message stack
71 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
72 p_msg_name => G_UNEXPECTED_ERROR,
73 p_token1 => G_SQLCODE_TOKEN,
74 p_token1_value => sqlcode,
75 p_token2 => G_SQLERRM_TOKEN,
76 p_token2_value => sqlerrm);
77
78 -- notify UNEXPECTED error
79 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
80 RETURN l_return_status;
81 END check_unique_combination;
82
83 -- bug 4741121 - smadhava - Added - Start
84 -- Start of comments
85 --
86 -- Procedure Name : activate_lat
87 -- Description : Procedure to change the status of LAT once the workflow is approved
88 -- Business Rules : The LAT version and header statuses are moved to ACTIVE.
89 -- Parameters :
90 -- Version : 1.0
91 -- End of comments
92 PROCEDURE activate_lat (p_api_version IN NUMBER,
93 p_init_msg_list IN VARCHAR2,
94 x_return_status OUT NOCOPY VARCHAR2,
95 x_msg_count OUT NOCOPY NUMBER,
96 x_msg_data OUT NOCOPY VARCHAR2,
97 p_lat_version_id IN NUMBER)IS
98 l_api_name CONSTANT VARCHAR2(40) := 'activate_lat';
99 l_api_version CONSTANT NUMBER := p_api_version;
100 l_init_msg_list VARCHAR2(1) := p_init_msg_list;
101 l_msg_count NUMBER := x_msg_count ;
102 l_msg_data VARCHAR2(2000);
103 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
104
105 l_latv_rec latv_rec_type;
106 lx_latv_rec latv_rec_type;
107 l_lavv_rec lavv_rec_type;
108 lx_lavv_rec lavv_rec_type;
109 l_parameter_list wf_parameter_list_t;
110 p_event_name VARCHAR2(240) := 'oracle.apps.okl.sales.leaseapplication.template_activated';
111
112 l_chk_vers_sts VARCHAR2(1);
113
114 -- cursor to get the LAT header information of the particular version
115 CURSOR c_get_lat_data( cp_lav_id OKL_LEASEAPP_TEMPL_VERSIONS_V.ID%TYPE) IS
116 SELECT LATV.ID LAT_HDR_ID
117 , LATV.OBJECT_VERSION_NUMBER HDR_OBJ_VER_NO
118 , LAVV.OBJECT_VERSION_NUMBER VER_OBJ_VER_NO
119 FROM OKL_LEASEAPP_TEMPLATES LATV,
120 OKL_LEASEAPP_TEMPL_VERSIONS_V LAVV
121 WHERE LATV.ID = LAVV.LEASEAPP_TEMPLATE_ID
122 AND LAVV.ID = cp_lav_id;
123
124 -- cursor to check if all the versions of the LAT are active
125 CURSOR c_chk_ver_sts( cp_lat_id OKL_LEASEAPP_TEMPLATES.ID%TYPE) IS
126 SELECT 'X'
127 FROM OKL_LEASEAPP_TEMPL_VERSIONS_V LAVV
128 WHERE LAVV.LEASEAPP_TEMPLATE_ID = cp_lat_id
129 AND LAVV.VERSION_STATUS <> G_STATUS_ACTIVE;
130
131 l_module CONSTANT fnd_log_messages.module%TYPE :='okl.plsql.OKL_LEASEAPP_TEMPLATE_PVT.activate_lat';
132 l_debug_enabled VARCHAR2(10);
133 is_debug_procedure_on BOOLEAN;
134 is_debug_statement_on BOOLEAN;
135
136 BEGIN
137 x_return_status := OKL_API.G_RET_STS_SUCCESS;
138 -- check for logging on PROCEDURE level
139 l_debug_enabled := okl_debug_pub.check_log_enabled;
140 -- call START_ACTIVITY to create savepoint, check compatibility
141 -- and initialize message list
142 l_return_status := OKL_API.START_ACTIVITY( p_api_name => l_api_name
143 ,p_pkg_name => G_PKG_NAME
144 ,p_init_msg_list => p_init_msg_list
145 ,l_api_version => l_api_version
146 ,p_api_version => p_api_version
147 ,p_api_type => g_api_type
148 ,x_return_status => x_return_status);
149 -- check if activity started successfully
150 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
151 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
152 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
153 RAISE OKL_API.G_EXCEPTION_ERROR;
154 END IF;
155
156 OPEN c_get_lat_data(p_lat_version_id);
157 FETCH c_get_lat_data INTO l_latv_rec.id
158 , l_latv_rec.object_version_number
159 , l_lavv_rec.object_version_number;
160 CLOSE c_get_lat_data;
161
162 -- Change the status of the version and header to ACTIVE
163 l_lavv_rec.version_status := G_STATUS_ACTIVE;
164 l_latv_rec.template_status := G_STATUS_ACTIVE;
165
166 l_lavv_rec.id := p_lat_version_id;
167 -- call the TAPI insert_row to update lease application template version
168 OKL_LAV_PVT.update_row(p_api_version => p_api_version
169 ,p_init_msg_list => p_init_msg_list
170 ,x_return_status => l_return_status
171 ,x_msg_count => x_msg_count
172 ,x_msg_data => x_msg_data
173 ,p_lavv_rec => l_lavv_rec
174 ,x_lavv_rec => lx_lavv_rec);
175 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
176 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
177 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
178 RAISE OKL_API.G_EXCEPTION_ERROR;
179 END IF;
180
181 OPEN c_chk_ver_sts(l_latv_rec.ID);
182 FETCH c_chk_ver_sts INTO l_chk_vers_sts;
183 CLOSE c_chk_ver_sts;
184
185 IF l_chk_vers_sts IS NULL THEN
186
187 -- call the TAPI update_row to update lease application template
188 OKL_LAT_PVT.update_row(p_api_version => p_api_version
189 ,p_init_msg_list => p_init_msg_list
190 ,x_return_status => l_return_status
191 ,x_msg_count => x_msg_count
192 ,x_msg_data => x_msg_data
193 ,p_latv_rec => l_latv_rec
194 ,x_latv_rec => lx_latv_rec);
195
196 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
197 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
198 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
199 RAISE OKL_API.G_EXCEPTION_ERROR;
200 END IF;
201
202 END IF;
203 -- raise the business event passing the Template id and the version id added to the parameter list
204 wf_event.addparametertolist('LAT_ID'
205 ,l_latv_rec.ID
206 ,l_parameter_list);
207
208 wf_event.addparametertolist('LAT_VERSION_ID'
209 ,p_lat_version_id
210 ,l_parameter_list);
211
212 okl_wf_pvt.raise_event( p_api_version => p_api_version
213 ,p_init_msg_list => p_init_msg_list
214 ,x_return_status => x_return_status
215 ,x_msg_count => x_msg_count
216 ,x_msg_data => x_msg_data
217 ,p_event_name => p_event_name
218 ,p_parameters => l_parameter_list);
219
220 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
221 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
222 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
223 RAISE OKL_API.G_EXCEPTION_ERROR;
224 END IF;
225
226 x_return_status := l_return_status;
227 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count
228 ,x_msg_data => x_msg_data);
229
230 EXCEPTION
231 WHEN OKL_API.G_EXCEPTION_ERROR THEN
232 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
233 p_api_name => l_api_name,
234 p_pkg_name => G_PKG_NAME,
235 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
236 x_msg_count => x_msg_count,
237 x_msg_data => x_msg_data,
238 p_api_type => g_api_type);
239 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
240 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
241 p_api_name => l_api_name,
242 p_pkg_name => G_PKG_NAME,
243 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
244 x_msg_count => x_msg_count,
245 x_msg_data => x_msg_data,
246 p_api_type => g_api_type);
247 WHEN OTHERS THEN
248 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
249 p_api_name => l_api_name,
250 p_pkg_name => G_PKG_NAME,
251 p_exc_name => 'OTHERS',
252 x_msg_count => x_msg_count,
253 x_msg_data => x_msg_data,
254 p_api_type => g_api_type);
255 END activate_lat;
256 -- bug 4741121 - smadhava - Added - End
257
258 -------------------------------------------------------------------------------
259 -- PROCEDURE create_lease_app_template
260 -------------------------------------------------------------------------------
261 -- Start of comments
262 --
263 -- Procedure Name : create_lease_app_template
264 -- Description : This procedure is a wrapper that creates transaction records for
265 -- : lease application template.
266 --
267 -- Business Rules : this procedure is used to create lease application template
268 -- this procedure inserts records into the OKL_LEASEAPP_TEMPLATES and
269 -- OKL_LEASEAPP_TEMPL_VERSIONS_B table
270 --
271 -- Parameters :
272 -- Version : 1.0
273 -- History : 12-MAY-2005 SKGAUTAM created
274 -- End of comments
275 PROCEDURE create_leaseapp_template(p_api_version IN NUMBER,
276 p_init_msg_list IN VARCHAR2,
277 x_return_status OUT NOCOPY VARCHAR2,
278 x_msg_count OUT NOCOPY NUMBER,
279 x_msg_data OUT NOCOPY VARCHAR2,
280 p_latv_rec IN latv_rec_type,
281 x_latv_rec OUT NOCOPY latv_rec_type,
282 p_lavv_rec IN lavv_rec_type,
283 x_lavv_rec OUT NOCOPY lavv_rec_type)IS
284 -- Variables Declarations
285 l_api_version CONSTANT NUMBER DEFAULT 1.0;
286 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'CREATE_LEASEAPP_TEMPLATE';
287 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
288
289 -- Record/Table Type Declarations
290 l_latv_rec latv_rec_type;
291 lx_latv_rec latv_rec_type;
292 l_lavv_rec lavv_rec_type;
293 lx_lavv_rec lavv_rec_type;
294 l_debug_enabled VARCHAR2(10);
295 BEGIN
296 x_return_status := OKL_API.G_RET_STS_SUCCESS;
297 -- check for logging on PROCEDURE level
298 l_debug_enabled := okl_debug_pub.check_log_enabled;
299 -- call START_ACTIVITY to create savepoint, check compatibility
300 -- and initialize message list
301 l_return_status := OKL_API.START_ACTIVITY( p_api_name => l_api_name
302 ,p_pkg_name => G_PKG_NAME
303 ,p_init_msg_list => p_init_msg_list
304 ,l_api_version => l_api_version
305 ,p_api_version => p_api_version
306 ,p_api_type => g_api_type
307 ,x_return_status => x_return_status);
308 -- check if activity started successfully
309 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
310 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
311 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
312 RAISE OKL_API.G_EXCEPTION_ERROR;
313 END IF;
314 l_latv_rec := p_latv_rec;
315 l_lavv_rec := p_lavv_rec;
316
317 --set the version and status for lease application template
318 l_latv_rec.id := null;
319 l_lavv_rec.id := null;
320 l_lavv_rec.version_number := 1;
321 l_lavv_rec.version_status := G_INIT_TMPLT_STATUS;
322
323 --Synchonrize the template header effective dates and status with
324 --headers version details
325
326 --Begin- varangan-bug#4684557 - Version From date validation
327 l_lavv_rec.valid_from:=TRUNC(l_lavv_rec.valid_from);
328 l_lavv_rec.valid_to:=TRUNC(l_lavv_rec.valid_to);
329 --End- varangan-bug#4684557 - Version From date validation
330
331 l_latv_rec.valid_from := l_lavv_rec.valid_from;
332 l_latv_rec.valid_to := l_lavv_rec.valid_to;
333 l_latv_rec.template_status := l_lavv_rec.version_status;
334
335 --Bug # 5189655 ssdeshpa start
336 --Removing check_unique_combination while create/update/duplicate of LAT
337
338 --call function to check the unique combination for lease application template
339 /*l_return_status := check_unique_combination(l_latv_rec,
340 l_lavv_rec);
341 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
342 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
343 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
344 RAISE OKL_API.G_EXCEPTION_ERROR;
345 END IF; */
346 --Bug # 5189655 ssdeshpa end
347
348 IF(l_debug_enabled='Y') THEN
349 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LAT_PVT.INSERT_ROW'
350 ,'begin debug OKLSLATB.pls call insert_row');
351 END IF;
352 -- call the TAPI insert_row to create a lease application template
353 OKL_LAT_PVT.insert_row(p_api_version => p_api_version
354 ,p_init_msg_list => p_init_msg_list
355 ,x_return_status => x_return_status
356 ,x_msg_count => x_msg_count
357 ,x_msg_data => x_msg_data
358 ,p_latv_rec => l_latv_rec
359 ,x_latv_rec => lx_latv_rec);
360 IF(l_debug_enabled='Y') THEN
361 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LAT_PVT.INSERT_ROW'
362 ,'end debug OKLSLATB.pls call insert_row');
363 END IF;
364 -- write to log
365 IF(NVL(l_debug_enabled,'N')='Y') THEN
366 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,'okl.plsql.OKL_LAT_PVT.INSERT_ROW',
367 ' l_latv_name ' || l_latv_rec.NAME ||
368 ' expiring lease application template with ret status ' || x_return_status ||
369 ' x_msg_data ' || x_msg_data);
370 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
371
372 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
373 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
374 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR)THEN
375 RAISE OKL_API.G_EXCEPTION_ERROR;
376 END IF;
377
378 l_lavv_rec.LEASEAPP_TEMPLATE_ID := lx_latv_rec.ID;
379
380 IF(l_debug_enabled='Y') THEN
381 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LAV_PVT.INSERT_ROW'
382 ,'begin debug OKLSLAVB.pls call insert_row');
383 END IF;
384 -- call the TAPI insert_row to create a lease application template version details
385 OKL_LAV_PVT.insert_row(p_api_version => p_api_version
386 ,p_init_msg_list => p_init_msg_list
387 ,x_return_status => x_return_status
388 ,x_msg_count => x_msg_count
389 ,x_msg_data => x_msg_data
390 ,p_lavv_rec => l_lavv_rec
391 ,x_lavv_rec => lx_lavv_rec);
392 IF(l_debug_enabled='Y') THEN
393 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LAV_PVT.INSERT_ROW'
394 ,'end debug OKLSLAVB.pls call insert_row');
395 END IF;
396 -- write to log
397 IF(NVL(l_debug_enabled,'N')='Y') THEN
398 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,'okl.plsql.OKL_LAV_PVT.INSERT_ROW',
399 'l_lavv_rec.version_number '||to_char(l_lavv_rec.version_number)||
400 'l_lavv_rec.version_status '||l_lavv_rec.version_status||
401 ' expiring lease application template with ret status '||x_return_status||' x_msg_data '||x_msg_data);
402 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
403
404 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
405 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
406 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR)THEN
407 RAISE OKL_API.G_EXCEPTION_ERROR;
408 END IF;
409 x_latv_rec := lx_latv_rec;
410 x_lavv_rec := lx_lavv_rec;
411 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count
412 ,x_msg_data => x_msg_data);
413 EXCEPTION
414 WHEN OKL_API.G_EXCEPTION_ERROR THEN
415 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
416 p_api_name => l_api_name,
417 p_pkg_name => G_PKG_NAME,
418 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
419 x_msg_count => x_msg_count,
420 x_msg_data => x_msg_data,
421 p_api_type => g_api_type);
422 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
423 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
424 p_api_name => l_api_name,
425 p_pkg_name => G_PKG_NAME,
426 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
427 x_msg_count => x_msg_count,
428 x_msg_data => x_msg_data,
429 p_api_type => g_api_type);
430 WHEN OTHERS THEN
431 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
432 p_api_name => l_api_name,
433 p_pkg_name => G_PKG_NAME,
434 p_exc_name => 'OTHERS',
435 x_msg_count => x_msg_count,
436 x_msg_data => x_msg_data,
437 p_api_type => g_api_type);
438 END create_leaseapp_template;
439
440 -------------------------------------------------------------------------------
441 -- PROCEDURE upadte_lease_app_template
442 -------------------------------------------------------------------------------
443 -- Start of comments
444 --
445 -- Procedure Name : update_lease_app_template
446 -- Description : This procedure is a wrapper thatupdates the transaction records for
447 -- : lease application template.
448 --
449 -- Business Rules : this procedure is used to update lease application template
450 -- this procedure updates records of the OKL_LEASEAPP_TEMPLATES and
451 -- OKL_LEASEAPP_TEMPL_VERSIONS_B table.
452 --
453 -- Parameters :
454 -- Version : 1.0
455 -- History : 12-MAY-2005 SKGAUTAM created
456 -- End of comments
457 PROCEDURE update_leaseapp_template(p_api_version IN NUMBER,
458 p_init_msg_list IN VARCHAR2,
459 x_return_status OUT NOCOPY VARCHAR2,
460 x_msg_count OUT NOCOPY NUMBER,
461 x_msg_data OUT NOCOPY VARCHAR2,
462 p_latv_rec IN latv_rec_type,
463 x_latv_rec OUT NOCOPY latv_rec_type,
464 p_lavv_rec IN lavv_rec_type,
465 x_lavv_rec OUT NOCOPY lavv_rec_type,
466 p_ident_flag IN VARCHAR2)IS
467 -- Variables Declarations
468 l_api_version CONSTANT NUMBER DEFAULT 1.0;
469 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'UPDATE_LEASEAPP_TEMPLATE';
470 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
471 -- Record/Table Type Declarations
472
473 -- Begin - bug#4632503 - varangan- 11-Oct-2005
474 l_source_type CONSTANT VARCHAR2(30) DEFAULT 'LEASE_APPL_TEMPLATE';
475 -- End - bug#4632503 - varangan- 11-Oct-2005
476
477 l_latv_rec latv_rec_type;
478 lx_latv_rec latv_rec_type;
479 l_lavv_rec lavv_rec_type;
480 lx_lavv_rec lavv_rec_type;
481 l_debug_enabled VARCHAR2(10);
482 l_valid_from DATE;
483 l_ident_flag VARCHAR2(10):= 'U';
484 l_max_start_date DATE;
485
486 --Get the min/max effective dates of versions to synchonize temlate header
487 --effective dates
488 CURSOR c_get_valid_dates( p_lat_id okl_leaseapp_templates.id%TYPE) IS
489 SELECT MIN(LAVV.valid_from) valid_from
490 FROM Okl_Leaseapp_Templates LATV,
491 okl_leaseapp_templ_versions_v LAVV
492 WHERE LATV.ID = LAVV.LEASEAPP_TEMPLATE_ID
493 AND LATV.ID = p_lat_id
494 GROUP BY LATV.ID;
495
496 -- Get the max start date from Lease App and Vendor Prog
497 -- for associated lease app template
498 CURSOR c_get_max_start_date( p_lat_id okl_leaseapp_templates.id%TYPE) IS
499 SELECT MAX(START_DATE) max_start_date
500 FROM
501 (
502 SELECT chr.START_DATE START_DATE
503 FROM okc_k_headers_b chr,
504 okl_vp_associations vpa,
505 Okl_Leaseapp_Templates lat,
506 okl_leaseapp_templ_versions_b lav
507 WHERE chr.scs_code = 'PROGRAM'
508 AND chr.sts_code = 'ACTIVE'
509 AND chr.id = vpa.chr_id
510 AND vpa.ASSOC_OBJECT_TYPE_CODE = 'LA_TEMPLATE'
511 AND vpa.ASSOC_OBJECT_ID = lat.ID
512 AND vpa.ASSOC_OBJECT_VERSION = lav.VERSION_NUMBER
513 AND LAT.ID = LAV.LEASEAPP_TEMPLATE_ID
514 AND lat.ID = p_lat_id
515 UNION
516 SELECT laa.VALID_FROM START_DATE
517 FROM OKL_LEASE_APPLICATIONS_B laa,
518 okl_leaseapp_templates lat,
519 okl_leaseapp_templ_versions_v lav
520 WHERE lat.id = lav.leaseapp_template_id
521 AND laa.LEASEAPP_TEMPLATE_ID = lav.ID
522 AND laa.APPLICATION_STATUS IN('CONV-CL','CONV-K','CR-APPROVED','CR-SUBMITTED',
523 'PR-ACCEPTED','PR-APPROVED','PR-SUBMITTED')
524 AND laa.VALID_FROM >= lav.VALID_FROM
525 AND lat.ID = p_lat_id
526 ) MY_START_DATE;
527
528 BEGIN
529 x_return_status := OKL_API.G_RET_STS_SUCCESS;
530 -- check for logging on PROCEDURE level
531 l_debug_enabled := okl_debug_pub.check_log_enabled;
532 -- call START_ACTIVITY to create savepoint, check compatibility
533 -- and initialize message list
534 l_return_status := OKL_API.START_ACTIVITY( p_api_name => l_api_name
535 ,p_pkg_name => G_PKG_NAME
536 ,p_init_msg_list => p_init_msg_list
537 ,l_api_version => l_api_version
538 ,p_api_version => p_api_version
539 ,p_api_type => g_api_type
540 ,x_return_status => x_return_status);
541 -- check if activity started successfully
542 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
543 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
544 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
545 RAISE OKL_API.G_EXCEPTION_ERROR;
546 END IF;
547 l_latv_rec := p_latv_rec;
548 l_lavv_rec := p_lavv_rec;
549 l_ident_flag := p_ident_flag;
550
551 --Synchonrize the template header effective dates and status with
552 --headers version details
553 OPEN c_get_valid_dates(l_latv_rec.ID);
554 FETCH c_get_valid_dates INTO l_valid_from;
555 CLOSE c_get_valid_dates;
556 IF l_ident_flag = 'U' THEN
557 IF l_lavv_rec.version_number = 1 THEN
558 l_latv_rec.valid_from := TRUNC(l_lavv_rec.valid_from); -- bug#4632503 - varangan- 11-Oct-2005
559 l_latv_rec.valid_to := TRUNC(l_lavv_rec.valid_to); -- bug#4632503 - varangan- 11-Oct-2005
560 l_latv_rec.template_status := G_INIT_TMPLT_STATUS;
561 ELSE
562 l_latv_rec.valid_from := TRUNC(l_valid_from); -- bug#4632503 - varangan- 11-Oct-2005
563 l_latv_rec.valid_to := TRUNC(l_lavv_rec.valid_to); -- bug#4632503 - varangan- 11-Oct-2005
564 l_latv_rec.template_status := G_STATUS_UNDERREVISION;
565 END IF;
566 ELSIF l_ident_flag = 'V' THEN
567 l_latv_rec.valid_from := TRUNC(l_valid_from); -- bug#4632503 - varangan- 11-Oct-2005
568 l_latv_rec.template_status := G_STATUS_UNDERREVISION;
569 ELSIF l_ident_flag = 'A' THEN
570 l_latv_rec.valid_from := TRUNC(l_valid_from); -- bug#4632503 - varangan- 11-Oct-2005
571 IF l_lavv_rec.version_status = G_STATUS_ACTIVE THEN
572 l_latv_rec.template_status := G_STATUS_ACTIVE;
573 -- bug 4741121 - smadhava - Modified - Start
574 -- Commented as Activation process before it calls a workflow for a single version LAT
575 -- should have the status of LAT header as 'NEW'
576 /*
577 ELSE
578 l_latv_rec.template_status := G_STATUS_UNDERREVISION;
579 */
580 -- bug 4741121 - smadhava - Modified - End
581 END IF;
582 ELSIF l_ident_flag = 'UA' THEN
583
584 IF l_lavv_rec.version_number = 1 THEN
585 l_latv_rec.valid_from := TRUNC(l_lavv_rec.valid_from); -- bug#4632503 - varangan- 11-Oct-2005
586 l_latv_rec.valid_to := TRUNC(l_lavv_rec.valid_to); -- bug#4632503 - varangan- 11-Oct-2005
587 l_latv_rec.template_status := G_STATUS_ACTIVE;
588 ELSE
589 l_latv_rec.valid_from := TRUNC(l_valid_from); -- bug#4632503 - varangan- 11-Oct-2005
590 l_latv_rec.valid_to := TRUNC(l_lavv_rec.valid_to); -- bug#4632503 - varangan- 11-Oct-2005
591 l_latv_rec.template_status := G_STATUS_ACTIVE;
592 END IF;
593 END IF;
594
595 --Bug # 5189655 ssdeshpa start
596 --Removing check_unique_combination while create/update/duplicate of LAT
597
598 --call function to check the unique combination for lease application template
599 /*l_return_status := check_unique_combination(l_latv_rec,
600 l_lavv_rec);
601 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
602 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
603 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
604 RAISE OKL_API.G_EXCEPTION_ERROR;
605 END IF; */
606 --Bug # 5189655 ssdeshpa end
607 --if we are updating an active version, check if the valid to date is greater than or equal to
608 --all the lease applications and vendor programs start date which uses this template
609 IF l_lavv_rec.version_status = G_STATUS_ACTIVE THEN
610 -- Fetch the Maximum of Start Date of the contracts using this template
611 FOR r_new_date_rec IN c_get_max_start_date(l_latv_rec.id)
612 LOOP
613 l_max_start_date := r_new_date_rec.max_start_date;
614 END LOOP;
615
616 IF( l_max_start_date IS NULL) THEN
617 -- If no start_date is found, then take the this versions start_date
618 -- as the max_Start_date
619 l_max_start_date := l_lavv_rec.valid_from;
620 END IF;
621 IF( l_max_start_date< l_lavv_rec.valid_from ) THEN
622 -- If no start_date is found, then take the this versions start_date
623 -- as the max_Start_date
624 l_max_start_date := l_lavv_rec.valid_from;
625 END IF;
626
627 IF l_lavv_rec.valid_to <> OKL_API.G_MISS_DATE AND l_lavv_rec.valid_to < l_max_start_date THEN
628 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
629 p_msg_name => 'OKL_LAT_INVALID_VALITO',
630 p_token1 => 'VALID_TO',
631 p_token1_value => l_max_start_date);
632 RAISE OKL_API.G_EXCEPTION_ERROR;
633
634 END IF;
635 -- l_latv_rec.template_status := G_STATUS_ACTIVE;
636 END IF;
637
638 IF ((l_latv_rec.ID IS NOT NULL OR l_latv_rec.id <> OKL_API.G_MISS_NUM)) THEN
639 IF(l_debug_enabled='Y') THEN
640 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LAT_PVT.UPADTE_ROW'
641 ,'begin debug OKLSLATB.pls call upadte_row');
642 END IF;
643 -- call the TAPI update_row to create a lease application template
644 OKL_LAT_PVT.update_row(p_api_version => p_api_version
645 ,p_init_msg_list => p_init_msg_list
646 ,x_return_status => x_return_status
647 ,x_msg_count => x_msg_count
648 ,x_msg_data => x_msg_data
649 ,p_latv_rec => l_latv_rec
650 ,x_latv_rec => lx_latv_rec);
651 IF(l_debug_enabled='Y') THEN
652 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LAT_PVT.UPADTE_ROW'
653 ,'end debug OKLSLATB.pls call upadte_row');
654 END IF;
655 -- write to log
656 IF(NVL(l_debug_enabled,'N')='Y') THEN
657 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,'okl.plsql.OKL_LAT_PVT.UPDATE_ROW',
658 'l_latv_name '||l_latv_rec.NAME
659 ||'lease application template with ret status '||x_return_status||' x_msg_data '||x_msg_data);
660 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
661
662 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
663 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
664 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR)THEN
665 RAISE OKL_API.G_EXCEPTION_ERROR;
666 END IF;
667 END IF;
668
669 IF ((l_lavv_rec.ID IS NOT NULL OR l_lavv_rec.id <> OKL_API.G_MISS_NUM)) THEN
670 IF(l_debug_enabled='Y') THEN
671 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LAV_PVT.UPADTE_ROW'
672 ,'begin debug OKLSLAVB.pls call upadte_row');
673 END IF;
674 -- call the TAPI insert_row to create a lease application template
675 OKL_LAV_PVT.update_row(p_api_version => p_api_version
676 ,p_init_msg_list => p_init_msg_list
677 ,x_return_status => x_return_status
678 ,x_msg_count => x_msg_count
679 ,x_msg_data => x_msg_data
680 ,p_lavv_rec => l_lavv_rec
681 ,x_lavv_rec => lx_lavv_rec);
682 IF(l_debug_enabled='Y') THEN
683 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LAV_PVT.UPADTE_ROW'
684 ,'end debug OKLSLAVB.pls call upadte_row');
685 END IF;
686 -- write to log
687 IF(NVL(l_debug_enabled,'N')='Y') THEN
688 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,'okl.plsql.OKL_LAV_PVT.UPDATE_ROW',
689 'l_lavv_rec.version_number '||to_char(l_lavv_rec.version_number) ||'l_lavv_rec.version_status '
690 ||l_lavv_rec.version_status
691 ||'lease application template with ret status '||x_return_status||'x_msg_data '||x_msg_data);
692 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
693 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
694 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
695 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR)THEN
696 RAISE OKL_API.G_EXCEPTION_ERROR;
697 END IF;
698 END IF;
699 -- Begin - Bug#4632503 - Updating Eligibility criteria's end date while updating version end date- varangan- 11-Oct-2005
700 OKL_ECC_VALUES_PVT.end_date_eligibility_criteria(p_api_version => p_api_version
701 ,p_init_msg_list => p_init_msg_list
702 ,x_return_status => x_return_status
703 ,x_msg_count => x_msg_count
704 ,x_msg_data => x_msg_data
705 ,p_source_id => l_lavv_rec.ID
706 ,p_source_type => l_source_type
707 ,p_end_date => l_lavv_rec.valid_to);
708 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
709 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
710 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR)THEN
711 RAISE OKL_API.G_EXCEPTION_ERROR;
712 END IF;
713 -- End - Bug#4632503 - Updating Eligibility criteria's end date while updating version end date- varangan- 11-Oct-2005
714 x_latv_rec := lx_latv_rec;
715 x_lavv_rec := lx_lavv_rec;
716 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count
717 ,x_msg_data => x_msg_data);
718 EXCEPTION
719 WHEN OKL_API.G_EXCEPTION_ERROR THEN
720 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
721 p_api_name => l_api_name,
722 p_pkg_name => G_PKG_NAME,
723 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
724 x_msg_count => x_msg_count,
725 x_msg_data => x_msg_data,
726 p_api_type => g_api_type);
727 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
728 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
729 p_api_name => l_api_name,
730 p_pkg_name => G_PKG_NAME,
731 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
732 x_msg_count => x_msg_count,
733 x_msg_data => x_msg_data,
734 p_api_type => g_api_type);
735 WHEN OTHERS THEN
736 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
737 p_api_name => l_api_name,
738 p_pkg_name => G_PKG_NAME,
739 p_exc_name => 'OTHERS',
740 x_msg_count => x_msg_count,
741 x_msg_data => x_msg_data,
742 p_api_type => g_api_type);
743 END update_leaseapp_template;
744
745 -------------------------------------------------------------------------------
746 -- PROCEDURE version_duplicate_leaseapp_template
747 -------------------------------------------------------------------------------
748 -- Start of comments
749 --
750 -- Procedure Name : version_duplicate_lseapp_tmpl
751 -- Description : This procedure is a wrapper that duplicates and versions records for
752 -- : lease application template.
753 --
754 -- Business Rules : this procedure is used to duplicate and version lease application template
755 -- this procedure inserts records into the OKL_LEASEAPP_TEMPLATES and
756 -- OKL_LEASEAPP_TEMPL_VERSIONS_B table
757 --
758 -- Parameters :
759 -- Version : 1.0
760 -- History : 13-MAY-2005 SKGAUTAM created
761 -- End of comments
762 PROCEDURE version_duplicate_lseapp_tmpl( p_api_version IN NUMBER,
763 p_init_msg_list IN VARCHAR2,
764 x_return_status OUT NOCOPY VARCHAR2,
765 x_msg_count OUT NOCOPY NUMBER,
766 x_msg_data OUT NOCOPY VARCHAR2,
767 p_latv_rec IN latv_rec_type,
768 x_latv_rec OUT NOCOPY latv_rec_type,
769 p_lavv_rec IN lavv_rec_type,
770 x_lavv_rec OUT NOCOPY lavv_rec_type,
771 p_mode IN VARCHAR2)IS
772 -- Variables Declarations
773 l_api_version CONSTANT NUMBER DEFAULT 1.0;
774 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'VERSION_DUP_LEASEAPP_TEMP';
775 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
776 -- Record/Table Type Declarations
777 l_latv_rec_old latv_rec_type;
778 l_latv_rec_new latv_rec_type;
779 x_latv_rec_old latv_rec_type;
780 x_latv_rec_new latv_rec_type;
781 lx_latv_rec latv_rec_type;
782 l_lavv_rec_old lavv_rec_type;
783 l_lavv_rec_new lavv_rec_type;
784 x_lavv_rec_old lavv_rec_type;
785 x_lavv_rec_new lavv_rec_type;
786 l_max_start_date DATE;
787 l_new_seq_value NUMBER;
788 l_debug_enabled VARCHAR2(10);
789 -- Declare Cursor Definations
790 --asawanka bug 4966317 fix starts
791 CURSOR get_latest_activever_dates(p_lat_id IN okl_leaseapp_templates.id%TYPE) IS
792 /* SELECT max(to_number(lav.version_number)), lav.valid_from,lav.valid_to
793 FROM okl_leaseapp_templates lat,
794 okl_leaseapp_templ_versions_v lav
795 WHERE lat.id = lav.leaseapp_template_id
796 AND lat.id = p_lat_id
797 group by lav.valid_from,lav.valid_to; */
798 --Fixed ssdeshpa Bug # 6487421 Start
799 SELECT LAV.VERSION_NUMBER
800 , LAV.VALID_FROM
801 , LAV.VALID_TO
802 FROM OKL_LEASEAPP_TEMPLATES LAT
803 , OKL_LEASEAPP_TEMPL_VERSIONS_B LAV
804 WHERE LAT.ID = LAV.LEASEAPP_TEMPLATE_ID
805 AND LAT.ID = p_lat_id
806 AND TO_NUMBER(LAV.VERSION_NUMBER) = (SELECT MAX(TO_NUMBER(LAV1.VERSION_NUMBER))
807 FROM OKL_LEASEAPP_TEMPL_VERSIONS_B LAV1
808 WHERE LAV1.LEASEAPP_TEMPLATE_ID = LAT.ID);
809 --Fixed ssdeshpa Bug # 6487421 End
810 --asawanka bug 4966317 fix ends
811 BEGIN
812 x_return_status := OKL_API.G_RET_STS_SUCCESS;
813 -- check for logging on PROCEDURE level
814 l_debug_enabled := okl_debug_pub.check_log_enabled;
815 -- call START_ACTIVITY to create savepoint, check compatibility
816 -- and initialize message list
817 l_return_status := OKL_API.START_ACTIVITY( p_api_name => l_api_name
818 ,p_pkg_name => G_PKG_NAME
819 ,p_init_msg_list => p_init_msg_list
820 ,l_api_version => l_api_version
821 ,p_api_version => p_api_version
822 ,p_api_type => g_api_type
823 ,x_return_status => x_return_status);
824 -- check if activity started successfully
825 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
826 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
827 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
828 RAISE OKL_API.G_EXCEPTION_ERROR;
829 END IF;
830 --Bug 5149659 PAGARG Populate old rec from DB for previous version update
831 l_latv_rec_new := p_latv_rec;
832 l_lavv_rec_new := p_lavv_rec;
833
834 IF p_mode IS NULL OR p_mode = G_DEFAULT_MODE THEN
835 -- Duplicates the lease application template
836 l_latv_rec_new.id := NULL;
837 l_lavv_rec_new.id := NULL;
838 --set the version and status for lease application template
839 l_lavv_rec_new.version_number := 1; -- New ID will be created
840 l_lavv_rec_new.version_status := G_INIT_TMPLT_STATUS; -- Make the New Template Status to NEW
841
842 --Begin- varangan-bug#4684557 - Version From date validation
843 l_lavv_rec_new.valid_from:=TRUNC(l_lavv_rec_new.valid_from);
844 l_lavv_rec_new.valid_to:=TRUNC(l_lavv_rec_new.valid_to);
845 --End- varangan-bug#4684557 - Version From date validation
846
847 l_latv_rec_new.valid_from := l_lavv_rec_new.valid_from;
848 l_latv_rec_new.valid_to := l_lavv_rec_new.valid_to;
849 l_latv_rec_new.template_status := l_lavv_rec_new.version_status;
850
851 IF(l_debug_enabled='Y') THEN
852 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LEASEAPP_TEMPLATE_PVT.create_leaseapp_template'
853 ,'begin debug OKLRLATB.pls call create_leaseapp_template');
854 END IF;
855 -- call the APLI to create a lease application template
856 create_leaseapp_template(p_api_version => p_api_version
857 ,p_init_msg_list => p_init_msg_list
858 ,x_return_status => x_return_status
859 ,x_msg_count => x_msg_count
860 ,x_msg_data => x_msg_data
861 ,p_latv_rec => l_latv_rec_new
862 ,x_latv_rec => x_latv_rec_new
863 ,p_lavv_rec => l_lavv_rec_new
864 ,x_lavv_rec => x_lavv_rec_new);
865 IF(l_debug_enabled='Y') THEN
866 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LEASEAPP_TEMPLATE_PVT.create_leaseapp_template'
867 ,'begin debug OKLRLATB.pls call create_leaseapp_template');
868 END IF;
869 -- write to log
870 IF(NVL(l_debug_enabled,'N')='Y') THEN
871 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,'okl.plsql.OKL_LEASEAPP_TEMPLATE_PVT.create_leaseapp_template',
872 ' l_latv_name '||l_latv_rec_new.NAME ||
873 ' expiring lease application template with ret status '||x_return_status||' x_msg_data '||x_msg_data );
874 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
875
876 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
877 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
878 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR)THEN
879 RAISE OKL_API.G_EXCEPTION_ERROR;
880 END IF;
881 x_latv_rec := x_latv_rec_new;
882 x_lavv_rec := x_lavv_rec_new;
883 ELSE
884 l_latv_rec_old := okl_lat_pvt.get_rec(p_latv_rec.id, l_return_status);
885 l_lavv_rec_old := okl_lav_pvt.get_rec(p_lavv_rec.id, l_return_status);
886 -- Fetch the Maximum of Start Date of the contracts using this template
887 max_valid_from_date(p_api_version => p_api_version
888 ,p_init_msg_list => p_init_msg_list
889 ,x_return_status => x_return_status
890 ,x_msg_count => x_msg_count
891 ,x_msg_data => x_msg_data
892 ,p_latv_rec => p_latv_rec
893 ,x_latv_rec => lx_latv_rec);
894
895 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
896 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
897 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR)THEN
898 RAISE OKL_API.G_EXCEPTION_ERROR;
899 END IF;
900
901 IF l_lavv_rec_new.valid_from < lx_latv_rec.valid_from THEN
902 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
903 p_msg_name => 'OKL_LAT_INVALID_VALIDFROM',
904 p_token1 => 'VALID_FROM',
905 p_token1_value => lx_latv_rec.valid_from);
906 RAISE OKL_API.G_EXCEPTION_ERROR;
907 END IF;
908 -- Modifications to be done for the current template version
909 -- Current version template end_date will be updated with
910 -- new version valid from -1
911 l_lavv_rec_old.valid_to := l_lavv_rec_new.valid_from -1;
912
913 FOR r_actver_dates_rec IN get_latest_activever_dates(p_latv_rec.id) LOOP
914 l_lavv_rec_old.valid_from := r_actver_dates_rec.valid_from;
915 END LOOP;
916 --asawanka bug 4966317 fix ends
917 IF(l_debug_enabled='Y') THEN
918 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LAT_PVT.UPADTE_ROW'
919 ,'begin debug OKLSLATB.pls call upadte_row');
920 END IF;
921
922 IF(l_debug_enabled='Y') THEN
923 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LEASEAPP_TEMPLATE_PVT.update_leaseapp_template'
924 ,'end debug OKLRLATB.pls call update_leaseapp_template');
925 END IF;
926 --Begin- varangan-bug#4684557 - Version From date validation
927 l_lavv_rec_old.valid_from:=TRUNC(l_lavv_rec_old.valid_from);
928 l_lavv_rec_old.valid_to:=TRUNC(l_lavv_rec_old.valid_to);
929 l_latv_rec_old.valid_from:=TRUNC(l_latv_rec_old.valid_from);
930 l_latv_rec_old.valid_to:=TRUNC(l_latv_rec_old.valid_to);
931 --End- varangan-bug#4684557 - Version From date validation
932
933 -- call the API to update a lease application template
934 update_leaseapp_template(p_api_version => p_api_version
935 ,p_init_msg_list => p_init_msg_list
936 ,x_return_status => x_return_status
937 ,x_msg_count => x_msg_count
938 ,x_msg_data => x_msg_data
939 ,p_latv_rec => l_latv_rec_old
940 ,x_latv_rec => x_latv_rec_old
941 ,p_lavv_rec => l_lavv_rec_old
942 ,x_lavv_rec => x_lavv_rec_old
943 ,p_ident_flag => 'V');
944 IF(l_debug_enabled='Y') THEN
945 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LEASEAPP_TEMPLATE_PVT.update_leaseapp_template'
946 ,'end debug OKLRLATB.pls call update_leaseapp_template');
947 END IF;
948 -- write to log
949 IF(NVL(l_debug_enabled,'N')='Y') THEN
950 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,'okl.plsql.OKL_LEASEAPP_TEMPLATE_PVT.update_leaseapp_template',
951 'l_latv_name '||l_latv_rec_old.NAME ||
952 'lease application template with ret status '||x_return_status||' x_msg_data '||x_msg_data);
953 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
954 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
955 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
956 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR)THEN
957 RAISE OKL_API.G_EXCEPTION_ERROR;
958 END IF;
959
960 -- Modifications for New Version
961 l_lavv_rec_new.version_number := l_lavv_rec_old.version_number + 1 ;
962 l_lavv_rec_new.id := NULL; -- New ID will be created
963 l_lavv_rec_new.version_status := G_INIT_TMPLT_STATUS; -- Make the New Template Status to NEW
964 l_lavv_rec_new.leaseapp_template_id := l_latv_rec_old.ID;
965
966 IF(l_debug_enabled='Y') THEN
967 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LAV_PVT.INSERT_ROW'
968 ,'begin debug OKLSLAVB.pls call insert_row');
969 END IF;
970
971 --Begin- varangan-bug#4684557 - Version From date validation
972 l_lavv_rec_new.valid_from := TRUNC(l_lavv_rec_new.valid_from);
973 l_lavv_rec_new.valid_to := TRUNC(l_lavv_rec_new.valid_to);
974 --Bug # 5189655 ssdeshpa start
975 --Removing check_unique_combination while create/update/duplicate of LAT
976
977 --End- varangan-bug#4684557 - Version From date validation
978 --call function to check the unique combination for lease application template
979 /* l_return_status := check_unique_combination(l_latv_rec_new,
980 l_lavv_rec_new);
981 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
982 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
983 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
984 RAISE OKL_API.G_EXCEPTION_ERROR;
985 END IF; */
986 --Bug # 5189655 ssdeshpa end
987 -- call the TAPI insert_row to create a lease application template version
988 OKL_LAV_PVT.insert_row(p_api_version => p_api_version
989 ,p_init_msg_list => p_init_msg_list
990 ,x_return_status => x_return_status
991 ,x_msg_count => x_msg_count
992 ,x_msg_data => x_msg_data
993 ,p_lavv_rec => l_lavv_rec_new
994 ,x_lavv_rec => x_lavv_rec_new);
995 IF(l_debug_enabled='Y') THEN
996 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,'okl.plsql.OKL_LAV_PVT.INSERT_ROW'
997 ,'end debug OKLSLAVB.pls call insert_row');
998 END IF;
999 -- write to log
1000 IF(NVL(l_debug_enabled,'N')='Y') THEN
1001 okl_debug_pub.log_debug(FND_LOG.LEVEL_STATEMENT,'okl.plsql.OKL_LAT_PVT.INSERT_ROW',
1002 ' l_latv_name '||l_latv_rec_old.NAME ||
1003 ' expiring lease application template with ret status '||x_return_status||' x_msg_data '||x_msg_data);
1004 END IF; -- end of NVL(l_debug_enabled,'N')='Y'
1005
1006 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)THEN
1007 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1008 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR)THEN
1009 RAISE OKL_API.G_EXCEPTION_ERROR;
1010 END IF;
1011 x_latv_rec := x_latv_rec_old;
1012 x_lavv_rec := x_lavv_rec_new;
1013 END IF;
1014
1015 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count
1016 ,x_msg_data => x_msg_data);
1017 EXCEPTION
1018 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1019 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1020 p_api_name => l_api_name,
1021 p_pkg_name => G_PKG_NAME,
1022 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1023 x_msg_count => x_msg_count,
1024 x_msg_data => x_msg_data,
1025 p_api_type => g_api_type);
1026 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1027 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1028 p_api_name => l_api_name,
1029 p_pkg_name => G_PKG_NAME,
1030 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1031 x_msg_count => x_msg_count,
1032 x_msg_data => x_msg_data,
1033 p_api_type => g_api_type);
1034 WHEN OTHERS THEN
1035 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1036 p_api_name => l_api_name,
1037 p_pkg_name => G_PKG_NAME,
1038 p_exc_name => 'OTHERS',
1039 x_msg_count => x_msg_count,
1040 x_msg_data => x_msg_data,
1041 p_api_type => g_api_type);
1042 END version_duplicate_lseapp_tmpl;
1043
1044 -------------------------------------------------------------------------------
1045 -- FUNCTION get_lookup_meaning
1046 -------------------------------------------------------------------------------
1047 -- Start of comments
1048 --
1049 -- Function Name : get_lookup_meaning
1050 -- Description : This function returns the lookup meaning for specified
1051 -- : lookup_code and lookup_type
1052 --
1053 -- Business Rules :
1054 --
1055 -- Parameters :
1056 -- Version : 1.0
1057 -- History : 12-MAY-2005 SKGAUTAM created
1058 -- End of comments
1059 FUNCTION get_lookup_meaning( p_lookup_type FND_LOOKUPS.LOOKUP_TYPE%TYPE
1060 ,p_lookup_code FND_LOOKUPS.LOOKUP_CODE%TYPE)
1061 RETURN VARCHAR IS
1062 CURSOR fnd_lookup_csr( p_lookup_type fnd_lookups.lookup_type%TYPE
1063 ,p_lookup_code fnd_lookups.lookup_code%TYPE) IS
1064 SELECT MEANING
1065 FROM FND_LOOKUPS FND
1066 WHERE FND.LOOKUP_TYPE = p_lookup_type
1067 AND FND.LOOKUP_CODE = p_lookup_code;
1068 l_return_value VARCHAR2(80) := NULL;
1069 BEGIN
1070 IF ( p_lookup_type IS NOT NULL AND p_lookup_code IS NOT NULL )THEN
1071 OPEN fnd_lookup_csr( p_lookup_type, p_lookup_code );
1072 FETCH fnd_lookup_csr INTO l_return_value;
1073 CLOSE fnd_lookup_csr;
1074 END IF;
1075 RETURN l_return_value;
1076 END get_lookup_meaning;
1077
1078 -------------------------------------------------------------------------------
1079 -- PROCEDURE put_messages_in_table
1080 -------------------------------------------------------------------------------
1081 -- Start of comments
1082 --
1083 -- PROCEDURE Name : put_messages_in_table
1084 -- Description : This procedure writes message in error table
1085 --
1086 -- Business Rules :
1087 --
1088 -- Parameters :
1089 -- Version : 1.0
1090 -- History : 12-MAY-2005 SKGAUTAM created
1091 -- End of comments
1092 PROCEDURE put_messages_in_table(p_msg_name IN VARCHAR2
1093 ,p_during_upd_flag IN VARCHAR
1094 ,x_msg_out OUT NOCOPY VARCHAR2
1095 ,p_token1 IN VARCHAR2 DEFAULT NULL
1096 ,p_value1 IN VARCHAR2 DEFAULT NULL
1097 ,p_token2 IN VARCHAR2 DEFAULT NULL
1098 ,p_value2 IN VARCHAR2 DEFAULT NULL)IS
1099 l_msg VARCHAR2(2700);
1100 BEGIN
1101 FND_MESSAGE.SET_NAME( g_app_name, p_msg_name );
1102 IF ( p_token1 IS NOT NULL ) THEN
1103 FND_MESSAGE.SET_TOKEN( TOKEN => p_token1,
1104 VALUE => p_value1);
1105 END IF;
1106 IF ( p_token2 IS NOT NULL ) THEN
1107 FND_MESSAGE.SET_TOKEN( TOKEN => p_token2,
1108 VALUE => p_value2 );
1109 END IF;
1110 l_msg := FND_MESSAGE.GET;
1111 IF ( UPPER(p_during_upd_flag) = 'T' ) THEN
1112 OKL_API.SET_MESSAGE( g_app_name
1113 ,p_msg_name
1114 ,p_token1
1115 ,p_value1
1116 ,p_token2
1117 ,p_value2
1118 );
1119 END IF;
1120 -- Return the Message
1121 x_msg_out := l_msg;
1122 END put_messages_in_table;
1123
1124 -------------------------------------------------------------------------------
1125 -- PROCEDURE validate_lease_app_template
1126 -------------------------------------------------------------------------------
1127 -- Start of comments
1128 --
1129 -- Procedure Name : validate_lease_app_template
1130 -- Description : This procedure is a wrapper that validates the asociated
1131 -- : checklist , quote and contract templates for
1132 -- : lease application template.
1133 --
1134 -- Business Rules : this procedure is used to validate and update lease application template
1135 -- this procedure updates records of the OKL_LEASEAPP_TEMPLATES and
1136 -- OKL_LEASEAPP_TEMPL_VERSIONS_B table
1137 --
1138 -- Parameters :
1139 -- Version : 1.0
1140 -- History : 12-MAY-2005 SKGAUTAM created
1141 -- End of comments
1142 PROCEDURE validate_lease_app_template(p_api_version IN NUMBER,
1143 p_init_msg_list IN VARCHAR2,
1144 x_return_status OUT NOCOPY VARCHAR2,
1145 x_msg_count OUT NOCOPY NUMBER,
1146 x_msg_data OUT NOCOPY VARCHAR2,
1147 p_latv_rec IN latv_rec_type,
1148 x_latv_rec OUT NOCOPY latv_rec_type,
1149 p_lavv_rec IN lavv_rec_type,
1150 x_lavv_rec OUT NOCOPY lavv_rec_type,
1151 p_during_upd_flag IN VARCHAR2,
1152 x_error_msgs_tbl OUT NOCOPY error_msgs_tbl_type) IS
1153 -- Variables Declarations
1154 l_api_version CONSTANT NUMBER DEFAULT 1.0;
1155 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'VALIDATE_LEASEAPP_TEMPLATE';
1156 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1157 -- Record/Table Type Declarations
1158 l_latv_rec latv_rec_type;
1159 lx_latv_rec latv_rec_type;
1160 l_lavv_rec lavv_rec_type;
1161 lx_lavv_rec lavv_rec_type;
1162
1163 l_error_msgs_tbl error_msgs_tbl_type;
1164 l_wrn_msgs_tbl error_msgs_tbl_type;
1165 l_msgs_count NUMBER DEFAULT 0;
1166 l_message VARCHAR2(2700);
1167 l_debug_enabled VARCHAR2(10);
1168
1169 --Declare Cursor Definations
1170 --Validate Checklist template
1171 CURSOR c_validate_checklist_tmplt(p_lavv_id OKL_LEASEAPP_TEMPL_VERSIONS_V.ID%TYPE) IS
1172 SELECT CHECKLIST_NUMBER TEMPLATE_NUMBER
1173 FROM OKL_CHECKLISTS CHK,
1174 OKL_LEASEAPP_TEMPLATES LATV,
1175 OKL_LEASEAPP_TEMPL_VERSIONS_V LAVV
1176 WHERE LATV.ID = LAVV.LEASEAPP_TEMPLATE_ID
1177 AND LAVV.CHECKLIST_ID = CHK.ID
1178 AND ( (LAVV.VALID_FROM NOT BETWEEN NVL(CHK.START_DATE,LAVV.VALID_FROM)
1179 AND NVL(CHK.END_DATE,LAVV.VALID_FROM))
1180 OR CHK.STATUS_CODE <> 'ACTIVE'
1181 OR CHK.CHECKLIST_PURPOSE_CODE NOT IN ('CHECKLIST_TEMPLATE', 'CHECKLIST_TEMPLATE_GROUP')
1182 OR CHK.ORG_ID <> LATV.ORG_ID)
1183 AND LAVV.ID = p_lavv_id;
1184
1185 --Validate Contract Template
1186 CURSOR c_validate_contract_tmplt(p_lavv_id OKL_LEASEAPP_TEMPL_VERSIONS_V.ID%TYPE) IS
1187 SELECT OKH.CONTRACT_NUMBER TEMPLATE_NUMBER
1188 FROM OKC_K_HEADERS_B OKH,
1189 OKL_LEASEAPP_TEMPLATES LATV,
1190 OKL_LEASEAPP_TEMPL_VERSIONS_V LAVV,
1191 OKL_K_HEADERS KHR,
1192 OKC_STATUSES_V STS
1193 WHERE LATV.ID = LAVV.LEASEAPP_TEMPLATE_ID
1194 AND LAVV.CONTRACT_TEMPLATE_ID = OKH.ID
1195 AND OKH.ID = KHR.ID
1196 AND OKH.STS_CODE = STS.CODE
1197 --Bug#6850094 : Include contract template with any status
1198 /*
1199 AND (TEMPLATE_YN <> 'Y'
1200 OR STS.STE_CODE <> 'ACTIVE'
1201 OR NVL(KHR.TEMPLATE_TYPE_CODE, 'X') <> 'LEASEAPP'
1202 OR OKH.AUTHORING_ORG_ID <> LATV.ORG_ID)
1203 */
1204 AND (OKH.TEMPLATE_YN <> 'Y'
1205 OR (NVL(KHR.TEMPLATE_TYPE_CODE,'X') ='LEASEAPP' AND STS.STE_CODE <> 'ACTIVE')
1206 OR NVL(KHR.TEMPLATE_TYPE_CODE, 'X') NOT IN ('LEASEAPP','CONTRACT')
1207 OR OKH.AUTHORING_ORG_ID <> LATV.ORG_ID)
1208 --Bug#6850094:End
1209 AND LAVV.ID = p_lavv_id;
1210
1211 -- bug 4741121 - smadhava - Added - Start
1212 l_parameter_list WF_PARAMETER_LIST_T;
1213 l_event_name wf_events.name%TYPE;
1214 -- bug 4741121 - smadhava - Added - End
1215 BEGIN
1216 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1217 -- check for logging on PROCEDURE level
1218 l_debug_enabled := okl_debug_pub.check_log_enabled;
1219 -- call START_ACTIVITY to create savepoint, check compatibility
1220 -- and initialize message list
1221 l_return_status := OKL_API.START_ACTIVITY( p_api_name => l_api_name
1222 ,p_pkg_name => G_PKG_NAME
1223 ,p_init_msg_list => p_init_msg_list
1224 ,l_api_version => l_api_version
1225 ,p_api_version => p_api_version
1226 ,p_api_type => g_api_type
1227 ,x_return_status => x_return_status);
1228 -- check if activity started successfully
1229 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1230 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1231 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1232 RAISE OKL_API.G_EXCEPTION_ERROR;
1233 END IF;
1234 l_latv_rec := p_latv_rec;
1235 l_lavv_rec := p_lavv_rec;
1236 --Validate the Checklist Template and Poulate the Error Table
1237 FOR r_validate_checklist_tmplt IN c_validate_checklist_tmplt(l_lavv_rec.ID) LOOP
1238 l_msgs_count := l_msgs_count+1;
1239 put_messages_in_table(G_OKL_VAL_CHECKLIST_TEMPLATE
1240 ,p_during_upd_flag
1241 ,l_message
1242 ,p_token1 => G_TEMPLATE_NUMBER
1243 ,p_value1 => r_validate_checklist_tmplt.template_number);
1244 l_error_msgs_tbl(l_msgs_count).error_message := l_message;
1245 l_error_msgs_tbl(l_msgs_count).error_type_code := G_TYPE_ERROR;
1246 l_error_msgs_tbl(l_msgs_count).error_type_meaning := GET_LOOKUP_MEANING( G_CP_SET_OUTCOME, G_TYPE_ERROR);
1247 END LOOP;
1248 --Validate the Contract Template and Populate the Error Table
1249 FOR r_validate_contract_tmplt IN c_validate_contract_tmplt(l_lavv_rec.ID) LOOP
1250 l_msgs_count := l_msgs_count+1;
1251 put_messages_in_table(G_OKL_VAL_CONTRACT_TEMPLATE
1252 ,p_during_upd_flag
1253 ,l_message
1254 ,p_token1 => G_TEMPLATE_NUMBER
1255 ,p_value1 => r_validate_contract_tmplt.template_number);
1256 l_error_msgs_tbl(l_msgs_count).error_message := l_message;
1257 l_error_msgs_tbl(l_msgs_count).error_type_code := G_TYPE_ERROR;
1258 l_error_msgs_tbl(l_msgs_count).error_type_meaning := GET_LOOKUP_MEANING( G_CP_SET_OUTCOME, G_TYPE_ERROR);
1259 END LOOP;
1260 -- Update the template status based on the x_return_status
1261 -- or l_error_msgs_tbl.COUNT
1262 -- Determine the status of the Template after the Validation
1263 IF( l_error_msgs_tbl.COUNT IS NULL OR l_error_msgs_tbl.COUNT = 0 ) THEN
1264 -- When the status of the template getting updated is Active, after
1265 -- successful validation , the status remains at Active.
1266 IF (l_lavv_rec.VERSION_STATUS = G_STATUS_ACTIVE) THEN
1267 l_lavv_rec.VERSION_STATUS := G_STATUS_ACTIVE;
1268 ELSE
1269 -- In all other cases the Template status should be SUBMITED FOR APPROVAL
1270 -- bug 4741121 - smadhava - Modified - Start
1271 l_lavv_rec.VERSION_STATUS := G_STATUS_SUBMITTEDFORAPPROVAL;
1272 --raise workflow submit event
1273 l_event_name := G_WF_EVT_LAT_PENDING;
1274
1275 -- Add the version id to the wf parameter list
1276 wf_event.AddParameterToList(G_WF_LAT_VERSION_ID
1277 , l_lavv_rec.ID
1278 , l_parameter_list);
1279
1280 -- Check the profile for the AME approval process
1281 IF NVL(FND_PROFILE.VALUE(G_PROF_FE_APPROVAL_PROCESS),'NONE') = 'NONE' THEN
1282 activate_lat (p_api_version
1283 , p_init_msg_list
1284 , l_return_status
1285 , x_msg_count
1286 , x_msg_data
1287 , l_lavv_rec.ID);
1288 IF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1289 RAISE OKL_API.G_EXCEPTION_ERROR;
1290 ELSIF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1291 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1292 END IF;
1293
1294 ELSE
1295 OKL_WF_PVT.raise_event(p_api_version => p_api_version,
1296 p_init_msg_list => p_init_msg_list,
1297 x_return_status => l_return_status,
1298 x_msg_count => x_msg_count,
1299 x_msg_data => x_msg_data,
1300 p_event_name => l_event_name,
1301 p_parameters => l_parameter_list);
1302 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1303 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1304 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1305 RAISE OKL_API.G_EXCEPTION_ERROR;
1306 END IF;
1307 END IF;
1308 -- bug 4741121 - smadhava - Modified - End
1309 END IF;
1310 ELSE
1311 -- Change the template status to INVALID
1312 l_lavv_rec.VERSION_STATUS := G_STATUS_INVALID;
1313
1314 update_leaseapp_template(p_api_version => p_api_version
1315 ,p_init_msg_list => p_init_msg_list
1316 ,x_return_status => x_return_status
1317 ,x_msg_count => x_msg_count
1318 ,x_msg_data => x_msg_data
1319 ,p_latv_rec => l_latv_rec
1320 ,x_latv_rec => lx_latv_rec
1321 ,p_lavv_rec => l_lavv_rec
1322 ,x_lavv_rec => lx_lavv_rec
1323 ,p_ident_flag => 'A');
1324 END IF;
1325
1326 x_error_msgs_tbl := l_error_msgs_tbl;
1327 x_latv_rec := l_latv_rec;
1328 x_lavv_rec := l_lavv_rec;
1329 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count
1330 ,x_msg_data => x_msg_data);
1331 EXCEPTION
1332 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1333 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1334 p_api_name => l_api_name,
1335 p_pkg_name => G_PKG_NAME,
1336 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1337 x_msg_count => x_msg_count,
1338 x_msg_data => x_msg_data,
1339 p_api_type => g_api_type);
1340 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1341 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1342 p_api_name => l_api_name,
1343 p_pkg_name => G_PKG_NAME,
1344 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1345 x_msg_count => x_msg_count,
1346 x_msg_data => x_msg_data,
1347 p_api_type => g_api_type);
1348 WHEN OTHERS THEN
1349 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1350 p_api_name => l_api_name,
1351 p_pkg_name => G_PKG_NAME,
1352 p_exc_name => 'OTHERS',
1353 x_msg_count => x_msg_count,
1354 x_msg_data => x_msg_data,
1355 p_api_type => g_api_type);
1356 END validate_lease_app_template;
1357
1358 -------------------------------------------------------------------------------
1359 -- PROCEDURE max_valid_from_date
1360 -------------------------------------------------------------------------------
1361 -- Start of comments
1362 --
1363 -- Procedure Name : max_valid_from_date
1364 -- Description : This function returns the valid from date after the start dates
1365 -- : of all Lease Applications, with statuses 'Submitted to Credit'
1366 -- : and greater, and Vendor Programs that use the template.
1367 --
1368 -- Business Rules :
1369 --
1370 -- Parameters :
1371 -- Version : 1.0
1372 -- History : 12-MAY-2005 SKGAUTAM created
1373 -- End of comments
1374 PROCEDURE max_valid_from_date(p_api_version IN NUMBER,
1375 p_init_msg_list IN VARCHAR2,
1376 x_return_status OUT NOCOPY VARCHAR2,
1377 x_msg_count OUT NOCOPY NUMBER,
1378 x_msg_data OUT NOCOPY VARCHAR2,
1379 p_latv_rec IN latv_rec_type,
1380 x_latv_rec OUT NOCOPY latv_rec_type)IS
1381 -- Variables Declarations
1382 l_api_version CONSTANT NUMBER DEFAULT 1.0;
1383 l_api_name CONSTANT VARCHAR2(30) DEFAULT 'MAX_VALID_FROM_DATE';
1384 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1385 -- Record/Table Type Declarations
1386 l_latv_rec latv_rec_type;
1387 l_max_start_date DATE;
1388 l_valid_from DATE;
1389 l_valid_to DATE;
1390 -- Declare Cursor Definations
1391 -- Get the max start date from Lease App and Vendor Prog
1392 -- for associated lease app template
1393 CURSOR c_get_max_start_date( p_lat_id okl_leaseapp_templates.id%TYPE) IS
1394 SELECT MAX(START_DATE) max_start_date
1395 FROM
1396 (
1397 SELECT chr.START_DATE START_DATE
1398 FROM okc_k_headers_b chr,
1399 okl_vp_associations vpa,
1400 Okl_Leaseapp_Templates lat,
1401 okl_leaseapp_templ_versions_b lav
1402 WHERE chr.scs_code = 'PROGRAM'
1403 AND chr.sts_code = 'ACTIVE'
1404 AND chr.id = vpa.chr_id
1405 AND vpa.ASSOC_OBJECT_TYPE_CODE = 'LA_TEMPLATE'
1406 AND vpa.ASSOC_OBJECT_ID = lat.ID
1407 AND vpa.ASSOC_OBJECT_VERSION = lav.VERSION_NUMBER
1408 AND LAT.ID = LAV.LEASEAPP_TEMPLATE_ID
1409 AND lat.ID = p_lat_id
1410 UNION
1411 SELECT laa.VALID_FROM START_DATE
1412 FROM OKL_LEASE_APPLICATIONS_B laa,
1413 okl_leaseapp_templates lat,
1414 okl_leaseapp_templ_versions_v lav
1415 WHERE lat.id = lav.leaseapp_template_id
1416 AND laa.LEASEAPP_TEMPLATE_ID = lav.ID
1417 AND laa.APPLICATION_STATUS IN('CONV-CL','CONV-K','CR-APPROVED','CR-SUBMITTED',
1418 'PR-ACCEPTED','PR-APPROVED','PR-SUBMITTED')
1419 AND trunc(laa.VALID_FROM) >= trunc(lav.VALID_FROM)
1420 AND lat.ID = p_lat_id
1421 ) MY_START_DATE;
1422 --asawanka bug 4966317 fix starts
1423 CURSOR get_latest_activever_dates(p_lat_id IN okl_leaseapp_templates.id%TYPE) IS
1424 /* SELECT max(to_number(lav.version_number)), lav.valid_from,lav.valid_to
1425 FROM okl_leaseapp_templates lat,
1426 okl_leaseapp_templ_versions_v lav
1427 WHERE lat.id = lav.leaseapp_template_id
1428 AND lat.id = p_lat_id
1429 group by lav.valid_from,lav.valid_to;*/
1430 --Fixed ssdeshpa Bug # 6487421 Start
1431 SELECT LAV.VERSION_NUMBER
1432 , LAV.VALID_FROM
1433 , LAV.VALID_TO
1434 FROM OKL_LEASEAPP_TEMPLATES LAT
1435 , OKL_LEASEAPP_TEMPL_VERSIONS_B LAV
1436 WHERE LAT.ID = LAV.LEASEAPP_TEMPLATE_ID
1437 AND LAT.ID = p_lat_id
1438 AND TO_NUMBER(LAV.VERSION_NUMBER) = (SELECT MAX(TO_NUMBER(LAV1.VERSION_NUMBER))
1439 FROM OKL_LEASEAPP_TEMPL_VERSIONS_B LAV1
1440 WHERE LAV1.LEASEAPP_TEMPLATE_ID = LAT.ID);
1441 --Fixed ssdeshpa Bug # 6487421 End
1442 --asawanka bug 4966317 fix ends
1443 BEGIN
1444 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1445 -- call START_ACTIVITY to create savepoint, check compatibility
1446 -- and initialize message list
1447 l_return_status := OKL_API.START_ACTIVITY( p_api_name => l_api_name
1448 ,p_pkg_name => G_PKG_NAME
1449 ,p_init_msg_list => p_init_msg_list
1450 ,l_api_version => l_api_version
1451 ,p_api_version => p_api_version
1452 ,p_api_type => g_api_type
1453 ,x_return_status => x_return_status);
1454 -- check if activity started successfully
1455 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1456 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1457 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1458 RAISE OKL_API.G_EXCEPTION_ERROR;
1459 END IF;
1460 l_latv_rec := p_latv_rec;
1461 -- Fetch the Maximum of Start Date of the Vendor Program and Lease application using this template
1462 FOR r_new_date_rec IN c_get_max_start_date(l_latv_rec.id) LOOP
1463 l_max_start_date := r_new_date_rec.max_start_date;
1464 END LOOP;
1465 --asawanka bug 4966317 fix starts
1466 FOR r_actver_dates_rec IN get_latest_activever_dates(l_latv_rec.id) LOOP
1467 l_valid_from := r_actver_dates_rec.valid_from;
1468 l_valid_to := r_actver_dates_rec.valid_to;
1469 END LOOP;
1470 --asawanka bug 4966317 fix ends
1471 IF(l_max_start_date IS NULL) THEN
1472 --asawanka bug 4966317 fix starts
1473 -- If no start_date is found, then take the previous version valid to date,
1474 -- if present, else take previous version valid from
1475 -- as the max_Start_date
1476 IF l_valid_to IS NULL THEN
1477 l_max_start_date := l_valid_from;
1478 ELSE
1479 l_max_start_date := l_valid_to;
1480 END IF;
1481 END IF;
1482 IF l_valid_to IS NOT NULL AND l_valid_to > l_max_start_date THEN
1483 l_max_start_date := l_valid_to;
1484 END IF;
1485 --Bug 5149659 PAGARG If max start date is less than valid from of the current
1486 --version then set valid from of current version as max start date
1487 IF l_max_start_date < l_valid_from
1488 THEN
1489 l_max_start_date := l_valid_from;
1490 END IF;
1491 -- New version template valid from date will be updated with l_max_start_date + 1
1492 l_latv_rec.valid_from := l_max_start_date + 1;
1493 --asawanka bug 4966317 fix ends
1494 x_latv_rec := l_latv_rec;
1495 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count
1496 ,x_msg_data => x_msg_data);
1497 EXCEPTION
1498 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1499 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1500 p_api_name => l_api_name,
1501 p_pkg_name => G_PKG_NAME,
1502 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1503 x_msg_count => x_msg_count,
1504 x_msg_data => x_msg_data,
1505 p_api_type => g_api_type);
1506 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1507 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1508 p_api_name => l_api_name,
1509 p_pkg_name => G_PKG_NAME,
1510 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1511 x_msg_count => x_msg_count,
1512 x_msg_data => x_msg_data,
1513 p_api_type => g_api_type);
1514 WHEN OTHERS THEN
1515 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1516 p_api_name => l_api_name,
1517 p_pkg_name => G_PKG_NAME,
1518 p_exc_name => 'OTHERS',
1519 x_msg_count => x_msg_count,
1520 x_msg_data => x_msg_data,
1521 p_api_type => g_api_type);
1522 END max_valid_from_date;
1523
1524 END OKL_LEASEAPP_TEMPLATE_PVT;