[Home] [Help]
PACKAGE BODY: APPS.OKS_CONTRACT_SLL_PUB
Source
1 PACKAGE BODY oks_contract_sll_pub AS
2 /* $Header: OKSPSLLB.pls 120.0 2005/05/25 18:31:51 appldev noship $ */
3 PROCEDURE create_sll(
4 p_api_version IN NUMBER,
5 p_init_msg_list IN VARCHAR2,
6 x_return_status OUT NOCOPY VARCHAR2,
7 x_msg_count OUT NOCOPY NUMBER,
8 x_msg_data OUT NOCOPY VARCHAR2,
9 p_sllv_rec IN sllv_rec_type,
10 x_sllv_rec OUT NOCOPY sllv_rec_type,
11 p_validate_yn IN VARCHAR2) IS
12
13 l_init_msg_list VARCHAR2(10);
14 BEGIN
15 x_return_status := G_RET_STS_SUCCESS;
16 IF p_validate_yn = 'Y' THEN
17 validate_sll
18 (
19 p_api_version => p_api_version,
20 p_init_msg_list => l_init_msg_list,
21 x_return_status => x_return_status,
22 x_msg_count => x_msg_count,
23 x_msg_data => x_msg_data,
24 p_sllv_rec => p_sllv_rec
25 );
26 END IF;
27 IF x_return_status = G_RET_STS_SUCCESS THEN
28 oks_sll_pvt.insert_row
29 (
30 p_api_version => p_api_version,
31 p_init_msg_list => p_init_msg_list,
32 x_return_status => x_return_status,
33 x_msg_count => x_msg_count,
34 x_msg_data => x_msg_data,
35 p_sllv_rec => p_sllv_rec,
36 x_sllv_rec => x_sllv_rec
37 );
38 END IF;
39 END create_sll;
40
41 PROCEDURE create_sll(
42 p_api_version IN NUMBER,
43 p_init_msg_list IN VARCHAR2,
44 x_return_status OUT NOCOPY VARCHAR2,
45 x_msg_count OUT NOCOPY NUMBER,
46 x_msg_data OUT NOCOPY VARCHAR2,
47 p_sllv_tbl IN sllv_tbl_type,
48 x_sllv_tbl OUT NOCOPY sllv_tbl_type,
49 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE,
50 p_validate_yn IN VARCHAR2) IS
51 BEGIN
52 x_return_status := G_RET_STS_SUCCESS;
53 IF p_validate_yn = 'Y' THEN
54 validate_sll
55 (
56 p_api_version => p_api_version,
57 p_init_msg_list => p_init_msg_list,
58 x_return_status => x_return_status,
59 x_msg_count => x_msg_count,
60 x_msg_data => x_msg_data,
61 p_sllv_tbl => p_sllv_tbl,
62 px_error_tbl => px_error_tbl
63 );
64 END IF;
65 IF x_return_status = G_RET_STS_SUCCESS THEN
66 oks_sll_pvt.insert_row
67 (
68 p_api_version => p_api_version,
69 p_init_msg_list => p_init_msg_list,
70 x_return_status => x_return_status,
71 x_msg_count => x_msg_count,
72 x_msg_data => x_msg_data,
73 p_sllv_tbl => p_sllv_tbl,
74 x_sllv_tbl => x_sllv_tbl,
75 px_error_tbl => px_error_tbl
76 );
77 END IF;
78 END create_sll;
79
80 PROCEDURE create_sll(
81 p_api_version IN NUMBER,
82 p_init_msg_list IN VARCHAR2,
83 x_return_status OUT NOCOPY VARCHAR2,
84 x_msg_count OUT NOCOPY NUMBER,
85 x_msg_data OUT NOCOPY VARCHAR2,
86 p_sllv_tbl IN sllv_tbl_type,
87 x_sllv_tbl OUT NOCOPY sllv_tbl_type,
88 p_validate_yn IN VARCHAR2) IS
89 BEGIN
90 x_return_status := G_RET_STS_SUCCESS;
91 IF p_validate_yn = 'Y' THEN
92 validate_sll
93 (
94 p_api_version => p_api_version,
95 p_init_msg_list => p_init_msg_list,
96 x_return_status => x_return_status,
97 x_msg_count => x_msg_count,
98 x_msg_data => x_msg_data,
99 p_sllv_tbl => p_sllv_tbl
100 );
101 END IF;
102 IF x_return_status = G_RET_STS_SUCCESS THEN
103 oks_sll_pvt.insert_row
104 (
105 p_api_version => p_api_version,
106 p_init_msg_list => p_init_msg_list,
107 x_return_status => x_return_status,
108 x_msg_count => x_msg_count,
109 x_msg_data => x_msg_data,
110 p_sllv_tbl => p_sllv_tbl,
111 x_sllv_tbl => x_sllv_tbl
112 );
113 END IF;
114 END create_sll;
115
116 PROCEDURE update_sll(
117 p_api_version IN NUMBER,
118 p_init_msg_list IN VARCHAR2,
119 x_return_status OUT NOCOPY VARCHAR2,
120 x_msg_count OUT NOCOPY NUMBER,
121 x_msg_data OUT NOCOPY VARCHAR2,
122 p_sllv_rec IN sllv_rec_type,
123 x_sllv_rec OUT NOCOPY sllv_rec_type,
124 p_validate_yn IN VARCHAR2) IS
125 BEGIN
126 x_return_status := G_RET_STS_SUCCESS;
127 IF p_validate_yn = 'Y' THEN
128 validate_sll
129 (
130 p_api_version => p_api_version,
131 p_init_msg_list => p_init_msg_list,
132 x_return_status => x_return_status,
133 x_msg_count => x_msg_count,
134 x_msg_data => x_msg_data,
135 p_sllv_rec => p_sllv_rec
136 );
137 END IF;
138 IF x_return_status = G_RET_STS_SUCCESS THEN
139 oks_sll_pvt.update_row
140 (
141 p_api_version => p_api_version,
142 p_init_msg_list => p_init_msg_list,
143 x_return_status => x_return_status,
144 x_msg_count => x_msg_count,
145 x_msg_data => x_msg_data,
146 p_sllv_rec => p_sllv_rec,
147 x_sllv_rec => x_sllv_rec
148 );
149 END IF;
150 END update_sll;
151
152 PROCEDURE update_sll(
153 p_api_version IN NUMBER,
154 p_init_msg_list IN VARCHAR2,
155 x_return_status OUT NOCOPY VARCHAR2,
156 x_msg_count OUT NOCOPY NUMBER,
157 x_msg_data OUT NOCOPY VARCHAR2,
158 p_sllv_tbl IN sllv_tbl_type,
159 x_sllv_tbl OUT NOCOPY sllv_tbl_type,
160 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE,
161 p_validate_yn IN VARCHAR2) IS
162 BEGIN
163 x_return_status := G_RET_STS_SUCCESS;
164 IF p_validate_yn = 'Y' THEN
165 validate_sll
166 (
167 p_api_version => p_api_version,
168 p_init_msg_list => p_init_msg_list,
169 x_return_status => x_return_status,
170 x_msg_count => x_msg_count,
171 x_msg_data => x_msg_data,
172 p_sllv_tbl => p_sllv_tbl,
173 px_error_tbl => px_error_tbl
174 );
175 END IF;
176 IF x_return_status = G_RET_STS_SUCCESS THEN
177 oks_sll_pvt.update_row
178 (
179 p_api_version => p_api_version,
180 p_init_msg_list => p_init_msg_list,
181 x_return_status => x_return_status,
182 x_msg_count => x_msg_count,
183 x_msg_data => x_msg_data,
184 p_sllv_tbl => p_sllv_tbl,
185 x_sllv_tbl => x_sllv_tbl,
186 px_error_tbl => px_error_tbl
187 );
188 END IF;
189 END update_sll;
190
191 PROCEDURE update_sll(
192 p_api_version IN NUMBER,
193 p_init_msg_list IN VARCHAR2,
194 x_return_status OUT NOCOPY VARCHAR2,
195 x_msg_count OUT NOCOPY NUMBER,
196 x_msg_data OUT NOCOPY VARCHAR2,
197 p_sllv_tbl IN sllv_tbl_type,
198 x_sllv_tbl OUT NOCOPY sllv_tbl_type,
199 p_validate_yn IN VARCHAR2) IS
200 BEGIN
201 x_return_status := G_RET_STS_SUCCESS;
202 IF p_validate_yn = 'Y' THEN
203 validate_sll
204 (
205 p_api_version => p_api_version,
206 p_init_msg_list => p_init_msg_list,
207 x_return_status => x_return_status,
208 x_msg_count => x_msg_count,
209 x_msg_data => x_msg_data,
210 p_sllv_tbl => p_sllv_tbl
211 );
212 END IF;
213 IF x_return_status = G_RET_STS_SUCCESS THEN
214 oks_sll_pvt.update_row
215 (
216 p_api_version => p_api_version,
217 p_init_msg_list => p_init_msg_list,
218 x_return_status => x_return_status,
219 x_msg_count => x_msg_count,
220 x_msg_data => x_msg_data,
221 p_sllv_tbl => p_sllv_tbl,
222 x_sllv_tbl => x_sllv_tbl
223 );
224 END IF;
225 END update_sll;
226
227 PROCEDURE lock_sll(
228 p_api_version IN NUMBER,
229 p_init_msg_list IN VARCHAR2,
230 x_return_status OUT NOCOPY VARCHAR2,
231 x_msg_count OUT NOCOPY NUMBER,
232 x_msg_data OUT NOCOPY VARCHAR2,
233 p_sllv_rec IN sllv_rec_type) IS
234 BEGIN
235 oks_sll_pvt.lock_row
236 (
237 p_api_version => p_api_version,
238 p_init_msg_list => p_init_msg_list,
239 x_return_status => x_return_status,
240 x_msg_count => x_msg_count,
241 x_msg_data => x_msg_data,
242 p_sllv_rec => p_sllv_rec
243 );
244 END lock_sll;
245
246 PROCEDURE lock_sll(
247 p_api_version IN NUMBER,
248 p_init_msg_list IN VARCHAR2,
249 x_return_status OUT NOCOPY VARCHAR2,
250 x_msg_count OUT NOCOPY NUMBER,
251 x_msg_data OUT NOCOPY VARCHAR2,
252 p_sllv_tbl IN sllv_tbl_type,
253 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
254 BEGIN
255 oks_sll_pvt.lock_row
256 (
257 p_api_version => p_api_version,
258 p_init_msg_list => p_init_msg_list,
259 x_return_status => x_return_status,
260 x_msg_count => x_msg_count,
261 x_msg_data => x_msg_data,
262 p_sllv_tbl => p_sllv_tbl,
263 px_error_tbl => px_error_tbl
264 );
265 END lock_sll;
266
267 PROCEDURE lock_sll(
268 p_api_version IN NUMBER,
269 p_init_msg_list IN VARCHAR2,
270 x_return_status OUT NOCOPY VARCHAR2,
271 x_msg_count OUT NOCOPY NUMBER,
272 x_msg_data OUT NOCOPY VARCHAR2,
273 p_sllv_tbl IN sllv_tbl_type) IS
274 BEGIN
275 oks_sll_pvt.lock_row
276 (
277 p_api_version => p_api_version,
278 p_init_msg_list => p_init_msg_list,
279 x_return_status => x_return_status,
280 x_msg_count => x_msg_count,
281 x_msg_data => x_msg_data,
282 p_sllv_tbl => p_sllv_tbl
283 );
284 END lock_sll;
285
286 PROCEDURE delete_sll(
287 p_api_version IN NUMBER,
288 p_init_msg_list IN VARCHAR2,
289 x_return_status OUT NOCOPY VARCHAR2,
290 x_msg_count OUT NOCOPY NUMBER,
291 x_msg_data OUT NOCOPY VARCHAR2,
292 p_sllv_rec IN sllv_rec_type) IS
293 BEGIN
294 oks_sll_pvt.delete_row
295 (
296 p_api_version => p_api_version,
297 p_init_msg_list => p_init_msg_list,
298 x_return_status => x_return_status,
299 x_msg_count => x_msg_count,
300 x_msg_data => x_msg_data,
301 p_sllv_rec => p_sllv_rec
302 );
303 END delete_sll;
304
305 PROCEDURE delete_sll(
306 p_api_version IN NUMBER,
307 p_init_msg_list IN VARCHAR2,
308 x_return_status OUT NOCOPY VARCHAR2,
309 x_msg_count OUT NOCOPY NUMBER,
310 x_msg_data OUT NOCOPY VARCHAR2,
311 p_sllv_tbl IN sllv_tbl_type,
312 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
313 BEGIN
314 oks_sll_pvt.delete_row
315 (
316 p_api_version => p_api_version,
317 p_init_msg_list => p_init_msg_list,
318 x_return_status => x_return_status,
319 x_msg_count => x_msg_count,
320 x_msg_data => x_msg_data,
321 p_sllv_tbl => p_sllv_tbl,
322 px_error_tbl => px_error_tbl
323 );
324 END delete_sll;
325
326 PROCEDURE delete_sll(
327 p_api_version IN NUMBER,
328 p_init_msg_list IN VARCHAR2,
329 x_return_status OUT NOCOPY VARCHAR2,
330 x_msg_count OUT NOCOPY NUMBER,
331 x_msg_data OUT NOCOPY VARCHAR2,
332 p_sllv_tbl IN sllv_tbl_type) IS
333 BEGIN
334 oks_sll_pvt.delete_row
335 (
336 p_api_version => p_api_version,
337 p_init_msg_list => p_init_msg_list,
338 x_return_status => x_return_status,
339 x_msg_count => x_msg_count,
340 x_msg_data => x_msg_data,
341 p_sllv_tbl => p_sllv_tbl
342 );
343 END delete_sll;
344
345 PROCEDURE validate_sll(
346 p_api_version IN NUMBER,
347 p_init_msg_list IN VARCHAR2,
348 x_return_status OUT NOCOPY VARCHAR2,
349 x_msg_count OUT NOCOPY NUMBER,
350 x_msg_data OUT NOCOPY VARCHAR2,
351 p_sllv_rec IN sllv_rec_type) IS
352 BEGIN
353 x_return_status := G_RET_STS_SUCCESS;
354 END validate_sll;
355
356 PROCEDURE validate_sll(
357 p_api_version IN NUMBER,
358 p_init_msg_list IN VARCHAR2,
359 x_return_status OUT NOCOPY VARCHAR2,
360 x_msg_count OUT NOCOPY NUMBER,
361 x_msg_data OUT NOCOPY VARCHAR2,
362 p_sllv_tbl IN sllv_tbl_type,
363 px_error_tbl IN OUT NOCOPY OKC_API.ERROR_TBL_TYPE) IS
364 BEGIN
365 x_return_status := G_RET_STS_SUCCESS;
366 END validate_sll;
367
368 PROCEDURE validate_sll(
369 p_api_version IN NUMBER,
370 p_init_msg_list IN VARCHAR2,
371 x_return_status OUT NOCOPY VARCHAR2,
372 x_msg_count OUT NOCOPY NUMBER,
373 x_msg_data OUT NOCOPY VARCHAR2,
374 p_sllv_tbl IN sllv_tbl_type) IS
375 BEGIN
376 x_return_status := G_RET_STS_SUCCESS;
377 END validate_sll;
378
379 PROCEDURE INSERT_ROW_UPG(x_return_status OUT NOCOPY VARCHAR2,
380 p_sllv_tbl IN sllv_tbl_type) IS
381
382 l_tabsize NUMBER := p_sllv_tbl.COUNT;
383 in_id OKC_DATATYPES.NumberTabTyp;
384 in_chr_id OKC_DATATYPES.NumberTabTyp;
385 in_cle_id OKC_DATATYPES.NumberTabTyp;
386 in_dnz_chr_id OKC_DATATYPES.NumberTabTyp;
387 in_sequence_no OKC_DATATYPES.NumberTabTyp;
388 in_uom_code OKC_DATATYPES.Var3TabTyp;
389 in_start_date OKC_DATATYPES.DateTabTyp;
390 in_end_date OKC_DATATYPES.DateTabTyp;
391 in_level_periods OKC_DATATYPES.NumberTabTyp;
392 in_uom_per_period OKC_DATATYPES.NumberTabTyp;
393 in_advance_periods OKC_DATATYPES.NumberTabTyp;
394 in_level_amount OKC_DATATYPES.NumberTabTyp;
395 in_invoice_offset_days OKC_DATATYPES.NumberTabTyp;
396 in_interface_offset_days OKC_DATATYPES.NumberTabTyp;
397 in_comments OKC_DATATYPES.Var1995TabTyp;
398 in_due_arr_yn OKC_DATATYPES.Var3TabTyp;
399 in_amount OKC_DATATYPES.NumberTabTyp;
400 in_lines_detailed_yn OKC_DATATYPES.Var3TabTyp;
401 in_object_version_number OKC_DATATYPES.NumberTabTyp;
402 in_request_id OKC_DATATYPES.NumberTabTyp;
403 in_created_by OKC_DATATYPES.Number15TabTyp;
404 in_creation_date OKC_DATATYPES.DateTabTyp;
405 in_last_updated_by OKC_DATATYPES.Number15TabTyp;
406 in_last_update_date OKC_DATATYPES.DateTabTyp;
407 in_last_update_login OKC_DATATYPES.Number15TabTyp;
408 i number;
409 j number;
410 Begin
411 -- Initialize return status
412 x_return_status := OKC_API.G_RET_STS_SUCCESS;
413
414 i := p_sllv_tbl.FIRST;
415 j:=0;
416
417 while i is not null
418 LOOP
419 j:= j+1;
420 in_id(j) := p_sllv_tbl(i).id;
421 in_chr_id(j) := p_sllv_tbl(i).chr_id;
422 in_cle_id(j) := p_sllv_tbl(i).cle_id;
423 in_dnz_chr_id(j) := p_sllv_tbl(i).dnz_chr_id;
424 in_sequence_no(j) := p_sllv_tbl(i).sequence_no;
425 in_uom_code(j) := p_sllv_tbl(i).uom_code;
426 in_start_date(j) := p_sllv_tbl(i).start_date;
427 in_end_date(j) := p_sllv_tbl(i).end_date;
428 in_level_periods(j) := p_sllv_tbl(i).level_periods;
429 in_uom_per_period(j) := p_sllv_tbl(i).uom_per_period;
430 in_advance_periods(j) := p_sllv_tbl(i).advance_periods;
431 in_level_amount(j) := p_sllv_tbl(i).level_amount;
432 in_invoice_offset_days(j) := p_sllv_tbl(i).invoice_offset_days;
433 in_interface_offset_days(j) := p_sllv_tbl(i).interface_offset_days;
434 in_comments(j) := p_sllv_tbl(i).comments;
435 in_due_arr_yn(j) := p_sllv_tbl(i).due_arr_yn;
436 in_amount(j) := p_sllv_tbl(i).amount;
437 in_lines_detailed_yn(j) := p_sllv_tbl(i).lines_detailed_yn;
438 in_object_version_number(j) := p_sllv_tbl(i).object_version_number;
439 in_request_id(j) := p_sllv_tbl(i).request_id;
440 in_created_by(j) := p_sllv_tbl(i).created_by;
441 in_creation_date(j) := p_sllv_tbl(i).creation_date;
442 in_last_updated_by(j) := p_sllv_tbl(i).last_updated_by;
443 in_last_update_date(j) := p_sllv_tbl(i).last_update_date;
444 in_last_update_login (j) := p_sllv_tbl(i).last_update_login;
445 i:= p_sllv_tbl.next(i);
446 End Loop;
447
448 FORALL i in 1..l_tabsize
449 INSERT
450 INTO OKS_STREAM_LEVELS_B(
451 id,
452 chr_id,
453 cle_id,
454 dnz_chr_id,
455 sequence_no,
456 uom_code,
457 start_date,
458 end_date,
459 level_periods,
460 uom_per_period,
461 advance_periods,
462 level_amount,
463 invoice_offset_days,
464 interface_offset_days,
465 comments,
466 due_arr_yn,
467 amount,
468 lines_detailed_yn,
469 object_version_number,
470 request_id,
471 created_by,
472 creation_date,
473 last_updated_by,
474 last_update_date,
475 last_update_login
476 ) VALUES (
477 in_id(i),
478 in_chr_id(i),
479 in_cle_id(i),
480 in_dnz_chr_id(i),
481 in_sequence_no(i),
482 in_uom_code(i),
483 in_start_date(i),
484 in_end_date(i),
485 in_level_periods(i),
486 in_uom_per_period(i),
487 in_advance_periods(i),
488 in_level_amount(i),
489 in_invoice_offset_days(i),
490 in_interface_offset_days(i),
491 in_comments(i),
492 in_due_arr_yn(i),
493 in_amount(i),
494 in_lines_detailed_yn(i),
495 in_object_version_number(i),
496 in_request_id(i),
497 in_created_by(i),
498 in_creation_date(i),
499 in_last_updated_by(i),
500 in_last_update_date(i),
501 in_last_update_login(i)
502 );
503
504 EXCEPTION
505 WHEN OTHERS THEN
506
507 -- store SQL error message on message stack
508 OKC_API.SET_MESSAGE(
509 p_app_name => G_APP_NAME,
510 p_msg_name => G_UNEXPECTED_ERROR,
511 p_token1 => G_SQLCODE_TOKEN,
512 p_token1_value => SQLCODE,
513 p_token2 => G_SQLERRM_TOKEN,
514 p_token2_value => SQLERRM);
515 -- notify caller of an error as UNEXPETED error
516 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
517
518 END INSERT_ROW_UPG;
519
520 END oks_contract_sll_pub;
521