[Home] [Help]
PACKAGE BODY: APPS.POR_IFT_INFO_PKG
Source
1 PACKAGE BODY por_ift_info_pkg AS
2 /* $Header: PORIFTB.pls 120.2 2011/12/20 10:05:36 krsethur ship $ */
3
4 /******************************************************************
5 * Concatenate all attribute codes and values into a text value for an associated *
6 * Requisition Line. *
7 ******************************************************************/
8 PROCEDURE get_attach_text(
9 p_requisition_line_id IN NUMBER,
10 p_preparer_language IN VARCHAR2,
11 p_to_supplier_text OUT NOCOPY LONG,
12 p_to_supplier_name OUT NOCOPY VARCHAR2,
13 p_to_buyer_text OUT NOCOPY LONG,
14 p_to_buyer_name OUT NOCOPY VARCHAR2) IS
15
16 l_to_supplier_text LONG := NULL;
17 l_to_buyer_text LONG := NULL;
18 l_to_supplier_name VARCHAR2(240) :=NULL;
19 l_to_buyer_name VARCHAR2(240) :=NULL;
20 l_newline varchar2(10) := '
21 ';
22
23 -- cursor to get all info template attribute information
24 -- input parameter: attachment category id
25 CURSOR c_attributes (p_category_id IN NUMBER) IS
26 SELECT ATT.DESCRIPTION,
27 decode(ATB.FLEX_VALUE_SET_ID, null, INFO.ATTRIBUTE_VALUE,
28 (select VTL.FLEX_VALUE_MEANING
29 from FND_FLEX_VALUES VAL,
30 FND_FLEX_VALUES_TL VTL
31 where INFO.ATTRIBUTE_VALUE = VAL.FLEX_VALUE
32 AND VAL.FLEX_VALUE_SET_ID = ATB.FLEX_VALUE_SET_ID
33 AND VAL.FLEX_VALUE_ID = VTL.FLEX_VALUE_ID
34 AND VTL.LANGUAGE = NVL(p_preparer_language, USERENV('LANG')))) value,
35 TMPT.TEMPLATE_NAME
36 FROM POR_TEMPLATE_INFO INFO,
37 POR_TEMPLATES_ALL_B TMPB,
38 POR_TEMPLATES_ALL_TL TMPT,
39 POR_TEMPLATE_ATTRIBUTES_B ATB,
40 POR_TEMPLATE_ATTRIBUTES_TL ATT
41 WHERE INFO.REQUISITION_LINE_ID = p_requisition_line_id
42 AND INFO.ATTRIBUTE_CODE = ATB.ATTRIBUTE_CODE
43 AND TMPB.ATTACHMENT_CATEGORY_ID = p_category_id
44 AND TMPB.TEMPLATE_CODE = TMPT.TEMPLATE_CODE
45 AND TMPT.LANGUAGE = NVL(p_preparer_language, USERENV('LANG'))
46 AND TMPB.TEMPLATE_CODE = ATB.TEMPLATE_CODE
47 AND ATB.ATTRIBUTE_CODE = ATT.ATTRIBUTE_CODE
48 AND ATT.LANGUAGE = NVL(p_preparer_language, USERENV('LANG'))
49 ORDER BY
50 TMPB.TEMPLATE_CODE, ATB.SEQUENCE;
51
52 BEGIN
53 -- Get 'To Supplier' attachment text and name
54 FOR c_att_cur IN c_attributes(33) LOOP
55 IF l_to_supplier_text IS NULL then
56 l_to_supplier_text := c_att_cur.description|| '=' || c_att_cur.value;
57 l_to_supplier_name := c_att_cur.template_name;
58 ELSE
59 l_to_supplier_text := l_to_supplier_text || l_newline || c_att_cur.description|| '=' || c_att_cur.value;
60 IF(l_to_supplier_name <> c_att_cur.template_name)
61 THEN
62 l_to_supplier_name := fnd_message.get_string('ICX', 'ICX_POR_MULTIPLE_TEMPLATES');
63 END IF;
64 END IF;
65 END LOOP;
66
67 p_to_supplier_text := l_to_supplier_text;
68 p_to_supplier_name := l_to_supplier_name;
69
70 -- Get 'To Buyer' attachment text and name
71 FOR c_att_cur IN c_attributes(34) LOOP
72 IF l_to_buyer_text IS NULL then
73 l_to_buyer_text := c_att_cur.description|| '=' || c_att_cur.value;
74 l_to_buyer_name := c_att_cur.template_name;
75 ELSE
76 l_to_buyer_text := l_to_buyer_text || l_newline || c_att_cur.description|| '=' || c_att_cur.value;
77 IF(l_to_buyer_name <> c_att_cur.template_name)
78 THEN
79 l_to_buyer_name := fnd_message.get_string('ICX', 'ICX_POR_MULTIPLE_TEMPLATES');
80 END IF;
81 END IF;
82 END LOOP;
83
84 p_to_buyer_text := l_to_buyer_text;
85 p_to_buyer_name := l_to_buyer_name;
86
87 END get_attach_text;
88
89 /******************************************************************
90 * Gets Requisition Lines that have associated info template data *
91 ******************************************************************/
92 PROCEDURE add_info_template_attachment(
93 p_req_header_id IN NUMBER,
94 p_category_id IN NUMBER DEFAULT 33,
95 p_preparer_language IN VARCHAR2)
96
97 IS
98
99 l_to_supplier_text LONG := NULL;
100 l_to_buyer_text LONG := NULL;
101 l_file_name VARCHAR2(255) := '';
102 l_seq_num NUMBER :=0;
103 l_to_supplier_name VARCHAR2(255);
104 l_to_buyer_name VARCHAR2(255);
105 l_attachment_id VARCHAR2(255);
106 l_datatype_id number;
107
108 -- cursor to retrieve requisition line id for a particular req header
109 CURSOR c_req_id IS
110 SELECT porl.requisition_header_id,
111 porl.requisition_line_id,
112 NVL(MAX(porl.created_by), 1) AS created_by
113 FROM por_template_info pti,
114 po_requisition_lines_all porl
115 WHERE porl.requisition_header_id = p_req_header_id
116 AND porl.requisition_line_id = pti.requisition_line_id
117 GROUP BY porl.requisition_header_id,
118 porl.requisition_line_id;
119 BEGIN
120 FOR c_req_id_cur in c_req_id
121 LOOP
122
123 -- Get 'To Supplier' attachment text and name
124 get_attach_text(
125 p_requisition_line_id => c_req_id_cur.requisition_line_id,
126 p_preparer_language => p_preparer_language,
127 p_to_supplier_text => l_to_supplier_text,
128 p_to_supplier_name => l_to_supplier_name,
129 p_to_buyer_text => l_to_buyer_text,
130 p_to_buyer_name => l_to_buyer_name);
131
132 IF l_to_supplier_name is not null
133 THEN
134 BEGIN
135 -- update existing attachment
136 select attach.attached_document_id,attach.seq_num
137 into l_attachment_id,l_seq_num --Get l_seq_num to pass it to update_attachment 2451462.
138 from fnd_attached_documents attach, fnd_documents doc
139 where attach.document_id = doc.document_id
140 and doc.category_id = 33
141 and attach.entity_name = 'REQ_LINES'
142 and attach.pk1_value=to_char(c_req_id_cur.requisition_line_id)
143 and attach.pk2_value='INFO_TEMPLATE';
144
145 if(lengthb(l_to_supplier_text) <4000) then
146 l_datatype_id := 1;
147 else
148 l_datatype_id := 2;
149 end if;
150
151 fnd_webattch.update_attachment(
152 seq_num => l_seq_num ,
153 category_id => 33 , -- to supplier
154 document_description => l_to_supplier_name ,
155 datatype_id => l_datatype_id ,
156 text => l_to_supplier_text ,
157 file_name => l_file_name ,
158 url => NULL ,
159 function_name => 'PO_POXRQERQ' ,
160 entity_name => 'REQ_LINES' ,
161 pk1_value => to_char(c_req_id_cur.requisition_line_id) ,
162 pk2_value => 'INFO_TEMPLATE' ,
163 pk3_value => NULL ,
164 pk4_value => NULL ,
165 pk5_value => NULL ,
166 media_id => NULL ,
167 user_id => c_req_id_cur.created_by ,
168 ATTACHED_DOCUMENT_ID => l_attachment_id);
169
170 EXCEPTION
171 -- insert new attachment
172 WHEN NO_DATA_FOUND THEN
173 l_seq_num := 0;
174
175 SELECT MAX(seq_num)
176 INTO l_seq_num
177 FROM fnd_attached_documents
178 WHERE pk1_value = to_char(c_req_id_cur.requisition_line_id)
179 AND entity_name = 'REQ_LINES';
180
181 IF l_seq_num is null THEN
182 l_seq_num := 10;
183 ELSE
184 l_seq_num := l_seq_num + 10;
185 END IF;
186
187 if(lengthb(l_to_supplier_text) <4000) then
188 l_datatype_id := 1;
189 else
190 l_datatype_id := 2;
191 end if;
192
193
194 fnd_webattch.add_attachment(
195 seq_num => l_seq_num ,
196 category_id => 33 ,
197 document_description => l_to_supplier_name ,
198 datatype_id => l_datatype_id ,
199 text => l_to_supplier_text ,
200 file_name => l_file_name ,
201 url => NULL ,
202 function_name => 'PO_POXRQERQ' ,
203 entity_name => 'REQ_LINES' ,
204 pk1_value => to_char(c_req_id_cur.requisition_line_id) ,
205 pk2_value => 'INFO_TEMPLATE' ,
206 pk3_value => NULL ,
207 pk4_value => NULL ,
208 pk5_value => NULL ,
209 media_id => NULL ,
210 user_id => c_req_id_cur.created_by);
211 END;
212 END IF;
213
214 IF l_to_buyer_name is not null
215 THEN
216 BEGIN
217 -- update existing attachment
218 select attach.attached_document_id,attach.seq_num
219 into l_attachment_id,l_seq_num --Get l_seq_num to pass it to update_attachment 2451462.
220 from fnd_attached_documents attach, fnd_documents doc
221 where attach.document_id = doc.document_id
222 and doc.category_id = 34 -- to buyer
223 and attach.entity_name = 'REQ_LINES'
224 and attach.pk1_value=to_char(c_req_id_cur.requisition_line_id)
225 and attach.pk2_value='INFO_TEMPLATE';
226
227 if(lengthb(l_to_buyer_text) <4000) then
228 l_datatype_id := 1;
229 else
230 l_datatype_id := 2;
231 end if;
232
233 fnd_webattch.update_attachment(
234 seq_num => l_seq_num ,
235 category_id => 34 ,
236 document_description => l_to_buyer_name ,
237 datatype_id => l_datatype_id,
238 text => l_to_buyer_text ,
239 file_name => l_file_name ,
240 url => NULL ,
241 function_name => 'PO_POXRQERQ' ,
242 entity_name => 'REQ_LINES' ,
243 pk1_value => to_char(c_req_id_cur.requisition_line_id) ,
244 pk2_value => 'INFO_TEMPLATE' ,
245 pk3_value => NULL ,
246 pk4_value => NULL ,
247 pk5_value => NULL ,
248 media_id => NULL ,
249 user_id => c_req_id_cur.created_by ,
250 ATTACHED_DOCUMENT_ID => l_attachment_id);
251
252 EXCEPTION
253 -- insert new attachment
254 WHEN NO_DATA_FOUND THEN
255 l_seq_num := 0;
256
257 SELECT MAX(seq_num)
258 INTO l_seq_num
259 FROM fnd_attached_documents
260 WHERE pk1_value = to_char(c_req_id_cur.requisition_line_id)
261 AND entity_name = 'REQ_LINES';
262
263 IF l_seq_num is null THEN
264 l_seq_num := 10;
265 ELSE
266 l_seq_num := l_seq_num + 10;
267 END IF;
268
269 if(lengthb(l_to_buyer_text) <4000) then
270 l_datatype_id := 1;
271 else
272 l_datatype_id := 2;
273 end if;
274
275
276 fnd_webattch.add_attachment(
277 seq_num => l_seq_num ,
278 category_id => 34 ,
279 document_description => l_to_buyer_name ,
280 datatype_id => l_datatype_id ,
281 text => l_to_buyer_text ,
282 file_name => l_file_name ,
283 url => NULL ,
284 function_name => 'PO_POXRQERQ' ,
285 entity_name => 'REQ_LINES' ,
286 pk1_value => to_char(c_req_id_cur.requisition_line_id) ,
287 pk2_value => 'INFO_TEMPLATE' ,
288 pk3_value => NULL ,
289 pk4_value => NULL ,
290 pk5_value => NULL ,
291 media_id => NULL ,
292 user_id => c_req_id_cur.created_by);
293 END;
294 END IF;
295 END LOOP;
296 END add_info_template_attachment;
297
298 END por_ift_info_pkg;