1 PACKAGE PON_CONSOLE_PVT AUTHID CURRENT_USER AS
2 /* $Header: PONVCONS.pls 120.1 2011/11/03 05:40:52 puppulur ship $ */
3
4 ---
5 --- +=======================================================================+
6 --- | Copyright (c) 2004 Oracle Corporation, Redwood Shores, CA, USA |
7 --- | All rights reserved. |
8 --- +=======================================================================+
9 --- |
10 --- | FILENAME
11 --- | PONVCONS.pls
12 --- |
13 --- |
14 --- | DESCRIPTION
15 --- |
16 --- | This package contains procedures called from the live console
17 --- | for Supplier activites
18 --- |
19 --- | HISTORY
20 --- |
21 --- | 14-Jun-2004 sparames Initial version
22 --- | 30-Jun-2004 sahegde Added procedrues for supplier activities,
23 --- | debugging
24 --- | 02-Dec-2004 sahegde Added procedure for calculate_console_summary
25 --- |
26 --- |
27 --- +=======================================================================+
28 ---
29
30 --------------------------------------------------------------------------------
31 -- get_time_axis_tick_labels --
32 --------------------------------------------------------------------------------
33 -- Start of Comments
34 --
35 -- API Name: get_time_axis_tick_labels
36 --
37 -- Type : Private
38 --
39 -- Pre-reqs: None
40 --
41 -- Function: This API provides the live console UI with the values of the ticks
42 -- for the time-axis graphs. Time-axis graphs have been implemented
43 -- in FPK using BI Beans. However, the time-axis graphs available
44 -- in BI Beans do not have all the features required by Sourcing.
45 -- Hence these graphs have been implemented as regular scatter graphs
46 -- which plot numeric values on the x and y-axes. Since the requirement
47 -- was to plot time on the x-axis, this procedure helps define the
48 -- ticks on the x-axis and other parameters that help the calling
49 -- function plot values correctly
50 --
51 -- Parameters:
52 --
53 -- p_auction_header_id IN NUMBER
54 -- Auction header id - required
55 --
56 -- p_graph_start_date IN DATE,
57 -- If the caller knows the starting point of the graph,
58 -- then passing this parameter in will help this procedure
59 -- recognize that it does not need to determine it
60 -- independently. The first time this procedure is called
61 -- for the header or a line, this will be NULL. The procedure
62 -- will determine this date and pass it back in
63 -- x_graph_start_date
64 --
65 -- p_auction_close_date IN DATE,
66 -- This is the final date of the graph. All the graphs are
67 -- used to plot bids coming in and since all bids have to be
68 -- in by the close date, this will determine the end point of
69 -- the graph
70 --
71 -- x_graph_start_date OUT NOCOPY DATE,
72 -- As mentioned above, this is the starting point of the graph
73 -- on the x-axis. This is also the value that the caller must
74 -- use to determine the offset so that it can be converted to
75 -- a number. For example, if the start date is 01-Jan-04 and
76 -- the end date is 30-Jan-04, then the entire graph spans 30
77 -- days or a little more. This 30 days is made equivalent to
78 -- a constant defined in the package body - 2882880. If a bid
79 -- comes in on 04-Jan-04, then it is 3 days after the start date
80 -- and is plotted proportionately. The start date may not be
81 -- the date of the first bid but may be at a convenient point
82 -- in time slightly before it so that dates are displayed
83 -- pleasingly to the user. This is in the server timezone.
84 --
85 -- x_graph_end_date OUT NOCOPY DATE,
86 -- The end date of the graph. This may not be the auction
87 -- but will be the date of the last tick. Currently not
88 -- used by the caller. This is in the server timezone.
89 --
90 -- x_number_of_ticks OUT NOCOPY NUMBER,
91 -- This tells the caller the number of divisions on the
92 -- graph. This does not include the starting point of the
93 -- graph. Hence if there are three divisions, this field
94 -- will be 3 but 4 points will be plotted on the x-axis
95 --
96 -- x_multiplier OUT NOCOPY NUMBER,
97 -- The caller will multiply the difference between the date
98 -- to be plotted and the start date of the graph with this value
99 -- to obtain a numeric value that is passed to BI Beans
100 --
101 -- x_tick_length OUT NOCOPY NUMBER,
102 -- This contains the length of each tick as a numeric value.
103 -- This is sent to BI Beans so that it knows where to plot
104 -- each tick. It is the range of the graph / number of ticks.
105 --
106 -- x_tick_label_1-14 OUT NOCOPY VARCHAR2,
107 -- This is the tick label as plotted on the graph
108 -- Depending on the scale of the graph, this will include
109 -- the date or date and time or just time. e.g. of values:
110 -- 07jun, 07jun 10:40, 10:40
111 -- These are coded as individual elements and not as a
112 -- table because Java does not understand tables. These times
113 -- are in the client's timezone since these have to be displayed
114 -- to the user.
115 --
116 -- End of Comments
117 --------------------------------------------------------------------------------
118
119 PROCEDURE get_time_axis_tick_labels(
120 p_auction_header_id IN NUMBER,
121 p_graph_start_date IN DATE,
122 p_auction_close_date IN DATE,
123 x_graph_start_date OUT NOCOPY DATE,
124 x_graph_end_date OUT NOCOPY DATE,
125 x_number_of_ticks OUT NOCOPY NUMBER,
126 x_multiplier OUT NOCOPY NUMBER,
127 x_tick_length OUT NOCOPY NUMBER,
128 x_tick_label_1 OUT NOCOPY VARCHAR2,
129 x_tick_label_2 OUT NOCOPY VARCHAR2,
130 x_tick_label_3 OUT NOCOPY VARCHAR2,
131 x_tick_label_4 OUT NOCOPY VARCHAR2,
132 x_tick_label_5 OUT NOCOPY VARCHAR2,
133 x_tick_label_6 OUT NOCOPY VARCHAR2,
134 x_tick_label_7 OUT NOCOPY VARCHAR2,
135 x_tick_label_8 OUT NOCOPY VARCHAR2,
136 x_tick_label_9 OUT NOCOPY VARCHAR2,
137 x_tick_label_10 OUT NOCOPY VARCHAR2,
138 x_tick_label_11 OUT NOCOPY VARCHAR2,
139 x_tick_label_12 OUT NOCOPY VARCHAR2,
140 x_tick_label_13 OUT NOCOPY VARCHAR2,
141 x_tick_label_14 OUT NOCOPY VARCHAR2);
142
143 --------------------------------------------------------------------------------
144 -- check_estimated_qty_available --
145 --------------------------------------------------------------------------------
146 -- Start of Comments
147 --
148 -- API Name: check_estimated_qty_available
149 --
150 -- Type : Private
151 --
152 -- Pre-reqs: None
153 --
154 -- Function: This API is called by the live console UI for a blanket or CPA
155 -- that has lines. It determines whether the estimated quantity is
156 -- available on all lines. If estimated quantity is not available,
157 -- then certain graphs like the savings or amount graphs cannot
158 -- be shown.
159 --
160 --
161 -- Parameters:
162 --
163 -- p_auction_header_id IN NUMBER
164 -- Auction header id - required
165 --
166 -- p_auction_line_number IN NUMBER
167 -- If the graph is being plotted for a line, then the line
168 -- number is required. If this is omitted, the procedure
169 -- assumes that a header-level graph is being plotted.
170 -- The caller can pass -1 or null to indicate that this
171 -- is a header level graph
172 --
173 -- x_est_qty_available_flag OUT VARCHAR2
174 -- Returns Y if estimated quantity is available N if not
175 --
176 --
177 -- End of Comments
178 --------------------------------------------------------------------------------
179
180 PROCEDURE check_estimated_qty_available(
181 p_auction_header_id IN NUMBER,
182 p_auction_line_number IN NUMBER,
183 x_est_qty_available_flag OUT NOCOPY VARCHAR2);
184
185 --------------------------------------------------------------------------------
186 -- upgrade_bid_colors --
187 --------------------------------------------------------------------------------
188 -- Start of Comments
189 --
190 -- API Name: upgrade_bid_colors
191 --
192 -- Type : Private
193 --
194 -- Pre-reqs: None
195 --
196 -- Function: This API is called by the live console UI when a negotiation
197 -- that is being handled does not have colors set for bids.
198 --
199 -- Colors are assigned to each bid for the first time. If
200 -- a rebid occurs, the color is carried forward. This enables
201 -- the graph to plot all related bids with the same color and
202 -- shape icon. Though only 16 colors are handled today,
203 -- color ids are assigned sequentially ascending and can exceed
204 -- any number. The console does a mod 16 to display the color.
205 -- This allows the solution to be scalable - in case it is
206 -- decided to show more colors, the data in the bids does not have
207 -- to be changed.
208 --
209 -- Related bids are identified as having the same supplier,
210 -- supplier site and creator
211 --
212 -- Use of this procedure eliminates the need to do a costly
213 -- upgrade of all the pre-FPK data. Data is "upgraded" on
214 -- a need basis and the display will only be a little slow
215 -- for the first time any user accesses an old negotiation.
216 --
217 -- The procedure is an autonomous transaction so that it can
218 -- perform its updates without affecting the transaction of
219 -- the caller
220 --
221 -- Parameters:
222 --
223 -- p_auction_header_id IN NUMBER
224 -- Identifier of the auction that for which the live
225 -- console is being shown
226 --
227 --
228 -- End of Comments
229 --------------------------------------------------------------------------------
230
231 PROCEDURE upgrade_bid_colors( p_auction_header_id IN NUMBER);
232
233 --------------------------------------------------------------------------------
234 -- record_supplier_activity --
235 --------------------------------------------------------------------------------
236 -- Start of Comments
237 --
238 -- API Name: record_supplier_activities
239 --
240 -- Type: Private
241 --
242 -- Pre-reqs: None
243 --
244 -- Function: This API is called from within the recordSupplierActivity Util
245 -- method in the class java/util/SourcingCommonUtil
246 --
247 -- Effectively it will be called from controllers of all the pages that a
248 -- supplier might visit during the course of bidding on a negotiation.
249 --
250 -- IN Parameters:
251 -- p_auction_header_id NUMBER
252 -- p_auction_header_id_orig_amend NUMBER
253 -- p_trading_partner_id NUMBER
254 -- p_trading_partner_contact_id NUMBER
255 -- p_session_id NUMBER
256 -- p_activity_code VARCHAR2(25)
257 -- OUT Parameters:
258 -- p_record_status BOOLEAN
259 -- TRUE - activity recorded successfully
260 -- FALSE - activity recording failed
261 --
262 -- End of Comments
263 --------------------------------------------------------------------------------
264
265 PROCEDURE record_supplier_activity( p_auction_header_id IN NUMBER
266 , p_auction_header_id_orig_amend IN NUMBER
267 , p_trading_partner_id IN NUMBER
268 , p_trading_partner_contact_id IN NUMBER
269 , p_session_id IN NUMBER
270 , p_last_activity_code IN VARCHAR2
271 , p_bid_number IN NUMBER
272 , x_record_status OUT NOCOPY VARCHAR2);
273
274
275 --------------------------------------------------------------------------------
276 -- update_supplier_access --
277 --------------------------------------------------------------------------------
278 -- Start of Comments
279 --
280 -- API Name: update_supplier_access
281 --
282 -- Type: Private
283 --
284 -- Pre-reqs: None
285 --
286 -- Function: This API is called from within the updateSupplierAccess Util
287 -- method in the class java/util/SourcingCommonUtil
288 --
289 -- Effectively it will be called from the Manage Supplier Activities page
290 -- to update the access for a supplier on a negotiation
291 --
292 -- IN Parameters:
293 -- p_auction_header_id NUMBER
294 -- p_auction_header_id_orig_amend NUMBER
295 -- p_supplier_trading_partner_id NUMBER
296 -- p_buyer_contact_id NUMBER
297 -- p_action VARCHAR2(25)
298 -- OUT Parameters:
299 -- p_record_status BOOLEAN
300 -- TRUE - access update/insert successfully
301 -- FALSE - access updation failed
302 --
303 -- End of Comments
304 --------------------------------------------------------------------------------
305
306 PROCEDURE update_supplier_access( p_auction_header_id IN NUMBER
307 , p_auction_header_id_orig_amend IN NUMBER
308 , p_supplier_trading_partner_id IN NUMBER
309 , p_buyer_tp_contact_id IN NUMBER
310 , p_lock_status IN VARCHAR2
311 , p_lock_reason IN VARCHAR2
312 , x_record_status OUT NOCOPY VARCHAR2);
313
314 -----------------------------------------------------------------
315 ---- calculate_console_summary ----
316 -----------------------------------------------------------------
317 --
318 -- Start of Comments
319 --
320 -- API Name: calculate_console_summary
321 --
322 -- Type : public
323 --
324 -- Pre-reqs: None
325 --
326 -- Function: This API is called from ConsoleAMImpl.java to calculate
327 -- Auction Value, Current Value, Optimal Value(based on Auto
328 -- Award Recommendation), no bid value and num of lines without bids
329 --
330 -- Parameters:
331 --
332 -- P_AUCTION_ID IN NUMBER
333 -- Required - Auction_header_id of the negotiation
334 --
335 -- x_auction_value OUT NUMBER
336 -- Total value of the negotiation, calculated based on
337 -- line qty and current price
338 -- x_current_value OUT NUMBER
339 -- Total current value of the negotiation, calculated based on
340 -- awarded qty and current price
341 -- x_optimal_value OUT NUMBER
342 -- Total Value of the negotiation, calculated based on
343 -- awarded qty and bid price
344 -- x_no_bid_value OUT NUMBER
345 -- Total value of the lines that didn't receive bids, calculated
346 -- based on line qty and current price
347 -- x_no_bid_lines OUT NUMBER
348 -- Number of lines without bids
349 -----------------------------------------------------------------
350 PROCEDURE calculate_console_summary( p_auction_id IN NUMBER,
351 x_auction_value OUT NOCOPY NUMBER,
352 x_current_value OUT NOCOPY NUMBER,
353 x_optimal_value OUT NOCOPY NUMBER,
354 x_no_bid_value OUT NOCOPY NUMBER,
355 x_no_bid_lines OUT NOCOPY NUMBER
356 );
357
358 END PON_CONSOLE_PVT;
[Home] [Help]