[Home] [Help]
PACKAGE BODY: APPS.PON_RESP_SCORES_PKG
Source
1 PACKAGE BODY PON_RESP_SCORES_PKG as
2 /* $Header: PONSCOREB.pls 120.1 2006/11/03 18:21:43 mxfang noship $ */
3
4 /*======================================================================
5 FUNCTION : display_db_date_string
6 PARAMETERS:
7 p_date_str IN a date str in server time zone
8 p_client_timezone_id IN client (viewer) time zone id
9 p_server_timezone_id IN server time zone id
10 p_datetime_flag IN is this a datetime string
11 p_date_format_mask IN client date format
12
13 COMMENT : If p_datetime_flag is 'N', convert a date string p_date_str
14 of format (DD-MM-YYYY) to another format p_date_format_mask.
15
16 If p_datetime_flag is 'Y', convert a date time string p_date_str
17 of format (DD-MM-YYYY HH24:MI:SS time part optional) to
18 another format p_date_format_mask + HH24:MI:SS. Also performs
19 timezone conversion.
20 ======================================================================*/
21
22 FUNCTION display_db_date_string(p_date_str IN VARCHAR2,
23 p_client_timezone_id IN VARCHAR2,
24 p_server_timezone_id IN VARCHAR2,
25 p_datetime_flag IN VARCHAR2,
26 p_date_format_mask IN VARCHAR2)
27 RETURN VARCHAR2
28 IS
29
30 v_client_timezone VARCHAR2(50);
31 v_date_server DATE;
32 v_date_client DATE;
33 dateTimeFmtMask VARCHAR2(50) := p_date_format_mask || ' HH24:MI:SS';
34 dateTimeFmtSave VARCHAR2(50) := 'DD-MM-YYYY HH24:MI:SS';
35 dateFmtSave VARCHAR2(50) := 'DD-MM-YYYY';
36
37 BEGIN
38
39 if (p_date_str is null) then
40 return null;
41 end if;
42
43 if (p_datetime_flag = 'N') then
44 -- if just date object, don't do timezone conversion
45 return to_char(to_date(p_date_str, dateFmtSave), p_date_format_mask);
46 end if;
47
48 if (instr(p_date_str, ':') = 0) then
49 v_date_server := to_date(p_date_str, dateFmtSave);
50 else
51 v_date_server := to_date(p_date_str, dateTimeFmtSave);
52 end if;
53
54 v_date_client := PON_OA_UTIL_PKG.CONVERT_DATE(v_date_server, p_client_timezone_id, p_server_timezone_id);
55
56 return to_char(v_date_client, dateTimeFmtMask);
57
58 END display_db_date_string;
59
60
61 procedure get_acceptable_values(p_auction_id in number,
62 p_line_number in number,
63 p_attr_seq_number in number,
64 p_acc_values out NOCOPY varchar2,
65 p_scores out NOCOPY varchar2) IS
66
67 x_auction_id number;
68 x_line_number number;
69 x_attr_seq_number number;
70
71 -- what happens if this buffer size is not enough
72 -- how to assign buffers dynamically in pl/sql
73
74 x_acc_values varchar2(2000);
75 x_scores varchar2(2000);
76 x_show_scores varchar2(25);
77
78
79 -- set up all the translatable msgs to
80 -- be inserted in the returned string
81
82 msgAtMost VARCHAR2(2000); -- PON_AUC_AT_MOST
83 msgAtLeast VARCHAR2(2000); -- PON_AUC_AT_LEAST
84 msgOnOrBefore VARCHAR2(2000); -- PON_AUC_ON_OR_BEFORE
85 msgOnOrAfter VARCHAR2(2000); -- PON_AUC_ON_OR_AFTER
86 msgTo VARCHAR2(2000); -- PON_AUC_TO
87 msgFrom VARCHAR2(2000); -- PON_AUCTS_FROM
88
89 msgSeparator VARCHAR2(2) := ' ';
90
91 dateFmtMask VARCHAR2(24);
92 dateFmtSave VARCHAR2(24);
93 numFmtMask VARCHAR2(2);
94 numFmtWithD VARCHAR2(200);
95 numFmtWithoutD VARCHAR2(200);
96 internalNumFmt VARCHAR2(200);
97 numFmtToScore VARCHAR2(200);
98 numFmtFromScore VARCHAR2(200);
99
100 l_client_timezone_id VARCHAR2(10) := fnd_profile.value_specific('CLIENT_TIMEZONE_ID');
101 l_server_timezone_id VARCHAR2(10) := fnd_profile.value_specific('SERVER_TIMEZONE_ID');
102 l_datetime_flag VARCHAR2(1);
103
104 cursor c_scores is
105 select
106 a.from_range,
107 a.to_range,
108 a.value,
109 a.score,
110 b.datatype,
111 b.sequence_number
112 from
113 pon_attribute_scores a,
114 pon_auction_attributes b
115 where
116 b.auction_header_id = x_auction_id
117 and b.line_number = x_line_number
118 and b.sequence_number = x_attr_seq_number
119 and a.auction_header_id = b.auction_header_id
120 and a.line_number = b.line_number
121 and a.attribute_sequence_number = b.sequence_number;
122
123 begin
124
125 PON_AUCTION_PKG.SET_SESSION_LANGUAGE(null, USERENV('LANG'));
126
127 x_auction_id := p_auction_id;
128 x_line_number := p_line_number;
129 x_attr_seq_number := p_attr_seq_number;
130
131 -- get the flag to determine whether we need to
132 -- show the scores to the user
133
134 select SHOW_BIDDER_SCORES into x_show_scores
135 from pon_auction_headers_all
136 where auction_header_id = x_auction_id;
137
138
139 msgAtMost := PON_AUCTION_PKG.getMessage('PON_AUC_AT_MOST');
140 msgAtLeast := PON_AUCTION_PKG.getMessage('PON_AUC_AT_LEAST');
141 msgOnOrBefore := PON_AUCTION_PKG.getMessage('PON_AUC_ON_OR_BEFORE');
142 msgOnOrAfter := PON_AUCTION_PKG.getMessage('PON_AUC_ON_OR_AFTER');
143 msgTo := PON_AUCTION_PKG.getMessage('PON_AUC_TO');
144 msgFrom := PON_AUCTION_PKG.getMessage('PON_AUCTS_FROM');
145
146 numFmtMask := fnd_global.nls_numeric_characters;
147 -- 'G' corresponds to group separator; 'D' corresponds to decimal separator
148 numFmtWithD := 'FM999G999G999G999G999G999G999G999G999G999G999G990D0999999999999';
149 numFmtWithoutD := 'FM999G999G999G999G999G999G999G999G999G999G999G999G999G999G999';
150 internalNumFmt := '9999999999999999999999999999999999999999999999D9999999999999999';
151
152 dateFmtMask := fnd_global.nls_date_format;
153 dateFmtSave := 'DD-MM-RRRR';
154
155 for c_score in c_scores loop --{
156
157 --{
158
159 if (c_score.datatype = 'TXT') then
160
161 -- if the attribute datatype is text, then
162 -- simply print the acceptable values
163 -- no need to add to, from etc.
164
165 --{
166
167 x_acc_values := x_acc_values || c_score.value || msgSeparator;
168
169 --}
170
171 elsif(c_score.datatype = 'NUM' OR c_score.datatype = 'INT') then
172
173 --{
174 -- using the numeric format, we need to correctly display the
175 if(instr(c_score.to_range, '.') = 0) then
176 numFmtToScore := to_char(to_number(c_score.to_range, internalNumFmt, 'nls_numeric_characters=''.,'''), numFmtWithoutD);
177 else
178 numFmtToScore := to_char(to_number(c_score.to_range, internalNumFmt, 'nls_numeric_characters=''.,'''), numFmtWithD);
179 end if;
180
181 if(instr(c_score.from_range, '.') = 0) then
182 numFmtFromScore := to_char(to_number(c_score.from_range, internalNumFmt, 'nls_numeric_characters=''.,'''), numFmtWithoutD);
183 else
184 numFmtFromScore := to_char(to_number(c_score.from_range, internalNumFmt, 'nls_numeric_characters=''.,'''), numFmtWithD);
185 end if;
186
187 if(c_score.from_range = '' OR c_score.from_range is null ) then
188
189 x_acc_values := x_acc_values || msgAtMost || msgSeparator || numFmtToScore || msgSeparator;
190
191 elsif (c_score.to_range = '' OR c_score.to_range is null) then
192
193 x_acc_values := x_acc_values || msgAtLeast || msgSeparator || numFmtFromScore || msgSeparator;
194
195 else
196
197 x_acc_values := x_acc_values || msgFrom || msgSeparator || numFmtFromScore || msgSeparator || msgTo || msgSeparator || numFmtToScore || msgSeparator;
198
199 end if;
200
201 --}
202
203 elsif(c_score.datatype = 'DAT') then
204
205 if (c_score.sequence_number = -10) then
206 l_datetime_flag := 'Y'; -- need-by date is datetime
207 else
208 l_datetime_flag := 'N';
209 end if;
210 --{
211
212 if(c_score.from_range = '' OR c_score.from_range is null) then
213
214 x_acc_values := x_acc_values || msgOnOrBefore || msgSeparator || display_db_date_string(c_score.to_range, l_client_timezone_id, l_server_timezone_id, l_datetime_flag, dateFmtMask) || msgSeparator;
215
216 elsif (c_score.to_range = '' OR c_score.to_range is null) then
217
218 x_acc_values := x_acc_values || msgOnOrAfter || msgSeparator || display_db_date_string(c_score.from_range, l_client_timezone_id, l_server_timezone_id, l_datetime_flag, dateFmtMask) || msgSeparator;
219
220 else
221
222 x_acc_values := x_acc_values || msgFrom || msgSeparator || display_db_date_string(c_score.from_range, l_client_timezone_id, l_server_timezone_id, l_datetime_flag, dateFmtMask) || msgSeparator
223 || msgTo || msgSeparator || display_db_date_string(c_score.to_range, l_client_timezone_id, l_server_timezone_id, l_datetime_flag, dateFmtMask) || msgSeparator;
224
225 end if;
226
227 --}
228
229 end if;
230
231 --}
232
233 if(x_show_scores = 'Y' OR x_show_scores = 'SCORE_WEIGHT') then --{
234
235 x_acc_values := x_acc_values || '<B>(' || c_score.score || ')</B>' || '<BR>';
236
237 --}
238
239 else --{
240
241 x_acc_values := x_acc_values || '<BR>';
242
243 end if;
244
245 --}
246
247 x_scores := x_scores || c_score.score || '<BR>';
248
249 --dbms_output.put_line('AccVal = ' || x_acc_values || ' Scores = ' || x_scores);
250
251 end loop; --}
252
253 p_acc_values := x_acc_values;
254 p_scores := x_scores;
255
256 --dbms_output.put_line('Returning AccValues = ' || p_acc_values || ' Scores = ' || p_scores);
257
258 exception
259 when others then
260 null;
261 end;
262
263
264
265
266 END PON_RESP_SCORES_PKG;