[Home] [Help]
PACKAGE BODY: APPS.JTF_NOTES_UTIL_PVT
Source
4 PROCEDURE GetContexts
1 PACKAGE BODY JTF_NOTES_UTIL_PVT AS
2 /* $Header: jtfvnub.pls 115.10 2002/11/16 00:30:13 hbouten ship $ */
3
5 ( p_api_version IN NUMBER
6 , p_init_msg_list IN VARCHAR2
7 , p_validation_level IN NUMBER
8 , p_note_id IN NUMBER
9 , x_context_count OUT NOCOPY NUMBER
10 , x_context_id OUT NOCOPY JTF_NUMBER_TABLE
11 , x_context_type_code OUT NOCOPY JTF_VARCHAR2_TABLE_100
12 , x_context_type_name OUT NOCOPY JTF_VARCHAR2_TABLE_100
13 , x_context_select_id OUT NOCOPY JTF_NUMBER_TABLE
14 , x_context_select_name OUT NOCOPY JTF_VARCHAR2_TABLE_2000
15 , x_context_select_details OUT NOCOPY JTF_VARCHAR2_TABLE_2000
16 , x_return_status OUT NOCOPY VARCHAR2
17 , x_msg_count OUT NOCOPY NUMBER
18 , x_msg_data OUT NOCOPY VARCHAR2
19 ) AS
20
21 CURSOR c_get_note_contexts
22 (
23 p_note_id NUMBER
24 ) IS
25 SELECT JNS.note_context_id note_context_id,
26 JNS.jtf_note_id jtf_note_id,
27 --Bug # 1978242, change everything to Party
31 JOB.select_id select_id,
28 DECODE(JNS.note_context_type,'PARTY_ORGANIZATION','PARTY','PARTY_PERSON',
29 'PARTY','PARTY_RELATIONSHIP','PARTY',JNS.note_context_type) note_context_type,
30 JNS.note_context_type_id note_context_type_id,
32 JOB.select_name select_name,
33 JOB.select_details select_details,
34 JOB.from_table from_table,
35 JOB.where_clause where_clause,
36 JOB.order_by_clause order_by_clause,
37 JOL.NAME note_context_type_name
38 FROM JTF_OBJECTS_TL JOL,
39 JTF_OBJECTS_B JOB,
40 JTF_NOTE_CONTEXTS JNS
41 WHERE JNS.JTF_NOTE_ID = p_note_id
42 AND JOB.OBJECT_CODE = DECODE(JNS.note_context_type,'PARTY_ORGANIZATION','PARTY',
43 'PARTY_PERSON','PARTY','PARTY_RELATIONSHIP','PARTY',JNS.note_context_type)
44 AND JOL.OBJECT_CODE = JOB.OBJECT_CODE
45 AND JOL.LANGUAGE = USERENV('LANG');
46
47 l_jtf_objects_sql VARCHAR2(2000);
48 l_context_select_name VARCHAR2(2000);
49 l_context_select_details VARCHAR2(2000);
50
51 BEGIN
52 x_return_status := 'S';
53 x_msg_count := 0;
54 x_msg_data := '';
55 x_context_count := 0;
56 --First instantiate the collection
57 x_context_id := JTF_NUMBER_TABLE();
58 x_context_type_code := JTF_VARCHAR2_TABLE_100();
59 x_context_type_name := JTF_VARCHAR2_TABLE_100();
60 x_context_select_id := JTF_NUMBER_TABLE();
61 x_context_select_name := JTF_VARCHAR2_TABLE_2000();
62 x_context_select_details := JTF_VARCHAR2_TABLE_2000();
63
64 FOR ref_note_contexts IN c_get_note_contexts(p_note_id)
65 LOOP
66 x_context_count := x_context_count + 1;
67 --First instantiate the collection
68 x_context_id.extend(x_context_count);
69 x_context_type_code.extend(x_context_count);
70 x_context_type_name.extend(x_context_count);
71 x_context_select_id.extend(x_context_count);
72 x_context_select_name.extend(x_context_count);
73 x_context_select_details.extend(x_context_count);
74 --Copy the data
75 x_context_id(x_context_count) := ref_note_contexts.note_context_id;
76 x_context_type_code(x_context_count) := ref_note_contexts.note_context_type;
77 x_context_type_name(x_context_count) := ref_note_contexts.note_context_type_name;
78 x_context_select_id(x_context_count) := ref_note_contexts.note_context_type_id;
79 --Initialize remaining items.
80 x_context_select_name(x_context_count) := NULL;
81 x_context_select_details(x_context_count) := NULL;
82 --Build the query
83 IF ((ref_note_contexts.select_id IS NOT NULL) AND
84 (ref_note_contexts.select_name IS NOT NULL) AND
85 (ref_note_contexts.from_table IS NOT NULL))
86 THEN
87 l_jtf_objects_sql := 'SELECT ' || ref_note_contexts.select_name;
88 IF (ref_note_contexts.select_details IS NOT NULL)
89 THEN
90 l_jtf_objects_sql := l_jtf_objects_sql || ',' ||
91 ref_note_contexts.select_details;
92 ELSE
93 l_jtf_objects_sql := l_jtf_objects_sql || ',NULL';
94 END IF;
95 l_jtf_objects_sql := l_jtf_objects_sql || ' FROM ' ||
96 ref_note_contexts.from_table;
97 IF (ref_note_contexts.where_clause IS NOT NULL)
98 THEN
99 l_jtf_objects_sql := l_jtf_objects_sql || ' WHERE ' ||
100 ref_note_contexts.where_clause || ' AND ';
101 ELSE
102 l_jtf_objects_sql := l_jtf_objects_sql || ' WHERE ';
103 END IF;
104 l_jtf_objects_sql := l_jtf_objects_sql || ref_note_contexts.select_id ||
105 ' = :note_context_type_id';
106 BEGIN
107 EXECUTE IMMEDIATE l_jtf_objects_sql
108 INTO x_context_select_name(x_context_count),
109 x_context_select_details(x_context_count)
110 USING ref_note_contexts.note_context_type_id;
111 EXCEPTION
112 WHEN OTHERS THEN
113 x_context_select_name(x_context_count) := NULL;
114 x_context_select_details(x_context_count) := NULL;
115 x_msg_count := x_msg_count + 1;
116 x_msg_data := x_msg_data ||'Error executing query for notes Context ' ||
117 ref_note_contexts.note_context_type_name || ' and ID ' ||
118 ref_note_contexts.note_context_type_id || '. ';
119 END;
120 ELSE
121 x_msg_count := x_msg_count + 1;
122 x_msg_data := x_msg_data ||'Error building query for notes Context ' ||
123 ref_note_contexts.note_context_type_name || '. ';
124 END IF;
125 END LOOP;
126
127 EXCEPTION
128 WHEN OTHERS THEN
129 x_return_status := 'U';
130 x_msg_count := x_msg_count + 1;
131 x_msg_data := x_msg_data ||'Error fetching note context information : ' || sqlerrm;
132
133 END GetContexts;
134
135 FUNCTION GetNotesDetail
136 (
137 p_note_id IN NUMBER
138 ) RETURN VARCHAR2
139 AS
140
141 CURSOR c_get_notes_detail
142 (
143 p_note_id NUMBER
144 ) IS
145 SELECT NOTES_DETAIL
146 FROM JTF_NOTES_TL
147 WHERE JTF_NOTE_ID = p_note_id
148 AND LANGUAGE = USERENV('LANG');
149
150 l_notes_detail JTF_NOTES_TL.NOTES_DETAIL%TYPE;
151
152 Amount BINARY_INTEGER := 32767;
153 Position INTEGER := 1;
154 Buffer varchar2(32767);
155 CHUNKSIZE INTEGER;
156
157 BEGIN
158 OPEN c_get_notes_detail(p_note_id);
162
159 FETCH c_get_notes_detail
160 INTO l_notes_detail;
161 CLOSE c_get_notes_detail;
163 chunksize := DBMS_LOB.getchunksize(l_notes_detail);
164
165 If chunksize is not null then
166
167 if chunksize < 32767 then
168 amount := (32767/chunksize) * chunksize;
169 end if;
170
171 dbms_lob.read(l_notes_detail,amount,position,buffer);
172
173 end if;
174
175 RETURN buffer;
176
177 END GetNotesDetail;
178
179 FUNCTION CheckAttachments
180 (
181 p_note_id IN NUMBER
182 ) RETURN NUMBER
183 IS
184
185 CURSOR GetAttachments
186 (
187 p_note_id VARCHAR2
188 ) IS
189 SELECT attached_document_id
190 FROM FND_ATTACHED_DOCS_FORM_VL
191 WHERE function_name = 'JTF_CAL_ATTACHMENTS'
192 AND function_type = 'F'
193 AND (security_type = 4 OR publish_flag = 'Y')
194 AND entity_name = 'JTF_NOTES_B'
195 AND pk1_value = p_note_id;
196
197 l_dummy NUMBER := 0;
198 l_return NUMBER := 0;
199
200 BEGIN
201 OPEN GetAttachments(TO_CHAR(p_note_id));
202 FETCH GetAttachments
203 INTO l_dummy;
204 IF GetAttachments%FOUND
205 THEN
206 l_return := 1;
207 END IF;
208 CLOSE GetAttachments;
209
210 RETURN l_return;
211
212 END CheckAttachments;
213
214
215 FUNCTION HasCLOB
216 ( p_note_id IN NUMBER
217 ) RETURN VARCHAR2
218 IS
219
220 CURSOR c_note
221 (
222 p_note_id VARCHAR2
223 ) IS
224 SELECT DECODE(DBMS_LOB.SUBSTR(notes_detail,1,1),NULL,'N','Y') hasCLOB
225 FROM jtf_notes_tl
226 WHERE jtf_note_id = p_note_id
227 AND language = userenv('LANG');
228
229 l_HasCLOB VARCHAR2(1):='N';
230
231 BEGIN
232 OPEN c_note(p_note_id);
233 FETCH c_note INTO l_hasCLOB;
234 CLOSE c_note;
235 RETURN l_hasCLOB;
236 END HasCLOB;
237
238 FUNCTION JTFObjectValid
239 /*
240 function to verify whether the JTF_OBJECT definition for an object is enough to
241 create a SQL statement, if not we don't even want to expose the object.
242 */
243 ( p_object_code IN VARCHAR2
244 ) RETURN VARCHAR2
245 IS
246 CURSOR c_object
247 (b_object_code IN VARCHAR2
248 )IS SELECT job.select_id
249 , job.select_name
250 , job.from_table
251 FROM jtf_objects_b job
252 WHERE job.object_code = b_object_code;
253
254 r_object c_object%ROWTYPE;
255
256 l_return VARCHAR2(1):= 'N';
257
258 BEGIN
259 IF (c_object%ISOPEN)
260 THEN
261 CLOSE c_object;
262 END IF;
263
264 OPEN c_object(p_object_code);
265
266 FETCH c_object INTO r_object;
267
268 IF (c_object%FOUND)
269 THEN
270 IF ( (r_object.select_id IS NULL)
271 OR (r_object.select_name IS NULL)
272 OR (r_object.from_table IS NULL)
273 )
274 THEN
275 l_return := 'N';
276 ELSE
277 l_return := 'Y';
278 END IF;
279 ELSE
280 l_return := 'N';
281 END IF;
282
283 IF (c_object%ISOPEN)
284 THEN
285 CLOSE c_object;
286 END IF;
287
288 RETURN l_return;
289 EXCEPTION
290 WHEN OTHERS
291 THEN
292 IF (c_object%ISOPEN)
293 THEN
294 CLOSE c_object;
295 END IF;
296 RETURN 'N';
297
298 END JTFObjectValid;
299
300 FUNCTION SelectNameVARCHAR2
301 ( p_select_name IN VARCHAR2
302 ) RETURN VARCHAR2
303 IS
304 BEGIN
305 RETURN p_select_name;
306 END SelectNameVARCHAR2;
307
308 FUNCTION SelectNameVARCHAR2
309 ( p_select_name IN NUMBER
310 ) RETURN VARCHAR2
311 IS
312 BEGIN
313 RETURN TO_CHAR(p_select_name);
314 END SelectNameVARCHAR2;
315
316
317
318 END JTF_NOTES_UTIL_PVT;