[Home] [Help]
PACKAGE BODY: APPS.OKL_ITEM_RESIDUALS_PVT
Source
1 PACKAGE BODY OKL_ITEM_RESIDUALS_PVT AS
2 /* $Header: OKLRIRSB.pls 120.8 2006/08/09 14:18:17 pagarg noship $ */
3 G_WF_EVT_IRS_PENDING CONSTANT wf_events.name%TYPE DEFAULT 'oracle.apps.okl.fe.irsapproval';
4 G_WF_IRS_VERSION_ID CONSTANT VARCHAR2(50) := 'VERSION_ID';
5
6 FUNCTION is_latest(p_item_residual_id NUMBER, p_ver_number VARCHAR2) RETURN BOOLEAN IS
7 max_version NUMBER := 1;
8
9 CURSOR get_max_ver(p_item_residual_id NUMBER) IS
10 SELECT
11 MAX(TO_NUMBER(ICPV.VERSION_NUMBER))
12 FROM
13 OKL_ITM_CAT_RV_PRCS_V ICPV
14 WHERE
15 ICPV.ITEM_RESIDUAL_ID = p_item_residual_id;
16 BEGIN
17
18 OPEN get_max_ver(p_item_residual_id);
19 FETCH get_max_ver INTO max_version;
20 CLOSE get_max_ver;
21
22 IF TO_NUMBER(p_ver_number) = max_version THEN
23 RETURN TRUE;
24 ELSE RETURN FALSE;
25 END IF;
26 END is_latest;
27
28 -- Gets the latest active version of an item residual
29 FUNCTION is_latest_active (p_item_residual_id NUMBER, p_ver_number VARCHAR2) RETURN BOOLEAN IS
30 max_version NUMBER := 1;
31
32 CURSOR get_max_ver(p_item_residual_id NUMBER) IS
33 SELECT
34 MAX(TO_NUMBER(ICPV.VERSION_NUMBER))
35 FROM
36 OKL_ITM_CAT_RV_PRCS_V ICPV
37 WHERE
38 ICPV.STS_CODE = G_STS_ACTIVE
39 AND ICPV.ITEM_RESIDUAL_ID = p_item_residual_id ;
40 BEGIN
41
42 OPEN get_max_ver(p_item_residual_id);
43 FETCH get_max_ver INTO max_version;
44 CLOSE get_max_ver;
45
46 IF TO_NUMBER(p_ver_number) = max_version THEN
47 RETURN TRUE;
48 ELSE RETURN FALSE;
49 END IF;
50 END is_latest_active;
51
52 -- Checks if the percent values lie in the range 0-100
53 PROCEDURE validate_percent_values(p_irv_tbl IN okl_irv_tbl) IS
54 BEGIN
55 FOR i IN p_irv_tbl.FIRST..p_irv_tbl.LAST
56 LOOP
57 IF p_irv_tbl(i).residual_value > 100 THEN
58 OKL_API.set_message(p_app_name => G_APP_NAME,
59 p_msg_name => 'OKL_INVALID_VALUE',
60 p_token1 => G_COL_NAME_TOKEN,
61 p_token1_value => 'Term ' || p_irv_tbl(i).term_in_months );
62 RAISE OKL_API.G_EXCEPTION_ERROR;
63
64 END IF;
65 END LOOP;
66 END;
67
68 -- Returns false for invalid effective from date
69 -- Checks if the effective from is later than the previous version effective from date
70 -- Checks if the effective from date later than the previous version effective to date
71 FUNCTION valid_version_effective_date(p_effective_from DATE, p_residual_id NUMBER, p_version_number VARCHAR2) RETURN BOOLEAN IS
72 return_bool BOOLEAN := TRUE;
73 CURSOR prev_effective_dates (p_residual_id NUMBER, p_version_number VARCHAR2) IS
74 SELECT
75 ICPV.START_DATE
76 , ICPV.END_DATE
77 FROM
78 OKL_ITM_CAT_RV_PRCS_V ICPV
79 WHERE
80 ICPV.ITEM_RESIDUAL_ID = p_residual_id
81 AND TO_NUMBER(ICPV.VERSION_NUMBER) = TO_NUMBER(p_version_number)-1 ;
82
83 l_ver_rec prev_effective_dates%ROWTYPE;
84 BEGIN
85 OPEN prev_effective_dates(p_residual_id,p_version_number);
86 FETCH prev_effective_dates INTO l_ver_rec;
87 CLOSE prev_effective_dates;
88
89 IF l_ver_rec.START_DATE >= p_effective_from AND l_ver_rec.END_DATE IS NULL THEN
90 return_bool := FALSE;
91 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
92 p_msg_name => 'OKL_ST_INVALID_EFFECTIVE_FROM',
93 p_token1 => 'EFF_DATE',
94 p_token1_value => l_ver_rec.START_DATE+1);
95 ELSIF l_ver_rec.END_DATE >= p_effective_from THEN
96 return_bool := FALSE;
97 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
98 p_msg_name => 'OKL_ST_INVALID_EFFECTIVE_FROM',
99 p_token1 => 'EFF_DATE',
100 p_token1_value => l_ver_rec.END_DATE+1);
101 END IF;
102
103 IF TO_NUMBER(p_version_number) = 1 THEN
104 return_bool := TRUE;
105 END IF;
106
107 RETURN return_bool;
108 END valid_version_effective_date;
109
110 PROCEDURE get_effective_date(
111 p_api_version IN NUMBER
112 , p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
113 , x_return_status OUT NOCOPY VARCHAR2
114 , x_msg_count OUT NOCOPY NUMBER
115 , x_msg_data OUT NOCOPY VARCHAR2
116 , p_item_resdl_ver_id IN NUMBER
117 , x_calc_date OUT NOCOPY DATE
118 ) IS
119 l_api_name CONSTANT VARCHAR2(65) := 'get_effective_date';
120 l_api_version CONSTANT NUMBER := p_api_version;
121 l_init_msg_list VARCHAR2(1) := p_init_msg_list;
122 l_msg_count NUMBER := x_msg_count ;
123 l_msg_data VARCHAR2(2000);
124 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
125
126 l_calc_date DATE := NULL;
127 l_calc_date1 DATE := NULL;
128 l_calc_date2 DATE := NULL;
129
130 -- cursor to fetch the maximum start date of lease quotes referencing Lease Rate Sets - When source is Item
131 CURSOR lrs_lq_csr_itm(p_version_id IN NUMBER) IS
132 SELECT max(expected_start_date) start_date FROM okl_lease_quotes_b
133 WHERE rate_card_id IN(SELECT
134 LRFVERV.RATE_SET_ID LRS_ID
135 FROM
136 OKL_FE_ITEM_RESIDUAL IRHV
137 , OKL_ITM_CAT_RV_PRCS_V ICPV
138 , OKL_FE_EO_TERM_OBJECTS EOTL
139 , OKL_FE_EO_TERM_VERS_V EOTVERV
140 , OKL_FE_RATE_SET_VERSIONS_V LRFVERV
141 WHERE
142 EOTL.END_OF_TERM_VER_ID = EOTVERV.END_OF_TERM_VER_ID
143 AND EOTVERV.END_OF_TERM_VER_ID = LRFVERV.END_OF_TERM_VER_ID
144 AND EOTVERV.EOT_TYPE_CODE IN ('RESIDUAL_PERCENT','RESIDUAL_AMOUNT')
145 AND IRHV.INVENTORY_ITEM_ID = EOTL.INVENTORY_ITEM_ID
146 AND IRHV.ORGANIZATION_ID = EOTL.ORGANIZATION_ID
147 AND IRHV.CATEGORY_SET_ID = EOTL.CATEGORY_SET_ID
148 AND ICPV.ITEM_RESIDUAL_ID = IRHV.ITEM_RESIDUAL_ID
149 AND LRFVERV.EFFECTIVE_FROM_DATE BETWEEN ICPV.START_DATE AND NVL(ICPV.END_DATE,TO_DATE('31-12-9999','dd-mm-yyyy'))
150 AND ICPV.ID = p_version_id);
151
152 -- cursor to fetch the maximum start date of lease quotes referencing Lease Rate Sets - When source is Item Category
153 CURSOR lrs_lq_csr_itm_cat(p_version_id IN NUMBER) IS
154 SELECT max(expected_start_date) start_date FROM okl_lease_quotes_b
155 WHERE rate_card_id IN(SELECT
156 LRFVERV.RATE_SET_ID LRS_ID
157 FROM
158 OKL_FE_ITEM_RESIDUAL IRHV
159 , OKL_ITM_CAT_RV_PRCS_V ICPV
160 , OKL_FE_EO_TERM_OBJECTS EOTL
161 , OKL_FE_EO_TERM_VERS_V EOTVERV
162 , OKL_FE_RATE_SET_VERSIONS_V LRFVERV
163 WHERE
164 EOTL.END_OF_TERM_VER_ID = EOTVERV.END_OF_TERM_VER_ID
165 AND EOTVERV.END_OF_TERM_VER_ID = LRFVERV.END_OF_TERM_VER_ID
166 AND EOTVERV.EOT_TYPE_CODE IN ('RESIDUAL_PERCENT','RESIDUAL_AMOUNT')
167 AND IRHV.CATEGORY_ID = EOTL.CATEGORY_ID
168 AND IRHV.CATEGORY_SET_ID = EOTL.CATEGORY_SET_ID
169 AND ICPV.ITEM_RESIDUAL_ID = IRHV.ITEM_RESIDUAL_ID
170 AND LRFVERV.EFFECTIVE_FROM_DATE BETWEEN ICPV.START_DATE AND NVL(ICPV.END_DATE,TO_DATE('31-12-9999','dd-mm-yyyy'))
171 AND ICPV.ID = p_version_id);
172
173 -- cursor to fetch the maximum start date of lease quotes referencing Lease Rate Sets - When source is Residual Category set
174 CURSOR lrs_lq_csr_rcs(p_version_id IN NUMBER) IS
175 SELECT max(expected_start_date) start_date FROM okl_lease_quotes_b
176 WHERE rate_card_id IN(SELECT
177 LRFVERV.RATE_SET_ID LRS_ID
178 FROM
179 OKL_FE_ITEM_RESIDUAL IRHV
180 , OKL_ITM_CAT_RV_PRCS_V ICPV
181 , OKL_FE_EO_TERM_OBJECTS EOTL
182 , OKL_FE_EO_TERM_VERS_V EOTVERV
183 , OKL_FE_RATE_SET_VERSIONS_V LRFVERV
184 WHERE
185 EOTL.END_OF_TERM_VER_ID = EOTVERV.END_OF_TERM_VER_ID
186 AND EOTVERV.END_OF_TERM_VER_ID = LRFVERV.END_OF_TERM_VER_ID
187 AND EOTVERV.EOT_TYPE_CODE IN ('RESIDUAL_PERCENT','RESIDUAL_AMOUNT')
188 AND IRHV.RESI_CATEGORY_SET_ID = EOTL.RESI_CATEGORY_SET_ID
189 AND ICPV.ITEM_RESIDUAL_ID = IRHV.ITEM_RESIDUAL_ID
190 AND LRFVERV.EFFECTIVE_FROM_DATE BETWEEN ICPV.START_DATE AND NVL(ICPV.END_DATE,TO_DATE('31-12-9999','dd-mm-yyyy'))
191 AND ICPV.ID = p_version_id);
192
193 -- cursor to fetch the maximum start date of quick quotes referencing Lease Rate Sets - When source is Item
194 CURSOR lrs_qq_csr_itm(p_version_id IN NUMBER) IS
195 SELECT max(expected_start_date) start_date FROM okl_quick_quotes_b
196 WHERE rate_card_id IN (SELECT
197 LRFVERV.RATE_SET_ID LRS_ID
198 FROM
199 OKL_FE_ITEM_RESIDUAL IRHV
200 , OKL_ITM_CAT_RV_PRCS_V ICPV
201 , OKL_FE_EO_TERM_OBJECTS EOTL
202 , OKL_FE_EO_TERM_VERS_V EOTVERV
203 , OKL_FE_RATE_SET_VERSIONS_V LRFVERV
204 WHERE
205 EOTL.END_OF_TERM_VER_ID = EOTVERV.END_OF_TERM_VER_ID
206 AND EOTVERV.END_OF_TERM_VER_ID = LRFVERV.END_OF_TERM_VER_ID
207 AND EOTVERV.EOT_TYPE_CODE IN ('RESIDUAL_PERCENT','RESIDUAL_AMOUNT')
208 AND IRHV.INVENTORY_ITEM_ID = EOTL.INVENTORY_ITEM_ID
209 AND IRHV.ORGANIZATION_ID = EOTL.ORGANIZATION_ID
210 AND IRHV.CATEGORY_SET_ID = EOTL.CATEGORY_SET_ID
211 AND ICPV.ITEM_RESIDUAL_ID = IRHV.ITEM_RESIDUAL_ID
212 AND LRFVERV.EFFECTIVE_FROM_DATE BETWEEN ICPV.START_DATE AND NVL(ICPV.END_DATE,TO_DATE('31-12-9999','dd-mm-yyyy'))
213 AND ICPV.ID = p_version_id);
214
215 -- cursor to fetch the maximum start date of quick quotes referencing Lease Rate Sets - When source is Item Category
216 CURSOR lrs_qq_csr_itm_cat(p_version_id IN NUMBER) IS
217 SELECT max(expected_start_date) start_date FROM okl_quick_quotes_b
218 WHERE rate_card_id IN (SELECT
219 LRFVERV.RATE_SET_ID LRS_ID
220 FROM
221 OKL_FE_ITEM_RESIDUAL IRHV
222 , OKL_ITM_CAT_RV_PRCS_V ICPV
223 , OKL_FE_EO_TERM_OBJECTS EOTL
224 , OKL_FE_EO_TERM_VERS_V EOTVERV
225 , OKL_FE_RATE_SET_VERSIONS_V LRFVERV
226 WHERE
227 EOTL.END_OF_TERM_VER_ID = EOTVERV.END_OF_TERM_VER_ID
228 AND EOTVERV.END_OF_TERM_VER_ID = LRFVERV.END_OF_TERM_VER_ID
229 AND EOTVERV.EOT_TYPE_CODE IN ('RESIDUAL_PERCENT','RESIDUAL_AMOUNT')
230 AND IRHV.CATEGORY_ID = EOTL.CATEGORY_ID
231 AND IRHV.CATEGORY_SET_ID = EOTL.CATEGORY_SET_ID
232 AND ICPV.ITEM_RESIDUAL_ID = IRHV.ITEM_RESIDUAL_ID
233 AND LRFVERV.EFFECTIVE_FROM_DATE BETWEEN ICPV.START_DATE AND NVL(ICPV.END_DATE,TO_DATE('31-12-9999','dd-mm-yyyy'))
234 AND ICPV.ID = p_version_id);
235
236 -- cursor to fetch the maximum start date of quick quotes referencing Lease Rate Sets - When source is Residual Category set
237 CURSOR lrs_qq_csr_rcs(p_version_id IN NUMBER) IS
238 SELECT max(expected_start_date) start_date FROM okl_quick_quotes_b
239 WHERE rate_card_id IN (SELECT
240 LRFVERV.RATE_SET_ID LRS_ID
241 FROM
242 OKL_FE_ITEM_RESIDUAL IRHV
243 , OKL_ITM_CAT_RV_PRCS_V ICPV
244 , OKL_FE_EO_TERM_OBJECTS EOTL
245 , OKL_FE_EO_TERM_VERS_V EOTVERV
246 , OKL_FE_RATE_SET_VERSIONS_V LRFVERV
247 WHERE
248 EOTL.END_OF_TERM_VER_ID = EOTVERV.END_OF_TERM_VER_ID
249 AND EOTVERV.END_OF_TERM_VER_ID = LRFVERV.END_OF_TERM_VER_ID
250 AND EOTVERV.EOT_TYPE_CODE IN ('RESIDUAL_PERCENT','RESIDUAL_AMOUNT')
251 AND IRHV.RESI_CATEGORY_SET_ID = EOTL.RESI_CATEGORY_SET_ID
252 AND ICPV.ITEM_RESIDUAL_ID = IRHV.ITEM_RESIDUAL_ID
253 AND LRFVERV.EFFECTIVE_FROM_DATE BETWEEN ICPV.START_DATE AND NVL(ICPV.END_DATE,TO_DATE('31-12-9999','dd-mm-yyyy'))
254 AND ICPV.ID = p_version_id);
255
256 CURSOR get_version_dtls(p_ver_id NUMBER) IS
257 SELECT
258 ICPV.START_DATE
259 , ICPV.END_DATE
260 , ICPV.VERSION_NUMBER
261 , ICPV.STS_CODE
262 , ICPV.ITEM_RESIDUAL_ID
263 , IRHV.CATEGORY_TYPE_CODE
264 FROM
265 OKL_ITM_CAT_RV_PRCS_V ICPV
266 , OKL_FE_ITEM_RESIDUAL IRHV
267 WHERE
268 IRHV.ITEM_RESIDUAL_ID = ICPV.ITEM_RESIDUAL_ID
269 AND ICPV.ID = p_ver_id;
270
271 l_version_details get_version_dtls%ROWTYPE;
272
273 l_module CONSTANT fnd_log_messages.module%TYPE :='okl.plsql.OKL_ITEM_RESIDUALS_PVT.get_effective_date';
274 l_debug_enabled VARCHAR2(10);
275 is_debug_procedure_on BOOLEAN;
276 is_debug_statement_on BOOLEAN;
277
278 BEGIN
279 l_debug_enabled := okl_debug_pub.check_log_enabled;
280 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
281 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
282 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRIRSB.pls call get_effective_date');
283 END IF;
284
285 l_return_status := OKL_API.start_activity(l_api_name
286 ,G_PKG_NAME
287 ,p_init_msg_list
288 ,l_api_version
289 ,p_api_version
290 ,'_PVT'
291 ,x_return_status);
292 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
293 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
294 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
295 RAISE OKL_API.G_EXCEPTION_ERROR;
296 END IF;
297
298 -- Get the end date of the version.
299 OPEN get_version_dtls(p_item_resdl_ver_id);
300 FETCH get_version_dtls INTO l_version_details;
301 CLOSE get_version_dtls;
302
303 -- If Source is Item then call cursor for items
304 -- Get the maximum date of the referenced Quotes
305 CASE l_version_details.category_type_code
306 WHEN G_CAT_ITEM THEN
307 OPEN lrs_lq_csr_itm(p_item_resdl_ver_id);
308 FETCH lrs_lq_csr_itm INTO l_calc_date1;
309 CLOSE lrs_lq_csr_itm;
310
311 OPEN lrs_qq_csr_itm(p_item_resdl_ver_id);
312 FETCH lrs_qq_csr_itm INTO l_calc_date2;
313 CLOSE lrs_qq_csr_itm;
314
315 -- If Source is Item category then call cursor for item categories
316 -- Get the maximum date of the referenced Quotes
317 WHEN G_CAT_ITEM_CAT THEN
318 OPEN lrs_lq_csr_itm_cat(p_item_resdl_ver_id);
319 FETCH lrs_lq_csr_itm_cat INTO l_calc_date1;
320 CLOSE lrs_lq_csr_itm_cat;
321
322 OPEN lrs_qq_csr_itm_cat(p_item_resdl_ver_id);
323 FETCH lrs_qq_csr_itm_cat INTO l_calc_date2;
324 CLOSE lrs_qq_csr_itm_cat;
325 -- If Source is Residual Category Set then call cursor for residual category sets
326 -- Get the maximum date of the referenced Quotes
327 WHEN G_CAT_RES_CAT THEN
328 OPEN lrs_lq_csr_rcs(p_item_resdl_ver_id);
329 FETCH lrs_lq_csr_rcs INTO l_calc_date1;
330 CLOSE lrs_lq_csr_rcs;
331
332 OPEN lrs_qq_csr_rcs(p_item_resdl_ver_id);
333 FETCH lrs_qq_csr_rcs INTO l_calc_date2;
334 CLOSE lrs_qq_csr_rcs;
335 END CASE;
336
337 -- If the maximum date of referenced Quotes is not null
338 -- END DATE = max date + 1
339 IF l_calc_date1 IS NOT NULL OR l_calc_date2 IS NOT NULL THEN
340 IF l_calc_date1 > l_calc_date2 THEN
341 l_calc_date := l_calc_date1;
342 ELSE
343 l_calc_date := l_calc_date2;
344 END IF;
345
346 -- If the maximum date of referenced Quotes is null
347 ELSE
348 -- If the end date is null, then return start date + 1
349 -- else return end date + 1
350 IF l_version_details.END_DATE IS NULL THEN
351 l_calc_date := l_version_details.START_DATE + 1;
352 ELSE
353 l_calc_date := l_version_details.END_DATE + 1;
354 END IF;
355 END IF;
356
357 x_calc_date := l_calc_date;
358 x_return_status := l_return_status;
359 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
360
361 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
362 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRIRSB.pls call get_effective_date');
363 END IF;
364
365 EXCEPTION
366 WHEN OKL_API.G_EXCEPTION_ERROR THEN
367
368 x_return_status := OKL_API.HANDLE_EXCEPTIONS
369 (
370 l_api_name,
371 G_PKG_NAME,
372 'OKL_API.G_RET_STS_ERROR',
373 x_msg_count,
374 x_msg_data,
375 '_PVT'
376 );
377
378 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
379
380 x_return_status := OKL_API.HANDLE_EXCEPTIONS
381 (
382 l_api_name,
383 G_PKG_NAME,
384 'OKL_API.G_RET_STS_UNEXP_ERROR',
385 x_msg_count,
386 x_msg_data,
387 '_PVT'
388 );
389
390 WHEN OTHERS THEN
391
392 x_return_status := OKL_API.HANDLE_EXCEPTIONS
393 (
394 l_api_name,
395 G_PKG_NAME,
396 'OTHERS',
397 x_msg_count,
398 x_msg_data,
399 '_PVT'
400 );
401 END get_effective_date;
402 PROCEDURE create_irs (
403 p_api_version IN NUMBER
404 , p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
405 , x_return_status OUT NOCOPY VARCHAR2
406 , x_msg_count OUT NOCOPY NUMBER
407 , x_msg_data OUT NOCOPY VARCHAR2
408 , p_irhv_rec IN okl_irhv_rec
409 , p_icpv_rec IN okl_icpv_rec
410 , p_irv_tbl IN okl_irv_tbl
411 , x_irhv_rec OUT NOCOPY okl_irhv_rec
412 , x_icpv_rec OUT NOCOPY okl_icpv_rec
413 ) IS
414 l_api_name CONSTANT VARCHAR2(40) := 'create_irs';
415 l_api_version CONSTANT NUMBER := p_api_version;
416 l_init_msg_list VARCHAR2(1) := p_init_msg_list;
417 l_msg_count NUMBER := x_msg_count ;
418 l_msg_data VARCHAR2(2000);
419 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
420
421 lx_irv_tbl okl_irv_tbl;
422 lp_icpv_rec okl_icpv_rec := p_icpv_rec;
423 lp_irv_tbl okl_irv_tbl := p_irv_tbl;
424
425 l_module CONSTANT fnd_log_messages.module%TYPE :='okl.plsql.OKL_ITEM_RESIDUALS_PVT.create_irs';
426 l_debug_enabled VARCHAR2(10);
427 is_debug_procedure_on BOOLEAN;
428 is_debug_statement_on BOOLEAN;
429 BEGIN
430 l_debug_enabled := okl_debug_pub.check_log_enabled;
431 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
432 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
433 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRIRSB.pls call create_irs');
434 END IF;
435
436 l_return_status := OKL_API.start_activity(l_api_name
437 ,G_PKG_NAME
438 ,p_init_msg_list
439 ,l_api_version
440 ,p_api_version
441 ,'_PVT'
442 ,x_return_status);
443 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
444 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
445 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
446 RAISE OKL_API.G_EXCEPTION_ERROR;
447 END IF;
448 -- Insert row into the header table
449 OKL_IRH_PVT.insert_row(
450 p_api_version => p_api_version
451 , p_init_msg_list => p_init_msg_list
452 , x_return_status => l_return_status
453 , x_msg_count => x_msg_count
454 , x_msg_data => x_msg_data
455 , p_irhv_rec => p_irhv_rec
456 , x_irhv_rec => x_irhv_rec);
457 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
458 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
459 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
460 RAISE OKL_API.G_EXCEPTION_ERROR;
461 END IF;
462
463 --Set the ITEM_RESIDUAL_ID in the versions table
464 lp_icpv_rec.item_residual_id := x_irhv_rec.item_residual_id;
465 lp_icpv_rec.version_number := '1';
466 lp_icpv_rec.object_version_number := 1;
467 -- Insert row in the versions table
468 OKL_ICP_PVT.insert_row(
469 p_api_version => p_api_version
470 , p_init_msg_list => p_init_msg_list
471 , x_return_status => l_return_status
472 , x_msg_count => x_msg_count
473 , x_msg_data => x_msg_data
474 , p_icpv_rec => lp_icpv_rec
475 , x_icpv_rec => x_icpv_rec);
476 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
477 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
478 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
479 RAISE OKL_API.G_EXCEPTION_ERROR;
480 END IF;
481
482 IF lp_irv_tbl.COUNT > 0 THEN
483 -- validate the Values of terms
484 IF p_irhv_rec.residual_type_code = G_RESD_PERCENTAGE THEN
485 validate_percent_values(lp_irv_tbl);
486 END IF;
487 -- Set the ITEM_RESDL_VERSION_ID, OBJECT_VERSION_NUMBER columns of OKL_FE_ITEM_RESDL_VALUES table.
488 FOR i IN lp_irv_tbl.FIRST..lp_irv_tbl.LAST
489 LOOP
490 lp_irv_tbl(i).item_residual_id := x_icpv_rec.item_residual_id;
491 lp_irv_tbl(i).item_resdl_version_id := x_icpv_rec.id;
492 lp_irv_tbl(i).object_version_number := 1;
493 END LOOP;
494
495 -- Insert the lines - term value pairs for the item residual
496 OKL_IRV_PVT.insert_row(
497 p_api_version => p_api_version
498 , p_init_msg_list => p_init_msg_list
499 , x_return_status => l_return_status
500 , x_msg_count => x_msg_count
501 , x_msg_data => x_msg_data
502 , p_irv_tbl => lp_irv_tbl
503 , x_irv_tbl => lx_irv_tbl );
504 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
505 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
506 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
507 RAISE OKL_API.G_EXCEPTION_ERROR;
508 END IF;
509 END IF; -- end of check for empty term- value pairs
510
511 x_return_status := l_return_status;
512 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
513
514 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
515 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRIRSB.pls call create_irs');
516 END IF;
517
518 EXCEPTION
519 WHEN OKL_API.G_EXCEPTION_ERROR THEN
520
521 x_return_status := OKL_API.HANDLE_EXCEPTIONS
522 (
523 l_api_name,
524 G_PKG_NAME,
525 'OKL_API.G_RET_STS_ERROR',
526 x_msg_count,
527 x_msg_data,
528 '_PVT'
529 );
530
531 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
532
533 x_return_status := OKL_API.HANDLE_EXCEPTIONS
534 (
535 l_api_name,
536 G_PKG_NAME,
537 'OKL_API.G_RET_STS_UNEXP_ERROR',
538 x_msg_count,
539 x_msg_data,
540 '_PVT'
541 );
542
543 WHEN OTHERS THEN
544
545 x_return_status := OKL_API.HANDLE_EXCEPTIONS
546 (
547 l_api_name,
548 G_PKG_NAME,
549 'OTHERS',
550 x_msg_count,
551 x_msg_data,
552 '_PVT'
553 );
554 END create_irs;
555
556 PROCEDURE update_version_irs (
557 p_api_version IN NUMBER
558 , p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
559 , x_return_status OUT NOCOPY VARCHAR2
560 , x_msg_count OUT NOCOPY NUMBER
561 , x_msg_data OUT NOCOPY VARCHAR2
562 , p_irhv_rec IN okl_irhv_rec
563 , p_icpv_rec IN okl_icpv_rec
564 , p_irv_tbl IN okl_irv_tbl
565 , x_irhv_rec OUT NOCOPY okl_irhv_rec
566 , x_icpv_rec OUT NOCOPY okl_icpv_rec
567 ) IS
568 l_api_name CONSTANT VARCHAR2(40) := 'update_version_irs';
569 l_api_version CONSTANT NUMBER := p_api_version;
570 l_init_msg_list VARCHAR2(1) := p_init_msg_list;
571 l_msg_count NUMBER := x_msg_count ;
572 l_msg_data VARCHAR2(2000);
573 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
574 l_end_date DATE := NULL;
575 l_change_sts VARCHAR2(1) := 'n';
576 l_confirm_yn VARCHAR2(3) := 'yes';
577
578 lp_irhv_rec okl_irhv_rec := p_irhv_rec;
579 lp_icpv_rec okl_icpv_rec := p_icpv_rec;
580
581 lp_crt_irv_tbl okl_irv_tbl;
582 lp_upd_irv_tbl okl_irv_tbl;
583 lx_crt_irv_tbl okl_irv_tbl;
584 lx_upd_irv_tbl okl_irv_tbl;
585 l_lrs_list lrs_ref_tbl;
586 l_calc_date DATE := NULL;
587 l_db_ver_end_date DATE := NULL;
588 j NUMBER := 1;
589 k NUMBER := 1;
590
591 l_module CONSTANT fnd_log_messages.module%TYPE :='okl.plsql.OKL_ITEM_RESIDUALS_PVT.update_version_irs';
592 l_debug_enabled VARCHAR2(10);
593 is_debug_procedure_on BOOLEAN;
594 is_debug_statement_on BOOLEAN;
595
596 -- Select the effective to and compare.
597 CURSOR get_version_details(p_version_id NUMBER) IS
598 SELECT
599 ICP.END_DATE
600 FROM
601 OKL_ITM_CAT_RV_PRCS ICP
602 WHERE
603 ICP.ID = p_version_id;
604
605 BEGIN
606 l_debug_enabled := okl_debug_pub.check_log_enabled;
607 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
608 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
609 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRIRSB.pls call update_version_irs');
610 END IF;
611
612 -- Initialize the OUT records
613 x_irhv_rec := p_irhv_rec;
614 x_icpv_rec := p_icpv_rec;
615
616 l_return_status := OKL_API.start_activity(l_api_name
617 ,G_PKG_NAME
618 ,p_init_msg_list
619 ,l_api_version
620 ,p_api_version
621 ,'_PVT'
622 ,x_return_status);
623 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
624 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
625 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
626 RAISE OKL_API.G_EXCEPTION_ERROR;
627 END IF;
628
629 /* Calculate the effective date */
630 get_effective_date(
631 l_api_version
632 , l_init_msg_list
633 , l_return_status
634 , x_msg_count
635 , x_msg_data
636 , lp_icpv_rec.id
637 , l_calc_date
638 );
639 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
640 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
641 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
642 RAISE OKL_API.G_EXCEPTION_ERROR;
643 END IF;
644
645 OPEN get_version_details(lp_icpv_rec.id);
646 FETCH get_version_details INTO l_db_ver_end_date;
647 CLOSE get_version_details;
648
649 -- Set for G_MISS_CHANGES. Set to G_MISS if null is to be updated
650 IF lp_icpv_rec.end_date IS NULL THEN
651 lp_icpv_rec.end_date := OKL_API.G_MISS_DATE;
652 END IF;
653
654 OKL_ICP_PVT.update_row(
655 p_api_version => p_api_version
656 , p_init_msg_list => p_init_msg_list
657 , x_return_status => l_return_status
658 , x_msg_count => x_msg_count
659 , x_msg_data => x_msg_data
660 , p_icpv_rec => lp_icpv_rec
661 , x_icpv_rec => x_icpv_rec);
662 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
663 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
664 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
665 RAISE OKL_API.G_EXCEPTION_ERROR;
666 END IF;
667
668 j := 1;
669 k:=1;
670 IF p_irv_tbl.COUNT >0 THEN
671 -- validate the Values of terms
672 IF p_irhv_rec.residual_type_code = G_RESD_PERCENTAGE THEN
673 validate_percent_values(p_irv_tbl);
674 END IF;
675 FOR i IN p_irv_tbl.FIRST..p_irv_tbl.LAST
676 LOOP
677 IF p_irv_tbl(i).item_resdl_value_id IS NULL OR p_irv_tbl(i).item_resdl_value_id = OKL_API.G_MISS_NUM THEN
678 lp_crt_irv_tbl(j) := p_irv_tbl(i);
679 lp_crt_irv_tbl(j).item_residual_id := x_icpv_rec.item_residual_id;
680 lp_crt_irv_tbl(j).item_resdl_version_id := x_icpv_rec.id;
681 j := j+1;
682
683 ELSE
684 lp_upd_irv_tbl(k) := p_irv_tbl(i);
685 lp_upd_irv_tbl(j).item_resdl_version_id := p_icpv_rec.id;
686 k := k+1;
687
688 END IF;
689 END LOOP;
690
691 -- Update the existing term value pairs if any
692 IF lp_upd_irv_tbl.COUNT >0 THEN
693 OKL_IRV_PVT.update_row(
694 p_api_version => p_api_version
695 , p_init_msg_list => p_init_msg_list
696 , x_return_status => l_return_status
697 , x_msg_count => x_msg_count
698 , x_msg_data => x_msg_data
699 , p_irv_tbl => lp_upd_irv_tbl
700 , x_irv_tbl => lx_upd_irv_tbl );
701 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
702 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
703 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
704 RAISE OKL_API.G_EXCEPTION_ERROR;
705 END IF;
706 END IF;
707
708 IF lp_crt_irv_tbl.COUNT >0 THEN
709 OKL_IRV_PVT.insert_row(
710 p_api_version => p_api_version
711 , p_init_msg_list => p_init_msg_list
712 , x_return_status => l_return_status
713 , x_msg_count => x_msg_count
714 , x_msg_data => x_msg_data
715 , p_irv_tbl => lp_crt_irv_tbl
716 , x_irv_tbl => lx_crt_irv_tbl );
717 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
718 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
719 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
720 RAISE OKL_API.G_EXCEPTION_ERROR;
721 END IF;
722 END IF;
723 END IF;
724
725 -- If this is the latest active version
726 IF is_latest_active(x_icpv_rec.item_residual_id,x_icpv_rec.version_number) THEN
727 -- Check if any change in end date has been updated
728 IF l_db_ver_end_date IS NULL AND x_icpv_rec.end_date IS NOT NULL THEN
729
730 lp_irhv_rec.item_residual_id := x_icpv_rec.item_residual_id;
731 lp_irhv_rec.effective_to_date := x_icpv_rec.end_date;
732
733 -- Update the header's end date also.
734 OKL_IRH_PVT.update_row(
735 p_api_version => p_api_version
736 , p_init_msg_list => p_init_msg_list
737 , x_return_status => l_return_status
738 , x_msg_count => x_msg_count
739 , x_msg_data => x_msg_data
740 , p_irhv_rec => lp_irhv_rec
741 , x_irhv_rec => x_irhv_rec);
742 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
743 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
744 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
745 RAISE OKL_API.G_EXCEPTION_ERROR;
746 END IF;
747
748 -- Call the API to end date or abandon any Lease Rate Set referring to this item residual
749 change_LRS_sts (
750 p_api_version => p_api_version
751 , p_init_msg_list => p_init_msg_list
752 , x_return_status => l_return_status
753 , x_msg_count => x_msg_count
754 , x_msg_data => x_msg_count
755 , p_confirm_yn => l_confirm_yn
756 , p_icpv_rec => x_icpv_rec
757 , x_lrs_list => l_lrs_list
758 , x_change_sts => l_change_sts
759 );
760 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
761 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
762 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
763 RAISE OKL_API.G_EXCEPTION_ERROR;
764 END IF;
765 END IF; -- end of check for version end date change
766 END IF; -- end of check for latest version
767
768 x_return_status := l_return_status;
769 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
770
771 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
772 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRIRSB.pls call update_version_irs');
773 END IF;
774
775 EXCEPTION
776 WHEN OKL_API.G_EXCEPTION_ERROR THEN
777
778 x_return_status := OKL_API.HANDLE_EXCEPTIONS
779 (
780 l_api_name,
781 G_PKG_NAME,
782 'OKL_API.G_RET_STS_ERROR',
783 x_msg_count,
784 x_msg_data,
785 '_PVT'
786 );
787
788 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
789
790 x_return_status := OKL_API.HANDLE_EXCEPTIONS
791 (
792 l_api_name,
793 G_PKG_NAME,
794 'OKL_API.G_RET_STS_UNEXP_ERROR',
795 x_msg_count,
796 x_msg_data,
797 '_PVT'
798 );
799
800 WHEN OTHERS THEN
801
802 x_return_status := OKL_API.HANDLE_EXCEPTIONS
803 (
804 l_api_name,
805 G_PKG_NAME,
806 'OTHERS',
807 x_msg_count,
808 x_msg_data,
809 '_PVT'
810 );
811 END update_version_irs;
812
813 procedure create_version_irs (
814 p_api_version IN NUMBER
815 , p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
816 , x_return_status OUT NOCOPY VARCHAR2
817 , x_msg_count OUT NOCOPY NUMBER
818 , x_msg_data OUT NOCOPY VARCHAR2
819 , p_irhv_rec IN okl_irhv_rec
820 , p_icpv_rec IN okl_icpv_rec
821 , p_irv_tbl IN okl_irv_tbl
822 , x_irhv_rec OUT NOCOPY okl_irhv_rec
823 , x_icpv_rec OUT NOCOPY okl_icpv_rec
824 ) IS
825 l_api_name CONSTANT VARCHAR2(40) := 'create_version_irs';
826 l_api_version CONSTANT NUMBER := p_api_version;
827 l_init_msg_list VARCHAR2(1) := p_init_msg_list;
828 l_msg_count NUMBER := x_msg_count ;
829 l_msg_data VARCHAR2(2000);
830 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
831
832 lx_irv_tbl okl_irv_tbl;
833 lp_irhv_rec okl_irhv_rec := p_irhv_rec;
834 lp_icpv_rec okl_icpv_rec := p_icpv_rec;
835 lp_irv_tbl okl_irv_tbl := p_irv_tbl;
836 l_new_version_no NUMBER := '1';
837
838 l_module CONSTANT fnd_log_messages.module%TYPE :='okl.plsql.OKL_ITEM_RESIDUALS_PVT.create_version_irs';
839 l_debug_enabled VARCHAR2(10);
840 is_debug_procedure_on BOOLEAN;
841 is_debug_statement_on BOOLEAN;
842
843 BEGIN
844 l_debug_enabled := okl_debug_pub.check_log_enabled;
845 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
846 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
847 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRIRSB.pls call create_version_irs');
848 END IF;
849
850 l_return_status := OKL_API.start_activity(l_api_name
851 ,G_PKG_NAME
852 ,p_init_msg_list
853 ,l_api_version
854 ,p_api_version
855 ,'_PVT'
856 ,x_return_status);
857 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
858 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
859 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
860 RAISE OKL_API.G_EXCEPTION_ERROR;
861 END IF;
862
863 lp_icpv_rec.item_residual_id := lp_irhv_rec.item_residual_id;
864
865 -- Insert the new version in the OKL_ITM_CAT_RV_PRCS table.
866 OKL_ICP_PVT.insert_row(
867 p_api_version => p_api_version
868 , p_init_msg_list => p_init_msg_list
869 , x_return_status => l_return_status
870 , x_msg_count => x_msg_count
871 , x_msg_data => x_msg_data
872 , p_icpv_rec => lp_icpv_rec
873 , x_icpv_rec => x_icpv_rec);
874 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
875 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
876 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
877 RAISE OKL_API.G_EXCEPTION_ERROR;
878 END IF;
879
880 IF lp_irv_tbl.COUNT > 0 THEN
881 -- validate the Values of terms
882 IF p_irhv_rec.residual_type_code = G_RESD_PERCENTAGE THEN
883 validate_percent_values(lp_irv_tbl);
884 END IF;
885
886 FOR i IN p_irv_tbl.FIRST..p_irv_tbl.LAST
887 LOOP
888 lp_irv_tbl(i).item_residual_id := x_icpv_rec.item_residual_id;
889 lp_irv_tbl(i).item_resdl_version_id := x_icpv_rec.id;
890 END LOOP;
891
892 -- Insert the lines - term value pairs for the new version
893 OKL_IRV_PVT.insert_row(
894 p_api_version => p_api_version
895 , p_init_msg_list => p_init_msg_list
896 , x_return_status => l_return_status
897 , x_msg_count => x_msg_count
898 , x_msg_data => x_msg_data
899 , p_irv_tbl => lp_irv_tbl
900 , x_irv_tbl => lx_irv_tbl );
901 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
902 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
903 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
904 RAISE OKL_API.G_EXCEPTION_ERROR;
905 END IF;
906 END IF;
907
908 -- Update the header record with the Under revision status
909 lp_irhv_rec.sts_code := G_STS_UNDER_REV;
910 -- lp_irhv_rec.effective_to_date := x_icpv_rec.end_date;
911 OKL_IRH_PVT.update_row(
912 p_api_version => p_api_version
913 , p_init_msg_list => p_init_msg_list
914 , x_return_status => l_return_status
915 , x_msg_count => x_msg_count
916 , x_msg_data => x_msg_data
917 , p_irhv_rec => lp_irhv_rec
918 , x_irhv_rec => x_irhv_rec);
919 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
920 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
921 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
922 RAISE OKL_API.G_EXCEPTION_ERROR;
923 END IF;
924
925 x_return_status := l_return_status;
926 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
927 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
928 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRIRSB.pls call create_version_irs');
929 END IF;
930
931 EXCEPTION
932 WHEN OKL_API.G_EXCEPTION_ERROR THEN
933
934 x_return_status := OKL_API.HANDLE_EXCEPTIONS
935 (
936 l_api_name,
937 G_PKG_NAME,
938 'OKL_API.G_RET_STS_ERROR',
939 x_msg_count,
940 x_msg_data,
941 '_PVT'
942 );
943
944 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
945
946 x_return_status := OKL_API.HANDLE_EXCEPTIONS
947 (
948 l_api_name,
949 G_PKG_NAME,
950 'OKL_API.G_RET_STS_UNEXP_ERROR',
951 x_msg_count,
952 x_msg_data,
953 '_PVT'
954 );
955 WHEN OTHERS THEN
956
957 x_return_status := OKL_API.HANDLE_EXCEPTIONS
958 (
959 l_api_name,
960 G_PKG_NAME,
961 'OTHERS',
962 x_msg_count,
963 x_msg_data,
964 '_PVT'
965 );
966 END create_version_irs;
967
968 PROCEDURE change_LRS_sts (
969 p_api_version IN NUMBER
970 , p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
971 , x_return_status OUT NOCOPY VARCHAR2
972 , x_msg_count OUT NOCOPY NUMBER
973 , x_msg_data OUT NOCOPY VARCHAR2
974 , p_confirm_yn IN VARCHAR2
975 , p_icpv_rec IN okl_icpv_rec
976 , x_lrs_list OUT NOCOPY lrs_ref_tbl
977 , x_change_sts OUT NOCOPY VARCHAR2 -- Indicates if the lease rate set needs to be abandoned
978 ) IS
979 l_api_name CONSTANT VARCHAR2(40) := 'change_LRS_sts';
980 l_api_version CONSTANT NUMBER := p_api_version;
981 l_init_msg_list VARCHAR2(1) := p_init_msg_list;
982 l_msg_count NUMBER := x_msg_count ;
983 l_msg_data VARCHAR2(2000);
984 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
985 l_lrs_list lrs_ref_tbl;
986 lp_lrtv_tbl okl_lrs_id_tbl;
987
988 l_src_code VARCHAR2(30) := NULL;
989 l_ver_end_date DATE := NULL;
990 i NUMBER := 1;
991
992 -- cursor to retrieve all the Lease rate sets that reference the item
993 CURSOR lrs_itm_csr(p_ver_id IN NUMBER) IS
994 SELECT
995 LRFVERV.RATE_SET_VERSION_ID LRS_VERSION_ID
996 , LRFV.NAME LRS_NAME
997 , LRFVERV.VERSION_NUMBER LRS_VERSION_NUMBER
998 FROM
999 OKL_FE_ITEM_RESIDUAL IRHV
1000 , OKL_ITM_CAT_RV_PRCS_V ICPV
1001 , OKL_FE_EO_TERM_OBJECTS EOTL
1002 , OKL_FE_EO_TERM_VERS_V EOTVERV
1003 , OKL_FE_RATE_SET_VERSIONS_V LRFVERV
1004 , OKL_LS_RT_FCTR_SETS_V LRFV
1005 WHERE
1006 EOTL.END_OF_TERM_VER_ID = EOTVERV.END_OF_TERM_VER_ID
1007 AND EOTVERV.END_OF_TERM_VER_ID = LRFVERV.END_OF_TERM_VER_ID
1008 AND EOTVERV.EOT_TYPE_CODE = 'RESIDUAL_PERCENT'
1009 AND EOTVERV.CATEGORY_TYPE_CODE = IRHV.CATEGORY_TYPE_CODE
1010 AND EOTVERV.END_OF_TERM_ID = LRFV.END_OF_TERM_ID
1011 AND LRFVERV.RATE_SET_ID = LRFV.ID
1012 AND LRFVERV.STS_CODE = 'ACTIVE'
1013 AND IRHV.INVENTORY_ITEM_ID = EOTL.INVENTORY_ITEM_ID
1014 AND IRHV.ORGANIZATION_ID = EOTL.ORGANIZATION_ID
1015 -- AND IRHV.CATEGORY_SET_ID = EOTL.CATEGORY_SET_ID
1016 AND ICPV.ITEM_RESIDUAL_ID = IRHV.ITEM_RESIDUAL_ID
1017 AND NVL(p_icpv_rec.end_date,LRFVERV.EFFECTIVE_FROM_DATE +1) < LRFVERV.EFFECTIVE_FROM_DATE
1018 AND LRFVERV.EFFECTIVE_FROM_DATE >= ICPV.START_DATE
1019 AND ICPV.ID = p_ver_id;
1020
1021 -- cursor to retrieve all the Lease rate sets that reference the item category
1022 CURSOR lrs_itm_cat_csr(p_ver_id IN NUMBER) IS
1023 SELECT
1024 LRFVERV.RATE_SET_VERSION_ID LRS_VERSION_ID
1025 , LRFV.NAME LRS_NAME
1026 , LRFVERV.VERSION_NUMBER LRS_VERSION_NUMBER
1027 FROM
1028 OKL_FE_ITEM_RESIDUAL IRHV
1029 , OKL_ITM_CAT_RV_PRCS_V ICPV
1030 , OKL_FE_EO_TERM_OBJECTS EOTL
1031 , OKL_FE_EO_TERM_VERS_V EOTVERV
1032 , OKL_FE_RATE_SET_VERSIONS_V LRFVERV
1033 , OKL_LS_RT_FCTR_SETS_V LRFV
1034 WHERE
1035 EOTL.END_OF_TERM_VER_ID = EOTVERV.END_OF_TERM_VER_ID
1036 AND EOTVERV.END_OF_TERM_VER_ID = LRFVERV.END_OF_TERM_VER_ID
1037 AND EOTVERV.EOT_TYPE_CODE = 'RESIDUAL_PERCENT'
1038 AND EOTVERV.CATEGORY_TYPE_CODE = IRHV.CATEGORY_TYPE_CODE
1039 AND EOTVERV.END_OF_TERM_ID = LRFV.END_OF_TERM_ID
1040 AND LRFVERV.RATE_SET_ID = LRFV.ID
1041 AND LRFVERV.STS_CODE = 'ACTIVE'
1042 AND IRHV.CATEGORY_ID = EOTL.CATEGORY_ID
1043 AND IRHV.CATEGORY_SET_ID = EOTL.CATEGORY_SET_ID
1044 AND ICPV.ITEM_RESIDUAL_ID = IRHV.ITEM_RESIDUAL_ID
1045 AND NVL(p_icpv_rec.end_date,LRFVERV.EFFECTIVE_FROM_DATE +1) < LRFVERV.EFFECTIVE_FROM_DATE
1046 AND LRFVERV.EFFECTIVE_FROM_DATE >= ICPV.START_DATE
1047 AND ICPV.ID = p_ver_id;
1048
1049 -- cursor to retrieve all the Lease rate sets that reference the residual category set
1050 CURSOR lrs_res_cat_csr(p_ver_id IN NUMBER) IS
1051 SELECT
1052 LRFVERV.RATE_SET_VERSION_ID LRS_VERSION_ID
1053 , LRFV.NAME LRS_NAME
1054 , LRFVERV.VERSION_NUMBER LRS_VERSION_NUMBER
1055 FROM
1056 OKL_FE_ITEM_RESIDUAL IRHV
1057 , OKL_ITM_CAT_RV_PRCS_V ICPV
1058 , OKL_FE_EO_TERM_OBJECTS EOTL
1059 , OKL_FE_EO_TERM_VERS_V EOTVERV
1060 , OKL_FE_RATE_SET_VERSIONS_V LRFVERV
1061 , OKL_LS_RT_FCTR_SETS_V LRFV
1062 WHERE
1063 EOTL.END_OF_TERM_VER_ID = EOTVERV.END_OF_TERM_VER_ID
1064 AND EOTVERV.END_OF_TERM_VER_ID = LRFVERV.END_OF_TERM_VER_ID
1065 AND EOTVERV.EOT_TYPE_CODE = 'RESIDUAL_PERCENT'
1066 AND EOTVERV.CATEGORY_TYPE_CODE = IRHV.CATEGORY_TYPE_CODE
1067 AND EOTVERV.END_OF_TERM_ID = LRFV.END_OF_TERM_ID
1068 AND LRFVERV.RATE_SET_ID = LRFV.ID
1069 AND LRFVERV.STS_CODE = 'ACTIVE'
1070 AND IRHV.RESI_CATEGORY_SET_ID = EOTL.RESI_CATEGORY_SET_ID
1071 AND ICPV.ITEM_RESIDUAL_ID = IRHV.ITEM_RESIDUAL_ID
1072 AND NVL(p_icpv_rec.end_date,LRFVERV.EFFECTIVE_FROM_DATE +1) < LRFVERV.EFFECTIVE_FROM_DATE
1073 AND LRFVERV.EFFECTIVE_FROM_DATE >= ICPV.START_DATE
1074 AND ICPV.ID = p_ver_id;
1075
1076 -- Identifies whether this item residual is for an Item or Item category or a residual category set
1077 CURSOR get_category_type(p_item_residual_id NUMBER)IS
1078 SELECT
1079 CATEGORY_TYPE_CODE
1080 FROM
1081 OKL_FE_ITEM_RESIDUAL
1082 WHERE
1083 ITEM_RESIDUAL_ID = p_item_residual_id; -- Item residual ID
1084
1085 -- Cursor to identify change in version effective To date
1086 CURSOR get_version_date (p_version_id NUMBER) IS
1087 SELECT
1088 end_date
1089 FROM
1090 OKL_ITM_CAT_RV_PRCS_V
1091 WHERE
1092 ID = p_version_id;
1093
1094 l_module CONSTANT fnd_log_messages.module%TYPE :='okl.plsql.OKL_ITEM_RESIDUALS_PVT.change_LRS_sts';
1095 l_debug_enabled VARCHAR2(10);
1096 is_debug_procedure_on BOOLEAN;
1097 is_debug_statement_on BOOLEAN;
1098
1099 BEGIN
1100 l_debug_enabled := okl_debug_pub.check_log_enabled;
1101 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
1102 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
1103 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRIRSB.pls call change_LRS_sts');
1104 END IF;
1105
1106
1107 l_return_status := OKL_API.start_activity(l_api_name
1108 ,G_PKG_NAME
1109 ,p_init_msg_list
1110 ,l_api_version
1111 ,p_api_version
1112 ,'_PVT'
1113 ,x_return_status);
1114 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1115 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1116 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1117 RAISE OKL_API.G_EXCEPTION_ERROR;
1118 END IF;
1119
1120 /*
1121 Select the category type of the item residual.
1122 */
1123 OPEN get_category_type(p_icpv_rec.item_residual_id);
1124 FETCH get_category_type INTO l_src_code;
1125 CLOSE get_category_type;
1126
1127 OPEN get_version_date (p_icpv_rec.id);
1128 FETCH get_version_date INTO l_ver_end_date;
1129 CLOSE get_version_date;
1130
1131 i :=1;
1132 -- Donot query for the LRS versions if the Item Residual version has not been end-dated
1133 -- This check is ensured by checking the p_icpv_rec.end_date to null and the prior value in db to be null
1134 IF (p_confirm_yn ='no' AND l_ver_end_date IS NULL AND p_icpv_rec.end_date IS NOT NULL) OR p_confirm_yn = 'yes' THEN
1135 CASE l_src_code
1136 WHEN G_CAT_ITEM THEN
1137 FOR lrs_record IN lrs_itm_csr(p_icpv_rec.id)
1138 LOOP
1139 l_lrs_list(i).ID := lrs_record.LRS_VERSION_ID;
1140 l_lrs_list(i).NAME := lrs_record.LRS_NAME;
1141 l_lrs_list(i).VERSION := lrs_record.LRS_VERSION_NUMBER;
1142 i := i+1;
1143 END LOOP;
1144 WHEN G_CAT_ITEM_CAT THEN
1145 FOR lrs_record IN lrs_itm_cat_csr(p_icpv_rec.id)
1146 LOOP
1147 l_lrs_list(i).ID := lrs_record.LRS_VERSION_ID;
1148 l_lrs_list(i).NAME := lrs_record.LRS_NAME;
1149 l_lrs_list(i).VERSION := lrs_record.LRS_VERSION_NUMBER;
1150 i := i+1;
1151 END LOOP;
1152 WHEN G_CAT_RES_CAT THEN
1153 FOR lrs_record IN lrs_res_cat_csr(p_icpv_rec.id)
1154 LOOP
1155 l_lrs_list(i).ID := lrs_record.LRS_VERSION_ID;
1156 l_lrs_list(i).NAME := lrs_record.LRS_NAME;
1157 l_lrs_list(i).VERSION := lrs_record.LRS_VERSION_NUMBER;
1158 i := i+1;
1159 END LOOP;
1160 END CASE;
1161 END IF; --end of version end date null check
1162
1163 -- Set out values
1164 x_lrs_list := l_lrs_list;
1165 IF i > 1 THEN
1166 x_change_sts := 'y';
1167 ELSE
1168 x_change_sts := 'n';
1169 END IF;
1170
1171 -- Update the status of the LRS if the flag is confirmed
1172 IF p_confirm_yn ='yes' THEN
1173
1174 /* Call the API of LRS which in end dating or abandoning the LRS
1175 with lp_lrtv_tbl as the argument.
1176 */
1177 -- Check if the there are any Lease rate sets referencing this item residual
1178 IF x_change_sts = 'y' THEN
1179
1180 FOR i IN l_lrs_list.FIRST..l_lrs_list.LAST
1181 LOOP
1182 lp_lrtv_tbl(i) := l_lrs_list(i).id;
1183 END LOOP;
1184
1185 OKL_LEASE_RATE_SETS_PVT.enddate_lease_rate_set(
1186 p_api_version => p_api_version
1187 , p_init_msg_list => p_init_msg_list
1188 , x_return_status => l_return_status
1189 , x_msg_count => l_msg_count
1190 , x_msg_data => l_msg_data
1191 , p_lrv_id_tbl => lp_lrtv_tbl
1192 , p_end_date => p_icpv_rec.end_date
1193 );
1194
1195 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1196 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1197 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1198 RAISE OKL_API.G_EXCEPTION_ERROR;
1199 END IF;
1200 END IF;
1201 END IF; -- End of confirmation check
1202
1203 x_return_status := l_return_status;
1204 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1205
1206 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
1207 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRIRSB.pls call change_LRS_sts');
1208 END IF;
1209
1210 EXCEPTION
1211 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1212
1213 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1214 (
1215 l_api_name,
1216 G_PKG_NAME,
1217 'OKL_API.G_RET_STS_ERROR',
1218 x_msg_count,
1219 x_msg_data,
1220 '_PVT'
1221 );
1222
1223 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1224
1225 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1226 (
1227 l_api_name,
1228 G_PKG_NAME,
1229 'OKL_API.G_RET_STS_UNEXP_ERROR',
1230 x_msg_count,
1231 x_msg_data,
1232 '_PVT'
1233 );
1234 WHEN OTHERS THEN
1235
1236 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1237 (
1238 l_api_name,
1239 G_PKG_NAME,
1240 'OTHERS',
1241 x_msg_count,
1242 x_msg_data,
1243 '_PVT'
1244 );
1245 END change_LRS_sts;
1246
1247 -- Returns the status of the header based on the statuses of the versions.
1248 FUNCTION get_header_status (var_item_residual_id NUMBER) RETURN VARCHAR2 IS
1249 var_new_sts BOOLEAN := TRUE;
1250 var_active_sts BOOLEAN := TRUE;
1251 var_return_sts VARCHAR2(30);
1252 -- Cursor to retrieve all versions of a residual
1253 CURSOR all_versions (residual_id NUMBER) IS
1254 SELECT
1255 ICPV.STS_CODE STATUS_CODE
1256 FROM
1257 OKL_ITM_CAT_RV_PRCS_V ICPV
1258 WHERE
1259 ICPV.ITEM_RESIDUAL_ID = residual_id;
1260 BEGIN
1261
1262 FOR var_sts IN all_versions(var_item_residual_id)
1263 LOOP
1264 IF var_sts.STATUS_CODE <> G_STS_NEW THEN
1265 var_new_sts := FALSE;
1266 ELSIF var_sts.STATUS_CODE <> G_STS_ACTIVE THEN
1267 var_active_sts := FALSE;
1268 END IF;
1269 END LOOP;
1270
1271 IF var_new_sts THEN
1272 var_return_sts := G_STS_NEW;
1273 ELSIF var_active_sts THEN
1274 var_return_sts := G_STS_ACTIVE;
1275 ELSE
1276 var_return_sts := G_STS_UNDER_REV;
1277 END IF;
1278
1279 RETURN var_return_sts;
1280 END get_header_status;
1281
1282 PROCEDURE activate_item_residual(
1283 p_api_version IN NUMBER
1284 , p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
1285 , x_return_status OUT NOCOPY VARCHAR2
1286 , x_msg_count OUT NOCOPY NUMBER
1287 , x_msg_data OUT NOCOPY VARCHAR2
1288 , p_item_resdl_version_id IN NUMBER ) IS
1289 l_api_name CONSTANT VARCHAR2(40) := 'activate_item_residual';
1290 l_api_version CONSTANT NUMBER := p_api_version;
1291 l_init_msg_list VARCHAR2(1) := p_init_msg_list;
1292 l_msg_count NUMBER := x_msg_count ;
1293 l_msg_data VARCHAR2(2000);
1294 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1295
1296 l_version_no VARCHAR2(30) ;
1297 l_residual_id NUMBER;
1298 l_change_sts VARCHAR2(1) := 'n';
1299 l_confirm_yn VARCHAR2(3) := 'yes';
1300
1301 lp_icpv_curr_rec okl_icpv_rec;
1302 lp_icpv_prev_rec okl_icpv_rec;
1303 lp_irhv_rec okl_irhv_rec;
1304
1305 lx_icpv_curr_rec okl_icpv_rec;
1306 lx_icpv_prev_rec okl_icpv_rec;
1307 lx_irhv_rec okl_irhv_rec;
1308
1309 l_lrs_list lrs_ref_tbl;
1310
1311 CURSOR get_version_details(var_version_id NUMBER) IS
1312 SELECT
1313 ICPV.ITEM_RESIDUAL_ID
1314 , ICPV.VERSION_NUMBER
1315 , IRHV.OBJECT_VERSION_NUMBER HDR_OBJECT_VERSION_NUMBER
1316 FROM
1317 OKL_ITM_CAT_RV_PRCS ICPV
1318 , OKL_FE_ITEM_RESIDUAL IRHV
1319 WHERE
1320 IRHV.ITEM_RESIDUAL_ID = ICPV.ITEM_RESIDUAL_ID
1321 AND ICPV.ID = var_version_id;
1322
1323 CURSOR get_version_rec(var_residual_id NUMBER, ver_no VARCHAR2) IS
1324 SELECT
1325 ICPV.ID ID
1326 , ICPV.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
1327 , ICPV.ITEM_RESIDUAL_ID ITEM_RESIDUAL_ID
1328 , ICPV.STS_CODE STS_CODE
1329 , ICPV.VERSION_NUMBER VERSION_NUMBER
1330 , ICPV.START_DATE EFFECTIVE_FROM_DATE
1331 , ICPV.END_DATE EFFECTIVE_TO_DATE
1332 FROM
1333 OKL_ITM_CAT_RV_PRCS_V ICPV
1334 WHERE
1335 ICPV.ITEM_RESIDUAL_ID = var_residual_id
1336 AND ICPV.VERSION_NUMBER = ver_no;
1337
1338
1339 l_version_rec get_version_rec%ROWTYPE;
1340 l_version_details get_version_details%ROWTYPE;
1341
1342
1343 l_module CONSTANT fnd_log_messages.module%TYPE :='okl.plsql.OKL_ITEM_RESIDUALS_PVT.activate_item_residual';
1344 l_debug_enabled VARCHAR2(10);
1345 is_debug_procedure_on BOOLEAN;
1346 is_debug_statement_on BOOLEAN;
1347 BEGIN
1348 l_debug_enabled := okl_debug_pub.check_log_enabled;
1349 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
1350 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
1351 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRIRSB.pls call activate_item_residual');
1352 END IF;
1353
1354 l_return_status := OKL_API.start_activity(l_api_name
1355 ,G_PKG_NAME
1356 ,p_init_msg_list
1357 ,l_api_version
1358 ,p_api_version
1359 ,'_PVT'
1360 ,x_return_status);
1361 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1362 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1363 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1364 RAISE OKL_API.G_EXCEPTION_ERROR;
1365 END IF;
1366
1367 -- Get the residual id and the version number of the version to be approved
1368 OPEN get_version_details(p_item_resdl_version_id);
1369 FETCH get_version_details INTO l_version_details ;
1370 CLOSE get_version_details;
1371
1372 l_residual_id := l_version_details.item_residual_id;
1373 l_version_no := l_version_details.version_number;
1374
1375 -- Set the residual id version number of header
1376 lp_irhv_rec.item_residual_id := l_residual_id;
1377 lp_irhv_rec.object_version_number := l_version_details.hdr_object_version_number;
1378
1379 -- Get the version details of the version to be approved
1380 OPEN get_version_rec(l_residual_id, l_version_no );
1381 FETCH get_version_rec INTO l_version_rec;
1382 CLOSE get_version_rec;
1383
1384 lp_icpv_curr_rec.id := l_version_rec.id;
1385 lp_icpv_curr_rec.object_version_number := l_version_rec.object_version_number;
1386 lp_icpv_curr_rec.start_date := l_version_rec.effective_from_date;
1387 lp_icpv_curr_rec.end_date := l_version_rec.effective_to_date;
1388
1389 --1.Make version status active
1390 lp_icpv_curr_rec.sts_code := G_STS_ACTIVE;
1391
1392 --2.put header eff to date as eff to of this version if this is the latest version
1393 IF is_latest(l_residual_id,l_version_no) THEN
1394 IF lp_icpv_curr_rec.end_date IS NOT NULL THEN
1395 lp_irhv_rec.effective_to_date := lp_icpv_curr_rec.end_date;
1396 ELSE
1397 -- If it is null, expilcitly assign G_MISS_DATE to update null in table
1398 lp_irhv_rec.effective_to_date := OKL_API.G_MISS_DATE;
1399 END IF;
1400 END IF;
1401
1402 --3.if this is the first version then dont do the effective_from validation
1403 --Else Put effective to date of previous version as new ver eff from -1
1404 IF TO_NUMBER(l_version_no) > 1 THEN
1405 -- Get the previous version record details
1406 OPEN get_version_rec(l_residual_id, l_version_no-1 );
1407 FETCH get_version_rec INTO l_version_rec;
1408 CLOSE get_version_rec;
1409
1410 -- end of check for no change in effective TO date
1411 IF lp_icpv_prev_rec.end_date IS NULL OR (lp_icpv_prev_rec.end_date IS NOT NULL AND lp_icpv_prev_rec.end_date <> lp_icpv_curr_rec.START_DATE -1) THEN
1412 lp_icpv_prev_rec.id := l_version_rec.id;
1413 lp_icpv_prev_rec.object_version_number := l_version_rec.object_version_number;
1414 lp_icpv_prev_rec.end_date := lp_icpv_curr_rec.START_DATE -1;
1415
1416 -- Update the previous version's effectiveTo date
1417 OKL_ICP_PVT.update_row(
1418 p_api_version => p_api_version
1419 , p_init_msg_list => p_init_msg_list
1420 , x_return_status => l_return_status
1421 , x_msg_count => x_msg_count
1422 , x_msg_data => x_msg_data
1423 , p_icpv_rec => lp_icpv_prev_rec
1424 , x_icpv_rec => lx_icpv_prev_rec);
1425 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1426 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1427 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1428 RAISE OKL_API.G_EXCEPTION_ERROR;
1429 END IF;
1430 END IF; -- end of check for no change in effective TO date
1431
1432 -- Check if the effective to date is already present for the previous version
1433 IF l_version_rec.effective_to_date IS NULL THEN
1434 -- End date or Abandon any Lease Rate Sets referencing this Item resiudal
1435 change_LRS_sts (
1436 p_api_version => p_api_version
1437 , p_init_msg_list => p_init_msg_list
1438 , x_return_status => l_return_status
1439 , x_msg_count => x_msg_count
1440 , x_msg_data => x_msg_count
1441 , p_confirm_yn => l_confirm_yn
1442 , p_icpv_rec => lx_icpv_prev_rec
1443 , x_lrs_list => l_lrs_list
1444 , x_change_sts => l_change_sts
1445 );
1446 END IF; -- End of check for previous end date null
1447
1448 END IF; -- end of version number check
1449 -- Update the current version record status
1450 -- Update the previous version's effectiveTo date
1451 OKL_ICP_PVT.update_row(
1452 p_api_version => p_api_version
1453 , p_init_msg_list => p_init_msg_list
1454 , x_return_status => l_return_status
1455 , x_msg_count => x_msg_count
1456 , x_msg_data => x_msg_data
1457 , p_icpv_rec => lp_icpv_curr_rec
1458 , x_icpv_rec => lx_icpv_curr_rec);
1459 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1460 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1461 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1462 RAISE OKL_API.G_EXCEPTION_ERROR;
1463 END IF;
1464 --4.Set the header status
1465 lp_irhv_rec.sts_code := get_header_status(l_residual_id);
1466
1467 --update the header
1468 OKL_IRH_PVT.update_row(
1469 p_api_version => p_api_version
1470 , p_init_msg_list => p_init_msg_list
1471 , x_return_status => l_return_status
1472 , x_msg_count => x_msg_count
1473 , x_msg_data => x_msg_data
1474 , p_irhv_rec => lp_irhv_rec
1475 , x_irhv_rec => lx_irhv_rec);
1476 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1477 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1478 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1479 RAISE OKL_API.G_EXCEPTION_ERROR;
1480 END IF;
1481 x_return_status := l_return_status;
1482 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1483
1484 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
1485 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRIRSB.pls.pls call activate_item_residual');
1486 END IF;
1487
1488 EXCEPTION
1489 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1490
1491 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1492 (
1493 l_api_name,
1494 G_PKG_NAME,
1495 'OKL_API.G_RET_STS_ERROR',
1496 x_msg_count,
1497 x_msg_data,
1498 '_PVT'
1499 );
1500
1501 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1502
1503 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1504 (
1505 l_api_name,
1506 G_PKG_NAME,
1507 'OKL_API.G_RET_STS_UNEXP_ERROR',
1508 x_msg_count,
1509 x_msg_data,
1510 '_PVT'
1511 );
1512 WHEN OTHERS THEN
1513
1514 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1515 (
1516 l_api_name,
1517 G_PKG_NAME,
1518 'OTHERS',
1519 x_msg_count,
1520 x_msg_data,
1521 '_PVT'
1522 );
1523 END activate_item_residual;
1524
1525 PROCEDURE submit_item_residual(
1526 p_api_version IN NUMBER,
1527 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1528 x_return_status OUT NOCOPY VARCHAR2,
1529 x_msg_count OUT NOCOPY NUMBER,
1530 x_msg_data OUT NOCOPY VARCHAR2,
1531 p_itm_rsdl_version_id IN OKL_ITM_CAT_RV_PRCS_V.ID%TYPE
1532 )IS
1533 l_api_name CONSTANT VARCHAR2(65) := 'submit_item_residual';
1534 l_api_version CONSTANT NUMBER := p_api_version;
1535 lx_return_status VARCHAR2(1);
1536 l_parameter_list WF_PARAMETER_LIST_T;
1537 l_event_name wf_events.name%TYPE;
1538
1539 l_module CONSTANT fnd_log_messages.module%TYPE :='okl.plsql.OKL_ITEM_RESIDUALS_PVT.submit_item_residual';
1540 l_debug_enabled VARCHAR2(10);
1541 is_debug_procedure_on BOOLEAN;
1542 is_debug_statement_on BOOLEAN;
1543
1544 -- Cusrsor to fetch the Residual ID of the version
1545 CURSOR get_residual_id(p_itm_rsdl_version_id NUMBER) IS
1546 SELECT
1547 ICPV.ITEM_RESIDUAL_ID
1548 FROM
1549 OKL_ITM_CAT_RV_PRCS_V ICPV
1550 WHERE
1551 ICPV.ID = p_itm_rsdl_version_id;
1552
1553 -- Cusrsor to fetch the Source type(also called Category Type)
1554 CURSOR get_source_type(p_itm_rsdl_id NUMBER) IS
1555 SELECT
1556 IRESDV.CATEGORY_TYPE_CODE
1557 FROM
1558 OKL_FE_ITEM_RESIDUAL IRESDV
1559 WHERE
1560 IRESDV.item_residual_id = p_itm_rsdl_id;
1561
1562 -- Cursor to check if the residual category sets are active before Activating the Item Residual
1563 -- Pass the Item Residual Identifier and the Status as ACTIVE to check for Inactive Residual Category Sets
1564 CURSOR check_active_resi_cat_sets(p_itm_rsdl_id NUMBER, p_rcs_sts_code VARCHAR2) IS
1565 SELECT
1566 RCSV.RESI_CATEGORY_SET_ID ID
1567 , RCSV.RESI_CAT_NAME NAME
1568 FROM
1569 OKL_FE_RESI_CAT_V RCSV
1570 , OKL_FE_ITEM_RESIDUAL IRESDV
1571 WHERE
1572 IRESDV.CATEGORY_TYPE_CODE = G_CAT_RES_CAT
1573 AND IRESDV.RESI_CATEGORY_SET_ID = RCSV.RESI_CATEGORY_SET_ID
1574 AND RCSV.STS_CODE <> p_rcs_sts_code
1575 AND IRESDV.item_residual_id = p_itm_rsdl_id;
1576
1577 l_item_residual_id NUMBER;
1578 l_source_type OKL_FE_ITEM_RESIDUAL_ALL.CATEGORY_TYPE_CODE%TYPE;
1579 l_rcs_rec check_active_resi_cat_sets%ROWTYPE;
1580
1581 BEGIN
1582 l_debug_enabled := okl_debug_pub.check_log_enabled;
1583 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
1584 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
1585 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRIRSB.pls call submit_item_residual');
1586 END IF;
1587
1588 lx_return_status := OKL_API.start_activity(l_api_name
1589 ,G_PKG_NAME
1590 ,p_init_msg_list
1591 ,l_api_version
1592 ,p_api_version
1593 ,'_PVT'
1594 ,x_return_status);
1595 IF lx_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1596 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1597 ELSIF lx_return_status = OKL_API.G_RET_STS_ERROR THEN
1598 RAISE OKL_API.G_EXCEPTION_ERROR;
1599 END IF;
1600
1601 -- Check if all the Residual category sets of this Item Residual are Active at the time of Submission
1602 OPEN get_residual_id(p_itm_rsdl_version_id);
1603 FETCH get_residual_id INTO l_item_residual_id;
1604 CLOSE get_residual_id;
1605
1606 OPEN get_source_type (l_item_residual_id);
1607 FETCH get_source_type INTO l_source_type;
1608 CLOSE get_source_type;
1609
1610 IF l_source_type = G_CAT_RES_CAT THEN
1611 OPEN check_active_resi_cat_sets(l_item_residual_id,G_STS_ACTIVE);
1612 FETCH check_active_resi_cat_sets INTO l_rcs_rec;
1613 IF check_active_resi_cat_sets%FOUND THEN
1614 LOOP
1615 OKL_API.set_message(p_app_name => G_APP_NAME,
1616 p_msg_name => 'OKL_RCS_STS_INACTIVE',
1617 p_token1 => G_COL_NAME_TOKEN,
1618 p_token1_value => l_rcs_rec.name);
1619 FETCH check_active_resi_cat_sets INTO l_rcs_rec;
1620 EXIT WHEN check_active_resi_cat_sets%NOTFOUND;
1621 END LOOP;
1622 RAISE OKL_API.G_EXCEPTION_ERROR;
1623 END IF;
1624 CLOSE check_active_resi_cat_sets;
1625 END IF;
1626 --raise workflow submit event
1627 l_event_name := G_WF_EVT_IRS_PENDING;
1628
1629 wf_event.AddParameterToList(G_WF_IRS_VERSION_ID, p_itm_rsdl_version_id, l_parameter_list);
1630 --added by akrangan
1631 wf_event.AddParameterToList('ORG_ID',mo_global.get_current_org_id ,l_parameter_list);
1632
1633 -- Check for the AME approval process
1634 IF NVL(FND_PROFILE.VALUE('OKL_PE_APPROVAL_PROCESS'),'NONE') = 'NONE' THEN
1635 activate_item_residual(p_api_version,p_init_msg_list,lx_return_status,x_msg_count,x_msg_data,p_itm_rsdl_version_id);
1636 IF lx_return_status = OKL_API.G_RET_STS_ERROR THEN
1637 RAISE OKL_API.G_EXCEPTION_ERROR;
1638 ELSIF lx_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1639 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1640 END IF;
1641
1642 ELSE
1643 OKL_WF_PVT.raise_event(p_api_version => p_api_version,
1644 p_init_msg_list => p_init_msg_list,
1645 x_return_status => lx_return_status,
1646 x_msg_count => x_msg_count,
1647 x_msg_data => x_msg_data,
1648 p_event_name => l_event_name,
1649 p_parameters => l_parameter_list);
1650 IF lx_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1651 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1652 ELSIF lx_return_status = OKL_API.G_RET_STS_ERROR THEN
1653 RAISE OKL_API.G_EXCEPTION_ERROR;
1654 END IF;
1655 END IF;
1656 x_return_status := lx_return_status;
1657 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1658
1659 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
1660 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRIRSB.pls call submit_item_residual');
1661 END IF;
1662
1663 EXCEPTION
1664 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1665
1666 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1667 (
1668 l_api_name,
1669 G_PKG_NAME,
1670 'OKL_API.G_RET_STS_ERROR',
1671 x_msg_count,
1672 x_msg_data,
1673 '_PVT'
1674 );
1675
1676 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1677
1678 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1679 (
1680 l_api_name,
1681 G_PKG_NAME,
1682 'OKL_API.G_RET_STS_UNEXP_ERROR',
1683 x_msg_count,
1684 x_msg_data,
1685 '_PVT'
1686 );
1687
1688 WHEN OTHERS THEN
1689
1690 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1691 (
1692 l_api_name,
1693 G_PKG_NAME,
1694 'OTHERS',
1695 x_msg_count,
1696 x_msg_data,
1697 '_PVT'
1698 );
1699 END submit_item_residual;
1700
1701 PROCEDURE create_irs_submit (
1702 p_api_version IN NUMBER
1703 , p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
1704 , x_return_status OUT NOCOPY VARCHAR2
1705 , x_msg_count OUT NOCOPY NUMBER
1706 , x_msg_data OUT NOCOPY VARCHAR2
1707 , p_irhv_rec IN okl_irhv_rec
1708 , p_icpv_rec IN okl_icpv_rec
1709 , p_irv_tbl IN okl_irv_tbl
1710 , x_irhv_rec OUT NOCOPY okl_irhv_rec
1711 , x_icpv_rec OUT NOCOPY okl_icpv_rec
1712 ) IS
1713 l_api_name CONSTANT VARCHAR2(40) := 'create_irs_submit';
1714 l_api_version CONSTANT NUMBER := p_api_version;
1715 l_init_msg_list VARCHAR2(1) := p_init_msg_list;
1716 l_msg_count NUMBER := x_msg_count ;
1717 l_msg_data VARCHAR2(2000);
1718 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1719
1720 lp_icpv_rec okl_icpv_rec := p_icpv_rec;
1721
1722 l_module CONSTANT fnd_log_messages.module%TYPE :='okl.plsql.OKL_ITEM_RESIDUALS_PVT.create_irs_submit';
1723 l_debug_enabled VARCHAR2(10);
1724 is_debug_procedure_on BOOLEAN;
1725 is_debug_statement_on BOOLEAN;
1726
1727 BEGIN
1728 l_debug_enabled := okl_debug_pub.check_log_enabled;
1729 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
1730 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
1731 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRIRSB.pls call create_irs_submit');
1732 END IF;
1733
1734 l_return_status := OKL_API.start_activity(l_api_name
1735 ,G_PKG_NAME
1736 ,p_init_msg_list
1737 ,l_api_version
1738 ,p_api_version
1739 ,'_PVT'
1740 ,x_return_status);
1741 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1742 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1743 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1744 RAISE OKL_API.G_EXCEPTION_ERROR;
1745 END IF;
1746 -- Set the status changes for the version record.
1747 lp_icpv_rec.sts_code := G_STS_SUBMITTED;
1748
1749 /* Check if there is atleast one item or item category or residual category set
1750 * associated with this item residual.
1751 */
1752 IF p_irv_tbl.COUNT = 0 THEN
1753 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1754 p_msg_name => 'OKL_ST_IRS_RESIDUALS_MISSING');
1755 RAISE OKL_API.G_EXCEPTION_ERROR; END IF;
1756
1757
1758 -- Call the create_irs procedure to create the item residual
1759 create_irs (
1760 p_api_version => l_api_version
1761 , p_init_msg_list => l_init_msg_list
1762 , x_return_status => l_return_status
1763 , x_msg_count => l_msg_count
1764 , x_msg_data => l_msg_data
1765 , p_irhv_rec => p_irhv_rec
1766 , p_icpv_rec => lp_icpv_rec
1767 , p_irv_tbl => p_irv_tbl
1768 , x_irhv_rec => x_irhv_rec
1769 , x_icpv_rec => x_icpv_rec
1770 );
1771 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1772 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1773 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1774 RAISE OKL_API.G_EXCEPTION_ERROR;
1775 END IF;
1776
1777 -- submit for approval
1778 submit_item_residual(
1779 p_api_version => l_api_version
1780 , p_init_msg_list => l_init_msg_list
1781 , x_return_status => l_return_status
1782 , x_msg_count => l_msg_count
1783 , x_msg_data => l_msg_data
1784 , p_itm_rsdl_version_id => x_icpv_rec.id );
1785 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1786 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1787 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1788 RAISE OKL_API.G_EXCEPTION_ERROR;
1789 END IF;
1790
1791 x_return_status := l_return_status;
1792 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1793
1794 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
1795 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRIRSB.pls call create_irs_submit');
1796 END IF;
1797
1798 EXCEPTION
1799 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1800
1801 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1802 (
1803 l_api_name,
1804 G_PKG_NAME,
1805 'OKL_API.G_RET_STS_ERROR',
1806 x_msg_count,
1807 x_msg_data,
1808 '_PVT'
1809 );
1810
1811 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1812
1813 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1814 (
1815 l_api_name,
1816 G_PKG_NAME,
1817 'OKL_API.G_RET_STS_UNEXP_ERROR',
1818 x_msg_count,
1819 x_msg_data,
1820 '_PVT'
1821 );
1822
1823 WHEN OTHERS THEN
1824
1825 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1826 (
1827 l_api_name,
1828 G_PKG_NAME,
1829 'OTHERS',
1830 x_msg_count,
1831 x_msg_data,
1832 '_PVT'
1833 );
1834 END create_irs_submit;
1835
1836 PROCEDURE update_version_irs_submit (
1837 p_api_version IN NUMBER
1838 , p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
1839 , x_return_status OUT NOCOPY VARCHAR2
1840 , x_msg_count OUT NOCOPY NUMBER
1841 , x_msg_data OUT NOCOPY VARCHAR2
1842 , p_irhv_rec IN okl_irhv_rec
1843 , p_icpv_rec IN okl_icpv_rec
1844 , p_irv_tbl IN okl_irv_tbl
1845 , x_irhv_rec OUT NOCOPY okl_irhv_rec
1846 , x_icpv_rec OUT NOCOPY okl_icpv_rec
1847 ) IS
1848 l_api_name CONSTANT VARCHAR2(40) := 'update_version_irs_submit';
1849 l_api_version CONSTANT NUMBER := p_api_version;
1850 l_init_msg_list VARCHAR2(1) := p_init_msg_list;
1851 l_msg_count NUMBER := x_msg_count ;
1852 l_msg_data VARCHAR2(2000);
1853 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1854
1855 lp_icpv_rec okl_icpv_rec := p_icpv_rec;
1856
1857 l_module CONSTANT fnd_log_messages.module%TYPE :='okl.plsql.OKL_ITEM_RESIDUALS_PVT.update_version_irs_submit';
1858 l_debug_enabled VARCHAR2(10);
1859 is_debug_procedure_on BOOLEAN;
1860 is_debug_statement_on BOOLEAN;
1861 BEGIN
1862 l_debug_enabled := okl_debug_pub.check_log_enabled;
1863 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
1864 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
1865 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRIRSB.pls call update_version_irs_submit');
1866 END IF;
1867
1868 l_return_status := OKL_API.start_activity(l_api_name
1869 ,G_PKG_NAME
1870 ,p_init_msg_list
1871 ,l_api_version
1872 ,p_api_version
1873 ,'_PVT'
1874 ,x_return_status);
1875 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1876 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1877 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1878 RAISE OKL_API.G_EXCEPTION_ERROR;
1879 END IF;
1880
1881 -- Set the status changes for the version record.
1882 lp_icpv_rec.sts_code := G_STS_SUBMITTED;
1883
1884 -- Effective From validation
1885 IF NOT valid_version_effective_date(lp_icpv_rec.START_DATE, p_irhv_rec.item_residual_id, lp_icpv_rec.version_number) THEN
1886 RAISE OKL_API.G_EXCEPTION_ERROR;
1887 END IF;
1888
1889 /* Check if there is atleast one item or item category or residual category set
1890 * associated with this item residual.
1891 */
1892 IF p_irv_tbl.COUNT = 0 THEN
1893 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1894 p_msg_name => 'OKL_ST_IRS_RESIDUALS_MISSING');
1895 RAISE OKL_API.G_EXCEPTION_ERROR; END IF;
1896
1897 -- Call the update_version_irs procedure to create the item residual
1898 update_version_irs (
1899 p_api_version => l_api_version
1900 , p_init_msg_list => l_init_msg_list
1901 , x_return_status => l_return_status
1902 , x_msg_count => x_msg_count
1903 , x_msg_data => x_msg_data
1904 , p_irhv_rec => p_irhv_rec
1905 , p_icpv_rec => lp_icpv_rec
1906 , p_irv_tbl => p_irv_tbl
1907 , x_irhv_rec => x_irhv_rec
1908 , x_icpv_rec => x_icpv_rec
1909 );
1910 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1911 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1912 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1913 RAISE OKL_API.G_EXCEPTION_ERROR;
1914 END IF;
1915 -- submit for approval
1916 submit_item_residual(
1917 p_api_version => l_api_version
1918 , p_init_msg_list => l_init_msg_list
1919 , x_return_status => l_return_status
1920 , x_msg_count => l_msg_count
1921 , x_msg_data => l_msg_data
1922 , p_itm_rsdl_version_id => x_icpv_rec.id );
1923 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
1924 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1925 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
1926 RAISE OKL_API.G_EXCEPTION_ERROR;
1927 END IF;
1928 x_return_status := l_return_status;
1929
1930 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1931
1932 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
1933 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRIRSB.pls call update_version_irs_submit');
1934 END IF;
1935
1936 EXCEPTION
1937 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1938
1939 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1940 (
1941 l_api_name,
1942 G_PKG_NAME,
1943 'OKL_API.G_RET_STS_ERROR',
1944 x_msg_count,
1945 x_msg_data,
1946 '_PVT'
1947 );
1948
1949 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1950
1951 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1952 (
1953 l_api_name,
1954 G_PKG_NAME,
1955 'OKL_API.G_RET_STS_UNEXP_ERROR',
1956 x_msg_count,
1957 x_msg_data,
1958 '_PVT'
1959 );
1960
1961 WHEN OTHERS THEN
1962
1963 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1964 (
1965 l_api_name,
1966 G_PKG_NAME,
1967 'OTHERS',
1968 x_msg_count,
1969 x_msg_data,
1970 '_PVT'
1971 );
1972 END update_version_irs_submit;
1973
1974 PROCEDURE create_version_irs_submit (
1975 p_api_version IN NUMBER
1976 , p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
1977 , x_return_status OUT NOCOPY VARCHAR2
1978 , x_msg_count OUT NOCOPY NUMBER
1979 , x_msg_data OUT NOCOPY VARCHAR2
1980 , p_irhv_rec IN okl_irhv_rec
1981 , p_icpv_rec IN okl_icpv_rec
1982 , p_irv_tbl IN okl_irv_tbl
1983 , x_irhv_rec OUT NOCOPY okl_irhv_rec
1984 , x_icpv_rec OUT NOCOPY okl_icpv_rec
1985 ) IS
1986 l_api_name CONSTANT VARCHAR2(40) := 'create_version_irs_submit';
1987 l_api_version CONSTANT NUMBER := p_api_version;
1988 l_init_msg_list VARCHAR2(1) := p_init_msg_list;
1989 l_msg_count NUMBER := x_msg_count ;
1990 l_msg_data VARCHAR2(2000);
1991 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1992
1993 lp_icpv_rec okl_icpv_rec := p_icpv_rec;
1994 l_calc_date DATE := NULL;
1995 l_prev_ver_id NUMBER;
1996
1997 CURSOR get_prev_ver(p_item_residual_id NUMBER, p_ver_no NUMBER) IS
1998 SELECT
1999 ID
2000 FROM
2001 OKL_ITM_CAT_RV_PRCS_V
2002 WHERE
2003 ITEM_RESIDUAL_ID = p_item_residual_id
2004 AND TO_NUMBER(VERSION_NUMBER) = p_ver_no - 1;
2005
2006 l_module CONSTANT fnd_log_messages.module%TYPE :='okl.plsql.OKL_ITEM_RESIDUALS_PVT.create_version_irs_submit';
2007 l_debug_enabled VARCHAR2(10);
2008 is_debug_procedure_on BOOLEAN;
2009 is_debug_statement_on BOOLEAN;
2010
2011 BEGIN
2012 l_debug_enabled := okl_debug_pub.check_log_enabled;
2013 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
2014 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
2015 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRIRSB.pls call create_version_irs_submit');
2016 END IF;
2017
2018 l_return_status := OKL_API.start_activity(l_api_name
2019 ,G_PKG_NAME
2020 ,p_init_msg_list
2021 ,l_api_version
2022 ,p_api_version
2023 ,'_PVT'
2024 ,x_return_status);
2025 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
2026 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2027 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
2028 RAISE OKL_API.G_EXCEPTION_ERROR;
2029 END IF;
2030
2031 -- Set the status changes for the version record.
2032 lp_icpv_rec.sts_code := G_STS_SUBMITTED;
2033
2034 -- Effective From validation
2035 IF NOT valid_version_effective_date(lp_icpv_rec.START_DATE, p_irhv_rec.item_residual_id, lp_icpv_rec.version_number) THEN
2036 RAISE OKL_API.G_EXCEPTION_ERROR;
2037 END IF;
2038
2039
2040 IF lp_icpv_rec.version_number > 1 THEN
2041 OPEN get_prev_ver(p_irhv_rec.item_residual_id, TO_NUMBER(lp_icpv_rec.version_number));
2042 FETCH get_prev_ver INTO l_prev_ver_id;
2043 CLOSE get_prev_ver;
2044
2045 /* Calculate the effective date */
2046 get_effective_date(
2047 l_api_version
2048 , l_init_msg_list
2049 , l_return_status
2050 , x_msg_count
2051 , x_msg_data
2052 , l_prev_ver_id
2053 , l_calc_date
2054 );
2055
2056 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
2057 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2058 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
2059 RAISE OKL_API.G_EXCEPTION_ERROR;
2060 END IF;
2061 /*
2062 * Check if end dating the previous version is valid
2063 * by checking the calculated date with the new version's start date.
2064 */
2065 IF l_calc_date > lp_icpv_rec.start_date THEN
2066 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
2067 p_msg_name => 'OKL_PRIOR_EFFECTIVE_FROM',
2068 p_token1 => 'EFF_FROM',
2069 p_token1_value => l_calc_date);
2070 RAISE OKL_API.G_EXCEPTION_ERROR;
2071 END IF;
2072 END IF; -- end of version check
2073
2074 /* Check if there is atleast one item or item category or residual category set
2075 * associated with this item residual.
2076 */
2077 IF p_irv_tbl.COUNT = 0 THEN
2078 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
2079 p_msg_name => 'OKL_ST_IRS_RESIDUALS_MISSING');
2080 RAISE OKL_API.G_EXCEPTION_ERROR;
2081 END IF;
2082
2083 -- Call the update_version_irs procedure to create the item residual
2084 create_version_irs (
2085 p_api_version => l_api_version
2086 , p_init_msg_list => l_init_msg_list
2087 , x_return_status => l_return_status
2088 , x_msg_count => x_msg_count
2089 , x_msg_data => x_msg_data
2090 , p_irhv_rec => p_irhv_rec
2091 , p_icpv_rec => lp_icpv_rec
2092 , p_irv_tbl => p_irv_tbl
2093 , x_irhv_rec => x_irhv_rec
2094 , x_icpv_rec => x_icpv_rec
2095 );
2096 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
2097 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2098 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
2099 RAISE OKL_API.G_EXCEPTION_ERROR;
2100 END IF;
2101 -- submit for approval
2102 submit_item_residual(
2103 p_api_version => l_api_version
2104 , p_init_msg_list => l_init_msg_list
2105 , x_return_status => l_return_status
2106 , x_msg_count => l_msg_count
2107 , x_msg_data => l_msg_data
2108 , p_itm_rsdl_version_id => x_icpv_rec.id );
2109 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
2110 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2111 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
2112 RAISE OKL_API.G_EXCEPTION_ERROR;
2113 END IF;
2114
2115 x_return_status := l_return_status;
2116
2117 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2118
2119 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
2120 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRIRSB.pls call create_version_irs_submit');
2121 END IF;
2122 EXCEPTION
2123 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2124
2125 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2126 (
2127 l_api_name,
2128 G_PKG_NAME,
2129 'OKL_API.G_RET_STS_ERROR',
2130 x_msg_count,
2131 x_msg_data,
2132 '_PVT'
2133 );
2134
2135 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2136
2137 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2138 (
2139 l_api_name,
2140 G_PKG_NAME,
2141 'OKL_API.G_RET_STS_UNEXP_ERROR',
2142 x_msg_count,
2143 x_msg_data,
2144 '_PVT'
2145 );
2146
2147 WHEN OTHERS THEN
2148
2149 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2150 (
2151 l_api_name,
2152 G_PKG_NAME,
2153 'OTHERS',
2154 x_msg_count,
2155 x_msg_data,
2156 '_PVT'
2157 );
2158 END create_version_irs_submit;
2159
2160 PROCEDURE remove_terms(
2161 p_api_version IN NUMBER
2162 , p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
2163 , x_return_status OUT NOCOPY VARCHAR2
2164 , x_msg_count OUT NOCOPY NUMBER
2165 , x_msg_data OUT NOCOPY VARCHAR2
2166 , p_irv_tbl IN okl_irv_tbl) IS
2167 l_api_name CONSTANT VARCHAR2(40) := 'remove_terms';
2168 l_api_version NUMBER := p_api_version;
2169 l_init_msg_list VARCHAR2(1) := p_init_msg_list;
2170 l_return_status VARCHAR2(1) := x_return_status;
2171
2172 l_module CONSTANT fnd_log_messages.module%TYPE :='okl.plsql.OKL_ITEM_RESIDUALS_PVT.remove_terms';
2173 l_debug_enabled VARCHAR2(10);
2174 is_debug_procedure_on BOOLEAN;
2175 is_debug_statement_on BOOLEAN;
2176
2177 BEGIN
2178 l_debug_enabled := okl_debug_pub.check_log_enabled;
2179 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module,FND_LOG.LEVEL_PROCEDURE);
2180 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
2181 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRIRSB.pls call remove_terms');
2182 END IF;
2183
2184 l_return_status := OKL_API.start_activity(l_api_name
2185 ,G_PKG_NAME
2186 ,p_init_msg_list
2187 ,l_api_version
2188 ,p_api_version
2189 ,'_PVT'
2190 ,x_return_status);
2191 IF l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR THEN
2192 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2193 ELSIF l_return_status = OKL_API.G_RET_STS_ERROR THEN
2194 RAISE OKL_API.G_EXCEPTION_ERROR;
2195 END IF;
2196
2197 OKL_IRV_PVT.delete_row(
2198 p_api_version => l_api_version,
2199 p_init_msg_list => l_init_msg_list,
2200 x_return_status => l_return_status,
2201 x_msg_count => x_msg_count,
2202 x_msg_data => x_msg_data,
2203 p_irv_tbl => p_irv_tbl);
2204
2205 x_return_status := l_return_status;
2206
2207 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2208
2209 IF(l_debug_enabled='Y' AND is_debug_procedure_on) THEN
2210 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRIRSB.pls call remove_terms');
2211 END IF;
2212
2213 EXCEPTION
2214 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2215
2216 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2217 (
2218 l_api_name,
2219 G_PKG_NAME,
2220 'OKL_API.G_RET_STS_ERROR',
2221 x_msg_count,
2222 x_msg_data,
2223 '_PVT'
2224 );
2225
2226 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2227
2228 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2229 (
2230 l_api_name,
2231 G_PKG_NAME,
2232 'OKL_API.G_RET_STS_UNEXP_ERROR',
2233 x_msg_count,
2234 x_msg_data,
2235 '_PVT'
2236 );
2237
2238 WHEN OTHERS THEN
2239
2240 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2241 (
2242 l_api_name,
2243 G_PKG_NAME,
2244 'OTHERS',
2245 x_msg_count,
2246 x_msg_data,
2247 '_PVT'
2248 );
2249 END remove_terms;
2250
2251 END OKL_ITEM_RESIDUALS_PVT; -- End of package Body