[Home] [Help]
PACKAGE BODY: APPS.POR_IFT_INFO_PKG
Source
1 PACKAGE BODY por_ift_info_pkg AS
2 /* $Header: PORIFTB.pls 120.1 2005/12/09 11:02:46 sonmukhe noship $ */
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
107 -- cursor to retrieve requisition line id for a particular req header
108 CURSOR c_req_id IS
109 SELECT porl.requisition_header_id,
110 porl.requisition_line_id,
111 NVL(MAX(porl.created_by), 1) AS created_by
112 FROM por_template_info pti,
113 po_requisition_lines_all porl
114 WHERE porl.requisition_header_id = p_req_header_id
115 AND porl.requisition_line_id = pti.requisition_line_id
116 GROUP BY porl.requisition_header_id,
117 porl.requisition_line_id;
118 BEGIN
119 FOR c_req_id_cur in c_req_id
120 LOOP
121
122 -- Get 'To Supplier' attachment text and name
123 get_attach_text(
124 p_requisition_line_id => c_req_id_cur.requisition_line_id,
125 p_preparer_language => p_preparer_language,
126 p_to_supplier_text => l_to_supplier_text,
127 p_to_supplier_name => l_to_supplier_name,
128 p_to_buyer_text => l_to_buyer_text,
129 p_to_buyer_name => l_to_buyer_name);
130
131 IF l_to_supplier_name is not null
132 THEN
133 BEGIN
134 -- update existing attachment
135 select attach.attached_document_id,attach.seq_num
136 into l_attachment_id,l_seq_num --Get l_seq_num to pass it to update_attachment 2451462.
137 from fnd_attached_documents attach, fnd_documents doc
138 where attach.document_id = doc.document_id
139 and doc.category_id = 33
140 and attach.entity_name = 'REQ_LINES'
141 and attach.pk1_value=to_char(c_req_id_cur.requisition_line_id)
142 and attach.pk2_value='INFO_TEMPLATE';
143
144 fnd_webattch.update_attachment(
145 seq_num => l_seq_num ,
146 category_id => 33 , -- to supplier
147 document_description => l_to_supplier_name ,
148 datatype_id => 2 ,
149 text => l_to_supplier_text ,
150 file_name => l_file_name ,
151 url => NULL ,
152 function_name => 'PO_POXRQERQ' ,
153 entity_name => 'REQ_LINES' ,
154 pk1_value => to_char(c_req_id_cur.requisition_line_id) ,
155 pk2_value => 'INFO_TEMPLATE' ,
156 pk3_value => NULL ,
157 pk4_value => NULL ,
158 pk5_value => NULL ,
159 media_id => NULL ,
160 user_id => c_req_id_cur.created_by ,
161 ATTACHED_DOCUMENT_ID => l_attachment_id);
162
163 EXCEPTION
164 -- insert new attachment
165 WHEN NO_DATA_FOUND THEN
166 l_seq_num := 0;
167
168 SELECT MAX(seq_num)
169 INTO l_seq_num
170 FROM fnd_attached_documents
171 WHERE pk1_value = to_char(c_req_id_cur.requisition_line_id)
172 AND entity_name = 'REQ_LINES';
173
174 IF l_seq_num is null THEN
175 l_seq_num := 10;
176 ELSE
177 l_seq_num := l_seq_num + 10;
178 END IF;
179
180 fnd_webattch.add_attachment(
181 seq_num => l_seq_num ,
182 category_id => 33 ,
183 document_description => l_to_supplier_name ,
184 datatype_id => 2 ,
185 text => l_to_supplier_text ,
186 file_name => l_file_name ,
187 url => NULL ,
188 function_name => 'PO_POXRQERQ' ,
189 entity_name => 'REQ_LINES' ,
190 pk1_value => to_char(c_req_id_cur.requisition_line_id) ,
191 pk2_value => 'INFO_TEMPLATE' ,
192 pk3_value => NULL ,
193 pk4_value => NULL ,
194 pk5_value => NULL ,
195 media_id => NULL ,
196 user_id => c_req_id_cur.created_by);
197 END;
198 END IF;
199
200 IF l_to_buyer_name is not null
201 THEN
202 BEGIN
203 -- update existing attachment
204 select attach.attached_document_id,attach.seq_num
205 into l_attachment_id,l_seq_num --Get l_seq_num to pass it to update_attachment 2451462.
206 from fnd_attached_documents attach, fnd_documents doc
207 where attach.document_id = doc.document_id
208 and doc.category_id = 34 -- to buyer
209 and attach.entity_name = 'REQ_LINES'
210 and attach.pk1_value=to_char(c_req_id_cur.requisition_line_id)
211 and attach.pk2_value='INFO_TEMPLATE';
212
213 fnd_webattch.update_attachment(
214 seq_num => l_seq_num ,
215 category_id => 34 ,
216 document_description => l_to_buyer_name ,
217 datatype_id => 2 ,
218 text => l_to_buyer_text ,
219 file_name => l_file_name ,
220 url => NULL ,
221 function_name => 'PO_POXRQERQ' ,
222 entity_name => 'REQ_LINES' ,
223 pk1_value => to_char(c_req_id_cur.requisition_line_id) ,
224 pk2_value => 'INFO_TEMPLATE' ,
225 pk3_value => NULL ,
226 pk4_value => NULL ,
227 pk5_value => NULL ,
228 media_id => NULL ,
229 user_id => c_req_id_cur.created_by ,
230 ATTACHED_DOCUMENT_ID => l_attachment_id);
231
232 EXCEPTION
233 -- insert new attachment
234 WHEN NO_DATA_FOUND THEN
235 l_seq_num := 0;
236
237 SELECT MAX(seq_num)
238 INTO l_seq_num
239 FROM fnd_attached_documents
240 WHERE pk1_value = to_char(c_req_id_cur.requisition_line_id)
241 AND entity_name = 'REQ_LINES';
242
243 IF l_seq_num is null THEN
244 l_seq_num := 10;
245 ELSE
246 l_seq_num := l_seq_num + 10;
247 END IF;
248
249 fnd_webattch.add_attachment(
250 seq_num => l_seq_num ,
251 category_id => 34 ,
252 document_description => l_to_buyer_name ,
253 datatype_id => 2 ,
254 text => l_to_buyer_text ,
255 file_name => l_file_name ,
256 url => NULL ,
257 function_name => 'PO_POXRQERQ' ,
258 entity_name => 'REQ_LINES' ,
259 pk1_value => to_char(c_req_id_cur.requisition_line_id) ,
260 pk2_value => 'INFO_TEMPLATE' ,
261 pk3_value => NULL ,
262 pk4_value => NULL ,
263 pk5_value => NULL ,
264 media_id => NULL ,
265 user_id => c_req_id_cur.created_by);
266 END;
267 END IF;
268 END LOOP;
269 END add_info_template_attachment;
270
271 END por_ift_info_pkg;