DBA Data[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;