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