[Home] [Help]
PACKAGE BODY: APPS.OKC_TERMS_DISP_PVT
Source
1 PACKAGE BODY OKC_TERMS_DISP_PVT AS
2 /* $Header: OKCVTERMSDISPB.pls 120.0 2005/05/25 19:33:17 appldev noship $ */
3 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
4 G_GLOBAL_TEMP_LOADED VARCHAR2(1) := 'N';
5
6 ---------------------------------------------------------------------------
7 -- GLOBAL MESSAGE CONSTANTS
8 ---------------------------------------------------------------------------
9 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
10 G_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := OKC_API.G_UNABLE_TO_RESERVE_REC;
11 G_RECORD_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_DELETED;
12 G_RECORD_CHANGED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_CHANGED;
13 G_RECORD_LOGICALLY_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_LOGICALLY_DELETED;
14 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_REQUIRED_VALUE;
15 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_INVALID_VALUE;
16 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_COL_NAME_TOKEN;
17 G_PARENT_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_PARENT_TABLE_TOKEN;
18 G_CHILD_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_CHILD_TABLE_TOKEN;
19 ---------------------------------------------------------------------------
20 -- GLOBAL CONSTANTS
21 ---------------------------------------------------------------------------
22 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_TERMS_DISP_PVT';
23 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
24 ------------------------------------------------------------------------------
25 -- GLOBAL CONSTANTS
26 ------------------------------------------------------------------------------
27 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
28 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
29
30 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
31 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
32 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
33
34 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_UNEXPECTED_ERROR';
35 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
36 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
37 ------------------------------------------------------------------------------
38 -- GLOBAL EXCEPTIONS
39 ------------------------------------------------------------------------------
40 E_Resource_Busy EXCEPTION;
41 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
42
43 --==================== INTERNAL PROCEDURES ============================
44
45 FUNCTION get_terms_display_order
46 ( p_terms_id IN NUMBER,
47 p_terms_type IN VARCHAR2)
48 RETURN VARCHAR2 IS
49 l_display_order VARCHAR2(2000);
50 CURSOR l_get_display_order IS
51 SELECT terms_display_order
52 FROM okc_terms_artsec_disp_temp
53 WHERE terms_id = p_terms_id
54 AND terms_type = p_terms_type
55 AND processed_flag = 'Y';
56
57 BEGIN
58
59 OPEN l_get_display_order;
60 FETCH l_get_display_order INTO l_display_order;
61 CLOSE l_get_display_order;
62
63 RETURN l_display_order;
64
65 END;
66
67 PROCEDURE populate_temp_tab
68 (
69 p_document_id IN NUMBER,
70 p_document_type IN VARCHAR2,
71 p_run_id IN VARCHAR2) IS
72 pragma AUTONOMOUS_TRANSACTION;
73 l_max_level NUMBER := 1;
74 l_display_order NUMBER := 1;
75 l_parent_disp_order VARCHAR2(2000) := '0';
76 l_old_parent_id NUMBER := 0;
77
78
79 CURSOR l_lvl1_scn_csr IS
80 SELECT document_id,
81 document_type,
82 terms_type,
83 terms_id,
84 parent_id,
85 terms_display_sequence,
86 terms_display_level,
87 LPAD(ROWNUM,5,'0') disp_order,
88 'Y',
89 runid
90 FROM okc_terms_artsec_disp_temp
91 WHERE document_id = p_document_id
92 AND document_type = p_document_type
93 AND processed_flag = 'N'
94 AND parent_id IS null
95 AND terms_type = 'SECTION'
96 ORDER BY terms_display_sequence ;
97
98
99 CURSOR l_terms_csr
100 ( lc_document_id IN NUMBER,
101 lc_document_type IN VARCHAR2,
102 lc_level IN NUMBER) IS
103 SELECT document_id,
104 document_type,
105 terms_type,
106 terms_id,
107 parent_id,
108 terms_display_level,
109 terms_display_sequence,
110 runid
111 FROM okc_terms_artsec_disp_temp t
112 WHERE document_id = lc_document_id
113 AND document_type = lc_document_type
114 AND processed_flag = 'N'
115 AND exists (SELECT 1
116 FROM okc_terms_artsec_disp_temp t1
117 WHERE t1.terms_type = 'SECTION'
118 AND t1.terms_id = t.parent_id
119 AND t1.document_id = lc_document_id
120 AND t1.document_type = lc_document_type
121 AND t1.terms_display_level = lc_level)
122 ORDER BY parent_id,terms_display_sequence;
123
124 CURSOR l_max_levels_csr (
125 lc_document_id IN NUMBER,
126 lc_document_type IN VARCHAR2) IS
127 SELECT NVL(MAX(terms_display_level),0)
128 FROM okc_terms_artsec_disp_temp
129 WHERE document_id = lc_document_id
130 AND document_type = lc_document_type
131 AND processed_flag = 'N';
132
133 BEGIN
134
135 DELETE FROM okc_terms_artsec_disp_temp
136 WHERE document_id = p_document_id
137 AND document_type = p_document_type;
138
139 INSERT INTO okc_terms_artsec_disp_temp
140 (document_id,
141 document_type,
142 terms_type,
143 terms_id,
144 parent_id,
145 terms_display_sequence,
146 terms_display_level,
147 terms_display_order,
148 processed_flag,
149 runid
150 )
151 (select document_id,
152 document_type,
153 'SECTION',
154 id,
155 scn_id,
156 section_sequence,
157 level,
158 '',
159 'N',
160 p_run_id
161 from okc_sections_b
162 where document_id = p_document_id
163 and document_type = p_document_type
164 connect by prior id = scn_id
165 start with scn_id is null);
166
167 INSERT INTO okc_terms_artsec_disp_temp
168 (document_id,
169 document_type,
170 terms_type,
171 terms_id,
172 parent_id,
173 terms_display_sequence,
174 terms_display_level,
175 terms_display_order,
176 processed_flag,
177 runid
178 )
179 (select document_id,
180 document_type,
181 'ARTICLE',
182 id,
183 scn_id,
184 display_sequence,
185 null,
186 '',
187 'N',
188 p_run_id
189 from okc_k_articles_b
190 where document_id = p_document_id
191 and document_type = p_document_type);
192
193 OPEN l_max_levels_csr(p_document_id,
194 p_document_type);
195 FETCH l_max_levels_csr INTO l_max_level;
196 CLOSE l_max_levels_csr;
197
198 /***************************************
199 insert into okc_terms_artsec_disp_temp
200 (document_id,
201 document_type,
202 terms_type,
203 terms_id,
204 parent_id,
205 terms_display_sequence,
206 terms_display_level,
207 terms_display_order,
208 processed_flag
209 )
210 SELECT document_id,
211 document_type,
212 terms_type,
213 terms_id,
214 parent_id,
215 terms_display_sequence,
216 terms_display_level,
217 LPAD(ROWNUM,5,'0'),
218 'Y'
219 FROM okc_terms_artsec_disp_temp
220 WHERE document_id = p_document_id
221 AND document_type = p_document_type
222 AND processed_flag = 'N'
223 AND parent_id IS null
224 AND terms_type = 'SECTION'
225 ORDER BY terms_display_sequence ;
226 **********************************/
227
228 l_display_order := 1;
229
230 FOR rec in l_lvl1_scn_csr LOOP
231 insert into okc_terms_artsec_disp_temp
232 (document_id,
233 document_type,
234 terms_type,
235 terms_id,
236 parent_id,
237 terms_display_sequence,
238 terms_display_level,
239 terms_display_order,
240 processed_flag,
241 runid
242 )
243 VALUES
244 ( rec.document_id,
245 rec.document_type,
246 rec.terms_type,
247 rec.terms_id,
248 rec.parent_id,
249 rec.terms_display_sequence,
250 rec.terms_display_level,
251 LPAD(l_display_order,5,'0'),
252 'Y',
253 rec.runid);
254 l_display_order := l_display_order + 1;
255
256
257 END LOOP;
258
259 l_display_order := 1;
260
261
262 FOR i in 1..l_max_level LOOP
263
264 FOR rec in l_terms_csr(p_document_id,p_document_type,i) LOOP
265
266 l_display_order := l_display_order + 1;
267
268
269 IF l_old_parent_id <> rec.parent_id THEN
270 l_display_order := 1;
271 l_parent_disp_order :=
272 get_terms_display_order
273 ( p_terms_type => 'SECTION',
274 p_terms_id => rec.parent_id);
275 l_old_parent_id := rec.parent_id;
276 END IF;
277
278 insert into okc_terms_artsec_disp_temp
279 ( document_id,
280 document_type,
281 terms_type,
282 terms_id,
283 parent_id,
284 terms_display_sequence,
285 terms_display_level,
286 terms_display_order,
287 processed_flag,
288 runid
289 )
290 VALUES
291 ( rec.document_id,
292 rec.document_type,
293 rec.terms_type,
294 rec.terms_id,
295 rec.parent_id,
296 rec.terms_display_sequence,
297 rec.terms_display_level,
298 l_parent_disp_order||'.'||LPAD(l_display_order,5,'0'),
299 'Y',
300 rec.runid
301 );
302
303
304 END LOOP;
305
306
307 END LOOP;
308 DELETE FROM okc_terms_artsec_disp_temp
309 WHERE document_id = p_document_id
310 AND document_type = p_document_type
311 AND processed_flag = 'N';
312 commit;
313 END;
314
315 FUNCTION get_terms_display_order
316 (
317 p_api_version IN NUMBER,
318 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
319
320 p_document_id IN NUMBER,
321 p_document_type IN VARCHAR2,
322 p_terms_type IN VARCHAR2,
323 p_terms_id IN NUMBER,
324 p_run_id IN VARCHAR2
325 ) RETURN VARCHAR2 IS
326 l_terms_display_order VARCHAR2(2000);
327 l_global_temp_loaded VARCHAR2(1) := 'N';
328 CURSOR l_global_temp_csr IS
329 SELECT '1'
330 FROM OKC_TERMS_ARTSEC_DISP_TEMP
331 WHERE document_type = p_document_type
332 AND document_id = p_document_id
333 AND runid = p_run_id;
334 BEGIN
335 /****
336 IF G_GLOBAL_TEMP_LOADED = 'N' THEN
337 populate_temp_tab(p_document_id => p_document_id,
338 p_document_type => p_document_type);
339 G_GLOBAL_TEMP_LOADED := 'Y';
340 END IF;
341 *****/
342
343 OPEN l_global_temp_csr;
344 FETCH l_global_temp_csr INTO l_global_temp_loaded;
345 IF l_global_temp_csr%NOTFOUND THEN
346 populate_temp_tab(p_document_id => p_document_id,
347 p_document_type => p_document_type,
348 p_run_id => p_run_id);
349 END IF;
350 CLOSE l_global_temp_csr;
351
352 l_terms_display_order := get_terms_display_order
353 ( p_terms_type => p_terms_type,
354 p_terms_id => p_terms_id);
355
356 RETURN l_terms_display_order;
357 END;
358
359
360 FUNCTION get_terms_structure_level
361 (
362 p_api_version IN NUMBER,
363 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
364
365 p_document_id IN NUMBER,
366 p_document_type IN VARCHAR2,
367 p_terms_type IN VARCHAR2,
368 p_terms_id IN NUMBER,
369 p_run_id IN VARCHAR2
370 ) RETURN NUMBER IS
371 l_terms_display_order VARCHAR2(2000) := 0;
372 l_global_temp_loaded VARCHAR2(1) := 'N';
373 l_terms_structure_level number := 0;
374 CURSOR l_global_temp_csr IS
375 SELECT '1'
376 FROM OKC_TERMS_ARTSEC_DISP_TEMP
377 WHERE document_type = p_document_type
378 AND document_id = p_document_id;
379 BEGIN
380 /****
381 IF G_GLOBAL_TEMP_LOADED = 'N' THEN
382 populate_temp_tab(p_document_id => p_document_id,
383 p_document_type => p_document_type);
384 G_GLOBAL_TEMP_LOADED := 'Y';
385 END IF;
386 *****/
387
388 l_terms_display_order := get_terms_display_order(
389 p_api_version => p_api_version,
390 p_init_msg_list => p_init_msg_list,
391
392 p_document_id => p_document_id,
393 p_document_type => p_document_type,
394 p_terms_type => p_terms_type,
395 p_terms_id => p_terms_id,
396 p_run_id => p_run_id
397 );
398
399 IF LENGTH(l_terms_display_order) <= 5 THEN
400 l_terms_structure_level := 1;
401 ELSE
402 l_terms_structure_level := (((length(l_terms_display_order)-5)/6)+1);
403 END IF;
404
405
406 RETURN l_terms_structure_level;
407 END;
408
409 END OKC_TERMS_DISP_PVT;