[Home] [Help]
PACKAGE BODY: APPS.PON_AUCTION_INTERFACE_PKG
Source
1 PACKAGE BODY PON_AUCTION_INTERFACE_PKG AS
2 /* $Header: PONAUCIB.pls 120.21 2007/08/22 16:20:38 tarkumar ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'PON_AUCTION_INTERFACE_PKG';
5 g_progress_payment_type pon_auction_headers_all.progress_payment_type%TYPE := 'NONE';
6
7 -- global variables added for header price break default project
8 g_price_break_type pon_auction_item_prices_all.price_break_type%type;
9 g_price_break_neg_flag pon_auction_item_prices_all.price_break_neg_flag%type;
10
11 /*
12 ===================
13 PROCEDURES
14 ===================
15 ========================================================================
16 PROCEDURE : Create_Draft_Negotiation PUBLIC
17 PARAMETERS:
18 P_DOCUMENT_TITLE IN Title of negotiation
19 P_DOCUMENT_TYPE IN 'BUYER_AUCTION' or 'REQUEST_FOR_QUOTE'
20 P_CONTRACT_TYPE IN 'STANDARD' or 'BLANKET'
21 P_ORIGINATION_CODE IN 'REQUISITION' or caller product name
22 P_ORG_ID IN Organization id of creator
23 P_BUYER_ID IN FND_USER_ID of creator
24 P_NEG_STYLE_ID IN negotiation style id
25 P_PO_STYLE_ID IN po style id
26 P_DOCUMENT_NUMBER OUT Created Document number
27 P_DOCUMENT_URL OUT Additional parameters to PON_AUC_EDIT_DRAFT_B
28 form function for editing draft
29 P_RESULT OUT One of (error, success)
30 P_ERROR_CODE OUT Internal code for error
31 P_ERROR_MESSAGE OUT Displayable error
32 COMMENT : Creates a draft auction
33 ======================================================================*/
34 PROCEDURE Create_Draft_Negotiation(
35 P_DOCUMENT_TITLE IN VARCHAR2,
36 P_DOCUMENT_TYPE IN VARCHAR2,
37 P_CONTRACT_TYPE IN VARCHAR2,
38 P_ORIGINATION_CODE IN VARCHAR2,
39 P_ORG_ID IN NUMBER,
40 P_BUYER_ID IN NUMBER,
41 P_NEG_STYLE_ID IN NUMBER,
42 P_PO_STYLE_ID IN NUMBER,
43 P_DOCUMENT_NUMBER OUT NOCOPY NUMBER,
44 P_DOCUMENT_URL OUT NOCOPY VARCHAR2,
45 P_RESULT OUT NOCOPY NUMBER,
46 P_ERROR_CODE OUT NOCOPY VARCHAR2,
47 P_ERROR_MESSAGE OUT NOCOPY VARCHAR2) IS
48
49 v_debug_status VARCHAR2(100);
50 v_doctype_id pon_auc_doctypes.doctype_id%TYPE;
51 v_transaction_type pon_auc_doctypes.transaction_type%TYPE;
52 v_site_id pon_auction_headers_all.trading_partner_id%TYPE;
53 v_site_name pon_auction_headers_all.trading_partner_name%TYPE;
54 v_multi_org fnd_product_groups.multi_org_flag%TYPE := 'Y';
55 v_price_tiers_indicator pon_auction_headers_all.price_tiers_indicator%type;
56
57 l_price_break_response pon_auction_headers_all.price_break_response%type;
58
59 l_style_name po_doc_style_headers.style_name%TYPE;
60 l_style_description po_doc_style_headers.style_description%TYPE;
61 l_style_type po_doc_style_headers.style_type%TYPE;
62 l_status po_doc_style_headers.status%TYPE;
63 l_advances_flag po_doc_style_headers.advances_flag%TYPE;
64 l_retainage_flag po_doc_style_headers.retainage_flag%TYPE;
65 l_price_breaks_flag po_doc_style_headers.price_breaks_flag%TYPE;
66 l_price_differentials_flag po_doc_style_headers.price_differentials_flag%TYPE;
67 l_progress_payment_flag po_doc_style_headers.progress_payment_flag%TYPE;
68 l_contract_financing_flag po_doc_style_headers.contract_financing_flag%TYPE;
69 l_line_type_allowed po_doc_style_headers.line_type_allowed%TYPE;
70
71 l_line_attribute_enabled_flag pon_negotiation_styles.line_attribute_enabled_flag%TYPE;
72 l_line_mas_enabled_flag pon_negotiation_styles.line_mas_enabled_flag%TYPE;
73 l_price_element_enabled_flag pon_negotiation_styles.price_element_enabled_flag%TYPE;
74 l_rfi_line_enabled_flag pon_negotiation_styles.rfi_line_enabled_flag%TYPE;
75 l_lot_enabled_flag pon_negotiation_styles.lot_enabled_flag%TYPE;
76 l_group_enabled_flag pon_negotiation_styles.group_enabled_flag%TYPE;
77 l_large_neg_enabled_flag pon_negotiation_styles.large_neg_enabled_flag%TYPE;
78 l_hdr_attribute_enabled_flag pon_negotiation_styles.hdr_attribute_enabled_flag%TYPE;
79 l_neg_team_enabled_flag pon_negotiation_styles.neg_team_enabled_flag%TYPE;
80 l_proxy_bidding_enabled_flag pon_negotiation_styles.proxy_bidding_enabled_flag%TYPE;
81 l_power_bidding_enabled_flag pon_negotiation_styles.power_bidding_enabled_flag%TYPE;
82 l_auto_extend_enabled_flag pon_negotiation_styles.auto_extend_enabled_flag%TYPE;
83 l_team_scoring_enabled_flag pon_negotiation_styles.team_scoring_enabled_flag%TYPE;
84 l_qty_price_tier_enabled_flag pon_negotiation_styles.qty_price_tiers_enabled_flag%TYPE;
85
86 BEGIN
87 IF (P_DOCUMENT_TYPE NOT IN ('BUYER_AUCTION', 'REQUEST_FOR_QUOTE')) THEN
88 P_RESULT := error;
89 P_ERROR_CODE := 'CREATE_DRAFT:INVALID_DOC_TYPE';
90 P_ERROR_MESSAGE := 'Invalid Document Type ' || P_DOCUMENT_TYPE;
91 RETURN;
92 END IF;
93
94 IF (P_CONTRACT_TYPE NOT IN ('BLANKET', 'STANDARD')) THEN
95 P_RESULT := error;
96 P_ERROR_CODE := 'CREATE_DRAFT:INVALID_CONTRACT_TYPE';
97 P_ERROR_MESSAGE := 'Invalid Contract Type ' || P_CONTRACT_TYPE;
98 RETURN;
99 END IF;
100
101 IF (P_ORIGINATION_CODE <> 'REQUISITION') THEN
102 P_RESULT := error;
103 P_ERROR_CODE := 'CREATE_DRAFT:UNKNOWN_ORIGINATION';
104 P_ERROR_MESSAGE := 'Invalid Origination Code ' || P_ORIGINATION_CODE;
105 RETURN;
106 END IF;
107
108 IF (P_BUYER_ID IS NULL) THEN
109 P_RESULT := error;
110 P_ERROR_CODE := 'CREATE_DRAFT:NULL_BUYER_ID';
111 P_ERROR_MESSAGE := 'Please specify a BUYER_ID';
112 RETURN;
113 END IF;
114
115 -- Is this multiorg?
116 v_debug_status := 'MULTIORG';
117 BEGIN
118 SELECT multi_org_flag
119 INTO v_multi_org
120 FROM fnd_product_groups;
121 EXCEPTION
122 WHEN no_data_found THEN
123 P_RESULT := error;
124 P_ERROR_CODE := 'CREATE_DRAFT:MULTI_ORG_QUERY';
125 fnd_message.set_name('PON', 'PON_AUC_PLSQL_ERR');
126 fnd_message.set_token('PACKAGE','pon_auction_interface_pkg');
127 fnd_message.set_token('PROCEDURE','Create_Draft_Negotiation');
128 fnd_message.set_token('ERROR','Multi-Org Query Failed [' || SQLERRM || ']');
129 fnd_message.retrieve(P_ERROR_MESSAGE);
130 RETURN;
131 END;
132
133 IF (P_ORG_ID IS NULL AND v_multi_org = 'Y') THEN
134 P_RESULT := error;
135 P_ERROR_CODE := 'CREATE_DRAFT:NULL_ORG_ID';
136 P_ERROR_MESSAGE := 'Please specify an ORG_ID';
137 RETURN;
138 END IF;
139
140 -- Get site ID for the enterprise
141 v_debug_status := 'SITE_ID';
142 pos_enterprise_util_pkg.get_enterprise_partyId(v_site_id,
143 P_ERROR_CODE,
144 P_ERROR_MESSAGE);
145 IF (P_ERROR_CODE IS NOT NULL OR v_site_id IS NULL) THEN
146 P_RESULT := error;
147 P_ERROR_CODE := 'CREATE_DRAFT:GET_ENTERPRISE_ID';
148 P_ERROR_MESSAGE := 'Could not get the Enterprise ID';
149 RETURN;
150 END IF;
151
152 -- Get site name for the enterprise
153 v_debug_status := 'SITE_NAME';
154 pos_enterprise_util_pkg.get_enterprise_party_name(v_site_name,
155 P_ERROR_CODE,
156 P_ERROR_MESSAGE);
157 IF (P_ERROR_CODE IS NOT NULL) THEN
158 P_RESULT := error;
159 P_ERROR_CODE := 'CREATE_DRAFT:GET_ENTERPRISE_NAME';
160 P_ERROR_MESSAGE := 'Could not get the Enterprise Name';
161 RETURN;
162 END IF;
163
164 IF ( p_po_style_id IS NOT NULL) THEN
165 PO_DOC_STYLE_GRP.GET_DOCUMENT_STYLE_SETTINGS(
166 p_api_version => 1.0
167 , p_style_id => p_po_style_id
168 , x_style_name => l_style_name
169 , x_style_description => l_style_description
170 , x_style_type => l_style_type
171 , x_status => l_status
172 , x_advances_flag => l_advances_flag
173 , x_retainage_flag => l_retainage_flag
174 , x_price_breaks_flag => l_price_breaks_flag
175 , x_price_differentials_flag => l_price_differentials_flag
176 , x_progress_payment_flag => l_progress_payment_flag
177 , x_contract_financing_flag => l_contract_financing_flag
178 , x_line_type_allowed => l_line_type_allowed);
179
180 IF l_progress_Payment_flag = 'Y' THEN
181 IF (P_DOCUMENT_TYPE <> 'REQUEST_FOR_QUOTE') THEN
182 P_RESULT := error;
183 P_ERROR_CODE := 'CREATE_DRAFT:INVALID_DOC_TYPE';
184 P_ERROR_MESSAGE := 'Invalid Document Type For Complex Work Style ' || P_CONTRACT_TYPE;
185 RETURN;
186 END IF;
187 IF (p_Contract_type <> 'STANDARD') then
188 P_RESULT := error;
189 P_ERROR_CODE := 'CREATE_DRAFT:INVALID_CONTRACT_TYPE';
190 P_ERROR_MESSAGE := 'Invalid Contract Type For Complex Work Style ' || P_CONTRACT_TYPE;
191 RETURN;
192 END IF;
193 --Set the following attribute on negotiation-
194 IF (l_contract_financing_flag = 'Y') THEN
195 g_progress_payment_type := 'FINANCE';
196 ELSE
197 g_progress_payment_type := 'ACTUAL';
198 END IF;
199
200 END IF;
201 END IF;
202 -- Get doctypeID
203 v_debug_status := 'DOCTYPE_ID';
204 SELECT doctype_id, transaction_type
205 INTO v_doctype_id, v_transaction_type
206 FROM pon_auc_doctypes
207 WHERE internal_name = P_DOCUMENT_TYPE;
208
209
210 -- price break header setting
211 PON_AUCTION_PKG.get_default_hdr_pb_settings (
212 v_doctype_id,
213 v_site_id,
214 l_price_break_response);
215
216 -- Insert a row into PON_AUCTION_HEADERS_ALL
217 -- See NegotiationDoc.java for the majority of defaulting - setDefaults()
218
219 -- Get all the style related columns from PON_NEGOTIATION_STYLES table for the style id.
220 -- Populate all the style related columns in PON_AUCTION_HEADERS_ALL table.
221 -- This procedure is invoked from two flows.
222 -- 1. HTML Autocreate : We select the syle id from the UI and the style id is passed as an arugment here.
223 -- 2. Forms based Autocreate : We will not have any option to select style from the forms and the style id wil be null here.
224 IF P_NEG_STYLE_ID IS NOT NULL THEN
225 BEGIN
226 SELECT
227 LINE_ATTRIBUTE_ENABLED_FLAG, LINE_MAS_ENABLED_FLAG, PRICE_ELEMENT_ENABLED_FLAG,
228 RFI_LINE_ENABLED_FLAG, LOT_ENABLED_FLAG, GROUP_ENABLED_FLAG, LARGE_NEG_ENABLED_FLAG,
229 HDR_ATTRIBUTE_ENABLED_FLAG, NEG_TEAM_ENABLED_FLAG, PROXY_BIDDING_ENABLED_FLAG,
230 POWER_BIDDING_ENABLED_FLAG, AUTO_EXTEND_ENABLED_FLAG, TEAM_SCORING_ENABLED_FLAG , QTY_PRICE_TIERS_ENABLED_FLAG
231 INTO
232 l_line_attribute_enabled_flag, l_line_mas_enabled_flag, l_price_element_enabled_flag,
233 l_rfi_line_enabled_flag, l_lot_enabled_flag, l_group_enabled_flag, l_large_neg_enabled_flag,
234 l_hdr_attribute_enabled_flag, l_neg_team_enabled_flag, l_proxy_bidding_enabled_flag,
235 l_power_bidding_enabled_flag, l_auto_extend_enabled_flag, l_team_scoring_enabled_flag, l_qty_price_tier_enabled_flag
236 FROM
237 PON_NEGOTIATION_STYLES WHERE STYLE_ID = P_NEG_STYLE_ID;
238 EXCEPTION
239 WHEN NO_DATA_FOUND THEN
240 l_line_attribute_enabled_flag := NULL;
241 l_line_mas_enabled_flag := NULL;
242 l_price_element_enabled_flag := NULL;
243 l_rfi_line_enabled_flag := NULL;
244 l_lot_enabled_flag := NULL;
245 l_group_enabled_flag := NULL;
246 l_large_neg_enabled_flag := NULL;
247 l_hdr_attribute_enabled_flag := NULL;
248 l_neg_team_enabled_flag := NULL;
249 l_proxy_bidding_enabled_flag := NULL;
250 l_power_bidding_enabled_flag := NULL;
251 l_auto_extend_enabled_flag := NULL;
252 l_team_scoring_enabled_flag := NULL;
253 l_qty_price_tier_enabled_flag := 'Y';
254 END;
255 END IF;
256
257 -- R12.1 Price tiers Project
258 -- Get Default price tiers indicator
259
260 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN --{
261 fnd_log.string(fnd_log.level_statement,
262 'pon.plsql.pon_auction_interface_pkg.Create_Draft_Negotiation',
263 'Calling the PON_AUCTION_PKG.GET_DEFAULT_TIERS_INDICATOR API to get the' ||
264 ' default price tiers indicator value.');
265 END IF;
266
267 v_debug_status := 'PRICE_TIERS_INDICATOR';
268 PON_AUCTION_PKG.GET_DEFAULT_TIERS_INDICATOR(
269 p_contract_type => P_CONTRACT_TYPE,
270 p_price_breaks_enabled => l_price_breaks_flag,
271 p_qty_price_tiers_enabled => l_qty_price_tier_enabled_flag,
272 p_doctype_id => v_doctype_id,
273 x_price_tiers_indicator => v_price_tiers_indicator);
274
275 v_debug_status := 'INSERT-PAH';
276 INSERT INTO PON_AUCTION_HEADERS_ALL (
277 AUCTION_HEADER_ID,
278 DOCUMENT_NUMBER,
279 AUCTION_HEADER_ID_ORIG_AMEND,
280 AUCTION_HEADER_ID_ORIG_ROUND,
281 AMENDMENT_NUMBER,
282 AUCTION_TITLE,
283 AUCTION_STATUS,
284 AWARD_STATUS,
285 AUCTION_TYPE,
286 CONTRACT_TYPE,
287 TRADING_PARTNER_NAME,
288 TRADING_PARTNER_NAME_UPPER,
289 TRADING_PARTNER_ID,
290 LANGUAGE_CODE,
291 BID_VISIBILITY_CODE,
292 ATTACHMENT_FLAG,
293 CREATION_DATE,
294 CREATED_BY,
295 LAST_UPDATE_DATE,
296 LAST_UPDATED_BY,
297 AUCTION_ORIGINATION_CODE,
298 DOCTYPE_ID,
299 ORG_ID,
300 BUYER_ID,
301 MANUAL_EDIT_FLAG,
302 SHARE_AWARD_DECISION,
303 APPROVAL_STATUS,
304 GLOBAL_AGREEMENT_FLAG,
305 ATTRIBUTE_LINE_NUMBER,
306 HAS_HDR_ATTR_FLAG,
307 HAS_ITEMS_FLAG,
308 STYLE_ID,
309 PO_STYLE_ID,
310 PRICE_BREAK_RESPONSE,
311 NUMBER_OF_LINES,
312 ADVANCE_NEGOTIABLE_FLAG,
313 RECOUPMENT_NEGOTIABLE_FLAG,
314 PROGRESS_PYMT_NEGOTIABLE_FLAG,
315 RETAINAGE_NEGOTIABLE_FLAG,
316 MAX_RETAINAGE_NEGOTIABLE_FLAG,
317 SUPPLIER_ENTERABLE_PYMT_FLAG,
318 PROGRESS_PAYMENT_TYPE,
319 LINE_ATTRIBUTE_ENABLED_FLAG,
320 LINE_MAS_ENABLED_FLAG,
321 PRICE_ELEMENT_ENABLED_FLAG,
322 RFI_LINE_ENABLED_FLAG,
323 LOT_ENABLED_FLAG,
324 GROUP_ENABLED_FLAG,
325 LARGE_NEG_ENABLED_FLAG,
326 HDR_ATTRIBUTE_ENABLED_FLAG,
327 NEG_TEAM_ENABLED_FLAG,
328 PROXY_BIDDING_ENABLED_FLAG,
329 POWER_BIDDING_ENABLED_FLAG,
330 AUTO_EXTEND_ENABLED_FLAG,
331 TEAM_SCORING_ENABLED_FLAG,
332 PRICE_TIERS_INDICATOR,
333 QTY_PRICE_TIERS_ENABLED_FLAG
334 ) VALUES (
335 pon_auction_headers_all_s.nextval, -- AUCTION_HEADER_ID
336 pon_auction_headers_all_s.currval, -- DOCUMENT_NUMBER
337 pon_auction_headers_all_s.currval, -- AUCTION_HEADER_ID_ORIG_AMEND,
338 pon_auction_headers_all_s.currval, -- AUCTION_HEADER_ID_ORIG_ROUND,
339 0, -- AMENDMENT_NUMBER
340 P_DOCUMENT_TITLE, -- AUCTION_TITLE
341 'DRAFT', -- AUCTION_STATUS
342 'NO', -- AWARD_STATUS
343 v_transaction_type, -- AUCTION_TYPE
344 P_CONTRACT_TYPE, -- CONTRACT_TYPE
345 v_site_name, -- TRADING_PARTNER_NAME
346 upper(v_site_name), -- TRADING_PARTNER_NAME_UPPER
347 v_site_id, -- TRADING_PARTNER_ID
348 userenv('LANG'), -- LANGUAGE_CODE
349 'OPEN_BIDDING', -- BID_VISIBILITY_CODE
350 'N', -- ATTACHMENT_FLAG
351 sysdate, -- CREATION_DATE
352 P_BUYER_ID, -- CREATED_BY
353 sysdate, -- LAST_UPDATE_DATE
354 P_BUYER_ID, -- LAST_UPDATED_BY
355 P_ORIGINATION_CODE, -- AUCTION_ORIGINATION_CODE
356 v_doctype_id, -- DOCTYPE_ID
357 P_ORG_ID, -- ORG_ID
358 P_BUYER_ID, -- BUYER_ID
359 'N', -- MANUAL_EDIT_FLAG
360 'N', -- SHARE_AWARD_DECISION
361 'NOT_REQUIRED', -- APPROVAL_STATUS
362 'N', -- GLOBAL_AGREEMENT_FLAG
363 -1, -- ATTRIBUTE_LINE_NUMBER
364 'N', -- HAS_HDR_ATTR_FLAG
365 'Y', -- HAS_ITEMS_FLAG
366 P_NEG_STYLE_ID, -- STYLE_ID
367 P_PO_STYLE_ID, -- PO_STYLE_ID
368 l_price_break_response, -- PRICE_BREAK_RESPONSE,
369 0, -- NUMBER_OF_LINES
370 'N', --ADVANCE_NEGOTIABLE_FLAG
371 'N', --RECOUPMENT_NEGOTIABLE_FLAG
372 'N', --PROGRESS_PYMT_NEGOTIABLE_FLAG
373 'N', --RETAINAGE_NEGOTIABLE_FLAG
374 'N', --MAX_RETAINAGE_NEGOTIABLE_FLAG
375 'N', --SUPPLIER_ENTERABLE_PYMT_FLAG
376 g_progress_payment_type, --PROGRESS_PAYMENT_TYPE
377 l_line_attribute_enabled_flag,
378 l_line_mas_enabled_flag,
379 l_price_element_enabled_flag,
380 l_rfi_line_enabled_flag,
381 l_lot_enabled_flag,
382 l_group_enabled_flag,
383 l_large_neg_enabled_flag,
384 l_hdr_attribute_enabled_flag,
385 l_neg_team_enabled_flag,
386 l_proxy_bidding_enabled_flag,
387 l_power_bidding_enabled_flag,
388 l_auto_extend_enabled_flag,
389 l_team_scoring_enabled_flag,
390 v_price_tiers_indicator,
391 l_qty_price_tier_enabled_flag
392 )
393 RETURNING auction_header_id INTO P_DOCUMENT_NUMBER;
394
395 -- price break line setting
396 PON_AUCTION_PKG.get_default_pb_settings (p_document_number,
397 g_price_break_type,
398 g_price_break_neg_flag);
399
400
401 -- Construct URL to Edit Document
402 v_debug_status := 'DOC_URL';
403 P_DOCUMENT_URL := '&' || 'auctionID=' || P_DOCUMENT_NUMBER;
404
405 P_RESULT := success;
406 P_ERROR_CODE := NULL;
407 P_ERROR_MESSAGE := NULL;
408 EXCEPTION
409 WHEN OTHERS THEN
410 fnd_message.set_name('PON', 'PON_AUC_PLSQL_ERR');
411 fnd_message.set_token('PACKAGE','pon_auction_interface_pkg');
412 fnd_message.set_token('PROCEDURE','Create_Draft_Negotiation');
413 fnd_message.set_token('ERROR',v_debug_status || ' [' || SQLERRM || ']');
414 APP_EXCEPTION.RAISE_EXCEPTION;
415 END;
416
417 /*======================================================================
418 PROCEDURE : Add_Negotiation_Line
419 PARAMETERS:
420 P_DOCUMENT_NUMBER IN Document number to add line
421 P_CONTRACT_TYPE IN 'STANDARD' or 'BLANKET'
422 P_ORIGINATION_CODE IN 'REQUISITION' or caller product name
423 P_ORG_ID IN Organization id of creator
424 P_BUYER_ID IN FND_USER_ID of creator
425 P_GROUPING_TYPE IN 'DEFAULT' or 'NONE' grouping
426 P_REQUISITION_HEADER_ID IN Requisition header
427 P_REQUISITION_NUMBER IN Requisition header formatted for display
428 P_REQUISITION_LINE_ID IN Requisition line
429 P_LINE_TYPE_ID IN Line type
430 P_CATEGORY_ID IN Line category
431 P_ITEM_DESCRIPTION IN Item Desription
432 P_ITEM_ID IN Item Id
433 P_ITEM_NUMBER IN Item Number formatted for display
434 P_ITEM_REVISION IN Item Revision
435 P_UOM_CODE IN UOM_CODE from MTL_UNITS_OF_MEASURE
436 P_QUANTITY IN Quantity
437 P_NEED_BY_DATE IN Item Need-By
438 P_SHIP_TO_LOCATION_ID IN Ship To
439 P_NOTE_TO_VENDOR IN Note to Supplier
440 P_PRICE IN Start price for line
441 P_JOB_ID IN Job_id for the services job
442 P_JOB_DETAILS IN job details if any
443 P_PO_AGREED_AMOUNT IN PO Agreed Amount
444 P_HAS_PRICE_DIFF_FLAG IN If the line has any price differentials flag
445 P_LINE_NUMBER OUT Line number to which the demand was added
446 P_RESULT OUT One of (error, success)
447 P_ERROR_CODE OUT Internal Error Code
448 P_ERROR_MESSAGE OUT Displayable error
449 COMMENT : Creates a line in a draft auction
450 ======================================================================*/
451 PROCEDURE Add_Negotiation_Line(
452 P_DOCUMENT_NUMBER IN NUMBER,
453 P_CONTRACT_TYPE IN VARCHAR2,
454 P_ORIGINATION_CODE IN VARCHAR2,
455 P_ORG_ID IN NUMBER,
456 P_BUYER_ID IN NUMBER,
457 P_GROUPING_TYPE IN VARCHAR2,
458 P_REQUISITION_HEADER_ID IN NUMBER,
459 P_REQUISITION_NUMBER IN VARCHAR2,
460 P_REQUISITION_LINE_ID IN NUMBER,
461 P_LINE_TYPE_ID IN NUMBER,
462 P_CATEGORY_ID IN NUMBER,
463 P_ITEM_DESCRIPTION IN VARCHAR2,
464 P_ITEM_ID IN NUMBER,
465 P_ITEM_NUMBER IN VARCHAR2,
466 P_ITEM_REVISION IN VARCHAR2,
467 P_UOM_CODE IN VARCHAR2,
468 P_QUANTITY IN NUMBER,
469 P_NEED_BY_DATE IN DATE,
470 P_SHIP_TO_LOCATION_ID IN NUMBER,
471 P_NOTE_TO_VENDOR IN VARCHAR2,
472 P_PRICE IN NUMBER,
473 P_JOB_ID IN NUMBER, -- ADDED FOR SERVICES PROCUREMENT PROJECT
474 P_JOB_DETAILS IN VARCHAR2,-- ADDED FOR SERVICES PROCUREMENT PROJECT
475 P_PO_AGREED_AMOUNT IN NUMBER,-- ADDED FOR SERVICES PROCUREMENT PROJECT
476 P_HAS_PRICE_DIFF_FLAG IN VARCHAR2,-- ADDED FOR SERVICES PROCUREMENT PROJECT
477 P_LINE_NUMBER OUT NOCOPY NUMBER,
478 P_RESULT OUT NOCOPY NUMBER,
479 P_ERROR_CODE OUT NOCOPY VARCHAR2,
480 P_ERROR_MESSAGE OUT NOCOPY VARCHAR2) IS
481
482 v_debug_status VARCHAR2(100);
483 v_was_grouped VARCHAR2(1);
484 v_header_attach_count NUMBER;
485 v_item_attach_count NUMBER;
486 v_site_id pon_auction_headers_all.trading_partner_id%TYPE :=NULL;
487 v_org_id pon_auction_headers_all.org_id%TYPE;
488 v_seq_num fnd_attached_documents.seq_num%TYPE;
489 v_price pon_auction_item_prices_all.current_price%TYPE;
490 v_category_name pon_auction_item_prices_all.category_name%TYPE;
491 v_ip_category_id pon_auction_item_prices_all.ip_category_id%TYPE;
492 v_quantity pon_auction_item_prices_all.quantity%TYPE;
493 v_uom_code pon_auction_item_prices_all.uom_code%TYPE;
494 v_has_attachments pon_auction_item_prices_all.attachment_flag%TYPE:= 'N';
495 v_multi_org fnd_product_groups.multi_org_flag%TYPE := 'Y';
496 v_order_type_lookup_code po_line_types_b.order_type_lookup_code%TYPE;
497 v_purchase_basis po_line_types_b.purchase_basis%TYPE;
498 v_att_category_id fnd_document_categories.category_id%TYPE;
499 v_service_based_line VARCHAR2(1);
500 v_from_ip_catalog VARCHAR2(1);
501
502 v_blanket_po_header_id po_requisition_lines_all.blanket_po_header_id%TYPE;
503 v_blanket_po_line_num po_requisition_lines_all.blanket_po_line_num%TYPE;
504
505 BEGIN
506 IF (P_DOCUMENT_NUMBER IS NULL) THEN
507 P_RESULT := error;
508 P_ERROR_CODE := 'ADD_NEG_LINE:DOCUMENT_NUMBER';
509 P_ERROR_MESSAGE := 'Please provide a DOCUMENT_NUMBER';
510 RETURN;
511 END IF;
512
513 IF (P_CONTRACT_TYPE NOT IN ('BLANKET', 'STANDARD')) THEN
514 P_RESULT := error;
515 P_ERROR_CODE := 'ADD_NEG_LINE:INVALID_CONTRACT_TYPE';
516 P_ERROR_MESSAGE := 'Invalid Contract Type ' || P_CONTRACT_TYPE;
517 RETURN;
518 END IF;
519
520 IF (P_ORIGINATION_CODE <> 'REQUISITION') THEN
521 P_RESULT := error;
522 P_ERROR_CODE := 'ADD_NEG_LINE:UNKNOWN_ORIGINATION';
523 P_ERROR_MESSAGE := 'Invalid Origination Code ' || P_ORIGINATION_CODE;
524 RETURN;
525 END IF;
526
527 IF (P_BUYER_ID IS NULL) THEN
528 P_RESULT := error;
529 P_ERROR_CODE := 'ADD_NEG_LINE:NULL_BUYER_ID';
530 P_ERROR_MESSAGE := 'Please specify a BUYER_ID';
531 RETURN;
532 END IF;
533
534 IF (P_CATEGORY_ID IS NULL) THEN
535 P_RESULT := error;
536 P_ERROR_CODE := 'ADD_NEG_LINE:NULL_CATEGORY_ID';
537 P_ERROR_MESSAGE := 'Please specify a CATEGORY_ID';
538 RETURN;
539 END IF;
540
541 -- Amount based line?
542 v_debug_status := 'ORDER_TYPE_LOOKUP';
543 BEGIN
544 SELECT order_type_lookup_code
545 INTO v_order_type_lookup_code
546 FROM po_line_types_b
547 WHERE P_LINE_TYPE_ID = line_type_id;
548 EXCEPTION
549 WHEN no_data_found THEN
550 P_RESULT := error;
551 P_ERROR_CODE := 'ADD_NEG_LINE:ORDER_TYPE_LOOKUP';
552 P_ERROR_MESSAGE := 'An order_type_lookup_code could not be found for line_type_id ' || P_LINE_TYPE_ID;
553 RETURN;
554 END;
555
556 -- Get the purchase basis for this line type
557 BEGIN
558 SELECT purchase_basis
559 INTO v_purchase_basis
560 FROM po_line_types_b
561 WHERE P_LINE_TYPE_ID = line_type_id;
562 EXCEPTION
563 WHEN no_data_found THEN
564 P_RESULT := error;
565 P_ERROR_CODE := 'ADD_NEG_LINE:PURCHASE_BASIS_LOOKUP';
566 P_ERROR_MESSAGE := 'A purchase basis could not be found for line_type_id ' || P_LINE_TYPE_ID;
567 RETURN;
568 END;
569
570 IF ((P_UOM_CODE IS NULL) AND (v_order_type_lookup_code <> 'FIXED PRICE')) THEN
571 P_RESULT := error;
572 P_ERROR_CODE := 'ADD_NEG_LINE:NULL_UOM_CODE';
573 P_ERROR_MESSAGE := 'Please specify a UOM_CODE';
574 RETURN;
575 END IF;
576
577 IF (P_QUANTITY IS NULL AND ((v_purchase_basis <> 'SERVICES') AND (v_purchase_basis <> 'TEMP LABOR'))) THEN
578 P_RESULT := error;
579 P_ERROR_CODE := 'ADD_NEG_LINE:NULL_QUANTITY';
580 P_ERROR_MESSAGE := 'Please specify a QUANTITY';
581 RETURN;
582 END IF;
583
584 --check if only valid line types for complex work
585 IF g_progress_payment_type in ('FINANCE', 'ACTUAL') THEN
586 IF NOT ((V_PURCHASE_BASIS = 'GOODS' AND V_ORDER_TYPE_LOOKUP_CODE = 'QUANTITY') OR
587 (V_PURCHASE_BASIS = 'SERVICES' AND V_ORDER_TYPE_LOOKUP_CODE = 'FIXED PRICE')
588 ) THEN
589 P_RESULT := error;
590 P_ERROR_CODE := 'ADD_NEG_LINE:INVALID_LINE_TYPE';
591 P_ERROR_MESSAGE := 'The line_type_id is invalid for Complex work Style' || P_LINE_TYPE_ID;
592 RETURN;
593 END IF;
594
595 END IF;
596
597
598 IF (P_SHIP_TO_LOCATION_ID IS NULL) THEN
599 P_RESULT := error;
600 P_ERROR_CODE := 'ADD_NEG_LINE:NULL_SHIP_TO';
601 P_ERROR_MESSAGE := 'Please specify a SHIP_TO';
602 RETURN;
603 END IF;
604
605 -- Is this multiorg?
606 v_debug_status := 'MULTIORG';
607 BEGIN
608 SELECT multi_org_flag
609 INTO v_multi_org
610 FROM fnd_product_groups;
611 EXCEPTION
612 WHEN no_data_found THEN
613 P_RESULT := error;
614 P_ERROR_CODE := 'ADD_NEG_LINE:MULTI_ORG_QUERY';
615 fnd_message.set_name('PON', 'PON_AUC_PLSQL_ERR');
616 fnd_message.set_token('PACKAGE','pon_auction_interface_pkg');
617 fnd_message.set_token('PROCEDURE','Add_Negotiation_Line');
618 fnd_message.set_token('ERROR','Multi-Org Query Failed [' || SQLERRM || ']');
619 fnd_message.retrieve(P_ERROR_MESSAGE);
620 RETURN;
621 END;
622
623 IF (P_ORG_ID IS NULL AND v_multi_org = 'Y') THEN
624 P_RESULT := error;
625 P_ERROR_CODE := 'ADD_NEG_LINE:NULL_ORG_ID';
626 P_ERROR_MESSAGE := 'Please specify an ORG_ID';
627 RETURN;
628 END IF;
629
630 -- Does P_ORG_ID match that of the auction header?
631 v_debug_status := 'ORG_ID_MATCH';
632 BEGIN
633 SELECT org_id
634 INTO v_org_id
635 FROM pon_auction_headers_all
636 WHERE auction_header_id = P_DOCUMENT_NUMBER
637 AND nvl(org_id, -9999) = nvl(P_ORG_ID, -9999);
638 EXCEPTION
639 WHEN no_data_found THEN
640 P_RESULT := error;
641 P_ERROR_CODE := 'ADD_NEG_LINE:ORG_ID_CONFLICT';
642 P_ERROR_MESSAGE := 'You cannot add lines to another organization''s Negotiation';
643 RETURN;
644 END;
645
646 -- get category id for VENDOR attachments
647 v_debug_status := 'ATTACHMENT_CATEGORY_ID';
648 BEGIN
649 SELECT category_id
650 INTO v_att_category_id
651 FROM fnd_document_categories
652 WHERE upper(name) = 'VENDOR';
653 EXCEPTION
654 WHEN no_data_found THEN
655 P_RESULT := error;
656 P_ERROR_CODE := 'ADD_NEG_LINE:ATTACHMENT_CATEGORY_ID';
657 P_ERROR_MESSAGE := 'The attachment category id for name=VENDOR could not be found';
658 RETURN;
659 END;
660
661 -- Does this requisition line have any attachments?
662 SELECT count(*)
663 INTO v_header_attach_count
664 FROM fnd_attached_documents ad, fnd_documents doc
665 WHERE ad.entity_name = 'REQ_HEADERS'
666 AND ad.pk1_value = to_char(p_requisition_header_id)
667 AND ad.document_id = doc.document_id
668 AND doc.category_id = v_att_category_id;
669
670 SELECT count(*)
671 INTO v_item_attach_count
672 FROM fnd_attached_documents ad, fnd_documents doc
673 WHERE ad.entity_name = 'REQ_LINES'
674 AND ad.pk1_value = to_char(p_requisition_line_id)
675 AND ad.document_id = doc.document_id
676 AND doc.category_id = v_att_category_id;
677
678 IF (v_header_attach_count > 0 OR v_item_attach_count > 0) THEN
679 v_has_attachments := 'Y';
680 END IF;
681
682 -- Check to see if this is a services based line type
683 -- ie one of - temp labor or fixed price services
684 -- if it is then donot group the lines, since you cannot
685 -- group services based line types. Even if the p_grouping_type
686 -- is set to 'DEFAULT', overwrite it
687 --Complex work- Requisitions should not be grouped if complex work neg
688 v_service_based_line := 'N';
689 IF ((v_order_type_lookup_code = 'FIXED PRICE') OR (v_purchase_basis = 'TEMP LABOR')
690 OR(g_progress_payment_type in('FINANCE', 'ACTUAL'))) THEN
691 v_service_based_line := 'Y';
692 END IF;
693
694 -- check to see if this line type is TEMP LABOR RATE or fixed price temp labor based
695 -- in which case we want to update the global agreement flag at the
696 -- header level, since temp labor line types can exist only on
697 -- global agreements
698 IF (v_purchase_basis = 'TEMP LABOR' AND p_contract_type = 'BLANKET' ) THEN
699 UPDATE pon_auction_headers_all
700 SET global_agreement_flag = 'Y'
701 WHERE auction_header_id = p_document_number;
702 END IF;
703
704
705 -- Get the shopping category (ip_category_id) when creating a blanket line
706 -- Two cases:
707 -- 1) If the requisition is tied to a catalog, get the shopping category from the
708 -- category line
709 -- 2) Else use the po category to ip category mappings
710
711 v_ip_category_id := null;
712 v_from_ip_catalog := 'N';
713
714 IF (p_contract_type = 'BLANKET') THEN
715
716 SELECT blanket_po_header_id, blanket_po_line_num
717 INTO v_blanket_po_header_id, v_blanket_po_line_num
718 FROM po_requisition_lines_all
719 WHERE requisition_header_id = p_requisition_header_id and
720 requisition_line_id = p_requisition_line_id;
721
722 IF (v_blanket_po_header_id is not null and v_blanket_po_line_num is not null) THEN
723
724 -- get the ip category from the catalog
725
726 v_from_ip_catalog := 'Y';
727
728 SELECT ip_category_id
729 INTO v_ip_category_id
730 FROM po_lines_all
731 WHERE po_header_id = v_blanket_po_header_id and
732 line_num = v_blanket_po_line_num;
733
734 ELSE
735
736 -- get the ip ccategory from the category mappings
737
738 v_ip_category_id := PON_AUCTION_PKG.get_mapped_ip_category(p_category_id);
739
740 if (v_ip_category_id = -2) then
741 v_ip_category_id := null;
742 end if;
743
744 END IF;
745
746
747 END IF;
748
749
750
751 -- Insert or Update row in PON_AUCTION_ITEM_PRICES
752 P_LINE_NUMBER := NULL;
753 IF (P_GROUPING_TYPE = 'DEFAULT' AND v_service_based_line <> 'Y') THEN
754
755 v_debug_status := 'GROUPING_QUERY';
756
757 IF (P_CONTRACT_TYPE = 'STANDARD') THEN
758
759 SELECT max(line_number)
760 INTO P_LINE_NUMBER
761 FROM pon_auction_item_prices_all
762 WHERE auction_header_id = P_DOCUMENT_NUMBER
763 AND line_type_id = P_LINE_TYPE_ID
764 AND nvl(p_item_id, -1) = nvl(item_id, -1)
765 AND nvl(p_item_revision, -1) = nvl(item_revision, -1)
766 AND nvl(p_item_description, 'NULL') = nvl(item_description, 'NULL')
767 AND p_category_id = category_id
768 AND p_ship_to_location_id = ship_to_location_id
769 -- Ignore UOM code for amount based lines
770 AND decode(v_order_type_lookup_code, 'AMOUNT', '1', p_uom_code) = decode(v_order_type_lookup_code, 'AMOUNT', '1', uom_code);
771
772 ELSE
773
774 SELECT max(paip.line_number)
775 INTO P_LINE_NUMBER
776 FROM pon_auction_item_prices_all paip,
777 pon_backing_requisitions pbr,
778 po_requisition_lines_all prl
779 WHERE paip.auction_header_id = P_DOCUMENT_NUMBER
780 AND paip.line_type_id = P_LINE_TYPE_ID
781 AND nvl(p_item_id, -1) = nvl(paip.item_id, -1)
782 AND nvl(p_item_revision, -1) = nvl(paip.item_revision, -1)
783 AND nvl(p_item_description, 'NULL') = nvl(paip.item_description, 'NULL')
784 AND p_category_id = paip.category_id
785 AND nvl(v_ip_category_id, -1) = nvl(paip.ip_category_id, -1)
786 -- Ignore UOM code for amount based lines
787 AND decode(v_order_type_lookup_code, 'AMOUNT', '1', p_uom_code) = decode(v_order_type_lookup_code, 'AMOUNT', '1', paip.uom_code)
788 AND paip.auction_header_id = pbr.auction_header_id
789 AND paip.line_number = pbr.line_number
790 AND pbr.requisition_header_id = prl.requisition_header_id
791 AND pbr.requisition_line_id = prl.requisition_line_id
792 AND nvl(prl.blanket_po_header_id, -1) = nvl(v_blanket_po_header_id, -1)
793 AND nvl(prl.blanket_po_line_num, -1) = nvl(v_blanket_po_line_num, -1);
794
795 END IF;
796
797 END IF;
798
799 -- Update if we're grouping
800 IF (P_LINE_NUMBER IS NOT NULL) THEN
801 v_was_grouped := 'Y';
802
803 IF (v_order_type_lookup_code = 'AMOUNT') THEN
804 -- Update row in PON_AUCTION_ITEM_PRICES
805 v_debug_status := 'UPDATE_PAIP_1';
806 UPDATE pon_auction_item_prices_all
807 SET requisition_number = 'MULTIPLE',
808 -- problem: least() and greater() return NULL if any argument is NULL
809 -- need_by_start := NULL if P_NEED_BY_DATE and need_by_start == NULL
810 need_by_start_date = decode(P_CONTRACT_TYPE, 'BLANKET', to_date(NULL), least(nvl(need_by_start_date,P_NEED_BY_DATE), nvl(P_NEED_BY_DATE,need_by_start_date))),
811 -- if P_NEED_BY_DATE is NULL, keep existing need_by_date
812 need_by_date = decode(P_CONTRACT_TYPE, 'BLANKET', to_date(NULL), greatest(nvl(need_by_date,P_NEED_BY_DATE), nvl(P_NEED_BY_DATE,need_by_date))),
813 attachment_flag = decode(attachment_flag, 'Y', 'Y', v_has_attachments),
814 -- if P_PRICE is NULL, keep existing current_price
815 current_price = nvl(current_price,0) + P_QUANTITY
816 WHERE auction_header_id = P_DOCUMENT_NUMBER
817 AND line_number = P_LINE_NUMBER;
818 ELSE
819 -- Update row in PON_AUCTION_ITEM_PRICES
820 v_debug_status := 'UPDATE_PAIP_2';
821 UPDATE pon_auction_item_prices_all
822 SET quantity = quantity + P_QUANTITY,
823 residual_quantity = residual_quantity + P_QUANTITY,
824 requisition_number = 'MULTIPLE',
825 -- problem: least() and greater() return NULL if any argument is NULL
826 -- set need_by_start to NULL if P_NEED_BY_DATE and need_by_start == NULL
827 need_by_start_date = decode(P_CONTRACT_TYPE, 'BLANKET', to_date(NULL), least(nvl(need_by_start_date,P_NEED_BY_DATE), nvl(P_NEED_BY_DATE,need_by_start_date))),
828 -- if P_NEED_BY_DATE is NULL, keep existing need_by_date
829 need_by_date = decode(P_CONTRACT_TYPE, 'BLANKET', to_date(NULL), greatest(nvl(need_by_date,P_NEED_BY_DATE), nvl(P_NEED_BY_DATE,need_by_date))),
830 attachment_flag = decode(attachment_flag, 'Y', 'Y', v_has_attachments),
831 -- if P_PRICE is NULL, keep existing current_price
832 current_price = decode(P_PRICE, 0, to_number(NULL), decode(current_price, NULL, NULL, least(current_price, P_PRICE)))
833 WHERE auction_header_id = P_DOCUMENT_NUMBER
834 AND line_number = P_LINE_NUMBER;
835 END IF;
836 ELSE
837 v_was_grouped := 'N';
838
839 IF (v_order_type_lookup_code = 'AMOUNT') THEN
840 v_price := P_QUANTITY;
841 v_quantity := 1;
842
843 -- Find the UOM Code
844 -- Get site ID for the enterprise
845 v_debug_status := 'SITE_ID';
846 pos_enterprise_util_pkg.get_enterprise_partyId(v_site_id,
847 P_ERROR_CODE,
848 P_ERROR_MESSAGE);
849 IF (v_site_id IS NULL OR P_ERROR_CODE IS NOT NULL) THEN
850 P_RESULT := error;
851 P_ERROR_CODE := 'ADD_NEG_LINE:GET_ENTERPRISE_ID';
852 P_ERROR_MESSAGE := 'Could not get the Enterprise ID';
853 RETURN;
854 END IF;
855
856 v_debug_status := 'UOM_SELECT';
857 BEGIN
858 SELECT preference_value
859 INTO v_uom_code
860 FROM pon_party_preferences
861 WHERE preference_name = 'AMOUNT_BASED_UOM'
862 AND app_short_name = 'PON'
863 AND party_id = v_site_id;
864 EXCEPTION
865 WHEN others THEN
866 -- Don't fail! Use 'Each' and let the user change it later
867 v_debug_status := 'UOM_SELECT_EACH';
868 SELECT uom_code
869 INTO v_uom_code
870 FROM mtl_units_of_measure
871 WHERE unit_of_measure = 'Each';
872 END;
873 ELSE
874 -- bug 4677078 set price to null if requisition price is 0
875 IF (P_PRICE = 0) THEN
876 v_price := to_number(NULL);
877 ELSE
878 v_price := P_PRICE;
879 END IF;
880
881 -- if its services line type you donot want to carry over the quantity column
882 -- For 11i10+ we WILL carry over the quantity as is done by iP for the
883 -- rate based line type
884 IF (v_order_type_lookup_code = 'FIXED PRICE') THEN
885 v_quantity := NULL;
886 ELSE
887 v_quantity := P_QUANTITY;
888 END IF;
889 v_uom_code := P_UOM_CODE;
890 END IF;
891
892 -- Get category name from category_id
893 v_debug_status := 'GET_CATEGORY_NAME';
894 BEGIN
895 SELECT FND_FLEX_EXT.get_segs('INV', 'MCAT', STRUCTURE_ID, CATEGORY_ID)
896 INTO v_category_name
897 FROM mtl_categories_kfv
898 WHERE category_id = P_CATEGORY_ID;
899 EXCEPTION
900 WHEN no_data_found THEN
901 -- Don't fail! The user can assign this later
902 v_category_name := NULL;
903 END;
904
905 -- Insert row in PON_AUCTION_ITEM_PRICES
906 v_debug_status := 'GET_LINE_NUMBER';
907
908 -- The value that is calculated here for p_line_number is used
909 -- for setting the number_of_lines and last_line_number fields
910 -- also.
911 SELECT nvl(max(line_number),0)+1
912 INTO P_LINE_NUMBER
913 FROM pon_auction_item_prices_all
914 WHERE auction_header_id = P_DOCUMENT_NUMBER;
915
916 v_debug_status := 'INSERT_PAIP';
917 INSERT INTO PON_AUCTION_ITEM_PRICES_ALL (
918 AUCTION_HEADER_ID,
919 LINE_NUMBER,
920 DISP_LINE_NUMBER,
921 LAST_AMENDMENT_UPDATE,
922 MODIFIED_DATE,
923 ITEM_DESCRIPTION,
924 CATEGORY_ID,
925 CATEGORY_NAME,
926 IP_CATEGORY_ID,
927 UOM_CODE,
928 QUANTITY,
929 RESIDUAL_QUANTITY,
930 NEED_BY_START_DATE,
931 NEED_BY_DATE,
932 SHIP_TO_LOCATION_ID,
933 NUMBER_OF_BIDS,
934 CREATION_DATE,
935 CREATED_BY,
936 LAST_UPDATE_DATE,
937 LAST_UPDATED_BY,
938 CURRENT_PRICE,
939 NOTE_TO_BIDDERS,
940 ATTACHMENT_FLAG,
941 HAS_ATTRIBUTES_FLAG,
942 ORG_ID,
943 LINE_TYPE_ID,
944 ORDER_TYPE_LOOKUP_CODE,
945 PURCHASE_BASIS,
946 ITEM_ID,
947 ITEM_NUMBER,
948 ITEM_REVISION,
949 LINE_ORIGINATION_CODE,
950 REQUISITION_NUMBER,
951 PRICE_BREAK_TYPE,
952 PRICE_BREAK_NEG_FLAG,
953 HAS_SHIPMENTS_FLAG,
954 HAS_QUANTITY_TIERS,
955 PRICE_DISABLED_FLAG,
956 quantity_disabled_flag,
957 JOB_ID,
958 ADDITIONAL_JOB_DETAILS,
959 PO_AGREED_AMOUNT,
960 HAS_PRICE_DIFFERENTIALS_FLAG,
961 PRICE_DIFF_SHIPMENT_NUMBER,
962 DIFFERENTIAL_RESPONSE_TYPE,
963 GROUP_TYPE,
964 DOCUMENT_DISP_LINE_NUMBER,
965 SUB_LINE_SEQUENCE_NUMBER,
966 HAS_PAYMENTS_FLAG,
967 PROGRESS_PYMT_RATE_PERCENT
968 ) VALUES (
969 P_DOCUMENT_NUMBER, -- AUCTION_HEADER_ID
970 P_LINE_NUMBER,
971 P_LINE_NUMBER, -- DISP_LINE_NUMBER
972 0, -- LAST_AMENDMENT_UPDATE
973 sysdate, -- MODIFIED_DATE
974 P_ITEM_DESCRIPTION,
975 P_CATEGORY_ID,
976 v_category_name, -- CATEGORY_NAME
977 v_ip_category_id, -- IP_CATEGORY_ID
978 v_uom_code, -- UOM_CODE
979 v_quantity, -- QUANTITY
980 v_quantity, -- RESIDUAL_QUANTITY,
981 decode(P_CONTRACT_TYPE, 'BLANKET', to_date(NULL), P_NEED_BY_DATE), -- NEED_BY_START_DATE
982 decode(P_CONTRACT_TYPE, 'BLANKET', to_date(NULL), P_NEED_BY_DATE), -- NEED_BY_DATE
983 decode(P_CONTRACT_TYPE, 'BLANKET', NULL, p_ship_to_location_id), -- SHIP_TO_LOCATION_ID
984 0, -- NUMBER_OF_BIDS
985 sysdate, -- CREATION_DATE
986 P_BUYER_ID, -- CREATED_BY
987 sysdate, -- LAST_UPDATE_DATE
988 P_BUYER_ID, -- LAST_UPDATED_BY
989 v_price, -- CURRENT_PRICE
990 P_NOTE_TO_VENDOR, -- NOTE_TO_BIDDERS
991 v_has_attachments, -- ATTACHMENT_FLAG
992 'N', -- HAS_ATTRIBUTE_FLAG
993 P_ORG_ID, -- ORG_ID
994 P_LINE_TYPE_ID,
995 v_order_type_lookup_code, -- ORDER_TYPE_LOOKUP_CODE
996 v_purchase_basis, -- Purchase Basis
997 P_ITEM_ID,
998 P_ITEM_NUMBER,
999 P_ITEM_REVISION,
1000 P_ORIGINATION_CODE, -- LINE_ORIGINATION_CODE
1001 P_REQUISITION_NUMBER,
1002 decode(v_order_type_lookup_code,'AMOUNT', 'NONE', 'FIXED PRICE', 'NONE', g_price_break_type), -- PRICE_BREAK_TYPE
1003 g_price_break_neg_flag, -- PRICE_BREAK_NEG_FLAG
1004 'N', -- HAS_SHIPMENTS_FLAG
1005 'N', -- HAS_QUANTITY_TIERS
1006 'N', -- PRICE_DISABLED_FLAG
1007 'N', -- QUANTITY_DISABLED_FLAG
1008 P_JOB_ID, -- JOB ID - ADDED FOR SERVICES PROCUREMENT PROJECT
1009 P_JOB_DETAILS, -- ADDITIONAL JOB DETAILS -ADDED FOR SERVICES PROCUREMENT PROJECT
1010 P_PO_AGREED_AMOUNT, -- PO AGREED AMOUNT -ADDED FOR SERVICES PROCUREMENT PROJECT
1011 p_has_price_diff_flag, -- LINE HAS PRICE DIFFERENTIALS ADDED FOR SERVICES PROCUREMENT PROJECT-
1012 -1, --price diff shipment number is -1 by default
1013 Decode(p_has_price_diff_flag,'Y','OPTIONAL', NULL),
1014 'LINE', -- GROUP_TYPE
1015 P_LINE_NUMBER, -- DOCUMENT_DISP_LINE_NUMBER
1016 P_LINE_NUMBER, -- SUB_LINE_SEQUENCE_NUMBER
1017 'N', --has_payments_flag
1018 decode(g_progress_payment_type, 'FINANCE', 100,null) --PROGRESS_PYMT_RATE_PERCENT
1019 );
1020
1021 -- We already do an nvl(max(line_number),0)+1 to find the line number of
1022 -- the newly added line. So this value can be used for the fields
1023 -- number_of_lines and last_line_number.
1024 -- The number of lines will be equal to the line number of the new line added
1025 -- The last line number will be equal to the line number of the newly added line
1026 UPDATE PON_AUCTION_HEADERS_ALL
1027 SET
1028 NUMBER_OF_LINES = P_LINE_NUMBER,
1029 LAST_LINE_NUMBER = P_LINE_NUMBER
1030 WHERE
1031 AUCTION_HEADER_ID = P_DOCUMENT_NUMBER;
1032
1033 END IF;
1034
1035 -- Insert row into PON_BACKING_REQUISITIONS
1036 v_debug_status := 'INSERT_PBR_YUMMY';
1037 INSERT INTO PON_BACKING_REQUISITIONS (
1038 AUCTION_HEADER_ID,
1039 LINE_NUMBER,
1040 REQUISITION_HEADER_ID,
1041 REQUISITION_LINE_ID,
1042 REQUISITION_QUANTITY,
1043 REQUISITION_NUMBER
1044 ) VALUES (
1045 P_DOCUMENT_NUMBER,
1046 P_LINE_NUMBER,
1047 P_REQUISITION_HEADER_ID,
1048 P_REQUISITION_LINE_ID,
1049 P_QUANTITY,
1050 P_REQUISITION_NUMBER
1051 );
1052
1053 -- Copy attachments from requisition (header, item and line)
1054 IF (v_has_attachments = 'Y') THEN
1055 -- Copy requisition header attachments
1056 v_debug_status := 'INSERT_HEADER_ATTACHMENT';
1057 fnd_attached_documents2_pkg.COPY_ATTACHMENTS (
1058 'REQ_HEADERS', --from_entity_name
1059 to_char(p_requisition_header_id), -- from_pk1_value
1060 NULL, -- from_pk2_value
1061 NULL, -- from_pk3_value
1062 NULL, -- from_pk4_value
1063 NULL, -- from_pk5_value
1064 'PON_AUCTION_ITEM_PRICES_ALL', -- entity_name
1065 to_char(P_DOCUMENT_NUMBER), -- PK1_VALUE
1066 to_char(P_LINE_NUMBER), -- PK2_VALUE
1067 NULL, -- PK3_VALUE
1068 NULL, -- PK4_VALUE
1069 NULL, -- PK5_VALUE
1070 p_buyer_id, -- CREATED_BY
1071 p_buyer_id, -- LAST_UPDATE_LOGIN
1072 NULL, -- program_application_id
1073 NULL, -- program_id
1074 NULL, -- request_id
1075 NULL, -- automatically_added_flag
1076 33, -- from_category_id (Vendor)
1077 33); -- to_category_id (Vendor)
1078
1079 -- Copy requisition line attachments
1080 v_debug_status := 'INSERT_LINE_ATTACHMENT';
1081 fnd_attached_documents2_pkg.COPY_ATTACHMENTS (
1082 'REQ_LINES', --from_entity_name
1083 to_char(p_requisition_line_id), -- from_pk1_value
1084 NULL, -- from_pk2_value
1085 NULL, -- from_pk3_value
1086 NULL, -- from_pk4_value
1087 NULL, -- from_pk5_value
1088 'PON_AUCTION_ITEM_PRICES_ALL', -- entity_name
1089 to_char(P_DOCUMENT_NUMBER), -- PK1_VALUE
1090 to_char(P_LINE_NUMBER), -- PK2_VALUE
1091 NULL, -- PK3_VALUE
1092 NULL, -- PK4_VALUE
1093 NULL, -- PK5_VALUE
1094 p_buyer_id, -- CREATED_BY
1095 p_buyer_id, -- LAST_UPDATE_LOGIN
1096 NULL, -- program_application_id
1097 NULL, -- program_id
1098 NULL, -- request_id
1099 NULL, -- automatically_added_flag
1100 33, -- from_category_id (Vendor)
1101 33); -- to_category_id (Vendor)
1102
1103 END IF; -- v_has_attachments
1104
1105 P_RESULT := success;
1106 P_ERROR_CODE := NULL;
1107 P_ERROR_MESSAGE := NULL;
1108 EXCEPTION
1109 WHEN OTHERS THEN
1110 fnd_message.set_name('PON', 'PON_AUC_PLSQL_ERR');
1111 fnd_message.set_token('PACKAGE','pon_auction_interface_pkg');
1112 fnd_message.set_token('PROCEDURE','Add_Negotiation_Line');
1113 fnd_message.set_token('ERROR',v_debug_status || ' [' || SQLERRM || ']');
1114 APP_EXCEPTION.RAISE_EXCEPTION;
1115 END;
1116
1117 /*============ADDED FOR UNIFIED CATALOG PROJECT=====================
1118 PROCEDURE : Add_Catalog_Descriptors
1119 PARAMETERS:
1120 P_API_VERSION IN NUMBER
1121 P_DOCUMENT_NUMBER IN NUMBER
1122 X_RETURN_STATUS OUT NOCOPY VARCHAR2
1123 X_MSG_COUNT OUT NOCOPY NUMBER
1124 X_MSG_DATA OUT NOCOPY VARCHAR2
1125 COMMENT : Adds ip descriptors to a draft auction
1126 ======================================================================*/
1127
1128 PROCEDURE Add_Catalog_Descriptors (
1129 P_API_VERSION IN NUMBER,
1130 P_DOCUMENT_NUMBER IN NUMBER,
1131 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1132 X_MSG_COUNT OUT NOCOPY NUMBER,
1133 X_MSG_DATA OUT NOCOPY VARCHAR2) IS
1134 v_contract_type pon_auction_headers_all.contract_type%TYPE;
1135 v_buyer_id NUMBER;
1136 v_ip_attr_default_option VARCHAR2(10);
1137 v_default_attr_group pon_auction_attributes.attr_group%TYPE;
1138 v_attr_group_name fnd_lookup_values.meaning%TYPE;
1139 v_max_seq_number NUMBER;
1140 v_line_number NUMBER;
1141 v_ip_category_id NUMBER;
1142 v_debug_status VARCHAR2(100);
1143
1144 v_return_status VARCHAR2(1);
1145 v_msg_count NUMBER;
1146 v_msg_data VARCHAR2(400);
1147
1148 CURSOR catalogLines IS
1149 SELECT distinct interface_line_number
1150 FROM pon_attributes_interface
1151 WHERE interface_auction_header_id = p_document_number;
1152
1153
1154 CURSOR nonCatalogLines IS
1155 SELECT distinct paip.line_number, paip.ip_category_id
1156 FROM pon_auction_item_prices_all paip,
1157 pon_backing_requisitions pbr,
1158 po_requisition_lines_all prl
1159 WHERE paip.auction_header_id = p_document_number and
1160 paip.auction_header_id = pbr.auction_header_id and
1161 paip.line_number = pbr.line_number and
1162 pbr.requisition_header_id = prl.requisition_header_id and
1163 pbr.requisition_line_id = prl.requisition_line_id and
1164 prl.blanket_po_header_id is null and
1165 prl.blanket_po_line_num is null;
1166
1167 BEGIN
1168
1169 SELECT contract_type, created_by
1170 INTO v_contract_type, v_buyer_id
1171 FROM pon_auction_headers_all
1172 WHERE auction_header_id = p_document_number;
1173
1174 v_ip_attr_default_option := fnd_profile.value('PON_IP_ATTR_DEFAULT_OPTION');
1175
1176 IF (v_contract_type <> 'BLANKET' or v_ip_attr_default_option is null or v_ip_attr_default_option = 'NONE') THEN
1177 RETURN;
1178 END IF;
1179
1180 select nvl(ppp.preference_value,'GENERAL'),
1181 flv.meaning
1182 into v_default_attr_group,
1183 v_attr_group_name
1184 from pon_party_preferences ppp,
1185 fnd_lookup_values flv
1186 where ppp.app_short_name = 'PON' and
1187 ppp.preference_name = 'LINE_ATTR_DEFAULT_GROUP' and
1188 ppp.party_id = (select trading_partner_id from pon_auction_headers_all where auction_header_id = p_document_number) and
1189 flv.lookup_type = 'PON_LINE_ATTRIBUTE_GROUPS' and
1190 nvl(ppp.preference_value,'GENERAL') = flv.lookup_code and
1191 flv.view_application_id = 0 and
1192 flv.security_group_id = 0 and
1193 flv.language = userenv('LANG');
1194
1195 v_max_seq_number := 9999999999999;
1196
1197
1198 PO_NEGOTIATIONS4_GRP.insert_attributes(
1199 p_api_version => 1.0,
1200 p_commit => fnd_api.g_false,
1201 p_init_msg_list => fnd_api.g_false,
1202 p_validation_level => fnd_api.g_valid_level_full,
1203 p_auction_header_id => p_document_number,
1204 x_return_status => v_return_status,
1205 x_msg_count => v_msg_count,
1206 x_msg_data => v_msg_data);
1207
1208 FOR catalogLine in catalogLines
1209 LOOP
1210
1211 v_line_number := catalogLine.interface_line_number;
1212
1213 INSERT INTO PON_AUCTION_ATTRIBUTES (
1214 AUCTION_HEADER_ID,
1215 LINE_NUMBER,
1216 ATTRIBUTE_NAME,
1217 DESCRIPTION,
1218 DATATYPE,
1219 MANDATORY_FLAG,
1220 VALUE,
1221 DISPLAY_PROMPT,
1222 HELP_TEXT,
1223 DISPLAY_TARGET_FLAG,
1224 CREATION_DATE,
1225 CREATED_BY,
1226 LAST_UPDATE_DATE,
1227 LAST_UPDATED_BY,
1228 ATTRIBUTE_LIST_ID,
1229 DISPLAY_ONLY_FLAG,
1230 SEQUENCE_NUMBER,
1231 COPIED_FROM_CAT_FLAG,
1232 WEIGHT,
1233 SCORING_TYPE,
1234 ATTR_LEVEL,
1235 ATTR_GROUP,
1236 SECTION_NAME,
1237 ATTR_MAX_SCORE,
1238 INTERNAL_ATTR_FLAG,
1239 ATTR_GROUP_SEQ_NUMBER,
1240 ATTR_DISP_SEQ_NUMBER,
1241 MODIFIED_FLAG,
1242 MODIFIED_DATE,
1243 LAST_AMENDMENT_UPDATE,
1244 IP_CATEGORY_ID,
1245 IP_DESCRIPTOR_ID
1246 )
1247 SELECT
1248 P_DOCUMENT_NUMBER, -- AUCTION_HEADER_ID
1249 INTERFACE_LINE_NUMBER, -- LINE_NUMBER
1250 ATTRIBUTE_NAME, -- ATTRIBUTE_NAME
1251 null, -- DESCRIPTION
1252 DATATYPE, -- DATATYPE
1253 'N', -- MANDATORY_FLAG
1254 VALUE, -- VALUE
1255 null, -- DISPLAY_PROMPT
1256 null, -- HELP_TEXT
1257 'N', -- DISPLAY_TARGET_FLAG
1258 SYSDATE, -- CREATION_DATE
1259 v_buyer_id, -- CREATED_BY
1260 SYSDATE, -- LAST_UPDATE_DATE
1261 v_buyer_id, -- LAST_UPDATED_BY
1262 -1, -- ATTRIBUTE_LIST_ID
1263 'N', -- DISPLAY_ONLY_FLAG
1264 (ROWNUM*10), -- SEQUENCE_NUMBER
1265 null, -- COPIED_FROM_CAT_FLAG
1266 null, -- WEIGHT
1267 null, -- SCORING_TYPE
1268 'LINE', -- ATTR_LEVEL
1269 v_default_attr_group, -- ATTR_GROUP
1270 v_attr_group_name, -- SECTION_NAME
1271 null, -- ATTR_MAX_SCORE
1272 'N', -- INTERNAL_ATTR_FLAG
1273 10, -- ATTR_GROUP_SEQ_NUMBER
1274 (ROWNUM*10), -- ATTR_DISP_SEQ_NUMBER
1275 null, -- MODIFIED_FLAG
1276 null, -- MODIFIED_DATE
1277 null, -- LAST_AMENDMENT_UPDATE
1278 IP_CATEGORY_ID, -- IP_CATEGORY_ID
1279 IP_DESCRIPTOR_ID -- IP_DESCRIPTOR_ID
1280 FROM
1281 (SELECT interface_line_number, attribute_name, datatype,
1282 value, ip_category_id, ip_descriptor_id
1283 FROM pon_attributes_interface
1284 WHERE interface_auction_header_id = P_DOCUMENT_NUMBER AND
1285 interface_line_number = v_line_number AND
1286 ((ip_category_id = 0 and v_ip_attr_default_option in ('ALL', 'BASE')) or
1287 (ip_category_id <> 0 and v_ip_attr_default_option in ('ALL', 'CATEGORY')))
1288 ORDER BY nvl(interface_sequence_number, v_max_seq_number) asc);
1289
1290 END LOOP;
1291
1292 DELETE FROM PON_ATTRIBUTES_INTERFACE
1293 WHERE interface_auction_header_id = P_DOCUMENT_NUMBER;
1294
1295
1296 FOR nonCatalogLine in nonCatalogLines
1297 LOOP
1298
1299 v_line_number := nonCatalogLine.line_number;
1300 v_ip_category_id := nonCatalogLine.ip_category_id;
1301
1302 INSERT INTO PON_AUCTION_ATTRIBUTES (
1303 AUCTION_HEADER_ID,
1304 LINE_NUMBER,
1305 ATTRIBUTE_NAME,
1306 DESCRIPTION,
1307 DATATYPE,
1308 MANDATORY_FLAG,
1309 VALUE,
1310 DISPLAY_PROMPT,
1311 HELP_TEXT,
1312 DISPLAY_TARGET_FLAG,
1313 CREATION_DATE,
1314 CREATED_BY,
1315 LAST_UPDATE_DATE,
1316 LAST_UPDATED_BY,
1317 ATTRIBUTE_LIST_ID,
1318 DISPLAY_ONLY_FLAG,
1319 SEQUENCE_NUMBER,
1320 COPIED_FROM_CAT_FLAG,
1321 WEIGHT,
1322 SCORING_TYPE,
1323 ATTR_LEVEL,
1324 ATTR_GROUP,
1325 SECTION_NAME,
1326 ATTR_MAX_SCORE,
1327 INTERNAL_ATTR_FLAG,
1328 ATTR_GROUP_SEQ_NUMBER,
1329 ATTR_DISP_SEQ_NUMBER,
1330 MODIFIED_FLAG,
1331 MODIFIED_DATE,
1332 LAST_AMENDMENT_UPDATE,
1333 IP_CATEGORY_ID,
1334 IP_DESCRIPTOR_ID
1335 )
1336 SELECT
1337 P_DOCUMENT_NUMBER, -- AUCTION_HEADER_ID
1338 v_line_number, -- LINE_NUMBER
1339 DESCRIPTOR_NAME, -- ATTRIBUTE_NAME
1340 null, -- DESCRIPTION
1341 DATATYPE, -- DATATYPE
1342 'N', -- MANDATORY_FLAG
1343 null, -- VALUE
1344 null, -- DISPLAY_PROMPT
1345 null, -- HELP_TEXT
1346 'N', -- DISPLAY_TARGET_FLAG
1347 SYSDATE, -- CREATION_DATE
1348 v_buyer_id, -- CREATED_BY
1349 SYSDATE, -- LAST_UPDATE_DATE
1350 v_buyer_id, -- LAST_UPDATED_BY
1351 -1, -- ATTRIBUTE_LIST_ID
1352 'N', -- DISPLAY_ONLY_FLAG
1353 (ROWNUM*10), -- SEQUENCE_NUMBER
1354 null, -- COPIED_FROM_CAT_FLAG
1355 null, -- WEIGHT
1356 null, -- SCORING_TYPE
1357 'LINE', -- ATTR_LEVEL
1358 v_default_attr_group, -- ATTR_GROUP
1359 v_attr_group_name, -- SECTION_NAME
1360 null, -- ATTR_MAX_SCORE
1361 'N', -- INTERNAL_ATTR_FLAG
1362 10, -- ATTR_GROUP_SEQ_NUMBER
1363 (ROWNUM*10), -- ATTR_DISP_SEQ_NUMBER
1364 null, -- MODIFIED_FLAG
1365 null, -- MODIFIED_DATE
1366 null, -- LAST_AMENDMENT_UPDATE
1367 IP_CATEGORY_ID, -- IP_CATEGORY_ID
1368 IP_DESCRIPTOR_ID -- IP_DESCRIPTOR_ID
1369 FROM
1370 (SELECT attribute_name descriptor_name, decode(type, 1, 'NUM', 'TXT') datatype,
1371 rt_category_id ip_category_id, attribute_id ip_descriptor_id
1372 FROM icx_cat_agreement_attrs_v
1373 WHERE ((rt_category_id = 0 and v_ip_attr_default_option in ('ALL', 'BASE')) or
1374 (rt_category_id = v_ip_category_id and v_ip_attr_default_option in ('ALL', 'CATEGORY'))) and language = userenv('LANG')
1375 ORDER BY nvl(sequence, v_max_seq_number) asc);
1376
1377 END LOOP;
1378
1379 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1380 X_MSG_COUNT := 0;
1381 X_MSG_DATA := NULL;
1382
1383 EXCEPTION
1384 WHEN OTHERS THEN
1385 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR ;
1386 X_MSG_COUNT := 1;
1387 fnd_message.set_name('PON', 'PON_AUC_PLSQL_ERR');
1388 fnd_message.set_token('PACKAGE','pon_auction_interface_pkg');
1389 fnd_message.set_token('PROCEDURE','Add_Catalog_Descriptors');
1390 fnd_message.set_token('ERROR',v_debug_status || ' [' || SQLERRM || ']');
1391 fnd_message.retrieve(X_MSG_DATA);
1392 RETURN;
1393 END Add_Catalog_Descriptors;
1394
1395 /*============ADDED FOR SERVICES PROCUREMENT PROJECT=====================
1396 PROCEDURE : Add_Price_Differential
1397 PARAMETERS:
1398 P_DOCUMENT_NUMBER IN Document number to add line
1399 P_LINE_NUMBER IN Line number
1400 P_SHIPMENT_NUMBER IN Shipment number
1401 P_PRICE_TYPE IN Price Type
1402 P_MULTIPLIER IN Multiplier
1403 P_BUYER_ID IN FND_USER_ID of the creator
1404 P_PRICE_DIFFERENTIAL_NUMBER OUT Price Differential Number
1405
1406 P_RESULT OUT One of (error, success)
1407 P_ERROR_CODE OUT Internal Error Code
1408 P_ERROR_MESSAGE OUT Displayable error
1409 COMMENT : Creates a price differential in a draft auction
1410 ======================================================================*/
1411
1412 PROCEDURE Add_Price_Differential (
1413 P_DOCUMENT_NUMBER IN NUMBER,
1414 P_LINE_NUMBER IN NUMBER,
1415 P_SHIPMENT_NUMBER IN NUMBER,
1416 P_PRICE_TYPE IN VARCHAR2,
1417 P_MULTIPLIER IN NUMBER,
1418 P_BUYER_ID IN NUMBER,
1419 P_PRICE_DIFFERENTIAL_NUMBER OUT NOCOPY NUMBER,
1420 P_RESULT OUT NOCOPY NUMBER,
1421 P_ERROR_CODE OUT NOCOPY VARCHAR2,
1422 P_ERROR_MESSAGE OUT NOCOPY VARCHAR2)IS
1423
1424 v_debug_status VARCHAR2(100);
1425
1426 BEGIN
1427
1428
1429
1430 -- Find the max price_diff_line number for this line and save it
1431 SELECT nvl(max(price_differential_number),0)+1
1432 INTO p_price_differential_number
1433 FROM pon_price_differentials
1434 WHERE auction_header_id = p_document_number AND
1435 line_number = p_line_number AND
1436 shipment_number = p_shipment_number;
1437
1438 IF (p_document_number IS NULL) THEN
1439 p_result := error;
1440 p_error_code := 'ADD_PRICE_DIFFERENTIAL:DOCUMENT_NUMBER';
1441 p_error_message := 'Please provide a valid document number';
1442 RETURN;
1443 END IF;
1444
1445 IF (p_line_number IS NULL OR p_line_number < 0) THEN
1446 p_result := error;
1447 p_error_code := 'ADD_PRICE_DIFFERENTIAL:LINE_NUMBER';
1448 p_error_message := 'Line Number cannot be null. Its either a valid positive number or -1.';
1449 RETURN;
1450 END IF ;
1451
1452 IF (p_shipment_number IS NULL) THEN
1453 p_result := error;
1454 p_error_code := 'ADD_PRICE_DIFFERENTIAL:SHIPMENT_NUMBER';
1455 p_error_message := 'Shipment Number cannot be null. Its either a valid positive number or -1.';
1456 RETURN;
1457 END IF ;
1458
1459 IF ((p_line_number = -1) AND (p_shipment_number = -1)) THEN
1460 p_result := error;
1461 p_error_code := 'ADD_PRICE_DIFFERENTIAL:INVALID_VALUES';
1462 p_error_message := 'Both Line Number and Shipment Number cannot be -1';
1463 RETURN;
1464 END IF;
1465
1466
1467 v_debug_status := 'INSERT_PRICE_DIFFERENTIALS';
1468
1469 INSERT INTO pon_price_differentials
1470 (
1471 auction_header_id,
1472 line_number,
1473 shipment_number,
1474 price_differential_number,
1475 price_type,
1476 multiplier,
1477 creation_date,
1478 created_by,
1479 last_update_date,
1480 last_updated_by
1481 )VALUES(
1482 p_document_number, -- Auction Header Id
1483 p_line_number, -- Line Number
1484 p_shipment_number, -- Shipment Number
1485 p_price_differential_number, -- Price Differentials Number
1486 p_price_type, -- Price Type
1487 p_multiplier, -- Multiplier
1488 Sysdate, -- creation date
1489 p_buyer_id, -- created by
1490 Sysdate, -- last update date
1491 p_buyer_id -- last updated by
1492 );
1493
1494 EXCEPTION
1495 WHEN OTHERS THEN
1496 fnd_message.set_name('PON', 'PON_AUC_PLSQL_ERR');
1497 fnd_message.set_token('PACKAGE','pon_auction_interface_pkg');
1498 fnd_message.set_token('PROCEDURE','Add_Price_Differential');
1499 fnd_message.set_token('ERROR',v_debug_status || ' [' || SQLERRM || ']');
1500 APP_EXCEPTION.RAISE_EXCEPTION;
1501 END;
1502
1503
1504 /*========================================================================
1505 PROCEDURE : Get_Negotiation_Owner
1506 PARAMETERS:
1507 P_DOCUMENT_NUMBER IN Document Id
1508 P_OWNER_NAME OUT FND_USER.USER_NAME of document owner
1509 P_RESULT OUT One of (error, success)
1510 P_ERROR_CODE OUT Internal Error Code
1511 P_ERROR_MESSAGE OUT Displayable error
1512 COMMENT : Returns the owner name for a negotiation document
1513 --======================================================================*/
1514 PROCEDURE Get_Negotiation_Owner(
1515 P_DOCUMENT_NUMBER IN NUMBER,
1516 P_OWNER_NAME OUT NOCOPY VARCHAR2,
1517 P_RESULT OUT NOCOPY NUMBER,
1518 P_ERROR_CODE OUT NOCOPY VARCHAR2,
1519 P_ERROR_MESSAGE OUT NOCOPY VARCHAR2) IS
1520
1521 v_buyer_name fnd_user.user_name%TYPE := NULL;
1522
1523 BEGIN
1524 BEGIN
1525 SELECT u1.user_name, u2.user_name
1526 INTO P_OWNER_NAME, v_buyer_name
1527 FROM fnd_user u1, fnd_user u2, pon_auction_headers_all ah
1528 WHERE ah.auction_header_id = P_DOCUMENT_NUMBER
1529 AND ah.trading_partner_contact_id = u1.person_party_id(+)
1530 AND ah.buyer_id = u2.user_id(+);
1531 EXCEPTION
1532 WHEN no_data_found THEN
1533 -- No owner found for a Document is not an error.
1534 P_OWNER_NAME := NULL;
1535 END;
1536
1537 IF (P_OWNER_NAME IS NULL) THEN
1538 P_OWNER_NAME := v_buyer_name;
1539 END IF;
1540
1541 P_RESULT := success;
1542 P_ERROR_CODE := NULL;
1543 P_ERROR_MESSAGE := NULL;
1544 EXCEPTION
1545 WHEN OTHERS THEN
1546 P_RESULT := error;
1547 P_ERROR_CODE := 'GET_NEG_OWNER';
1548 fnd_message.set_name('PON', 'PON_AUC_PLSQL_ERR');
1549 fnd_message.set_token('PACKAGE','pon_auction_interface_pkg');
1550 fnd_message.set_token('PROCEDURE','Get_Negotiation_Owner');
1551 fnd_message.set_token('ERROR','Document ' || P_DOCUMENT_NUMBER || ' [' || SQLERRM || ']');
1552 fnd_message.retrieve(P_ERROR_MESSAGE);
1553 END;
1554
1555 /*========================================================================
1556 PROCEDURE : Get_PO_Negotiation_Link PUBLIC
1557 PARAMETERS:
1558 P_PO_HEADER_ID IN PO Header Id
1559 P_DOCUMENT_ID OUT Negotiation document id
1560 P_DOCUMENT_NUMBER OUT Negotiation Document number display
1561 P_DOCUMENT_URL OUT URL to view negotiation document
1562 P_RESULT OUT One of (error, success)
1563 P_ERROR_CODE OUT Internal code for error
1564 P_ERROR_MESSAGE OUT Displayable error message
1565 COMMENT : Returns the Negotiation Document number and Sourcing URL
1566 for viewing the Negotiation Document. The Negotiation Document number
1567 returned is formatted for display and may not be the same as the
1568 pon_auction_headers.auction_header_id. The Document Number should not
1569 be used in subsequent calls to this API.
1570 FPJ: As we migrated to OA, this API is also updated. Because we cannot
1571 encrypt id at pl/sql, we return id as an out parameter. The caller
1572 needs to encrypt the id, and append to the url.
1573 ======================================================================*/
1574 PROCEDURE Get_PO_Negotiation_Link(
1575 P_PO_HEADER_ID IN NUMBER,
1576 P_DOCUMENT_ID OUT NOCOPY NUMBER,
1577 P_DOCUMENT_NUMBER OUT NOCOPY VARCHAR2,
1578 P_DOCUMENT_URL OUT NOCOPY VARCHAR2,
1579 P_RESULT OUT NOCOPY NUMBER,
1580 P_ERROR_CODE OUT NOCOPY VARCHAR2,
1581 P_ERROR_MESSAGE OUT NOCOPY VARCHAR2) IS
1582
1583 v_debug_status VARCHAR2(60);
1584
1585 BEGIN
1586 BEGIN
1587 SELECT ah.auction_header_id, ah.document_number
1588 INTO P_DOCUMENT_ID, P_DOCUMENT_NUMBER
1589 FROM pon_bid_headers bh, pon_auction_headers_all ah
1590 WHERE bh.po_header_id = P_PO_HEADER_ID
1591 AND bh.auction_header_id = ah.auction_header_id;
1592 EXCEPTION
1593 WHEN no_data_found THEN
1594 -- No negotiation found for a PO is not an error
1595 P_DOCUMENT_ID := NULL;
1596 P_DOCUMENT_NUMBER := NULL;
1597 P_DOCUMENT_URL := NULL;
1598 P_RESULT := success;
1599 P_ERROR_CODE := NULL;
1600 P_ERROR_MESSAGE := NULL;
1601 RETURN;
1602 END;
1603
1604 -- Construct URL to View Negotation
1605 v_debug_status := 'DOC_URL';
1606 P_DOCUMENT_URL := 'OA.jsp?OAFunc=PON_NEG_SUMMARY';
1607 P_RESULT := success;
1608 P_ERROR_CODE := NULL;
1609 P_ERROR_MESSAGE := NULL;
1610 EXCEPTION
1611 WHEN OTHERS THEN
1612 P_RESULT := error;
1613 P_ERROR_CODE := 'GET_NEG_OWNER';
1614 fnd_message.set_name('PON', 'PON_AUC_PLSQL_ERR');
1615 fnd_message.set_token('PACKAGE','pon_auction_interface_pkg');
1616 fnd_message.set_token('PROCEDURE','Get_PO_Negotiation_Link');
1617 fnd_message.set_token('ERROR','PO Header ' || P_PO_HEADER_ID || ' [' || SQLERRM || ']');
1618 fnd_message.retrieve(P_ERROR_MESSAGE);
1619 END;
1620 --
1621 /*===================================================================
1622 PROCEDURE: add_negotiation_invitees PUBLIC
1623 PARAMETERS:
1624 p_api_version IN version of the api
1625 x_return_status OUT FND_API.G_RET_STS_SUCCESS or FND_API.G_RET_STS_ERROR
1626 x_msg_count OUT Internal code for error
1627 x_msg_data OUT Displayable error message
1628 P_DOCUMENT_NUMBER IN Negotiation Document number
1629 P_BUYER_ID IN FND_USER_ID of the creator
1630 COMMENT: Gets distinct vendor_ids and vendor sites
1631 across all the requisition lines that are part of the
1632 negotiation and adds (bulk inserts ) them as invitees. We do not check
1633 for inactive suppliers/ sites in the autocreate process; these
1634 will be validated at publish time.
1635 =====================================================================*/
1636 PROCEDURE add_negotiation_invitees(
1637 p_api_version IN NUMBER,
1638 x_return_status OUT NOCOPY VARCHAR2,
1639 x_msg_count OUT NOCOPY NUMBER,
1640 x_msg_data OUT NOCOPY VARCHAR2,
1641 P_DOCUMENT_NUMBER IN NUMBER,
1642 P_BUYER_ID IN NUMBER) IS
1643 l_api_version CONSTANT NUMBER := 1.0;
1644 l_api_name CONSTANT VARCHAR2(50):= 'add_negotiation_invitees';
1645
1646 BEGIN
1647
1648 -- Check for call compatibility.
1649 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
1650 l_api_name, g_pkg_name)
1651 THEN
1652 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1653 END IF;
1654
1655 IF (P_DOCUMENT_NUMBER IS NULL) THEN
1656 x_return_status := FND_API.G_RET_STS_SUCCESS;
1657 x_msg_count := 0;
1658 x_msg_data := 'Please provide a DOCUMENT_NUMBER';
1659 RETURN;
1660 END IF;
1661
1662
1663 IF (P_BUYER_ID IS NULL) THEN
1664 x_return_status := FND_API.G_RET_STS_SUCCESS;
1665 x_msg_count := 0;
1666 x_msg_data := 'Please specify a BUYER_ID';
1667 RETURN;
1668 END IF;
1669
1670 INSERT INTO PON_BIDDING_PARTIES
1671 (
1672 AUCTION_HEADER_ID,
1673 List_ID,
1674 LAST_UPDATE_DATE,
1675 LAST_UPDATED_BY,
1676 SEQUENCE,
1677 TRADING_PARTNER_NAME,
1678 TRADING_PARTNER_ID,
1679 TRADING_PARTNER_CONTACT_NAME,
1680 TRADING_PARTNER_CONTACT_ID,
1681 CREATION_DATE,
1682 CREATED_BY,
1683 NUMBER_PRICE_DECIMALS,
1684 ROUND_NUMBER,
1685 LAST_AMENDMENT_UPDATE,
1686 VENDOR_SITE_ID,
1687 VENDOR_SITE_CODE,
1688 ACCESS_TYPE
1689 )
1690 SELECT
1691 P_DOCUMENT_NUMBER, --AUCTION_HEADER_ID
1692 -1, -- List_ID
1693 sysdate, -- LAST_UPDATE_DATE
1694 p_buyer_id, -- LAST_UPDATED_BY
1695 rownum * 10, -- SEQUENCE
1696 vendor_name, -- TRADING_PARTNER_NAME
1697 party_id, -- TRADING_PARTNER_ID
1698 null, -- TRADING_PARTNER_CONTACT_NAME
1699 null, -- TRADING_PARTNER_CONTACT_ID
1700 sysdate, -- CREATION_DATE
1701 p_buyer_id, -- CREATED_BY
1702 NUMBER_PRICE_DECIMALS, -- NUMBER_PRICE_DECIMALS
1703 1, -- ROUND_NUMBER
1704 0, -- LAST_AMENDMENT_UPDATE
1705 vendor_site_id, -- VENDOR_SITE_ID
1706 vendor_site_code, -- VENDOR_SITE_CODE
1707 'FULL' -- ACCESS_TYPE
1708 FROM
1709 (SELECT DISTINCT
1710 pv.vendor_name vendor_name,
1711 pv.party_id party_id,
1712 ponah.number_price_decimals number_price_decimals,
1713 nvl(prl.vendor_site_id, -1) vendor_site_id,
1714 nvl(ps.vendor_site_code, -1) vendor_site_code
1715 FROM po_requisition_lines_all prl,
1716 pon_backing_requisitions ponbr,
1717 pon_auction_headers_all ponah,
1718 po_vendors pv,
1719 po_vendor_sites_all ps
1720 WHERE ponah.auction_header_id = p_document_number
1721 and ponbr.auction_header_id = ponah.auction_header_id
1722 and ponbr.requisition_header_id = prl.requisition_header_id
1723 and ponbr.requisition_line_id = prl.requisition_line_id
1724 and prl.vendor_id is not null
1725 and prl.vendor_id = pv.vendor_id
1726 and nvl(pv.start_date_active, sysdate) <= sysdate
1727 and nvl(pv.end_date_active, sysdate) >= sysdate
1728 and ps.vendor_id(+) = prl.vendor_id
1729 and ps.vendor_site_id(+) = prl.vendor_site_id )
1730 ORDER BY vendor_name;
1731
1732
1733 x_return_status := FND_API.G_RET_STS_SUCCESS;
1734 x_msg_count := 0;
1735 x_msg_data := NULL;
1736
1737 EXCEPTION
1738 WHEN NO_DATA_FOUND THEN
1739 x_return_status := FND_API.G_RET_STS_SUCCESS;
1740 x_msg_count := 0;
1741 x_msg_data := NULL;
1742 RETURN;
1743 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1744 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1745 x_msg_count := 1;
1746 fnd_message.set_name('PON', 'PON_AUC_PLSQL_ERR');
1747 fnd_message.set_token('PACKAGE','pon_auction_interface_pkg');
1748 fnd_message.set_token('PROCEDURE','add_negotiation_invitees');
1749 fnd_message.set_token('ERROR', ' [' || SQLERRM || ']');
1750 fnd_message.retrieve(x_msg_data);
1751 RETURN;
1752 WHEN OTHERS THEN
1753 x_return_status := FND_API.G_RET_STS_ERROR ;
1754 x_msg_count := 1;
1755 fnd_message.set_name('PON', 'PON_AUC_PLSQL_ERR');
1756 fnd_message.set_token('PACKAGE','pon_auction_interface_pkg');
1757 fnd_message.set_token('PROCEDURE','add_negotiation_invitees');
1758 fnd_message.set_token('ERROR', ' [' || SQLERRM || ']');
1759 --APP_EXCEPTION.RAISE_EXCEPTION;
1760 fnd_message.retrieve(x_msg_data);
1761 RETURN;
1762 END;
1763
1764 -- API used by html autocreation for default negotiation style
1765 PROCEDURE get_default_negotiation_style(
1766 x_style_id OUT NOCOPY NUMBER,
1767 x_style_name OUT NOCOPY VARCHAR2) IS
1768
1769 BEGIN
1770
1771 select style_id, style_name
1772 into x_style_id, x_style_name
1773 from pon_negotiation_styles_tl
1774 where style_id = 1
1775 and language = userenv('LANG');
1776
1777 EXCEPTION
1778 WHEN OTHERS THEN
1779 x_style_id := 1;
1780 END;
1781
1782 --
1783 END PON_AUCTION_INTERFACE_PKG;