1 PACKAGE PON_AWARD_PKG AS
2 -- $Header: PONAWRDS.pls 120.10 2007/08/27 19:34:30 liangxia ship $
3
4 g_xml_upload_mode CONSTANT VARCHAR2(3) := 'XML';
5 g_txt_upload_mode CONSTANT VARCHAR2(3) := 'TXT';
6
7 PROCEDURE clean_unawarded_items (p_batch_id IN NUMBER);
8
9 PROCEDURE reject_unawarded_active_bids(p_auction_header_id IN NUMBER,
10 p_user_id IN NUMBER,
11 p_note_to_rejected IN VARCHAR2,
12 p_neg_has_lines IN VARCHAR2);
13
14 PROCEDURE complete_award (p_auction_header_id_encrypted IN VARCHAR2,
15 p_auction_header_id IN NUMBER,
16 p_note_to_rejected IN VARCHAR2,
17 p_shared_award_decision IN VARCHAR2,
18 p_user_id IN NUMBER,
19 p_create_po_flag IN VARCHAR2,
20 p_source_reqs_flag IN VARCHAR2,
21 p_no_bids_flag IN VARCHAR2,
22 p_has_backing_reqs_flag IN VARCHAR2,
23 p_outcome_status IN VARCHAR2,
24 p_has_scoring_teams_flag IN VARCHAR2,
25 p_scoring_lock_tpc_id IN NUMBER);
26
27 PROCEDURE complete_auction (p_auction_header_id IN NUMBER );
28
29 PROCEDURE award_notification (p_auction_header_id_encrypted IN VARCHAR2,
30 p_auction_header_id IN NUMBER,
31 p_shared_award_decision IN VARCHAR2);
32
33 PROCEDURE complete_item_disposition (p_auction_header_id IN NUMBER,
34 p_line_number IN NUMBER,
35 p_award_quantity IN NUMBER);
36
37 PROCEDURE award_item_disposition (p_auction_header_id IN NUMBER,
38 p_line_number IN NUMBER,
39 p_award_quantity IN NUMBER);
40 --
41 TYPE PON_AWARD_LINES_REC IS RECORD (
42 bid_number NUMBER,
43 line_number NUMBER,
44 award_status VARCHAR2(10),
45 award_quantity NUMBER,
46 award_date DATE,
47 note_to_supplier VARCHAR2(4000),
48 group_type pon_auction_item_prices_all.group_type%type,
49 award_shipment_number NUMBER
50 );
51 TYPE t_award_lines IS TABLE OF PON_AWARD_LINES_REC
52 INDEX BY BINARY_INTEGER;
53
54 -- FPK: CPA
55 TYPE PON_AWARD_HEADER_REC IS RECORD (
56 bid_number NUMBER,
57 award_status PON_BID_HEADERS.AWARD_STATUS%TYPE,
58 award_date DATE
59 );
60
61 TYPE t_awarded_bid_headers IS TABLE OF PON_AWARD_HEADER_REC
62 INDEX BY BINARY_INTEGER;
63
64 t_emptytbl t_awarded_bid_headers;
65
66 TYPE Number_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
67
68 TYPE Date_tbl_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
69
70 TYPE Char25_tbl_type IS TABLE OF VARCHAR2(25) INDEX BY BINARY_INTEGER;
71 -- end of FPK: CPA
72
73 --
74 -- Constants for mode values
75 g_AWARD_QUOTE VARCHAR2(20) := 'AWARD_QUOTE';
76 g_AWARD_MULTIPLE_LINES VARCHAR2(20) := 'AWARD_MULTIPLE_LINES';
77 g_AWARD_LINE VARCHAR2(20) := 'AWARD_LINE';
78 --Awarded by Line on Award Line page(Horizontal Page)
79 g_AWARD_LINE_H VARCHAR2(20) := 'AWARD_LINE_H';
80 g_AWARD_GROUP VARCHAR2(20) := 'AWARD_GROUP';
81 g_AWARD_GROUP_H VARCHAR2(20) := 'AWARD_GROUP_H';
82 g_AWARD_AUTO_RECOMMEND CONSTANT VARCHAR2(30) := 'AWARD_AUTO_RECOMMEND';
83 g_AWARD_OPTIMIZATION CONSTANT VARCHAR2(30) := 'AWARD_OPTIMIZATION';
84 --
85 g_AWARD_OUTCOME_WIN CONSTANT VARCHAR2(10) := 'WIN';
86 g_AWARD_OUTCOME_LOSE CONSTANT VARCHAR2(10) := 'LOSE';
87 g_AWARD_OUTCOME_NOAWARD CONSTANT VARCHAR2(10) := 'NA';
88 g_AWARD_OUTCOME_NOBID CONSTANT VARCHAR2(10) := 'NB';
89 --
90 --
91 PROCEDURE award_auction
92 ( p_auctioneer_id IN NUMBER
93 , p_auction_header_id IN NUMBER
94 , p_last_update_date IN DATE
95 , p_mode IN VARCHAR2
96 , p_line_num IN NUMBER
97 , p_award_table IN PON_AWARD_TABLE
98 , p_note_to_accepted IN VARCHAR2
99 , p_note_to_rejected IN VARCHAR2
100 , p_batch_id IN NUMBER
101 , x_status OUT NOCOPY VARCHAR2
102 );
103 --
104 PROCEDURE update_bid_item_prices
105 (
106 p_auction_id IN NUMBER,
107 p_award_lines IN t_award_lines,
108 p_auctioneer_id IN NUMBER,
109 p_mode IN VARCHAR2
110 );
111 --
112 /*==========================================================================================================================
113 * PROCEDURE : upd_single_bid_item_prices_qt
114 * PARAMETERS: 1. p_bid_number - bid number for which the award_price and shipment no to be updated.
115 * 2. p_line_number - corresponding line number
116 * 3. p_award_status - award status 'AWARDED' or 'REJECTED'
117 * 4. p_award_quantity - The quantity awarded
118 * 5. p_award_date -- Award Datw
119 * 6. p_auctioneer_id - Id of person who is saving award
120 * 7. p_award_shipment_number - Quantity awarded falls in the tiers range corresponding to the shipment number
121 * COMMENT : This procedure calculates the award price based on the per unit and fixed amount component and
122 * corresponding to the award shipment number. PON_BID_ITEM_PRICES is updated accordingly
123 *==========================================================================================================================*/
124 PROCEDURE upd_single_bid_item_prices_qt
125 (
126 p_bid_number IN NUMBER,
127 p_line_number IN NUMBER,
128 p_award_status IN VARCHAR2,
129 p_award_quantity IN NUMBER,
130 p_award_date IN DATE,
131 p_auctioneer_id IN NUMBER,
132 p_award_shipment_number IN NUMBER
133 );
134 --
135 PROCEDURE update_single_bid_item_prices
136 (
137 p_bid_number IN NUMBER,
138 p_line_number IN NUMBER,
139 p_award_status IN VARCHAR2,
140 p_award_quantity IN NUMBER,
141 p_award_date IN DATE,
142 p_auctioneer_id IN NUMBER
143 );
144 --
145 PROCEDURE update_bid_headers
146 (
147 p_auction_id IN NUMBER,
148 p_auctioneer_id IN NUMBER,
149 p_awarded_bid_headers IN t_awarded_bid_headers DEFAULT t_emptytbl, -- FPK: CPA
150 p_neg_has_lines IN VARCHAR2 -- FPK: CPA
151 );
152 --
153 PROCEDURE update_single_bid_header
154 (
155 p_bid_number IN NUMBER,
156 p_auctioneer_id IN NUMBER
157
158 );
159 --
160 PROCEDURE update_auction_item_prices
161 (
162 p_auction_id IN NUMBER,
163 p_line_number IN NUMBER,
164 p_award_date IN DATE,
165 p_auctioneer_id IN NUMBER,
166 p_mode IN VARCHAR2
167 );
168 --
169 PROCEDURE update_single_auction_item
170 (
171 p_auction_id IN NUMBER,
172 p_line_number IN NUMBER,
173 p_auctioneer_id IN NUMBER,
174 p_mode IN pon_auction_item_prices_all.award_mode%type
175 );
176 --
177 --
178 PROCEDURE update_auction_headers
179 (
180 p_auction_id IN NUMBER,
181 p_mode IN VARCHAR2,
182 p_award_date IN DATE,
183 p_auctioneer_id IN NUMBER,
184 p_neg_has_lines IN VARCHAR2 -- FPK: CPA
185 );
186 --
187 --
188 PROCEDURE update_award_agreement_amount
189 (
190 p_auction_id IN NUMBER,
191 p_auctioneer_id IN NUMBER
192 );
193 --
194 --
195 PROCEDURE bulk_update_pon_acceptances
196 ( p_auction_header_id IN NUMBER,
197 p_line_number IN NUMBER,
198 p_note_to_accepted IN VARCHAR2,
199 p_note_to_rejected IN VARCHAR2,
200 p_award_date IN DATE,
201 p_auctioneer_id IN NUMBER,
202 p_mode IN VARCHAR2
203 )
204 ;
205 --
206 PROCEDURE update_unawarded_acceptances
207 (
208 p_auction_header_id IN NUMBER,
209 p_line_number IN NUMBER,
210 p_note_to_rejected IN VARCHAR2,
211 p_award_date IN DATE,
212 p_auctioneer_id IN NUMBER
213 )
214 ;
215 --
216 FUNCTION get_award_status(award_outcome IN VARCHAR2 ) RETURN VARCHAR2;
217 --
218
219 PROCEDURE update_notes_for_bid
220 (
221 p_bid_number IN NUMBER,
222 p_note_to_supplier IN VARCHAR2,
223 p_internal_note IN VARCHAR2,
224 p_auctioneer_id IN NUMBER
225 );
226
227 --
228 PROCEDURE clear_draft_awards
229 (
230 p_auction_header_id IN NUMBER,
231 p_line_number IN NUMBER,
232 p_award_date IN DATE,
233 p_auctioneer_id IN NUMBER,
234 p_neg_has_lines IN VARCHAR2 -- FPK: CPA
235 );
236 --
237 PROCEDURE clear_awards_recommendation
238 (
239 p_auction_header_id IN NUMBER,
240 p_award_date IN DATE,
241 p_auctioneer_id IN NUMBER
242 );
243 --
244 PROCEDURE save_award_recommendation
245 (
246 p_batch_id IN NUMBER,
247 p_auctioneer_id IN NUMBER,
248 p_last_update_date IN DATE,
249 p_mode IN VARCHAR2,
250 x_status OUT NOCOPY VARCHAR2
251 );
252 --
253 PROCEDURE accept_award_scenario
254 (
255 p_scenario_id IN NUMBER,
256 p_auctioneer_id IN NUMBER,
257 p_last_update_date IN DATE,
258 x_status OUT NOCOPY VARCHAR2
259 );
260 --
261 PROCEDURE copy_award_scenario
262 (
263 p_scenario_id IN NUMBER,
264 p_user_id IN NUMBER,
265 p_cost_scenario_flag IN VARCHAR2,
266 x_cost_scenario_id OUT NOCOPY NUMBER,
267 x_status OUT NOCOPY VARCHAR2
268 );
269 --
270 PROCEDURE save_award_spreadsheet
271 (
272 p_batch_id IN NUMBER,
273 p_auction_header_id IN NUMBER,
274 p_mode IN VARCHAR2,
275 p_auctioneer_id IN NUMBER,
276 p_last_update_date IN DATE,
277 p_batch_enabled IN VARCHAR2,
278 p_is_xml_upload IN VARCHAR2,
279 x_status OUT NOCOPY VARCHAR2
280 );
281
282 PROCEDURE batch_award_spreadsheet
283 (
284 p_auction_header_id IN NUMBER,
285 p_mode IN VARCHAR2,
286 p_auctioneer_id IN NUMBER,
287 p_last_update_date IN DATE,
288 x_status OUT NOCOPY VARCHAR2
289 );
290
291 --
292 FUNCTION is_auction_not_updated
293 (
294 p_auction_header_id NUMBER,
295 p_last_update_date DATE
296 ) RETURN BOOLEAN;
297 --
298 PROCEDURE toggle_shortlisting
299 ( p_user_id IN NUMBER
300 , p_bid_number IN NUMBER
301 , p_event IN VARCHAR2
302 );
303 --
304 FUNCTION get_award_amount(p_auction_header_id IN NUMBER) RETURN NUMBER;
305 --
306 PROCEDURE award_bi_subline (
307 p_auction_header_id IN pon_bid_headers.auction_header_id%TYPE,
308 p_bid_number IN pon_bid_headers.bid_number%TYPE,
309 p_parent_line_number IN pon_bid_item_prices.line_number%TYPE,
310 p_award_status IN pon_bid_item_prices.award_status%TYPE,
311 p_award_date IN pon_bid_item_prices.award_date%TYPE,
312 p_auctioneer_id pon_bid_item_prices.LAST_UPDATED_BY%TYPE);
313 --
314 --
315 ----------------------------------------------------------------
316 --and sets the award status of parent line by querying up the child lines
317 ----------------------------------------------------------------
318 PROCEDURE update_bi_group_award (
319 p_auction_header_id IN pon_bid_headers.auction_header_id%TYPE,
320 p_bid_number IN pon_bid_headers.bid_number%TYPE,
321 p_parent_line_number IN pon_auction_item_prices_all.parent_line_number%TYPE,
322 p_award_date IN pon_bid_item_prices.award_date%TYPE,
323 p_auctioneer_id IN pon_bid_item_prices.last_updated_by%TYPE);
324 --
325 --
326 PROCEDURE update_ai_group_award (
327 p_auction_header_id IN pon_bid_headers.auction_header_id%TYPE,
328 p_line_number IN pon_bid_item_prices.line_number%TYPE,
329 p_award_date IN pon_bid_item_prices.award_date%TYPE,
330 p_auctioneer_id IN pon_bid_item_prices.last_updated_by%TYPE);
331 --
332
333 PROCEDURE get_award_totals(
334 p_auction_header_id in number,
335 p_award_total out nocopy number,
336 p_current_total out nocopy number,
337 p_savings_total out nocopy number,
338 p_savings_percent out nocopy number);
339
340
341 FUNCTION does_bid_exist
342 (
343 p_scenario_id IN PON_OPTIMIZE_CONSTRAINTS.SCENARIO_ID%TYPE,
344 p_sequence_number IN PON_OPTIMIZE_CONSTRAINTS.SEQUENCE_NUMBER%TYPE,
345 p_bid_number IN PON_BID_HEADERS.BID_NUMBER%TYPE
346 ) RETURN VARCHAR2;
347
348
349 FUNCTION has_scored_attribute
350 (
351 p_auction_header_id IN PON_AUCTION_ATTRIBUTES.AUCTION_HEADER_ID%TYPE,
352 p_line_number IN PON_AUCTION_ATTRIBUTES.LINE_NUMBER%TYPE
353 ) RETURN VARCHAR2;
354
355
356 PROCEDURE preprocess_cost_of_constraint
357 (
358 p_scenario_id IN NUMBER,
359 p_user_id IN NUMBER,
360 p_cost_constraint_flag IN VARCHAR2,
361 p_constraint_type IN VARCHAR2,
362 p_internal_type IN VARCHAR2,
363 p_line_number IN NUMBER,
364 p_sequence_number IN NUMBER,
365 x_cost_scenario_id OUT NOCOPY NUMBER,
366 x_status OUT NOCOPY VARCHAR2
367 );
368
369 PROCEDURE postprocess_cost_of_constraint
370 (
371 p_scenario_id IN NUMBER,
372 p_constraint_type IN VARCHAR2,
373 p_internal_type IN VARCHAR2,
374 p_line_number IN NUMBER,
375 p_sequence_number IN NUMBER,
376 x_status OUT NOCOPY VARCHAR2
377 );
378
379 PROCEDURE reset_cost_of_constraint
380 (
381 p_scenario_id IN NUMBER,
382 x_status OUT NOCOPY VARCHAR2
383 );
384
385 FUNCTION GET_SAVING_PERCENT_INCENTIVE (p_scenario_id IN NUMBER)
386 RETURN NUMBER;
387
388 END PON_AWARD_PKG;
[Home] [Help]