[Home] [Help]
PACKAGE BODY: APPS.OKC_TERMS_DISP_PVT
Source
1 PACKAGE BODY OKC_TERMS_DISP_PVT AS
2 /* $Header: OKCVTERMSDISPB.pls 120.0.12010000.2 2009/09/22 09:06:57 harchand ship $ */
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 and document_id = p_document_id
167 and document_type = p_document_type);
168
169 INSERT INTO okc_terms_artsec_disp_temp
170 (document_id,
171 document_type,
172 terms_type,
173 terms_id,
174 parent_id,
175 terms_display_sequence,
176 terms_display_level,
177 terms_display_order,
178 processed_flag,
179 runid
180 )
181 (select document_id,
182 document_type,
183 'ARTICLE',
184 id,
185 scn_id,
186 display_sequence,
187 null,
188 '',
189 'N',
190 p_run_id
191 from okc_k_articles_b
192 where document_id = p_document_id
193 and document_type = p_document_type);
194
195 OPEN l_max_levels_csr(p_document_id,
196 p_document_type);
197 FETCH l_max_levels_csr INTO l_max_level;
198 CLOSE l_max_levels_csr;
199
200 /***************************************
201 insert into okc_terms_artsec_disp_temp
202 (document_id,
203 document_type,
204 terms_type,
205 terms_id,
206 parent_id,
207 terms_display_sequence,
208 terms_display_level,
209 terms_display_order,
210 processed_flag
211 )
212 SELECT document_id,
213 document_type,
214 terms_type,
215 terms_id,
216 parent_id,
217 terms_display_sequence,
218 terms_display_level,
219 LPAD(ROWNUM,5,'0'),
220 'Y'
221 FROM okc_terms_artsec_disp_temp
222 WHERE document_id = p_document_id
223 AND document_type = p_document_type
224 AND processed_flag = 'N'
225 AND parent_id IS null
226 AND terms_type = 'SECTION'
227 ORDER BY terms_display_sequence ;
228 **********************************/
229
230 l_display_order := 1;
231
232 FOR rec in l_lvl1_scn_csr LOOP
233 insert into okc_terms_artsec_disp_temp
234 (document_id,
235 document_type,
236 terms_type,
237 terms_id,
238 parent_id,
239 terms_display_sequence,
240 terms_display_level,
241 terms_display_order,
242 processed_flag,
243 runid
244 )
245 VALUES
246 ( rec.document_id,
247 rec.document_type,
248 rec.terms_type,
249 rec.terms_id,
250 rec.parent_id,
251 rec.terms_display_sequence,
252 rec.terms_display_level,
253 LPAD(l_display_order,5,'0'),
254 'Y',
255 rec.runid);
256 l_display_order := l_display_order + 1;
257
258
259 END LOOP;
260
261 l_display_order := 1;
262
263
264 FOR i in 1..l_max_level LOOP
265
266 FOR rec in l_terms_csr(p_document_id,p_document_type,i) LOOP
267
268 l_display_order := l_display_order + 1;
269
270
271 IF l_old_parent_id <> rec.parent_id THEN
272 l_display_order := 1;
273 l_parent_disp_order :=
274 get_terms_display_order
275 ( p_terms_type => 'SECTION',
276 p_terms_id => rec.parent_id);
277 l_old_parent_id := rec.parent_id;
278 END IF;
279
280 insert into okc_terms_artsec_disp_temp
281 ( document_id,
282 document_type,
283 terms_type,
284 terms_id,
285 parent_id,
286 terms_display_sequence,
287 terms_display_level,
288 terms_display_order,
289 processed_flag,
290 runid
291 )
292 VALUES
293 ( rec.document_id,
294 rec.document_type,
295 rec.terms_type,
296 rec.terms_id,
297 rec.parent_id,
298 rec.terms_display_sequence,
299 rec.terms_display_level,
300 l_parent_disp_order||'.'||LPAD(l_display_order,5,'0'),
301 'Y',
302 rec.runid
303 );
304
305
306 END LOOP;
307
308
309 END LOOP;
310 DELETE FROM okc_terms_artsec_disp_temp
311 WHERE document_id = p_document_id
312 AND document_type = p_document_type
313 AND processed_flag = 'N';
314 commit;
315 END;
316
317 FUNCTION get_terms_display_order
318 (
319 p_api_version IN NUMBER,
320 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
321
322 p_document_id IN NUMBER,
323 p_document_type IN VARCHAR2,
324 p_terms_type IN VARCHAR2,
325 p_terms_id IN NUMBER,
326 p_run_id IN VARCHAR2
327 ) RETURN VARCHAR2 IS
328 l_terms_display_order VARCHAR2(2000);
329 l_global_temp_loaded VARCHAR2(1) := 'N';
330 CURSOR l_global_temp_csr IS
331 SELECT '1'
332 FROM OKC_TERMS_ARTSEC_DISP_TEMP
333 WHERE document_type = p_document_type
334 AND document_id = p_document_id
335 AND runid = p_run_id;
336 BEGIN
337 /****
338 IF G_GLOBAL_TEMP_LOADED = 'N' THEN
339 populate_temp_tab(p_document_id => p_document_id,
340 p_document_type => p_document_type);
341 G_GLOBAL_TEMP_LOADED := 'Y';
342 END IF;
343 *****/
344
345 OPEN l_global_temp_csr;
346 FETCH l_global_temp_csr INTO l_global_temp_loaded;
347 IF l_global_temp_csr%NOTFOUND THEN
348 populate_temp_tab(p_document_id => p_document_id,
349 p_document_type => p_document_type,
350 p_run_id => p_run_id);
351 END IF;
352 CLOSE l_global_temp_csr;
353
354 l_terms_display_order := get_terms_display_order
355 ( p_terms_type => p_terms_type,
356 p_terms_id => p_terms_id);
357
358 RETURN l_terms_display_order;
359 END;
360
361
362 FUNCTION get_terms_structure_level
363 (
364 p_api_version IN NUMBER,
365 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
366
367 p_document_id IN NUMBER,
368 p_document_type IN VARCHAR2,
369 p_terms_type IN VARCHAR2,
370 p_terms_id IN NUMBER,
371 p_run_id IN VARCHAR2
372 ) RETURN NUMBER IS
373 l_terms_display_order VARCHAR2(2000) := 0;
374 l_global_temp_loaded VARCHAR2(1) := 'N';
375 l_terms_structure_level number := 0;
376 CURSOR l_global_temp_csr IS
377 SELECT '1'
378 FROM OKC_TERMS_ARTSEC_DISP_TEMP
379 WHERE document_type = p_document_type
380 AND document_id = p_document_id;
381 BEGIN
382 /****
383 IF G_GLOBAL_TEMP_LOADED = 'N' THEN
384 populate_temp_tab(p_document_id => p_document_id,
385 p_document_type => p_document_type);
386 G_GLOBAL_TEMP_LOADED := 'Y';
387 END IF;
388 *****/
389
390 l_terms_display_order := get_terms_display_order(
391 p_api_version => p_api_version,
392 p_init_msg_list => p_init_msg_list,
393
394 p_document_id => p_document_id,
395 p_document_type => p_document_type,
396 p_terms_type => p_terms_type,
397 p_terms_id => p_terms_id,
398 p_run_id => p_run_id
399 );
400
401 IF LENGTH(l_terms_display_order) <= 5 THEN
402 l_terms_structure_level := 1;
403 ELSE
404 l_terms_structure_level := (((length(l_terms_display_order)-5)/6)+1);
405 END IF;
406
407
408 RETURN l_terms_structure_level;
409 END;
410
411 END OKC_TERMS_DISP_PVT;