[Home] [Help]
PACKAGE BODY: APPS.OKL_TXL_ITM_INSTS_PVT
Source
1 package body OKL_TXL_ITM_INSTS_PVT as
2 /* $Header: OKLCITIB.pls 120.6 2005/10/30 04:03:39 appldev noship $ */
3
4 /*
5 * sjalasut: aug 18, 04 added constants used in raising business event. BEGIN
6 */
7 G_WF_EVT_ASSET_SERIAL_CRTD CONSTANT VARCHAR2(65) := 'oracle.apps.okl.la.lease_contract.asset_serial_numbers_created';
8 G_WF_EVT_ASSET_SERIAL_RMVD CONSTANT VARCHAR2(65) := 'oracle.apps.okl.la.lease_contract.remove_asset_serial_numbers';
9 G_WF_ITM_CONTRACT_ID CONSTANT VARCHAR2(30) := 'CONTRACT_ID';
10 G_WF_ITM_ASSET_ID CONSTANT VARCHAR2(30) := 'ASSET_ID';
11 G_WF_ITM_SERIAL_NUM CONSTANT VARCHAR2(30) := 'SERIAL_NUMBER';
12 G_WF_ITM_CONTRACT_PROCESS CONSTANT VARCHAR2(30) := 'CONTRACT_PROCESS';
13 /*
14 * sjalasut: aug 18, 04 added constants used in raising business event. END
15 */
16
17 /*
18 * sjalasut: aug 18, 04 added procedure to call private wrapper that raises the business event. BEGIN
19 * the procedure is located at the global level and not at the insert row level as the same procedure
20 * can later be used for capturing other DML logic.
21 */
22 -------------------------------------------------------------------------------
23 -- PROCEDURE raise_business_event
24 -------------------------------------------------------------------------------
25 -- Start of comments
26 --
27 -- Procedure Name : raise_business_event
28 -- Description : This procedure is a wrapper that raises a business event
29 -- : when ever asset serial numbers are created or deleted.
30 -- Business Rules :
31 -- Parameters : p_chr_id,p_asset_id, p_ser_num,p_event_name along with other api params
32 -- Version : 1.0
33 -- History : 30-AUG-2004 SJALASUT created
34 -- End of comments
35
36 PROCEDURE raise_business_event(p_api_version IN NUMBER,
37 p_init_msg_list IN VARCHAR2,
38 p_chr_id IN okc_k_headers_b.id%TYPE,
39 p_asset_id IN okc_k_lines_b.id%TYPE,
40 p_ser_num IN okl_txl_itm_insts.serial_number%TYPE,
41 p_event_name IN VARCHAR2,
42 x_return_status OUT NOCOPY VARCHAR2,
43 x_msg_count OUT NOCOPY NUMBER,
44 x_msg_data OUT NOCOPY VARCHAR2
45 ) IS
46 l_parameter_list wf_parameter_list_t;
47 l_contract_process VARCHAR2(20);
48 BEGIN
49 x_return_status := OKL_API.G_RET_STS_SUCCESS;
50
51 -- wrapper API to get contract process. this API determines in which status the
52 -- contract in question is.
53 l_contract_process := okl_lla_util_pvt.get_contract_process(p_chr_id => p_chr_id);
54 wf_event.AddParameterToList(G_WF_ITM_CONTRACT_ID, p_chr_id, l_parameter_list);
55 wf_event.AddParameterToList(G_WF_ITM_ASSET_ID, p_asset_id, l_parameter_list);
56 wf_event.AddParameterToList(G_WF_ITM_SERIAL_NUM, p_ser_num, l_parameter_list);
57 wf_event.AddParameterToList(G_WF_ITM_CONTRACT_PROCESS, l_contract_process, l_parameter_list);
58
59 OKL_WF_PVT.raise_event(p_api_version => p_api_version,
60 p_init_msg_list => p_init_msg_list,
61 x_return_status => x_return_status,
62 x_msg_count => x_msg_count,
63 x_msg_data => x_msg_data,
64 p_event_name => p_event_name,
65 p_parameters => l_parameter_list);
66 EXCEPTION
67 WHEN OTHERS THEN
68 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
69 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
70 END raise_business_event;
71
72 /*
73 * sjalasut: aug 18, 04 added procedure to call private wrapper that raises the business event. END
74 */
75
76
77 PROCEDURE Create_txl_itm_insts(
78 p_api_version IN NUMBER,
79 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
80 x_return_status OUT NOCOPY VARCHAR2,
81 x_msg_count OUT NOCOPY NUMBER,
82 x_msg_data OUT NOCOPY VARCHAR2,
83 p_iivv_rec IN iivv_rec_type,
84 x_iivv_rec OUT NOCOPY iivv_rec_type)
85 IS
86 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_TXL_ITM_INSTS';
87 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
88
89 /*
90 * sjalasut aug 18, 04: added cursor to derive the dnz_chr_id from the dnz_cle_id
91 * to pass the parameters to the business event. BEGIN
92 */
93 CURSOR get_chr_id (p_cle_id okc_k_lines_b.id%TYPE) IS
94 SELECT dnz_chr_id
95 FROM okc_k_lines_b
96 WHERE id = p_cle_id;
97
98 l_chr_id okc_k_headers_b.id%TYPE;
99 /*
100 * sjalasut aug 18, 04: added cursor to derive the dnz_chr_id from the dnz_cle_id
101 * to pass the parameters to the business event. END
102 */
103
104 BEGIN
105 -- Call start_activity to create savepoint, check compatibility
106 -- and initialize message list
107 l_return_status := OKC_API.START_ACTIVITY (l_api_name
108 ,p_init_msg_list
109 ,'_PVT'
110 ,x_return_status);
111 -- Check if activity started successfully
112 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
113 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
114 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
115 RAISE OKC_API.G_EXCEPTION_ERROR;
116 END IF;
117 -- evaluate conditions, build outcomes for true conditions and
118 -- put them on outcome queue
119 OKL_ITI_PVT.insert_row(p_api_version,
120 p_init_msg_list,
121 x_return_status,
122 x_msg_count,
123 x_msg_data,
124 p_iivv_rec,
125 x_iivv_rec);
126 /*
127 * sjalasut: oct 14, 04 added code to enable business event. BEGIN
128 * raise the event only if the context contract is LEASE contract
129 */
130 -- get the contract header id
131 OPEN get_chr_id(p_iivv_rec.DNZ_CLE_ID);
132 FETCH get_chr_id INTO l_chr_id;
133 CLOSE get_chr_id;
134 --vthiruva..09-Dec-2004..added condition to check thats serial number is not null
135 IF(OKL_LLA_UTIL_PVT.is_lease_contract(l_chr_id)= OKL_API.G_TRUE AND
136 p_iivv_rec.serial_number IS NOT NULL AND p_iivv_rec.serial_number <> OKL_API.G_MISS_CHAR)THEN
137 raise_business_event(p_api_version => p_api_version,
138 p_init_msg_list => p_init_msg_list,
139 p_chr_id => l_chr_id,
140 p_asset_id => p_iivv_rec.DNZ_CLE_ID,
141 p_ser_num => p_iivv_rec.serial_number,
142 p_event_name => G_WF_EVT_ASSET_SERIAL_CRTD,
143 x_return_status => x_return_status,
144 x_msg_count => x_msg_count,
145 x_msg_data => x_msg_data
146 );
147 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
148 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
149 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
150 RAISE OKL_API.G_EXCEPTION_ERROR;
151 END IF;
152 END IF;
153 /*
154 * sjalasut: oct 14, 04 added code to enable business event. END
155 */
156
157 OKC_API.END_ACTIVITY (x_msg_count
158 ,x_msg_data );
159 EXCEPTION
160 WHEN OKC_API.G_EXCEPTION_ERROR THEN
161 x_return_status := OKC_API.HANDLE_EXCEPTIONS
162 (l_api_name,
163 G_PKG_NAME,
164 'OKC_API.G_RET_STS_ERROR',
165 x_msg_count,
166 x_msg_data,
167 '_PVT');
168 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
169 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
170 (l_api_name,
171 G_PKG_NAME,
172 'OKC_API.G_RET_STS_UNEXP_ERROR',
173 x_msg_count,
174 x_msg_data,
175 '_PVT');
176 WHEN OTHERS THEN x_return_status :=OKC_API.HANDLE_EXCEPTIONS
177 (l_api_name,
178 G_PKG_NAME,
179 'OTHERS',
180 x_msg_count,
181 x_msg_data,
182 '_PVT');
183 END Create_txl_itm_insts;
184
185 PROCEDURE Create_txl_itm_insts(
186 p_api_version IN NUMBER,
187 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
188 x_return_status OUT NOCOPY VARCHAR2,
189 x_msg_count OUT NOCOPY NUMBER,
190 x_msg_data OUT NOCOPY VARCHAR2,
191 p_iivv_tbl IN iivv_tbl_type,
192 x_iivv_tbl OUT NOCOPY iivv_tbl_type)
193 IS
194 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_TXL_ITM_INSTS';
195 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
196 BEGIN
197 -- Call start_activity to create savepoint, check compatibility
198 -- and initialize message list
199 l_return_status := OKC_API.START_ACTIVITY (l_api_name
200 ,p_init_msg_list
201 ,'_PVT'
202 , x_return_status);
203 -- Check if activity started successfully
204 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
205 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
206 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
207 RAISE OKC_API.G_EXCEPTION_ERROR;
208 END IF;
209 -- evaluate conditions, build outcomes for true conditions and
210 -- put them on outcome queue
211 OKL_ITI_PVT.insert_row(p_api_version,
212 p_init_msg_list,
213 x_return_status,
214 x_msg_count,
215 x_msg_data,
216 p_iivv_tbl,
217 x_iivv_tbl);
218
219 OKC_API.END_ACTIVITY (x_msg_count
220 ,x_msg_data );
221
222 EXCEPTION
223 WHEN OKC_API.G_EXCEPTION_ERROR THEN
224 x_return_status := OKC_API.HANDLE_EXCEPTIONS
225 (l_api_name,
226 G_PKG_NAME,
227 'OKC_API.G_RET_STS_ERROR',
228 x_msg_count,
229 x_msg_data,
230 '_PVT');
231 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
232 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
233 (l_api_name,
234 G_PKG_NAME,
235 'OKC_API.G_RET_STS_UNEXP_ERROR',
236 x_msg_count,
237 x_msg_data,
238 '_PVT');
239 WHEN OTHERS THEN x_return_status :=OKC_API.HANDLE_EXCEPTIONS
240 ( l_api_name,
241 G_PKG_NAME,
242 'OTHERS',
243 x_msg_count,
244 x_msg_data,
245 '_PVT');
246 END Create_txl_itm_insts;
247
248 PROCEDURE lock_txl_itm_insts(
249 p_api_version IN NUMBER,
250 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
251 x_return_status OUT NOCOPY VARCHAR2,
252 x_msg_count OUT NOCOPY NUMBER,
253 x_msg_data OUT NOCOPY VARCHAR2,
254 p_iivv_rec IN iivv_rec_type)
255 IS
256 l_api_name CONSTANT VARCHAR2(30) := 'LOCK_TXL_ITM_INSTS';
257 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
258 BEGIN
259 -- Call start_activity to create savepoint, check compatibility
260 -- and initialize message list
261 l_return_status := OKC_API.START_ACTIVITY (l_api_name
262 ,p_init_msg_list
263 ,'_PVT'
264 ,x_return_status);
265 -- Check if activity started successfully
266 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
267 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
268 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
269 RAISE OKC_API.G_EXCEPTION_ERROR;
270 END IF;
271 -- evaluate conditions, build outcomes for true conditions and
272 -- put them on outcome queue
273 OKL_ITI_PVT.lock_row(p_api_version,
274 p_init_msg_list,
275 x_return_status,
276 x_msg_count,
277 x_msg_data,
278 p_iivv_rec);
279 OKC_API.END_ACTIVITY (x_msg_count
280 ,x_msg_data );
281 EXCEPTION
282 WHEN OKC_API.G_EXCEPTION_ERROR THEN
283 x_return_status := OKC_API.HANDLE_EXCEPTIONS
284 (l_api_name,
285 G_PKG_NAME,
286 'OKC_API.G_RET_STS_ERROR',
287 x_msg_count,
288 x_msg_data,
289 '_PVT');
290 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
291 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
292 (l_api_name,
293 G_PKG_NAME,
294 'OKC_API.G_RET_STS_UNEXP_ERROR',
295 x_msg_count,
296 x_msg_data,
297 '_PVT');
298 WHEN OTHERS THEN x_return_status :=OKC_API.HANDLE_EXCEPTIONS
299 (l_api_name,
300 G_PKG_NAME,
301 'OTHERS',
302 x_msg_count,
303 x_msg_data,
304 '_PVT');
305 END lock_txl_itm_insts;
306
307 PROCEDURE lock_txl_itm_insts(
308 p_api_version IN NUMBER,
309 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
310 x_return_status OUT NOCOPY VARCHAR2,
311 x_msg_count OUT NOCOPY NUMBER,
312 x_msg_data OUT NOCOPY VARCHAR2,
313 p_iivv_tbl IN iivv_tbl_type)
314 IS
315 l_api_name CONSTANT VARCHAR2(30) := 'LOCK_TXL_ITM_INSTS';
316 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
317 BEGIN
318 -- Call start_activity to create savepoint, check compatibility
319 -- and initialize message list
320 l_return_status := OKC_API.START_ACTIVITY (l_api_name
321 ,p_init_msg_list
322 ,'_PVT'
323 ,x_return_status);
324 -- Check if activity started successfully
325 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
326 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
327 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
328 RAISE OKC_API.G_EXCEPTION_ERROR;
329 END IF;
330 -- evaluate conditions, build outcomes for true conditions and
331 -- put them on outcome queue
332 OKL_ITI_PVT.lock_row(p_api_version,
333 p_init_msg_list,
334 x_return_status,
335 x_msg_count,
336 x_msg_data,
337 p_iivv_tbl);
338 OKC_API.END_ACTIVITY (x_msg_count
339 ,x_msg_data );
340 EXCEPTION
341 WHEN OKC_API.G_EXCEPTION_ERROR THEN
342 x_return_status := OKC_API.HANDLE_EXCEPTIONS
343 (l_api_name,
344 G_PKG_NAME,
345 'OKC_API.G_RET_STS_ERROR',
346 x_msg_count,
347 x_msg_data,
348 '_PVT');
349 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
350 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
351 (l_api_name,
352 G_PKG_NAME,
353 'OKC_API.G_RET_STS_UNEXP_ERROR',
354 x_msg_count,
355 x_msg_data,
356 '_PVT');
357 WHEN OTHERS THEN x_return_status :=OKC_API.HANDLE_EXCEPTIONS
358 (l_api_name,
359 G_PKG_NAME,
360 'OTHERS',
361 x_msg_count,
362 x_msg_data,
363 '_PVT');
364 END lock_txl_itm_insts;
365
366 PROCEDURE update_txl_itm_insts(
367 p_api_version IN NUMBER,
368 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
369 x_return_status OUT NOCOPY VARCHAR2,
370 x_msg_count OUT NOCOPY NUMBER,
371 x_msg_data OUT NOCOPY VARCHAR2,
372 p_iivv_rec IN iivv_rec_type,
373 x_iivv_rec OUT NOCOPY iivv_rec_type)
374 IS
375 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TXL_ITM_INSTS';
376 ln_chr_id OKC_K_LINES_B.DNZ_CHR_ID%TYPE;
377 -- Bug# 3477560
378 CURSOR get_chr_id(p_kle_id OKL_TXL_ASSETS_B.KLE_ID%TYPE)
379 IS
380 SELECT cle.dnz_chr_id
381 FROM okc_k_lines_b cle
382 WHERE cle.id = p_kle_id;
383 --vthiruva..09-Dec-2004..Added code to enable Business Events..START
384 --cursor to fetch the serial number of the item instance record being updated
385 CURSOR get_serial_num(p_id okl_txl_itm_insts.id%TYPE) IS
386 SELECT serial_number, dnz_cle_id
387 FROM okl_txl_itm_insts
388 WHERE id = p_id;
389
390 l_old_serial_num okl_txl_itm_insts.serial_number%TYPE;
391 l_asset_id okl_txl_itm_insts.dnz_cle_id%TYPE;
392 --vthiruva..09-Dec-2004..Added code to enable Business Events..END
393 BEGIN
394 x_return_status := OKC_API.G_RET_STS_SUCCESS;
395 -- Call start_activity to create savepoint, check compatibility
396 -- and initialize message list
397 x_return_status := OKC_API.START_ACTIVITY (l_api_name
398 ,p_init_msg_list
399 ,'_PVT'
400 ,x_return_status);
401 -- Check if activity started successfully
402 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
403 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
404 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
405 RAISE OKC_API.G_EXCEPTION_ERROR;
406 END IF;
407
408 --vthiruva..09-Dec-2004..Added code to enable Business Events..START
409 OPEN get_serial_num(p_iivv_rec.id);
410 IF get_serial_num%NOTFOUND THEN
411 RAISE OKC_API.G_EXCEPTION_ERROR;
412 END IF;
413 FETCH get_serial_num INTO l_old_serial_num, l_asset_id;
414 CLOSE get_serial_num;
415 --vthiruva..09-Dec-2004..Added code to enable Business Events..END
416 -- evaluate conditions, build outcomes for true conditions and
417 -- put them on outcome queue
418 OKL_ITI_PVT.update_row(p_api_version,
419 p_init_msg_list,
420 x_return_status,
421 x_msg_count,
422 x_msg_data,
423 p_iivv_rec,
424 x_iivv_rec);
425 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
426 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
427 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
428 RAISE OKC_API.G_EXCEPTION_ERROR;
429 END IF;
430 IF x_iivv_rec.tal_type in ('CFA','CIB','CRB','CRL','CRV','CSP','ALI') THEN
431 OPEN get_chr_id(x_iivv_rec.kle_Id);
432 IF get_chr_id%NOTFOUND THEN
433 RAISE OKC_API.G_EXCEPTION_ERROR;
434 END IF;
435 FETCH get_chr_id INTO ln_chr_id;
436 CLOSE get_chr_id;
437 -- We need to change the status of the header whenever there is updating happening
438 -- after the contract status is approved
439 IF (ln_chr_id is NOT NULL) AND
440 (ln_chr_id <> OKL_API.G_MISS_NUM) THEN
441 --cascade edit status on to lines
442 okl_contract_status_pub.cascade_lease_status_edit
443 (p_api_version => p_api_version,
444 p_init_msg_list => p_init_msg_list,
445 x_return_status => x_return_status,
446 x_msg_count => x_msg_count,
447 x_msg_data => x_msg_data,
448 p_chr_id => ln_chr_id);
449 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
450 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
451 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
452 RAISE OKC_API.G_EXCEPTION_ERROR;
453 END IF;
454 END IF;
455 --vthiruva..09-Dec-2004..Added code to enable Business Events..START
456 IF(OKL_LLA_UTIL_PVT.is_lease_contract(ln_chr_id)= OKL_API.G_TRUE)THEN
457 IF(l_old_serial_num IS NULL AND
458 (p_iivv_rec.serial_number IS NOT NULL AND
459 p_iivv_rec.serial_number <> OKL_API.G_MISS_CHAR)) THEN
460 raise_business_event(p_api_version => p_api_version,
461 p_init_msg_list => p_init_msg_list,
462 p_chr_id => ln_chr_id,
463 p_asset_id => p_iivv_rec.dnz_cle_id,
464 p_ser_num => p_iivv_rec.serial_number,
465 p_event_name => G_WF_EVT_ASSET_SERIAL_CRTD,
466 x_return_status => x_return_status,
467 x_msg_count => x_msg_count,
468 x_msg_data => x_msg_data
469 );
470 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
471 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
472 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
473 RAISE OKL_API.G_EXCEPTION_ERROR;
474 END IF;
475 ELSIF(l_old_serial_num IS NOT NULL AND
476 (p_iivv_rec.serial_number IS NULL OR
477 p_iivv_rec.serial_number = OKL_API.G_MISS_CHAR)) THEN
478 raise_business_event(p_api_version => p_api_version,
479 p_init_msg_list => p_init_msg_list,
480 p_chr_id => ln_chr_id,
481 p_asset_id => l_asset_id,
482 p_ser_num => l_old_serial_num,
483 p_event_name => G_WF_EVT_ASSET_SERIAL_RMVD,
484 x_return_status => x_return_status,
485 x_msg_count => x_msg_count,
486 x_msg_data => x_msg_data
487 );
488 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
489 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
490 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
491 RAISE OKL_API.G_EXCEPTION_ERROR;
492 END IF;
493 END IF;
494 END IF;
495 --vthiruva..09-Dec-2004..Added code to enable Business Events..END
496 END IF;
497 OKC_API.END_ACTIVITY (x_msg_count
498 ,x_msg_data );
499 EXCEPTION
500 WHEN OKC_API.G_EXCEPTION_ERROR THEN
501 x_return_status := OKC_API.HANDLE_EXCEPTIONS
502 (l_api_name,
503 G_PKG_NAME,
504 'OKC_API.G_RET_STS_ERROR',
505 x_msg_count,
506 x_msg_data,
507 '_PVT');
508 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
509 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
510 (l_api_name,
511 G_PKG_NAME,
512 'OKC_API.G_RET_STS_UNEXP_ERROR',
513 x_msg_count,
514 x_msg_data,
515 '_PVT');
516 WHEN OTHERS THEN x_return_status :=OKC_API.HANDLE_EXCEPTIONS
517 (l_api_name,
518 G_PKG_NAME,
519 'OTHERS',
520 x_msg_count,
521 x_msg_data,
522 '_PVT');
523 END update_txl_itm_insts;
524
525 PROCEDURE update_txl_itm_insts(
526 p_api_version IN NUMBER,
527 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
528 x_return_status OUT NOCOPY VARCHAR2,
529 x_msg_count OUT NOCOPY NUMBER,
530 x_msg_data OUT NOCOPY VARCHAR2,
531 p_iivv_tbl IN iivv_tbl_type,
532 x_iivv_tbl OUT NOCOPY iivv_tbl_type)
533 IS
534 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TXL_ITM_INSTS';
535 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
536 BEGIN
537 -- Call start_activity to create savepoint, check compatibility
538 -- and initialize message list
539 l_return_status := OKC_API.START_ACTIVITY (l_api_name
540 ,p_init_msg_list
541 ,'_PVT'
542 ,x_return_status);
543 -- Check if activity started successfully
544 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
545 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
546 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
547 RAISE OKC_API.G_EXCEPTION_ERROR;
548 END IF;
549 -- evaluate conditions, build outcomes for true conditions and
550 -- put them on outcome queue
551 OKL_ITI_PVT.update_row(p_api_version,
552 p_init_msg_list,
553 x_return_status,
554 x_msg_count,
555 x_msg_data,
556 p_iivv_tbl,
557 x_iivv_tbl);
558 OKC_API.END_ACTIVITY (x_msg_count
559 ,x_msg_data );
560 EXCEPTION
561 WHEN OKC_API.G_EXCEPTION_ERROR THEN
562 x_return_status := OKC_API.HANDLE_EXCEPTIONS
563 (l_api_name,
564 G_PKG_NAME,
565 'OKC_API.G_RET_STS_ERROR',
566 x_msg_count,
567 x_msg_data,
568 '_PVT');
569 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
570 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
571 (l_api_name,
572 G_PKG_NAME,
573 'OKC_API.G_RET_STS_UNEXP_ERROR',
574 x_msg_count,
575 x_msg_data,
576 '_PVT');
577 WHEN OTHERS THEN x_return_status :=OKC_API.HANDLE_EXCEPTIONS
578 (l_api_name,
579 G_PKG_NAME,
580 'OTHERS',
581 x_msg_count,
582 x_msg_data,
583 '_PVT');
584 END update_txl_itm_insts;
585
586 PROCEDURE delete_txl_itm_insts(
587 p_api_version IN NUMBER,
588 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
589 x_return_status OUT NOCOPY VARCHAR2,
590 x_msg_count OUT NOCOPY NUMBER,
591 x_msg_data OUT NOCOPY VARCHAR2,
592 p_iivv_rec IN iivv_rec_type)
593 IS
594 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_TXL_ITM_INSTS';
595 ln_chr_id OKC_K_LINES_B.DNZ_CHR_ID%TYPE;
596 -- Bug# 3477560
597 CURSOR get_chr_id(p_kle_id OKL_TXL_ASSETS_B.KLE_ID%TYPE)
598 IS
599 SELECT cle.dnz_chr_id
600 FROM okc_k_lines_b cle
601 WHERE cle.id = p_kle_id;
602 /*
603 * sjalasut aug 18, 04: added cursor to derive the dnz_chr_id and dnz_cle_id
604 * from the item instance to pass the parameters to the business event. BEGIN
605 */
606 CURSOR get_chr_cle_id(p_inst_id okl_txl_itm_insts.id%TYPE) IS
607 SELECT lines.dnz_chr_id, items.dnz_cle_id, items.serial_number
608 FROM okc_k_lines_b lines, okl_txl_itm_insts items
609 WHERE items.id = p_inst_id
610 AND lines.id = items.dnz_cle_id;
611
612 l_chr_id okc_k_headers_b.id%TYPE;
613 l_cle_id okc_k_lines_b.id%TYPE;
614 l_ser_num okl_txl_itm_insts.serial_number%TYPE;
615
616 /*
617 * sjalasut aug 18, 04: added cursor to derive the dnz_chr_id and dnz_cle_id
618 * from the item instance to pass the parameters to the business event. EMD
619 */
620
621 BEGIN
622 x_return_status := OKC_API.G_RET_STS_SUCCESS;
623 -- Call start_activity to create savepoint, check compatibility
624 -- and initialize message list
625 x_return_status := OKC_API.START_ACTIVITY (l_api_name
626 ,p_init_msg_list
627 ,'_PVT'
628 ,x_return_status);
629 -- Check if activity started successfully
630 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
631 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
632 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
633 RAISE OKC_API.G_EXCEPTION_ERROR;
634 END IF;
635 /*
636 * sjalasut: oct 19, 04 added cursor to fetch the contract in context. BEGIN
637 *
638 */
639 OPEN get_chr_cle_id(p_iivv_rec.id);
640 FETCH get_chr_cle_id INTO l_chr_id,l_cle_id,l_ser_num;
641 CLOSE get_chr_cle_id;
642 /*
643 * sjalasut: oct 19, 04 added cursor to fetch the contract in context. END
644 *
645 */
646 -- evaluate conditions, build outcomes for true conditions and
647 -- put them on outcome queue
648 OKL_ITI_PVT.delete_row(p_api_version,
649 p_init_msg_list,
650 x_return_status,
651 x_msg_count,
652 x_msg_data,
653 p_iivv_rec);
654 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
655 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
656 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
657 RAISE OKC_API.G_EXCEPTION_ERROR;
658 END IF;
659 /*
660 * sjalasut: oct 14, 04 added code to enable business event. BEGIN
661 * raise business event only if the contract is a LEASE contract
662 */
663 IF(OKL_LLA_UTIL_PVT.is_lease_contract(l_chr_id)= OKL_API.G_TRUE)THEN
664 raise_business_event(p_api_version => p_api_version,
665 p_init_msg_list => p_init_msg_list,
666 p_chr_id => l_chr_id,
667 p_asset_id => l_cle_id,
668 p_ser_num => l_ser_num,
669 p_event_name => G_WF_EVT_ASSET_SERIAL_RMVD,
670 x_return_status => x_return_status,
671 x_msg_count => x_msg_count,
672 x_msg_data => x_msg_data
673 );
674 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
675 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
676 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
677 RAISE OKL_API.G_EXCEPTION_ERROR;
678 END IF;
679 END IF;
680 /*
681 * sjalasut: oct 14, 04 added code to enable business event. END
682 */
683
684 IF p_iivv_rec.tal_type in ('CFA','CIB','CRB','CRL','CRV','CSP','ALI') THEN
685 OPEN get_chr_id(p_iivv_rec.kle_Id);
686 IF get_chr_id%NOTFOUND THEN
687 RAISE OKC_API.G_EXCEPTION_ERROR;
688 END IF;
689 FETCH get_chr_id INTO ln_chr_id;
690 CLOSE get_chr_id;
691 -- We need to change the status of the header whenever there is updating happening
692 -- after the contract status is approved
693 IF (ln_chr_id is NOT NULL) AND
694 (ln_chr_id <> OKL_API.G_MISS_NUM) THEN
695 --cascade edit status on to lines
696 okl_contract_status_pub.cascade_lease_status_edit
697 (p_api_version => p_api_version,
698 p_init_msg_list => p_init_msg_list,
699 x_return_status => x_return_status,
700 x_msg_count => x_msg_count,
701 x_msg_data => x_msg_data,
702 p_chr_id => ln_chr_id);
703 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
704 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
705 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
706 RAISE OKC_API.G_EXCEPTION_ERROR;
707 END IF;
708 END IF;
709 END IF;
710
711 OKC_API.END_ACTIVITY (x_msg_count
712 ,x_msg_data );
713 EXCEPTION
714 WHEN OKC_API.G_EXCEPTION_ERROR THEN
715 x_return_status := OKC_API.HANDLE_EXCEPTIONS
716 (l_api_name,
717 G_PKG_NAME,
718 'OKC_API.G_RET_STS_ERROR',
719 x_msg_count,
720 x_msg_data,
721 '_PVT');
722 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
723 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
724 (l_api_name,
725 G_PKG_NAME,
726 'OKC_API.G_RET_STS_UNEXP_ERROR',
727 x_msg_count,
728 x_msg_data,
729 '_PVT');
730 WHEN OTHERS THEN x_return_status :=OKC_API.HANDLE_EXCEPTIONS
731 (l_api_name,
732 G_PKG_NAME,
733 'OTHERS',
734 x_msg_count,
735 x_msg_data,
736 '_PVT');
737 END delete_txl_itm_insts;
738
739 PROCEDURE delete_txl_itm_insts(
740 p_api_version IN NUMBER,
741 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
742 x_return_status OUT NOCOPY VARCHAR2,
743 x_msg_count OUT NOCOPY NUMBER,
744 x_msg_data OUT NOCOPY VARCHAR2,
745 p_iivv_tbl IN iivv_tbl_type)
746 IS
747 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_TXL_ITM_INSTS';
748 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
749 BEGIN
750 -- Call start_activity to create savepoint, check compatibility
751 -- and initialize message list
752 l_return_status := OKC_API.START_ACTIVITY (l_api_name
753 ,p_init_msg_list
754 ,'_PVT'
755 ,x_return_status);
756 -- Check if activity started successfully
757 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
758 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
759 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
760 RAISE OKC_API.G_EXCEPTION_ERROR;
761 END IF;
762 -- evaluate conditions, build outcomes for true conditions and
763 -- put them on outcome queue
764 OKL_ITI_PVT.delete_row(p_api_version,
765 p_init_msg_list,
766 x_return_status,
767 x_msg_count,
768 x_msg_data,
769 p_iivv_tbl);
770 OKC_API.END_ACTIVITY (x_msg_count
771 ,x_msg_data );
772 EXCEPTION
773 WHEN OKC_API.G_EXCEPTION_ERROR THEN
774 x_return_status := OKC_API.HANDLE_EXCEPTIONS
775 (l_api_name,
776 G_PKG_NAME,
777 'OKC_API.G_RET_STS_ERROR',
778 x_msg_count,
779 x_msg_data,
780 '_PVT');
781 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
782 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
783 (l_api_name,
784 G_PKG_NAME,
785 'OKC_API.G_RET_STS_UNEXP_ERROR',
786 x_msg_count,
787 x_msg_data,
788 '_PVT');
789 WHEN OTHERS THEN x_return_status :=OKC_API.HANDLE_EXCEPTIONS
790 (l_api_name,
791 G_PKG_NAME,
792 'OTHERS',
793 x_msg_count,
794 x_msg_data,
795 '_PVT');
796 END delete_txl_itm_insts;
797
798 PROCEDURE validate_txl_itm_insts(
799 p_api_version IN NUMBER,
800 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
801 x_return_status OUT NOCOPY VARCHAR2,
802 x_msg_count OUT NOCOPY NUMBER,
803 x_msg_data OUT NOCOPY VARCHAR2,
804 p_iivv_rec IN iivv_rec_type)
805 IS
806 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_TXL_ITM_INSTS';
807 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
808 BEGIN
809 -- Call start_activity to create savepoint, check compatibility
810 -- and initialize message list
811 l_return_status := OKC_API.START_ACTIVITY (l_api_name
812 ,p_init_msg_list
813 ,'_PVT'
814 ,x_return_status);
815 -- Check if activity started successfully
816 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
817 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
818 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
819 RAISE OKC_API.G_EXCEPTION_ERROR;
820 END IF;
821 -- evaluate conditions, build outcomes for true conditions and
822 -- put them on outcome queue
823 OKL_ITI_PVT.validate_row(p_api_version,
824 p_init_msg_list,
825 x_return_status,
826 x_msg_count,
827 x_msg_data,
828 p_iivv_rec);
829 OKC_API.END_ACTIVITY (x_msg_count
830 ,x_msg_data );
831 EXCEPTION
832 WHEN OKC_API.G_EXCEPTION_ERROR THEN
833 x_return_status := OKC_API.HANDLE_EXCEPTIONS
834 (l_api_name,
835 G_PKG_NAME,
836 'OKC_API.G_RET_STS_ERROR',
837 x_msg_count,
838 x_msg_data,
839 '_PVT');
840 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
841 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
842 (l_api_name,
843 G_PKG_NAME,
844 'OKC_API.G_RET_STS_UNEXP_ERROR',
845 x_msg_count,
846 x_msg_data,
847 '_PVT');
848 WHEN OTHERS THEN x_return_status :=OKC_API.HANDLE_EXCEPTIONS
849 (l_api_name,
850 G_PKG_NAME,
851 'OTHERS',
852 x_msg_count,
853 x_msg_data,
854 '_PVT');
855 END validate_txl_itm_insts;
856
857 PROCEDURE validate_txl_itm_insts(
858 p_api_version IN NUMBER,
859 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
860 x_return_status OUT NOCOPY VARCHAR2,
861 x_msg_count OUT NOCOPY NUMBER,
862 x_msg_data OUT NOCOPY VARCHAR2,
863 p_iivv_tbl IN iivv_tbl_type)
864 IS
865 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_TXL_ITM_INSTS';
866 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
867 BEGIN
868 -- Call start_activity to create savepoint, check compatibility
869 -- and initialize message list
870 l_return_status := OKC_API.START_ACTIVITY (l_api_name
871 ,p_init_msg_list
872 ,'_PVT'
873 ,x_return_status);
874 -- Check if activity started successfully
875 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
876 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
877 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
878 RAISE OKC_API.G_EXCEPTION_ERROR;
879 END IF;
880 -- evaluate conditions, build outcomes for true conditions and
881 -- put them on outcome queue
882 OKL_ITI_PVT.validate_row(p_api_version,
883 p_init_msg_list,
884 x_return_status,
885 x_msg_count,
886 x_msg_data,
887 p_iivv_tbl);
888 OKC_API.END_ACTIVITY (x_msg_count
889 ,x_msg_data );
890 EXCEPTION
891 WHEN OKC_API.G_EXCEPTION_ERROR THEN
892 x_return_status := OKC_API.HANDLE_EXCEPTIONS
893 (l_api_name,
894 G_PKG_NAME,
895 'OKC_API.G_RET_STS_ERROR',
896 x_msg_count,
897 x_msg_data,
898 '_PVT');
899 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
900 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
901 (l_api_name,
902 G_PKG_NAME,
903 'OKC_API.G_RET_STS_UNEXP_ERROR',
904 x_msg_count,
905 x_msg_data,
906 '_PVT');
907 WHEN OTHERS THEN x_return_status :=OKC_API.HANDLE_EXCEPTIONS
908 (l_api_name,
909 G_PKG_NAME,
910 'OTHERS',
911 x_msg_count,
912 x_msg_data,
913 '_PVT');
914 END validate_txl_itm_insts;
915
916 -- Start of comments
917 -- Procedure Name : reset_item_srl_number
918 --
919 -- Description : This API resets non-serialized item's
920 -- serial number to NULL.
921 --
922 -- Business Rules : Blank out serial numbers from an asset
923 -- for which associated item is not serialized.
924 --
925 -- In case p_asset_line_id is NULL, the program
926 -- will update serial number(s) to NULL for all
927 -- asset line(s) having non-serialized item.
928 -- Assets with Serialized items will be ignored.
929 --
930 -- In case p_asset_line_id is NOT NULL and the item
931 -- associated to it is serialized, the program
932 -- will raise an error and will not update
933 -- serial number(s).
934 --
935 -- Parameters : p_chr_id - Contract ID (Must be not null)
936 -- - Contract must not be BOOKED
937 -- p_asset_line_id - Asset Top Line ID
938 -- - Either provide a valid line ID
939 -- or NULL for all assets
940 --
941 -- Version : 1.0, dedey
942 -- End of comments
943
944 PROCEDURE reset_item_srl_number(
945 p_api_version IN NUMBER,
946 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
947 x_return_status OUT NOCOPY VARCHAR2,
948 x_msg_count OUT NOCOPY NUMBER,
949 x_msg_data OUT NOCOPY VARCHAR2,
950 p_chr_id IN NUMBER,
951 p_asset_line_id IN NUMBER
952 ) IS
953
954 l_api_name CONSTANT VARCHAR2(30) := 'RESET_ITEM_SRL_NUMBER';
955
956 CURSOR chr_csr (p_chr_id IN NUMBER) IS
957 SELECT 'Y'
958 FROM okc_k_headers_b chr,
959 okc_statuses_b sts
960 WHERE chr.id = p_chr_id
961 AND sts.code = chr.sts_code
962 AND sts.ste_code IN ('SIGNED', 'ENTERED');
963
964 CURSOR ff1_csr (p_chr_id IN NUMBER,
965 p_line_id IN NUMBER) IS
966 SELECT line.id asset_id
967 FROM okc_k_lines_b line,
968 okc_line_styles_b style,
969 okc_statuses_b sts
970 WHERE line.lse_id = style.id
971 AND style.lty_code = 'FREE_FORM1'
972 AND line.dnz_chr_id = p_chr_id
973 AND line.id = nvl(p_line_id,line.id)
974 AND sts.code = line.sts_code
975 AND sts.ste_code not in ( 'HOLD', 'TERMINATED', 'EXPIRED','CANCELLED');
976
977 CURSOR check_item_csr (p_line_id IN NUMBER) IS
978 SELECT mtl.serial_number_control_code,
979 mtl.description item_desc
980 FROM okc_k_lines_b line,
981 okc_line_styles_b style,
982 okc_k_items kitem,
983 mtl_system_items mtl
984 WHERE line.lse_id = style.id
985 AND style.lty_code = 'ITEM'
986 AND line.id = kitem.cle_id
987 AND kitem.jtot_object1_code = 'OKX_SYSITEM'
988 AND kitem.object1_id1 = mtl.inventory_item_id
989 AND kitem.object1_id2 = TO_CHAR(mtl.organization_id)
990 AND line.cle_id = p_line_id;
991
992 CURSOR inst_csr (p_asset_id IN NUMBER) IS
993 SELECT inst.id inst_id
994 FROM okc_k_lines_b ff2,
995 okc_k_lines_b inst,
996 okc_line_styles_b ff2style,
997 okc_line_styles_b inststyle,
998 okl_txl_itm_insts txl
999 WHERE ff2.lse_id = ff2style.id
1000 AND ff2style.lty_code = 'FREE_FORM2'
1001 AND ff2.id = inst.cle_id
1002 AND inst.lse_id = inststyle.id
1003 AND inststyle.lty_code = 'INST_ITEM'
1004 AND txl.kle_id = inst.id
1005 AND ff2.cle_id = p_asset_id;
1006
1007 l_iivv_tbl okl_txl_itm_insts_pvt.iivv_tbl_type;
1008 x_iivv_tbl okl_txl_itm_insts_pvt.iivv_tbl_type;
1009
1010 l_iti_rec okl_iti_pvt.iti_rec_type;
1011 l_iivv_rec okl_txl_itm_insts_pvt.iivv_rec_type;
1012 x_iivv_rec okl_txl_itm_insts_pvt.iivv_rec_type;
1013
1014 l_itiv_rec okl_iti_pvt.itiv_rec_type;
1015
1016 x_no_data_found BOOLEAN;
1017
1018 l_chr_valid VARCHAR2(1);
1019 l_asset_line_id NUMBER;
1020 l_return_status VARCHAR2(1);
1021 l_serial_control mtl_system_items.serial_number_control_code%TYPE;
1022 l_item_desc mtl_system_items.description%TYPE;
1023
1024
1025 FUNCTION get_rec (
1026 p_id IN NUMBER,
1027 x_no_data_found OUT NOCOPY BOOLEAN
1028 ) RETURN okl_iti_pvt.itiv_rec_type IS
1029 CURSOR okl_itiv_pk_csr (p_id IN NUMBER) IS
1030 SELECT ID,
1031 OBJECT_VERSION_NUMBER,
1032 TAS_ID,
1033 TAL_ID,
1034 KLE_ID,
1035 TAL_TYPE,
1036 LINE_NUMBER,
1037 INSTANCE_NUMBER_IB,
1038 OBJECT_ID1_NEW,
1039 OBJECT_ID2_NEW,
1040 JTOT_OBJECT_CODE_NEW,
1041 OBJECT_ID1_OLD,
1042 OBJECT_ID2_OLD,
1043 JTOT_OBJECT_CODE_OLD,
1044 INVENTORY_ORG_ID,
1045 SERIAL_NUMBER,
1046 MFG_SERIAL_NUMBER_YN,
1047 INVENTORY_ITEM_ID,
1048 INV_MASTER_ORG_ID,
1049 ATTRIBUTE_CATEGORY,
1050 ATTRIBUTE1,
1051 ATTRIBUTE2,
1052 ATTRIBUTE3,
1053 ATTRIBUTE4,
1054 ATTRIBUTE5,
1055 ATTRIBUTE6,
1056 ATTRIBUTE7,
1057 ATTRIBUTE8,
1058 ATTRIBUTE9,
1059 ATTRIBUTE10,
1060 ATTRIBUTE11,
1061 ATTRIBUTE12,
1062 ATTRIBUTE13,
1063 ATTRIBUTE14,
1064 ATTRIBUTE15,
1065 CREATED_BY,
1066 CREATION_DATE,
1067 LAST_UPDATED_BY,
1068 LAST_UPDATE_DATE,
1069 LAST_UPDATE_LOGIN,
1070 DNZ_CLE_ID,
1071 instance_id,
1072 selected_for_split_flag,
1073 asd_id
1074 FROM OKL_TXL_ITM_INSTS_V iti
1075 WHERE iti.kle_id = p_id;
1076 l_okl_itiv_pk okl_itiv_pk_csr%ROWTYPE;
1077 l_itiv_rec okl_iti_pvt.itiv_rec_type;
1078 BEGIN
1079 x_no_data_found := TRUE;
1080 -- Get current database values
1081 OPEN okl_itiv_pk_csr (p_id);
1082 FETCH okl_itiv_pk_csr INTO
1083 l_itiv_rec.ID,
1084 l_itiv_rec.OBJECT_VERSION_NUMBER,
1085 l_itiv_rec.TAS_ID,
1086 l_itiv_rec.TAL_ID,
1087 l_itiv_rec.KLE_ID,
1088 l_itiv_rec.TAL_TYPE,
1089 l_itiv_rec.LINE_NUMBER,
1090 l_itiv_rec.INSTANCE_NUMBER_IB,
1091 l_itiv_rec.OBJECT_ID1_NEW,
1092 l_itiv_rec.OBJECT_ID2_NEW,
1093 l_itiv_rec.JTOT_OBJECT_CODE_NEW,
1094 l_itiv_rec.OBJECT_ID1_OLD,
1095 l_itiv_rec.OBJECT_ID2_OLD,
1096 l_itiv_rec.JTOT_OBJECT_CODE_OLD,
1097 l_itiv_rec.INVENTORY_ORG_ID,
1098 l_itiv_rec.SERIAL_NUMBER,
1099 l_itiv_rec.MFG_SERIAL_NUMBER_YN,
1100 l_itiv_rec.INVENTORY_ITEM_ID,
1101 l_itiv_rec.INV_MASTER_ORG_ID,
1102 l_itiv_rec.ATTRIBUTE_CATEGORY,
1103 l_itiv_rec.ATTRIBUTE1,
1104 l_itiv_rec.ATTRIBUTE2,
1105 l_itiv_rec.ATTRIBUTE3,
1106 l_itiv_rec.ATTRIBUTE4,
1107 l_itiv_rec.ATTRIBUTE5,
1108 l_itiv_rec.ATTRIBUTE6,
1109 l_itiv_rec.ATTRIBUTE7,
1110 l_itiv_rec.ATTRIBUTE8,
1111 l_itiv_rec.ATTRIBUTE9,
1112 l_itiv_rec.ATTRIBUTE10,
1113 l_itiv_rec.ATTRIBUTE11,
1114 l_itiv_rec.ATTRIBUTE12,
1115 l_itiv_rec.ATTRIBUTE13,
1116 l_itiv_rec.ATTRIBUTE14,
1117 l_itiv_rec.ATTRIBUTE15,
1118 l_itiv_rec.CREATED_BY,
1119 l_itiv_rec.CREATION_DATE,
1120 l_itiv_rec.LAST_UPDATED_BY,
1121 l_itiv_rec.LAST_UPDATE_DATE,
1122 l_itiv_rec.LAST_UPDATE_LOGIN,
1123 l_itiv_rec.DNZ_CLE_ID,
1124 l_itiv_rec.instance_id,
1125 l_itiv_rec.selected_for_split_flag,
1126 l_itiv_rec.asd_id;
1127 x_no_data_found := okl_itiv_pk_csr%NOTFOUND;
1128 CLOSE okl_itiv_pk_csr;
1129 RETURN(l_itiv_rec);
1130 END get_rec;
1131
1132 BEGIN
1133
1134 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1135
1136 -- Call start_activity to create savepoint, check compatibility
1137 -- and initialize message list
1138 l_return_status := OKC_API.START_ACTIVITY (l_api_name
1139 ,p_init_msg_list
1140 ,'_PVT'
1141 ,x_return_status);
1142 -- Check if activity started successfully
1143 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1144 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1145 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1146 RAISE OKC_API.G_EXCEPTION_ERROR;
1147 END IF;
1148
1149 l_chr_valid := 'N';
1150 OPEN chr_csr(p_chr_id);
1151 FETCH chr_csr INTO l_chr_valid;
1152 CLOSE chr_csr;
1153
1154 IF (l_chr_valid <> 'Y') THEN
1155 OKL_API.set_message(
1156 p_app_name => G_APP_NAME,
1157 p_msg_name => 'OKL_INVALID_VALUE',
1158 p_token1 => 'COL_NAME',
1159 p_token1_value => 'p_chr_id'
1160 );
1161
1162 --dbms_output.put_line('No data found: CHR_ID');
1163 RAISE OKC_API.G_EXCEPTION_ERROR;
1164 END IF;
1165
1166 IF (p_asset_line_id IS NOT NULL) THEN -- check for FREE_FORM1 line
1167
1168 OPEN ff1_csr (p_chr_id,
1169 p_asset_line_id);
1170 FETCH ff1_csr INTO l_asset_line_id;
1171 IF ff1_csr%NOTFOUND THEN
1172 OKL_API.set_message(
1173 p_app_name => G_APP_NAME,
1174 p_msg_name => 'OKL_INVALID_VALUE',
1175 p_token1 => 'COL_NAME',
1176 p_token1_value => 'p_asset_line_id'
1177 );
1178 --dbms_output.put_line('No data found: LINE_ID');
1179 RAISE OKC_API.G_EXCEPTION_ERROR;
1180 END IF;
1181 CLOSE ff1_csr;
1182 END IF;
1183
1184 FOR ff1_rec IN ff1_csr (p_chr_id,
1185 p_asset_line_id)
1186 LOOP
1187
1188 -- Check for item type
1189 -- report error is asset line id is passed
1190 -- otherwise skip the asset line with serialized item
1191 -- note: ff1_rec.asset_id will be same as p_asset_line_id
1192 -- when user specified p_asset_line_id
1193
1194 OPEN check_item_csr (ff1_rec.asset_id);
1195 FETCH check_item_csr INTO l_serial_control,
1196 l_item_desc;
1197 CLOSE check_item_csr;
1198
1199 IF (l_serial_control <> 1) THEN -- seriallized
1200 IF (p_asset_line_id IS NOT NULL) THEN
1201 --dbms_output.put_line('Item: '||l_item_desc);
1202 OKL_API.set_message(
1203 p_app_name => G_APP_NAME,
1204 p_msg_name => 'OKL_LLA_SRL_CNTRL',
1205 p_token1 => 'ITEM_DESC',
1206 p_token1_value => l_item_desc
1207 );
1208 RAISE OKC_API.G_EXCEPTION_ERROR;
1209 END IF;
1210 ELSE -- reset serial number
1211 FOR inst_rec IN inst_csr(ff1_rec.asset_id)
1212 LOOP
1213
1214 --dbms_output.put_line('ID before get_rec: '||inst_rec.inst_id);
1215 l_itiv_rec := get_rec(inst_rec.inst_id,
1216 x_no_data_found);
1217
1218 IF (x_no_data_found) THEN
1219
1220 --dbms_output.put_line('No Data Found');
1221 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1222 END IF;
1223
1224 l_iivv_rec := l_itiv_rec;
1225 l_iivv_rec.serial_number := NULL; -- reset serial number to NULL
1226
1227 --dbms_output.put_line('ID: '||l_iivv_rec.id);
1228 --dbms_output.put_line('SRL No: '||l_iivv_rec.serial_number);
1229
1230 okl_txl_itm_insts_pvt.update_txl_itm_insts(
1231 p_api_version => 1.0,
1232 p_init_msg_list => p_init_msg_list,
1233 x_return_status => x_return_status,
1234 x_msg_count => x_msg_count,
1235 x_msg_data => x_msg_data,
1236 p_iivv_rec => l_iivv_rec,
1237 x_iivv_rec => x_iivv_rec
1238 );
1239 --dbms_output.put_line('After Update call...'||x_return_status);
1240 --dbms_output.put_line('Error: '||x_msg_data);
1241
1242 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1243 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1244 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1245 RAISE OKC_API.G_EXCEPTION_ERROR;
1246 END IF;
1247
1248 END LOOP; --inst_csr
1249
1250 END IF; -- l_non_srl
1251
1252 END LOOP; --ff1_csr
1253
1254 OKC_API.END_ACTIVITY (x_msg_count
1255 ,x_msg_data );
1256
1257 EXCEPTION
1258
1259 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1260 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1261 (l_api_name,
1262 G_PKG_NAME,
1263 'OKC_API.G_RET_STS_ERROR',
1264 x_msg_count,
1265 x_msg_data,
1266 '_PVT');
1267 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1268 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1269 (l_api_name,
1270 G_PKG_NAME,
1271 'OKC_API.G_RET_STS_UNEXP_ERROR',
1272 x_msg_count,
1273 x_msg_data,
1274 '_PVT');
1275 WHEN OTHERS THEN x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1276 (l_api_name,
1277 G_PKG_NAME,
1278 'OTHERS',
1279 x_msg_count,
1280 x_msg_data,
1281 '_PVT');
1282 END reset_item_srl_number;
1283
1284 END OKL_TXL_ITM_INSTS_PVT;