[Home] [Help]
PACKAGE BODY: APPS.OKL_SERVICE_INTEGRATION_PVT
Source
1 PACKAGE BODY OKL_SERVICE_INTEGRATION_PVT AS
2 /* $Header: OKLRSRIB.pls 120.9 2006/08/11 10:39:26 gboomina noship $*/
3
4 G_MODULE VARCHAR2(255) := 'okl.stream.esg.okl_esg_transport_pvt';
5 G_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
6 G_IS_DEBUG_STATEMENT_ON BOOLEAN;
7
8 -- Global Variables
9 G_INIT_NUMBER NUMBER := -9999;
10 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_SERVICE_INTEGRATION_PVT';
11 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
12 G_API_TYPE CONSTANT VARCHAR2(4) := '_PVT';
13 G_LINK CONSTANT VARCHAR2(10) := 'LINKED';
14 G_DELINK CONSTANT VARCHAR2(10) := 'DELINKED';
15 G_IB_TXN_TYPE_NOT_FOUND Constant Varchar2(200) := 'OKL_LLA_IB_TXN_TYPE_NOT_FOUND';
16 G_TXN_TYPE_TOKEN Constant Varchar2(30) := 'TXN_TYPE';
17 G_LLA_SRV_PROD_INST CONSTANT VARCHAR2(35) := 'OKL_LLA_SRV_PROD_INST';
18 G_LLA_SRV_LOC_TYPE CONSTANT VARCHAR2(35) := 'OKL_LLA_SRV_LOC_TYPE';
19
20 /*
21 * sjalasut: aug 18, 04 added constants used in raising business event. BEGIN
22 */
23 G_WF_EVT_KHR_SERV_CREATED CONSTANT VARCHAR2(65) := 'oracle.apps.okl.la.lease_contract.service_created_from_contract';
24 G_WF_EVT_KHR_SERV_DELETED CONSTANT VARCHAR2(65) := 'oracle.apps.okl.la.lease_contract.remove_service_fee';
25 G_WF_ITM_CONTRACT_ID CONSTANT VARCHAR2(30) := 'CONTRACT_ID';
26 G_WF_ITM_CONTRACT_LINE_ID CONSTANT VARCHAR2(30) := 'SERVICE_LINE_ID';
27 G_WF_ITM_S_CONTRACT_ID CONSTANT VARCHAR2(30) := 'SERVICE_CONTRACT_ID';
28 G_WF_ITM_S_CONTRACT_LINE_ID CONSTANT VARCHAR2(30) := 'SERVICE_CONTRACT_LINE_ID';
29 G_WF_ITM_CONTRACT_PROCESS CONSTANT VARCHAR2(30) := 'CONTRACT_PROCESS';
30 /*
31 * sjalasut: aug 18, 04 added constants used in raising business event. END
32 */
33
34
35 SUBTYPE crjv_rec_type IS OKC_K_REL_OBJS_PUB.crjv_rec_type;
36 SUBTYPE crjv_tbl_type IS OKC_K_REL_OBJS_PUB.crjv_tbl_type;
37 --SUBTYPE clev_rec_type IS OKL_CREATE_KLE_PUB.clev_rec_type;
38 --SUBTYPE clev_tbl_type IS OKL_CREATE_KLE_PUB.clev_tbl_type;
39 --SUBTYPE klev_rec_type IS OKL_CREATE_KLE_PUB.klev_rec_type;
40 SUBTYPE cimv_rec_type IS OKL_CREATE_KLE_PUB.cimv_rec_type;
41 SUBTYPE cplv_rec_type IS OKL_OKC_MIGRATION_PVT.cplv_rec_type;
42 SUBTYPE tcnv_rec_type IS OKL_TRX_CONTRACTS_PVT.tcnv_rec_type;
43
44 G_STY_ID OKL_K_LINES.STY_ID%TYPE; -- populate from create_service_from_oks() only -- Bug 4011710
45
46 ------------------------------------------------------------------------------
47 -- PROCEDURE Report_Error
48 -- It is a generalized routine to display error on Concurrent Manager Log file
49 -- Calls:
50 -- Called by:
51 ------------------------------------------------------------------------------
52
53 PROCEDURE Report_Error(
54 x_msg_count OUT NOCOPY NUMBER,
55 x_msg_data OUT NOCOPY VARCHAR2
56 ) IS
57
58 x_msg_index_out NUMBER;
59 x_msg_out VARCHAR2(2000);
60
61 BEGIN
62
63 okl_api.end_activity(
64 X_msg_count => x_msg_count,
65 X_msg_data => x_msg_data
66 );
67
68 FOR i in 1..x_msg_count
69 LOOP
70 FND_MSG_PUB.GET(
71 p_msg_index => i,
72 p_encoded => FND_API.G_FALSE,
73 p_data => x_msg_data,
74 p_msg_index_out => x_msg_index_out
75 );
76
77 END LOOP;
78 return;
79 EXCEPTION
80 WHEN OTHERS THEN
81 NULL;
82 END Report_Error;
83
84 /*
85 * sjalasut: aug 18, 04 added procedure to call private wrapper that raises the business event. BEGIN
86 */
87 -------------------------------------------------------------------------------
88 -- PROCEDURE raise_business_event
89 -------------------------------------------------------------------------------
90 -- Start of comments
91 --
92 -- Procedure Name : raise_business_event
93 -- Description : This procedure is a wrapper that raises a business event
94 -- : when ever service line is created or deleted. this api also
95 -- : gets optional service contract parameters if the service line
96 -- : is created from a service contract.
97 -- Business Rules :
98 -- Parameters : p_chr_id,p_asset_id, p_event_name along with other api params
99 -- : p_oks_chr_id, p_oks_service_line_id
100 -- Version : 1.0
101 -- History : 30-AUG-2004 SJALASUT created
102 -- End of comments
103 PROCEDURE raise_business_event(p_api_version IN NUMBER,
104 p_init_msg_list IN VARCHAR2,
105 p_chr_id IN okc_k_headers_b.id%TYPE,
106 p_oks_chr_id IN okc_k_headers_b.id%TYPE,
107 p_okl_service_line_id IN okc_k_lines_b.id%TYPE,
108 p_oks_service_line_id IN okc_k_lines_b.id%TYPE,
109 p_event_name IN VARCHAR2,
110 x_return_status OUT NOCOPY VARCHAR2,
111 x_msg_count OUT NOCOPY NUMBER,
112 x_msg_data OUT NOCOPY VARCHAR2
113 ) IS
114 l_parameter_list wf_parameter_list_t;
115 l_contract_process VARCHAR2(20);
116 BEGIN
117 x_return_status := OKL_API.G_RET_STS_SUCCESS;
118
119 -- wrapper API to get contract process. this API determines in which status the
120 -- contract in question is.
121 l_contract_process := okl_lla_util_pvt.get_contract_process(p_chr_id => p_chr_id);
122 wf_event.AddParameterToList(G_WF_ITM_CONTRACT_ID, p_chr_id, l_parameter_list);
123 wf_event.AddParameterToList(G_WF_ITM_CONTRACT_LINE_ID, p_okl_service_line_id, l_parameter_list);
124 -- pass the service contract and service contract line only if they are present.
125 -- since service can be created even without a service contract, these parameters
126 -- are null when not created from a service contract. therefore these parameters can be
127 -- ignored in such a case.
128 IF(p_oks_chr_id IS NOT NULL AND p_oks_service_line_id IS NOT NULL)THEN
129 wf_event.AddParameterToList(G_WF_ITM_S_CONTRACT_ID, p_oks_chr_id, l_parameter_list);
130 wf_event.AddParameterToList(G_WF_ITM_S_CONTRACT_LINE_ID, p_oks_service_line_id, l_parameter_list);
131 END IF;
132 wf_event.AddParameterToList(G_WF_ITM_CONTRACT_PROCESS, l_contract_process, l_parameter_list);
133
134 OKL_WF_PVT.raise_event(p_api_version => p_api_version,
135 p_init_msg_list => p_init_msg_list,
136 x_return_status => x_return_status,
137 x_msg_count => x_msg_count,
138 x_msg_data => x_msg_data,
139 p_event_name => p_event_name,
140 p_parameters => l_parameter_list);
141
142 EXCEPTION
143 WHEN OTHERS THEN
144 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
145 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
146 END raise_business_event;
147
148 /*
149 * sjalasut: aug 18, 04 added procedure to call private wrapper that raises the business event. END
150 */
151
152
153 ------------------------------------------------------------------------------
154 -- PROCEDURE create_link_service_line
155 --
156 -- This procedure creates and links service line under a given contract in OKL. The
157 -- service line information comes from OKS service contract number provided as
158 -- an input parameter.
159 --
160 -- Calls:
161 -- Called By:
162 ------------------------------------------------------------------------------
163 PROCEDURE create_link_service_line(
164 p_api_version IN NUMBER,
165 p_init_msg_list IN VARCHAR2,
166 x_return_status OUT NOCOPY VARCHAR2,
167 x_msg_count OUT NOCOPY NUMBER,
168 x_msg_data OUT NOCOPY VARCHAR2,
169 p_okl_chr_id IN OKC_K_HEADERS_V.ID%TYPE, -- Lease Contract Header ID
170 p_oks_chr_id IN OKC_K_HEADERS_V.ID%TYPE, -- Service Contract Header ID
171 p_oks_service_line_id IN OKC_K_LINES_V.ID%TYPE, -- Service Contract Service Top Line ID
172 p_supplier_id IN NUMBER, -- OKL_VENDOR
173 x_okl_service_line_id OUT NOCOPY OKC_K_LINES_V.ID%TYPE -- Returns Lease Contract Service TOP Line ID
174 )IS
175
176 l_api_name VARCHAR2(35) := 'create_link_service_line';
177 l_proc_name VARCHAR2(35) := 'CREATE_LINK_SERVICE_LINE';
178 l_api_version CONSTANT NUMBER := 1;
179
180 l_okl_service_line_id OKC_K_HEADERS_V.ID%TYPE;
181
182 BEGIN
183 IF (G_DEBUG_ENABLED = 'Y') THEN
184 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
185 END IF;
186
187 x_return_status := OKC_API.G_RET_STS_SUCCESS;
188 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
189 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
190 END IF;
191
192 -- call START_ACTIVITY to create savepoint, check compatibility
193 -- and initialize message list
194 x_return_status := OKC_API.START_ACTIVITY(
195 p_api_name => l_api_name,
196 p_pkg_name => G_PKG_NAME,
197 p_init_msg_list => p_init_msg_list,
198 l_api_version => l_api_version,
199 p_api_version => p_api_version,
200 p_api_type => G_API_TYPE,
201 x_return_status => x_return_status);
202
203 -- check if activity started successfully
204 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
205 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
206 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
207 raise OKC_API.G_EXCEPTION_ERROR;
208 END IF;
209
210 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
211 create_service_line(
212 p_api_version => 1.0,
213 p_init_msg_list => OKL_API.G_FALSE,
214 x_return_status => x_return_status,
215 x_msg_count => x_msg_count,
216 x_msg_data => x_msg_data,
217 p_okl_chr_id => p_okl_chr_id,
218 p_oks_chr_id => p_oks_chr_id,
219 p_oks_service_line_id => p_oks_service_line_id,
220 p_supplier_id => p_supplier_id,
221 x_okl_service_line_id => l_okl_service_line_id
222 );
223
224 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
225 RAISE OKC_API.G_EXCEPTION_ERROR;
226 END IF;
227
228 link_service_line(
229 p_api_version => 1.0,
230 p_init_msg_list => OKL_API.G_FALSE,
231 x_return_status => x_return_status,
232 x_msg_count => x_msg_count,
233 x_msg_data => x_msg_data,
234 p_okl_chr_id => p_okl_chr_id,
235 p_oks_chr_id => p_oks_chr_id,
236 p_okl_service_line_id => l_okl_service_line_id,
237 p_oks_service_line_id => p_oks_service_line_id
238 );
239
240 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
241 RAISE OKC_API.G_EXCEPTION_ERROR;
242 END IF;
243
244 x_okl_service_line_id := l_okl_service_line_id;
245
246 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
247
248 /*
249 * sjalasut: aug 18, 04 added code to enable business event. BEGIN
250 * raise business event only if the context contract is a LEASE contract
251 */
252 IF(OKL_LLA_UTIL_PVT.is_lease_contract(p_okl_chr_id)= OKL_API.G_TRUE)THEN
253 raise_business_event(p_api_version => p_api_version,
254 p_init_msg_list => p_init_msg_list,
255 p_chr_id => p_okl_chr_id,
256 p_oks_chr_id => p_oks_chr_id,
257 p_okl_service_line_id => l_okl_service_line_id,
258 p_oks_service_line_id => p_oks_service_line_id,
259 p_event_name => G_WF_EVT_KHR_SERV_CREATED,
260 x_return_status => x_return_status,
261 x_msg_count => x_msg_count,
262 x_msg_data => x_msg_data
263 );
264 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
265 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
266 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
267 RAISE OKL_API.G_EXCEPTION_ERROR;
268 END IF;
269 END IF;
270 /*
271 * sjalasut: aug 18, 04 added code to enable business event. END
272 */
273
274
275 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
276 x_msg_data => x_msg_data);
277
278 EXCEPTION
279
280 when OKC_API.G_EXCEPTION_ERROR then
281 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
282 p_api_name => l_api_name,
283 p_pkg_name => G_PKG_NAME,
284 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
285 x_msg_count => x_msg_count,
286 x_msg_data => x_msg_data,
287 p_api_type => G_API_TYPE);
288
289 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
290 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
291 p_api_name => l_api_name,
292 p_pkg_name => G_PKG_NAME,
293 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
294 x_msg_count => x_msg_count,
295 x_msg_data => x_msg_data,
296 p_api_type => G_API_TYPE);
297
298 when OTHERS then
299 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
300 p_api_name => l_api_name,
301 p_pkg_name => G_PKG_NAME,
302 p_exc_name => 'OTHERS',
303 x_msg_count => x_msg_count,
304 x_msg_data => x_msg_data,
305 p_api_type => G_API_TYPE);
306
307 END create_link_service_line;
308
309
310 ------------------------------------------------------------------------------
311 -- PROCEDURE get_bill_to
312 --
313 -- This procedure returns bill to id from contract header
314 --
315 -- Calls:
316 -- Called By:
317 ------------------------------------------------------------------------------
318 PROCEDURE get_bill_to(
319 x_return_status OUT NOCOPY VARCHAR2,
320 x_msg_count OUT NOCOPY NUMBER,
321 x_msg_data OUT NOCOPY VARCHAR2,
322 p_chr_id IN OKC_K_HEADERS_B.ID%TYPE,
323 x_bill_to_id OUT NOCOPY OKC_K_HEADERS_B.BILL_TO_SITE_USE_ID%TYPE
324 ) IS
325
326 l_proc_name VARCHAR2(35) := 'GET_BILL_TO';
327
328 CURSOR bill_to_csr (p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
329 SELECT bill_to_site_use_id
330 FROM okc_k_headers_b
331 WHERE id = p_chr_id;
332
333 bill_to_failed EXCEPTION;
334
335 BEGIN
336 IF (G_DEBUG_ENABLED = 'Y') THEN
337 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
338 END IF;
339 x_return_status := OKL_API.G_RET_STS_SUCCESS;
340 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
341 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
342 END IF;
343
344 x_bill_to_id := NULL;
345 OPEN bill_to_csr (p_chr_id);
346 FETCH bill_to_csr INTO x_bill_to_id;
347 IF bill_to_csr%NOTFOUND THEN
348 RAISE bill_to_failed;
349 END IF;
350 CLOSE bill_to_csr;
351
352 IF (x_bill_to_id IS NULL) THEN
353 RAISE bill_to_failed;
354 END IF;
355
356 RETURN;
357
358 EXCEPTION
359
360 WHEN bill_to_failed THEN
361 IF bill_to_csr%ISOPEN THEN
362 CLOSE bill_to_csr;
363 END IF;
364
365 x_return_status := OKL_API.G_RET_STS_ERROR;
366
367 okl_api.set_message(
368 G_APP_NAME,
369 G_UNEXPECTED_ERROR,
370 'OKL_SQLCODE',
371 SQLCODE,
372 'OKL_SQLERRM',
373 SQLERRM || ': '||G_PKG_NAME||'.'||l_proc_name
374 );
375
376 WHEN OTHERS THEN
377 x_return_status := OKC_API.G_RET_STS_ERROR;
378 okl_api.set_message(
379 G_APP_NAME,
380 G_UNEXPECTED_ERROR,
381 'OKL_SQLCODE',
382 SQLCODE,
383 'OKL_SQLERRM',
384 SQLERRM || ': '||G_PKG_NAME||'.'||l_proc_name
385 );
386 END get_bill_to;
387
388 ------------------------------------------------------------------------------
389 -- PROCEDURE validate_integration
390 --
391 -- This procedure validates currency, bill_to and customer of lease and
392 -- service contract.
393 --
394 -- Calls:
395 -- Called By:
396 ------------------------------------------------------------------------------
397 PROCEDURE validate_integration(
398 x_return_status OUT NOCOPY VARCHAR2,
399 x_msg_count OUT NOCOPY NUMBER,
400 x_msg_data OUT NOCOPY VARCHAR2,
401 p_okl_chr_id IN OKC_K_HEADERS_V.ID%TYPE,
402 p_oks_chr_id IN OKC_K_HEADERS_V.ID%TYPE
403 ) IS
404
405
406 -- Check for 11.5.9 or 11.5.10 OKS version
407 CURSOR check_oks_ver IS
408 SELECT 1
409 FROM okc_class_operations
410 WHERE cls_code = 'SERVICE'
411 AND opn_code = 'CHECK_RULE';
412
413 l_dummy NUMBER;
414 l_oks_ver VARCHAR2(3);
415
416 CURSOR oks_info_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
417 SELECT contract_number,
418 currency_code,
419 customer_id,
420 bill_to_id
421 FROM okl_la_link_service_uv
422 WHERE chr_id = p_chr_id;
423
424 CURSOR oks_info_csr9 (p_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
425 SELECT contract_number,
426 currency_code,
427 customer_id,
428 bill_to_id
429 FROM okl_la_link_service_uv9
430 WHERE chr_id = p_chr_id;
431
432 CURSOR okl_curr_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
433 SELECT contract_number,
434 currency_code
435 FROM okc_k_headers_v
436 WHERE id = p_chr_id;
437
438 CURSOR okl_cust_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
439 SELECT role.object1_id1
440 FROM okc_k_party_roles_v role
441 WHERE role.chr_id = p_chr_id
442 AND role.rle_code = 'LESSEE';
443
444 CURSOR okl_bill_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
445 SELECT rule.object1_id1
446 FROM okc_rule_groups_v rgp,
447 okc_rules_v rule
448 WHERE rgp.chr_id = p_chr_id
449 AND rgp.id = rule.rgp_id
450 AND rgp.rgd_code = 'LABILL'
451 AND rule.rule_information_category = 'BTO';
452
453 CURSOR prev_link_csr (p_okl_chr_id OKC_K_HEADERS_B.ID%TYPE,
454 p_oks_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
455 SELECT chr_id
456 FROM okc_k_rel_objs_v
457 WHERE chr_id <> p_okl_chr_id
458 AND object1_id1 = p_oks_chr_id
459 AND rty_code = 'OKLSRV';
460
461 l_proc_name VARCHAR2(35) := 'VALIDATE_INTEGRATION';
462
463 l_okl_currency OKC_K_HEADERS_V.CURRENCY_CODE%TYPE;
464 l_oks_currency OKC_K_HEADERS_V.CURRENCY_CODE%TYPE;
465
466 l_okl_customer_id OKC_K_PARTY_ROLES_V.OBJECT1_ID1%TYPE;
467 l_oks_customer_id OKL_LA_LINK_SERVICE_UV.CUSTOMER_ID%TYPE;
468
469 l_okl_bill_to_id OKC_RULES_V.OBJECT1_ID1%TYPE;
470 l_oks_bill_to_id OKL_LA_LINK_SERVICE_UV.BILL_TO_ID%TYPE;
471
472 l_oks_contract_number OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE;
473 l_okl_contract_number OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE;
474
475 validation_failed EXCEPTION;
476 BEGIN
477 IF (G_DEBUG_ENABLED = 'Y') THEN
478 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
479 END IF;
480
481 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
482 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
483 END IF;
484
485 l_oks_ver := '?';
486 OPEN check_oks_ver;
487 FETCH check_oks_ver INTO l_dummy;
488 IF check_oks_ver%NOTFOUND THEN
489 l_oks_ver := '9';
490 ELSE
491 l_oks_ver := '10';
492 END IF;
493 CLOSE check_oks_ver;
494
495 IF (l_oks_ver = '10') THEN
496
497 FOR oks_rec IN oks_info_csr (p_oks_chr_id)
498 LOOP
499 l_oks_currency := oks_rec.currency_code;
500 l_oks_contract_number := oks_rec.contract_number;
501 l_oks_customer_id := oks_rec.customer_id;
502 l_oks_bill_to_id := oks_rec.bill_to_id;
503 END LOOP;
504
505 ELSE -- oks_ver = 9
506 FOR oks_rec IN oks_info_csr9 (p_oks_chr_id)
507 LOOP
508 l_oks_currency := oks_rec.currency_code;
509 l_oks_contract_number := oks_rec.contract_number;
510 l_oks_customer_id := oks_rec.customer_id;
511 l_oks_bill_to_id := TO_NUMBER(oks_rec.bill_to_id);
512 END LOOP;
513
514 END IF; -- l_oks_ver
515
516 FOR okl_curr_rec IN okl_curr_csr (p_okl_chr_id)
517 LOOP
518 l_okl_contract_number := okl_curr_rec.contract_number;
519 l_okl_currency := okl_curr_rec.currency_code;
520 END LOOP;
521
522 IF (l_okl_currency <> l_oks_currency) THEN
523 okl_api.set_message(
524 G_APP_NAME,
525 G_LLA_CURR_MISMATCH
526 );
527 RAISE validation_failed;
528 END IF;
529
530 FOR okl_cust_rec IN okl_cust_csr (p_okl_chr_id)
531 LOOP
532 l_okl_customer_id := okl_cust_rec.object1_id1;
533 END LOOP;
534
535 IF (l_okl_customer_id <> l_oks_customer_id) THEN
536 okl_api.set_message(
537 G_APP_NAME,
538 G_LLA_CUST_MISMATCH
539 );
540 RAISE validation_failed;
541 END IF;
542
543 get_bill_to(
544 x_return_status => x_return_status,
545 x_msg_count => x_msg_count,
546 x_msg_data => x_msg_data,
547 p_chr_id => p_okl_chr_id,
548 x_bill_to_id => l_okl_bill_to_id
549 );
550
551 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
552 RAISE validation_failed;
553 END IF;
554 /* BTO rule migration
555 FOR okl_bill_rec IN okl_bill_csr (p_okl_chr_id)
556 LOOP
557 l_okl_bill_to_id := okl_bill_rec.object1_id1;
558 END LOOP;
559 */
560 IF (l_okl_bill_to_id <> l_oks_bill_to_id) THEN
561 okl_api.set_message(
562 G_APP_NAME,
563 G_LLA_BILL_TO_MISMATCH
564 );
565 RAISE validation_failed;
566 END IF;
567
568 FOR prev_link_rec IN prev_link_csr (p_okl_chr_id,
569 p_oks_chr_id)
570 LOOP
571
572 l_okl_contract_number := NULL;
573 l_okl_currency := NULL;
574
575 OPEN okl_curr_csr (prev_link_rec.chr_id);
576 FETCH okl_curr_csr INTO l_okl_contract_number,
577 l_okl_currency;
578 CLOSE okl_curr_csr;
579
580 okl_api.set_message(
581 G_APP_NAME,
582 G_SERVICE_LINK_EXIST,
583 'OKL_CONTRACT',
584 l_okl_contract_number
585 );
586 RAISE validation_failed;
587 END LOOP;
588
589 EXCEPTION
590 WHEN validation_failed THEN
591 x_return_status := OKL_API.G_RET_STS_ERROR;
592 END validate_integration;
593
594 ------------------------------------------------------------------------------
595 -- PROCEDURE create_service_line
596 --
597 -- This procedure creates a service line under a given contract in OKL. The
598 -- service line information comes from OKS service contract number provided as
599 -- an input parameter.
600 --
601 -- Calls:
602 -- Called By:
603 ------------------------------------------------------------------------------
604 PROCEDURE create_service_line(
605 p_api_version IN NUMBER,
606 p_init_msg_list IN VARCHAR2,
607 x_return_status OUT NOCOPY VARCHAR2,
608 x_msg_count OUT NOCOPY NUMBER,
609 x_msg_data OUT NOCOPY VARCHAR2,
610 p_okl_chr_id IN OKC_K_HEADERS_V.ID%TYPE, -- Lease Contract Header ID
611 p_oks_chr_id IN OKC_K_HEADERS_V.ID%TYPE, -- Service Contract Header ID
612 p_oks_service_line_id IN OKC_K_LINES_V.ID%TYPE, -- Service Contract Service Top Line ID
613 p_supplier_id IN NUMBER,
614 x_okl_service_line_id OUT NOCOPY OKC_K_LINES_V.ID%TYPE -- Returns Lease Contract Service TOP Line ID
615 )IS
616 l_api_name VARCHAR2(35) := 'create_service_line';
617 l_proc_name VARCHAR2(35) := 'CREATE_SERVICE_LINE';
618 l_api_version CONSTANT NUMBER := 1;
619
620 CURSOR line_style_csr (p_style okc_line_styles_v.lty_code%TYPE) IS
621 SELECT id
622 FROM okc_line_styles_v
623 WHERE lty_code = p_style;
624
625 CURSOR currency_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
626 SELECT currency_code
627 FROM okc_k_headers_v
628 WHERE id = p_chr_id;
629
630 TYPE srv_okl_rec_type IS RECORD (
631 item_id mtl_system_items.inventory_item_id%type,
632 item_name mtl_system_items.description%type,
633 price_negotiated okc_k_lines_v.price_negotiated%type,
634 fin_line_id okc_k_lines_v.id%type,
635 asset_number okc_k_lines_v.name%type,
636 okl_no_of_item number,
637 srv_no_of_item number
638 );
639
640 TYPE srv_okl_tbl_type IS TABLE OF srv_okl_rec_type
641 INDEX BY BINARY_INTEGER;
642
643 l_srv_okl_tbl srv_okl_tbl_type;
644
645 /*
646 CURSOR srv_okl_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE,
647 p_srv_line_id OKC_K_LINES_V.ID%TYPE) IS
648 SELECT srv_prod.inventory_item_id item_id,
649 srv_prod.name item_name,
650 srv_line.price_negotiated,
651 okl_fin_line.id fin_line_id,
652 okl_fin_line.name asset_number,
653 okl_item.number_of_items okl_no_of_item,
654 srv_item.number_of_items srv_no_of_item
655 FROM okc_k_lines_b srv_line,
656 okc_line_styles_b srv_style,
657 okc_k_items srv_item,
658 okx_customer_products_v srv_prod,
659 okc_k_items okl_item,
660 mtl_system_items_b okl_mtl,
661 okc_k_lines_b okl_item_line,
662 okc_k_lines_v okl_fin_line,
663 okc_line_styles_b okl_style
664 WHERE srv_line.lse_id = srv_style.id
665 AND srv_line.id = srv_item.cle_id
666 AND srv_item.object1_id1 = srv_prod.id1
667 AND srv_prod.inventory_item_id = okl_mtl.inventory_item_id
668 AND srv_prod.organization_id = okl_mtl.organization_id
669 AND okl_item.object1_id1 = okl_mtl.inventory_item_id
670 AND okl_item.object1_id2 = TO_CHAR(okl_mtl.organization_id) -- Bug# 2887948
671 AND okl_item.cle_id = okl_item_line.id
672 AND okl_item_line.lse_id = okl_style.id
673 AND okl_item_line.cle_id = okl_fin_line.id
674 AND okl_style.id = okl_item_line.lse_id
675 AND okl_style.lty_code = 'ITEM'
676 AND okl_item_line.dnz_chr_id = p_chr_id
677 AND srv_style.lty_code = 'COVER_PROD'
678 AND srv_line.sts_code = 'ACTIVE'
679 AND srv_line.cle_id = p_srv_line_id;
680 */
681
682
683 CURSOR srv_amt_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE,
684 p_srv_line_id OKC_K_LINES_V.ID%TYPE) IS
685 SELECT nvl(sum(l.price_negotiated),0) tot_amount
686 FROM okc_k_lines_v l,
687 okc_line_styles_v s,
688 okc_k_items_v item,
689 okx_install_items_v prod
690 WHERE l.lse_id = s.id
691 AND l.id = item.cle_id
692 AND item.object1_id1 = prod.id1
693 AND EXISTS (
694 SELECT 'Y'
695 FROM okc_k_items_v item,
696 okx_system_items_v mtl,
697 okc_k_lines_v line,
698 okc_line_styles_v style
699 WHERE item.object1_id1 = mtl.id1
700 AND item.object1_id2 = TO_CHAR(mtl.id2) -- Bug# 2887948
701 AND item.cle_id = line.id
702 AND line.lse_id = style.id
703 AND line.dnz_chr_id = p_chr_id
704 AND mtl.id1 = prod.inventory_item_id
705 AND style.lty_code = 'ITEM'
706 )
707 AND s.lty_code = 'COVER_PROD'
708 AND l.sts_code = 'ACTIVE'
709 --Bug# :
710 --AND l.cle_id = p_srv_line_id;
711 AND l.cle_id = nvl(p_srv_line_id,l.cle_id);
712 --AND prod.organization_id = SYS_CONTEXT('OKC_CONTEXT','ORGANIZATION_ID');
713
714
715 CURSOR srv_line_csr (p_line_id OKC_K_LINES_V.ID%TYPE) IS
716 SELECT start_date,
717 end_date
718 FROM okc_k_lines_v
719 WHERE id = p_line_id;
720
721 CURSOR lease_con_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
722 SELECT start_date,
723 end_date,
724 sts_code
725 FROM okc_k_headers_v
726 WHERE id = p_chr_id;
727
728
729 CURSOR item_csr (p_cle_id OKC_K_LINES_V.ID%TYPE) IS
730 SELECT object1_id1,
731 object1_id2,
732 uom_code
733 FROM okc_k_items_v
734 WHERE cle_id = p_cle_id
735 AND jtot_object1_code = 'OKX_SERVICE';
736
737 l_lse_id OKC_K_LINES_V.LSE_ID%TYPE;
738 l_currency_code OKC_K_HEADERS_V.CURRENCY_CODE%TYPE;
739 l_srv_amount NUMBER;
740 x_cle_id OKC_K_LINES_V.ID%TYPE;
741
742 l_srv_st_date OKC_K_LINES_V.START_DATE%TYPE;
743 l_srv_end_date OKC_K_LINES_V.START_DATE%TYPE;
744 l_lease_st_date OKC_K_LINES_V.START_DATE%TYPE;
745 l_lease_end_date OKC_K_LINES_V.START_DATE%TYPE;
746
747 l_inv_item_id NUMBER;
748 l_inv_org_id NUMBER;
749
750 l_uom_code OKC_K_ITEMS_V.UOM_CODE%TYPE;
751
752 l_start_date DATE;
753 l_end_date DATE;
754
755 p_klev_rec klev_rec_type;
756 p_clev_rec clev_rec_type;
757 x_klev_rec klev_rec_type;
758 x_clev_rec clev_rec_type;
759
760 p_cimv_rec okl_okc_migration_pvt.cimv_rec_type;
761 x_cimv_rec okl_okc_migration_pvt.cimv_rec_type;
762
763 p_cplv_rec cplv_rec_type;
764 x_cplv_rec cplv_rec_type;
765
766 i NUMBER := 0;
767 l_lease_sts_code OKC_K_HEADERS_V.STS_CODE%TYPE;
768
769 --Bug# 4558486
770 l_kplv_rec OKL_K_PARTY_ROLES_PVT.kplv_rec_type;
771 x_kplv_rec OKL_K_PARTY_ROLES_PVT.kplv_rec_type;
772
773 BEGIN -- main process begins here
774 IF (G_DEBUG_ENABLED = 'Y') THEN
775 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
776 END IF;
777
778 x_return_status := OKC_API.G_RET_STS_SUCCESS;
779 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
780 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
781 END IF;
782
783 -- call START_ACTIVITY to create savepoint, check compatibility
784 -- and initialize message list
785 x_return_status := OKC_API.START_ACTIVITY(
786 p_api_name => l_api_name,
787 p_pkg_name => G_PKG_NAME,
788 p_init_msg_list => p_init_msg_list,
789 l_api_version => l_api_version,
790 p_api_version => p_api_version,
791 p_api_type => G_API_TYPE,
792 x_return_status => x_return_status);
793
794 -- check if activity started successfully
795 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
796 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
797 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
798 raise OKC_API.G_EXCEPTION_ERROR;
799 END IF;
800
801 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
802
803 validate_integration(
804 x_return_status => x_return_status,
805 x_msg_count => x_msg_count,
806 x_msg_data => x_msg_data,
807 p_okl_chr_id => p_okl_chr_id,
808 p_oks_chr_id => p_oks_chr_id
809 );
810
811 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
812 RAISE OKC_API.G_EXCEPTION_ERROR;
813 END IF;
814
815 -- Get Line Style ID for SOLD_SERVICE
816 OPEN line_style_csr ('SOLD_SERVICE');
817 FETCH line_style_csr INTO l_lse_id;
818 CLOSE line_style_csr;
819
820 -- Get contract currency
821 OPEN currency_csr (p_okl_chr_id);
822 FETCH currency_csr INTO l_currency_code;
823 CLOSE currency_csr;
824
825 -- Get COVERED_PRODUCT detail, subline of OKS service top line
826 l_srv_amount := 0;
827 i := 1;
828 /*
829 FOR srv_okl_rec IN srv_okl_csr(p_okl_chr_id,
830 p_oks_service_line_id)
831 LOOP
832 l_srv_okl_tbl(i).item_id := srv_okl_rec.item_id;
833 l_srv_okl_tbl(i).item_name := srv_okl_rec.item_name;
834 l_srv_okl_tbl(i).price_negotiated := srv_okl_rec.price_negotiated;
835 l_srv_okl_tbl(i).fin_line_id := srv_okl_rec.fin_line_id;
836 l_srv_okl_tbl(i).asset_number := srv_okl_rec.asset_number;
837 l_srv_okl_tbl(i).okl_no_of_item := srv_okl_rec.okl_no_of_item;
838 l_srv_okl_tbl(i).srv_no_of_item := srv_okl_rec.srv_no_of_item;
839
840 l_srv_amount := l_srv_amount + srv_okl_rec.price_negotiated;
841
842 IF (srv_okl_rec.okl_no_of_item <> srv_okl_rec.srv_no_of_item) THEN
843 okl_api.set_message(
844 G_APP_NAME,
845 G_OKL_ITEM_QTY_MISMATCH
846 );
847 RAISE OKC_API.G_EXCEPTION_ERROR;
848 END IF;
849
850 i := i + 1;
851 END LOOP;
852 */
853
854 FOR srv_amt_rec IN srv_amt_csr (p_okl_chr_id,
855 p_oks_service_line_id)
856 LOOP
857 l_srv_amount := srv_amt_rec.tot_amount;
858 END LOOP;
859
860 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
861 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Amount: '||l_srv_amount);
862 END IF;
863 IF (l_srv_amount = 0 ) THEN
864 okl_api.set_message(
865 G_APP_NAME,
866 G_OKL_ITEM_MISMATCH
867 );
868 RAISE OKC_API.G_EXCEPTION_ERROR;
869 END IF;
870
871 -- Get line start and end Date
872 OPEN srv_line_csr (p_oks_service_line_id);
873 FETCH srv_line_csr INTO l_srv_st_date,
874 l_srv_end_date;
875 CLOSE srv_line_csr;
876
877 -- Get lease contract start, end date
878 OPEN lease_con_csr (p_okl_chr_id);
879 FETCH lease_con_csr INTO l_lease_st_date,
880 l_lease_end_date,
881 l_lease_sts_code;
882 CLOSE lease_con_csr;
883
884 IF (l_lease_st_date > l_srv_end_date) THEN
885 okl_api.set_message(
886 G_APP_NAME,
887 G_OKL_LINK_CON_ERROR
888 );
889 RAISE OKC_API.G_EXCEPTION_ERROR;
890 END IF;
891
892 IF (l_lease_end_date < l_srv_st_date) THEN
893 okl_api.set_message(
894 G_APP_NAME,
895 G_OKL_LINK_CON_ERROR
896 );
897 RAISE OKC_API.G_EXCEPTION_ERROR;
898 END IF;
899
900 IF (l_lease_st_date < l_srv_st_date) THEN
901 l_start_date := l_srv_st_date;
902 ELSE
903 l_start_date := l_lease_st_date;
904 END IF;
905
906 IF (l_lease_end_date < l_srv_end_date) THEN
907 l_end_date := l_lease_end_date;
908 ELSE
909 l_end_date := l_srv_end_date;
910 END IF;
911
912 -- Create OKL Service TOP line
913 p_clev_rec.chr_id := p_okl_chr_id;
914 p_clev_rec.dnz_chr_id := p_okl_chr_id;
915 p_clev_rec.lse_id := l_lse_id;
916 p_clev_rec.line_number := 1;
917 p_clev_rec.sts_code := l_lease_sts_code;
918 --p_clev_rec.sts_code := 'NEW';
919 p_clev_rec.exception_yn := 'N';
920 p_clev_rec.display_sequence := 1;
921 p_clev_rec.currency_code := l_currency_code;
922 p_klev_rec.amount := l_srv_amount;
923
924 p_clev_rec.start_date := l_start_date;
925 p_clev_rec.end_date := l_end_date;
926
927 p_klev_rec.sty_id := G_STY_ID; -- Bug 4011710
928
929 okl_contract_pub.create_contract_line(
930 p_api_version => 1.0,
931 p_init_msg_list => OKL_API.G_FALSE,
932 x_return_status => x_return_status,
933 x_msg_count => x_msg_count,
934 x_msg_data => x_msg_data,
935 p_clev_rec => p_clev_rec,
936 p_klev_rec => p_klev_rec,
937 x_clev_rec => x_clev_rec,
938 x_klev_rec => x_klev_rec
939 );
940
941 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
942 x_return_status := OKL_API.G_RET_STS_ERROR;
943 RAISE OKC_API.G_EXCEPTION_ERROR;
944 END IF;
945
946 x_cle_id := x_clev_rec.id;
947
948 -- create item line to link TOP line, created above, to MTL_SYSTEM_ITEMS
949
950 OPEN item_csr (p_oks_service_line_id);
951 FETCH item_csr INTO l_inv_item_id,
952 l_inv_org_id,
953 l_uom_code;
954 CLOSE item_csr;
955
956 -- Create Item link to service top line
957 p_cimv_rec := NULL;
958 p_cimv_rec.cle_id := x_cle_id;
959 --p_cimv_rec.chr_id := p_okl_chr_id;
960 p_cimv_rec.dnz_chr_id := p_okl_chr_id;
961
962 p_cimv_rec.object1_id1 := l_inv_item_id; --???
963 p_cimv_rec.object1_id2 := l_inv_org_id; --???
964 p_cimv_rec.uom_code := 'EA'; --l_uom_code;
965 --p_cimv_rec.number_of_items := l_no_of_items;
966 p_cimv_rec.jtot_object1_code := 'OKX_SERVICE';
967 p_cimv_rec.exception_yn := 'N';
968
969 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
970 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Item: '||l_inv_item_id);
971 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Org : '||l_inv_org_id);
972 END IF;
973
974 OKL_OKC_MIGRATION_PVT.create_contract_item(
975 p_api_version => 1.0,
976 p_init_msg_list => OKL_API.G_FALSE,
977 x_return_status => x_return_status,
978 x_msg_count => x_msg_count,
979 x_msg_data => x_msg_data,
980 p_cimv_rec => p_cimv_rec,
981 x_cimv_rec => x_cimv_rec
982 );
983 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
984 x_return_status := OKL_API.G_RET_STS_ERROR;
985 RAISE OKC_API.G_EXCEPTION_ERROR;
986 END IF;
987
988 x_okl_service_line_id := x_cle_id;
989
990 p_cplv_rec.chr_id := NULL;
991 p_cplv_rec.dnz_chr_id := p_okl_chr_id;
992 p_cplv_rec.cle_id := x_cle_id;
993
994 p_cplv_rec.object1_id1 := p_supplier_id;
995 p_cplv_rec.object1_id2 := '#';
996 p_cplv_rec.jtot_object1_code := 'OKX_VENDOR';
997 p_cplv_rec.rle_code := 'OKL_VENDOR';
998
999 --Bug# 4558486: Changed call to okl_k_party_roles_pvt api
1000 -- to create records in tables
1001 -- okc_k_party_roles_b and okl_k_party_roles
1002 /*
1003 OKL_OKC_MIGRATION_PVT.create_k_party_role(
1004 p_api_version => 1.0,
1005 p_init_msg_list => OKL_API.G_FALSE,
1006 x_return_status => x_return_status,
1007 x_msg_count => x_msg_count,
1008 x_msg_data => x_msg_data,
1009 p_cplv_rec => p_cplv_rec,
1010 x_cplv_rec => x_cplv_rec
1011 );
1012 */
1013
1014 OKL_K_PARTY_ROLES_PVT.create_k_party_role(
1015 p_api_version => 1.0,
1016 p_init_msg_list => OKL_API.G_FALSE,
1017 x_return_status => x_return_status,
1018 x_msg_count => x_msg_count,
1019 x_msg_data => x_msg_data,
1020 p_cplv_rec => p_cplv_rec,
1021 x_cplv_rec => x_cplv_rec,
1022 p_kplv_rec => l_kplv_rec,
1023 x_kplv_rec => x_kplv_rec
1024 );
1025
1026 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1027 RAISE OKC_API.G_EXCEPTION_ERROR;
1028 END IF;
1029
1030 /* Don't create covered asset line automatically
1031
1032 -- Create Covered Asset line, service sub line(s)
1033
1034 -- Get Line Style ID for SOLD_SERVICE
1035 OPEN line_style_csr ('LINK_SERV_ASSET');
1036 FETCH line_style_csr INTO l_lse_id;
1037 CLOSE line_style_csr;
1038
1039 FOR i IN 1..l_srv_okl_tbl.COUNT
1040 LOOP
1041 p_clev_rec := NULL;
1042 p_clev_rec.cle_id := x_cle_id; -- service top line id
1043 p_clev_rec.chr_id := NULL;
1044 p_clev_rec.dnz_chr_id := p_okl_chr_id;
1045 p_clev_rec.lse_id := l_lse_id;
1046 p_clev_rec.line_number := 1;
1047 p_clev_rec.name := l_srv_okl_tbl(i).asset_number;
1048 p_clev_rec.sts_code := 'NEW';
1049 p_clev_rec.exception_yn := 'N';
1050 p_clev_rec.display_sequence := 1;
1051
1052 p_clev_rec.start_date := l_start_date;
1053 p_clev_rec.end_date := l_end_date;
1054
1055 p_klev_rec.capital_amount := l_srv_okl_tbl(i).price_negotiated;
1056
1057 okl_contract_pub.create_contract_line(
1058 p_api_version => 1.0,
1059 p_init_msg_list => OKL_API.G_FALSE,
1060 x_return_status => x_return_status,
1061 x_msg_count => x_msg_count,
1062 x_msg_data => x_msg_data,
1063 p_clev_rec => p_clev_rec,
1064 p_klev_rec => p_klev_rec,
1065 x_clev_rec => x_clev_rec,
1066 x_klev_rec => x_klev_rec
1067 );
1068
1069 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1070 x_return_status := OKL_API.G_RET_STS_ERROR;
1071 RAISE OKC_API.G_EXCEPTION_ERROR;
1072 END IF;
1073
1074 -- Create item link for covered asset line
1075 p_cimv_rec := NULL;
1076 p_cimv_rec.cle_id := x_clev_rec.id;
1077 p_cimv_rec.chr_id := p_okl_chr_id;
1078 p_cimv_rec.dnz_chr_id := p_okl_chr_id;
1079 p_cimv_rec.object1_id1 := l_srv_okl_tbl(i).fin_line_id;
1080 p_cimv_rec.object1_id2 := '#';
1081 p_cimv_rec.jtot_object1_code := 'OKX_COVASST';
1082 p_cimv_rec.exception_yn := 'N';
1083
1084 okl_okc_migration_pvt.create_contract_item(
1085 p_api_version => 1.0,
1086 p_init_msg_list => OKL_API.G_FALSE,
1087 x_return_status => x_return_status,
1088 x_msg_count => x_msg_count,
1089 x_msg_data => x_msg_data,
1090 p_cimv_rec => p_cimv_rec,
1091 x_cimv_rec => x_cimv_rec
1092 );
1093 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1094 x_return_status := OKL_API.G_RET_STS_ERROR;
1095 RAISE OKC_API.G_EXCEPTION_ERROR;
1096 END IF;
1097
1098 END LOOP;
1099 */
1100
1101 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1102
1103 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1104 x_msg_data => x_msg_data);
1105
1106 EXCEPTION
1107
1108 when OKC_API.G_EXCEPTION_ERROR then
1109 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1110 p_api_name => l_api_name,
1111 p_pkg_name => G_PKG_NAME,
1112 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1113 x_msg_count => x_msg_count,
1114 x_msg_data => x_msg_data,
1115 p_api_type => G_API_TYPE);
1116
1117 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
1118 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1119 p_api_name => l_api_name,
1120 p_pkg_name => G_PKG_NAME,
1121 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1122 x_msg_count => x_msg_count,
1123 x_msg_data => x_msg_data,
1124 p_api_type => G_API_TYPE);
1125
1126 when OTHERS then
1127 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1128 p_api_name => l_api_name,
1129 p_pkg_name => G_PKG_NAME,
1130 p_exc_name => 'OTHERS',
1131 x_msg_count => x_msg_count,
1132 x_msg_data => x_msg_data,
1133 p_api_type => G_API_TYPE);
1134
1135 END create_service_line;
1136
1137 ------------------------------------------------------------------------------
1138 -- PROCEDURE link_service_line
1139 --
1140 -- This procedure links
1141 -- 1. Lease and Service Contract Header
1142 -- 2. Lease Contract Service Line and Service Contract service line
1143 --
1144 -- Calls:
1145 -- Called By:
1146 ------------------------------------------------------------------------------
1147 PROCEDURE link_service_line(
1148 p_api_version IN NUMBER,
1149 p_init_msg_list IN VARCHAR2,
1150 x_return_status OUT NOCOPY VARCHAR2,
1151 x_msg_count OUT NOCOPY NUMBER,
1152 x_msg_data OUT NOCOPY VARCHAR2,
1153 p_okl_chr_id IN OKC_K_HEADERS_V.ID%TYPE, -- Lease Contract Header ID
1154 p_oks_chr_id IN OKC_K_HEADERS_V.ID%TYPE, -- Service Contract Header ID
1155 p_okl_service_line_id IN OKC_K_LINES_V.ID%TYPE, -- Lease Service Top Line ID
1156 p_oks_service_line_id IN OKC_K_LINES_V.ID%TYPE -- Service Contract - Service TOP Line ID
1157 ) IS
1158
1159 l_api_name VARCHAR2(35) := 'link_service_line';
1160 l_proc_name VARCHAR2(35) := 'LINK_SERVICE_LINE';
1161 l_api_version CONSTANT NUMBER := 1;
1162
1163 l_crjv_rec crjv_rec_type;
1164 x_crjv_rec crjv_rec_type;
1165
1166 l_service_contract_id OKC_K_HEADERS_V.ID%TYPE;
1167 l_oks_service_line_id OKC_K_LINES_V.ID%TYPE;
1168
1169 BEGIN -- main process begins here
1170 IF (G_DEBUG_ENABLED = 'Y') THEN
1171 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
1172 END IF;
1173
1174 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1175 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1176 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
1177 END IF;
1178 -- call START_ACTIVITY to create savepoint, check compatibility
1179 -- and initialize message list
1180 x_return_status := OKC_API.START_ACTIVITY(
1181 p_api_name => l_api_name,
1182 p_pkg_name => G_PKG_NAME,
1183 p_init_msg_list => p_init_msg_list,
1184 l_api_version => l_api_version,
1185 p_api_version => p_api_version,
1186 p_api_type => G_API_TYPE,
1187 x_return_status => x_return_status);
1188
1189 -- check if activity started successfully
1190 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1191 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1192 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
1193 raise OKC_API.G_EXCEPTION_ERROR;
1194 END IF;
1195
1196 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1197
1198 --
1199 -- Check for any existing link
1200 --
1201 check_service_link(
1202 p_api_version => 1.0,
1203 p_init_msg_list => OKL_API.G_FALSE,
1204 x_return_status => x_return_status,
1205 x_msg_count => x_msg_count,
1206 x_msg_data => x_msg_data,
1207 p_lease_contract_id => p_okl_chr_id,
1208 x_service_contract_id => l_service_contract_id
1209 );
1210
1211 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1212 raise OKC_API.G_EXCEPTION_ERROR;
1213 END IF;
1214
1215 IF (l_service_contract_id IS NOT NULL
1216 AND
1217 l_service_contract_id <> p_oks_chr_id
1218 ) THEN
1219 okl_api.set_message(
1220 G_APP_NAME,
1221 G_OKL_MULTI_LINK_ERROR
1222 );
1223 RAISE OKC_API.G_EXCEPTION_ERROR;
1224 END IF;
1225
1226 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1227 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Before creating link');
1228 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKL :'||p_okl_chr_id);
1229 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKS :'||p_oks_chr_id);
1230 END IF;
1231 --
1232 -- Link OKL and OKS Contract at Header level
1233 --
1234 --
1235 -- Check for an existing service line link
1236 --
1237 check_service_line_link(
1238 p_api_version => 1.0,
1239 p_init_msg_list => OKL_API.G_FALSE,
1240 x_return_status => x_return_status,
1241 x_msg_count => x_msg_count,
1242 x_msg_data => x_msg_data,
1243 p_lease_contract_id => p_okl_chr_id,
1244 p_oks_service_line_id => p_oks_service_line_id,
1245 x_service_contract_id => l_service_contract_id
1246 );
1247
1248 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
1249 raise OKC_API.G_EXCEPTION_ERROR;
1250 END IF;
1251
1252 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1253 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKS Line: '||l_oks_service_line_id);
1254 END IF;
1255
1256 IF (l_service_contract_id IS NULL) THEN -- create a new header link
1257 l_crjv_rec := NULL;
1258 l_crjv_rec.chr_id := p_okl_chr_id;
1259 l_crjv_rec.rty_code := 'OKLSRV';
1260 l_crjv_rec.object1_id1 := to_char(p_oks_chr_id);
1261 l_crjv_rec.object1_id2 := '#';
1262 l_crjv_rec.jtot_object1_code := 'OKL_SERVICE_CONNECTOR'; -- Fix Bug# 2872267
1263
1264 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1265 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Before calling 1'||':'||x_return_status);
1266 END IF;
1267 OKC_K_REL_OBJS_PUB.create_row (
1268 p_api_version => 1.0,
1269 p_init_msg_list => OKC_API.G_FALSE,
1270 x_return_status => x_return_status,
1271 x_msg_count => x_msg_count,
1272 x_msg_data => x_msg_data,
1273 p_crjv_rec => l_crjv_rec,
1274 x_crjv_rec => x_crjv_rec
1275 );
1276
1277 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1278 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After calling 1'||':'||x_return_status);
1279 END IF;
1280 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1281 RAISE OKC_API.G_EXCEPTION_ERROR;
1282 END IF;
1283 END IF; -- l_service_contract is NULL
1284
1285 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1286 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After creating header link');
1287 END IF;
1288 --
1289 -- Link OKL and OKS Contract at Line level
1290 --
1291 l_crjv_rec.chr_id := p_okl_chr_id;
1292 l_crjv_rec.cle_id := p_okl_service_line_id;
1293 l_crjv_rec.rty_code := 'OKLSRV'; -- Need to be seeded ???
1294 l_crjv_rec.object1_id1 := to_char(p_oks_service_line_id);
1295 l_crjv_rec.object1_id2 := '#';
1296 l_crjv_rec.jtot_object1_code := 'OKL_SERVICE_LINE';
1297
1298 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1299 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKL Line ID: '|| p_okl_service_line_id);
1300 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKS Line ID: '|| p_oks_service_line_id);
1301 END IF;
1302
1303 OKC_K_REL_OBJS_PUB.create_row (
1304 p_api_version => 1.0,
1305 p_init_msg_list => OKC_API.G_FALSE,
1306 x_return_status => x_return_status,
1307 x_msg_count => x_msg_count,
1308 x_msg_data => x_msg_data,
1309 p_crjv_rec => l_crjv_rec,
1310 x_crjv_rec => x_crjv_rec
1311 );
1312
1313 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1314 RAISE OKC_API.G_EXCEPTION_ERROR;
1315 END IF;
1316
1317 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1318 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After line link creation');
1319 END IF;
1320
1321 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1322
1323 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1324 x_msg_data => x_msg_data);
1325
1326 EXCEPTION
1327 when OKC_API.G_EXCEPTION_ERROR then
1328 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1329 p_api_name => l_api_name,
1330 p_pkg_name => G_PKG_NAME,
1331 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1332 x_msg_count => x_msg_count,
1333 x_msg_data => x_msg_data,
1334 p_api_type => G_API_TYPE);
1335
1336 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
1337 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1338 p_api_name => l_api_name,
1339 p_pkg_name => G_PKG_NAME,
1340 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1341 x_msg_count => x_msg_count,
1342 x_msg_data => x_msg_data,
1343 p_api_type => G_API_TYPE);
1344
1345 when OTHERS then
1346 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1347 p_api_name => l_api_name,
1348 p_pkg_name => G_PKG_NAME,
1349 p_exc_name => 'OTHERS',
1350 x_msg_count => x_msg_count,
1351 x_msg_data => x_msg_data,
1352 p_api_type => G_API_TYPE);
1353
1354 END link_service_line;
1355
1356 ------------------------------------------------------------------------------
1357 -- PROCEDURE delete_service_line
1358 --
1359 -- This procedure deletes service line. It also checks for any existing links
1360 -- with OKS Service contract, if so, it deltes the link too.
1361 --
1362 -- Calls:
1363 -- Called By:
1364 ------------------------------------------------------------------------------
1365 PROCEDURE delete_service_line(
1366 p_api_version IN NUMBER,
1367 p_init_msg_list IN VARCHAR2,
1368 x_return_status OUT NOCOPY VARCHAR2,
1369 x_msg_count OUT NOCOPY NUMBER,
1370 x_msg_data OUT NOCOPY VARCHAR2,
1371 p_clev_rec IN clev_rec_type,
1372 p_klev_rec IN klev_rec_type
1373 ) IS
1374 l_api_name VARCHAR2(35) := 'delete_service_link';
1375 l_proc_name VARCHAR2(35) := 'DELETE_SERVICE_LINK';
1376 l_api_version CONSTANT NUMBER := 1;
1377
1378 CURSOR line_link_csr (p_okl_service_line_id OKC_K_LINES_V.ID%TYPE) IS
1379 SELECT id
1380 FROM okc_k_rel_objs_v
1381 WHERE cle_id = p_okl_service_line_id
1382 AND rty_code = 'OKLSRV' -- ???
1383 AND jtot_object1_code = 'OKL_SERVICE_LINE';
1384
1385 CURSOR any_more_link_csr(p_okl_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
1386 SELECT id
1387 FROM okc_k_rel_objs_v t1
1388 WHERE chr_id = p_okl_chr_id
1389 AND cle_id IS NULL
1390 AND NOT EXISTS (SELECT 'Y'
1391 FROM okc_k_rel_objs t2
1392 WHERE t2.cle_id IS NOT NULL
1393 AND t2.chr_id = t1.chr_id
1394 AND t2.rty_code = 'OKLSRV'
1395 AND t2.jtot_object1_code = 'OKL_SERVICE_LINE'
1396 );
1397 /*
1398 * sjalsut: aug 18, 04 added cursors to fetch the service contract id and
1399 * service line id from the context contract and contract top line. BEGIN.
1400 IF (G_DEBUG_ENABLED = 'Y') THEN
1401 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
1402 END IF;
1403 */
1404
1405 CURSOR get_service_top_line IS
1406 SELECT rlobj.object1_id1
1407 FROM okc_k_rel_objs_v rlobj
1408 WHERE rlobj.chr_id = p_clev_rec.dnz_chr_id
1409 AND rlobj.cle_id = p_clev_rec.id
1410 AND rlobj.rty_code = 'OKLSRV'
1411 AND rlobj.jtot_object1_code = 'OKL_SERVICE_LINE';
1412
1413
1414 CURSOR get_service_header_id(p_oks_service_line_id okc_k_lines_b.id%TYPE) IS
1415 SELECT dnz_chr_id
1416 FROM okc_k_lines_b
1417 WHERE id = p_oks_service_line_id;
1418 /*
1419 * sjalsut: aug 18, 04 added cursors to fetch the service contract id and
1420 * service line id from the context contract and contract top line. END.
1421 */
1422
1423
1424 l_crjv_rec crjv_rec_type;
1425 l_header_rel_id okc_k_rel_objs_v.id%type;
1426 l_line_rel_id okc_k_rel_objs_v.id%type;
1427
1428 /*
1429 * sjalsut: aug 18, 04 added variables to hold service contract header id and
1430 * service contract line id. BEGIN.
1431 */
1432 l_serv_contract_hdr_id okc_k_headers_b.id%TYPE;
1433 l_serv_contract_line_id okc_k_lines_b.id%TYPE;
1434
1435 /*
1436 * sjalsut: aug 18, 04 added variables to hold service contract header id and
1437 * service contract line id. BEGIN.
1438 */
1439
1440
1441 BEGIN -- main process begins here
1442
1443 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1444 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1445 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
1446 END IF;
1447 -- call START_ACTIVITY to create savepoint, check compatibility
1448 -- and initialize message list
1449 x_return_status := OKC_API.START_ACTIVITY(
1450 p_api_name => l_api_name,
1451 p_pkg_name => G_PKG_NAME,
1452 p_init_msg_list => p_init_msg_list,
1453 l_api_version => l_api_version,
1454 p_api_version => p_api_version,
1455 p_api_type => G_API_TYPE,
1456 x_return_status => x_return_status);
1457
1458 -- check if activity started successfully
1459 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1460 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1461 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
1462 raise OKC_API.G_EXCEPTION_ERROR;
1463 END IF;
1464
1465 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1466
1467 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1468 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'DELETE 1');
1469 END IF;
1470 -- First delete the link
1471 l_line_rel_id := NULL;
1472 OPEN line_link_csr(p_clev_rec.id); -- Service top line ID
1473 FETCH line_link_csr INTO l_line_rel_id;
1474 CLOSE line_link_csr;
1475
1476 IF (l_line_rel_id IS NOT NULL) THEN -- Link exists, delete it
1477 l_crjv_rec.id := l_line_rel_id;
1478 OKC_K_REL_OBJS_PUB.delete_row (
1479 p_api_version => p_api_version,
1480 p_init_msg_list => OKL_API.G_FALSE,
1481 x_return_status => x_return_status,
1482 x_msg_count => x_msg_count,
1483 x_msg_data => x_msg_data,
1484 p_crjv_rec => l_crjv_rec
1485 );
1486
1487 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1488 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1489 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
1490 raise OKC_API.G_EXCEPTION_ERROR;
1491 END IF;
1492
1493
1494 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1495 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'DELETE 2');
1496 END IF;
1497 --
1498 -- Check for any more link left, if not, delete header link
1499 --
1500 l_header_rel_id := NULL;
1501 OPEN any_more_link_csr (p_clev_rec.dnz_chr_id);
1502 FETCH any_more_link_csr INTO l_header_rel_id;
1503 IF any_more_link_csr%NOTFOUND THEN
1504 l_header_rel_id := NULL;
1505 END IF;
1506 CLOSE any_more_link_csr;
1507
1508 IF (l_header_rel_id IS NOT NULL) THEN
1509 l_crjv_rec.id := l_header_rel_id;
1510 OKC_K_REL_OBJS_PUB.delete_row (
1511 p_api_version => p_api_version,
1512 p_init_msg_list => OKL_API.G_FALSE,
1513 x_return_status => x_return_status,
1514 x_msg_count => x_msg_count,
1515 x_msg_data => x_msg_data,
1516 p_crjv_rec => l_crjv_rec
1517 );
1518
1519 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1520 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1521 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
1522 raise OKC_API.G_EXCEPTION_ERROR;
1523 END IF;
1524
1525 END IF;
1526
1527 END IF;
1528 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1529 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'DELETE 3');
1530 END IF;
1531 --
1532 -- delete service line
1533 --
1534 okl_contract_pub.delete_contract_line(
1535 p_api_version => p_api_version,
1536 p_init_msg_list => OKL_API.G_FALSE,
1537 x_return_status => x_return_status,
1538 x_msg_count => x_msg_count,
1539 x_msg_data => x_msg_data,
1540 p_clev_rec => p_clev_rec,
1541 p_klev_rec => p_klev_rec,
1542 p_delete_cascade_yn => 'Y'
1543 );
1544
1545 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1546 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1547 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
1548 raise OKC_API.G_EXCEPTION_ERROR;
1549 END IF;
1550
1551 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1552 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'DELETE 4');
1553 END IF;
1554 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1555 /*
1556 * sjalasut: aug 18, 04 added code to enable business event. BEGIN
1557 */
1558 OPEN get_service_top_line;
1559 FETCH get_service_top_line INTO l_serv_contract_line_id;
1560 CLOSE get_service_top_line;
1561 -- fetch the service contract header details only if the service line is not
1562 -- null.
1563 IF(l_serv_contract_line_id IS NOT NULL)THEN
1564 OPEN get_service_header_id(l_serv_contract_line_id);
1565 FETCH get_service_header_id INTO l_serv_contract_hdr_id;
1566 CLOSE get_service_header_id;
1567 END IF;
1568
1569 IF(OKL_LLA_UTIL_PVT.is_lease_contract(p_clev_rec.dnz_chr_id)= OKL_API.G_TRUE)THEN
1570 raise_business_event(p_api_version => p_api_version,
1571 p_init_msg_list => p_init_msg_list,
1572 p_chr_id => p_clev_rec.dnz_chr_id,
1573 p_oks_chr_id => l_serv_contract_hdr_id,
1574 p_okl_service_line_id => p_clev_rec.id,
1575 p_oks_service_line_id => l_serv_contract_line_id,
1576 p_event_name => G_WF_EVT_KHR_SERV_DELETED,
1577 x_return_status => x_return_status,
1578 x_msg_count => x_msg_count,
1579 x_msg_data => x_msg_data
1580 );
1581 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1582 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1583 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1584 RAISE OKL_API.G_EXCEPTION_ERROR;
1585 END IF;
1586 END IF;
1587 /*
1588 * sjalasut: aug 18, 04 added code to enable business event. END
1589 */
1590
1591
1592 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1593 x_msg_data => x_msg_data);
1594
1595 EXCEPTION
1596 when OKC_API.G_EXCEPTION_ERROR then
1597 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1598 p_api_name => l_api_name,
1599 p_pkg_name => G_PKG_NAME,
1600 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1601 x_msg_count => x_msg_count,
1602 x_msg_data => x_msg_data,
1603 p_api_type => G_API_TYPE);
1604
1605 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
1606 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1607 p_api_name => l_api_name,
1608 p_pkg_name => G_PKG_NAME,
1609 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1610 x_msg_count => x_msg_count,
1611 x_msg_data => x_msg_data,
1612 p_api_type => G_API_TYPE);
1613
1614 when OTHERS then
1615 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1616 p_api_name => l_api_name,
1617 p_pkg_name => G_PKG_NAME,
1618 p_exc_name => 'OTHERS',
1619 x_msg_count => x_msg_count,
1620 x_msg_data => x_msg_data,
1621 p_api_type => G_API_TYPE);
1622
1623 END delete_service_line;
1624
1625 ------------------------------------------------------------------------------
1626 -- PROCEDURE update_service_line
1627 --
1628 -- This procedure updates existing service line link. It deletes existing
1629 -- OKL Service line and recreate the same from OKS service line. It re-establish
1630 -- the link at the end.
1631 --
1632 -- Calls:
1633 -- Called By:
1634 ------------------------------------------------------------------------------
1635 PROCEDURE update_service_line(
1636 p_api_version IN NUMBER,
1637 p_init_msg_list IN VARCHAR2,
1638 x_return_status OUT NOCOPY VARCHAR2,
1639 x_msg_count OUT NOCOPY NUMBER,
1640 x_msg_data OUT NOCOPY VARCHAR2,
1641 p_okl_chr_id IN OKC_K_HEADERS_V.ID%TYPE, -- Lease Contract Header ID
1642 p_oks_chr_id IN OKC_K_HEADERS_V.ID%TYPE, -- Service Contract Header ID
1643 p_oks_service_line_id IN OKC_K_LINES_V.ID%TYPE, -- Service Contract Service Top Line ID
1644 p_supplier_id IN NUMBER,
1645 p_clev_rec IN clev_rec_type,
1646 p_klev_rec IN klev_rec_type,
1647 x_okl_service_line_id OUT NOCOPY OKC_K_LINES_V.ID%TYPE
1648 ) IS
1649 l_api_name VARCHAR2(35) := 'update_service_link';
1650 l_proc_name VARCHAR2(35) := 'UPDATE_SERVICE_LINK';
1651 l_api_version CONSTANT NUMBER := 1;
1652
1653 BEGIN -- main process begins here
1654 IF (G_DEBUG_ENABLED = 'Y') THEN
1655 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
1656 END IF;
1657
1658 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1659 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1660 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
1661 END IF;
1662 -- call START_ACTIVITY to create savepoint, check compatibility
1663 -- and initialize message list
1664 x_return_status := OKC_API.START_ACTIVITY(
1665 p_api_name => l_api_name,
1666 p_pkg_name => G_PKG_NAME,
1667 p_init_msg_list => p_init_msg_list,
1668 l_api_version => l_api_version,
1669 p_api_version => p_api_version,
1670 p_api_type => G_API_TYPE,
1671 x_return_status => x_return_status);
1672
1673 -- check if activity started successfully
1674 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1675 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1676 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
1677 raise OKC_API.G_EXCEPTION_ERROR;
1678 END IF;
1679
1680 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1681
1682 --
1683 -- delete existing service line and link
1684 --
1685 okl_service_integration_pvt.delete_service_line(
1686 p_api_version => p_api_version,
1687 p_init_msg_list => OKL_API.G_TRUE,
1688 x_return_status => x_return_status,
1689 x_msg_count => x_msg_count,
1690 x_msg_data => x_msg_data,
1691 p_clev_rec => p_clev_rec,
1692 p_klev_rec => p_klev_rec
1693 );
1694
1695 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1696 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1697 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
1698 raise OKC_API.G_EXCEPTION_ERROR;
1699 END IF;
1700
1701 --
1702 -- create service line and link
1703 --
1704 okl_service_integration_pvt.create_link_service_line(
1705 p_api_version => p_api_version,
1706 p_init_msg_list => OKL_API.G_TRUE,
1707 x_return_status => x_return_status,
1708 x_msg_count => x_msg_count,
1709 x_msg_data => x_msg_data,
1710 p_okl_chr_id => p_okl_chr_id,
1711 p_oks_chr_id => p_oks_chr_id,
1712 p_oks_service_line_id => p_oks_service_line_id,
1713 p_supplier_id => p_supplier_id,
1714 x_okl_service_line_id => x_okl_service_line_id
1715 );
1716
1717 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1718 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1719 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
1720 raise OKC_API.G_EXCEPTION_ERROR;
1721 END IF;
1722
1723 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1724
1725 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1726 x_msg_data => x_msg_data);
1727
1728 EXCEPTION
1729 when OKC_API.G_EXCEPTION_ERROR then
1730 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1731 p_api_name => l_api_name,
1732 p_pkg_name => G_PKG_NAME,
1733 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1734 x_msg_count => x_msg_count,
1735 x_msg_data => x_msg_data,
1736 p_api_type => G_API_TYPE);
1737
1738 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
1739 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1740 p_api_name => l_api_name,
1741 p_pkg_name => G_PKG_NAME,
1742 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1743 x_msg_count => x_msg_count,
1744 x_msg_data => x_msg_data,
1745 p_api_type => G_API_TYPE);
1746
1747 when OTHERS then
1748 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1749 p_api_name => l_api_name,
1750 p_pkg_name => G_PKG_NAME,
1751 p_exc_name => 'OTHERS',
1752 x_msg_count => x_msg_count,
1753 x_msg_data => x_msg_data,
1754 p_api_type => G_API_TYPE);
1755
1756 END update_service_line;
1757
1758
1759 ------------------------------------------------------------------------------
1760 -- PROCEDURE check_service_line_link
1761 --
1762 -- This procedure checks whether a service contract is linked to the lease
1763 -- contract. It also looks for line level link.
1764 -- If a link exists, the service contract information along with OKS service
1765 -- line is returned back.
1766 -- If no link exists, it returns NULL to service contract out variables.
1767 --
1768 -- Calls:
1769 -- Called By:
1770 ------------------------------------------------------------------------------
1771 PROCEDURE check_service_line_link (
1772 p_api_version IN NUMBER,
1773 p_init_msg_list IN VARCHAR2,
1774 x_return_status OUT NOCOPY VARCHAR2,
1775 x_msg_count OUT NOCOPY NUMBER,
1776 x_msg_data OUT NOCOPY VARCHAR2,
1777 p_lease_contract_id IN OKC_K_HEADERS_V.ID%TYPE,
1778 p_oks_service_line_id IN OKC_K_LINES_V.ID%TYPE,
1779 x_service_contract_id OUT NOCOPY OKC_K_HEADERS_V.ID%TYPE
1780 ) IS
1781 l_api_name VARCHAR2(35) := 'check_service_line_link';
1782 l_proc_name VARCHAR2(35) := 'CHECK_SERVICE_LINE_LINK';
1783 l_api_version CONSTANT NUMBER := 1;
1784
1785
1786 CURSOR link_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
1787 SELECT object1_id1
1788 FROM okc_k_rel_objs_v
1789 WHERE chr_id = p_chr_id
1790 AND cle_id IS NULL
1791 AND rty_code = 'OKLSRV'; -- ???
1792
1793 CURSOR line_link_csr(p_chr_id OKC_K_HEADERS_V.ID%TYPE,
1794 p_line_id OKC_K_LINES_V.ID%TYPE) IS
1795 SELECT 'Y' answer
1796 FROM okc_k_rel_objs_v
1797 WHERE chr_id = p_chr_id
1798 AND object1_id1 = p_line_id
1799 AND jtot_object1_code = 'OKL_SERVICE_LINE';
1800
1801 l_ret_val VARCHAR2(1) := '?';
1802
1803 BEGIN -- main process begins here
1804 IF (G_DEBUG_ENABLED = 'Y') THEN
1805 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
1806 END IF;
1807
1808 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1809 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1810 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
1811 END IF;
1812 -- call START_ACTIVITY to create savepoint, check compatibility
1813 -- and initialize message list
1814 x_return_status := OKC_API.START_ACTIVITY(
1815 p_api_name => l_api_name,
1816 p_pkg_name => G_PKG_NAME,
1817 p_init_msg_list => p_init_msg_list,
1818 l_api_version => l_api_version,
1819 p_api_version => p_api_version,
1820 p_api_type => G_API_TYPE,
1821 x_return_status => x_return_status);
1822
1823 -- check if activity started successfully
1824 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1825 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1826 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
1827 raise OKC_API.G_EXCEPTION_ERROR;
1828 END IF;
1829
1830 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1831 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1832 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Before Cursor');
1833 END IF;
1834 x_service_contract_id := NULL;
1835
1836 FOR link_rec IN link_csr(p_lease_contract_id)
1837 LOOP
1838 x_service_contract_id := link_rec.object1_id1;
1839 END LOOP;
1840
1841 l_ret_val := '?';
1842 FOR line_link_rec IN line_link_csr(p_lease_contract_id,
1843 p_oks_service_line_id)
1844 LOOP
1845 l_ret_val := line_link_rec.answer;
1846 END LOOP;
1847
1848 IF (l_ret_val = 'Y') THEN
1849 okl_api.set_message(
1850 G_APP_NAME,
1851 G_LLA_SERV_LINE_LINK_ERROR
1852 );
1853 RAISE OKC_API.G_EXCEPTION_ERROR;
1854 END IF;
1855 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1856 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After Cursor');
1857 END IF;
1858
1859 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1860
1861 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1862 x_msg_data => x_msg_data);
1863
1864 EXCEPTION
1865 when OKC_API.G_EXCEPTION_ERROR then
1866 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1867 p_api_name => l_api_name,
1868 p_pkg_name => G_PKG_NAME,
1869 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1870 x_msg_count => x_msg_count,
1871 x_msg_data => x_msg_data,
1872 p_api_type => G_API_TYPE);
1873
1874 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
1875 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1876 p_api_name => l_api_name,
1877 p_pkg_name => G_PKG_NAME,
1878 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1879 x_msg_count => x_msg_count,
1880 x_msg_data => x_msg_data,
1881 p_api_type => G_API_TYPE);
1882
1883 when OTHERS then
1884 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1885 p_api_name => l_api_name,
1886 p_pkg_name => G_PKG_NAME,
1887 p_exc_name => 'OTHERS',
1888 x_msg_count => x_msg_count,
1889 x_msg_data => x_msg_data,
1890 p_api_type => G_API_TYPE);
1891
1892 END check_service_line_link;
1893
1894 ------------------------------------------------------------------------------
1895 -- PROCEDURE update_jtf_code
1896 --
1897 -- This procedure updates JTF code for header link on K_REL_OBJS
1898 -- to OKL_SERVICE from OKL_SERVICE_CONNECTOR.
1899 --
1900 -- Calls:
1901 -- Called By:
1902 ------------------------------------------------------------------------------
1903 PROCEDURE update_jtf_code (
1904 p_api_version IN NUMBER,
1905 p_init_msg_list IN VARCHAR2,
1906 x_return_status OUT NOCOPY VARCHAR2,
1907 x_msg_count OUT NOCOPY NUMBER,
1908 x_msg_data OUT NOCOPY VARCHAR2,
1909 p_okl_chr_id IN OKC_K_HEADERS_B.ID%TYPE,
1910 p_oks_chr_id IN OKC_K_HEADERS_B.ID%TYPE,
1911 p_jtf_code IN VARCHAR2
1912 ) IS
1913
1914 l_api_name VARCHAR2(35) := 'update_jtf_code';
1915 l_proc_name VARCHAR2(35) := 'UPDATE_JTF_CODE';
1916 l_api_version CONSTANT NUMBER := 1;
1917
1918 CURSOR k_rel_csr (p_okl_chr_id OKC_K_HEADERS_B.ID%TYPE,
1919 p_oks_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
1920 SELECT id
1921 FROM okc_k_rel_objs_v
1922 WHERE chr_id = p_okl_chr_id
1923 AND object1_id1 = p_oks_chr_id
1924 AND rty_code = 'OKLSRV'
1925 AND jtot_object1_code = 'OKL_SERVICE_CONNECTOR';
1926
1927 l_rel_id OKC_K_REL_OBJS_V.ID%TYPE;
1928
1929 l_crjv_rec crjv_rec_type;
1930 x_crjv_rec crjv_rec_type;
1931
1932 BEGIN
1933 IF (G_DEBUG_ENABLED = 'Y') THEN
1934 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
1935 END IF;
1936
1937 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1938 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1939 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
1940 END IF;
1941
1942 -- call START_ACTIVITY to create savepoint, check compatibility
1943 -- and initialize message list
1944 x_return_status := OKC_API.START_ACTIVITY(
1945 p_api_name => l_api_name,
1946 p_pkg_name => G_PKG_NAME,
1947 p_init_msg_list => p_init_msg_list,
1948 l_api_version => l_api_version,
1949 p_api_version => p_api_version,
1950 p_api_type => G_API_TYPE,
1951 x_return_status => x_return_status);
1952
1953 -- check if activity started successfully
1954 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1955 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1956 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
1957 raise OKC_API.G_EXCEPTION_ERROR;
1958 END IF;
1959
1960 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1961
1962 l_rel_id := NULL;
1963 OPEN k_rel_csr (p_okl_chr_id,
1964 p_oks_chr_id);
1965 FETCH k_rel_csr INTO l_rel_id;
1966 CLOSE k_rel_csr;
1967
1968 IF (l_rel_id IS NOT NULL) THEN
1969
1970 l_crjv_rec := NULL;
1971 l_crjv_rec.id := l_rel_id;
1972 --l_crjv_rec.jtot_object1_code := p_jtf_code;
1973
1974 OKC_K_REL_OBJS_PUB.DELETE_ROW(
1975 p_api_version => p_api_version,
1976 p_init_msg_list => OKL_API.G_FALSE,
1977 x_return_status => x_return_status,
1978 x_msg_count => x_msg_count,
1979 x_msg_data => x_msg_data,
1980 p_crjv_rec => l_crjv_rec
1981 );
1982
1983 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
1984 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1985 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
1986 raise OKC_API.G_EXCEPTION_ERROR;
1987 END IF;
1988
1989 l_crjv_rec := NULL;
1990 l_crjv_rec.chr_id := p_okl_chr_id;
1991 l_crjv_rec.rty_code := 'OKLSRV';
1992 l_crjv_rec.object1_id1 := p_oks_chr_id;
1993 l_crjv_rec.object1_id2 := '#';
1994 l_crjv_rec.jtot_object1_code := 'OKL_SERVICE';
1995
1996
1997 OKC_K_REL_OBJS_PUB.create_row (
1998 p_api_version => p_api_version,
1999 p_init_msg_list => OKC_API.G_FALSE,
2000 x_return_status => x_return_status,
2001 x_msg_count => x_msg_count,
2002 x_msg_data => x_msg_data,
2003 p_crjv_rec => l_crjv_rec,
2004 x_crjv_rec => x_crjv_rec
2005 );
2006
2007 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
2008 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2009 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
2010 raise OKC_API.G_EXCEPTION_ERROR;
2011 END IF;
2012
2013 END IF;
2014
2015 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2016
2017 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
2018 x_msg_data => x_msg_data);
2019
2020 EXCEPTION
2021 when OKC_API.G_EXCEPTION_ERROR then
2022 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2023 p_api_name => l_api_name,
2024 p_pkg_name => G_PKG_NAME,
2025 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
2026 x_msg_count => x_msg_count,
2027 x_msg_data => x_msg_data,
2028 p_api_type => G_API_TYPE);
2029
2030 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
2031 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2032 p_api_name => l_api_name,
2033 p_pkg_name => G_PKG_NAME,
2034 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
2035 x_msg_count => x_msg_count,
2036 x_msg_data => x_msg_data,
2037 p_api_type => G_API_TYPE);
2038
2039 when OTHERS then
2040 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2041 p_api_name => l_api_name,
2042 p_pkg_name => G_PKG_NAME,
2043 p_exc_name => 'OTHERS',
2044 x_msg_count => x_msg_count,
2045 x_msg_data => x_msg_data,
2046 p_api_type => G_API_TYPE);
2047
2048 END update_jtf_code;
2049
2050 ------------------------------------------------------------------------------
2051 -- PROCEDURE check_service_link
2052 --
2053 -- This procedure checks whether a service contract is linked to the lease
2054 -- contract.
2055 -- If a link exists, the service contract information is returned back.
2056 -- If no link exists, it returns NULL to service contract out variables.
2057 --
2058 -- Calls:
2059 -- Called By:
2060 ------------------------------------------------------------------------------
2061 PROCEDURE check_service_link (
2062 p_api_version IN NUMBER,
2063 p_init_msg_list IN VARCHAR2,
2064 x_return_status OUT NOCOPY VARCHAR2,
2065 x_msg_count OUT NOCOPY NUMBER,
2066 x_msg_data OUT NOCOPY VARCHAR2,
2067 p_lease_contract_id IN OKC_K_HEADERS_V.ID%TYPE,
2068 x_service_contract_id OUT NOCOPY OKC_K_HEADERS_V.ID%TYPE
2069 ) IS
2070 l_api_name VARCHAR2(35) := 'check_service_link';
2071 l_proc_name VARCHAR2(35) := 'CHECK_SERVICE_LINK';
2072 l_api_version CONSTANT NUMBER := 1;
2073
2074
2075 CURSOR link_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
2076 SELECT object1_id1
2077 FROM okc_k_rel_objs_v
2078 WHERE chr_id = p_chr_id
2079 AND cle_id IS NULL
2080 AND rty_code = 'OKLSRV'; -- ???
2081
2082 BEGIN -- main process begins here
2083 IF (G_DEBUG_ENABLED = 'Y') THEN
2084 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
2085 END IF;
2086
2087 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2088 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2089 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
2090 END IF;
2091 -- call START_ACTIVITY to create savepoint, check compatibility
2092 -- and initialize message list
2093 x_return_status := OKC_API.START_ACTIVITY(
2094 p_api_name => l_api_name,
2095 p_pkg_name => G_PKG_NAME,
2096 p_init_msg_list => p_init_msg_list,
2097 l_api_version => l_api_version,
2098 p_api_version => p_api_version,
2099 p_api_type => G_API_TYPE,
2100 x_return_status => x_return_status);
2101
2102 -- check if activity started successfully
2103 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
2104 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2105 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
2106 raise OKC_API.G_EXCEPTION_ERROR;
2107 END IF;
2108
2109 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2110 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2111 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Before Cursor');
2112 END IF;
2113 FOR link_rec IN link_csr(p_lease_contract_id)
2114 LOOP
2115 x_service_contract_id := link_rec.object1_id1;
2116 END LOOP;
2117
2118 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2119 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After Cursor');
2120 END IF;
2121
2122 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2123
2124 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
2125 x_msg_data => x_msg_data);
2126
2127 EXCEPTION
2128 when OKC_API.G_EXCEPTION_ERROR then
2129 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2130 p_api_name => l_api_name,
2131 p_pkg_name => G_PKG_NAME,
2132 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
2133 x_msg_count => x_msg_count,
2134 x_msg_data => x_msg_data,
2135 p_api_type => G_API_TYPE);
2136
2137 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
2138 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2139 p_api_name => l_api_name,
2140 p_pkg_name => G_PKG_NAME,
2141 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
2142 x_msg_count => x_msg_count,
2143 x_msg_data => x_msg_data,
2144 p_api_type => G_API_TYPE);
2145
2146 when OTHERS then
2147 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2148 p_api_name => l_api_name,
2149 p_pkg_name => G_PKG_NAME,
2150 p_exc_name => 'OTHERS',
2151 x_msg_count => x_msg_count,
2152 x_msg_data => x_msg_data,
2153 p_api_type => G_API_TYPE);
2154
2155 END check_service_link;
2156
2157 ------------------------------------------------------------------------------
2158 -- PROCEDURE get_service_link_line
2159 --
2160 -- This procedure returns linked lease and service contract top lines ID.
2161 -- It also returns linked OKS service contract header id
2162 -- Note: Service contract id will be NULL in case lease contract is not
2163 -- linked to a service contract.
2164 -- Calls:
2165 -- Called By:
2166 ------------------------------------------------------------------------------
2167 PROCEDURE get_service_link_line (
2168 p_api_version IN NUMBER,
2169 p_init_msg_list IN VARCHAR2,
2170 x_return_status OUT NOCOPY VARCHAR2,
2171 x_msg_count OUT NOCOPY NUMBER,
2172 x_msg_data OUT NOCOPY VARCHAR2,
2173 p_lease_contract_id IN OKC_K_HEADERS_V.ID%TYPE,
2174 x_link_line_tbl OUT NOCOPY LINK_LINE_TBL_TYPE,
2175 x_service_contract_id OUT NOCOPY OKC_K_HEADERS_V.ID%TYPE
2176 ) IS
2177 l_api_name VARCHAR2(35) := 'get_service_link_line';
2178 l_proc_name VARCHAR2(35) := 'GET_SREVICE_LINK_LINE';
2179 l_api_version CONSTANT NUMBER := 1;
2180
2181 CURSOR link_csr (p_okl_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
2182
2183 SELECT cle_id,
2184 object1_id1
2185 FROM okc_k_rel_objs_v
2186 WHERE chr_id = p_okl_chr_id
2187 AND cle_id IS NOT NULL
2188 AND rty_code = 'OKLSRV'
2189 AND jtot_object1_code = 'OKL_COV_PROD';
2190
2191 link_count NUMBER := 0;
2192 l_service_contract_id OKC_K_HEADERS_V.ID%TYPE;
2193
2194 BEGIN
2195
2196 -- call START_ACTIVITY to create savepoint, check compatibility
2197 -- and initialize message list
2198 x_return_status := OKC_API.START_ACTIVITY(
2199 p_api_name => l_api_name,
2200 p_pkg_name => G_PKG_NAME,
2201 p_init_msg_list => p_init_msg_list,
2202 l_api_version => l_api_version,
2203 p_api_version => p_api_version,
2204 p_api_type => G_API_TYPE,
2205 x_return_status => x_return_status);
2206
2207 -- check if activity started successfully
2208 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
2209 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2210 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
2211 raise OKC_API.G_EXCEPTION_ERROR;
2212 END IF;
2213
2214 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2215 check_service_link (
2216 p_api_version => 1.0,
2217 p_init_msg_list => OKL_API.G_FALSE,
2218 x_return_status => x_return_status,
2219 x_msg_count => x_msg_count,
2220 x_msg_data => x_msg_data,
2221 p_lease_contract_id => p_lease_contract_id,
2222 x_service_contract_id => l_service_contract_id
2223 );
2224
2225 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
2226 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2227 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
2228 raise OKC_API.G_EXCEPTION_ERROR;
2229 END IF;
2230
2231 x_service_contract_id := l_service_contract_id;
2232
2233 IF (x_service_contract_id IS NOT NULL) THEN
2234
2235 link_count := 0;
2236 FOR link_rec IN link_csr (p_lease_contract_id)
2237 LOOP
2238 link_count := link_count + 1;
2239 x_link_line_tbl(link_count).okl_service_line_id := link_rec.cle_id;
2240 x_link_line_tbl(link_count).oks_service_line_id := link_rec.object1_id1;
2241 END LOOP;
2242
2243 END IF;
2244
2245 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2246
2247 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
2248 x_msg_data => x_msg_data);
2249
2250 EXCEPTION
2251 when OKC_API.G_EXCEPTION_ERROR then
2252 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2253 p_api_name => l_api_name,
2254 p_pkg_name => G_PKG_NAME,
2255 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
2256 x_msg_count => x_msg_count,
2257 x_msg_data => x_msg_data,
2258 p_api_type => G_API_TYPE);
2259
2260 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
2261 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2262 p_api_name => l_api_name,
2263 p_pkg_name => G_PKG_NAME,
2264 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
2265 x_msg_count => x_msg_count,
2266 x_msg_data => x_msg_data,
2267 p_api_type => G_API_TYPE);
2268
2269 when OTHERS then
2270 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2271 p_api_name => l_api_name,
2272 p_pkg_name => G_PKG_NAME,
2273 p_exc_name => 'OTHERS',
2274 x_msg_count => x_msg_count,
2275 x_msg_data => x_msg_data,
2276 p_api_type => G_API_TYPE);
2277
2278 END get_service_link_line;
2279
2280 ------------------------------------------------------------------------------
2281 -- PROCEDURE check_prod_instance
2282 --
2283 -- This procedure checks OKS Covered product install site and
2284 -- raises error if the location is not defined as INSTALL_AT
2285 -- Bug 3569441
2286 -- Calls:
2287 -- Called By:
2288 ------------------------------------------------------------------------------
2289 PROCEDURE check_prod_instance(
2290 x_return_status OUT NOCOPY VARCHAR2,
2291 x_msg_count OUT NOCOPY NUMBER,
2292 x_msg_data OUT NOCOPY VARCHAR2,
2293 p_cov_prod IN VARCHAR2,
2294 p_instance_id IN NUMBER
2295 ) IS
2296
2297 CURSOR csi_csr (p_instance_id NUMBER) IS
2298 SELECT install_location_id location_id,
2299 install_location_type_code location_type_code
2300 FROM csi_item_instances
2301 WHERE instance_id = p_instance_id;
2302
2303 CURSOR hz_loc_csr (p_instance_id NUMBER) IS -- for location type = 'HZ_LOCATIONS'
2304 SELECT csi.install_location_id
2305 FROM csi_item_instances csi,
2306 hz_locations hl
2307 WHERE install_location_type_code = 'HZ_LOCATIONS'
2308 AND csi.owner_party_source_table = 'HZ_PARTIES'
2309 AND hl.location_id = csi.install_location_id
2310 AND NOT EXISTS (SELECT 1
2311 FROM hz_party_sites hps,
2312 hz_party_site_uses hpsu
2313 WHERE hps.location_id = hl.location_id
2314 AND hps.party_id = csi.owner_party_id
2315 AND hpsu.party_site_id = hps.party_site_id
2316 AND hpsu.site_use_type = 'INSTALL_AT'
2317 AND NVL(hpsu.status,'X') = 'A'
2318 )
2319 AND csi.instance_id = p_instance_id;
2320
2321 CURSOR hz_party_site_csr (p_instance_id NUMBER) IS -- for location type = 'HZ_PARTY_SITES'
2322 SELECT hps.location_id
2323 FROM csi_item_instances csi,
2324 hz_party_sites hps
2325 WHERE install_location_type_code = 'HZ_PARTY_SITES'
2326 AND csi.owner_party_source_table = 'HZ_PARTIES'
2327 AND csi.install_location_id = hps.party_site_id
2328 AND csi.owner_party_id = hps.party_id
2329 AND NOT EXISTS (SELECT 1
2330 FROM hz_party_site_uses hpsu
2331 WHERE hpsu.party_site_id = hps.party_site_id
2332 AND hpsu.site_use_type = 'INSTALL_AT'
2333 AND NVL(hpsu.status, 'X') = 'A'
2334 )
2335 AND csi.instance_id = p_instance_id;
2336
2337 CURSOR loc_detail_csr (p_location_id NUMBER) IS
2338 SELECT
2339 substr(arp_addr_label_pkg.format_address(null,hl.address1,hl.address2,hl.address3,
2340 hl.address4,hl.city,hl.county,hl.state,hl.province,hl.postal_code,null,hl.country,null,
2341 null,null,null,null,null,null,'n','n',80,1,1),1,80) loc_address
2342 FROM hz_locations hl
2343 WHERE hl.location_id = p_location_id;
2344
2345 l_api_name VARCHAR2(35) := 'check_prod_instance';
2346 l_proc_name VARCHAR2(35) := 'CHECK_PROD_INSTANCE';
2347
2348 l_location_id CSI_ITEM_INSTANCES.LOCATION_ID%TYPE;
2349 l_location_type_code CSI_ITEM_INSTANCES.LOCATION_TYPE_CODE%TYPE;
2350 l_hz_Loc_id HZ_LOCATIONS.location_id%TYPE;
2351
2352 instance_failed EXCEPTION;
2353
2354 BEGIN
2355 IF (G_DEBUG_ENABLED = 'Y') THEN
2356 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
2357 END IF;
2358 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2359 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2360 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
2361 END IF;
2362
2363 OPEN csi_csr (p_instance_id);
2364 FETCH csi_csr INTO l_location_id,
2365 l_location_type_code;
2366 IF csi_csr%NOTFOUND THEN
2367 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2368 END IF;
2369 CLOSE csi_csr;
2370
2371 IF (l_location_type_code = 'HZ_LOCATIONS') THEN
2372 OPEN hz_loc_csr (p_instance_id);
2373 FETCH hz_loc_csr INTO l_hz_loc_id;
2374 IF hz_loc_csr%FOUND THEN
2375 -- Raise Error
2376 x_return_status := OKL_API.G_RET_STS_ERROR;
2377
2378 FOR loc_addr_rec IN loc_detail_csr (l_hz_loc_id)
2379 LOOP
2380 okl_api.set_message(
2381 G_APP_NAME,
2382 G_LLA_SRV_PROD_INST,
2383 'COV_PROD',
2384 p_cov_prod,
2385 'LOC_ADDR',
2386 loc_addr_rec.loc_address
2387 );
2388
2389 END LOOP;
2390 RAISE instance_failed;
2391 END IF;
2392 CLOSE hz_loc_csr;
2393 ELSIF (l_location_type_code = 'HZ_PARTY_SITES') THEN
2394 OPEN hz_party_site_csr (p_instance_id);
2395 FETCH hz_party_site_csr INTO l_hz_loc_id;
2396 IF hz_party_site_csr%FOUND THEN
2397 -- Raise Error
2398 x_return_status := OKL_API.G_RET_STS_ERROR;
2399 FOR loc_addr_rec IN loc_detail_csr (l_hz_loc_id)
2400 LOOP
2401 okl_api.set_message(
2402 G_APP_NAME,
2403 G_LLA_SRV_PROD_INST,
2404 'COV_PROD',
2405 p_cov_prod,
2406 'LOC_ADDR',
2407 loc_addr_rec.loc_address
2408 );
2409
2410 END LOOP;
2411 RAISE instance_failed;
2412 END IF;
2413 CLOSE hz_party_site_csr;
2414 ELSE
2415 -- Invalid location type error
2416 x_return_status := OKL_API.G_RET_STS_ERROR;
2417 okl_api.set_message(
2418 G_APP_NAME,
2419 G_LLA_SRV_LOC_TYPE,
2420 'COV_PROD',
2421 p_cov_prod,
2422 'LOC_TYPE',
2423 l_location_type_code
2424 );
2425
2426 RAISE instance_failed;
2427 END IF;
2428
2429 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2430
2431 EXCEPTION
2432
2433 WHEN instance_failed THEN
2434
2435 x_return_status := OKL_API.G_RET_STS_ERROR;
2436
2437 IF csi_csr%ISOPEN THEN
2438 CLOSE csi_csr;
2439 END IF;
2440
2441 IF hz_loc_csr%ISOPEN THEN
2442 CLOSE hz_loc_csr;
2443 END IF;
2444
2445 IF hz_party_site_csr%ISOPEN THEN
2446 CLOSE hz_party_site_csr;
2447 END IF;
2448
2449 when OKC_API.G_EXCEPTION_ERROR then
2450 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2451 p_api_name => l_api_name,
2452 p_pkg_name => G_PKG_NAME,
2453 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
2454 x_msg_count => x_msg_count,
2455 x_msg_data => x_msg_data,
2456 p_api_type => G_API_TYPE);
2457
2458 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
2459 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2460 p_api_name => l_api_name,
2461 p_pkg_name => G_PKG_NAME,
2462 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
2463 x_msg_count => x_msg_count,
2464 x_msg_data => x_msg_data,
2465 p_api_type => G_API_TYPE);
2466
2467 when OTHERS then
2468 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2469 p_api_name => l_api_name,
2470 p_pkg_name => G_PKG_NAME,
2471 p_exc_name => 'OTHERS',
2472 x_msg_count => x_msg_count,
2473 x_msg_data => x_msg_data,
2474 p_api_type => G_API_TYPE);
2475
2476 END check_prod_instance;
2477
2478 ------------------------------------------------------------------------------
2479 -- PROCEDURE create_cov_line_link
2480 --
2481 -- This procedure creates link between OKL service sub-line and OKS covered
2482 -- product line.
2483 --
2484 -- Calls:
2485 -- Called By:
2486 ------------------------------------------------------------------------------
2487 PROCEDURE create_cov_line_link(
2488 x_return_status OUT NOCOPY VARCHAR2,
2489 x_msg_count OUT NOCOPY NUMBER,
2490 x_msg_data OUT NOCOPY VARCHAR2,
2491 p_okl_chr_id IN OKC_K_HEADERS_V.ID%TYPE,
2492 p_okl_cov_line_id IN OKC_K_LINES_V.ID%TYPE,
2493 p_oks_cov_line_id IN OKC_K_LINES_V.ID%TYPE
2494 ) IS
2495
2496 l_proc_name VARCHAR2(35) := 'CREATE_COV_LINE_LINK';
2497
2498 CURSOR cov_link_csr (p_okl_line_id OKC_K_LINES_V.ID%TYPE,
2499 p_oks_line_id OKC_K_LINES_V.ID%TYPE) IS
2500 SELECT ID
2501 FROM okc_k_rel_objs_v
2502 WHERE cle_id = p_okl_line_id
2503 AND object1_id1 = p_oks_line_id
2504 AND jtot_object1_code = 'OKL_COV_PROD'
2505 AND rty_code = 'OKLSRV';
2506
2507 l_link_id OKC_K_REL_OBJS_V.ID%TYPE;
2508 l_crjv_rec crjv_rec_type;
2509 x_crjv_rec crjv_rec_type;
2510 cov_link_error EXCEPTION;
2511
2512 BEGIN
2513 IF (G_DEBUG_ENABLED = 'Y') THEN
2514 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
2515 END IF;
2516
2517 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2518 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2519 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
2520 END IF;
2521
2522 OPEN cov_link_csr (p_okl_cov_line_id,
2523 p_oks_cov_line_id);
2524 FETCH cov_link_csr INTO l_link_id;
2525 IF cov_link_csr%NOTFOUND THEN
2526 -- Create a link
2527 l_crjv_rec := NULL;
2528 l_crjv_rec.chr_id := p_okl_chr_id;
2529 l_crjv_rec.cle_id := p_okl_cov_line_id;
2530 l_crjv_rec.rty_code := 'OKLSRV';
2531 l_crjv_rec.object1_id1 := p_oks_cov_line_id;
2532 l_crjv_rec.object1_id2 := '#';
2533 l_crjv_rec.jtot_object1_code := 'OKL_COV_PROD';
2534
2535 OKC_K_REL_OBJS_PUB.create_row (
2536 p_api_version => 1.0,
2537 p_init_msg_list => OKC_API.G_FALSE,
2538 x_return_status => x_return_status,
2539 x_msg_count => x_msg_count,
2540 x_msg_data => x_msg_data,
2541 p_crjv_rec => l_crjv_rec,
2542 x_crjv_rec => x_crjv_rec
2543 );
2544
2545 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2546 RAISE cov_link_error;
2547 END IF;
2548 ELSE
2549 -- update the link, i.e. delete and create the link
2550 l_crjv_rec.id := l_link_id;
2551 OKC_K_REL_OBJS_PUB.delete_row (
2552 p_api_version => 1.0,
2553 p_init_msg_list => OKL_API.G_FALSE,
2554 x_return_status => x_return_status,
2555 x_msg_count => x_msg_count,
2556 x_msg_data => x_msg_data,
2557 p_crjv_rec => l_crjv_rec
2558 );
2559 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2560 RAISE cov_link_error;
2561 END IF;
2562
2563 l_crjv_rec := NULL;
2564 l_crjv_rec.chr_id := p_okl_chr_id;
2565 l_crjv_rec.cle_id := p_okl_cov_line_id;
2566 l_crjv_rec.rty_code := 'OKLSRV';
2567 l_crjv_rec.object1_id1 := p_oks_cov_line_id;
2568 l_crjv_rec.object1_id2 := '#';
2569 l_crjv_rec.jtot_object1_code := 'OKL_COV_PROD';
2570
2571 OKC_K_REL_OBJS_PUB.create_row (
2572 p_api_version => 1.0,
2573 p_init_msg_list => OKC_API.G_FALSE,
2574 x_return_status => x_return_status,
2575 x_msg_count => x_msg_count,
2576 x_msg_data => x_msg_data,
2577 p_crjv_rec => l_crjv_rec,
2578 x_crjv_rec => x_crjv_rec
2579 );
2580
2581 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2582 RAISE cov_link_error;
2583 END IF;
2584 END IF;
2585
2586 EXCEPTION
2587 WHEN cov_link_error THEN
2588 RETURN; -- handle error in calling block
2589 END create_cov_line_link;
2590
2591 ------------------------------------------------------------------------------
2592 -- PROCEDURE create_cov_asset_line
2593 --
2594 -- This procedure validates covered asset and creates covered asset line
2595 -- under OKL service top line.
2596 --
2597 -- Calls:
2598 -- Called By:
2599 ------------------------------------------------------------------------------
2600 PROCEDURE create_cov_asset_line(
2601 p_api_version IN NUMBER,
2602 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2603 x_return_status OUT NOCOPY VARCHAR2,
2604 x_msg_count OUT NOCOPY NUMBER,
2605 x_msg_data OUT NOCOPY VARCHAR2,
2606 p_clev_tbl IN clev_tbl_type,
2607 p_klev_tbl IN klev_tbl_type,
2608 p_cimv_tbl IN cimv_tbl_type,
2609 p_cov_tbl IN srv_cov_tbl_type,
2610 x_clev_tbl OUT NOCOPY clev_tbl_type,
2611 x_klev_tbl OUT NOCOPY klev_tbl_type,
2612 x_cimv_tbl OUT NOCOPY cimv_tbl_type
2613 ) IS
2614 l_api_name VARCHAR2(35) := 'create_cov_asset_line';
2615 l_proc_name VARCHAR2(35) := 'CREATE_COV_ASSET_LINE';
2616 l_api_version CONSTANT NUMBER := 1;
2617
2618
2619 CURSOR cov_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE,
2620 p_srv_top_id OKC_K_LINES_V.ID%TYPE,
2621 p_asset OKL_LA_COV_ASSET_UV.NAME%TYPE) IS
2622 SELECT 'Y',
2623 item_description,
2624 srv_prod_instance_id
2625 FROM okl_la_cov_asset_uv
2626 WHERE chr_id = p_chr_id
2627 AND serv_top_line_id = p_srv_top_id
2628 AND name = p_asset;
2629
2630 l_ok VARCHAR2(1);
2631 l_instance_id OKL_LA_COV_ASSET_UV.srv_prod_instance_id%TYPE;
2632 l_item_description OKL_LA_COV_ASSET_UV.item_description%TYPE;
2633
2634 BEGIN -- main process begins here
2635 IF (G_DEBUG_ENABLED = 'Y') THEN
2636 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
2637 END IF;
2638
2639 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2640 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2641 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
2642 END IF;
2643 -- call START_ACTIVITY to create savepoint, check compatibility
2644 -- and initialize message list
2645 x_return_status := OKC_API.START_ACTIVITY(
2646 p_api_name => l_api_name,
2647 p_pkg_name => G_PKG_NAME,
2648 p_init_msg_list => p_init_msg_list,
2649 l_api_version => l_api_version,
2650 p_api_version => p_api_version,
2651 p_api_type => G_API_TYPE,
2652 x_return_status => x_return_status);
2653
2654 -- check if activity started successfully
2655 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
2656 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2657 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
2658 raise OKC_API.G_EXCEPTION_ERROR;
2659 END IF;
2660
2661 FOR i IN 1..p_clev_tbl.COUNT
2662 LOOP
2663 l_ok := '?';
2664 OPEN cov_csr (p_clev_tbl(i).dnz_chr_id,
2665 p_clev_tbl(i).cle_id,
2666 p_clev_tbl(i).name);
2667
2668 FETCH cov_csr INTO l_ok,
2669 l_item_description,
2670 l_instance_id;
2671 CLOSE cov_csr;
2672
2673 IF (l_ok <> 'Y') THEN
2674 okl_api.set_message(
2675 G_APP_NAME,
2676 G_LLA_COV_ASSET_ERROR,
2677 'ASSET_NUMBER',
2678 p_clev_tbl(i).name
2679 );
2680 RAISE OKC_API.G_EXCEPTION_ERROR;
2681 END IF;
2682
2683 --
2684 -- Check OKS Covered product Install site, for linked like only
2685 -- raise error if it is not defined as INSTALL_AT
2686 -- Bug 3569441
2687 --
2688 IF (l_instance_id IS NOT NULL) THEN -- it is a linked service line
2689 check_prod_instance(
2690 x_return_status => x_return_status,
2691 x_msg_count => x_msg_count,
2692 x_msg_data => x_msg_data,
2693 p_cov_prod => l_item_description,
2694 p_instance_id => l_instance_id
2695 );
2696
2697 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
2698 RAISE OKC_API.G_EXCEPTION_ERROR;
2699 END IF;
2700 END IF;
2701
2702 END LOOP;
2703
2704 -- Create Covered Asset Line
2705 OKL_CONTRACT_LINE_ITEM_PUB.CREATE_CONTRACT_LINE_ITEM (
2706 p_api_version => p_api_version,
2707 p_init_msg_list => OKL_API.G_FALSE,
2708 x_return_status => x_return_status,
2709 x_msg_count => x_msg_count,
2710 x_msg_data => x_msg_data,
2711 p_clev_tbl => p_clev_tbl,
2712 p_klev_tbl => p_klev_tbl,
2713 p_cimv_tbl => p_cimv_tbl,
2714 x_clev_tbl => x_clev_tbl,
2715 x_klev_tbl => x_klev_tbl,
2716 x_cimv_tbl => x_cimv_tbl
2717 );
2718
2719 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
2720 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2721 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
2722 raise OKC_API.G_EXCEPTION_ERROR;
2723 END IF;
2724
2725 --
2726 -- Create Covered Line Link
2727 --
2728 FOR i IN 1..p_cov_tbl.COUNT
2729 LOOP
2730
2731 IF (p_cov_tbl(i).oks_cov_prod_line_id IS NOT NULL) THEN
2732 create_cov_line_link(
2733 x_return_status => x_return_status,
2734 x_msg_count => x_msg_count,
2735 x_msg_data => x_msg_data,
2736 p_okl_chr_id => x_clev_tbl(i).dnz_chr_id,
2737 p_okl_cov_line_id => x_clev_tbl(i).id,
2738 p_oks_cov_line_id => p_cov_tbl(i).oks_cov_prod_line_id
2739 );
2740
2741 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
2742 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2743 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
2744 raise OKC_API.G_EXCEPTION_ERROR;
2745 END IF;
2746 END IF;
2747 END LOOP;
2748
2749
2750 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
2751 x_msg_data => x_msg_data);
2752
2753 EXCEPTION
2754 when OKC_API.G_EXCEPTION_ERROR then
2755 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2756 p_api_name => l_api_name,
2757 p_pkg_name => G_PKG_NAME,
2758 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
2759 x_msg_count => x_msg_count,
2760 x_msg_data => x_msg_data,
2761 p_api_type => G_API_TYPE);
2762
2763 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
2764 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2765 p_api_name => l_api_name,
2766 p_pkg_name => G_PKG_NAME,
2767 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
2768 x_msg_count => x_msg_count,
2769 x_msg_data => x_msg_data,
2770 p_api_type => G_API_TYPE);
2771
2772 when OTHERS then
2773 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2774 p_api_name => l_api_name,
2775 p_pkg_name => G_PKG_NAME,
2776 p_exc_name => 'OTHERS',
2777 x_msg_count => x_msg_count,
2778 x_msg_data => x_msg_data,
2779 p_api_type => G_API_TYPE);
2780
2781 END create_cov_asset_line;
2782
2783 ------------------------------------------------------------------------------
2784 -- PROCEDURE update_cov_asset_line
2785 --
2786 -- This procedure validates covered asset and updates covered asset line
2787 -- under OKL service top line.
2788 --
2789 -- Calls:
2790 -- Called By:
2791 ------------------------------------------------------------------------------
2792 PROCEDURE update_cov_asset_line(
2793 p_api_version IN NUMBER,
2794 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2795 x_return_status OUT NOCOPY VARCHAR2,
2796 x_msg_count OUT NOCOPY NUMBER,
2797 x_msg_data OUT NOCOPY VARCHAR2,
2798 p_clev_tbl IN clev_tbl_type,
2799 p_klev_tbl IN klev_tbl_type,
2800 p_cimv_tbl IN cimv_tbl_type,
2801 p_cov_tbl IN srv_cov_tbl_type,
2802 x_clev_tbl OUT NOCOPY clev_tbl_type,
2803 x_klev_tbl OUT NOCOPY klev_tbl_type,
2804 x_cimv_tbl OUT NOCOPY cimv_tbl_type
2805 ) IS
2806 l_api_name VARCHAR2(35) := 'update_cov_asset_line';
2807 l_proc_name VARCHAR2(35) := 'UPDATE_COV_ASSET_LINE';
2808 l_api_version CONSTANT NUMBER := 1;
2809
2810
2811 CURSOR cov_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE,
2812 p_srv_top_id OKC_K_LINES_V.ID%TYPE,
2813 p_asset OKL_LA_COV_ASSET_UV.NAME%TYPE) IS
2814 SELECT 'Y'
2815 FROM okl_la_cov_asset_uv
2816 WHERE chr_id = p_chr_id
2817 AND serv_top_line_id = p_srv_top_id
2818 AND name = p_asset;
2819
2820 l_ok VARCHAR2(1);
2821
2822 BEGIN -- main process begins here
2823 IF (G_DEBUG_ENABLED = 'Y') THEN
2824 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
2825 END IF;
2826
2827 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2828 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2829 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
2830 END IF;
2831 -- call START_ACTIVITY to create savepoint, check compatibility
2832 -- and initialize message list
2833 x_return_status := OKC_API.START_ACTIVITY(
2834 p_api_name => l_api_name,
2835 p_pkg_name => G_PKG_NAME,
2836 p_init_msg_list => p_init_msg_list,
2837 l_api_version => l_api_version,
2838 p_api_version => p_api_version,
2839 p_api_type => G_API_TYPE,
2840 x_return_status => x_return_status);
2841
2842 -- check if activity started successfully
2843 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
2844 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2845 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
2846 raise OKC_API.G_EXCEPTION_ERROR;
2847 END IF;
2848
2849 FOR i IN 1..p_clev_tbl.COUNT
2850 LOOP
2851 l_ok := '?';
2852 OPEN cov_csr (p_clev_tbl(i).dnz_chr_id,
2853 p_clev_tbl(i).cle_id,
2854 p_clev_tbl(i).name);
2855
2856 FETCH cov_csr INTO l_ok;
2857 CLOSE cov_csr;
2858
2859 IF (l_ok <> 'Y') THEN
2860 okl_api.set_message(
2861 G_APP_NAME,
2862 G_LLA_COV_ASSET_ERROR,
2863 'ASSET_NUMBER',
2864 p_clev_tbl(i).name
2865 );
2866 RAISE OKC_API.G_EXCEPTION_ERROR;
2867 END IF;
2868
2869 END LOOP;
2870
2871 -- Create Covered Asset Line
2872 OKL_CONTRACT_LINE_ITEM_PUB.UPDATE_CONTRACT_LINE_ITEM (
2873 p_api_version => p_api_version,
2874 p_init_msg_list => OKL_API.G_FALSE,
2875 x_return_status => x_return_status,
2876 x_msg_count => x_msg_count,
2877 x_msg_data => x_msg_data,
2878 p_clev_tbl => p_clev_tbl,
2879 p_klev_tbl => p_klev_tbl,
2880 p_cimv_tbl => p_cimv_tbl,
2881 x_clev_tbl => x_clev_tbl,
2882 x_klev_tbl => x_klev_tbl,
2883 x_cimv_tbl => x_cimv_tbl
2884 );
2885
2886 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
2887 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2888 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
2889 raise OKC_API.G_EXCEPTION_ERROR;
2890 END IF;
2891
2892 --
2893 -- Update Covered Line Link
2894 --
2895 FOR i IN 1..p_cov_tbl.COUNT
2896 LOOP
2897 IF (p_cov_tbl(i).oks_cov_prod_line_id IS NOT NULL) THEN
2898 create_cov_line_link(
2899 x_return_status => x_return_status,
2900 x_msg_count => x_msg_count,
2901 x_msg_data => x_msg_data,
2902 p_okl_chr_id => x_clev_tbl(i).dnz_chr_id,
2903 p_okl_cov_line_id => x_clev_tbl(i).id,
2904 p_oks_cov_line_id => p_cov_tbl(i).oks_cov_prod_line_id
2905 );
2906
2907 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
2908 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2909 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
2910 raise OKC_API.G_EXCEPTION_ERROR;
2911 END IF;
2912 END IF;
2913 END LOOP;
2914
2915 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
2916 x_msg_data => x_msg_data);
2917
2918 EXCEPTION
2919 when OKC_API.G_EXCEPTION_ERROR then
2920 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2921 p_api_name => l_api_name,
2922 p_pkg_name => G_PKG_NAME,
2923 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
2924 x_msg_count => x_msg_count,
2925 x_msg_data => x_msg_data,
2926 p_api_type => G_API_TYPE);
2927
2928 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
2929 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2930 p_api_name => l_api_name,
2931 p_pkg_name => G_PKG_NAME,
2932 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
2933 x_msg_count => x_msg_count,
2934 x_msg_data => x_msg_data,
2935 p_api_type => G_API_TYPE);
2936
2937 when OTHERS then
2938 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2939 p_api_name => l_api_name,
2940 p_pkg_name => G_PKG_NAME,
2941 p_exc_name => 'OTHERS',
2942 x_msg_count => x_msg_count,
2943 x_msg_data => x_msg_data,
2944 p_api_type => G_API_TYPE);
2945
2946 END update_cov_asset_line;
2947
2948 ------------------------------------------------------------------------------
2949 -- PROCEDURE update_oks_ar_intf
2950 --
2951 -- This procedure updates OKS AR Interface flag.
2952 --
2953 -- Calls:
2954 -- Called By:
2955 ------------------------------------------------------------------------------
2956 PROCEDURE update_oks_ar_intf(
2957 x_return_status OUT NOCOPY VARCHAR2,
2958 x_msg_count OUT NOCOPY NUMBER,
2959 x_msg_data OUT NOCOPY VARCHAR2,
2960 p_oks_chr_id IN OKC_K_HEADERS_B.ID%TYPE,
2961 p_ar_intf_val IN VARCHAR2
2962 )IS
2963
2964 l_api_name VARCHAR2(35) := 'update_oks_arintf';
2965 l_proc_name VARCHAR2(35) := 'UPDATE_OKS_ARINTF';
2966 l_api_version CONSTANT NUMBER := 1;
2967
2968 CURSOR arintf_csr(p_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
2969 SELECT rule.id
2970 FROM okc_rules_v rule,
2971 okc_rule_groups_v rg
2972 WHERE rule.rgp_id = rg.id
2973 AND rule.rule_information_category = 'SBG'
2974 AND rule.jtot_object1_code = 'OKS_TRXTYPE'
2975 AND rg.chr_id = p_chr_id;
2976
2977 p_rulv_rec OKC_RULE_PUB.RULV_REC_TYPE;
2978 x_rulv_rec OKC_RULE_PUB.RULV_REC_TYPE;
2979
2980 l_arintf_rule_id OKC_RULES_V.ID%TYPE;
2981
2982 BEGIN
2983 IF (G_DEBUG_ENABLED = 'Y') THEN
2984 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
2985 END IF;
2986
2987 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2988 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
2989 END IF;
2990 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2991
2992 OPEN arintf_csr(p_oks_chr_id);
2993 FETCH arintf_csr INTO l_arintf_rule_id;
2994 CLOSE arintf_csr;
2995
2996 --dbms_output.put_line('Rule id: '||l_arintf_rule_id);
2997
2998 IF (l_arintf_rule_id IS NOT NULL) THEN
2999
3000 p_rulv_rec.id := l_arintf_rule_id;
3001 p_rulv_rec.rule_information11 := p_ar_intf_val;
3002
3003 okc_rule_pub.update_rule(
3004 p_api_version => 1.0,
3005 p_init_msg_list => OKL_API.G_TRUE,
3006 x_return_status => x_return_status,
3007 x_msg_count => x_msg_count,
3008 x_msg_data => x_msg_data,
3009 p_rulv_rec => p_rulv_rec,
3010 x_rulv_rec => x_rulv_rec
3011 );
3012
3013 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
3014 x_return_status := OKL_API.G_RET_STS_ERROR;
3015 END IF;
3016
3017 END IF;
3018
3019 RETURN;
3020
3021 EXCEPTION
3022
3023 WHEN OTHERS THEN
3024 x_return_status := OKL_API.G_RET_STS_ERROR;
3025
3026 END update_oks_ar_intf;
3027
3028
3029 ------------------------------------------------------------------------------
3030 -- PROCEDURE initiate_service_booking
3031 --
3032 -- This procedure is being called from activate API. It checks for service
3033 -- link and associates IB instances from OKS service line with
3034 -- corresponding IB line instances at lease contract.
3035 --
3036 -- Calls:
3037 -- Called By:
3038 ------------------------------------------------------------------------------
3039 PROCEDURE initiate_service_booking(
3040 p_api_version IN NUMBER,
3041 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
3042 x_return_status OUT NOCOPY VARCHAR2,
3043 x_msg_count OUT NOCOPY NUMBER,
3044 x_msg_data OUT NOCOPY VARCHAR2,
3045 p_okl_chr_id IN OKC_K_HEADERS_B.ID%TYPE
3046 ) IS
3047 l_api_name VARCHAR2(35) := 'initiate_service_booking';
3048 l_proc_name VARCHAR2(35) := 'INITIATE_SERVICE_BOOKING';
3049 l_api_version CONSTANT NUMBER := 1;
3050
3051 CURSOR ib_csr (p_okl_chr_id OKC_K_HEADERS_B.ID%TYPE,
3052 p_okl_cov_line_id OKC_K_LINES_B.ID%TYPE) IS
3053 SELECT ib_line.id,
3054 ib_item.id,
3055 ib_item.object1_id1,
3056 ib_item.object1_id2
3057 FROM okc_k_lines_b ib_line,
3058 okc_line_styles_b ib_style,
3059 okc_k_items ib_item,
3060 okc_k_lines_b cov_line,
3061 okc_k_items cov_item,
3062 okc_k_lines_b inst_line,
3063 okc_line_styles_b inst_style,
3064 okc_k_rel_objs rel
3065 WHERE rel.cle_id = cov_line.id
3066 AND rel.chr_id = cov_line.dnz_chr_id
3067 AND cov_line.id = cov_item.cle_id
3068 AND cov_line.dnz_chr_id = rel.chr_id
3069 AND cov_item.object1_id1 = inst_line.cle_id
3070 AND cov_item.jtot_object1_code = 'OKX_COVASST'
3071 AND inst_style.lty_code = 'FREE_FORM2'
3072 AND inst_line.lse_id = inst_style.id
3073 AND inst_line.id = ib_line.cle_id
3074 AND inst_line.dnz_chr_id = ib_line.dnz_chr_id
3075 AND ib_line.lse_id = ib_style.id
3076 AND ib_style.lty_code = 'INST_ITEM'
3077 AND ib_line.id = ib_item.cle_id
3078 AND ib_item.dnz_chr_id = ib_line.dnz_chr_id
3079 AND ib_item.jtot_object1_code = 'OKX_IB_ITEM'
3080 AND rel.cle_id = p_okl_cov_line_id
3081 AND rel.chr_id = p_okl_chr_id
3082 AND ib_item.object1_id1 IS NULL
3083 AND ROWNUM < 2; -- to ensure first line with null instance_id
3084
3085 CURSOR oks_ib_csr (p_oks_chr_id OKC_K_HEADERS_B.ID%TYPE,
3086 p_oks_cov_line_id OKC_K_LINES_B.ID%TYPE) IS
3087 SELECT object1_id1,
3088 object1_id2
3089 FROM okc_k_items
3090 WHERE cle_id = p_oks_cov_line_id
3091 AND dnz_chr_id = p_oks_chr_id;
3092 --AND chr_id = p_oks_chr_id;
3093 -- don't use chr_id, use dnz_chr_id instead Bug# 2739831
3094
3095 CURSOR inst_csr (p_chr_id NUMBER) IS
3096 SELECT
3097 csi.instance_id,
3098 csi.inventory_item_id,
3099 csi.inv_master_organization_id,
3100 csi.install_location_id location_id,
3101 csi.install_location_type_code location_type_code,
3102 csi.instance_number
3103 FROM okc_k_rel_objs krel,
3104 okc_k_items kitem,
3105 csi_item_instances csi,
3106 okc_k_lines_b line,
3107 okc_line_styles_b style,
3108 OKC_STATUSES_B sts
3109 WHERE krel.rty_code = 'OKLSRV'
3110 AND krel.jtot_object1_code = 'OKL_COV_PROD'
3111 AND kitem.cle_id = krel.object1_id1
3112 AND kitem.jtot_object1_code = 'OKX_CUSTPROD'
3113 AND csi.instance_id = kitem.object1_id1
3114 AND krel.cle_id = line.id
3115 AND line.lse_id = style.id
3116 AND style.lty_code = 'LINK_SERV_ASSET'
3117 AND line.dnz_chr_id = p_chr_id
3118 AND sts.code = line.sts_code
3119 AND sts.ste_code NOT IN ('HOLD', 'TERMINATED', 'EXPIRED', 'CANCELLED');
3120
3121 CURSOR mtl_csr (p_inv_id NUMBER,
3122 p_org_id NUMBER) IS
3123 SELECT description
3124 FROM mtl_system_items
3125 WHERE inventory_item_id = p_inv_id
3126 AND organization_id = p_org_id;
3127
3128 l_oks_chr_id OKC_K_HEADERS_B.ID%TYPE;
3129 l_link_line_tbl OKL_SERVICE_INTEGRATION_PUB.link_line_tbl_type;
3130 l_cimv_rec okc_contract_item_pub.cimv_rec_type;
3131 x_cimv_rec okc_contract_item_pub.cimv_rec_type;
3132 l_ib_line_id OKC_K_LINES_B.ID%TYPE;
3133 l_ib_item_id OKC_K_ITEMS_V.ID%TYPE;
3134 l_ib_object1_id1 OKC_K_ITEMS_V.OBJECT1_ID1%TYPE;
3135 l_ib_object1_id2 OKC_K_ITEMS_V.OBJECT1_ID2%TYPE;
3136
3137 l_oks_object1_id1 OKC_K_ITEMS_V.OBJECT1_ID1%TYPE;
3138 l_oks_object1_id2 OKC_K_ITEMS_V.OBJECT1_ID2%TYPE;
3139
3140 l_item_desc OKL_LA_COV_ASSET_UV.item_description%TYPE;
3141
3142 BEGIN -- main process begins here
3143 IF (G_DEBUG_ENABLED = 'Y') THEN
3144 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
3145 END IF;
3146
3147 x_return_status := OKC_API.G_RET_STS_SUCCESS;
3148 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3149 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
3150 END IF;
3151
3152 -- call START_ACTIVITY to create savepoint, check compatibility
3153 -- and initialize message list
3154 x_return_status := OKC_API.START_ACTIVITY(
3155 p_api_name => l_api_name,
3156 p_pkg_name => G_PKG_NAME,
3157 p_init_msg_list => p_init_msg_list,
3158 l_api_version => l_api_version,
3159 p_api_version => p_api_version,
3160 p_api_type => G_API_TYPE,
3161 x_return_status => x_return_status);
3162
3163 -- check if activity started successfully
3164 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3165 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3166 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
3167 raise OKC_API.G_EXCEPTION_ERROR;
3168 END IF;
3169 --************************************************
3170 check_service_link(
3171 p_api_version => 1.0,
3172 p_init_msg_list => OKL_API.G_FALSE,
3173 x_return_status => x_return_status,
3174 x_msg_count => x_msg_count,
3175 x_msg_data => x_msg_data,
3176 p_lease_contract_id => p_okl_chr_id,
3177 x_service_contract_id => l_oks_chr_id
3178 );
3179
3180 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
3181 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3182 END IF;
3183
3184 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3185 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Service contract ID: '||l_oks_chr_id);
3186 END IF;
3187
3188 IF (l_oks_chr_id IS NOT NULL) THEN -- Service line is linked to OKS Contract
3189
3190 --
3191 -- Check OKS Covered product Install site,
3192 -- raise error if it is not defined as INSTALL_AT
3193 -- Bug 3569441
3194 --
3195 FOR inst_rec IN inst_csr (p_okl_chr_id)
3196 LOOP
3197 OPEN mtl_csr (inst_rec.inventory_item_id,
3198 inst_rec.inv_master_organization_id);
3199 FETCH mtl_csr INTO l_item_desc;
3200 CLOSE mtl_csr;
3201
3202 check_prod_instance(
3203 x_return_status => x_return_status,
3204 x_msg_count => x_msg_count,
3205 x_msg_data => x_msg_data,
3206 p_cov_prod => l_item_desc,
3207 p_instance_id => inst_rec.instance_id
3208 );
3209
3210 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
3211 raise OKC_API.G_EXCEPTION_ERROR;
3212 END IF;
3213
3214 END LOOP;
3215
3216 --
3217 -- Update JTOT_OBJECT1_CODE to 'OKL_SERVICE'
3218 -- Fix Bug# 2872267
3219 --
3220 update_jtf_code(
3221 p_api_version => 1.0,
3222 p_init_msg_list => OKL_API.G_FALSE,
3223 x_return_status => x_return_status,
3224 x_msg_count => x_msg_count,
3225 x_msg_data => x_msg_data,
3226 p_okl_chr_id => p_okl_chr_id,
3227 p_oks_chr_id => l_oks_chr_id,
3228 p_jtf_code => 'OKL_SERVICE'
3229 );
3230
3231 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
3232 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3233 END IF;
3234
3235 --
3236 -- set AR Interface flag to 'N' to stop billing
3237 -- Bug# 2776123
3238 --
3239 /* Removed as this flag must remain as Y for OKS billing process to run
3240 * DEDEY - 02/15/2003
3241
3242 update_oks_ar_intf(
3243 x_return_status => x_return_status,
3244 x_msg_count => x_msg_count,
3245 x_msg_data => x_msg_data,
3246 p_oks_chr_id => l_oks_chr_id,
3247 p_ar_intf_val => 'N'
3248 );
3249
3250 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
3251 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3252 END IF;
3253 */
3254
3255 get_service_link_line (
3256 p_api_version => 1.0,
3257 p_init_msg_list => OKL_API.G_FALSE,
3258 x_return_status => x_return_status,
3259 x_msg_count => x_msg_count,
3260 x_msg_data => x_msg_data,
3261 p_lease_contract_id => p_okl_chr_id,
3262 x_link_line_tbl => l_link_line_tbl,
3263 x_service_contract_id => l_oks_chr_id
3264 );
3265
3266 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
3267 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3268 END IF;
3269
3270 --dbms_output.put_line('Line Link count: '||l_link_line_tbl.COUNT);
3271
3272 --
3273 -- Expire IB instance(s)
3274 --
3275 expire_lease_instance(
3276 p_api_version => 1.0,
3277 p_init_msg_list => OKL_API.G_FALSE,
3278 x_return_status => x_return_status,
3279 x_msg_count => x_msg_count,
3280 x_msg_data => x_msg_data,
3281 p_okl_chr_id => p_okl_chr_id
3282 );
3283
3284 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
3285 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3286 END IF;
3287
3288 FOR i IN 1..l_link_line_tbl.COUNT
3289 LOOP
3290 OPEN ib_csr (p_okl_chr_id,
3291 l_link_line_tbl(i).okl_service_line_id);
3292 FETCH ib_csr INTO l_ib_line_id,
3293 l_ib_item_id,
3294 l_ib_object1_id1,
3295 l_ib_object1_id2;
3296 CLOSE ib_csr;
3297
3298 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3299 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'IB: '||l_ib_line_id||', '||l_ib_item_id||', '||l_ib_object1_id1);
3300 END IF;
3301
3302 IF (l_ib_line_id IS NOT NULL
3303 AND
3304 l_ib_object1_id1 IS NULL) THEN
3305 OPEN oks_ib_csr (l_oks_chr_id,
3306 l_link_line_tbl(i).oks_service_line_id);
3307 FETCH oks_ib_csr INTO l_oks_object1_id1,
3308 l_oks_object1_id2;
3309 CLOSE oks_ib_csr;
3310
3311 --dbms_output.put_line('Instance : '||l_link_line_tbl(i).oks_service_line_id||' : '||l_oks_object1_id1);
3312 -- Update IB line
3313 l_cimv_rec.id := l_ib_item_id;
3314 l_cimv_rec.object1_id1 := l_oks_object1_id1; -- copy instance ID from OKS to IB line in OKL
3315 l_cimv_rec.object1_id2 := l_oks_object1_id2;
3316
3317
3318 ----- Changes by Kanti
3319 ----- Validate the JTOT Object code, ID1 and ID2
3320
3321 okl_la_validation_util_pvt.VALIDATE_STYLE_JTOT (p_api_version => l_api_version,
3322 p_init_msg_list => OKC_API.G_FALSE,
3323 x_return_status => x_return_status,
3324 x_msg_count => x_msg_count,
3325 x_msg_data => x_msg_data,
3326 p_object_name => l_cimv_rec.jtot_object1_code,
3327 p_id1 => l_cimv_rec.object1_id1,
3328 p_id2 => l_cimv_rec.object1_id2);
3329 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3330 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3331 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3332 RAISE OKC_API.G_EXCEPTION_ERROR;
3333 END IF;
3334
3335 ---- Changes End
3336
3337
3338 okc_contract_item_pub.update_contract_item(
3339 p_api_version => 1.0,
3340 p_init_msg_list => OKL_API.G_FALSE,
3341 x_return_status => x_return_status,
3342 x_msg_count => x_msg_count,
3343 x_msg_data => x_msg_data,
3344 p_cimv_rec => l_cimv_rec,
3345 x_cimv_rec => x_cimv_rec
3346 );
3347
3348 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
3349 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3350 END IF;
3351
3352 END IF;
3353
3354 END LOOP;
3355
3356 END IF;
3357
3358 --************************************************
3359
3360 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
3361 x_msg_data => x_msg_data);
3362
3363 EXCEPTION
3364 when OKC_API.G_EXCEPTION_ERROR then
3365 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3366 p_api_name => l_api_name,
3367 p_pkg_name => G_PKG_NAME,
3368 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
3369 x_msg_count => x_msg_count,
3370 x_msg_data => x_msg_data,
3371 p_api_type => G_API_TYPE);
3372
3373 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
3374 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3375 p_api_name => l_api_name,
3376 p_pkg_name => G_PKG_NAME,
3377 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
3378 x_msg_count => x_msg_count,
3379 x_msg_data => x_msg_data,
3380 p_api_type => G_API_TYPE);
3381
3382 when OTHERS then
3383 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3384 p_api_name => l_api_name,
3385 p_pkg_name => G_PKG_NAME,
3386 p_exc_name => 'OTHERS',
3387 x_msg_count => x_msg_count,
3388 x_msg_data => x_msg_data,
3389 p_api_type => G_API_TYPE);
3390
3391 END initiate_service_booking;
3392
3393 ------------------------------------------------------------------------------
3394 -- PROCEDURE process_serial_item
3395 --
3396 -- This procedure is used to create and attach service sub-line
3397 -- for serialized inventory item. One service line in OKL might
3398 -- be linked to multiple covered product line(s) in case of
3399 -- serialized items attached to an asset/product.
3400 --
3401 -- Calls:
3402 -- Called By:
3403 ------------------------------------------------------------------------------
3404 PROCEDURE process_serial_item (
3405 x_return_status OUT NOCOPY VARCHAR2,
3406 x_msg_count OUT NOCOPY NUMBER,
3407 x_msg_data OUT NOCOPY VARCHAR2,
3408 p_okl_chr_id IN OKC_K_HEADERS_V.ID%TYPE,
3409 p_free_form1_id IN OKC_K_LINES_V.ID%TYPE,
3410 p_okl_srv_line_id IN OKC_K_LINES_V.ID%TYPE
3411 ) IS
3412
3413 l_proc_name VARCHAR2(35) := 'process_serial_item';
3414
3415 CURSOR serial_csr (p_srv_line_id OKC_K_LINES_B.ID%TYPE,
3416 p_asset_id OKC_K_LINES_B.ID%TYPE) IS
3417 SELECT *
3418 FROM okl_la_cov_asset_uv
3419 WHERE serv_top_line_id = p_srv_line_id
3420 AND id1 = p_asset_id
3421 AND serial_number_control_code <> 1;
3422
3423 TYPE cov_prod_rec IS RECORD (
3424 oks_cov_line_id OKC_K_LINES_B.ID%TYPE,
3425 serv_top_line_id OKC_K_LINES_B.ID%TYPE
3426 );
3427 TYPE cov_prod_tbl IS TABLE OF cov_prod_rec
3428 INDEX BY BINARY_INTEGER;
3429
3430 l_cov_prod_tbl cov_prod_tbl;
3431 l_srv_qty NUMBER;
3432 l_lease_qty NUMBER;
3433 l_price_negotiated NUMBER;
3434 l_asset_number VARCHAR2(35);
3435 i NUMBER;
3436 j NUMBER;
3437
3438 l_line_item_tbl okl_contract_line_item_pvt.line_item_tbl_type;
3439 x_line_item_tbl okl_contract_line_item_pvt.line_item_tbl_type;
3440
3441 BEGIN
3442 IF (G_DEBUG_ENABLED = 'Y') THEN
3443 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
3444 END IF;
3445
3446 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3447
3448 l_srv_qty := 0;
3449 l_lease_qty := 0;
3450 i := 0;
3451 l_cov_prod_tbl.DELETE;
3452 l_price_negotiated := 0;
3453 FOR serial_rec IN serial_csr (p_okl_srv_line_id,
3454 p_free_form1_id)
3455 LOOP
3456 i := serial_csr%ROWCOUNT;
3457 l_srv_qty := l_srv_qty + serial_rec.service_item_qty;
3458 l_price_negotiated := l_price_negotiated + serial_rec.price_negotiated;
3459 l_cov_prod_tbl(i).oks_cov_line_id := serial_rec.oks_cov_line_id;
3460 l_cov_prod_tbl(i).serv_top_line_id := serial_rec.serv_top_line_id;
3461 l_lease_qty := serial_rec.lease_item_qty;
3462 l_asset_number := serial_rec.name;
3463 END LOOP;
3464
3465 j := 0;
3466 IF (l_lease_qty = l_srv_qty
3467 AND
3468 l_lease_qty <> 0
3469 AND
3470 l_srv_qty <> 0) THEN
3471
3472 j := j + 1;
3473 l_line_item_tbl(1).chr_id := p_okl_chr_id;
3474 l_line_item_tbl(1).parent_cle_id := p_okl_srv_line_id;
3475 l_line_item_tbl(1).name := l_asset_number;
3476 l_line_item_tbl(1).capital_amount := l_price_negotiated;
3477 l_line_item_tbl(1).serv_cov_prd_id := l_cov_prod_tbl(1).oks_cov_line_id;
3478 l_line_item_tbl(1).item_object1_code := 'OKX_COVASST';
3479 l_line_item_tbl(1).item_id1 := p_free_form1_id;
3480 l_line_item_tbl(1).item_id2 := '#';
3481
3482 -- Call linked asset line creation api
3483 okl_contract_line_item_pub.create_contract_line_item(
3484 p_api_version => 1.0,
3485 p_init_msg_list => OKL_API.G_FALSE,
3486 x_return_status => x_return_status,
3487 x_msg_count => x_msg_count,
3488 x_msg_data => x_msg_data,
3489 p_line_item_tbl => l_line_item_tbl,
3490 x_line_item_tbl => x_line_item_tbl
3491 );
3492 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3493 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'===>After calling okl_contract_line_item_pub.create_contract_line_item');
3494 END IF;
3495 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3496 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3497 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
3498 raise OKC_API.G_EXCEPTION_ERROR;
3499 END IF;
3500
3501 END IF;
3502
3503 -- 1st covered prod is being linked in previous call
3504 -- now link rest of the covered prod lines
3505 -- many-to-one relation between okl linked service line and
3506 -- oks covered prod line for serialized item
3507 FOR k IN 2..l_cov_prod_tbl.COUNT
3508 LOOP
3509 -- Link coverd product
3510
3511 create_cov_line_link(
3512 x_return_status => x_return_status,
3513 x_msg_count => x_msg_count,
3514 x_msg_data => x_msg_data,
3515 p_okl_chr_id => p_okl_chr_id,
3516 p_okl_cov_line_id => x_line_item_tbl(1).cle_id,
3517 p_oks_cov_line_id => l_cov_prod_tbl(k).oks_cov_line_id
3518 );
3519
3520 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3521 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3522 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
3523 raise OKC_API.G_EXCEPTION_ERROR;
3524 END IF;
3525
3526 END LOOP;
3527
3528 END process_serial_item;
3529
3530 ------------------------------------------------------------------------------
3531 -- PROCEDURE create_service_from_oks
3532 --
3533 -- This procedure is used to create service link(s) and associate
3534 -- assets on one go. Only input is OKS and OKL contract ID. This
3535 -- process will look into OKS contract and create service line in OKL. It
3536 -- also creates associations of assets in OKL.
3537 --
3538 -- Calls:
3539 -- Called By:
3540 ------------------------------------------------------------------------------
3541 PROCEDURE create_service_from_oks(
3542 p_api_version IN NUMBER,
3543 p_init_msg_list IN VARCHAR2,
3544 x_return_status OUT NOCOPY VARCHAR2,
3545 x_msg_count OUT NOCOPY NUMBER,
3546 x_msg_data OUT NOCOPY VARCHAR2,
3547 p_okl_chr_id IN OKC_K_HEADERS_V.ID%TYPE, -- Lease Contract Header ID
3548 p_oks_chr_id IN OKC_K_HEADERS_V.ID%TYPE, -- Service Contract Header ID
3549 p_supplier_id IN NUMBER, -- OKL_VENDOR
3550 p_sty_id IN OKL_K_LINES.STY_ID%TYPE DEFAULT NULL, -- Bug 4011710
3551 x_okl_service_line_id OUT NOCOPY OKC_K_LINES_V.ID%TYPE
3552 ) IS
3553
3554 l_api_name VARCHAR2(35) := 'create_service_from_oks';
3555 l_proc_name VARCHAR2(35) := 'CREATE_SERVICE_FROM_OKS';
3556 l_api_version CONSTANT NUMBER := 1;
3557
3558 -- Check for 11.5.9 or 11.5.10 OKS version
3559 CURSOR check_oks_ver IS
3560 SELECT 1
3561 FROM okc_class_operations
3562 WHERE cls_code = 'SERVICE'
3563 AND opn_code = 'CHECK_RULE';
3564
3565 l_dummy NUMBER;
3566 l_oks_ver VARCHAR2(3);
3567
3568 CURSOR oks_serv_csr (p_oks_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
3569 SELECT DISTINCT cle_id
3570 FROM okl_la_link_service_uv
3571 WHERE chr_id = p_oks_chr_id;
3572
3573 CURSOR oks_serv_csr9 (p_oks_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
3574 SELECT DISTINCT cle_id
3575 FROM okl_la_link_service_uv9
3576 WHERE chr_id = p_oks_chr_id;
3577
3578 CURSOR oks_cov_csr(p_oks_serv_id OKC_K_LINES_V.ID%TYPE) IS
3579 SELECT line.id,
3580 line.price_negotiated
3581 FROM okc_k_lines_b line,
3582 okc_line_styles_v style
3583 WHERE line.lse_id = style.id
3584 AND cle_id = p_oks_serv_id
3585 AND lty_code = 'COVER_PROD'
3586 AND NOT EXISTS (SELECT 'Y'
3587 FROM okc_k_rel_objs rel
3588 WHERE TO_CHAR(line.id) = rel.object1_id1
3589 AND jtot_object1_code = 'OKL_COV_PROD'
3590 AND rty_code = 'OKLSRV'); -- to pickup only non-linked lines
3591
3592 CURSOR okl_asset_csr (p_oks_cov_line_id OKC_K_LINES_V.ID%TYPE,
3593 p_okl_serv_line_id OKC_K_LINES_V.ID%TYPE) IS
3594 SELECT name,
3595 id1,
3596 id2 --,
3597 --lease_item_qty,
3598 --service_item_qty
3599 FROM okl_la_cov_asset_uv cov_asset
3600 WHERE oks_cov_line_id = p_oks_cov_line_id
3601 AND serv_top_line_id = p_okl_serv_line_id
3602 -- bug 4889070. START
3603 AND lease_item_qty = service_item_qty
3604 -- bug 4889070. END
3605 AND NOT EXISTS (SELECT 'Y'
3606 FROM okc_k_items cov_item,
3607 okc_k_lines_b line
3608 WHERE cov_item.object1_id1 = cov_asset.id1
3609 AND cov_item.jtot_object1_code = 'OKX_COVASST'
3610 AND line.id = cov_item.cle_id
3611 AND line.id = cov_asset.serv_top_line_id
3612 AND line.sts_code <> 'ABANDONED');
3613
3614 CURSOR check_serial_csr (p_okl_service_id OKC_K_LINES_V.ID%TYPE) IS
3615 SELECT distinct id1
3616 FROM okl_la_cov_asset_uv
3617 WHERE serv_top_line_id = p_okl_service_id
3618 AND serial_number_control_code <> 1; -- serial item
3619
3620 l_line_item_tbl okl_contract_line_item_pvt.line_item_tbl_type;
3621 x_line_item_tbl okl_contract_line_item_pvt.line_item_tbl_type;
3622 i NUMBER := 0;
3623 l_asset_number okl_la_cov_asset_uv.name%TYPE;
3624 l_id1 okl_la_cov_asset_uv.id1%TYPE;
3625 l_id2 okl_la_cov_asset_uv.id2%TYPE;
3626
3627 -- bug 4889070 commented local vars as they are no longer reqd
3628 -- l_lease_item_qty NUMBER;
3629 -- l_service_item_qty NUMBER;
3630
3631 x_tcnv_rec tcnv_rec_type;
3632
3633 BEGIN
3634 IF (G_DEBUG_ENABLED = 'Y') THEN
3635 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
3636 END IF;
3637
3638 x_return_status := OKC_API.G_RET_STS_SUCCESS;
3639 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3640 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
3641 END IF;
3642
3643 -- call START_ACTIVITY to create savepoint, check compatibility
3644 -- and initialize message list
3645 x_return_status := OKC_API.START_ACTIVITY(
3646 p_api_name => l_api_name,
3647 p_pkg_name => G_PKG_NAME,
3648 p_init_msg_list => p_init_msg_list,
3649 l_api_version => l_api_version,
3650 p_api_version => p_api_version,
3651 p_api_type => G_API_TYPE,
3652 x_return_status => x_return_status);
3653
3654 -- check if activity started successfully
3655 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3656 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3657 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
3658 raise OKC_API.G_EXCEPTION_ERROR;
3659 END IF;
3660
3661 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
3662
3663 -- populate stream id to global stream id
3664 G_STY_ID := p_sty_id; -- Bug 4011710
3665
3666 l_oks_ver := '?';
3667 OPEN check_oks_ver;
3668 FETCH check_oks_ver INTO l_dummy;
3669 IF check_oks_ver%NOTFOUND THEN
3670 l_oks_ver := '9';
3671 ELSE
3672 l_oks_ver := '10';
3673 END IF;
3674 CLOSE check_oks_ver;
3675
3676 IF (l_oks_ver = '10') THEN -- 11.5.10 OKS code
3677
3678 FOR oks_serv_rec IN oks_serv_csr(p_oks_chr_id)
3679 LOOP
3680 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3681 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'===>Before calling create_link_service_line');
3682 END IF;
3683 create_link_service_line(
3684 p_api_version => 1.0,
3685 p_init_msg_list => OKL_API.G_FALSE,
3686 x_return_status => x_return_status,
3687 x_msg_count => x_msg_count,
3688 x_msg_data => x_msg_data,
3689 p_okl_chr_id => p_okl_chr_id,
3690 p_oks_chr_id => p_oks_chr_id,
3691 p_oks_service_line_id => oks_serv_rec.cle_id,
3692 p_supplier_id => p_supplier_id,
3693 x_okl_service_line_id => x_okl_service_line_id
3694 );
3695 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3696 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'===>After calling create_link_service_line');
3697 END IF;
3698
3699 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3700 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3701 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
3702 raise OKC_API.G_EXCEPTION_ERROR;
3703 END IF;
3704
3705 --
3706 -- Create LINK transaction
3707 --
3708 okl_transaction_pvt.create_service_transaction(
3709 p_api_version => 1.0,
3710 p_init_msg_list => OKL_API.G_FALSE,
3711 x_return_status => x_return_status,
3712 x_msg_count => x_msg_count,
3713 x_msg_data => x_msg_data,
3714 p_lease_id => p_okl_chr_id,
3715 p_service_id => p_oks_chr_id,
3716 p_description => 'Link Service Contract',
3717 p_trx_date => SYSDATE,
3718 p_status => G_LINK,
3719 x_tcnv_rec => x_tcnv_rec
3720 );
3721
3722
3723 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3724 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3725 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
3726 raise OKC_API.G_EXCEPTION_ERROR;
3727 END IF;
3728
3729 --
3730 -- Start Serial processing
3731 FOR check_serial_rec IN check_serial_csr (x_okl_service_line_id)
3732 LOOP
3733 process_serial_item (
3734 x_return_status => x_return_status,
3735 x_msg_count => x_msg_count,
3736 x_msg_data => x_msg_data,
3737 p_okl_chr_id => p_okl_chr_id,
3738 p_free_form1_id => check_serial_rec.id1,
3739 p_okl_srv_line_id => x_okl_service_line_id
3740 );
3741
3742 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3743 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3744 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
3745 raise OKC_API.G_EXCEPTION_ERROR;
3746 END IF;
3747
3748 END LOOP;
3749 -- End Serial processing
3750
3751 i := 0;
3752 l_line_item_tbl.delete;
3753 FOR oks_cov_rec IN oks_cov_csr(oks_serv_rec.cle_id)
3754 LOOP
3755
3756 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3757 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKS COV LINE: '||oks_cov_rec.id);
3758 END IF;
3759
3760 OPEN okl_asset_csr(oks_cov_rec.id,
3761 x_okl_service_line_id
3762 );
3763 FETCH okl_asset_csr INTO l_asset_number,
3764 l_id1,
3765 l_id2;
3766 -- l_lease_item_qty,
3767 -- l_service_item_qty;
3768 IF ( okl_asset_csr%NOTFOUND ) THEN
3769 -- bug 4889070 commented this quantity mismatch condn as it is included in the cursor
3770 -- OR l_lease_item_qty <> l_service_item_qty) THEN
3771 okl_api.set_message(
3772 G_APP_NAME,
3773 G_SRV_NO_ASSET_MATCH
3774 );
3775 x_return_status := OKL_API.G_RET_STS_ERROR;
3776 raise OKC_API.G_EXCEPTION_ERROR;
3777 END IF;
3778
3779 CLOSE okl_asset_csr;
3780
3781 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3782 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'===>Asset number: '||l_asset_number);
3783 END IF;
3784
3785 i := i + 1;
3786 l_line_item_tbl(i).chr_id := p_okl_chr_id;
3787 l_line_item_tbl(i).parent_cle_id := x_okl_service_line_id;
3788 l_line_item_tbl(i).name := l_asset_number;
3789 l_line_item_tbl(i).capital_amount := oks_cov_rec.price_negotiated;
3790 l_line_item_tbl(i).serv_cov_prd_id := oks_cov_rec.id;
3791 l_line_item_tbl(i).item_object1_code := 'OKX_COVASST';
3792 l_line_item_tbl(i).item_id1 := l_id1;
3793 l_line_item_tbl(i).item_id2 := l_id2;
3794
3795 END LOOP;
3796
3797 IF (i > 0) THEN -- Covered line found
3798 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3799 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'===>Before calling okl_contract_line_item_pub.create_contract_line_item');
3800 END IF;
3801 okl_contract_line_item_pub.create_contract_line_item(
3802 p_api_version => 1.0,
3803 p_init_msg_list => OKL_API.G_FALSE,
3804 x_return_status => x_return_status,
3805 x_msg_count => x_msg_count,
3806 x_msg_data => x_msg_data,
3807 p_line_item_tbl => l_line_item_tbl,
3808 x_line_item_tbl => x_line_item_tbl
3809 );
3810 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3811 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'===>After calling okl_contract_line_item_pub.create_contract_line_item');
3812 END IF;
3813 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3814 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3815 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
3816 raise OKC_API.G_EXCEPTION_ERROR;
3817 END IF;
3818
3819 END IF;
3820
3821 END LOOP; --oks_serv_csr 11.5.10
3822
3823 ELSE -- 11.5.9 code
3824
3825 FOR oks_serv_rec IN oks_serv_csr9(p_oks_chr_id)
3826 LOOP
3827 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3828 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'===>Before calling create_link_service_line');
3829 END IF;
3830 create_link_service_line(
3831 p_api_version => 1.0,
3832 p_init_msg_list => OKL_API.G_FALSE,
3833 x_return_status => x_return_status,
3834 x_msg_count => x_msg_count,
3835 x_msg_data => x_msg_data,
3836 p_okl_chr_id => p_okl_chr_id,
3837 p_oks_chr_id => p_oks_chr_id,
3838 p_oks_service_line_id => oks_serv_rec.cle_id,
3839 p_supplier_id => p_supplier_id,
3840 x_okl_service_line_id => x_okl_service_line_id
3841 );
3842 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3843 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'===>After calling create_link_service_line');
3844 END IF;
3845
3846 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3847 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3848 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
3849 raise OKC_API.G_EXCEPTION_ERROR;
3850 END IF;
3851
3852 --
3853 -- Create LINK transaction
3854 --
3855 okl_transaction_pvt.create_service_transaction(
3856 p_api_version => 1.0,
3857 p_init_msg_list => OKL_API.G_FALSE,
3858 x_return_status => x_return_status,
3859 x_msg_count => x_msg_count,
3860 x_msg_data => x_msg_data,
3861 p_lease_id => p_okl_chr_id,
3862 p_service_id => p_oks_chr_id,
3863 p_description => 'Link Service Contract',
3864 p_trx_date => SYSDATE,
3865 p_status => G_LINK,
3866 x_tcnv_rec => x_tcnv_rec
3867 );
3868
3869
3870 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3871 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3872 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
3873 raise OKC_API.G_EXCEPTION_ERROR;
3874 END IF;
3875
3876 --
3877 -- Start Serial processing
3878 FOR check_serial_rec IN check_serial_csr (x_okl_service_line_id)
3879 LOOP
3880 process_serial_item (
3881 x_return_status => x_return_status,
3882 x_msg_count => x_msg_count,
3883 x_msg_data => x_msg_data,
3884 p_okl_chr_id => p_okl_chr_id,
3885 p_free_form1_id => check_serial_rec.id1,
3886 p_okl_srv_line_id => x_okl_service_line_id
3887 );
3888
3889 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3890 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3891 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
3892 raise OKC_API.G_EXCEPTION_ERROR;
3893 END IF;
3894
3895 END LOOP;
3896 -- End Serial processing
3897
3898 i := 0;
3899 l_line_item_tbl.delete;
3900 FOR oks_cov_rec IN oks_cov_csr(oks_serv_rec.cle_id)
3901 LOOP
3902
3903 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3904 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKS COV LINE: '||oks_cov_rec.id);
3905 END IF;
3906
3907 OPEN okl_asset_csr(oks_cov_rec.id,
3908 x_okl_service_line_id
3909 );
3910 FETCH okl_asset_csr INTO l_asset_number,
3911 l_id1,
3912 l_id2;
3913 --l_lease_item_qty,
3914 --l_service_item_qty;
3915 IF ( okl_asset_csr%NOTFOUND) THEN
3916 -- bug 4889070 commented this quantity mismatch condn as it is included in the cursor
3917 -- OR l_lease_item_qty <> l_service_item_qty) THEN
3918
3919 okl_api.set_message(
3920 G_APP_NAME,
3921 G_SRV_NO_ASSET_MATCH
3922 );
3923 x_return_status := OKL_API.G_RET_STS_ERROR;
3924 raise OKC_API.G_EXCEPTION_ERROR;
3925 END IF;
3926
3927 CLOSE okl_asset_csr;
3928
3929 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3930 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'===>Asset number: '||l_asset_number);
3931 END IF;
3932
3933 i := i + 1;
3934 l_line_item_tbl(i).chr_id := p_okl_chr_id;
3935 l_line_item_tbl(i).parent_cle_id := x_okl_service_line_id;
3936 l_line_item_tbl(i).name := l_asset_number;
3937 l_line_item_tbl(i).capital_amount := oks_cov_rec.price_negotiated;
3938 l_line_item_tbl(i).serv_cov_prd_id := oks_cov_rec.id;
3939 l_line_item_tbl(i).item_object1_code := 'OKX_COVASST';
3940 l_line_item_tbl(i).item_id1 := l_id1;
3941 l_line_item_tbl(i).item_id2 := l_id2;
3942
3943 END LOOP;
3944
3945 IF (i > 0) THEN -- Covered line found
3946 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3947 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'===>Before calling okl_contract_line_item_pub.create_contract_line_item');
3948 END IF;
3949 okl_contract_line_item_pub.create_contract_line_item(
3950 p_api_version => 1.0,
3951 p_init_msg_list => OKL_API.G_FALSE,
3952 x_return_status => x_return_status,
3953 x_msg_count => x_msg_count,
3954 x_msg_data => x_msg_data,
3955 p_line_item_tbl => l_line_item_tbl,
3956 x_line_item_tbl => x_line_item_tbl
3957 );
3958 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3959 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'===>After calling okl_contract_line_item_pub.create_contract_line_item');
3960 END IF;
3961 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
3962 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3963 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
3964 raise OKC_API.G_EXCEPTION_ERROR;
3965 END IF;
3966
3967 END IF;
3968
3969 END LOOP; --oks_serv_csr 11.5.9
3970 END IF; -- l_oks_ver
3971 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
3972
3973 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
3974 x_msg_data => x_msg_data);
3975
3976 EXCEPTION
3977
3978 when OKC_API.G_EXCEPTION_ERROR then
3979 IF (okl_asset_csr%ISOPEN) THEN
3980 CLOSE okl_asset_csr;
3981 END IF;
3982
3983 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3984 p_api_name => l_api_name,
3985 p_pkg_name => G_PKG_NAME,
3986 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
3987 x_msg_count => x_msg_count,
3988 x_msg_data => x_msg_data,
3989 p_api_type => G_API_TYPE);
3990
3991 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
3992 IF (okl_asset_csr%ISOPEN) THEN
3993 CLOSE okl_asset_csr;
3994 END IF;
3995
3996 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
3997 p_api_name => l_api_name,
3998 p_pkg_name => G_PKG_NAME,
3999 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
4000 x_msg_count => x_msg_count,
4001 x_msg_data => x_msg_data,
4002 p_api_type => G_API_TYPE);
4003
4004 when OTHERS then
4005 IF (okl_asset_csr%ISOPEN) THEN
4006 CLOSE okl_asset_csr;
4007 END IF;
4008
4009 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
4010 p_api_name => l_api_name,
4011 p_pkg_name => G_PKG_NAME,
4012 p_exc_name => 'OTHERS',
4013 x_msg_count => x_msg_count,
4014 x_msg_data => x_msg_data,
4015 p_api_type => G_API_TYPE);
4016
4017 END create_service_from_oks;
4018
4019 ------------------------------------------------------------------------------
4020 -- PROCEDURE delink_service_contract
4021 --
4022 -- This procedure delinks service contract from lease. It also updates
4023 -- de-linked service line status to ABANDONED in lease
4024 --
4025 -- Calls:
4026 -- Called By:
4027 ------------------------------------------------------------------------------
4028 PROCEDURE delink_service_contract(
4029 p_api_version IN NUMBER,
4030 p_init_msg_list IN VARCHAR2,
4031 x_return_status OUT NOCOPY VARCHAR2,
4032 x_msg_count OUT NOCOPY NUMBER,
4033 x_msg_data OUT NOCOPY VARCHAR2,
4034 p_okl_chr_id IN OKC_K_HEADERS_V.ID%TYPE
4035 ) IS
4036 l_api_name VARCHAR2(35) := 'delink_service_contract';
4037 l_proc_name VARCHAR2(35) := 'DELINK_SERVICE_CONTRACT';
4038 l_api_version CONSTANT NUMBER := 1;
4039
4040 CURSOR rel_link_csr(p_okl_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
4041 SELECT id,
4042 object1_id1,
4043 cle_id
4044 FROM okc_k_rel_objs_v
4045 WHERE chr_id = p_okl_chr_id
4046 AND rty_code = 'OKLSRV';
4047
4048 l_service_contract_id OKC_K_HEADERS_V.ID%TYPE;
4049 l_crjv_tbl crjv_tbl_type;
4050 i NUMBER;
4051 j NUMBER;
4052
4053 l_clev_tbl clev_tbl_type;
4054 x_clev_tbl clev_tbl_type;
4055 l_klev_tbl klev_tbl_type;
4056 x_klev_tbl klev_tbl_type;
4057
4058 l_oks_chr_id OKC_K_HEADERS_V.ID%TYPE;
4059 x_tcnv_rec tcnv_rec_type;
4060
4061 BEGIN
4062 IF (G_DEBUG_ENABLED = 'Y') THEN
4063 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
4064 END IF;
4065
4066 x_return_status := OKC_API.G_RET_STS_SUCCESS;
4067 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4068 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
4069 END IF;
4070
4071 -- call START_ACTIVITY to create savepoint, check compatibility
4072 -- and initialize message list
4073 x_return_status := OKC_API.START_ACTIVITY(
4074 p_api_name => l_api_name,
4075 p_pkg_name => G_PKG_NAME,
4076 p_init_msg_list => p_init_msg_list,
4077 l_api_version => l_api_version,
4078 p_api_version => p_api_version,
4079 p_api_type => G_API_TYPE,
4080 x_return_status => x_return_status);
4081
4082 -- check if activity started successfully
4083 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
4084 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4085 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
4086 raise OKC_API.G_EXCEPTION_ERROR;
4087 END IF;
4088
4089 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
4090 i := 0;
4091 j := 0;
4092 l_clev_tbl.DELETE;
4093 l_klev_tbl.DELETE;
4094
4095 FOR rel_rec IN rel_link_csr (p_okl_chr_id)
4096 LOOP
4097 i := rel_link_csr%ROWCOUNT;
4098 l_crjv_tbl(i).id := rel_rec.id;
4099
4100 IF (rel_rec.cle_id IS NOT NULL) THEN
4101 j := j + 1;
4102 l_clev_tbl(j).id := rel_rec.cle_id;
4103 l_klev_tbl(j).id := rel_rec.cle_id;
4104 l_clev_tbl(j).sts_code := 'ABANDONED';
4105 ELSE
4106 l_oks_chr_id := TO_NUMBER(rel_rec.object1_id1);
4107 END IF;
4108
4109 END LOOP;
4110
4111 IF (i = 0) THEN
4112 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4113 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'No Serviec contract is linked to the lease contract');
4114 END IF;
4115 RAISE OKC_API.G_EXCEPTION_ERROR;
4116 ELSE
4117
4118 --
4119 -- Create DE-LINK transaction
4120 --
4121 okl_transaction_pvt.create_service_transaction(
4122 p_api_version => 1.0,
4123 p_init_msg_list => OKL_API.G_FALSE,
4124 x_return_status => x_return_status,
4125 x_msg_count => x_msg_count,
4126 x_msg_data => x_msg_data,
4127 p_lease_id => p_okl_chr_id,
4128 p_service_id => l_oks_chr_id,
4129 p_description => 'De-Link Service Contract',
4130 p_trx_date => SYSDATE,
4131 p_status => G_DELINK,
4132 x_tcnv_rec => x_tcnv_rec
4133 );
4134
4135
4136 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
4137 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4138 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
4139 raise OKC_API.G_EXCEPTION_ERROR;
4140 END IF;
4141
4142 END IF;
4143
4144 OKC_K_REL_OBJS_PUB.delete_row (
4145 p_api_version => p_api_version,
4146 p_init_msg_list => OKL_API.G_FALSE,
4147 x_return_status => x_return_status,
4148 x_msg_count => x_msg_count,
4149 x_msg_data => x_msg_data,
4150 p_crjv_tbl => l_crjv_tbl
4151 );
4152
4153 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
4154 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4155 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
4156 raise OKC_API.G_EXCEPTION_ERROR;
4157 END IF;
4158
4159 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4160 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'De-linked...');
4161 END IF;
4162 --
4163 -- ABANDON the de-linked service line in OKL
4164 --
4165 okl_contract_pub.update_contract_line(
4166 p_api_version => p_api_version,
4167 p_init_msg_list => OKL_API.G_FALSE,
4168 x_return_status => x_return_status,
4169 x_msg_count => x_msg_count,
4170 x_msg_data => x_msg_data,
4171 p_clev_tbl => l_clev_tbl,
4172 p_klev_tbl => l_klev_tbl,
4173 x_clev_tbl => x_clev_tbl,
4174 x_klev_tbl => x_klev_tbl
4175 );
4176
4177 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4178 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error: '||x_msg_data);
4179 END IF;
4180
4181 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
4182 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4183 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
4184 raise OKC_API.G_EXCEPTION_ERROR;
4185 END IF;
4186
4187 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4188 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Abandoned...');
4189 END IF;
4190
4191 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
4192
4193 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
4194 x_msg_data => x_msg_data);
4195
4196 EXCEPTION
4197
4198 when OKC_API.G_EXCEPTION_ERROR then
4199 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
4200 p_api_name => l_api_name,
4201 p_pkg_name => G_PKG_NAME,
4202 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
4203 x_msg_count => x_msg_count,
4204 x_msg_data => x_msg_data,
4205 p_api_type => G_API_TYPE);
4206
4207 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
4208 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
4209 p_api_name => l_api_name,
4210 p_pkg_name => G_PKG_NAME,
4211 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
4212 x_msg_count => x_msg_count,
4213 x_msg_data => x_msg_data,
4214 p_api_type => G_API_TYPE);
4215
4216 when OTHERS then
4217 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
4218 p_api_name => l_api_name,
4219 p_pkg_name => G_PKG_NAME,
4220 p_exc_name => 'OTHERS',
4221 x_msg_count => x_msg_count,
4222 x_msg_data => x_msg_data,
4223 p_api_type => G_API_TYPE);
4224
4225 END delink_service_contract;
4226
4227 ------------------------------------------------------------------------------
4228 --Start of comments
4229 --
4230 --Procedure Name : get_trx_rec
4231 --Purpose : Gets source transaction record for IB interface
4232 --Modification History :
4233 --15-Jun-2001 ashish.singh Created
4234 --Notes : Assigns values to transaction_type_id and source_line_ref_id
4235 --End of Comments
4236 ------------------------------------------------------------------------------
4237 PROCEDURE get_trx_rec
4238 (p_api_version IN NUMBER,
4239 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
4240 x_return_status OUT NOCOPY VARCHAR2,
4241 x_msg_count OUT NOCOPY NUMBER,
4242 x_msg_data OUT NOCOPY VARCHAR2,
4243 p_cle_id IN NUMBER,
4244 p_transaction_type IN VARCHAR2,
4245 x_trx_rec OUT NOCOPY CSI_DATASTRUCTURES_PUB.transaction_rec) is
4246
4247 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4248 l_api_name CONSTANT VARCHAR2(30) := 'GET_TRX_REC';
4249 l_api_version CONSTANT NUMBER := 1.0;
4250
4251 --Following cursor assumes that a transaction type called
4252 --'OKL_BOOK' will be seeded in IB
4253 Cursor okl_trx_type_csr(p_transaction_type IN VARCHAR2)is
4254 select transaction_type_id
4255 from CSI_TXN_TYPES
4256 where source_transaction_type = p_transaction_type;
4257 l_trx_type_id NUMBER;
4258 Begin
4259 open okl_trx_type_csr(p_transaction_type);
4260 Fetch okl_trx_type_csr
4261 into l_trx_type_id;
4262 If okl_trx_type_csr%NotFound Then
4263 --OKL LINE ACTIVATION not seeded as a source transaction in IB
4264 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
4265 p_msg_name => G_IB_TXN_TYPE_NOT_FOUND,
4266 p_token1 => G_TXN_TYPE_TOKEN,
4267 p_token1_value => p_transaction_type
4268 );
4269 Raise OKL_API.G_EXCEPTION_ERROR;
4270 End If;
4271 close okl_trx_type_csr;
4272 --Assign transaction Type id to seeded value in cs_lookups
4273 x_trx_rec.transaction_type_id := l_trx_type_id;
4274 --Assign Source Line Ref id to contract line id of IB instance line
4275 x_trx_rec.source_line_ref_id := p_cle_id;
4276 x_trx_rec.transaction_date := sysdate;
4277 --confirm whether this has to be sysdate or creation date on line
4278 x_trx_rec.source_transaction_date := sysdate;
4279 Exception
4280 When OKL_API.G_EXCEPTION_ERROR Then
4281 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
4282 (
4283 l_api_name,
4284 G_PKG_NAME,
4285 'OKL_API.G_RET_STS_ERROR',
4286 x_msg_count,
4287 x_msg_data,
4288 '_PVT'
4289 );
4290 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4291 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
4292 (
4293 l_api_name,
4294 G_PKG_NAME,
4295 'OKL_API.G_RET_STS_UNEXP_ERROR',
4296 x_msg_count,
4297 x_msg_data,
4298 '_PVT'
4299 );
4300 WHEN OTHERS THEN
4301 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
4302 (
4303 l_api_name,
4304 G_PKG_NAME,
4305 'OTHERS',
4306 x_msg_count,
4307 x_msg_data,
4308 '_PVT'
4309 );
4310 END get_trx_rec;
4311
4312 ------------------------------------------------------------------------------
4313 -- PROCEDURE expire_lease_instance
4314 --
4315 -- This procedure expires IB instances from lease contract. This procedure is
4316 -- called during activation of lease contract having a linked service contract.
4317 --
4318 -- This procedure sets IB instance(s) to NULL on lease contract, which is later
4319 -- being assigned with corresponding OKS IB instance(s).
4320 --
4321 -- Calls:
4322 -- Called By:
4323 ------------------------------------------------------------------------------
4324 PROCEDURE expire_lease_instance(
4325 p_api_version IN NUMBER,
4326 p_init_msg_list IN VARCHAR2,
4327 x_return_status OUT NOCOPY VARCHAR2,
4328 x_msg_count OUT NOCOPY NUMBER,
4329 x_msg_data OUT NOCOPY VARCHAR2,
4330 p_okl_chr_id IN OKC_K_HEADERS_V.ID%TYPE
4331 ) IS
4332 l_api_name VARCHAR2(35) := 'expire_lease_instance';
4333 l_proc_name VARCHAR2(35) := 'EXPIRE_LEASE_INSTANCE';
4334 l_api_version CONSTANT NUMBER := 1;
4335
4336 CURSOR link_asset_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
4337 SELECT kitem.object1_id1
4338 FROM okc_k_rel_objs rel,
4339 okc_k_items kitem
4340 WHERE rel.cle_id = kitem.cle_id
4341 AND rel.rty_code = 'OKLSRV'
4342 AND rel.jtot_object1_code = 'OKL_COV_PROD'
4343 AND kitem.jtot_object1_code = 'OKX_COVASST'
4344 AND rel.chr_id = p_chr_id;
4345
4346 CURSOR inst_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE,
4347 p_cle_id OKC_K_LINES_V.ID%TYPE) IS
4348 SELECT kitem.id,
4349 kitem.object1_id1,
4350 kitem.cle_id
4351 FROM okc_k_lines_b form2,
4352 okc_line_styles_b f2_style,
4353 okc_k_lines_b inst,
4354 okc_line_styles_b inst_style,
4355 okc_k_items_v kitem
4356 WHERE form2.lse_id = f2_style.id
4357 AND f2_style.lty_code = 'FREE_FORM2'
4358 AND inst.lse_id = inst_style.id
4359 AND inst_style.lty_code = 'INST_ITEM'
4360 AND inst.id = kitem.cle_id
4361 AND kitem.jtot_object1_code = 'OKX_IB_ITEM'
4362 AND form2.id = inst.cle_id
4363 AND form2.dnz_chr_id = p_chr_id
4364 AND form2.cle_id = p_cle_id;
4365
4366 CURSOR oks_ib_csr (p_instance_id OKC_K_ITEMS.OBJECT1_ID1%TYPE) IS
4367 SELECT DECODE(count(1),0,'N','Y') ib_exists
4368 FROM okc_k_items
4369 WHERE object1_id1 = p_instance_id
4370 AND jtot_object1_code = 'OKX_CUSTPROD';
4371
4372 l_ib_used VARCHAR2(1) := 'N';
4373 l_instance_rec csi_datastructures_pub.instance_rec;
4374 l_txn_rec csi_datastructures_pub.transaction_rec;
4375 x_inst_tbl csi_datastructures_pub.id_tbl;
4376
4377 l_cimv_rec cimv_rec_type;
4378 x_cimv_rec cimv_rec_type;
4379
4380 BEGIN
4381 IF (G_DEBUG_ENABLED = 'Y') THEN
4382 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
4383 END IF;
4384
4385 x_return_status := OKC_API.G_RET_STS_SUCCESS;
4386 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4387 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
4388 END IF;
4389
4390 -- call START_ACTIVITY to create savepoint, check compatibility
4391 -- and initialize message list
4392 x_return_status := OKC_API.START_ACTIVITY(
4393 p_api_name => l_api_name,
4394 p_pkg_name => G_PKG_NAME,
4395 p_init_msg_list => p_init_msg_list,
4396 l_api_version => l_api_version,
4397 p_api_version => p_api_version,
4398 p_api_type => G_API_TYPE,
4399 x_return_status => x_return_status);
4400
4401 -- check if activity started successfully
4402 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
4403 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4404 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
4405 raise OKC_API.G_EXCEPTION_ERROR;
4406 END IF;
4407
4408 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
4409 FOR link_asset_rec IN link_asset_csr(p_okl_chr_id)
4410 LOOP
4411 FOR okl_inst_rec IN inst_csr (p_okl_chr_id,
4412 TO_NUMBER(link_asset_rec.object1_id1))
4413 LOOP
4414 IF (okl_inst_rec.object1_id1 IS NOT NULL) THEN -- If IB instance exists then
4415
4416 --
4417 -- Check whether IB instance is being used
4418 -- by any Service contract
4419 -- If so, don't expire that instance
4420 --
4421 l_ib_used := 'N';
4422 OPEN oks_ib_csr (okl_inst_rec.object1_id1);
4423 FETCH oks_ib_csr INTO l_ib_used;
4424 IF (oks_ib_csr%NOTFOUND) THEN
4425 l_ib_used := 'N';
4426 END IF;
4427 CLOSE oks_ib_csr;
4428
4429 IF (l_ib_used = 'N') THEN -- expire IB instance now
4430
4431 l_instance_rec.instance_id := TO_NUMBER(okl_inst_rec.object1_id1);
4432 l_instance_rec.object_version_number := 1;
4433 l_instance_rec.instance_status_id := NULL;
4434
4435 get_trx_rec(
4436 p_api_version => p_api_version,
4437 p_init_msg_list => p_init_msg_list,
4438 x_return_status => x_return_status,
4439 x_msg_count => x_msg_count,
4440 x_msg_data => x_msg_data,
4441 p_cle_id => okl_inst_rec.cle_id,
4442 p_transaction_type => 'OKL_OTHER',
4443 x_trx_rec => l_txn_rec
4444 );
4445
4446 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
4447 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4448 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
4449 raise OKC_API.G_EXCEPTION_ERROR;
4450 END IF;
4451
4452 csi_item_instance_pub.expire_item_instance(
4453 p_api_version => p_api_version,
4454 p_commit => 'F',
4455 p_init_msg_list => p_init_msg_list,
4456 p_validation_level => fnd_api.g_valid_level_full,
4457 p_instance_rec => l_instance_rec,
4458 p_expire_children => OKL_API.G_FALSE,
4459 p_txn_rec => l_txn_rec,
4460 x_instance_id_lst => x_inst_tbl,
4461 x_return_status => x_return_status,
4462 x_msg_count => x_msg_count,
4463 x_msg_data => x_msg_data
4464 );
4465
4466 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
4467 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4468 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
4469 raise OKC_API.G_EXCEPTION_ERROR;
4470 END IF;
4471
4472 END IF; -- if l_ib_used
4473
4474 l_cimv_rec.id := okl_inst_rec.id;
4475 l_cimv_rec.object1_id1 := NULL; -- Update with IB instance from OKS contract later
4476
4477 okl_okc_migration_pvt.update_contract_item(
4478 p_api_version => p_api_version,
4479 p_init_msg_list => p_init_msg_list,
4480 x_return_status => x_return_status,
4481 x_msg_count => x_msg_count,
4482 x_msg_data => x_msg_data,
4483 p_cimv_rec => l_cimv_rec,
4484 x_cimv_rec => x_cimv_rec
4485 );
4486
4487 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
4488 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4489 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
4490 raise OKC_API.G_EXCEPTION_ERROR;
4491 END IF;
4492
4493 END IF; -- If IB exists
4494
4495 END LOOP; -- okl_inst_csr
4496
4497 END LOOP; -- link_asset_csr
4498
4499 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
4500
4501 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
4502 x_msg_data => x_msg_data);
4503
4504 EXCEPTION
4505
4506 when OKC_API.G_EXCEPTION_ERROR then
4507 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
4508 p_api_name => l_api_name,
4509 p_pkg_name => G_PKG_NAME,
4510 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
4511 x_msg_count => x_msg_count,
4512 x_msg_data => x_msg_data,
4513 p_api_type => G_API_TYPE);
4514
4515 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
4516 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
4517 p_api_name => l_api_name,
4518 p_pkg_name => G_PKG_NAME,
4519 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
4520 x_msg_count => x_msg_count,
4521 x_msg_data => x_msg_data,
4522 p_api_type => G_API_TYPE);
4523
4524 when OTHERS then
4525 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
4526 p_api_name => l_api_name,
4527 p_pkg_name => G_PKG_NAME,
4528 p_exc_name => 'OTHERS',
4529 x_msg_count => x_msg_count,
4530 x_msg_data => x_msg_data,
4531 p_api_type => G_API_TYPE);
4532
4533 END expire_lease_instance;
4534
4535 ------------------------------------------------------------------------------
4536 -- PROCEDURE relink_service_contract
4537 --
4538 -- This procedure is used to link service contract to a BOOKED lease
4539 -- contract.
4540 --
4541 -- Calls:
4542 -- Called By:
4543 ------------------------------------------------------------------------------
4544 PROCEDURE relink_service_contract(
4545 p_api_version IN NUMBER,
4546 p_init_msg_list IN VARCHAR2,
4547 x_return_status OUT NOCOPY VARCHAR2,
4548 x_msg_count OUT NOCOPY NUMBER,
4549 x_msg_data OUT NOCOPY VARCHAR2,
4550 p_okl_chr_id IN OKC_K_HEADERS_V.ID%TYPE, -- Lease Contract Header ID
4551 p_oks_chr_id IN OKC_K_HEADERS_V.ID%TYPE, -- Service Contract Header ID
4552 p_supplier_id IN NUMBER, -- OKL_VENDOR
4553 p_sty_id IN OKL_K_LINES.STY_ID%TYPE DEFAULT NULL, -- Bug 4011710
4554 x_okl_service_line_id OUT NOCOPY OKC_K_LINES_V.ID%TYPE
4555 ) IS
4556
4557 l_api_name VARCHAR2(35) := 'relink_service_contract';
4558 l_proc_name VARCHAR2(35) := 'RELINK_SERVICE_CONTRACT';
4559 l_api_version CONSTANT NUMBER := 1;
4560
4561 BEGIN
4562 IF (G_DEBUG_ENABLED = 'Y') THEN
4563 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
4564 END IF;
4565
4566 x_return_status := OKC_API.G_RET_STS_SUCCESS;
4567 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4568 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
4569 END IF;
4570
4571 -- call START_ACTIVITY to create savepoint, check compatibility
4572 -- and initialize message list
4573 x_return_status := OKC_API.START_ACTIVITY(
4574 p_api_name => l_api_name,
4575 p_pkg_name => G_PKG_NAME,
4576 p_init_msg_list => p_init_msg_list,
4577 l_api_version => l_api_version,
4578 p_api_version => p_api_version,
4579 p_api_type => G_API_TYPE,
4580 x_return_status => x_return_status);
4581
4582 -- check if activity started successfully
4583 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
4584 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4585 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
4586 raise OKC_API.G_EXCEPTION_ERROR;
4587 END IF;
4588
4589 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
4590
4591 okl_service_integration_pvt.create_service_from_oks(
4592 p_api_version => p_api_version,
4593 p_init_msg_list => p_init_msg_list,
4594 x_return_status => x_return_status,
4595 x_msg_count => x_msg_count,
4596 x_msg_data => x_msg_data,
4597 p_okl_chr_id => p_okl_chr_id,
4598 p_oks_chr_id => p_oks_chr_id,
4599 p_supplier_id => p_supplier_id,
4600 p_sty_id => p_sty_id, -- Bug 4011710
4601 x_okl_service_line_id => x_okl_service_line_id
4602 );
4603
4604 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
4605 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4606 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
4607 raise OKC_API.G_EXCEPTION_ERROR;
4608 END IF;
4609
4610 okl_service_integration_pvt.initiate_service_booking(
4611 p_api_version => p_api_version,
4612 p_init_msg_list => p_init_msg_list,
4613 x_return_status => x_return_status,
4614 x_msg_count => x_msg_count,
4615 x_msg_data => x_msg_data,
4616 p_okl_chr_id => p_okl_chr_id
4617 );
4618
4619 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
4620 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4621 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) then
4622 raise OKC_API.G_EXCEPTION_ERROR;
4623 END IF;
4624
4625 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
4626
4627 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
4628 x_msg_data => x_msg_data);
4629
4630 EXCEPTION
4631
4632 when OKC_API.G_EXCEPTION_ERROR then
4633
4634 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
4635 p_api_name => l_api_name,
4636 p_pkg_name => G_PKG_NAME,
4637 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
4638 x_msg_count => x_msg_count,
4639 x_msg_data => x_msg_data,
4640 p_api_type => G_API_TYPE);
4641
4642 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
4643
4644 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
4645 p_api_name => l_api_name,
4646 p_pkg_name => G_PKG_NAME,
4647 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
4648 x_msg_count => x_msg_count,
4649 x_msg_data => x_msg_data,
4650 p_api_type => G_API_TYPE);
4651
4652 when OTHERS then
4653
4654 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
4655 p_api_name => l_api_name,
4656 p_pkg_name => G_PKG_NAME,
4657 p_exc_name => 'OTHERS',
4658 x_msg_count => x_msg_count,
4659 x_msg_data => x_msg_data,
4660 p_api_type => G_API_TYPE);
4661
4662 END relink_service_contract;
4663
4664 END OKL_SERVICE_INTEGRATION_PVT;