[Home] [Help]
PACKAGE BODY: APPS.OKE_CONTRACT_PUB
Source
1 PACKAGE BODY OKE_CONTRACT_PUB AS
2 /* $Header: OKEPCCCB.pls 120.6 2006/06/07 19:10:52 ifilimon noship $ */
3
4 g_api_type CONSTANT VARCHAR2(4) := '_PUB';
5
6
7
8 --
9 -- Private Procedure to get contract number if auto-numbering
10 -- option is enabled
11 --
12 PROCEDURE Assign_Doc_Number
13 ( X_K_Type_Code IN VARCHAR2
14 , X_Buy_Or_Sell IN VARCHAR2
15 , X_Template_Flag IN VARCHAR2
16 , X_K_Number IN OUT NOCOPY VARCHAR2
17 , X_Return_Status IN OUT NOCOPY VARCHAR2
18 ) IS
19
20 l_num_mode VARCHAR2(30);
21 l_num_type VARCHAR2(30);
22
23 BEGIN
24 --
25 -- Bypass check for templates
26 --
27 IF ( X_Template_Flag = 'Y' ) THEN
28 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
29 RETURN;
30 END IF;
31
32 --
33 -- Get Numbering Option from Document Type and Intent
34 --
35 OKE_NUMBER_SEQUENCES_PKG.Number_Option
36 ( X_K_Type_Code
37 , X_Buy_Or_Sell
38 , 'HEADER'
39 , l_num_mode
40 , l_num_type );
41
42
43 IF ( l_num_mode = 'MANUAL' ) THEN
44 --
45 -- Numbering Mode is MANUAL
46 --
47 -- Make sure number adheres to Numbering Type
48 --
49 IF ( l_num_type = 'NUMERIC' AND
50 OKE_NUMBER_SEQUENCES_PKG.Value_Is_Numeric(X_K_Number) = 'N' ) THEN
51 OKE_API.Set_Message( p_msg_name => 'OKE_NUMSEQ_INVALID_NUMERIC' );
52 X_Return_Status := OKE_API.G_RET_STS_ERROR;
53 RETURN;
54 END IF;
55
56 ELSE
57 --
58 -- Numbering Mode is AUTOMATIC
59 --
60 X_K_Number := OKE_NUMBER_SEQUENCES_PKG.Next_Contract_Number
61 ( X_K_Type_Code , X_Buy_Or_Sell );
62
63 END IF;
64
65 X_Return_Status := OKE_API.G_RET_STS_SUCCESS;
66
67 EXCEPTION
68 WHEN OTHERS THEN
69 FND_MSG_PUB.Add_Exc_Msg
70 ( p_pkg_name => g_pkg_name
71 , p_procedure_name => 'ASSIGN_DOC_NUMER'
72 );
73 X_Return_Status := OKE_API.G_RET_STS_UNEXP_ERROR;
74 END Assign_Doc_Number;
75
76
77
78 PROCEDURE create_contract_header(
79 p_api_version IN NUMBER,
80 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
81 x_return_status OUT NOCOPY VARCHAR2,
82 x_msg_count OUT NOCOPY NUMBER,
83 x_msg_data OUT NOCOPY VARCHAR2,
84 p_chr_rec IN oke_chr_pvt.chr_rec_type,
85 p_chrv_rec IN okc_contract_pub.chrv_rec_type,
86 x_chr_rec OUT NOCOPY oke_chr_pvt.chr_rec_type,
87 x_chrv_rec OUT NOCOPY okc_contract_pub.chrv_rec_type) IS
88
89 l_chr_rec oke_chr_pvt.chr_rec_type;
90 l_chrv_rec okc_contract_pub.chrv_rec_type;
91 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_CONTRACT_HEADER';
92 l_api_version CONSTANT NUMBER := 1.0;
93 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
94
95
96
97
98 BEGIN
99
100 -- call START_ACTIVITY to create savepoint, check compatibility
101 -- and initialize message list
102 l_return_status := OKE_API.START_ACTIVITY(
103 p_api_name => l_api_name,
104 p_pkg_name => g_pkg_name,
105 p_init_msg_list => p_init_msg_list,
106 l_api_version => l_api_version,
107 p_api_version => p_api_version,
108 p_api_type => g_api_type,
109 x_return_status => x_return_status);
110
111 -- check if activity started successfully
112 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
113 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
114 Elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
115 raise OKE_API.G_EXCEPTION_ERROR;
116
117 End If;
118
119 l_chr_rec := p_chr_rec;
120 l_chrv_rec := p_chrv_rec;
121
122 --
123 -- call procedure in complex API
124 --
125 -- First Assign Document Number if Numbering Option is Automatic
126 --
127 Assign_Doc_Number( X_K_Type_Code => l_chr_rec.k_type_code
128 , X_Buy_Or_Sell => l_chrv_rec.buy_or_sell
129 , X_Template_Flag => l_chrv_rec.template_yn
130 , X_K_Number => l_chrv_rec.contract_number
131 , X_Return_Status => X_Return_Status
132 );
133
134 IF X_Return_Status = OKE_API.G_RET_STS_UNEXP_ERROR Then
135 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
136 ELSIF X_Return_Status = OKE_API.G_RET_STS_ERROR Then
137 raise OKE_API.G_EXCEPTION_ERROR;
138 End If;
139
140 --
141 -- get default contract number modifier
142 --
143
144 If l_chrv_rec.contract_number_modifier is null or l_chrv_rec.contract_number_modifier = OKE_API.G_MISS_CHAR then
145 IF l_chr_rec.boa_id is null or l_chr_rec.boa_id = OKE_API.G_MISS_NUM THEN
146 l_chrv_rec.contract_number_modifier
147 := l_chr_rec.k_type_code || '.'|| l_chrv_rec.buy_or_sell || '.';
148 ELSE
149 l_chrv_rec.contract_number_modifier
150 := l_chr_rec.k_type_code || '.'|| l_chrv_rec.buy_or_sell || '.' || l_chr_rec.boa_id;
151 END IF;
152
153 End If;
154
155 --
156 -- set okc context before API call
157 --
158
159 OKC_CONTEXT.SET_OKC_ORG_CONTEXT(l_chrv_rec.authoring_org_id,l_chrv_rec.inv_organization_id);
160 OKC_CONTRACT_PUB.create_contract_header(
161 p_api_version => p_api_version,
162 p_init_msg_list => p_init_msg_list,
163 x_return_status => x_return_status,
164 x_msg_count => x_msg_count,
165 x_msg_data => x_msg_data,
166 p_chrv_rec => l_chrv_rec,
167 x_chrv_rec => x_chrv_rec);
168
169 -- check return status
170 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
171 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
172 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
173 raise OKC_API.G_EXCEPTION_ERROR;
174 End If;
175
176
177 -- get id from OKC record
178
179 l_chr_rec.K_HEADER_ID := x_chrv_rec.ID;
180
181 -- get default contract number modifier
182 --l_chrv_rec.contract_number_modifier := l_chr_rec.k_type_code || '.'|| l_chrv_rec.buy_or_sell || '.' || l_chr_rec.boa_id;
183
184
185 -- call procedure in complex API
186 OKE_CONTRACT_PVT.create_contract_header(
187 p_api_version => p_api_version,
188 p_init_msg_list => p_init_msg_list,
189 x_return_status => x_return_status,
190 x_msg_count => x_msg_count,
191 x_msg_data => x_msg_data,
192 p_chr_rec => l_chr_rec,
193 x_chr_rec => x_chr_rec);
194
195
196 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
197 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
198 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
199 raise OKE_API.G_EXCEPTION_ERROR;
200 End If;
201
202 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
203 x_msg_data => x_msg_data);
204 EXCEPTION
205 when OKE_API.G_EXCEPTION_ERROR then
206 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
207 p_api_name => l_api_name,
208 p_pkg_name => g_pkg_name,
209 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
210 x_msg_count => x_msg_count,
211 x_msg_data => x_msg_data,
212 p_api_type => g_api_type);
213
214 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
215 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
216 p_api_name => l_api_name,
217 p_pkg_name => g_pkg_name,
218 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
219 x_msg_count => x_msg_count,
220 x_msg_data => x_msg_data,
221 p_api_type => g_api_type);
222
223 when OKC_API.G_EXCEPTION_ERROR then
224 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
225 p_api_name => l_api_name,
226 p_pkg_name => g_pkg_name,
227 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
228 x_msg_count => x_msg_count,
229 x_msg_data => x_msg_data,
230 p_api_type => g_api_type);
231
232 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
233 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
234 p_api_name => l_api_name,
235 p_pkg_name => g_pkg_name,
236 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
237 x_msg_count => x_msg_count,
238 x_msg_data => x_msg_data,
239 p_api_type => g_api_type);
240
241 when OTHERS then
242 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
243 p_api_name => l_api_name,
244 p_pkg_name => g_pkg_name,
245 p_exc_name => 'OTHERS',
246 x_msg_count => x_msg_count,
247 x_msg_data => x_msg_data,
248 p_api_type => g_api_type);
249
250 END create_contract_header;
251
252 PROCEDURE create_contract_header(
253 p_api_version IN NUMBER,
254 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
255 x_return_status OUT NOCOPY VARCHAR2,
256 x_msg_count OUT NOCOPY NUMBER,
257 x_msg_data OUT NOCOPY VARCHAR2,
258 p_chr_tbl IN oke_chr_pvt.chr_tbl_type,
259 p_chrv_tbl IN okc_contract_pub.chrv_tbl_type,
260 x_chr_tbl OUT NOCOPY oke_chr_pvt.chr_tbl_type,
261 x_chrv_tbl OUT NOCOPY okc_contract_pub.chrv_tbl_type) IS
262
263 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_CONTRACT_HEADER';
264 l_api_version CONSTANT NUMBER := 1.0;
265 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
266 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
267 i NUMBER;
268 l_chr_tbl OKE_CHR_PVT.chr_tbl_type := p_chr_tbl;
269 BEGIN
270 -- call START_ACTIVITY to create savepoint, check compatibility
271 -- and initialize message list
272 l_return_status := OKE_API.START_ACTIVITY(
273 p_api_name => l_api_name,
274 p_pkg_name => g_pkg_name,
275 p_init_msg_list => p_init_msg_list,
276 l_api_version => l_api_version,
277 p_api_version => p_api_version,
278 p_api_type => g_api_type,
279 x_return_status => x_return_status);
280
281 -- check if activity started successfully
282 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
283 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
284 Elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
285 raise OKE_API.G_EXCEPTION_ERROR;
286
287 End If;
288
289 If (p_chrv_tbl.COUNT > 0) Then
290 i := p_chrv_tbl.FIRST;
291 LOOP
292 -- call procedure in public API for a record
293 OKE_CONTRACT_PUB.create_contract_header(
294 p_api_version => p_api_version,
295 p_init_msg_list => p_init_msg_list,
296 x_return_status => x_return_status,
297 x_msg_count => x_msg_count,
298 x_msg_data => x_msg_data,
299 p_chr_rec => l_chr_tbl(i),
300 p_chrv_rec => p_chrv_tbl(i),
301 x_chr_rec => x_chr_tbl(i),
302 x_chrv_rec => x_chrv_tbl(i));
303
304 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
305 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
306 l_overall_status := x_return_status;
307 End If;
308 End If;
309
310 EXIT WHEN (i = p_chrv_tbl.LAST);
311 i := p_chrv_tbl.NEXT(i);
312 END LOOP;
313
314 -- return overall status
315 x_return_status := l_overall_status;
316 End If;
317
318 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
319 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
320 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
321 raise OKE_API.G_EXCEPTION_ERROR;
322 End If;
323
324 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
325 x_msg_data => x_msg_data);
326 EXCEPTION
327 when OKE_API.G_EXCEPTION_ERROR then
328 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
329 p_api_name => l_api_name,
330 p_pkg_name => g_pkg_name,
331 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
332 x_msg_count => x_msg_count,
333 x_msg_data => x_msg_data,
334 p_api_type => g_api_type);
335
336 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
337 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
338 p_api_name => l_api_name,
339 p_pkg_name => g_pkg_name,
340 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
341 x_msg_count => x_msg_count,
342 x_msg_data => x_msg_data,
343 p_api_type => g_api_type);
344
345 when OTHERS then
346 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
347 p_api_name => l_api_name,
348 p_pkg_name => g_pkg_name,
349 p_exc_name => 'OTHERS',
350 x_msg_count => x_msg_count,
351 x_msg_data => x_msg_data,
352 p_api_type => g_api_type);
353
354 END create_contract_header;
355
356 PROCEDURE update_contract_header(
357 p_api_version IN NUMBER,
358 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
359 x_return_status OUT NOCOPY VARCHAR2,
360 x_msg_count OUT NOCOPY NUMBER,
361 x_msg_data OUT NOCOPY VARCHAR2,
362 p_restricted_update IN VARCHAR2 DEFAULT OKE_API.G_TRUE,
363 p_chr_rec IN oke_chr_pvt.chr_rec_type,
364 p_chrv_rec IN okc_contract_pub.chrv_rec_type,
365 x_chr_rec OUT NOCOPY oke_chr_pvt.chr_rec_type,
366 x_chrv_rec OUT NOCOPY okc_contract_pub.chrv_rec_type) IS
367
368 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CONTRACT_HEADER';
369 l_api_version CONSTANT NUMBER := 1.0;
370 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
371 BEGIN
372 -- call START_ACTIVITY to create savepoint, check compatibility
373 -- and initialize message list
374
375 l_return_status := OKE_API.START_ACTIVITY(
376 p_api_name => l_api_name,
377 p_pkg_name => g_pkg_name,
378 p_init_msg_list => p_init_msg_list,
379 l_api_version => l_api_version,
380 p_api_version => p_api_version,
381 p_api_type => g_api_type,
382 x_return_status => x_return_status);
383
384 -- check if activity started successfully
385 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
386 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
387 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
388 raise OKE_API.G_EXCEPTION_ERROR;
389 End If;
390
391
392 -- call procedure in complex API
393 OKC_CONTRACT_PUB.update_contract_header(
394 p_api_version => p_api_version,
395 p_init_msg_list => p_init_msg_list,
396 x_return_status => x_return_status,
397 x_msg_count => x_msg_count,
398 x_msg_data => x_msg_data,
399 p_restricted_update => p_restricted_update,
400 p_chrv_rec => p_chrv_rec,
401 x_chrv_rec => x_chrv_rec);
402
403 -- check return status
404 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
405 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
406 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
407 raise OKC_API.G_EXCEPTION_ERROR;
408 End If;
409
410 -- call complex api
411
412 OKE_CONTRACT_PVT.update_contract_header(
413 p_api_version => p_api_version,
414 p_init_msg_list => p_init_msg_list,
415 x_return_status => x_return_status,
416 x_msg_count => x_msg_count,
417 x_msg_data => x_msg_data,
418 p_chr_rec => p_chr_rec,
419 x_chr_rec => x_chr_rec);
420
421 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
422 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
423 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
424 raise OKE_API.G_EXCEPTION_ERROR;
425 End If;
426
427 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
428 x_msg_data => x_msg_data);
429 EXCEPTION
430 when OKE_API.G_EXCEPTION_ERROR then
431 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
432 p_api_name => l_api_name,
433 p_pkg_name => g_pkg_name,
434 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
435 x_msg_count => x_msg_count,
436 x_msg_data => x_msg_data,
437 p_api_type => g_api_type);
438
439 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
440 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
441 p_api_name => l_api_name,
442 p_pkg_name => g_pkg_name,
443 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
444 x_msg_count => x_msg_count,
445 x_msg_data => x_msg_data,
446 p_api_type => g_api_type);
447
448 when OKC_API.G_EXCEPTION_ERROR then
449 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
450 p_api_name => l_api_name,
451 p_pkg_name => g_pkg_name,
452 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
453 x_msg_count => x_msg_count,
454 x_msg_data => x_msg_data,
455 p_api_type => g_api_type);
456
457 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
458 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
459 p_api_name => l_api_name,
460 p_pkg_name => g_pkg_name,
461 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
462 x_msg_count => x_msg_count,
463 x_msg_data => x_msg_data,
464 p_api_type => g_api_type);
465
466 when OTHERS then
467 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
468 p_api_name => l_api_name,
469 p_pkg_name => g_pkg_name,
470 p_exc_name => 'OTHERS',
471 x_msg_count => x_msg_count,
472 x_msg_data => x_msg_data,
473 p_api_type => g_api_type);
474
475 END update_contract_header;
476
477 PROCEDURE update_contract_header(
478 p_api_version IN NUMBER,
479 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
480 x_return_status OUT NOCOPY VARCHAR2,
481 x_msg_count OUT NOCOPY NUMBER,
482 x_msg_data OUT NOCOPY VARCHAR2,
483 p_restricted_update IN VARCHAR2 DEFAULT OKE_API.G_TRUE,
484 p_chr_tbl IN oke_chr_pvt.chr_tbl_type,
485 p_chrv_tbl IN okc_contract_pub.chrv_tbl_type,
486 x_chr_tbl OUT NOCOPY oke_chr_pvt.chr_tbl_type,
487 x_chrv_tbl OUT NOCOPY okc_contract_pub.chrv_tbl_type) IS
488
489 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CONTRACT_HEADER';
490 l_api_version CONSTANT NUMBER := 1.0;
491 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
492 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
493 i NUMBER;
494 BEGIN
495 -- call START_ACTIVITY to create savepoint, check compatibility
496 -- and initialize message list
497
498 l_return_status := OKE_API.START_ACTIVITY(
499 p_api_name => l_api_name,
500 p_pkg_name => g_pkg_name,
501 p_init_msg_list => p_init_msg_list,
502 l_api_version => l_api_version,
503 p_api_version => p_api_version,
504 p_api_type => g_api_type,
505 x_return_status => x_return_status);
506
507 -- check if activity started successfully
508 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
509 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
510 Elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
511 raise OKE_API.G_EXCEPTION_ERROR;
512
513 End If;
514
515 If (p_chrv_tbl.COUNT > 0) Then
516 i := p_chrv_tbl.FIRST;
517 LOOP
518 -- call procedure in public API for a record
519 OKE_CONTRACT_PUB.update_contract_header(
520 p_api_version => p_api_version,
521 p_init_msg_list => p_init_msg_list,
522 x_return_status => x_return_status,
523 x_msg_count => x_msg_count,
524 x_msg_data => x_msg_data,
525 p_restricted_update => p_restricted_update,
526 p_chr_rec => p_chr_tbl(i),
527 p_chrv_rec => p_chrv_tbl(i),
528 x_chr_rec => x_chr_tbl(i),
529 x_chrv_rec => x_chrv_tbl(i));
530
531
532 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
533 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
534 l_overall_status := x_return_status;
535 End If;
536 End If;
537
538 EXIT WHEN (i = p_chrv_tbl.LAST);
539 i := p_chrv_tbl.NEXT(i);
540 END LOOP;
541
542 -- return overall status
543 x_return_status := l_overall_status;
544 End If;
545
546 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
547 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
548 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
549 raise OKE_API.G_EXCEPTION_ERROR;
550 End If;
551
552 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
553 x_msg_data => x_msg_data);
554 EXCEPTION
555 when OKE_API.G_EXCEPTION_ERROR then
556 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
557 p_api_name => l_api_name,
558 p_pkg_name => g_pkg_name,
559 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
560 x_msg_count => x_msg_count,
561 x_msg_data => x_msg_data,
562 p_api_type => g_api_type);
563
564 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
565 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
566 p_api_name => l_api_name,
567 p_pkg_name => g_pkg_name,
568 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
569 x_msg_count => x_msg_count,
570 x_msg_data => x_msg_data,
571 p_api_type => g_api_type);
572
573 when OTHERS then
574 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
575 p_api_name => l_api_name,
576 p_pkg_name => g_pkg_name,
577 p_exc_name => 'OTHERS',
578 x_msg_count => x_msg_count,
579 x_msg_data => x_msg_data,
580 p_api_type => g_api_type);
581
582 END update_contract_header;
583
584 PROCEDURE delete_contract_header(
585 p_api_version IN NUMBER,
586 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
587 x_return_status OUT NOCOPY VARCHAR2,
588 x_msg_count OUT NOCOPY NUMBER,
589 x_msg_data OUT NOCOPY VARCHAR2,
590 p_chr_rec IN oke_chr_pvt.chr_rec_type,
591 p_chrv_rec IN okc_contract_pub.chrv_rec_type) IS
592
593 l_chr_rec oke_chr_pvt.chr_rec_type;
594 l_chrv_rec okc_contract_pub.chrv_rec_type;
595 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_CONTRACT_HEADER';
596 l_api_version CONSTANT NUMBER := 1.0;
597 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
598 BEGIN
599 -- call START_ACTIVITY to create savepoint, check compatibility
600 -- and initialize message list
601 l_return_status := OKE_API.START_ACTIVITY(
602 p_api_name => l_api_name,
603 p_pkg_name => g_pkg_name,
604 p_init_msg_list => p_init_msg_list,
605 l_api_version => l_api_version,
606 p_api_version => p_api_version,
607 p_api_type => g_api_type,
608 x_return_status => x_return_status);
609
610 -- check if activity started successfully
611 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
612 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
613 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
614 raise OKE_API.G_EXCEPTION_ERROR;
615 End If;
616
617 l_chr_rec := p_chr_rec;
618 l_chrv_rec := p_chrv_rec;
619
620 -- call complex api
621
622 OKE_CONTRACT_PVT.delete_contract_header(
623 p_api_version => p_api_version,
624 p_init_msg_list => p_init_msg_list,
625 x_return_status => x_return_status,
626 x_msg_count => x_msg_count,
627 x_msg_data => x_msg_data,
628 p_chr_rec => l_chr_rec);
629
630 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
631 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
632 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
633 raise OKE_API.G_EXCEPTION_ERROR;
634 End If;
635
636 -- call procedure in complex API
637 OKC_CONTRACT_PUB.delete_contract_header(
638 p_api_version => p_api_version,
639 p_init_msg_list => p_init_msg_list,
640 x_return_status => x_return_status,
641 x_msg_count => x_msg_count,
642 x_msg_data => x_msg_data,
643 p_chrv_rec => l_chrv_rec);
644
645 -- check return status
646 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
647 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
648 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
649 raise OKC_API.G_EXCEPTION_ERROR;
650 End If;
651
652 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
653 x_msg_data => x_msg_data);
654 EXCEPTION
655 when OKE_API.G_EXCEPTION_ERROR then
656 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
657 p_api_name => l_api_name,
658 p_pkg_name => g_pkg_name,
659 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
660 x_msg_count => x_msg_count,
661 x_msg_data => x_msg_data,
662 p_api_type => g_api_type);
663
664 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
665 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
666 p_api_name => l_api_name,
667 p_pkg_name => g_pkg_name,
668 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
669 x_msg_count => x_msg_count,
670 x_msg_data => x_msg_data,
671 p_api_type => g_api_type);
672
673 when OKC_API.G_EXCEPTION_ERROR then
674 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
675 p_api_name => l_api_name,
676 p_pkg_name => g_pkg_name,
677 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
678 x_msg_count => x_msg_count,
679 x_msg_data => x_msg_data,
680 p_api_type => g_api_type);
681
682 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
683 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
684 p_api_name => l_api_name,
685 p_pkg_name => g_pkg_name,
686 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
687 x_msg_count => x_msg_count,
688 x_msg_data => x_msg_data,
689 p_api_type => g_api_type);
690
691 when OTHERS then
692 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
693 p_api_name => l_api_name,
694 p_pkg_name => g_pkg_name,
695 p_exc_name => 'OTHERS',
696 x_msg_count => x_msg_count,
697 x_msg_data => x_msg_data,
698 p_api_type => g_api_type);
699
700 END delete_contract_header;
701
702 PROCEDURE delete_contract_header(
703 p_api_version IN NUMBER,
704 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
705 x_return_status OUT NOCOPY VARCHAR2,
706 x_msg_count OUT NOCOPY NUMBER,
707 x_msg_data OUT NOCOPY VARCHAR2,
708 p_chr_tbl IN oke_chr_pvt.chr_tbl_type,
709 p_chrv_tbl IN okc_contract_pub.chrv_tbl_type) IS
710
711 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_CONTRACT_HEADER';
712 l_api_version CONSTANT NUMBER := 1.0;
713 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
714 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
715 i NUMBER;
716 BEGIN
717 -- call START_ACTIVITY to create savepoint, check compatibility
718 -- and initialize message list
719 l_return_status := OKE_API.START_ACTIVITY(
720 p_api_name => l_api_name,
721 p_pkg_name => g_pkg_name,
722 p_init_msg_list => p_init_msg_list,
723 l_api_version => l_api_version,
724 p_api_version => p_api_version,
725 p_api_type => g_api_type,
726 x_return_status => x_return_status);
727
728 -- check if activity started successfully
729 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
730 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
731 Elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
732 raise OKE_API.G_EXCEPTION_ERROR;
733
734 End If;
735
736 If (p_chrv_tbl.COUNT > 0) Then
737 i := p_chrv_tbl.FIRST;
738 LOOP
739
740 -- call complex API
741
742 OKE_CONTRACT_PVT.delete_contract_header(
743
744 p_api_version => p_api_version,
745 p_init_msg_list => p_init_msg_list,
746 x_return_status => x_return_status,
747 x_msg_count => x_msg_count,
748 x_msg_data => x_msg_data,
749 p_chr_rec => p_chr_tbl(i));
750
751 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
752 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
753 l_overall_status := x_return_status;
754 End If;
755 End If;
756
757 -- call procedure in public API for a record
758 OKC_CONTRACT_PUB.delete_contract_header(
759 p_api_version => p_api_version,
760 p_init_msg_list => p_init_msg_list,
761 x_return_status => x_return_status,
762 x_msg_count => x_msg_count,
763 x_msg_data => x_msg_data,
764 p_chrv_rec => p_chrv_tbl(i));
765
766 -- store the highest degree of error
767 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
768 If l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
769 l_overall_status := x_return_status;
770 End If;
771 End If;
772
773 EXIT WHEN (i = p_chrv_tbl.LAST);
774 i := p_chrv_tbl.NEXT(i);
775 END LOOP;
776
777 -- return overall status
778 x_return_status := l_overall_status;
779 End If;
780
781 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
782 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
783 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
784 raise OKE_API.G_EXCEPTION_ERROR;
785 End If;
786
787 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
788 x_msg_data => x_msg_data);
789 EXCEPTION
790 when OKE_API.G_EXCEPTION_ERROR then
791 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
792 p_api_name => l_api_name,
793 p_pkg_name => g_pkg_name,
794 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
795 x_msg_count => x_msg_count,
796 x_msg_data => x_msg_data,
797 p_api_type => g_api_type);
798
799 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
800 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
801 p_api_name => l_api_name,
802 p_pkg_name => g_pkg_name,
803 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
804 x_msg_count => x_msg_count,
805 x_msg_data => x_msg_data,
806 p_api_type => g_api_type);
807
808 when OKC_API.G_EXCEPTION_ERROR then
809 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
810 p_api_name => l_api_name,
811 p_pkg_name => g_pkg_name,
812 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
813 x_msg_count => x_msg_count,
814 x_msg_data => x_msg_data,
815 p_api_type => g_api_type);
816
817 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
818 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
819 p_api_name => l_api_name,
820 p_pkg_name => g_pkg_name,
821 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
822 x_msg_count => x_msg_count,
823 x_msg_data => x_msg_data,
824 p_api_type => g_api_type);
825
826 when OTHERS then
827 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
828 p_api_name => l_api_name,
829 p_pkg_name => g_pkg_name,
830 p_exc_name => 'OTHERS',
831 x_msg_count => x_msg_count,
832 x_msg_data => x_msg_data,
833 p_api_type => g_api_type);
834
835 END delete_contract_header;
836
837 PROCEDURE validate_contract_header(
838 p_api_version IN NUMBER,
839 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
840 x_return_status OUT NOCOPY VARCHAR2,
841 x_msg_count OUT NOCOPY NUMBER,
842 x_msg_data OUT NOCOPY VARCHAR2,
843 p_chr_rec IN oke_chr_pvt.chr_rec_type,
844 p_chrv_rec IN okc_contract_pub.chrv_rec_type) IS
845
846 l_chr_rec oke_chr_pvt.chr_rec_type;
847 l_chrv_rec okc_contract_pub.chrv_rec_type;
848 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_CONTRACT_HEADER';
849 l_api_version CONSTANT NUMBER := 1.0;
850 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
851
852 BEGIN
853 -- call START_ACTIVITY to create savepoint, check compatibility
854 -- and initialize message list
855 l_return_status := OKE_API.START_ACTIVITY(
856 p_api_name => l_api_name,
857 p_pkg_name => g_pkg_name,
858 p_init_msg_list => p_init_msg_list,
859 l_api_version => l_api_version,
860 p_api_version => p_api_version,
861 p_api_type => g_api_type,
862 x_return_status => x_return_status);
863
864 -- check if activity started successfully
865 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
866 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
867 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
868 raise OKE_API.G_EXCEPTION_ERROR;
869 End If;
870
871 -- call BEFORE user hook
872 l_chr_rec := p_chr_rec;
873 l_chrv_rec := p_chrv_rec;
874
875 -- call procedure in complex API
876 OKC_CONTRACT_PUB.validate_contract_header(
877 p_api_version => p_api_version,
878 p_init_msg_list => p_init_msg_list,
879 x_return_status => x_return_status,
880 x_msg_count => x_msg_count,
881 x_msg_data => x_msg_data,
882 p_chrv_rec => l_chrv_rec);
883
884 -- check return status
885 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
886 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
887 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
888 raise OKC_API.G_EXCEPTION_ERROR;
889 End If;
890
891 -- get id for OKE record
892
893 l_chr_rec.K_HEADER_ID := l_chrv_rec.ID;
894
895 -- call complex API
896 OKE_CONTRACT_PVT.validate_contract_header(
897 p_api_version => p_api_version,
898 p_init_msg_list => p_init_msg_list,
899 x_return_status => x_return_status,
900 x_msg_count => x_msg_count,
901 x_msg_data => x_msg_data,
902 p_chr_rec => l_chr_rec);
903
904 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
905 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
906 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
907 raise OKC_API.G_EXCEPTION_ERROR;
908 End If;
909
910 -- end activity
911 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
912 x_msg_data => x_msg_data);
913 EXCEPTION
914 when OKE_API.G_EXCEPTION_ERROR then
915 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
916 p_api_name => l_api_name,
917 p_pkg_name => g_pkg_name,
918 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
919 x_msg_count => x_msg_count,
920 x_msg_data => x_msg_data,
921 p_api_type => g_api_type);
922
923 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
924 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
925 p_api_name => l_api_name,
926 p_pkg_name => g_pkg_name,
927 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
928 x_msg_count => x_msg_count,
929 x_msg_data => x_msg_data,
930 p_api_type => g_api_type);
931
932 when OKC_API.G_EXCEPTION_ERROR then
933 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
934 p_api_name => l_api_name,
935 p_pkg_name => g_pkg_name,
936 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
937 x_msg_count => x_msg_count,
938 x_msg_data => x_msg_data,
939 p_api_type => g_api_type);
940
941 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
942 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
943 p_api_name => l_api_name,
944 p_pkg_name => g_pkg_name,
945 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
946 x_msg_count => x_msg_count,
947 x_msg_data => x_msg_data,
948 p_api_type => g_api_type);
949
950 when OTHERS then
951 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
952 p_api_name => l_api_name,
953 p_pkg_name => g_pkg_name,
954 p_exc_name => 'OTHERS',
955 x_msg_count => x_msg_count,
956 x_msg_data => x_msg_data,
957 p_api_type => g_api_type);
958
959 END validate_contract_header;
960
961 PROCEDURE validate_contract_header(
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_chr_tbl IN oke_chr_pvt.chr_tbl_type,
968 p_chrv_tbl IN okc_contract_pub.chrv_tbl_type) IS
969
970 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_CONTRACT_HEADER';
971
972 l_api_version CONSTANT NUMBER := 1.0;
973 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
974 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
975 i NUMBER;
976 l_chr_tbl oke_chr_pvt.chr_tbl_type := p_chr_tbl;
977 BEGIN
978 -- call START_ACTIVITY to create savepoint, check compatibility
979 -- and initialize message list
980 l_return_status := OKE_API.START_ACTIVITY(
981 p_api_name => l_api_name,
982 p_pkg_name => g_pkg_name,
983 p_init_msg_list => p_init_msg_list,
984 l_api_version => l_api_version,
985 p_api_version => p_api_version,
986 p_api_type => g_api_type,
987 x_return_status => x_return_status);
988
989 -- check if activity started successfully
990 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
991 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
992 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
993 raise OKE_API.G_EXCEPTION_ERROR;
994 End If;
995
996 If (p_chrv_tbl.COUNT > 0) Then
997 i := p_chrv_tbl.FIRST;
998 LOOP
999 -- call procedure in complex API
1000 OKC_CONTRACT_PUB.validate_contract_header(
1001 p_api_version => p_api_version,
1002 p_init_msg_list => p_init_msg_list,
1003 x_return_status => x_return_status,
1004 x_msg_count => x_msg_count,
1005 x_msg_data => x_msg_data,
1006 p_chrv_rec => p_chrv_tbl(i));
1007
1008 -- store the highest degree of error
1009 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
1010 If l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
1011 l_overall_status := x_return_status;
1012 End If;
1013 End If;
1014
1015 l_chr_tbl(i).K_HEADER_ID := p_chrv_tbl(i).ID;
1016
1017 -- call complex API
1018 OKE_CONTRACT_PVT.validate_contract_header(
1019 p_api_version => p_api_version,
1020 p_init_msg_list => p_init_msg_list,
1021 x_return_status => x_return_status,
1022 x_msg_count => x_msg_count,
1023 x_msg_data => x_msg_data,
1024 p_chr_rec => l_chr_tbl(i));
1025
1026 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
1027 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
1028 l_overall_status := x_return_status;
1029 End If;
1030 End If;
1031
1032 EXIT WHEN (i = p_chrv_tbl.LAST);
1033 i := p_chrv_tbl.NEXT(i);
1034 END LOOP;
1035
1036 -- return overall status
1037 x_return_status := l_overall_status;
1038 End If;
1039
1040 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
1041 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1042 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
1043 raise OKE_API.G_EXCEPTION_ERROR;
1044 End If;
1045
1046 -- end activity
1047 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
1048 x_msg_data => x_msg_data);
1049 EXCEPTION
1050 when OKE_API.G_EXCEPTION_ERROR then
1051 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1052 p_api_name => l_api_name,
1053 p_pkg_name => g_pkg_name,
1054 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1055 x_msg_count => x_msg_count,
1056 x_msg_data => x_msg_data,
1057 p_api_type => g_api_type);
1058
1059 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
1060 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1061 p_api_name => l_api_name,
1062 p_pkg_name => g_pkg_name,
1063 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1064 x_msg_count => x_msg_count,
1065 x_msg_data => x_msg_data,
1066 p_api_type => g_api_type);
1067
1068 when OKC_API.G_EXCEPTION_ERROR then
1069 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1070 p_api_name => l_api_name,
1071 p_pkg_name => g_pkg_name,
1072 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1073 x_msg_count => x_msg_count,
1074 x_msg_data => x_msg_data,
1075 p_api_type => g_api_type);
1076
1077 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
1078 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1079 p_api_name => l_api_name,
1080 p_pkg_name => g_pkg_name,
1081 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1082 x_msg_count => x_msg_count,
1083 x_msg_data => x_msg_data,
1084 p_api_type => g_api_type);
1085
1086 when OTHERS then
1087 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1088 p_api_name => l_api_name,
1089 p_pkg_name => g_pkg_name,
1090 p_exc_name => 'OTHERS',
1091 x_msg_count => x_msg_count,
1092 x_msg_data => x_msg_data,
1093 p_api_type => g_api_type);
1094
1095 END validate_contract_header;
1096
1097 PROCEDURE create_contract_line(
1098 p_api_version IN NUMBER,
1099 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
1100 x_return_status OUT NOCOPY VARCHAR2,
1101 x_msg_count OUT NOCOPY NUMBER,
1102 x_msg_data OUT NOCOPY VARCHAR2,
1103 p_restricted_update IN VARCHAR2 DEFAULT OKE_API.G_TRUE,
1104 p_cle_rec IN oke_cle_pvt.cle_rec_type,
1105 p_clev_rec IN okc_contract_pub.clev_rec_type,
1106 x_cle_rec OUT NOCOPY oke_cle_pvt.cle_rec_type,
1107 x_clev_rec OUT NOCOPY okc_contract_pub.clev_rec_type) IS
1108
1109 l_cle_rec oke_cle_pvt.cle_rec_type;
1110 l_clev_rec okc_contract_pub.clev_rec_type;
1111 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_CONTRACT_LINE';
1112 l_api_version CONSTANT NUMBER := 1.0;
1113 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1114 l_line_number VARCHAR2(120);
1115
1116 l_sts_code OKC_ASSENTS.STS_CODE%TYPE;
1117 l_scs_code OKC_ASSENTS.SCS_CODE%TYPE;
1118 l_return_value VARCHAR2(1):='?';
1119
1120 CURSOR c_assent IS
1121 SELECT allowed_yn
1122 FROM OKC_ASSENTS
1123 WHERE sts_code = l_sts_code
1124 AND scs_code = l_scs_code
1125 AND opn_code = 'UPDATE';
1126
1127 Cursor l_chrv_csr Is
1128 SELECT sts_code, scs_code
1129 FROM OKC_K_HEADERS_B
1130 WHERE id = p_clev_rec.dnz_chr_id;
1131
1132 BEGIN
1133 -- call START_ACTIVITY to create savepoint, check compatibility
1134 -- and initialize message list
1135 l_return_status := OKE_API.START_ACTIVITY(
1136 p_api_name => l_api_name,
1137 p_pkg_name => g_pkg_name,
1138 p_init_msg_list => p_init_msg_list,
1139 l_api_version => l_api_version,
1140 p_api_version => p_api_version,
1141 p_api_type => g_api_type,
1142 x_return_status => x_return_status);
1143
1144 -- check if activity started successfully
1145 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
1146 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1147 Elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
1148 raise OKE_API.G_EXCEPTION_ERROR;
1149
1150 End If;
1151
1152 l_cle_rec := p_cle_rec;
1153 l_clev_rec := p_clev_rec;
1154
1155 -- get original line number
1156
1157 l_line_number := p_clev_rec.line_number;
1158
1159 -- call procedure in complex API
1160
1161 -- removing p_restricted_update and adding circumvent around okc_assent
1162
1163 Open l_chrv_csr;
1164 Fetch l_chrv_csr Into l_sts_code, l_scs_code;
1165 If l_chrv_csr%FOUND Then
1166 Close l_chrv_csr;
1167
1168 Open C_assent;
1169 Fetch C_assent INTO L_return_value;
1170 Close C_assent;
1171
1172 If (l_return_value in ('N')) Then
1173 UPDATE OKC_ASSENTS SET ALLOWED_YN = 'Y'
1174 WHERE sts_code = l_sts_code
1175 AND scs_code = l_scs_code
1176 AND opn_code = 'UPDATE';
1177 End If;
1178 Else
1179 Close l_chrv_csr;
1180 End If;
1181
1182 OKC_CONTRACT_PUB.create_contract_line(
1183 p_api_version => p_api_version,
1184 p_init_msg_list => p_init_msg_list,
1185 x_return_status => x_return_status,
1186 x_msg_count => x_msg_count,
1187 x_msg_data => x_msg_data,
1188 -- p_restricted_update => p_restricted_update,
1189 p_clev_rec => l_clev_rec,
1190 x_clev_rec => x_clev_rec);
1191
1192
1193 If (l_return_value in ('N')) Then
1194 UPDATE OKC_ASSENTS SET ALLOWED_YN = l_return_value
1195 WHERE sts_code = l_sts_code
1196 AND scs_code = l_scs_code
1197 AND opn_code = 'UPDATE';
1198 End If;
1199
1200
1201 -- check return status
1202 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
1203 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1204 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
1205 raise OKC_API.G_EXCEPTION_ERROR;
1206 End If;
1207
1208 -- get id from OKC record
1209
1210 l_cle_rec.K_LINE_ID := x_clev_rec.ID;
1211
1212
1213 -- update the original line number instead of OKC generated line number
1214
1215 UPDATE OKC_K_LINES_B
1216 SET line_number = l_line_number
1217 WHERE ID = l_cle_rec.K_LINE_ID;
1218
1219 -- prepare the OUT NOCOPY /* file.sql.39 change */ okc rec to reflact the update
1220 -- x_clev_rec.line_number := l_line_number;
1221
1222 -- call procedure in complex API
1223 OKE_CONTRACT_PVT.create_contract_line(
1224 p_api_version => p_api_version,
1225 p_init_msg_list => p_init_msg_list,
1226 x_return_status => x_return_status,
1227 x_msg_count => x_msg_count,
1228 x_msg_data => x_msg_data,
1229 p_cle_rec => l_cle_rec,
1230 x_cle_rec => x_cle_rec);
1231
1232 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
1233 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1234 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
1235 raise OKE_API.G_EXCEPTION_ERROR;
1236 End If;
1237
1238
1239 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
1240 x_msg_data => x_msg_data);
1241 EXCEPTION
1242 when OKE_API.G_EXCEPTION_ERROR then
1243 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1244 p_api_name => l_api_name,
1245 p_pkg_name => g_pkg_name,
1246 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1247 x_msg_count => x_msg_count,
1248 x_msg_data => x_msg_data,
1249 p_api_type => g_api_type);
1250
1251 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
1252 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1253 p_api_name => l_api_name,
1254 p_pkg_name => g_pkg_name,
1255 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1256 x_msg_count => x_msg_count,
1257 x_msg_data => x_msg_data,
1258 p_api_type => g_api_type);
1259
1260 when OKC_API.G_EXCEPTION_ERROR then
1261 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1262 p_api_name => l_api_name,
1263 p_pkg_name => g_pkg_name,
1264 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1265 x_msg_count => x_msg_count,
1266 x_msg_data => x_msg_data,
1267 p_api_type => g_api_type);
1268
1269 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
1270 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1271 p_api_name => l_api_name,
1272 p_pkg_name => g_pkg_name,
1273 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1274 x_msg_count => x_msg_count,
1275 x_msg_data => x_msg_data,
1276 p_api_type => g_api_type);
1277
1278 when OTHERS then
1279 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1280 p_api_name => l_api_name,
1281 p_pkg_name => g_pkg_name,
1282 p_exc_name => 'OTHERS',
1283 x_msg_count => x_msg_count,
1284 x_msg_data => x_msg_data,
1285 p_api_type => g_api_type);
1286
1287 END create_contract_line;
1288
1289 PROCEDURE create_contract_line(
1290 p_api_version IN NUMBER,
1291 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
1292 x_return_status OUT NOCOPY VARCHAR2,
1293 x_msg_count OUT NOCOPY NUMBER,
1294 x_msg_data OUT NOCOPY VARCHAR2,
1295 p_restricted_update IN VARCHAR2 DEFAULT OKE_API.G_TRUE,
1296 p_cle_tbl IN oke_cle_pvt.cle_tbl_type,
1297 p_clev_tbl IN okc_contract_pub.clev_tbl_type,
1298 x_cle_tbl OUT NOCOPY oke_cle_pvt.cle_tbl_type,
1299 x_clev_tbl OUT NOCOPY okc_contract_pub.clev_tbl_type) IS
1300
1301 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_CONTRACT_LINE';
1302 l_api_version CONSTANT NUMBER := 1.0;
1303 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1304 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1305 i NUMBER;
1306 l_cle_tbl oke_cle_pvt.cle_tbl_type;
1307 BEGIN
1308
1309 -- call START_ACTIVITY to create savepoint, check compatibility
1310 -- and initialize message list
1311 l_return_status := OKE_API.START_ACTIVITY(
1312 p_api_name => l_api_name,
1313 p_pkg_name => g_pkg_name,
1314 p_init_msg_list => p_init_msg_list,
1315 l_api_version => l_api_version,
1316 p_api_version => p_api_version,
1317 p_api_type => g_api_type,
1318 x_return_status => x_return_status);
1319
1320 -- check if activity started successfully
1321 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
1322 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1323 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
1324 raise OKE_API.G_EXCEPTION_ERROR;
1325
1326 End If;
1327
1328 If (p_clev_tbl.COUNT > 0) Then
1329 i := p_clev_tbl.FIRST;
1330 LOOP
1331 -- call procedure in public API for a record
1332 OKE_CONTRACT_PUB.create_contract_line(
1333 p_api_version => p_api_version,
1334 p_init_msg_list => p_init_msg_list,
1335 x_return_status => x_return_status,
1336 x_msg_count => x_msg_count,
1337 x_msg_data => x_msg_data,
1338 p_restricted_update => p_restricted_update,
1339 p_cle_rec => p_cle_tbl(i),
1340 p_clev_rec => p_clev_tbl(i),
1341 x_cle_rec => x_cle_tbl(i),
1342 x_clev_rec => x_clev_tbl(i));
1343
1344 -- store the highest degree of error
1345 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
1346 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
1347 l_overall_status := x_return_status;
1348 End If;
1349 End If;
1350
1351 EXIT WHEN (i = p_clev_tbl.LAST);
1352 i := p_clev_tbl.NEXT(i);
1353 END LOOP;
1354
1355 -- return overall status
1356 x_return_status := l_overall_status;
1357 End If;
1358
1359 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
1360 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1361 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
1362 raise OKE_API.G_EXCEPTION_ERROR;
1363 End If;
1364
1365 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
1366 x_msg_data => x_msg_data);
1367 EXCEPTION
1368 when OKE_API.G_EXCEPTION_ERROR then
1369 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1370 p_api_name => l_api_name,
1371 p_pkg_name => g_pkg_name,
1372 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1373 x_msg_count => x_msg_count,
1374 x_msg_data => x_msg_data,
1375 p_api_type => g_api_type);
1376
1377 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
1378 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1379 p_api_name => l_api_name,
1380 p_pkg_name => g_pkg_name,
1381 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1382 x_msg_count => x_msg_count,
1383 x_msg_data => x_msg_data,
1384 p_api_type => g_api_type);
1385
1386 when OTHERS then
1387 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1388 p_api_name => l_api_name,
1389 p_pkg_name => g_pkg_name,
1390 p_exc_name => 'OTHERS',
1391 x_msg_count => x_msg_count,
1392 x_msg_data => x_msg_data,
1393 p_api_type => g_api_type);
1394
1395 END create_contract_line;
1396
1397 PROCEDURE update_contract_line(
1398 p_api_version IN NUMBER,
1399 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
1400 x_return_status OUT NOCOPY VARCHAR2,
1401 x_msg_count OUT NOCOPY NUMBER,
1402 x_msg_data OUT NOCOPY VARCHAR2,
1403 p_restricted_update IN VARCHAR2 DEFAULT OKE_API.G_TRUE,
1404 p_cle_rec IN oke_cle_pvt.cle_rec_type,
1405 p_clev_rec IN okc_contract_pub.clev_rec_type,
1406 x_cle_rec OUT NOCOPY oke_cle_pvt.cle_rec_type,
1407 x_clev_rec OUT NOCOPY okc_contract_pub.clev_rec_type) IS
1408
1409 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CONTRACT_LINE';
1410 l_api_version CONSTANT NUMBER := 1.0;
1411 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1412 BEGIN
1413 -- call START_ACTIVITY to create savepoint, check compatibility
1414 -- and initialize message list
1415
1416
1417 l_return_status := OKE_API.START_ACTIVITY(
1418 p_api_name => l_api_name,
1419 p_pkg_name => g_pkg_name,
1420 p_init_msg_list => p_init_msg_list,
1421 l_api_version => l_api_version,
1422 p_api_version => p_api_version,
1423 p_api_type => g_api_type,
1424 x_return_status => x_return_status);
1425
1426 -- check if activity started successfully
1427 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
1428 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1429 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
1430 raise OKE_API.G_EXCEPTION_ERROR;
1431 End If;
1432
1433 -- call procedure in complex API
1434 OKC_CONTRACT_PUB.update_contract_line(
1435 p_api_version => p_api_version,
1436 p_init_msg_list => p_init_msg_list,
1437 x_return_status => x_return_status,
1438 x_msg_count => x_msg_count,
1439 x_msg_data => x_msg_data,
1440 p_restricted_update => p_restricted_update,
1441 p_clev_rec => p_clev_rec,
1442 x_clev_rec => x_clev_rec);
1443
1444 -- check return status
1445 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
1446 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1447 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
1448 raise OKC_API.G_EXCEPTION_ERROR;
1449 End If;
1450
1451 -- call complex api
1452
1453 OKE_CONTRACT_PVT.update_contract_line(
1454 p_api_version => p_api_version,
1455 p_init_msg_list => p_init_msg_list,
1456 x_return_status => x_return_status,
1457 x_msg_count => x_msg_count,
1458 x_msg_data => x_msg_data,
1459 p_cle_rec => p_cle_rec,
1460 x_cle_rec => x_cle_rec);
1461
1462 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
1463 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1464 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
1465 raise OKE_API.G_EXCEPTION_ERROR;
1466 End If;
1467
1468 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
1469 x_msg_data => x_msg_data);
1470 EXCEPTION
1471 when OKE_API.G_EXCEPTION_ERROR then
1472 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1473 p_api_name => l_api_name,
1474 p_pkg_name => g_pkg_name,
1475 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1476 x_msg_count => x_msg_count,
1477 x_msg_data => x_msg_data,
1478 p_api_type => g_api_type);
1479
1480 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
1481 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1482 p_api_name => l_api_name,
1483 p_pkg_name => g_pkg_name,
1484 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1485 x_msg_count => x_msg_count,
1486 x_msg_data => x_msg_data,
1487 p_api_type => g_api_type);
1488
1489 when OKC_API.G_EXCEPTION_ERROR then
1490 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1491 p_api_name => l_api_name,
1492 p_pkg_name => g_pkg_name,
1493 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1494 x_msg_count => x_msg_count,
1495 x_msg_data => x_msg_data,
1496 p_api_type => g_api_type);
1497
1498 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
1499 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1500 p_api_name => l_api_name,
1501 p_pkg_name => g_pkg_name,
1502 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1503 x_msg_count => x_msg_count,
1504 x_msg_data => x_msg_data,
1505 p_api_type => g_api_type);
1506
1507 when OTHERS then
1508 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1509 p_api_name => l_api_name,
1510 p_pkg_name => g_pkg_name,
1511 p_exc_name => 'OTHERS',
1512 x_msg_count => x_msg_count,
1513 x_msg_data => x_msg_data,
1514 p_api_type => g_api_type);
1515
1516 END update_contract_line;
1517
1518 PROCEDURE update_contract_line(
1519 p_api_version IN NUMBER,
1520 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
1521 x_return_status OUT NOCOPY VARCHAR2,
1522 x_msg_count OUT NOCOPY NUMBER,
1523 x_msg_data OUT NOCOPY VARCHAR2,
1524 p_restricted_update IN VARCHAR2 DEFAULT OKE_API.G_TRUE,
1525 p_cle_tbl IN oke_cle_pvt.cle_tbl_type,
1526 p_clev_tbl IN okc_contract_pub.clev_tbl_type,
1527 x_cle_tbl OUT NOCOPY oke_cle_pvt.cle_tbl_type,
1528 x_clev_tbl OUT NOCOPY okc_contract_pub.clev_tbl_type) IS
1529
1530 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CONTRACT_HEADER';
1531 l_api_version CONSTANT NUMBER := 1.0;
1532 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1533 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1534 i NUMBER;
1535 BEGIN
1536 -- call START_ACTIVITY to create savepoint, check compatibility
1537 -- and initialize message list
1538
1539 l_return_status := OKE_API.START_ACTIVITY(
1540 p_api_name => l_api_name,
1541 p_pkg_name => g_pkg_name,
1542 p_init_msg_list => p_init_msg_list,
1543 l_api_version => l_api_version,
1544 p_api_version => p_api_version,
1545 p_api_type => g_api_type,
1546 x_return_status => x_return_status);
1547
1548 -- check if activity started successfully
1549 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
1550 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1551 Elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
1552 raise OKE_API.G_EXCEPTION_ERROR;
1553
1554 End If;
1555
1556 If (p_clev_tbl.COUNT > 0) Then
1557 i := p_clev_tbl.FIRST;
1558 LOOP
1559 -- call procedure in public API for a record
1560 OKE_CONTRACT_PUB.update_contract_line(
1561 p_api_version => p_api_version,
1562 p_init_msg_list => p_init_msg_list,
1563 x_return_status => x_return_status,
1564 x_msg_count => x_msg_count,
1565 x_msg_data => x_msg_data,
1566 p_restricted_update => p_restricted_update,
1567 p_cle_rec => p_cle_tbl(i),
1568 p_clev_rec => p_clev_tbl(i),
1569 x_cle_rec => x_cle_tbl(i),
1570 x_clev_rec => x_clev_tbl(i));
1571
1572 -- store the highest degree of error
1573 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
1574 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
1575 l_overall_status := x_return_status;
1576 End If;
1577 End If;
1578
1579 EXIT WHEN (i = p_clev_tbl.LAST);
1580 i := p_clev_tbl.NEXT(i);
1581 END LOOP;
1582
1583 -- return overall status
1584 x_return_status := l_overall_status;
1585 End If;
1586
1587 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
1588 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1589 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
1590 raise OKE_API.G_EXCEPTION_ERROR;
1591 End If;
1592
1593 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
1594 x_msg_data => x_msg_data);
1595 EXCEPTION
1596 when OKE_API.G_EXCEPTION_ERROR then
1597 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1598 p_api_name => l_api_name,
1599 p_pkg_name => g_pkg_name,
1600 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1601 x_msg_count => x_msg_count,
1602 x_msg_data => x_msg_data,
1603 p_api_type => g_api_type);
1604
1605 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
1606 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1607 p_api_name => l_api_name,
1608 p_pkg_name => g_pkg_name,
1609 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1610 x_msg_count => x_msg_count,
1611 x_msg_data => x_msg_data,
1612 p_api_type => g_api_type);
1613
1614 when OTHERS then
1615 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1616 p_api_name => l_api_name,
1617 p_pkg_name => g_pkg_name,
1618 p_exc_name => 'OTHERS',
1619 x_msg_count => x_msg_count,
1620 x_msg_data => x_msg_data,
1621 p_api_type => g_api_type);
1622
1623 END update_contract_line;
1624
1625 PROCEDURE delete_contract_line(
1626
1627 p_api_version IN NUMBER,
1628 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
1629 x_return_status OUT NOCOPY VARCHAR2,
1630 x_msg_count OUT NOCOPY NUMBER,
1631 x_msg_data OUT NOCOPY VARCHAR2,
1632 p_cle_rec IN oke_cle_pvt.cle_rec_type,
1633 p_clev_rec IN okc_contract_pub.clev_rec_type) IS
1634
1635 l_cle_rec oke_cle_pvt.cle_rec_type;
1636 l_clev_rec okc_contract_pub.clev_rec_type;
1637 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_CONTRACT_LINE';
1638 l_api_version CONSTANT NUMBER := 1.0;
1639 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1640 BEGIN
1641 -- call START_ACTIVITY to create savepoint, check compatibility
1642 -- and initialize message list
1643 l_return_status := OKE_API.START_ACTIVITY(
1644 p_api_name => l_api_name,
1645 p_pkg_name => g_pkg_name,
1646 p_init_msg_list => p_init_msg_list,
1647 l_api_version => l_api_version,
1648 p_api_version => p_api_version,
1649 p_api_type => g_api_type,
1650 x_return_status => x_return_status);
1651
1652 -- check if activity started successfully
1653 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
1654 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1655 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
1656 raise OKE_API.G_EXCEPTION_ERROR;
1657 End If;
1658
1659 l_cle_rec := p_cle_rec;
1660 l_clev_rec := p_clev_rec;
1661
1662 -- call complex api
1663
1664 OKE_CONTRACT_PVT.delete_contract_line(
1665 p_api_version => p_api_version,
1666 p_init_msg_list => p_init_msg_list,
1667 x_return_status => x_return_status,
1668 x_msg_count => x_msg_count,
1669 x_msg_data => x_msg_data,
1670 p_cle_rec => l_cle_rec);
1671
1672 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
1673 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1674 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
1675 raise OKE_API.G_EXCEPTION_ERROR;
1676 End If;
1677
1678 -- call procedure in complex API
1679 OKC_CONTRACT_PUB.delete_contract_line(
1680 p_api_version => p_api_version,
1681 p_init_msg_list => p_init_msg_list,
1682 x_return_status => x_return_status,
1683 x_msg_count => x_msg_count,
1684 x_msg_data => x_msg_data,
1685 p_clev_rec => l_clev_rec);
1686
1687 -- check return status
1688 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
1689 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1690 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
1691 raise OKC_API.G_EXCEPTION_ERROR;
1692 End If;
1693
1694 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
1695 x_msg_data => x_msg_data);
1696 EXCEPTION
1697 when OKE_API.G_EXCEPTION_ERROR then
1698 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1699 p_api_name => l_api_name,
1700 p_pkg_name => g_pkg_name,
1701 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1702 x_msg_count => x_msg_count,
1703 x_msg_data => x_msg_data,
1704 p_api_type => g_api_type);
1705
1706 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
1707 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1708 p_api_name => l_api_name,
1709 p_pkg_name => g_pkg_name,
1710 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1711 x_msg_count => x_msg_count,
1712 x_msg_data => x_msg_data,
1713 p_api_type => g_api_type);
1714
1715 when OKC_API.G_EXCEPTION_ERROR then
1716 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1717 p_api_name => l_api_name,
1718 p_pkg_name => g_pkg_name,
1719 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1720 x_msg_count => x_msg_count,
1721 x_msg_data => x_msg_data,
1722 p_api_type => g_api_type);
1723
1724 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
1725 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1726 p_api_name => l_api_name,
1727 p_pkg_name => g_pkg_name,
1728 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1729 x_msg_count => x_msg_count,
1730 x_msg_data => x_msg_data,
1731 p_api_type => g_api_type);
1732
1733 when OTHERS then
1734 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1735 p_api_name => l_api_name,
1736 p_pkg_name => g_pkg_name,
1737 p_exc_name => 'OTHERS',
1738 x_msg_count => x_msg_count,
1739 x_msg_data => x_msg_data,
1740 p_api_type => g_api_type);
1741
1742 END delete_contract_line;
1743
1744 PROCEDURE delete_contract_line(
1745 p_api_version IN NUMBER,
1746 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
1747 x_return_status OUT NOCOPY VARCHAR2,
1748 x_msg_count OUT NOCOPY NUMBER,
1749 x_msg_data OUT NOCOPY VARCHAR2,
1750 p_cle_tbl IN oke_cle_pvt.cle_tbl_type,
1751 p_clev_tbl IN okc_contract_pub.clev_tbl_type) IS
1752
1753 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_CONTRACT_HEADER';
1754 l_api_version CONSTANT NUMBER := 1.0;
1755 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1756 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1757 i NUMBER;
1758 BEGIN
1759 -- call START_ACTIVITY to create savepoint, check compatibility
1760 -- and initialize message list
1761 l_return_status := OKE_API.START_ACTIVITY(
1762 p_api_name => l_api_name,
1763 p_pkg_name => g_pkg_name,
1764 p_init_msg_list => p_init_msg_list,
1765 l_api_version => l_api_version,
1766 p_api_version => p_api_version,
1767 p_api_type => g_api_type,
1768 x_return_status => x_return_status);
1769
1770 -- check if activity started successfully
1771 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
1772 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1773 Elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
1774 raise OKE_API.G_EXCEPTION_ERROR;
1775
1776 End If;
1777
1778 If (p_clev_tbl.COUNT > 0) Then
1779 i := p_clev_tbl.FIRST;
1780 LOOP
1781
1782 -- call complex API
1783
1784 OKE_CONTRACT_PVT.delete_contract_line(
1785
1786 p_api_version => p_api_version,
1787 p_init_msg_list => p_init_msg_list,
1788 x_return_status => x_return_status,
1789 x_msg_count => x_msg_count,
1790 x_msg_data => x_msg_data,
1791 p_cle_rec => p_cle_tbl(i));
1792
1793 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
1794 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
1795 l_overall_status := x_return_status;
1796 End If;
1797 End If;
1798
1799 -- call procedure in public API for a record
1800 OKC_CONTRACT_PUB.delete_contract_line(
1801 p_api_version => p_api_version,
1802 p_init_msg_list => p_init_msg_list,
1803 x_return_status => x_return_status,
1804 x_msg_count => x_msg_count,
1805 x_msg_data => x_msg_data,
1806 p_clev_rec => p_clev_tbl(i));
1807
1808 -- store the highest degree of error
1809 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
1810 If l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
1811 l_overall_status := x_return_status;
1812 End If;
1813 End If;
1814
1815 EXIT WHEN (i = p_clev_tbl.LAST);
1816 i := p_clev_tbl.NEXT(i);
1817 END LOOP;
1818
1819 -- return overall status
1820 x_return_status := l_overall_status;
1821 End If;
1822
1823 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
1824 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1825 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
1826 raise OKE_API.G_EXCEPTION_ERROR;
1827 End If;
1828
1829 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
1830 x_msg_data => x_msg_data);
1831 EXCEPTION
1832 when OKE_API.G_EXCEPTION_ERROR then
1833 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1834 p_api_name => l_api_name,
1835 p_pkg_name => g_pkg_name,
1836 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1837 x_msg_count => x_msg_count,
1838 x_msg_data => x_msg_data,
1839 p_api_type => g_api_type);
1840
1841 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
1842 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1843 p_api_name => l_api_name,
1844 p_pkg_name => g_pkg_name,
1845 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1846 x_msg_count => x_msg_count,
1847 x_msg_data => x_msg_data,
1848 p_api_type => g_api_type);
1849
1850 when OKC_API.G_EXCEPTION_ERROR then
1851 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1852 p_api_name => l_api_name,
1853 p_pkg_name => g_pkg_name,
1854 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1855 x_msg_count => x_msg_count,
1856 x_msg_data => x_msg_data,
1857 p_api_type => g_api_type);
1858
1859 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
1860 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1861 p_api_name => l_api_name,
1862 p_pkg_name => g_pkg_name,
1863 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1864 x_msg_count => x_msg_count,
1865 x_msg_data => x_msg_data,
1866 p_api_type => g_api_type);
1867
1868 when OTHERS then
1869 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1870 p_api_name => l_api_name,
1871 p_pkg_name => g_pkg_name,
1872 p_exc_name => 'OTHERS',
1873 x_msg_count => x_msg_count,
1874 x_msg_data => x_msg_data,
1875 p_api_type => g_api_type);
1876
1877 END delete_contract_line;
1878
1879 PROCEDURE delete_contract_line(
1880 p_api_version IN NUMBER,
1881 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
1882 x_return_status OUT NOCOPY VARCHAR2,
1883 x_msg_count OUT NOCOPY NUMBER,
1884 x_msg_data OUT NOCOPY VARCHAR2,
1885 p_line_id IN NUMBER) IS
1886 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_CONTRACT_LINE';
1887
1888 BEGIN
1889
1890 OKE_CONTRACT_PVT.delete_contract_line(
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_line_id => p_line_id);
1897
1898 OKC_CONTRACT_PVT.delete_contract_line(
1899 p_api_version => p_api_version,
1900 p_init_msg_list => p_init_msg_list,
1901 x_return_status => x_return_status,
1902 x_msg_count => x_msg_count,
1903 x_msg_data => x_msg_data,
1904 p_line_id => p_line_id);
1905
1906 EXCEPTION
1907 when OKE_API.G_EXCEPTION_ERROR then
1908 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1909 p_api_name => l_api_name,
1910 p_pkg_name => g_pkg_name,
1911 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1912 x_msg_count => x_msg_count,
1913 x_msg_data => x_msg_data,
1914 p_api_type => g_api_type);
1915
1916 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
1917 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1918 p_api_name => l_api_name,
1919 p_pkg_name => g_pkg_name,
1920 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1921 x_msg_count => x_msg_count,
1922 x_msg_data => x_msg_data,
1923 p_api_type => g_api_type);
1924
1925 when OKC_API.G_EXCEPTION_ERROR then
1926 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1927 p_api_name => l_api_name,
1928 p_pkg_name => g_pkg_name,
1929 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1930 x_msg_count => x_msg_count,
1931 x_msg_data => x_msg_data,
1932 p_api_type => g_api_type);
1933
1934 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
1935 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1936 p_api_name => l_api_name,
1937 p_pkg_name => g_pkg_name,
1938 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1939 x_msg_count => x_msg_count,
1940 x_msg_data => x_msg_data,
1941 p_api_type => g_api_type);
1942
1943 when OTHERS then
1944 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1945 p_api_name => l_api_name,
1946 p_pkg_name => g_pkg_name,
1947 p_exc_name => 'OTHERS',
1948 x_msg_count => x_msg_count,
1949 x_msg_data => x_msg_data,
1950 p_api_type => g_api_type);
1951
1952 END delete_contract_line;
1953
1954 PROCEDURE validate_contract_line(
1955
1956 p_api_version IN NUMBER,
1957 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
1958 x_return_status OUT NOCOPY VARCHAR2,
1959 x_msg_count OUT NOCOPY NUMBER,
1960 x_msg_data OUT NOCOPY VARCHAR2,
1961 p_cle_rec IN oke_cle_pvt.cle_rec_type,
1962 p_clev_rec IN okc_contract_pub.clev_rec_type) IS
1963
1964 l_cle_rec oke_cle_pvt.cle_rec_type;
1965 l_clev_rec okc_contract_pub.clev_rec_type;
1966 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_CONTRACT_LINE';
1967 l_api_version CONSTANT NUMBER := 1.0;
1968 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1969
1970 BEGIN
1971 -- call START_ACTIVITY to create savepoint, check compatibility
1972 -- and initialize message list
1973 l_return_status := OKE_API.START_ACTIVITY(
1974 p_api_name => l_api_name,
1975 p_pkg_name => g_pkg_name,
1976 p_init_msg_list => p_init_msg_list,
1977 l_api_version => l_api_version,
1978 p_api_version => p_api_version,
1979 p_api_type => g_api_type,
1980 x_return_status => x_return_status);
1981
1982 -- check if activity started successfully
1983 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
1984 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1985 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
1986 raise OKE_API.G_EXCEPTION_ERROR;
1987 End If;
1988
1989 -- call BEFORE user hook
1990 l_cle_rec := p_cle_rec;
1991 l_clev_rec := p_clev_rec;
1992
1993 -- call procedure in complex API
1994 OKC_CONTRACT_PUB.validate_contract_line(
1995 p_api_version => p_api_version,
1996 p_init_msg_list => p_init_msg_list,
1997 x_return_status => x_return_status,
1998 x_msg_count => x_msg_count,
1999 x_msg_data => x_msg_data,
2000 p_clev_rec => l_clev_rec);
2001
2002 -- check return status
2003 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
2004 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2005 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
2006 raise OKC_API.G_EXCEPTION_ERROR;
2007 End If;
2008
2009 -- get id for OKE record
2010
2011 l_cle_rec.K_LINE_ID := l_clev_rec.ID;
2012
2013 -- call complex API
2014 OKE_CONTRACT_PVT.validate_contract_line(
2015 p_api_version => p_api_version,
2016 p_init_msg_list => p_init_msg_list,
2017 x_return_status => x_return_status,
2018 x_msg_count => x_msg_count,
2019 x_msg_data => x_msg_data,
2020 p_cle_rec => l_cle_rec);
2021
2022 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
2023 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2024 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
2025 raise OKE_API.G_EXCEPTION_ERROR;
2026 End If;
2027
2028 -- end activity
2029 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
2030 x_msg_data => x_msg_data);
2031 EXCEPTION
2032 when OKE_API.G_EXCEPTION_ERROR then
2033 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2034 p_api_name => l_api_name,
2035 p_pkg_name => g_pkg_name,
2036 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
2037 x_msg_count => x_msg_count,
2038 x_msg_data => x_msg_data,
2039 p_api_type => g_api_type);
2040
2041 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
2042 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2043 p_api_name => l_api_name,
2044 p_pkg_name => g_pkg_name,
2045 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
2046 x_msg_count => x_msg_count,
2047 x_msg_data => x_msg_data,
2048 p_api_type => g_api_type);
2049
2050 when OKC_API.G_EXCEPTION_ERROR then
2051 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2052 p_api_name => l_api_name,
2053 p_pkg_name => g_pkg_name,
2054 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
2055 x_msg_count => x_msg_count,
2056 x_msg_data => x_msg_data,
2057 p_api_type => g_api_type);
2058
2059 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
2060 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2061 p_api_name => l_api_name,
2062 p_pkg_name => g_pkg_name,
2063 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
2064 x_msg_count => x_msg_count,
2065 x_msg_data => x_msg_data,
2066 p_api_type => g_api_type);
2067
2068 when OTHERS then
2069 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2070 p_api_name => l_api_name,
2071 p_pkg_name => g_pkg_name,
2072 p_exc_name => 'OTHERS',
2073 x_msg_count => x_msg_count,
2074 x_msg_data => x_msg_data,
2075 p_api_type => g_api_type);
2076
2077 END validate_contract_line;
2078
2079 PROCEDURE validate_contract_line(
2080 p_api_version IN NUMBER,
2081 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
2082 x_return_status OUT NOCOPY VARCHAR2,
2083 x_msg_count OUT NOCOPY NUMBER,
2084 x_msg_data OUT NOCOPY VARCHAR2,
2085 p_cle_tbl IN oke_cle_pvt.cle_tbl_type,
2086 p_clev_tbl IN okc_contract_pub.clev_tbl_type) IS
2087
2088 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_CONTRACT_LINE';
2089
2090 l_api_version CONSTANT NUMBER := 1.0;
2091 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2092 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2093 i NUMBER;
2094 l_cle_tbl oke_cle_pvt.cle_tbl_type := p_cle_tbl;
2095 BEGIN
2096 -- call START_ACTIVITY to create savepoint, check compatibility
2097 -- and initialize message list
2098 l_return_status := OKE_API.START_ACTIVITY(
2099 p_api_name => l_api_name,
2100 p_pkg_name => g_pkg_name,
2101 p_init_msg_list => p_init_msg_list,
2102 l_api_version => l_api_version,
2103 p_api_version => p_api_version,
2104 p_api_type => g_api_type,
2105 x_return_status => x_return_status);
2106
2107 -- check if activity started successfully
2108 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
2109 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2110 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
2111 raise OKE_API.G_EXCEPTION_ERROR;
2112 End If;
2113
2114 If (p_clev_tbl.COUNT > 0) Then
2115 i := p_clev_tbl.FIRST;
2116 LOOP
2117 -- call procedure in complex API
2118 OKC_CONTRACT_PUB.validate_contract_line(
2119 p_api_version => p_api_version,
2120 p_init_msg_list => p_init_msg_list,
2121 x_return_status => x_return_status,
2122 x_msg_count => x_msg_count,
2123 x_msg_data => x_msg_data,
2124 p_clev_rec => p_clev_tbl(i));
2125
2126 -- store the highest degree of error
2127 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
2128 If l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
2129 l_overall_status := x_return_status;
2130 End If;
2131 End If;
2132
2133 l_cle_tbl(i).K_LINE_ID := p_clev_tbl(i).ID;
2134
2135 -- call complex API
2136 OKE_CONTRACT_PVT.validate_contract_line(
2137 p_api_version => p_api_version,
2138 p_init_msg_list => p_init_msg_list,
2139 x_return_status => x_return_status,
2140 x_msg_count => x_msg_count,
2141 x_msg_data => x_msg_data,
2142 p_cle_rec => p_cle_tbl(i));
2143
2144 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
2145 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
2146 l_overall_status := x_return_status;
2147 End If;
2148 End If;
2149
2150 EXIT WHEN (i = p_clev_tbl.LAST);
2151 i := p_clev_tbl.NEXT(i);
2152 END LOOP;
2153
2154 -- return overall status
2155 x_return_status := l_overall_status;
2156 End If;
2157
2158 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
2159 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2160 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
2161 raise OKE_API.G_EXCEPTION_ERROR;
2162 End If;
2163
2164 -- end activity
2165 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
2166 x_msg_data => x_msg_data);
2167 EXCEPTION
2168 when OKE_API.G_EXCEPTION_ERROR then
2169 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2170 p_api_name => l_api_name,
2171 p_pkg_name => g_pkg_name,
2172 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
2173 x_msg_count => x_msg_count,
2174 x_msg_data => x_msg_data,
2175 p_api_type => g_api_type);
2176
2177 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
2178 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2179 p_api_name => l_api_name,
2180 p_pkg_name => g_pkg_name,
2181 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
2182 x_msg_count => x_msg_count,
2183 x_msg_data => x_msg_data,
2184 p_api_type => g_api_type);
2185
2186 when OKC_API.G_EXCEPTION_ERROR then
2187 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2188 p_api_name => l_api_name,
2189 p_pkg_name => g_pkg_name,
2190 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
2191 x_msg_count => x_msg_count,
2192 x_msg_data => x_msg_data,
2193 p_api_type => g_api_type);
2194
2195 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
2196 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2197 p_api_name => l_api_name,
2198 p_pkg_name => g_pkg_name,
2199 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
2200 x_msg_count => x_msg_count,
2201 x_msg_data => x_msg_data,
2202 p_api_type => g_api_type);
2203
2204 when OTHERS then
2205 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2206 p_api_name => l_api_name,
2207 p_pkg_name => g_pkg_name,
2208 p_exc_name => 'OTHERS',
2209 x_msg_count => x_msg_count,
2210 x_msg_data => x_msg_data,
2211 p_api_type => g_api_type);
2212
2213 END validate_contract_line;
2214
2215 -----------------------------------------------------------------------------
2216 -- deliverable section
2217 -----------------------------------------------------------------------------
2218 PROCEDURE create_deliverable(
2219 p_api_version IN NUMBER,
2220 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
2221 x_return_status OUT NOCOPY VARCHAR2,
2222 x_msg_count OUT NOCOPY NUMBER,
2223 x_msg_data OUT NOCOPY VARCHAR2,
2224 p_del_rec IN oke_deliverable_pvt.del_rec_type,
2225
2226 x_del_rec OUT NOCOPY oke_deliverable_pvt.del_rec_type) IS
2227
2228 l_del_rec oke_deliverable_pvt.del_rec_type;
2229
2230 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_DELIVERABLE';
2231 l_api_version CONSTANT NUMBER := 1.0;
2232 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2233 l_line_number VARCHAR2(120);
2234 BEGIN
2235 -- call START_ACTIVITY to create savepoint, check compatibility
2236 -- and initialize message list
2237 l_return_status := OKE_API.START_ACTIVITY(
2238 p_api_name => l_api_name,
2239 p_pkg_name => g_pkg_name,
2240 p_init_msg_list => p_init_msg_list,
2241 l_api_version => l_api_version,
2242 p_api_version => p_api_version,
2243 p_api_type => g_api_type,
2244 x_return_status => x_return_status);
2245
2246 -- check if activity started successfully
2247 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
2248 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2249 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
2250 raise OKE_API.G_EXCEPTION_ERROR;
2251
2252 End If;
2253
2254 l_del_rec := p_del_rec;
2255
2256 -- call procedure in complex API
2257 OKE_CONTRACT_PVT.create_deliverable(
2258 p_api_version => p_api_version,
2259 p_init_msg_list => p_init_msg_list,
2260 x_return_status => x_return_status,
2261 x_msg_count => x_msg_count,
2262 x_msg_data => x_msg_data,
2263 p_del_rec => l_del_rec,
2264 x_del_rec => x_del_rec);
2265
2266 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
2267 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2268 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
2269 raise OKE_API.G_EXCEPTION_ERROR;
2270 End If;
2271
2272 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
2273 x_msg_data => x_msg_data);
2274 EXCEPTION
2275 when OKE_API.G_EXCEPTION_ERROR then
2276 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2277 p_api_name => l_api_name,
2278 p_pkg_name => g_pkg_name,
2279 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
2280 x_msg_count => x_msg_count,
2281 x_msg_data => x_msg_data,
2282 p_api_type => g_api_type);
2283
2284 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
2285 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2286 p_api_name => l_api_name,
2287 p_pkg_name => g_pkg_name,
2288 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
2289 x_msg_count => x_msg_count,
2290 x_msg_data => x_msg_data,
2291 p_api_type => g_api_type);
2292
2293 when OTHERS then
2294 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2295 p_api_name => l_api_name,
2296 p_pkg_name => g_pkg_name,
2297 p_exc_name => 'OTHERS',
2298 x_msg_count => x_msg_count,
2299 x_msg_data => x_msg_data,
2300 p_api_type => g_api_type);
2301
2302 END create_deliverable;
2303
2304 PROCEDURE create_deliverable(
2305 p_api_version IN NUMBER,
2306 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
2307 x_return_status OUT NOCOPY VARCHAR2,
2308 x_msg_count OUT NOCOPY NUMBER,
2309 x_msg_data OUT NOCOPY VARCHAR2,
2310 p_del_tbl IN oke_deliverable_pvt.del_tbl_type,
2311 x_del_tbl OUT NOCOPY oke_deliverable_pvt.del_tbl_type) IS
2312
2313 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_DELIVERABLE';
2314 l_api_version CONSTANT NUMBER := 1.0;
2315 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2316 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2317 i NUMBER;
2318 l_del_tbl oke_deliverable_pvt.del_tbl_type;
2319 BEGIN
2320 -- call START_ACTIVITY to create savepoint, check compatibility
2321 -- and initialize message list
2322 l_return_status := OKE_API.START_ACTIVITY(
2323 p_api_name => l_api_name,
2324 p_pkg_name => g_pkg_name,
2325 p_init_msg_list => p_init_msg_list,
2326 l_api_version => l_api_version,
2327 p_api_version => p_api_version,
2328 p_api_type => g_api_type,
2329 x_return_status => x_return_status);
2330
2331 -- check if activity started successfully
2332 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
2333 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2334 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
2335 raise OKE_API.G_EXCEPTION_ERROR;
2336
2337 End If;
2338
2339 If (p_del_tbl.COUNT > 0) Then
2340 i := p_del_tbl.FIRST;
2341 LOOP
2342 -- call procedure in public API for a record
2343 OKE_CONTRACT_PUB.create_deliverable(
2344 p_api_version => p_api_version,
2345 p_init_msg_list => p_init_msg_list,
2346 x_return_status => x_return_status,
2347 x_msg_count => x_msg_count,
2348 x_msg_data => x_msg_data,
2349 p_del_rec => p_del_tbl(i),
2350 x_del_rec => x_del_tbl(i));
2351
2352 -- store the highest degree of error
2353 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
2354 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
2355 l_overall_status := x_return_status;
2356 End If;
2357 End If;
2358
2359 EXIT WHEN (i = p_del_tbl.LAST);
2360 i := p_del_tbl.NEXT(i);
2361 END LOOP;
2362
2363 -- return overall status
2364 x_return_status := l_overall_status;
2365 End If;
2366
2367 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
2368 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2369 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
2370 raise OKE_API.G_EXCEPTION_ERROR;
2371 End If;
2372
2373 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
2374 x_msg_data => x_msg_data);
2375 EXCEPTION
2376 when OKE_API.G_EXCEPTION_ERROR then
2377 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2378 p_api_name => l_api_name,
2379 p_pkg_name => g_pkg_name,
2380 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
2381 x_msg_count => x_msg_count,
2382 x_msg_data => x_msg_data,
2383 p_api_type => g_api_type);
2384
2385 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
2386 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2387 p_api_name => l_api_name,
2388 p_pkg_name => g_pkg_name,
2389 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
2390 x_msg_count => x_msg_count,
2391 x_msg_data => x_msg_data,
2392 p_api_type => g_api_type);
2393
2394 when OTHERS then
2395 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2396 p_api_name => l_api_name,
2397 p_pkg_name => g_pkg_name,
2398 p_exc_name => 'OTHERS',
2399 x_msg_count => x_msg_count,
2400 x_msg_data => x_msg_data,
2401 p_api_type => g_api_type);
2402
2403 END create_deliverable;
2404
2405 PROCEDURE update_deliverable(
2406
2407 p_api_version IN NUMBER,
2408 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
2409 x_return_status OUT NOCOPY VARCHAR2,
2410 x_msg_count OUT NOCOPY NUMBER,
2411 x_msg_data OUT NOCOPY VARCHAR2,
2412 p_del_rec IN oke_deliverable_pvt.del_rec_type,
2413 x_del_rec OUT NOCOPY oke_deliverable_pvt.del_rec_type) IS
2414
2415 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_DELIVERABLE';
2416 l_api_version CONSTANT NUMBER := 1.0;
2417 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2418 BEGIN
2419 -- call START_ACTIVITY to create savepoint, check compatibility
2420 -- and initialize message list
2421
2422 l_return_status := OKE_API.START_ACTIVITY(
2423 p_api_name => l_api_name,
2424 p_pkg_name => g_pkg_name,
2425 p_init_msg_list => p_init_msg_list,
2426 l_api_version => l_api_version,
2427 p_api_version => p_api_version,
2428 p_api_type => g_api_type,
2429 x_return_status => x_return_status);
2430
2431 -- check if activity started successfully
2432 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
2433 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2434 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
2435 raise OKE_API.G_EXCEPTION_ERROR;
2436 End If;
2437
2438 -- call complex api
2439
2440 OKE_CONTRACT_PVT.update_deliverable(
2441 p_api_version => p_api_version,
2442 p_init_msg_list => p_init_msg_list,
2443 x_return_status => x_return_status,
2444 x_msg_count => x_msg_count,
2445 x_msg_data => x_msg_data,
2446 p_del_rec => p_del_rec,
2447 x_del_rec => x_del_rec);
2448
2449 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
2450 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2451 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
2452 raise OKE_API.G_EXCEPTION_ERROR;
2453 End If;
2454
2455 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
2456 x_msg_data => x_msg_data);
2457 EXCEPTION
2458 when OKE_API.G_EXCEPTION_ERROR then
2459 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2460 p_api_name => l_api_name,
2461 p_pkg_name => g_pkg_name,
2462 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
2463 x_msg_count => x_msg_count,
2464 x_msg_data => x_msg_data,
2465 p_api_type => g_api_type);
2466
2467 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
2468 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2469 p_api_name => l_api_name,
2470 p_pkg_name => g_pkg_name,
2471 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
2472 x_msg_count => x_msg_count,
2473 x_msg_data => x_msg_data,
2474 p_api_type => g_api_type);
2475
2476 when OTHERS then
2477 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2478 p_api_name => l_api_name,
2479 p_pkg_name => g_pkg_name,
2480 p_exc_name => 'OTHERS',
2481 x_msg_count => x_msg_count,
2482 x_msg_data => x_msg_data,
2483 p_api_type => g_api_type);
2484
2485 END update_deliverable;
2486
2487 PROCEDURE update_deliverable(
2488 p_api_version IN NUMBER,
2489 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
2490 x_return_status OUT NOCOPY VARCHAR2,
2491 x_msg_count OUT NOCOPY NUMBER,
2492 x_msg_data OUT NOCOPY VARCHAR2,
2493 p_del_tbl IN oke_deliverable_pvt.del_tbl_type,
2494 x_del_tbl OUT NOCOPY oke_deliverable_pvt.del_tbl_type) IS
2495
2496 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CONTRACT_HEADER';
2497 l_api_version CONSTANT NUMBER := 1.0;
2498 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2499 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2500 i NUMBER;
2501 BEGIN
2502 -- call START_ACTIVITY to create savepoint, check compatibility
2503 -- and initialize message list
2504
2505 l_return_status := OKE_API.START_ACTIVITY(
2506 p_api_name => l_api_name,
2507 p_pkg_name => g_pkg_name,
2508 p_init_msg_list => p_init_msg_list,
2509 l_api_version => l_api_version,
2510 p_api_version => p_api_version,
2511 p_api_type => g_api_type,
2512 x_return_status => x_return_status);
2513
2514 -- check if activity started successfully
2515 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
2516 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2517 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
2518 raise OKE_API.G_EXCEPTION_ERROR;
2519
2520 End If;
2521
2522 If (p_del_tbl.COUNT > 0) Then
2523 i := p_del_tbl.FIRST;
2524 LOOP
2525 -- call procedure in public API for a record
2526 OKE_CONTRACT_PUB.update_deliverable(
2527 p_api_version => p_api_version,
2528 p_init_msg_list => p_init_msg_list,
2529 x_return_status => x_return_status,
2530 x_msg_count => x_msg_count,
2531 x_msg_data => x_msg_data,
2532 p_del_rec => p_del_tbl(i),
2533 x_del_rec => x_del_tbl(i));
2534
2535 -- store the highest degree of error
2536 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
2537 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
2538 l_overall_status := x_return_status;
2539 End If;
2540 End If;
2541
2542 EXIT WHEN (i = p_del_tbl.LAST);
2543 i := p_del_tbl.NEXT(i);
2544 END LOOP;
2545
2546 -- return overall status
2547 x_return_status := l_overall_status;
2548 End If;
2549
2550 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
2551 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2552 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
2553 raise OKE_API.G_EXCEPTION_ERROR;
2554 End If;
2555
2556 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
2557 x_msg_data => x_msg_data);
2558 EXCEPTION
2559 when OKE_API.G_EXCEPTION_ERROR then
2560 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2561 p_api_name => l_api_name,
2562 p_pkg_name => g_pkg_name,
2563 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
2564 x_msg_count => x_msg_count,
2565 x_msg_data => x_msg_data,
2566 p_api_type => g_api_type);
2567
2568 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
2569 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2570 p_api_name => l_api_name,
2571 p_pkg_name => g_pkg_name,
2572 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
2573 x_msg_count => x_msg_count,
2574 x_msg_data => x_msg_data,
2575 p_api_type => g_api_type);
2576
2577 when OTHERS then
2578 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2579 p_api_name => l_api_name,
2580 p_pkg_name => g_pkg_name,
2581 p_exc_name => 'OTHERS',
2582 x_msg_count => x_msg_count,
2583 x_msg_data => x_msg_data,
2584 p_api_type => g_api_type);
2585
2586 END update_deliverable;
2587
2588 PROCEDURE delete_deliverable(
2589
2590 p_api_version IN NUMBER,
2591 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
2592 x_return_status OUT NOCOPY VARCHAR2,
2593 x_msg_count OUT NOCOPY NUMBER,
2594 x_msg_data OUT NOCOPY VARCHAR2,
2595 p_del_rec IN oke_deliverable_pvt.del_rec_type) IS
2596
2597 l_del_rec oke_deliverable_pvt.del_rec_type;
2598 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_DELIVERABLE';
2599 l_api_version CONSTANT NUMBER := 1.0;
2600 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2601 BEGIN
2602 -- call START_ACTIVITY to create savepoint, check compatibility
2603 -- and initialize message list
2604 l_return_status := OKE_API.START_ACTIVITY(
2605 p_api_name => l_api_name,
2606 p_pkg_name => g_pkg_name,
2607 p_init_msg_list => p_init_msg_list,
2608 l_api_version => l_api_version,
2609 p_api_version => p_api_version,
2610 p_api_type => g_api_type,
2611 x_return_status => x_return_status);
2612
2613 -- check if activity started successfully
2614 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
2615 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2616 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
2617 raise OKE_API.G_EXCEPTION_ERROR;
2618 End If;
2619
2620 l_del_rec := p_del_rec;
2621
2622 -- call procedure in complex API
2623 OKE_CONTRACT_PVT.delete_deliverable(
2624 p_api_version => p_api_version,
2625 p_init_msg_list => p_init_msg_list,
2626 x_return_status => x_return_status,
2627 x_msg_count => x_msg_count,
2628 x_msg_data => x_msg_data,
2629 p_del_rec => l_del_rec);
2630
2631 -- check return status
2632 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
2633 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2634 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
2635 raise OKE_API.G_EXCEPTION_ERROR;
2636 End If;
2637
2638 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
2639 x_msg_data => x_msg_data);
2640 EXCEPTION
2641 when OKE_API.G_EXCEPTION_ERROR then
2642 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2643 p_api_name => l_api_name,
2644 p_pkg_name => g_pkg_name,
2645 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
2646 x_msg_count => x_msg_count,
2647 x_msg_data => x_msg_data,
2648 p_api_type => g_api_type);
2649
2650 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
2651 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2652 p_api_name => l_api_name,
2653 p_pkg_name => g_pkg_name,
2654 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
2655 x_msg_count => x_msg_count,
2656 x_msg_data => x_msg_data,
2657 p_api_type => g_api_type);
2658
2659 when OTHERS then
2660 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2661 p_api_name => l_api_name,
2662 p_pkg_name => g_pkg_name,
2663 p_exc_name => 'OTHERS',
2664 x_msg_count => x_msg_count,
2665 x_msg_data => x_msg_data,
2666 p_api_type => g_api_type);
2667
2668 END delete_deliverable;
2669
2670 PROCEDURE delete_deliverable(
2671 p_api_version IN NUMBER,
2672 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
2673 x_return_status OUT NOCOPY VARCHAR2,
2674 x_msg_count OUT NOCOPY NUMBER,
2675 x_msg_data OUT NOCOPY VARCHAR2,
2676 p_del_tbl IN oke_deliverable_pvt.del_tbl_type) IS
2677
2678 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_CONTRACT_HEADER';
2679 l_api_version CONSTANT NUMBER := 1.0;
2680 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2681 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2682 i NUMBER;
2683 BEGIN
2684 -- call START_ACTIVITY to create savepoint, check compatibility
2685 -- and initialize message list
2686 l_return_status := OKE_API.START_ACTIVITY(
2687 p_api_name => l_api_name,
2688 p_pkg_name => g_pkg_name,
2689 p_init_msg_list => p_init_msg_list,
2690 l_api_version => l_api_version,
2691 p_api_version => p_api_version,
2692 p_api_type => g_api_type,
2693 x_return_status => x_return_status);
2694
2695 -- check if activity started successfully
2696 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
2697 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2698 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
2699 raise OKE_API.G_EXCEPTION_ERROR;
2700
2701 End If;
2702
2703 If (p_del_tbl.COUNT > 0) Then
2704 i := p_del_tbl.FIRST;
2705 LOOP
2706
2707 -- call procedure in public API for a record
2708 OKE_CONTRACT_PUB.delete_deliverable(
2709 p_api_version => p_api_version,
2710 p_init_msg_list => p_init_msg_list,
2711 x_return_status => x_return_status,
2712 x_msg_count => x_msg_count,
2713 x_msg_data => x_msg_data,
2714 p_del_rec => p_del_tbl(i));
2715
2716 -- store the highest degree of error
2717 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
2718 If l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
2719 l_overall_status := x_return_status;
2720 End If;
2721 End If;
2722
2723 EXIT WHEN (i = p_del_tbl.LAST);
2724 i := p_del_tbl.NEXT(i);
2725 END LOOP;
2726
2727 -- return overall status
2728 x_return_status := l_overall_status;
2729 End If;
2730
2731 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
2732 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2733 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
2734 raise OKE_API.G_EXCEPTION_ERROR;
2735 End If;
2736
2737 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
2738 x_msg_data => x_msg_data);
2739 EXCEPTION
2740 when OKE_API.G_EXCEPTION_ERROR then
2741 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2742 p_api_name => l_api_name,
2743 p_pkg_name => g_pkg_name,
2744 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
2745 x_msg_count => x_msg_count,
2746 x_msg_data => x_msg_data,
2747 p_api_type => g_api_type);
2748
2749 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
2750 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2751 p_api_name => l_api_name,
2752 p_pkg_name => g_pkg_name,
2753 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
2754 x_msg_count => x_msg_count,
2755 x_msg_data => x_msg_data,
2756 p_api_type => g_api_type);
2757
2758 when OTHERS then
2759 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2760 p_api_name => l_api_name,
2761 p_pkg_name => g_pkg_name,
2762 p_exc_name => 'OTHERS',
2763 x_msg_count => x_msg_count,
2764 x_msg_data => x_msg_data,
2765 p_api_type => g_api_type);
2766
2767 END delete_deliverable;
2768
2769 PROCEDURE delete_deliverable(
2770 p_api_version IN NUMBER,
2771 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
2772 x_return_status OUT NOCOPY VARCHAR2,
2773 x_msg_count OUT NOCOPY NUMBER,
2774 x_msg_data OUT NOCOPY VARCHAR2,
2775 p_deliverable_id IN NUMBER) IS
2776
2777 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_DELIVERABLE';
2778 BEGIN
2779
2780 OKE_CONTRACT_PVT.delete_deliverable(
2781 p_api_version => p_api_version,
2782 p_init_msg_list => p_init_msg_list,
2783 x_return_status => x_return_status,
2784 x_msg_count => x_msg_count,
2785 x_msg_data => x_msg_data,
2786 p_deliverable_id => p_deliverable_id);
2787
2788 EXCEPTION
2789 when OTHERS then
2790 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2791
2792 p_api_name => l_api_name,
2793 p_pkg_name => g_pkg_name,
2794 p_exc_name => 'OTHERS',
2795 x_msg_count => x_msg_count,
2796 x_msg_data => x_msg_data,
2797 p_api_type => g_api_type);
2798
2799 END delete_deliverable;
2800
2801 PROCEDURE validate_deliverable(
2802
2803 p_api_version IN NUMBER,
2804 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
2805 x_return_status OUT NOCOPY VARCHAR2,
2806 x_msg_count OUT NOCOPY NUMBER,
2807 x_msg_data OUT NOCOPY VARCHAR2,
2808 p_del_rec IN oke_deliverable_pvt.del_rec_type) IS
2809
2810 l_del_rec oke_deliverable_pvt.del_rec_type;
2811
2812 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_DELIVERABLE';
2813 l_api_version CONSTANT NUMBER := 1.0;
2814 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2815
2816 BEGIN
2817 -- call START_ACTIVITY to create savepoint, check compatibility
2818 -- and initialize message list
2819 l_return_status := OKE_API.START_ACTIVITY(
2820 p_api_name => l_api_name,
2821 p_pkg_name => g_pkg_name,
2822 p_init_msg_list => p_init_msg_list,
2823 l_api_version => l_api_version,
2824 p_api_version => p_api_version,
2825 p_api_type => g_api_type,
2826 x_return_status => x_return_status);
2827
2828 -- check if activity started successfully
2829 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
2830 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2831 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
2832 raise OKE_API.G_EXCEPTION_ERROR;
2833 End If;
2834
2835 l_del_rec := p_del_rec;
2836
2837 -- call complex API
2838 OKE_CONTRACT_PVT.validate_deliverable(
2839 p_api_version => p_api_version,
2840 p_init_msg_list => p_init_msg_list,
2841 x_return_status => x_return_status,
2842 x_msg_count => x_msg_count,
2843 x_msg_data => x_msg_data,
2844 p_del_rec => l_del_rec);
2845
2846 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
2847 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2848 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
2849 raise OKE_API.G_EXCEPTION_ERROR;
2850 End If;
2851
2852 -- end activity
2853 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
2854 x_msg_data => x_msg_data);
2855 EXCEPTION
2856 when OKE_API.G_EXCEPTION_ERROR then
2857 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2858 p_api_name => l_api_name,
2859 p_pkg_name => g_pkg_name,
2860 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
2861 x_msg_count => x_msg_count,
2862 x_msg_data => x_msg_data,
2863 p_api_type => g_api_type);
2864
2865 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
2866 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2867 p_api_name => l_api_name,
2868 p_pkg_name => g_pkg_name,
2869 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
2870 x_msg_count => x_msg_count,
2871 x_msg_data => x_msg_data,
2872 p_api_type => g_api_type);
2873
2874 when OTHERS then
2875 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2876 p_api_name => l_api_name,
2877 p_pkg_name => g_pkg_name,
2878 p_exc_name => 'OTHERS',
2879 x_msg_count => x_msg_count,
2880 x_msg_data => x_msg_data,
2881 p_api_type => g_api_type);
2882
2883 END validate_deliverable;
2884
2885 PROCEDURE validate_deliverable(
2886 p_api_version IN NUMBER,
2887 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
2888 x_return_status OUT NOCOPY VARCHAR2,
2889 x_msg_count OUT NOCOPY NUMBER,
2890 x_msg_data OUT NOCOPY VARCHAR2,
2891 p_del_tbl IN oke_deliverable_pvt.del_tbl_type) IS
2892
2893 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_DELIVERABLE';
2894
2895 l_api_version CONSTANT NUMBER := 1.0;
2896 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2897 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2898 i NUMBER;
2899 l_del_tbl oke_deliverable_pvt.del_tbl_type := p_del_tbl;
2900 BEGIN
2901 -- call START_ACTIVITY to create savepoint, check compatibility
2902 -- and initialize message list
2903 l_return_status := OKE_API.START_ACTIVITY(
2904 p_api_name => l_api_name,
2905 p_pkg_name => g_pkg_name,
2906 p_init_msg_list => p_init_msg_list,
2907 l_api_version => l_api_version,
2908 p_api_version => p_api_version,
2909 p_api_type => g_api_type,
2910 x_return_status => x_return_status);
2911
2912 -- check if activity started successfully
2913 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
2914 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2915 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
2916 raise OKE_API.G_EXCEPTION_ERROR;
2917 End If;
2918
2919 If (p_del_tbl.COUNT > 0) Then
2920 i := p_del_tbl.FIRST;
2921 LOOP
2922
2923 -- call complex API
2924 OKE_CONTRACT_PVT.validate_deliverable(
2925 p_api_version => p_api_version,
2926 p_init_msg_list => p_init_msg_list,
2927 x_return_status => x_return_status,
2928 x_msg_count => x_msg_count,
2929 x_msg_data => x_msg_data,
2930 p_del_rec => p_del_tbl(i));
2931
2932 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
2933 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
2934 l_overall_status := x_return_status;
2935 End If;
2936 End If;
2937
2938 EXIT WHEN (i = p_del_tbl.LAST);
2939 i := p_del_tbl.NEXT(i);
2940 END LOOP;
2941
2942 -- return overall status
2943 x_return_status := l_overall_status;
2944 End If;
2945
2946 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
2947 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2948 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
2949 raise OKE_API.G_EXCEPTION_ERROR;
2950 End If;
2951
2952 -- end activity
2953 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
2954 x_msg_data => x_msg_data);
2955 EXCEPTION
2956 when OKE_API.G_EXCEPTION_ERROR then
2957 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2958 p_api_name => l_api_name,
2959 p_pkg_name => g_pkg_name,
2960 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
2961 x_msg_count => x_msg_count,
2962 x_msg_data => x_msg_data,
2963 p_api_type => g_api_type);
2964
2965 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
2966 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2967 p_api_name => l_api_name,
2968 p_pkg_name => g_pkg_name,
2969 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
2970 x_msg_count => x_msg_count,
2971 x_msg_data => x_msg_data,
2972 p_api_type => g_api_type);
2973
2974 when OTHERS then
2975 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2976 p_api_name => l_api_name,
2977 p_pkg_name => g_pkg_name,
2978 p_exc_name => 'OTHERS',
2979 x_msg_count => x_msg_count,
2980 x_msg_data => x_msg_data,
2981 p_api_type => g_api_type);
2982
2983 END validate_deliverable;
2984
2985 PROCEDURE lock_deliverable(
2986 p_api_version IN NUMBER,
2987 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
2988 x_return_status OUT NOCOPY VARCHAR2,
2989 x_msg_count OUT NOCOPY NUMBER,
2990 x_msg_data OUT NOCOPY VARCHAR2,
2991 p_del_rec IN oke_deliverable_pvt.del_rec_type) IS
2992
2993 l_del_rec oke_deliverable_pvt.del_rec_type;
2994 l_api_name CONSTANT VARCHAR2(30) := 'LOCK_DELIVERABLE';
2995 l_api_version CONSTANT NUMBER := 1.0;
2996 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2997 BEGIN
2998 -- call START_ACTIVITY to create savepoint, check compatibility
2999 -- and initialize message list
3000 l_return_status := OKE_API.START_ACTIVITY(
3001 p_api_name => l_api_name,
3002 p_pkg_name => g_pkg_name,
3003 p_init_msg_list => p_init_msg_list,
3004 l_api_version => l_api_version,
3005 p_api_version => p_api_version,
3006 p_api_type => g_api_type,
3007 x_return_status => x_return_status);
3008
3009 -- check if activity started successfully
3010 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
3011 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3012 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
3013 raise OKE_API.G_EXCEPTION_ERROR;
3014 End If;
3015
3016 -- call BEFORE user hook
3017 l_del_rec := p_del_rec;
3018
3019 -- call procedure in complex API
3020 OKE_CONTRACT_PVT.lock_deliverable(
3021 p_api_version => p_api_version,
3022 p_init_msg_list => p_init_msg_list,
3023 x_return_status => x_return_status,
3024 x_msg_count => x_msg_count,
3025 x_msg_data => x_msg_data,
3026 p_del_rec => l_del_rec);
3027
3028 -- check return status
3029 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
3030 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3031 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
3032 raise OKE_API.G_EXCEPTION_ERROR;
3033 End If;
3034
3035 -- end activity
3036 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
3037 x_msg_data => x_msg_data);
3038 EXCEPTION
3039 when OKE_API.G_EXCEPTION_ERROR then
3040 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
3041 p_api_name => l_api_name,
3042 p_pkg_name => g_pkg_name,
3043 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
3044 x_msg_count => x_msg_count,
3045 x_msg_data => x_msg_data,
3046 p_api_type => g_api_type);
3047
3048 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
3049 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
3050 p_api_name => l_api_name,
3051 p_pkg_name => g_pkg_name,
3052 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
3053 x_msg_count => x_msg_count,
3054 x_msg_data => x_msg_data,
3055 p_api_type => g_api_type);
3056
3057 when OTHERS then
3058 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
3059 p_api_name => l_api_name,
3060 p_pkg_name => g_pkg_name,
3061 p_exc_name => 'OTHERS',
3062 x_msg_count => x_msg_count,
3063 x_msg_data => x_msg_data,
3064 p_api_type => g_api_type);
3065
3066 END lock_deliverable;
3067
3068 PROCEDURE lock_deliverable(
3069 p_api_version IN NUMBER,
3070 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
3071 x_return_status OUT NOCOPY VARCHAR2,
3072 x_msg_count OUT NOCOPY NUMBER,
3073 x_msg_data OUT NOCOPY VARCHAR2,
3074 p_del_tbl IN oke_deliverable_pvt.del_tbl_type) IS
3075
3076 l_api_name CONSTANT VARCHAR2(30) := 'LOCK_DELIVERABLE';
3077 l_api_version CONSTANT NUMBER := 1.0;
3078 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
3079 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
3080 i NUMBER;
3081 BEGIN
3082 -- call START_ACTIVITY to create savepoint, check compatibility
3083 -- and initialize message list
3084 l_return_status := OKE_API.START_ACTIVITY(
3085 p_api_name => l_api_name,
3086 p_pkg_name => g_pkg_name,
3087 p_init_msg_list => p_init_msg_list,
3088 l_api_version => l_api_version,
3089 p_api_version => p_api_version,
3090 p_api_type => g_api_type,
3091 x_return_status => x_return_status);
3092
3093 -- check if activity started successfully
3094 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
3095 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3096 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
3097 raise OKE_API.G_EXCEPTION_ERROR;
3098 End If;
3099
3100 If (p_del_tbl.COUNT > 0) Then
3101 i := p_del_tbl.FIRST;
3102 LOOP
3103 -- call procedure in complex API
3104 OKE_CONTRACT_PVT.lock_deliverable(
3105 p_api_version => p_api_version,
3106 p_init_msg_list => p_init_msg_list,
3107 x_return_status => x_return_status,
3108 x_msg_count => x_msg_count,
3109 x_msg_data => x_msg_data,
3110 p_del_rec => p_del_tbl(i));
3111
3112 -- store the highest degree of error
3113 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
3114 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
3115 l_overall_status := x_return_status;
3116 End If;
3117 End If;
3118 EXIT WHEN (i = p_del_tbl.LAST);
3119 i := p_del_tbl.NEXT(i);
3120 END LOOP;
3121 -- return overall status
3122 x_return_status := l_overall_status;
3123 End If;
3124
3125 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
3126 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3127 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
3128 raise OKE_API.G_EXCEPTION_ERROR;
3129 End If;
3130
3131 -- end activity
3132 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
3133 x_msg_data => x_msg_data);
3134 EXCEPTION
3135 when OKE_API.G_EXCEPTION_ERROR then
3136 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
3137 p_api_name => l_api_name,
3138 p_pkg_name => g_pkg_name,
3139 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
3140 x_msg_count => x_msg_count,
3141 x_msg_data => x_msg_data,
3142 p_api_type => g_api_type);
3143
3144 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
3145 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
3146 p_api_name => l_api_name,
3147 p_pkg_name => g_pkg_name,
3148 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
3149 x_msg_count => x_msg_count,
3150 x_msg_data => x_msg_data,
3151 p_api_type => g_api_type);
3152
3153 when OTHERS then
3154 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
3155 p_api_name => l_api_name,
3156 p_pkg_name => g_pkg_name,
3157 p_exc_name => 'OTHERS',
3158 x_msg_count => x_msg_count,
3159 x_msg_data => x_msg_data,
3160 p_api_type => g_api_type);
3161
3162 END lock_deliverable;
3163
3164 PROCEDURE default_deliverable (
3165 p_api_version IN NUMBER,
3166 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
3167 x_return_status OUT NOCOPY VARCHAR2,
3168 x_msg_count OUT NOCOPY NUMBER,
3169 x_msg_data OUT NOCOPY VARCHAR2,
3170 p_header_id IN NUMBER,
3171 p_first_default_flag IN VARCHAR2,
3172 x_del_tbl OUT NOCOPY oke_deliverable_pvt.del_tbl_type) IS
3173
3174 l_api_version CONSTANT NUMBER := 1;
3175 l_del_tbl oke_deliverable_pvt.del_tbl_type;
3176 l_api_name CONSTANT VARCHAR2(30) := 'DEFAULT_DELIVERABLE';
3177
3178 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
3179 l_buy_or_sell VARCHAR2(1);
3180 l_direction VARCHAR2(3);
3181 l_org_id Number;
3182
3183 cursor l_default_csr(p_id number) is
3184 select buy_or_sell from okc_k_headers_b
3185 where id = p_id;
3186
3187 cursor l_csr(p_id NUMBER) is
3188 select l.k_line_id,
3189 l.line_number,
3190 l.project_id,
3191 l.inventory_item_id,
3192 l.line_description,
3193 l.delivery_date,
3194 l.status_code,
3195 l.start_date,
3196 l.end_date,
3197 k.priority_code,
3198 h.currency_code,
3199 l.unit_price,
3200 l.uom_code,
3201 l.line_quantity,
3202 k.country_of_origin_code,
3203 l.subcontracted_flag,
3204 l.billable_flag,
3205 l.drop_shipped_flag,
3206 l.completed_flag,
3207 l.shippable_flag,
3208 l.cfe_flag,
3209 l.inspection_req_flag,
3210 l.interim_rpt_req_flag,
3211 l.customer_approval_req_flag,
3212 l.as_of_date,
3213 l.date_of_first_submission,
3214 l.frequency,
3215 l.data_item_subtitle,
3216 l.copies_required,
3217 l.cdrl_category,
3218 l.data_item_name,
3219 l.export_flag
3220 from oke_k_lines_v l, okc_k_headers_b h, oke_k_headers k
3221 where h.id = p_id
3222 and l.header_id = p_id
3223 and h.id = k.k_header_id
3224 and not exists (select 'x' from okc_k_lines_b s where s.cle_id = l.k_line_id)
3225 and exists (select 'x' from okc_assents a
3226 where a.opn_code = 'CREATE_DELV'
3227 and a.sts_code = l.status_code
3228 and a.scs_code = 'PROJECT'
3229 and a.allowed_yn = 'Y');
3230
3231 cursor l_csr2(p_id NUMBER) is
3232 select l.k_line_id,
3233 l.line_number,
3234 l.project_id,
3235 l.inventory_item_id,
3236 l.line_description,
3237 l.delivery_date,
3238 l.status_code,
3239 l.start_date,
3240 l.end_date,
3241 k.priority_code,
3242 h.currency_code,
3243 l.unit_price,
3244 l.uom_code,
3245 l.line_quantity,
3246 k.country_of_origin_code,
3247 l.subcontracted_flag,
3248 l.billable_flag,
3249 l.drop_shipped_flag,
3250 l.completed_flag,
3251 l.shippable_flag,
3252 l.cfe_flag,
3253 l.inspection_req_flag,
3254 l.interim_rpt_req_flag,
3255 l.customer_approval_req_flag,
3256 l.as_of_date,
3257 l.date_of_first_submission,
3258 l.frequency,
3259 l.data_item_subtitle,
3260 l.copies_required,
3261 l.cdrl_category,
3262 l.data_item_name,
3263 l.export_flag
3264 from oke_k_lines_v l, okc_k_headers_b h, oke_k_headers k
3265 where h.id = p_id
3266 and l.header_id = p_id
3267 and h.id = k.k_header_id
3268 and not exists (select 'x' from okc_k_lines_b s where s.cle_id = l.k_line_id)
3269 and not exists (select 'x' from oke_k_deliverables_b where k_line_id = l.k_line_id)
3270 and exists (select 'x' from okc_assents a
3271 where a.opn_code = 'CREATE_DELV'
3272 and a.sts_code = l.status_code
3273 and a.scs_code = 'PROJECT'
3274 and a.allowed_yn = 'Y');
3275
3276 cursor item_csr(p_id number) is
3277 select object1_id2
3278 from okc_k_items_v
3279 where cle_id = p_id;
3280
3281 cursor cust_csr(p_id Number) is
3282 select cust_account_id from oke_cust_site_uses_v
3283 where id1 = p_id;
3284
3285
3286
3287
3288 l_csr_rec l_csr%ROWTYPE;
3289 l_csr2_rec l_csr2%ROWTYPE;
3290 l_id number;
3291 i NUMBER;
3292 l_inventory_org_id NUMBER;
3293 l_ship_to_id number;
3294 l_ship_from_id number;
3295
3296 PROCEDURE get_org(p_header_id number,
3297 p_line_id number,
3298 x_ship_to_id OUT NOCOPY NUMBER,
3299 x_ship_from_id OUT NOCOPY NUMBER) IS
3300
3301 cursor party_csr1(p_id Number,p_code varchar2) is
3302 select object1_id1, object1_id2, jtot_object1_code
3303 from okc_k_party_roles_b
3304 where dnz_chr_id = p_header_id and cle_id = p_id
3305 and rle_code = p_code;
3306
3307 cursor party_csr2(p_id Number,p_code varchar2) is
3308 select object1_id1, object1_id2, jtot_object1_code
3309 from okc_k_party_roles_b
3310 where dnz_chr_id = p_id and chr_id = p_id
3311 and rle_code = p_code;
3312
3313 cursor line_party(p_code Varchar2) is
3314 select Max(a.level_sequence) from okc_ancestrys a
3315 where a.cle_id = p_line_id
3316 and exists(select 'x' from okc_k_party_roles_b b where b.cle_id = a.cle_id_ascendant and b.rle_code = p_code and object1_id1 is not null);
3317
3318 cursor header_party(p_code Varchar2) is
3319 select count(*) from okc_k_party_roles_b
3320 where dnz_chr_id = p_header_id and chr_id = p_header_id
3321 and rle_code = p_code
3322 and object1_id1 is not null;
3323
3324 cursor c is
3325 select buy_or_sell from okc_k_headers_b
3326 where id = p_header_id;
3327
3328 cursor top_line is
3329 select 'x' from okc_ancestrys
3330 where cle_id = p_line_id;
3331
3332 Cursor Inv_C(P_Id Number) Is
3333 Select 'x'
3334 From HR_ALL_ORGANIZATION_UNITS hr, MTL_PARAMETERS mp
3335 Where hr.Organization_Id = P_Id
3336 And mp.Organization_Id = hr.Organization_Id;
3337
3338
3339 l_ship_to_id number;
3340 l_ship_from_id number;
3341 l_id1 varchar2(40);
3342 l_id2 varchar2(200);
3343 l_object_code varchar2(30);
3344 l_level Number;
3345 l_value Varchar2(1);
3346 l_found Boolean := TRUE;
3347
3348 c1info party_csr1%rowtype;
3349 c2info party_csr2%rowtype;
3350
3351 l_row_count number;
3352 l_buy_or_sell varchar2(1);
3353
3354 BEGIN
3355
3356 select buy_or_sell into l_buy_or_sell
3357 from okc_k_headers_b
3358 where id = p_header_id;
3359
3360
3361 IF p_line_id is not null then
3362
3363 SELECT COUNT(*) INTO l_row_count
3364 FROM OKC_K_PARTY_ROLES_B
3365 WHERE dnz_chr_id = p_header_id and cle_id = p_line_id
3366 and rle_code = 'SHIP_FROM'
3367 and object1_id1 is not null;
3368
3369 if l_row_count = 1 then
3370
3371 l_id := p_line_id;
3372
3373 open party_csr1(l_id,'SHIP_FROM');
3374 fetch party_csr1 into c1info;
3375 close party_csr1;
3376
3377 l_object_code := c1info.jtot_object1_code;
3378 if l_buy_or_sell = 'B' then
3379 if l_object_code = 'OKX_VENDOR' then
3380 l_id1 := c1info.object1_id1;
3381 end if;
3382 else
3383 if l_object_code = 'OKX_INVENTORY' then
3384 -- only inventory_org will be defaulted down to DTS
3385
3386 Open Inv_C(c1info.object1_Id1);
3387 Fetch Inv_C Into L_Value;
3388 Close Inv_C;
3389
3390 if l_value = 'x' then
3391 l_id1 := c1info.object1_id1;
3392 end if;
3393
3394 end if;
3395 end if;
3396
3397 elsif l_row_count = 0 then
3398
3399 -- if the line is top line, go directly to header, else search parent line
3400 open top_line;
3401 fetch top_line into l_value;
3402 l_found := top_line%found;
3403 close top_line;
3404
3405 if l_found then
3406 open line_party('SHIP_FROM');
3407 fetch line_party into l_level;
3408 l_found := line_party%found;
3409 close line_party;
3410
3411 end if;
3412
3413 if l_level is not null then
3414
3415 -- check parent line default
3416 select cle_id_ascendant into l_id
3417 from okc_ancestrys
3418 where cle_id = p_line_id
3419 and level_sequence = l_level;
3420
3421 select count(*) into l_row_count
3422 from okc_k_party_roles_b
3423 where dnz_chr_id = p_header_id and cle_id = l_id
3424 and rle_code = 'SHIP_FROM';
3425
3426 if l_row_count = 1 then
3427 open party_csr1(l_id, 'SHIP_FROM');
3428 fetch party_csr1 into c1info;
3429 close party_csr1;
3430 l_object_code := c1info.jtot_object1_code;
3431
3432
3433 if l_buy_or_sell = 'B' then
3434 if l_object_code = 'OKX_VENDOR' then
3435 l_id1 := c1info.object1_id1;
3436 end if;
3437 else
3438 if l_object_code = 'OKX_INVENTORY' then
3439 Open Inv_C(c1info.object1_id1);
3440 Fetch Inv_C Into L_Value;
3441 Close Inv_C;
3442 if l_value = 'x' then
3443 l_id1 := c1info.object1_id1;
3444 end if;
3445
3446 end if;
3447 end if;
3448 end if;
3449 else
3450
3451 -- check header party for default
3452 open header_party('SHIP_FROM');
3453 fetch header_party into l_level;
3454 l_found := header_party%found;
3455 close header_party;
3456
3457 if l_level > 0 then
3458 if l_level = 1 then
3459 open party_csr2(p_header_id, 'SHIP_FROM');
3460 fetch party_csr2 into c2info;
3461 close party_csr2;
3462 l_object_code := c2info.jtot_object1_code;
3463
3464
3465
3466 if l_buy_or_sell = 'B' then
3467 if l_object_code = 'OKX_VENDOR' then
3468 l_id1 := c2info.object1_id1;
3469 end if;
3470 else
3471 if l_object_code = 'OKX_INVENTORY' then
3472 -- only inventory_org will be defaulted down to DTS
3473 Open Inv_C(c2info.object1_id1);
3474 Fetch Inv_C Into L_Value;
3475 Close Inv_C;
3476
3477 if l_value = 'x' then
3478 l_id1 := c2info.object1_id1;
3479 end if;
3480
3481 end if;
3482 end if;
3483 end if;
3484
3485 end if;
3486 end if;
3487 end if;
3488 end if;
3489
3490
3491 if l_id1 is not null then
3492 l_ship_from_id := to_number(l_id1);
3493 l_id1 := null;
3494 end if;
3495
3496 select count(*) into l_row_count
3497 from okc_k_party_roles_b
3498 where dnz_chr_id = p_header_id and cle_id = p_line_id
3499 and rle_code = 'SHIP_TO';
3500
3501 if l_row_count = 1 then
3502 l_id := p_line_id;
3503
3504 open party_csr1(l_id,'SHIP_TO');
3505 fetch party_csr1 into c1info;
3506 close party_csr1;
3507
3508 l_object_code := c1info.jtot_object1_code;
3509 if l_buy_or_sell = 'S' then
3510 if l_object_code = 'OKE_SHIPTO' then
3511
3512 l_id1 := c1info.object1_id1;
3513 end if;
3514 else
3515 if l_object_code = 'OKX_INVENTORY' then
3516 -- only inventory_org will be defaulted down to DTS
3517 Open Inv_C(c1info.object1_id1);
3518 Fetch Inv_C Into L_Value;
3519 Close Inv_C;
3520
3521 if l_value = 'x' then
3522 l_id1 := c1info.object1_id1;
3523 end if;
3524
3525 end if;
3526 end if;
3527
3528 elsif l_row_count = 0 then
3529
3530 open line_party('SHIP_TO');
3531 fetch line_party into l_level;
3532 l_found := line_party%found;
3533 close line_party;
3534
3535 if l_level is not null then
3536
3537 -- check parent line default
3538 select cle_id_ascendant into l_id
3539 from okc_ancestrys
3540 where cle_id = p_line_id
3541 and level_sequence = l_level;
3542
3543 select count(*) into l_row_count
3544 from okc_k_party_roles_b
3545 where dnz_chr_id = p_header_id and cle_id = l_id
3546 and rle_code = 'SHIP_TO';
3547
3548 if l_row_count = 1 then
3549 open party_csr1(l_id, 'SHIP_TO');
3550 fetch party_csr1 into c1info;
3551 close party_csr1;
3552 l_object_code := c1info.jtot_object1_code;
3553 if l_buy_or_sell = 'S' then
3554 if l_object_code = 'OKE_SHIPTO' then
3555 l_id1 := c1info.object1_id1;
3556 end if;
3557 else
3558 if l_object_code = 'OKX_INVENTORY' then
3559 -- only inventory_org will be defaulted down to DTS
3560 Open Inv_C(c1info.object1_id1);
3561 Fetch Inv_C Into L_Value;
3562 Close Inv_C;
3563
3564 if l_value = 'x' then
3565 l_id1 := c1info.object1_id1;
3566 end if;
3567
3568 end if;
3569 end if;
3570 end if;
3571
3572 else
3573
3574 -- check header party for default
3575 open header_party('SHIP_TO');
3576 fetch header_party into l_level;
3577 l_found := header_party%found;
3578 close header_party;
3579
3580 if l_found then
3581
3582 if l_level = 1 then
3583 open party_csr2(p_header_id, 'SHIP_TO');
3584 fetch party_csr2 into c2info;
3585 close party_csr2;
3586
3587 l_object_code := c2info.jtot_object1_code;
3588 if l_buy_or_sell = 'S' then
3589 if l_object_code = 'OKE_SHIPTO' then
3590 l_id1 := c2info.object1_id1;
3591 end if;
3592 else
3593 if l_object_code = 'OKX_INVENTORY' then
3594 -- only inventory_org will be defaulted down to DTS
3595 Open Inv_C(c2info.object1_id1);
3596 Fetch Inv_C Into L_Value;
3597 Close Inv_C;
3598
3599 if l_value = 'x' then
3600 l_id1 := c2info.object1_id1;
3601 end if;
3602
3603 end if;
3604 end if;
3605
3606 end if;
3607
3608 end if;
3609
3610 end if;
3611
3612 end if;
3613
3614 if l_id1 is not null then
3615
3616 l_ship_to_id := to_number(l_id1);
3617 l_id1 := null;
3618 end if;
3619
3620 x_ship_to_id := l_ship_to_id;
3621 x_ship_from_id := l_ship_from_id;
3622
3623 END;
3624
3625
3626 BEGIN
3627 -- call START_ACTIVITY to create savepoint, check compatibility
3628 -- and initialize message list
3629
3630 l_return_status := OKE_API.START_ACTIVITY(
3631 p_api_name => l_api_name,
3632 p_pkg_name => g_pkg_name,
3633 p_init_msg_list => p_init_msg_list,
3634 l_api_version => l_api_version,
3635 p_api_version => p_api_version,
3636 p_api_type => g_api_type,
3637 x_return_status => x_return_status);
3638
3639 -- check if activity started successfully
3640 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
3641 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3642 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
3643 raise OKE_API.G_EXCEPTION_ERROR;
3644 End If;
3645
3646 i := 1;
3647
3648 -- default deliverable direction based on buy_or_sell
3649 open l_default_csr(p_header_id);
3650 fetch l_default_csr into l_buy_or_sell;
3651 close l_default_csr;
3652
3653 if l_buy_or_sell = 'B' then
3654 l_direction := 'IN';
3655 else
3656 l_direction := 'OUT';
3657 end if;
3658
3659 -- get inventory_org_id from header
3660 SELECT INV_ORGANIZATION_ID INTO l_inventory_org_id
3661 FROM OKC_K_HEADERS_B
3662 WHERE ID = P_HEADER_ID;
3663 IF p_first_default_flag = 'Y' then
3664
3665 for l_csr_rec in l_csr(p_header_id)
3666 loop
3667 -- get ship_from, ship_to from line/header
3668 get_org(p_header_id, l_csr_rec.k_line_id, l_ship_to_id, l_ship_from_id);
3669
3670
3671 if l_direction = 'IN' then
3672
3673 l_del_tbl(i).ship_to_org_id := l_ship_to_id;
3674
3675 else
3676 if l_buy_or_sell = 'S' then
3677 l_del_tbl(i).ship_to_location_id := l_ship_to_id;
3678 open cust_csr(l_ship_to_id);
3679 fetch cust_csr into l_org_id;
3680 close cust_csr;
3681
3682 l_del_tbl(i).ship_to_org_id := l_org_id;
3683 else
3684 l_del_tbl(i).ship_to_org_id := l_ship_to_id;
3685 end if;
3686
3687
3688 end if;
3689 l_del_tbl(i).ship_from_org_id := l_ship_from_id;
3690
3691 --
3692 -- default inventory org id if ship_to/ship_from org exists, bug # 1743406
3693 --
3694
3695 if l_direction = 'IN' then
3696 if l_del_tbl(i).ship_to_org_id is not null and l_del_tbl(i).ship_to_org_id <> oke_api.g_miss_num then
3697 l_del_tbl(i).inventory_org_id := l_del_tbl(i).ship_to_org_id;
3698 else
3699 l_del_tbl(i).inventory_org_id := l_inventory_org_id;
3700 end if;
3701 else
3702 if l_del_tbl(i).ship_from_org_id is not null and l_del_tbl(i).ship_from_org_id <> oke_api.g_miss_num then
3703 l_del_tbl(i).inventory_org_id := l_del_tbl(i).ship_from_org_id;
3704 else
3705 l_del_tbl(i).inventory_org_id := l_inventory_org_id;
3706 end if;
3707 end if;
3708
3709 -- l_del_tbl(i).inventory_org_id := l_inventory_org_id;
3710 l_del_tbl(i).k_line_id := l_csr_rec.k_line_id;
3711
3712 l_del_tbl(i).defaulted_flag := 'Y';
3713 l_del_tbl(i).direction := l_direction;
3714 l_del_tbl(i).k_header_id := p_header_id;
3715 l_del_tbl(i).deliverable_num := l_csr_rec.line_number;
3716 l_del_tbl(i).project_id := l_csr_rec.project_id;
3717 l_del_tbl(i).item_id := l_csr_rec.inventory_item_id;
3718 l_del_tbl(i).description := l_csr_rec.line_description;
3719 l_del_tbl(i).delivery_date := l_csr_rec.delivery_date;
3720 l_del_tbl(i).status_code := l_csr_rec.status_code;
3721 l_del_tbl(i).start_date := l_csr_rec.start_date;
3722 l_del_tbl(i).end_date := l_csr_rec.end_date;
3723 l_del_tbl(i).priority_code := l_csr_rec.priority_code;
3724 l_del_tbl(i).currency_code := l_csr_rec.currency_code;
3725 l_del_tbl(i).unit_price := l_csr_rec.unit_price;
3726 l_del_tbl(i).uom_code := l_csr_rec.uom_code;
3727 l_del_tbl(i).quantity := l_csr_rec.line_quantity;
3728 l_del_tbl(i).country_of_origin_code := l_csr_rec.country_of_origin_code;
3729
3730 l_del_tbl(i).subcontracted_flag := l_csr_rec.subcontracted_flag;
3731 l_del_tbl(i).billable_flag := l_csr_rec.billable_flag;
3732 l_del_tbl(i).drop_shipped_flag := l_csr_rec.drop_shipped_flag;
3733 l_del_tbl(i).completed_flag := l_csr_rec.completed_flag;
3734 l_del_tbl(i).shippable_flag := l_csr_rec.shippable_flag;
3735 l_del_tbl(i).cfe_req_flag := l_csr_rec.cfe_flag;
3736 l_del_tbl(i).inspection_req_flag := l_csr_rec.inspection_req_flag;
3737 l_del_tbl(i).interim_rpt_req_flag := l_csr_rec.interim_rpt_req_flag;
3738 l_del_tbl(i).customer_approval_req_flag := l_csr_rec.customer_approval_req_flag;
3739 l_del_tbl(i).as_of_date := l_csr_rec.as_of_date;
3740 l_del_tbl(i).date_of_first_submission := l_csr_rec.date_of_first_submission;
3741 l_del_tbl(i).frequency := l_csr_rec.frequency;
3742 l_del_tbl(i).data_item_subtitle := l_csr_rec.data_item_subtitle;
3743 l_del_tbl(i).total_num_of_copies := l_csr_rec.copies_required;
3744 l_del_tbl(i).cdrl_category := l_csr_rec.cdrl_category;
3745 l_del_tbl(i).data_item_name := l_csr_rec.data_item_name;
3746 l_del_tbl(i).export_flag := l_csr_rec.export_flag;
3747 /* if l_del_tbl(i).item_id is not null then
3748 open item_csr(l_csr_rec.k_line_id);
3749 fetch item_csr into l_id;
3750 close item_csr;
3751 l_del_tbl(i).inventory_org_id := l_id;
3752 end if; */
3753
3754
3755 i := i + 1;
3756
3757 end loop;
3758
3759 ELSE
3760 for l_csr2_rec in l_csr2(p_header_id)
3761 loop
3762 -- get ship_from, ship_to from line/header
3763 get_org(p_header_id, l_csr2_rec.k_line_id, l_ship_to_id, l_ship_from_id);
3764
3765 if l_direction = 'IN' then
3766
3767 l_del_tbl(i).ship_to_org_id := l_ship_to_id;
3768
3769 else
3770 if l_buy_or_sell = 'S' then
3771 l_del_tbl(i).ship_to_location_id := l_ship_to_id;
3772 open cust_csr(l_ship_to_id);
3773 fetch cust_csr into l_org_id;
3774 close cust_csr;
3775 l_del_tbl(i).ship_to_org_id := l_org_id;
3776 else
3777 l_del_tbl(i).ship_to_org_id := l_ship_to_id;
3778 end if;
3779 end if;
3780
3781 l_del_tbl(i).ship_from_org_id := l_ship_from_id;
3782
3783 --
3784 -- default inventory org id if ship_to/ship_from org exists, bug # 1743406
3785 --
3786 if l_direction = 'IN' then
3787 if l_del_tbl(i).ship_to_org_id is not null and l_del_tbl(i).ship_to_org_id <> oke_api.g_miss_num then
3788 l_del_tbl(i).inventory_org_id := l_del_tbl(i).ship_to_org_id;
3789 else
3790 l_del_tbl(i).inventory_org_id := l_inventory_org_id;
3791 end if;
3792 else
3793 if l_del_tbl(i).ship_from_org_id is not null and l_del_tbl(i).ship_from_org_id <> oke_api.g_miss_num then
3794
3795 l_del_tbl(i).inventory_org_id := l_del_tbl(i).ship_from_org_id;
3796 else
3797
3798 l_del_tbl(i).inventory_org_id := l_inventory_org_id;
3799 end if;
3800 end if;
3801
3802 -- l_del_tbl(i).inventory_org_id := l_inventory_org_id;
3803 l_del_tbl(i).k_line_id := l_csr2_rec.k_line_id;
3804
3805 l_del_tbl(i).defaulted_flag := 'Y';
3806 l_del_tbl(i).direction := l_direction;
3807 l_del_tbl(i).k_header_id := p_header_id;
3808 l_del_tbl(i).deliverable_num := l_csr2_rec.line_number;
3809 l_del_tbl(i).project_id := l_csr2_rec.project_id;
3810 l_del_tbl(i).item_id := l_csr2_rec.inventory_item_id;
3811 l_del_tbl(i).description := l_csr2_rec.line_description;
3812 l_del_tbl(i).delivery_date := l_csr2_rec.delivery_date;
3813 l_del_tbl(i).status_code := l_csr2_rec.status_code;
3814 l_del_tbl(i).start_date := l_csr2_rec.start_date;
3815 l_del_tbl(i).end_date := l_csr2_rec.end_date;
3816 l_del_tbl(i).priority_code := l_csr2_rec.priority_code;
3817 l_del_tbl(i).currency_code := l_csr2_rec.currency_code;
3818 l_del_tbl(i).unit_price := l_csr2_rec.unit_price;
3819 l_del_tbl(i).uom_code := l_csr2_rec.uom_code;
3820 l_del_tbl(i).quantity := l_csr2_rec.line_quantity;
3821 l_del_tbl(i).country_of_origin_code := l_csr2_rec.country_of_origin_code;
3822 l_del_tbl(i).subcontracted_flag := l_csr2_rec.subcontracted_flag;
3823 l_del_tbl(i).billable_flag := l_csr2_rec.billable_flag;
3824 l_del_tbl(i).drop_shipped_flag := l_csr2_rec.drop_shipped_flag;
3825 l_del_tbl(i).completed_flag := l_csr2_rec.completed_flag;
3826 l_del_tbl(i).shippable_flag := l_csr2_rec.shippable_flag;
3827 l_del_tbl(i).cfe_req_flag := l_csr2_rec.cfe_flag;
3828 l_del_tbl(i).inspection_req_flag := l_csr2_rec.inspection_req_flag;
3829 l_del_tbl(i).interim_rpt_req_flag := l_csr2_rec.interim_rpt_req_flag;
3830 l_del_tbl(i).customer_approval_req_flag := l_csr2_rec.customer_approval_req_flag;
3831 l_del_tbl(i).as_of_date := l_csr2_rec.as_of_date;
3832 l_del_tbl(i).date_of_first_submission := l_csr2_rec.date_of_first_submission;
3833 l_del_tbl(i).frequency := l_csr2_rec.frequency;
3834 l_del_tbl(i).data_item_subtitle := l_csr2_rec.data_item_subtitle;
3835 l_del_tbl(i).total_num_of_copies := l_csr2_rec.copies_required;
3836 l_del_tbl(i).cdrl_category := l_csr2_rec.cdrl_category;
3837 l_del_tbl(i).data_item_name := l_csr2_rec.data_item_name;
3838 l_del_tbl(i).export_flag := l_csr2_rec.export_flag;
3839 /* if l_del_tbl(i).item_id is not null then
3840 open item_csr(l_csr_rec.k_line_id);
3841 fetch item_csr into l_id;
3842 close item_csr;
3843 l_del_tbl(i).inventory_org_id := l_id;
3844 end if; */
3845
3846 i := i + 1;
3847
3848 end loop;
3849 END IF;
3850
3851
3852
3853 -- insert into deliverable table
3854 If (l_del_tbl.COUNT > 0) Then
3855
3856 -- call procedure in public API for a record
3857 OKE_CONTRACT_PUB.create_deliverable(
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_del_tbl => l_del_tbl,
3864 x_del_tbl => x_del_tbl);
3865
3866 If (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
3867 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3868 Elsif (x_return_status = OKE_API.G_RET_STS_ERROR) then
3869 raise OKE_API.G_EXCEPTION_ERROR;
3870 End If;
3871
3872 -- copy related entities to the deliverable
3873 commit;
3874
3875 end if;
3876 -- end activity
3877 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
3878 x_msg_data => x_msg_data);
3879 EXCEPTION
3880 when OKE_API.G_EXCEPTION_ERROR then
3881 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
3882 p_api_name => l_api_name,
3883 p_pkg_name => g_pkg_name,
3884 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
3885 x_msg_count => x_msg_count,
3886 x_msg_data => x_msg_data,
3887 p_api_type => g_api_type);
3888
3889 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
3890 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
3891 p_api_name => l_api_name,
3892 p_pkg_name => g_pkg_name,
3893 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
3894 x_msg_count => x_msg_count,
3895 x_msg_data => x_msg_data,
3896 p_api_type => g_api_type);
3897
3898 when OTHERS then
3899 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
3900 p_api_name => l_api_name,
3901 p_pkg_name => g_pkg_name,
3902 p_exc_name => 'OTHERS',
3903 x_msg_count => x_msg_count,
3904 x_msg_data => x_msg_data,
3905 p_api_type => g_api_type);
3906 end default_deliverable;
3907
3908 PROCEDURE Check_Delete_Contract(
3909 p_api_version IN NUMBER,
3910 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
3911 x_return_status OUT NOCOPY VARCHAR2,
3912 x_msg_count OUT NOCOPY NUMBER,
3913 x_msg_data OUT NOCOPY VARCHAR2,
3914 p_chr_id IN NUMBER,
3915 x_return_code OUT NOCOPY VARCHAR2) IS
3916
3917 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_DELETE_CONTRACT';
3918 l_api_version CONSTANT NUMBER := 1.0;
3919 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
3920 l_check VARCHAR2(1);
3921
3922
3923 CURSOR check_opns IS
3924 SELECT 'X' FROM OKC_ASSENTS
3925 WHERE OPN_CODE ='DELETE'
3926 AND STS_CODE =
3927 (SELECT STS_CODE FROM OKC_K_HEADERS_B WHERE ID = P_CHR_ID)
3928 AND SCS_CODE = 'PROJECT'
3929 AND ALLOWED_YN = 'Y';
3930
3931 CURSOR check_boa IS
3932 SELECT 'x'
3933 FROM OKE_K_HEADERS
3934 WHERE K_HEADER_ID = p_chr_id
3935 AND K_TYPE_CODE IN (SELECT K_TYPE_CODE FROM OKE_K_TYPES_B WHERE TYPE_CLASS_CODE='BOA')
3936 AND K_HEADER_ID IN (SELECT BOA_ID FROM OKE_K_HEADERS);
3937
3938 CURSOR check_sts IS
3939 SELECT 'x'
3940 FROM OKC_STATUSES_B ST, OKC_K_HEADERS_B KH
3941 WHERE KH.ID = p_chr_id
3942 AND ST.CODE = KH.STS_CODE
3943 AND ST.STE_CODE not in ( 'ENTERED','CANCELLED');
3944
3945 CURSOR check_po IS
3946 SELECT 'x'
3947 FROM OKE_K_DELIVERABLES_B DV
3948 WHERE PO_REF_1 IS NOT NULL
3949 AND DV.K_HEADER_ID = p_chr_id;
3950
3951 CURSOR check_mps IS
3952 SELECT 'x'
3953 FROM OKE_K_DELIVERABLES_B DV
3954 WHERE MPS_TRANSACTION_ID IS NOT NULL
3955 AND DV.K_HEADER_ID = p_chr_id;
3956
3957 CURSOR check_wsh IS
3958 SELECT 'x'
3959 FROM OKE_K_DELIVERABLES_B DV
3960 WHERE SHIPPING_REQUEST_ID IS NOT NULL
3961 AND DV.K_HEADER_ID = p_chr_id;
3962
3963 CURSOR check_funding IS
3964 SELECT 'x'
3965 FROM OKE_K_FUND_ALLOCATIONS FA, OKE_K_FUNDING_SOURCES FS
3966 WHERE FA.PA_FLAG = 'Y'
3967 AND FA.FUNDING_SOURCE_ID = FS.FUNDING_SOURCE_ID
3968 AND FS.OBJECT_ID = p_chr_id;
3969
3970
3971
3972 BEGIN
3973 -- call START_ACTIVITY to create savepoint, check compatibility
3974 -- and initialize message list
3975 l_return_status := OKE_API.START_ACTIVITY(
3976 p_api_name => l_api_name,
3977 p_pkg_name => g_pkg_name,
3978 p_init_msg_list => p_init_msg_list,
3979 l_api_version => l_api_version,
3980 p_api_version => p_api_version,
3981 p_api_type => g_api_type,
3982 x_return_status => x_return_status);
3983
3984 -- check if activity started successfully
3985 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
3986 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3987 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
3988 raise OKE_API.G_EXCEPTION_ERROR;
3989 End If;
3990 l_check := '?';
3991 OPEN check_sts;
3992 FETCH check_sts INTO l_check;
3993 CLOSE check_sts;
3994
3995 IF l_check = 'x' THEN
3996 X_Return_Status := OKE_API.G_RET_STS_ERROR;
3997 x_return_code := 'STS';
3998 raise OKE_API.G_EXCEPTION_ERROR;
3999 END IF;
4000
4001 l_check := '?';
4002 OPEN check_opns;
4003 FETCH check_opns INTO l_check;
4004 CLOSE check_opns;
4005
4006 IF l_check = '?' THEN
4007 X_Return_Status := OKE_API.G_RET_STS_ERROR;
4008 x_return_code := 'OPN';
4009 raise OKE_API.G_EXCEPTION_ERROR;
4010 END IF;
4011
4012 l_check := '?';
4013 OPEN check_boa;
4014 FETCH check_boa INTO l_check;
4015 CLOSE check_boa;
4016
4017 IF l_check = 'x' THEN
4018 X_Return_Status := OKE_API.G_RET_STS_ERROR;
4019 x_return_code := 'BOA';
4020 raise OKE_API.G_EXCEPTION_ERROR;
4021 END IF;
4022
4023 l_check := '?';
4024 OPEN check_po;
4025 FETCH check_po INTO l_check;
4026 CLOSE check_po;
4027
4028 IF l_check = 'x' THEN
4029 X_Return_Status := OKE_API.G_RET_STS_ERROR;
4030 x_return_code := 'PO';
4031 raise OKE_API.G_EXCEPTION_ERROR;
4032 END IF;
4033
4034 l_check := '?';
4035 OPEN check_mps;
4036 FETCH check_mps INTO l_check;
4037 CLOSE check_mps;
4038
4039 IF l_check = 'x' THEN
4040 X_Return_Status := OKE_API.G_RET_STS_ERROR;
4041 x_return_code := 'PLAN';
4042 raise OKE_API.G_EXCEPTION_ERROR;
4043 END IF;
4044
4045 l_check := '?';
4046 OPEN check_wsh;
4047 FETCH check_wsh INTO l_check;
4048 CLOSE check_wsh;
4049 IF l_check = 'x' THEN
4050 X_Return_Status := OKE_API.G_RET_STS_ERROR;
4051 x_return_code := 'SHIP';
4052 raise OKE_API.G_EXCEPTION_ERROR;
4053 END IF;
4054
4055 l_check := '?';
4056 OPEN check_funding;
4057 FETCH check_funding INTO l_check;
4058 CLOSE check_funding;
4059
4060 IF l_check = 'x' THEN
4061 X_Return_Status := OKE_API.G_RET_STS_ERROR;
4062 x_return_code := 'FUND';
4063 raise OKE_API.G_EXCEPTION_ERROR;
4064 END IF;
4065
4066 If OKC_CONTRACT_PVT.Is_Process_Active(p_chr_id)='Y' then
4067 X_Return_Status := OKE_API.G_RET_STS_ERROR;
4068 x_return_code := 'WFA';
4069 raise OKE_API.G_EXCEPTION_ERROR;
4070 end if;
4071 x_return_status := l_return_status;
4072
4073 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
4074 x_msg_data => x_msg_data);
4075 EXCEPTION
4076 when OKE_API.G_EXCEPTION_ERROR then
4077 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
4078 p_api_name => l_api_name,
4079 p_pkg_name => g_pkg_name,
4080 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
4081 x_msg_count => x_msg_count,
4082 x_msg_data => x_msg_data,
4083 p_api_type => g_api_type);
4084
4085 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
4086 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
4087 p_api_name => l_api_name,
4088 p_pkg_name => g_pkg_name,
4089 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
4090 x_msg_count => x_msg_count,
4091 x_msg_data => x_msg_data,
4092 p_api_type => g_api_type);
4093
4094 when OTHERS then
4095 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
4096 p_api_name => l_api_name,
4097 p_pkg_name => g_pkg_name,
4098 p_exc_name => 'OTHERS',
4099 x_msg_count => x_msg_count,
4100 x_msg_data => x_msg_data,
4101 p_api_type => g_api_type);
4102
4103 END Check_Delete_Contract;
4104
4105
4106
4107
4108
4109 PROCEDURE delete_contract (
4110 p_api_version IN NUMBER,
4111 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
4112 x_return_status OUT NOCOPY VARCHAR2,
4113 x_msg_count OUT NOCOPY NUMBER,
4114 x_msg_data OUT NOCOPY VARCHAR2,
4115 p_chr_id IN NUMBER,
4116 p_pre_deletion_check_yn IN VARCHAR2 DEFAULT 'Y') IS
4117
4118
4119 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_CONTRACT';
4120 l_api_version CONSTANT NUMBER := 1.0;
4121 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
4122 l_status VARCHAR2(1);
4123 l_check VARCHAR2(1);
4124 i number;
4125
4126 l_return_code VARCHAR2(30);
4127
4128 l_note_tbl oke_note_pvt.note_tbl_type;
4129 l_form_tbl oke_form_pvt.form_tbl_type;
4130 l_term_tbl oke_term_pvt.term_tbl_type;
4131 l_article_rec okc_k_article_pub.catv_rec_type;
4132
4133 l_contact_tbl okc_contract_party_pub.ctcv_tbl_type;
4134 l_party_tbl okc_contract_party_pub.cplv_tbl_type;
4135 l_deliverable_tbl OKE_DELIVERABLE_PVT.del_tbl_type;
4136
4137
4138 l_item_tbl OKC_CONTRACT_ITEM_PUB.cimv_tbl_type;
4139 l_item_out_tbl OKC_CONTRACT_ITEM_PUB.cimv_tbl_type;
4140
4141 l_cle_tbl OKE_CLE_PVT.cle_tbl_type;
4142 l_chr_rec OKE_CHR_PVT.chr_rec_type;
4143 l_clev_tbl OKC_CONTRACT_PVT.clev_tbl_type;
4144 l_chrv_rec OKC_CONTRACT_PVT.chrv_rec_type;
4145
4146
4147 l_doc_id NUMBER;
4148 l_doc_type VARCHAR2(30);
4149
4150 cursor c_cle is
4151 select id from okc_k_lines_b
4152 where dnz_chr_id = p_chr_id;
4153
4154 cursor c_item is
4155 select id
4156 from okc_k_items
4157 where dnz_chr_id = p_chr_id;
4158
4159 cursor c_note is
4160 select standard_notes_id from oke_k_standard_notes_b
4161 where k_header_id = p_chr_id;
4162
4163
4164 cursor c_form is
4165 select print_form_code,k_header_id,k_line_id
4166 from oke_k_print_forms
4167 where k_header_id = p_chr_id;
4168
4169 cursor c_article is
4170 select id,object_version_number from okc_k_articles_b
4171 where dnz_chr_id = p_chr_id;
4172
4173 cursor c_term is
4174 select term_code,k_header_id,k_line_id, term_value_pk1, term_value_pk2
4175 from oke_k_terms
4176 where k_header_id = p_chr_id;
4177
4178 cursor c_contact is
4179 select id from okc_contacts
4180 where dnz_chr_id = p_chr_id;
4181
4182 cursor c_party is
4183 select id from okc_k_party_roles_b
4184 where dnz_chr_id = p_chr_id;
4185
4186 cursor c_alloc is
4187 select fund_allocation_id
4188 from oke_k_fund_allocations
4189 where object_id = p_chr_id;
4190
4191
4192 cursor c_fund is
4193 select funding_source_id
4194 from oke_k_funding_sources
4195 where object_id = p_chr_id;
4196
4197 cursor c_source is
4198 select funding_source_id
4199 from oke_k_funding_sources
4200 where object_id = p_chr_id
4201 and object_type = 'OKE_K_HEADER';
4202
4203 cursor c_del is
4204 select deliverable_id
4205 from oke_k_deliverables_b
4206 where k_header_id = p_chr_id;
4207
4208
4209 BEGIN
4210 -- call START_ACTIVITY to create savepoint, check compatibility
4211 -- and initialize message list
4212 l_return_status := OKE_API.START_ACTIVITY(
4213 p_api_name => l_api_name,
4214 p_pkg_name => g_pkg_name,
4215 p_init_msg_list => p_init_msg_list,
4216 l_api_version => l_api_version,
4217 p_api_version => p_api_version,
4218 p_api_type => g_api_type,
4219 x_return_status => x_return_status);
4220
4221 -- check if activity started successfully
4222 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
4223 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4224 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
4225 raise OKE_API.G_EXCEPTION_ERROR;
4226 End If;
4227
4228 IF p_pre_deletion_check_yn = 'Y' THEN
4229 Check_Delete_Contract(
4230 p_api_version => l_api_version,
4231 p_init_msg_list => p_init_msg_list,
4232 x_return_status => l_check,
4233 x_msg_count => x_msg_count,
4234 x_msg_data => x_msg_data,
4235 p_chr_id => p_chr_id,
4236 x_return_code => l_return_code );
4237
4238 If (l_check = OKE_API.G_RET_STS_UNEXP_ERROR) then
4239 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4240 Elsif (l_check = OKE_API.G_RET_STS_ERROR) then
4241 raise OKE_API.G_EXCEPTION_ERROR;
4242 End If;
4243
4244 END IF;
4245
4246
4247 i:=1;
4248 for c in c_note
4249 loop
4250 l_note_tbl(i).standard_notes_id := c.standard_notes_id;
4251 i:= i+1;
4252 end loop;
4253 OKE_STANDARD_NOTES_PUB.delete_standard_note(
4254 p_api_version => l_api_version,
4255 p_init_msg_list => OKE_API.G_FALSE,
4256 x_return_status => l_status,
4257 x_msg_count => x_msg_count,
4258 x_msg_data => x_msg_data,
4259 p_note_tbl => l_note_tbl);
4260
4261 If (l_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
4262 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4263 Elsif (l_status = OKE_API.G_RET_STS_ERROR) then
4264 raise OKE_API.G_EXCEPTION_ERROR;
4265 End If;
4266
4267
4268
4269 i:=1;
4270 for c in c_form
4271 loop
4272 l_form_tbl(i).print_form_code := c.print_form_code;
4273 l_form_tbl(i).k_header_id := c.k_header_id;
4274 l_form_tbl(i).k_line_id := c.k_line_id;
4275 i:= i+1;
4276 end loop;
4277
4278 OKE_K_PRINT_FORMS_PUB.delete_print_form(
4279 p_api_version => l_api_version,
4280 p_init_msg_list => OKE_API.G_FALSE,
4281 x_return_status => l_status,
4282 x_msg_count => x_msg_count,
4283 x_msg_data => x_msg_data,
4284 p_form_tbl => l_form_tbl);
4285
4286 If (l_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
4287 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4288 Elsif (l_status = OKE_API.G_RET_STS_ERROR) then
4289 raise OKE_API.G_EXCEPTION_ERROR;
4290 End If;
4291
4292
4293
4294
4295 OKC_TERMS_UTIL_GRP.Get_contract_document_type_id(
4296 p_api_version => 1,
4297 p_init_msg_list => FND_API.G_FALSE,
4298 x_return_status => l_status,
4299 x_msg_count => x_msg_count,
4300 x_msg_data => x_msg_data,
4301 p_chr_id => p_chr_id,
4302 x_doc_id => l_doc_id,
4303 x_doc_type => l_doc_type);
4304
4305
4306 If (l_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
4307 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4308 Elsif (l_status = OKE_API.G_RET_STS_ERROR) then
4309 raise OKE_API.G_EXCEPTION_ERROR;
4310 End If;
4311
4312
4313 OKC_TERMS_UTIL_GRP.delete_doc(
4314 p_api_version => 1,
4315 p_init_msg_list => FND_API.G_FALSE,
4316 p_commit => FND_API.G_FALSE,
4317 x_return_status => l_status,
4318 x_msg_data => x_msg_data,
4319 x_msg_count => x_msg_count,
4320 p_validate_commit => FND_API.G_FALSE,
4321 p_validation_string => NULL,
4322 p_doc_type => l_doc_type,
4323 p_doc_id => l_doc_id);
4324
4325
4326
4327
4328 If (l_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
4329 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4330 Elsif (l_status = OKE_API.G_RET_STS_ERROR) then
4331 raise OKE_API.G_EXCEPTION_ERROR;
4332 End If;
4333
4334
4335
4336
4337 i:=1;
4338 for c in c_term
4339 loop
4340 l_term_tbl(i).term_code := c.term_code;
4341 l_term_tbl(i).k_header_id := c.k_header_id;
4342 l_term_tbl(i).k_line_id := c.k_line_id;
4343 l_term_tbl(i).term_value_pk1 := c.term_value_pk1;
4344 l_term_tbl(i).term_value_pk2 := c.term_value_pk2;
4345 i:= i+1;
4346 end loop;
4347
4348 OKE_TERMS_PUB.delete_term(
4349 p_api_version => l_api_version,
4350 p_init_msg_list => OKE_API.G_FALSE,
4351 x_return_status => l_status,
4352 x_msg_count => x_msg_count,
4353 x_msg_data => x_msg_data,
4354 p_term_tbl => l_term_tbl);
4355
4356 If (l_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
4357 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4358 Elsif (l_status = OKE_API.G_RET_STS_ERROR) then
4359 raise OKE_API.G_EXCEPTION_ERROR;
4360 End If;
4361
4362
4363
4364
4365 i:=1;
4366 for c in c_contact
4367 loop
4368 l_contact_tbl(i).id := c.id;
4369 i:= i+1;
4370 end loop;
4371
4372 OKC_CONTRACT_PARTY_PUB.delete_contact(
4373 p_api_version => l_api_version,
4374 p_init_msg_list => OKC_API.G_FALSE,
4375 x_return_status => l_status,
4376 x_msg_count => x_msg_count,
4377 x_msg_data => x_msg_data,
4378 p_ctcv_tbl => l_contact_tbl);
4379
4380 If (l_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
4381 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4382 Elsif (l_status = OKE_API.G_RET_STS_ERROR) then
4383 raise OKE_API.G_EXCEPTION_ERROR;
4384 End If;
4385
4386
4387
4388
4389 i:=1;
4390 for c in c_party
4391 loop
4392 l_party_tbl(i).id := c.id;
4393 i:= i+1;
4394 end loop;
4395
4396 OKC_CONTRACT_PARTY_PUB.delete_k_party_role(
4397 p_api_version => l_api_version,
4398 p_init_msg_list => OKC_API.G_FALSE,
4399 x_return_status => l_status,
4400 x_msg_count => x_msg_count,
4401 x_msg_data => x_msg_data,
4402 p_cplv_tbl => l_party_tbl);
4403
4404 If (l_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
4405 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4406 Elsif (l_status = OKE_API.G_RET_STS_ERROR) then
4407 raise OKE_API.G_EXCEPTION_ERROR;
4408 End If;
4409
4410
4411
4412
4413 OKE_CONTRACT_PVT.delete_minor_entities (
4414 p_header_id => p_chr_id,
4415 x_return_status => l_status);
4416
4417 If (l_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
4418 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4419 Elsif (l_status = OKE_API.G_RET_STS_ERROR) then
4420 raise OKE_API.G_EXCEPTION_ERROR;
4421 End If;
4422
4423
4424
4425
4426
4427 for c in c_alloc
4428
4429 loop
4430 OKE_FUNDING_PUB.delete_allocation (
4431 p_api_version => l_api_version,
4432 p_init_msg_list => OKE_API.G_FALSE,
4433 p_commit => OKE_API.G_FALSE,
4434 x_return_status => l_status,
4435 x_msg_count => x_msg_count,
4436 x_msg_data => x_msg_data,
4437 p_fund_allocation_id => c.fund_allocation_id );
4438
4439 If (l_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
4440 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4441 Elsif (l_status = OKE_API.G_RET_STS_ERROR) then
4442 raise OKE_API.G_EXCEPTION_ERROR;
4443 End If;
4444
4445
4446 end loop;
4447
4448
4449 for c in c_fund
4450 loop
4451 OKE_FUNDING_PUB.delete_funding(
4452 p_api_version => l_api_version,
4453 p_init_msg_list => OKE_API.G_FALSE,
4454 p_commit => OKE_API.G_FALSE,
4455 x_return_status => l_status,
4456 x_msg_count => x_msg_count,
4457 x_msg_data => x_msg_data,
4458 p_funding_source_id => c.funding_source_id);
4459
4460
4461
4462
4463 If (l_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
4464 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4465 Elsif (l_status = OKE_API.G_RET_STS_ERROR) then
4466 raise OKE_API.G_EXCEPTION_ERROR;
4467 End If;
4468
4469
4470 end loop;
4471
4472
4473 i := 1;
4474 for c in c_del
4475 loop
4476 l_deliverable_tbl(i).deliverable_id := c.deliverable_id;
4477 i:= i+1;
4478 end loop;
4479
4480 OKE_DELIVERABLE_PVT.delete_row(
4481 p_api_version => l_api_version,
4482 p_init_msg_list => OKE_API.G_FALSE,
4483 x_return_status => l_status,
4484 x_msg_count => x_msg_count,
4485 x_msg_data => x_msg_data,
4486 p_del_tbl => l_deliverable_tbl);
4487
4488 If (l_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
4489 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4490 Elsif (l_status = OKE_API.G_RET_STS_ERROR) then
4491 raise OKE_API.G_EXCEPTION_ERROR;
4492 End If;
4493
4494
4495
4496 i:= 1;
4497 for c in c_item
4498 loop
4499 l_item_tbl(i).id := c.id;
4500 i:=i+1;
4501 end loop;
4502
4503 OKC_CONTRACT_ITEM_PUB.delete_contract_item(
4504 p_api_version => l_api_version,
4505 p_init_msg_list => OKC_API.G_FALSE,
4506 x_return_status => l_status,
4507 x_msg_count => x_msg_count,
4508 x_msg_data => x_msg_data,
4509 p_cimv_tbl => l_item_tbl);
4510
4511 If (l_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
4512 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4513 Elsif (l_status = OKE_API.G_RET_STS_ERROR) then
4514 raise OKE_API.G_EXCEPTION_ERROR;
4515 End If;
4516
4517
4518
4519
4520
4521 i:=1;
4522 for c in c_cle
4523 loop
4524 l_cle_tbl(i).k_line_id := c.id;
4525 l_clev_tbl(i).id := c.id;
4526 i:= i+1;
4527 end loop;
4528
4529
4530 OKE_CLE_PVT.delete_row(
4531 p_api_version => l_api_version,
4532 p_init_msg_list => OKE_API.G_FALSE,
4533 x_return_status => l_status,
4534 x_msg_count => x_msg_count,
4535 x_msg_data => x_msg_data,
4536 p_cle_tbl => l_cle_tbl);
4537
4538
4539 If (l_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
4540 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4541 Elsif (l_status = OKE_API.G_RET_STS_ERROR) then
4542 raise OKE_API.G_EXCEPTION_ERROR;
4543 End If;
4544
4545
4546
4547 OKC_CONTRACT_PVT.delete_contract_line(
4548 p_api_version => l_api_version,
4549 p_init_msg_list => OKC_API.G_FALSE,
4550 x_return_status => l_status,
4551 x_msg_count => x_msg_count,
4552 x_msg_data => x_msg_data,
4553 p_clev_tbl => l_clev_tbl);
4554
4555 If (l_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
4556 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4557 Elsif (l_status = OKE_API.G_RET_STS_ERROR) then
4558 raise OKE_API.G_EXCEPTION_ERROR;
4559 End If;
4560
4561
4562
4563 l_chr_rec.k_header_id := p_chr_id;
4564 l_chrv_rec.id := p_chr_id;
4565
4566
4567 OKE_CHR_PVT.delete_row(
4568 p_api_version => l_api_version,
4569 p_init_msg_list => OKE_API.G_FALSE,
4570 x_return_status => l_status,
4571 x_msg_count => x_msg_count,
4572 x_msg_data => x_msg_data,
4573 p_chr_rec => l_chr_rec);
4574
4575 If (l_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
4576 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4577 Elsif (l_status = OKE_API.G_RET_STS_ERROR) then
4578 raise OKE_API.G_EXCEPTION_ERROR;
4579 End If;
4580
4581
4582
4583 OKE_CONTRACT_PVT.delete_version_records (
4584 p_api_version => l_api_version,
4585 p_header_id => p_chr_id,
4586 x_return_status => l_status,
4587 x_msg_count => x_msg_count,
4588 x_msg_data => x_msg_data);
4589
4590
4591
4592 If (l_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
4593 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4594 Elsif (l_status = OKE_API.G_RET_STS_ERROR) then
4595 raise OKE_API.G_EXCEPTION_ERROR;
4596 End If;
4597
4598
4599 OKC_CONTRACT_PVT.delete_contract_header(
4600 p_api_version => l_api_version,
4601 p_init_msg_list => OKC_API.G_FALSE,
4602 x_return_status => l_status,
4603 x_msg_count => x_msg_count,
4604 x_msg_data => x_msg_data,
4605 p_chrv_rec => l_chrv_rec);
4606
4607
4608
4609 If (l_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
4610 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4611 Elsif (l_status = OKE_API.G_RET_STS_ERROR) then
4612 raise OKE_API.G_EXCEPTION_ERROR;
4613 End If;
4614
4615
4616
4617
4618 -- end activity
4619 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
4620 x_msg_data => x_msg_data);
4621 EXCEPTION
4622 when OKE_API.G_EXCEPTION_ERROR then
4623 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
4624 p_api_name => l_api_name,
4625 p_pkg_name => g_pkg_name,
4626 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
4627 x_msg_count => x_msg_count,
4628 x_msg_data => x_msg_data,
4629 p_api_type => g_api_type);
4630
4631 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
4632 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
4633 p_api_name => l_api_name,
4634 p_pkg_name => g_pkg_name,
4635 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
4636 x_msg_count => x_msg_count,
4637 x_msg_data => x_msg_data,
4638 p_api_type => g_api_type);
4639
4640 when OTHERS then
4641 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
4642 p_api_name => l_api_name,
4643 p_pkg_name => g_pkg_name,
4644 p_exc_name => 'OTHERS',
4645 x_msg_count => x_msg_count,
4646 x_msg_data => x_msg_data,
4647 p_api_type => g_api_type);
4648
4649 END Delete_Contract;
4650
4651
4652
4653
4654
4655 END OKE_CONTRACT_PUB;