1 PACKAGE BODY PO_XML_UTILS_GRP AS
2 /* $Header: POXMLUTB.pls 120.6.12020000.3 2013/02/10 16:32:48 vegajula ship $ */
3
4 PROCEDURE getAttachmentUrl (p_document_id IN NUMBER, x_attachment_content OUT NOCOPY VARCHAR2) AS
5 BEGIN
6 SELECT url INTO x_attachment_content FROM fnd_documents_vl WHERE document_id=p_document_id;
7 EXCEPTION
8 WHEN OTHERS THEN
9 NULL;
10 END getAttachmentUrl;
11
12 procedure getAttachment (p_media_id in NUMBER,
13 p_datatype_id in NUMBER,
14 x_attachment_content out NOCOPY CLOB) as
15 file_content blob;
16 l_short_text varchar2(2000);
17 l_long_text clob;
18 len number;
19
20 begin
21 dbms_lob.createtemporary(x_attachment_content,TRUE,DBMS_LOB.SESSION);
22
23 if (p_datatype_id = 1) then /* short_text */
24 select short_text into l_short_text
25 from fnd_documents_short_text
26 where media_id = p_media_id;
27 len := length(l_short_text);
28 dbms_lob.write( lob_loc => x_attachment_content,
29 amount => len,
30 offset => 1,
31 buffer => l_short_text);
32
33 elsif (p_datatype_id = 2) then /* long_text */
34 select long_text into l_long_text
35 from fnd_documents_long_text
36 where media_id = p_media_id;
40 offset => 1,
37 len := length(l_long_text);
38 dbms_lob.write( lob_loc => x_attachment_content,
39 amount => len,
41 buffer => l_long_text);
42
43 end if;
44
45 exception when others then
46 null; -- We don't want to raise the exception here as it will stop generation of XML
47
48 end getAttachment;
49
50 procedure getAttachmentFile (p_media_id in NUMBER,
51 p_pk1_value in NUMBER,
52 p_pk2_value IN NUMBER,
53 p_pk3_value IN NUMBER,
54 p_pk4_value IN NUMBER,
55 p_pk5_value IN NUMBER,
56 p_entity_name IN VARCHAR2,
57 x_cid out NOCOPY VARCHAR2) as
58 l_lang varchar2(30);
59
60 begin
61
62 select userenv('LANG') into l_lang from dual;
63
64 ECX_ATTACHMENT.register_attachment(p_entity_name, p_pk1_value, p_pk2_value,
65 p_pk3_value, p_pk4_value, p_pk5_value,
66 p_media_id, 6, x_cid);
67
68 exception when others then
69 null; -- We don't want to raise the exception here as it will stop generation of XML
70
71 end getAttachmentFile;
72
73 /* split ECX_PARAMETER3 to user_id, responsibility_id and application_id */
74 procedure splitforids (p_ecx_parameter3 in VARCHAR2,
75 x_user_id out NOCOPY NUMBER,
76 x_resp_id out NOCOPY NUMBER,
77 x_appl_id out NOCOPY NUMBER)
78 is
79 l_resp_appl varchar2(150);
80
81 begin
82 x_user_id := to_number(substr(p_ecx_parameter3, 1, instr(p_ecx_parameter3,':')-1));
83 l_resp_appl := substr(p_ecx_parameter3, instr(p_ecx_parameter3,':')+1);
84 x_resp_id := to_number(substr(l_resp_appl, 1, instr(l_resp_appl,':')-1));
85 x_appl_id := to_number(substr(l_resp_appl, instr(l_resp_appl,':')+1));
86
87 exception
88 when others then
89 wf_core.context('PO_XML_UTILS_GRP','splitforids',SQLERRM);
90 raise;
91
92 end splitforids;
93
94
95 procedure getBlanketPONumber (p_release_id in NUMBER,
96 p_po_type in VARCHAR2,
97 p_Blanket_PO_Num out NOCOPY VARCHAR2
98 )
99 is
100
101 begin
102 if (upper(p_po_type) = 'RELEASE') then
103 /*
104 select pha.segment1 into p_blanket_po_num
105 from po_headers_all pha, po_releases_all pra
106 where pra.po_header_id = pha.po_header_id and
107 pra.po_release_id = p_release_id;
108 */
109 select segment1 into p_blanket_po_num
110 from po_headers_all
111 where po_header_id = p_release_id;
112 end if;
113
114 exception
115 when others then
116 wf_core.context('PO_XML_UTILS_GRP','splitforids',SQLERRM);
117 -- raise; we don't want to raise exception here as it will lead to failure in XML gen.
118
119
120 end getBlanketPONumber;
121
122 --Bug 6692126 Changing the signature of this procedure
123 procedure getTandC (p_document_id in NUMBER,
124 p_document_type in VARCHAR2,
125 x_TandCcontent out NOCOPY CLOB)
126 is
127
128 l_filedir varchar2(256);
129 l_filename varchar2(256);
130 l_filename_lang varchar2 (600);
131 v_filehandle UTL_FILE.file_type;
132 l_terms varchar2(32000);
133 voffset integer := 1;
134 l_newline varchar2(10); -- bug#4278861: stores new line value
135
136 begin
137
138 /* initialize APPS context */
139 /*Bug 6692126 Call procedure get_preparer_profile instead of getting profile options in preparers context*/
140 /* fnd_global.APPS_INITIALIZE (p_user_id, p_resp_id, p_appl_id);
141 FND_PROFILE.GET('PO_EMAIL_TERMS_DIR_NAME', l_filedir);
142 FND_PROFILE.GET('PO_EMAIL_TERMS_FILE_NAME', l_filename); */
143
144 l_filedir := PO_COMMUNICATION_PVT.get_preparer_profile(p_document_id,p_document_type,'PO_EMAIL_TERMS_DIR_NAME');
145 l_filename := PO_COMMUNICATION_PVT.get_preparer_profile(p_document_id,p_document_type,'PO_EMAIL_TERMS_FILE_NAME');
146
147 --bug#4278861: populate l_newline with new line value from FND_GLOBAL
148 select fnd_global.NEWLINE into l_newline from dual;
149
150 if ((l_filedir is not null) and (l_filename is not null)) then
151
152 /* Check for supplier site language tandc file first if that doesn't exist then check for base language tandc file else check for just l_filename */
153 l_filename_lang := l_filename || '_' || userenv('LANG');
154
155 BEGIN
156 /* open the file */
157
158 v_filehandle := UTL_FILE.FOPEN(l_filedir,l_filename_lang,'r',32000); --bug#4278861: Open the file handle with 32k buffer
159
160 EXCEPTION WHEN OTHERS THEN
161
162 BEGIN
163 l_filename_lang := l_filename || '_' || fnd_global.base_language;
164 v_filehandle := UTL_FILE.FOPEN(l_filedir,l_filename_lang, 'r',32000); --bug#4278861: Open the file handle with 32k buffer
165
166 EXCEPTION WHEN OTHERS THEN
167 begin
168 v_filehandle := UTL_FILE.FOPEN(l_filedir,l_filename, 'r',32000); --bug#4278861: Open the file handle with 32k buffer
169 exception when others then
170 return;
171 end;
175 dbms_lob.createtemporary(x_TandCcontent,TRUE,DBMS_LOB.SESSION);
172 END;
173 END;
174
176
177 /* Read the file line by line and append it to CLOB */
178 if (UTL_FILE.is_open(v_filehandle) = true) then
179 loop
180 begin
181 /* write the contents into the document */
182 UTL_FILE.GET_LINE(v_filehandle,l_terms);
183 if (l_terms is null) then
184 l_terms := ' ';
185 end if;
186 l_terms := l_terms||l_newline; --bug#4278861: Appended the new line to the line read from file
187 dbms_lob.write(x_TandCcontent,length(l_terms),voffset,l_terms);
188 voffset := voffset + length(l_terms);
189
190 --bug#4278861: Commented the code as it adds extra space to the output dbms_lob
191 --l_terms := ' ';
192 --dbms_lob.write(x_TandCcontent,length(l_terms),voffset,l_terms);
193 --voffset := voffset + length(l_terms);
194
195
196 exception when no_data_found then
197 exit;
198 end;
199 end loop;
200 UTL_FILE.fclose(v_filehandle);
201 end if;
202 end if;
203
204 exception when others then
205 --dbms_output.put_line(SQLERRM);
206 null; -- We don't want to raise the exception here as it will stop generation of XML
207
208 end getTandC;
209
210 /*Added for bug#6912518*/
211 procedure getTandCforXML (p_po_header_id in NUMBER,
212 p_po_release_id in NUMBER,
213 x_TandCcontent out NOCOPY CLOB)
214 is
215 begin
216
217 if p_po_release_id is null then
218 getTandC (p_po_header_id, 'STANDARD', x_TandCcontent);
219 else
220 getTandC (p_po_release_id, 'RELEASE', x_TandCcontent);
221 end if;
222
223 exception when others then
224 --dbms_output.put_line(SQLERRM);
225 null; -- don't raise exception as it will stop generation of XML
226 end getTandCforXML;
227
228 procedure regenandsend(p_po_header_id in NUMBER,
229 p_po_type in VARCHAR2,
230 p_po_revision in NUMBER,
231 p_user_id in NUMBER,
232 p_responsibility_id in NUMBER,
233 p_application_id NUMBER,
234 p_preparer_user_name VARCHAR2)
235 IS
236
237 x_progress VARCHAR2(100) := '000';
238 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
239 l_event_key varchar2(100);
240 l_event_key2 varchar2(100);
241 l_wf_item_seq number;
242 l_wf_item_seq2 number;
243 l_event_name varchar2(100) := 'oracle.apps.po.event.setxml';
244 l_org_id number;
245 l_po_number varchar2(100);
246 INVALID_TXN_TYPE EXCEPTION;
247 BEGIN
248
249
250 wf_core.context('PO_ECX_UTIL_PKG','regenandsend',x_progress);
251
252 -- Create EVENT_KEY and EVENT_NAME
253 select PO_WF_ITEMKEY_S.nextval into l_wf_item_seq from dual;
254 l_event_key := to_char(p_po_header_id) || '-' || to_char(l_wf_item_seq);
255
256 x_progress := '001';
257 wf_core.context('POM_ECX_UTIL_PKG','regenandsend',x_progress);
258
259 if (upper(p_po_type) = 'RELEASE') then
260 select pha.segment1, pha.org_id
261 into l_po_number, l_org_id
262 from po_releases_all pra, po_headers_all pha
263 where pra.po_release_id = p_po_header_id
264 and pra.po_header_id = pha.po_header_id;
265
266
267 elsif (upper(p_po_type) = 'STANDARD') then
268 select pha.segment1, pha.org_id
269 into l_po_number, l_org_id
270 from po_headers_all pha
271 where pha.po_header_id = p_po_header_id;
272 /*
273 else ideally we should raise an exception.
274 */
275
276
277 end if;
278 x_progress := '002';
279 wf_core.context('POM_ECX_UTIL_PKG','regenandsend',x_progress);
280
281 -- Add Parameters
282 wf_event.AddParameterToList(p_name =>'DOCUMENT_ID',
283 p_value => p_po_header_id,
284 p_parameterlist => l_parameter_list);
285
286 wf_event.AddParameterToList(p_name =>'DOCUMENT_TYPE',
287 p_value => p_po_type,
288 p_parameterlist => l_parameter_list);
289 wf_event.AddParameterToList(p_name =>'PO_REVISION_NUM',
290 p_value => p_po_revision,
291 p_parameterlist => l_parameter_list);
292 wf_event.AddParameterToList(p_name =>'USER_ID',
293 p_value => p_user_id,
294 p_parameterlist => l_parameter_list);
295 wf_event.AddParameterToList(p_name =>'RESPONSIBILITY_ID',
296 p_value => p_responsibility_id,
297 p_parameterlist => l_parameter_list);
298 wf_event.AddParameterToList(p_name =>'APPLICATION_ID',
299 p_value => p_application_id,
300 p_parameterlist => l_parameter_list);
301 wf_event.AddParameterToList(p_name =>'PREPARER_USER_NAME',
302 p_value => p_preparer_user_name,
303 p_parameterlist => l_parameter_list);
304 wf_event.AddParameterToList(p_name =>'ORG_ID',
305 p_value => l_org_id,
306 p_parameterlist => l_parameter_list);
307 wf_event.AddParameterToList(p_name =>'PO_NUMBER',
308 p_value => l_po_number,
309 p_parameterlist => l_parameter_list);
310
311
312
313
314
315
316 -- dbms_output.put_line (l_event_key);
317
318 x_progress := '002';
319 wf_core.context('PO_ECX_UTIL_PKG','regenandsend',x_progress);
320 wf_event.raise( p_event_name => l_event_name,
321 p_event_key => l_event_key,
322 p_parameters => l_parameter_list);
323 x_progress := '002';
324
325
326
327
328 l_parameter_list.DELETE;
329 commit;
330 EXCEPTION
331 when others then
332 x_progress := x_progress || SQLERRM;
333 wf_core.context('PO_ECX_UTIL_PKG','regenandsend',x_progress);
334
335 --dbms_output.put_line (SQLERRM);
336 raise;
337
338
339 END regenandsend;
340
341 procedure getGlobalAgreementInfo (p_po_line_id in NUMBER,
342 x_GLOBALCONTRACT OUT NOCOPY VARCHAR2,
343 x_GLOBALCONTRACTLIN OUT NOCOPY VARCHAR2) is
344 begin
345
346 select glblH.segment1
347 into x_GLOBALCONTRACT
348 from po_headers_all glblH, po_lines_all glblSPO
349 where glblSPO.po_line_id = p_po_line_id and
350 glblSPO.from_header_id = glblH.po_header_id and
351 glblH.global_agreement_flag = 'Y';
352
353 select to_char(glblL.line_Num)
354 into x_GLOBALCONTRACTLIN
355 from po_lines_all glblL, po_lines_all glblSPO
356 where glblSPO.po_line_id = p_po_line_id and
357 glblSPO.from_line_id = glblL.po_line_id;
358
359 EXCEPTION
360 when others then
361 null; --We don't want to raise here as this would break the XML generation.
362
363 end;
364
365 procedure getTaxDetails (p_po_line_loc_id IN NUMBER,
366 x_TAX_RATE OUT NOCOPY varchar2,
367 x_IS_VAT_RECOVERABLE OUT NOCOPY varchar2,
368 x_TAX_TYPE OUT NOCOPY varchar2,
369 x_TAX_NAME OUT NOCOPY varchar2,
370 x_ESTIMATED_TAX_AMOUNT OUT NOCOPY number,
371 x_TAX_DESCRIPTION OUT NOCOPY varchar2
372 ) is
373 l_isTaxable varchar2(1);
374 l_po_line_id number;
375 l_type_1099 varchar2(10);
376 l_tax_id number;
377 l_TAX_RECOVERY_RATE number;
378
379
380 begin
381
382 select po_line_id, TAXABLE_FLAG, ESTIMATED_TAX_AMOUNT
383 into l_po_line_id, l_isTaxable, x_ESTIMATED_TAX_AMOUNT
384 from po_line_locations_archive_all
385 where line_location_id = p_po_line_loc_id;
386
387 if (l_isTaxable = 'Y') then
388 select TAX_NAME, TYPE_1099, TAX_CODE_ID
389 into x_TAX_NAME, l_type_1099, l_tax_id
390 from PO_LINES_ARCHIVE_ALL
391 where PO_LINE_ID = l_po_line_id and taxable_flag = 'Y';
392 else
393 /* bottom two are mandatory fields in cXML. So always populate these. */
394 x_ESTIMATED_TAX_AMOUNT := 0;
395 x_TAX_TYPE := 'Non-Taxable';
396
397 end if;
398
399 if (l_tax_id is not null) then
400 select NAME, TAX_TYPE, DESCRIPTION, TAX_RATE, TAX_RECOVERY_RATE
401 into x_TAX_NAME, x_TAX_TYPE, x_TAX_DESCRIPTION, x_TAX_RATE, l_TAX_RECOVERY_RATE
402 from AP_TAX_CODES_ALL
403 where tax_id = l_tax_id;
404
405 if (x_TAX_TYPE = 'VAT' and l_tax_recovery_rate = 100) then
406 x_IS_VAT_RECOVERABLE := 'yes';
407 else
408 x_IS_VAT_RECOVERABLE := '';
409 end if;
410
411 end if;
412
413 exception when others then
414 null; --We don't want to raise the exception here as it will stop generation of XML.
415
416 end;
417
418 procedure getTaxInfo (p_po_line_loc_id IN NUMBER,
419 X_TAXABLE OUT NOCOPY varchar2) is
420
421 begin
422
423 select TAXABLE_FLAG
424 into X_TAXABLE
425 from po_line_locations_archive_all
426 where line_location_id = p_po_line_loc_id;
427
428 if (X_TAXABLE = 'Y') then
429 X_TAXABLE := 'Taxable';
430 elsif (X_TAXABLE = 'N') then
431 X_TAXABLE := 'Nontaxable';
432 end if;
433
434 exception when others then
435 null; --We don't want to raise exception here to stop generation of XML.
436 end getTaxInfo;
437
438 procedure getUserEnvLang (x_lang OUT NOCOPY varchar2) is
439
440 begin
441
442 select userenv('lang') into x_lang from dual;
443 exception when others then
444 x_lang := 'US'; --We don't want to error out here. Instead let default be : 'US'.
445 end;
446
447 END PO_XML_UTILS_GRP;
448
449
[Home] [Help]