[Home] [Help]
PACKAGE BODY: APPS.PA_PCO_PKG
Source
1 package body PA_PCO_PKG as
2 /* $Header: PAPCORPB.pls 120.2.12010000.1 2009/07/20 10:03:14 sosharma noship $ */
3
4 /*
5 Procedure : create_fnd_attachment
6 Type : Public
7 Purpose : This procedure first checks for the existance of a record in FND_LOBS for the passed primary_key values.
8 In this case, p_CR_id and p_CR_version_number form the primary key.
9 If a record is found, then only the data in FND_LOBS is deleted, and reinserted with empty_blob.
10 In case match is not found, an additional step is performed to insert data into fnd_documents and fnd_attached_documents
11 also. This is done by calling fnd_attached_documents_pkg.Insert_Row.
12
13 Parameters Type Required Description and Purpose
14 ========== ==== ======== =======================
15 p_doc_category_name VARCHAR YES identifier in FND_LOBS
16 p_entity_name VARCHAR YES identifier in FND_LOBS
17 p_file_name VARCHAR YES Name of the attachment
18 p_file_content_type VARCHAR YES Content type of the attachment
19 p_CR_id VARCHAR YES primary key 1 value (Change req id)
20 p_CR_version_number VARCHAR YES primary key 1 value (Version number)
21 p_file_id NUMBER NO file_id in FND_LOBS
22 */
23
24
25 procedure create_fnd_attachment
26 (
27 p_doc_category_name varchar,
28 p_entity_name varchar,
29 p_file_name fnd_lobs.file_name%type,
30 p_file_content_type fnd_lobs.file_content_type%type,
31 p_CR_id varchar,
32 p_CR_version_number varchar,
33 p_file_id IN OUT nocopy number)
34 is
35
36
37 x_Rowid varchar2(100);
38 x_document_id number;
39 x_media_id number;
40 l_attached_document_id number;
41
42
43 l_blob_data blob;
44 l_category_id number;
45 l_entity_name varchar2(100);
46
47 l_doc_category_name varchar2(100);
48 l_file_name fnd_lobs.file_name%type;
49 l_file_content_type fnd_lobs.file_content_type%type;
50 l_call_Insert_Row boolean := False;
51
52
53 begin
54
55 pa_debug.debug('100: Inside create_fnd_attachment');
56
57 /* sosharma The doc_category_name parameters are redundant now as we would be storing the attachments with category_id = 1 */
58 -- l_doc_category_name :=p_doc_category_name;
59 l_entity_name := p_entity_name;
60 l_file_name := p_file_name;
61 l_file_content_type := p_file_content_type;
62 l_blob_data := empty_blob();
63
64
65 /* sosharma
66 Commented as per change in implementation
67 SELECT category_id
68 into l_category_id
69 from fnd_document_categories
70 where name = l_doc_category_name;*/
71
72
73 l_category_id := 1;
74
75 pa_debug.debug('200: Calling get_attachment_file_id ');
76
77 get_attachment_file_id
78 (p_entity_name,
79 p_CR_id,
80 p_CR_version_number,
81 null,
82 null,
83 null,
84 p_file_id);
85
86 /* if p_file_id is returned as Null, it means attachments have not been created for this version of the change request.
87 Next value of X_media_id is derived, and l_call_Insert_Row is set to True to invoke fnd_attached_documents_pkg.Insert_Row to insert
88 new records into fnd_documents and fnd_attached_documents.
89
90 Otherwise p_file_id conatins the file_id of the existing attachment, which is then deleted and record is inserted only into FND_LOBS.
91 */
92 if p_file_id is null then
93
94 SELECT fnd_lobs_s.nextval
95 INTO X_media_id
96 FROM dual;
97
98 l_call_Insert_Row := TRUE;
99 else
100
101 /* delete the existing attachment from fnd_lobs */
102
103 delete from fnd_lobs
104 where file_id = p_file_id;
105
106 X_media_id := p_file_id;
107 l_call_Insert_Row := False;
108
109 end if;
110
111
112 pa_debug.debug('300: x_media_id:' || x_media_id || ', l_file_name:' || l_file_name);
113
114 INSERT INTO fnd_lobs (
115 file_id
116 , File_name
117 , file_content_type
118 , upload_date
119 , expiration_date
120 , program_name
121 , program_tag
122 , file_data
123 , language
124 , oracle_charset
125 , file_format)
126 VALUES
127 ( x_media_id
128 , l_file_name
129 , l_file_content_type
130 , sysdate
131 , null
132 , 'PA_PCO_REPORT'
133 , null
134 , l_blob_data
135 , null
136 , null
137 , 'binary');
138
139 pa_debug.debug('400: After Insertion into fnd_lobs ');
140
141 select fnd_attached_documents_s.nextval
142 into l_attached_document_id
143 from dual;
144
145
146 /* l_call_Insert_Row would be true only if its the first call for this version of the change request to create an attachment.
147 Only in that case, there is a need to insert records into fnd_documents and fnd_attached_documents
148 */
149 if (l_call_Insert_Row) then
150 pa_debug.debug('500: Calling fnd_attached_documents_pkg.Insert_Row');
151
152 fnd_attached_documents_pkg.Insert_Row(
153 X_Rowid => X_Rowid
154 , X_attached_document_id => l_attached_document_id
155 , X_document_id => X_document_id
156 , X_creation_date => SYSDATE
157 , X_created_by => 1
158 , X_last_update_date => SYSDATE
159 , X_last_updated_by => 1
160 , X_last_update_login => 1
161 , X_seq_num => 10
162 , X_entity_name => l_entity_name
163 , X_column1 => NULL
164 , X_pk1_value => p_CR_id
165 , X_pk2_value => p_CR_version_number
166 , X_pk3_value => NULL
167 , X_pk4_value => NULL
168 , X_pk5_value => NULL
169 , X_automatically_added_flag => 'N'
170 /* columns necessary for creating a document on the fly */
171 , X_datatype_id => 6
172 , X_category_id => l_category_id
173 , X_security_type => 1
174 , X_publish_flag => 'Y'
175 , X_usage_type => 'S'
176 , X_language => NULL
177 , X_media_id => X_media_id
178 , X_doc_attribute_Category => NULL
179 , X_doc_attribute1 => NULL
180 , X_doc_attribute2 => NULL
181 , X_doc_attribute3 => NULL
182 , X_doc_attribute4 => NULL
183 , X_doc_attribute5 => NULL
184 , X_doc_attribute6 => NULL
185 , X_doc_attribute7 => NULL
186 , X_doc_attribute8 => NULL
187 , X_doc_attribute9 => NULL
188 , X_doc_attribute10 => NULL
189 , X_doc_attribute11 => NULL
190 , X_doc_attribute12 => NULL
191 , X_doc_attribute13 => NULL
192 , X_doc_attribute14 => NULL
193 , X_doc_attribute15 => NULL
194 );
195
196 pa_debug.debug('600: After call to fnd_attached_documents_pkg.Insert_Row ');
197
198 else
199 pa_debug.debug('700: Attachment records exist already, so not creating fresh records ');
200 end if;
201
202 p_file_id := x_media_id;
203
204 exception
205 when others then
206 p_file_id := null;
207 pa_debug.debug('700: In exception block...');
208 pa_debug.debug('SQLERRM:' || SQLERRM || ', SQLCODE:' || SQLCODE);
209 end create_fnd_attachment;
210
211
212
213 /*
214 Procedure : get_attachment_file_id
215 Type : Public
216 Purpose : This procedure returns the file_id of the FND attachment. It accepts the primary key values
217 and then checks for existence in fnd_documents and fnd_attached_documents.
218
219 Parameters Type Required Description and Purpose
220 ========== ==== ======== =======================
221 p_entity_name NUMBER YES identifier in FND_LOBS
222 p_pk1_value NUMBER YES Primary key 1 (Change req id)
223 p_pk2_value NUMBER YES Primary key 2 (Version Number)
224 p_pk3_value NUMBER NO Primary key 3
225 p_pk4_value NUMBER NO Primary key 4
226 p_pk5_value NUMBER NO Primary key 5
227 p_file_id NUMBER NO file_id in FND_LOBS
228 */
229
230 procedure get_attachment_file_id
231 (p_entity_name IN varchar2,
232 p_pk1_value IN varchar2 default NULL,
233 p_pk2_value IN varchar2 default NULL,
234 p_pk3_value IN varchar2 default NULL,
235 p_pk4_value IN varchar2 default NULL,
236 p_pk5_value IN varchar2 default NULL,
237 p_file_id IN OUT nocopy number) is
238
239 l_file_id number;
240
241 begin
242
243 pa_debug.debug('Inside get_attachment_file_id...');
244
245 select media_id
246 into l_file_id
247 from fnd_documents doc,
248 fnd_attached_documents ad
249 where ad.document_id = doc.document_id
250 and ad.entity_name = p_entity_name
251 and nvl(ad.pk1_value,'-99') = nvl(p_pk1_value,nvl(ad.pk1_value,'-99'))
252 and nvl(ad.pk2_value,'-99') = nvl(p_pk2_value,nvl(ad.pk2_value,'-99'))
253 and nvl(ad.pk3_value,'-99') = nvl(p_pk3_value,nvl(ad.pk3_value,'-99'))
254 and nvl(ad.pk4_value,'-99') = nvl(p_pk4_value,nvl(ad.pk4_value,'-99'))
255 and nvl(ad.pk5_value,'-99') = NVL(p_pk5_value,nvl(ad.pk5_value,'-99'));
256
257 pa_debug.debug('10: l_file_id = ' || l_file_id);
258 p_file_id := l_file_id;
259
260 exception
261 when no_data_found then
262 p_file_id := null;
263 pa_debug.debug('20: returning null l_file_id');
264
265 end get_attachment_file_id;
266
267 end PA_PCO_PKG;