1 PACKAGE PON_NEGOTIATION_HELPER_PVT AUTHID CURRENT_USER AS
2 /* $Header: PONNEGHS.pls 120.11.12020000.2 2013/02/09 07:38:57 hvutukur ship $ */
3
4 /*======================================================================
5 PROCEDURE : get_search_min_disp_line_num
6 PARAMETERS: 1. p_auction_header_id - The auction header id
7 2. p_value - The value entered by the user for search
8 3. x_min_disp_line_num - Out parameter to indicate at which
9 line to start displaying
10 COMMENT : This procedure is invoked when the user searches on the
11 lines region with line number as the search criteria
12 and greater than as the search condition.
13 Given the value entered by the user (p_value) this
14 procedure will return the disp_line_number above which
15 all lines should be shown.
16 ======================================================================*/
17
18 PROCEDURE get_search_min_disp_line_num (
19 p_auction_header_id IN NUMBER,
20 p_value IN NUMBER,
21 x_min_disp_line_num OUT NOCOPY NUMBER);
22
23 /*======================================================================
24 PROCEDURE : get_search_max_disp_line_num
25 PARAMETERS: 1. p_auction_header_id - The auction header id
26 2. p_value - The value entered by the user for search
27 3. x_max_disp_line_num - Out parameter to indicate at which
28 line to stop displaying
29 COMMENT : This procedure is invoked when the user searches on the
30 lines region with line number as the search criteria
31 and less than as the search condition.
32 Given the value entered by the user (p_value) this
33 procedure will return the disp_line_number below which
34 all lines should be shown.
35 ======================================================================*/
36
37 PROCEDURE get_search_max_disp_line_num (
38 p_auction_header_id IN NUMBER,
39 p_value IN NUMBER,
40 x_max_disp_line_num OUT NOCOPY NUMBER);
41
42 /*======================================================================
43 PROCEDURE : get_auction_request_id
44 PARAMETERS: 1. p_auction_header_id - The auction header id
45 2. x_request_id - The reuqest id on the auction.
46 COMMENT : This procedure will return the value present in the
47 REQUEST_ID column of PON_AUCTION_HEADERS_ALL table
48 ======================================================================*/
49
50 PROCEDURE get_auction_request_id (
51 p_auction_header_id IN NUMBER,
52 x_request_id OUT NOCOPY NUMBER);
53
54 /*======================================================================
55 PROCEDURE : has_fixed_amt_or_per_unit_pe
56 PARAMETERS: 1. p_auction_header_id - The auction header id
57 2. x_has_fixed_amt_or_per_unit_pe - return value - Y if there
58 are fixed amount or per unit price elements else N.
59 3. x_result - return status.
60 4. x_error_code - error code
61 5. x_error_message - The actual error message
62 COMMENT : This procedure will return Y if there are any
63 fixed amount or per unit price elements
64 ======================================================================*/
65
66 PROCEDURE has_fixed_amt_or_per_unit_pe(
67 p_auction_header_id IN NUMBER,
68 x_has_fixed_amt_or_per_unit_pe OUT NOCOPY VARCHAR2,
69 x_result OUT NOCOPY VARCHAR2,
70 x_error_code OUT NOCOPY VARCHAR2,
71 x_error_message OUT NOCOPY VARCHAR2);
72
73 /*======================================================================
74 PROCEDURE : has_goods_line_fixed_amount_pe
75 PARAMETERS: 1. p_auction_header_id - The auction header id
76 2. x_has_goods_line_fixed_amount_pe - return value - Y if there
77 are goods lines with fixed amt price elements
78 3. x_result - return status.
79 4. x_error_code - error code
80 5. x_error_message - The actual error message
81 COMMENT : This procedure will return Y if there are any goods lines
82 with fixed amount price elements
83 ======================================================================*/
84
85 PROCEDURE has_goods_line_fixed_amount_pe(
86 p_auction_header_id IN NUMBER,
87 x_has_goods_line_fixed_amt_pe OUT NOCOPY VARCHAR2,
88 x_result OUT NOCOPY VARCHAR2,
89 x_error_code OUT NOCOPY VARCHAR2,
90 x_error_message OUT NOCOPY VARCHAR2);
91
92 /*======================================================================
93 PROCEDURE : has_items
94 PARAMETERS: 1. p_auction_header_id - The auction header id
95 2. x_has_items - return value Y if there
96 items else N.
97 3. x_result - return status.
98 4. x_error_code - error code
99 5. x_error_message - The actual error message
100 COMMENT : This method returns Y if there are any items present
101 in the negotiation. else it will return N
102 ======================================================================*/
103
104 PROCEDURE has_items (
105 p_auction_header_id IN NUMBER,
106 x_has_items OUT NOCOPY VARCHAR2,
107 x_result OUT NOCOPY VARCHAR2,
108 x_error_code OUT NOCOPY VARCHAR2,
109 x_error_message OUT NOCOPY VARCHAR2);
110
111 /*======================================================================
112 PROCEDURE : get_number_of_lines
113 PARAMETERS: 1. p_auction_header_id - The auction header id
114 2. x_number_of_lines - Return value containing
115 the number of lines.
116 3. x_result - return status.
117 4. x_error_code - error code
118 5. x_error_message - The actual error message
119 COMMENT : This procedure will return the number of lines in the
120 negotiation.
121 ======================================================================*/
122
123 PROCEDURE get_number_of_lines (
124 p_auction_header_id IN NUMBER,
125 x_number_of_lines OUT NOCOPY NUMBER,
126 x_result OUT NOCOPY VARCHAR2,
127 x_error_code OUT NOCOPY VARCHAR2,
128 x_error_message OUT NOCOPY VARCHAR2);
129
130 /*======================================================================
131 PROCEDURE : get_max_internal_and_doc_line_num
132 PARAMETERS: 1. p_auction_header_id - The auction header id
133 2. x_max_internal_line_num - The maximum internal line
134 number in all the rounds
135 3. x_max_document_line_num - The maximum subline sequence
136 number in all the rounds
137 4. x_result - return status.
138 5. x_error_code - error code
139 6. x_error_message - The actual error message
140 COMMENT : This procedure will return the maximum value of the
141 LINE_NUMBER and SUB_LINE_SEQUENCE_NUMBER columns in all
142 the rounds
143 ======================================================================*/
144
145 PROCEDURE get_max_internal_and_doc_num (
146 p_auction_header_id IN NUMBER,
147 x_max_internal_line_num OUT NOCOPY NUMBER,
148 x_max_document_line_num OUT NOCOPY NUMBER,
149 x_max_disp_line_num OUT NOCOPY NUMBER,
150 x_result OUT NOCOPY VARCHAR2,
151 x_error_code OUT NOCOPY VARCHAR2,
152 x_error_message OUT NOCOPY VARCHAR2);
153
154 /*======================================================================
155 PROCEDURE : remove_score
156 PARAMETERS: 1. x_result - return status.
157 2. x_error_code - error code
158 3. x_error_message - The actual error message
159 4. p_auction_header_id - The auction header id
160 COMMENT : This procedure will remove the scoring information from
161 the given negotiation.
162 ======================================================================*/
163
164 PROCEDURE remove_score (
165 x_result OUT NOCOPY VARCHAR2,
166 x_error_code OUT NOCOPY VARCHAR2,
167 x_error_message OUT NOCOPY VARCHAR2,
168 p_auction_header_id IN NUMBER);
169
170 /*======================================================================
171 PROCEDURE : has_price_elements
172 PARAMETERS: 1. x_result - return status.
173 2. x_error_code - error code
174 3. x_error_message - The actual error message
175 4. p_auction_header_id - The auction header id
176 COMMENT : This procedure will return Y if there are price elements
177 on the negotiation else it will return N
178 ======================================================================*/
179
180 PROCEDURE has_price_elements (
181 x_result OUT NOCOPY VARCHAR2,
182 x_error_code OUT NOCOPY VARCHAR2,
183 x_error_message OUT NOCOPY VARCHAR2,
184 p_auction_header_id IN NUMBER,
185 x_has_price_elements OUT NOCOPY VARCHAR2);
186
187 /*======================================================================
188 PROCEDURE : has_supplier_price_elements
189 PARAMETERS: 1. x_result - return status.
190 2. x_error_code - error code
191 3. x_error_message - The actual error message
192 4. p_auction_header_id - The auction header id
193 COMMENT : This procedure will return Y if there are supplier price
194 elements on the negotiation else it will return N
195 ======================================================================*/
196
197 PROCEDURE has_supplier_price_elements (
198 x_result OUT NOCOPY VARCHAR2,
199 x_error_code OUT NOCOPY VARCHAR2,
200 x_error_message OUT NOCOPY VARCHAR2,
201 p_auction_header_id IN NUMBER,
202 x_has_supplier_price_elements OUT NOCOPY VARCHAR2);
203
204 /*======================================================================
205 PROCEDURE : has_buyer_price_elements
206 PARAMETERS: 1. x_result - return status.
207 2. x_error_code - error code
208 3. x_error_message - The actual error message
209 4. p_auction_header_id - The auction header id
210 COMMENT : This procedure will return Y if there are buyer price
211 elements on the negotiation else it will return N
212 ======================================================================*/
213
214 PROCEDURE has_buyer_price_elements (
215 x_result OUT NOCOPY VARCHAR2,
216 x_error_code OUT NOCOPY VARCHAR2,
217 x_error_message OUT NOCOPY VARCHAR2,
218 p_auction_header_id IN NUMBER,
219 x_has_buyer_price_elements OUT NOCOPY VARCHAR2);
220
221 /*====================================================================================
222 PROCEDURE : SYNC_PF_VALUES_BIDDING_PARTIES
223 DESCRIPTION: Procedure to synchronize the price factor values due to modification
224 in the supplier invited
225 PARAMETERS: 1. p_auction_header_id - The auction header id
226 2. p_line_number - The line_number of that line which is, or the
227 price factors of which, are modified
228 3. p_action - The task to be performed. The possible values it takes is
229 ADD_SUPPLIER => Add price factor values for a new supplier
230 DELETE_SUPPLIER => Delete the price factor values for a supplier
231 who is deleted
232 4. x_result - return status.
233 5. x_error_code - error code
234 6. x_error_message - The actual error message
235 COMMENT : This procedure will synchronise the price factor
236 values when a supplier is added/deleted
237 ====================================================================================*/
238
239 PROCEDURE SYNC_PF_VALUES_BIDDING_PARTIES(
240 p_auction_header_id IN NUMBER,
241 p_supplier_seq_num IN NUMBER,
242 p_action IN VARCHAR2,
243 x_result OUT NOCOPY VARCHAR2,
244 x_error_code OUT NOCOPY VARCHAR2,
245 x_error_message OUT NOCOPY VARCHAR2);
246
247 /*====================================================================================
248 PROCEDURE : SYNC_PF_VALUES_ITEM_PRICES
249 DESCRIPTION: Procedure to synchronize the price factor values due to modification
250 in the lines or their price factors
251 PARAMETERS: 1. p_auction_header_id - The auction header id
252 2. p_line_number - The line_number of that line which is, or the
253 price factors of which, are modified
254 3. p_add_pf - 'T' implies the new price factors have to be added
255 4. p_del_pf - 'T' implies the deleted price factors have to be removed
256 5. x_result - return status.
257 6. x_error_code - error code
258 7. x_error_message - The actual error message
259 COMMENT : This procedure will synchronise the price factor
260 values table when the price factors of a line is added/deleted/modified
261 ====================================================================================*/
262
263 PROCEDURE SYNC_PF_VALUES_ITEM_PRICES(
264 p_auction_header_id IN NUMBER,
265 p_line_number IN NUMBER,
266 p_add_pf IN VARCHAR2,
267 p_del_pf IN VARCHAR2,
268 x_result OUT NOCOPY VARCHAR2,
269 x_error_code OUT NOCOPY VARCHAR2,
270 x_error_message OUT NOCOPY VARCHAR2);
271
272 /*======================================================================
273 PROCEDURE : delete_all_lines
274 PARAMETERS: 1. x_result - return status.
275 2. x_error_code - error code
276 3. x_error_message - The actual error message
277 4. p_auction_header_id - The auction header id
278 COMMENT : This procedure delete all the lines in the negotiation
279 and also its children
280 ======================================================================*/
281
282 PROCEDURE delete_all_lines (
283 x_result OUT NOCOPY VARCHAR2,
284 x_error_code OUT NOCOPY VARCHAR2,
285 x_error_message OUT NOCOPY VARCHAR2,
286 p_auction_header_id IN NUMBER);
287
291 2. x_error_code - error code
288 /*======================================================================
289 PROCEDURE : delete_single_line
290 PARAMETERS: 1. x_result - return status.
292 3. x_error_message - The actual error message
293 4. p_auction_header_id - The auction header id
294 5. p_line_number - The line to be deleted
295 6. p_group_type - The group type of the line to be
296 deleted.
297 7. p_origination_code - The origination code for this line
298 8. p_org_id - The org id for this line
299 9. p_parent_line_number - The parent line number for
300 this line
301 10. p_sub_line_sequence_number - The sub line sequence
302 number for this line
303 COMMENT : This procedure will delete the given line. If it is a lot
304 or a group then all the lot line and group lines will
305 also be deleted.
306 ======================================================================*/
307
308 PROCEDURE delete_single_line (
309 x_result OUT NOCOPY VARCHAR2,
310 x_error_code OUT NOCOPY VARCHAR2,
311 x_error_message OUT NOCOPY VARCHAR2,
312 p_auction_header_id IN NUMBER,
313 p_line_number IN NUMBER,
314 p_group_type IN VARCHAR2,
315 p_origination_code IN VARCHAR2,
316 p_org_id IN NUMBER,
317 p_parent_line_number IN NUMBER,
318 p_sub_line_sequence_number IN NUMBER,
319 x_number_of_lines_deleted IN OUT NOCOPY NUMBER);
320
321 /*======================================================================
322 PROCEDURE : RENUMBER_LINES
323 PARAMETERS: 1. p_auction_header_id - The auction header id
324 2. p_min_disp_line_number_parent - The disp line number
325 of the minimum LINE/GROUP/LOT from where to correct
326 the sequences
327 3. p_min_disp_line_number_child - The disp line number of
328 the minimum LOT_LINE/GROUP_LINE from where to correct
329 the sequences.
330 4. p_min_child_parent_line_num - The parent line number
331 of the line given in step 3.
332 5. x_last_line_number - The sub_line_sequence of the last
333 row that is a lot/line/group.
334 COMMENT : This procedure will correct the sequence numbers -
335 SUB_LINE_SEQUENCE_NUMBER, DISP_LINE_NUMBER and
336 DOCUMENT_DISP_LINE_NUMBER
337 ======================================================================*/
338
339 PROCEDURE RENUMBER_LINES (
340 x_result OUT NOCOPY VARCHAR2,
341 x_error_code OUT NOCOPY VARCHAR2,
342 x_error_message OUT NOCOPY VARCHAR2,
343 p_auction_header_id IN NUMBER,
344 p_min_disp_line_number_parent IN NUMBER,
345 p_min_disp_line_number_child IN NUMBER,
346 p_min_child_parent_line_num IN NUMBER,
347 x_last_line_number OUT NOCOPY NUMBER);
348
349 /*======================================================================
350 PROCEDURE : Delete_Payment_Attachments
351 PARAMETERS: 1. p_auction_header_id - The auction header id
352 2. p_curr_from_line_number - The starting line number- Needed in batching
353 3. p_curr_to_line_number - The max line number- needed for batching
354
355 COMMENT : This procedure will delete the attachments for all the payments for lines in range
356 ======================================================================*/
357 PROCEDURE Delete_Payment_Attachments (
358 p_auction_header_id IN NUMBER,
359 p_curr_from_line_number IN NUMBER,
360 p_curr_to_line_number IN NUMBER
361 );
362
363
364 /*======================================================================
365 PROCEDURE : get_srch_max_disp_line_numbers
366 PARAMETERS: 1. p_curr_auction_header_id - The current auction header id
367 2. p_prev_auction_header_id - The previous auction header id
368 3. p_value - The value entered by the user for search
369 4. x_curr_max_disp_line_num - Out parameter to indicate at which
370 line to stop displaying
371 5. x_prev_max_disp_line_num - Out parameter to indicate at which
372 line to stop displaying
373 COMMENT : This procedure is invoked when the user searches on the
374 lines region with line number as the search criteria
375 and less than as the search condition.
376 Given the value entered by the user (p_value) this
377 procedure will return the disp_line_number below which
378 all lines should be shown.
379 ======================================================================*/
380
381 PROCEDURE get_srch_max_disp_line_numbers (
382 p_curr_auction_header_id IN NUMBER,
383 p_prev_auction_header_id IN NUMBER,
384 p_value IN NUMBER,
385 x_curr_max_disp_line_num OUT NOCOPY NUMBER,
386 x_prev_max_disp_line_num OUT NOCOPY NUMBER
387 );
388
389
390 /*======================================================================
391 PROCEDURE : get_srch_min_disp_line_numbers
392 PARAMETERS: 1. p_curr_auction_header_id - The current auction header id
393 2. p_prev_auction_header_id - The previous auction header id
394 3. p_value - The value entered by the user for search
395 4. x_curr_min_disp_line_num - Out parameter to indicate at which
396 line to start displaying for current auction
397 5. x_prev_min_disp_line_num - Out parameter to indicate at which
398 line to start displaying for previous auction
399 COMMENT : This procedure is invoked when the user searches on the
400 lines region with line number as the search criteria
401 and greater than as the search condition.
405 ======================================================================*/
402 Given the value entered by the user (p_value) this
403 procedure will return the disp_line_number above which
404 all lines should be shown.
406
407 PROCEDURE get_srch_min_disp_line_numbers(
408 p_curr_auction_header_id IN NUMBER,
409 p_prev_auction_header_id IN NUMBER,
410 p_value IN NUMBER,
411 x_curr_min_disp_line_num OUT NOCOPY NUMBER,
412 x_prev_min_disp_line_num OUT NOCOPY NUMBER
413 );
414
415
416 /*======================================================================
417 PROCEDURE : DELETE_DISCUSSIONS
418 PARAMETERS: 1. x_result - return status.
419 2. x_error_code - error code
420 3. x_error_message - The actual error message
421 4. p_auction_header_id - The auction header id
422 COMMENT : This procedure deletes all the discussions in the negotiation
423 and also its children
424 ======================================================================*/
425
426 PROCEDURE DELETE_DISCUSSIONS (
427 x_result OUT NOCOPY VARCHAR2,
428 x_error_code OUT NOCOPY VARCHAR2,
429 x_error_message OUT NOCOPY VARCHAR2,
430 p_auction_header_id IN NUMBER);
431
432 /*======================================================================
433 PROCEDURE : UPDATE_STAG_LINES_CLOSE_DATES
434 PARAMETERS: 1. p_auction_header_id - The auction header id
435 2. p_first_line_close_date - The staggered closing interval
436 3. p_staggered_closing_interval - The auction header id
437 4. x_last_line_close_date - The close date of the last line
438 5. x_result - return status.
439 6. x_error_code - error code
440 7. x_error_message - The actual error message
441 COMMENT : This procedure updates the close dates of the lines when
442 the draft negotiation is saved
443 ======================================================================*/
444
445 PROCEDURE UPDATE_STAG_LINES_CLOSE_DATES(
446 x_result OUT NOCOPY VARCHAR2,
447 x_error_code OUT NOCOPY VARCHAR2,
448 x_error_message OUT NOCOPY VARCHAR2,
449 p_auction_header_id in Number,
450 p_first_line_close_date in date,
451 p_staggered_closing_interval in number,
452 p_start_disp_line_number in number,
453 x_last_line_close_date out nocopy date);
454
455 /*======================================================================
456 * FUNCTION : COUNT_LINES_LOTS_GROUPS PUBLIC
457 * PARAMETERS:
458 * p_auction_header_id IN header id of the auction
459 *
460 * COMMENT : returns the count of LINES, LOTS and GROUPS in the
461 * negotiation
462 *======================================================================*/
463
464 FUNCTION COUNT_LINES_LOTS_GROUPS (p_auction_header_id IN NUMBER) RETURN NUMBER;
465
466 /*======================================================================
467 * FUNCTION : GET_PO_AUTHORIZATION_STATUS PUBLIC
468 * PARAMETERS:
469 * p_document_id IN po header id
470 * p_document_type IN the PO document type ('PO'/'PA')
471 * p_document_subtype IN PO subdoctype id (null)
472 *
473 * COMMENT : returns the authorization status of PO
474 *
475 *======================================================================*/
476
477 FUNCTION GET_PO_AUTHORIZATION_STATUS (
478 p_document_id IN VARCHAR2 ,
479 p_document_type IN VARCHAR2 ,
480 p_document_subtype IN VARCHAR2
481 ) RETURN VARCHAR2;
482
483 /*======================================================================
484 * PROCEDURE : HAS_PRICE_TIERS
485 * PARAMETERS: 1. x_result - return status.
486 * 2. x_error_code - error code
487 * 3. x_error_message - The actual error message
488 * 4. p_auction_header_id - The auction header id
489 * 5. x_has_price_tiers - flag to indicate if negotiation has price tiers or not
490 * COMMENT : It takes auction header id as the in parameter and returns Y if there is a line with price
491 * tier, for this auction,. If there is no such line it returns N.
492 *======================================================================*/
493
494 PROCEDURE HAS_PRICE_TIERS (
495 x_result OUT NOCOPY VARCHAR2,
496 x_error_code OUT NOCOPY VARCHAR2,
497 x_error_message OUT NOCOPY VARCHAR2,
498 p_auction_header_id IN NUMBER,
499 x_has_price_tiers OUT NOCOPY VARCHAR2
500 ) ;
501
502 /*======================================================================
503 * PROCEDURE : HANDLE_CHANGE_PRICE_TIERS
504 * PARAMETERS: 1. x_result - return status.
505 * 2. x_error_code - error code
506 * 3. x_error_message - The actual error message
507 * 4. p_auction_header_id - The auction header id
508 * 5. p_delete_price_tiers -- Flag to indicate if price tiers to be removed or not
509 * COMMENT : This methods deletes all the lines in the DB table PON_AUCTION_SHIPMENTS_ALL,
510 * for the given auction header id, sets the modify falg for new round and amendments
511 * and sets the default price break settings.
512 *======================================================================*/
513
514 PROCEDURE HANDLE_CHANGE_PRICE_TIERS (
515 x_result OUT NOCOPY VARCHAR2,
516 x_error_code OUT NOCOPY VARCHAR2,
517 x_error_message OUT NOCOPY VARCHAR2,
518 p_auction_header_id IN NUMBER,
519 p_delete_price_tiers IN VARCHAR2
520 );
521
522
523 --Bug 6074506
524 /*======================================================================
525 * FUNCTION : GET_ABBR_DOC_TYPE_GRP_NAME PUBLIC
526 * PARAMETERS:
527 * p_doctype_id IN document type id of the auction
528 *
529 * COMMENT : returns the document froup name in English language
530 *
531 *======================================================================*/
532
533 FUNCTION GET_ABBR_DOC_TYPE_GRP_NAME (p_doctype_id IN NUMBER) RETURN VARCHAR2;
534
535 END PON_NEGOTIATION_HELPER_PVT;
[Home] [Help]