1 PACKAGE BODY GR_ATTACH_DOCUMENTS_PKG AS
2 /* $Header: GRATTCHB.pls 115.7 2004/07/12 20:34:49 methomas noship $ */
3
4 /*===========================================================================
5 -- FUNCTION:
6 -- attach_to_entity
7 --
8 -- DESCRIPTION:
9 -- This PL/SQL function is used to attach a document to an entity (Regulatory Item,
10 -- Inventory Item, etc) based upon the values stored in the attribute columns.
11 -- For the moment it only supports attachment to Regulatory Items.
12 --
13 -- PARAMETERS:
14 -- p_itemtype IN VARCHAR2 - The type of workflow
15 -- p_itemkey IN NUMBER - The key to the workflow record
16 --
17 -- RETURNS:
18 -- YES
19 -- NO
20 --
21 -- SYNOPSIS:
22 -- x_status := GR_ATTACH_DOCUMENTS.attach_to_entity(p_itemtype,p_itemkey);
23 --
24 -- HISTORY
25 --=========================================================================== */
26 PROCEDURE ATTACH_TO_ENTITY(p_itemtype VARCHAR2,
27 p_itemkey VARCHAR2,
28 p_actid NUMBER,
29 p_funcmode VARCHAR2,
30 p_resultout OUT NOCOPY VARCHAR2) AS
31
32
33 /************* Local Variables *************/
34
35 l_event_key varchar2(240);
36 l_file_status varchar2(15);
37 l_category_name VARCHAR2(30);
38 l_entity_name VARCHAR2(80);
39 l_rowid VARCHAR2(200);
40 l_attached_document_id NUMBER;
41 l_document_id NUMBER;
42 l_temp_value VARCHAR2(80);
43 l_pk1_value VARCHAR2(100);
44 l_pk2_value VARCHAR2(100);
45 l_pk3_value VARCHAR2(100);
46 l_pk4_value VARCHAR2(150);
47 l_pk5_value VARCHAR2(150);
48 l_seq NUMBER;
49 l_media NUMBER;
50 l_category_id NUMBER;
51 l_function_name VARCHAR2(200);
52 /* M.Thomas 3756011 07/09/2004 Added the following local variable */
53 l_delivery_detail_id NUMBER;
54 /* M.Thomas 3756011 07/09/2004 End of the changes */
55
56 /***************** Cursors ****************/
57
58 /* Used to get the document information */
59 CURSOR c_doc_info IS
60 SELECT *
61 FROM fnd_documents_vl
62 WHERE document_id = l_document_id;
63 l_doc c_doc_info%ROWTYPE;
64
65 /* Used to see if this attachment already exists */
66 CURSOR c_attachment_exists IS
67 SELECT rowid, attached_document_id, seq_num
68 FROM fnd_attached_documents
69 WHERE entity_name = l_entity_name AND
70 NVL(pk1_value,'NULL') = NVL(l_pk1_value,'NULL') AND
71 NVL(pk2_value,'NULL') = NVL(l_pk2_value,'NULL') AND
72 NVL(pk3_value,'NULL') = NVL(l_pk3_value,'NULL') AND
73 NVL(pk4_value,'NULL') = NVL(l_pk4_value,'NULL') AND
74 NVL(pk5_value,'NULL') = NVL(l_pk5_value,'NULL') AND
75 attribute_category = l_doc.doc_attribute_category AND
76 attribute1 = l_doc.doc_attribute1 AND
77 attribute2 = l_doc.doc_attribute2 AND
78 attribute3 = l_doc.doc_attribute3 AND
79 attribute4 = l_doc.doc_attribute4 AND
80 attribute5 = l_doc.doc_attribute5;
81
82 /* Used to get the next attached_document_id for a new attachment */
83 CURSOR c_get_id IS
84 SELECT fnd_attached_documents_s.nextval
85 FROM dual;
86
87 /* Used to get the next sequence number for a new attachment */
88 CURSOR c_get_seq IS
89 SELECT NVL(max(seq_num),0) + 10
90 FROM fnd_attached_documents
91 WHERE entity_name = l_entity_name AND
92 NVL(pk1_value,'NULL') = NVL(l_pk1_value,'NULL') AND
93 NVL(pk2_value,'NULL') = NVL(l_pk2_value,'NULL') AND
94 NVL(pk3_value,'NULL') = NVL(l_pk3_value,'NULL') AND
95 NVL(pk4_value,'NULL') = NVL(l_pk4_value,'NULL') AND
96 NVL(pk5_value,'NULL') = NVL(l_pk5_value,'NULL');
97
98 /* Used to get category ID for MSDS_REJECTED */
99 CURSOR c_get_category_id IS
100 SELECT category_id
101 FROM fnd_document_categories
102 WHERE name = 'MSDS_REJECTED';
103
104 /* Used to get list of functions for the categories */
105 CURSOR c_get_entity_info IS
106 SELECT FUNCTION_NAME
107 FROM FND_DOC_CATEGORY_USAGES_VL
108 WHERE name in ('MSDS_REG_ITEM', 'MSDS_INV_ITEM', 'MSDS_SALES_ORDER');
109
110 /* M.Thomas 3756011 07/09/2004 The following cursor has been added to get the delivery detail line information */
111
112 /* Used to get the delivery detail id for entity WSH_DELIVERY_DETAILS */
113 CURSOR c_get_delivery_detail_id IS
114 SELECT delivery_detail_id
115 FROM WSH_DELIVERABLES_V
116 WHERE container_flag = 'N'
117 and source_code = 'OE'
118 and released_status in ('N', 'R', 'S', 'Y', 'B', 'X')
119 and inventory_item_id = (select distinct inventory_item_id from mtl_system_items where segment1 = l_doc.doc_attribute1)
120 and organization_id = (SELECT organization_id FROM mtl_parameters WHERE organization_code = l_doc.doc_attribute5)
121 and source_header_number = l_doc.doc_attribute8
122 and source_line_number = to_number(l_doc.doc_attribute9)
123 Order by delivery_detail_id;
124 /* M.Thomas 3756011 07/09/2004 End of the changes */
125
126 BEGIN
127
128 /* M.Thomas 3211481 The following change as been made to the initialization due to the GSCC warning */
129 l_event_key := WF_ENGINE.GETITEMATTRTEXT(
130 itemtype=>p_itemtype,
131 itemkey=>P_itemkey,
132 aname=>'EVENT_KEY');
133
134 l_file_status := WF_ENGINE.GETITEMATTRTEXT(
135 itemtype=>p_itemtype,
136 itemkey=>P_itemkey,
137 aname=>'FILE_STATUS');
138 /* M.Thomas 3211481 End of the changes */
139
140 /* Get the category name */
141 EDR_FILE_UTIL_PUB.get_category_name(l_event_key, l_category_name);
142
143 /* M. Grosser 11-Jan-2004 BUG 3359911 - Modified to account for change
144 in event status from SUCCESS to NO APPROVAL
145 for no approval required uploads.
146 */
147 /* Only execute this code if the document has been approved */
148 IF (l_file_status = 'SUCCESS' OR l_file_status = 'NO APPROVAL') THEN
149
150 /* Set up table name for the attachment. Only attaching to regulatory items for this pass */
151 IF (l_category_name = 'MSDS_REG_ITEM') THEN
152 l_entity_name := 'GR_ITEM_GENERAL';
153 /* M. Thomas Bug 3211481 14-May-2004 Added the following to attach the document to the Shipment */
154 ELSIF (l_category_name = 'MSDS_SALES_ORDER') THEN
155 l_entity_name := 'WSH_DELIVERY_DETAILS';
156 /* M. Thomas Bug 3211481 14-May-2004 End of the following changes. */
157 END IF;
158
159 IF l_entity_name is NOT NULL THEN
160
161 /* Get the document ID */
162 EDR_FILE_UTIL_PUB.get_attribute(l_event_key, 'fnd_document_id',l_temp_value);
163 l_document_id := TO_NUMBER(l_temp_value);
164
165 /* Get the document information */
166 OPEN c_doc_info;
167 FETCH c_doc_info INTO l_doc;
168 CLOSE c_doc_info;
169
170 /* Set up the key fields for the table */
171 IF (l_category_name = 'MSDS_REG_ITEM') THEN
172 l_pk1_value :=l_doc.doc_attribute1;
173 l_pk2_value := NULL;
174 l_pk3_value := NULL;
175 l_pk4_value := NULL;
176 l_pk5_value := NULL;
177 END IF;
178
179 /* Set up the key fields for the table */
180 /* M. Thomas Bug 3211481 14-May-2004 Added the following to update the key fields for the attached document for a Shipment */
181 IF (l_category_name = 'MSDS_SALES_ORDER') THEN
182
183 /* M.Thomas 3756011 07/09/2004 the following code has been added in order to insert/update
184 the primary key delivery detail line id instead of sales order number for the entity WSH_DELIVERY_DETAILS */
185
186 /* Get the Primary Key delivery Detail Id for the entity WSH_DELIVERY_DETAILS */
187 OPEN c_get_delivery_detail_id;
188 FETCH c_get_delivery_detail_id INTO l_delivery_detail_id;
189 CLOSE c_get_delivery_detail_id;
190
191 If l_delivery_detail_id IS NOT NULL THEN
192 l_pk1_value :=l_delivery_detail_id;
193 END IF;
194 /* M.Thomas 3756011 07/09/2004 End of the code changes */
195
196 l_pk2_value := NULL;
197 l_pk3_value := NULL;
198 l_pk4_value := NULL;
199 l_pk5_value := NULL;
200 END IF;
201 /* M. Thomas Bug 3211481 14-May-2004 End of the changes */
202
203 /* Check to see if a previous version of this document has already been attached */
204 OPEN c_attachment_exists;
205 FETCH c_attachment_exists INTO l_rowid, l_attached_document_id, l_seq;
206
207 /* If not, attach the new document */
208 IF c_attachment_exists%NOTFOUND THEN
209
210 /* Get the next id for attached documents */
211 OPEN c_get_id;
212 FETCH c_get_id INTO l_attached_document_id;
213 CLOSE c_get_id;
214
215 /* Get the next sequence number */
216 OPEN c_get_seq;
217 FETCH c_get_seq INTO l_seq;
218 IF c_get_seq%NOTFOUND THEN
219 l_seq := 10;
220 END IF;
221 CLOSE c_get_seq;
222
223 /* M. Grosser 11-Jan-2004 BUG 3359911 - Modified to correct overwrite of document
224 description
225 */
226 FND_ATTACHED_DOCUMENTS_PKG.Insert_Row(X_Rowid => l_Rowid,
227 X_attached_document_id => l_attached_document_id,
228 X_document_id => l_doc.document_id,
229 X_creation_date => SYSDATE,
230 X_created_by => l_doc.created_by,
231 X_last_update_date => SYSDATE,
232 X_last_updated_by => l_doc.last_updated_by,
233 X_last_update_login => NULL,
234 X_seq_num => l_seq,
235 X_entity_name => l_entity_name,
236 X_column1 => NULL,
237 X_pk1_value => l_pk1_value,
238 X_pk2_value => l_pk2_value,
239 X_pk3_value => l_pk3_value,
240 X_pk4_value => l_pk4_value,
241 X_pk5_value => l_pk5_value,
242 X_automatically_added_flag => 'Y',
243 X_datatype_id => l_doc.datatype_id,
244 X_category_id => l_doc.category_id,
245 X_security_type => l_doc.security_type,
246 X_security_id => l_doc.security_id,
247 X_publish_flag => l_doc.publish_flag,
248 X_storage_type => l_doc.storage_type,
249 X_usage_type => l_doc.usage_type,
250 X_language => l_doc.doc_attribute3,
251 X_description => l_doc.description,
252 X_file_name => l_doc.file_name,
253 X_media_id => l_doc.media_id,
254 X_attribute_category => l_doc.doc_attribute_category,
255 X_attribute1 => l_doc.doc_attribute1,
256 X_attribute2 => l_doc.doc_attribute2,
257 X_attribute3 => l_doc.doc_attribute3,
258 X_attribute4 => l_doc.doc_attribute4,
259 X_attribute5 => l_doc.doc_attribute5,
260 X_attribute6 => l_doc.doc_attribute6,
261 X_attribute7 => l_doc.doc_attribute7,
262 X_attribute8 => l_doc.doc_attribute8,
263 X_attribute9 => l_doc.doc_attribute9,
264 X_attribute10 => l_doc.doc_attribute10,
265 X_attribute11 => l_doc.doc_attribute11,
266 X_attribute12 => l_doc.doc_attribute12,
267 X_attribute13 => l_doc.doc_attribute13,
268 X_attribute14 => l_doc.doc_attribute14,
269 X_attribute15 => l_doc.doc_attribute15,
270 X_create_doc => 'N');
271 ELSE
272 /* The attachment already exists so we will just update it with the new file */
273 fnd_attached_documents_pkg.update_row(
274 X_rowid => l_Rowid,
275 X_attached_document_id => l_attached_document_id,
276 X_document_id => l_document_id,
277 X_last_update_date => SYSDATE,
278 X_last_updated_by => l_doc.last_updated_by,
279 X_last_update_login => NULL,
280 X_seq_num => l_seq,
281 X_column1 => NULL,
282 X_entity_name => l_entity_name,
283 X_pk1_value => l_pk1_value,
284 X_pk2_value => l_pk2_value,
285 X_pk3_value => l_pk3_value,
286 X_pk4_value => l_pk4_value,
287 X_pk5_value => l_pk5_value,
288 X_automatically_added_flag => 'Y',
289 X_request_id => NULL,
290 X_program_application_id => NULL,
291 X_program_id => NULL,
292 X_program_update_date => NULL,
293 X_attribute_category => l_doc.doc_attribute_category,
294 X_attribute1 => l_doc.doc_attribute1,
295 X_attribute2 => l_doc.doc_attribute2,
296 X_attribute3 => l_doc.doc_attribute3,
297 X_attribute4 => l_doc.doc_attribute4,
298 X_attribute5 => l_doc.doc_attribute5,
299 X_attribute6 => l_doc.doc_attribute6,
300 X_attribute7 => l_doc.doc_attribute7,
301 X_attribute8 => l_doc.doc_attribute8,
302 X_attribute9 => l_doc.doc_attribute9,
303 X_attribute10 => l_doc.doc_attribute10,
304 X_attribute11 => l_doc.doc_attribute11,
305 X_attribute12 => l_doc.doc_attribute12,
306 X_attribute13 => l_doc.doc_attribute13,
307 X_attribute14 => l_doc.doc_attribute14,
308 X_attribute15 => l_doc.doc_attribute15,
309 X_datatype_id => l_doc.datatype_id,
310 X_category_id => l_doc.category_id,
311 X_security_type => l_doc.security_type,
312 X_security_id => l_doc.security_id,
313 X_publish_flag => l_doc.publish_flag,
314 X_image_type => l_doc.image_type,
315 X_storage_type => l_doc.storage_type,
316 X_usage_type => l_doc.usage_type,
317 X_start_date_active => SYSDATE,
318 X_end_date_active => NULL ,
319 X_language => l_doc.doc_attribute3,
320 X_description => l_doc.description,
321 X_file_name => l_doc.file_name,
322 X_media_id => l_doc.media_id);
323
324 /* M. Grosser 11-Jan-2004 BUG 3359911 - End of changes */
325
326 END IF; /* If attachment already exists */
327 END IF; /* If entity name is not NULL */
328 p_resultout := G_YES;
329 CLOSE c_get_entity_info;
330 ELSE /* Document has not been approved, don't attach it */
331
332 IF (l_category_name in ('MSDS_REG_ITEM','MSDS_INV_ITEM','MSDS_SALES_ORDER','MSDS_RECIPIENT')) THEN
333 /* Get the document ID */
334 EDR_FILE_UTIL_PUB.get_attribute(l_event_key, 'fnd_document_id',l_temp_value);
335 l_document_id := TO_NUMBER(l_temp_value);
336
337 /* Get the category id for MSDS_REJECTED */
338 OPEN c_get_category_id;
339 FETCH c_get_category_id INTO l_category_id;
340 CLOSE c_get_category_id;
341
342 /* Change the document category to the REJECTED category to segregate it out from
343 the approved documents. There is no ther way to determine that is has been rejected */
344 UPDATE fnd_documents
345 SET category_id = l_category_id
346 WHERE document_id = l_document_id;
347
348 END IF; /* If this is an MSDS document */
349
350 p_resultout := G_NO;
351
352 END IF; /* If the document has been approved */
353
354
355 EXCEPTION
356 WHEN OTHERS THEN
357 p_resultout := G_NO;
358
359 END attach_to_entity;
360
361 END GR_ATTACH_DOCUMENTS_PKG;
[Home] [Help]