[Home] [Help]
PACKAGE BODY: APPS.OKE_CONTRACT_PVT
Source
1 PACKAGE BODY OKE_CONTRACT_PVT AS
2 /* $Header: OKEVCCCB.pls 120.1 2005/10/03 12:52:08 ausmani noship $ */
3
4
5 -- GLOBAL VARIABLES
6
7 G_APP_NAME CONSTANT VARCHAR2(3) := OKE_API.G_APP_NAME;
8 G_NO_PARENT_RECORD CONSTANT VARCHAR2(200) := 'OKE_NO_PARENT_RECORD';
9 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKE_CONTRACTS_UNEXPECTED_ERROR';
10
11 G_PARENT_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKE_API.G_PARENT_TABLE_TOKEN;
12 G_CHILD_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKE_API.G_CHILD_TABLE_TOKEN;
13 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
14 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
15 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKE_API.G_REQUIRED_VALUE;
16 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKE_API.G_INVALID_VALUE;
17 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKE_API.G_COL_NAME_TOKEN;
18 G_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKE_API.G_CHILD_TABLE_TOKEN;
19 G_EXCEPTION_HALT_VALIDATION exception;
20
21 NO_CONTRACT_FOUND exception;
22
23 G_NO_UPDATE_ALLOWED_EXCEPTION exception;
24 G_NO_UPDATE_ALLOWED CONSTANT VARCHAR2(200) := 'OKE_NO_UPDATE_ALLOWED';
25 G_EXCEPTION_HALT_PROCESS exception;
26
27 PROCEDURE create_contract_header(
28 p_api_version IN NUMBER,
29 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
30 x_return_status OUT NOCOPY VARCHAR2,
31 x_msg_count OUT NOCOPY NUMBER,
32 x_msg_data OUT NOCOPY VARCHAR2,
33 p_chr_rec IN OKE_CHR_PVT.chr_rec_type,
34 x_chr_rec OUT NOCOPY OKE_CHR_PVT.chr_rec_type) IS
35
36 l_chr_rec OKE_CHR_PVT.chr_rec_type := p_chr_rec;
37 BEGIN
38 -- initialize return status
39 x_return_status := OKE_API.G_RET_STS_SUCCESS;
40
41
42
43 OKE_CHR_PVT.Insert_Row(
44 p_api_version => p_api_version,
45 p_init_msg_list => p_init_msg_list,
46 x_return_status => x_return_status,
47 x_msg_count => x_msg_count,
48 x_msg_data => x_msg_data,
49 p_chr_rec => l_chr_rec,
50 x_chr_rec => x_chr_rec);
51
52 END create_contract_header;
53
54 FUNCTION Increment_Minor_Version(p_chr_id IN NUMBER) RETURN VARCHAR2 IS
55
56 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
57
58 BEGIN
59
60 RETURN(l_return_status);
61
62 END;
63
64 PROCEDURE create_contract_header(
65 p_api_version IN NUMBER,
66 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
67 x_return_status OUT NOCOPY VARCHAR2,
68 x_msg_count OUT NOCOPY NUMBER,
69 x_msg_data OUT NOCOPY VARCHAR2,
70 p_chr_tbl IN OKE_CHR_PVT.chr_tbl_type,
71 x_chr_tbl OUT NOCOPY OKE_CHR_PVT.chr_tbl_type) IS
72
73 BEGIN
74
75 OKE_CHR_PVT.Insert_Row(
76
77 p_api_version => p_api_version,
78 p_init_msg_list => p_init_msg_list,
79 x_return_status => x_return_status,
80 x_msg_count => x_msg_count,
81 x_msg_data => x_msg_data,
82 p_chr_tbl => p_chr_tbl,
83 x_chr_tbl => x_chr_tbl);
84
85 END create_contract_header;
86
87 PROCEDURE update_contract_header(
88 p_api_version IN NUMBER,
89 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
90 x_return_status OUT NOCOPY VARCHAR2,
91 x_msg_count OUT NOCOPY NUMBER,
92 x_msg_data OUT NOCOPY VARCHAR2,
93 p_chr_rec IN OKE_CHR_PVT.chr_rec_type,
94 x_chr_rec OUT NOCOPY OKE_CHR_PVT.chr_rec_type) IS
95
96 BEGIN
97
98 OKE_CHR_PVT.Update_Row(
99 p_api_version => p_api_version,
100 p_init_msg_list => p_init_msg_list,
101 x_return_status => x_return_status,
102 x_msg_count => x_msg_count,
103 x_msg_data => x_msg_data,
104 p_chr_rec => p_chr_rec,
105 x_chr_rec => x_chr_rec);
106
107
108 exception
109 when OTHERS then
110 -- store SQL error message on message stack
111 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
112 p_msg_name => g_unexpected_error,
113 p_token1 => g_sqlcode_token,
114 p_token1_value => sqlcode,
115 p_token2 => g_sqlerrm_token,
116 p_token2_value => sqlerrm);
117
118 -- notify caller of an UNEXPETED error
119 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
120 END update_contract_header;
121
122 PROCEDURE update_contract_header(
123 p_api_version IN NUMBER,
124 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
125 x_return_status OUT NOCOPY VARCHAR2,
126 x_msg_count OUT NOCOPY NUMBER,
127 x_msg_data OUT NOCOPY VARCHAR2,
128 p_chr_tbl IN OKE_CHR_PVT.chr_tbl_type,
129 x_chr_tbl OUT NOCOPY OKE_CHR_PVT.chr_tbl_type) IS
130
131 BEGIN
132
133 OKE_CHR_PVT.Update_Row(
134 p_api_version => p_api_version,
135 p_init_msg_list => p_init_msg_list,
136 x_return_status => x_return_status,
137 x_msg_count => x_msg_count,
138 x_msg_data => x_msg_data,
139 p_chr_tbl => p_chr_tbl,
140 x_chr_tbl => x_chr_tbl);
141 END update_contract_header;
142
143 PROCEDURE delete_contract_header(
144 p_api_version IN NUMBER,
145 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
146 x_return_status OUT NOCOPY VARCHAR2,
147 x_msg_count OUT NOCOPY NUMBER,
148 x_msg_data OUT NOCOPY VARCHAR2,
149 p_chr_rec IN OKE_CHR_PVT.chr_rec_type) IS
150
151
152
153 BEGIN
154
155 OKE_CHR_PVT.delete_row(
156 p_api_version => p_api_version,
157 p_init_msg_list => p_init_msg_list,
158 x_return_status => x_return_status,
159 x_msg_count => x_msg_count,
160 x_msg_data => x_msg_data,
161 p_chr_rec => p_chr_rec);
162 EXCEPTION
163
164 when OTHERS then
165 -- store SQL error message on message stack
166 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
167 p_msg_name => g_unexpected_error,
168 p_token1 => g_sqlcode_token,
169 p_token1_value => sqlcode,
170 p_token2 => g_sqlerrm_token,
171 p_token2_value => sqlerrm);
172
173 -- notify caller of an UNEXPETED error
174 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
175 END delete_contract_header;
176
177
178
179 PROCEDURE delete_contract_header(
180 p_api_version IN NUMBER,
181 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
182 x_return_status OUT NOCOPY VARCHAR2,
183 x_msg_count OUT NOCOPY NUMBER,
184 x_msg_data OUT NOCOPY VARCHAR2,
185 p_chr_tbl IN OKE_CHR_PVT.chr_tbl_type) IS
186
187 BEGIN
188 OKE_CHR_PVT.Delete_Row(
189 p_api_version => p_api_version,
190 p_init_msg_list => p_init_msg_list,
191 x_return_status => x_return_status,
192 x_msg_count => x_msg_count,
193 x_msg_data => x_msg_data,
194 p_chr_tbl => p_chr_tbl);
195 END delete_contract_header;
196
197 PROCEDURE validate_contract_header(
198 p_api_version IN NUMBER,
199 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
200 x_return_status OUT NOCOPY VARCHAR2,
201 x_msg_count OUT NOCOPY NUMBER,
202 x_msg_data OUT NOCOPY VARCHAR2,
203 p_chr_rec IN OKE_CHR_PVT.chr_rec_type) IS
204
205 BEGIN
206 OKE_CHR_PVT.Validate_Row(
207 p_api_version => p_api_version,
208 p_init_msg_list => p_init_msg_list,
209 x_return_status => x_return_status,
210 x_msg_count => x_msg_count,
211 x_msg_data => x_msg_data,
212 p_chr_rec => p_chr_rec);
213 END validate_contract_header;
214
215 PROCEDURE validate_contract_header(
216 p_api_version IN NUMBER,
217 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
218 x_return_status OUT NOCOPY VARCHAR2,
219 x_msg_count OUT NOCOPY NUMBER,
220 x_msg_data OUT NOCOPY VARCHAR2,
221 p_chr_tbl IN OKE_CHR_PVT.chr_tbl_type) IS
222
223 BEGIN
224 OKE_CHR_PVT.Validate_Row(
225 p_api_version => p_api_version,
226 p_init_msg_list => p_init_msg_list,
227 x_return_status => x_return_status,
228 x_msg_count => x_msg_count,
229 x_msg_data => x_msg_data,
230 p_chr_tbl => p_chr_tbl);
231 END validate_contract_header;
232
233 PROCEDURE create_contract_line(
234 p_api_version IN NUMBER,
235 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
236 x_return_status OUT NOCOPY VARCHAR2,
237 x_msg_count OUT NOCOPY NUMBER,
238 x_msg_data OUT NOCOPY VARCHAR2,
239 p_cle_rec IN OKE_CLE_PVT.cle_rec_type,
240 x_cle_rec OUT NOCOPY OKE_CLE_PVT.cle_rec_type) IS
241
242 l_cle_rec OKE_CLE_PVT.cle_rec_type := p_cle_rec;
243 BEGIN
244 -- initialize return status
245 x_return_status := OKE_API.G_RET_STS_SUCCESS;
246
247
248
249 OKE_CLE_PVT.Insert_Row(
250 p_api_version => p_api_version,
251 p_init_msg_list => p_init_msg_list,
252 x_return_status => x_return_status,
253 x_msg_count => x_msg_count,
254 x_msg_data => x_msg_data,
255 p_cle_rec => l_cle_rec,
256 x_cle_rec => x_cle_rec);
257
258 END create_contract_line;
259
260 PROCEDURE create_contract_line(
261 p_api_version IN NUMBER,
262 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
263 x_return_status OUT NOCOPY VARCHAR2,
264 x_msg_count OUT NOCOPY NUMBER,
265 x_msg_data OUT NOCOPY VARCHAR2,
266 p_cle_tbl IN OKE_CLE_PVT.cle_tbl_type,
267 x_cle_tbl OUT NOCOPY OKE_CLE_PVT.cle_tbl_type) IS
268
269 BEGIN
270
271 OKE_CLE_PVT.Insert_Row(
272
273 p_api_version => p_api_version,
274 p_init_msg_list => p_init_msg_list,
275 x_return_status => x_return_status,
276 x_msg_count => x_msg_count,
277 x_msg_data => x_msg_data,
278 p_cle_tbl => p_cle_tbl,
279 x_cle_tbl => x_cle_tbl);
280
281 END create_contract_line;
282
283 PROCEDURE update_contract_line(
284 p_api_version IN NUMBER,
285 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
286 x_return_status OUT NOCOPY VARCHAR2,
287 x_msg_count OUT NOCOPY NUMBER,
288 x_msg_data OUT NOCOPY VARCHAR2,
289 p_cle_rec IN OKE_CLE_PVT.cle_rec_type,
290 x_cle_rec OUT NOCOPY OKE_CLE_PVT.cle_rec_type) IS
291
292 BEGIN
293
294
295
296 OKE_CLE_PVT.Update_Row(
297 p_api_version => p_api_version,
298 p_init_msg_list => p_init_msg_list,
299 x_return_status => x_return_status,
300 x_msg_count => x_msg_count,
301 x_msg_data => x_msg_data,
302 p_cle_rec => p_cle_rec,
303 x_cle_rec => x_cle_rec);
304
305
306 exception
307 when OTHERS then
308 -- store SQL error message on message stack
309 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
310 p_msg_name => g_unexpected_error,
311 p_token1 => g_sqlcode_token,
312 p_token1_value => sqlcode,
313 p_token2 => g_sqlerrm_token,
314 p_token2_value => sqlerrm);
315
316 -- notify caller of an UNEXPETED error
317 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
318 END update_contract_line;
319
320 PROCEDURE update_contract_line(
321 p_api_version IN NUMBER,
322 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
323 x_return_status OUT NOCOPY VARCHAR2,
324 x_msg_count OUT NOCOPY NUMBER,
325 x_msg_data OUT NOCOPY VARCHAR2,
326 p_cle_tbl IN OKE_CLE_PVT.cle_tbl_type,
327 x_cle_tbl OUT NOCOPY OKE_CLE_PVT.cle_tbl_type) IS
328
329 BEGIN
330 OKE_CLE_PVT.Update_Row(
331 p_api_version => p_api_version,
332 p_init_msg_list => p_init_msg_list,
333 x_return_status => x_return_status,
334 x_msg_count => x_msg_count,
335 x_msg_data => x_msg_data,
336 p_cle_tbl => p_cle_tbl,
337 x_cle_tbl => x_cle_tbl);
338 END update_contract_line;
339
340 PROCEDURE delete_contract_line(
341 p_api_version IN NUMBER,
342 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
343 x_return_status OUT NOCOPY VARCHAR2,
344 x_msg_count OUT NOCOPY NUMBER,
345 x_msg_data OUT NOCOPY VARCHAR2,
346 p_cle_rec IN OKE_CLE_PVT.cle_rec_type) IS
347
348 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
349 l_chr_id NUMBER;
350 l_dummy_val NUMBER;
351 CURSOR l_csr IS
352 SELECT COUNT(*)
353 FROM OKE_K_LINES_V
354 WHERE PARENT_LINE_ID = p_cle_rec.K_LINE_ID;
355
356 BEGIN
357 OPEN l_csr;
358 FETCH l_csr INTO l_dummy_val;
359 CLOSE l_csr;
360
361 -- delete only if there are no detail records
362 IF (l_dummy_val = 0) THEN
363
364 OKE_CLE_PVT.delete_row(
365 p_api_version => p_api_version,
366 p_init_msg_list => p_init_msg_list,
367 x_return_status => x_return_status,
368 x_msg_count => x_msg_count,
369 x_msg_data => x_msg_data,
370 p_cle_rec => p_cle_rec);
371 ELSE
372 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
373 p_msg_name => g_no_parent_record,
374 p_token1 => g_child_table_token,
375 p_token1_value => 'OKE_K_LINES_full_V',
376 p_token2 => g_parent_table_token,
377 p_token2_value => 'OKE_K_LINES_V');
378 -- notify caller of an error
379 x_return_status := OKE_API.G_RET_STS_ERROR;
380 End If;
381
382 EXCEPTION
383 when OTHERS then
384 -- store SQL error message on message stack
385 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
386 p_msg_name => g_unexpected_error,
387 p_token1 => g_sqlcode_token,
388 p_token1_value => sqlcode,
389 p_token2 => g_sqlerrm_token,
390 p_token2_value => sqlerrm);
391
392 -- notify caller of an UNEXPETED error
393 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
394 END delete_contract_line;
395
396
397
398 PROCEDURE delete_contract_line(
399 p_api_version IN NUMBER,
400 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
401 x_return_status OUT NOCOPY VARCHAR2,
402 x_msg_count OUT NOCOPY NUMBER,
403 x_msg_data OUT NOCOPY VARCHAR2,
404 p_cle_tbl IN OKE_CLE_PVT.cle_tbl_type) IS
405
406 BEGIN
407 OKE_CLE_PVT.Delete_Row(
408 p_api_version => p_api_version,
409 p_init_msg_list => p_init_msg_list,
410 x_return_status => x_return_status,
411 x_msg_count => x_msg_count,
412 x_msg_data => x_msg_data,
413 p_cle_tbl => p_cle_tbl);
414 END delete_contract_line;
415
416 PROCEDURE delete_contract_line(
417 p_api_version IN NUMBER,
418 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
419 x_return_status OUT NOCOPY VARCHAR2,
420 x_msg_count OUT NOCOPY NUMBER,
421 x_msg_data OUT NOCOPY VARCHAR2,
422 p_line_id IN NUMBER) IS
423
424
425 l_cle_Id NUMBER;
426 v_Index Binary_Integer;
427
428 CURSOR Child_Cur1(P_Parent_Id IN NUMBER)
429 IS SELECT ID
430 FROM OKc_K_Lines_b
431 WHERE cle_id=P_Parent_Id;
432
433 CURSOR Child_Cur2(P_Parent_Id IN NUMBER)
434 IS SELECT ID
435 FROM Okc_K_Lines_b
436 WHERE cle_Id=P_Parent_Id;
437
438 CURSOR Child_Cur3(P_Parent_Id IN NUMBER)
439 IS SELECT ID
440 FROM Okc_K_Lines_b
441 WHERE cle_Id=P_Parent_Id;
442
443 CURSOR Child_Cur4(P_Parent_Id IN NUMBER)
444 IS SELECT ID
445 FROM Okc_K_Lines_b
446 WHERE cle_Id=P_Parent_Id;
447
448 CURSOR Child_Cur5(P_Parent_Id IN NUMBER)
449 IS SELECT ID
450 FROM Okc_K_Lines_b
451 WHERE cle_Id=P_Parent_Id;
452
453 n NUMBER:=0;
454 l_cle_tbl_in OKE_CLE_PVT.cle_tbl_type;
455 l_cle_tbl_tmp OKE_CLE_PVT.cle_tbl_type;
456
457 l_api_version CONSTANT NUMBER := 1.0;
458 l_init_msg_list CONSTANT VARCHAR2(1) := 'T';
459 l_return_status VARCHAR2(1);
460 l_msg_count NUMBER;
461 l_msg_data VARCHAR2(2000):=null;
462 l_msg_index_out Number;
463 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Contract_Line';
464 e_error Exception;
465 c_clev NUMBER:=1;
466
467 l_lse_Id NUMBER;
468
469
470 -- PROCEDURE Validate_Line_id
471
472 PROCEDURE Validate_Line_id(
473
474 p_line_id IN NUMBER,
475 x_return_status OUT NOCOPY VARCHAR2) IS
476 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
477 l_Count NUMBER;
478 CURSOR Cur_Line(P_Line_Id IN NUMBER) IS
479 SELECT COUNT(*) FROM OKC_K_LINES_V
480 WHERE id=P_Line_Id;
481 BEGIN
482 IF P_Line_id = OKE_API.G_MISS_NUM OR
483 P_Line_Id IS NULL
484 THEN
485
486
487 OKE_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'P_Lin
488 e_Id');
489
490
491 l_return_status := OKE_API.G_RET_STS_ERROR;
492 END IF;
493
494 OPEN Cur_Line(P_LIne_Id);
495 FETCH Cur_Line INTO l_Count;
496 CLOSE Cur_Line;
497 IF NOT l_Count = 1
498
499 THEN
500 OKE_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'P_Line
501 _Id');
502
503
504 l_return_status := OKE_API.G_RET_STS_ERROR;
505 END IF;
506 x_return_status := l_return_status;
507 EXCEPTION
508 WHEN OTHERS THEN
509 -- store SQL error message on message stack for caller
510
511 OKE_API.set_message(G_APP_NAME,
512 G_UNEXPECTED_ERROR,
513 G_SQLCODE_TOKEN,
514 SQLCODE,
515 G_SQLERRM_TOKEN,
516 SQLERRM);
517 -- notify caller of an UNEXPECTED error
518 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
519 END Validate_Line_id;
520 BEGIN
521 x_return_status:=OKE_API.G_RET_STS_SUCCESS;
522
523 Validate_Line_id(p_line_id,l_return_status);
524 IF NOT l_Return_Status ='S'
525 THEN RETURN;
526 END IF;
527
528 l_cle_tbl_tmp(c_clev).K_LINE_ID:=P_Line_Id;
529 c_clev:=c_clev+1;
530 FOR Child_Rec1 IN Child_Cur1(P_Line_Id)
531 LOOP
532 l_cle_tbl_tmp(c_clev).K_LINE_ID:=Child_Rec1.ID;
533 c_clev:=c_clev+1;
534 FOR Child_Rec2 IN Child_Cur2(Child_Rec1.Id)
535
536 LOOP
537 l_cle_tbl_tmp(c_clev).K_LINE_ID:=Child_Rec2.Id;
538 c_clev:=c_clev+1;
539 FOR Child_Rec3 IN Child_Cur3(Child_Rec2.Id)
540 LOOP
541 l_cle_tbl_tmp(c_clev).K_LINE_ID:=Child_Rec3.Id;
542 c_clev:=c_clev+1;
543 FOR Child_Rec4 IN Child_Cur4(Child_Rec3.Id)
544 LOOP
545 l_cle_tbl_tmp(c_clev).K_LINE_ID:=Child_Rec4.Id;
546 c_clev:=c_clev+1;
547 FOR Child_Rec5 IN Child_Cur5(Child_Rec4.Id)
548 LOOP
549 l_cle_tbl_tmp(c_clev).K_LINE_ID:=Child_Rec5.Id;
550 c_clev:=c_clev+1;
551 END LOOP;
552 END LOOP;
553 END LOOP;
554 END LOOP;
555 END LOOP;
556 c_clev:=1;
557 FOR v_Index IN REVERSE l_cle_tbl_tmp.FIRST .. l_cle_tbl_tmp.LAST
558
559 LOOP
560 l_cle_tbl_in(c_clev).K_LINE_ID:= l_cle_tbl_tmp(v_Index).K_LINE_ID;
561 c_clev:=c_Clev+1;
562 END LOOP;
563
564 -- get objects linked to the line
565 -- delete check goes here
566
567 IF NOT l_cle_tbl_in.COUNT=0
568 THEN
569 delete_contract_line(
570
571 p_api_version => l_api_version,
572 p_init_msg_list => l_init_msg_list,
573 x_return_status => l_return_status,
574 x_msg_count => l_msg_count,
575 x_msg_data => l_msg_data,
576 p_cle_tbl => l_cle_tbl_in);
577
578 IF nvl(l_return_status,'*') <> 'S'
579 THEN
580 IF l_msg_count > 0
581 THEN
582
583 FOR i in 1..l_msg_count
584 LOOP
585 fnd_msg_pub.get (p_msg_index => -1,
586 p_encoded => 'T', -- OKC$APPLICATION.GET_FALSE,
587
588
589 p_data => l_msg_data,
590 p_msg_index_out => l_msg_index_out);
591 END LOOP;
592 END IF;
593 RAISE e_Error;
594
595 END IF;
596 END IF;
597
598 EXCEPTION
599 WHEN e_Error THEN
600 -- notify caller of an error as UNEXPETED error
601 x_msg_count :=l_msg_count;
602 x_msg_data:=l_msg_data;
603 x_return_status := OKE_API.HANDLE_EXCEPTIONS
604 (
605 l_api_name,
606 'Delete_Contract_Line',
607 'OKE_API.G_RET_STS_ERROR',
608 l_msg_count,
609 l_msg_data,
610 '_PVT'
611 );
612 WHEN OKE_API.G_EXCEPTION_ERROR THEN
613 x_msg_count :=l_msg_count;
614 x_msg_data:=l_msg_data;
615 x_return_status := OKE_API.HANDLE_EXCEPTIONS
616 (
617 l_api_name,
618 'Delete_Contract_Line',
619 'OKE_API.G_RET_STS_ERROR',
620 l_msg_count,
621 l_msg_data,
622 '_PVT'
623 );
624 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
625 x_msg_count :=l_msg_count;
626 x_msg_data:=l_msg_data;
627 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
628 (
629 l_api_name,
630 'Delete_Contract_Line',
631 'OKE_API.G_RET_STS_UNEXP_ERROR',
632 l_msg_count,
633 l_msg_data,
634 '_PVT'
635 );
636 WHEN OTHERS THEN
637 x_msg_count :=l_msg_count;
638 OKE_API.SET_MESSAGE(
639 p_app_name => g_app_name,
640 p_msg_name => g_unexpected_error,
641 p_token1 => g_sqlcode_token,
642 p_token1_value => sqlcode,
643 p_token2 => g_sqlerrm_token,
644 p_token2_value => sqlerrm);
645 -- notify caller of an error as UNEXPETED error
646 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
647
648 END delete_contract_line;
649
650 PROCEDURE validate_contract_line(
651 p_api_version IN NUMBER,
652 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
653 x_return_status OUT NOCOPY VARCHAR2,
654 x_msg_count OUT NOCOPY NUMBER,
655 x_msg_data OUT NOCOPY VARCHAR2,
656 p_cle_rec IN OKE_CLE_PVT.cle_rec_type) IS
657
658 BEGIN
659 OKE_CLE_PVT.Validate_Row(
660 p_api_version => p_api_version,
661 p_init_msg_list => p_init_msg_list,
662 x_return_status => x_return_status,
663 x_msg_count => x_msg_count,
664 x_msg_data => x_msg_data,
665 p_cle_rec => p_cle_rec);
666 END validate_contract_line;
667
668 PROCEDURE validate_contract_line(
669 p_api_version IN NUMBER,
670 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
671 x_return_status OUT NOCOPY VARCHAR2,
672 x_msg_count OUT NOCOPY NUMBER,
673 x_msg_data OUT NOCOPY VARCHAR2,
674 p_cle_tbl IN OKE_CLE_PVT.cle_tbl_type) IS
675
676 BEGIN
677 OKE_CLE_PVT.Validate_Row(
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_cle_tbl => p_cle_tbl);
684 END validate_contract_line;
685
686 -- deliverable section
687
688 PROCEDURE copy_related_entities (p_line_id NUMBER,
689 p_deliverable_id NUMBER,
690 x_return_status OUT NOCOPY VARCHAR2) IS
691
692
693
694 cursor l_standard_notes_csr (p_id NUMBER) is
695 select b.type_code,
696 b.attribute_category,
697 b.attribute1,
698 b.attribute2,
699 b.attribute3,
700 b.attribute4,
701 b.attribute5,
702 b.attribute6,
703 b.attribute7,
704 b.attribute8,
705 b.attribute9,
706 b.attribute10,
707 b.attribute11,
708 b.attribute12,
709 b.attribute13,
710 b.attribute14,
711 b.attribute15,
712 t.sfwt_flag,
713 t.description,
714 t.name,
715 t.text
716 from oke_k_standard_notes_b b, oke_k_standard_notes_tl t
717 where k_line_id = p_id;
718 l_standard_notes l_standard_notes_csr%ROWTYPE;
719
720
721 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
722 l_id NUMBER;
723 BEGIN
724
725 for l_standard_notes in l_standard_notes_csr(p_line_id)
726 loop
727 select oke_k_standard_notes_s.nextval into l_id from dual;
728 insert into oke_k_standard_notes_b
729 (standard_notes_id,
730 creation_date,
731 created_by,
732 last_update_date,
733 last_update_login,
734 last_updated_by,
735 k_header_id,
736 k_line_id,
737 deliverable_id,
738 type_code,
739 attribute_category,
740 attribute1,
741 attribute2,
742 attribute3,
743 attribute4,
744 attribute5,
745 attribute6,
746 attribute7,
747 attribute8,
748 attribute9,
749 attribute10,
750 attribute11,
751 attribute12,
752 attribute13,
753 attribute14,
754 attribute15)
755 values(
756 l_id,
757 sysdate,
758 fnd_global.user_id,
759 sysdate,
760 fnd_global.login_id,
761 fnd_global.user_id,
762 null,
763 null,
764 p_deliverable_id,
765 l_standard_notes.type_code,
766 l_standard_notes.attribute_category,
767 l_standard_notes.attribute1,
768 l_standard_notes.attribute2,
769 l_standard_notes.attribute3,
770 l_standard_notes.attribute4,
771 l_standard_notes.attribute5,
772 l_standard_notes.attribute6,
773 l_standard_notes.attribute7,
774 l_standard_notes.attribute8,
775 l_standard_notes.attribute9,
776 l_standard_notes.attribute10,
777 l_standard_notes.attribute11,
778 l_standard_notes.attribute12,
779 l_standard_notes.attribute13,
780 l_standard_notes.attribute14,
781 l_standard_notes.attribute15);
782 end loop;
783
784 end;
785
786 PROCEDURE create_deliverable(
787 p_api_version IN NUMBER,
788 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
789 x_return_status OUT NOCOPY VARCHAR2,
790 x_msg_count OUT NOCOPY NUMBER,
791 x_msg_data OUT NOCOPY VARCHAR2,
792 p_del_rec IN OKE_DELIVERABLE_PVT.del_rec_type,
793 x_del_rec OUT NOCOPY OKE_DELIVERABLE_PVT.del_rec_type) IS
794
795 l_del_rec OKE_DELIVERABLE_PVT.del_rec_type := p_del_rec;
796 BEGIN
797 -- initialize return status
798 x_return_status := OKE_API.G_RET_STS_SUCCESS;
799
800
801
802 OKE_DELIVERABLE_PVT.Insert_Row(
803 p_api_version => p_api_version,
804 p_init_msg_list => p_init_msg_list,
805 x_return_status => x_return_status,
806 x_msg_count => x_msg_count,
807 x_msg_data => x_msg_data,
808 p_del_rec => l_del_rec,
809 x_del_rec => x_del_rec);
810
811 -- copy related entities
812 /* copy_related_entities(x_del_rec.k_line_id,
813 x_del_rec.deliverable_id,
814 x_return_status);*/
815
816 If x_return_status = OKE_API.G_RET_STS_SUCCESS Then
817 OKE_DTS_WORKFLOW.LAUNCH_MAIN_PROCESS(x_del_rec.deliverable_id,'AUTO');
818 End If;
819
820
821 END create_deliverable;
822
823 PROCEDURE create_deliverable(
824 p_api_version IN NUMBER,
825 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
826 x_return_status OUT NOCOPY VARCHAR2,
827 x_msg_count OUT NOCOPY NUMBER,
828 x_msg_data OUT NOCOPY VARCHAR2,
829 p_del_tbl IN OKE_DELIVERABLE_PVT.del_tbl_type,
830 x_del_tbl OUT NOCOPY OKE_DELIVERABLE_PVT.del_tbl_type) IS
831
832 BEGIN
833
834 OKE_DELIVERABLE_PVT.Insert_Row(
835
836 p_api_version => p_api_version,
837 p_init_msg_list => p_init_msg_list,
838 x_return_status => x_return_status,
839 x_msg_count => x_msg_count,
840 x_msg_data => x_msg_data,
841 p_del_tbl => p_del_tbl,
842 x_del_tbl => x_del_tbl);
843
844 END create_deliverable;
845
846 PROCEDURE update_deliverable(
847 p_api_version IN NUMBER,
848 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
849 x_return_status OUT NOCOPY VARCHAR2,
850 x_msg_count OUT NOCOPY NUMBER,
851 x_msg_data OUT NOCOPY VARCHAR2,
852 p_del_rec IN OKE_DELIVERABLE_PVT.del_rec_type,
853 x_del_rec OUT NOCOPY OKE_DELIVERABLE_PVT.del_rec_type) IS
854
855 BEGIN
856
857
858
859 OKE_DELIVERABLE_PVT.Update_Row(
860 p_api_version => p_api_version,
861 p_init_msg_list => p_init_msg_list,
862 x_return_status => x_return_status,
863 x_msg_count => x_msg_count,
864 x_msg_data => x_msg_data,
865 p_del_rec => p_del_rec,
866 x_del_rec => x_del_rec);
867
868
869 exception
870 when OTHERS then
871 -- store SQL error message on message stack
872 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
873 p_msg_name => g_unexpected_error,
874 p_token1 => g_sqlcode_token,
875 p_token1_value => sqlcode,
876 p_token2 => g_sqlerrm_token,
877 p_token2_value => sqlerrm);
878
879 -- notify caller of an UNEXPETED error
880 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
881 END update_deliverable;
882
883 PROCEDURE update_deliverable(
884 p_api_version IN NUMBER,
885 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
886 x_return_status OUT NOCOPY VARCHAR2,
887 x_msg_count OUT NOCOPY NUMBER,
888 x_msg_data OUT NOCOPY VARCHAR2,
889 p_del_tbl IN OKE_DELIVERABLE_PVT.del_tbl_type,
890 x_del_tbl OUT NOCOPY OKE_DELIVERABLE_PVT.del_tbl_type) IS
891
892 BEGIN
893 OKE_DELIVERABLE_PVT.Update_Row(
894 p_api_version => p_api_version,
895 p_init_msg_list => p_init_msg_list,
896 x_return_status => x_return_status,
897 x_msg_count => x_msg_count,
898 x_msg_data => x_msg_data,
899 p_del_tbl => p_del_tbl,
900 x_del_tbl => x_del_tbl);
901 END update_deliverable;
902
903 PROCEDURE delete_deliverable(
904 p_api_version IN NUMBER,
905 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
906 x_return_status OUT NOCOPY VARCHAR2,
907 x_msg_count OUT NOCOPY NUMBER,
908 x_msg_data OUT NOCOPY VARCHAR2,
909 p_del_rec IN OKE_DELIVERABLE_PVT.del_rec_type) IS
910
911 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
912 l_chr_id NUMBER;
913 l_dummy_val NUMBER;
914 CURSOR l_csr IS
915 SELECT COUNT(*)
916 FROM OKE_K_DELIVERABLES_VL
917 WHERE PARENT_DELIVERABLE_ID = p_del_rec.DELIVERABLE_ID;
918
919 BEGIN
920 OPEN l_csr;
921 FETCH l_csr INTO l_dummy_val;
922 CLOSE l_csr;
923
924 -- delete only if there are no detail records
925 IF (l_dummy_val = 0) THEN
926
927 OKE_DELIVERABLE_PVT.delete_row(
928 p_api_version => p_api_version,
929 p_init_msg_list => p_init_msg_list,
930 x_return_status => x_return_status,
931 x_msg_count => x_msg_count,
932 x_msg_data => x_msg_data,
933 p_del_rec => p_del_rec);
934 ELSE
935 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
936 p_msg_name => g_no_parent_record,
937 p_token1 => g_child_table_token,
938 p_token1_value => 'OKE_K_LINES_full_V',
939 p_token2 => g_parent_table_token,
940 p_token2_value => 'OKE_K_LINES_V');
941 -- notify caller of an error
942 x_return_status := OKE_API.G_RET_STS_ERROR;
943 End If;
944
945 EXCEPTION
946 when OTHERS then
947 -- store SQL error message on message stack
948 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
949 p_msg_name => g_unexpected_error,
950 p_token1 => g_sqlcode_token,
951 p_token1_value => sqlcode,
952 p_token2 => g_sqlerrm_token,
953 p_token2_value => sqlerrm);
954
955 -- notify caller of an UNEXPETED error
956 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
957 END delete_deliverable;
958
959
960
961 PROCEDURE delete_deliverable(
962 p_api_version IN NUMBER,
963 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
964 x_return_status OUT NOCOPY VARCHAR2,
965 x_msg_count OUT NOCOPY NUMBER,
966 x_msg_data OUT NOCOPY VARCHAR2,
967 p_del_tbl IN OKE_DELIVERABLE_PVT.del_tbl_type) IS
968
969 BEGIN
970 OKE_DELIVERABLE_PVT.Delete_Row(
971 p_api_version => p_api_version,
972 p_init_msg_list => p_init_msg_list,
973 x_return_status => x_return_status,
974 x_msg_count => x_msg_count,
975 x_msg_data => x_msg_data,
976 p_del_tbl => p_del_tbl);
977 END delete_deliverable;
978
979 PROCEDURE delete_deliverable(
980 p_api_version IN NUMBER,
981 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
982 x_return_status OUT NOCOPY VARCHAR2,
983 x_msg_count OUT NOCOPY NUMBER,
984 x_msg_data OUT NOCOPY VARCHAR2,
985 p_deliverable_id IN NUMBER) IS
986
987
988 l_del_Id NUMBER;
989 v_Index Binary_Integer;
990
991 CURSOR Child_Cur1(P_Parent_Id IN NUMBER)
992 IS SELECT DELIVERABLE_ID
993 FROM OKE_K_DELIVERABLES_B
994 WHERE parent_deliverable_id=P_Parent_Id;
995
996 CURSOR Child_Cur2(P_Parent_Id IN NUMBER)
997 IS SELECT DELIVERABLE_ID
998 FROM oke_k_deliverables_b
999 WHERE parent_deliverable_id = P_Parent_Id;
1000
1001 CURSOR Child_Cur3(P_Parent_Id IN NUMBER)
1002 IS SELECT DELIVERABLE_ID
1003 FROM oke_k_deliverables_b
1004 WHERE parent_deliverable_id = P_Parent_Id ;
1005
1006 CURSOR Child_Cur4(P_Parent_Id IN NUMBER)
1007 IS SELECT DELIVERABLE_ID
1008 FROM oke_k_deliverables_b
1009 WHERE parent_deliverable_id=P_Parent_Id;
1010
1011 CURSOR Child_Cur5(P_Parent_Id IN NUMBER)
1012 IS SELECT DELIVERABLE_ID
1013 FROM oke_k_deliverables_b
1014 WHERE parent_deliverable_id=P_Parent_Id;
1015
1016 n NUMBER:=0;
1017 l_del_tbl_in OKE_DELIVERABLE_PVT.del_tbl_type;
1018 l_del_tbl_tmp OKE_DELIVERABLE_PVT.del_tbl_type;
1019
1020 l_api_version CONSTANT NUMBER := 1.0;
1021 l_init_msg_list CONSTANT VARCHAR2(1) := 'T';
1022 l_return_status VARCHAR2(1);
1023 l_msg_count NUMBER;
1024 l_msg_data VARCHAR2(2000):=null;
1025 l_msg_index_out Number;
1026 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Deliverable';
1027 e_error Exception;
1028 c_delv NUMBER:=1;
1029
1030 l_lse_Id NUMBER;
1031
1032
1033 -- PROCEDURE Validate_Deliverable_id
1034
1035 PROCEDURE Validate_Deliverable_id(
1036
1037 p_deliverable_id IN NUMBER,
1038 x_return_status OUT NOCOPY VARCHAR2) IS
1039 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1040 l_Count NUMBER;
1041 CURSOR Cur_Deliverable(P_Deliverable_Id IN NUMBER) IS
1042 SELECT COUNT(*) FROM oke_k_deliverables_b
1043 WHERE deliverable_id=P_Deliverable_Id;
1044 BEGIN
1045 IF p_deliverable_id = OKE_API.G_MISS_NUM OR
1046 P_Deliverable_Id IS NULL
1047 THEN
1048
1049
1050 OKE_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'P_Deliverable_Id');
1051
1052
1053 l_return_status := OKE_API.G_RET_STS_ERROR;
1054 END IF;
1055
1056 OPEN Cur_Deliverable(P_Deliverable_Id);
1057 FETCH Cur_Deliverable INTO l_Count;
1058 CLOSE Cur_Deliverable;
1059 IF NOT l_Count = 1
1060
1061 THEN
1062 OKE_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'P_Line
1063 _Id');
1064
1065
1066 l_return_status := OKE_API.G_RET_STS_ERROR;
1067 END IF;
1068 x_return_status := l_return_status;
1069 EXCEPTION
1070 WHEN OTHERS THEN
1071 -- store SQL error message on message stack for caller
1072
1073 OKE_API.set_message(G_APP_NAME,
1074 G_UNEXPECTED_ERROR,
1075 G_SQLCODE_TOKEN,
1076 SQLCODE,
1077 G_SQLERRM_TOKEN,
1078 SQLERRM);
1079 -- notify caller of an UNEXPECTED error
1080 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1081 END Validate_Deliverable_id;
1082 BEGIN
1083 x_return_status:=OKE_API.G_RET_STS_SUCCESS;
1084
1085 Validate_Deliverable_id(p_deliverable_id,l_return_status);
1086 IF NOT l_Return_Status ='S'
1087 THEN RETURN;
1088 END IF;
1089
1090 l_del_tbl_tmp(c_delv).DELIVERABLE_ID:=P_Deliverable_Id;
1091 c_delv:=c_delv+1;
1092 FOR Child_Rec1 IN Child_Cur1(P_Deliverable_Id)
1093 LOOP
1094 l_del_tbl_tmp(c_delv).DELIVERABLE_ID:=Child_Rec1.DELIVERABLE_ID;
1095 c_delv:=c_delv+1;
1096 FOR Child_Rec2 IN Child_Cur2(Child_Rec1.deliverable_id)
1097
1098 LOOP
1099 l_del_tbl_tmp(c_delv).DELIVERABLE_ID:=Child_Rec2.deliverable_id;
1100 c_delv:=c_delv+1;
1101 FOR Child_Rec3 IN Child_Cur3(Child_Rec2.deliverable_id)
1102 LOOP
1103 l_del_tbl_tmp(c_delv).DELIVERABLE_ID:=Child_Rec3.deliverable_id;
1104 c_delv:=c_delv+1;
1105 FOR Child_Rec4 IN Child_Cur4(Child_Rec3.deliverable_id)
1106 LOOP
1107 l_del_tbl_tmp(c_delv).DELIVERABLE_ID:=Child_Rec4.deliverable_id;
1108 c_delv:=c_delv+1;
1109 FOR Child_Rec5 IN Child_Cur5(Child_Rec4.deliverable_id)
1110 LOOP
1111 l_del_tbl_tmp(c_delv).DELIVERABLE_ID:=Child_Rec5.deliverable_id;
1112 c_delv:=c_delv+1;
1113 END LOOP;
1114 END LOOP;
1115 END LOOP;
1116 END LOOP;
1117 END LOOP;
1118 c_delv:=1;
1119 FOR v_Index IN REVERSE l_del_tbl_tmp.FIRST .. l_del_tbl_tmp.LAST
1120
1121 LOOP
1122 l_del_tbl_in(c_delv).DELIVERABLE_ID:= l_del_tbl_tmp(v_Index).DELIVERABLE_ID;
1123 c_delv:=c_Delv+1;
1124 END LOOP;
1125
1126 -- get objects linked to the line
1127 -- delete check goes here
1128
1129 IF NOT l_del_tbl_in.COUNT=0
1130 THEN
1131 delete_deliverable(
1132
1133 p_api_version => l_api_version,
1134 p_init_msg_list => l_init_msg_list,
1135 x_return_status => l_return_status,
1136 x_msg_count => l_msg_count,
1137 x_msg_data => l_msg_data,
1138 p_del_tbl => l_del_tbl_in);
1139
1140 IF nvl(l_return_status,'*') <> 'S'
1141 THEN
1142 IF l_msg_count > 0
1143 THEN
1144
1145 FOR i in 1..l_msg_count
1146 LOOP
1147 fnd_msg_pub.get (p_msg_index => -1,
1148 p_encoded => 'T', -- OKC$APPLICATION.GET_FALSE,
1149 p_data => l_msg_data,
1150 p_msg_index_out => l_msg_index_out);
1151 END LOOP;
1152 END IF;
1153 RAISE e_Error;
1154
1155 END IF;
1156 END IF;
1157
1158 EXCEPTION
1159 WHEN e_Error THEN
1160 -- notify caller of an error as UNEXPETED error
1161 x_msg_count :=l_msg_count;
1162 x_msg_data:=l_msg_data;
1163 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1164 (
1165 l_api_name,
1166 'Delete_Deliverable',
1167 'OKE_API.G_RET_STS_ERROR',
1168 l_msg_count,
1169 l_msg_data,
1170 '_PVT'
1171 );
1172 WHEN OKE_API.G_EXCEPTION_ERROR THEN
1173 x_msg_count :=l_msg_count;
1174 x_msg_data:=l_msg_data;
1175 x_return_status := OKE_API.HANDLE_EXCEPTIONS
1176 (
1177 l_api_name,
1178 'Delete_Deliverable',
1179 'OKE_API.G_RET_STS_ERROR',
1180 l_msg_count,
1181 l_msg_data,
1182 '_PVT'
1183 );
1184 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1185 x_msg_count :=l_msg_count;
1186 x_msg_data:=l_msg_data;
1187 x_return_status :=OKE_API.HANDLE_EXCEPTIONS
1188 (
1189 l_api_name,
1190 'Delete_Deliverable',
1191 'OKE_API.G_RET_STS_UNEXP_ERROR',
1192 l_msg_count,
1193 l_msg_data,
1194 '_PVT'
1195 );
1196 WHEN OTHERS THEN
1197 x_msg_count :=l_msg_count;
1198 OKE_API.SET_MESSAGE(
1199 p_app_name => g_app_name,
1200 p_msg_name => g_unexpected_error,
1201 p_token1 => g_sqlcode_token,
1202 p_token1_value => sqlcode,
1203 p_token2 => g_sqlerrm_token,
1204 p_token2_value => sqlerrm);
1205 -- notify caller of an error as UNEXPETED error
1206 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1207
1208 END delete_deliverable;
1209
1210 PROCEDURE validate_deliverable(
1211 p_api_version IN NUMBER,
1212 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
1213 x_return_status OUT NOCOPY VARCHAR2,
1214 x_msg_count OUT NOCOPY NUMBER,
1215 x_msg_data OUT NOCOPY VARCHAR2,
1216 p_del_rec IN OKE_DELIVERABLE_PVT.del_rec_type) IS
1217
1218 BEGIN
1219 OKE_DELIVERABLE_PVT.Validate_Row(
1220 p_api_version => p_api_version,
1221 p_init_msg_list => p_init_msg_list,
1222 x_return_status => x_return_status,
1223 x_msg_count => x_msg_count,
1224 x_msg_data => x_msg_data,
1225 p_del_rec => p_del_rec);
1226 END validate_deliverable;
1227
1228 PROCEDURE validate_deliverable(
1229 p_api_version IN NUMBER,
1230 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
1231 x_return_status OUT NOCOPY VARCHAR2,
1232 x_msg_count OUT NOCOPY NUMBER,
1233 x_msg_data OUT NOCOPY VARCHAR2,
1234 p_del_tbl IN OKE_DELIVERABLE_PVT.del_tbl_type) IS
1235
1236 BEGIN
1237 OKE_DELIVERABLE_PVT.Validate_Row(
1238 p_api_version => p_api_version,
1239 p_init_msg_list => p_init_msg_list,
1240 x_return_status => x_return_status,
1241 x_msg_count => x_msg_count,
1242 x_msg_data => x_msg_data,
1243 p_del_tbl => p_del_tbl);
1244 END validate_deliverable;
1245
1246 PROCEDURE lock_deliverable(
1247 p_api_version IN NUMBER,
1248 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
1249 x_return_status OUT NOCOPY VARCHAR2,
1250 x_msg_count OUT NOCOPY NUMBER,
1251 x_msg_data OUT NOCOPY VARCHAR2,
1252 p_del_rec IN OKE_DELIVERABLE_PVT.del_rec_type) IS
1253
1254 BEGIN
1255 OKE_DELIVERABLE_PVT.Lock_Row(
1256 p_api_version => p_api_version,
1257 p_init_msg_list => p_init_msg_list,
1258 x_return_status => x_return_status,
1259 x_msg_count => x_msg_count,
1260 x_msg_data => x_msg_data,
1261 p_del_rec => p_del_rec);
1262 END lock_deliverable;
1263
1264 PROCEDURE lock_deliverable(
1265 p_api_version IN NUMBER,
1266 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
1267 x_return_status OUT NOCOPY VARCHAR2,
1268 x_msg_count OUT NOCOPY NUMBER,
1269 x_msg_data OUT NOCOPY VARCHAR2,
1270 p_del_tbl IN OKE_DELIVERABLE_PVT.del_tbl_type) IS
1271
1272 BEGIN
1273 OKE_DELIVERABLE_PVT.Lock_Row(
1274 p_api_version => p_api_version,
1275 p_init_msg_list => p_init_msg_list,
1276 x_return_status => x_return_status,
1277 x_msg_count => x_msg_count,
1278 x_msg_data => x_msg_data,
1279 p_del_tbl => p_del_tbl);
1280 END lock_deliverable;
1281
1282
1283 PROCEDURE delete_minor_entities (
1284 p_header_id IN NUMBER,
1285 x_return_status OUT NOCOPY VARCHAR2) IS
1286
1287 cursor project_parties is
1288 select project_party_id
1289 from pa_project_parties
1290 where object_type='OKE_K_HEADERS'
1291 and resource_type_id=101
1292 and object_id = p_header_id;
1293
1294 BEGIN
1295 for c in project_parties
1296 loop
1297 pa_project_parties_pkg.delete_row
1298 (x_project_id => null,
1299 x_project_party_id => c.project_party_id,
1300 x_record_version_number => null);
1301 end loop;
1302
1303
1304 delete from oke_k_user_attributes
1305 where k_header_id = p_header_id;
1306
1307 delete from oke_chg_logs
1308 where chg_request_id in
1309 (select chg_request_id
1310 from oke_chg_requests
1311 where k_header_id = p_header_id);
1312
1313
1314
1315
1316
1317 delete from oke_chg_requests
1318 where k_header_id = p_header_id;
1319
1320 delete from oke_k_holds
1321 where k_header_id = p_header_id;
1322
1323
1324
1325
1326 delete from oke_k_communications
1327 where k_header_id = p_header_id;
1328
1329
1330 delete from oke_dependencies
1331 where deliverable_id in
1332 (select deliverable_id
1333 from oke_k_deliverables_b
1334 where k_header_id = p_header_id);
1335
1336
1337 delete from oke_k_billing_methods
1338 where k_header_id = p_header_id;
1339
1340 delete from oke_k_related_entities
1341 where k_header_id=p_header_id;
1342
1343 delete from oke_k_related_entities
1344 where related_entity_id = p_header_id;
1345
1346 delete from oke_k_fifo_logs
1347 where k_header_id = p_header_id;
1348
1349 delete from okc_k_grpings
1350 where included_chr_id = p_header_id;
1351
1352 delete from oke_k_billing_events
1353 where k_header_id= p_header_id;
1354
1355
1356 x_return_status := OKE_API.G_RET_STS_SUCCESS;
1357
1358
1359 EXCEPTION
1360 when OTHERS then
1361 -- store SQL error message on message stack
1362 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
1363 p_msg_name => g_unexpected_error,
1364 p_token1 => g_sqlcode_token,
1365 p_token1_value => sqlcode,
1366 p_token2 => g_sqlerrm_token,
1367 p_token2_value => sqlerrm);
1368
1369 -- notify caller of an UNEXPETED error
1370 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1371
1372 END delete_minor_entities;
1373
1374 PROCEDURE delete_version_records (
1375 p_api_version IN VARCHAR2,
1376 p_header_id IN NUMBER,
1377 x_return_status OUT NOCOPY varchar2,
1378 x_msg_count OUT NOCOPY NUMBER,
1379 x_msg_data OUT NOCOPY VARCHAR2) IS
1380
1381 l_return_status VARCHAR2(1);
1382 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Version_Records';
1383 l_msg_count NUMBER;
1384 l_msg_data VARCHAR2(2000):=null;
1385
1386 BEGIN
1387
1388 delete from OKE_K_HEADERS_H
1389 where k_header_id = p_header_id;
1390
1391 delete from OKE_K_LINES_H
1392 where k_line_id in
1393 (select id from okc_k_lines_bh
1394 where dnz_chr_id = p_header_id);
1395
1396 delete from OKE_K_DELIVERABLES_TLH
1397 where deliverable_id in
1398 (select deliverable_id from OKE_K_DELIVERABLES_BH
1399 where k_header_id = p_header_id);
1400
1401 delete from OKE_K_DELIVERABLES_BH
1402 where k_header_id = p_header_id;
1403
1404 delete from OKE_K_FUNDING_SOURCES_H
1405 where object_id = p_header_id and object_type = 'OKE_K_HEADER';
1406
1407 delete from OKE_K_FUND_ALLOCATIONS_H
1408 where object_id = p_header_id;
1409
1410 delete from OKE_K_TERMS_H
1411 where k_header_id = p_header_id;
1412
1413 delete from OKE_K_BILLING_METHODS_H
1414 where k_header_id = p_header_id;
1415
1416 delete from OKE_K_STANDARD_NOTES_TLH
1417 where standard_notes_id in(
1418 select standard_notes_id
1419 from oke_k_standard_notes_bh
1420 where k_header_id = p_header_id);
1421
1422 delete from OKE_K_STANDARD_NOTES_BH
1423 where k_header_id = p_header_id;
1424
1425
1426
1427 delete from OKE_K_USER_ATTRIBUTES_H
1428 where k_header_id = p_header_id;
1429
1430 delete from OKE_K_VERS_NUMBERS_H
1431 where k_header_id = p_header_id;
1432
1433 dbms_output.put_line('before erase saved version'||l_return_status);
1434
1435 OKC_VERSION_PVT.delete_version
1436 (p_chr_id => p_header_id,
1437 p_major_version => 0,
1438 p_minor_version => 0,
1439 p_called_from => 'RESTORE_VERSION');
1440
1441
1442 dbms_output.put_line('after erase saved version'||l_return_status);
1443
1444 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1445 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1446 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1447 RAISE OKE_API.G_EXCEPTION_ERROR;
1448 END IF;
1449
1450 dbms_output.put_line('erase saved version succcess');
1451
1452
1453 x_return_status := OKE_API.G_RET_STS_SUCCESS;
1454
1455
1456 EXCEPTION
1457 when OTHERS then
1458
1459 dbms_output.put_line('exception in del ver');
1460 -- store SQL error message on message stack
1461 OKE_API.SET_MESSAGE(p_app_name => g_app_name,
1462 p_msg_name => g_unexpected_error,
1463 p_token1 => g_sqlcode_token,
1464 p_token1_value => sqlcode,
1465 p_token2 => g_sqlerrm_token,
1466 p_token2_value => sqlerrm);
1467
1468 -- notify caller of an UNEXPETED error
1469 x_return_status := OKE_API.G_RET_STS_UNEXP_ERROR;
1470
1471 END delete_version_records;
1472
1473
1474
1475
1476
1477 END OKE_CONTRACT_PVT;
1478