[Home] [Help]
PACKAGE BODY: APPS.POR_ITEM_ATTRIBUTES_PKG
Source
1 PACKAGE BODY POR_ITEM_ATTRIBUTES_PKG AS
2 /* $Header: PORATTRB.pls 120.0.12020000.2 2013/02/08 18:43:21 bpulivar 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 -- for bug 14537896
181 from fnd_attached_documents fad,
182 fnd_documents_tl fdt
183 where pk1_value=to_char(l_requisition_line_id)
184 -- and pk2_value='ONE_TIME_LOCATION';
185 and fad.document_id = fdt.document_id
186 and fad.entity_name='REQ_LINES'
187 and fad.category_id=33
188 and fdt.language = USERENV('LANG')
189 and fdt.description like 'POR:%'
190 and rownum=1;
191 -- end for bug 14537896
192 -- prefix document_description with POR: for autocreate to identifer
193 -- this attachment as one time location.
194
195 fnd_webattch.update_attachment(
196 seq_num => l_seq_num ,
197 category_id => p_category_id ,
198 document_description => 'POR:' || l_template_name ,
199 datatype_id => 2 ,
200 text => l_text ,
201 file_name => l_file_name ,
202 url => NULL ,
203 function_name => 'PO_POXRQERQ' ,
204 entity_name => 'REQ_LINES' ,
205 pk1_value => l_requisition_line_id ,
206 pk2_value => 'ONE_TIME_LOCATION' ,
207 pk3_value => NULL ,
208 pk4_value => NULL ,
209 pk5_value => NULL ,
210 media_id => NULL ,
211 user_id => l_created_by ,
212 ATTACHED_DOCUMENT_ID => l_attachment_id);
213
214 EXCEPTION
215 WHEN NO_DATA_FOUND THEN
216
217 fnd_webattch.add_attachment(
218 seq_num => l_seq_num ,
219 category_id => p_category_id ,
220 document_description => 'POR:' || l_template_name ,
221 datatype_id => 2 ,
222 text => l_text ,
223 file_name => l_file_name ,
224 url => NULL ,
225 function_name => 'PO_POXRQERQ' ,
226 entity_name => 'REQ_LINES' ,
227 pk1_value => l_requisition_line_id ,
228 pk2_value => 'ONE_TIME_LOCATION' ,
229 pk3_value => NULL ,
230 pk4_value => NULL ,
231 pk5_value => NULL ,
232 media_id => NULL ,
233 user_id => l_created_by);
234 end;
235
236 END IF;
237
238 FETCH c_req_id
239 INTO l_requisition_header_id,
240 l_requisition_line_id,
241 l_created_by;
242
243 l_progress := 'add_attribute_attachment: 004 - ' ||
244 ' req_header_id = ' || l_requisition_header_id ||
245 ' req_line_id = ' || l_requisition_line_id ||
246 ' created_by = ' || l_created_by;
247 PO_WF_DEBUG_PKG.insert_debug(p_wf_item_type,p_wf_item_key,l_progress);
248
249 END LOOP;
250 CLOSE c_req_id;
251
252 EXCEPTION
253 when others then
254 l_progress := 'add_attribute_attachment: 005 - ' ||
255 'exception';
256 PO_WF_DEBUG_PKG.insert_debug(p_wf_item_type,p_wf_item_key,l_progress);
257
258 END add_attribute_attachment;
259
260
261 /******************************************************************
262 * Concatenate all attribute codes and values into a text value *
263 * for an associated Requisition Line. *
264 ******************************************************************/
265 PROCEDURE get_attach_text(p_requisition_line_id IN NUMBER,
266 p_requisition_header_id IN NUMBER,
267 p_item_type IN VARCHAR2,
268 p_text OUT NOCOPY LONG)
269 IS
270
271 l_text LONG := NULL;
272 l_a1 VARCHAR2(240);
273 l_a2 VARCHAR2(240);
274 l_a3 VARCHAR2(240);
275 l_a4 VARCHAR2(240);
276 l_a5 VARCHAR2(240);
277 l_a6 VARCHAR2(240);
278 l_a7 VARCHAR2(240);
279 l_a8 VARCHAR2(240);
280 l_a9 VARCHAR2(240);
281 l_a10 VARCHAR2(240);
282 l_a11 VARCHAR2(240);
283 l_a12 VARCHAR2(240);
284 l_a13 VARCHAR2(240);
285 l_a14 VARCHAR2(240);
286 l_a15 VARCHAR2(240);
287
288 BEGIN
289
290 SELECT attribute1,
291 attribute2,
292 attribute3,
293 attribute4,
294 attribute5,
295 attribute6,
296 attribute7,
297 attribute8,
298 attribute9,
299 attribute10,
300 attribute11,
301 attribute12,
302 attribute13,
303 attribute14,
304 attribute15
305 INTO l_a1, l_a2, l_a3, l_a4, l_a5,
306 l_a6, l_a7, l_a8, l_a9, l_a10,
307 l_a11, l_a12, l_a13, l_a14, l_a15
308 FROM por_item_attribute_values
309 WHERE item_type = p_item_type
310 AND requisition_header_id = p_requisition_header_id
311 AND requisition_line_id = p_requisition_line_id;
312
313 /* 2977976. l_text should be appended only if the the variables
314 l_a1 .. l_a15 is not null.
315 This is done to avoid insertion of unnecessary
316 line feeds in the attachments even when the values are
317 null. These line feeds in the attachment was causing
318 blank lines to be printed in Printed Purchase Order report
319 */
320
321 l_text := append_if_not_null(l_text,l_a1,l_a2,l_a3,l_a4,l_a5,
322 l_a6,l_a7,l_a8,l_a9,l_a10,
323 l_a11,l_a12,l_a13,l_a14,l_a15);
324
325 p_text := l_text;
326
327 EXCEPTION
328
329 when NO_DATA_FOUND then
330 p_text := NULL;
331
332 END get_attach_text;
333
334 /* 2977976
335 Appends the variables m_a1 to m_a15 to the existing text
336 if existing_text is not null */
337 function append_if_not_null(existing_text IN long,
338 m_a1 IN varchar2,
339 m_a2 IN varchar2,
340 m_a3 IN varchar2,
341 m_a4 IN varchar2,
342 m_a5 IN varchar2,
343 m_a6 IN varchar2,
344 m_a7 IN varchar2,
345 m_a8 IN varchar2,
346 m_a9 IN varchar2,
347 m_a10 IN varchar2,
348 m_a11 IN varchar2,
349 m_a12 IN varchar2,
350 m_a13 IN varchar2,
351 m_a14 IN varchar2,
352 m_a15 IN varchar2)
353 return long
354 IS
355 m_existing_text long :=existing_text;
356 begin
357 if (m_a1 is not null ) then
358 m_existing_text := m_existing_text || m_a1 || fnd_global.local_chr(10);
359 end if;
360
361 if (m_a2 is not null ) then
362 m_existing_text := m_existing_text || m_a2 || fnd_global.local_chr(10);
363 end if;
364
365 if (m_a3 is not null ) then
366 m_existing_text := m_existing_text || m_a3 || fnd_global.local_chr(10);
367 end if;
368
369 if (m_a4 is not null ) then
370 m_existing_text := m_existing_text || m_a4 || fnd_global.local_chr(10);
371 end if;
372
373 if (m_a5 is not null ) then
374 m_existing_text :=m_existing_text || m_a5 || fnd_global.local_chr(10);
375 end if;
376
377 if (m_a6 is not null ) then
378 m_existing_text :=m_existing_text || m_a6 || fnd_global.local_chr(10);
379 end if;
380
381 if (m_a7 is not null ) then
382 m_existing_text :=m_existing_text || m_a7 || fnd_global.local_chr(10);
383 end if;
384
385 if (m_a8 is not null ) then
386 m_existing_text :=m_existing_text || m_a8 || fnd_global.local_chr(10);
387 end if;
388
389 if (m_a9 is not null ) then
390 m_existing_text :=m_existing_text || m_a9 || fnd_global.local_chr(10);
391 end if;
392
393 if (m_a10 is not null ) then
394 m_existing_text :=m_existing_text || m_a10 || fnd_global.local_chr(10);
395 end if;
396
397 if (m_a11 is not null ) then
398 m_existing_text :=m_existing_text || m_a11 || fnd_global.local_chr(10);
399 end if;
400
401 if (m_a12 is not null ) then
402 m_existing_text :=m_existing_text || m_a12 || fnd_global.local_chr(10);
403 end if;
404
405 if (m_a13 is not null ) then
406 m_existing_text :=m_existing_text || m_a13 || fnd_global.local_chr(10);
407 end if;
408
409 if (m_a14 is not null ) then
410 m_existing_text :=m_existing_text || m_a14 || fnd_global.local_chr(10);
411 end if;
412
413 if (m_a15 is not null ) then
414 m_existing_text :=m_existing_text || m_a15 || fnd_global.local_chr(10);
415 end if;
416
417 return m_existing_text;
418
419 end append_if_not_null;
420
421
422
423 END POR_ITEM_ATTRIBUTES_PKG;