[Home] [Help]
PACKAGE BODY: APPS.POR_ITEM_ATTRIBUTES_PKG
Source
1 PACKAGE BODY POR_ITEM_ATTRIBUTES_PKG AS
2 /* $Header: PORATTRB.pls 115.14 2003/11/14 02:46:47 kaholee ship $ */
3
4
5
6 PROCEDURE Create_Attach_Item_Attr(itemtype in varchar2,
7 itemkey in varchar2,
8 actid in number,
9 funcmode in varchar2,
10 resultout out NOCOPY varchar2) IS
11
12 l_req_header_id NUMBER:='';
13 l_progress VARCHAR2(300) := '000';
14 l_doc_string varchar2(200);
15 l_org_id number;
16
17 BEGIN
18
19 IF (funcmode='RUN') THEN
20 l_progress := 'Create_Attach_Item_Attr: 001';
21 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
22
23 -- Set the multi-org context
24
25 l_org_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
26 itemkey => itemkey,
27 aname => 'ORG_ID');
28
29 IF l_org_id is NOT NULL THEN
30
31 fnd_client_info.set_org_context(to_char(l_org_id));
32
33 END IF;
34
35 l_req_header_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
36 itemkey => itemkey,
37 aname => 'DOCUMENT_ID');
38
39 l_progress := 'Create_Attach_Item_Attr: 002 - ' ||
40 to_char(l_req_header_id);
41 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
42
43 add_attribute_attachment(l_req_header_id,
44 'AD_HOC_LOCATION',
45 33,
46 itemtype,
47 itemkey);
48
49 l_progress := 'Create_Attach_Item_Attr: 005 - add_attr_attachment';
50 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
51
52 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
53 return;
54
55 END IF; -- run mode
56 l_progress := 'Create_Attach_Item_Attr: 999';
57 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
58
59 EXCEPTION
60 WHEN OTHERS THEN
61 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
62 wf_core.context('POR_ITEM_ATTRIBUTES_PKG',
63 'Create_Attach_Item_Attr',
64 l_progress,sqlerrm);
65 /* the api for the send_error_notif has been changed to include
66 username and hence commenting out since this is only in the exception
67 block. in a hurry to release patch and no time to figure out*/
68
69 /*PO_REQAPPROVAL_INIT1.send_error_notif(itemType,
70 itemkey,
71 l_doc_string,
72 sqlerrm,
73 'Create_Attach_Item_Attr');*/
74 RAISE;
75
76 END Create_Attach_Item_Attr;
77
78
79 /******************************************************************
80 * Gets Requisition Lines that have associated adhoc data *
81 ******************************************************************/
82 PROCEDURE add_attribute_attachment(p_req_header_id IN NUMBER,
83 p_item_type IN VARCHAR2,
84 p_category_id IN NUMBER DEFAULT 33,
85 p_wf_item_type IN VARCHAR2,
86 p_wf_item_key IN VARCHAR2)
87
88 IS
89
90 l_requisition_header_id NUMBER;
91 l_requisition_line_id NUMBER;
92 l_text LONG := NULL;
93 l_seq_num NUMBER :=0;
94 l_profile_value VARCHAR2(255) := fnd_profile.value('POR_ONE_TIME_LOCATION');
95 l_template_name VARCHAR2(255);
96 l_file_name VARCHAR2(255) := '';
97 l_created_by NUMBER;
98 l_progress LONG :=NULL;
99 l_attachment_id VARCHAR2(255);
100
101 CURSOR c_req_id IS
102 SELECT requisition_header_id,
103 requisition_line_id,
104 created_by
105 FROM po_requisition_lines_all
106 WHERE requisition_header_id = p_req_header_id;
107
108 BEGIN
109
110 l_progress := 'add_attribute_attachment: 000';
111 PO_WF_DEBUG_PKG.insert_debug(p_wf_item_type,p_wf_item_key,l_progress);
112
113 select hrtl2.location_code into l_template_name
114 from hr_locations_all_tl hrtl1, hr_locations_all_tl hrtl2
115 where hrtl1.location_id = hrtl2.location_id
116 and hrtl2.language = userenv('LANG')
117 and hrtl1.location_code = l_profile_value
118 and hrtl1.language in
119 ( select language_code
120 FROM fnd_languages
121 WHERE installed_flag in ( 'B', 'I'))
122 and rownum = 1 ;
123
124
125 OPEN c_req_id;
126 FETCH c_req_id
127 INTO l_requisition_header_id,
128 l_requisition_line_id,
129 l_created_by;
130
131 l_progress := 'add_attribute_attachment: 001 - ' ||
132 ' req_header_id = ' || l_requisition_header_id ||
133 ' req_line_id = ' || l_requisition_line_id ||
134 ' created_by = ' || l_created_by;
135 PO_WF_DEBUG_PKG.insert_debug(p_wf_item_type,p_wf_item_key,l_progress);
136
137 WHILE c_req_id%FOUND LOOP
138 -- Get Max Sequence Number and add 10
139 l_seq_num := 0;
140
141 SELECT NVL(MAX(seq_num), 0)
142 INTO l_seq_num
143 FROM fnd_attached_documents
144 WHERE pk1_value = to_char(l_requisition_line_id)
145 AND entity_name = 'REQ_LINES';
146
147 l_seq_num := l_seq_num + 10;
148
149 l_progress := 'add_attribute_attachment: 002 - ' ||
150 ' l_seq_num = ' || l_seq_num;
151 PO_WF_DEBUG_PKG.insert_debug(p_wf_item_type,p_wf_item_key,l_progress);
152
153 -- Run procedure to get concatenated text string
154 get_attach_text(p_requisition_line_id => l_requisition_line_id,
155 p_requisition_header_id => l_requisition_header_id,
156 p_item_type => p_item_type,
157 p_text => l_text);
158
159 l_progress := 'add_attribute_attachment: 003 - ' ||
160 ' l_text = ' || l_text;
161 PO_WF_DEBUG_PKG.insert_debug(p_wf_item_type,p_wf_item_key,l_progress);
162
163 IF (l_text IS NOT NULL) THEN
164 -- Run add_attachment API to add the attachment to FND tables
165 -- datatype_id is 2 because fnd doesn't support short text in 10.7
166 l_progress := 'add_attribute_attachment: 006 - ' || l_template_name ||
167 ' l_seq_num = ' || l_seq_num ||
168 ' p_category_id = ' || p_category_id ||
169 ' l_file_name = ' || l_file_name ||
170 ' line_id = ' || l_requisition_line_id ||
171 ' l_created_by = ' || l_created_by;
172
173 PO_WF_DEBUG_PKG.insert_debug(p_wf_item_type,p_wf_item_key,l_progress);
174
175
176 begin
177
178 select attached_document_id
179 into l_attachment_id
180 from fnd_attached_documents
181 where pk1_value=to_char(l_requisition_line_id)
182 and pk2_value='ONE_TIME_LOCATION';
183
184 -- prefix document_description with POR: for autocreate to identifer
185 -- this attachment as one time location.
186
187 fnd_webattch.update_attachment(
188 seq_num => l_seq_num ,
189 category_id => p_category_id ,
190 document_description => 'POR:' || l_template_name ,
191 datatype_id => 2 ,
192 text => l_text ,
193 file_name => l_file_name ,
194 url => NULL ,
195 function_name => 'PO_POXRQERQ' ,
196 entity_name => 'REQ_LINES' ,
197 pk1_value => l_requisition_line_id ,
198 pk2_value => 'ONE_TIME_LOCATION' ,
199 pk3_value => NULL ,
200 pk4_value => NULL ,
201 pk5_value => NULL ,
202 media_id => NULL ,
203 user_id => l_created_by ,
204 ATTACHED_DOCUMENT_ID => l_attachment_id);
205
206 EXCEPTION
207 WHEN NO_DATA_FOUND THEN
208
209 fnd_webattch.add_attachment(
210 seq_num => l_seq_num ,
211 category_id => p_category_id ,
212 document_description => 'POR:' || l_template_name ,
213 datatype_id => 2 ,
214 text => l_text ,
215 file_name => l_file_name ,
216 url => NULL ,
217 function_name => 'PO_POXRQERQ' ,
218 entity_name => 'REQ_LINES' ,
219 pk1_value => l_requisition_line_id ,
220 pk2_value => 'ONE_TIME_LOCATION' ,
221 pk3_value => NULL ,
222 pk4_value => NULL ,
223 pk5_value => NULL ,
224 media_id => NULL ,
225 user_id => l_created_by);
226 end;
227
228 END IF;
229
230 FETCH c_req_id
231 INTO l_requisition_header_id,
232 l_requisition_line_id,
233 l_created_by;
234
235 l_progress := 'add_attribute_attachment: 004 - ' ||
236 ' req_header_id = ' || l_requisition_header_id ||
237 ' req_line_id = ' || l_requisition_line_id ||
238 ' created_by = ' || l_created_by;
239 PO_WF_DEBUG_PKG.insert_debug(p_wf_item_type,p_wf_item_key,l_progress);
240
241 END LOOP;
242 CLOSE c_req_id;
243
244 EXCEPTION
245 when others then
246 l_progress := 'add_attribute_attachment: 005 - ' ||
247 'exception';
248 PO_WF_DEBUG_PKG.insert_debug(p_wf_item_type,p_wf_item_key,l_progress);
249
250 END add_attribute_attachment;
251
252
253 /******************************************************************
254 * Concatenate all attribute codes and values into a text value *
255 * for an associated Requisition Line. *
256 ******************************************************************/
257 PROCEDURE get_attach_text(p_requisition_line_id IN NUMBER,
258 p_requisition_header_id IN NUMBER,
259 p_item_type IN VARCHAR2,
260 p_text OUT NOCOPY LONG)
261 IS
262
263 l_text LONG := NULL;
264 l_a1 VARCHAR2(240);
265 l_a2 VARCHAR2(240);
266 l_a3 VARCHAR2(240);
267 l_a4 VARCHAR2(240);
268 l_a5 VARCHAR2(240);
269 l_a6 VARCHAR2(240);
270 l_a7 VARCHAR2(240);
271 l_a8 VARCHAR2(240);
272 l_a9 VARCHAR2(240);
273 l_a10 VARCHAR2(240);
274 l_a11 VARCHAR2(240);
275 l_a12 VARCHAR2(240);
276 l_a13 VARCHAR2(240);
277 l_a14 VARCHAR2(240);
278 l_a15 VARCHAR2(240);
279
280 BEGIN
281
282 SELECT attribute1,
283 attribute2,
284 attribute3,
285 attribute4,
286 attribute5,
287 attribute6,
288 attribute7,
289 attribute8,
290 attribute9,
291 attribute10,
292 attribute11,
293 attribute12,
294 attribute13,
295 attribute14,
296 attribute15
297 INTO l_a1, l_a2, l_a3, l_a4, l_a5,
298 l_a6, l_a7, l_a8, l_a9, l_a10,
299 l_a11, l_a12, l_a13, l_a14, l_a15
300 FROM por_item_attribute_values
301 WHERE item_type = p_item_type
302 AND requisition_header_id = p_requisition_header_id
303 AND requisition_line_id = p_requisition_line_id;
304
305 /* 2977976. l_text should be appended only if the the variables
306 l_a1 .. l_a15 is not null.
307 This is done to avoid insertion of unnecessary
308 line feeds in the attachments even when the values are
309 null. These line feeds in the attachment was causing
310 blank lines to be printed in Printed Purchase Order report
311 */
312
313 l_text := append_if_not_null(l_text,l_a1,l_a2,l_a3,l_a4,l_a5,
314 l_a6,l_a7,l_a8,l_a9,l_a10,
315 l_a11,l_a12,l_a13,l_a14,l_a15);
316
317 p_text := l_text;
318
319 EXCEPTION
320
321 when NO_DATA_FOUND then
322 p_text := NULL;
323
324 END get_attach_text;
325
326 /* 2977976
327 Appends the variables m_a1 to m_a15 to the existing text
328 if existing_text is not null */
329 function append_if_not_null(existing_text IN long,
330 m_a1 IN varchar2,
331 m_a2 IN varchar2,
332 m_a3 IN varchar2,
333 m_a4 IN varchar2,
334 m_a5 IN varchar2,
335 m_a6 IN varchar2,
336 m_a7 IN varchar2,
337 m_a8 IN varchar2,
338 m_a9 IN varchar2,
339 m_a10 IN varchar2,
340 m_a11 IN varchar2,
341 m_a12 IN varchar2,
342 m_a13 IN varchar2,
343 m_a14 IN varchar2,
344 m_a15 IN varchar2)
345 return long
346 IS
347 m_existing_text long :=existing_text;
348 begin
349 if (m_a1 is not null ) then
350 m_existing_text := m_existing_text || m_a1 || fnd_global.local_chr(10);
351 end if;
352
353 if (m_a2 is not null ) then
354 m_existing_text := m_existing_text || m_a2 || fnd_global.local_chr(10);
355 end if;
356
357 if (m_a3 is not null ) then
358 m_existing_text := m_existing_text || m_a3 || fnd_global.local_chr(10);
359 end if;
360
361 if (m_a4 is not null ) then
362 m_existing_text := m_existing_text || m_a4 || fnd_global.local_chr(10);
363 end if;
364
365 if (m_a5 is not null ) then
366 m_existing_text :=m_existing_text || m_a5 || fnd_global.local_chr(10);
367 end if;
368
369 if (m_a6 is not null ) then
370 m_existing_text :=m_existing_text || m_a6 || fnd_global.local_chr(10);
371 end if;
372
373 if (m_a7 is not null ) then
374 m_existing_text :=m_existing_text || m_a7 || fnd_global.local_chr(10);
375 end if;
376
377 if (m_a8 is not null ) then
378 m_existing_text :=m_existing_text || m_a8 || fnd_global.local_chr(10);
379 end if;
380
381 if (m_a9 is not null ) then
382 m_existing_text :=m_existing_text || m_a9 || fnd_global.local_chr(10);
383 end if;
384
385 if (m_a10 is not null ) then
386 m_existing_text :=m_existing_text || m_a10 || fnd_global.local_chr(10);
387 end if;
388
389 if (m_a11 is not null ) then
390 m_existing_text :=m_existing_text || m_a11 || fnd_global.local_chr(10);
391 end if;
392
393 if (m_a12 is not null ) then
394 m_existing_text :=m_existing_text || m_a12 || fnd_global.local_chr(10);
395 end if;
396
397 if (m_a13 is not null ) then
398 m_existing_text :=m_existing_text || m_a13 || fnd_global.local_chr(10);
399 end if;
400
401 if (m_a14 is not null ) then
402 m_existing_text :=m_existing_text || m_a14 || fnd_global.local_chr(10);
403 end if;
404
405 if (m_a15 is not null ) then
406 m_existing_text :=m_existing_text || m_a15 || fnd_global.local_chr(10);
407 end if;
408
409 return m_existing_text;
410
411 end append_if_not_null;
412
413
414
415 END POR_ITEM_ATTRIBUTES_PKG;