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