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