[Home] [Help]
PACKAGE BODY: APPS.OKC_DOCLIST_PVT
Source
1 PACKAGE BODY OKC_DOCLIST_PVT AS
2 /*$Header: OKCRUBLB.pls 120.0 2005/05/25 19:14:14 appldev noship $*/
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 --===================
7 -- TYPES
8 --===================
9 -- add your type declarations here if any
10 --
11
12
13
14 --===================
15 -- PACKAGE CONSTANTS
16 --===================
17 --
18 x_msg_count NUMBER;
19 x_msg_data VARCHAR2(2000);
20 x_return_status VARCHAR2(1);
21
22 --===================
26 PROCEDURE process_row (
23 -- LOCAL PROCEDURES AND FUNCTIONS
24 --===================
25 --
27 row_type IN VARCHAR2
28 ,x_return_status OUT NOCOPY VARCHAR2
29 ,x_msg_count OUT NOCOPY NUMBER
30 ,x_msg_data OUT NOCOPY VARCHAR2 )
31 IS
32
33 CURSOR ubl_csr_type (cp_program IN VARCHAR2
34 ,cp_user IN NUMBER
35 ,cp_type IN VARCHAR2 ) IS
36 SELECT id, creation_date, contract_id
37 FROM okc_user_bins
38 WHERE program_name = cp_program
39 AND created_by = cp_user
40 AND bin_type = cp_type
41 ORDER BY creation_date;
42
43 TYPE id_table_type IS TABLE OF okc_user_bins.id%TYPE INDEX BY BINARY_INTEGER;
44 TYPE cdate_tbl_type IS TABLE OF okc_user_bins.creation_date%TYPE INDEX BY BINARY_INTEGER;
45 TYPE cid_tbl_type IS TABLE OF okc_user_bins.contract_id%TYPE INDEX BY BINARY_INTEGER;
46
47 l_api_name VARCHAR2(20) := 'PROCESS_ROW';
48 l_dups_deleted NUMBER := 0;
49 l_fifos_deleted NUMBER := 0;
50
51 l_return_status VARCHAR2(1) := okc_api.g_ret_sts_success;
52 l_msg_count NUMBER := 0;
53 l_msg_data VARCHAR2(2000);
54 bin_rec ubl_csr_type%ROWTYPE;
55 id_tab id_table_type;
56 cdate_tab cdate_tbl_type;
57 cid_tab cid_tbl_type;
58 i NUMBER := 0;
59 BEGIN
60
61 --
62 -- Start the logical activity...
63 --
64 x_return_status := OKC_API.start_activity (
65 P_API_NAME => l_api_name
66 ,P_INIT_MSG_LIST => g_init_msg_list
67 ,P_API_TYPE => '_PVT'
68 ,X_RETURN_STATUS => x_return_status );
69
70 IF (x_return_status = okc_api.g_ret_sts_unexp_error) THEN
71 RAISE okc_api.g_exception_unexpected_error;
72 ELSIF (x_return_status = okc_api.g_ret_sts_error) THEN
73 RAISE okc_api.g_exception_error;
74 END IF;
75
76 --
77 -- validate the record before calling insert
78 --
79 okc_ubl_pvt.validate_row (
80 p_api_version => g_package_version
81 ,p_init_msg_list => g_init_msg_list
82 ,x_return_status => l_return_status
83 ,x_msg_count => l_msg_count
84 ,x_msg_data => l_msg_data
85 ,p_ubnv_rec => g_ubl_recin);
86
87 IF (x_return_status = okc_api.g_ret_sts_unexp_error) THEN
88 RAISE okc_api.g_exception_unexpected_error;
89 ELSIF (x_return_status = okc_api.g_ret_sts_error) THEN
90 RAISE okc_api.g_exception_error;
91 END IF;
92
93 --
94 -- insert if OK
95 --
96 okc_ubl_pvt.insert_row (
97 p_api_version => g_package_version
98 ,p_init_msg_list => g_init_msg_list
99 ,x_return_status => x_return_status
100 ,x_msg_count => x_msg_count
101 ,x_msg_data => x_msg_data
102 ,p_ubnv_rec => g_ubl_recin
103 ,x_ubnv_rec => g_ubl_recout );
104
105 IF (x_return_status = okc_api.g_ret_sts_unexp_error) THEN
106 RAISE okc_api.g_exception_unexpected_error;
107 ELSIF (x_return_status = okc_api.g_ret_sts_error) THEN
108 RAISE okc_api.g_exception_error;
109 END IF;
110 --*********Entire logic Modified by MKS
111 --
112 -- check if a duplicate has been inserted. Duplicate is same contract, same type, same user
113 -- If exists, delete the duplicate and continue checking for rows to trim from the list length
114 --
115 IF ROW_TYPE in ('BOOKMARK', 'RECENT') THEN
116 OPEN ubl_csr_type (g_ubl_recout.program_name, g_ubl_recout.created_by, g_ubl_recout.bin_type);
117 -- FETCH ubl_csr_type BULK COLLECT INTO id_tab, cdate_tab, cid_tab;
118 i := 1;
119 LOOP
120 FETCH ubl_csr_type INTO id_tab(i), cdate_tab(i), cid_tab(i);
121 EXIT WHEN ubl_csr_type%NOTFOUND;
122 i := i+1;
123 END LOOP;
124 CLOSE ubl_csr_type;
125 x_return_status := okc_api.g_ret_sts_success;
126 l_dups_deleted := 0;
127 l_fifos_deleted := 0;
128 i := 0;
129
130 FOR i in id_tab.FIRST..id_tab.LAST LOOP
131 --
132 -- check if a duplicate
133 --
134 -- dbms_output.put_line('checking Dups:' ||id_tab.LAST ||'*'|| l_dups_deleted ||'*'|| l_fifos_deleted ||'*'|| g_max_list_length );
135 -- Because of the following logic, there can be at most one duplicate at a time. Therefore exit after finding the duplicate
136 IF cid_tab(i) = g_ubl_recout.contract_id
137 AND cdate_tab(i) <> g_ubl_recout.creation_date THEN
138 delete_entry ( p_ubl_id => id_tab(i)
139 ,x_return_status => x_return_status
140 ,x_msg_count => x_msg_count
141 ,x_msg_data => x_msg_data);
142 IF (x_return_status = okc_api.g_ret_sts_unexp_error) THEN
143 RAISE okc_api.g_exception_unexpected_error;
144 ELSIF (x_return_status = okc_api.g_ret_sts_error) THEN
145 RAISE okc_api.g_exception_error;
146 END IF;
147 l_dups_deleted := l_dups_deleted + 1;
148 id_tab.DELETE(i);
149 cid_tab.DELETE(i);
150 cdate_tab.DELETE(i);
151 exit;
152 END IF;
153 END LOOP;
154 IF ROW_TYPE = 'RECENT' THEN
155 --
156 -- delete rows beyond list length
157 --
158 IF (id_tab.COUNT - l_dups_deleted ) > g_max_list_length THEN
159 --FOR i in id_tab.FIRST..id_tab.LAST LOOP
160 --dbms_output.put_line('checking LIFOs:' ||id_tab.COUNT ||'*'|| l_dups_deleted ||'*'|| l_fifos_deleted ||'*'|| g_max_list_length );
161 --IF (id_tab.COUNT - l_dups_deleted - l_fifos_deleted) > g_max_list_length THEN
162 -- delete_entry ( p_ubl_id => id_tab(i)
163 delete_entry ( p_ubl_id => id_tab(id_tab.FIRST)
164 ,x_return_status => x_return_status
165 ,x_msg_count => x_msg_count
166 ,x_msg_data => x_msg_data);
167 IF (x_return_status = okc_api.g_ret_sts_unexp_error) THEN
168 RAISE okc_api.g_exception_unexpected_error;
169 ELSIF (x_return_status = okc_api.g_ret_sts_error) THEN
170 RAISE okc_api.g_exception_error;
171 END IF;
172 -- l_fifos_deleted := l_fifos_deleted + 1;
173 -- END IF;
174 -- END LOOP;
175 END IF;
176 END IF;
177 END IF;
178
179 --********* End of Mod
180 --
181 -- close the activity
182 --
183 okc_api.end_activity (
184 x_msg_count => x_msg_count
185 ,x_msg_data => x_msg_data);
186
187
188 EXCEPTION
189 WHEN okc_api.g_exception_error THEN
190 IF ubl_csr_type%ISOPEN THEN
191 close ubl_csr_type;
192 END IF;
193 x_return_status := okc_api.handle_exceptions (
194 p_api_name => l_api_name
195 ,p_pkg_name => g_package_name
196 ,p_exc_name => 'OKC_API.g_ret_sts_error'
197 ,x_msg_count => x_msg_count
198 ,x_msg_data => x_msg_data
199 ,p_api_type => '_PVT' );
200
201 WHEN okc_api.g_exception_unexpected_error THEN
202 IF ubl_csr_type%ISOPEN THEN
203 close ubl_csr_type;
204 END IF;
205 x_return_status := okc_api.handle_exceptions (
206 p_api_name => l_api_name
207 ,p_pkg_name => g_package_name
208 ,p_exc_name => 'OKC_API.g_ret_sts_unexp_error'
209 ,x_msg_count => x_msg_count
210 ,x_msg_data => x_msg_data
211 ,p_api_type => '_PVT' );
212 WHEN OTHERS THEN
213 IF ubl_csr_type%ISOPEN THEN
214 close ubl_csr_type;
215 END IF;
216 x_return_status := okc_api.handle_exceptions (
217 p_api_name => l_api_name
218 ,p_pkg_name => g_package_name
219 ,p_exc_name => 'OTHERS'
220 ,x_msg_count => x_msg_count
221 ,x_msg_data => x_msg_data
222 ,p_api_type => '_PVT' );
223
224 END Process_row;
225
226 --===================
227 -- PACKAGE PROCEDURES AND FUNCTIONS
228 --===================
229 --
230
231 --
232 -- ---------------------------------------------------------------------------------
233 -- PROCEDURE: Add_Recent --
234 -- DESCRIPTION: Adds a recent document row (type R) to the okc_user_bins table --
235 -- using the record passed --
236 -- DEPENDENCIES: none --
237 -- CHANGE HISTORY: --
238 -- 12/aug/99 - created --
239 -- --
240 -- ---------------------------------------------------------------------------------
241 --
242 PROCEDURE add_recent (
243 p_contract_id IN OKC_K_HEADERS_B.id%TYPE
244 ,p_contract_number IN OKC_K_HEADERS_B.contract_number%TYPE
245 ,p_contract_type IN OKC_K_HEADERS_B.chr_type%TYPE
246 ,p_contract_modifier IN OKC_K_HEADERS_B.contract_number_modifier%TYPE
247 ,p_short_description IN OKC_K_HEADERS_TL.short_description%TYPE
248 ,p_program_name IN OKC_USER_BINS.program_name%TYPE
249 ,x_return_status OUT NOCOPY VARCHAR2
250 ,x_msg_count OUT NOCOPY NUMBER
251 ,x_msg_data OUT NOCOPY VARCHAR2 )
252 IS
253 PRAGMA AUTONOMOUS_TRANSACTION;
254
255 l_api_name VARCHAR2(20) := 'ADD_RECENT';
256
257 BEGIN
258 --
259 -- move the passed parameters to a record structure and process
260 --
261 g_ubl_recin.id := 0;
262 g_ubl_recin.contract_id := p_contract_id;
263 g_ubl_recin.contract_number := p_contract_number;
264 g_ubl_recin.contract_type := p_contract_type;
265 g_ubl_recin.contract_number_modifier := p_contract_modifier;
266 g_ubl_recin.program_name := p_program_name;
267 g_ubl_recin.short_description := p_short_description;
268 g_ubl_recin.bin_type := g_recent_type;
269 g_ubl_recin.created_by := 0;
270 g_ubl_recin.creation_date := null;
271
272 x_return_status := okc_api.g_ret_sts_success;
273 Process_row (
274 row_type => 'RECENT'
275 ,x_return_status => x_return_status
276 ,x_msg_count => x_msg_count
277 ,x_msg_data => x_msg_data );
278
279 IF (x_return_status = okc_api.g_ret_sts_unexp_error) THEN
280 RAISE okc_api.g_exception_unexpected_error;
281 ELSIF (x_return_status = okc_api.g_ret_sts_error) THEN
282 RAISE okc_api.g_exception_error;
283 END IF;
284
285 COMMIT;
286
287 EXCEPTION
288 WHEN okc_api.g_exception_error THEN
289 x_return_status := okc_api.handle_exceptions (
290 p_api_name => l_api_name
291 ,p_pkg_name => g_package_name
292 ,p_exc_name => 'OKC_API.g_ret_sts_error'
293 ,x_msg_count => x_msg_count
294 ,x_msg_data => x_msg_data
295 ,p_api_type => '_PVT' );
296
297 WHEN okc_api.g_exception_unexpected_error THEN
298 x_return_status := okc_api.handle_exceptions (
299 p_api_name => l_api_name
300 ,p_pkg_name => g_package_name
301 ,p_exc_name => 'OKC_API.g_ret_sts_unexp_error'
302 ,x_msg_count => x_msg_count
303 ,x_msg_data => x_msg_data
304 ,p_api_type => '_PVT' );
305 WHEN OTHERS THEN
306 x_return_status := okc_api.handle_exceptions (
307 p_api_name => l_api_name
308 ,p_pkg_name => g_package_name
309 ,p_exc_name => 'OTHERS'
310 ,x_msg_count => x_msg_count
311 ,x_msg_data => x_msg_data
312 ,p_api_type => '_PVT' );
313
314 END add_recent;
315
316
317
318 --
319 -- ---------------------------------------------------------------------------------
320 -- PROCEDURE: Add_Bookmark --
321 -- DESCRIPTION: Inserts a row for a bookmarked contract (Type B) into --
322 -- okc_user_bins using the row record passed --
323 -- DEPENDENCIES: none --
324 -- CHANGE HISTORY: --
325 -- 12/aug/99 - created --
326 -- --
327 -- ---------------------------------------------------------------------------------
328 --
329 PROCEDURE add_bookmark (
330 p_contract_id IN OKC_K_HEADERS_B.id%TYPE
331 ,p_contract_number IN OKC_K_HEADERS_B.contract_number%TYPE
332 ,p_contract_type IN OKC_K_HEADERS_B.chr_type%TYPE
333 ,p_contract_modifier IN OKC_K_HEADERS_B.contract_number_modifier%TYPE
334 ,p_short_description IN OKC_K_HEADERS_TL.short_description%TYPE
335 ,p_program_name IN VARCHAR2
336 ,x_return_status OUT NOCOPY VARCHAR2
337 ,x_msg_count OUT NOCOPY NUMBER
338 ,x_msg_data OUT NOCOPY VARCHAR2 )
339 IS
340 PRAGMA AUTONOMOUS_TRANSACTION;
341
342 l_api_name VARCHAR2(20) := 'ADD_BOOKMARK';
343
344 BEGIN
345 --
346 -- move the passed parameters to a record structure and process
347 --
348 g_ubl_recin.id := 0;
349 g_ubl_recin.contract_id := p_contract_id;
350 g_ubl_recin.contract_number := p_contract_number;
351 g_ubl_recin.contract_type := p_contract_type;
352 g_ubl_recin.contract_number_modifier := p_contract_modifier;
353 g_ubl_recin.program_name := p_program_name;
354 g_ubl_recin.short_description := p_short_description;
355 g_ubl_recin.bin_type := g_bookmark_type;
356 g_ubl_recin.created_by := 0;
357 g_ubl_recin.creation_date := null;
358
359 x_return_status := okc_api.g_ret_sts_success;
360 process_row (
361 row_type => 'BOOKMARK'
362 ,x_return_status => x_return_status
363 ,x_msg_count => x_msg_count
364 ,x_msg_data => x_msg_data);
365
366 /* IF (x_return_status = okc_api.g_ret_sts_unexp_error) THEN
367 RAISE okc_api.g_exception_unexpected_error;
368 ELSIF (x_return_status = okc_api.g_ret_sts_error) THEN
369 RAISE okc_api.g_exception_error;
370 END IF;
371 */
372
373 IF (x_return_status = okc_api.g_ret_sts_success) THEN
374 COMMIT;
375 END IF;
376
377 EXCEPTION
378 WHEN okc_api.g_exception_error THEN
379 x_return_status := okc_api.handle_exceptions (
380 p_api_name => l_api_name
381 ,p_pkg_name => g_package_name
382 ,p_exc_name => 'OKC_API.g_ret_sts_error'
383 ,x_msg_count => x_msg_count
384 ,x_msg_data => x_msg_data
385 ,p_api_type => '_PVT' );
386
387 WHEN okc_api.g_exception_unexpected_error THEN
388 x_return_status := okc_api.handle_exceptions (
389 p_api_name => l_api_name
390 ,p_pkg_name => g_package_name
391 ,p_exc_name => 'OKC_API.g_ret_sts_unexp_error'
392 ,x_msg_count => x_msg_count
393 ,x_msg_data => x_msg_data
394 ,p_api_type => '_PVT' );
395 WHEN OTHERS THEN
396 x_return_status := okc_api.handle_exceptions (
397 p_api_name => l_api_name
398 ,p_pkg_name => g_package_name
399 ,p_exc_name => 'OTHERS'
400 ,x_msg_count => x_msg_count
401 ,x_msg_data => x_msg_data
402 ,p_api_type => '_PVT' );
403
404 END add_bookmark;
405
406
407 --
408 -- ---------------------------------------------------------------------------------
409 -- PROCEDURE: Delete_Entry --
410 -- DESCRIPTION: Deletes the row identified in the record passed --
411 -- --
412 -- DEPENDENCIES: none --
413 -- CHANGE HISTORY: --
414 -- 12/aug/99 - created --
415 -- --
416 -- ---------------------------------------------------------------------------------
417 --
418 PROCEDURE delete_entry (
419 p_ubl_id IN OKC_USER_BINS.id%TYPE
420 ,x_return_status OUT NOCOPY VARCHAR2
421 ,x_msg_count OUT NOCOPY NUMBER
422 ,x_msg_data OUT NOCOPY VARCHAR2 )
423 IS
424 PRAGMA AUTONOMOUS_TRANSACTION;
425
426 l_api_name VARCHAR2(30) := 'DELETE_ENTRY';
427
428 BEGIN
429 --
430 -- set-up the record structure then call delete
431 --
432 g_ubl_recin.id := p_ubl_id;
433 g_ubl_recin.contract_id := null;
434 g_ubl_recin.contract_number := null;
435 g_ubl_recin.contract_type := null;
436 g_ubl_recin.contract_number_modifier :=null;
437 g_ubl_recin.program_name := null;
438 g_ubl_recin.short_description := null;
439 g_ubl_recin.bin_type := null;
440 g_ubl_recin.created_by := null;
441 g_ubl_recin.creation_date := null;
442
443 okc_ubl_pvt.delete_row (
444 p_api_version => g_package_version
445 ,p_init_msg_list => g_init_msg_list
446 ,x_return_status => x_return_status
447 ,x_msg_count => x_msg_count
448 ,x_msg_data => x_msg_data
449 ,p_ubnv_rec => g_ubl_recin );
450
451 /* IF (x_return_status = okc_api.g_ret_sts_unexp_error) THEN
452 RAISE okc_api.g_exception_unexpected_error;
453 ELSIF (x_return_status = okc_api.g_ret_sts_error) THEN
454 RAISE okc_api.g_exception_error;
455 END IF;
456 */
457 IF (x_return_status = okc_api.g_ret_sts_success) THEN
458 COMMIT;
459 END IF;
460
461 EXCEPTION
462 WHEN okc_api.g_exception_error THEN
463 x_return_status := okc_api.handle_exceptions (
464 p_api_name => l_api_name
465 ,p_pkg_name => g_package_name
466 ,p_exc_name => 'OKC_API.g_ret_sts_error'
467 ,x_msg_count => x_msg_count
468 ,x_msg_data => x_msg_data
469 ,p_api_type => '_PVT' );
470
471 WHEN okc_api.g_exception_unexpected_error THEN
472 x_return_status := okc_api.handle_exceptions (
473 p_api_name => l_api_name
474 ,p_pkg_name => g_package_name
475 ,p_exc_name => 'OKC_API.g_ret_sts_unexp_error'
476 ,x_msg_count => x_msg_count
477 ,x_msg_data => x_msg_data
478 ,p_api_type => '_PVT' );
479 WHEN OTHERS THEN
480 x_return_status := okc_api.handle_exceptions (
481 p_api_name => l_api_name
482 ,p_pkg_name => g_package_name
483 ,p_exc_name => 'OTHERS'
484 ,x_msg_count => x_msg_count
485 ,x_msg_data => x_msg_data
486 ,p_api_type => '_PVT' );
487
488 END delete_entry;
489
490 END OKC_DOCLIST_PVT;