[Home] [Help]
PACKAGE BODY: APPS.OKE_CONTRACT_PUB
Source
1 PACKAGE BODY OKE_CONTRACT_PUB AS
2 /* $Header: OKEPCCCB.pls 120.7 2010/04/09 05:27:53 aveeraba ship $ */
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 --bug 9354391 changed length of line_number to 150 as per table
1115 l_line_number VARCHAR2(150);
1116
1117 l_sts_code OKC_ASSENTS.STS_CODE%TYPE;
1118 l_scs_code OKC_ASSENTS.SCS_CODE%TYPE;
1119 l_return_value VARCHAR2(1):='?';
1120
1121 CURSOR c_assent IS
1122 SELECT allowed_yn
1123 FROM OKC_ASSENTS
1124 WHERE sts_code = l_sts_code
1125 AND scs_code = l_scs_code
1126 AND opn_code = 'UPDATE';
1127
1128 Cursor l_chrv_csr Is
1129 SELECT sts_code, scs_code
1130 FROM OKC_K_HEADERS_B
1131 WHERE id = p_clev_rec.dnz_chr_id;
1132
1133 BEGIN
1134 -- call START_ACTIVITY to create savepoint, check compatibility
1135 -- and initialize message list
1136 l_return_status := OKE_API.START_ACTIVITY(
1137 p_api_name => l_api_name,
1138 p_pkg_name => g_pkg_name,
1139 p_init_msg_list => p_init_msg_list,
1140 l_api_version => l_api_version,
1141 p_api_version => p_api_version,
1142 p_api_type => g_api_type,
1143 x_return_status => x_return_status);
1144
1145 -- check if activity started successfully
1146 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
1147 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1148 Elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
1149 raise OKE_API.G_EXCEPTION_ERROR;
1150
1151 End If;
1152
1153 l_cle_rec := p_cle_rec;
1154 l_clev_rec := p_clev_rec;
1155
1156 -- get original line number
1157
1158 l_line_number := p_clev_rec.line_number;
1159
1160 -- call procedure in complex API
1161
1162 -- removing p_restricted_update and adding circumvent around okc_assent
1163
1164 Open l_chrv_csr;
1165 Fetch l_chrv_csr Into l_sts_code, l_scs_code;
1166 If l_chrv_csr%FOUND Then
1167 Close l_chrv_csr;
1168
1169 Open C_assent;
1170 Fetch C_assent INTO L_return_value;
1171 Close C_assent;
1172
1173 If (l_return_value in ('N')) Then
1174 UPDATE OKC_ASSENTS SET ALLOWED_YN = 'Y'
1175 WHERE sts_code = l_sts_code
1176 AND scs_code = l_scs_code
1177 AND opn_code = 'UPDATE';
1178 End If;
1179 Else
1180 Close l_chrv_csr;
1181 End If;
1182
1183 OKC_CONTRACT_PUB.create_contract_line(
1184 p_api_version => p_api_version,
1185 p_init_msg_list => p_init_msg_list,
1186 x_return_status => x_return_status,
1187 x_msg_count => x_msg_count,
1188 x_msg_data => x_msg_data,
1189 -- p_restricted_update => p_restricted_update,
1190 p_clev_rec => l_clev_rec,
1191 x_clev_rec => x_clev_rec);
1192
1193
1194 If (l_return_value in ('N')) Then
1195 UPDATE OKC_ASSENTS SET ALLOWED_YN = l_return_value
1196 WHERE sts_code = l_sts_code
1197 AND scs_code = l_scs_code
1198 AND opn_code = 'UPDATE';
1199 End If;
1200
1201
1202 -- check return status
1203 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
1204 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1205 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
1206 raise OKC_API.G_EXCEPTION_ERROR;
1207 End If;
1208
1209 -- get id from OKC record
1210
1211 l_cle_rec.K_LINE_ID := x_clev_rec.ID;
1212
1213
1214 -- update the original line number instead of OKC generated line number
1215
1216 UPDATE OKC_K_LINES_B
1217 SET line_number = l_line_number
1218 WHERE ID = l_cle_rec.K_LINE_ID;
1219
1220 -- prepare the OUT NOCOPY /* file.sql.39 change */ okc rec to reflact the update
1221 -- x_clev_rec.line_number := l_line_number;
1222
1223 -- call procedure in complex API
1224 OKE_CONTRACT_PVT.create_contract_line(
1225 p_api_version => p_api_version,
1226 p_init_msg_list => p_init_msg_list,
1227 x_return_status => x_return_status,
1228 x_msg_count => x_msg_count,
1229 x_msg_data => x_msg_data,
1230 p_cle_rec => l_cle_rec,
1231 x_cle_rec => x_cle_rec);
1232
1233 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
1234 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1235 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
1236 raise OKE_API.G_EXCEPTION_ERROR;
1237 End If;
1238
1239
1240 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
1241 x_msg_data => x_msg_data);
1242 EXCEPTION
1243 when OKE_API.G_EXCEPTION_ERROR then
1244 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1245 p_api_name => l_api_name,
1246 p_pkg_name => g_pkg_name,
1247 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1248 x_msg_count => x_msg_count,
1249 x_msg_data => x_msg_data,
1250 p_api_type => g_api_type);
1251
1252 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
1253 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1254 p_api_name => l_api_name,
1255 p_pkg_name => g_pkg_name,
1256 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1257 x_msg_count => x_msg_count,
1258 x_msg_data => x_msg_data,
1259 p_api_type => g_api_type);
1260
1261 when OKC_API.G_EXCEPTION_ERROR then
1262 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1263 p_api_name => l_api_name,
1264 p_pkg_name => g_pkg_name,
1265 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1266 x_msg_count => x_msg_count,
1267 x_msg_data => x_msg_data,
1268 p_api_type => g_api_type);
1269
1270 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
1271 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1272 p_api_name => l_api_name,
1273 p_pkg_name => g_pkg_name,
1274 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1275 x_msg_count => x_msg_count,
1276 x_msg_data => x_msg_data,
1277 p_api_type => g_api_type);
1278
1279 when OTHERS then
1280 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1281 p_api_name => l_api_name,
1282 p_pkg_name => g_pkg_name,
1283 p_exc_name => 'OTHERS',
1284 x_msg_count => x_msg_count,
1285 x_msg_data => x_msg_data,
1286 p_api_type => g_api_type);
1287
1288 END create_contract_line;
1289
1290 PROCEDURE create_contract_line(
1291 p_api_version IN NUMBER,
1292 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
1293 x_return_status OUT NOCOPY VARCHAR2,
1294 x_msg_count OUT NOCOPY NUMBER,
1295 x_msg_data OUT NOCOPY VARCHAR2,
1296 p_restricted_update IN VARCHAR2 DEFAULT OKE_API.G_TRUE,
1297 p_cle_tbl IN oke_cle_pvt.cle_tbl_type,
1298 p_clev_tbl IN okc_contract_pub.clev_tbl_type,
1299 x_cle_tbl OUT NOCOPY oke_cle_pvt.cle_tbl_type,
1300 x_clev_tbl OUT NOCOPY okc_contract_pub.clev_tbl_type) IS
1301
1302 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_CONTRACT_LINE';
1303 l_api_version CONSTANT NUMBER := 1.0;
1304 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1305 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1306 i NUMBER;
1307 l_cle_tbl oke_cle_pvt.cle_tbl_type;
1308 BEGIN
1309
1310 -- call START_ACTIVITY to create savepoint, check compatibility
1311 -- and initialize message list
1312 l_return_status := OKE_API.START_ACTIVITY(
1313 p_api_name => l_api_name,
1314 p_pkg_name => g_pkg_name,
1315 p_init_msg_list => p_init_msg_list,
1316 l_api_version => l_api_version,
1317 p_api_version => p_api_version,
1318 p_api_type => g_api_type,
1319 x_return_status => x_return_status);
1320
1321 -- check if activity started successfully
1322 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
1323 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1324 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
1325 raise OKE_API.G_EXCEPTION_ERROR;
1326
1327 End If;
1328
1329 If (p_clev_tbl.COUNT > 0) Then
1330 i := p_clev_tbl.FIRST;
1331 LOOP
1332 -- call procedure in public API for a record
1333 OKE_CONTRACT_PUB.create_contract_line(
1334 p_api_version => p_api_version,
1335 p_init_msg_list => p_init_msg_list,
1336 x_return_status => x_return_status,
1337 x_msg_count => x_msg_count,
1338 x_msg_data => x_msg_data,
1339 p_restricted_update => p_restricted_update,
1340 p_cle_rec => p_cle_tbl(i),
1341 p_clev_rec => p_clev_tbl(i),
1342 x_cle_rec => x_cle_tbl(i),
1343 x_clev_rec => x_clev_tbl(i));
1344
1345 -- store the highest degree of error
1346 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
1347 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
1348 l_overall_status := x_return_status;
1349 End If;
1350 End If;
1351
1352 EXIT WHEN (i = p_clev_tbl.LAST);
1353 i := p_clev_tbl.NEXT(i);
1354 END LOOP;
1355
1356 -- return overall status
1357 x_return_status := l_overall_status;
1358 End If;
1359
1360 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
1361 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1362 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
1363 raise OKE_API.G_EXCEPTION_ERROR;
1364 End If;
1365
1366 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
1367 x_msg_data => x_msg_data);
1368 EXCEPTION
1369 when OKE_API.G_EXCEPTION_ERROR then
1370 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1371 p_api_name => l_api_name,
1372 p_pkg_name => g_pkg_name,
1373 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1374 x_msg_count => x_msg_count,
1375 x_msg_data => x_msg_data,
1376 p_api_type => g_api_type);
1377
1378 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
1379 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1380 p_api_name => l_api_name,
1381 p_pkg_name => g_pkg_name,
1382 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1383 x_msg_count => x_msg_count,
1384 x_msg_data => x_msg_data,
1385 p_api_type => g_api_type);
1386
1387 when OTHERS then
1388 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1389 p_api_name => l_api_name,
1390 p_pkg_name => g_pkg_name,
1391 p_exc_name => 'OTHERS',
1392 x_msg_count => x_msg_count,
1393 x_msg_data => x_msg_data,
1394 p_api_type => g_api_type);
1395
1396 END create_contract_line;
1397
1398 PROCEDURE update_contract_line(
1399 p_api_version IN NUMBER,
1400 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
1401 x_return_status OUT NOCOPY VARCHAR2,
1402 x_msg_count OUT NOCOPY NUMBER,
1403 x_msg_data OUT NOCOPY VARCHAR2,
1404 p_restricted_update IN VARCHAR2 DEFAULT OKE_API.G_TRUE,
1405 p_cle_rec IN oke_cle_pvt.cle_rec_type,
1406 p_clev_rec IN okc_contract_pub.clev_rec_type,
1407 x_cle_rec OUT NOCOPY oke_cle_pvt.cle_rec_type,
1408 x_clev_rec OUT NOCOPY okc_contract_pub.clev_rec_type) IS
1409
1410 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CONTRACT_LINE';
1411 l_api_version CONSTANT NUMBER := 1.0;
1412 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1413 BEGIN
1414 -- call START_ACTIVITY to create savepoint, check compatibility
1415 -- and initialize message list
1416
1417
1418 l_return_status := OKE_API.START_ACTIVITY(
1419 p_api_name => l_api_name,
1420 p_pkg_name => g_pkg_name,
1421 p_init_msg_list => p_init_msg_list,
1422 l_api_version => l_api_version,
1423 p_api_version => p_api_version,
1424 p_api_type => g_api_type,
1425 x_return_status => x_return_status);
1426
1427 -- check if activity started successfully
1428 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
1429 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1430 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
1431 raise OKE_API.G_EXCEPTION_ERROR;
1432 End If;
1433
1434 -- call procedure in complex API
1435 OKC_CONTRACT_PUB.update_contract_line(
1436 p_api_version => p_api_version,
1437 p_init_msg_list => p_init_msg_list,
1438 x_return_status => x_return_status,
1439 x_msg_count => x_msg_count,
1440 x_msg_data => x_msg_data,
1441 p_restricted_update => p_restricted_update,
1442 p_clev_rec => p_clev_rec,
1443 x_clev_rec => x_clev_rec);
1444
1445 -- check return status
1446 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
1447 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1448 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
1449 raise OKC_API.G_EXCEPTION_ERROR;
1450 End If;
1451
1452 -- call complex api
1453
1454 OKE_CONTRACT_PVT.update_contract_line(
1455 p_api_version => p_api_version,
1456 p_init_msg_list => p_init_msg_list,
1457 x_return_status => x_return_status,
1458 x_msg_count => x_msg_count,
1459 x_msg_data => x_msg_data,
1460 p_cle_rec => p_cle_rec,
1461 x_cle_rec => x_cle_rec);
1462
1463 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
1464 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1465 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
1466 raise OKE_API.G_EXCEPTION_ERROR;
1467 End If;
1468
1469 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
1470 x_msg_data => x_msg_data);
1471 EXCEPTION
1472 when OKE_API.G_EXCEPTION_ERROR then
1473 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1474 p_api_name => l_api_name,
1475 p_pkg_name => g_pkg_name,
1476 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1477 x_msg_count => x_msg_count,
1478 x_msg_data => x_msg_data,
1479 p_api_type => g_api_type);
1480
1481 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
1482 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1483 p_api_name => l_api_name,
1484 p_pkg_name => g_pkg_name,
1485 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1486 x_msg_count => x_msg_count,
1487 x_msg_data => x_msg_data,
1488 p_api_type => g_api_type);
1489
1490 when OKC_API.G_EXCEPTION_ERROR then
1491 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1492 p_api_name => l_api_name,
1493 p_pkg_name => g_pkg_name,
1494 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1495 x_msg_count => x_msg_count,
1496 x_msg_data => x_msg_data,
1497 p_api_type => g_api_type);
1498
1499 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
1500 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1501 p_api_name => l_api_name,
1502 p_pkg_name => g_pkg_name,
1503 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1504 x_msg_count => x_msg_count,
1505 x_msg_data => x_msg_data,
1506 p_api_type => g_api_type);
1507
1508 when OTHERS then
1509 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1510 p_api_name => l_api_name,
1511 p_pkg_name => g_pkg_name,
1512 p_exc_name => 'OTHERS',
1513 x_msg_count => x_msg_count,
1514 x_msg_data => x_msg_data,
1515 p_api_type => g_api_type);
1516
1517 END update_contract_line;
1518
1519 PROCEDURE update_contract_line(
1520 p_api_version IN NUMBER,
1521 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
1522 x_return_status OUT NOCOPY VARCHAR2,
1523 x_msg_count OUT NOCOPY NUMBER,
1524 x_msg_data OUT NOCOPY VARCHAR2,
1525 p_restricted_update IN VARCHAR2 DEFAULT OKE_API.G_TRUE,
1526 p_cle_tbl IN oke_cle_pvt.cle_tbl_type,
1527 p_clev_tbl IN okc_contract_pub.clev_tbl_type,
1528 x_cle_tbl OUT NOCOPY oke_cle_pvt.cle_tbl_type,
1529 x_clev_tbl OUT NOCOPY okc_contract_pub.clev_tbl_type) IS
1530
1531 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CONTRACT_HEADER';
1532 l_api_version CONSTANT NUMBER := 1.0;
1533 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1534 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1535 i NUMBER;
1536 BEGIN
1537 -- call START_ACTIVITY to create savepoint, check compatibility
1538 -- and initialize message list
1539
1540 l_return_status := OKE_API.START_ACTIVITY(
1541 p_api_name => l_api_name,
1542 p_pkg_name => g_pkg_name,
1543 p_init_msg_list => p_init_msg_list,
1544 l_api_version => l_api_version,
1545 p_api_version => p_api_version,
1546 p_api_type => g_api_type,
1547 x_return_status => x_return_status);
1548
1549 -- check if activity started successfully
1550 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
1551 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1552 Elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
1553 raise OKE_API.G_EXCEPTION_ERROR;
1554
1555 End If;
1556
1557 If (p_clev_tbl.COUNT > 0) Then
1558 i := p_clev_tbl.FIRST;
1559 LOOP
1560 -- call procedure in public API for a record
1561 OKE_CONTRACT_PUB.update_contract_line(
1562 p_api_version => p_api_version,
1563 p_init_msg_list => p_init_msg_list,
1564 x_return_status => x_return_status,
1565 x_msg_count => x_msg_count,
1566 x_msg_data => x_msg_data,
1567 p_restricted_update => p_restricted_update,
1568 p_cle_rec => p_cle_tbl(i),
1569 p_clev_rec => p_clev_tbl(i),
1570 x_cle_rec => x_cle_tbl(i),
1571 x_clev_rec => x_clev_tbl(i));
1572
1573 -- store the highest degree of error
1574 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
1575 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
1576 l_overall_status := x_return_status;
1577 End If;
1578 End If;
1579
1580 EXIT WHEN (i = p_clev_tbl.LAST);
1581 i := p_clev_tbl.NEXT(i);
1582 END LOOP;
1583
1584 -- return overall status
1585 x_return_status := l_overall_status;
1586 End If;
1587
1588 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
1589 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1590 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
1591 raise OKE_API.G_EXCEPTION_ERROR;
1592 End If;
1593
1594 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
1595 x_msg_data => x_msg_data);
1596 EXCEPTION
1597 when OKE_API.G_EXCEPTION_ERROR then
1598 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1599 p_api_name => l_api_name,
1600 p_pkg_name => g_pkg_name,
1601 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1602 x_msg_count => x_msg_count,
1603 x_msg_data => x_msg_data,
1604 p_api_type => g_api_type);
1605
1606 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
1607 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1608 p_api_name => l_api_name,
1609 p_pkg_name => g_pkg_name,
1610 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1611 x_msg_count => x_msg_count,
1612 x_msg_data => x_msg_data,
1613 p_api_type => g_api_type);
1614
1615 when OTHERS then
1616 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1617 p_api_name => l_api_name,
1618 p_pkg_name => g_pkg_name,
1619 p_exc_name => 'OTHERS',
1620 x_msg_count => x_msg_count,
1621 x_msg_data => x_msg_data,
1622 p_api_type => g_api_type);
1623
1624 END update_contract_line;
1625
1626 PROCEDURE delete_contract_line(
1627
1628 p_api_version IN NUMBER,
1629 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
1630 x_return_status OUT NOCOPY VARCHAR2,
1631 x_msg_count OUT NOCOPY NUMBER,
1632 x_msg_data OUT NOCOPY VARCHAR2,
1633 p_cle_rec IN oke_cle_pvt.cle_rec_type,
1634 p_clev_rec IN okc_contract_pub.clev_rec_type) IS
1635
1636 l_cle_rec oke_cle_pvt.cle_rec_type;
1637 l_clev_rec okc_contract_pub.clev_rec_type;
1638 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_CONTRACT_LINE';
1639 l_api_version CONSTANT NUMBER := 1.0;
1640 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1641 BEGIN
1642 -- call START_ACTIVITY to create savepoint, check compatibility
1643 -- and initialize message list
1644 l_return_status := OKE_API.START_ACTIVITY(
1645 p_api_name => l_api_name,
1646 p_pkg_name => g_pkg_name,
1647 p_init_msg_list => p_init_msg_list,
1648 l_api_version => l_api_version,
1649 p_api_version => p_api_version,
1650 p_api_type => g_api_type,
1651 x_return_status => x_return_status);
1652
1653 -- check if activity started successfully
1654 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
1655 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1656 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
1657 raise OKE_API.G_EXCEPTION_ERROR;
1658 End If;
1659
1660 l_cle_rec := p_cle_rec;
1661 l_clev_rec := p_clev_rec;
1662
1663 -- call complex api
1664
1665 OKE_CONTRACT_PVT.delete_contract_line(
1666 p_api_version => p_api_version,
1667 p_init_msg_list => p_init_msg_list,
1668 x_return_status => x_return_status,
1669 x_msg_count => x_msg_count,
1670 x_msg_data => x_msg_data,
1671 p_cle_rec => l_cle_rec);
1672
1673 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
1674 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1675 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
1676 raise OKE_API.G_EXCEPTION_ERROR;
1677 End If;
1678
1679 -- call procedure in complex API
1680 OKC_CONTRACT_PUB.delete_contract_line(
1681 p_api_version => p_api_version,
1682 p_init_msg_list => p_init_msg_list,
1683 x_return_status => x_return_status,
1684 x_msg_count => x_msg_count,
1685 x_msg_data => x_msg_data,
1686 p_clev_rec => l_clev_rec);
1687
1688 -- check return status
1689 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
1690 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1691 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
1692 raise OKC_API.G_EXCEPTION_ERROR;
1693 End If;
1694
1695 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
1696 x_msg_data => x_msg_data);
1697 EXCEPTION
1698 when OKE_API.G_EXCEPTION_ERROR then
1699 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1700 p_api_name => l_api_name,
1701 p_pkg_name => g_pkg_name,
1702 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1703 x_msg_count => x_msg_count,
1704 x_msg_data => x_msg_data,
1705 p_api_type => g_api_type);
1706
1707 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
1708 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1709 p_api_name => l_api_name,
1710 p_pkg_name => g_pkg_name,
1711 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1712 x_msg_count => x_msg_count,
1713 x_msg_data => x_msg_data,
1714 p_api_type => g_api_type);
1715
1716 when OKC_API.G_EXCEPTION_ERROR then
1717 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1718 p_api_name => l_api_name,
1719 p_pkg_name => g_pkg_name,
1720 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1721 x_msg_count => x_msg_count,
1722 x_msg_data => x_msg_data,
1723 p_api_type => g_api_type);
1724
1725 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
1726 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1727 p_api_name => l_api_name,
1728 p_pkg_name => g_pkg_name,
1729 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1730 x_msg_count => x_msg_count,
1731 x_msg_data => x_msg_data,
1732 p_api_type => g_api_type);
1733
1734 when OTHERS then
1735 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1736 p_api_name => l_api_name,
1737 p_pkg_name => g_pkg_name,
1738 p_exc_name => 'OTHERS',
1739 x_msg_count => x_msg_count,
1740 x_msg_data => x_msg_data,
1741 p_api_type => g_api_type);
1742
1743 END delete_contract_line;
1744
1745 PROCEDURE delete_contract_line(
1746 p_api_version IN NUMBER,
1747 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
1748 x_return_status OUT NOCOPY VARCHAR2,
1749 x_msg_count OUT NOCOPY NUMBER,
1750 x_msg_data OUT NOCOPY VARCHAR2,
1751 p_cle_tbl IN oke_cle_pvt.cle_tbl_type,
1752 p_clev_tbl IN okc_contract_pub.clev_tbl_type) IS
1753
1754 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_CONTRACT_HEADER';
1755 l_api_version CONSTANT NUMBER := 1.0;
1756 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1757 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1758 i NUMBER;
1759 BEGIN
1760 -- call START_ACTIVITY to create savepoint, check compatibility
1761 -- and initialize message list
1762 l_return_status := OKE_API.START_ACTIVITY(
1763 p_api_name => l_api_name,
1764 p_pkg_name => g_pkg_name,
1765 p_init_msg_list => p_init_msg_list,
1766 l_api_version => l_api_version,
1767 p_api_version => p_api_version,
1768 p_api_type => g_api_type,
1769 x_return_status => x_return_status);
1770
1771 -- check if activity started successfully
1772 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
1773 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1774 Elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
1775 raise OKE_API.G_EXCEPTION_ERROR;
1776
1777 End If;
1778
1779 If (p_clev_tbl.COUNT > 0) Then
1780 i := p_clev_tbl.FIRST;
1781 LOOP
1782
1783 -- call complex API
1784
1785 OKE_CONTRACT_PVT.delete_contract_line(
1786
1787 p_api_version => p_api_version,
1788 p_init_msg_list => p_init_msg_list,
1789 x_return_status => x_return_status,
1790 x_msg_count => x_msg_count,
1791 x_msg_data => x_msg_data,
1792 p_cle_rec => p_cle_tbl(i));
1793
1794 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
1795 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
1796 l_overall_status := x_return_status;
1797 End If;
1798 End If;
1799
1800 -- call procedure in public API for a record
1801 OKC_CONTRACT_PUB.delete_contract_line(
1802 p_api_version => p_api_version,
1803 p_init_msg_list => p_init_msg_list,
1804 x_return_status => x_return_status,
1805 x_msg_count => x_msg_count,
1806 x_msg_data => x_msg_data,
1807 p_clev_rec => p_clev_tbl(i));
1808
1809 -- store the highest degree of error
1810 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
1811 If l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
1812 l_overall_status := x_return_status;
1813 End If;
1814 End If;
1815
1816 EXIT WHEN (i = p_clev_tbl.LAST);
1817 i := p_clev_tbl.NEXT(i);
1818 END LOOP;
1819
1820 -- return overall status
1821 x_return_status := l_overall_status;
1822 End If;
1823
1824 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
1825 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1826 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
1827 raise OKE_API.G_EXCEPTION_ERROR;
1828 End If;
1829
1830 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
1831 x_msg_data => x_msg_data);
1832 EXCEPTION
1833 when OKE_API.G_EXCEPTION_ERROR then
1834 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1835 p_api_name => l_api_name,
1836 p_pkg_name => g_pkg_name,
1837 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1838 x_msg_count => x_msg_count,
1839 x_msg_data => x_msg_data,
1840 p_api_type => g_api_type);
1841
1842 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
1843 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1844 p_api_name => l_api_name,
1845 p_pkg_name => g_pkg_name,
1846 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1847 x_msg_count => x_msg_count,
1848 x_msg_data => x_msg_data,
1849 p_api_type => g_api_type);
1850
1851 when OKC_API.G_EXCEPTION_ERROR then
1852 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1853 p_api_name => l_api_name,
1854 p_pkg_name => g_pkg_name,
1855 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1856 x_msg_count => x_msg_count,
1857 x_msg_data => x_msg_data,
1858 p_api_type => g_api_type);
1859
1860 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
1861 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1862 p_api_name => l_api_name,
1863 p_pkg_name => g_pkg_name,
1864 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1865 x_msg_count => x_msg_count,
1866 x_msg_data => x_msg_data,
1867 p_api_type => g_api_type);
1868
1869 when OTHERS then
1870 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1871 p_api_name => l_api_name,
1872 p_pkg_name => g_pkg_name,
1873 p_exc_name => 'OTHERS',
1874 x_msg_count => x_msg_count,
1875 x_msg_data => x_msg_data,
1876 p_api_type => g_api_type);
1877
1878 END delete_contract_line;
1879
1880 PROCEDURE delete_contract_line(
1881 p_api_version IN NUMBER,
1882 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
1883 x_return_status OUT NOCOPY VARCHAR2,
1884 x_msg_count OUT NOCOPY NUMBER,
1885 x_msg_data OUT NOCOPY VARCHAR2,
1886 p_line_id IN NUMBER) IS
1887 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_CONTRACT_LINE';
1888
1889 BEGIN
1890
1891 OKE_CONTRACT_PVT.delete_contract_line(
1892 p_api_version => p_api_version,
1893 p_init_msg_list => p_init_msg_list,
1894 x_return_status => x_return_status,
1895 x_msg_count => x_msg_count,
1896 x_msg_data => x_msg_data,
1897 p_line_id => p_line_id);
1898
1899 OKC_CONTRACT_PVT.delete_contract_line(
1900 p_api_version => p_api_version,
1901 p_init_msg_list => p_init_msg_list,
1902 x_return_status => x_return_status,
1903 x_msg_count => x_msg_count,
1904 x_msg_data => x_msg_data,
1905 p_line_id => p_line_id);
1906
1907 EXCEPTION
1908 when OKE_API.G_EXCEPTION_ERROR then
1909 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1910 p_api_name => l_api_name,
1911 p_pkg_name => g_pkg_name,
1912 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1913 x_msg_count => x_msg_count,
1914 x_msg_data => x_msg_data,
1915 p_api_type => g_api_type);
1916
1917 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
1918 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1919 p_api_name => l_api_name,
1920 p_pkg_name => g_pkg_name,
1921 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1922 x_msg_count => x_msg_count,
1923 x_msg_data => x_msg_data,
1924 p_api_type => g_api_type);
1925
1926 when OKC_API.G_EXCEPTION_ERROR then
1927 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1928 p_api_name => l_api_name,
1929 p_pkg_name => g_pkg_name,
1930 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1931 x_msg_count => x_msg_count,
1932 x_msg_data => x_msg_data,
1933 p_api_type => g_api_type);
1934
1935 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
1936 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1937 p_api_name => l_api_name,
1938 p_pkg_name => g_pkg_name,
1939 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1940 x_msg_count => x_msg_count,
1941 x_msg_data => x_msg_data,
1942 p_api_type => g_api_type);
1943
1944 when OTHERS then
1945 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
1946 p_api_name => l_api_name,
1947 p_pkg_name => g_pkg_name,
1948 p_exc_name => 'OTHERS',
1949 x_msg_count => x_msg_count,
1950 x_msg_data => x_msg_data,
1951 p_api_type => g_api_type);
1952
1953 END delete_contract_line;
1954
1955 PROCEDURE validate_contract_line(
1956
1957 p_api_version IN NUMBER,
1958 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
1959 x_return_status OUT NOCOPY VARCHAR2,
1960 x_msg_count OUT NOCOPY NUMBER,
1961 x_msg_data OUT NOCOPY VARCHAR2,
1962 p_cle_rec IN oke_cle_pvt.cle_rec_type,
1963 p_clev_rec IN okc_contract_pub.clev_rec_type) IS
1964
1965 l_cle_rec oke_cle_pvt.cle_rec_type;
1966 l_clev_rec okc_contract_pub.clev_rec_type;
1967 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_CONTRACT_LINE';
1968 l_api_version CONSTANT NUMBER := 1.0;
1969 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
1970
1971 BEGIN
1972 -- call START_ACTIVITY to create savepoint, check compatibility
1973 -- and initialize message list
1974 l_return_status := OKE_API.START_ACTIVITY(
1975 p_api_name => l_api_name,
1976 p_pkg_name => g_pkg_name,
1977 p_init_msg_list => p_init_msg_list,
1978 l_api_version => l_api_version,
1979 p_api_version => p_api_version,
1980 p_api_type => g_api_type,
1981 x_return_status => x_return_status);
1982
1983 -- check if activity started successfully
1984 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
1985 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
1986 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
1987 raise OKE_API.G_EXCEPTION_ERROR;
1988 End If;
1989
1990 -- call BEFORE user hook
1991 l_cle_rec := p_cle_rec;
1992 l_clev_rec := p_clev_rec;
1993
1994 -- call procedure in complex API
1995 OKC_CONTRACT_PUB.validate_contract_line(
1996 p_api_version => p_api_version,
1997 p_init_msg_list => p_init_msg_list,
1998 x_return_status => x_return_status,
1999 x_msg_count => x_msg_count,
2000 x_msg_data => x_msg_data,
2001 p_clev_rec => l_clev_rec);
2002
2003 -- check return status
2004 If x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR Then
2005 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2006 Elsif x_return_status = OKC_API.G_RET_STS_ERROR Then
2007 raise OKC_API.G_EXCEPTION_ERROR;
2008 End If;
2009
2010 -- get id for OKE record
2011
2012 l_cle_rec.K_LINE_ID := l_clev_rec.ID;
2013
2014 -- call complex API
2015 OKE_CONTRACT_PVT.validate_contract_line(
2016 p_api_version => p_api_version,
2017 p_init_msg_list => p_init_msg_list,
2018 x_return_status => x_return_status,
2019 x_msg_count => x_msg_count,
2020 x_msg_data => x_msg_data,
2021 p_cle_rec => l_cle_rec);
2022
2023 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
2024 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2025 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
2026 raise OKE_API.G_EXCEPTION_ERROR;
2027 End If;
2028
2029 -- end activity
2030 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
2031 x_msg_data => x_msg_data);
2032 EXCEPTION
2033 when OKE_API.G_EXCEPTION_ERROR then
2034 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2035 p_api_name => l_api_name,
2036 p_pkg_name => g_pkg_name,
2037 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
2038 x_msg_count => x_msg_count,
2039 x_msg_data => x_msg_data,
2040 p_api_type => g_api_type);
2041
2042 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
2043 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2044 p_api_name => l_api_name,
2045 p_pkg_name => g_pkg_name,
2046 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
2047 x_msg_count => x_msg_count,
2048 x_msg_data => x_msg_data,
2049 p_api_type => g_api_type);
2050
2051 when OKC_API.G_EXCEPTION_ERROR then
2052 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2053 p_api_name => l_api_name,
2054 p_pkg_name => g_pkg_name,
2055 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
2056 x_msg_count => x_msg_count,
2057 x_msg_data => x_msg_data,
2058 p_api_type => g_api_type);
2059
2060 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
2061 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2062 p_api_name => l_api_name,
2063 p_pkg_name => g_pkg_name,
2064 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
2065 x_msg_count => x_msg_count,
2066 x_msg_data => x_msg_data,
2067 p_api_type => g_api_type);
2068
2069 when OTHERS then
2070 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2071 p_api_name => l_api_name,
2072 p_pkg_name => g_pkg_name,
2073 p_exc_name => 'OTHERS',
2074 x_msg_count => x_msg_count,
2075 x_msg_data => x_msg_data,
2076 p_api_type => g_api_type);
2077
2078 END validate_contract_line;
2079
2080 PROCEDURE validate_contract_line(
2081 p_api_version IN NUMBER,
2082 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
2083 x_return_status OUT NOCOPY VARCHAR2,
2084 x_msg_count OUT NOCOPY NUMBER,
2085 x_msg_data OUT NOCOPY VARCHAR2,
2086 p_cle_tbl IN oke_cle_pvt.cle_tbl_type,
2087 p_clev_tbl IN okc_contract_pub.clev_tbl_type) IS
2088
2089 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_CONTRACT_LINE';
2090
2091 l_api_version CONSTANT NUMBER := 1.0;
2092 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2093 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2094 i NUMBER;
2095 l_cle_tbl oke_cle_pvt.cle_tbl_type := p_cle_tbl;
2096 BEGIN
2097 -- call START_ACTIVITY to create savepoint, check compatibility
2098 -- and initialize message list
2099 l_return_status := OKE_API.START_ACTIVITY(
2100 p_api_name => l_api_name,
2101 p_pkg_name => g_pkg_name,
2102 p_init_msg_list => p_init_msg_list,
2103 l_api_version => l_api_version,
2104 p_api_version => p_api_version,
2105 p_api_type => g_api_type,
2106 x_return_status => x_return_status);
2107
2108 -- check if activity started successfully
2109 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
2110 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2111 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
2112 raise OKE_API.G_EXCEPTION_ERROR;
2113 End If;
2114
2115 If (p_clev_tbl.COUNT > 0) Then
2116 i := p_clev_tbl.FIRST;
2117 LOOP
2118 -- call procedure in complex API
2119 OKC_CONTRACT_PUB.validate_contract_line(
2120 p_api_version => p_api_version,
2121 p_init_msg_list => p_init_msg_list,
2122 x_return_status => x_return_status,
2123 x_msg_count => x_msg_count,
2124 x_msg_data => x_msg_data,
2125 p_clev_rec => p_clev_tbl(i));
2126
2127 -- store the highest degree of error
2128 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
2129 If l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
2130 l_overall_status := x_return_status;
2131 End If;
2132 End If;
2133
2134 l_cle_tbl(i).K_LINE_ID := p_clev_tbl(i).ID;
2135
2136 -- call complex API
2137 OKE_CONTRACT_PVT.validate_contract_line(
2138 p_api_version => p_api_version,
2139 p_init_msg_list => p_init_msg_list,
2140 x_return_status => x_return_status,
2141 x_msg_count => x_msg_count,
2142 x_msg_data => x_msg_data,
2143 p_cle_rec => p_cle_tbl(i));
2144
2145 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
2146 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
2147 l_overall_status := x_return_status;
2148 End If;
2149 End If;
2150
2151 EXIT WHEN (i = p_clev_tbl.LAST);
2152 i := p_clev_tbl.NEXT(i);
2153 END LOOP;
2154
2155 -- return overall status
2156 x_return_status := l_overall_status;
2157 End If;
2158
2159 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
2160 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2161 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
2162 raise OKE_API.G_EXCEPTION_ERROR;
2163 End If;
2164
2165 -- end activity
2166 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
2167 x_msg_data => x_msg_data);
2168 EXCEPTION
2169 when OKE_API.G_EXCEPTION_ERROR then
2170 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2171 p_api_name => l_api_name,
2172 p_pkg_name => g_pkg_name,
2173 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
2174 x_msg_count => x_msg_count,
2175 x_msg_data => x_msg_data,
2176 p_api_type => g_api_type);
2177
2178 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
2179 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2180 p_api_name => l_api_name,
2181 p_pkg_name => g_pkg_name,
2182 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
2183 x_msg_count => x_msg_count,
2184 x_msg_data => x_msg_data,
2185 p_api_type => g_api_type);
2186
2187 when OKC_API.G_EXCEPTION_ERROR then
2188 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2189 p_api_name => l_api_name,
2190 p_pkg_name => g_pkg_name,
2191 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
2192 x_msg_count => x_msg_count,
2193 x_msg_data => x_msg_data,
2194 p_api_type => g_api_type);
2195
2196 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
2197 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2198 p_api_name => l_api_name,
2199 p_pkg_name => g_pkg_name,
2200 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
2201 x_msg_count => x_msg_count,
2202 x_msg_data => x_msg_data,
2203 p_api_type => g_api_type);
2204
2205 when OTHERS then
2206 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2207 p_api_name => l_api_name,
2208 p_pkg_name => g_pkg_name,
2209 p_exc_name => 'OTHERS',
2210 x_msg_count => x_msg_count,
2211 x_msg_data => x_msg_data,
2212 p_api_type => g_api_type);
2213
2214 END validate_contract_line;
2215
2216 -----------------------------------------------------------------------------
2217 -- deliverable section
2218 -----------------------------------------------------------------------------
2219 PROCEDURE create_deliverable(
2220 p_api_version IN NUMBER,
2221 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
2222 x_return_status OUT NOCOPY VARCHAR2,
2223 x_msg_count OUT NOCOPY NUMBER,
2224 x_msg_data OUT NOCOPY VARCHAR2,
2225 p_del_rec IN oke_deliverable_pvt.del_rec_type,
2226
2227 x_del_rec OUT NOCOPY oke_deliverable_pvt.del_rec_type) IS
2228
2229 l_del_rec oke_deliverable_pvt.del_rec_type;
2230
2231 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_DELIVERABLE';
2232 l_api_version CONSTANT NUMBER := 1.0;
2233 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2234 --bug 9354391 changed length of line_number to 150 as per table
2235 l_line_number VARCHAR2(150);
2236 BEGIN
2237 -- call START_ACTIVITY to create savepoint, check compatibility
2238 -- and initialize message list
2239 l_return_status := OKE_API.START_ACTIVITY(
2240 p_api_name => l_api_name,
2241 p_pkg_name => g_pkg_name,
2242 p_init_msg_list => p_init_msg_list,
2243 l_api_version => l_api_version,
2244 p_api_version => p_api_version,
2245 p_api_type => g_api_type,
2246 x_return_status => x_return_status);
2247
2248 -- check if activity started successfully
2249 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
2250 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2251 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
2252 raise OKE_API.G_EXCEPTION_ERROR;
2253
2254 End If;
2255
2256 l_del_rec := p_del_rec;
2257
2258 -- call procedure in complex API
2259 OKE_CONTRACT_PVT.create_deliverable(
2260 p_api_version => p_api_version,
2261 p_init_msg_list => p_init_msg_list,
2262 x_return_status => x_return_status,
2263 x_msg_count => x_msg_count,
2264 x_msg_data => x_msg_data,
2265 p_del_rec => l_del_rec,
2266 x_del_rec => x_del_rec);
2267
2268 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
2269 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2270 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
2271 raise OKE_API.G_EXCEPTION_ERROR;
2272 End If;
2273
2274 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
2275 x_msg_data => x_msg_data);
2276 EXCEPTION
2277 when OKE_API.G_EXCEPTION_ERROR then
2278 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2279 p_api_name => l_api_name,
2280 p_pkg_name => g_pkg_name,
2281 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
2282 x_msg_count => x_msg_count,
2283 x_msg_data => x_msg_data,
2284 p_api_type => g_api_type);
2285
2286 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
2287 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2288 p_api_name => l_api_name,
2289 p_pkg_name => g_pkg_name,
2290 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
2291 x_msg_count => x_msg_count,
2292 x_msg_data => x_msg_data,
2293 p_api_type => g_api_type);
2294
2295 when OTHERS then
2296 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2297 p_api_name => l_api_name,
2298 p_pkg_name => g_pkg_name,
2299 p_exc_name => 'OTHERS',
2300 x_msg_count => x_msg_count,
2301 x_msg_data => x_msg_data,
2302 p_api_type => g_api_type);
2303
2304 END create_deliverable;
2305
2306 PROCEDURE create_deliverable(
2307 p_api_version IN NUMBER,
2308 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
2309 x_return_status OUT NOCOPY VARCHAR2,
2310 x_msg_count OUT NOCOPY NUMBER,
2311 x_msg_data OUT NOCOPY VARCHAR2,
2312 p_del_tbl IN oke_deliverable_pvt.del_tbl_type,
2313 x_del_tbl OUT NOCOPY oke_deliverable_pvt.del_tbl_type) IS
2314
2315 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_DELIVERABLE';
2316 l_api_version CONSTANT NUMBER := 1.0;
2317 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2318 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2319 i NUMBER;
2320 l_del_tbl oke_deliverable_pvt.del_tbl_type;
2321 BEGIN
2322 -- call START_ACTIVITY to create savepoint, check compatibility
2323 -- and initialize message list
2324 l_return_status := OKE_API.START_ACTIVITY(
2325 p_api_name => l_api_name,
2326 p_pkg_name => g_pkg_name,
2327 p_init_msg_list => p_init_msg_list,
2328 l_api_version => l_api_version,
2329 p_api_version => p_api_version,
2330 p_api_type => g_api_type,
2331 x_return_status => x_return_status);
2332
2333 -- check if activity started successfully
2334 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
2335 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2336 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
2337 raise OKE_API.G_EXCEPTION_ERROR;
2338
2339 End If;
2340
2341 If (p_del_tbl.COUNT > 0) Then
2342 i := p_del_tbl.FIRST;
2343 LOOP
2344 -- call procedure in public API for a record
2345 OKE_CONTRACT_PUB.create_deliverable(
2346 p_api_version => p_api_version,
2347 p_init_msg_list => p_init_msg_list,
2348 x_return_status => x_return_status,
2349 x_msg_count => x_msg_count,
2350 x_msg_data => x_msg_data,
2351 p_del_rec => p_del_tbl(i),
2352 x_del_rec => x_del_tbl(i));
2353
2354 -- store the highest degree of error
2355 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
2356 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
2357 l_overall_status := x_return_status;
2358 End If;
2359 End If;
2360
2361 EXIT WHEN (i = p_del_tbl.LAST);
2362 i := p_del_tbl.NEXT(i);
2363 END LOOP;
2364
2365 -- return overall status
2366 x_return_status := l_overall_status;
2367 End If;
2368
2369 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
2370 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2371 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
2372 raise OKE_API.G_EXCEPTION_ERROR;
2373 End If;
2374
2375 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
2376 x_msg_data => x_msg_data);
2377 EXCEPTION
2378 when OKE_API.G_EXCEPTION_ERROR then
2379 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2380 p_api_name => l_api_name,
2381 p_pkg_name => g_pkg_name,
2382 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
2383 x_msg_count => x_msg_count,
2384 x_msg_data => x_msg_data,
2385 p_api_type => g_api_type);
2386
2387 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
2388 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2389 p_api_name => l_api_name,
2390 p_pkg_name => g_pkg_name,
2391 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
2392 x_msg_count => x_msg_count,
2393 x_msg_data => x_msg_data,
2394 p_api_type => g_api_type);
2395
2396 when OTHERS then
2397 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2398 p_api_name => l_api_name,
2399 p_pkg_name => g_pkg_name,
2400 p_exc_name => 'OTHERS',
2401 x_msg_count => x_msg_count,
2402 x_msg_data => x_msg_data,
2403 p_api_type => g_api_type);
2404
2405 END create_deliverable;
2406
2407 PROCEDURE update_deliverable(
2408
2409 p_api_version IN NUMBER,
2410 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
2411 x_return_status OUT NOCOPY VARCHAR2,
2412 x_msg_count OUT NOCOPY NUMBER,
2413 x_msg_data OUT NOCOPY VARCHAR2,
2414 p_del_rec IN oke_deliverable_pvt.del_rec_type,
2415 x_del_rec OUT NOCOPY oke_deliverable_pvt.del_rec_type) IS
2416
2417 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_DELIVERABLE';
2418 l_api_version CONSTANT NUMBER := 1.0;
2419 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2420 BEGIN
2421 -- call START_ACTIVITY to create savepoint, check compatibility
2422 -- and initialize message list
2423
2424 l_return_status := OKE_API.START_ACTIVITY(
2425 p_api_name => l_api_name,
2426 p_pkg_name => g_pkg_name,
2427 p_init_msg_list => p_init_msg_list,
2428 l_api_version => l_api_version,
2429 p_api_version => p_api_version,
2430 p_api_type => g_api_type,
2431 x_return_status => x_return_status);
2432
2433 -- check if activity started successfully
2434 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
2435 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2436 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
2437 raise OKE_API.G_EXCEPTION_ERROR;
2438 End If;
2439
2440 -- call complex api
2441
2442 OKE_CONTRACT_PVT.update_deliverable(
2443 p_api_version => p_api_version,
2444 p_init_msg_list => p_init_msg_list,
2445 x_return_status => x_return_status,
2446 x_msg_count => x_msg_count,
2447 x_msg_data => x_msg_data,
2448 p_del_rec => p_del_rec,
2449 x_del_rec => x_del_rec);
2450
2451 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
2452 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2453 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
2454 raise OKE_API.G_EXCEPTION_ERROR;
2455 End If;
2456
2457 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
2458 x_msg_data => x_msg_data);
2459 EXCEPTION
2460 when OKE_API.G_EXCEPTION_ERROR then
2461 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2462 p_api_name => l_api_name,
2463 p_pkg_name => g_pkg_name,
2464 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
2465 x_msg_count => x_msg_count,
2466 x_msg_data => x_msg_data,
2467 p_api_type => g_api_type);
2468
2469 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
2470 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2471 p_api_name => l_api_name,
2472 p_pkg_name => g_pkg_name,
2473 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
2474 x_msg_count => x_msg_count,
2475 x_msg_data => x_msg_data,
2476 p_api_type => g_api_type);
2477
2478 when OTHERS then
2479 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2480 p_api_name => l_api_name,
2481 p_pkg_name => g_pkg_name,
2482 p_exc_name => 'OTHERS',
2483 x_msg_count => x_msg_count,
2484 x_msg_data => x_msg_data,
2485 p_api_type => g_api_type);
2486
2487 END update_deliverable;
2488
2489 PROCEDURE update_deliverable(
2490 p_api_version IN NUMBER,
2491 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
2492 x_return_status OUT NOCOPY VARCHAR2,
2493 x_msg_count OUT NOCOPY NUMBER,
2494 x_msg_data OUT NOCOPY VARCHAR2,
2495 p_del_tbl IN oke_deliverable_pvt.del_tbl_type,
2496 x_del_tbl OUT NOCOPY oke_deliverable_pvt.del_tbl_type) IS
2497
2498 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CONTRACT_HEADER';
2499 l_api_version CONSTANT NUMBER := 1.0;
2500 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2501 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2502 i NUMBER;
2503 BEGIN
2504 -- call START_ACTIVITY to create savepoint, check compatibility
2505 -- and initialize message list
2506
2507 l_return_status := OKE_API.START_ACTIVITY(
2508 p_api_name => l_api_name,
2509 p_pkg_name => g_pkg_name,
2510 p_init_msg_list => p_init_msg_list,
2511 l_api_version => l_api_version,
2512 p_api_version => p_api_version,
2513 p_api_type => g_api_type,
2514 x_return_status => x_return_status);
2515
2516 -- check if activity started successfully
2517 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
2518 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2519 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
2520 raise OKE_API.G_EXCEPTION_ERROR;
2521
2522 End If;
2523
2524 If (p_del_tbl.COUNT > 0) Then
2525 i := p_del_tbl.FIRST;
2526 LOOP
2527 -- call procedure in public API for a record
2528 OKE_CONTRACT_PUB.update_deliverable(
2529 p_api_version => p_api_version,
2530 p_init_msg_list => p_init_msg_list,
2531 x_return_status => x_return_status,
2532 x_msg_count => x_msg_count,
2533 x_msg_data => x_msg_data,
2534 p_del_rec => p_del_tbl(i),
2535 x_del_rec => x_del_tbl(i));
2536
2537 -- store the highest degree of error
2538 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
2539 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
2540 l_overall_status := x_return_status;
2541 End If;
2542 End If;
2543
2544 EXIT WHEN (i = p_del_tbl.LAST);
2545 i := p_del_tbl.NEXT(i);
2546 END LOOP;
2547
2548 -- return overall status
2549 x_return_status := l_overall_status;
2550 End If;
2551
2552 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
2553 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2554 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
2555 raise OKE_API.G_EXCEPTION_ERROR;
2556 End If;
2557
2558 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
2559 x_msg_data => x_msg_data);
2560 EXCEPTION
2561 when OKE_API.G_EXCEPTION_ERROR then
2562 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2563 p_api_name => l_api_name,
2564 p_pkg_name => g_pkg_name,
2565 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
2566 x_msg_count => x_msg_count,
2567 x_msg_data => x_msg_data,
2568 p_api_type => g_api_type);
2569
2570 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
2571 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2572 p_api_name => l_api_name,
2573 p_pkg_name => g_pkg_name,
2574 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
2575 x_msg_count => x_msg_count,
2576 x_msg_data => x_msg_data,
2577 p_api_type => g_api_type);
2578
2579 when OTHERS then
2580 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2581 p_api_name => l_api_name,
2582 p_pkg_name => g_pkg_name,
2583 p_exc_name => 'OTHERS',
2584 x_msg_count => x_msg_count,
2585 x_msg_data => x_msg_data,
2586 p_api_type => g_api_type);
2587
2588 END update_deliverable;
2589
2590 PROCEDURE delete_deliverable(
2591
2592 p_api_version IN NUMBER,
2593 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
2594 x_return_status OUT NOCOPY VARCHAR2,
2595 x_msg_count OUT NOCOPY NUMBER,
2596 x_msg_data OUT NOCOPY VARCHAR2,
2597 p_del_rec IN oke_deliverable_pvt.del_rec_type) IS
2598
2599 l_del_rec oke_deliverable_pvt.del_rec_type;
2600 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_DELIVERABLE';
2601 l_api_version CONSTANT NUMBER := 1.0;
2602 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2603 BEGIN
2604 -- call START_ACTIVITY to create savepoint, check compatibility
2605 -- and initialize message list
2606 l_return_status := OKE_API.START_ACTIVITY(
2607 p_api_name => l_api_name,
2608 p_pkg_name => g_pkg_name,
2609 p_init_msg_list => p_init_msg_list,
2610 l_api_version => l_api_version,
2611 p_api_version => p_api_version,
2612 p_api_type => g_api_type,
2613 x_return_status => x_return_status);
2614
2615 -- check if activity started successfully
2616 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
2617 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2618 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
2619 raise OKE_API.G_EXCEPTION_ERROR;
2620 End If;
2621
2622 l_del_rec := p_del_rec;
2623
2624 -- call procedure in complex API
2625 OKE_CONTRACT_PVT.delete_deliverable(
2626 p_api_version => p_api_version,
2627 p_init_msg_list => p_init_msg_list,
2628 x_return_status => x_return_status,
2629 x_msg_count => x_msg_count,
2630 x_msg_data => x_msg_data,
2631 p_del_rec => l_del_rec);
2632
2633 -- check return status
2634 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
2635 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2636 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
2637 raise OKE_API.G_EXCEPTION_ERROR;
2638 End If;
2639
2640 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
2641 x_msg_data => x_msg_data);
2642 EXCEPTION
2643 when OKE_API.G_EXCEPTION_ERROR then
2644 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2645 p_api_name => l_api_name,
2646 p_pkg_name => g_pkg_name,
2647 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
2648 x_msg_count => x_msg_count,
2649 x_msg_data => x_msg_data,
2650 p_api_type => g_api_type);
2651
2652 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
2653 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2654 p_api_name => l_api_name,
2655 p_pkg_name => g_pkg_name,
2656 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
2657 x_msg_count => x_msg_count,
2658 x_msg_data => x_msg_data,
2659 p_api_type => g_api_type);
2660
2661 when OTHERS then
2662 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2663 p_api_name => l_api_name,
2664 p_pkg_name => g_pkg_name,
2665 p_exc_name => 'OTHERS',
2666 x_msg_count => x_msg_count,
2667 x_msg_data => x_msg_data,
2668 p_api_type => g_api_type);
2669
2670 END delete_deliverable;
2671
2672 PROCEDURE delete_deliverable(
2673 p_api_version IN NUMBER,
2674 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
2675 x_return_status OUT NOCOPY VARCHAR2,
2676 x_msg_count OUT NOCOPY NUMBER,
2677 x_msg_data OUT NOCOPY VARCHAR2,
2678 p_del_tbl IN oke_deliverable_pvt.del_tbl_type) IS
2679
2680 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_CONTRACT_HEADER';
2681 l_api_version CONSTANT NUMBER := 1.0;
2682 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2683 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2684 i NUMBER;
2685 BEGIN
2686 -- call START_ACTIVITY to create savepoint, check compatibility
2687 -- and initialize message list
2688 l_return_status := OKE_API.START_ACTIVITY(
2689 p_api_name => l_api_name,
2690 p_pkg_name => g_pkg_name,
2691 p_init_msg_list => p_init_msg_list,
2692 l_api_version => l_api_version,
2693 p_api_version => p_api_version,
2694 p_api_type => g_api_type,
2695 x_return_status => x_return_status);
2696
2697 -- check if activity started successfully
2698 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
2699 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2700 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
2701 raise OKE_API.G_EXCEPTION_ERROR;
2702
2703 End If;
2704
2705 If (p_del_tbl.COUNT > 0) Then
2706 i := p_del_tbl.FIRST;
2707 LOOP
2708
2709 -- call procedure in public API for a record
2710 OKE_CONTRACT_PUB.delete_deliverable(
2711 p_api_version => p_api_version,
2712 p_init_msg_list => p_init_msg_list,
2713 x_return_status => x_return_status,
2714 x_msg_count => x_msg_count,
2715 x_msg_data => x_msg_data,
2716 p_del_rec => p_del_tbl(i));
2717
2718 -- store the highest degree of error
2719 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
2720 If l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
2721 l_overall_status := x_return_status;
2722 End If;
2723 End If;
2724
2725 EXIT WHEN (i = p_del_tbl.LAST);
2726 i := p_del_tbl.NEXT(i);
2727 END LOOP;
2728
2729 -- return overall status
2730 x_return_status := l_overall_status;
2731 End If;
2732
2733 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
2734 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2735 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
2736 raise OKE_API.G_EXCEPTION_ERROR;
2737 End If;
2738
2739 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
2740 x_msg_data => x_msg_data);
2741 EXCEPTION
2742 when OKE_API.G_EXCEPTION_ERROR then
2743 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2744 p_api_name => l_api_name,
2745 p_pkg_name => g_pkg_name,
2746 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
2747 x_msg_count => x_msg_count,
2748 x_msg_data => x_msg_data,
2749 p_api_type => g_api_type);
2750
2751 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
2752 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2753 p_api_name => l_api_name,
2754 p_pkg_name => g_pkg_name,
2755 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
2756 x_msg_count => x_msg_count,
2757 x_msg_data => x_msg_data,
2758 p_api_type => g_api_type);
2759
2760 when OTHERS then
2761 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2762 p_api_name => l_api_name,
2763 p_pkg_name => g_pkg_name,
2764 p_exc_name => 'OTHERS',
2765 x_msg_count => x_msg_count,
2766 x_msg_data => x_msg_data,
2767 p_api_type => g_api_type);
2768
2769 END delete_deliverable;
2770
2771 PROCEDURE delete_deliverable(
2772 p_api_version IN NUMBER,
2773 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
2774 x_return_status OUT NOCOPY VARCHAR2,
2775 x_msg_count OUT NOCOPY NUMBER,
2776 x_msg_data OUT NOCOPY VARCHAR2,
2777 p_deliverable_id IN NUMBER) IS
2778
2779 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_DELIVERABLE';
2780 BEGIN
2781
2782 OKE_CONTRACT_PVT.delete_deliverable(
2783 p_api_version => p_api_version,
2784 p_init_msg_list => p_init_msg_list,
2785 x_return_status => x_return_status,
2786 x_msg_count => x_msg_count,
2787 x_msg_data => x_msg_data,
2788 p_deliverable_id => p_deliverable_id);
2789
2790 EXCEPTION
2791 when OTHERS then
2792 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2793
2794 p_api_name => l_api_name,
2795 p_pkg_name => g_pkg_name,
2796 p_exc_name => 'OTHERS',
2797 x_msg_count => x_msg_count,
2798 x_msg_data => x_msg_data,
2799 p_api_type => g_api_type);
2800
2801 END delete_deliverable;
2802
2803 PROCEDURE validate_deliverable(
2804
2805 p_api_version IN NUMBER,
2806 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
2807 x_return_status OUT NOCOPY VARCHAR2,
2808 x_msg_count OUT NOCOPY NUMBER,
2809 x_msg_data OUT NOCOPY VARCHAR2,
2810 p_del_rec IN oke_deliverable_pvt.del_rec_type) IS
2811
2812 l_del_rec oke_deliverable_pvt.del_rec_type;
2813
2814 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_DELIVERABLE';
2815 l_api_version CONSTANT NUMBER := 1.0;
2816 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2817
2818 BEGIN
2819 -- call START_ACTIVITY to create savepoint, check compatibility
2820 -- and initialize message list
2821 l_return_status := OKE_API.START_ACTIVITY(
2822 p_api_name => l_api_name,
2823 p_pkg_name => g_pkg_name,
2824 p_init_msg_list => p_init_msg_list,
2825 l_api_version => l_api_version,
2826 p_api_version => p_api_version,
2827 p_api_type => g_api_type,
2828 x_return_status => x_return_status);
2829
2830 -- check if activity started successfully
2831 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
2832 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2833 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
2834 raise OKE_API.G_EXCEPTION_ERROR;
2835 End If;
2836
2837 l_del_rec := p_del_rec;
2838
2839 -- call complex API
2840 OKE_CONTRACT_PVT.validate_deliverable(
2841 p_api_version => p_api_version,
2842 p_init_msg_list => p_init_msg_list,
2843 x_return_status => x_return_status,
2844 x_msg_count => x_msg_count,
2845 x_msg_data => x_msg_data,
2846 p_del_rec => l_del_rec);
2847
2848 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
2849 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2850 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
2851 raise OKE_API.G_EXCEPTION_ERROR;
2852 End If;
2853
2854 -- end activity
2855 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
2856 x_msg_data => x_msg_data);
2857 EXCEPTION
2858 when OKE_API.G_EXCEPTION_ERROR then
2859 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2860 p_api_name => l_api_name,
2861 p_pkg_name => g_pkg_name,
2862 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
2863 x_msg_count => x_msg_count,
2864 x_msg_data => x_msg_data,
2865 p_api_type => g_api_type);
2866
2867 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
2868 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2869 p_api_name => l_api_name,
2870 p_pkg_name => g_pkg_name,
2871 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
2872 x_msg_count => x_msg_count,
2873 x_msg_data => x_msg_data,
2874 p_api_type => g_api_type);
2875
2876 when OTHERS then
2877 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2878 p_api_name => l_api_name,
2879 p_pkg_name => g_pkg_name,
2880 p_exc_name => 'OTHERS',
2881 x_msg_count => x_msg_count,
2882 x_msg_data => x_msg_data,
2883 p_api_type => g_api_type);
2884
2885 END validate_deliverable;
2886
2887 PROCEDURE validate_deliverable(
2888 p_api_version IN NUMBER,
2889 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
2890 x_return_status OUT NOCOPY VARCHAR2,
2891 x_msg_count OUT NOCOPY NUMBER,
2892 x_msg_data OUT NOCOPY VARCHAR2,
2893 p_del_tbl IN oke_deliverable_pvt.del_tbl_type) IS
2894
2895 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_DELIVERABLE';
2896
2897 l_api_version CONSTANT NUMBER := 1.0;
2898 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2899 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2900 i NUMBER;
2901 l_del_tbl oke_deliverable_pvt.del_tbl_type := p_del_tbl;
2902 BEGIN
2903 -- call START_ACTIVITY to create savepoint, check compatibility
2904 -- and initialize message list
2905 l_return_status := OKE_API.START_ACTIVITY(
2906 p_api_name => l_api_name,
2907 p_pkg_name => g_pkg_name,
2908 p_init_msg_list => p_init_msg_list,
2909 l_api_version => l_api_version,
2910 p_api_version => p_api_version,
2911 p_api_type => g_api_type,
2912 x_return_status => x_return_status);
2913
2914 -- check if activity started successfully
2915 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
2916 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2917 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
2918 raise OKE_API.G_EXCEPTION_ERROR;
2919 End If;
2920
2921 If (p_del_tbl.COUNT > 0) Then
2922 i := p_del_tbl.FIRST;
2923 LOOP
2924
2925 -- call complex API
2926 OKE_CONTRACT_PVT.validate_deliverable(
2927 p_api_version => p_api_version,
2928 p_init_msg_list => p_init_msg_list,
2929 x_return_status => x_return_status,
2930 x_msg_count => x_msg_count,
2931 x_msg_data => x_msg_data,
2932 p_del_rec => p_del_tbl(i));
2933
2934 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
2935 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
2936 l_overall_status := x_return_status;
2937 End If;
2938 End If;
2939
2940 EXIT WHEN (i = p_del_tbl.LAST);
2941 i := p_del_tbl.NEXT(i);
2942 END LOOP;
2943
2944 -- return overall status
2945 x_return_status := l_overall_status;
2946 End If;
2947
2948 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
2949 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
2950 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
2951 raise OKE_API.G_EXCEPTION_ERROR;
2952 End If;
2953
2954 -- end activity
2955 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
2956 x_msg_data => x_msg_data);
2957 EXCEPTION
2958 when OKE_API.G_EXCEPTION_ERROR then
2959 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2960 p_api_name => l_api_name,
2961 p_pkg_name => g_pkg_name,
2962 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
2963 x_msg_count => x_msg_count,
2964 x_msg_data => x_msg_data,
2965 p_api_type => g_api_type);
2966
2967 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
2968 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2969 p_api_name => l_api_name,
2970 p_pkg_name => g_pkg_name,
2971 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
2972 x_msg_count => x_msg_count,
2973 x_msg_data => x_msg_data,
2974 p_api_type => g_api_type);
2975
2976 when OTHERS then
2977 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
2978 p_api_name => l_api_name,
2979 p_pkg_name => g_pkg_name,
2980 p_exc_name => 'OTHERS',
2981 x_msg_count => x_msg_count,
2982 x_msg_data => x_msg_data,
2983 p_api_type => g_api_type);
2984
2985 END validate_deliverable;
2986
2987 PROCEDURE lock_deliverable(
2988 p_api_version IN NUMBER,
2989 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
2990 x_return_status OUT NOCOPY VARCHAR2,
2991 x_msg_count OUT NOCOPY NUMBER,
2992 x_msg_data OUT NOCOPY VARCHAR2,
2993 p_del_rec IN oke_deliverable_pvt.del_rec_type) IS
2994
2995 l_del_rec oke_deliverable_pvt.del_rec_type;
2996 l_api_name CONSTANT VARCHAR2(30) := 'LOCK_DELIVERABLE';
2997 l_api_version CONSTANT NUMBER := 1.0;
2998 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
2999 BEGIN
3000 -- call START_ACTIVITY to create savepoint, check compatibility
3001 -- and initialize message list
3002 l_return_status := OKE_API.START_ACTIVITY(
3003 p_api_name => l_api_name,
3004 p_pkg_name => g_pkg_name,
3005 p_init_msg_list => p_init_msg_list,
3006 l_api_version => l_api_version,
3007 p_api_version => p_api_version,
3008 p_api_type => g_api_type,
3009 x_return_status => x_return_status);
3010
3011 -- check if activity started successfully
3012 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
3013 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3014 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
3015 raise OKE_API.G_EXCEPTION_ERROR;
3016 End If;
3017
3018 -- call BEFORE user hook
3019 l_del_rec := p_del_rec;
3020
3021 -- call procedure in complex API
3022 OKE_CONTRACT_PVT.lock_deliverable(
3023 p_api_version => p_api_version,
3024 p_init_msg_list => p_init_msg_list,
3025 x_return_status => x_return_status,
3026 x_msg_count => x_msg_count,
3027 x_msg_data => x_msg_data,
3028 p_del_rec => l_del_rec);
3029
3030 -- check return status
3031 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
3032 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3033 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
3034 raise OKE_API.G_EXCEPTION_ERROR;
3035 End If;
3036
3037 -- end activity
3038 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
3039 x_msg_data => x_msg_data);
3040 EXCEPTION
3041 when OKE_API.G_EXCEPTION_ERROR then
3042 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
3043 p_api_name => l_api_name,
3044 p_pkg_name => g_pkg_name,
3045 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
3046 x_msg_count => x_msg_count,
3047 x_msg_data => x_msg_data,
3048 p_api_type => g_api_type);
3049
3050 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
3051 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
3052 p_api_name => l_api_name,
3053 p_pkg_name => g_pkg_name,
3054 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
3055 x_msg_count => x_msg_count,
3056 x_msg_data => x_msg_data,
3057 p_api_type => g_api_type);
3058
3059 when OTHERS then
3060 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
3061 p_api_name => l_api_name,
3062 p_pkg_name => g_pkg_name,
3063 p_exc_name => 'OTHERS',
3064 x_msg_count => x_msg_count,
3065 x_msg_data => x_msg_data,
3066 p_api_type => g_api_type);
3067
3068 END lock_deliverable;
3069
3070 PROCEDURE lock_deliverable(
3071 p_api_version IN NUMBER,
3072 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
3073 x_return_status OUT NOCOPY VARCHAR2,
3074 x_msg_count OUT NOCOPY NUMBER,
3075 x_msg_data OUT NOCOPY VARCHAR2,
3076 p_del_tbl IN oke_deliverable_pvt.del_tbl_type) IS
3077
3078 l_api_name CONSTANT VARCHAR2(30) := 'LOCK_DELIVERABLE';
3079 l_api_version CONSTANT NUMBER := 1.0;
3080 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
3081 l_overall_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
3082 i NUMBER;
3083 BEGIN
3084 -- call START_ACTIVITY to create savepoint, check compatibility
3085 -- and initialize message list
3086 l_return_status := OKE_API.START_ACTIVITY(
3087 p_api_name => l_api_name,
3088 p_pkg_name => g_pkg_name,
3089 p_init_msg_list => p_init_msg_list,
3090 l_api_version => l_api_version,
3091 p_api_version => p_api_version,
3092 p_api_type => g_api_type,
3093 x_return_status => x_return_status);
3094
3095 -- check if activity started successfully
3096 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
3097 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3098 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
3099 raise OKE_API.G_EXCEPTION_ERROR;
3100 End If;
3101
3102 If (p_del_tbl.COUNT > 0) Then
3103 i := p_del_tbl.FIRST;
3104 LOOP
3105 -- call procedure in complex API
3106 OKE_CONTRACT_PVT.lock_deliverable(
3107 p_api_version => p_api_version,
3108 p_init_msg_list => p_init_msg_list,
3109 x_return_status => x_return_status,
3110 x_msg_count => x_msg_count,
3111 x_msg_data => x_msg_data,
3112 p_del_rec => p_del_tbl(i));
3113
3114 -- store the highest degree of error
3115 If x_return_status <> OKE_API.G_RET_STS_SUCCESS Then
3116 If l_overall_status <> OKE_API.G_RET_STS_UNEXP_ERROR Then
3117 l_overall_status := x_return_status;
3118 End If;
3119 End If;
3120 EXIT WHEN (i = p_del_tbl.LAST);
3121 i := p_del_tbl.NEXT(i);
3122 END LOOP;
3123 -- return overall status
3124 x_return_status := l_overall_status;
3125 End If;
3126
3127 If x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR Then
3128 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3129 Elsif x_return_status = OKE_API.G_RET_STS_ERROR Then
3130 raise OKE_API.G_EXCEPTION_ERROR;
3131 End If;
3132
3133 -- end activity
3134 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
3135 x_msg_data => x_msg_data);
3136 EXCEPTION
3137 when OKE_API.G_EXCEPTION_ERROR then
3138 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
3139 p_api_name => l_api_name,
3140 p_pkg_name => g_pkg_name,
3141 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
3142 x_msg_count => x_msg_count,
3143 x_msg_data => x_msg_data,
3144 p_api_type => g_api_type);
3145
3146 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
3147 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
3148 p_api_name => l_api_name,
3149 p_pkg_name => g_pkg_name,
3150 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
3151 x_msg_count => x_msg_count,
3152 x_msg_data => x_msg_data,
3153 p_api_type => g_api_type);
3154
3155 when OTHERS then
3156 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
3157 p_api_name => l_api_name,
3158 p_pkg_name => g_pkg_name,
3159 p_exc_name => 'OTHERS',
3160 x_msg_count => x_msg_count,
3161 x_msg_data => x_msg_data,
3162 p_api_type => g_api_type);
3163
3164 END lock_deliverable;
3165
3166 PROCEDURE default_deliverable (
3167 p_api_version IN NUMBER,
3168 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
3169 x_return_status OUT NOCOPY VARCHAR2,
3170 x_msg_count OUT NOCOPY NUMBER,
3171 x_msg_data OUT NOCOPY VARCHAR2,
3172 p_header_id IN NUMBER,
3173 p_first_default_flag IN VARCHAR2,
3174 x_del_tbl OUT NOCOPY oke_deliverable_pvt.del_tbl_type) IS
3175
3176 l_api_version CONSTANT NUMBER := 1;
3177 l_del_tbl oke_deliverable_pvt.del_tbl_type;
3178 l_api_name CONSTANT VARCHAR2(30) := 'DEFAULT_DELIVERABLE';
3179
3180 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
3181 l_buy_or_sell VARCHAR2(1);
3182 l_direction VARCHAR2(3);
3183 l_org_id Number;
3184
3185 cursor l_default_csr(p_id number) is
3186 select buy_or_sell from okc_k_headers_b
3187 where id = p_id;
3188
3189 cursor l_csr(p_id NUMBER) is
3190 select l.k_line_id,
3191 l.line_number,
3192 l.project_id,
3193 l.inventory_item_id,
3194 l.line_description,
3195 l.delivery_date,
3196 l.status_code,
3197 l.start_date,
3198 l.end_date,
3199 k.priority_code,
3200 h.currency_code,
3201 l.unit_price,
3202 l.uom_code,
3203 l.line_quantity,
3204 k.country_of_origin_code,
3205 l.subcontracted_flag,
3206 l.billable_flag,
3207 l.drop_shipped_flag,
3208 l.completed_flag,
3209 l.shippable_flag,
3210 l.cfe_flag,
3211 l.inspection_req_flag,
3212 l.interim_rpt_req_flag,
3213 l.customer_approval_req_flag,
3214 l.as_of_date,
3215 l.date_of_first_submission,
3216 l.frequency,
3217 l.data_item_subtitle,
3218 l.copies_required,
3219 l.cdrl_category,
3220 l.data_item_name,
3221 l.export_flag
3222 from oke_k_lines_v l, okc_k_headers_b h, oke_k_headers k
3223 where h.id = p_id
3224 and l.header_id = p_id
3225 and h.id = k.k_header_id
3226 and not exists (select 'x' from okc_k_lines_b s where s.cle_id = l.k_line_id)
3227 and exists (select 'x' from okc_assents a
3228 where a.opn_code = 'CREATE_DELV'
3229 and a.sts_code = l.status_code
3230 and a.scs_code = 'PROJECT'
3231 and a.allowed_yn = 'Y');
3232
3233 cursor l_csr2(p_id NUMBER) is
3234 select l.k_line_id,
3235 l.line_number,
3236 l.project_id,
3237 l.inventory_item_id,
3238 l.line_description,
3239 l.delivery_date,
3240 l.status_code,
3241 l.start_date,
3242 l.end_date,
3243 k.priority_code,
3244 h.currency_code,
3245 l.unit_price,
3246 l.uom_code,
3247 l.line_quantity,
3248 k.country_of_origin_code,
3249 l.subcontracted_flag,
3250 l.billable_flag,
3251 l.drop_shipped_flag,
3252 l.completed_flag,
3253 l.shippable_flag,
3254 l.cfe_flag,
3255 l.inspection_req_flag,
3256 l.interim_rpt_req_flag,
3257 l.customer_approval_req_flag,
3258 l.as_of_date,
3259 l.date_of_first_submission,
3260 l.frequency,
3261 l.data_item_subtitle,
3262 l.copies_required,
3263 l.cdrl_category,
3264 l.data_item_name,
3265 l.export_flag
3266 from oke_k_lines_v l, okc_k_headers_b h, oke_k_headers k
3267 where h.id = p_id
3268 and l.header_id = p_id
3269 and h.id = k.k_header_id
3270 and not exists (select 'x' from okc_k_lines_b s where s.cle_id = l.k_line_id)
3271 and not exists (select 'x' from oke_k_deliverables_b where k_line_id = l.k_line_id)
3272 and exists (select 'x' from okc_assents a
3273 where a.opn_code = 'CREATE_DELV'
3274 and a.sts_code = l.status_code
3275 and a.scs_code = 'PROJECT'
3276 and a.allowed_yn = 'Y');
3277
3278 cursor item_csr(p_id number) is
3279 select object1_id2
3280 from okc_k_items_v
3281 where cle_id = p_id;
3282
3283 cursor cust_csr(p_id Number) is
3284 select cust_account_id from oke_cust_site_uses_v
3285 where id1 = p_id;
3286
3287
3288
3289
3290 l_csr_rec l_csr%ROWTYPE;
3291 l_csr2_rec l_csr2%ROWTYPE;
3292 l_id number;
3293 i NUMBER;
3294 l_inventory_org_id NUMBER;
3295 l_ship_to_id number;
3296 l_ship_from_id number;
3297
3298 PROCEDURE get_org(p_header_id number,
3299 p_line_id number,
3300 x_ship_to_id OUT NOCOPY NUMBER,
3301 x_ship_from_id OUT NOCOPY NUMBER) IS
3302
3303 cursor party_csr1(p_id Number,p_code varchar2) is
3304 select object1_id1, object1_id2, jtot_object1_code
3305 from okc_k_party_roles_b
3306 where dnz_chr_id = p_header_id and cle_id = p_id
3307 and rle_code = p_code;
3308
3309 cursor party_csr2(p_id Number,p_code varchar2) is
3310 select object1_id1, object1_id2, jtot_object1_code
3311 from okc_k_party_roles_b
3312 where dnz_chr_id = p_id and chr_id = p_id
3313 and rle_code = p_code;
3314
3315 cursor line_party(p_code Varchar2) is
3316 select Max(a.level_sequence) from okc_ancestrys a
3317 where a.cle_id = p_line_id
3318 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);
3319
3320 cursor header_party(p_code Varchar2) is
3321 select count(*) from okc_k_party_roles_b
3322 where dnz_chr_id = p_header_id and chr_id = p_header_id
3323 and rle_code = p_code
3324 and object1_id1 is not null;
3325
3326 cursor c is
3327 select buy_or_sell from okc_k_headers_b
3328 where id = p_header_id;
3329
3330 cursor top_line is
3331 select 'x' from okc_ancestrys
3332 where cle_id = p_line_id;
3333
3334 Cursor Inv_C(P_Id Number) Is
3335 Select 'x'
3336 From HR_ALL_ORGANIZATION_UNITS hr, MTL_PARAMETERS mp
3337 Where hr.Organization_Id = P_Id
3338 And mp.Organization_Id = hr.Organization_Id;
3339
3340
3341 l_ship_to_id number;
3342 l_ship_from_id number;
3343 l_id1 varchar2(40);
3344 l_id2 varchar2(200);
3345 l_object_code varchar2(30);
3346 l_level Number;
3347 l_value Varchar2(1);
3348 l_found Boolean := TRUE;
3349
3350 c1info party_csr1%rowtype;
3351 c2info party_csr2%rowtype;
3352
3353 l_row_count number;
3354 l_buy_or_sell varchar2(1);
3355
3356 BEGIN
3357
3358 select buy_or_sell into l_buy_or_sell
3359 from okc_k_headers_b
3360 where id = p_header_id;
3361
3362
3363 IF p_line_id is not null then
3364
3365 SELECT COUNT(*) INTO l_row_count
3366 FROM OKC_K_PARTY_ROLES_B
3367 WHERE dnz_chr_id = p_header_id and cle_id = p_line_id
3368 and rle_code = 'SHIP_FROM'
3369 and object1_id1 is not null;
3370
3371 if l_row_count = 1 then
3372
3373 l_id := p_line_id;
3374
3375 open party_csr1(l_id,'SHIP_FROM');
3376 fetch party_csr1 into c1info;
3377 close party_csr1;
3378
3379 l_object_code := c1info.jtot_object1_code;
3380 if l_buy_or_sell = 'B' then
3381 if l_object_code = 'OKX_VENDOR' then
3382 l_id1 := c1info.object1_id1;
3383 end if;
3384 else
3385 if l_object_code = 'OKX_INVENTORY' then
3386 -- only inventory_org will be defaulted down to DTS
3387
3388 Open Inv_C(c1info.object1_Id1);
3389 Fetch Inv_C Into L_Value;
3390 Close Inv_C;
3391
3392 if l_value = 'x' then
3393 l_id1 := c1info.object1_id1;
3394 end if;
3395
3396 end if;
3397 end if;
3398
3399 elsif l_row_count = 0 then
3400
3401 -- if the line is top line, go directly to header, else search parent line
3402 open top_line;
3403 fetch top_line into l_value;
3404 l_found := top_line%found;
3405 close top_line;
3406
3407 if l_found then
3408 open line_party('SHIP_FROM');
3409 fetch line_party into l_level;
3410 l_found := line_party%found;
3411 close line_party;
3412
3413 end if;
3414
3415 if l_level is not null then
3416
3417 -- check parent line default
3418 select cle_id_ascendant into l_id
3419 from okc_ancestrys
3420 where cle_id = p_line_id
3421 and level_sequence = l_level;
3422
3423 select count(*) into l_row_count
3424 from okc_k_party_roles_b
3425 where dnz_chr_id = p_header_id and cle_id = l_id
3426 and rle_code = 'SHIP_FROM';
3427
3428 if l_row_count = 1 then
3429 open party_csr1(l_id, 'SHIP_FROM');
3430 fetch party_csr1 into c1info;
3431 close party_csr1;
3432 l_object_code := c1info.jtot_object1_code;
3433
3434
3435 if l_buy_or_sell = 'B' then
3436 if l_object_code = 'OKX_VENDOR' then
3437 l_id1 := c1info.object1_id1;
3438 end if;
3439 else
3440 if l_object_code = 'OKX_INVENTORY' then
3441 Open Inv_C(c1info.object1_id1);
3442 Fetch Inv_C Into L_Value;
3443 Close Inv_C;
3444 if l_value = 'x' then
3445 l_id1 := c1info.object1_id1;
3446 end if;
3447
3448 end if;
3449 end if;
3450 end if;
3451 else
3452
3453 -- check header party for default
3454 open header_party('SHIP_FROM');
3455 fetch header_party into l_level;
3456 l_found := header_party%found;
3457 close header_party;
3458
3459 if l_level > 0 then
3460 if l_level = 1 then
3461 open party_csr2(p_header_id, 'SHIP_FROM');
3462 fetch party_csr2 into c2info;
3463 close party_csr2;
3464 l_object_code := c2info.jtot_object1_code;
3465
3466
3467
3468 if l_buy_or_sell = 'B' then
3469 if l_object_code = 'OKX_VENDOR' then
3470 l_id1 := c2info.object1_id1;
3471 end if;
3472 else
3473 if l_object_code = 'OKX_INVENTORY' then
3474 -- only inventory_org will be defaulted down to DTS
3475 Open Inv_C(c2info.object1_id1);
3476 Fetch Inv_C Into L_Value;
3477 Close Inv_C;
3478
3479 if l_value = 'x' then
3480 l_id1 := c2info.object1_id1;
3481 end if;
3482
3483 end if;
3484 end if;
3485 end if;
3486
3487 end if;
3488 end if;
3489 end if;
3490 end if;
3491
3492
3493 if l_id1 is not null then
3494 l_ship_from_id := to_number(l_id1);
3495 l_id1 := null;
3496 end if;
3497
3498 select count(*) into l_row_count
3499 from okc_k_party_roles_b
3500 where dnz_chr_id = p_header_id and cle_id = p_line_id
3501 and rle_code = 'SHIP_TO';
3502
3503 if l_row_count = 1 then
3504 l_id := p_line_id;
3505
3506 open party_csr1(l_id,'SHIP_TO');
3507 fetch party_csr1 into c1info;
3508 close party_csr1;
3509
3510 l_object_code := c1info.jtot_object1_code;
3511 if l_buy_or_sell = 'S' then
3512 if l_object_code = 'OKE_SHIPTO' then
3513
3514 l_id1 := c1info.object1_id1;
3515 end if;
3516 else
3517 if l_object_code = 'OKX_INVENTORY' then
3518 -- only inventory_org will be defaulted down to DTS
3519 Open Inv_C(c1info.object1_id1);
3520 Fetch Inv_C Into L_Value;
3521 Close Inv_C;
3522
3523 if l_value = 'x' then
3524 l_id1 := c1info.object1_id1;
3525 end if;
3526
3527 end if;
3528 end if;
3529
3530 elsif l_row_count = 0 then
3531
3532 open line_party('SHIP_TO');
3533 fetch line_party into l_level;
3534 l_found := line_party%found;
3535 close line_party;
3536
3537 if l_level is not null then
3538
3539 -- check parent line default
3540 select cle_id_ascendant into l_id
3541 from okc_ancestrys
3542 where cle_id = p_line_id
3543 and level_sequence = l_level;
3544
3545 select count(*) into l_row_count
3546 from okc_k_party_roles_b
3547 where dnz_chr_id = p_header_id and cle_id = l_id
3548 and rle_code = 'SHIP_TO';
3549
3550 if l_row_count = 1 then
3551 open party_csr1(l_id, 'SHIP_TO');
3552 fetch party_csr1 into c1info;
3553 close party_csr1;
3554 l_object_code := c1info.jtot_object1_code;
3555 if l_buy_or_sell = 'S' then
3556 if l_object_code = 'OKE_SHIPTO' then
3557 l_id1 := c1info.object1_id1;
3558 end if;
3559 else
3560 if l_object_code = 'OKX_INVENTORY' then
3561 -- only inventory_org will be defaulted down to DTS
3562 Open Inv_C(c1info.object1_id1);
3563 Fetch Inv_C Into L_Value;
3564 Close Inv_C;
3565
3566 if l_value = 'x' then
3567 l_id1 := c1info.object1_id1;
3568 end if;
3569
3570 end if;
3571 end if;
3572 end if;
3573
3574 else
3575
3576 -- check header party for default
3577 open header_party('SHIP_TO');
3578 fetch header_party into l_level;
3579 l_found := header_party%found;
3580 close header_party;
3581
3582 if l_found then
3583
3584 if l_level = 1 then
3585 open party_csr2(p_header_id, 'SHIP_TO');
3586 fetch party_csr2 into c2info;
3587 close party_csr2;
3588
3589 l_object_code := c2info.jtot_object1_code;
3590 if l_buy_or_sell = 'S' then
3591 if l_object_code = 'OKE_SHIPTO' then
3592 l_id1 := c2info.object1_id1;
3593 end if;
3594 else
3595 if l_object_code = 'OKX_INVENTORY' then
3596 -- only inventory_org will be defaulted down to DTS
3597 Open Inv_C(c2info.object1_id1);
3598 Fetch Inv_C Into L_Value;
3599 Close Inv_C;
3600
3601 if l_value = 'x' then
3602 l_id1 := c2info.object1_id1;
3603 end if;
3604
3605 end if;
3606 end if;
3607
3608 end if;
3609
3610 end if;
3611
3612 end if;
3613
3614 end if;
3615
3616 if l_id1 is not null then
3617
3618 l_ship_to_id := to_number(l_id1);
3619 l_id1 := null;
3620 end if;
3621
3622 x_ship_to_id := l_ship_to_id;
3623 x_ship_from_id := l_ship_from_id;
3624
3625 END;
3626
3627
3628 BEGIN
3629 -- call START_ACTIVITY to create savepoint, check compatibility
3630 -- and initialize message list
3631
3632 l_return_status := OKE_API.START_ACTIVITY(
3633 p_api_name => l_api_name,
3634 p_pkg_name => g_pkg_name,
3635 p_init_msg_list => p_init_msg_list,
3636 l_api_version => l_api_version,
3637 p_api_version => p_api_version,
3638 p_api_type => g_api_type,
3639 x_return_status => x_return_status);
3640
3641 -- check if activity started successfully
3642 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
3643 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3644 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
3645 raise OKE_API.G_EXCEPTION_ERROR;
3646 End If;
3647
3648 i := 1;
3649
3650 -- default deliverable direction based on buy_or_sell
3651 open l_default_csr(p_header_id);
3652 fetch l_default_csr into l_buy_or_sell;
3653 close l_default_csr;
3654
3655 if l_buy_or_sell = 'B' then
3656 l_direction := 'IN';
3657 else
3658 l_direction := 'OUT';
3659 end if;
3660
3661 -- get inventory_org_id from header
3662 SELECT INV_ORGANIZATION_ID INTO l_inventory_org_id
3663 FROM OKC_K_HEADERS_B
3664 WHERE ID = P_HEADER_ID;
3665 IF p_first_default_flag = 'Y' then
3666
3667 for l_csr_rec in l_csr(p_header_id)
3668 loop
3669 -- get ship_from, ship_to from line/header
3670 get_org(p_header_id, l_csr_rec.k_line_id, l_ship_to_id, l_ship_from_id);
3671
3672
3673 if l_direction = 'IN' then
3674
3675 l_del_tbl(i).ship_to_org_id := l_ship_to_id;
3676
3677 else
3678 if l_buy_or_sell = 'S' then
3679 l_del_tbl(i).ship_to_location_id := l_ship_to_id;
3680 open cust_csr(l_ship_to_id);
3681 fetch cust_csr into l_org_id;
3682 close cust_csr;
3683
3684 l_del_tbl(i).ship_to_org_id := l_org_id;
3685 else
3686 l_del_tbl(i).ship_to_org_id := l_ship_to_id;
3687 end if;
3688
3689
3690 end if;
3691 l_del_tbl(i).ship_from_org_id := l_ship_from_id;
3692
3693 --
3694 -- default inventory org id if ship_to/ship_from org exists, bug # 1743406
3695 --
3696
3697 if l_direction = 'IN' then
3698 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
3699 l_del_tbl(i).inventory_org_id := l_del_tbl(i).ship_to_org_id;
3700 else
3701 l_del_tbl(i).inventory_org_id := l_inventory_org_id;
3702 end if;
3703 else
3704 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
3705 l_del_tbl(i).inventory_org_id := l_del_tbl(i).ship_from_org_id;
3706 else
3707 l_del_tbl(i).inventory_org_id := l_inventory_org_id;
3708 end if;
3709 end if;
3710
3711 -- l_del_tbl(i).inventory_org_id := l_inventory_org_id;
3712 l_del_tbl(i).k_line_id := l_csr_rec.k_line_id;
3713
3714 l_del_tbl(i).defaulted_flag := 'Y';
3715 l_del_tbl(i).direction := l_direction;
3716 l_del_tbl(i).k_header_id := p_header_id;
3717 l_del_tbl(i).deliverable_num := l_csr_rec.line_number;
3718 l_del_tbl(i).project_id := l_csr_rec.project_id;
3719 l_del_tbl(i).item_id := l_csr_rec.inventory_item_id;
3720 l_del_tbl(i).description := l_csr_rec.line_description;
3721 l_del_tbl(i).delivery_date := l_csr_rec.delivery_date;
3722 l_del_tbl(i).status_code := l_csr_rec.status_code;
3723 l_del_tbl(i).start_date := l_csr_rec.start_date;
3724 l_del_tbl(i).end_date := l_csr_rec.end_date;
3725 l_del_tbl(i).priority_code := l_csr_rec.priority_code;
3726 l_del_tbl(i).currency_code := l_csr_rec.currency_code;
3727 l_del_tbl(i).unit_price := l_csr_rec.unit_price;
3728 l_del_tbl(i).uom_code := l_csr_rec.uom_code;
3729 l_del_tbl(i).quantity := l_csr_rec.line_quantity;
3730 l_del_tbl(i).country_of_origin_code := l_csr_rec.country_of_origin_code;
3731
3732 l_del_tbl(i).subcontracted_flag := l_csr_rec.subcontracted_flag;
3733 l_del_tbl(i).billable_flag := l_csr_rec.billable_flag;
3734 l_del_tbl(i).drop_shipped_flag := l_csr_rec.drop_shipped_flag;
3735 l_del_tbl(i).completed_flag := l_csr_rec.completed_flag;
3736 l_del_tbl(i).shippable_flag := l_csr_rec.shippable_flag;
3737 l_del_tbl(i).cfe_req_flag := l_csr_rec.cfe_flag;
3738 l_del_tbl(i).inspection_req_flag := l_csr_rec.inspection_req_flag;
3739 l_del_tbl(i).interim_rpt_req_flag := l_csr_rec.interim_rpt_req_flag;
3740 l_del_tbl(i).customer_approval_req_flag := l_csr_rec.customer_approval_req_flag;
3741 l_del_tbl(i).as_of_date := l_csr_rec.as_of_date;
3742 l_del_tbl(i).date_of_first_submission := l_csr_rec.date_of_first_submission;
3743 l_del_tbl(i).frequency := l_csr_rec.frequency;
3744 l_del_tbl(i).data_item_subtitle := l_csr_rec.data_item_subtitle;
3745 l_del_tbl(i).total_num_of_copies := l_csr_rec.copies_required;
3746 l_del_tbl(i).cdrl_category := l_csr_rec.cdrl_category;
3747 l_del_tbl(i).data_item_name := l_csr_rec.data_item_name;
3748 l_del_tbl(i).export_flag := l_csr_rec.export_flag;
3749 /* if l_del_tbl(i).item_id is not null then
3750 open item_csr(l_csr_rec.k_line_id);
3751 fetch item_csr into l_id;
3752 close item_csr;
3753 l_del_tbl(i).inventory_org_id := l_id;
3754 end if; */
3755
3756
3757 i := i + 1;
3758
3759 end loop;
3760
3761 ELSE
3762 for l_csr2_rec in l_csr2(p_header_id)
3763 loop
3764 -- get ship_from, ship_to from line/header
3765 get_org(p_header_id, l_csr2_rec.k_line_id, l_ship_to_id, l_ship_from_id);
3766
3767 if l_direction = 'IN' then
3768
3769 l_del_tbl(i).ship_to_org_id := l_ship_to_id;
3770
3771 else
3772 if l_buy_or_sell = 'S' then
3773 l_del_tbl(i).ship_to_location_id := l_ship_to_id;
3774 open cust_csr(l_ship_to_id);
3775 fetch cust_csr into l_org_id;
3776 close cust_csr;
3777 l_del_tbl(i).ship_to_org_id := l_org_id;
3778 else
3779 l_del_tbl(i).ship_to_org_id := l_ship_to_id;
3780 end if;
3781 end if;
3782
3783 l_del_tbl(i).ship_from_org_id := l_ship_from_id;
3784
3785 --
3786 -- default inventory org id if ship_to/ship_from org exists, bug # 1743406
3787 --
3788 if l_direction = 'IN' then
3789 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
3790 l_del_tbl(i).inventory_org_id := l_del_tbl(i).ship_to_org_id;
3791 else
3792 l_del_tbl(i).inventory_org_id := l_inventory_org_id;
3793 end if;
3794 else
3795 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
3796
3797 l_del_tbl(i).inventory_org_id := l_del_tbl(i).ship_from_org_id;
3798 else
3799
3800 l_del_tbl(i).inventory_org_id := l_inventory_org_id;
3801 end if;
3802 end if;
3803
3804 -- l_del_tbl(i).inventory_org_id := l_inventory_org_id;
3805 l_del_tbl(i).k_line_id := l_csr2_rec.k_line_id;
3806
3807 l_del_tbl(i).defaulted_flag := 'Y';
3808 l_del_tbl(i).direction := l_direction;
3809 l_del_tbl(i).k_header_id := p_header_id;
3810 l_del_tbl(i).deliverable_num := l_csr2_rec.line_number;
3811 l_del_tbl(i).project_id := l_csr2_rec.project_id;
3812 l_del_tbl(i).item_id := l_csr2_rec.inventory_item_id;
3813 l_del_tbl(i).description := l_csr2_rec.line_description;
3814 l_del_tbl(i).delivery_date := l_csr2_rec.delivery_date;
3815 l_del_tbl(i).status_code := l_csr2_rec.status_code;
3816 l_del_tbl(i).start_date := l_csr2_rec.start_date;
3817 l_del_tbl(i).end_date := l_csr2_rec.end_date;
3818 l_del_tbl(i).priority_code := l_csr2_rec.priority_code;
3819 l_del_tbl(i).currency_code := l_csr2_rec.currency_code;
3820 l_del_tbl(i).unit_price := l_csr2_rec.unit_price;
3821 l_del_tbl(i).uom_code := l_csr2_rec.uom_code;
3822 l_del_tbl(i).quantity := l_csr2_rec.line_quantity;
3823 l_del_tbl(i).country_of_origin_code := l_csr2_rec.country_of_origin_code;
3824 l_del_tbl(i).subcontracted_flag := l_csr2_rec.subcontracted_flag;
3825 l_del_tbl(i).billable_flag := l_csr2_rec.billable_flag;
3826 l_del_tbl(i).drop_shipped_flag := l_csr2_rec.drop_shipped_flag;
3827 l_del_tbl(i).completed_flag := l_csr2_rec.completed_flag;
3828 l_del_tbl(i).shippable_flag := l_csr2_rec.shippable_flag;
3829 l_del_tbl(i).cfe_req_flag := l_csr2_rec.cfe_flag;
3830 l_del_tbl(i).inspection_req_flag := l_csr2_rec.inspection_req_flag;
3831 l_del_tbl(i).interim_rpt_req_flag := l_csr2_rec.interim_rpt_req_flag;
3832 l_del_tbl(i).customer_approval_req_flag := l_csr2_rec.customer_approval_req_flag;
3833 l_del_tbl(i).as_of_date := l_csr2_rec.as_of_date;
3834 l_del_tbl(i).date_of_first_submission := l_csr2_rec.date_of_first_submission;
3835 l_del_tbl(i).frequency := l_csr2_rec.frequency;
3836 l_del_tbl(i).data_item_subtitle := l_csr2_rec.data_item_subtitle;
3837 l_del_tbl(i).total_num_of_copies := l_csr2_rec.copies_required;
3838 l_del_tbl(i).cdrl_category := l_csr2_rec.cdrl_category;
3839 l_del_tbl(i).data_item_name := l_csr2_rec.data_item_name;
3840 l_del_tbl(i).export_flag := l_csr2_rec.export_flag;
3841 /* if l_del_tbl(i).item_id is not null then
3842 open item_csr(l_csr_rec.k_line_id);
3843 fetch item_csr into l_id;
3844 close item_csr;
3845 l_del_tbl(i).inventory_org_id := l_id;
3846 end if; */
3847
3848 i := i + 1;
3849
3850 end loop;
3851 END IF;
3852
3853
3854
3855 -- insert into deliverable table
3856 If (l_del_tbl.COUNT > 0) Then
3857
3858 -- call procedure in public API for a record
3859 OKE_CONTRACT_PUB.create_deliverable(
3860 p_api_version => p_api_version,
3861 p_init_msg_list => p_init_msg_list,
3862 x_return_status => x_return_status,
3863 x_msg_count => x_msg_count,
3864 x_msg_data => x_msg_data,
3865 p_del_tbl => l_del_tbl,
3866 x_del_tbl => x_del_tbl);
3867
3868 If (x_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
3869 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3870 Elsif (x_return_status = OKE_API.G_RET_STS_ERROR) then
3871 raise OKE_API.G_EXCEPTION_ERROR;
3872 End If;
3873
3874 -- copy related entities to the deliverable
3875 commit;
3876
3877 end if;
3878 -- end activity
3879 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
3880 x_msg_data => x_msg_data);
3881 EXCEPTION
3882 when OKE_API.G_EXCEPTION_ERROR then
3883 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
3884 p_api_name => l_api_name,
3885 p_pkg_name => g_pkg_name,
3886 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
3887 x_msg_count => x_msg_count,
3888 x_msg_data => x_msg_data,
3889 p_api_type => g_api_type);
3890
3891 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
3892 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
3893 p_api_name => l_api_name,
3894 p_pkg_name => g_pkg_name,
3895 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
3896 x_msg_count => x_msg_count,
3897 x_msg_data => x_msg_data,
3898 p_api_type => g_api_type);
3899
3900 when OTHERS then
3901 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
3902 p_api_name => l_api_name,
3903 p_pkg_name => g_pkg_name,
3904 p_exc_name => 'OTHERS',
3905 x_msg_count => x_msg_count,
3906 x_msg_data => x_msg_data,
3907 p_api_type => g_api_type);
3908 end default_deliverable;
3909
3910 PROCEDURE Check_Delete_Contract(
3911 p_api_version IN NUMBER,
3912 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
3913 x_return_status OUT NOCOPY VARCHAR2,
3914 x_msg_count OUT NOCOPY NUMBER,
3915 x_msg_data OUT NOCOPY VARCHAR2,
3916 p_chr_id IN NUMBER,
3917 x_return_code OUT NOCOPY VARCHAR2) IS
3918
3919 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_DELETE_CONTRACT';
3920 l_api_version CONSTANT NUMBER := 1.0;
3921 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
3922 l_check VARCHAR2(1);
3923
3924
3925 CURSOR check_opns IS
3926 SELECT 'X' FROM OKC_ASSENTS
3927 WHERE OPN_CODE ='DELETE'
3928 AND STS_CODE =
3929 (SELECT STS_CODE FROM OKC_K_HEADERS_B WHERE ID = P_CHR_ID)
3930 AND SCS_CODE = 'PROJECT'
3931 AND ALLOWED_YN = 'Y';
3932
3933 CURSOR check_boa IS
3934 SELECT 'x'
3935 FROM OKE_K_HEADERS
3936 WHERE K_HEADER_ID = p_chr_id
3937 AND K_TYPE_CODE IN (SELECT K_TYPE_CODE FROM OKE_K_TYPES_B WHERE TYPE_CLASS_CODE='BOA')
3938 AND K_HEADER_ID IN (SELECT BOA_ID FROM OKE_K_HEADERS);
3939
3940 CURSOR check_sts IS
3941 SELECT 'x'
3942 FROM OKC_STATUSES_B ST, OKC_K_HEADERS_B KH
3943 WHERE KH.ID = p_chr_id
3944 AND ST.CODE = KH.STS_CODE
3945 AND ST.STE_CODE not in ( 'ENTERED','CANCELLED');
3946
3947 CURSOR check_po IS
3948 SELECT 'x'
3949 FROM OKE_K_DELIVERABLES_B DV
3950 WHERE PO_REF_1 IS NOT NULL
3951 AND DV.K_HEADER_ID = p_chr_id;
3952
3953 CURSOR check_mps IS
3954 SELECT 'x'
3955 FROM OKE_K_DELIVERABLES_B DV
3956 WHERE MPS_TRANSACTION_ID IS NOT NULL
3957 AND DV.K_HEADER_ID = p_chr_id;
3958
3959 CURSOR check_wsh IS
3960 SELECT 'x'
3961 FROM OKE_K_DELIVERABLES_B DV
3962 WHERE SHIPPING_REQUEST_ID IS NOT NULL
3963 AND DV.K_HEADER_ID = p_chr_id;
3964
3965 CURSOR check_funding IS
3966 SELECT 'x'
3967 FROM OKE_K_FUND_ALLOCATIONS FA, OKE_K_FUNDING_SOURCES FS
3968 WHERE FA.PA_FLAG = 'Y'
3969 AND FA.FUNDING_SOURCE_ID = FS.FUNDING_SOURCE_ID
3970 AND FS.OBJECT_ID = p_chr_id;
3971
3972
3973
3974 BEGIN
3975 -- call START_ACTIVITY to create savepoint, check compatibility
3976 -- and initialize message list
3977 l_return_status := OKE_API.START_ACTIVITY(
3978 p_api_name => l_api_name,
3979 p_pkg_name => g_pkg_name,
3980 p_init_msg_list => p_init_msg_list,
3981 l_api_version => l_api_version,
3982 p_api_version => p_api_version,
3983 p_api_type => g_api_type,
3984 x_return_status => x_return_status);
3985
3986 -- check if activity started successfully
3987 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
3988 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
3989 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
3990 raise OKE_API.G_EXCEPTION_ERROR;
3991 End If;
3992 l_check := '?';
3993 OPEN check_sts;
3994 FETCH check_sts INTO l_check;
3995 CLOSE check_sts;
3996
3997 IF l_check = 'x' THEN
3998 X_Return_Status := OKE_API.G_RET_STS_ERROR;
3999 x_return_code := 'STS';
4000 raise OKE_API.G_EXCEPTION_ERROR;
4001 END IF;
4002
4003 l_check := '?';
4004 OPEN check_opns;
4005 FETCH check_opns INTO l_check;
4006 CLOSE check_opns;
4007
4008 IF l_check = '?' THEN
4009 X_Return_Status := OKE_API.G_RET_STS_ERROR;
4010 x_return_code := 'OPN';
4011 raise OKE_API.G_EXCEPTION_ERROR;
4012 END IF;
4013
4014 l_check := '?';
4015 OPEN check_boa;
4016 FETCH check_boa INTO l_check;
4017 CLOSE check_boa;
4018
4019 IF l_check = 'x' THEN
4020 X_Return_Status := OKE_API.G_RET_STS_ERROR;
4021 x_return_code := 'BOA';
4022 raise OKE_API.G_EXCEPTION_ERROR;
4023 END IF;
4024
4025 l_check := '?';
4026 OPEN check_po;
4027 FETCH check_po INTO l_check;
4028 CLOSE check_po;
4029
4030 IF l_check = 'x' THEN
4031 X_Return_Status := OKE_API.G_RET_STS_ERROR;
4032 x_return_code := 'PO';
4033 raise OKE_API.G_EXCEPTION_ERROR;
4034 END IF;
4035
4036 l_check := '?';
4037 OPEN check_mps;
4038 FETCH check_mps INTO l_check;
4039 CLOSE check_mps;
4040
4041 IF l_check = 'x' THEN
4042 X_Return_Status := OKE_API.G_RET_STS_ERROR;
4043 x_return_code := 'PLAN';
4044 raise OKE_API.G_EXCEPTION_ERROR;
4045 END IF;
4046
4047 l_check := '?';
4048 OPEN check_wsh;
4049 FETCH check_wsh INTO l_check;
4050 CLOSE check_wsh;
4051 IF l_check = 'x' THEN
4052 X_Return_Status := OKE_API.G_RET_STS_ERROR;
4053 x_return_code := 'SHIP';
4054 raise OKE_API.G_EXCEPTION_ERROR;
4055 END IF;
4056
4057 l_check := '?';
4058 OPEN check_funding;
4059 FETCH check_funding INTO l_check;
4060 CLOSE check_funding;
4061
4062 IF l_check = 'x' THEN
4063 X_Return_Status := OKE_API.G_RET_STS_ERROR;
4064 x_return_code := 'FUND';
4065 raise OKE_API.G_EXCEPTION_ERROR;
4066 END IF;
4067
4068 If OKC_CONTRACT_PVT.Is_Process_Active(p_chr_id)='Y' then
4069 X_Return_Status := OKE_API.G_RET_STS_ERROR;
4070 x_return_code := 'WFA';
4071 raise OKE_API.G_EXCEPTION_ERROR;
4072 end if;
4073 x_return_status := l_return_status;
4074
4075 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
4076 x_msg_data => x_msg_data);
4077 EXCEPTION
4078 when OKE_API.G_EXCEPTION_ERROR then
4079 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
4080 p_api_name => l_api_name,
4081 p_pkg_name => g_pkg_name,
4082 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
4083 x_msg_count => x_msg_count,
4084 x_msg_data => x_msg_data,
4085 p_api_type => g_api_type);
4086
4087 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
4088 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
4089 p_api_name => l_api_name,
4090 p_pkg_name => g_pkg_name,
4091 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
4092 x_msg_count => x_msg_count,
4093 x_msg_data => x_msg_data,
4094 p_api_type => g_api_type);
4095
4096 when OTHERS then
4097 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
4098 p_api_name => l_api_name,
4099 p_pkg_name => g_pkg_name,
4100 p_exc_name => 'OTHERS',
4101 x_msg_count => x_msg_count,
4102 x_msg_data => x_msg_data,
4103 p_api_type => g_api_type);
4104
4105 END Check_Delete_Contract;
4106
4107
4108
4109
4110
4111 PROCEDURE delete_contract (
4112 p_api_version IN NUMBER,
4113 p_init_msg_list IN VARCHAR2 DEFAULT OKE_API.G_FALSE,
4114 x_return_status OUT NOCOPY VARCHAR2,
4115 x_msg_count OUT NOCOPY NUMBER,
4116 x_msg_data OUT NOCOPY VARCHAR2,
4117 p_chr_id IN NUMBER,
4118 p_pre_deletion_check_yn IN VARCHAR2 DEFAULT 'Y') IS
4119
4120
4121 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_CONTRACT';
4122 l_api_version CONSTANT NUMBER := 1.0;
4123 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
4124 l_status VARCHAR2(1);
4125 l_check VARCHAR2(1);
4126 i number;
4127
4128 l_return_code VARCHAR2(30);
4129
4130 l_note_tbl oke_note_pvt.note_tbl_type;
4131 l_form_tbl oke_form_pvt.form_tbl_type;
4132 l_term_tbl oke_term_pvt.term_tbl_type;
4133 l_article_rec okc_k_article_pub.catv_rec_type;
4134
4135 l_contact_tbl okc_contract_party_pub.ctcv_tbl_type;
4136 l_party_tbl okc_contract_party_pub.cplv_tbl_type;
4137 l_deliverable_tbl OKE_DELIVERABLE_PVT.del_tbl_type;
4138
4139
4140 l_item_tbl OKC_CONTRACT_ITEM_PUB.cimv_tbl_type;
4141 l_item_out_tbl OKC_CONTRACT_ITEM_PUB.cimv_tbl_type;
4142
4143 l_cle_tbl OKE_CLE_PVT.cle_tbl_type;
4144 l_chr_rec OKE_CHR_PVT.chr_rec_type;
4145 l_clev_tbl OKC_CONTRACT_PVT.clev_tbl_type;
4146 l_chrv_rec OKC_CONTRACT_PVT.chrv_rec_type;
4147
4148
4149 l_doc_id NUMBER;
4150 l_doc_type VARCHAR2(30);
4151
4152 cursor c_cle is
4153 select id from okc_k_lines_b
4154 where dnz_chr_id = p_chr_id;
4155
4156 cursor c_item is
4157 select id
4158 from okc_k_items
4159 where dnz_chr_id = p_chr_id;
4160
4161 cursor c_note is
4162 select standard_notes_id from oke_k_standard_notes_b
4163 where k_header_id = p_chr_id;
4164
4165
4166 cursor c_form is
4167 select print_form_code,k_header_id,k_line_id
4168 from oke_k_print_forms
4169 where k_header_id = p_chr_id;
4170
4171 cursor c_article is
4172 select id,object_version_number from okc_k_articles_b
4173 where dnz_chr_id = p_chr_id;
4174
4175 cursor c_term is
4176 select term_code,k_header_id,k_line_id, term_value_pk1, term_value_pk2
4177 from oke_k_terms
4178 where k_header_id = p_chr_id;
4179
4180 cursor c_contact is
4181 select id from okc_contacts
4182 where dnz_chr_id = p_chr_id;
4183
4184 cursor c_party is
4185 select id from okc_k_party_roles_b
4186 where dnz_chr_id = p_chr_id;
4187
4188 cursor c_alloc is
4189 select fund_allocation_id
4190 from oke_k_fund_allocations
4191 where object_id = p_chr_id;
4192
4193
4194 cursor c_fund is
4195 select funding_source_id
4196 from oke_k_funding_sources
4197 where object_id = p_chr_id;
4198
4199 cursor c_source is
4200 select funding_source_id
4201 from oke_k_funding_sources
4202 where object_id = p_chr_id
4203 and object_type = 'OKE_K_HEADER';
4204
4205 cursor c_del is
4206 select deliverable_id
4207 from oke_k_deliverables_b
4208 where k_header_id = p_chr_id;
4209
4210
4211 BEGIN
4212 -- call START_ACTIVITY to create savepoint, check compatibility
4213 -- and initialize message list
4214 l_return_status := OKE_API.START_ACTIVITY(
4215 p_api_name => l_api_name,
4216 p_pkg_name => g_pkg_name,
4217 p_init_msg_list => p_init_msg_list,
4218 l_api_version => l_api_version,
4219 p_api_version => p_api_version,
4220 p_api_type => g_api_type,
4221 x_return_status => x_return_status);
4222
4223 -- check if activity started successfully
4224 If (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
4225 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4226 Elsif (l_return_status = OKE_API.G_RET_STS_ERROR) then
4227 raise OKE_API.G_EXCEPTION_ERROR;
4228 End If;
4229
4230 IF p_pre_deletion_check_yn = 'Y' THEN
4231 Check_Delete_Contract(
4232 p_api_version => l_api_version,
4233 p_init_msg_list => p_init_msg_list,
4234 x_return_status => l_check,
4235 x_msg_count => x_msg_count,
4236 x_msg_data => x_msg_data,
4237 p_chr_id => p_chr_id,
4238 x_return_code => l_return_code );
4239
4240 If (l_check = OKE_API.G_RET_STS_UNEXP_ERROR) then
4241 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4242 Elsif (l_check = OKE_API.G_RET_STS_ERROR) then
4243 raise OKE_API.G_EXCEPTION_ERROR;
4244 End If;
4245
4246 END IF;
4247
4248
4249 i:=1;
4250 for c in c_note
4251 loop
4252 l_note_tbl(i).standard_notes_id := c.standard_notes_id;
4253 i:= i+1;
4254 end loop;
4255 OKE_STANDARD_NOTES_PUB.delete_standard_note(
4256 p_api_version => l_api_version,
4257 p_init_msg_list => OKE_API.G_FALSE,
4258 x_return_status => l_status,
4259 x_msg_count => x_msg_count,
4260 x_msg_data => x_msg_data,
4261 p_note_tbl => l_note_tbl);
4262
4263 If (l_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
4264 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4265 Elsif (l_status = OKE_API.G_RET_STS_ERROR) then
4266 raise OKE_API.G_EXCEPTION_ERROR;
4267 End If;
4268
4269
4270
4271 i:=1;
4272 for c in c_form
4273 loop
4274 l_form_tbl(i).print_form_code := c.print_form_code;
4275 l_form_tbl(i).k_header_id := c.k_header_id;
4276 l_form_tbl(i).k_line_id := c.k_line_id;
4277 i:= i+1;
4278 end loop;
4279
4280 OKE_K_PRINT_FORMS_PUB.delete_print_form(
4281 p_api_version => l_api_version,
4282 p_init_msg_list => OKE_API.G_FALSE,
4283 x_return_status => l_status,
4284 x_msg_count => x_msg_count,
4285 x_msg_data => x_msg_data,
4286 p_form_tbl => l_form_tbl);
4287
4288 If (l_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
4289 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4290 Elsif (l_status = OKE_API.G_RET_STS_ERROR) then
4291 raise OKE_API.G_EXCEPTION_ERROR;
4292 End If;
4293
4294
4295
4296
4297 OKC_TERMS_UTIL_GRP.Get_contract_document_type_id(
4298 p_api_version => 1,
4299 p_init_msg_list => FND_API.G_FALSE,
4300 x_return_status => l_status,
4301 x_msg_count => x_msg_count,
4302 x_msg_data => x_msg_data,
4303 p_chr_id => p_chr_id,
4304 x_doc_id => l_doc_id,
4305 x_doc_type => l_doc_type);
4306
4307
4308 If (l_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
4309 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4310 Elsif (l_status = OKE_API.G_RET_STS_ERROR) then
4311 raise OKE_API.G_EXCEPTION_ERROR;
4312 End If;
4313
4314
4315 OKC_TERMS_UTIL_GRP.delete_doc(
4316 p_api_version => 1,
4317 p_init_msg_list => FND_API.G_FALSE,
4318 p_commit => FND_API.G_FALSE,
4319 x_return_status => l_status,
4320 x_msg_data => x_msg_data,
4321 x_msg_count => x_msg_count,
4322 p_validate_commit => FND_API.G_FALSE,
4323 p_validation_string => NULL,
4324 p_doc_type => l_doc_type,
4325 p_doc_id => l_doc_id);
4326
4327
4328
4329
4330 If (l_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
4331 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4332 Elsif (l_status = OKE_API.G_RET_STS_ERROR) then
4333 raise OKE_API.G_EXCEPTION_ERROR;
4334 End If;
4335
4336
4337
4338
4339 i:=1;
4340 for c in c_term
4341 loop
4342 l_term_tbl(i).term_code := c.term_code;
4343 l_term_tbl(i).k_header_id := c.k_header_id;
4344 l_term_tbl(i).k_line_id := c.k_line_id;
4345 l_term_tbl(i).term_value_pk1 := c.term_value_pk1;
4346 l_term_tbl(i).term_value_pk2 := c.term_value_pk2;
4347 i:= i+1;
4348 end loop;
4349
4350 OKE_TERMS_PUB.delete_term(
4351 p_api_version => l_api_version,
4352 p_init_msg_list => OKE_API.G_FALSE,
4353 x_return_status => l_status,
4354 x_msg_count => x_msg_count,
4355 x_msg_data => x_msg_data,
4356 p_term_tbl => l_term_tbl);
4357
4358 If (l_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
4359 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4360 Elsif (l_status = OKE_API.G_RET_STS_ERROR) then
4361 raise OKE_API.G_EXCEPTION_ERROR;
4362 End If;
4363
4364
4365
4366
4367 i:=1;
4368 for c in c_contact
4369 loop
4370 l_contact_tbl(i).id := c.id;
4371 i:= i+1;
4372 end loop;
4373
4374 OKC_CONTRACT_PARTY_PUB.delete_contact(
4375 p_api_version => l_api_version,
4376 p_init_msg_list => OKC_API.G_FALSE,
4377 x_return_status => l_status,
4378 x_msg_count => x_msg_count,
4379 x_msg_data => x_msg_data,
4380 p_ctcv_tbl => l_contact_tbl);
4381
4382 If (l_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
4383 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4384 Elsif (l_status = OKE_API.G_RET_STS_ERROR) then
4385 raise OKE_API.G_EXCEPTION_ERROR;
4386 End If;
4387
4388
4389
4390
4391 i:=1;
4392 for c in c_party
4393 loop
4394 l_party_tbl(i).id := c.id;
4395 i:= i+1;
4396 end loop;
4397
4398 OKC_CONTRACT_PARTY_PUB.delete_k_party_role(
4399 p_api_version => l_api_version,
4400 p_init_msg_list => OKC_API.G_FALSE,
4401 x_return_status => l_status,
4402 x_msg_count => x_msg_count,
4403 x_msg_data => x_msg_data,
4404 p_cplv_tbl => l_party_tbl);
4405
4406 If (l_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
4407 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4408 Elsif (l_status = OKE_API.G_RET_STS_ERROR) then
4409 raise OKE_API.G_EXCEPTION_ERROR;
4410 End If;
4411
4412
4413
4414
4415 OKE_CONTRACT_PVT.delete_minor_entities (
4416 p_header_id => p_chr_id,
4417 x_return_status => l_status);
4418
4419 If (l_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
4420 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4421 Elsif (l_status = OKE_API.G_RET_STS_ERROR) then
4422 raise OKE_API.G_EXCEPTION_ERROR;
4423 End If;
4424
4425
4426
4427
4428
4429 for c in c_alloc
4430
4431 loop
4432 OKE_FUNDING_PUB.delete_allocation (
4433 p_api_version => l_api_version,
4434 p_init_msg_list => OKE_API.G_FALSE,
4435 p_commit => OKE_API.G_FALSE,
4436 x_return_status => l_status,
4437 x_msg_count => x_msg_count,
4438 x_msg_data => x_msg_data,
4439 p_fund_allocation_id => c.fund_allocation_id );
4440
4441 If (l_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
4442 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4443 Elsif (l_status = OKE_API.G_RET_STS_ERROR) then
4444 raise OKE_API.G_EXCEPTION_ERROR;
4445 End If;
4446
4447
4448 end loop;
4449
4450
4451 for c in c_fund
4452 loop
4453 OKE_FUNDING_PUB.delete_funding(
4454 p_api_version => l_api_version,
4455 p_init_msg_list => OKE_API.G_FALSE,
4456 p_commit => OKE_API.G_FALSE,
4457 x_return_status => l_status,
4458 x_msg_count => x_msg_count,
4459 x_msg_data => x_msg_data,
4460 p_funding_source_id => c.funding_source_id);
4461
4462
4463
4464
4465 If (l_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
4466 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4467 Elsif (l_status = OKE_API.G_RET_STS_ERROR) then
4468 raise OKE_API.G_EXCEPTION_ERROR;
4469 End If;
4470
4471
4472 end loop;
4473
4474
4475 i := 1;
4476 for c in c_del
4477 loop
4478 l_deliverable_tbl(i).deliverable_id := c.deliverable_id;
4479 i:= i+1;
4480 end loop;
4481
4482 OKE_DELIVERABLE_PVT.delete_row(
4483 p_api_version => l_api_version,
4484 p_init_msg_list => OKE_API.G_FALSE,
4485 x_return_status => l_status,
4486 x_msg_count => x_msg_count,
4487 x_msg_data => x_msg_data,
4488 p_del_tbl => l_deliverable_tbl);
4489
4490 If (l_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
4491 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4492 Elsif (l_status = OKE_API.G_RET_STS_ERROR) then
4493 raise OKE_API.G_EXCEPTION_ERROR;
4494 End If;
4495
4496
4497
4498 i:= 1;
4499 for c in c_item
4500 loop
4501 l_item_tbl(i).id := c.id;
4502 i:=i+1;
4503 end loop;
4504
4505 OKC_CONTRACT_ITEM_PUB.delete_contract_item(
4506 p_api_version => l_api_version,
4507 p_init_msg_list => OKC_API.G_FALSE,
4508 x_return_status => l_status,
4509 x_msg_count => x_msg_count,
4510 x_msg_data => x_msg_data,
4511 p_cimv_tbl => l_item_tbl);
4512
4513 If (l_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
4514 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4515 Elsif (l_status = OKE_API.G_RET_STS_ERROR) then
4516 raise OKE_API.G_EXCEPTION_ERROR;
4517 End If;
4518
4519
4520
4521
4522
4523 i:=1;
4524 for c in c_cle
4525 loop
4526 l_cle_tbl(i).k_line_id := c.id;
4527 l_clev_tbl(i).id := c.id;
4528 i:= i+1;
4529 end loop;
4530
4531
4532 OKE_CLE_PVT.delete_row(
4533 p_api_version => l_api_version,
4534 p_init_msg_list => OKE_API.G_FALSE,
4535 x_return_status => l_status,
4536 x_msg_count => x_msg_count,
4537 x_msg_data => x_msg_data,
4538 p_cle_tbl => l_cle_tbl);
4539
4540
4541 If (l_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
4542 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4543 Elsif (l_status = OKE_API.G_RET_STS_ERROR) then
4544 raise OKE_API.G_EXCEPTION_ERROR;
4545 End If;
4546
4547
4548
4549 OKC_CONTRACT_PVT.delete_contract_line(
4550 p_api_version => l_api_version,
4551 p_init_msg_list => OKC_API.G_FALSE,
4552 x_return_status => l_status,
4553 x_msg_count => x_msg_count,
4554 x_msg_data => x_msg_data,
4555 p_clev_tbl => l_clev_tbl);
4556
4557 If (l_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
4558 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4559 Elsif (l_status = OKE_API.G_RET_STS_ERROR) then
4560 raise OKE_API.G_EXCEPTION_ERROR;
4561 End If;
4562
4563
4564
4565 l_chr_rec.k_header_id := p_chr_id;
4566 l_chrv_rec.id := p_chr_id;
4567
4568
4569 OKE_CHR_PVT.delete_row(
4570 p_api_version => l_api_version,
4571 p_init_msg_list => OKE_API.G_FALSE,
4572 x_return_status => l_status,
4573 x_msg_count => x_msg_count,
4574 x_msg_data => x_msg_data,
4575 p_chr_rec => l_chr_rec);
4576
4577 If (l_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
4578 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4579 Elsif (l_status = OKE_API.G_RET_STS_ERROR) then
4580 raise OKE_API.G_EXCEPTION_ERROR;
4581 End If;
4582
4583
4584
4585 OKE_CONTRACT_PVT.delete_version_records (
4586 p_api_version => l_api_version,
4587 p_header_id => p_chr_id,
4588 x_return_status => l_status,
4589 x_msg_count => x_msg_count,
4590 x_msg_data => x_msg_data);
4591
4592
4593
4594 If (l_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
4595 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4596 Elsif (l_status = OKE_API.G_RET_STS_ERROR) then
4597 raise OKE_API.G_EXCEPTION_ERROR;
4598 End If;
4599
4600
4601 OKC_CONTRACT_PVT.delete_contract_header(
4602 p_api_version => l_api_version,
4603 p_init_msg_list => OKC_API.G_FALSE,
4604 x_return_status => l_status,
4605 x_msg_count => x_msg_count,
4606 x_msg_data => x_msg_data,
4607 p_chrv_rec => l_chrv_rec);
4608
4609
4610
4611 If (l_status = OKE_API.G_RET_STS_UNEXP_ERROR) then
4612 raise OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
4613 Elsif (l_status = OKE_API.G_RET_STS_ERROR) then
4614 raise OKE_API.G_EXCEPTION_ERROR;
4615 End If;
4616
4617
4618
4619
4620 -- end activity
4621 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count,
4622 x_msg_data => x_msg_data);
4623 EXCEPTION
4624 when OKE_API.G_EXCEPTION_ERROR then
4625 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
4626 p_api_name => l_api_name,
4627 p_pkg_name => g_pkg_name,
4628 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
4629 x_msg_count => x_msg_count,
4630 x_msg_data => x_msg_data,
4631 p_api_type => g_api_type);
4632
4633 when OKE_API.G_EXCEPTION_UNEXPECTED_ERROR then
4634 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
4635 p_api_name => l_api_name,
4636 p_pkg_name => g_pkg_name,
4637 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
4638 x_msg_count => x_msg_count,
4639 x_msg_data => x_msg_data,
4640 p_api_type => g_api_type);
4641
4642 when OTHERS then
4643 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
4644 p_api_name => l_api_name,
4645 p_pkg_name => g_pkg_name,
4646 p_exc_name => 'OTHERS',
4647 x_msg_count => x_msg_count,
4648 x_msg_data => x_msg_data,
4649 p_api_type => g_api_type);
4650
4651 END Delete_Contract;
4652
4653
4654
4655
4656
4657 END OKE_CONTRACT_PUB;