[Home] [Help]
PACKAGE BODY: APPS.PO_POXPRRFP_XMLP_PKG
Source
1 PACKAGE BODY PO_POXPRRFP_XMLP_PKG AS
2 /* $Header: POXPRRFPB.pls 120.1 2007/12/25 11:33:02 krreddy noship $ */
3
4 USER_EXIT_FAILURE EXCEPTION;
5
6 DO_SQL_FAILURE EXCEPTION;
7
8 function where_clauseFormula return VARCHAR2 is
9 begin
10
11 if P_report_type = 'N' then
12 return('AND poh.printed_date is null');
13 elsif P_report_type = 'C' then
14 return('AND poh.revised_date > poh.printed_date');
15 elsif P_report_type = 'A' then
16 return('AND 1=1');
17 else
18 return('AND 1=1');
19 end if;
20
21 RETURN NULL; end;
22
23 function BeforeReport return boolean is
24 begin
25 /*srw.do_sql('alter session set SQL_TRACE=True');*/null;
26 p_language_where:=' and 1=1';
27
28 if p_uom_join_pll is null then
29 p_uom_join_pll:=' ';
30 end if;
31
32 BEGIN
33 select displayed_field
34 into C_address_at_top
35 from po_lookup_codes
36 where lookup_type = 'PO_POXPRRFP_XMLP_PKG'
37 and lookup_code = 'ADDRESS AT TOP';
38
39
40 /*SRW.USER_EXIT('FND SRWINIT');*/null;
41
42
43
44
45 If P_rfq_num_from = P_rfq_num_to THEN
46 P_single_rfq_print := 1;
47 END IF;
48
49
50
51 null;
52 if (MLS_INSTALLED) then
53 POPULATE_MLS_LEXICALS;
54 end if;
55
56 RETURN TRUE;
57
58 END; return (TRUE);
59 end;
60
61 function AfterReport return boolean is
62 begin
63 execute immediate 'alter session set SQL_TRACE=false';
64
65
66 /*SRW.USER_EXIT('FND SRWEXIT');*/null;
67 return (TRUE);
68 end;
69
70 function c_amount_pllformula(pll_quantity_ordered in number, pll_price_override in number) return number is
71 begin
72
73 return(pll_quantity_ordered * pll_price_override);
74 end;
75
76 function g_headersgroupfilter(rfq_num_type in varchar2, poh_rfq_num in varchar2,poh_po_header_id in number,poh_sequence_num in number) return boolean is
77 begin
78
79 declare
80
81 check_rfq_num number;
82 check_rfq_num_from number;
83 check_rfq_num_to number;
84
85 begin
86
87 if rfq_num_type = 'NUMERIC' then
88 if rtrim(poh_rfq_num, '0123456789') is null then
89 check_rfq_num := to_number(poh_rfq_num);
90 else
91 check_rfq_num := -1;
92 end if;
93
94 if rtrim(nvl(P_rfq_num_from, poh_rfq_num), '0123456789') is null then
95 check_rfq_num_from := to_number(nvl(P_rfq_num_from, poh_rfq_num));
96 else
97 check_rfq_num_from := -1;
98 end if;
99
100 if rtrim(nvl(P_rfq_num_to, poh_rfq_num), '0123456789') is null then
101 check_rfq_num_to := to_number(nvl(P_rfq_num_to, poh_rfq_num));
102
103 else
104 check_rfq_num_to := -1;
105 end if;
106
107 if check_rfq_num between check_rfq_num_from and check_rfq_num_to then
108 if (check_security(poh_rfq_num) ) then
109 return FALSE ;
110 else
111 if P_test_flag <> 'Y' then
112 UPDATE po_headers
113 SET printed_date = sysdate
114 , print_count = nvl(print_count,0) + 1
115 , status_lookup_code = 'P'
116 WHERE po_header_id = poh_po_header_id;
117
118
119 COMMIT;
120 UPDATE po_rfq_vendors
121 SET printed_date = sysdate
122 , print_count = nvl(print_count,0) + 1
123 , print_flag = 'N'
124 WHERE po_header_id = poh_po_header_id
125 AND sequence_num = poh_sequence_num;
126
127 COMMIT;
128 end if;
129 end if ;
130 else
131 return false;
132 end if;
133 else
134 if (check_security(poh_rfq_num) ) then
135 return FALSE ;
136 else
137 if P_test_flag <> 'Y' then
138 UPDATE po_headers
139 SET printed_date = sysdate
140 , print_count = nvl(print_count,0) + 1
141 , status_lookup_code = 'P'
142 WHERE po_header_id = poh_po_header_id;
143
144
145 COMMIT;
146 UPDATE po_rfq_vendors
147 SET printed_date = sysdate
148 , print_count = nvl(print_count,0) + 1
149 , print_flag = 'N'
150 WHERE po_header_id = poh_po_header_id
151 AND sequence_num = poh_sequence_num;
152
153 COMMIT;
154 end if;
155 end if ;
156 end if;
157
158 end ;
159
160
161 return (TRUE);
162 end;
163
164 function c_shiptoformula(pll_ship_address_line1 in varchar2, poh_ship_address_line1 in varchar2, pll_ship_address_line2 in varchar2, poh_ship_address_line2 in varchar2,
165 pll_ship_address_line3 in varchar2, poh_ship_address_line3 in varchar2, pll_ship_adr_info in varchar2, poh_ship_adr_info in varchar2, pll_ship_country in varchar2, poh_ship_country in varchar2) return varchar2 is
166 begin
167
168 if nvl(pll_ship_address_line1,'1') = nvl(poh_ship_address_line1,'1')
169 and nvl(pll_ship_address_line2,'2') = nvl(poh_ship_address_line2,'2')
170 and nvl(pll_ship_address_line3,'3') = nvl(poh_ship_address_line3,'3')
171 and nvl(pll_ship_adr_info,'4') = nvl(poh_ship_adr_info,'4')
172 and nvl(pll_ship_country,'5') = nvl(poh_ship_country,'5')
173 then
174 return (C_address_at_top) ;
175 else return (pll_ship_address_line1) ;
176 end if;
177 RETURN NULL; end;
178
179 function check_security(poh_rfq_num in varchar2) return boolean is
180
181 l_dummy VARCHAR2(200) ;
182
183 begin
184
185 select 'This PO is OK to be displayed so far as security is concerned'
186 into l_dummy
187 from dual
188 where poh_rfq_num in
189 (select ph.segment1
190 from PO_HEADERS PH,
191 PO_DOCUMENT_TYPES_ALL_TL T, PO_DOCUMENT_TYPES_ALL_B B ,
192 fnd_user fnd,
193 po_system_parameters psp
194 WHERE fnd.user_id = P_user_id
195 and fnd.employee_id is not null
196 and ph.type_lookup_code = 'RFQ'
197 and B.DOCUMENT_TYPE_CODE = T.DOCUMENT_TYPE_CODE
198 AND B.DOCUMENT_SUBTYPE = T.DOCUMENT_SUBTYPE
199 AND B.ORG_ID = T.ORG_ID AND B.ORG_ID = PH.ORG_ID AND T.LANGUAGE = USERENV('LANG')
200 and B.document_type_code = ph.type_lookup_code
201 and B.document_subtype = 'STANDARD'
202 and ( ph.agent_id = fnd.employee_id
203 OR B.security_level_code = 'PUBLIC'
204 OR
205 ( B.security_level_code = 'PURCHASING'
206 AND EXISTS
207 ( SELECT 'Is the user an agent'
208 FROM PO_AGENTS POA
209 WHERE POA.AGENT_ID =
210 fnd.employee_id ))
211 OR
212 ( B.security_level_code = 'HIERARCHY'
213 AND fnd.employee_id IN
214 ( SELECT POEH.SUPERIOR_ID
215 FROM PO_EMPLOYEE_HIERARCHIES POEH
216 WHERE POEH.EMPLOYEE_ID =
217 PH.AGENT_ID
218 AND POEH.POSITION_STRUCTURE_ID =
219 PSP.SECURITY_POSITION_STRUCTURE_ID)))) ;
220
221 return TRUE ;
222
223 RETURN NULL; exception when no_data_found then return false ;
224 when others then return false ;
225 end;
226
227 function round_pol_amt(c_amount_pol in number, c_currency_precision in number) return number is
228 begin
229
230 /*srw.reference(c_amount_pol);*/null;
231
232 /*srw.reference(c_currency_precision);*/null;
233
234
235 return(round(c_amount_pol,c_currency_precision));
236 end;
237
238 procedure POPULATE_MLS_LEXICALS is
239 statement varchar2(1000);
240 SESSION_LANGUAGE FND_LANGUAGES.NLS_LANGUAGE%TYPE;
241 BASE_LANGUAGE FND_LANGUAGES.NLS_LANGUAGE%TYPE;
242
243 begin
244
245 select substr(userenv('LANGUAGE'),1,instr(userenv('LANGUAGE'),'_')-1)
246 into SESSION_LANGUAGE
247 from dual;
248
249 select nls_language
250 into BASE_LANGUAGE
251 from fnd_languages
252 where installed_flag = 'B';
253
254 p_language_where := 'and nvl(poh.language,' || '''' ||
255 BASE_LANGUAGE || ''') = ' || '''' ||
256 SESSION_LANGUAGE || '''';
257
258 execute immediate 'select attribute_column_name
259 from ak_translated_columns atc,
260 ak_language_attribute_xrefs alax
261 where atc.table_name = ' || '''PO_LINES''' ||
262 ' and atc.column_name = ' || '''ITEM_DESCRIPTION''' ||
263 ' and alax.translated_column_number = atc.translated_column_number
264 and alax.language = ' || '''' || SESSION_LANGUAGE || ''''
265 into temp_col_name;
266
267
268
269
270 if temp_col_name is not NULL then
271 p_description := 'pol.' || rtrim(temp_col_name,' ');
272 end if;
273
274 execute immediate 'select alax.attribute_column_name
275 from ak_translated_columns atc,
276 ak_language_attribute_xrefs alax
277 where atc.table_name = ' || '''MTL_UNITS_OF_MEASURE''' ||
278 ' and atc.column_name = ' || '''UNIT_OF_MEASURE''' || 'and alax.translated_column_number = atc.translated_column_number
279 and alax.language = ' || '''' || SESSION_LANGUAGE || ''''
280 into temp_col_name;
281
282
283
284
285 if temp_col_name is not NULL then
286 p_uom_join_pol := ',mtl_units_of_measure mum
287 WHERE pol.unit_of_measure = mum.unit_of_measure';
288
289 p_uom_join_pll := ',mtl_units_of_measure mum
290 WHERE pll.lead_time_unit = mum.unit_of_measure';
291
292 p_uom_col_pol := 'mum.' || rtrim(temp_col_name,' ');
293
294 p_uom_col_pll := p_uom_col_pol;
295
296 end if;
297
298 exception
299 when others then raise_application_error(-20001,' Error While processing POPULATE_MLS_LEXICALS');
300
301 end POPULATE_MLS_LEXICALS;
302
303 function mls_installed return boolean is
304
305 sql_stmt varchar2(500);
306
307 BEGIN
308
309 sql_stmt := 'select multi_lingual_flag
310 into :MLS_FLAG
311 from fnd_product_groups';
312
313 /*srw.do_sql(sql_stmt);*/null;
314
315
316 if (MLS_FLAG = 'Y') then
317 return TRUE;
318 else
319 return FALSE;
320 end if;
321
322
323 RETURN NULL; exception
324 when DO_SQL_FAILURE /*srw.do_sql_failure */then return FALSE;
325 when others then return FALSE;
326
327
328 END mls_installed;
329
330 function c_fax_headerformula(C_first_page in varchar2) return char is
331 d_fax_header varchar2(500);
332 begin
333
334 if (C_first_page <> 1) then
335 return (' ');
336 end if;
337
338 if (UPPER(P_fax_enable) = 'Y') then
339 d_fax_header := '{{begin}}{{doctype rfqfax}}';
340 return(d_fax_header);
341 end if;
342
343 return(' ');
344 end;
345
346 function c_fax_trailerformula(poh_no_of_lines in number, C_last_sum in number, CS_poh_vendor_name in varchar2, CS_poh_rfq_num in varchar2, CS_poh_buyer in varchar2, CS_poh_agent_id in varchar2) return char is
347 d_fax_trailer varchar2(200);
348 begin
349 if (poh_no_of_lines is null and C_last_sum is null) or
350 (C_Last_Sum = poh_no_of_lines) then
351 if (UPPER(P_fax_enable) = 'Y') then
352 d_fax_trailer := '{{company ' ||
353 substrb(CS_poh_vendor_name,1,15) || '}}{{fax ' ||
354 p_fax_num || '}}{{comment RFQ# ' ||
355 CS_poh_rfq_num || '}}{{owner ' ||
356 CS_poh_buyer || '}}{{lookup ' ||
357 to_char(CS_poh_agent_id) || ' buyer.inc}}{{end}}';
358 return (d_fax_trailer);
359 end if;
360 end if;
361
362 return(' ');
363 end;
364
365 function c_item_descformula(pol_po_item_id in number, pol_item_description in varchar2, C_msi_desc in varchar2, C_msit_desc in varchar2) return char is
366 begin
367
368 if (pol_po_item_id is null) then
369 return (pol_item_description);
370 end if;
371 if (pol_item_description is null) or (C_msi_desc is null) then
372 return (pol_item_description);
373 end if;
374
375 if (pol_item_description = C_msi_desc) then
376 return (C_msit_desc);
377 else
378 return (pol_item_description);
379 end if;
380
381 end;
382
383 function header_noteformula(header_note_datatype_id in number, header_note_media_id in number) return char is
384 short_note Varchar2(2000);
385 long_note Long;
386 begin
387 if header_note_datatype_id = 1 then
388 select short_text
389 into short_note
390 from fnd_documents_short_text
391 where media_id = header_note_media_id;
392 return short_note;
393 elsif header_note_datatype_id = 2 then
394 select long_text
395 into long_note
396 from fnd_documents_long_text
397 where media_id = header_note_media_id;
398 return long_note;
399 else
400 return 'Attachment is not a Text format';
401 end if;
402
403 end;
404
405 function line_noteformula(line_note_datatype_id in number, line_note_media_id in number) return char is
406 short_note Varchar2(2000);
407 long_note Long;
408 begin
409 if line_note_datatype_id = 1 then
410 select short_text
411 into short_note
412 from fnd_documents_short_text
413 where media_id = line_note_media_id;
414 return short_note;
415 elsif line_note_datatype_id = 2 then
416 select long_text
417 into long_note
418 from fnd_documents_long_text
419 where media_id = line_note_media_id;
420 return long_note;
421 else
422 return 'Attachment is not a Text format';
423 end if;
424
425 end;
426
427 function item_noteformula(item_note_datatype_id in number, item_note_media_id in number) return char is
428 short_note Varchar2(2000);
429 long_note Long;
430 begin
431 if item_note_datatype_id = 1 then
432 select short_text
433 into short_note
434 from fnd_documents_short_text
435 where media_id = item_note_media_id;
436 return short_note;
437 elsif item_note_datatype_id = 2 then
438 select long_text
439 into long_note
440 from fnd_documents_long_text
441 where media_id = item_note_media_id;
442 return long_note;
443 else
444 return 'Attachment is not a Text format';
445 end if;
446
447 end;
448
449 --Functions to refer Oracle report placeholders--
450
451 Function C_address_at_top_p return varchar2 is
452 Begin
453 return C_address_at_top;
454 END;
455 END PO_POXPRRFP_XMLP_PKG ;
456