DBA Data[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;