1 PACKAGE BODY OKE_CONTRACT_PRINTING_PKG AS
2 /* $Header: OKEKCPPB.pls 120.1 2007/12/20 06:29:50 neerakum ship $ */
3
4 FUNCTION get_article_subject_name(p_sbt_code IN VARCHAR2)RETURN VARCHAR2
5 IS
6 l_not_found BOOLEAN;
7 l_meaning VARCHAR2(80);
8
9 Cursor c Is
10 SELECT MEANING
11 FROM FND_LOOKUPS
12 WHERE LOOKUP_TYPE ='OKC_SUBJECT'
13 AND LOOKUP_CODE = p_sbt_code;
14
15 BEGIN
16 open c;
17 fetch c into l_meaning;
18 l_not_found := c%NOTFOUND;
19 close c;
20
21 /*
22 If (l_not_found) Then
23 raise NO_DATA_FOUND;
24 End If;
25 */
26 RETURN l_meaning;
27
28 END get_article_subject_name;
29
30 FUNCTION get_full_path_linenum(vk_line_id NUMBER,vVersion NUMBER) RETURN VARCHAR2
31 IS
32 l_parent_id NUMBER;
33 l_linenum varchar2(300);
34 isTop_line varchar2(1) :='?';
35
36 cursor c_isTop_line is
37 select 'x'
38 FROM OKE_K_LINES_FULL_HV
39 WHERE k_line_id= vk_line_id
40 AND parent_line_id is NULL
41 AND MAJOR_VERSION=vVersion;
42
43 BEGIN
44 SELECT line_number
45 INTO l_linenum
46 FROM OKE_K_LINES_FULL_HV
47 WHERE k_line_id =vk_line_id
48 AND MAJOR_VERSION=vVersion;
49
50 OPEN c_isTop_line;
51 FETCH c_isTop_line INTO isTop_line;
52 CLOSE c_isTop_line;
53
54 IF isTop_line = 'x' THEN
55 RETURN l_linenum;
56 ELSE
57 SELECT parent_line_id
58 INTO l_parent_id
59 FROM OKE_K_LINES_FULL_HV
60 WHERE k_line_id=vk_line_id
61 AND MAJOR_VERSION=vVersion;
62 RETURN (get_full_path_linenum(l_parent_id,vVersion)||'-->'||l_linenum);
63
64 END IF;
65
66 EXCEPTION
67 WHEN NO_DATA_FOUND THEN
68 NULL;
69 WHEN OTHERS THEN
70 NULL;
71
72 END get_full_path_linenum;
73
74 FUNCTION get_full_path_linenum(vk_line_id NUMBER) RETURN VARCHAR2
75 IS
76 l_parent_id NUMBER;
77 l_linenum varchar2(300);
78 isTop_line varchar2(1) :='?';
79
80 cursor c_isTop_line is
81 select 'x'
82 FROM OKE_K_LINES_FULL_V
83 WHERE k_line_id= vk_line_id
84 AND parent_line_id is NULL;
85
86 BEGIN
87 SELECT line_number
88 INTO l_linenum
89 FROM OKE_K_LINES_FULL_V
90 WHERE k_line_id =vk_line_id;
91
92 OPEN c_isTop_line;
93 FETCH c_isTop_line INTO isTop_line;
94 CLOSE c_isTop_line;
95
96 IF isTop_line = 'x' THEN
97 RETURN l_linenum;
98 ELSE
99 SELECT parent_line_id
100 INTO l_parent_id
101 FROM OKE_K_LINES_FULL_V
102 WHERE k_line_id=vk_line_id;
103 RETURN (get_full_path_linenum(l_parent_id)||'-->'||l_linenum);
104
105 END IF;
106
107 EXCEPTION
108 WHEN NO_DATA_FOUND THEN
109 NULL;
110 WHEN OTHERS THEN
111 NULL;
112
113 END get_full_path_linenum;
114
115 PROCEDURE get_item_master_org(p_header_id IN NUMBER
116 ,x_org_name OUT NOCOPY VARCHAR2)
117 IS
118
119 l_org_name VARCHAR2(240);
120 --l_own_org_id NUMBER := name_in('K_HEADER.OWNING_ORGANIZATION_ID');
121 --l_own_org_name VARCHAR2(60);
122
123 CURSOR c(p_id number) is
124 select org.name
125 from hr_all_organization_units org
126 , okc_k_headers_b ch
127 where org.organization_id = ch.inv_organization_id
128 and ch.id =p_id ;
129
130
131 BEGIN
132 OPEN C(p_header_id);
133 FETCH C INTO l_org_name;
134
135 IF(C%NOTFOUND) THEN
136 CLOSE C;
137 RAISE NO_DATA_FOUND;
138 END IF;
139
140 CLOSE C;
141
142 --dbms_output.put_line('l org' ||l_org_name);
143 x_org_name :=l_org_name;
144
145 END get_item_master_org;
146
147
148
149 PROCEDURE get_partyOrContact_name(p_jtot_object1_code IN VARCHAR2
150 ,p_object1_id1 IN VARCHAR2
151 ,p_object1_id2 IN VARCHAR2
152 ,p_name OUT NOCOPY VARCHAR2
153 )
154 IS
155 l_name VARCHAR2(4000) ;
156
157 BEGIN
158
159 l_name :=OKC_UTIL.get_name_from_jtfv(p_jtot_object1_code,p_object1_id1,p_object1_id2);
160
161 -- DBMS_OUTPUT.PUT_LINE(l_name);
162 /*
163 EXCEPTION
164 WHEN NO_DATA_FOUND THEN
165 NULL;
166 WHEN OTHERS THEN
167 NULL;
168 */
169 p_name :=l_name;
170 END get_partyOrContact_name;
171
172 PROCEDURE get_article_info(p_cat_type IN VARCHAR2
173 ,p_sav_sae_id IN NUMBER
174 ,p_sbt_code IN VARCHAR2
175 ,p_article_name IN VARCHAR2
176 ,x_sbt_code OUT NOCOPY VARCHAR2
177 ,x_article_name OUT NOCOPY VARCHAR2
178 ,x_subject_name OUT NOCOPY VARCHAR2)
179 IS
180
181 CURSOR C (p_id number)IS
182 SELECT NAME,SBT_CODE
183 FROM OKC_STD_ARTICLES_V
184 WHERE ID = p_id;
185
186 l_name VARCHAR2(150);
187 l_sbt_code VARCHAR2(30);
188 l_not_found BOOLEAN;
189
190 BEGIN
191 IF p_cat_type = 'STA' THEN
192 If (p_sav_sae_id is not null) Then
193
194 OPEN C(p_sav_sae_id);
195 FETCH C into l_name,l_sbt_code;
196
197 IF (C%NOTFOUND) THEN
198 CLOSE C;
199 RAISE NO_DATA_FOUND;
200 End If;
201
202 CLOSE C;
203
204 x_article_name := l_name;
205 x_sbt_code :=l_sbt_code;
206 x_subject_name := get_article_subject_name(x_sbt_code);
207
208 END IF;
209
210 ELSE
211 x_article_name :=p_article_name;
212 x_sbt_code :=p_sbt_code;
213 x_subject_name := get_article_subject_name(p_sbt_code);
214
215 END IF;
216 END get_article_info;
217
218 PROCEDURE get_article_application(p_id IN NUMBER
219 ,p_version IN NUMBER
220 ,p_cat_type IN VARCHAR2
221 ,p_sav_sae_id IN NUMBER
222 ,p_sav_sav_release IN VARCHAR2
223 ,x_comments OUT NOCOPY VARCHAR2
224 ,x_lines_applied OUT NOCOPY VARCHAR2
225 ,x_text OUT NOCOPY CLOB)
226 IS
227
228 l_line_number VARCHAR2(150);
229 l_lines_applied VARCHAR2(2000) := '';
230 l_text CLOB;
231 l_comments VARCHAR2(1995);
232 l_latest_version NUMBER; -- The latest version number of the contract
233
234 cursor c_version is
235 select max(major_version)
236 from oke_k_headers_hv
237 where k_header_id =p_id;
238
239 cursor c_line_number_h(p_line_id NUMBER) is
240 select line_number
241 from oke_k_lines_hv
242 where header_id = p_id
243 and k_line_id = p_line_id
244 and major_version = p_version;
245
246 cursor c_line_number(p_line_id NUMBER) is
247 select line_number
248 from oke_k_lines_v
249 where header_id = p_id
250 and k_line_id = p_line_id;
251
252 cursor c_line_ids_h is
253 select cle_id,comments,text
254 from okc_k_articles_hv
255 where dnz_chr_id = p_id
256 and cat_type=p_cat_type
257 and sav_sae_id=p_sav_sae_id
258 and sav_sav_release = p_sav_sav_release
259 and major_version = p_version;
260
261 cursor c_line_ids is
262 select cle_id,comments,text
263 from okc_k_articles_v
264 where dnz_chr_id = p_id
265 and cat_type=p_cat_type
266 and sav_sae_id=p_sav_sae_id
267 and sav_sav_release = p_sav_sav_release;
268
269 BEGIN
270
271 OPEN c_version;
272 FETCH c_version INTO l_latest_version;
273 CLOSE c_version;
274
275
276 IF p_version<=l_latest_version THEN
277
278 for c_line_id in c_line_ids_h loop
279
280 if c_line_id.cle_id is null then
281 l_line_number :='';
282 else
283 open c_line_number_h(c_line_id.cle_id);
284 fetch c_line_number_h into l_line_number;
285 close c_line_number_h;
286
287 if l_lines_applied is null then
288 l_lines_applied := l_line_number;
289 else
290 l_lines_applied := l_lines_applied ||','||l_line_number;
291 end if;
292 end if;
293
294 l_text :=c_line_id.text;
295 l_comments :=c_line_id.comments;
296
297 end loop;
298
299 ELSE
300
301 for c_line_id in c_line_ids loop
302
303 if c_line_id.cle_id is null then
304 l_line_number :='';
305 else
306 open c_line_number(c_line_id.cle_id);
307 fetch c_line_number into l_line_number;
308 close c_line_number;
309
310 if l_lines_applied is null then
311 l_lines_applied := l_line_number;
312 else
313 l_lines_applied := l_lines_applied ||','||l_line_number;
314 end if;
315 end if;
316
317 l_text :=c_line_id.text;
318 l_comments :=c_line_id.comments;
319
320 end loop;
321 END IF;
322
323
324 x_lines_applied := l_lines_applied;
325
326
327 x_text := l_text;
328 x_comments :=l_comments;
329
330 EXCEPTION
331 WHEN NO_DATA_FOUND THEN
332 NULL;
333 WHEN OTHERS THEN
334 NULL;
335
336
337 END get_article_application;
338
339 PROCEDURE convert_date(p_date IN DATE
340 ,x_date OUT NOCOPY VARCHAR2)
341 IS
342
343 BEGIN
344
345 IF p_date IS NOT NULL THEN
346 x_date := to_char(p_date, 'DD-MM-YYYY');
347 ELSE
348 x_date :='';
349 END IF;
350
351 END convert_date;
352
353 PROCEDURE get_line_number(p_id IN NUMBER
354 ,p_version IN NUMBER
355 ,p_line_id IN NUMBER
356 ,x_line_number OUT NOCOPY VARCHAR2)
357 IS
358 l_latest_version NUMBER; -- The latest version number of the contract
359 l_line_number VARCHAR2(150) := '';
360
361 cursor c_version is
362 select max(major_version)
363 from oke_k_headers_hv
364 where k_header_id =p_id;
365
366 cursor c_line_number_h is
367 select line_number
368 from oke_k_lines_hv
369 where header_id = p_id
370 and k_line_id = p_line_id
371 and major_version = p_version;
372
373 cursor c_line_number is
374 select line_number
375 from oke_k_lines_v
376 where header_id = p_id
377 and k_line_id = p_line_id;
378
379 BEGIN
380 OPEN c_version;
381 FETCH c_version INTO l_latest_version;
382 CLOSE c_version;
383
384 IF p_line_id IS NOT NULL THEN
385 IF p_version<=l_latest_version THEN
386 open c_line_number_h;
387 fetch c_line_number_h into l_line_number;
388 close c_line_number_h;
389 ELSE
390 open c_line_number;
391 fetch c_line_number into l_line_number;
392 close c_line_number;
393 END IF;
394
395 x_line_number :=l_line_number;
396 ELSE
397 l_line_number :='';
398 END IF;
399
400
401
402 EXCEPTION
403 WHEN NO_DATA_FOUND THEN
404 NULL;
405 WHEN OTHERS THEN
406 NULL;
407 END get_line_number;
408
409
410
411 END OKE_CONTRACT_PRINTING_PKG;
412
[Home] [Help]