[Home] [Help]
PACKAGE BODY: APPS.OKC_CONTRACT_PVT
Source
1 PACKAGE BODY OKC_CONTRACT_PVT AS
2 /* $Header: OKCCCHRB.pls 120.15.12010000.2 2008/10/24 07:59:53 ssreekum ship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 -- subtype control_rec_type is okc_util.okc_control_rec_type;
7
8 ---------------------------------------------------------------------------
9 -- GLOBAL VARIABLES
10 ---------------------------------------------------------------------------
11 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
12 G_NO_PARENT_RECORD CONSTANT VARCHAR2(200) := 'OKC_NO_PARENT_RECORD';
13 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXPECTED_ERROR';
14 G_PARENT_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_PARENT_TABLE_TOKEN;
15 G_CHILD_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_CHILD_TABLE_TOKEN;
16 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
17 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
18 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_REQUIRED_VALUE;
19 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_INVALID_VALUE;
20 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_COL_NAME_TOKEN;
21 G_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_CHILD_TABLE_TOKEN;
22 G_EXCEPTION_HALT_VALIDATION exception;
23 NO_CONTRACT_FOUND exception;
24 G_NO_UPDATE_ALLOWED_EXCEPTION exception;
25 G_NO_UPDATE_ALLOWED CONSTANT VARCHAR2(200) := 'OKC_NO_UPDATE_ALLOWED';
26 G_EXCEPTION_HALT_PROCESS exception;
27 G_CREATE_NA_EXCEPTION exception;
28 ---------------------------------------------------------------------------
29 -- /striping/
30 p_rule_code OKC_RULE_DEFS_B.rule_code%TYPE;
31 p_appl_id OKC_RULE_DEFS_B.application_id%TYPE;
32 p_dff_name OKC_RULE_DEFS_B.descriptive_flexfield_name%TYPE;
33
34 -- Start of comments
35 -- BUG#4066428 HKAMDAR 29-Dec-2004 Part 1
36 -- Procedure Name : is_rule_allowed
37 -- Description : Checks if rules are allowed for contracts class,
38 -- Business Rules :
39 -- Version : 1.0
40 -- End of comments
41
42 Procedure is_rule_allowed(p_id number,
43 p_object varchar2,
44 x_return out NOCOPY varchar2,
45 x_rule_meaning out NOCOPY varchar2) IS
46
47 cursor cur_k_appl_id is
48 Select application_id
49 from okc_k_headers_b
50 where id = p_id;
51
52 k_appl_id number;
53
54 Begin
55
56 Open cur_k_appl_id;
57 Fetch cur_k_appl_id into k_appl_id;
58 Close cur_k_appl_id;
59
60 --For OKS ks no rule/rule group allowed
61 If k_appl_id =515 Then
62 x_return :='N';
63 Else
64 x_return := 'Y';
65 End If;
66
67 End Is_rule_allowed;
68 -- BUG#4066428 End Part 1
69 --
70 -- Function to check if a workflow is active for a contract
71 -- Function Name: Is_Process_Active
72 -- An item is considered active if its end_date is NULL
73 --
74 FUNCTION Is_Process_Active(p_chr_id IN NUMBER) RETURN VARCHAR2 Is
75
76 l_wf_name OKC_PROCESS_DEFS_B.WF_NAME%TYPE;
77 l_item_key OKC_K_PROCESSES.PROCESS_ID%TYPE;
78 l_return_code VARCHAR2(1) := 'N';
79 l_end_date DATE;
80
81 -- cursor for item type and item key
82 Cursor l_pdfv_csr Is
83 SELECT pdfv.wf_name, cpsv.process_id
84 FROM okc_process_defs_b pdfv,
85 okc_k_processes cpsv
86 WHERE pdfv.id = cpsv.pdf_id
87 AND cpsv.chr_id = p_chr_id;
88
89 -- cursor to check active process
90 Cursor l_wfitems_csr Is
91 SELECT end_date
92 FROM wf_items
93 WHERE item_type = l_wf_name
94 AND item_key = l_item_key;
95 BEGIN
96
97 -- get item type and item key
98 Open l_pdfv_csr;
99 Fetch l_pdfv_csr into l_wf_name, l_item_key;
100 If (l_pdfv_csr%NOTFOUND or l_wf_name IS NULL or l_item_key IS NULL) Then
101 Close l_pdfv_csr;
102 return l_return_code;
103 End If;
104 Close l_pdfv_csr;
105
106 -- check whether process is active or not
107 Open l_wfitems_csr;
108 Fetch l_wfitems_csr into l_end_date;
109 If (l_wfitems_csr%NOTFOUND or l_end_date IS NOT NULL) Then
110 l_return_code := 'N';
111 Else
112 l_return_code := 'Y';
113 End If;
114 Close l_wfitems_csr;
115
116 return l_return_code;
117 exception
118 when NO_DATA_FOUND then
119 return (l_return_code);
120 END Is_Process_Active;
121
122 ----------------------------------------------------------------------------------------------
123 ---Funtion Returns 'Y' if user has update access on a category
124 ----------------------------------------------------------------------------------------------
125 FUNCTION Validate_Access_level(p_scs_code IN VARCHAR2) RETURN VARCHAR2 IS
126
127 Cursor Access_Level_csr is
128 Select access_level
129 from okc_subclass_resps_v
130 where scs_code=p_scs_code
131 and resp_id=fnd_global.resp_id
132 and sysdate between start_date and nvl(end_date,sysdate);
133
134 l_access_level VARCHAR2(1);
135 l_return_val VARCHAR2(1) := 'N' ;
136
137 BEGIN
138
139 Open Access_Level_csr;
140 Fetch Access_Level_csr into l_access_level;
141 Close Access_Level_csr;
142
143 If l_access_level = 'U' then
144 l_return_val := 'Y';
145 End If;
146 return (l_return_val);
147
148 EXCEPTION
149 WHEN NO_DATA_FOUND then
150 return(l_return_val);
151
152 END Validate_Access_Level;
153
154
155 PROCEDURE GENERATE_CONTRACT_NUMBER(
156 p_scs_code IN VARCHAR2,
157 p_modifier IN VARCHAR2,
158 x_return_status OUT NOCOPY VARCHAR2,
159 x_contract_number IN OUT NOCOPY OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE) Is
160
161 l_unique_number_found BOOLEAN := FALSE;
162 l_chrv_rec OKC_CHR_PVT.chrv_rec_type;
163 l_return_status VARCHAR2(1);
164
165 BEGIN
166 IF (l_debug = 'Y') THEN
167 okc_debug.Set_Indentation('Generate Contract Number');
168 okc_debug.log('1000: Entering generate contract number');
169 END IF;
170 -- initialize return status
171 x_return_status := OKC_API.G_RET_STS_SUCCESS;
172
173 -- if contract number is null, polpulate default contract number
174 If (x_contract_number = OKC_API.G_MISS_CHAR or
175 x_contract_number is null)
176 Then
177 IF (l_debug = 'Y') THEN
178 okc_debug.log('1010: x_contract_Number is null');
179 END IF;
180 -- Loop till a unique contract number + modifier found
181 -- WHILE (NOT l_unique_number_found)
182 -- LOOP
183 IF (l_debug = 'Y') THEN
184 okc_debug.log('1020: Before Get_K_Number');
185 END IF;
186 OKC_CONTRACT_SEQ_PUB.GET_K_NUMBER (
187 p_scs_code => p_scs_code,
188 p_contract_number_modifier => p_modifier,
189 x_contract_number => x_contract_number,
190 x_return_status => l_return_status );
191 IF (l_debug = 'Y') THEN
192 okc_debug.log('1030: After Get_K_Number');
193 okc_debug.log('1040: x_contract_number ' || x_contract_number);
194 END IF;
195
196 x_return_status := l_return_status;
197 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
198 Raise G_EXCEPTION_HALT_VALIDATION;
199 End If;
200
201 -- check for unique contract_number + modifier
202 /* l_chrv_rec.contract_number := x_contract_number;
203 l_chrv_rec.contract_number_modifier := p_modifier;
204
205 l_return_status := OKC_CHR_PVT.Is_Unique(l_chrv_rec);
206 If (l_return_status = OKC_API.G_RET_STS_SUCCESS) Then
207 l_unique_number_found := TRUE;
208 End If;
209 x_return_status := l_return_status;
210 END LOOP; */
211 End If;
212 IF (l_debug = 'Y') THEN
213 okc_debug.log('1050: Exiting Generate Contract Number');
214 okc_debug.reset_indentation;
215 END IF;
216 exception
217 when G_EXCEPTION_HALT_VALIDATION then
218 IF (l_debug = 'Y') THEN
219 okc_debug.log('1060: Exiting Generate Contract Number');
220 okc_debug.reset_indentation;
221 END IF;
222 when OTHERS then
223 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
224 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
225 p_msg_name => g_unexpected_error,
226 p_token1 => g_sqlcode_token,
227 p_token1_value => sqlcode,
228 p_token2 => g_sqlerrm_token,
229 p_token2_value => sqlerrm);
230 IF (l_debug = 'Y') THEN
231 okc_debug.log('1070: Exiting Generate Contract Number');
232 okc_debug.reset_indentation;
233 END IF;
234 END GENERATE_CONTRACT_NUMBER;
235
236
237 FUNCTION Update_Minor_Version(p_chr_id IN NUMBER) RETURN VARCHAR2 Is
238 l_api_version NUMBER := 1;
239 l_init_msg_list VARCHAR2(1) := 'F';
240 x_return_status VARCHAR2(1);
241 x_msg_count NUMBER;
242 x_msg_data VARCHAR2(2000);
243 x_out_rec OKC_CVM_PVT.cvmv_rec_type;
244 l_cvmv_rec OKC_CVM_PVT.cvmv_rec_type;
245 BEGIN
246
247 -- initialize return status
248 x_return_status := OKC_API.G_RET_STS_SUCCESS;
249
250 -- assign/populate contract header id
251 l_cvmv_rec.chr_id := p_chr_id;
252
253 OKC_CVM_PVT.update_contract_version(
254 p_api_version => l_api_version,
255 p_init_msg_list => l_init_msg_list,
256 x_return_status => x_return_status,
257 x_msg_count => x_msg_count,
258 x_msg_data => x_msg_data,
259 p_cvmv_rec => l_cvmv_rec,
260 x_cvmv_rec => x_out_rec);
261
262 -- Error handling....
263 -- calls OTHERS exception
264 return (x_return_status);
265 EXCEPTION
266 when OTHERS then
267 -- notify caller of an error
268 x_return_status := OKC_API.G_RET_STS_ERROR;
269
270 -- store SQL error message on message stack
271 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
272 p_msg_name => g_unexpected_error,
273 p_token1 => g_sqlcode_token,
274 p_token1_value => sqlcode,
275 p_token2 => g_sqlerrm_token,
276 p_token2_value => sqlerrm);
277
278 return (x_return_status);
279 END;
280
281 -------------------------------------------------------------------
282 -- This procedure overrides the trasaction-id check
283 -- Use this only to increase the minor version by force
284 -- called in authoring form when coming back from rule editor
285 -- This function directly calls the Update_Row for version table
286 ------------------------------------------------------------------
287 FUNCTION Increment_Minor_Version(p_chr_id IN NUMBER) RETURN VARCHAR2 IS
288
289 p_api_version NUMBER := 1;
290 p_init_msg_list VARCHAR2(1) := 'F';
291 x_return_status VARCHAR2(1);
292 x_msg_count NUMBER;
293 x_msg_data VARCHAR2(2000);
294 x_cvmv_rec OKC_CVM_PVT.cvmv_rec_type;
295
296 l_cvmv_rec OKC_CVM_PVT.cvmv_rec_type;
297 BEGIN
298
299 x_return_status := OKC_API.G_RET_STS_SUCCESS;
300
301 l_cvmv_rec.chr_id := p_chr_id;
302
303 -- Major version update is not allowed
304 l_cvmv_rec.major_version := OKC_API.G_MISS_NUM;
305
306 -- update contract version number
307 OKC_CVM_PVT.update_row(
308 p_api_version,
309 p_init_msg_list,
310 x_return_status,
311 x_msg_count,
312 x_msg_data,
313 l_cvmv_rec,
314 x_cvmv_rec);
315
316 -- assign current trasaction id to global_trans_id
317 OKC_CVM_PVT.g_trans_id := dbms_transaction.local_transaction_id;
318 return x_return_status;
319 EXCEPTION
320 WHEN OTHERS THEN
321 -- store SQL error message on message stack
322 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
323 p_msg_name => g_unexpected_error,
324 p_token1 => g_sqlcode_token,
325 p_token1_value => sqlcode,
326 p_token2 => g_sqlerrm_token,
327 p_token2_value => sqlerrm);
328
329 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
330 return x_return_status;
331 END Increment_Minor_Version;
332
333 FUNCTION Create_Version(p_chr_id IN NUMBER) RETURN VARCHAR2 Is
334 l_api_version NUMBER := 1;
335 l_init_msg_list VARCHAR2(1) := 'F';
336 x_return_status VARCHAR2(1);
337 x_msg_count NUMBER;
338 x_msg_data VARCHAR2(2000);
339 x_out_rec OKC_CVM_PVT.cvmv_rec_type;
340 l_cvmv_rec OKC_CVM_PVT.cvmv_rec_type;
341 BEGIN
342
343 -- initialize return status
344 x_return_status := OKC_API.G_RET_STS_SUCCESS;
345
346 -- assign/populate contract header id
347 l_cvmv_rec.chr_id := p_chr_id;
348
349 OKC_CVM_PVT.create_contract_version(
350 p_api_version => l_api_version,
351 p_init_msg_list => l_init_msg_list,
352 x_return_status => x_return_status,
353 x_msg_count => x_msg_count,
354 x_msg_data => x_msg_data,
355 p_cvmv_rec => l_cvmv_rec,
356 x_cvmv_rec => x_out_rec);
357
358 -- Error handling....
359 -- calls OTHERS exception
360 return (x_return_status);
361 EXCEPTION
362 when OTHERS then
363 -- notify caller of an error
364 x_return_status := OKC_API.G_RET_STS_ERROR;
365
366 -- store SQL error message on message stack
367 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
368 p_msg_name => g_unexpected_error,
369 p_token1 => g_sqlcode_token,
370 p_token1_value => sqlcode,
371 p_token2 => g_sqlerrm_token,
372 p_token2_value => sqlerrm);
373
374 return (x_return_status);
375 END;
376
377 FUNCTION Delete_Version(p_chr_id IN NUMBER) RETURN VARCHAR2 Is
378 l_api_version NUMBER := 1;
379 l_init_msg_list VARCHAR2(1) := 'F';
380 x_return_status VARCHAR2(1);
381 x_msg_count NUMBER;
382 x_msg_data VARCHAR2(2000);
383 l_cvmv_rec OKC_CVM_PVT.cvmv_rec_type;
384 BEGIN
385
386 -- initialize return status
387 x_return_status := OKC_API.G_RET_STS_SUCCESS;
388
389 -- assign/populate contract header id
390 l_cvmv_rec.chr_id := p_chr_id;
391
392 OKC_CVM_PVT.delete_contract_version(
393 p_api_version => l_api_version,
394 p_init_msg_list => l_init_msg_list,
395 x_return_status => x_return_status,
396 x_msg_count => x_msg_count,
397 x_msg_data => x_msg_data,
398 p_cvmv_rec => l_cvmv_rec);
399
400 -- Error handling....
401 -- calls OTHERS exception
402 return (x_return_status);
403 EXCEPTION
404 when OTHERS then
405 -- notify caller of an error
406 x_return_status := OKC_API.G_RET_STS_ERROR;
407
408 -- store SQL error message on message stack
409 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
410 p_msg_name => g_unexpected_error,
411 p_token1 => g_sqlcode_token,
412 p_token1_value => sqlcode,
413 p_token2 => g_sqlerrm_token,
414 p_token2_value => sqlerrm);
415 return (x_return_status);
416
417 END;
418
419 PROCEDURE create_contract_header(
420 p_api_version IN NUMBER,
421 p_init_msg_list IN VARCHAR2 ,
422 x_return_status OUT NOCOPY VARCHAR2,
423 x_msg_count OUT NOCOPY NUMBER,
424 x_msg_data OUT NOCOPY VARCHAR2,
425 p_chrv_rec IN OKC_CHR_PVT.chrv_rec_type,
426 x_chrv_rec OUT NOCOPY OKC_CHR_PVT.chrv_rec_type,
427 p_check_access IN VARCHAR2 ) IS
428
429 l_chrv_rec OKC_CHR_PVT.chrv_rec_type := p_chrv_rec;
430 Cursor subclass_csr is
431 Select meaning
432 from okc_subclasses_v
433 where code=p_chrv_rec.scs_code;
434 l_scs_meaning VARCHAR2(30);
435 l_hstv_rec OKC_K_HISTORY_PVT.hstv_rec_type;
436 x_hstv_rec OKC_K_HISTORY_PVT.hstv_rec_type;
437 l_version VARCHAR2(255);
438
439 CURSOR version_csr(p_chr_id NUMBER) IS
440 SELECT to_char (major_version)||'.'||to_char(minor_version)
441 FROM okc_k_vers_numbers
442 WHERE chr_id=p_chr_id;
443
444 BEGIN
445 -- initialize return status
446 x_return_status := OKC_API.G_RET_STS_SUCCESS;
447
448 --if p_check_access is 'Y' , check if user has update access on category
449
450 If (p_check_access = 'Y' AND
451 validate_access_level(p_chrv_rec.scs_code)<> 'Y')
452 Then
453 Open Subclass_csr;
454 Fetch subclass_csr into l_scs_meaning;
455 Close subclass_csr;
456 RAISE G_CREATE_NA_EXCEPTION;
457 End If;
458
459 -- if contract number is null, polpulate default contract number
460 If (l_chrv_rec.contract_number = OKC_API.G_MISS_CHAR or
461 l_chrv_rec.contract_number is null)
462 Then
463 OKC_CONTRACT_PVT.GENERATE_CONTRACT_NUMBER(
464 p_scs_code => l_chrv_rec.scs_code,
465 p_modifier => l_chrv_rec.contract_number_modifier,
466 x_return_status => x_return_status,
467 x_contract_number => l_chrv_rec.contract_number);
468
469 /* If (x_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
470 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
471 p_msg_name => g_unexpected_error,
472 p_token1 => g_sqlcode_token,
473 p_token1_value => sqlcode,
474 p_token2 => g_sqlerrm_token,
475 p_token2_value => sqlerrm);
476 End If; */
477 End If;
478
479 If (x_return_status = OKC_API.G_RET_STS_SUCCESS) Then
480
481 OKC_CHR_PVT.Insert_Row(
482 p_api_version => p_api_version,
483 p_init_msg_list => p_init_msg_list,
484 x_return_status => x_return_status,
485 x_msg_count => x_msg_count,
486 x_msg_data => x_msg_data,
487 p_chrv_rec => l_chrv_rec,
488 x_chrv_rec => x_chrv_rec);
489 End If;
490
491 -- Create version
492 If (x_return_status = OKC_API.G_RET_STS_SUCCESS) Then
493 x_return_status := OKC_CONTRACT_PVT.Create_Version(x_chrv_rec.id);
494 End If;
495
496 -- Create record in OKC_K_HISTORY tables
497 If (x_return_status = OKC_API.G_RET_STS_SUCCESS) Then
498 l_hstv_rec.chr_id := x_chrv_rec.id;
499 l_hstv_rec.sts_code_from := NULL;
500 l_hstv_rec.sts_code_to := x_chrv_rec.sts_code;
501 l_hstv_rec.reason_code := 'CREATE';
502 l_hstv_rec.opn_code := 'STS_CHG';
503 l_hstv_rec.manual_yn := 'N';
504
505 open version_csr(x_chrv_rec.id);
506 fetch version_csr into l_version;
507 close version_csr;
508
509 l_hstv_rec.contract_version := l_version;
510
511 OKC_K_HISTORY_PUB.create_k_history(
512 p_api_version => p_api_version,
513 p_init_msg_list => p_init_msg_list,
514 x_return_status => x_return_status,
515 x_msg_count => x_msg_count,
516 x_msg_data => x_msg_data,
517 p_hstv_rec => l_hstv_rec,
518 x_hstv_rec => x_hstv_rec);
519 End If;
520
521 EXCEPTION
522 When G_CREATE_NA_EXCEPTION Then
523 OKC_API.SET_MESSAGE(p_app_name => 'OKC',
524 p_msg_name => 'OKC_CREATE_NA',
525 p_token1 => 'CATEGORY',
526 p_token1_value => l_scs_meaning);
527 x_return_status:=OKC_API.G_RET_STS_ERROR;
528
529 END create_contract_header;
530
531 PROCEDURE create_contract_header(
532 p_api_version IN NUMBER,
533 p_init_msg_list IN VARCHAR2 ,
534 x_return_status OUT NOCOPY VARCHAR2,
535 x_msg_count OUT NOCOPY NUMBER,
536 x_msg_data OUT NOCOPY VARCHAR2,
537 p_chrv_tbl IN OKC_CHR_PVT.chrv_tbl_type,
538 x_chrv_tbl OUT NOCOPY OKC_CHR_PVT.chrv_tbl_type,
539 p_check_access IN VARCHAR2 ) IS
540
541 BEGIN
542 OKC_CHR_PVT.Insert_Row(
543 p_api_version => p_api_version,
544 p_init_msg_list => p_init_msg_list,
545 x_return_status => x_return_status,
546 x_msg_count => x_msg_count,
547 x_msg_data => x_msg_data,
548 p_chrv_tbl => p_chrv_tbl,
549 x_chrv_tbl => x_chrv_tbl);
550 END create_contract_header;
551
552
553 PROCEDURE update_contract_header(
554 p_api_version IN NUMBER,
555 p_init_msg_list IN VARCHAR2 ,
556 x_return_status OUT NOCOPY VARCHAR2,
557 x_msg_count OUT NOCOPY NUMBER,
558 x_msg_data OUT NOCOPY VARCHAR2,
559 p_restricted_update IN VARCHAR2 ,
560 p_chrv_rec IN OKC_CHR_PVT.chrv_rec_type,
561 x_chrv_rec OUT NOCOPY OKC_CHR_PVT.chrv_rec_type) IS
562
563 l_currency_code VARCHAR2(5);
564 l_hstv_rec OKC_K_HISTORY_PVT.hstv_rec_type;
565 x_hstv_rec OKC_K_HISTORY_PVT.hstv_rec_type;
566 l_version VARCHAR2(255);
567 l_status VARCHAR2(30) := NULL;
568 l_new_status VARCHAR2(30) := NULL;
569
570
571 Cursor l_chrv_csr Is
572 SELECT currency_code
573 --npalepu 26-10-2005 modified for bug # 4691662.
574 --Replaced table okc_k_headers_b with headers_All_b table
575 /* FROM okc_k_headers_b */
576 FROM okc_k_headers_all_b
577 --end npalepu
578 WHERE ID = p_chrv_rec.id;
579
580 CURSOR version_csr(p_chr_id NUMBER) IS
581 SELECT to_char (major_version)||'.'||to_char(minor_version)
582 FROM okc_k_vers_numbers
583 WHERE chr_id=p_chr_id;
584
585 Cursor l_status_csr Is
586 SELECT sts_code
587 --npalepu 26-10-2005 modified for bug # 4691662.
588 --Replaced table okc_k_headers_b with headers_All_b table
589 /* FROM okc_k_headers_b */
590 FROM okc_k_headers_all_b
591 --end npalepu
592 WHERE ID = p_chrv_rec.id;
593
594 BEGIN
595 -- if the update is not a restricted update (restricted_update <> 'Y'),
596 -- check whether update is allowed or not
597 If (p_restricted_update <> OKC_API.G_TRUE) Then
598 If (OKC_CONTRACT_PUB.Update_Allowed(p_chrv_rec.id) <> 'Y') Then
599 raise G_NO_UPDATE_ALLOWED_EXCEPTION;
600 End If;
601 End If;
602
603 -- If currency code is changed in header, all line's currency
604 -- codes are to be changed. So retrieve and remember the old currency code.
605 open l_chrv_csr;
606 fetch l_chrv_csr into l_currency_code;
607 close l_chrv_csr;
608
609 -- To get the old status
610 open l_status_csr;
611 fetch l_status_csr into l_status;
612 close l_status_csr;
613
614 If (p_chrv_rec.sts_code = OKC_API.G_MISS_CHAR) Then
615 l_new_status := NULL;
616 Else
617 l_new_status := p_chrv_rec.sts_code;
618 End If;
619
620 OKC_CHR_PVT.Update_Row(
621 p_api_version => p_api_version,
622 p_init_msg_list => p_init_msg_list,
623 x_return_status => x_return_status,
624 x_msg_count => x_msg_count,
625 x_msg_data => x_msg_data,
626 p_restricted_update => p_restricted_update,
627 p_chrv_rec => p_chrv_rec,
628 x_chrv_rec => x_chrv_rec);
629
630 -- Update minor version
631 /*fix for bug6688656*/
632 If x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
633 x_return_status := Update_Minor_Version(p_chrv_rec.id);
634 End if;
635 /*end of fix for bug6688656*/
636
637 -- Call action assembler if status is changed
638 If (x_return_status = OKC_API.G_RET_STS_SUCCESS AND
639 p_chrv_rec.old_sts_code is not null AND
640 p_chrv_rec.new_sts_code is not null AND
641 p_chrv_rec.old_ste_code is not null AND
642 p_chrv_rec.new_ste_code is not null AND
643 (p_chrv_rec.old_sts_code <> p_chrv_rec.new_sts_code OR
644 p_chrv_rec.old_ste_code <> p_chrv_rec.new_ste_code
645 )
646 )
647 Then
648 OKC_K_STS_CHG_ASMBLR_PVT.Acn_Assemble(
649 p_api_version => p_api_version,
650 p_init_msg_list => p_init_msg_list,
651 x_return_status => x_return_status,
652 x_msg_count => x_msg_count,
653 x_msg_data => x_msg_data,
654 p_k_id => x_chrv_rec.id,
655 p_k_number => x_chrv_rec.contract_number,
656 p_k_nbr_mod => x_chrv_rec.contract_number_modifier,
657 p_k_cur_sts_code => p_chrv_rec.new_sts_code,
658 p_k_cur_sts_type => p_chrv_rec.new_ste_code,
659 p_k_pre_sts_code => p_chrv_rec.old_sts_code,
660 p_k_pre_sts_type => p_chrv_rec.old_ste_code,
661 p_k_source_system_code => p_chrv_rec.ORIG_SYSTEM_SOURCE_CODE);
662 Else
663 If ( ( (x_return_status = OKC_API.G_RET_STS_SUCCESS) AND (l_new_status is not null) AND (l_status is not null) )
664 AND (l_status <> l_new_status) ) Then
665
666 -- To insert record in history tables
667 l_hstv_rec.chr_id := x_chrv_rec.id;
668 l_hstv_rec.sts_code_from := l_status;
669 l_hstv_rec.sts_code_to := l_new_status;
670 l_hstv_rec.opn_code := 'STS_CHG';
671
672 open version_csr(x_chrv_rec.id);
673 fetch version_csr into l_version;
674 close version_csr;
675
676 l_hstv_rec.contract_version := l_version;
677
678 OKC_K_HISTORY_PUB.create_k_history(
679 p_api_version => p_api_version,
680 p_init_msg_list => p_init_msg_list,
681 x_return_status => x_return_status,
682 x_msg_count => x_msg_count,
683 x_msg_data => x_msg_data,
684 p_hstv_rec => l_hstv_rec,
685 x_hstv_rec => x_hstv_rec);
686 End If;
687 End If;
688
689 -- Update Currency change
690 If (x_return_status = OKC_API.G_RET_STS_SUCCESS) Then
691 -- If currency code changed, update all lines
692 If (x_chrv_rec.currency_code <> l_currency_code) Then
693 UPDATE okc_k_lines_b
694 SET currency_code = x_chrv_rec.currency_code
695 WHERE dnz_chr_id = x_chrv_rec.id;
696 End If;
697 /*commented and written above for bug6688656
698 x_return_status := Update_Minor_Version(p_chrv_rec.id);
699 */
700 End If;
701 exception
702 when G_NO_UPDATE_ALLOWED_EXCEPTION then
703 x_return_status := OKC_API.G_RET_STS_ERROR;
704 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
705 p_msg_name => g_no_update_allowed,
706 p_token1 => 'VALUE1',
707 p_token1_value => 'Contract Header');
708 when OTHERS then
709 -- store SQL error message on message stack
710 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
711 p_msg_name => g_unexpected_error,
712 p_token1 => g_sqlcode_token,
713 p_token1_value => sqlcode,
714 p_token2 => g_sqlerrm_token,
715 p_token2_value => sqlerrm);
716
717 -- notify caller of an UNEXPETED error
718 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
719 END update_contract_header;
720
721
722 PROCEDURE update_contract_header(
723 p_api_version IN NUMBER,
724 p_init_msg_list IN VARCHAR2 ,
725 x_return_status OUT NOCOPY VARCHAR2,
726 x_msg_count OUT NOCOPY NUMBER,
727 x_msg_data OUT NOCOPY VARCHAR2,
728 p_restricted_update IN VARCHAR2 ,
729 p_chrv_tbl IN OKC_CHR_PVT.chrv_tbl_type,
730 x_chrv_tbl OUT NOCOPY OKC_CHR_PVT.chrv_tbl_type) IS
731
732 BEGIN
733 OKC_CHR_PVT.Update_Row(
734 p_api_version => p_api_version,
735 p_init_msg_list => p_init_msg_list,
736 x_return_status => x_return_status,
737 x_msg_count => x_msg_count,
738 x_msg_data => x_msg_data,
739 p_restricted_update => p_restricted_update,
740 p_chrv_tbl => p_chrv_tbl,
741 x_chrv_tbl => x_chrv_tbl);
742 END update_contract_header;
743
744 PROCEDURE update_contract_header(
745 p_api_version IN NUMBER,
746 p_init_msg_list IN VARCHAR2 ,
747 x_return_status OUT NOCOPY VARCHAR2,
748 x_msg_count OUT NOCOPY NUMBER,
749 x_msg_data OUT NOCOPY VARCHAR2,
750 p_restricted_update IN VARCHAR2 ,
751 p_chrv_rec IN OKC_CHR_PVT.chrv_rec_type,
752 p_control_rec IN control_rec_type,
753 x_chrv_rec OUT NOCOPY OKC_CHR_PVT.chrv_rec_type) IS
754
755 l_currency_code VARCHAR2(5);
756 l_hstv_rec OKC_K_HISTORY_PVT.hstv_rec_type;
757 x_hstv_rec OKC_K_HISTORY_PVT.hstv_rec_type;
758 l_version VARCHAR2(255);
759 l_status VARCHAR2(30) := NULL;
760 l_new_status VARCHAR2(30) := NULL;
761
762
763 Cursor l_chrv_csr Is
764 SELECT currency_code
765 --npalepu 26-10-2005 modified for bug # 4691662.
766 --Replaced table okc_k_headers_b with headers_All_b table
767 /* FROM okc_k_headers_b */
768 FROM okc_k_headers_all_b
769 --end npalepu
770 WHERE ID = p_chrv_rec.id;
771
772 CURSOR version_csr(p_chr_id NUMBER) IS
773 SELECT to_char (major_version)||'.'||to_char(minor_version)
774 FROM okc_k_vers_numbers
775 WHERE chr_id=p_chr_id;
776
777 Cursor l_status_csr Is
778 SELECT sts_code
779 --npalepu 26-10-2005 modified for bug # 4691662.
780 --Replaced table okc_k_headers_b with headers_All_b table
781 /* FROM okc_k_headers_b */
782 FROM okc_k_headers_all_b
783 --end npalepu
784 WHERE ID = p_chrv_rec.id;
785
786 BEGIN
787 -- if the update is not a restricted update (restricted_update <> 'Y'),
788 -- check whether update is allowed or not
789 If (p_restricted_update <> OKC_API.G_TRUE) Then
790 If (OKC_CONTRACT_PUB.Update_Allowed(p_chrv_rec.id) <> 'Y') Then
791 raise G_NO_UPDATE_ALLOWED_EXCEPTION;
792 End If;
793 End If;
794
795 -- If currency code is changed in header, all line's currency
796 -- codes are to be changed. So retrieve and remember the old currency code.
797 open l_chrv_csr;
798 fetch l_chrv_csr into l_currency_code;
799 close l_chrv_csr;
800
801 -- To get the old status
802 open l_status_csr;
803 fetch l_status_csr into l_status;
804 close l_status_csr;
805
806 If (p_chrv_rec.sts_code = OKC_API.G_MISS_CHAR) Then
807 l_new_status := NULL;
808 Else
809 l_new_status := p_chrv_rec.sts_code;
810 End If;
811
812 OKC_CHR_PVT.Update_Row(
813 p_api_version => p_api_version,
814 p_init_msg_list => p_init_msg_list,
815 x_return_status => x_return_status,
816 x_msg_count => x_msg_count,
817 x_msg_data => x_msg_data,
818 p_restricted_update => p_restricted_update,
819 p_chrv_rec => p_chrv_rec,
820 x_chrv_rec => x_chrv_rec);
821
822 -- Update minor version
823 /*fix for bug6688656*/
824 If x_return_status = OKC_API.G_RET_STS_SUCCESS THEN
825 x_return_status := Update_Minor_Version(p_chrv_rec.id);
826 End if;
827 /*end of fix for bug6688656*/
828
829 -- Call action assembler if status is changed
830 If (x_return_status = OKC_API.G_RET_STS_SUCCESS AND
831 p_chrv_rec.old_sts_code is not null AND
832 p_chrv_rec.new_sts_code is not null AND
833 p_chrv_rec.old_ste_code is not null AND
834 p_chrv_rec.new_ste_code is not null AND
835 (p_chrv_rec.old_sts_code <> p_chrv_rec.new_sts_code OR
836 p_chrv_rec.old_ste_code <> p_chrv_rec.new_ste_code
837 )
838 )
839 Then
840 OKC_K_STS_CHG_ASMBLR_PVT.Acn_Assemble(
841 p_api_version => p_api_version,
842 p_init_msg_list => p_init_msg_list,
843 x_return_status => x_return_status,
844 x_msg_count => x_msg_count,
845 x_msg_data => x_msg_data,
846 p_k_id => x_chrv_rec.id,
847 p_k_number => x_chrv_rec.contract_number,
848 p_k_nbr_mod => x_chrv_rec.contract_number_modifier,
849 p_k_cur_sts_code => p_chrv_rec.new_sts_code,
850 p_k_cur_sts_type => p_chrv_rec.new_ste_code,
851 p_k_pre_sts_code => p_chrv_rec.old_sts_code,
852 p_k_pre_sts_type => p_chrv_rec.old_ste_code,
853 p_k_source_system_code => p_chrv_rec.ORIG_SYSTEM_SOURCE_CODE,
854 p_control_rec => p_control_rec);
855 Else
856 If ( ( (x_return_status = OKC_API.G_RET_STS_SUCCESS) AND (l_new_status is not null) AND (l_status is not null) )
857 AND (l_status <> l_new_status) ) Then
858
859 -- To insert record in history tables
860 l_hstv_rec.chr_id := x_chrv_rec.id;
861 l_hstv_rec.sts_code_from := l_status;
862 l_hstv_rec.sts_code_to := l_new_status;
863 l_hstv_rec.opn_code := 'STS_CHG';
864
865 open version_csr(x_chrv_rec.id);
866 fetch version_csr into l_version;
867 close version_csr;
868
869 l_hstv_rec.contract_version := l_version;
870
871 OKC_K_HISTORY_PUB.create_k_history(
872 p_api_version => p_api_version,
873 p_init_msg_list => p_init_msg_list,
874 x_return_status => x_return_status,
875 x_msg_count => x_msg_count,
876 x_msg_data => x_msg_data,
877 p_hstv_rec => l_hstv_rec,
878 x_hstv_rec => x_hstv_rec);
879 End If;
880 End If;
881
882 -- Update Currency change
883 If (x_return_status = OKC_API.G_RET_STS_SUCCESS) Then
884 -- If currency code changed, update all lines
885 If (x_chrv_rec.currency_code <> l_currency_code) Then
886 UPDATE okc_k_lines_b
887 SET currency_code = x_chrv_rec.currency_code
888 WHERE dnz_chr_id = x_chrv_rec.id;
889 End If;
890 /*commented and written above for the bug6688656
891 x_return_status := Update_Minor_Version(p_chrv_rec.id);
892 */
893 End If;
894 exception
895 when G_NO_UPDATE_ALLOWED_EXCEPTION then
896 x_return_status := OKC_API.G_RET_STS_ERROR;
897 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
898 p_msg_name => g_no_update_allowed,
899 p_token1 => 'VALUE1',
900 p_token1_value => 'Contract Header');
901 when OTHERS then
902 -- store SQL error message on message stack
903 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
904 p_msg_name => g_unexpected_error,
905 p_token1 => g_sqlcode_token,
906 p_token1_value => sqlcode,
907 p_token2 => g_sqlerrm_token,
908 p_token2_value => sqlerrm);
909
910 -- notify caller of an UNEXPETED error
911 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
912 END update_contract_header;
913
914 PROCEDURE delete_contract_header(
915 p_api_version IN NUMBER,
916 p_init_msg_list IN VARCHAR2 ,
917 x_return_status OUT NOCOPY VARCHAR2,
918 x_msg_count OUT NOCOPY NUMBER,
919 x_msg_data OUT NOCOPY VARCHAR2,
920 p_chrv_rec IN OKC_CHR_PVT.chrv_rec_type) IS
921
922 l_dummy_val NUMBER;
923 l_major_version FND_ATTACHED_DOCUMENTS.PK2_VALUE%TYPE;
924
925 Cursor l_clev_csr Is
926 select count(*)
927 from OKC_K_LINES_B
928 where chr_id = p_chrv_rec.id;
929
930 Cursor l_crjv_csr Is
931 SELECT id, object_version_number
932 FROM OKC_K_REL_OBJS
933 WHERE chr_id = p_chrv_rec.id;
934
935 Cursor l_cvm_csr Is
936 SELECT to_char(major_version)
937 FROM okc_k_vers_numbers
938 WHERE chr_id = p_chrv_rec.id;
939
940 Cursor oie_csr Is
941 SELECT id
942 FROM okc_operation_instances
943 WHERE target_chr_id = p_chrv_rec.id;
944
945 Cursor l_scrv_csr Is --sales credit
946 SELECT id, object_version_number, dnz_chr_id
947 FROM okc_k_sales_credits
948 WHERE chr_id = p_chrv_rec.id;
949 Cursor cur_status Is
950 SELECT ste_code from okc_statuses_b status, okc_k_headers_b header
951 where status.code = header.sts_code
952 AND header.id = p_chrv_rec.id;
953
954
955
956 l_crjv_rec OKC_K_REL_OBJS_PUB.crjv_rec_type;
957 l_oiev_tbl OKC_OPER_INST_PUB.oiev_tbl_type;
958 i NUMBER := 0;
959
960 l_scrv_rec OKC_SALES_credit_PUB.scrv_rec_type;
961 l_chr_status OKC_K_HEADERS_B.STS_CODE%TYPE;
962
963
964 BEGIN
965 -- check whether delete is allowed or not
966 If (OKC_CONTRACT_PUB.Update_Allowed(p_chrv_rec.id) <> 'Y') Then
967 raise G_NO_UPDATE_ALLOWED_EXCEPTION;
968 End If;
969
970 -- check whether detail records exists
971 open l_clev_csr;
972 fetch l_clev_csr into l_dummy_val;
973 close l_clev_csr;
974
975 -- delete only if there are no detail records
976 If (l_dummy_val = 0) Then
977
978 --
979 -- Delete all related data that calls Update Minor Version procedure
980 --
981 -- Delete sales credits
982
983 For c In l_scrv_csr
984 Loop
985 l_scrv_rec.id := c.id;
986 l_scrv_rec.object_version_number := c.object_version_number;
987 l_scrv_rec.dnz_chr_id := c.dnz_chr_id;
988
989 OKC_SALES_credit_PUB.delete_Sales_credit(
990 p_api_version => p_api_version,
991 p_init_msg_list => p_init_msg_list,
992 x_return_status => x_return_status,
993 x_msg_count => x_msg_count,
994 x_msg_data => x_msg_data,
995 p_scrv_rec => l_scrv_rec);
996
997 End Loop;
998
999 Open cur_status;
1000 Fetch cur_status into l_chr_status;
1001 Close cur_status;
1002 If l_chr_status In ('ENTERED','CANCELLED')
1003 Then
1004 OKC_VERSION_PVT.delete_version (p_chr_id => p_chrv_rec.id,
1005 p_major_version => 0,
1006 p_minor_version => 0,
1007 p_called_from => 'RESTORE_VERSION');
1008 End if;
1009
1010 OKC_CHR_PVT.Delete_Row(
1011 p_api_version => p_api_version,
1012 p_init_msg_list => p_init_msg_list,
1013 x_return_status => x_return_status,
1014 x_msg_count => x_msg_count,
1015 x_msg_data => x_msg_data,
1016 p_chrv_rec => p_chrv_rec);
1017 Else
1018 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1019 p_msg_name => g_no_parent_record,
1020 p_token1 => g_child_table_token,
1021 p_token1_value => 'OKC_K_LINES_V',
1022 p_token2 => g_parent_table_token,
1023 p_token2_value => 'OKC_K_HEADERS_V');
1024 -- notify caller of an error
1025 x_return_status := OKC_API.G_RET_STS_ERROR;
1026 End If;
1027
1028 -- Delete operation instances (renewal links)
1029 If (x_return_status = OKC_API.G_RET_STS_SUCCESS) Then
1030 FOR oie_rec IN oie_csr
1031 LOOP
1032 i := i + 1;
1033 l_oiev_tbl(i).ID := oie_rec.ID;
1034 END LOOP;
1035
1036 If (i > 0) Then
1037 OKC_OPER_INST_PUB.Delete_Operation_Instance (
1038 p_api_version => p_api_version,
1039 p_init_msg_list => p_init_msg_list,
1040 x_return_status => x_return_status,
1041 x_msg_count => x_msg_count,
1042 x_msg_data => x_msg_data,
1043 p_oiev_tbl => l_oiev_tbl);
1044 End if;
1045 End if;
1046
1047 -- Delete relationships with header and other objects
1048 If (x_return_status = OKC_API.G_RET_STS_SUCCESS) Then
1049 For c In l_crjv_csr
1050 Loop
1051 l_crjv_rec.id := c.id;
1052 l_crjv_rec.object_version_number := c.object_version_number;
1053
1054 OKC_K_REL_OBJS_PUB.delete_row(
1055 p_api_version => p_api_version,
1056 p_init_msg_list => p_init_msg_list,
1057 x_return_status => x_return_status,
1058 x_msg_count => x_msg_count,
1059 x_msg_data => x_msg_data,
1060 p_crjv_rec => l_crjv_rec);
1061
1062 End Loop;
1063 End If;
1064
1065
1066
1067 -- get major version
1068 open l_cvm_csr;
1069 fetch l_cvm_csr into l_major_version;
1070 close l_cvm_csr;
1071
1072 -- Delete any attachments assiciated
1073 If (x_return_status = OKC_API.G_RET_STS_SUCCESS) Then
1074 If (fnd_attachment_util_pkg.get_atchmt_exists (
1075 l_entity_name => 'OKC_K_HEADERS_B',
1076 l_pkey1 => p_chrv_rec.id,
1077 l_pkey2 => l_major_version) = 'Y')
1078
1079 -- The following line to be added to the code once
1080 -- bug 1553916 completes
1081 -- l_pkey2 => l_major_version) = 'Y')
1082 -- also below remove the comments
1083 -- in fnd_attached_documents2_pkg.delete_attachments call
1084 Then
1085 fnd_attached_documents2_pkg.delete_attachments(
1086 x_entity_name => 'OKC_K_HEADERS_B',
1087 x_pk1_value => p_chrv_rec.id,
1088 x_pk2_value => l_major_version
1089 );
1090 End If;
1091 End If;
1092
1093 -- Delete version
1094 If (x_return_status = OKC_API.G_RET_STS_SUCCESS) Then
1095 x_return_status := Delete_Version(p_chrv_rec.id);
1096 End If;
1097 exception
1098 when G_NO_UPDATE_ALLOWED_EXCEPTION then
1099 x_return_status := OKC_API.G_RET_STS_ERROR;
1100 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1101 p_msg_name => g_no_update_allowed,
1102 p_token1 => 'VALUE1',
1103 p_token1_value => 'Contract Header');
1104 when OTHERS then
1105 -- store SQL error message on message stack
1106 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1107 p_msg_name => g_unexpected_error,
1108 p_token1 => g_sqlcode_token,
1109 p_token1_value => sqlcode,
1110 p_token2 => g_sqlerrm_token,
1111 p_token2_value => sqlerrm);
1112
1113 -- notify caller of an UNEXPETED error
1114 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1115 END delete_contract_header;
1116
1117 PROCEDURE delete_contract_header(
1118 p_api_version IN NUMBER,
1119 p_init_msg_list IN VARCHAR2 ,
1120 x_return_status OUT NOCOPY VARCHAR2,
1121 x_msg_count OUT NOCOPY NUMBER,
1122 x_msg_data OUT NOCOPY VARCHAR2,
1123 p_chrv_tbl IN OKC_CHR_PVT.chrv_tbl_type) IS
1124
1125 BEGIN
1126 OKC_CHR_PVT.Delete_Row(
1127 p_api_version => p_api_version,
1128 p_init_msg_list => p_init_msg_list,
1129 x_return_status => x_return_status,
1130 x_msg_count => x_msg_count,
1131 x_msg_data => x_msg_data,
1132 p_chrv_tbl => p_chrv_tbl);
1133 END delete_contract_header;
1134
1135 PROCEDURE lock_contract_header(
1136 p_api_version IN NUMBER,
1137 p_init_msg_list IN VARCHAR2 ,
1138 x_return_status OUT NOCOPY VARCHAR2,
1139 x_msg_count OUT NOCOPY NUMBER,
1140 x_msg_data OUT NOCOPY VARCHAR2,
1141 p_chrv_rec IN OKC_CHR_PVT.chrv_rec_type) IS
1142
1143 BEGIN
1144 OKC_CHR_PVT.Lock_Row(
1145 p_api_version => p_api_version,
1146 p_init_msg_list => p_init_msg_list,
1147 x_return_status => x_return_status,
1148 x_msg_count => x_msg_count,
1149 x_msg_data => x_msg_data,
1150 p_chrv_rec => p_chrv_rec);
1151 END lock_contract_header;
1152
1153 PROCEDURE lock_contract_header(
1154 p_api_version IN NUMBER,
1155 p_init_msg_list IN VARCHAR2 ,
1156 x_return_status OUT NOCOPY VARCHAR2,
1157 x_msg_count OUT NOCOPY NUMBER,
1158 x_msg_data OUT NOCOPY VARCHAR2,
1159 p_chrv_tbl IN OKC_CHR_PVT.chrv_tbl_type) IS
1160
1161 BEGIN
1162 OKC_CHR_PVT.Lock_Row(
1163 p_api_version => p_api_version,
1164 p_init_msg_list => p_init_msg_list,
1165 x_return_status => x_return_status,
1166 x_msg_count => x_msg_count,
1167 x_msg_data => x_msg_data,
1168 p_chrv_tbl => p_chrv_tbl);
1169 END lock_contract_header;
1170
1171 PROCEDURE validate_contract_header(
1172 p_api_version IN NUMBER,
1173 p_init_msg_list IN VARCHAR2 ,
1174 x_return_status OUT NOCOPY VARCHAR2,
1175 x_msg_count OUT NOCOPY NUMBER,
1176 x_msg_data OUT NOCOPY VARCHAR2,
1177 p_chrv_rec IN OKC_CHR_PVT.chrv_rec_type) IS
1178
1179 BEGIN
1180 OKC_CHR_PVT.Validate_Row(
1181 p_api_version => p_api_version,
1182 p_init_msg_list => p_init_msg_list,
1183 x_return_status => x_return_status,
1184 x_msg_count => x_msg_count,
1185 x_msg_data => x_msg_data,
1186 p_chrv_rec => p_chrv_rec);
1187 END validate_contract_header;
1188
1189 PROCEDURE validate_contract_header(
1190 p_api_version IN NUMBER,
1191 p_init_msg_list IN VARCHAR2 ,
1192 x_return_status OUT NOCOPY VARCHAR2,
1193 x_msg_count OUT NOCOPY NUMBER,
1194 x_msg_data OUT NOCOPY VARCHAR2,
1195 p_chrv_tbl IN OKC_CHR_PVT.chrv_tbl_type) IS
1196
1197 BEGIN
1198 OKC_CHR_PVT.Validate_Row(
1199 p_api_version => p_api_version,
1200 p_init_msg_list => p_init_msg_list,
1201 x_return_status => x_return_status,
1202 x_msg_count => x_msg_count,
1203 x_msg_data => x_msg_data,
1204 p_chrv_tbl => p_chrv_tbl);
1205 END validate_contract_header;
1206
1207 PROCEDURE create_ancestry(
1208 p_api_version IN NUMBER,
1209 p_init_msg_list IN VARCHAR2 ,
1210 x_return_status OUT NOCOPY VARCHAR2,
1211 x_msg_count OUT NOCOPY NUMBER,
1212 x_msg_data OUT NOCOPY VARCHAR2,
1213 p_clev_rec IN OKC_CLE_PVT.clev_rec_type) Is
1214
1215 l_acyv_rec OKC_ACY_PVT.acyv_rec_type;
1216 l_out_rec OKC_ACY_PVT.acyv_rec_type;
1217 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1218
1219 -- cursor to get the next level_sequence
1220 Cursor l_acyv_csr Is
1221 select NVL(MAX(level_sequence),0) + 1
1222 from OKC_ANCESTRYS
1223 where cle_id = p_clev_rec.cle_id;
1224
1225 -- cursor to get other ascendants
1226 Cursor l_acyv_csr2 Is
1227 select cle_id_ascendant, level_sequence
1228 from OKC_ANCESTRYS
1229 where cle_id = p_clev_rec.cle_id;
1230
1231 BEGIN
1232 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1233 -- insert ancestry, if line record has a parent line record
1234 If (p_clev_rec.cle_id <> OKC_API.G_MISS_NUM and
1235 p_clev_rec.cle_id is not null)
1236 Then
1237 -- get next level sequence
1238 open l_acyv_csr;
1239 fetch l_acyv_csr into l_acyv_rec.level_sequence;
1240 close l_acyv_csr;
1241
1242 l_acyv_rec.cle_id := p_clev_rec.id;
1243 l_acyv_rec.cle_id_ascendant := p_clev_rec.cle_id;
1244
1245 -- insert currect record to ancestry
1246 OKC_ACY_PVT.insert_row(
1247 p_api_version => p_api_version,
1248 p_init_msg_list => p_init_msg_list,
1249 x_return_status => l_return_status,
1250 x_msg_count => x_msg_count,
1251 x_msg_data => x_msg_data,
1252 p_acyv_rec => l_acyv_rec,
1253 x_acyv_rec => l_out_rec);
1254
1255 -- if the current insert is success,
1256 -- copy all other existing ancestry records of
1257 -- parent line (p_clev_rec.cle_id)
1258 If (l_return_status = OKC_API.G_RET_STS_SUCCESS) Then
1259 open l_acyv_csr2;
1260
1261 -- fetch first record
1262 fetch l_acyv_csr2 into l_acyv_rec.cle_id_ascendant,
1263 l_acyv_rec.level_sequence;
1264 while l_acyv_csr2%FOUND
1265 loop
1266 OKC_ACY_PVT.insert_row(
1267 p_api_version => p_api_version,
1268 p_init_msg_list => p_init_msg_list,
1269 x_return_status => l_return_status,
1270 x_msg_count => x_msg_count,
1271 x_msg_data => x_msg_data,
1272 p_acyv_rec => l_acyv_rec,
1273 x_acyv_rec => l_out_rec);
1274
1275 -- fetch next record
1276 fetch l_acyv_csr2 into l_acyv_rec.cle_id_ascendant,
1277 l_acyv_rec.level_sequence;
1278 end loop;
1279 close l_acyv_csr2;
1280 End If;
1281 End If;
1282 exception
1283 when OTHERS then
1284 -- store SQL error message on message stack
1285 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1286 p_msg_name => g_unexpected_error,
1287 p_token1 => g_sqlcode_token,
1288 p_token1_value => sqlcode,
1289 p_token2 => g_sqlerrm_token,
1290 p_token2_value => sqlerrm);
1291
1292 -- notify caller of an UNEXPETED error
1293 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1294
1295 -- verify that cursors were closed
1296 if l_acyv_csr%ISOPEN then
1297 close l_acyv_csr;
1298 end if;
1299
1300 if l_acyv_csr2%ISOPEN then
1301 close l_acyv_csr2;
1302 end if;
1303
1304 END create_ancestry;
1305
1306 --
1307 -- This procedure returns the contract header id corresponding to the
1308 -- contract line record
1309 -- Called only if dnz_chr_id is null in clev_rec
1310 --
1311 PROCEDURE Get_Contract_Id(p_clev_rec IN OKC_CLE_PVT.clev_rec_type,
1312 x_chr_id OUT NOCOPY NUMBER,
1313 x_return_status OUT NOCOPY VARCHAR2) Is
1314 Cursor l_clev_csr Is
1315 SELECT dnz_chr_id
1316 FROM OKC_K_LINES_B
1317 WHERE id = p_clev_rec.id;
1318 BEGIN
1319 -- initialize return status
1320 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1321 -- if dnz_chr_id is present, return it
1322 If (p_clev_rec.dnz_chr_id is not null and
1323 p_clev_rec.dnz_chr_id <> OKC_API.G_MISS_NUM)
1324 Then
1325 x_chr_id := p_clev_rec.dnz_chr_id;
1326 Else
1327 -- else if chr_id is present , return it
1328 If (p_clev_rec.chr_id is not null and
1329 p_clev_rec.chr_id <> OKC_API.G_MISS_NUM)
1330 Then
1331 x_chr_id := p_clev_rec.chr_id;
1332 Else
1333 -- else get header id from database
1334 Open l_clev_csr;
1335 Fetch l_clev_csr into x_chr_id;
1336 If (l_clev_csr%NOTFOUND) Then
1337 Close l_clev_csr;
1338 x_return_status := OKC_API.G_RET_STS_ERROR;
1339 RAISE OKC_API.G_EXCEPTION_ERROR;
1340 End If;
1341 Close l_clev_csr;
1342 End If;
1343 End If;
1344 exception
1345 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1346 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1347 p_msg_name => g_unexpected_error,
1348 p_token1 => g_sqlcode_token,
1349 p_token1_value => sqlcode,
1350 p_token2 => g_sqlerrm_token,
1351 p_token2_value => sqlerrm);
1352 x_return_status :=OKC_API.G_RET_STS_ERROR;
1353
1354 when OTHERS then
1355 -- store SQL error message on message stack
1356 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1357 p_msg_name => g_unexpected_error,
1358 p_token1 => g_sqlcode_token,
1359 p_token1_value => sqlcode,
1360 p_token2 => g_sqlerrm_token,
1361 p_token2_value => sqlerrm);
1362
1363 -- notify caller of an UNEXPETED error
1364 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1365 END Get_Contract_Id;
1366
1367 PROCEDURE create_contract_line(
1368 p_api_version IN NUMBER,
1369 p_init_msg_list IN VARCHAR2 ,
1370 x_return_status OUT NOCOPY VARCHAR2,
1371 x_msg_count OUT NOCOPY NUMBER,
1372 x_msg_data OUT NOCOPY VARCHAR2,
1373 p_restricted_update IN VARCHAR2 ,
1374 p_clev_rec IN OKC_CLE_PVT.clev_rec_type,
1375 x_clev_rec OUT NOCOPY OKC_CLE_PVT.clev_rec_type) IS
1376
1377 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1378 l_chr_id NUMBER;
1379 BEGIN
1380 -- check whether the contract is updateable or not
1381 OKC_CONTRACT_PVT.Get_Contract_Id(
1382 p_clev_rec => p_clev_rec,
1383 x_chr_id => l_chr_id,
1384 x_return_status => l_return_status);
1385
1386 If (l_return_status = OKC_API.G_RET_STS_SUCCESS) Then
1387 -- if the update is not a restricted update (restricted_update <> 'Y'),
1388 -- check whether update is allowed or not
1389 If (p_restricted_update <> OKC_API.G_TRUE) Then
1390 If (OKC_CONTRACT_PUB.Update_Allowed(l_chr_id) <> 'Y') Then
1391 raise G_NO_UPDATE_ALLOWED_EXCEPTION;
1392 End If;
1393 End If;
1394 Else
1395 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1396 End If;
1397
1398 OKC_CLE_PVT.Insert_Row(
1399 p_api_version => p_api_version,
1400 p_init_msg_list => p_init_msg_list,
1401 x_return_status => x_return_status,
1402 x_msg_count => x_msg_count,
1403 x_msg_data => x_msg_data,
1404 p_clev_rec => p_clev_rec,
1405 x_clev_rec => x_clev_rec);
1406 -- if the above process is success, create ancestry
1407 If (x_return_status = OKC_API.G_RET_STS_SUCCESS) Then
1408 x_clev_rec.cle_id := p_clev_rec.cle_id;
1409 create_ancestry(
1410 p_api_version => p_api_version,
1411 p_init_msg_list => p_init_msg_list,
1412 x_return_status => l_return_status,
1413 x_msg_count => x_msg_count,
1414 x_msg_data => x_msg_data,
1415 p_clev_rec => x_clev_rec);
1416 Else
1417 raise G_EXCEPTION_HALT_PROCESS;
1418 End If;
1419
1420 -- Update minor version
1421 If (l_return_status = OKC_API.G_RET_STS_SUCCESS) Then
1422 x_return_status := Update_Minor_Version(x_clev_rec.dnz_chr_id);
1423 End If;
1424 exception
1425 when G_EXCEPTION_HALT_PROCESS then
1426 null;
1427
1428 when G_NO_UPDATE_ALLOWED_EXCEPTION then
1429 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1430 p_msg_name => g_no_update_allowed,
1431 p_token1 => 'VALUE1',
1432 p_token1_value => 'Contract Lines');
1433
1434 -- notify caller of an error
1435 x_return_status := OKC_API.G_RET_STS_ERROR;
1436 when OTHERS then
1437 -- store SQL error message on message stack
1438 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1439 p_msg_name => g_unexpected_error,
1440 p_token1 => g_sqlcode_token,
1441 p_token1_value => sqlcode,
1442 p_token2 => g_sqlerrm_token,
1443 p_token2_value => sqlerrm);
1444
1445 -- notify caller of an UNEXPETED error
1446 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1447 END create_contract_line;
1448
1449 PROCEDURE create_contract_line(
1450 p_api_version IN NUMBER,
1451 p_init_msg_list IN VARCHAR2 ,
1452 x_return_status OUT NOCOPY VARCHAR2,
1453 x_msg_count OUT NOCOPY NUMBER,
1454 x_msg_data OUT NOCOPY VARCHAR2,
1455 p_clev_tbl IN OKC_CLE_PVT.clev_tbl_type,
1456 x_clev_tbl OUT NOCOPY OKC_CLE_PVT.clev_tbl_type) IS
1457
1458 BEGIN
1459 OKC_CLE_PVT.Insert_Row(
1460 p_api_version => p_api_version,
1461 p_init_msg_list => p_init_msg_list,
1462 x_return_status => x_return_status,
1463 x_msg_count => x_msg_count,
1464 x_msg_data => x_msg_data,
1465 p_clev_tbl => p_clev_tbl,
1466 x_clev_tbl => x_clev_tbl);
1467 END create_contract_line;
1468
1469 PROCEDURE update_contract_line(
1470 p_api_version IN NUMBER,
1471 p_init_msg_list IN VARCHAR2 ,
1472 x_return_status OUT NOCOPY VARCHAR2,
1473 x_msg_count OUT NOCOPY NUMBER,
1474 x_msg_data OUT NOCOPY VARCHAR2,
1475 p_restricted_update IN VARCHAR2 ,
1476 p_clev_rec IN OKC_CLE_PVT.clev_rec_type,
1477 x_clev_rec OUT NOCOPY OKC_CLE_PVT.clev_rec_type) IS
1478
1479 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1480 l_chr_id NUMBER;
1481 l_api_name CONSTANT VARCHAR2(30) := 'Update_Contract_Line';
1482
1483 l_contract_number VARCHAR2(120);
1484 l_modifier VARCHAR2(120);
1485 Cursor l_chr_csr(p_id NUMBER) Is
1486 SELECT contract_number,contract_number_modifier
1487 --npalepu 26-10-2005 modified for bug # 4691662.
1488 --Replaced table okc_k_headers_b with headers_All_b table
1489 /* FROM okc_k_headers_b */
1490 FROM okc_k_headers_all_b
1491 --end npalepu
1492 where id = p_id;
1493 BEGIN
1494 -- check whether the contract is updateable or not
1495 OKC_CONTRACT_PVT.Get_Contract_Id(
1496 p_clev_rec => p_clev_rec,
1497 x_chr_id => l_chr_id,
1498 x_return_status => l_return_status);
1499
1500 If (l_return_status = OKC_API.G_RET_STS_SUCCESS) Then
1501 -- if the update is not a restricted update (restricted_update <> 'Y'),
1502 -- check whether update is allowed or not
1503 If (p_restricted_update <> OKC_API.G_TRUE) Then
1504 If (OKC_CONTRACT_PUB.Update_Allowed(l_chr_id) <> 'Y') Then
1505 raise G_NO_UPDATE_ALLOWED_EXCEPTION;
1506 End If;
1507 End If;
1508 Else
1509 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1510 End If;
1511 OKC_CLE_PVT.Update_Row(
1512 p_api_version => p_api_version,
1513 p_init_msg_list => p_init_msg_list,
1514 x_return_status => x_return_status,
1515 x_msg_count => x_msg_count,
1516 x_msg_data => x_msg_data,
1517 p_restricted_update => p_restricted_update,
1518 p_clev_rec => p_clev_rec,
1519 x_clev_rec => x_clev_rec);
1520
1521 -- Call action assembler if status is changed
1522 If (x_return_status = OKC_API.G_RET_STS_SUCCESS AND
1523 NVL(p_clev_rec.Call_Action_Asmblr,'Y') = 'Y' AND
1524 p_clev_rec.old_sts_code is not null AND
1525 p_clev_rec.new_sts_code is not null AND
1526 p_clev_rec.old_ste_code is not null AND
1527 p_clev_rec.new_ste_code is not null AND
1528 (p_clev_rec.old_sts_code <> p_clev_rec.new_sts_code OR
1529 p_clev_rec.old_ste_code <> p_clev_rec.new_ste_code
1530 )
1531 )
1532 Then
1533 open l_chr_csr(x_clev_rec.dnz_chr_id);
1534 fetch l_chr_csr into l_contract_number,l_modifier;
1535 close l_chr_csr;
1536
1537 OKC_KL_STS_CHG_ASMBLR_PVT.Acn_Assemble(
1538 p_api_version => p_api_version,
1539 p_init_msg_list => p_init_msg_list,
1540 x_return_status => x_return_status,
1541 x_msg_count => x_msg_count,
1542 x_msg_data => x_msg_data,
1543 p_k_id => x_clev_rec.dnz_chr_id,
1544 p_kl_id => x_clev_rec.id,
1545 p_k_number => l_contract_number,
1546 p_k_nbr_mod => l_modifier,
1547 p_kl_number => x_clev_rec.line_number,
1548 p_kl_cur_sts_code => p_clev_rec.new_sts_code,
1549 p_kl_cur_sts_type => p_clev_rec.new_ste_code,
1550 p_kl_pre_sts_code => p_clev_rec.old_sts_code,
1551 p_kl_pre_sts_type => p_clev_rec.old_ste_code,
1552 p_kl_source_system_code => p_clev_rec.ORIG_SYSTEM_SOURCE_CODE);
1553 End If;
1554
1555 -- Update minor version
1556 If (x_return_status = OKC_API.G_RET_STS_SUCCESS) Then
1557 x_return_status := Update_Minor_Version(x_clev_rec.dnz_chr_id);
1558 End If;
1559 exception
1560 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1561 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1562 (
1563 l_api_name,
1564 G_PKG_NAME,
1565 'OKC_API.G_RET_STS_UNEXP_ERROR',
1566 x_msg_count,
1567 x_msg_data,
1568 '_PVT'
1569 );
1570 when G_NO_UPDATE_ALLOWED_EXCEPTION then
1571 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1572 p_msg_name => g_no_update_allowed,
1573 p_token1 => 'VALUE1',
1574 p_token1_value => 'Contract Lines');
1575
1576 -- notify caller of an error
1577 x_return_status := OKC_API.G_RET_STS_ERROR;
1578 when OTHERS then
1579 -- store SQL error message on message stack
1580 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1581 p_msg_name => g_unexpected_error,
1582 p_token1 => g_sqlcode_token,
1583 p_token1_value => sqlcode,
1584 p_token2 => g_sqlerrm_token,
1585 p_token2_value => sqlerrm);
1586
1587 -- notify caller of an UNEXPETED error
1588 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1589 END update_contract_line;
1590
1591 PROCEDURE update_contract_line(
1592 p_api_version IN NUMBER,
1593 p_init_msg_list IN VARCHAR2 ,
1594 x_return_status OUT NOCOPY VARCHAR2,
1595 x_msg_count OUT NOCOPY NUMBER,
1596 x_msg_data OUT NOCOPY VARCHAR2,
1597 p_restricted_update IN VARCHAR2 ,
1598 p_clev_tbl IN OKC_CLE_PVT.clev_tbl_type,
1599 x_clev_tbl OUT NOCOPY OKC_CLE_PVT.clev_tbl_type) IS
1600
1601 BEGIN
1602 OKC_CLE_PVT.Update_Row(
1603 p_api_version => p_api_version,
1604 p_init_msg_list => p_init_msg_list,
1605 x_return_status => x_return_status,
1606 x_msg_count => x_msg_count,
1607 x_msg_data => x_msg_data,
1608 p_restricted_update => p_restricted_update,
1609 p_clev_tbl => p_clev_tbl,
1610 x_clev_tbl => x_clev_tbl);
1611 END update_contract_line;
1612
1613 PROCEDURE delete_ancestry(
1614 p_api_version IN NUMBER,
1615 p_init_msg_list IN VARCHAR2 ,
1616 x_return_status OUT NOCOPY VARCHAR2,
1617 x_msg_count OUT NOCOPY NUMBER,
1618 x_msg_data OUT NOCOPY VARCHAR2,
1619 p_cle_id IN NUMBER) Is
1620
1621 l_acyv_rec OKC_ACY_PVT.acyv_rec_type;
1622 l_out_rec OKC_ACY_PVT.acyv_rec_type;
1623 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1624
1625 -- cursor to get ancestry records to delete
1626 Cursor l_acyv_csr Is
1627 select cle_id, cle_id_ascendant
1628 from OKC_ANCESTRYS
1629 where cle_id = p_cle_id;
1630
1631 BEGIN
1632 -- delete all ancestry records if p_cle_id is not null
1633 If (p_cle_id <> OKC_API.G_MISS_NUM and
1634 p_cle_id is not null)
1635 Then
1636 open l_acyv_csr;
1637
1638 -- fetch first record
1639 fetch l_acyv_csr into l_acyv_rec.cle_id,
1640 l_acyv_rec.cle_id_ascendant;
1641 while l_acyv_csr%FOUND
1642 loop
1643 OKC_ACY_PVT.delete_row(
1644 p_api_version => p_api_version,
1645 p_init_msg_list => p_init_msg_list,
1646 x_return_status => l_return_status,
1647 x_msg_count => x_msg_count,
1648 x_msg_data => x_msg_data,
1649 p_acyv_rec => l_acyv_rec);
1650 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
1651 raise G_EXCEPTION_HALT_VALIDATION;
1652 End If;
1653 -- fetch next record
1654 fetch l_acyv_csr into l_acyv_rec.cle_id,
1655 l_acyv_rec.cle_id_ascendant;
1656 end loop;
1657 close l_acyv_csr;
1658 x_return_status := l_return_status;
1659 End If;
1660 exception
1661 when G_EXCEPTION_HALT_VALIDATION then
1662 -- store SQL error message on message stack
1663 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1664 p_msg_name => g_unexpected_error,
1665 p_token1 => g_sqlcode_token,
1666 p_token1_value => sqlcode,
1667 p_token2 => g_sqlerrm_token,
1668 p_token2_value => sqlerrm);
1669 x_return_status := l_return_status;
1670
1671 when OTHERS then
1672 -- store SQL error message on message stack
1673 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1674 p_msg_name => g_unexpected_error,
1675 p_token1 => g_sqlcode_token,
1676 p_token1_value => sqlcode,
1677 p_token2 => g_sqlerrm_token,
1678 p_token2_value => sqlerrm);
1679
1680 -- notify caller of an UNEXPETED error
1681 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1682
1683 -- verify that cursor was closed
1684 if l_acyv_csr%ISOPEN then
1685 close l_acyv_csr;
1686 end if;
1687
1688 END delete_ancestry;
1689
1690 PROCEDURE delete_contract_line(
1691 p_api_version IN NUMBER,
1692 p_init_msg_list IN VARCHAR2 ,
1693 x_return_status OUT NOCOPY VARCHAR2,
1694 x_msg_count OUT NOCOPY NUMBER,
1695 x_msg_data OUT NOCOPY VARCHAR2,
1696 p_clev_rec IN OKC_CLE_PVT.clev_rec_type) IS
1697
1698 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1699 l_chr_id NUMBER;
1700 l_dummy_val NUMBER;
1701 l_major_version FND_ATTACHED_DOCUMENTS.PK2_VALUE%TYPE;
1702
1703 Cursor l_clev_csr Is
1704 select count(*)
1705 from OKC_K_LINES_B
1706 where cle_id = p_clev_rec.id;
1707
1708 Cursor l_cimv_csr Is
1709 select count(*)
1710 from OKC_K_ITEMS
1711 where cle_id = p_clev_rec.id;
1712
1713 Cursor l_crjv_csr Is
1714 SELECT id, object_version_number
1715 FROM OKC_K_REL_OBJS
1716 WHERE cle_id = p_clev_rec.id;
1717
1718 Cursor l_cvm_csr(p_chr_id NUMBER) Is
1719 SELECT to_char(major_version)
1720 FROM okc_k_vers_numbers
1721 WHERE chr_id = p_chr_id;
1722
1723
1724 Cursor l_scrv_csr Is
1725 SELECT id, object_version_number, dnz_chr_id
1726 FROM okc_k_sales_credits
1727 WHERE cle_id = p_clev_rec.id;
1728
1729 Cursor l_okc_ph_line_breaks_v_csr Is
1730 SELECT id, object_version_number, cle_id
1731 FROM okc_ph_line_breaks
1732 WHERE cle_id = p_clev_rec.id;
1733
1734 -- Bug #3358872; Added condition dnz_chr_id to improve the
1735 -- performance of the sql.
1736
1737 Cursor l_gvev_csr Is
1738 SELECT id, object_version_number
1739 FROM okc_governances
1740 WHERE cle_id = p_clev_rec.id
1741 AND dnz_chr_id = l_chr_id;
1742
1743
1744 l_crjv_rec OKC_K_REL_OBJS_PUB.crjv_rec_type;
1745 i NUMBER := 0;
1746
1747 l_scrv_rec OKC_SALES_credit_PUB.scrv_rec_type;
1748
1749
1750 l_okc_ph_line_breaks_v_rec OKC_PH_LINE_BREAKS_PUB.okc_ph_line_breaks_v_rec_type;
1751 l_lse_id NUMBER; --linestyle
1752 l_dnz_chr_id NUMBER;
1753 l_ph_pricing_type VARCHAR2(30);
1754
1755 l_gvev_rec OKC_GVE_PVT.gvev_rec_type;
1756
1757
1758 BEGIN
1759 -- check whether the contract is updateable or not
1760 OKC_CONTRACT_PVT.Get_Contract_Id(
1761 p_clev_rec => p_clev_rec,
1762 x_chr_id => l_chr_id,
1763 x_return_status => l_return_status);
1764
1765 If (l_return_status = OKC_API.G_RET_STS_SUCCESS) Then
1766 If (OKC_CONTRACT_PUB.Update_Allowed(l_chr_id) <> 'Y') Then
1767 raise G_NO_UPDATE_ALLOWED_EXCEPTION;
1768 End If;
1769 End If;
1770
1771 -- check whether detail records exists
1772 open l_clev_csr;
1773 fetch l_clev_csr into l_dummy_val;
1774 close l_clev_csr;
1775
1776 -- delete only if there are no detail records
1777 If (l_dummy_val = 0) Then
1778 -- check if there are any items exist for this contract line
1779 open l_cimv_csr;
1780 fetch l_cimv_csr into l_dummy_val;
1781 close l_cimv_csr;
1782
1783 -- delete only if there are no items
1784 If (l_dummy_val = 0) Then
1785 OKC_CLE_PVT.Delete_Row(
1786 p_api_version => p_api_version,
1787 p_init_msg_list => p_init_msg_list,
1788 x_return_status => x_return_status,
1789 x_msg_count => x_msg_count,
1790 x_msg_data => x_msg_data,
1791 p_clev_rec => p_clev_rec);
1792
1793 -- if the above process is success, delete all ancestrys
1794 If (x_return_status = OKC_API.G_RET_STS_SUCCESS) Then
1795 delete_ancestry(
1796 p_api_version => p_api_version,
1797 p_init_msg_list => p_init_msg_list,
1798 x_return_status => x_return_status,
1799 x_msg_count => x_msg_count,
1800 x_msg_data => x_msg_data,
1801 p_cle_id => p_clev_rec.id);
1802 End If;
1803
1804 Else
1805 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1806 p_msg_name => g_no_parent_record,
1807 p_token1 => g_child_table_token,
1808 p_token1_value => 'OKC_K_ITEMS_V',
1809 p_token2 => g_parent_table_token,
1810 p_token2_value => 'OKC_K_LINES_V');
1811 -- notify caller of an error
1812 x_return_status := OKC_API.G_RET_STS_ERROR;
1813 End If;
1814 Else
1815 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1816 p_msg_name => g_no_parent_record,
1817 p_token1 => g_child_table_token,
1818 p_token1_value => 'OKC_K_LINES_V',
1819 p_token2 => g_parent_table_token,
1820 p_token2_value => 'OKC_K_LINES_V');
1821 -- notify caller of an error
1822 x_return_status := OKC_API.G_RET_STS_ERROR;
1823 End If;
1824
1825 -- Delete relationships with line and other objects
1826 If (x_return_status = OKC_API.G_RET_STS_SUCCESS) Then
1827 For c In l_crjv_csr
1828 Loop
1829 l_crjv_rec.id := c.id;
1830 l_crjv_rec.object_version_number := c.object_version_number;
1831
1832 OKC_K_REL_OBJS_PUB.delete_row(
1833 p_api_version => p_api_version,
1834 p_init_msg_list => p_init_msg_list,
1835 x_return_status => x_return_status,
1836 x_msg_count => x_msg_count,
1837 x_msg_data => x_msg_data,
1838 p_crjv_rec => l_crjv_rec);
1839
1840 End Loop;
1841 End If;
1842
1843 -- Delete sales credits
1844 If (x_return_status = OKC_API.G_RET_STS_SUCCESS) Then
1845 For c In l_scrv_csr
1846 Loop
1847 l_scrv_rec.id := c.id;
1848 l_scrv_rec.object_version_number := c.object_version_number;
1849 l_scrv_rec.dnz_chr_id := c.dnz_chr_id;
1850
1851 OKC_SALES_credit_PUB.delete_Sales_credit(
1852 p_api_version => p_api_version,
1853 p_init_msg_list => p_init_msg_list,
1854 x_return_status => x_return_status,
1855 x_msg_count => x_msg_count,
1856 x_msg_data => x_msg_data,
1857 p_scrv_rec => l_scrv_rec);
1858
1859 End Loop;
1860 End If;
1861
1862
1863 -- Delete price hold line breaks
1864 If (x_return_status = OKC_API.G_RET_STS_SUCCESS) Then
1865
1866 /**********************************************
1867 don't need to do this for delete
1868 --added for price hold top lines
1869 IF l_lse_id = 61 THEN
1870 --if the contract line being deleted is a Price Hold top line,
1871 --we need to delete the corresponding entries in QP
1872
1873 OKC_PHI_PVT.process_price_hold(
1874 p_api_version => p_api_version,
1875 p_init_msg_list => p_init_msg_list,
1876 x_return_status => x_return_status,
1877 x_msg_count => x_msg_count,
1878 x_msg_data => x_msg_data,
1879 p_chr_id => l_dnz_chr_id,
1880 p_operation_code => 'TERMINATE');
1881 END IF;
1882 ****************************************************/
1883
1884 --added for price hold sublines
1885 IF l_ph_pricing_type = 'PRICE_BREAK' THEN
1886 --if the contract line being deleted is a Price Hold sub line with pricing type of 'Price Break'
1887 --we need to delete the price hold line breaks as well
1888
1889 For c In l_okc_ph_line_breaks_v_csr
1890 Loop
1891 l_okc_ph_line_breaks_v_rec.id := c.id;
1892 l_okc_ph_line_breaks_v_rec.object_version_number := c.object_version_number;
1893 l_okc_ph_line_breaks_v_rec.cle_id := c.cle_id;
1894
1895 OKC_PH_LINE_BREAKS_PUB.delete_Price_Hold_Line_Breaks(
1896 p_api_version => p_api_version,
1897 p_init_msg_list => p_init_msg_list,
1898 x_return_status => x_return_status,
1899 x_msg_count => x_msg_count,
1900 x_msg_data => x_msg_data,
1901 p_okc_ph_line_breaks_v_rec => l_okc_ph_line_breaks_v_rec
1902 );
1903 End Loop;
1904 End If;
1905
1906
1907 End If;
1908
1909
1910 -- Delete all contract governances information at the line level
1911 If (x_return_status = OKC_API.G_RET_STS_SUCCESS) Then
1912 --(note: we do not have to write code to delete goverances in delete_contract_header because
1913 --that is already being done in okc_delete_contract_pvt.delete_contract where the delete is done
1914 --on the basis of dnz_chr_id so lines are deleted there as well)
1915
1916 For c In l_gvev_csr
1917 Loop
1918
1919 l_gvev_rec.id := c.id;
1920 l_gvev_rec.object_version_number := c.object_version_number;
1921
1922 OKC_GVE_PVT.Delete_Row(
1923 p_api_version => p_api_version,
1924 p_init_msg_list => p_init_msg_list,
1925 x_return_status => x_return_status,
1926 x_msg_count => x_msg_count,
1927 x_msg_data => x_msg_data,
1928 p_gvev_rec => l_gvev_rec
1929 );
1930
1931 End Loop;
1932
1933 End If;
1934
1935
1936 -- get major version
1937 open l_cvm_csr(l_chr_id);
1938 fetch l_cvm_csr into l_major_version;
1939 close l_cvm_csr;
1940
1941 -- Delete any attachments assiciated with this line
1942 If (x_return_status = OKC_API.G_RET_STS_SUCCESS) Then
1943 If (fnd_attachment_util_pkg.get_atchmt_exists (
1944 l_entity_name => 'OKC_K_LINES_B',
1945 l_pkey1 => p_clev_rec.id,
1946 l_pkey2 => l_major_version) = 'Y')
1947
1948 -- The following line to be added to the code once
1949 -- bug 1553916 completes
1950 -- l_pkey2 => l_major_version) = 'Y')
1951 -- also below remove the comments
1952 -- in fnd_attached_documents2_pkg.delete_attachments call
1953 Then
1954 fnd_attached_documents2_pkg.delete_attachments(
1955 x_entity_name => 'OKC_K_LINES_B',
1956 x_pk1_value => p_clev_rec.id,
1957 x_pk2_value => l_major_version
1958 );
1959 End If;
1960 End If;
1961
1962 -- Update minor version
1963 If (x_return_status = OKC_API.G_RET_STS_SUCCESS) Then
1964 x_return_status := Update_Minor_Version(l_chr_id);
1965 End If;
1966 exception
1967 when G_NO_UPDATE_ALLOWED_EXCEPTION then
1968 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1969 p_msg_name => g_no_update_allowed,
1970 p_token1 => 'VALUE1',
1971 p_token1_value => 'Contract Lines');
1972
1973 -- notify caller of an error
1974 x_return_status := OKC_API.G_RET_STS_ERROR;
1975 when OTHERS then
1976 -- store SQL error message on message stack
1977 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1978 p_msg_name => g_unexpected_error,
1979 p_token1 => g_sqlcode_token,
1980 p_token1_value => sqlcode,
1981 p_token2 => g_sqlerrm_token,
1982 p_token2_value => sqlerrm);
1983
1984 -- notify caller of an UNEXPETED error
1985 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1986 END delete_contract_line;
1987
1988 PROCEDURE delete_contract_line(
1989 p_api_version IN NUMBER,
1990 p_init_msg_list IN VARCHAR2 ,
1991 x_return_status OUT NOCOPY VARCHAR2,
1992 x_msg_count OUT NOCOPY NUMBER,
1993 x_msg_data OUT NOCOPY VARCHAR2,
1994 p_clev_tbl IN OKC_CLE_PVT.clev_tbl_type) IS
1995
1996 BEGIN
1997 OKC_CLE_PVT.Delete_Row(
1998 p_api_version => p_api_version,
1999 p_init_msg_list => p_init_msg_list,
2000 x_return_status => x_return_status,
2001 x_msg_count => x_msg_count,
2002 x_msg_data => x_msg_data,
2003 p_clev_tbl => p_clev_tbl);
2004 END delete_contract_line;
2005
2006 PROCEDURE delete_contract_line(
2007 p_api_version IN NUMBER,
2008 p_init_msg_list IN VARCHAR2 ,
2009 x_return_status OUT NOCOPY VARCHAR2,
2010 x_msg_count OUT NOCOPY NUMBER,
2011 x_msg_data OUT NOCOPY VARCHAR2,
2012 p_line_id IN NUMBER) IS
2013
2014 l_Cov_cle_Id NUMBER;
2015 l_Item_id NUMBER;
2016 l_contact_Id NUMBER;
2017 l_RGP_Id NUMBER;
2018 l_Rule_Id NUMBER;
2019 l_cle_Id NUMBER;
2020 l_chr_id NUMBER;
2021 v_Index Binary_Integer;
2022
2023 CURSOR DNZ_Cur(p_id NUMBER) IS
2024 SELECT dnz_chr_id
2025 FROM okc_k_lines_b
2026 WHERE id = p_id;
2027 CURSOR Child_Cur1(P_Parent_Id IN NUMBER)
2028 IS SELECT ID
2029 FROM Okc_K_Lines_b
2030 WHERE cle_Id=P_Parent_Id;
2031 CURSOR Child_Cur2(P_Parent_Id IN NUMBER)
2032 IS SELECT ID
2033 FROM Okc_K_Lines_b
2034 WHERE cle_Id=P_Parent_Id;
2035 CURSOR Child_Cur3(P_Parent_Id IN NUMBER)
2036 IS SELECT ID
2037 FROM Okc_K_Lines_b
2038 WHERE cle_Id=P_Parent_Id;
2039 CURSOR Child_Cur4(P_Parent_Id IN NUMBER)
2040 IS SELECT ID
2041 FROM Okc_K_Lines_b
2042 WHERE cle_Id=P_Parent_Id;
2043 CURSOR Child_Cur5(P_Parent_Id IN NUMBER)
2044 IS SELECT ID
2045 FROM Okc_K_Lines_b
2046 WHERE cle_Id=P_Parent_Id;
2047
2048 CURSOR Item_Cur(P_Line_Id IN NUMBER)
2049 IS SELECT ID
2050 FROM Okc_K_ITEMS
2051 WHERE cle_Id=P_Line_Id;
2052
2053 CURSOR RT_Cur(P_Rule_Id IN NUMBER)
2054 IS SELECT Tve_ID
2055 FROM OKC_React_Intervals
2056 WHERE Rul_Id =P_Rule_Id;
2057
2058 CURSOR Kprl_Cur(P_cle_Id IN NUMBER) IS
2059 SELECT ID FROM OKC_K_PARTY_ROLES_B
2060 WHERE dnz_chr_id = l_chr_id
2061 AND cle_Id=P_cle_Id;
2062
2063 CURSOR Contact_Cur(P_cpl_Id IN NUMBER) IS
2064 SELECT ID FROM OKC_CONTACTS
2065 WHERE cpl_Id=P_cpl_Id;
2066
2067 CURSOR TRule_Cur( P_Rgp_Id IN NUMBER,
2068 P_Rule_Type IN Varchar2) IS
2069 SELECT ID FROM OKC_RULES_B
2070 WHERE Rgp_Id=P_Rgp_Id
2071 AND Rule_Information_category=P_rule_Type;
2072
2073 CURSOR Rl_Cur(P_Rgp_Id IN NUMBER) IS
2074 SELECT ID FROM OKC_RULES_B
2075 WHERE Rgp_Id=P_Rgp_Id;
2076
2077 CURSOR Rgp_Cur(P_cle_Id IN NUMBER) IS
2078 SELECT ID FROM OKC_RULE_GROUPS_B
2079 WHERE cle_Id=P_Cle_Id;
2080
2081 CURSOR Relobj_Cur(P_Cle_Id IN NUMBER) IS
2082 SELECT Id FROM OKC_K_REL_OBJS
2083 WHERE cle_Id = P_cle_Id;
2084
2085 n NUMBER:=0;
2086 l_clev_tbl_in okc_contract_pub.clev_tbl_type;
2087 l_clev_tbl_tmp okc_contract_pub.clev_tbl_type;
2088 l_rgpv_tbl_in okc_rule_pub.rgpv_tbl_type;
2089 l_rulv_tbl_in okc_rule_pub.rulv_tbl_type;
2090 l_cimv_tbl_in okc_Contract_Item_Pub.cimv_tbl_TYPE;
2091 l_ctcv_tbl_in okc_contract_party_pub.ctcv_tbl_type;
2092 l_cplv_tbl_in okc_contract_party_pub.cplv_tbl_type;
2093 l_crjv_tbl_in okc_k_rel_objs_pub.crjv_tbl_type;
2094 l_api_version CONSTANT NUMBER := 1.0;
2095 l_init_msg_list CONSTANT VARCHAR2(1) := 'T';
2096 l_return_status VARCHAR2(1);
2097 l_msg_count NUMBER;
2098 l_msg_data VARCHAR2(2000):=null;
2099 l_msg_index_out Number;
2100 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Contract_Line';
2101 l_catv_tbl_in okc_k_article_pub.catv_tbl_type;
2102 e_error Exception;
2103 c_clev NUMBER:=1;
2104 c_rulv NUMBER:=1;
2105 c_rgpv NUMBER:=1;
2106 c_cimv NUMBER:=1;
2107 c_ctcv NUMBER:=1;
2108 c_catv NUMBER:=1;
2109 c_cplv NUMBER:=1;
2110 c_crjv NUMBER:=1;
2111 l_lse_Id NUMBER;
2112
2113 -- BUG#4066428 HKAMDAR 29-Dec-2004 Part 4
2114 l_meaning VARCHAR2(80) ;
2115 lx_return_status VARCHAR2(1) ;
2116 -- BUG#4066428 HKAMDAR End Part 4
2117 ---------------------------------------------------------------------------
2118 -- PROCEDURE Validate_Line_id
2119 ---------------------------------------------------------------------------
2120 PROCEDURE Validate_Line_id(
2121 p_line_id IN NUMBER,
2122 x_return_status OUT NOCOPY VARCHAR2) IS
2123 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2124 l_Count NUMBER;
2125 CURSOR Cur_Line(P_Line_Id IN NUMBER) IS
2126 SELECT COUNT(*) FROM OKC_K_LINES_B
2127 WHERE id=P_Line_Id;
2128 BEGIN
2129 IF P_Line_id = OKC_API.G_MISS_NUM OR
2130 P_Line_Id IS NULL
2131 THEN
2132 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'P_Line_Id');
2133
2134 l_return_status := OKC_API.G_RET_STS_ERROR;
2135 END IF;
2136
2137 OPEN Cur_Line(P_LIne_Id);
2138 FETCH Cur_Line INTO l_Count;
2139 CLOSE Cur_Line;
2140 IF NOT l_Count = 1
2141 THEN
2142 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'P_Line_Id');
2143
2144 l_return_status := OKC_API.G_RET_STS_ERROR;
2145 END IF;
2146 x_return_status := l_return_status;
2147 EXCEPTION
2148 WHEN OTHERS THEN
2149 -- store SQL error message on message stack for caller
2150 OKC_API.set_message(G_APP_NAME,
2151 G_UNEXPECTED_ERROR,
2152 G_SQLCODE_TOKEN,
2153 SQLCODE,
2154 G_SQLERRM_TOKEN,
2155 SQLERRM);
2156 -- notify caller of an UNEXPECTED error
2157 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2158 END Validate_Line_id;
2159 BEGIN
2160 x_return_status:=OKC_API.G_RET_STS_SUCCESS;
2161 Validate_Line_id(p_line_id,l_return_status);
2162 IF NOT l_Return_Status ='S'
2163 THEN RETURN;
2164 END IF;
2165
2166 -- Get header id
2167 open DNZ_Cur(p_line_id);
2168 fetch DNZ_Cur into l_chr_id;
2169 close DNZ_Cur;
2170
2171 l_clev_tbl_tmp(c_clev).ID:=P_Line_Id;
2172 c_clev:=c_clev+1;
2173 FOR Child_Rec1 IN Child_Cur1(P_Line_Id)
2174 LOOP
2175 l_clev_tbl_tmp(c_clev).ID:=Child_Rec1.ID;
2176 c_clev:=c_clev+1;
2177 FOR Child_Rec2 IN Child_Cur2(Child_Rec1.Id)
2178 LOOP
2179 l_clev_tbl_tmp(c_clev).ID:=Child_Rec2.Id;
2180 c_clev:=c_clev+1;
2181 FOR Child_Rec3 IN Child_Cur3(Child_Rec2.Id)
2182 LOOP
2183 l_clev_tbl_tmp(c_clev).ID:=Child_Rec3.Id;
2184 c_clev:=c_clev+1;
2185 FOR Child_Rec4 IN Child_Cur4(Child_Rec3.Id)
2186 LOOP
2187 l_clev_tbl_tmp(c_clev).ID:=Child_Rec4.Id;
2188 c_clev:=c_clev+1;
2189 FOR Child_Rec5 IN Child_Cur5(Child_Rec4.Id)
2190 LOOP
2191 l_clev_tbl_tmp(c_clev).ID:=Child_Rec5.Id;
2192 c_clev:=c_clev+1;
2193 END LOOP;
2194 END LOOP;
2195 END LOOP;
2196 END LOOP;
2197 END LOOP;
2198 c_clev:=1;
2199 FOR v_Index IN REVERSE l_clev_tbl_tmp.FIRST .. l_clev_tbl_tmp.LAST
2200 LOOP
2201 l_clev_tbl_in(c_clev).ID:= l_clev_tbl_tmp(v_Index).ID;
2202 c_clev:=c_Clev+1;
2203 END LOOP;
2204 -- Get Relational Objects Linked to the lines
2205 FOR v_Index IN l_clev_tbl_in.FIRST .. l_clev_tbl_in.LAST
2206 LOOP
2207 FOR RelObj_REC IN RelObj_Cur(l_clev_tbl_in(v_Index).ID)
2208 LOOP
2209 l_crjv_tbl_in(c_crjv).ID:= RelObj_Rec.Id;
2210 c_crjv:=c_crjv+1;
2211 END LOOP;
2212 END LOOP;
2213
2214 -- Get Rule Groups and Rules
2215 FOR v_index IN l_clev_tbl_in.FIRST .. l_clev_tbl_In.LAST
2216 LOOP
2217 FOR rgp_rec IN Rgp_Cur(l_clev_tbl_in(v_index).id) LOOP
2218 l_Rgp_Id := rgp_rec.id;
2219 l_rgpv_tbl_in(c_rgpv).Id:=l_Rgp_Id;
2220 c_rgpv:=c_Rgpv+1;
2221 FOR Rl_Rec IN Rl_Cur(l_Rgp_Id)
2222 LOOP
2223 l_Rulv_tbl_in(c_rulv).ID:=Rl_Rec.ID;
2224 c_rulv:=c_rulv+1;
2225 END LOOP;
2226 END LOOP;
2227 END LOOP;
2228
2229 -- Get Items
2230 FOR v_Index IN l_clev_tbl_in.FIRST .. l_clev_tbl_in.LAST
2231 LOOP
2232 FOR ITEM_REC IN Item_Cur(l_clev_tbl_in(v_Index).ID)
2233 LOOP
2234 l_cimv_tbl_in(c_cimv).ID:= Item_Rec.Id;
2235 c_cimv:=c_cimv+1;
2236 END LOOP;
2237 END LOOP;
2238 -- GET K Party Roles and Contacts
2239 FOR v_Index IN l_clev_tbl_in.FIRST .. l_clev_tbl_in.LAST
2240 LOOP
2241 FOR Kprl_REC IN Kprl_Cur(l_clev_tbl_in(v_Index).ID)
2242 LOOP
2243 l_cplv_tbl_in(c_cplv).id:=Kprl_Rec.ID;
2244 c_cplv:=c_cplv+1;
2245 FOR Contact_Rec IN Contact_Cur(Kprl_Rec.id)
2246 LOOP
2247 l_ctcv_tbl_in(c_ctcv).id:= Contact_Rec.Id;
2248 c_ctcv:=c_ctcv+1;
2249 END LOOP;
2250 END LOOP;
2251 END LOOP;
2252
2253 IF NOT l_crjv_tbl_in.COUNT=0
2254 THEN
2255
2256 OKC_K_REL_OBJS_PUB.Delete_Row(
2257 p_api_version => l_api_version,
2258 p_init_msg_list => l_init_msg_list,
2259 x_return_status => l_return_status,
2260 x_msg_count => l_msg_count,
2261 x_msg_data => l_msg_data,
2262 p_crjv_tbl => l_crjv_tbl_in);
2263
2264 if not (l_return_status = OKC_API.G_RET_STS_SUCCESS)
2265 then
2266 return;
2267 end if;
2268 END IF;
2269 IF NOT l_ctcv_tbl_in.COUNT=0
2270 THEN
2271 OKC_CONTRACT_PARTY_PUB.Delete_Contact(
2272 p_api_version => l_api_version,
2273 p_init_msg_list => l_init_msg_list,
2274 x_return_status => l_return_status,
2275 x_msg_count => l_msg_count,
2276 x_msg_data => l_msg_data,
2277 p_ctcv_tbl => l_ctcv_tbl_in);
2278
2279 if not (l_return_status = OKC_API.G_RET_STS_SUCCESS)
2280 then
2281 return;
2282 end if;
2283 END IF;
2284 IF NOT l_cplv_tbl_in.COUNT=0
2285 THEN
2286 OKC_CONTRACT_PARTY_PUB.Delete_k_Party_Role(
2287 p_api_version => l_api_version,
2288 p_init_msg_list => l_init_msg_list,
2289 x_return_status => l_return_status,
2290 x_msg_count => l_msg_count,
2291 x_msg_data => l_msg_data,
2292 p_cplv_tbl => l_cplv_tbl_in);
2293
2294 if not (l_return_status = OKC_API.G_RET_STS_SUCCESS)
2295 then
2296 return;
2297 end if;
2298 END IF;
2299
2300 --BUG#4066428 HKAMDAR 29-Dec-2004 Part 2
2301 --/Rules Migration/
2302 Is_rule_allowed(l_chr_id,
2303 'RUL',
2304 lx_return_status,
2305 l_meaning);
2306
2307 IF lx_return_status = 'Y' Then
2308 -- End BUG#4066428 Part 2
2309
2310 IF NOT l_rulv_tbl_in.COUNT=0
2311 THEN
2312
2313 okc_Rule_pub.delete_Rule (
2314 p_api_version => l_api_version,
2315 p_init_msg_list => l_init_msg_list,
2316 x_return_status => l_return_status,
2317 x_msg_count => l_msg_count,
2318 x_msg_data => l_msg_data,
2319 p_rulv_tbl => l_rulv_tbl_in);
2320
2321 If not (l_return_status = OKC_API.G_RET_STS_SUCCESS)
2322 THEN
2323
2324 IF l_msg_count > 0
2325 THEN
2326 FOR i in 1..l_msg_count
2327 LOOP
2328 fnd_msg_pub.get (p_msg_index => -1,
2329 p_encoded => 'T', -- OKC$APPLICATION.GET_FALSE,
2330 p_data => l_msg_data,
2331 p_msg_index_out => l_msg_index_out);
2332 END LOOP;
2333 END IF;
2334 RAISE e_Error;
2335 End If;
2336 END IF;
2337
2338 IF NOT l_rgpv_tbl_in.COUNT=0
2339 THEN
2340 okc_Rule_pub.delete_Rule_group (
2341 p_api_version => l_api_version,
2342 p_init_msg_list => l_init_msg_list,
2343 x_return_status => l_return_status,
2344 x_msg_count => l_msg_count,
2345 x_msg_data => l_msg_data,
2346 p_rgpv_tbl => l_rgpv_tbl_in);
2347
2348 if not (l_return_status = OKC_API.G_RET_STS_SUCCESS)
2349 then
2350 return;
2351 end if;
2352 END IF;
2353
2354 END IF; --BUG#4066428 HKAMDAR 29-Dec-2004 Part 3
2355
2356 IF NOT l_cimv_tbl_in.COUNT=0
2357 THEN
2358 okc_contract_ITEM_pub.delete_Contract_ITEM (
2359 p_api_version => l_api_version,
2360 p_init_msg_list => l_init_msg_list,
2361 x_return_status => l_return_status,
2362 x_msg_count => l_msg_count,
2363 x_msg_data => l_msg_data,
2364 p_cimv_tbl => l_cimv_tbl_in);
2365
2366 IF nvl(l_return_status,'*') <> 'S'
2367 THEN
2368 IF l_msg_count > 0
2369 THEN
2370 FOR i in 1..l_msg_count
2371 LOOP
2372 fnd_msg_pub.get (p_msg_index => -1,
2373 p_encoded => 'T', -- OKC$APPLICATION.GET_FALSE,
2374 p_data => l_msg_data,
2375 p_msg_index_out => l_msg_index_out);
2376 END LOOP;
2377 END IF;
2378
2379 RAISE e_Error;
2380 END IF;
2381 END IF;
2382
2383 IF NOT l_clev_tbl_in.COUNT=0
2384 THEN
2385 okc_contract_pub.delete_contract_line (
2386 p_api_version => l_api_version,
2387 p_init_msg_list => l_init_msg_list,
2388 x_return_status => l_return_status,
2389 x_msg_count => l_msg_count,
2390 x_msg_data => l_msg_data,
2391 p_clev_tbl => l_clev_tbl_in);
2392
2393 IF nvl(l_return_status,'*') <> 'S'
2394 THEN
2395 IF l_msg_count > 0
2396 THEN
2397 FOR i in 1..l_msg_count
2398 LOOP
2399 fnd_msg_pub.get (p_msg_index => -1,
2400 p_encoded => 'T', -- OKC$APPLICATION.GET_FALSE,
2401 p_data => l_msg_data,
2402 p_msg_index_out => l_msg_index_out);
2403 END LOOP;
2404 END IF;
2405 RAISE e_Error;
2406 END IF;
2407 END IF;
2408
2409 EXCEPTION
2410 WHEN e_Error THEN
2411 -- notify caller of an error as UNEXPETED error
2412 x_msg_count :=l_msg_count;
2413 x_msg_data:=l_msg_data;
2414 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2415 (
2416 l_api_name,
2417 'Delete_Contract_Line',
2418 'OKC_API.G_RET_STS_ERROR',
2419 l_msg_count,
2420 l_msg_data,
2421 '_PVT'
2422 );
2423 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2424 x_msg_count :=l_msg_count;
2425 x_msg_data:=l_msg_data;
2426 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2427 (
2428 l_api_name,
2429 'Delete_Contract_Line',
2430 'OKC_API.G_RET_STS_ERROR',
2431 l_msg_count,
2432 l_msg_data,
2433 '_PVT'
2434 );
2435 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2436 x_msg_count :=l_msg_count;
2437 x_msg_data:=l_msg_data;
2438 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2439 (
2440 l_api_name,
2441 'Delete_Contract_Line',
2442 'OKC_API.G_RET_STS_UNEXP_ERROR',
2443 l_msg_count,
2444 l_msg_data,
2445 '_PVT'
2446 );
2447 WHEN OTHERS THEN
2448 x_msg_count :=l_msg_count;
2449 OKC_API.SET_MESSAGE(
2450 p_app_name => g_app_name,
2451 p_msg_name => g_unexpected_error,
2452 p_token1 => g_sqlcode_token,
2453 p_token1_value => sqlcode,
2454 p_token2 => g_sqlerrm_token,
2455 p_token2_value => sqlerrm);
2456 -- notify caller of an error as UNEXPETED error
2457 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2458
2459 END delete_contract_line;
2460
2461 PROCEDURE force_delete_contract_line(
2462 p_api_version IN NUMBER,
2463 p_init_msg_list IN VARCHAR2 ,
2464 x_return_status OUT NOCOPY VARCHAR2,
2465 x_msg_count OUT NOCOPY NUMBER,
2466 x_msg_data OUT NOCOPY VARCHAR2,
2467 p_line_id IN NUMBER) IS
2468
2469 l_Cov_cle_Id NUMBER;
2470 l_Item_id NUMBER;
2471 l_contact_Id NUMBER;
2472 l_RGP_Id NUMBER;
2473 l_Rule_Id NUMBER;
2474 l_cle_Id NUMBER;
2475 l_chr_id NUMBER;
2476 v_Index Binary_Integer;
2477
2478 CURSOR DNZ_Cur(p_id NUMBER) IS
2479 SELECT dnz_chr_id
2480 FROM okc_k_lines_b
2481 WHERE id = p_id;
2482 CURSOR Child_Cur1(P_Parent_Id IN NUMBER)
2483 IS SELECT ID
2484 FROM Okc_K_Lines_b
2485 WHERE cle_Id=P_Parent_Id;
2486 CURSOR Child_Cur2(P_Parent_Id IN NUMBER)
2487 IS SELECT ID
2488 FROM Okc_K_Lines_b
2489 WHERE cle_Id=P_Parent_Id;
2490 CURSOR Child_Cur3(P_Parent_Id IN NUMBER)
2491 IS SELECT ID
2492 FROM Okc_K_Lines_b
2493 WHERE cle_Id=P_Parent_Id;
2494 CURSOR Child_Cur4(P_Parent_Id IN NUMBER)
2495 IS SELECT ID
2496 FROM Okc_K_Lines_b
2497 WHERE cle_Id=P_Parent_Id;
2498 CURSOR Child_Cur5(P_Parent_Id IN NUMBER)
2499 IS SELECT ID
2500 FROM Okc_K_Lines_b
2501 WHERE cle_Id=P_Parent_Id;
2502
2503 CURSOR Item_Cur(P_Line_Id IN NUMBER)
2504 IS SELECT ID
2505 FROM Okc_K_ITEMS
2506 WHERE cle_Id=P_Line_Id;
2507
2508 CURSOR RT_Cur(P_Rule_Id IN NUMBER)
2509 IS SELECT Tve_ID
2510 FROM OKC_React_Intervals
2511 WHERE Rul_Id =P_Rule_Id;
2512
2513 CURSOR Kprl_Cur(P_cle_Id IN NUMBER) IS
2514 SELECT ID FROM OKC_K_PARTY_ROLES_B
2515 WHERE dnz_chr_id = l_chr_id
2516 AND cle_Id=P_cle_Id;
2517
2518 CURSOR Contact_Cur(P_cpl_Id IN NUMBER) IS
2519 SELECT ID FROM OKC_CONTACTS
2520 WHERE cpl_Id=P_cpl_Id;
2521
2522 CURSOR TRule_Cur( P_Rgp_Id IN NUMBER,
2523 P_Rule_Type IN Varchar2) IS
2524 SELECT ID FROM OKC_RULES_B
2525 WHERE Rgp_Id=P_Rgp_Id
2526 AND Rule_Information_category=P_rule_Type;
2527
2528 CURSOR Rl_Cur(P_Rgp_Id IN NUMBER) IS
2529 SELECT ID FROM OKC_RULES_B
2530 WHERE Rgp_Id=P_Rgp_Id;
2531
2532 CURSOR Rgp_Cur(P_cle_Id IN NUMBER) IS
2533 SELECT ID FROM OKC_RULE_GROUPS_B
2534 WHERE cle_Id=P_Cle_Id;
2535
2536 CURSOR Relobj_Cur(P_Cle_Id IN NUMBER) IS
2537 SELECT Id FROM OKC_K_REL_OBJS
2538 WHERE cle_Id = P_cle_Id;
2539
2540 n NUMBER:=0;
2541 l_clev_tbl_in okc_contract_pub.clev_tbl_type;
2542 l_clev_tbl_tmp okc_contract_pub.clev_tbl_type;
2543 l_rgpv_tbl_in okc_rule_pub.rgpv_tbl_type;
2544 l_rulv_tbl_in okc_rule_pub.rulv_tbl_type;
2545 l_cimv_tbl_in okc_Contract_Item_Pub.cimv_tbl_TYPE;
2546 l_ctcv_tbl_in okc_contract_party_pub.ctcv_tbl_type;
2547 l_cplv_tbl_in okc_contract_party_pub.cplv_tbl_type;
2548 l_crjv_tbl_in okc_k_rel_objs_pub.crjv_tbl_type;
2549 l_api_version CONSTANT NUMBER := 1.0;
2550 l_init_msg_list CONSTANT VARCHAR2(1) := 'T';
2551 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2552 l_msg_count NUMBER;
2553 l_msg_data VARCHAR2(2000):=null;
2554 l_msg_index_out Number;
2555 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Contract_Line';
2556 l_catv_tbl_in okc_k_article_pub.catv_tbl_type;
2557 e_error Exception;
2558 c_clev NUMBER:=1;
2559 c_rulv NUMBER:=1;
2560 c_rgpv NUMBER:=1;
2561 c_cimv NUMBER:=1;
2562 c_ctcv NUMBER:=1;
2563 c_catv NUMBER:=1;
2564 c_cplv NUMBER:=1;
2565 c_crjv NUMBER:=1;
2566 l_lse_Id NUMBER;
2567
2568 ---------------------------------------------------------------------------
2569 -- PROCEDURE Validate_Line_id
2570 ---------------------------------------------------------------------------
2571 PROCEDURE Validate_Line_id(
2572 p_line_id IN NUMBER,
2573 x_return_status OUT NOCOPY VARCHAR2) IS
2574 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2575 l_Count NUMBER;
2576 CURSOR Cur_Line(P_Line_Id IN NUMBER) IS
2577 SELECT COUNT(*) FROM OKC_K_LINES_B
2578 WHERE id=P_Line_Id;
2579 BEGIN
2580
2581 IF (l_debug = 'Y') THEN
2582 okc_debug.log('100: Validate_Line_id starts', 2);
2583 END IF;
2584 IF P_Line_id = OKC_API.G_MISS_NUM OR
2585 P_Line_Id IS NULL
2586 THEN
2587 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'P_Line_Id');
2588
2589 l_return_status := OKC_API.G_RET_STS_ERROR;
2590 END IF;
2591
2592 IF (l_debug = 'Y') THEN
2593 okc_debug.log('200: l_return_status = ' || l_return_status, 2);
2594 END IF;
2595
2596 OPEN Cur_Line(P_LIne_Id);
2597 FETCH Cur_Line INTO l_Count;
2598 CLOSE Cur_Line;
2599 IF NOT l_Count = 1
2600 THEN
2601 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'P_Line_Id');
2602
2603 l_return_status := OKC_API.G_RET_STS_ERROR;
2604 END IF;
2605 x_return_status := l_return_status;
2606 IF (l_debug = 'Y') THEN
2607 okc_debug.log('300: validate_line_id ends', 2);
2608 END IF;
2609 EXCEPTION
2610 WHEN OTHERS THEN
2611 -- store SQL error message on message stack for caller
2612 OKC_API.set_message(G_APP_NAME,
2613 G_UNEXPECTED_ERROR,
2614 G_SQLCODE_TOKEN,
2615 SQLCODE,
2616 G_SQLERRM_TOKEN,
2617 SQLERRM);
2618 -- notify caller of an UNEXPECTED error
2619 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2620 IF (l_debug = 'Y') THEN
2621 okc_debug.log('300: WHEN OTHERS EXCEPTION', 2);
2622 END IF;
2623 END Validate_Line_id;
2624
2625 PROCEDURE delete_rule(
2626 p_api_version IN NUMBER,
2627 p_init_msg_list IN VARCHAR2 ,
2628 x_return_status OUT NOCOPY VARCHAR2,
2629 x_msg_count OUT NOCOPY NUMBER,
2630 x_msg_data OUT NOCOPY VARCHAR2,
2631 p_rulv_rec IN OKC_RULE_PUB.rulv_rec_type) IS
2632
2633
2634 --x_return_status VARCHAR2 := 'S';
2635 l_dummy_var VARCHAR(1) := NULL;
2636 i NUMBER := 0;
2637 CURSOR l_ctiv_csr IS
2638 SELECT *
2639 FROM OKC_COVER_TIMES_V ctiv
2640 WHERE ctiv.RUL_ID = p_rulv_rec.id;
2641
2642 CURSOR l_atnv_csr IS
2643 SELECT 'x'
2644 FROM OKC_ARTICLE_TRANS_V atnv
2645 WHERE atnv.RUL_ID = p_rulv_rec.id;
2646
2647 CURSOR l_rilv_csr IS
2648 SELECT *
2649 FROM OKC_REACT_INTERVALS_V rilv
2650 WHERE rilv.RUL_ID = p_rulv_rec.id;
2651 l_ctiv_tbl OKC_CTI_PVT.ctiv_tbl_type;
2652 l_rilv_tbl OKC_RIL_PVT.rilv_tbl_type;
2653
2654 L_RIC OKC_RULES_V.RULE_INFORMATION_CATEGORY%TYPE;
2655
2656 CURSOR l_ric_csr IS
2657 SELECT RULE_INFORMATION_CATEGORY
2658 FROM OKC_RULES_B
2659 WHERE ID = p_rulv_rec.id;
2660 l_col_vals okc_time_util_pub.t_col_vals;
2661
2662 --
2663 --l_proc varchar2(72) := g_package||'delete_rule';
2664 l_proc varchar2(72) := 'delete_rule';
2665 --
2666 BEGIN
2667 IF (l_debug = 'Y') THEN
2668 okc_debug.log('10: starting delete rule', 2);
2669 END IF;
2670
2671 OPEN l_atnv_csr;
2672 FETCH l_atnv_csr into l_dummy_var;
2673 CLOSE l_atnv_csr;
2674 IF l_dummy_var = 'x' THEN
2675 IF (l_debug = 'Y') THEN
2676 okc_debug.log('G_EXCEPTION_CANNOT_DELETE');
2677 END IF;
2678 --RAISE G_EXCEPTION_CANNOT_DELETE;
2679 END IF;
2680
2681 --populate the Foreign key of the detail
2682 FOR l_ctiv_rec in l_ctiv_csr LOOP
2683 i := i + 1;
2684 l_ctiv_tbl(i).rul_id := l_ctiv_rec.rul_id;
2685 l_ctiv_tbl(i).tve_id := l_ctiv_rec.tve_id;
2686 END LOOP;
2687
2688 IF i > 0 THEN
2689 IF (l_debug = 'Y') THEN
2690 okc_debug.log('100: calling OKC_RULE_PUB.delete_cover_time', 2);
2691 END IF;
2692 --Delete the details
2693 -- call Public delete procedure
2694 OKC_RULE_PUB.delete_cover_time(
2695 p_api_version => p_api_version,
2696 p_init_msg_list => p_init_msg_list,
2697 x_return_status => x_return_status,
2698 x_msg_count => x_msg_count,
2699 x_msg_data => x_msg_data,
2700 p_ctiv_tbl => l_ctiv_tbl);
2701
2702 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
2703 IF (l_debug = 'Y') THEN
2704 okc_debug.log('200: x_return_status = ' || x_return_status, 2);
2705 END IF;
2706 -- stop delete process
2707 RETURN;
2708 END IF;
2709 END IF;
2710
2711 i := 0;
2712 --populate the Foreign key of the detail
2713 FOR l_rilv_rec in l_rilv_csr LOOP
2714 i := i + 1;
2715 l_rilv_tbl(i).rul_id := l_rilv_rec.rul_id;
2716 l_rilv_tbl(i).tve_id := l_rilv_rec.tve_id;
2717 END LOOP;
2718
2719 IF i > 0 THEN
2720 --Delete the details
2721 -- call Public delete procedure
2722 IF (l_debug = 'Y') THEN
2723 okc_debug.log('300: calling OKC_RULE_PUB.delete_react_interval', 2);
2724 END IF;
2725 OKC_RULE_PUB.delete_react_interval(
2726 p_api_version => p_api_version,
2727 p_init_msg_list => p_init_msg_list,
2728 x_return_status => x_return_status,
2729 x_msg_count => x_msg_count,
2730 x_msg_data => x_msg_data,
2731 p_rilv_tbl => l_rilv_tbl);
2732
2733 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
2734 IF (l_debug = 'Y') THEN
2735 okc_debug.log('300: x_return_status = ' || x_return_status, 2);
2736 END IF;
2737 -- stop delete process
2738 RETURN;
2739 END IF;
2740 END IF;
2741 /*
2742 --
2743 -- added for tve_id
2744 --
2745 open l_ric_csr;
2746 fetch l_ric_csr into L_RIC;
2747 close l_ric_csr;
2748
2749 -- /striping/
2750 p_appl_id := okc_rld_pvt.get_appl_id(L_RIC);
2751 p_dff_name := okc_rld_pvt.get_dff_name(L_RIC);
2752
2753 -- okc_time_util_pub.get_dff_column_values( p_app_id => 510, -- /striping/
2754 okc_time_util_pub.get_dff_column_values( p_app_id => p_appl_id,
2755 -- p_dff_name => 'OKC Rule Developer DF', -- /striping/
2756 p_dff_name => p_dff_name,
2757 p_rdf_code => l_ric,
2758 p_fvs_name =>'OKC_TIMEVALUES',
2759 p_rule_id =>p_rulv_rec.id,
2760 p_col_vals => l_col_vals,
2761 p_no_of_cols =>i );
2762 if (l_col_vals.COUNT>0) then
2763 i := l_col_vals.FIRST;
2764 LOOP
2765 IF (l_debug = 'Y') THEN
2766 okc_debug.log('600: calling okc_time_pub.delete_timevalues_n_tasks', 2);
2767 END IF;
2768 if (l_col_vals(i).col_value is not NULL) then
2769 okc_time_pub.delete_timevalues_n_tasks(
2770 p_api_version => p_api_version,
2771 p_init_msg_list => p_init_msg_list,
2772 x_return_status => x_return_status,
2773 x_msg_count => x_msg_count,
2774 x_msg_data => x_msg_data,
2775 p_tve_id => l_col_vals(i).col_value);
2776 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
2777 IF (l_debug = 'Y') THEN
2778 okc_debug.log('700: x_return_status = ' || x_return_status, 2);
2779 END IF;
2780 -- stop delete process
2781 RETURN;
2782 END IF;
2783 end if;
2784 EXIT WHEN (i=l_col_vals.LAST);
2785 i := l_col_vals.NEXT(i);
2786 END LOOP;
2787 end if;
2788 */
2789 --
2790 -- /tve_id
2791 --
2792 IF (l_debug = 'Y') THEN
2793 okc_debug.log('800: calling OKC_RUL_PVT.delete_row', 2);
2794 END IF;
2795 OKC_RUL_PVT.delete_row(
2796 p_api_version => p_api_version,
2797 p_init_msg_list => p_init_msg_list,
2798 x_return_status => x_return_status,
2799 x_msg_count => x_msg_count,
2800 x_msg_data => x_msg_data,
2801 p_rulv_rec => p_rulv_rec);
2802
2803 IF (l_debug = 'Y') THEN
2804 okc_debug.log('900: x_return_status = ' || x_return_status, 2);
2805 END IF;
2806
2807 EXCEPTION
2808 /*
2809 WHEN G_EXCEPTION_CANNOT_DELETE THEN
2810
2811
2812 -- store SQL error message on message stack
2813 OKC_API.SET_MESSAGE(
2814 p_app_name => G_APP_NAME,
2815 p_msg_name => G_CANNOT_DELETE_MASTER);
2816 -- notify caller of an error
2817 x_return_status := OKC_API.G_RET_STS_ERROR;
2818
2819 */
2820 WHEN OTHERS THEN
2821 -- store SQL error message on message stack
2822 OKC_API.SET_MESSAGE(
2823 p_app_name => G_APP_NAME,
2824 p_msg_name => G_UNEXPECTED_ERROR,
2825 p_token1 => G_SQLCODE_TOKEN,
2826 p_token1_value => SQLCODE,
2827 p_token2 => G_SQLERRM_TOKEN,
2828 p_token2_value => SQLERRM);
2829 -- notify caller of an error as UNEXPETED error
2830 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2831 -- verify that cursor was closed
2832 IF l_ctiv_csr%ISOPEN THEN
2833 CLOSE l_ctiv_csr;
2834 END IF;
2835 IF l_atnv_csr%ISOPEN THEN
2836 CLOSE l_atnv_csr;
2837 END IF;
2838 IF l_rilv_csr%ISOPEN THEN
2839 CLOSE l_rilv_csr;
2840 END IF;
2841 END delete_rule;
2842
2843 BEGIN
2844 IF (l_debug = 'Y') THEN
2845 okc_debug.log('100: starting force delete', 2);
2846 okc_debug.log('200: cle_id=' || to_char(p_line_id),2);
2847 END IF;
2848 x_return_status:=OKC_API.G_RET_STS_SUCCESS;
2849 Validate_Line_id(p_line_id,l_return_status);
2850 IF NOT l_Return_Status ='S'
2851 THEN
2852 x_return_status := l_return_status;
2853 IF (l_debug = 'Y') THEN
2854 okc_debug.log('300: Validate_Line_id failed', 2);
2855 END IF;
2856 RETURN;
2857 END IF;
2858
2859 -- Get header id
2860 open DNZ_Cur(p_line_id);
2861 fetch DNZ_Cur into l_chr_id;
2862 close DNZ_Cur;
2863
2864 l_clev_tbl_tmp(c_clev).ID:=P_Line_Id;
2865 c_clev:=c_clev+1;
2866 FOR Child_Rec1 IN Child_Cur1(P_Line_Id)
2867 LOOP
2868 l_clev_tbl_tmp(c_clev).ID:=Child_Rec1.ID;
2869 c_clev:=c_clev+1;
2870 FOR Child_Rec2 IN Child_Cur2(Child_Rec1.Id)
2871 LOOP
2872 l_clev_tbl_tmp(c_clev).ID:=Child_Rec2.Id;
2873 c_clev:=c_clev+1;
2874 FOR Child_Rec3 IN Child_Cur3(Child_Rec2.Id)
2875 LOOP
2876 l_clev_tbl_tmp(c_clev).ID:=Child_Rec3.Id;
2877 c_clev:=c_clev+1;
2878 FOR Child_Rec4 IN Child_Cur4(Child_Rec3.Id)
2879 LOOP
2880 l_clev_tbl_tmp(c_clev).ID:=Child_Rec4.Id;
2881 c_clev:=c_clev+1;
2882 FOR Child_Rec5 IN Child_Cur5(Child_Rec4.Id)
2883 LOOP
2884 l_clev_tbl_tmp(c_clev).ID:=Child_Rec5.Id;
2885 c_clev:=c_clev+1;
2886 END LOOP;
2887 END LOOP;
2888 END LOOP;
2889 END LOOP;
2890 END LOOP;
2891 c_clev:=1;
2892 FOR v_Index IN REVERSE l_clev_tbl_tmp.FIRST .. l_clev_tbl_tmp.LAST
2893 LOOP
2894 l_clev_tbl_in(c_clev).ID:= l_clev_tbl_tmp(v_Index).ID;
2895 c_clev:=c_Clev+1;
2896 END LOOP;
2897 -- Get Relational Objects Linked to the lines
2898 FOR v_Index IN l_clev_tbl_in.FIRST .. l_clev_tbl_in.LAST
2899 LOOP
2900 FOR RelObj_REC IN RelObj_Cur(l_clev_tbl_in(v_Index).ID)
2901 LOOP
2902 l_crjv_tbl_in(c_crjv).ID:= RelObj_Rec.Id;
2903 c_crjv:=c_crjv+1;
2904 END LOOP;
2905 END LOOP;
2906
2907 -- Get Rule Groups and Rules
2908 FOR v_index IN l_clev_tbl_in.FIRST .. l_clev_tbl_In.LAST
2909 LOOP
2910 FOR rgp_rec IN Rgp_Cur(l_clev_tbl_in(v_index).id) LOOP
2911 l_Rgp_Id := rgp_rec.id;
2912 l_rgpv_tbl_in(c_rgpv).Id:=l_Rgp_Id;
2913 c_rgpv:=c_Rgpv+1;
2914 FOR Rl_Rec IN Rl_Cur(l_Rgp_Id)
2915 LOOP
2916 l_Rulv_tbl_in(c_rulv).ID:=Rl_Rec.ID;
2917 c_rulv:=c_rulv+1;
2918 END LOOP;
2919 END LOOP;
2920 END LOOP;
2921
2922 -- Get Items
2923 FOR v_Index IN l_clev_tbl_in.FIRST .. l_clev_tbl_in.LAST
2924 LOOP
2925 FOR ITEM_REC IN Item_Cur(l_clev_tbl_in(v_Index).ID)
2926 LOOP
2927 l_cimv_tbl_in(c_cimv).ID:= Item_Rec.Id;
2928 c_cimv:=c_cimv+1;
2929 END LOOP;
2930 END LOOP;
2931 -- GET K Party Roles and Contacts
2932 FOR v_Index IN l_clev_tbl_in.FIRST .. l_clev_tbl_in.LAST
2933 LOOP
2934 FOR Kprl_REC IN Kprl_Cur(l_clev_tbl_in(v_Index).ID)
2935 LOOP
2936 l_cplv_tbl_in(c_cplv).id:=Kprl_Rec.ID;
2937 c_cplv:=c_cplv+1;
2938 FOR Contact_Rec IN Contact_Cur(Kprl_Rec.id)
2939 LOOP
2940 l_ctcv_tbl_in(c_ctcv).id:= Contact_Rec.Id;
2941 c_ctcv:=c_ctcv+1;
2942 END LOOP;
2943 END LOOP;
2944 END LOOP;
2945
2946 IF (l_debug = 'Y') THEN
2947 okc_debug.log('400: calling OKC_K_REL_OBJS_PUB.Delete_Row', 2);
2948 END IF;
2949 IF NOT l_crjv_tbl_in.COUNT=0
2950 THEN
2951
2952 OKC_K_REL_OBJS_PUB.Delete_Row(
2953 p_api_version => l_api_version,
2954 p_init_msg_list => l_init_msg_list,
2955 x_return_status => l_return_status,
2956 x_msg_count => l_msg_count,
2957 x_msg_data => l_msg_data,
2958 p_crjv_tbl => l_crjv_tbl_in);
2959
2960 IF (l_debug = 'Y') THEN
2961 okc_debug.log('500: l_return_status = ' || l_return_status, 2);
2962 END IF;
2963
2964 if not (l_return_status = OKC_API.G_RET_STS_SUCCESS)
2965 then
2966 x_return_status := l_return_status;
2967 return;
2968 end if;
2969 END IF;
2970
2971 IF (l_debug = 'Y') THEN
2972 okc_debug.log('600: calling OKC_CONTRACT_PARTY_PUB.Delete_Contact', 2);
2973 END IF;
2974
2975 IF NOT l_ctcv_tbl_in.COUNT=0
2976 THEN
2977 OKC_CONTRACT_PARTY_PUB.Delete_Contact(
2978 p_api_version => l_api_version,
2979 p_init_msg_list => l_init_msg_list,
2980 x_return_status => l_return_status,
2981 x_msg_count => l_msg_count,
2982 x_msg_data => l_msg_data,
2983 p_ctcv_tbl => l_ctcv_tbl_in);
2984
2985 IF (l_debug = 'Y') THEN
2986 okc_debug.log('700: l_return_status = ' || l_return_status, 2);
2987 END IF;
2988
2989 if not (l_return_status = OKC_API.G_RET_STS_SUCCESS)
2990 then
2991 x_return_status := l_return_status;
2992 return;
2993 end if;
2994 END IF;
2995
2996 IF (l_debug = 'Y') THEN
2997 okc_debug.log('800: calling OKC_CONTRACT_PARTY_PUB.Delete_k_Party_Role', 2);
2998 END IF;
2999
3000 IF NOT l_cplv_tbl_in.COUNT=0
3001 THEN
3002 OKC_CONTRACT_PARTY_PUB.Delete_k_Party_Role(
3003 p_api_version => l_api_version,
3004 p_init_msg_list => l_init_msg_list,
3005 x_return_status => l_return_status,
3006 x_msg_count => l_msg_count,
3007 x_msg_data => l_msg_data,
3008 p_cplv_tbl => l_cplv_tbl_in);
3009
3010 IF (l_debug = 'Y') THEN
3011 okc_debug.log('900: l_return_status = ' || l_return_status, 2);
3012 END IF;
3013 if not (l_return_status = OKC_API.G_RET_STS_SUCCESS)
3014 then
3015 x_return_status := l_return_status;
3016 return;
3017 end if;
3018 END IF;
3019
3020 IF (l_debug = 'Y') THEN
3021 okc_debug.log('1000: calling okc_Rule_pub.delete_Rule', 2);
3022 END IF;
3023 IF NOT l_rulv_tbl_in.COUNT=0
3024 THEN
3025 FOR i IN l_rulv_tbl_in.FIRST..l_rulv_tbl_in.LAST
3026 LOOP
3027
3028 --okc_Rule_pub.delete_Rule (
3029 delete_Rule(
3030 p_api_version => l_api_version,
3031 p_init_msg_list => l_init_msg_list,
3032 x_return_status => l_return_status,
3033 x_msg_count => l_msg_count,
3034 x_msg_data => l_msg_data,
3035 p_rulv_rec => l_rulv_tbl_in(i));
3036
3037 IF (l_debug = 'Y') THEN
3038 okc_debug.log('1050: l_return_status = ' || l_return_status, 2);
3039 okc_debug.log('1100: calling OKC_CONTRACT_PARTY_PUB.Delete_Contact', 2);
3040 END IF;
3041
3042 if not (l_return_status = OKC_API.G_RET_STS_SUCCESS)
3043 THEN
3044 /*
3045 IF l_msg_count > 0
3046 THEN
3047 FOR i in 1..l_msg_count
3048 LOOP
3049 fnd_msg_pub.get (p_msg_index => -1,
3050 p_encoded => 'T', -- OKC$APPLICATION.GET_FALSE,
3051 p_data => l_msg_data,
3052 p_msg_index_out => l_msg_index_out);
3053 END LOOP;
3054 END IF;
3055 RAISE e_Error;
3056 */
3057
3058 x_return_status := l_return_status;
3059 return;
3060 END IF;
3061
3062 END LOOP;
3063
3064 END IF;
3065
3066 IF (l_debug = 'Y') THEN
3067 okc_debug.log('1200: calling okc_Rule_pub.delete_Rule_group', 2);
3068 END IF;
3069
3070 IF NOT l_rgpv_tbl_in.COUNT=0
3071 THEN
3072 okc_Rule_pub.delete_Rule_group (
3073 p_api_version => l_api_version,
3074 p_init_msg_list => l_init_msg_list,
3075 x_return_status => l_return_status,
3076 x_msg_count => l_msg_count,
3077 x_msg_data => l_msg_data,
3078 p_rgpv_tbl => l_rgpv_tbl_in);
3079
3080 IF (l_debug = 'Y') THEN
3081 okc_debug.log('1300: l_return_status = ' || l_return_status, 2);
3082 END IF;
3083
3084 if not (l_return_status = OKC_API.G_RET_STS_SUCCESS)
3085 then
3086 x_return_status := l_return_status;
3087 return;
3088 end if;
3089 END IF;
3090
3091 IF (l_debug = 'Y') THEN
3092 okc_debug.log('1400: calling okc_contract_ITEM_pub.delete_Contract_ITEM', 2);
3093 END IF;
3094
3095 IF NOT l_cimv_tbl_in.COUNT=0
3096 THEN
3097 okc_contract_ITEM_pub.delete_Contract_ITEM (
3098 p_api_version => l_api_version,
3099 p_init_msg_list => l_init_msg_list,
3100 x_return_status => l_return_status,
3101 x_msg_count => l_msg_count,
3102 x_msg_data => l_msg_data,
3103 p_cimv_tbl => l_cimv_tbl_in);
3104
3105 IF (l_debug = 'Y') THEN
3106 okc_debug.log('1500: l_return_status = ' || l_return_status, 2);
3107 END IF;
3108
3109 IF nvl(l_return_status,'*') <> 'S'
3110 THEN
3111 IF l_msg_count > 0
3112 THEN
3113 FOR i in 1..l_msg_count
3114 LOOP
3115 fnd_msg_pub.get (p_msg_index => -1,
3116 p_encoded => 'T', -- OKC$APPLICATION.GET_FALSE,
3117 p_data => l_msg_data,
3118 p_msg_index_out => l_msg_index_out);
3119 END LOOP;
3120 END IF;
3121 RAISE e_Error;
3122 END IF;
3123 END IF;
3124
3125 IF (l_debug = 'Y') THEN
3126 okc_debug.log('1400: calling okc_cle_pvt.force_delete_row', 2);
3127 END IF;
3128
3129 IF NOT l_clev_tbl_in.COUNT=0
3130 THEN
3131 okc_cle_pvt.force_delete_row (
3132 p_api_version => l_api_version,
3133 p_init_msg_list => l_init_msg_list,
3134 x_return_status => l_return_status,
3135 x_msg_count => l_msg_count,
3136 x_msg_data => l_msg_data,
3137 p_clev_tbl => l_clev_tbl_in);
3138
3139 IF (l_debug = 'Y') THEN
3140 okc_debug.log('1500: l_return_status = ' || l_return_status, 2);
3141 END IF;
3142
3143 -- if the above process is success, delete all ancestrys
3144 If (l_return_status = OKC_API.G_RET_STS_SUCCESS) Then
3145
3146 IF (l_debug = 'Y') THEN
3147 okc_debug.log('1600: calling delete_ancestry', 2);
3148 END IF;
3149 FOR v_Index IN l_clev_tbl_in.FIRST .. l_clev_tbl_in.LAST
3150 LOOP
3151 delete_ancestry(
3152 p_api_version => p_api_version,
3153 p_init_msg_list => p_init_msg_list,
3154 x_return_status => l_return_status,
3155 x_msg_count => l_msg_count,
3156 x_msg_data => l_msg_data,
3157 p_cle_id => l_clev_tbl_in(v_Index).id);
3158
3159 IF (l_debug = 'Y') THEN
3160 okc_debug.log('1700: l_return_status = ' || l_return_status, 2);
3161 END IF;
3162
3163 If (l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
3164 x_return_status := l_return_status;
3165 exit;
3166 End If;
3167 END LOOP;
3168
3169 End If;
3170
3171 IF nvl(l_return_status,'*') <> 'S'
3172 THEN
3173 IF l_msg_count > 0
3174 THEN
3175 FOR i in 1..l_msg_count
3176 LOOP
3177 fnd_msg_pub.get (p_msg_index => -1,
3178 p_encoded => 'T', -- OKC$APPLICATION.GET_FALSE,
3179 p_data => l_msg_data,
3180 p_msg_index_out => l_msg_index_out);
3181 END LOOP;
3182 END IF;
3183 RAISE e_Error;
3184 END IF;
3185 END IF;
3186
3187 IF (l_debug = 'Y') THEN
3188 okc_debug.log('1800: x_return_status = ' || x_return_status, 2);
3189 END IF;
3190
3191 EXCEPTION
3192 WHEN e_Error THEN
3193 IF (l_debug = 'Y') THEN
3194 okc_debug.log('1900: WHEN e_Error EXCEPTION', 2);
3195 END IF;
3196 -- notify caller of an error as UNEXPETED error
3197 x_msg_count :=l_msg_count;
3198 x_msg_data:=l_msg_data;
3199 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3200 (
3201 l_api_name,
3202 'Delete_Contract_Line',
3203 'OKC_API.G_RET_STS_ERROR',
3204 l_msg_count,
3205 l_msg_data,
3206 '_PVT'
3207 );
3208 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3209 IF (l_debug = 'Y') THEN
3210 okc_debug.log('1900: WHEN OKC_API.G_EXCEPTION_ERROR EXCEPTION', 2);
3211 END IF;
3212 x_msg_count :=l_msg_count;
3213 x_msg_data:=l_msg_data;
3214 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3215 (
3216 l_api_name,
3217 'Delete_Contract_Line',
3218 'OKC_API.G_RET_STS_ERROR',
3219 l_msg_count,
3220 l_msg_data,
3221 '_PVT'
3222 );
3223 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3224 IF (l_debug = 'Y') THEN
3225 okc_debug.log('2000: WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR EXCEPTION', 2);
3226 END IF;
3227 x_msg_count :=l_msg_count;
3228 x_msg_data:=l_msg_data;
3229 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3230 (
3231 l_api_name,
3232 'Force_Delete_K_Line',
3233 'OKC_API.G_RET_STS_UNEXP_ERROR',
3234 l_msg_count,
3235 l_msg_data,
3236 '_PVT'
3237 );
3238 WHEN OTHERS THEN
3239 IF (l_debug = 'Y') THEN
3240 okc_debug.log('2100: WHEN OTHERS EXCEPTION', 2);
3241 END IF;
3242 x_msg_count :=l_msg_count;
3243 OKC_API.SET_MESSAGE(
3244 p_app_name => g_app_name,
3245 p_msg_name => g_unexpected_error,
3246 p_token1 => g_sqlcode_token,
3247 p_token1_value => sqlcode,
3248 p_token2 => g_sqlerrm_token,
3249 p_token2_value => sqlerrm);
3250 -- notify caller of an error as UNEXPETED error
3251 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3252
3253 END force_delete_contract_line;
3254
3255 PROCEDURE lock_contract_line(
3256 p_api_version IN NUMBER,
3257 p_init_msg_list IN VARCHAR2 ,
3258 x_return_status OUT NOCOPY VARCHAR2,
3259 x_msg_count OUT NOCOPY NUMBER,
3260 x_msg_data OUT NOCOPY VARCHAR2,
3261 p_clev_rec IN OKC_CLE_PVT.clev_rec_type) IS
3262
3263 BEGIN
3264 OKC_CLE_PVT.Lock_Row(
3265 p_api_version => p_api_version,
3266 p_init_msg_list => p_init_msg_list,
3267 x_return_status => x_return_status,
3268 x_msg_count => x_msg_count,
3269 x_msg_data => x_msg_data,
3270 p_clev_rec => p_clev_rec);
3271 END lock_contract_line;
3272
3273 PROCEDURE lock_contract_line(
3274 p_api_version IN NUMBER,
3275 p_init_msg_list IN VARCHAR2 ,
3276 x_return_status OUT NOCOPY VARCHAR2,
3277 x_msg_count OUT NOCOPY NUMBER,
3278 x_msg_data OUT NOCOPY VARCHAR2,
3279 p_clev_tbl IN OKC_CLE_PVT.clev_tbl_type) IS
3280
3281 BEGIN
3282 OKC_CLE_PVT.Lock_Row(
3283 p_api_version => p_api_version,
3284 p_init_msg_list => p_init_msg_list,
3285 x_return_status => x_return_status,
3286 x_msg_count => x_msg_count,
3287 x_msg_data => x_msg_data,
3288 p_clev_tbl => p_clev_tbl);
3289 END lock_contract_line;
3290
3291 PROCEDURE validate_contract_line(
3292 p_api_version IN NUMBER,
3293 p_init_msg_list IN VARCHAR2 ,
3294 x_return_status OUT NOCOPY VARCHAR2,
3295 x_msg_count OUT NOCOPY NUMBER,
3296 x_msg_data OUT NOCOPY VARCHAR2,
3297 p_clev_rec IN OKC_CLE_PVT.clev_rec_type) IS
3298
3299 BEGIN
3300 OKC_CLE_PVT.Validate_Row(
3301 p_api_version => p_api_version,
3302 p_init_msg_list => p_init_msg_list,
3303 x_return_status => x_return_status,
3304 x_msg_count => x_msg_count,
3305 x_msg_data => x_msg_data,
3306 p_clev_rec => p_clev_rec);
3307 END validate_contract_line;
3308
3309 PROCEDURE validate_contract_line(
3310 p_api_version IN NUMBER,
3311 p_init_msg_list IN VARCHAR2 ,
3312 x_return_status OUT NOCOPY VARCHAR2,
3313 x_msg_count OUT NOCOPY NUMBER,
3314 x_msg_data OUT NOCOPY VARCHAR2,
3315 p_clev_tbl IN OKC_CLE_PVT.clev_tbl_type) IS
3316
3317 BEGIN
3318 OKC_CLE_PVT.Validate_Row(
3319 p_api_version => p_api_version,
3320 p_init_msg_list => p_init_msg_list,
3321 x_return_status => x_return_status,
3322 x_msg_count => x_msg_count,
3323 x_msg_data => x_msg_data,
3324 p_clev_tbl => p_clev_tbl);
3325 END validate_contract_line;
3326
3327 PROCEDURE create_governance(
3328 p_api_version IN NUMBER,
3329 p_init_msg_list IN VARCHAR2 ,
3330 x_return_status OUT NOCOPY VARCHAR2,
3331 x_msg_count OUT NOCOPY NUMBER,
3332 x_msg_data OUT NOCOPY VARCHAR2,
3333 p_gvev_rec IN OKC_GVE_PVT.gvev_rec_type,
3334 x_gvev_rec OUT NOCOPY OKC_GVE_PVT.gvev_rec_type) IS
3335
3336 BEGIN
3337 OKC_GVE_PVT.Insert_Row(
3338 p_api_version => p_api_version,
3339 p_init_msg_list => p_init_msg_list,
3340 x_return_status => x_return_status,
3341 x_msg_count => x_msg_count,
3342 x_msg_data => x_msg_data,
3343 p_gvev_rec => p_gvev_rec,
3344 x_gvev_rec => x_gvev_rec);
3345
3346 -- Update minor version
3347 If (x_return_status = OKC_API.G_RET_STS_SUCCESS) Then
3348 x_return_status := Update_Minor_Version(x_gvev_rec.dnz_chr_id);
3349 End If;
3350 END create_governance;
3351
3352 PROCEDURE create_governance(
3353 p_api_version IN NUMBER,
3354 p_init_msg_list IN VARCHAR2 ,
3355 x_return_status OUT NOCOPY VARCHAR2,
3356 x_msg_count OUT NOCOPY NUMBER,
3357 x_msg_data OUT NOCOPY VARCHAR2,
3358 p_gvev_tbl IN OKC_GVE_PVT.gvev_tbl_type,
3359 x_gvev_tbl OUT NOCOPY OKC_GVE_PVT.gvev_tbl_type) IS
3360
3361 BEGIN
3362 OKC_GVE_PVT.Insert_Row(
3363 p_api_version => p_api_version,
3364 p_init_msg_list => p_init_msg_list,
3365 x_return_status => x_return_status,
3366 x_msg_count => x_msg_count,
3367 x_msg_data => x_msg_data,
3368 p_gvev_tbl => p_gvev_tbl,
3369 x_gvev_tbl => x_gvev_tbl);
3370 END create_governance;
3371
3372 PROCEDURE update_governance(
3373 p_api_version IN NUMBER,
3374 p_init_msg_list IN VARCHAR2 ,
3375 x_return_status OUT NOCOPY VARCHAR2,
3376 x_msg_count OUT NOCOPY NUMBER,
3377 x_msg_data OUT NOCOPY VARCHAR2,
3378 p_gvev_rec IN OKC_GVE_PVT.gvev_rec_type,
3379 x_gvev_rec OUT NOCOPY OKC_GVE_PVT.gvev_rec_type) IS
3380
3381 BEGIN
3382 OKC_GVE_PVT.Update_Row(
3383 p_api_version => p_api_version,
3384 p_init_msg_list => p_init_msg_list,
3385 x_return_status => x_return_status,
3386 x_msg_count => x_msg_count,
3387 x_msg_data => x_msg_data,
3388 p_gvev_rec => p_gvev_rec,
3389 x_gvev_rec => x_gvev_rec);
3390
3391 -- Update minor version
3392 If (x_return_status = OKC_API.G_RET_STS_SUCCESS) Then
3393 x_return_status := Update_Minor_Version(x_gvev_rec.dnz_chr_id);
3394 End If;
3395 END update_governance;
3396
3397 PROCEDURE update_governance(
3398 p_api_version IN NUMBER,
3399 p_init_msg_list IN VARCHAR2 ,
3400 x_return_status OUT NOCOPY VARCHAR2,
3401 x_msg_count OUT NOCOPY NUMBER,
3402 x_msg_data OUT NOCOPY VARCHAR2,
3403 p_gvev_tbl IN OKC_GVE_PVT.gvev_tbl_type,
3404 x_gvev_tbl OUT NOCOPY OKC_GVE_PVT.gvev_tbl_type) IS
3405
3406 BEGIN
3407 OKC_GVE_PVT.Update_Row(
3408 p_api_version => p_api_version,
3409 p_init_msg_list => p_init_msg_list,
3410 x_return_status => x_return_status,
3411 x_msg_count => x_msg_count,
3412 x_msg_data => x_msg_data,
3413 p_gvev_tbl => p_gvev_tbl,
3414 x_gvev_tbl => x_gvev_tbl);
3415 END update_governance;
3416
3417 PROCEDURE delete_governance(
3418 p_api_version IN NUMBER,
3419 p_init_msg_list IN VARCHAR2 ,
3420 x_return_status OUT NOCOPY VARCHAR2,
3421 x_msg_count OUT NOCOPY NUMBER,
3422 x_msg_data OUT NOCOPY VARCHAR2,
3423 p_gvev_rec IN OKC_GVE_PVT.gvev_rec_type) IS
3424
3425 l_chr_id NUMBER;
3426 Cursor l_gvev_csr Is
3427 SELECT dnz_chr_id
3428 FROM OKC_GOVERNANCES
3429 WHERE id = p_gvev_rec.id;
3430 BEGIN
3431 Open l_gvev_csr;
3432 Fetch l_gvev_csr into l_chr_id;
3433 Close l_gvev_csr;
3434
3435 OKC_GVE_PVT.Delete_Row(
3436 p_api_version => p_api_version,
3437 p_init_msg_list => p_init_msg_list,
3438 x_return_status => x_return_status,
3439 x_msg_count => x_msg_count,
3440 x_msg_data => x_msg_data,
3441 p_gvev_rec => p_gvev_rec);
3442
3443 -- Update minor version
3444 If (x_return_status = OKC_API.G_RET_STS_SUCCESS) Then
3445 x_return_status := Update_Minor_Version(l_chr_id);
3446 End If;
3447 END delete_governance;
3448
3449 PROCEDURE delete_governance(
3450 p_api_version IN NUMBER,
3451 p_init_msg_list IN VARCHAR2 ,
3452 x_return_status OUT NOCOPY VARCHAR2,
3453 x_msg_count OUT NOCOPY NUMBER,
3454 x_msg_data OUT NOCOPY VARCHAR2,
3455 p_gvev_tbl IN OKC_GVE_PVT.gvev_tbl_type) IS
3456
3457 BEGIN
3458 OKC_GVE_PVT.Delete_Row(
3459 p_api_version => p_api_version,
3460 p_init_msg_list => p_init_msg_list,
3461 x_return_status => x_return_status,
3462 x_msg_count => x_msg_count,
3463 x_msg_data => x_msg_data,
3464 p_gvev_tbl => p_gvev_tbl);
3465 END delete_governance;
3466
3467 PROCEDURE lock_governance(
3468 p_api_version IN NUMBER,
3469 p_init_msg_list IN VARCHAR2 ,
3470 x_return_status OUT NOCOPY VARCHAR2,
3471 x_msg_count OUT NOCOPY NUMBER,
3472 x_msg_data OUT NOCOPY VARCHAR2,
3473 p_gvev_rec IN OKC_GVE_PVT.gvev_rec_type) IS
3474
3475 BEGIN
3476 OKC_GVE_PVT.Lock_Row(
3477 p_api_version => p_api_version,
3478 p_init_msg_list => p_init_msg_list,
3479 x_return_status => x_return_status,
3480 x_msg_count => x_msg_count,
3481 x_msg_data => x_msg_data,
3482 p_gvev_rec => p_gvev_rec);
3483 END lock_governance;
3484
3485 PROCEDURE lock_governance(
3486 p_api_version IN NUMBER,
3487 p_init_msg_list IN VARCHAR2 ,
3488 x_return_status OUT NOCOPY VARCHAR2,
3489 x_msg_count OUT NOCOPY NUMBER,
3490 x_msg_data OUT NOCOPY VARCHAR2,
3491 p_gvev_tbl IN OKC_GVE_PVT.gvev_tbl_type) IS
3492
3493 BEGIN
3494 OKC_GVE_PVT.Lock_Row(
3495 p_api_version => p_api_version,
3496 p_init_msg_list => p_init_msg_list,
3497 x_return_status => x_return_status,
3498 x_msg_count => x_msg_count,
3499 x_msg_data => x_msg_data,
3500 p_gvev_tbl => p_gvev_tbl);
3501 END lock_governance;
3502
3503 PROCEDURE validate_governance(
3504 p_api_version IN NUMBER,
3505 p_init_msg_list IN VARCHAR2 ,
3506 x_return_status OUT NOCOPY VARCHAR2,
3507 x_msg_count OUT NOCOPY NUMBER,
3508 x_msg_data OUT NOCOPY VARCHAR2,
3509 p_gvev_rec IN OKC_GVE_PVT.gvev_rec_type) IS
3510
3511 BEGIN
3512 OKC_GVE_PVT.Validate_Row(
3513 p_api_version => p_api_version,
3514 p_init_msg_list => p_init_msg_list,
3515 x_return_status => x_return_status,
3516 x_msg_count => x_msg_count,
3517 x_msg_data => x_msg_data,
3518 p_gvev_rec => p_gvev_rec);
3519 END validate_governance;
3520
3521 PROCEDURE validate_governance(
3522 p_api_version IN NUMBER,
3523 p_init_msg_list IN VARCHAR2 ,
3524 x_return_status OUT NOCOPY VARCHAR2,
3525 x_msg_count OUT NOCOPY NUMBER,
3526 x_msg_data OUT NOCOPY VARCHAR2,
3527 p_gvev_tbl IN OKC_GVE_PVT.gvev_tbl_type) IS
3528
3529 BEGIN
3530 OKC_GVE_PVT.Validate_Row(
3531 p_api_version => p_api_version,
3532 p_init_msg_list => p_init_msg_list,
3533 x_return_status => x_return_status,
3534 x_msg_count => x_msg_count,
3535 x_msg_data => x_msg_data,
3536 p_gvev_tbl => p_gvev_tbl);
3537 END validate_governance;
3538
3539 PROCEDURE create_contract_process(
3540 p_api_version IN NUMBER,
3541 p_init_msg_list IN VARCHAR2 ,
3542 x_return_status OUT NOCOPY VARCHAR2,
3543 x_msg_count OUT NOCOPY NUMBER,
3544 x_msg_data OUT NOCOPY VARCHAR2,
3545 p_cpsv_rec IN OKC_CPS_PVT.cpsv_rec_type,
3546 x_cpsv_rec OUT NOCOPY OKC_CPS_PVT.cpsv_rec_type) IS
3547
3548 BEGIN
3549 OKC_CPS_PVT.Insert_Row(
3550 p_api_version => p_api_version,
3551 p_init_msg_list => p_init_msg_list,
3552 x_return_status => x_return_status,
3553 x_msg_count => x_msg_count,
3554 x_msg_data => x_msg_data,
3555 p_cpsv_rec => p_cpsv_rec,
3556 x_cpsv_rec => x_cpsv_rec);
3557
3558 -- Update minor version
3559 If (x_return_status = OKC_API.G_RET_STS_SUCCESS) Then
3560 If (p_cpsv_rec.chr_id is not null and
3561 p_cpsv_rec.chr_id <> OKC_API.G_MISS_NUM) Then
3562 x_return_status := Update_Minor_Version(p_cpsv_rec.chr_id);
3563 End If;
3564 End If;
3565 END create_contract_process;
3566
3567 PROCEDURE create_contract_process(
3568 p_api_version IN NUMBER,
3569 p_init_msg_list IN VARCHAR2 ,
3570 x_return_status OUT NOCOPY VARCHAR2,
3571 x_msg_count OUT NOCOPY NUMBER,
3572 x_msg_data OUT NOCOPY VARCHAR2,
3573 p_cpsv_tbl IN OKC_CPS_PVT.cpsv_tbl_type,
3574 x_cpsv_tbl OUT NOCOPY OKC_CPS_PVT.cpsv_tbl_type) IS
3575
3576 BEGIN
3577 OKC_CPS_PVT.Insert_Row(
3578 p_api_version => p_api_version,
3579 p_init_msg_list => p_init_msg_list,
3580 x_return_status => x_return_status,
3581 x_msg_count => x_msg_count,
3582 x_msg_data => x_msg_data,
3583 p_cpsv_tbl => p_cpsv_tbl,
3584 x_cpsv_tbl => x_cpsv_tbl);
3585 END create_contract_process;
3586
3587 PROCEDURE update_contract_process(
3588 p_api_version IN NUMBER,
3589 p_init_msg_list IN VARCHAR2 ,
3590 x_return_status OUT NOCOPY VARCHAR2,
3591 x_msg_count OUT NOCOPY NUMBER,
3592 x_msg_data OUT NOCOPY VARCHAR2,
3593 p_cpsv_rec IN OKC_CPS_PVT.cpsv_rec_type,
3594 x_cpsv_rec OUT NOCOPY OKC_CPS_PVT.cpsv_rec_type) IS
3595
3596 l_process_active_yn VARCHAR2(1) := 'N';
3597 BEGIN
3598 -- check whether the process is active or not
3599 l_process_active_yn := Is_Process_Active(p_cpsv_rec.chr_id);
3600 If (l_process_active_yn = 'Y') Then
3601 raise G_NO_UPDATE_ALLOWED_EXCEPTION;
3602 End If;
3603
3604 OKC_CPS_PVT.Update_Row(
3605 p_api_version => p_api_version,
3606 p_init_msg_list => p_init_msg_list,
3607 x_return_status => x_return_status,
3608 x_msg_count => x_msg_count,
3609 x_msg_data => x_msg_data,
3610 p_cpsv_rec => p_cpsv_rec,
3611 x_cpsv_rec => x_cpsv_rec);
3612
3613 -- Update minor version
3614 If (x_return_status = OKC_API.G_RET_STS_SUCCESS) Then
3615 If (p_cpsv_rec.chr_id is not null and
3616 p_cpsv_rec.chr_id <> OKC_API.G_MISS_NUM) Then
3617 x_return_status := Update_Minor_Version(p_cpsv_rec.chr_id);
3618 End If;
3619 End If;
3620 exception
3621 when G_NO_UPDATE_ALLOWED_EXCEPTION then
3622 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
3623 p_msg_name => g_no_update_allowed,
3624 p_token1 => 'VALUE1',
3625 p_token1_value => 'Contract Processes');
3626
3627 -- notify caller of an error
3628 x_return_status := OKC_API.G_RET_STS_ERROR;
3629 when OTHERS then
3630 -- store SQL error message on message stack
3631 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
3632 p_msg_name => g_unexpected_error,
3633 p_token1 => g_sqlcode_token,
3634 p_token1_value => sqlcode,
3635 p_token2 => g_sqlerrm_token,
3636 p_token2_value => sqlerrm);
3637
3638 -- notify caller of an UNEXPETED error
3639 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3640 END update_contract_process;
3641
3642 PROCEDURE update_contract_process(
3643 p_api_version IN NUMBER,
3644 p_init_msg_list IN VARCHAR2 ,
3645 x_return_status OUT NOCOPY VARCHAR2,
3646 x_msg_count OUT NOCOPY NUMBER,
3647 x_msg_data OUT NOCOPY VARCHAR2,
3648 p_cpsv_tbl IN OKC_CPS_PVT.cpsv_tbl_type,
3649 x_cpsv_tbl OUT NOCOPY OKC_CPS_PVT.cpsv_tbl_type) IS
3650
3651 BEGIN
3652 OKC_CPS_PVT.Update_Row(
3653 p_api_version => p_api_version,
3654 p_init_msg_list => p_init_msg_list,
3655 x_return_status => x_return_status,
3656 x_msg_count => x_msg_count,
3657 x_msg_data => x_msg_data,
3658 p_cpsv_tbl => p_cpsv_tbl,
3659 x_cpsv_tbl => x_cpsv_tbl);
3660 END update_contract_process;
3661
3662 PROCEDURE delete_contract_process(
3663 p_api_version IN NUMBER,
3664 p_init_msg_list IN VARCHAR2 ,
3665 x_return_status OUT NOCOPY VARCHAR2,
3666 x_msg_count OUT NOCOPY NUMBER,
3667 x_msg_data OUT NOCOPY VARCHAR2,
3668 p_cpsv_rec IN OKC_CPS_PVT.cpsv_rec_type) IS
3669
3670 l_process_active_yn VARCHAR2(1) := 'N';
3671 l_chr_id NUMBER;
3672 l_not_found BOOLEAN;
3673 Cursor l_cpsv_csr(p_id IN NUMBER) Is
3674 SELECT CHR_ID
3675 FROM OKC_K_PROCESSES
3676 WHERE ID = p_id;
3677 BEGIN
3678 -- check whether the process is active or not
3679 l_process_active_yn := Is_Process_Active(p_cpsv_rec.chr_id);
3680 If (l_process_active_yn = 'Y') Then
3681 raise G_NO_UPDATE_ALLOWED_EXCEPTION;
3682 End If;
3683
3684 open l_cpsv_csr(p_cpsv_rec.id);
3685 fetch l_cpsv_csr into l_chr_id;
3686 l_not_found := l_cpsv_csr%NOTFOUND;
3687 close l_cpsv_csr;
3688 If (l_not_found) Then
3689 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3690 -- store SQL error message on message stack
3691 OKC_API.SET_MESSAGE(
3692 p_app_name => g_app_name,
3693 p_msg_name => g_unexpected_error,
3694 p_token1 => g_sqlcode_token,
3695 p_token1_value => sqlcode,
3696 p_token2 => g_sqlerrm_token,
3697 p_token2_value => sqlerrm);
3698 End If;
3699
3700 OKC_CPS_PVT.Delete_Row(
3701 p_api_version => p_api_version,
3702 p_init_msg_list => p_init_msg_list,
3703 x_return_status => x_return_status,
3704 x_msg_count => x_msg_count,
3705 x_msg_data => x_msg_data,
3706 p_cpsv_rec => p_cpsv_rec);
3707
3708 -- Update minor version
3709 If (x_return_status = OKC_API.G_RET_STS_SUCCESS and
3710 l_chr_id is not null)
3711 Then
3712 x_return_status := Update_Minor_Version(l_chr_id);
3713 End If;
3714 exception
3715 when G_NO_UPDATE_ALLOWED_EXCEPTION then
3716 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
3717 p_msg_name => g_no_update_allowed,
3718 p_token1 => 'VALUE1',
3719 p_token1_value => 'Contract Processes');
3720
3721 -- notify caller of an error
3722 x_return_status := OKC_API.G_RET_STS_ERROR;
3723 when OTHERS then
3724 -- store SQL error message on message stack
3725 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
3726 p_msg_name => g_unexpected_error,
3727 p_token1 => g_sqlcode_token,
3728 p_token1_value => sqlcode,
3729 p_token2 => g_sqlerrm_token,
3730 p_token2_value => sqlerrm);
3731
3732 -- notify caller of an UNEXPETED error
3733 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3734 END delete_contract_process;
3735
3736 PROCEDURE delete_contract_process(
3737 p_api_version IN NUMBER,
3738 p_init_msg_list IN VARCHAR2 ,
3739 x_return_status OUT NOCOPY VARCHAR2,
3740 x_msg_count OUT NOCOPY NUMBER,
3741 x_msg_data OUT NOCOPY VARCHAR2,
3742 p_cpsv_tbl IN OKC_CPS_PVT.cpsv_tbl_type) IS
3743
3744 BEGIN
3745 OKC_CPS_PVT.Delete_Row(
3746 p_api_version => p_api_version,
3747 p_init_msg_list => p_init_msg_list,
3748 x_return_status => x_return_status,
3749 x_msg_count => x_msg_count,
3750 x_msg_data => x_msg_data,
3751 p_cpsv_tbl => p_cpsv_tbl);
3752 END delete_contract_process;
3753
3754 PROCEDURE lock_contract_process(
3755 p_api_version IN NUMBER,
3756 p_init_msg_list IN VARCHAR2 ,
3757 x_return_status OUT NOCOPY VARCHAR2,
3758 x_msg_count OUT NOCOPY NUMBER,
3759 x_msg_data OUT NOCOPY VARCHAR2,
3760 p_cpsv_rec IN OKC_CPS_PVT.cpsv_rec_type) IS
3761
3762 BEGIN
3763 OKC_CPS_PVT.Lock_Row(
3764 p_api_version => p_api_version,
3765 p_init_msg_list => p_init_msg_list,
3766 x_return_status => x_return_status,
3767 x_msg_count => x_msg_count,
3768 x_msg_data => x_msg_data,
3769 p_cpsv_rec => p_cpsv_rec);
3770 END lock_contract_process;
3771
3772 PROCEDURE lock_contract_process(
3773 p_api_version IN NUMBER,
3774 p_init_msg_list IN VARCHAR2 ,
3775 x_return_status OUT NOCOPY VARCHAR2,
3776 x_msg_count OUT NOCOPY NUMBER,
3777 x_msg_data OUT NOCOPY VARCHAR2,
3778 p_cpsv_tbl IN OKC_CPS_PVT.cpsv_tbl_type) IS
3779
3780 BEGIN
3781 OKC_CPS_PVT.Lock_Row(
3782 p_api_version => p_api_version,
3783 p_init_msg_list => p_init_msg_list,
3784 x_return_status => x_return_status,
3785 x_msg_count => x_msg_count,
3786 x_msg_data => x_msg_data,
3787 p_cpsv_tbl => p_cpsv_tbl);
3788 END lock_contract_process;
3789
3790 PROCEDURE validate_contract_process(
3791 p_api_version IN NUMBER,
3792 p_init_msg_list IN VARCHAR2 ,
3793 x_return_status OUT NOCOPY VARCHAR2,
3794 x_msg_count OUT NOCOPY NUMBER,
3795 x_msg_data OUT NOCOPY VARCHAR2,
3796 p_cpsv_rec IN OKC_CPS_PVT.cpsv_rec_type) IS
3797
3798 BEGIN
3799 OKC_CPS_PVT.Validate_Row(
3800 p_api_version => p_api_version,
3801 p_init_msg_list => p_init_msg_list,
3802 x_return_status => x_return_status,
3803 x_msg_count => x_msg_count,
3804 x_msg_data => x_msg_data,
3805 p_cpsv_rec => p_cpsv_rec);
3806 END validate_contract_process;
3807
3808 PROCEDURE validate_contract_process(
3809 p_api_version IN NUMBER,
3810 p_init_msg_list IN VARCHAR2 ,
3811 x_return_status OUT NOCOPY VARCHAR2,
3812 x_msg_count OUT NOCOPY NUMBER,
3813 x_msg_data OUT NOCOPY VARCHAR2,
3814 p_cpsv_tbl IN OKC_CPS_PVT.cpsv_tbl_type) IS
3815
3816 BEGIN
3817 OKC_CPS_PVT.Validate_Row(
3818 p_api_version => p_api_version,
3819 p_init_msg_list => p_init_msg_list,
3820 x_return_status => x_return_status,
3821 x_msg_count => x_msg_count,
3822 x_msg_data => x_msg_data,
3823 p_cpsv_tbl => p_cpsv_tbl);
3824 END validate_contract_process;
3825
3826 PROCEDURE create_contract_access(
3827 p_api_version IN NUMBER,
3828 p_init_msg_list IN VARCHAR2 ,
3829 x_return_status OUT NOCOPY VARCHAR2,
3830 x_msg_count OUT NOCOPY NUMBER,
3831 x_msg_data OUT NOCOPY VARCHAR2,
3832 p_cacv_rec IN OKC_CAC_PVT.cacv_rec_type,
3833 x_cacv_rec OUT NOCOPY OKC_CAC_PVT.cacv_rec_type) IS
3834
3835 BEGIN
3836 OKC_CAC_PVT.Insert_Row(
3837 p_api_version => p_api_version,
3838 p_init_msg_list => p_init_msg_list,
3839 x_return_status => x_return_status,
3840 x_msg_count => x_msg_count,
3841 x_msg_data => x_msg_data,
3842 p_cacv_rec => p_cacv_rec,
3843 x_cacv_rec => x_cacv_rec);
3844
3845 -- Update minor version
3846 If (x_return_status = OKC_API.G_RET_STS_SUCCESS) Then
3847 x_return_status := Update_Minor_Version(x_cacv_rec.chr_id);
3848 End If;
3849 END create_contract_access;
3850
3851 PROCEDURE create_contract_access(
3852 p_api_version IN NUMBER,
3853 p_init_msg_list IN VARCHAR2 ,
3854 x_return_status OUT NOCOPY VARCHAR2,
3855 x_msg_count OUT NOCOPY NUMBER,
3856 x_msg_data OUT NOCOPY VARCHAR2,
3857 p_cacv_tbl IN OKC_CAC_PVT.cacv_tbl_type,
3858 x_cacv_tbl OUT NOCOPY OKC_CAC_PVT.cacv_tbl_type) IS
3859
3860 BEGIN
3861 OKC_CAC_PVT.Insert_Row(
3862 p_api_version => p_api_version,
3863 p_init_msg_list => p_init_msg_list,
3864 x_return_status => x_return_status,
3865 x_msg_count => x_msg_count,
3866 x_msg_data => x_msg_data,
3867 p_cacv_tbl => p_cacv_tbl,
3868 x_cacv_tbl => x_cacv_tbl);
3869 END create_contract_access;
3870
3871 PROCEDURE update_contract_access(
3872 p_api_version IN NUMBER,
3873 p_init_msg_list IN VARCHAR2 ,
3874 x_return_status OUT NOCOPY VARCHAR2,
3875 x_msg_count OUT NOCOPY NUMBER,
3876 x_msg_data OUT NOCOPY VARCHAR2,
3877 p_cacv_rec IN OKC_CAC_PVT.cacv_rec_type,
3878 x_cacv_rec OUT NOCOPY OKC_CAC_PVT.cacv_rec_type) IS
3879
3880 BEGIN
3881 OKC_CAC_PVT.Update_Row(
3882 p_api_version => p_api_version,
3883 p_init_msg_list => p_init_msg_list,
3884 x_return_status => x_return_status,
3885 x_msg_count => x_msg_count,
3886 x_msg_data => x_msg_data,
3887 p_cacv_rec => p_cacv_rec,
3888 x_cacv_rec => x_cacv_rec);
3889
3890 -- Update minor version
3891 If (x_return_status = OKC_API.G_RET_STS_SUCCESS) Then
3892 x_return_status := Update_Minor_Version(x_cacv_rec.chr_id);
3893 End If;
3894 END update_contract_access;
3895
3896 PROCEDURE update_contract_access(
3897 p_api_version IN NUMBER,
3898 p_init_msg_list IN VARCHAR2 ,
3899 x_return_status OUT NOCOPY VARCHAR2,
3900 x_msg_count OUT NOCOPY NUMBER,
3901 x_msg_data OUT NOCOPY VARCHAR2,
3902 p_cacv_tbl IN OKC_CAC_PVT.cacv_tbl_type,
3903 x_cacv_tbl OUT NOCOPY OKC_CAC_PVT.cacv_tbl_type) IS
3904
3905 BEGIN
3906 OKC_CAC_PVT.Update_Row(
3907 p_api_version => p_api_version,
3908 p_init_msg_list => p_init_msg_list,
3909 x_return_status => x_return_status,
3910 x_msg_count => x_msg_count,
3911 x_msg_data => x_msg_data,
3912 p_cacv_tbl => p_cacv_tbl,
3913 x_cacv_tbl => x_cacv_tbl);
3914 END update_contract_access;
3915
3916 PROCEDURE delete_contract_access(
3917 p_api_version IN NUMBER,
3918 p_init_msg_list IN VARCHAR2 ,
3919 x_return_status OUT NOCOPY VARCHAR2,
3920 x_msg_count OUT NOCOPY NUMBER,
3921 x_msg_data OUT NOCOPY VARCHAR2,
3922 p_cacv_rec IN OKC_CAC_PVT.cacv_rec_type) IS
3923
3924 l_chr_id NUMBER;
3925 l_not_found BOOLEAN;
3926 Cursor l_cacv_csr Is
3927 SELECT chr_id
3928 FROM OKC_K_ACCESSES
3929 WHERE id = p_cacv_rec.id;
3930 BEGIN
3931 Open l_cacv_csr;
3932 Fetch l_cacv_csr into l_chr_id;
3933 l_not_found := l_cacv_csr%NOTFOUND;
3934 Close l_cacv_csr;
3935
3936 If (l_not_found) Then
3937 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3938 -- store SQL error message on message stack
3939 OKC_API.SET_MESSAGE(
3940 p_app_name => g_app_name,
3941 p_msg_name => g_unexpected_error,
3942 p_token1 => g_sqlcode_token,
3943 p_token1_value => sqlcode,
3944 p_token2 => g_sqlerrm_token,
3945 p_token2_value => sqlerrm);
3946 return;
3947 End If;
3948
3949 OKC_CAC_PVT.Delete_Row(
3950 p_api_version => p_api_version,
3951 p_init_msg_list => p_init_msg_list,
3952 x_return_status => x_return_status,
3953 x_msg_count => x_msg_count,
3954 x_msg_data => x_msg_data,
3955 p_cacv_rec => p_cacv_rec);
3956
3957 -- Update minor version
3958 If (x_return_status = OKC_API.G_RET_STS_SUCCESS) Then
3959 x_return_status := Update_Minor_Version(l_chr_id);
3960 End If;
3961 END delete_contract_access;
3962
3963 PROCEDURE delete_contract_access(
3964 p_api_version IN NUMBER,
3965 p_init_msg_list IN VARCHAR2 ,
3966 x_return_status OUT NOCOPY VARCHAR2,
3967 x_msg_count OUT NOCOPY NUMBER,
3968 x_msg_data OUT NOCOPY VARCHAR2,
3969 p_cacv_tbl IN OKC_CAC_PVT.cacv_tbl_type) IS
3970
3971 BEGIN
3972 OKC_CAC_PVT.Delete_Row(
3973 p_api_version => p_api_version,
3974 p_init_msg_list => p_init_msg_list,
3975 x_return_status => x_return_status,
3976 x_msg_count => x_msg_count,
3977 x_msg_data => x_msg_data,
3978 p_cacv_tbl => p_cacv_tbl);
3979 END delete_contract_access;
3980
3981 PROCEDURE lock_contract_access(
3982 p_api_version IN NUMBER,
3983 p_init_msg_list IN VARCHAR2 ,
3984 x_return_status OUT NOCOPY VARCHAR2,
3985 x_msg_count OUT NOCOPY NUMBER,
3986 x_msg_data OUT NOCOPY VARCHAR2,
3987 p_cacv_rec IN OKC_CAC_PVT.cacv_rec_type) IS
3988
3989 BEGIN
3990 OKC_CAC_PVT.Lock_Row(
3991 p_api_version => p_api_version,
3992 p_init_msg_list => p_init_msg_list,
3993 x_return_status => x_return_status,
3994 x_msg_count => x_msg_count,
3995 x_msg_data => x_msg_data,
3996 p_cacv_rec => p_cacv_rec);
3997 END lock_contract_access;
3998
3999 PROCEDURE lock_contract_access(
4000 p_api_version IN NUMBER,
4001 p_init_msg_list IN VARCHAR2 ,
4002 x_return_status OUT NOCOPY VARCHAR2,
4003 x_msg_count OUT NOCOPY NUMBER,
4004 x_msg_data OUT NOCOPY VARCHAR2,
4005 p_cacv_tbl IN OKC_CAC_PVT.cacv_tbl_type) IS
4006
4007 BEGIN
4008 OKC_CAC_PVT.Lock_Row(
4009 p_api_version => p_api_version,
4010 p_init_msg_list => p_init_msg_list,
4011 x_return_status => x_return_status,
4012 x_msg_count => x_msg_count,
4013 x_msg_data => x_msg_data,
4014 p_cacv_tbl => p_cacv_tbl);
4015 END lock_contract_access;
4016
4017 PROCEDURE validate_contract_access(
4018 p_api_version IN NUMBER,
4019 p_init_msg_list IN VARCHAR2 ,
4020 x_return_status OUT NOCOPY VARCHAR2,
4021 x_msg_count OUT NOCOPY NUMBER,
4022 x_msg_data OUT NOCOPY VARCHAR2,
4023 p_cacv_rec IN OKC_CAC_PVT.cacv_rec_type) IS
4024
4025 BEGIN
4026 OKC_CAC_PVT.Validate_Row(
4027 p_api_version => p_api_version,
4028 p_init_msg_list => p_init_msg_list,
4029 x_return_status => x_return_status,
4030 x_msg_count => x_msg_count,
4031 x_msg_data => x_msg_data,
4032 p_cacv_rec => p_cacv_rec);
4033 END validate_contract_access;
4034
4035 PROCEDURE validate_contract_access(
4036 p_api_version IN NUMBER,
4037 p_init_msg_list IN VARCHAR2 ,
4038 x_return_status OUT NOCOPY VARCHAR2,
4039 x_msg_count OUT NOCOPY NUMBER,
4040 x_msg_data OUT NOCOPY VARCHAR2,
4041 p_cacv_tbl IN OKC_CAC_PVT.cacv_tbl_type) IS
4042
4043 BEGIN
4044 OKC_CAC_PVT.Validate_Row(
4045 p_api_version => p_api_version,
4046 p_init_msg_list => p_init_msg_list,
4047 x_return_status => x_return_status,
4048 x_msg_count => x_msg_count,
4049 x_msg_data => x_msg_data,
4050 p_cacv_tbl => p_cacv_tbl);
4051 END validate_contract_access;
4052
4053 PROCEDURE add_language IS
4054 BEGIN
4055 OKC_CHR_PVT.add_language;
4056 OKC_CLE_PVT.add_language;
4057 END add_language;
4058
4059 PROCEDURE Get_Active_Process (
4060 p_api_version IN NUMBER,
4061 p_init_msg_list IN VARCHAR2,
4062 x_return_status OUT NOCOPY VARCHAR2,
4063 x_msg_count OUT NOCOPY NUMBER,
4064 x_msg_data OUT NOCOPY VARCHAR2,
4065 p_contract_number IN VARCHAR2,
4066 p_contract_number_modifier IN VARCHAR2,
4067 x_wf_name OUT NOCOPY VARCHAR2,
4068 x_wf_process_name OUT NOCOPY VARCHAR2,
4069 x_package_name OUT NOCOPY VARCHAR2,
4070 x_procedure_name OUT NOCOPY VARCHAR2,
4071 x_usage OUT NOCOPY VARCHAR2) Is
4072
4073 l_chr_id NUMBER;
4074 l_process_active_yn VARCHAR2(1);
4075 Cursor l_chrv_csr Is
4076 SELECT id
4077 FROM OKC_K_HEADERS_B
4078 WHERE contract_number = p_contract_number
4079 AND contract_number_modifier = p_contract_number_modifier;
4080
4081 Cursor l_chrv_csr2 Is
4082 SELECT id
4083 FROM OKC_K_HEADERS_B
4084 WHERE contract_number = p_contract_number
4085 AND contract_number_modifier is null;
4086
4087 Cursor l_pdfv_csr Is
4088 SELECT
4089 usage,
4090 name,
4091 wf_process_name,
4092 procedure_name,
4093 package_name
4094 FROM okc_process_defs_v pdfv,
4095 okc_k_processes cpsv
4096 WHERE pdfv.id = cpsv.pdf_id
4097 AND cpsv.chr_id = l_chr_id
4098 AND cpsv.last_update_date = (SELECT MAX(last_update_date)
4099 FROM okc_k_processes
4100 WHERE chr_id = l_chr_id);
4101 BEGIN
4102 x_return_status := OKC_API.G_RET_STS_SUCCESS;
4103 -- get id from header table
4104 If (p_contract_number_modifier is not null) Then
4105 Open l_chrv_csr;
4106 Fetch l_chrv_csr into l_chr_id;
4107 If l_chrv_csr%NOTFOUND Then
4108 raise NO_CONTRACT_FOUND;
4109 End If;
4110 close l_chrv_csr;
4111 Else
4112 Open l_chrv_csr2;
4113 Fetch l_chrv_csr2 into l_chr_id;
4114 If l_chrv_csr2%NOTFOUND Then
4115 raise NO_CONTRACT_FOUND;
4116 End If;
4117 close l_chrv_csr2;
4118 End If;
4119
4120 l_process_active_yn := Is_Process_Active(l_chr_id);
4121 If (l_process_active_yn = 'Y') Then
4122 Open l_pdfv_csr;
4123 Fetch l_pdfv_csr Into x_usage,
4124 x_wf_name,
4125 x_wf_process_name,
4126 x_package_name,
4127 x_procedure_name;
4128 If l_pdfv_csr%NOTFOUND Then
4129 x_return_status := OKC_API.G_RET_STS_ERROR;
4130 End If;
4131 Close l_pdfv_csr;
4132 Else
4133 x_usage := NULL;
4134 x_wf_name := NULL;
4135 x_wf_process_name := NULL;
4136 x_package_name := NULL;
4137 x_procedure_name := NULL;
4138 End If;
4139 exception
4140 when NO_CONTRACT_FOUND Then
4141 If (l_chrv_csr%ISOPEN) Then
4142 close l_chrv_csr;
4143 Elsif (l_chrv_csr2%ISOPEN) Then
4144 close l_chrv_csr2;
4145 End If;
4146
4147 -- pass NULLs to calling program
4148 x_usage := NULL;
4149 x_wf_name := NULL;
4150 x_wf_process_name := NULL;
4151 x_package_name := NULL;
4152 x_procedure_name := NULL;
4153
4154 -- OKC_API.SET_MESSAGE(p_app_name => g_app_name,
4155 -- p_msg_name => 'NO_CONTRACT_FOUND');
4156 -- x_return_status := OKC_API.G_RET_STS_ERROR;
4157 when OTHERS then
4158 -- store SQL error message on message stack
4159 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
4160 p_msg_name => g_unexpected_error,
4161 p_token1 => g_sqlcode_token,
4162 p_token1_value => sqlcode,
4163 p_token2 => g_sqlerrm_token,
4164 p_token2_value => sqlerrm);
4165
4166 -- notify caller of an UNEXPETED error
4167 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4168 END Get_Active_Process;
4169
4170 --
4171 -- function that checkes whether a contract is updateable or not
4172 -- returns 'Y' if updateable, 'N' if not.
4173 -- returns OKC_API.G_RET_STS_ERROR or OKC_API.G_RET_STS_UNEXP_ERROR
4174 -- in case of error
4175 --
4176 FUNCTION Update_Allowed(p_chr_id IN NUMBER) RETURN VARCHAR2 Is
4177 l_sts_code OKC_ASSENTS.STS_CODE%TYPE;
4178 l_scs_code OKC_ASSENTS.SCS_CODE%TYPE;
4179 l_return_value VARCHAR2(1) := 'Y';
4180
4181 Cursor l_chrv_csr Is
4182 SELECT sts_code, scs_code
4183 --npalepu 26-10-2005 modified for bug # 4691662.
4184 --Replaced table okc_k_headers_b with headers_All_b table
4185 /* FROM OKC_K_HEADERS_B */
4186 FROM OKC_K_HEADERS_ALL_B
4187 --end npalepu
4188 WHERE id = p_chr_id;
4189
4190 Cursor l_astv_csr Is
4191 SELECT upper(substr(allowed_yn,1,1))
4192 FROM okc_assents
4193 WHERE sts_code = l_sts_code
4194 AND scs_code = l_scs_code
4195 AND opn_code = 'UPDATE';
4196 BEGIN
4197 -- get status from contract headers
4198 Open l_chrv_csr;
4199 Fetch l_chrv_csr Into l_sts_code, l_scs_code;
4200 If l_chrv_csr%FOUND Then
4201 Close l_chrv_csr;
4202 Open l_astv_csr;
4203 Fetch l_astv_csr into l_return_value;
4204 If (l_return_value not in ('Y','N')) Then
4205 l_return_value := OKC_API.G_RET_STS_UNEXP_ERROR;
4206 End If;
4207 Close l_astv_csr;
4208 Else
4209 Close l_chrv_csr;
4210 End If;
4211 return l_return_value;
4212 Exception
4213 when OTHERS then
4214 -- store SQL error message on message stack
4215 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
4216 p_msg_name => g_unexpected_error,
4217 p_token1 => g_sqlcode_token,
4218 p_token1_value => sqlcode,
4219 p_token2 => g_sqlerrm_token,
4220 p_token2_value => sqlerrm);
4221
4222 -- notify caller of an UNEXPETED error
4223 l_return_value := OKC_API.G_RET_STS_UNEXP_ERROR;
4224 END Update_Allowed;
4225
4226 --------------------------------------------------------------------
4227 -- Procedure to clear/relink renewal links
4228 -- Added for renew, status related changes
4229 -- Parameter: p_target_chr_id = target contract id
4230 -- clean_relink_flag = 'CLEAN' (default) for cleaning
4231 -- = 'RELINK' for relinking
4232 --
4233 -- RELINK_RENEW procedure call this procedure with 'RELINK'
4234 --------------------------------------------------------------------
4235 PROCEDURE CLEAN_REN_LINKS(
4236 p_api_version IN NUMBER,
4237 p_init_msg_list IN VARCHAR2 ,
4238 x_return_status OUT NOCOPY VARCHAR2,
4239 x_msg_count OUT NOCOPY NUMBER,
4240 x_msg_data OUT NOCOPY VARCHAR2,
4241 p_target_chr_id IN NUMBER,
4242 clean_relink_flag VARCHAR2)
4243
4244 IS
4245 l_api_name VARCHAR2(30) := 'LINE_RENEWAL_LINKS';
4246 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4247 l_active_yn VARCHAR2(1) := 'N';
4248 l_no_of_lines NUMBER;
4249 l_no_of_op_lines NUMBER;
4250
4251 --
4252 -- Cursor to get source contract ids
4253 -- to update minor version, if p_is_parent <> 'Y'
4254 --
4255
4256 --Fix for bug 4948793
4257 Cursor ole_csr Is
4258
4259 SELECT distinct ol.object_chr_id
4260 FROM okc_operation_instances op
4261 , okc_class_operations cls
4262 , okc_subclasses_b sl
4263 , okc_operation_lines ol
4264 WHERE ol.subject_chr_id = p_target_chr_id
4265 And op.id = ol.oie_id
4266 AND op.cop_id = cls.id
4267 And cls.cls_code = sl.cls_code
4268 And sl.code = 'SERVICE'
4269 And cls.opn_code in ('RENEWAL','REN_CON');
4270
4271 /*SELECT distinct object_chr_id
4272 FROM okc_operation_lines
4273 WHERE subject_chr_id = p_target_chr_id;
4274 --AND active_yn = 'Y';
4275 */
4276
4277
4278 Cursor ole_csr2(p_subject_chr_id NUMBER, p_object_chr_id NUMBER) Is
4279 SELECT count(*)
4280 FROM okc_operation_lines
4281 WHERE SUBJECT_CHR_ID = p_subject_chr_id
4282 AND OBJECT_CHR_ID = p_object_chr_id
4283 AND SUBJECT_CLE_ID is not null;
4284
4285 Cursor cle_csr(p_subject_chr_id NUMBER, p_object_chr_id NUMBER) Is
4286 SELECT count(*)
4287 FROM okc_k_lines_b
4288 WHERE id IN (SELECT OBJECT_CLE_ID
4289 FROM okc_operation_lines
4290 WHERE SUBJECT_CHR_ID = p_subject_chr_id
4291 AND OBJECT_CHR_ID = p_object_chr_id)
4292 AND dnz_chr_id = p_object_chr_id;
4293
4294 BEGIN
4295 x_return_status := OKC_API.G_RET_STS_SUCCESS;
4296 l_return_status := OKC_API.START_ACTIVITY
4297 (l_api_name, p_init_msg_list, '_PVT', x_return_status);
4298
4299 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4300 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4301 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4302 RAISE OKC_API.G_EXCEPTION_ERROR;
4303 END IF;
4304
4305 -- set flag to update
4306 IF (NVL(clean_relink_flag,'CLEAN') = 'RELINK') Then
4307 l_active_yn := 'Y';
4308 END IF;
4309
4310 --
4311 -- Clean date_renewed in header (for CLEAN)
4312 --
4313 If (l_active_yn = 'N') Then
4314 -- Clear all source contract's date_renewed column
4315 UPDATE OKC_K_HEADERS_B
4316 SET date_renewed = null,
4317 object_version_number = object_version_number + 1,
4318 last_updated_by = FND_GLOBAL.USER_ID,
4319 last_update_date = sysdate,
4320 last_update_login = FND_GLOBAL.LOGIN_ID
4321 WHERE id in (
4322 SELECT distinct object_chr_id
4323 FROM okc_operation_lines
4324 WHERE subject_chr_id = p_target_chr_id
4325 AND active_yn = 'Y');
4326 --
4327 -- Set date renewed on all source contracts if all lines are renewed
4328 --
4329 Else
4330 FOR ole_rec IN ole_csr
4331 LOOP
4332 open ole_csr2(p_target_chr_id,ole_rec.object_chr_id);
4333 fetch ole_csr2 into l_no_of_op_lines;
4334 close ole_csr2;
4335
4336 open cle_csr(p_target_chr_id,ole_rec.object_chr_id);
4337 fetch cle_csr into l_no_of_lines;
4338 close cle_csr;
4339
4340 If ( l_no_of_op_lines = l_no_of_lines ) Then
4341 UPDATE OKC_K_HEADERS_B
4342 SET date_renewed = sysdate,
4343 object_version_number = object_version_number + 1,
4344 last_updated_by = FND_GLOBAL.USER_ID,
4345 last_update_date = sysdate,
4346 last_update_login = FND_GLOBAL.LOGIN_ID
4347 WHERE id = ole_rec.object_chr_id;
4348
4349 End If;
4350 END LOOP;
4351 End If;
4352
4353 --
4354 -- To clear renewal link of the target contract,
4355 -- set operation lines.active_yn = Y or N
4356 -- for the contract entry in operation lines table
4357 -- subject_chr_id is the child/renewed_to chr id
4358 --
4359 UPDATE okc_operation_lines ol
4360 SET active_yn = l_active_yn,
4361 object_version_number = object_version_number + 1,
4362 last_updated_by = FND_GLOBAL.USER_ID,
4363 last_update_date = sysdate,
4364 last_update_login = FND_GLOBAL.LOGIN_ID
4365 WHERE subject_chr_id = p_target_chr_id
4366 AND subject_cle_id is null
4367 AND object_cle_id is null
4368 And exists(Select 'x' --Fix Bug 4948793
4369 FROM okc_operation_instances op
4370 , okc_class_operations cls
4371 , okc_subclasses_b sl
4372 WHERE op.id = ol.oie_id
4373 AND op.cop_id = cls.id
4374 And cls.cls_code = sl.cls_code
4375 And sl.code = 'SERVICE'
4376 And cls.opn_code in ('RENEWAL','REN_CON') );
4377 --
4378 -- clear date_renewed in source contact(s) lines
4379 -- only for those lines in target contract
4380 --
4381 UPDATE OKC_K_LINES_B
4382 SET date_renewed = decode(l_active_yn,'Y',sysdate,null),
4383 object_version_number = object_version_number + 1,
4384 last_updated_by = FND_GLOBAL.USER_ID,
4385 last_update_date = sysdate,
4386 last_update_login = FND_GLOBAL.LOGIN_ID
4387 WHERE id in (Select ol.object_cle_id --Fix Bug 4948793
4388 FROM okc_operation_instances op
4389 , okc_class_operations cls
4390 , okc_subclasses_b sl
4391 , okc_operation_lines ol
4392 WHERE ol.subject_chr_id = p_target_chr_id
4393 And ol.object_cle_id is not null
4394 And op.id = ol.oie_id
4395 AND op.cop_id = cls.id
4396 And cls.cls_code = sl.cls_code
4397 And sl.code = 'SERVICE'
4398 And cls.opn_code in ('RENEWAL','REN_CON') );
4399
4400 --
4401 -- To set renewal link of contract lines,
4402 -- set operation lines.active_yn = Y or N
4403 -- for the contract line entries in operation lines table
4404 --
4405 UPDATE okc_operation_lines ol
4406 SET active_yn = l_active_yn,
4407 object_version_number = object_version_number + 1,
4408 last_updated_by = FND_GLOBAL.USER_ID,
4409 last_update_date = sysdate,
4410 last_update_login = FND_GLOBAL.LOGIN_ID
4411 WHERE subject_chr_id = p_target_chr_id
4412 AND subject_cle_id is not null
4413 AND object_cle_id is not null
4414 And exists(Select 'x' --Fix Bug 4948793
4415 FROM okc_operation_instances op
4416 , okc_class_operations cls
4417 , okc_subclasses_b sl
4418 WHERE op.id = ol.oie_id
4419 AND op.cop_id = cls.id
4420 And cls.cls_code = sl.cls_code
4421 And sl.code = 'SERVICE'
4422 And cls.opn_code in ('RENEWAL','REN_CON') );
4423
4424 --
4425 --Set minor version for updated contracts
4426 --
4427 FOR ole_rec IN ole_csr
4428 LOOP
4429 x_return_status := update_minor_version(ole_rec.object_chr_id);
4430
4431 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4432 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4433 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4434 raise OKC_API.G_EXCEPTION_ERROR;
4435 END IF;
4436 END LOOP;
4437
4438 --- Added for Bug# 2606251 --- to increment version for a renewed contract(when staus is changed)
4439 OKC_CVM_PVT.g_trans_id := 'XX-XX';
4440 --- Added for Bug# 2606251 ---
4441
4442 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
4443 EXCEPTION
4444 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4445 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4446 (
4447 l_api_name,
4448 G_PKG_NAME,
4449 'OKC_API.G_RET_STS_ERROR',
4450 x_msg_count,
4451 x_msg_data,
4452 '_PVT'
4453 );
4454 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4455 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4456 (
4457 l_api_name,
4458 G_PKG_NAME,
4459 'OKC_API.G_RET_STS_UNEXP_ERROR',
4460 x_msg_count,
4461 x_msg_data,
4462 '_PVT'
4463 );
4464 WHEN OTHERS THEN
4465 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4466 (
4467 l_api_name,
4468 G_PKG_NAME,
4469 'OTHERS',
4470 x_msg_count,
4471 x_msg_data,
4472 '_PVT'
4473 );
4474 END CLEAN_REN_LINKS;
4475
4476 PROCEDURE RELINK_RENEW(
4477 p_api_version IN NUMBER,
4478 p_init_msg_list IN VARCHAR2 ,
4479 x_return_status OUT NOCOPY VARCHAR2,
4480 x_msg_count OUT NOCOPY NUMBER,
4481 x_msg_data OUT NOCOPY VARCHAR2,
4482 p_target_chr_id IN number)
4483 IS
4484 BEGIN
4485 CLEAN_REN_LINKS(
4486 p_api_version => p_api_version,
4487 p_init_msg_list => p_init_msg_list,
4488 x_return_status => x_return_status,
4489 x_msg_count => x_msg_count,
4490 x_msg_data => x_msg_data,
4491 p_target_chr_id => p_target_chr_id,
4492 clean_relink_flag => 'RELINK');
4493
4494 END RELINK_RENEW;
4495
4496
4497 --For Bug.No.1789860, Function Get_concat_line_no is added.
4498 --The following procedure is added to display concatenated line nos.
4499 --For Bug.No.3339185, Function Get_concat_line_no is modified.
4500 ------------------------------------------------------------------------
4501 FUNCTION Get_concat_line_no(
4502 p_cle_id IN NUMBER,
4503 x_return_status OUT NOCOPY Varchar2) RETURN VARCHAR2 IS
4504
4505 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4506
4507 CURSOR l_line_csr IS
4508 SELECT display_sequence from
4509 OKC_K_LINES_B
4510 connect by prior cle_id = id
4511 start with id = p_cle_id;
4512
4513 CURSOR l_k_code_csr IS
4514 SELECT cls_code from
4515 OKC_SUBCLASSES_B WHERE code=(SELECT scs_code from OKC_K_HEADERS_B where id in
4516 (select dnz_chr_id from okc_k_lines_b where id =p_cle_id));
4517
4518 CURSOR l_chk_cle_csr IS
4519 SELECT clev.cle_id,RTRIM(clev.line_number) line_number,clev.lse_id
4520 FROM OKC_K_LINES_V clev
4521 WHERE clev.id = p_cle_id;
4522
4523 CURSOR l_get_top_line_number_csr (p_line_id NUMBER) IS
4524 SELECT line_number
4525 FROM OKC_K_LINES_B
4526 WHERE id = p_line_id;
4527
4528 CURSOR l_line_name_csr IS
4529 SELECT line_number "LINE_NAME"
4530 FROM OKC_LINE_STYLES_V lsev,
4531 OKC_K_LINES_V clev
4532 WHERE lsev.id = clev.lse_id
4533 AND clev.id = p_cle_id;
4534
4535 l_line_number Varchar2(2000);
4536 l_return Varchar2(2000);
4537 l_code Varchar2(2000);
4538 j Number :=0;
4539 l_line_name_rec l_line_name_csr%ROWTYPE;
4540 l_chk_cle_rec l_chk_cle_csr%ROWTYPE;
4541 l_line_name VARCHAR2(1000);
4542 l_get_top_line_number_rec l_get_top_line_number_csr%ROWTYPE;
4543
4544 BEGIN
4545 open l_k_code_csr;
4546 FETCH l_k_code_csr INTO l_code;
4547 close l_k_code_csr;
4548 IF(l_code='SERVICE') THEN --service contracts
4549 OPEN l_chk_cle_csr;
4550 FETCH l_chk_cle_csr INTO l_chk_cle_rec;
4551 IF l_chk_cle_rec.cle_id IS NULL
4552 THEN
4553 OPEN l_line_name_csr;
4554 FETCH l_line_name_csr INTO l_line_name_rec;
4555 CLOSE l_line_name_csr;
4556
4557 l_line_name := l_line_name_rec.line_name;
4558
4559 ELSE
4560 OPEN l_get_top_line_number_csr (l_chk_cle_rec.cle_id);
4561 FETCH l_get_top_line_number_csr INTO l_get_top_line_number_rec;
4562 CLOSE l_get_top_line_number_csr;
4563
4564 OPEN l_line_name_csr;
4565 FETCH l_line_name_csr INTO l_line_name_rec;
4566 CLOSE l_line_name_csr;
4567
4568 if ((l_chk_cle_rec.lse_id >=2 and l_chk_cle_rec.lse_id <=6) or (l_chk_cle_rec.lse_id=15)or (l_chk_cle_rec.lse_id=16)
4569 or (l_chk_cle_rec.lse_id=17)or (l_chk_cle_rec.lse_id>=21 and l_chk_cle_rec.lse_id <=24)) then
4570 l_line_name := l_get_top_line_number_rec.line_number; --coverage lines
4571 else --sub lines
4572 l_line_name := l_get_top_line_number_rec.line_number||'.'||l_line_name_rec.line_name;
4573 end if;
4574
4575 END IF; --IF l_chk_cle_rec.cle_id IS NULL
4576
4577 CLOSE l_chk_cle_csr;
4578
4579 IF l_line_name_rec.line_name IS NULL THEN
4580 RETURN('No Line Name Found');
4581 ELSE
4582 RETURN(l_line_name);
4583 END IF;
4584 ELSE -- other than service contracts
4585 open l_line_csr;
4586 Loop
4587 Fetch l_line_csr INTO l_line_number;
4588 Exit When l_line_csr%NOTFOUND;
4589 If j = 0 then
4590 l_return := l_line_number;
4591 j := j + 1;
4592 Else
4593 l_return := l_line_number || '.' || l_return ;
4594 End if;
4595 End Loop;
4596 close l_line_csr;
4597 return l_return;
4598 x_return_status := l_return_status;
4599 End if;
4600 EXCEPTION
4601 WHEN OTHERS then
4602 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4603 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
4604 p_msg_name => g_unexpected_error,
4605 p_token1 => g_sqlcode_token,
4606 p_token1_value => sqlcode,
4607 p_token2 => g_sqlerrm_token,
4608 p_token2_value => sqlerrm);
4609 -- verify that cursor was closed
4610 IF l_line_name_csr%ISOPEN THEN
4611 CLOSE l_line_name_csr;
4612 END IF;
4613 return Null;
4614
4615 END Get_concat_line_no;
4616
4617
4618 ---
4619
4620 --[llc] Update Contract Amount
4621
4622 /*
4623 The Header and Line Amounts should be updated when Change Status action is taken
4624 at the header/line/subline level. This is to ensure that the calualated amounts
4625 (price_negotiated, cancelled_amount, estimated_amount) ignores cancelled lines/sublines.
4626
4627 A new procedure Update_Contract_Amount is created which is called
4628 when cancel actions is taken at header/line/subline level.
4629
4630 */
4631
4632
4633 PROCEDURE UPDATE_CONTRACT_AMOUNT (
4634 p_api_version IN NUMBER,
4635 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
4636 p_id IN NUMBER,
4637 p_from_ste_code IN VARCHAR2,
4638 p_to_ste_code IN VARCHAR2,
4639 p_cle_id IN NUMBER,
4640 x_return_status OUT NOCOPY VARCHAR2,
4641 x_msg_count OUT NOCOPY NUMBER,
4642 x_msg_data OUT NOCOPY VARCHAR2 )
4643
4644 IS
4645
4646 l_cle_id Number := NULL;
4647 l_sub_line_amt Number := NULL;
4648 l_lse_id Number := NULL;
4649 l_hdr_estimated_amt Number := NULL;
4650 l_hdr_cancelled_amt Number := NULL;
4651 l_uncancelled_amt Number := NULL;
4652
4653
4654
4655 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CONTRACT_AMOUNT';
4656
4657
4658 --Cursor to get topline id for a particular subline; For a topline this will return NULL
4659
4660 Cursor get_line_lvl_csr is
4661 Select cle_id
4662 from okc_k_lines_b
4663 where id = p_cle_id
4664 and dnz_chr_id = p_id;
4665
4666 --Cursor to fetch amount for a particular subline
4667
4668 Cursor get_subline_amt_csr (p_cle_id NUMBER) IS
4669 Select cle.price_negotiated
4670 from okc_k_lines_b cle
4671 where cle.id = p_cle_id
4672 and cle.dnz_chr_id = p_id;
4673
4674 --Cursor to add price_negotiated and cancelled_amount of all the toplines
4675
4676 Cursor get_hdr_amts_csr IS
4677 select nvl(sum(nvl(price_negotiated,0)),0), nvl(sum(nvl(cancelled_amount,0)),0)
4678 from okc_k_lines_b cle
4679 where cle.dnz_chr_id = p_id
4680 and cle.lse_id in (1, 12, 14, 19, 46)
4681 and cle.cle_id is null;
4682
4683 --Cursor to fectch lse_id of topline
4684
4685 Cursor get_lse_id_csr (p_cle_id NUMBER) IS
4686 select lse_id
4687 from okc_k_lines_b
4688 where id=p_cle_id;
4689
4690
4691 --Cursor to add price_negotiated for all the sublines of a topline which are not in cancelled status
4692
4693 Cursor get_uncancelled_amt_csr(p_cle_id number, p_id number) IS
4694 select nvl(sum(nvl(price_negotiated,0)),0)
4695 from okc_k_lines_b
4696 where cle_id = p_cle_id
4697 and dnz_chr_id = p_id
4698 and date_cancelled is null;
4699
4700
4701 -- Bug Fix 5026369 maanand 08-FEB-2006
4702
4703 -- Cursor to fetch all the toplines of a contract whose term_cancel_source is null
4704 -- i.e. term_cancel_source is not ('IBTRANSFER', 'IBRETURN', 'IBTERMINATE', 'IBREPLACE')
4705
4706 CURSOR get_topline_id_csr IS
4707 SELECT id
4708 FROM okc_k_lines_b
4709 WHERE dnz_chr_id = p_id
4710 and cle_id is null
4711 and term_cancel_source is null;
4712
4713
4714 --
4715
4716 BEGIN
4717
4718
4719
4720 IF (l_debug = 'Y') THEN
4721 okc_debug.log('2200: Entered UPDATE_CONTRACT_AMOUNT');
4722 END IF;
4723
4724 IF ((p_from_ste_code is NULL) OR (p_to_ste_code is NULL) OR (p_id is null)) THEN
4725 raise FND_API.G_EXC_ERROR;
4726 END IF;
4727
4728 IF (l_debug = 'Y') THEN
4729 okc_debug.log('2210: Parameter Values ' ||
4730 'p_id - '|| p_id ||
4731 'p_from_ste_code - '||p_from_ste_code ||
4732 'p_to_ste_code - '||p_to_ste_code ||
4733 'p_cle_id- '||p_cle_id );
4734 END IF;
4735
4736 IF (p_cle_id is NOT NULL) THEN -- implies line or subline level
4737
4738 IF (l_debug = 'Y') THEN
4739 okc_debug.log('2300: p_cle_id is not null; Change Status called from line/subline level');
4740 END IF;
4741
4742 Open get_line_lvl_csr;
4743 Fetch get_line_lvl_csr into l_cle_id;
4744 Close get_line_lvl_csr;
4745
4746 IF (l_cle_id is NOT NULL) THEN --p_cle_id is a subline
4747
4748 IF (l_debug = 'Y') THEN
4749 okc_debug.log('2310: Updating topline of the subline due to status change of the subline');
4750 END IF;
4751
4752 Open get_subline_amt_csr(p_cle_id);
4753 Fetch get_subline_amt_csr into l_sub_line_amt;
4754 Close get_subline_amt_csr;
4755
4756 IF ((p_from_ste_code = 'ENTERED' ) AND (p_to_ste_code = 'CANCELLED')) THEN
4757
4758 IF (l_debug = 'Y') THEN
4759 okc_debug.log('2320: ENTERED -> CANCELLED; Updating price_negotiated and cancelled_amount for the topline of the subline');
4760 END IF;
4761
4762 Update okc_k_lines_b
4763 set price_negotiated = nvl(price_negotiated,0) - nvl(l_sub_line_amt,0),
4764 cancelled_amount = nvl(cancelled_amount,0) + nvl(l_sub_line_amt,0)
4765 Where id = l_cle_id -- top line id
4766 and dnz_chr_id = p_id;
4767
4768 ELSIF ((p_from_ste_code = 'CANCELLED' ) AND (p_to_ste_code = 'ENTERED')) THEN
4769
4770 IF (l_debug = 'Y') THEN
4771 okc_debug.log('2330: CANCELLED -> ENTERED; Updating price_negotiated and cancelled_amount for the topline of the subline');
4772 END IF;
4773
4774 Update okc_k_lines_b
4775 set price_negotiated = nvl(price_negotiated,0) + nvl(l_sub_line_amt,0),
4776 cancelled_amount = nvl(cancelled_amount,0) - nvl(l_sub_line_amt,0)
4777 Where id = l_cle_id -- top line id
4778 And dnz_chr_id = p_id;
4779
4780
4781 END IF; -- p_to_ste_code ='CANCELLED'
4782
4783 ELSE --l_cle_id is NULL --p_cle_id is a top line
4784
4785 IF (l_debug = 'Y') THEN
4786 okc_debug.log('2400: Updating the topline');
4787 END IF;
4788
4789 IF ((p_from_ste_code = 'ENTERED') AND (p_to_ste_code = 'CANCELLED')) THEN
4790
4791 IF (l_debug = 'Y') THEN
4792 okc_debug.log ('2410: ENTERED -> CANCELLED; Updating price_negotiated and cancelled_amount for topline');
4793 END IF;
4794
4795 Update okc_k_lines_b
4796 set cancelled_amount = nvl(cancelled_amount, 0) + nvl(price_negotiated, 0),
4797 price_negotiated = 0
4798 Where id = p_cle_id
4799 and dnz_chr_id = p_id;
4800
4801
4802 ELSIF ((p_from_ste_code = 'CANCELLED' ) AND (p_to_ste_code = 'ENTERED')) THEN
4803
4804 -- Cursor to get the lse_id of the top line
4805
4806 Open get_lse_id_csr (p_cle_id);
4807 Fetch get_lse_id_csr into l_lse_id;
4808 Close get_lse_id_csr;
4809
4810 IF (l_lse_id = 46 ) THEN --Checking if line type is of SUBSCRIPTION
4811
4812 IF (l_debug = 'Y') THEN
4813 okc_debug.log ('2420: CANCELLED -> ENTERED; Updating price_negotiated and cancelled_amount for SUBSCRIPTION topline');
4814 END IF;
4815
4816 --updating the topline price_negotiated and cancelled_amount for SUBSCRIPTION line type
4817
4818 Update okc_k_lines_b
4819 set price_negotiated = nvl(cancelled_amount,0),
4820 cancelled_amount = 0
4821 Where id = p_cle_id
4822 and dnz_chr_id = p_id;
4823
4824
4825 ELSE -- line type is not of SUBSCRIPTION type
4826
4827 IF (l_debug = 'Y') THEN
4828 okc_debug.log('2430: CANCELLED -> ENTERED; Updating price_negotiated and cancelled_amount for NON-SUBSCRIPTION topline');
4829 END IF;
4830
4831 Open get_uncancelled_amt_csr(p_cle_id, p_id);
4832 Fetch get_uncancelled_amt_csr Into l_uncancelled_amt;
4833 Close get_uncancelled_amt_csr;
4834
4835 -- updating price_negotiated and cancelled_amount for top line which are not of type SUBSCRIPTION
4836
4837 Update okc_k_lines_b
4838 set price_negotiated = nvl(price_negotiated, 0) + nvl(l_uncancelled_amt,0),
4839 cancelled_amount = nvl(cancelled_amount,0) - nvl(l_uncancelled_amt,0)
4840 Where id = p_cle_id
4841 and dnz_chr_id = p_id;
4842
4843
4844 END IF; -- l_lse_id = 46
4845
4846 END IF; -- (p_from_ste_code = 'ENTERED') AND (p_to_ste_code = 'CANCELLED')
4847
4848 END IF; -- l_cle_id is NOT NULL
4849
4850
4851 ELSE -- p_cle_id is NULL --implies action is taken on header
4852
4853 IF (l_debug = 'Y') THEN
4854 okc_debug.log('2500: Updating Header');
4855 END IF;
4856
4857 IF ((p_from_ste_code = 'ENTERED') AND (p_to_ste_code = 'CANCELLED')) THEN
4858
4859 IF (l_debug = 'Y') THEN
4860 okc_debug.log('2510: ENTERED -> CANCELLED; Updating price_negotiated and cancelled_amount for all toplines of contract');
4861 END IF;
4862
4863 -- updating price_negotiated and cancelled_amount for all the top lines of the contract
4864
4865 update okc_k_lines_b
4866 set cancelled_amount = nvl(cancelled_amount, 0) + nvl(price_negotiated, 0),
4867 price_negotiated = 0
4868 where dnz_chr_id = p_id
4869 and cle_id is NULL
4870 and lse_id in (1, 12, 14, 19, 46);
4871
4872
4873 ELSIF ((p_from_ste_code = 'CANCELLED' ) AND (p_to_ste_code = 'ENTERED')) THEN
4874
4875 IF (l_debug = 'Y') THEN
4876 okc_debug.log('2520: CANCELLED -> ENTERED; Updating price_negotiated and cancelled_amount for all toplines of contract');
4877 END IF;
4878
4879 -- Bug Fix 5026369 maanand 08-FEB-2006
4880 For topline In get_topline_id_csr
4881 Loop
4882
4883 Open get_uncancelled_amt_csr(topline.id, p_id);
4884 Fetch get_uncancelled_amt_csr Into l_uncancelled_amt;
4885 Close get_uncancelled_amt_csr;
4886
4887 -- updating price_negotiated and cancelled_amount for selected top line
4888
4889 Update okc_k_lines_b
4890 set price_negotiated = nvl(price_negotiated, 0) + nvl(l_uncancelled_amt,0),
4891 cancelled_amount = nvl(cancelled_amount,0) - nvl(l_uncancelled_amt,0)
4892 Where id = topline.id
4893 and dnz_chr_id = p_id;
4894
4895 End Loop;
4896
4897
4898 /**
4899 -- updating price_negotiated and cancelled_amount for all the top lines of the contract
4900
4901 update okc_k_lines_b
4902 set price_negotiated = nvl(price_negotiated, 0) + nvl(cancelled_amount, 0),
4903 cancelled_amount = 0
4904 where dnz_chr_id = p_id
4905 and cle_id is NULL
4906 and lse_id in (1, 12, 14, 19, 46);
4907
4908 **/
4909
4910 -- Bug Fix 5026369 maanand
4911
4912 END IF; --(p_from_ste_code = 'ENTERED') AND (p_to_ste_code = 'CANCELLED')
4913
4914 END IF; --p_cle_id is NULL
4915
4916 IF (l_debug = 'Y') THEN
4917 okc_debug.log('2600: Updating header estimated_amount and cancelled_amount');
4918 END IF;
4919
4920 -- updating estimated_amount, cancelled_amount for header level of the contract due to change in the status of line/subline/contract
4921
4922 Open get_hdr_amts_csr;
4923 Fetch get_hdr_amts_csr Into l_hdr_estimated_amt, l_hdr_cancelled_amt;
4924 Close get_hdr_amts_csr;
4925
4926 Update okc_k_headers_b
4927 set estimated_amount = l_hdr_estimated_amt,
4928 cancelled_amount = l_hdr_cancelled_amt
4929 where id = p_id;
4930
4931 ---
4932
4933 x_return_status := FND_API.G_RET_STS_SUCCESS;
4934
4935 ---
4936 Exception
4937
4938 WHEN FND_API.G_EXC_ERROR THEN
4939 x_return_status := FND_API.G_RET_STS_ERROR;
4940
4941 IF (l_debug = 'Y') THEN
4942 okc_debug.log('2700: Leaving OKC_CONTRACT_PVT, one or more mandatory parameters missing :FND_API.G_EXC_ERROR');
4943 END IF;
4944
4945 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4946 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4947
4948 IF (l_debug = 'Y') THEN
4949 okc_debug.log('2710: Leaving OKC_CONTRACT_PVT: FND_API.G_EXC_UNEXPECTED_ERROR '|| SQLERRM);
4950 END IF;
4951
4952 WHEN OTHERS THEN
4953 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4954
4955 IF (l_debug = 'Y') THEN
4956 okc_debug.log('2720: Leaving OKC_CONTRACT_PVT because of EXCEPTION: '||sqlerrm);
4957 END IF;
4958
4959 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
4960 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name, SQLERRM );
4961 END IF;
4962
4963 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
4964
4965
4966 END UPDATE_CONTRACT_AMOUNT;
4967
4968 --
4969
4970
4971
4972 --[llc] Cancelled Amount Calculation
4973
4974 /*
4975 These functions [ Get_hdr_cancelled_amount() and Get_line_cancelled_amount() ]
4976 calculates the cancelled amounts for a contract or a contract line.
4977 Cancellation amount is exclusive of the tax amount.
4978
4979 These functions are called from post_query of oks_headers and oks_lines respectively.
4980 This will populate the appropriate fields on the form.
4981 */
4982
4983 FUNCTION Get_hdr_cancelled_amount (p_id Number) RETURN NUMBER IS
4984
4985 l_hdr_cancelled_amt number := 0;
4986
4987 Cursor get_hdr_cancelled_amt IS
4988 SELECT cancelled_amount
4989 FROM okc_k_headers_b
4990 WHERE id = p_id;
4991
4992
4993 BEGIN
4994
4995 open get_hdr_cancelled_amt;
4996 fetch get_hdr_cancelled_amt into l_hdr_cancelled_amt;
4997 close get_hdr_cancelled_amt;
4998
4999 RETURN l_hdr_cancelled_amt;
5000
5001 End Get_hdr_cancelled_amount;
5002 --
5003
5004 FUNCTION Get_line_cancelled_amount (p_cle_id Number, p_id Number) RETURN NUMBER IS
5005
5006 l_line_cancelled_amt NUMBER := 0;
5007 l_cle_id NUMBER := NULL;
5008
5009
5010 Cursor get_top_line_cancelled_amt IS
5011 SELECT nvl(cancelled_amount, 0)
5012 FROM okc_k_lines_b
5013 WHERE id = p_cle_id
5014 and dnz_chr_id = p_id;
5015
5016 Cursor get_sub_line_cancelled_amt IS
5017 SELECT nvl(price_negotiated, 0)
5018 FROM okc_k_lines_b
5019 WHERE id = p_cle_id
5020 and dnz_chr_id = p_id
5021 and date_cancelled is not null;
5022
5023 Cursor get_line_lvl_csr IS
5024 SELECT cle_id
5025 FROM okc_k_lines_b
5026 WHERE id = p_cle_id
5027 and dnz_chr_id = p_id;
5028
5029
5030 BEGIN
5031
5032 Open get_line_lvl_csr;
5033 Fetch get_line_lvl_csr into l_cle_id;
5034 Close get_line_lvl_csr;
5035
5036 IF (l_cle_id is NOT NULL) THEN -- p_cle_id is a subline
5037
5038 Open get_sub_line_cancelled_amt;
5039 Fetch get_sub_line_cancelled_amt into l_line_cancelled_amt;
5040 Close get_sub_line_cancelled_amt;
5041
5042 ELSE -- p_cle_id is a topline
5043
5044 Open get_top_line_cancelled_amt;
5045 Fetch get_top_line_cancelled_amt into l_line_cancelled_amt;
5046 Close get_top_line_cancelled_amt;
5047
5048 END IF;
5049
5050 RETURN l_line_cancelled_amt;
5051
5052 End Get_line_cancelled_amount;
5053
5054 ---
5055
5056
5057 --[llc] Line_Renewal_links
5058
5059 /* Procedure to clear/relink renewal links */
5060
5061 Procedure Line_Renewal_links (
5062 p_api_version IN NUMBER,
5063 x_return_status OUT NOCOPY VARCHAR2,
5064 x_msg_count OUT NOCOPY NUMBER,
5065 x_msg_data OUT NOCOPY VARCHAR2,
5066 p_target_chr_id IN NUMBER ,
5067 p_target_line_id IN NUMBER ,
5068 clean_relink_flag IN VARCHAR2)
5069 is
5070
5071 l_source_code VARCHAR2(30):= 'DUMMY';
5072 l_api_name VARCHAR2(30) := 'CLEAN_REN_LINKS';
5073 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5074 l_active_yn VARCHAR2(1) := 'N';
5075
5076 l_object_cle_id NUMBER;
5077
5078
5079 --Fix Bug#4927824 18-JAN-2006 maanand
5080
5081 cursor trn_source_code (p_target_line_id NUMBER) IS
5082 Select term_cancel_source, object_cle_id
5083 from okc_k_lines_b olb, okc_operation_lines opl
5084 where olb.id= p_target_line_id
5085 and ( ( opl.object_cle_id = olb.id )
5086
5087 OR
5088
5089 (opl.subject_cle_id= olb.id )
5090 );
5091
5092 BEGIN
5093 x_return_status := OKC_API.G_RET_STS_SUCCESS;
5094
5095 -- set flag to update
5096 IF (NVL(clean_relink_flag,'CLEAN') = 'RELINK') Then
5097 l_active_yn := 'Y';
5098 END IF;
5099
5100 -- Get the source code of the line (MANUAL, IBTRANSFER, IBRETURN, IBTERMINATE, IBREPLACE)
5101 Open trn_source_code(p_target_line_id);
5102 Fetch trn_source_code into l_source_code, l_object_cle_id;
5103 Close trn_source_code;
5104
5105 --Fix Bug#4927824 18-JAN-2006 maanand
5106
5107 l_source_code := nvl(l_source_code, 'DUMMY');
5108
5109 IF ( (l_source_code NOT IN ('IBTRANSFER', 'IBRETURN', 'IBTERMINATE', 'IBREPLACE')) AND ( l_object_cle_id is not null) )
5110 THEN
5111
5112 --Fix Bug#4927824 18-JAN-2006 maanand
5113 --Made changes to this query to make is semanitically correct
5114
5115 UPDATE okc_operation_lines
5116 SET active_yn = l_active_yn,
5117 object_version_number = object_version_number + 1,
5118 last_updated_by = FND_GLOBAL.USER_ID,
5119 last_update_date = sysdate,
5120 last_update_login = FND_GLOBAL.LOGIN_ID
5121
5122 where subject_cle_id in ( select id
5123 from okc_k_lines_b kle1
5124 start with kle1.id = p_target_line_id
5125 connect by prior kle1.id = kle1.cle_id
5126 and kle1.dnz_chr_id = p_target_chr_id)
5127
5128 and subject_chr_id = p_target_chr_id;
5129
5130
5131 -- clear date_renewed only for those lines in target top lines
5132
5133 UPDATE OKC_K_LINES_B
5134 SET date_renewed = decode(l_active_yn,'Y',sysdate,null),
5135 object_version_number = object_version_number + 1,
5136 last_updated_by = FND_GLOBAL.USER_ID,
5137 last_update_date = sysdate,
5138 last_update_login = FND_GLOBAL.LOGIN_ID
5139 WHERE id in
5140 ( SELECT object_cle_id
5141 FROM okc_operation_lines ol1
5142 WHERE ol1.subject_cle_id = p_target_line_id );
5143
5144
5145 -- clear date_renewed only for those lines in target sub lines
5146
5147 UPDATE OKC_K_LINES_B
5148 SET date_renewed = decode(l_active_yn,'Y',sysdate,null),
5149 object_version_number = object_version_number + 1,
5150 last_updated_by = FND_GLOBAL.USER_ID,
5151 last_update_date = sysdate,
5152 last_update_login = FND_GLOBAL.LOGIN_ID
5153 WHERE cle_id in
5154 ( SELECT object_cle_id
5155 FROM okc_operation_lines ol1
5156 WHERE ol1.subject_cle_id = p_target_line_id );
5157
5158
5159 END IF;
5160
5161 --Set minor version for updated contracts
5162 --
5163 x_return_status := update_minor_version(p_target_chr_id);
5164
5165 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5166 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5167 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
5168 raise OKC_API.G_EXCEPTION_ERROR;
5169 END IF;
5170
5171
5172 OKC_CVM_PVT.g_trans_id := 'XX-XX';
5173
5174 EXCEPTION
5175 WHEN OKC_API.G_EXCEPTION_ERROR THEN
5176 x_return_status := OKC_API.HANDLE_EXCEPTIONS
5177 (
5178 l_api_name,
5179 G_PKG_NAME,
5180 'OKC_API.G_RET_STS_ERROR',
5181 x_msg_count,
5182 x_msg_data,
5183 '_PVT'
5184 );
5185 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5186 x_return_status := OKC_API.HANDLE_EXCEPTIONS
5187 (
5188 l_api_name,
5189 G_PKG_NAME,
5190 'OKC_API.G_RET_STS_UNEXP_ERROR',
5191 x_msg_count,
5192 x_msg_data,
5193 '_PVT'
5194 );
5195 WHEN OTHERS THEN
5196 x_return_status := OKC_API.HANDLE_EXCEPTIONS
5197 (
5198 l_api_name,
5199 G_PKG_NAME,
5200 'OTHERS',
5201 x_msg_count,
5202 x_msg_data,
5203 '_PVT'
5204 );
5205 END Line_Renewal_links;
5206
5207
5208 END OKC_CONTRACT_PVT;