[Home] [Help]
PACKAGE BODY: APPS.PON_AUCTION_INTERFACE_PKG
Source
1 PACKAGE BODY pon_auction_interface_pkg AS
2 /* $Header: PONAUCIB.pls 120.55.12020000.5 2013/02/09 06:02:15 hvutukur ship $ */
3 g_pkg_name CONSTANT VARCHAR2(30) := 'PON_AUCTION_INTERFACE_PKG';
4 g_progress_payment_type pon_auction_headers_all.progress_payment_type%TYPE := 'NONE';
5 -- global variables added for header price break default project
6 g_price_break_type pon_auction_item_prices_all.price_break_type%type;
7 g_price_break_neg_flag pon_auction_item_prices_all.price_break_neg_flag%type;
8 /*
9 ===================
10 PROCEDURES
11 ===================
12 */
13 PROCEDURE LOGME
14 (
15 p_base VARCHAR2,
16 p_message VARCHAR2)
17 IS
18 BEGIN
19 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
20 fnd_log.string(fnd_log.level_statement,'pon.plsql.pon_auction_interface_pkg.'||p_base, p_message);
21 END IF;
22 END;
23 /*
24 ========================================================================
25 PROCEDURE : Create_Draft_Negotiation PUBLIC
26 PARAMETERS:
27 P_DOCUMENT_TITLE IN Title of negotiation
28 P_DOCUMENT_TYPE IN 'BUYER_AUCTION' or 'REQUEST_FOR_QUOTE'
29 P_CONTRACT_TYPE IN 'STANDARD' or 'BLANKET'
30 P_ORIGINATION_CODE IN 'REQUISITION' or caller product name
31 P_ORG_ID IN Organization id of creator
32 P_BUYER_ID IN FND_USER_ID of creator
33 P_NEG_STYLE_ID IN negotiation style id
34 P_PO_STYLE_ID IN po style id
35 P_FAIR_OPP_NOTICE_FLAG IN VARCHAR2 DEFAULT NULL
36 P_DOCUMENT_NUMBER IN OUT Created Document number
37 P_DOCUMENT_URL OUT Additional parameters to PON_AUC_EDIT_DRAFT_B
38 form function for editing draft
39 P_RESULT OUT One of (error, success)
40 P_ERROR_CODE OUT Internal code for error
41 P_ERROR_MESSAGE OUT Displayable error
42 COMMENT : Creates a draft auction
43 ======================================================================*/
44 PROCEDURE Create_Draft_Negotiation
45 (
46 P_DOCUMENT_TITLE IN VARCHAR2,
47 P_DOCUMENT_TYPE IN VARCHAR2,
48 P_CONTRACT_TYPE IN VARCHAR2,
49 P_ORIGINATION_CODE IN VARCHAR2,
50 P_ORG_ID IN NUMBER,
51 P_BUYER_ID IN NUMBER,
52 P_NEG_STYLE_ID IN NUMBER,
53 P_PO_STYLE_ID IN NUMBER,
54 P_FAIR_OPP_NOTICE_FLAG IN VARCHAR2 DEFAULT NULL,
55 P_DOCUMENT_NUMBER IN OUT NOCOPY NUMBER,
56 P_DOCUMENT_URL OUT NOCOPY VARCHAR2,
57 P_RESULT OUT NOCOPY NUMBER,
58 P_ERROR_CODE OUT NOCOPY VARCHAR2,
59 P_ERROR_MESSAGE OUT NOCOPY VARCHAR2)
60 IS
61 l_log_module VARCHAR2(100) := 'CREATE_DRAFT_NEGOTIATION';
62 v_debug_status VARCHAR2(100);
63 v_doctype_id pon_auc_doctypes.doctype_id%TYPE;
64 v_transaction_type pon_auc_doctypes.transaction_type%TYPE;
65 v_site_id pon_auction_headers_all.trading_partner_id%TYPE;
66 v_site_name pon_auction_headers_all.trading_partner_name%TYPE;
67 v_multi_org fnd_product_groups.multi_org_flag%TYPE := 'Y';
68 v_price_tiers_indicator pon_auction_headers_all.price_tiers_indicator%type;
69 l_price_break_response pon_auction_headers_all.price_break_response%type;
70 l_style_name po_doc_style_headers.style_name%TYPE;
71 l_style_description po_doc_style_headers.style_description%TYPE;
72 l_style_type po_doc_style_headers.style_type%TYPE;
73 l_status po_doc_style_headers.status%TYPE;
74 l_advances_flag po_doc_style_headers.advances_flag%TYPE;
75 l_retainage_flag po_doc_style_headers.retainage_flag%TYPE;
76 l_price_breaks_flag po_doc_style_headers.price_breaks_flag%TYPE;
77 l_price_differentials_flag po_doc_style_headers.price_differentials_flag%TYPE;
78 l_progress_payment_flag po_doc_style_headers.progress_payment_flag%TYPE;
79 l_contract_financing_flag po_doc_style_headers.contract_financing_flag%TYPE;
80 l_line_type_allowed po_doc_style_headers.line_type_allowed%TYPE;
81 l_line_attribute_enabled_flag pon_negotiation_styles.line_attribute_enabled_flag%TYPE;
82 l_line_mas_enabled_flag pon_negotiation_styles.line_mas_enabled_flag%TYPE;
83 l_price_element_enabled_flag pon_negotiation_styles.price_element_enabled_flag%TYPE;
84 l_rfi_line_enabled_flag pon_negotiation_styles.rfi_line_enabled_flag%TYPE;
85 l_lot_enabled_flag pon_negotiation_styles.lot_enabled_flag%TYPE;
86 l_group_enabled_flag pon_negotiation_styles.group_enabled_flag%TYPE;
87 l_large_neg_enabled_flag pon_negotiation_styles.large_neg_enabled_flag%TYPE;
88 l_hdr_attribute_enabled_flag pon_negotiation_styles.hdr_attribute_enabled_flag%TYPE;
89 l_neg_team_enabled_flag pon_negotiation_styles.neg_team_enabled_flag%TYPE;
90 l_proxy_bidding_enabled_flag pon_negotiation_styles.proxy_bidding_enabled_flag%TYPE;
91 l_power_bidding_enabled_flag pon_negotiation_styles.power_bidding_enabled_flag%TYPE;
92 l_auto_extend_enabled_flag pon_negotiation_styles.auto_extend_enabled_flag%TYPE;
93 l_team_scoring_enabled_flag pon_negotiation_styles.team_scoring_enabled_flag%TYPE;
94 l_qty_price_tier_enabled_flag pon_negotiation_styles.qty_price_tiers_enabled_flag%TYPE;
95
96 -- Begin Supplier Management: Bug 14087712
97 l_supp_reg_qual_flag pon_negotiation_styles.supp_reg_qual_flag%TYPE;
98 l_supp_eval_flag pon_negotiation_styles.supp_eval_flag%TYPE;
99 l_hide_terms_flag pon_negotiation_styles.hide_terms_flag%TYPE;
100 l_hide_abstract_forms_flag pon_negotiation_styles.hide_abstract_forms_flag%TYPE;
101 l_hide_attachments_flag pon_negotiation_styles.hide_attachments_flag%TYPE;
102 l_internal_eval_flag pon_negotiation_styles.internal_eval_flag%TYPE;
103 l_hdr_supp_attr_enabled_flag pon_negotiation_styles.hdr_supp_attr_enabled_flag%TYPE;
104 l_intgr_hdr_attr_flag pon_negotiation_styles.intgr_hdr_attr_flag%TYPE;
105 l_intgr_hdr_attach_flag pon_negotiation_styles.intgr_hdr_attach_flag%TYPE;
106 l_line_supp_attr_enabled_flag pon_negotiation_styles.line_supp_attr_enabled_flag%TYPE;
107 l_item_supp_attr_enabled_flag pon_negotiation_styles.item_supp_attr_enabled_flag%TYPE;
108 l_intgr_cat_line_attr_flag pon_negotiation_styles.intgr_cat_line_attr_flag%TYPE;
109 l_intgr_item_line_attr_flag pon_negotiation_styles.intgr_item_line_attr_flag%TYPE;
110 l_intgr_cat_line_asl_flag pon_negotiation_styles.intgr_cat_line_asl_flag%TYPE;
111 -- End Supplier Management: Bug 14087712
112
113 --<Sol Project>
114 l_uda_template_id NUMBER;
115 l_uda_template_date DATE;
116 l_return_status VARCHAR2(1);
117 l_doc_exist VARCHAR2(1);
118 l_err_msg VARCHAR2(2000);
119 l_sol_type VARCHAR2(3);
120 clm_doc_number VARCHAR2(100);
121 l_standard_form pon_auction_headers_all.standard_form%TYPE :=NULL;
122 l_document_format pon_auction_headers_all.document_format%TYPE:=NULL;
123 l_draft_locked pon_auction_headers_all.draft_locked%TYPE;
124 l_draft_locked_by pon_auction_headers_all.draft_locked_by%TYPE;
125 l_draft_locked_by_contact_id pon_auction_headers_all.draft_locked_by_contact_id%TYPE;
126 i_draft_locked_by pon_auction_headers_all.draft_locked_by%TYPE;
127 i_draft_locked_by_contact_id pon_auction_headers_all.draft_locked_by_contact_id%TYPE;
128 BEGIN
129 --CLM Bug : 10096343
130 g_progress_payment_type := 'NONE';
131 IF (P_DOCUMENT_TYPE NOT IN ('BUYER_AUCTION', 'REQUEST_FOR_QUOTE','SOLICITATION')) THEN
132 P_RESULT := error;
133 P_ERROR_CODE := 'CREATE_DRAFT:INVALID_DOC_TYPE';
134 P_ERROR_MESSAGE := 'Invalid Document Type ' || P_DOCUMENT_TYPE;
135 logme(l_log_module, p_error_code ||' - '||p_error_message);
136 RETURN;
137 END IF;
138 IF (P_CONTRACT_TYPE NOT IN ('BLANKET', 'STANDARD')) THEN
139 P_RESULT := error;
140 P_ERROR_CODE := 'CREATE_DRAFT:INVALID_CONTRACT_TYPE';
141 P_ERROR_MESSAGE := 'Invalid Contract Type ' || P_CONTRACT_TYPE;
142 logme(l_log_module, p_error_code ||' - '||p_error_message);
143 RETURN;
144 END IF;
145 IF (P_ORIGINATION_CODE <> 'REQUISITION') THEN
146 P_RESULT := error;
147 P_ERROR_CODE := 'CREATE_DRAFT:UNKNOWN_ORIGINATION';
148 P_ERROR_MESSAGE := 'Invalid Origination Code ' || P_ORIGINATION_CODE;
149 logme(l_log_module, p_error_code ||' - '||p_error_message);
150 RETURN;
151 END IF;
152 IF (P_BUYER_ID IS NULL) THEN
153 P_RESULT := error;
154 P_ERROR_CODE := 'CREATE_DRAFT:NULL_BUYER_ID';
155 P_ERROR_MESSAGE := 'Please specify a BUYER_ID';
156 logme(l_log_module, p_error_code ||' - '||p_error_message);
157 RETURN;
158 END IF;
159 -- Is this multiorg?
160 v_debug_status := 'MULTIORG';
161 BEGIN
162 SELECT multi_org_flag INTO v_multi_org FROM fnd_product_groups;
163 EXCEPTION
164 WHEN no_data_found THEN
165 P_RESULT := error;
166 P_ERROR_CODE := 'CREATE_DRAFT:MULTI_ORG_QUERY';
167 fnd_message.set_name('PON', 'PON_AUC_PLSQL_ERR');
168 fnd_message.set_token('PACKAGE','pon_auction_interface_pkg');
169 fnd_message.set_token('PROCEDURE','Create_Draft_Negotiation');
170 fnd_message.set_token('ERROR','Multi-Org Query Failed [' || SQLERRM || ']');
171 fnd_message.retrieve(P_ERROR_MESSAGE);
172 logme(l_log_module, p_error_code ||' - '||p_error_message);
173 RETURN;
174 END;
175 IF (P_DOCUMENT_NUMBER IS NOT NULL) THEN
176 BEGIN
177 SELECT draft_locked,
178 draft_locked_by,
179 draft_locked_by_contact_id
180 INTO l_draft_locked,
181 l_draft_locked_by,
182 l_draft_locked_by_contact_id
183 FROM pon_auction_headers_all
184 WHERE auction_header_id = p_document_number;
185 EXCEPTION
186 WHEN no_data_found THEN
187 P_RESULT := error;
188 P_ERROR_CODE := 'ADD_TO_DRAFT:DOC_QUERY';
189 --fnd_message.set_name('PON', 'PON_AUC_PLSQL_ERR');
190 --fnd_message.set_token('PACKAGE','pon_auction_interface_pkg');
191 --fnd_message.retrieve(P_ERROR_MESSAGE);
192 logme(l_log_module, p_error_code ||' - '||p_error_message);
193 RETURN;
194 END;
195 logme(l_log_module, 'draft locked '|| l_draft_locked||' locked by '||
196 l_draft_locked_by|| 'locked by contact '|| l_draft_locked_by_contact_id);
197 IF l_draft_locked = 'Y' THEN
198 SELECT user_parties.party_id user_party_id,
199 company_parties.party_id company_party_id
200 INTO i_draft_locked_by,
201 i_draft_locked_by_contact_id
202 FROM fnd_user,
203 hz_parties user_parties,
204 hz_parties company_parties,
205 hz_relationships,
206 hz_code_assignments
207 WHERE fnd_user.person_party_id = user_parties.party_id
208 AND fnd_user.user_id = p_buyer_id
209 AND hz_relationships.object_id = company_parties.party_id
210 AND hz_relationships.subject_id = user_parties.party_id
211 AND hz_relationships.relationship_type = 'POS_EMPLOYMENT'
212 AND hz_relationships.relationship_code = 'EMPLOYEE_OF'
213 AND hz_relationships.start_date <= SYSDATE
214 AND hz_relationships.end_date >= SYSDATE
215 AND hz_code_assignments.owner_table_id = company_parties.party_id
216 AND hz_code_assignments.owner_table_name = 'HZ_PARTIES'
217 AND hz_code_assignments.class_category = 'POS_PARTICIPANT_TYPE'
218 AND hz_code_assignments.class_code = 'ENTERPRISE';
219
220 logme(l_log_module, 'current user '||i_draft_locked_by|| 'user contact '||i_draft_locked_by_contact_id);
221 IF l_draft_locked_by <> i_draft_locked_by OR l_draft_locked_by_contact_id <> i_draft_locked_by_contact_id THEN
222 --Unlock the document if it locked by a different user.
223 UPDATE PON_AUCTION_HEADERS_ALL
224 SET DRAFT_LOCKED = 'N',
225 DRAFT_LOCKED_BY = NULL,
226 DRAFT_LOCKED_BY_CONTACT_ID = NULL,
227 DRAFT_LOCKED_DATE = NULL,
228 DRAFT_UNLOCKED_BY = i_draft_locked_by,
229 DRAFT_UNLOCKED_BY_CONTACT_ID = i_draft_locked_by_contact_id,
230 DRAFT_UNLOCKED_DATE = SYSDATE,
231 LAST_UPDATE_DATE = SYSDATE,
232 LAST_UPDATED_BY = p_buyer_id
233 WHERE AUCTION_HEADER_ID = p_document_number;
234
235 logme(l_log_module, 'unlocked');
236 END IF;
237 END IF; -- l_draft_locked = 'Y' THEN
238
239 UPDATE pon_auction_headers_all
240 SET AUCTION_ORIGINATION_CODE = 'REQUISITION', BUYER_ID = p_buyer_id -- bug 13640015
241 WHERE AUCTION_HEADER_ID = p_document_number; -- bug 13640015
242
243 END IF;
244 IF (P_ORG_ID IS NULL AND v_multi_org = 'Y') THEN
245 P_RESULT := error;
246 P_ERROR_CODE := 'CREATE_DRAFT:NULL_ORG_ID';
247 P_ERROR_MESSAGE := 'Please specify an ORG_ID';
248 RETURN;
249 END IF;
250 logme(l_log_module, ' p_document_number '||p_document_number);
251 IF (P_DOCUMENT_NUMBER IS NULL) THEN
252 logme(l_log_module, 'creating new doc');
253 -- Get site ID for the enterprise
254 v_debug_status := 'SITE_ID';
255 pos_enterprise_util_pkg.get_enterprise_partyId(v_site_id, P_ERROR_CODE, P_ERROR_MESSAGE);
256 IF (P_ERROR_CODE IS NOT NULL OR v_site_id IS NULL) THEN
257 P_RESULT := error;
258 P_ERROR_CODE := 'CREATE_DRAFT:GET_ENTERPRISE_ID';
259 P_ERROR_MESSAGE := 'Could not get the Enterprise ID';
260 logme(l_log_module, p_error_code ||' - '||p_error_message);
261 RETURN;
262 END IF;
263 -- Get site name for the enterprise
264 v_debug_status := 'SITE_NAME';
265 pos_enterprise_util_pkg.get_enterprise_party_name(v_site_name, P_ERROR_CODE, P_ERROR_MESSAGE);
266 IF (P_ERROR_CODE IS NOT NULL) THEN
267 P_RESULT := error;
268 P_ERROR_CODE := 'CREATE_DRAFT:GET_ENTERPRISE_NAME';
269 P_ERROR_MESSAGE := 'Could not get the Enterprise Name';
270 logme(l_log_module, p_error_code ||' - '||p_error_message);
271 RETURN;
272 END IF;
273 IF ( p_po_style_id IS NOT NULL) THEN
274 PO_DOC_STYLE_GRP.GET_DOCUMENT_STYLE_SETTINGS( p_api_version => 1.0 ,
275 p_style_id => p_po_style_id ,
276 x_style_name => l_style_name ,
277 x_style_description => l_style_description ,
278 x_style_type => l_style_type ,
279 x_status => l_status ,
280 x_advances_flag => l_advances_flag ,
281 x_retainage_flag => l_retainage_flag ,
282 x_price_breaks_flag => l_price_breaks_flag ,
283 x_price_differentials_flag => l_price_differentials_flag ,
284 x_progress_payment_flag => l_progress_payment_flag ,
285 x_contract_financing_flag => l_contract_financing_flag ,
286 x_line_type_allowed => l_line_type_allowed);
287 IF l_progress_Payment_flag = 'Y' THEN
288 IF (P_DOCUMENT_TYPE NOT IN ('REQUEST_FOR_QUOTE','SOLICITATION') ) THEN
289 P_RESULT := error;
290 P_ERROR_CODE := 'CREATE_DRAFT:INVALID_DOC_TYPE';
291 P_ERROR_MESSAGE := 'Invalid Document Type For Complex Work Style ' || P_CONTRACT_TYPE;
292 logme(l_log_module, p_error_code ||' - '||p_error_message);
293 RETURN;
294 END IF;
295 IF (p_Contract_type <> 'STANDARD') THEN
296 P_RESULT := error;
297 P_ERROR_CODE := 'CREATE_DRAFT:INVALID_CONTRACT_TYPE';
298 P_ERROR_MESSAGE := 'Invalid Contract Type For Complex Work Style ' || P_CONTRACT_TYPE;
299 logme(l_log_module, p_error_code ||' - '||p_error_message);
300 RETURN;
301 END IF;
302 --Set the following attribute on negotiation-
303 IF (l_contract_financing_flag = 'Y') THEN
304 g_progress_payment_type := 'FINANCE';
305 ELSE
306 g_progress_payment_type := 'ACTUAL';
307 END IF;
308 END IF;
309 END IF;
310 -- Get doctypeID
311 v_debug_status := 'DOCTYPE_ID';
312 SELECT doctype_id,
313 transaction_type
314 INTO v_doctype_id,
315 v_transaction_type
316 FROM pon_auc_doctypes
317 WHERE internal_name = P_DOCUMENT_TYPE;
318 -- price break header setting
319 PON_AUCTION_PKG.get_default_hdr_pb_settings ( v_doctype_id, v_site_id, l_price_break_response);
320 -- Insert a row into PON_AUCTION_HEADERS_ALL
321 -- See NegotiationDoc.java for the majority of defaulting - setDefaults()
322 -- Get all the style related columns from PON_NEGOTIATION_STYLES table for the style id.
323 -- Populate all the style related columns in PON_AUCTION_HEADERS_ALL table.
324 -- This procedure is invoked from two flows.
325 -- 1. HTML Autocreate : We select the syle id from the UI and the style id is passed as an arugment here.
326 -- 2. Forms based Autocreate : We will not have any option to select style from the forms and the style id wil be null here.
327 IF P_NEG_STYLE_ID IS NOT NULL THEN
328 BEGIN
329 SELECT LINE_ATTRIBUTE_ENABLED_FLAG,
330 LINE_MAS_ENABLED_FLAG,
331 PRICE_ELEMENT_ENABLED_FLAG,
332 RFI_LINE_ENABLED_FLAG,
333 LOT_ENABLED_FLAG,
334 GROUP_ENABLED_FLAG,
335 LARGE_NEG_ENABLED_FLAG,
336 HDR_ATTRIBUTE_ENABLED_FLAG,
337 NEG_TEAM_ENABLED_FLAG,
338 PROXY_BIDDING_ENABLED_FLAG,
339 POWER_BIDDING_ENABLED_FLAG,
340 AUTO_EXTEND_ENABLED_FLAG,
341 TEAM_SCORING_ENABLED_FLAG ,
342 QTY_PRICE_TIERS_ENABLED_FLAG,
343 -- Begin Supplier Management: Bug 14087712
344 SUPP_REG_QUAL_FLAG,
345 SUPP_EVAL_FLAG,
346 HIDE_TERMS_FLAG,
347 HIDE_ABSTRACT_FORMS_FLAG,
348 HIDE_ATTACHMENTS_FLAG,
349 INTERNAL_EVAL_FLAG,
350 HDR_SUPP_ATTR_ENABLED_FLAG,
351 INTGR_HDR_ATTR_FLAG,
352 INTGR_HDR_ATTACH_FLAG,
353 LINE_SUPP_ATTR_ENABLED_FLAG,
354 ITEM_SUPP_ATTR_ENABLED_FLAG,
355 INTGR_CAT_LINE_ATTR_FLAG,
356 INTGR_ITEM_LINE_ATTR_FLAG,
357 INTGR_CAT_LINE_ASL_FLAG
358 -- End Supplier Management: Bug 14087712
359 INTO l_line_attribute_enabled_flag,
360 l_line_mas_enabled_flag,
361 l_price_element_enabled_flag,
362 l_rfi_line_enabled_flag,
363 l_lot_enabled_flag,
364 l_group_enabled_flag,
365 l_large_neg_enabled_flag,
366 l_hdr_attribute_enabled_flag,
367 l_neg_team_enabled_flag,
368 l_proxy_bidding_enabled_flag,
369 l_power_bidding_enabled_flag,
370 l_auto_extend_enabled_flag,
371 l_team_scoring_enabled_flag,
372 l_qty_price_tier_enabled_flag,
373 -- Begin Supplier Management: Bug 14087712
374 l_supp_reg_qual_flag,
375 l_supp_eval_flag,
376 l_hide_terms_flag,
377 l_hide_abstract_forms_flag,
378 l_hide_attachments_flag,
379 l_internal_eval_flag,
380 l_hdr_supp_attr_enabled_flag,
381 l_intgr_hdr_attr_flag,
382 l_intgr_hdr_attach_flag,
383 l_line_supp_attr_enabled_flag,
384 l_item_supp_attr_enabled_flag,
385 l_intgr_cat_line_attr_flag,
386 l_intgr_item_line_attr_flag,
387 l_intgr_cat_line_asl_flag
388 -- End Supplier Management: Bug 14087712
389 FROM PON_NEGOTIATION_STYLES
390 WHERE STYLE_ID = P_NEG_STYLE_ID;
391 EXCEPTION
392 WHEN NO_DATA_FOUND THEN
393 l_line_attribute_enabled_flag := NULL;
394 l_line_mas_enabled_flag := NULL;
395 l_price_element_enabled_flag := NULL;
396 l_rfi_line_enabled_flag := NULL;
397 l_lot_enabled_flag := NULL;
398 l_group_enabled_flag := NULL;
399 l_large_neg_enabled_flag := NULL;
400 l_hdr_attribute_enabled_flag := NULL;
401 l_neg_team_enabled_flag := NULL;
402 l_proxy_bidding_enabled_flag := NULL;
403 l_power_bidding_enabled_flag := NULL;
404 l_auto_extend_enabled_flag := NULL;
405 l_team_scoring_enabled_flag := NULL;
406 l_qty_price_tier_enabled_flag := 'Y';
407 END;
408 END IF;
409 -- R12.1 Price tiers Project
410 -- Get Default price tiers indicator
411 logme('pon.plsql.pon_auction_interface_pkg.Create_Draft_Negotiation',
412 'Calling the PON_AUCTION_PKG.GET_DEFAULT_TIERS_INDICATOR API to get the' ||
413 ' default price tiers indicator value.');
414 v_debug_status := 'PRICE_TIERS_INDICATOR';
415 PON_AUCTION_PKG.GET_DEFAULT_TIERS_INDICATOR( p_contract_type => P_CONTRACT_TYPE,
416 p_price_breaks_enabled => l_price_breaks_flag,
417 p_qty_price_tiers_enabled => l_qty_price_tier_enabled_flag,
418 p_doctype_id => v_doctype_id,
419 x_price_tiers_indicator => v_price_tiers_indicator);
420 --<Sol Project>
421 v_debug_status := 'UDA_TEMPLATE_ID';
422 l_uda_template_id := NULL;
423 l_uda_template_date := NULL;
424 l_sol_type := NULL;
425 IF PON_CLM_UTIL_PKG.IS_DOCUMENT_FEDERAL(p_doc_type_id => v_doctype_id) = 1 THEN
426 logme('pon.plsql.pon_auction_interface_pkg.Create_Draft_Negotiation',
427 'Retrieve Uda Template Id for Header');
428 l_uda_template_id := po_uda_data_util.get_template_id ( 'SOURCING',
429 'SOLICITATION', NULL, 'HEADER', sysdate, l_return_status, l_err_msg);
430 logme('pon.plsql.pon_auction_interface_pkg.Create_Draft_Negotiation',
431 'Return from po_uda_data_util.get_template_id '||l_return_status);
432 IF ( l_return_status = 'E' ) THEN
433 P_ERROR_CODE := 'CREATE_DRAFT:GET_HEADER_UDA_TEMPLATE_ID';
434 P_RESULT := l_return_status;
435 P_ERROR_MESSAGE := l_err_msg;
436 logme(l_log_module, p_error_code ||' - '||p_error_message);
437 RETURN;
438 END IF;
439 logme('pon.plsql.pon_auction_interface_pkg.Create_Draft_Negotiation',
440 'Got uda_template_id for Header'||l_uda_template_id);
441 l_uda_template_date := sysdate;
442 l_sol_type := 'RFQ';
443 END IF;
444 --<Sol Project End>
445 BEGIN
446 SELECT STANDARD_FORM,
447 DOCUMENT_FORMAT
448 INTO l_standard_form,
449 l_document_format
450 FROM PO_PRINT_FORM_FORMATS
451 WHERE FORM_SOURCE='PON'
452 AND DEFAULT_FLAG ='Y'
453 AND DOCUMENT_TYPE='PO_SOL_STD_FORM'
454 AND SYSDATE < NVL(INACTIVE_DATE,SYSDATE + 1);
455 EXCEPTION
456 WHEN Too_Many_Rows THEN
457 l_standard_form :=NULL;
458 l_document_format:=NULL;
459 WHEN No_Data_Found THEN
460 l_standard_form :=NULL;
461 l_document_format:=NULL;
462 END;
463 logme('pon.plsql.PON_auction_interface_pkg.Create_Draft_Negotiation',
464 'Inserting into pon_auction_headers_all');
465 v_debug_status := 'INSERT-PAH';
466 INSERT
467 INTO PON_AUCTION_HEADERS_ALL
468 (
469 AUCTION_HEADER_ID,
470 DOCUMENT_NUMBER,
471 AUCTION_HEADER_ID_ORIG_AMEND,
472 AUCTION_HEADER_ID_ORIG_ROUND,
473 AMENDMENT_NUMBER,
474 AUCTION_TITLE,
475 AUCTION_STATUS,
476 AWARD_STATUS,
477 AUCTION_TYPE,
478 CONTRACT_TYPE,
479 TRADING_PARTNER_NAME,
480 TRADING_PARTNER_NAME_UPPER,
481 TRADING_PARTNER_ID,
482 LANGUAGE_CODE,
483 BID_VISIBILITY_CODE,
484 ATTACHMENT_FLAG,
485 CREATION_DATE,
486 CREATED_BY,
487 LAST_UPDATE_DATE,
488 LAST_UPDATED_BY,
489 AUCTION_ORIGINATION_CODE,
490 DOCTYPE_ID,
491 ORG_ID,
492 BUYER_ID,
493 MANUAL_EDIT_FLAG,
494 SHARE_AWARD_DECISION,
495 APPROVAL_STATUS,
496 GLOBAL_AGREEMENT_FLAG,
497 ATTRIBUTE_LINE_NUMBER,
498 HAS_HDR_ATTR_FLAG,
499 HAS_ITEMS_FLAG,
500 STYLE_ID,
501 PO_STYLE_ID,
502 PRICE_BREAK_RESPONSE,
503 NUMBER_OF_LINES,
504 ADVANCE_NEGOTIABLE_FLAG,
505 RECOUPMENT_NEGOTIABLE_FLAG,
506 PROGRESS_PYMT_NEGOTIABLE_FLAG,
507 RETAINAGE_NEGOTIABLE_FLAG,
508 MAX_RETAINAGE_NEGOTIABLE_FLAG,
509 SUPPLIER_ENTERABLE_PYMT_FLAG,
510 PROGRESS_PAYMENT_TYPE,
511 LINE_ATTRIBUTE_ENABLED_FLAG,
512 LINE_MAS_ENABLED_FLAG,
513 PRICE_ELEMENT_ENABLED_FLAG,
514 RFI_LINE_ENABLED_FLAG,
515 LOT_ENABLED_FLAG,
516 GROUP_ENABLED_FLAG,
517 LARGE_NEG_ENABLED_FLAG,
518 HDR_ATTRIBUTE_ENABLED_FLAG,
519 NEG_TEAM_ENABLED_FLAG,
520 PROXY_BIDDING_ENABLED_FLAG,
521 POWER_BIDDING_ENABLED_FLAG,
522 AUTO_EXTEND_ENABLED_FLAG,
523 TEAM_SCORING_ENABLED_FLAG,
524 PRICE_TIERS_INDICATOR,
525 QTY_PRICE_TIERS_ENABLED_FLAG,
526 -- Begin Supplier Management: Bug 14087712
527 SUPP_REG_QUAL_FLAG,
528 SUPP_EVAL_FLAG,
529 HIDE_TERMS_FLAG,
530 HIDE_ABSTRACT_FORMS_FLAG,
531 HIDE_ATTACHMENTS_FLAG,
532 INTERNAL_EVAL_FLAG,
533 HDR_SUPP_ATTR_ENABLED_FLAG,
534 INTGR_HDR_ATTR_FLAG,
535 INTGR_HDR_ATTACH_FLAG,
536 LINE_SUPP_ATTR_ENABLED_FLAG,
537 ITEM_SUPP_ATTR_ENABLED_FLAG,
538 INTGR_CAT_LINE_ATTR_FLAG,
539 INTGR_ITEM_LINE_ATTR_FLAG,
540 INTGR_CAT_LINE_ASL_FLAG,
541 INTERNAL_ONLY_FLAG,
542 -- End Supplier Management: Bug 14087712
543 UDA_TEMPLATE_ID, --<Sol Project> uda_template_id
544 UDA_TEMPLATE_DATE,
545 SOLICITATION_TYPE,
546 REVISION,
547 STANDARD_FORM,
548 DOCUMENT_FORMAT,
549 FAIR_OPP_NOTICE_FLAG
550 )
551 VALUES
552 (
553 pon_auction_headers_all_s.nextval, -- AUCTION_HEADER_ID
554 pon_auction_headers_all_s.currval, -- DOCUMENT_NUMBER
555 pon_auction_headers_all_s.currval, -- AUCTION_HEADER_ID_ORIG_AMEND,
556 pon_auction_headers_all_s.currval, -- AUCTION_HEADER_ID_ORIG_ROUND,
557 0, -- AMENDMENT_NUMBER
558 P_DOCUMENT_TITLE, -- AUCTION_TITLE
559 'DRAFT', -- AUCTION_STATUS
560 'NO', -- AWARD_STATUS
561 v_transaction_type, -- AUCTION_TYPE
562 P_CONTRACT_TYPE, -- CONTRACT_TYPE
563 v_site_name, -- TRADING_PARTNER_NAME
564 upper(v_site_name), -- TRADING_PARTNER_NAME_UPPER
565 v_site_id, -- TRADING_PARTNER_ID
566 userenv('LANG'), -- LANGUAGE_CODE
567 'OPEN_BIDDING', -- BID_VISIBILITY_CODE
568 'N', -- ATTACHMENT_FLAG
569 sysdate, -- CREATION_DATE
570 P_BUYER_ID, -- CREATED_BY
571 sysdate, -- LAST_UPDATE_DATE
572 P_BUYER_ID, -- LAST_UPDATED_BY
573 P_ORIGINATION_CODE, -- AUCTION_ORIGINATION_CODE
574 v_doctype_id, -- DOCTYPE_ID
575 P_ORG_ID, -- ORG_ID
576 P_BUYER_ID, -- BUYER_ID
577 'N', -- MANUAL_EDIT_FLAG
578 'N', -- SHARE_AWARD_DECISION
579 'NOT_REQUIRED', -- APPROVAL_STATUS
580 'N', -- GLOBAL_AGREEMENT_FLAG
581 -1, -- ATTRIBUTE_LINE_NUMBER
582 'N', -- HAS_HDR_ATTR_FLAG
583 'Y', -- HAS_ITEMS_FLAG
584 P_NEG_STYLE_ID, -- STYLE_ID
585 P_PO_STYLE_ID, -- PO_STYLE_ID
586 l_price_break_response, -- PRICE_BREAK_RESPONSE,
587 0, -- NUMBER_OF_LINES
588 'N', --ADVANCE_NEGOTIABLE_FLAG
589 'N', --RECOUPMENT_NEGOTIABLE_FLAG
590 'N', --PROGRESS_PYMT_NEGOTIABLE_FLAG
591 'N', --RETAINAGE_NEGOTIABLE_FLAG
592 'N', --MAX_RETAINAGE_NEGOTIABLE_FLAG
593 'N', --SUPPLIER_ENTERABLE_PYMT_FLAG
594 g_progress_payment_type, --PROGRESS_PAYMENT_TYPE
595 l_line_attribute_enabled_flag,
596 l_line_mas_enabled_flag,
597 l_price_element_enabled_flag,
598 l_rfi_line_enabled_flag,
599 l_lot_enabled_flag,
600 l_group_enabled_flag,
601 l_large_neg_enabled_flag,
602 l_hdr_attribute_enabled_flag,
603 l_neg_team_enabled_flag,
604 l_proxy_bidding_enabled_flag,
605 l_power_bidding_enabled_flag,
606 l_auto_extend_enabled_flag,
607 l_team_scoring_enabled_flag,
608 v_price_tiers_indicator,
609 l_qty_price_tier_enabled_flag,
610 -- Begin Supplier Management: Bug 14087712
611 l_supp_reg_qual_flag,
612 l_supp_eval_flag,
613 l_hide_terms_flag,
614 l_hide_abstract_forms_flag,
615 l_hide_attachments_flag,
616 l_internal_eval_flag,
617 l_hdr_supp_attr_enabled_flag,
618 l_intgr_hdr_attr_flag,
619 l_intgr_hdr_attach_flag,
620 l_line_supp_attr_enabled_flag,
621 l_item_supp_attr_enabled_flag,
622 l_intgr_cat_line_attr_flag,
623 l_intgr_item_line_attr_flag,
624 l_intgr_cat_line_asl_flag,
625 'N',
626 -- End Supplier Management: Bug 14087712
627 l_uda_template_id, --<Sol Project> uda_template_id
628 l_uda_template_date,
629 l_sol_type,
630 0,
631 l_standard_form,
632 l_document_format,
633 p_fair_opp_notice_flag
634 )
635 RETURNING auction_header_id
636 INTO P_DOCUMENT_NUMBER;
637
638 logme
639 (
640 'pon.plsql.PON_auction_interface_pkg.Create_Draft_Negotiation',
641 'Inserted in PAH : auction_header_id : '||P_DOCUMENT_NUMBER
642 )
643 ;
644 IF PON_CLM_UTIL_PKG.IS_DOCUMENT_FEDERAL ( p_doc_type_id => v_doctype_id)
645 = 1 THEN
646 logme('pon.plsql.pon_auction_interface_pkg.Create_Draft_Negotiation',
647 'Default Document Number') ;
648 PO_DOC_NUMBERING_PKG.DEFAULT_DOC_NUMBER_UDA
649 (
650 p_doc_header_id => p_document_number,
651 p_draft_id => -1,
652 p_template_id => l_uda_template_id,
653 p_context_usage => 'Base Document',
654 p_source_org_owned => 'NA',
655 p_caller => 'SOL',
656 x_doc_number => clm_doc_number,
657 x_return_status => l_return_status
658 )
659 ;
660 logme
661 (
662 'pon.plsql.pon_auction_interface_pkg.Create_Draft_Negotiation', 'Defaulted Document Number '||
663 clm_doc_number||' status '||l_return_status
664 )
665 ;
666 IF
667 (
668 l_return_status = 'E'
669 )
670 THEN
671 P_ERROR_CODE := 'CREATE_DRAFT:DEFAULT_DOC_NUMBER_UDA';
672 P_RESULT := l_return_status;
673 P_ERROR_MESSAGE := 'Error in PO_DOC_NUMBERING_PKG.DEFAULT_DOC_NUMBER_UDA';
674 RETURN;
675 END IF;
676 /* Bug 9645160 - updated clm_document_number back in PON_AUCTION_HEADERS_ALL table. */
677 UPDATE pon_auction_headers_all
678 SET DOCUMENT_NUMBER = clm_doc_number
679 WHERE AUCTION_HEADER_ID = p_document_number;
680 END IF;
681 -- price break line setting
682 PON_AUCTION_PKG.get_default_pb_settings (p_document_number, g_price_break_type, g_price_break_neg_flag);
683 --<Sol Project>
684 --Copy Uda Data from Req Header to Sol Header
685 END IF; -- (P_DOCUMENT_NUMBER IS NULL) THEN
686 --<Sol Project End>
687 -- Construct URL to Edit Document
688 v_debug_status := 'DOC_URL';
689 P_DOCUMENT_URL := '&' || 'auctionID=' || P_DOCUMENT_NUMBER;
690 P_RESULT := success;
691 P_ERROR_CODE := NULL;
692 P_ERROR_MESSAGE := NULL;
693 logme('pon.plsql.pon_auction_interface_pkg.Create_Draft_Negotiation', 'Return from Create_Draft_Negotiation');
694 EXCEPTION
695 WHEN OTHERS THEN
696 fnd_message.set_name('PON', 'PON_AUC_PLSQL_ERR');
697 fnd_message.set_token('PACKAGE','pon_auction_interface_pkg');
698 fnd_message.set_token('PROCEDURE','Create_Draft_Negotiation');
699 fnd_message.set_token('ERROR',v_debug_status || ' [' || SQLERRM || ']');
700 APP_EXCEPTION.RAISE_EXCEPTION;
701 END;
702 /*======================================================================
703 PROCEDURE : Add_Negotiation_Line
704 PARAMETERS:
705 P_DOCUMENT_NUMBER IN Document number to add line
706 P_CONTRACT_TYPE IN 'STANDARD' or 'BLANKET'
707 P_ORIGINATION_CODE IN 'REQUISITION' or caller product name
708 P_ORG_ID IN Organization id of creator
709 P_BUYER_ID IN FND_USER_ID of creator
710 P_GROUPING_TYPE IN 'DEFAULT' or 'NONE' grouping
711 P_REQUISITION_HEADER_ID IN Requisition header
712 P_REQUISITION_NUMBER IN Requisition header formatted for display
713 P_REQUISITION_LINE_ID IN Requisition line
714 P_LINE_TYPE_ID IN Line type
715 P_CATEGORY_ID IN Line category
716 P_ITEM_DESCRIPTION IN Item Desription
717 P_ITEM_ID IN Item Id
718 P_ITEM_NUMBER IN Item Number formatted for display
719 P_ITEM_REVISION IN Item Revision
720 P_UOM_CODE IN UOM_CODE from MTL_UNITS_OF_MEASURE
721 P_QUANTITY IN Quantity
722 P_NEED_BY_DATE IN Item Need-By
723 P_SHIP_TO_LOCATION_ID IN Ship To
724 P_NOTE_TO_VENDOR IN Note to Supplier
725 P_PRICE IN Start price for line
726 P_JOB_ID IN Job_id for the services job
727 P_JOB_DETAILS IN job details if any
728 P_PO_AGREED_AMOUNT IN PO Agreed Amount
729 P_HAS_PRICE_DIFF_FLAG IN If the line has any price differentials flag
730 P_LINE_NUMBER IN OUT Line number to which the demand was added
731 P_RESULT OUT One of (error, success)
732 P_ERROR_CODE OUT Internal Error Code
733 P_ERROR_MESSAGE OUT Displayable error
734 COMMENT : Creates a line in a draft auction
735 ======================================================================*/
736 PROCEDURE Add_Negotiation_Line
737 (
738 P_DOCUMENT_NUMBER IN NUMBER,
739 P_CONTRACT_TYPE IN VARCHAR2,
740 P_ORIGINATION_CODE IN VARCHAR2,
741 P_ORG_ID IN NUMBER,
742 P_BUYER_ID IN NUMBER,
743 P_GROUPING_TYPE IN VARCHAR2,
744 P_REQUISITION_HEADER_ID IN NUMBER,
745 P_REQUISITION_NUMBER IN VARCHAR2,
746 P_REQUISITION_LINE_ID IN NUMBER,
747 P_LINE_TYPE_ID IN NUMBER,
748 P_CATEGORY_ID IN NUMBER,
749 P_ITEM_DESCRIPTION IN VARCHAR2,
750 P_ITEM_ID IN NUMBER,
751 P_ITEM_NUMBER IN VARCHAR2,
752 P_ITEM_REVISION IN VARCHAR2,
753 P_UOM_CODE IN VARCHAR2,
754 P_QUANTITY IN NUMBER,
755 P_NEED_BY_DATE IN DATE,
756 P_SHIP_TO_LOCATION_ID IN NUMBER,
757 P_NOTE_TO_VENDOR IN VARCHAR2,
758 P_PRICE IN NUMBER,
759 P_JOB_ID IN NUMBER, -- ADDED FOR SERVICES PROCUREMENT PROJECT
760 P_JOB_DETAILS IN VARCHAR2,-- ADDED FOR SERVICES PROCUREMENT PROJECT
761 P_PO_AGREED_AMOUNT IN NUMBER, -- ADDED FOR SERVICES PROCUREMENT PROJECT
762 P_HAS_PRICE_DIFF_FLAG IN VARCHAR2,-- ADDED FOR SERVICES PROCUREMENT PROJECT
763 P_LINE_NUMBER IN OUT NOCOPY NUMBER,
764 P_RESULT OUT NOCOPY NUMBER,
765 P_ERROR_CODE OUT NOCOPY VARCHAR2,
766 P_ERROR_MESSAGE OUT NOCOPY VARCHAR2)
767 IS
768 BEGIN
769 Add_Negotiation_Line( P_DOCUMENT_NUMBER,
770 P_CONTRACT_TYPE,
771 P_ORIGINATION_CODE,
772 P_ORG_ID, P_BUYER_ID ,
773 P_GROUPING_TYPE,
774 P_REQUISITION_HEADER_ID,
775 P_REQUISITION_NUMBER,
776 P_REQUISITION_LINE_ID,
777 P_LINE_TYPE_ID,
778 P_CATEGORY_ID,
779 P_ITEM_DESCRIPTION,
780 P_ITEM_ID,
781 P_ITEM_NUMBER,
782 P_ITEM_REVISION,
783 P_UOM_CODE,
784 P_QUANTITY ,
785 P_NEED_BY_DATE,
786 P_SHIP_TO_LOCATION_ID,
787 P_NOTE_TO_VENDOR,
788 P_PRICE,
789 P_JOB_ID, -- ADDED FOR SERVICES PROCUREMENT PROJECT
790 P_JOB_DETAILS, -- ADDED FOR SERVICES PROCUREMENT PROJECT
791 P_PO_AGREED_AMOUNT, -- ADDED FOR SERVICES PROCUREMENT PROJECT
792 P_HAS_PRICE_DIFF_FLAG, -- ADDED FOR SERVICES PROCUREMENT PROJECT
793 NULL, NULL, NULL, NULL, NULL, NULL,
794 NULL, NULL, NULL, NULL, NULL, NULL, NULL, --uda_template_id
795 NULL, --req_line_temp_id
796 P_LINE_NUMBER, NULL, P_RESULT, P_ERROR_CODE, P_ERROR_MESSAGE);
797 END ;
798 /*======================================================================
799 PROCEDURE : Add_Negotiation_Line (OverLoaded for CLIN SLIN PROJECT)
800 PARAMETERS:
801 P_DOCUMENT_NUMBER IN Document number to add line
802 P_CONTRACT_TYPE IN 'STANDARD' or 'BLANKET'
803 P_ORIGINATION_CODE IN 'REQUISITION' or caller product name
804 P_ORG_ID IN Organization id of creator
805 P_BUYER_ID IN FND_USER_ID of creator
806 P_GROUPING_TYPE IN 'DEFAULT' or 'NONE' grouping
807 P_REQUISITION_HEADER_ID IN Requisition header
808 P_REQUISITION_NUMBER IN Requisition header formatted for display
809 P_REQUISITION_LINE_ID IN Requisition line
810 P_LINE_TYPE_ID IN Line type
811 P_CATEGORY_ID IN Line category
812 P_ITEM_DESCRIPTION IN Item Desription
813 P_ITEM_ID IN Item Id
814 P_ITEM_NUMBER IN Item Number formatted for display
815 P_ITEM_REVISION IN Item Revision
816 P_UOM_CODE IN UOM_CODE from MTL_UNITS_OF_MEASURE
817 P_QUANTITY IN Quantity
818 P_NEED_BY_DATE IN Item Need-By
819 P_SHIP_TO_LOCATION_ID IN Ship To
820 P_NOTE_TO_VENDOR IN Note to Supplier
821 P_PRICE IN Start price for line
822 P_JOB_ID IN Job_id for the services job
823 P_JOB_DETAILS IN job details if any
824 P_PO_AGREED_AMOUNT IN PO Agreed Amount
825 P_HAS_PRICE_DIFF_FLAG IN If the line has any price differentials flag
826 P_LINE_NUM_DISPLAY IN CLM line number
827 P_GROUP_LINE_ID IN Parent Clin Number
828 P_CLM_INFO_FLAG in Is info Line
829 P_CLM_OPTION_INDICATOR In Option indicator
830 P_CLM_OPTION_NUM In option number
831 P_CLM_OPTION_FROM_DATE IN Option frm date
832 P_CLM_OPTION_TO_DATE in Option to date
833 P_CLM_FUNDED_FLAG in is funded line
834 P_CLM_BASE_LINE_NUM in option base line number
835 P_CLM_CONTRACT_TYPE IN CLM Contract Type
836 P_CLM_COST_CONSTRAINT IN CLM Cost Constraint
837 P_CLM_IDC_TYPE IN CLM IDC Type
838 P_UDA_TEMPLATE_ID IN Line UDA Template Id
839 P_REQ_LINE_TEMP_ID IN Requisition Line Template Id
840 P_LINE_NUMBER IN OUT Line number to which the demand was added
841 P_RESULT OUT One of (error, success)
842 P_ERROR_CODE OUT Internal Error Code
843 P_ERROR_MESSAGE OUT Displayable error
844 COMMENT : Creates a line in a draft auction
845 ======================================================================*/
846 PROCEDURE Add_Negotiation_Line
847 (
848 P_DOCUMENT_NUMBER IN NUMBER,
849 P_CONTRACT_TYPE IN VARCHAR2,
850 P_ORIGINATION_CODE IN VARCHAR2,
851 P_ORG_ID IN NUMBER,
852 P_BUYER_ID IN NUMBER,
853 P_GROUPING_TYPE IN VARCHAR2,
854 P_REQUISITION_HEADER_ID IN NUMBER,
855 P_REQUISITION_NUMBER IN VARCHAR2,
856 P_REQUISITION_LINE_ID IN NUMBER,
857 P_LINE_TYPE_ID IN NUMBER,
858 P_CATEGORY_ID IN NUMBER,
859 P_ITEM_DESCRIPTION IN VARCHAR2,
860 P_ITEM_ID IN NUMBER,
861 P_ITEM_NUMBER IN VARCHAR2,
862 P_ITEM_REVISION IN VARCHAR2,
863 P_UOM_CODE IN VARCHAR2,
864 P_QUANTITY IN NUMBER,
865 P_NEED_BY_DATE IN DATE,
866 P_SHIP_TO_LOCATION_ID IN NUMBER,
867 P_NOTE_TO_VENDOR IN VARCHAR2,
868 P_PRICE IN NUMBER,
869 P_JOB_ID IN NUMBER, -- ADDED FOR SERVICES PROCUREMENT PROJECT
870 P_JOB_DETAILS IN VARCHAR2,-- ADDED FOR SERVICES PROCUREMENT PROJECT
871 P_PO_AGREED_AMOUNT IN NUMBER, -- ADDED FOR SERVICES PROCUREMENT PROJECT
872 P_HAS_PRICE_DIFF_FLAG IN VARCHAR2,-- ADDED FOR SERVICES PROCUREMENT PROJECT
873 P_LINE_NUM_DISPLAY IN VARCHAR2,
874 P_GROUP_LINE_ID IN NUMBER,
875 P_CLM_INFO_FLAG IN VARCHAR2,
876 P_CLM_OPTION_INDICATOR IN VARCHAR2,
877 P_CLM_OPTION_NUM IN NUMBER,
878 P_CLM_OPTION_FROM_DATE IN DATE,
879 P_CLM_OPTION_TO_DATE IN DATE,
880 P_CLM_FUNDED_FLAG IN VARCHAR2,
881 P_CLM_BASE_LINE_NUM IN NUMBER,
882 P_CLM_CONTRACT_TYPE IN VARCHAR2,
883 P_CLM_COST_CONSTRAINT IN VARCHAR2,
884 P_CLM_IDC_TYPE IN VARCHAR2,
885 P_UDA_TEMPLATE_ID IN NUMBER,--<Sol Project>
886 P_REQ_LINE_TEMP_ID IN NUMBER,--<Sol Project>
887 P_LINE_NUMBER IN OUT NOCOPY NUMBER,
888 P_NEG_LINE_NUM_DISP IN VARCHAR2 DEFAULT NULL,
889 P_RESULT OUT NOCOPY NUMBER,
890 P_ERROR_CODE OUT NOCOPY VARCHAR2,
891 P_ERROR_MESSAGE OUT NOCOPY VARCHAR2)
892 IS
893 l_log_module VARCHAR2(100) := 'ADD_NEGOTIATION_LINE';
894 l_is_federal NUMBER;
895 v_debug_status VARCHAR2(100);
896 v_was_grouped VARCHAR2(1);
897 v_header_attach_count NUMBER;
898 v_item_attach_count NUMBER;
899 v_site_id pon_auction_headers_all.trading_partner_id%TYPE :=NULL;
900 v_org_id pon_auction_headers_all.org_id%TYPE;
901 v_seq_num fnd_attached_documents.seq_num%TYPE;
902 v_price pon_auction_item_prices_all.current_price%TYPE;
903 v_category_name pon_auction_item_prices_all.category_name%TYPE;
904 v_ip_category_id pon_auction_item_prices_all.ip_category_id%TYPE;
905 v_quantity pon_auction_item_prices_all.quantity%TYPE;
906 v_uom_code pon_auction_item_prices_all.uom_code%TYPE;
907 v_has_attachments pon_auction_item_prices_all.attachment_flag%TYPE:= 'N';
908 v_multi_org fnd_product_groups.multi_org_flag%TYPE := 'Y';
909 v_order_type_lookup_code po_line_types_b.order_type_lookup_code%TYPE;
910 v_purchase_basis po_line_types_b.purchase_basis%TYPE;
911 v_att_category_id fnd_document_categories.category_id%TYPE;
912 v_contract_type VARCHAR2(25);
913 v_service_based_line VARCHAR2(1);
914 v_from_ip_catalog VARCHAR2(1);
915 l_line_exist VARCHAR2(1) := 'N';
916 l_line_valid VARCHAR2(1) := 'Y';
917 v_blanket_po_header_id po_requisition_lines_all.blanket_po_header_id%TYPE;
918 v_blanket_po_line_num po_requisition_lines_all.blanket_po_line_num%TYPE;
919 v_msg_count NUMBER; --<Sol Project>
920 v_return_status VARCHAR2(1); --<Sol Project>
921 v_doctype_id pon_auction_headers_all.doctype_id%TYPE; --<Sol Project>
922 need_by_dt pon_auction_item_prices_all.need_by_date%TYPE;
923 pop_start_dt pon_auction_item_prices_all.need_by_date%TYPE;
924 pop_end_dt pon_auction_item_prices_all.need_by_date%TYPE;
925 --CLM Bug : 10096343
926 l_progress_payment_type pon_auction_headers_all.progress_payment_type%TYPE;
927 l_comp_pricing_grp_id po_uda_ag_template_usages.ATTRIBUTE_GROUP_ID%TYPE; -- bug 13571062
928 BEGIN
929 logme(l_log_module, 'Entered add_negotiation_line');
930 logme(l_log_module, 'P_DOCUMENT_NUMBER '||P_DOCUMENT_NUMBER);
931 logme(l_log_module, 'P_CONTRACT_TYPE '||P_CONTRACT_TYPE);
932 logme(l_log_module, 'P_ORIGINATION_CODE '||P_ORIGINATION_CODE);
933 logme(l_log_module, 'P_ORG_ID '||P_ORG_ID );
934 logme(l_log_module, 'P_BUYER_ID '||P_BUYER_ID);
935 logme(l_log_module, 'P_GROUPING_TYPE '||P_GROUPING_TYPE);
936 logme(l_log_module, 'P_REQUISITION_HEADER_ID '||P_REQUISITION_HEADER_ID);
937 logme(l_log_module, 'P_REQUISITION_NUMBER '||P_REQUISITION_NUMBER);
938 logme(l_log_module, 'P_REQUISITION_LINE_ID '||P_REQUISITION_LINE_ID);
939 logme(l_log_module, 'P_LINE_TYPE_ID '||P_LINE_TYPE_ID );
940 logme(l_log_module, 'P_CATEGORY_ID '||P_CATEGORY_ID);
941 logme(l_log_module, 'P_ITEM_DESCRIPTION '||P_ITEM_DESCRIPTION);
942 logme(l_log_module, 'P_ITEM_ID '||P_ITEM_ID );
943 logme(l_log_module, 'P_ITEM_NUMBER '||P_ITEM_NUMBER);
944 logme(l_log_module, 'P_ITEM_REVISION '||P_ITEM_REVISION);
945 logme(l_log_module, 'P_UOM_CODE '||P_UOM_CODE);
946 logme(l_log_module, 'P_QUANTITY '||P_QUANTITY);
947 logme(l_log_module, 'P_NEED_BY_DATE '||P_NEED_BY_DATE);
948 logme(l_log_module, 'P_SHIP_TO_LOCATION_ID '||P_SHIP_TO_LOCATION_ID);
949 logme(l_log_module, 'P_NOTE_TO_VENDOR '||P_NOTE_TO_VENDOR);
950 logme(l_log_module, 'P_PRICE '||P_PRICE);
951 logme(l_log_module, 'P_JOB_ID '||P_JOB_ID);
952 logme(l_log_module, 'P_JOB_DETAILS '||P_JOB_DETAILS);
953 logme(l_log_module, 'P_PO_AGREED_AMOUNT '||P_PO_AGREED_AMOUNT);
954 logme(l_log_module, 'P_HAS_PRICE_DIFF_FLAG '||P_HAS_PRICE_DIFF_FLAG);
955 logme(l_log_module, 'P_LINE_NUM_DISPLAY '||P_LINE_NUM_DISPLAY );
956 logme(l_log_module, 'P_GROUP_LINE_ID '||P_GROUP_LINE_ID );
957 logme(l_log_module, 'P_CLM_INFO_FLAG '||P_CLM_INFO_FLAG);
958 logme(l_log_module, 'P_CLM_OPTION_INDICATOR '||P_CLM_OPTION_INDICATOR);
959 logme(l_log_module, 'P_CLM_OPTION_NUM '||P_CLM_OPTION_NUM);
960 logme(l_log_module, 'P_CLM_OPTION_FROM_DATE '||P_CLM_OPTION_FROM_DATE);
961 logme(l_log_module, 'P_CLM_OPTION_TO_DATE '||P_CLM_OPTION_TO_DATE);
962 logme(l_log_module, 'P_CLM_FUNDED_FLAG '||P_CLM_FUNDED_FLAG);
963 logme(l_log_module, 'P_CLM_BASE_LINE_NUM '||P_CLM_BASE_LINE_NUM);
964 logme(l_log_module, 'P_CLM_CONTRACT_TYPE '||P_CLM_CONTRACT_TYPE);
965 logme(l_log_module, 'P_CLM_COST_CONSTRAINT '||P_CLM_COST_CONSTRAINT);
966 logme(l_log_module, 'P_CLM_IDC_TYPE '||P_CLM_IDC_TYPE);
967 logme(l_log_module, 'P_UDA_TEMPLATE_ID '||P_UDA_TEMPLATE_ID);
968 logme(l_log_module, 'P_REQ_LINE_TEMP_ID '||P_REQ_LINE_TEMP_ID);
969 logme(l_log_module, 'P_LINE_NUMBER '||P_LINE_NUMBER);
970 logme(l_log_module, 'P_NEG_LINE_NUM_DISP '||P_NEG_LINE_NUM_DISP);
971 IF (P_DOCUMENT_NUMBER IS NULL) THEN
972 P_RESULT := error;
973 P_ERROR_CODE := 'ADD_NEG_LINE:DOCUMENT_NUMBER';
974 P_ERROR_MESSAGE := 'Please provide a DOCUMENT_NUMBER';
975 logme(l_log_module, p_error_code ||' - '||p_error_message);
976 RETURN;
977 END IF;
978 /* IF (P_CONTRACT_TYPE NOT IN ('BLANKET', 'STANDARD')) THEN
979 P_RESULT := error;
980 P_ERROR_CODE := 'ADD_NEG_LINE:INVALID_CONTRACT_TYPE';
981 P_ERROR_MESSAGE := 'Invalid Contract Type ' || P_CONTRACT_TYPE;
982 logme(l_log_module, p_error_code ||' - '||p_error_message);
983 RETURN;
984 END IF;*/
985 IF (P_ORIGINATION_CODE <> 'REQUISITION') THEN
986 P_RESULT := error;
987 P_ERROR_CODE := 'ADD_NEG_LINE:UNKNOWN_ORIGINATION';
988 P_ERROR_MESSAGE := 'Invalid Origination Code ' || P_ORIGINATION_CODE;
989 logme(l_log_module, p_error_code ||' - '||p_error_message);
990 RETURN;
991 END IF;
992 IF (P_BUYER_ID IS NULL) THEN
993 P_RESULT := error;
994 P_ERROR_CODE := 'ADD_NEG_LINE:NULL_BUYER_ID';
995 P_ERROR_MESSAGE := 'Please specify a BUYER_ID';
996 logme(l_log_module, p_error_code ||' - '||p_error_message);
997 RETURN;
998 END IF;
999 IF (P_CATEGORY_ID IS NULL AND NVL(P_CLM_INFO_FLAG,'N') <> 'Y') THEN -- Clin - Slin Change : check relaxed for CLM info lines
1000 P_RESULT := error;
1001 P_ERROR_CODE := 'ADD_NEG_LINE:NULL_CATEGORY_ID';
1002 P_ERROR_MESSAGE := 'Please specify a CATEGORY_ID';
1003 logme(l_log_module, p_error_code ||' - '||p_error_message);
1004 RETURN;
1005 END IF;
1006 --CLM Bug : 10096343
1007 IF P_ORIGINATION_CODE = 'REQUISITION' AND P_CLM_COST_CONSTRAINT IN ('NC','NSP') THEN
1008 BEGIN
1009 SELECT progress_payment_type
1010 INTO l_progress_payment_type
1011 FROM pon_auction_headers_all
1012 WHERE auction_header_id=P_DOCUMENT_NUMBER;
1013 END;
1014 END IF;
1015 -- Amount based line?
1016 v_debug_status := 'ORDER_TYPE_LOOKUP';
1017 IF (NVL(P_CLM_INFO_FLAG,'N') <> 'Y') THEN -- Clin - Slin Change : check relaxed for CLM info lines
1018 BEGIN
1019 SELECT order_type_lookup_code
1020 INTO v_order_type_lookup_code
1021 FROM po_line_types_b
1022 WHERE P_LINE_TYPE_ID = line_type_id;
1023 EXCEPTION
1024 WHEN no_data_found THEN
1025 P_RESULT := error;
1026 P_ERROR_CODE := 'ADD_NEG_LINE:ORDER_TYPE_LOOKUP';
1027 P_ERROR_MESSAGE := 'An order_type_lookup_code could not be found for line_type_id ' || P_LINE_TYPE_ID;
1028 logme(l_log_module, p_error_code ||' - '||p_error_message);
1029 RETURN;
1030 END;
1031 END IF;
1032 -- Get the purchase basis for this line type
1033 IF (NVL(P_CLM_INFO_FLAG,'N') <> 'Y') THEN -- Clin - Slin Change : check relaxed for CLM info lines
1034 BEGIN
1035 SELECT purchase_basis
1036 INTO v_purchase_basis
1037 FROM po_line_types_b
1038 WHERE P_LINE_TYPE_ID = line_type_id;
1039 EXCEPTION
1040 WHEN no_data_found THEN
1041 P_RESULT := error;
1042 P_ERROR_CODE := 'ADD_NEG_LINE:PURCHASE_BASIS_LOOKUP';
1043 P_ERROR_MESSAGE := 'A purchase basis could not be found for line_type_id ' || P_LINE_TYPE_ID;
1044 logme(l_log_module, p_error_code ||' - '||p_error_message);
1045 RETURN;
1046 END;
1047 END IF;
1048 IF ((P_UOM_CODE IS NULL) AND (v_order_type_lookup_code <> 'FIXED PRICE') AND
1049 NVL(P_CLM_INFO_FLAG,'N') <> 'Y') THEN -- Clin - Slin Change : check relaxed for CLM info lines
1050 P_RESULT := error;
1051 P_ERROR_CODE := 'ADD_NEG_LINE:NULL_UOM_CODE';
1052 P_ERROR_MESSAGE := 'Please specify a UOM_CODE';
1053 logme(l_log_module, p_error_code ||' - '||p_error_message);
1054 RETURN;
1055 END IF;
1056 IF (P_QUANTITY IS NULL AND ((v_purchase_basis <> 'SERVICES') AND (v_purchase_basis <> 'TEMP LABOR')) AND
1057 NVL(P_CLM_INFO_FLAG,'N') <> 'Y') THEN -- Clin - Slin Change : check relaxed for CLM info lines
1058 P_RESULT := error;
1059 P_ERROR_CODE := 'ADD_NEG_LINE:NULL_QUANTITY';
1060 P_ERROR_MESSAGE := 'Please specify a QUANTITY';
1061 logme(l_log_module, p_error_code ||' - '||p_error_message);
1062 RETURN;
1063 END IF;
1064 --check if only valid line types for complex work
1065 IF (NVL(P_CLM_INFO_FLAG,'N') <> 'Y') THEN -- Clin - Slin Change : check relaxed for CLM info lines
1066 IF g_progress_payment_type IN ('FINANCE', 'ACTUAL') THEN
1067 IF NOT ((V_PURCHASE_BASIS = 'GOODS' AND V_ORDER_TYPE_LOOKUP_CODE = 'QUANTITY') OR
1068 (V_PURCHASE_BASIS = 'SERVICES' AND V_ORDER_TYPE_LOOKUP_CODE = 'FIXED PRICE') ) THEN
1069 P_RESULT := error;
1070 P_ERROR_CODE := 'ADD_NEG_LINE:INVALID_LINE_TYPE';
1071 P_ERROR_MESSAGE := 'The line_type_id is invalid for Complex work Style' || P_LINE_TYPE_ID;
1072 logme(l_log_module, p_error_code ||' - '||p_error_message);
1073 RETURN;
1074 END IF;
1075 END IF;
1076 END IF;
1077 IF (NVL(P_CLM_INFO_FLAG,'N') <> 'Y') THEN -- Clin - Slin Change : check relaxed for CLM info lines
1078 IF (P_SHIP_TO_LOCATION_ID IS NULL) THEN
1079 P_RESULT := error;
1080 P_ERROR_CODE := 'ADD_NEG_LINE:NULL_SHIP_TO';
1081 P_ERROR_MESSAGE := 'Please specify a SHIP_TO';
1082 logme(l_log_module, p_error_code ||' - '||p_error_message);
1083 RETURN;
1084 END IF;
1085 END IF;
1086 -- Is this multiorg?
1087 v_debug_status := 'MULTIORG';
1088 BEGIN
1089 SELECT multi_org_flag INTO v_multi_org FROM fnd_product_groups;
1090 EXCEPTION
1091 WHEN no_data_found THEN
1092 P_RESULT := error;
1093 P_ERROR_CODE := 'ADD_NEG_LINE:MULTI_ORG_QUERY';
1094 fnd_message.set_name('PON', 'PON_AUC_PLSQL_ERR');
1095 fnd_message.set_token('PACKAGE','pon_auction_interface_pkg');
1096 fnd_message.set_token('PROCEDURE','Add_Negotiation_Line');
1097 fnd_message.set_token('ERROR','Multi-Org Query Failed [' || SQLERRM || ']');
1098 fnd_message.retrieve(P_ERROR_MESSAGE);
1099 logme(l_log_module, p_error_code ||' - '||p_error_message);
1100 RETURN;
1101 END;
1102 IF (P_ORG_ID IS NULL AND v_multi_org = 'Y') THEN
1103 P_RESULT := error;
1104 P_ERROR_CODE := 'ADD_NEG_LINE:NULL_ORG_ID';
1105 P_ERROR_MESSAGE := 'Please specify an ORG_ID';
1106 logme(l_log_module, p_error_code ||' - '||p_error_message);
1107 RETURN;
1108 END IF;
1109 -- Does P_ORG_ID match that of the auction header?
1110 v_debug_status := 'ORG_ID_MATCH';
1111 logme( 'pon.plsql.pon_auction_interface_pkg.add_negotiation_line',
1112 'Get Org_Id ');
1113 BEGIN
1114 SELECT org_id,
1115 doctype_id, -- <Sol Project> Added doctype_id
1116 contract_type
1117 INTO v_org_id,
1118 v_doctype_id,
1119 v_contract_type
1120 FROM pon_auction_headers_all
1121 WHERE auction_header_id = P_DOCUMENT_NUMBER
1122 AND NVL(org_id, -9999) = NVL(P_ORG_ID, -9999);
1123 EXCEPTION
1124 WHEN no_data_found THEN
1125 P_RESULT := error;
1126 P_ERROR_CODE := 'ADD_NEG_LINE:ORG_ID_CONFLICT';
1127 P_ERROR_MESSAGE := 'You cannot add lines to another organization''s Negotiation';
1128 logme(l_log_module, p_error_code ||' - '||p_error_message);
1129 RETURN;
1130 END;
1131 l_is_federal := PON_CLM_UTIL_PKG.IS_DOCUMENT_FEDERAL(p_doc_type_id => v_doctype_id);
1132 logme('pon.plsql.pon_auction_interface_pkg.add_negotiation_line', 'Got Org_Id ');
1133 -- get category id for VENDOR attachments
1134 logme(l_log_module, 'v_CONTRACT_TYPE '||V_CONTRACT_TYPE);
1135 logme(l_log_module, 'l_is_federal '||l_is_federal);
1136 v_debug_status := 'ATTACHMENT_CATEGORY_ID';
1137 logme(l_log_module, v_debug_status);
1138 BEGIN
1139 SELECT category_id
1140 INTO v_att_category_id
1141 FROM fnd_document_categories
1142 WHERE upper(name) = 'VENDOR';
1143 EXCEPTION
1144 WHEN no_data_found THEN
1145 P_RESULT := error;
1146 P_ERROR_CODE := 'ADD_NEG_LINE:ATTACHMENT_CATEGORY_ID';
1147 P_ERROR_MESSAGE := 'The attachment category id for name=VENDOR could not be found';
1148 logme(l_log_module, p_error_code ||' - '||p_error_message);
1149 RETURN;
1150 END;
1151 -- Does this requisition line have any attachments?
1152 SELECT COUNT(*)
1153 INTO v_header_attach_count
1154 FROM fnd_attached_documents ad,
1155 fnd_documents doc
1156 WHERE ad.entity_name = 'REQ_HEADERS'
1157 AND ad.pk1_value = TO_CHAR(p_requisition_header_id)
1158 AND ad.document_id = doc.document_id
1159 AND doc.category_id = v_att_category_id;
1160 SELECT COUNT(*)
1161 INTO v_item_attach_count
1162 FROM fnd_attached_documents ad,
1163 fnd_documents doc
1164 WHERE ad.entity_name = 'REQ_LINES'
1165 AND ad.pk1_value = TO_CHAR(p_requisition_line_id)
1166 AND ad.document_id = doc.document_id
1167 AND doc.category_id = v_att_category_id;
1168 IF (v_header_attach_count > 0 OR v_item_attach_count > 0) THEN
1169 v_has_attachments := 'Y';
1170 END IF;
1171 -- Check to see if this is a services based line type
1172 -- ie one of - temp labor or fixed price services
1173 -- if it is then donot group the lines, since you cannot
1174 -- group services based line types. Even if the p_grouping_type
1175 -- is set to 'DEFAULT', overwrite it
1176 --Complex work- Requisitions should not be grouped if complex work neg
1177 -- Clin Slin Changes : These 2 checks will be automatically skipped for
1178 -- info Lines. This is because the v_purchase_basis
1179 -- and v_order_type_lookup_code will be null for info lines (due to skipping of checks above)
1180 v_service_based_line := 'N';
1181 IF ((v_order_type_lookup_code = 'FIXED PRICE') OR (v_purchase_basis = 'TEMP LABOR') OR
1182 (g_progress_payment_type IN('FINANCE', 'ACTUAL'))) THEN
1183 v_service_based_line := 'Y';
1184 p_line_number := NULL;
1185 -- Autocreate CLM 2: service lines are not grouped so line number is updated to NULL
1186 END IF;
1187 -- check to see if this line type is TEMP LABOR RATE or fixed price temp labor based
1188 -- in which case we want to update the global agreement flag at the
1189 -- header level, since temp labor line types can exist only on
1190 -- global agreements
1191 IF (v_purchase_basis = 'TEMP LABOR' AND v_contract_type = 'BLANKET' ) THEN
1192 UPDATE pon_auction_headers_all
1193 SET global_agreement_flag = 'Y'
1194 WHERE auction_header_id = p_document_number;
1195 END IF;
1196 -- Get the shopping category (ip_category_id) when creating a blanket line
1197 -- Two cases:
1198 -- 1) If the requisition is tied to a catalog, get the shopping category from the
1199 -- category line
1200 -- 2) Else use the po category to ip category mappings
1201 v_ip_category_id := NULL;
1202 v_from_ip_catalog := 'N';
1203 IF (v_contract_type = 'BLANKET') THEN
1204 SELECT blanket_po_header_id,
1205 blanket_po_line_num
1206 INTO v_blanket_po_header_id,
1207 v_blanket_po_line_num
1208 FROM po_requisition_lines_all
1209 WHERE requisition_header_id = p_requisition_header_id
1210 AND requisition_line_id = p_requisition_line_id;
1211 IF (v_blanket_po_header_id IS NOT NULL AND v_blanket_po_line_num IS NOT NULL) THEN
1212 -- get the ip category from the catalog
1213 v_from_ip_catalog := 'Y';
1214 SELECT ip_category_id
1215 INTO v_ip_category_id
1216 FROM po_lines_all
1217 WHERE po_header_id = v_blanket_po_header_id
1218 AND line_num = v_blanket_po_line_num;
1219 ELSE
1220 -- get the ip ccategory from the category mappings
1221 v_ip_category_id := PON_AUCTION_PKG.get_mapped_ip_category(p_category_id);
1222 IF (v_ip_category_id = -2) THEN
1223 v_ip_category_id := NULL;
1224 END IF;
1225 END IF;
1226 END IF;
1227 -- Insert or Update row in PON_AUCTION_ITEM_PRICES
1228 -- P_LINE_NUMBER := NULL;
1229 /****
1230 Clin Slin Changes : Grouping Logic Modifications
1231 1. For info lines p_category_id, p_ship_to_location_id and v_order_type_lookup_code would be null
1232 so need to place a nvl for them.
1233 2. For all lines we need to compare the info flag also, this flag will be null for the non clm line
1234 so need to place a nvl for info flag
1235 3. Skip Options from Grouping
1236 4. Skip Slins from grouping. Only Clins are to be merged
1237 ****/
1238 logme('pon.plsql.pon_auction_interface_pkg.add_negotiation_line',
1239 ' P_GROUPING_TYPE '|| p_grouping_type|| ' service based line '|| v_service_based_line ||
1240 ' p_group_line_id '||p_group_line_id|| ' option indicator '||p_clm_option_indicator);
1241
1242
1243 IF (P_GROUPING_TYPE = 'DEFAULT' AND v_service_based_line <> 'Y'
1244 AND NVL(P_GROUP_LINE_ID, -1) = -1) AND (NVL(p_clm_option_indicator, 'B') <> 'O') THEN
1245
1246
1247 v_debug_status := 'GROUPING_QUERY';
1248 logme('pon.plsql.pon_auction_interface_pkg.add_negotiation_line', v_debug_status);
1249
1250 IF (P_LINE_NUMBER IS NOT NULL AND P_LINE_NUMBER >0 ) THEN
1251 logme(l_log_module, 'Line exist check ');
1252 BEGIN
1253 SELECT 'Y'
1254 INTO l_line_exist
1255 FROM pon_auction_item_prices_all
1256 WHERE auction_header_id =p_document_number
1257 AND line_number = p_line_number;
1258 EXCEPTION
1259 WHEN OTHERS THEN
1260 P_LINE_NUMBER := NULL;
1261 END;
1262 logme('pon.plsql.pon_auction_interface_pkg.add_negotiation_line', 'l_line_exist '||l_line_exist);
1263 END IF;
1264
1265 IF l_line_exist = 'Y' THEN
1266 logme(l_log_module, 'Line valid test ');
1267 l_line_valid := 'N';
1268 IF (V_CONTRACT_TYPE = 'STANDARD') THEN
1269 BEGIN
1270 SELECT 'Y'
1271 INTO l_line_valid
1272 FROM pon_auction_item_prices_all
1273 WHERE auction_header_id = P_DOCUMENT_NUMBER
1274 AND line_number = P_LINE_NUMBER
1275 AND line_type_id = P_LINE_TYPE_ID
1276 AND NVL(p_item_id, -1) = NVL(item_id, -1)
1277 AND NVL(p_item_revision, -1) = NVL(item_revision, -1)
1278 -- AND nvl(p_item_description, 'NULL') = nvl(item_description, 'NULL')
1279 AND NVL(p_category_id, -1) = NVL(category_id , -1)
1280 AND NVL(p_ship_to_location_id, -1) = NVL(ship_to_location_id, -1)
1281 -- Ignore UOM code for amount based lines
1282 AND NVL(DECODE(v_order_type_lookup_code, 'AMOUNT', '1', p_uom_code),'NULL') =
1283 NVL(DECODE(v_order_type_lookup_code, 'AMOUNT', '1', uom_code),'NULL')
1284 --- adding info flag check form CLM : Clin Slin Changes
1285 AND NVL(P_CLM_INFO_FLAG, 'N') = 'N'
1286 AND NVL(clm_info_flag , 'N') = 'N'
1287 --- It should match only lines which are NOT Slins and Options
1288 AND NVL(clm_option_indicator, 'B') <> 'O'
1289 AND group_line_id IS NULL
1290 -- Bug 13701530
1291 -- Added the below condition so that only Autocreated Requisition Lines can be grouped
1292 AND line_origination_code = 'REQUISITION'
1293 AND rownum < 2;
1294 EXCEPTION
1295 WHEN OTHERS THEN
1296 NULL;
1297 END;
1298 ELSE
1299 BEGIN
1300 SELECT 'Y'
1301 INTO l_line_valid
1302 FROM pon_auction_item_prices_all paip,
1303 pon_backing_requisitions pbr,
1304 po_requisition_lines_all prl
1305 WHERE paip.auction_header_id = P_DOCUMENT_NUMBER
1306 AND paip.line_number = P_LINE_NUMBER
1307 AND paip.line_type_id = P_LINE_TYPE_ID
1308 AND NVL(p_item_id, -1) = NVL(paip.item_id, -1)
1309 AND NVL(p_item_revision, -1) = NVL(paip.item_revision, -1)
1310 -- AND nvl(p_item_description, 'NULL') = nvl(paip.item_description, 'NULL')
1311 AND NVL(p_category_id, -1) = NVL(paip.category_id, -1)
1312 AND NVL(v_ip_category_id, -1) = NVL(paip.ip_category_id, -1)
1313 -- Ignore UOM code for amount based lines
1314 AND NVL(DECODE(v_order_type_lookup_code, 'AMOUNT', '1', p_uom_code), 'NULL') =
1315 NVL(DECODE(v_order_type_lookup_code, 'AMOUNT', '1', paip.uom_code), 'NULL')
1316 --- adding info flag check form CLM : Clin Slin Changes
1317 AND NVL(P_CLM_INFO_FLAG, 'N') = 'N'
1318 AND NVL(paip.clm_info_flag , 'N') = 'N'
1319 AND paip.auction_header_id = pbr.auction_header_id
1320 AND paip.line_number = pbr.line_number
1321 AND pbr.requisition_header_id = prl.requisition_header_id
1322 AND pbr.requisition_line_id = prl.requisition_line_id
1323 AND NVL(prl.blanket_po_header_id, -1) = NVL(v_blanket_po_header_id, -1)
1324 AND NVL(prl.blanket_po_line_num, -1) = NVL(v_blanket_po_line_num, -1)
1325 --- It should match only lines which are NOT Slins and Options
1326 AND NVL(paip.clm_option_indicator, 'B') <> 'O'
1327 AND paip.group_line_id IS NULL
1328 -- Bug 13701530
1329 -- Added the below condition so that only Autocreated Requisition Lines can be grouped
1330 AND paip.line_origination_code = 'REQUISITION'
1331 AND rownum < 2;
1332 EXCEPTION
1333 WHEN OTHERS THEN
1334 NULL;
1335 END;
1336 END IF; --V_CONTRACT_TYPE =
1337 END IF; -- l_line_exist =
1338 logme('pon.plsql.pon_auction_interface_pkg.add_negotiation_line', 'l_line_valid '||l_line_valid);
1339
1340 IF l_line_valid = 'N' THEN
1341 p_line_number := NULL;
1342 END IF;
1343 logme('pon.plsql.pon_auction_interface_pkg.add_negotiation_line', 'p_line_number '||p_line_number);
1344
1345 IF (P_LINE_NUMBER IS NULL OR P_LINE_NUMBER = 0) THEN
1346 logme(l_log_module, 'Grouping Line search select ');
1347 IF (V_CONTRACT_TYPE = 'STANDARD') THEN
1348 SELECT MAX(line_number)
1349 INTO P_LINE_NUMBER
1350 FROM pon_auction_item_prices_all
1351 WHERE auction_header_id = P_DOCUMENT_NUMBER
1352 AND line_type_id = P_LINE_TYPE_ID
1353 AND NVL(p_item_id, -1) = NVL(item_id, -1)
1354 AND NVL(p_item_revision, -1) = NVL(item_revision, -1)
1355 AND NVL(p_item_description, 'NULL') = NVL(item_description, 'NULL')
1356 AND NVL(p_category_id, -1) = NVL(category_id , -1)
1357 AND NVL(p_ship_to_location_id, -1) = NVL(ship_to_location_id, -1)
1358 -- Ignore UOM code for amount based lines
1359 AND NVL(DECODE(v_order_type_lookup_code, 'AMOUNT', '1', p_uom_code),'NULL') =
1360 NVL(DECODE(v_order_type_lookup_code, 'AMOUNT', '1', uom_code),'NULL')
1361 --- adding info flag check form CLM : Clin Slin Changes
1362 AND NVL(P_CLM_INFO_FLAG, 'N') = 'N'
1363 AND NVL(clm_info_flag , 'N') = 'N'
1364 --- It should match only lines which are NOT Slins and Options
1365 AND NVL(clm_option_indicator, 'B') <> 'O'
1366 AND group_line_id IS NULL
1367 -- Bug 13701530
1368 -- Added the below condition so that only Autocreated Requisition Lines can be grouped
1369 AND line_origination_code = 'REQUISITION';
1370 ELSE
1371 SELECT MAX(paip.line_number)
1372 INTO P_LINE_NUMBER
1373 FROM pon_auction_item_prices_all paip,
1374 pon_backing_requisitions pbr,
1375 po_requisition_lines_all prl
1376 WHERE paip.auction_header_id = P_DOCUMENT_NUMBER
1377 AND paip.line_type_id = P_LINE_TYPE_ID
1378 AND NVL(p_item_id, -1) = NVL(paip.item_id, -1)
1379 AND NVL(p_item_revision, -1) = NVL(paip.item_revision, -1)
1380 AND NVL(p_item_description, 'NULL') = NVL(paip.item_description, 'NULL')
1381 AND NVL(p_category_id, -1) = NVL(paip.category_id, -1)
1382 AND NVL(v_ip_category_id, -1) = NVL(paip.ip_category_id, -1)
1383 -- Ignore UOM code for amount based lines
1384 AND NVL(DECODE(v_order_type_lookup_code, 'AMOUNT', '1', p_uom_code), 'NULL') =
1385 NVL(DECODE(v_order_type_lookup_code, 'AMOUNT', '1', paip.uom_code), 'NULL')
1386 --- adding info flag check form CLM : Clin Slin Changes
1387 AND NVL(P_CLM_INFO_FLAG, 'N') = 'N'
1388 AND NVL(paip.clm_info_flag , 'N') = 'N'
1389 AND paip.auction_header_id = pbr.auction_header_id
1390 AND paip.line_number = pbr.line_number
1391 AND pbr.requisition_header_id = prl.requisition_header_id
1392 AND pbr.requisition_line_id = prl.requisition_line_id
1393 AND NVL(prl.blanket_po_header_id, -1) = NVL(v_blanket_po_header_id, -1)
1394 AND NVL(prl.blanket_po_line_num, -1) = NVL(v_blanket_po_line_num, -1)
1395 --- It should match only lines which are NOT Slins and Options
1396 AND NVL(paip.clm_option_indicator, 'B') <> 'O'
1397 AND paip.group_line_id IS NULL
1398 -- Bug 13701530
1399 -- Added the below condition so that only Autocreated Requisition Lines can be grouped
1400 AND paip.line_origination_code = 'REQUISITION';
1401 END IF; --V_CONTRACT_TYPE =
1402 END IF; -- P_LINE_NUMBER IS NULL THEN
1403 logme('pon.plsql.pon_auction_interface_pkg.add_negotiation_line',
1404 'Grouping query result: p_line_number = '||p_line_number);
1405 ELSE
1406 P_LINE_NUMBER := NULL;
1407 END IF; -- (P_GROUPING_TYPE = 'DEFAULT' AND v_service_based_line <> 'Y' AND
1408
1409 -- Update if we're grouping
1410 IF (P_LINE_NUMBER IS NOT NULL AND P_LINE_NUMBER <> 0) THEN
1411 v_was_grouped := 'Y';
1412 IF (v_order_type_lookup_code = 'AMOUNT') THEN
1413 -- Update row in PON_AUCTION_ITEM_PRICES
1414 v_debug_status := 'UPDATE_PAIP_1';
1415 logme(l_log_module, v_debug_status);
1416 UPDATE pon_auction_item_prices_all
1417 SET requisition_number = 'MULTIPLE',
1418 -- problem: least() and greater() return NULL if any argument is NULL
1419 -- need_by_start := NULL if P_NEED_BY_DATE and need_by_start == NULL
1420 need_by_start_date = DECODE(V_CONTRACT_TYPE, 'BLANKET', to_date(NULL),
1421 least(NVL(need_by_start_date,P_NEED_BY_DATE), NVL(P_NEED_BY_DATE,need_by_start_date))),
1422 -- if P_NEED_BY_DATE is NULL, keep existing need_by_date
1423 need_by_date = DECODE(V_CONTRACT_TYPE, 'BLANKET', to_date(NULL),
1424 greatest(NVL(need_by_date,P_NEED_BY_DATE), NVL(P_NEED_BY_DATE,need_by_date))),
1425 attachment_flag = DECODE(attachment_flag, 'Y', 'Y', v_has_attachments),
1426 -- if P_PRICE is NULL, keep existing current_price
1427 current_price = NVL(current_price,0) + P_QUANTITY,
1428 clm_cost_constraint = DECODE(clm_cost_constraint,
1429 P_CLM_COST_CONSTRAINT, P_CLM_COST_CONSTRAINT, NULL),
1430 clm_contract_type = DECODE(NVL(clm_contract_type, 'NULL'),
1431 P_CLM_CONTRACT_TYPE, P_CLM_CONTRACT_TYPE, 'NULL', NULL, 'FP_FIRM')
1432 WHERE auction_header_id = P_DOCUMENT_NUMBER
1433 AND line_number = P_LINE_NUMBER;
1434 ELSIF ( v_order_type_lookup_code IS NULL) THEN
1435 -- Clin Slin Changes : Special case for info lines. We need to update only the req num field on the line.
1436 UPDATE pon_auction_item_prices_all
1437 SET requisition_number = 'MULTIPLE'
1438 WHERE auction_header_id = P_DOCUMENT_NUMBER
1439 AND line_number = P_LINE_NUMBER;
1440 ELSE
1441 -- Update row in PON_AUCTION_ITEM_PRICES
1442 v_debug_status := 'UPDATE_PAIP_2';
1443 logme(l_log_module, v_debug_status);
1444 UPDATE pon_auction_item_prices_all
1445 SET quantity = quantity + P_QUANTITY,
1446 residual_quantity = residual_quantity + P_QUANTITY,
1447 requisition_number = 'MULTIPLE',
1448 -- problem: least() and greater() return NULL if any argument is NULL
1449 -- set need_by_start to NULL if P_NEED_BY_DATE and need_by_start == NULL
1450 need_by_start_date = DECODE(V_CONTRACT_TYPE, 'BLANKET', to_date(NULL),
1451 least(NVL(need_by_start_date,P_NEED_BY_DATE), NVL(P_NEED_BY_DATE,need_by_start_date))),
1452 -- if P_NEED_BY_DATE is NULL, keep existing need_by_date
1453 need_by_date = DECODE(V_CONTRACT_TYPE, 'BLANKET', to_date(NULL),
1454 greatest(NVL(need_by_date,P_NEED_BY_DATE), NVL(P_NEED_BY_DATE,need_by_date))),
1455 attachment_flag = DECODE(attachment_flag, 'Y', 'Y', v_has_attachments),
1456 -- if P_PRICE is NULL, keep existing current_price
1457 current_price = DECODE(P_PRICE, 0, to_number(NULL),
1458 DECODE(current_price, NULL, NULL, least(current_price, P_PRICE)))
1459 WHERE auction_header_id = P_DOCUMENT_NUMBER
1460 AND line_number = P_LINE_NUMBER;
1461 END IF;
1462 ELSE
1463 v_was_grouped := 'N';
1464 IF (v_order_type_lookup_code = 'AMOUNT') THEN
1465 v_price := P_QUANTITY;
1466 v_quantity := 1;
1467 -- Find the UOM Code
1468 -- Get site ID for the enterprise
1469 v_debug_status := 'SITE_ID';
1470 logme(l_log_module, v_debug_status);
1471 pos_enterprise_util_pkg.get_enterprise_partyId(v_site_id, P_ERROR_CODE, P_ERROR_MESSAGE);
1472 IF (v_site_id IS NULL OR P_ERROR_CODE IS NOT NULL) THEN
1473 P_RESULT := error;
1474 P_ERROR_CODE := 'ADD_NEG_LINE:GET_ENTERPRISE_ID';
1475 P_ERROR_MESSAGE := 'Could not get the Enterprise ID';
1476 logme(l_log_module, p_error_code ||' - '||p_error_message);
1477 RETURN;
1478 END IF;
1479 v_debug_status := 'UOM_SELECT';
1480 logme(l_log_module, v_debug_status);
1481 BEGIN
1482 SELECT preference_value
1483 INTO v_uom_code
1484 FROM pon_party_preferences
1485 WHERE preference_name = 'AMOUNT_BASED_UOM'
1486 AND app_short_name = 'PON'
1487 AND party_id = v_site_id;
1488 EXCEPTION
1489 WHEN OTHERS THEN
1490 -- Don't fail! Use 'Each' and let the user change it later
1491 v_debug_status := 'UOM_SELECT_EACH';
1492 SELECT uom_code
1493 INTO v_uom_code
1494 FROM mtl_units_of_measure
1495 WHERE unit_of_measure = 'Each';
1496 END;
1497 ELSE
1498 -- bug 4677078 set price to null if requisition price is 0
1499 IF (P_PRICE = 0) THEN
1500 v_price := to_number(NULL);
1501 ELSE
1502 v_price := P_PRICE;
1503 END IF;
1504 -- if its services line type you donot want to carry over the quantity column
1505 -- For 11i10+ we WILL carry over the quantity as is done by iP for the
1506 -- rate based line type
1507 IF (v_order_type_lookup_code = 'FIXED PRICE') THEN
1508 v_quantity := NULL;
1509 ELSE
1510 v_quantity := P_QUANTITY;
1511 END IF;
1512 v_uom_code := P_UOM_CODE;
1513 END IF;
1514 -- Get category name from category_id
1515 v_debug_status := 'GET_CATEGORY_NAME';
1516 logme(l_log_module, v_debug_status);
1517 BEGIN
1518 SELECT FND_FLEX_EXT.get_segs('INV', 'MCAT', STRUCTURE_ID, CATEGORY_ID)
1519 INTO v_category_name
1520 FROM mtl_categories_kfv
1521 WHERE category_id = P_CATEGORY_ID;
1522 EXCEPTION
1523 WHEN no_data_found THEN
1524 -- Don't fail! The user can assign this later
1525 v_category_name := NULL;
1526 END;
1527 -- Insert row in PON_AUCTION_ITEM_PRICES
1528 v_debug_status := 'GET_LINE_NUMBER';
1529 logme(l_log_module, v_debug_status);
1530 -- The value that is calculated here for p_line_number is used
1531 -- for setting the number_of_lines and last_line_number fields
1532 -- also.
1533 SELECT NVL(MAX(line_number),0)+1
1534 INTO P_LINE_NUMBER
1535 FROM pon_auction_item_prices_all
1536 WHERE auction_header_id = P_DOCUMENT_NUMBER;
1537
1538 v_debug_status := 'INSERT_PAIP';
1539 logme(l_log_module, v_debug_status);
1540 INSERT
1541 INTO PON_AUCTION_ITEM_PRICES_ALL
1542 (
1543 AUCTION_HEADER_ID,
1544 LINE_NUMBER,
1545 DISP_LINE_NUMBER,
1546 LAST_AMENDMENT_UPDATE,
1547 MODIFIED_DATE,
1548 ITEM_DESCRIPTION,
1549 CATEGORY_ID,
1550 CATEGORY_NAME,
1551 IP_CATEGORY_ID,
1552 UOM_CODE,
1553 QUANTITY,
1554 RESIDUAL_QUANTITY,
1555 NEED_BY_START_DATE,
1556 NEED_BY_DATE,
1557 SHIP_TO_LOCATION_ID,
1558 NUMBER_OF_BIDS,
1559 CREATION_DATE,
1560 CREATED_BY,
1561 LAST_UPDATE_DATE,
1562 LAST_UPDATED_BY,
1563 CURRENT_PRICE,
1564 NOTE_TO_BIDDERS,
1565 ATTACHMENT_FLAG,
1566 HAS_ATTRIBUTES_FLAG,
1567 ORG_ID,
1568 LINE_TYPE_ID,
1569 ORDER_TYPE_LOOKUP_CODE,
1570 PURCHASE_BASIS,
1571 ITEM_ID,
1572 ITEM_NUMBER,
1573 ITEM_REVISION,
1574 LINE_ORIGINATION_CODE,
1575 REQUISITION_NUMBER,
1576 PRICE_BREAK_TYPE,
1577 PRICE_BREAK_NEG_FLAG,
1578 HAS_SHIPMENTS_FLAG,
1579 HAS_QUANTITY_TIERS,
1580 PRICE_DISABLED_FLAG,
1581 quantity_disabled_flag,
1582 JOB_ID,
1583 ADDITIONAL_JOB_DETAILS,
1584 PO_AGREED_AMOUNT,
1585 HAS_PRICE_DIFFERENTIALS_FLAG,
1586 PRICE_DIFF_SHIPMENT_NUMBER,
1587 DIFFERENTIAL_RESPONSE_TYPE,
1588 GROUP_TYPE,
1589 DOCUMENT_DISP_LINE_NUMBER,
1590 SUB_LINE_SEQUENCE_NUMBER,
1591 HAS_PAYMENTS_FLAG,
1592 PROGRESS_PYMT_RATE_PERCENT,
1593 -- added for CLIN SLIN changes
1594 LINE_NUM_DISPLAY,
1595 GROUP_LINE_ID,
1596 CLM_INFO_FLAG,
1597 CLM_OPTION_INDICATOR,
1598 CLM_OPTION_NUM,
1599 CLM_OPTION_FROM_DATE,
1600 CLM_OPTION_TO_DATE,
1601 CLM_FUNDED_FLAG,
1602 CLM_BASE_LINE_NUM,
1603 CLM_CONTRACT_TYPE,
1604 CLM_COST_CONSTRAINT,
1605 CLM_IDC_TYPE,
1606 UDA_TEMPLATE_ID
1607 )
1608 VALUES
1609 (
1610 P_DOCUMENT_NUMBER, -- AUCTION_HEADER_ID
1611 P_LINE_NUMBER,
1612 P_LINE_NUMBER, -- DISP_LINE_NUMBER
1613 0, -- LAST_AMENDMENT_UPDATE
1614 sysdate, -- MODIFIED_DATE
1615 P_ITEM_DESCRIPTION,
1616 P_CATEGORY_ID,
1617 v_category_name, -- CATEGORY_NAME
1618 v_ip_category_id, -- IP_CATEGORY_ID
1619 v_uom_code, -- UOM_CODE
1620 v_quantity, -- QUANTITY
1621 v_quantity, -- RESIDUAL_QUANTITY,
1622 DECODE(V_CONTRACT_TYPE, 'BLANKET', to_date(NULL), P_NEED_BY_DATE), -- NEED_BY_START_DATE
1623 DECODE(V_CONTRACT_TYPE, 'BLANKET', to_date(NULL), P_NEED_BY_DATE), -- NEED_BY_DATE
1624 DECODE(V_CONTRACT_TYPE, 'BLANKET', NULL, p_ship_to_location_id), -- SHIP_TO_LOCATION_ID
1625 0, -- NUMBER_OF_BIDS
1626 sysdate, -- CREATION_DATE
1627 P_BUYER_ID, -- CREATED_BY
1628 sysdate, -- LAST_UPDATE_DATE
1629 P_BUYER_ID, -- LAST_UPDATED_BY
1630 v_price, -- CURRENT_PRICE
1631 P_NOTE_TO_VENDOR, -- NOTE_TO_BIDDERS
1632 v_has_attachments, -- ATTACHMENT_FLAG
1633 'N', -- HAS_ATTRIBUTE_FLAG
1634 P_ORG_ID, -- ORG_ID
1635 P_LINE_TYPE_ID,
1636 v_order_type_lookup_code, -- ORDER_TYPE_LOOKUP_CODE
1637 v_purchase_basis, -- Purchase Basis
1638 P_ITEM_ID,
1639 P_ITEM_NUMBER,
1640 P_ITEM_REVISION,
1641 P_ORIGINATION_CODE, -- LINE_ORIGINATION_CODE
1642 P_REQUISITION_NUMBER,
1643 DECODE(v_order_type_lookup_code,
1644 'AMOUNT', 'NONE',
1645 'FIXED PRICE', 'NONE',
1646 g_price_break_type), -- PRICE_BREAK_TYPE
1647 g_price_break_neg_flag, -- PRICE_BREAK_NEG_FLAG
1648 'N', -- HAS_SHIPMENTS_FLAG
1649 'N', -- HAS_QUANTITY_TIERS
1650 'N', -- PRICE_DISABLED_FLAG
1651 'N', -- QUANTITY_DISABLED_FLAG
1652 P_JOB_ID, -- JOB ID - ADDED FOR SERVICES PROCUREMENT PROJECT
1653 P_JOB_DETAILS, -- ADDITIONAL JOB DETAILS -ADDED FOR SERVICES PROCUREMENT PROJECT
1654 P_PO_AGREED_AMOUNT, -- PO AGREED AMOUNT -ADDED FOR SERVICES PROCUREMENT PROJECT
1655 p_has_price_diff_flag, -- LINE HAS PRICE DIFFERENTIALS ADDED FOR SERVICES PROCUREMENT PROJECT-
1656 -1, --price diff shipment number is -1 by default
1657 DECODE(p_has_price_diff_flag,'Y','OPTIONAL', NULL),
1658 'LINE', -- GROUP_TYPE
1659 P_LINE_NUMBER, -- DOCUMENT_DISP_LINE_NUMBER
1660 P_LINE_NUMBER, -- SUB_LINE_SEQUENCE_NUMBER
1661 'N', --has_payments_flag
1662 DECODE(g_progress_payment_type, 'FINANCE', 100,NULL), --PROGRESS_PYMT_RATE_PERCENT
1663 NVL(P_NEG_LINE_NUM_DISP, P_LINE_NUM_DISPLAY),
1664 P_GROUP_LINE_ID,
1665 P_CLM_INFO_FLAG,
1666 P_CLM_OPTION_INDICATOR,
1667 P_CLM_OPTION_NUM,
1668 P_CLM_OPTION_FROM_DATE,
1669 P_CLM_OPTION_TO_DATE,
1670 P_CLM_FUNDED_FLAG,
1671 P_CLM_BASE_LINE_NUM,
1672 P_CLM_CONTRACT_TYPE,
1673 DECODE(l_progress_payment_type,'ACTUAL','','FINANCE','',P_CLM_COST_CONSTRAINT), -- CLM QA Bug : 10096343
1674 P_CLM_IDC_TYPE,
1675 P_UDA_TEMPLATE_ID
1676 );
1677 IF l_is_federal = 1 THEN
1678 SELECT need_by_date,
1679 clm_period_perf_start_date,
1680 clm_period_perf_end_date
1681 INTO need_by_dt,
1682 pop_start_dt,
1683 pop_end_dt
1684 FROM po_requisition_lines_all
1685 WHERE requisition_header_id = p_requisition_header_id
1686 AND requisition_line_id = p_requisition_line_id;
1687 UPDATE pon_auction_item_prices_all
1688 SET clm_need_by_date = DECODE(V_CONTRACT_TYPE, 'BLANKET', to_date(NULL), need_by_dt),
1689 need_by_start_date = DECODE(V_CONTRACT_TYPE, 'BLANKET', to_date(NULL), pop_start_dt),
1690 need_by_date = DECODE(V_CONTRACT_TYPE, 'BLANKET', to_date(NULL), pop_end_dt)
1691 WHERE auction_header_id = P_DOCUMENT_NUMBER
1692 AND line_number = P_LINE_NUMBER;
1693 END IF;
1694 logme(l_log_module, 'in add neg lines just inserted');
1695 -- We already do an nvl(max(line_number),0)+1 to find the line number of
1696 -- the newly added line. So this value can be used for the fields
1697 -- number_of_lines and last_line_number.
1698 -- The number of lines will be equal to the line number of the new line added
1699 -- The last line number will be equal to the line number of the newly added line
1700 UPDATE PON_AUCTION_HEADERS_ALL
1701 SET NUMBER_OF_LINES = P_LINE_NUMBER,
1702 LAST_LINE_NUMBER = P_LINE_NUMBER
1703 WHERE AUCTION_HEADER_ID = P_DOCUMENT_NUMBER;
1704 END IF;
1705 -- Insert row into PON_BACKING_REQUISITIONS
1706 v_debug_status := 'INSERT_PBR_YUMMY';
1707 logme(l_log_module, v_debug_status);
1708 INSERT
1709 INTO PON_BACKING_REQUISITIONS
1710 (
1711 AUCTION_HEADER_ID,
1712 LINE_NUMBER,
1713 REQUISITION_HEADER_ID,
1714 REQUISITION_LINE_ID,
1715 REQUISITION_QUANTITY,
1716 REQUISITION_NUMBER
1717 )
1718 VALUES
1719 (
1720 P_DOCUMENT_NUMBER,
1721 P_LINE_NUMBER,
1722 P_REQUISITION_HEADER_ID,
1723 P_REQUISITION_LINE_ID,
1724 P_QUANTITY,
1725 P_REQUISITION_NUMBER
1726 );
1727 -- Copy attachments from requisition (header, item and line)
1728 IF
1729 (
1730 v_has_attachments = 'Y'
1731 )
1732 THEN
1733 -- Copy requisition header attachments
1734 v_debug_status := 'INSERT_HEADER_ATTACHMENT';
1735 logme
1736 (
1737 l_log_module, v_debug_status
1738 )
1739 ;
1740 fnd_attached_documents2_pkg.COPY_ATTACHMENTS
1741 (
1742 'REQ_HEADERS', --from_entity_name
1743 TO_CHAR(p_requisition_header_id), -- from_pk1_value
1744 NULL, -- from_pk2_value
1745 NULL, -- from_pk3_value
1746 NULL, -- from_pk4_value
1747 NULL, -- from_pk5_value
1748 'PON_AUCTION_ITEM_PRICES_ALL', -- entity_name
1749 TO_CHAR(P_DOCUMENT_NUMBER), -- PK1_VALUE
1750 TO_CHAR(P_LINE_NUMBER), -- PK2_VALUE
1751 NULL, -- PK3_VALUE
1752 NULL, -- PK4_VALUE
1753 NULL, -- PK5_VALUE
1754 p_buyer_id, -- CREATED_BY
1755 p_buyer_id, -- LAST_UPDATE_LOGIN
1756 NULL, -- program_application_id
1757 NULL, -- program_id
1758 NULL, -- request_id
1759 NULL, -- automatically_added_flag
1760 33, -- from_category_id (Vendor)
1761 33
1762 )
1763 ; -- to_category_id (Vendor)
1764 -- Copy requisition line attachments
1765 v_debug_status := 'INSERT_LINE_ATTACHMENT';
1766 logme
1767 (
1768 l_log_module, v_debug_status
1769 )
1770 ;
1771 fnd_attached_documents2_pkg.COPY_ATTACHMENTS
1772 (
1773 'REQ_LINES', --from_entity_name
1774 TO_CHAR(p_requisition_line_id), -- from_pk1_value
1775 NULL, -- from_pk2_value
1776 NULL, -- from_pk3_value
1777 NULL, -- from_pk4_value
1778 NULL, -- from_pk5_value
1779 'PON_AUCTION_ITEM_PRICES_ALL', -- entity_name
1780 TO_CHAR(P_DOCUMENT_NUMBER), -- PK1_VALUE
1781 TO_CHAR(P_LINE_NUMBER), -- PK2_VALUE
1782 NULL, -- PK3_VALUE
1783 NULL, -- PK4_VALUE
1784 NULL, -- PK5_VALUE
1785 p_buyer_id, -- CREATED_BY
1786 p_buyer_id, -- LAST_UPDATE_LOGIN
1787 NULL, -- program_application_id
1788 NULL, -- program_id
1789 NULL, -- request_id
1790 NULL, -- automatically_added_flag
1791 33, -- from_category_id (Vendor)
1792 33
1793 )
1794 ; -- to_category_id (Vendor)
1795 END IF; -- v_has_attachments
1796 --<Sol Project>
1797 --copy line UDAs from Req Line to Sol Line
1798 IF
1799 (
1800 P_REQ_LINE_TEMP_ID IS NOT NULL
1801 )
1802 THEN
1803 v_debug_status := 'COPY_LINE_UDAS';
1804 logme
1805 (
1806 'pon.plsql.pon_auction_interface_pkg.Add_Negotiation_Line', 'Copy Line UDAs '||
1807 p_document_number||' '||p_line_number||' '||p_requisition_line_id||' '||
1808 p_uda_template_id||' '||p_req_line_temp_id
1809 )
1810 ;
1811 --<CLM UDA Project> Call Copy api only when federal document.
1812 IF
1813 (
1814 PON_CLM_UTIL_PKG.IS_UDA_ENABLED(v_doctype_id) = 1
1815 )
1816 THEN
1817 PON_COPY_UDAS_GRP.COPY_REQ_LINE_UDA
1818 (
1819 P_AUCTION_HEADER_ID=>P_DOCUMENT_NUMBER,
1820 P_AUCTION_LINE_NUMBER=>P_LINE_NUMBER,
1821 P_REQ_LINE_ID=>P_REQUISITION_LINE_ID,
1822 P_SRC_TEMPLATE_ID=>P_REQ_LINE_TEMP_ID,
1823 P_DEST_TEMPLATE_ID=>P_UDA_TEMPLATE_ID,
1824 X_RETURN_STATUS=>v_return_status,
1825 X_MSG_COUNT=>v_msg_count,
1826 X_MSG_DATA=>P_ERROR_MESSAGE
1827 )
1828 ;
1829 logme('pon.plsql.pon_auction_interface_pkg.Add_Negotiation_Line',
1830 'Copy Line UDAs Return '||v_return_status||' '||p_error_message) ;
1831 IF
1832 (
1833 v_return_status <> FND_API.G_RET_STS_SUCCESS
1834 )
1835 THEN
1836 P_RESULT := error;
1837 P_ERROR_CODE := 'ADD_NEG_LINE:COPY_LINE_UDA';
1838 logme
1839 (
1840 l_log_module, p_error_code ||' - '||p_error_message
1841 )
1842 ;
1843 RETURN;
1844 END IF;
1845 -- bug 9914034
1846 IF
1847 (
1848 v_order_type_lookup_code <> 'QUANTITY'
1849 )
1850 THEN
1851 BEGIN
1852 logme('pon.plsql.pon_auction_interface_pkg.Add_Negotiation_Line',
1853 'For Order type lookup code '||v_order_type_lookup_code);
1854 IF v_was_grouped = 'Y' THEN
1855 --When the lines are grouped delete
1856 -- the complex pricing attributes and
1857 -- add amount of grouped lines.
1858 logme('pon.plsql.pon_auction_interface_pkg.Add_Negotiation_Line',
1859 'When items were grouped');
1860 SELECT ATTRIBUTE_GROUP_ID INTO l_comp_pricing_grp_id
1861 FROM po_uda_ag_template_usages
1862 WHERE template_id = P_REQ_LINE_TEMP_ID
1863 AND ATTRIBUTE1 = P_CLM_IDC_TYPE
1864 AND ATTRIBUTE2 = P_CLM_CONTRACT_TYPE;
1865
1866 UPDATE pon_auction_item_prices_all
1867 SET clm_amount = clm_amount
1868 + PO_UDA_PUB.get_single_attr_value(
1869 p_entity_code => 'PO_REQ_LINE_EXT_ATTRS',
1870 pk1_value => P_REQUISITION_LINE_ID,
1871 p_attr_grp_id => l_comp_pricing_grp_id,
1872 p_attr_int_name => 'TOTAL_AMOUNT') -- bug 13571062
1873 WHERE auction_header_id = P_DOCUMENT_NUMBER
1874 AND line_number = P_LINE_NUMBER;
1875 logme('pon.plsql.pon_auction_interface_pkg.Add_Negotiation_Line',
1876 'add total amount');
1877
1878 delete pon_auction_item_prices_ext_b
1879 where auction_header_id = p_document_number
1880 and line_number=p_line_number
1881 and attr_group_id in
1882 (select attribute_group_id from po_uda_ag_template_usages
1883 where template_id=p_uda_template_id
1884 and nvl(attribute1,'X')=nvl(p_clm_idc_type,'X')
1885 and nvl(attribute2,'X')=nvl(p_clm_contract_type,'X'));
1886 logme('pon.plsql.pon_auction_interface_pkg.Add_Negotiation_Line',
1887 'Delete complex pricing UDA ');
1888
1889 ELSE
1890 SELECT ATTRIBUTE_GROUP_ID INTO l_comp_pricing_grp_id
1891 FROM po_uda_ag_template_usages
1892 WHERE template_id = P_UDA_TEMPLATE_ID
1893 AND ATTRIBUTE1 = P_CLM_IDC_TYPE
1894 AND ATTRIBUTE2 = P_CLM_CONTRACT_TYPE;
1895
1896 UPDATE pon_auction_item_prices_all
1897 SET clm_amount =
1898 PO_UDA_PUB.get_single_attr_value(
1899 p_entity_code => 'PON_AUC_PRICES_EXT_ATTRS',
1900 pk1_value => P_DOCUMENT_NUMBER,
1901 pk2_value => P_LINE_NUMBER,
1902 p_attr_grp_id => l_comp_pricing_grp_id,
1903 p_attr_int_name => 'TOTAL_AMOUNT') -- bug 13571062
1904 WHERE auction_header_id = P_DOCUMENT_NUMBER
1905 AND line_number = P_LINE_NUMBER;
1906 logme('pon.plsql.pon_auction_interface_pkg.Add_Negotiation_Line',
1907 'set amount from complex pricing UDA ');
1908 END IF;
1909 EXCEPTION
1910 WHEN OTHERS THEN
1911 logme('pon.plsql.pon_auction_interface_pkg.Add_Negotiation_Line',
1912 'Exception in updating complex pricing extended price ');
1913 END;
1914 ELSE -- quantity based lines
1915 UPDATE pon_auction_item_prices_all
1916 SET clm_unit_price = current_price,
1917 clm_amount = (NVL(quantity,0)*NVL(current_price,0))
1918 WHERE auction_header_id = P_DOCUMENT_NUMBER
1919 AND line_number = P_LINE_NUMBER;
1920 END IF;
1921 END IF;-- End if doc type federal
1922 END IF;
1923 P_RESULT := success;
1924 P_ERROR_CODE := NULL;
1925 P_ERROR_MESSAGE := NULL;
1926 EXCEPTION
1927 WHEN OTHERS THEN
1928 fnd_message.set_name('PON', 'PON_AUC_PLSQL_ERR');
1929 fnd_message.set_token('PACKAGE','pon_auction_interface_pkg');
1930 fnd_message.set_token('PROCEDURE','Add_Negotiation_Line');
1931 fnd_message.set_token('ERROR',v_debug_status || ' [' || SQLERRM || ']');
1932 APP_EXCEPTION.RAISE_EXCEPTION;
1933 END;
1934 /*============ADDED FOR UNIFIED CATALOG PROJECT=====================
1935 PROCEDURE : Add_Catalog_Descriptors
1936 PARAMETERS:
1937 P_API_VERSION IN NUMBER
1938 P_DOCUMENT_NUMBER IN NUMBER
1939 X_RETURN_STATUS OUT NOCOPY VARCHAR2
1940 X_MSG_COUNT OUT NOCOPY NUMBER
1941 X_MSG_DATA OUT NOCOPY VARCHAR2
1942 COMMENT : Adds ip descriptors to a draft auction
1943 ======================================================================*/
1944 PROCEDURE Add_Catalog_Descriptors
1945 (
1946 P_API_VERSION IN NUMBER,
1947 P_DOCUMENT_NUMBER IN NUMBER,
1948 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1949 X_MSG_COUNT OUT NOCOPY NUMBER,
1950 X_MSG_DATA OUT NOCOPY VARCHAR2)
1951 IS
1952 v_contract_type pon_auction_headers_all.contract_type%TYPE;
1953 v_buyer_id NUMBER;
1954 v_ip_attr_default_option VARCHAR2(10);
1955 v_default_attr_group pon_auction_attributes.attr_group%TYPE;
1956 v_attr_group_name fnd_lookup_values.meaning%TYPE;
1957 v_max_seq_number NUMBER;
1958 v_line_number NUMBER;
1959 v_ip_category_id NUMBER;
1960 v_debug_status VARCHAR2(100);
1961 v_return_status VARCHAR2(1);
1962 v_msg_count NUMBER;
1963 v_msg_data VARCHAR2(400);
1964 CURSOR catalogLines
1965 IS
1966 SELECT DISTINCT interface_line_number
1967 FROM pon_attributes_interface
1968 WHERE interface_auction_header_id = p_document_number;
1969 CURSOR nonCatalogLines
1970 IS
1971 SELECT DISTINCT paip.line_number,
1972 paip.ip_category_id
1973 FROM pon_auction_item_prices_all paip,
1974 pon_backing_requisitions pbr,
1975 po_requisition_lines_all prl
1976 WHERE paip.auction_header_id = p_document_number
1977 AND paip.auction_header_id = pbr.auction_header_id
1978 AND paip.line_number = pbr.line_number
1979 AND pbr.requisition_header_id = prl.requisition_header_id
1980 AND pbr.requisition_line_id = prl.requisition_line_id
1981 AND prl.blanket_po_header_id IS NULL
1982 AND prl.blanket_po_line_num IS NULL;
1983 BEGIN
1984 SELECT contract_type,
1985 created_by
1986 INTO v_contract_type,
1987 v_buyer_id
1988 FROM pon_auction_headers_all
1989 WHERE auction_header_id = p_document_number;
1990
1991 v_ip_attr_default_option := fnd_profile.value('PON_IP_ATTR_DEFAULT_OPTION');
1992 IF (v_contract_type <> 'BLANKET' OR v_ip_attr_default_option IS NULL OR
1993 v_ip_attr_default_option = 'NONE') THEN
1994 RETURN;
1995 END IF;
1996 SELECT NVL(ppp.preference_value,'GENERAL'),
1997 flv.meaning
1998 INTO v_default_attr_group,
1999 v_attr_group_name
2000 FROM pon_party_preferences ppp,
2001 fnd_lookup_values flv
2002 WHERE ppp.app_short_name = 'PON'
2003 AND ppp.preference_name = 'LINE_ATTR_DEFAULT_GROUP'
2004 AND ppp.party_id =
2005 (SELECT trading_partner_id
2006 FROM pon_auction_headers_all
2007 WHERE auction_header_id = p_document_number
2008 )
2009 AND flv.lookup_type = 'PON_LINE_ATTRIBUTE_GROUPS'
2010 AND NVL(ppp.preference_value,'GENERAL') = flv.lookup_code
2011 AND flv.view_application_id = 0
2012 AND flv.security_group_id = 0
2013 AND flv.language = userenv('LANG');
2014
2015 v_max_seq_number := 9999999999999;
2016 PO_NEGOTIATIONS4_GRP.insert_attributes( p_api_version => 1.0,
2017 p_commit => fnd_api.g_false,
2018 p_init_msg_list => fnd_api.g_false,
2019 p_validation_level => fnd_api.g_valid_level_full,
2020 p_auction_header_id => p_document_number,
2021 x_return_status => v_return_status,
2022 x_msg_count => v_msg_count,
2023 x_msg_data => v_msg_data);
2024 FOR catalogLine IN catalogLines
2025 LOOP
2026 v_line_number := catalogLine.interface_line_number;
2027 INSERT
2028 INTO PON_AUCTION_ATTRIBUTES
2029 (
2030 AUCTION_HEADER_ID,
2031 LINE_NUMBER,
2032 ATTRIBUTE_NAME,
2033 DESCRIPTION,
2034 DATATYPE,
2035 MANDATORY_FLAG,
2036 VALUE,
2037 DISPLAY_PROMPT,
2038 HELP_TEXT,
2039 DISPLAY_TARGET_FLAG,
2040 CREATION_DATE,
2041 CREATED_BY,
2042 LAST_UPDATE_DATE,
2043 LAST_UPDATED_BY,
2044 ATTRIBUTE_LIST_ID,
2045 DISPLAY_ONLY_FLAG,
2046 SEQUENCE_NUMBER,
2047 COPIED_FROM_CAT_FLAG,
2048 WEIGHT,
2049 SCORING_TYPE,
2050 ATTR_LEVEL,
2051 ATTR_GROUP,
2052 SECTION_NAME,
2053 ATTR_MAX_SCORE,
2054 INTERNAL_ATTR_FLAG,
2055 ATTR_GROUP_SEQ_NUMBER,
2056 ATTR_DISP_SEQ_NUMBER,
2057 MODIFIED_FLAG,
2058 MODIFIED_DATE,
2059 LAST_AMENDMENT_UPDATE,
2060 IP_CATEGORY_ID,
2061 IP_DESCRIPTOR_ID
2062 )
2063 SELECT P_DOCUMENT_NUMBER, -- AUCTION_HEADER_ID
2064 INTERFACE_LINE_NUMBER, -- LINE_NUMBER
2065 ATTRIBUTE_NAME, -- ATTRIBUTE_NAME
2066 NULL, -- DESCRIPTION
2067 DATATYPE, -- DATATYPE
2068 'N', -- MANDATORY_FLAG
2069 VALUE, -- VALUE
2070 NULL, -- DISPLAY_PROMPT
2071 NULL, -- HELP_TEXT
2072 'N', -- DISPLAY_TARGET_FLAG
2073 SYSDATE, -- CREATION_DATE
2074 v_buyer_id, -- CREATED_BY
2075 SYSDATE, -- LAST_UPDATE_DATE
2076 v_buyer_id, -- LAST_UPDATED_BY
2077 -1, -- ATTRIBUTE_LIST_ID
2078 'N', -- DISPLAY_ONLY_FLAG
2079 (ROWNUM*10), -- SEQUENCE_NUMBER
2080 NULL, -- COPIED_FROM_CAT_FLAG
2081 NULL, -- WEIGHT
2082 NULL, -- SCORING_TYPE
2083 'LINE', -- ATTR_LEVEL
2084 v_default_attr_group, -- ATTR_GROUP
2085 v_attr_group_name, -- SECTION_NAME
2086 NULL, -- ATTR_MAX_SCORE
2087 'N', -- INTERNAL_ATTR_FLAG
2088 10, -- ATTR_GROUP_SEQ_NUMBER
2089 (ROWNUM*10), -- ATTR_DISP_SEQ_NUMBER
2090 NULL, -- MODIFIED_FLAG
2091 NULL, -- MODIFIED_DATE
2092 NULL, -- LAST_AMENDMENT_UPDATE
2093 IP_CATEGORY_ID, -- IP_CATEGORY_ID
2094 IP_DESCRIPTOR_ID -- IP_DESCRIPTOR_ID
2095 FROM
2096 (SELECT interface_line_number,
2097 attribute_name,
2098 datatype,
2099 value,
2100 ip_category_id,
2101 ip_descriptor_id
2102 FROM pon_attributes_interface
2103 WHERE interface_auction_header_id = P_DOCUMENT_NUMBER
2104 AND interface_line_number = v_line_number
2105 AND ((ip_category_id = 0
2106 AND v_ip_attr_default_option IN ('ALL', 'BASE'))
2107 OR (ip_category_id <> 0
2108 AND v_ip_attr_default_option IN ('ALL', 'CATEGORY')))
2109 ORDER BY NVL(interface_sequence_number, v_max_seq_number) ASC
2110 );
2111 END LOOP;
2112 DELETE
2113 FROM PON_ATTRIBUTES_INTERFACE
2114 WHERE interface_auction_header_id = P_DOCUMENT_NUMBER;
2115
2116 FOR nonCatalogLine IN nonCatalogLines
2117 LOOP
2118 v_line_number := nonCatalogLine.line_number;
2119 v_ip_category_id := nonCatalogLine.ip_category_id;
2120 INSERT
2121 INTO PON_AUCTION_ATTRIBUTES
2122 (
2123 AUCTION_HEADER_ID,
2124 LINE_NUMBER,
2125 ATTRIBUTE_NAME,
2126 DESCRIPTION,
2127 DATATYPE,
2128 MANDATORY_FLAG,
2129 VALUE,
2130 DISPLAY_PROMPT,
2131 HELP_TEXT,
2132 DISPLAY_TARGET_FLAG,
2133 CREATION_DATE,
2134 CREATED_BY,
2135 LAST_UPDATE_DATE,
2136 LAST_UPDATED_BY,
2137 ATTRIBUTE_LIST_ID,
2138 DISPLAY_ONLY_FLAG,
2139 SEQUENCE_NUMBER,
2140 COPIED_FROM_CAT_FLAG,
2141 WEIGHT,
2142 SCORING_TYPE,
2143 ATTR_LEVEL,
2144 ATTR_GROUP,
2145 SECTION_NAME,
2146 ATTR_MAX_SCORE,
2147 INTERNAL_ATTR_FLAG,
2148 ATTR_GROUP_SEQ_NUMBER,
2149 ATTR_DISP_SEQ_NUMBER,
2150 MODIFIED_FLAG,
2151 MODIFIED_DATE,
2152 LAST_AMENDMENT_UPDATE,
2153 IP_CATEGORY_ID,
2154 IP_DESCRIPTOR_ID
2155 )
2156 SELECT P_DOCUMENT_NUMBER, -- AUCTION_HEADER_ID
2157 v_line_number, -- LINE_NUMBER
2158 DESCRIPTOR_NAME, -- ATTRIBUTE_NAME
2159 NULL, -- DESCRIPTION
2160 DATATYPE, -- DATATYPE
2161 'N', -- MANDATORY_FLAG
2162 NULL, -- VALUE
2163 NULL, -- DISPLAY_PROMPT
2164 NULL, -- HELP_TEXT
2165 'N', -- DISPLAY_TARGET_FLAG
2166 SYSDATE, -- CREATION_DATE
2167 v_buyer_id, -- CREATED_BY
2168 SYSDATE, -- LAST_UPDATE_DATE
2169 v_buyer_id, -- LAST_UPDATED_BY
2170 -1, -- ATTRIBUTE_LIST_ID
2171 'N', -- DISPLAY_ONLY_FLAG
2172 (ROWNUM*10), -- SEQUENCE_NUMBER
2173 NULL, -- COPIED_FROM_CAT_FLAG
2174 NULL, -- WEIGHT
2175 NULL, -- SCORING_TYPE
2176 'LINE', -- ATTR_LEVEL
2177 v_default_attr_group, -- ATTR_GROUP
2178 v_attr_group_name, -- SECTION_NAME
2179 NULL, -- ATTR_MAX_SCORE
2180 'N', -- INTERNAL_ATTR_FLAG
2181 10, -- ATTR_GROUP_SEQ_NUMBER
2182 (ROWNUM*10), -- ATTR_DISP_SEQ_NUMBER
2183 NULL, -- MODIFIED_FLAG
2184 NULL, -- MODIFIED_DATE
2185 NULL, -- LAST_AMENDMENT_UPDATE
2186 IP_CATEGORY_ID, -- IP_CATEGORY_ID
2187 IP_DESCRIPTOR_ID -- IP_DESCRIPTOR_ID
2188 FROM
2189 (SELECT attribute_name descriptor_name,
2190 DECODE(type, 1, 'NUM', 'TXT') datatype,
2191 rt_category_id ip_category_id,
2192 attribute_id ip_descriptor_id
2193 FROM icx_cat_agreement_attrs_v
2194 WHERE ((rt_category_id = 0
2195 AND v_ip_attr_default_option IN ('ALL', 'BASE'))
2196 OR (rt_category_id = v_ip_category_id
2197 AND v_ip_attr_default_option IN ('ALL', 'CATEGORY')))
2198 AND language = userenv('LANG')
2199 ORDER BY NVL(sequence, v_max_seq_number) ASC
2200 );
2201 END LOOP;
2202 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2203 X_MSG_COUNT := 0;
2204 X_MSG_DATA := NULL;
2205 EXCEPTION
2206 WHEN OTHERS THEN
2207 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR ;
2208 X_MSG_COUNT := 1;
2209 fnd_message.set_name('PON', 'PON_AUC_PLSQL_ERR');
2210 fnd_message.set_token('PACKAGE','pon_auction_interface_pkg');
2211 fnd_message.set_token('PROCEDURE','Add_Catalog_Descriptors');
2212 fnd_message.set_token('ERROR',v_debug_status || ' [' || SQLERRM || ']');
2213 fnd_message.retrieve(X_MSG_DATA);
2214 RETURN;
2215 END Add_Catalog_Descriptors;
2216 /*============ADDED FOR SERVICES PROCUREMENT PROJECT=====================
2217 PROCEDURE : Add_Price_Differential
2218 PARAMETERS:
2219 P_DOCUMENT_NUMBER IN Document number to add line
2220 P_LINE_NUMBER IN Line number
2221 P_SHIPMENT_NUMBER IN Shipment number
2222 P_PRICE_TYPE IN Price Type
2223 P_MULTIPLIER IN Multiplier
2224 P_BUYER_ID IN FND_USER_ID of the creator
2225 P_PRICE_DIFFERENTIAL_NUMBER OUT Price Differential Number
2226 P_RESULT OUT One of (error, success)
2227 P_ERROR_CODE OUT Internal Error Code
2228 P_ERROR_MESSAGE OUT Displayable error
2229 COMMENT : Creates a price differential in a draft auction
2230 ======================================================================*/
2231 PROCEDURE Add_Price_Differential
2232 (
2233 P_DOCUMENT_NUMBER IN NUMBER,
2234 P_LINE_NUMBER IN NUMBER,
2235 P_SHIPMENT_NUMBER IN NUMBER,
2236 P_PRICE_TYPE IN VARCHAR2,
2237 P_MULTIPLIER IN NUMBER,
2238 P_BUYER_ID IN NUMBER,
2239 P_PRICE_DIFFERENTIAL_NUMBER OUT NOCOPY NUMBER,
2240 P_RESULT OUT NOCOPY NUMBER,
2241 P_ERROR_CODE OUT NOCOPY VARCHAR2,
2242 P_ERROR_MESSAGE OUT NOCOPY VARCHAR2)
2243 IS
2244 v_debug_status VARCHAR2(100);
2245 BEGIN
2246 -- Find the max price_diff_line number for this line and save it
2247 SELECT NVL(MAX(price_differential_number),0)+1
2248 INTO p_price_differential_number
2249 FROM pon_price_differentials
2250 WHERE auction_header_id = p_document_number
2251 AND line_number = p_line_number
2252 AND shipment_number = p_shipment_number;
2253 IF (p_document_number IS NULL) THEN
2254 p_result := error;
2255 p_error_code := 'ADD_PRICE_DIFFERENTIAL:DOCUMENT_NUMBER';
2256 p_error_message := 'Please provide a valid document number';
2257 RETURN;
2258 END IF;
2259 IF (p_line_number IS NULL OR p_line_number < 0) THEN
2260 p_result := error;
2261 p_error_code := 'ADD_PRICE_DIFFERENTIAL:LINE_NUMBER';
2262 p_error_message := 'Line Number cannot be null. Its either a valid positive number or -1.';
2263 RETURN;
2264 END IF ;
2265 IF (p_shipment_number IS NULL) THEN
2266 p_result := error;
2267 p_error_code := 'ADD_PRICE_DIFFERENTIAL:SHIPMENT_NUMBER';
2268 p_error_message := 'Shipment Number cannot be null. Its either a valid positive number or -1.';
2269 RETURN;
2270 END IF ;
2271 IF ((p_line_number = -1) AND (p_shipment_number = -1)) THEN
2272 p_result := error;
2273 p_error_code := 'ADD_PRICE_DIFFERENTIAL:INVALID_VALUES';
2274 p_error_message := 'Both Line Number and Shipment Number cannot be -1';
2275 RETURN;
2276 END IF;
2277 v_debug_status := 'INSERT_PRICE_DIFFERENTIALS';
2278 INSERT
2279 INTO pon_price_differentials
2280 (
2281 auction_header_id,
2282 line_number,
2283 shipment_number,
2284 price_differential_number,
2285 price_type,
2286 multiplier,
2287 creation_date,
2288 created_by,
2289 last_update_date,
2290 last_updated_by
2291 )
2292 VALUES
2293 (
2294 p_document_number, -- Auction Header Id
2295 p_line_number, -- Line Number
2296 p_shipment_number, -- Shipment Number
2297 p_price_differential_number, -- Price Differentials Number
2298 p_price_type, -- Price Type
2299 p_multiplier, -- Multiplier
2300 Sysdate, -- creation date
2301 p_buyer_id, -- created by
2302 Sysdate, -- last update date
2303 p_buyer_id -- last updated by
2304 );
2305 EXCEPTION
2306 WHEN OTHERS THEN
2307 fnd_message.set_name
2308 (
2309 'PON', 'PON_AUC_PLSQL_ERR'
2310 )
2311 ;
2312 fnd_message.set_token
2313 (
2314 'PACKAGE','pon_auction_interface_pkg'
2315 )
2316 ;
2317 fnd_message.set_token
2318 (
2319 'PROCEDURE','Add_Price_Differential'
2320 )
2321 ;
2322 fnd_message.set_token
2323 (
2324 'ERROR',v_debug_status || ' [' || SQLERRM || ']'
2325 )
2326 ;
2327 APP_EXCEPTION.RAISE_EXCEPTION;
2328 END;
2329 /*========================================================================
2330 PROCEDURE : Get_Negotiation_Owner
2331 PARAMETERS:
2332 P_DOCUMENT_NUMBER IN Document Id
2333 P_OWNER_NAME OUT FND_USER.USER_NAME of document owner
2334 P_RESULT OUT One of (error, success)
2335 P_ERROR_CODE OUT Internal Error Code
2336 P_ERROR_MESSAGE OUT Displayable error
2337 COMMENT : Returns the owner name for a negotiation document
2338 --======================================================================*/
2339 PROCEDURE Get_Negotiation_Owner
2340 (
2341 P_DOCUMENT_NUMBER IN NUMBER,
2342 P_OWNER_NAME OUT NOCOPY VARCHAR2,
2343 P_RESULT OUT NOCOPY NUMBER,
2344 P_ERROR_CODE OUT NOCOPY VARCHAR2,
2345 P_ERROR_MESSAGE OUT NOCOPY VARCHAR2
2346 )
2347 IS
2348 v_buyer_name fnd_user.user_name%TYPE := NULL;
2349 BEGIN
2350 BEGIN
2351 SELECT u1.user_name,
2352 u2.user_name
2353 INTO P_OWNER_NAME,
2354 v_buyer_name
2355 FROM fnd_user u1,
2356 fnd_user u2,
2357 pon_auction_headers_all ah
2358 WHERE ah.auction_header_id = P_DOCUMENT_NUMBER
2359 AND ah.trading_partner_contact_id = u1.person_party_id(+)
2360 AND ah.buyer_id = u2.user_id(+);
2361 EXCEPTION
2362 WHEN no_data_found THEN
2363 -- No owner found for a Document is not an error.
2364 P_OWNER_NAME := NULL;
2365 END;
2366 IF (P_OWNER_NAME IS NULL) THEN
2367 P_OWNER_NAME := v_buyer_name;
2368 END IF;
2369 P_RESULT := success;
2370 P_ERROR_CODE := NULL;
2371 P_ERROR_MESSAGE := NULL;
2372 EXCEPTION
2373 WHEN OTHERS THEN
2374 P_RESULT := error;
2375 P_ERROR_CODE := 'GET_NEG_OWNER';
2376 fnd_message.set_name('PON', 'PON_AUC_PLSQL_ERR');
2377 fnd_message.set_token('PACKAGE','pon_auction_interface_pkg');
2378 fnd_message.set_token('PROCEDURE','Get_Negotiation_Owner');
2379 fnd_message.set_token('ERROR','Document ' || P_DOCUMENT_NUMBER || ' [' || SQLERRM || ']');
2380 fnd_message.retrieve(P_ERROR_MESSAGE);
2381 END;
2382 /*========================================================================
2383 PROCEDURE : Get_PO_Negotiation_Link PUBLIC
2384 PARAMETERS:
2385 P_PO_HEADER_ID IN PO Header Id
2386 P_DOCUMENT_ID OUT Negotiation document id
2387 P_DOCUMENT_NUMBER OUT Negotiation Document number display
2388 P_DOCUMENT_URL OUT URL to view negotiation document
2389 P_RESULT OUT One of (error, success)
2390 P_ERROR_CODE OUT Internal code for error
2391 P_ERROR_MESSAGE OUT Displayable error message
2392 COMMENT : Returns the Negotiation Document number and Sourcing URL
2393 for viewing the Negotiation Document. The Negotiation Document number
2394 returned is formatted for display and may not be the same as the
2395 pon_auction_headers.auction_header_id. The Document Number should not
2396 be used in subsequent calls to this API.
2397 FPJ: As we migrated to OA, this API is also updated. Because we cannot
2398 encrypt id at pl/sql, we return id as an out parameter. The caller
2399 needs to encrypt the id, and append to the url.
2400 ======================================================================*/
2401 PROCEDURE Get_PO_Negotiation_Link
2402 (
2403 P_PO_HEADER_ID IN NUMBER,
2404 P_DOCUMENT_ID OUT NOCOPY NUMBER,
2405 P_DOCUMENT_NUMBER OUT NOCOPY VARCHAR2,
2406 P_DOCUMENT_URL OUT NOCOPY VARCHAR2,
2407 P_RESULT OUT NOCOPY NUMBER,
2408 P_ERROR_CODE OUT NOCOPY VARCHAR2,
2409 P_ERROR_MESSAGE OUT NOCOPY VARCHAR2)
2410 IS
2411 v_debug_status VARCHAR2(60);
2412 BEGIN
2413 BEGIN
2414 SELECT ah.auction_header_id,
2415 ah.document_number
2416 INTO P_DOCUMENT_ID,
2417 P_DOCUMENT_NUMBER
2418 FROM pon_bid_headers bh,
2419 pon_auction_headers_all ah
2420 WHERE bh.po_header_id = P_PO_HEADER_ID
2421 AND bh.auction_header_id = ah.auction_header_id;
2422 EXCEPTION
2423 WHEN no_data_found THEN
2424 -- No negotiation found for a PO is not an error
2425 P_DOCUMENT_ID := NULL;
2426 P_DOCUMENT_NUMBER := NULL;
2427 P_DOCUMENT_URL := NULL;
2428 P_RESULT := success;
2429 P_ERROR_CODE := NULL;
2430 P_ERROR_MESSAGE := NULL;
2431 RETURN;
2432 END;
2433 -- Construct URL to View Negotation
2434 v_debug_status := 'DOC_URL';
2435 P_DOCUMENT_URL := 'OA.jsp?OAFunc=PON_NEG_SUMMARY';
2436 P_RESULT := success;
2437 P_ERROR_CODE := NULL;
2438 P_ERROR_MESSAGE := NULL;
2439 EXCEPTION
2440 WHEN OTHERS THEN
2441 P_RESULT := error;
2442 P_ERROR_CODE := 'GET_NEG_OWNER';
2443 fnd_message.set_name('PON', 'PON_AUC_PLSQL_ERR');
2444 fnd_message.set_token('PACKAGE','pon_auction_interface_pkg');
2445 fnd_message.set_token('PROCEDURE','Get_PO_Negotiation_Link');
2446 fnd_message.set_token('ERROR','PO Header ' || P_PO_HEADER_ID || ' [' || SQLERRM || ']');
2447 fnd_message.retrieve(P_ERROR_MESSAGE);
2448 END;
2449 --
2450 /*===================================================================
2451 PROCEDURE: add_negotiation_invitees PUBLIC
2452 PARAMETERS:
2453 p_api_version IN version of the api
2454 x_return_status OUT FND_API.G_RET_STS_SUCCESS or FND_API.G_RET_STS_ERROR
2455 x_msg_count OUT Internal code for error
2456 x_msg_data OUT Displayable error message
2457 P_DOCUMENT_NUMBER IN Negotiation Document number
2458 P_BUYER_ID IN FND_USER_ID of the creator
2459 COMMENT: Gets distinct vendor_ids and vendor sites
2460 across all the requisition lines that are part of the
2461 negotiation and adds (bulk inserts ) them as invitees. We do not check
2462 for inactive suppliers/ sites in the autocreate process; these
2463 will be validated at publish time.
2464 =====================================================================*/
2465 PROCEDURE add_negotiation_invitees
2466 (
2467 p_api_version IN NUMBER,
2468 x_return_status OUT NOCOPY VARCHAR2,
2469 x_msg_count OUT NOCOPY NUMBER,
2470 x_msg_data OUT NOCOPY VARCHAR2,
2471 P_DOCUMENT_NUMBER IN NUMBER,
2472 P_BUYER_ID IN NUMBER)
2473 IS
2474 l_api_version CONSTANT NUMBER := 1.0;
2475 l_api_name CONSTANT VARCHAR2(50):= 'add_negotiation_invitees';
2476 l_fair_opp_notice_flag pon_auction_headers_all.fair_opp_notice_flag%TYPE;
2477 l_umbrella_program_id pon_auction_headers_all.umbrella_program_id%TYPE;
2478 l_max_sequence NUMBER := 0;
2479 BEGIN
2480 -- Check for call compatibility.
2481 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2482 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2483 END IF;
2484 IF (P_DOCUMENT_NUMBER IS NULL) THEN
2485 x_return_status := FND_API.G_RET_STS_SUCCESS;
2486 x_msg_count := 0;
2487 x_msg_data := 'Please provide a DOCUMENT_NUMBER';
2488 RETURN;
2489 END IF;
2490 IF (P_BUYER_ID IS NULL) THEN
2491 x_return_status := FND_API.G_RET_STS_SUCCESS;
2492 x_msg_count := 0;
2493 x_msg_data := 'Please specify a BUYER_ID';
2494 RETURN;
2495 END IF;
2496 SELECT NVL(fair_opp_notice_flag, 'N'),
2497 umbrella_program_id
2498 INTO l_fair_opp_notice_flag,
2499 l_umbrella_program_id
2500 FROM pon_auction_headers_all
2501 WHERE auction_header_id = p_document_number;
2502 -- Do not add suppliers if the document is and FON with umbrella program not NULL
2503 IF l_fair_opp_notice_flag = 'Y' AND l_umbrella_program_id IS NOT NULL THEN
2504 RETURN;
2505 END IF;
2506 BEGIN
2507 SELECT MAX(sequence)
2508 INTO l_max_sequence
2509 FROM PON_BIDDING_PARTIES
2510 WHERE AUCTION_HEADER_ID = P_DOCUMENT_NUMBER;
2511 EXCEPTION
2512 WHEN OTHERS THEN
2513 NULL;
2514 END;
2515 INSERT
2516 INTO PON_BIDDING_PARTIES
2517 (
2518 AUCTION_HEADER_ID,
2519 List_ID,
2520 LAST_UPDATE_DATE,
2521 LAST_UPDATED_BY,
2522 SEQUENCE,
2523 TRADING_PARTNER_NAME,
2524 TRADING_PARTNER_ID,
2525 TRADING_PARTNER_CONTACT_NAME,
2526 TRADING_PARTNER_CONTACT_ID,
2527 CREATION_DATE,
2528 CREATED_BY,
2529 NUMBER_PRICE_DECIMALS,
2530 ROUND_NUMBER,
2531 LAST_AMENDMENT_UPDATE,
2532 VENDOR_SITE_ID,
2533 VENDOR_SITE_CODE,
2534 ACCESS_TYPE
2535 )
2536 SELECT P_DOCUMENT_NUMBER, --AUCTION_HEADER_ID
2537 -1, -- List_ID
2538 sysdate, -- LAST_UPDATE_DATE
2539 p_buyer_id, -- LAST_UPDATED_BY
2540 Nvl(l_max_sequence,0) + rownum * 10, -- SEQUENCE
2541 vendor_name, -- TRADING_PARTNER_NAME
2542 party_id, -- TRADING_PARTNER_ID
2543 NULL, -- TRADING_PARTNER_CONTACT_NAME
2544 NULL, -- TRADING_PARTNER_CONTACT_ID
2545 sysdate, -- CREATION_DATE
2546 p_buyer_id, -- CREATED_BY
2547 NUMBER_PRICE_DECIMALS, -- NUMBER_PRICE_DECIMALS
2548 1, -- ROUND_NUMBER
2549 0, -- LAST_AMENDMENT_UPDATE
2550 vendor_site_id, -- VENDOR_SITE_ID
2551 vendor_site_code, -- VENDOR_SITE_CODE
2552 'FULL' -- ACCESS_TYPE
2553 FROM
2554 (SELECT DISTINCT pv.vendor_name vendor_name,
2555 pv.party_id party_id,
2556 ponah.number_price_decimals number_price_decimals,
2557 NVL(prl.vendor_site_id, -1) vendor_site_id,
2558 NVL(ps.vendor_site_code, -1) vendor_site_code
2559 FROM po_requisition_lines_all prl,
2560 pon_backing_requisitions ponbr,
2561 pon_auction_headers_all ponah,
2562 po_vendors pv,
2563 po_vendor_sites_all ps
2564 WHERE ponah.auction_header_id = p_document_number
2565 AND ponbr.auction_header_id = ponah.auction_header_id
2566 AND ponbr.requisition_header_id = prl.requisition_header_id
2567 AND ponbr.requisition_line_id = prl.requisition_line_id
2568 AND prl.vendor_id IS NOT NULL
2569 AND prl.vendor_id = pv.vendor_id
2570 AND NVL(pv.start_date_active, sysdate) <= sysdate
2571 AND NVL(pv.end_date_active, sysdate) >= sysdate
2572 AND ps.vendor_id(+) = prl.vendor_id
2573 AND ps.vendor_site_id(+) = prl.vendor_site_id
2574 )
2575 ORDER BY vendor_name;
2576
2577 x_return_status := FND_API.G_RET_STS_SUCCESS;
2578 x_msg_count := 0;
2579 x_msg_data := NULL;
2580 EXCEPTION
2581 WHEN NO_DATA_FOUND THEN
2582 x_return_status := FND_API.G_RET_STS_SUCCESS;
2583 x_msg_count := 0;
2584 x_msg_data := NULL;
2585 RETURN;
2586 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2587 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2588 x_msg_count := 1;
2589 fnd_message.set_name('PON', 'PON_AUC_PLSQL_ERR');
2590 fnd_message.set_token('PACKAGE','pon_auction_interface_pkg');
2591 fnd_message.set_token('PROCEDURE','add_negotiation_invitees');
2592 fnd_message.set_token('ERROR', ' [' || SQLERRM || ']');
2593 fnd_message.retrieve(x_msg_data);
2594 RETURN;
2595 WHEN OTHERS THEN
2596 x_return_status := FND_API.G_RET_STS_ERROR ;
2597 x_msg_count := 1;
2598 fnd_message.set_name('PON', 'PON_AUC_PLSQL_ERR');
2599 fnd_message.set_token('PACKAGE','pon_auction_interface_pkg');
2600 fnd_message.set_token('PROCEDURE','add_negotiation_invitees');
2601 fnd_message.set_token('ERROR', ' [' || SQLERRM || ']');
2602 --APP_EXCEPTION.RAISE_EXCEPTION;
2603 fnd_message.retrieve(x_msg_data);
2604 RETURN;
2605 END;
2606 -- API used by html autocreation for default negotiation style
2607 PROCEDURE get_default_negotiation_style
2608 (
2609 x_style_id OUT NOCOPY NUMBER,
2610 x_style_name OUT NOCOPY VARCHAR2)
2611 IS
2612 BEGIN
2613 SELECT style_id,
2614 style_name
2615 INTO x_style_id,
2616 x_style_name
2617 FROM pon_negotiation_styles_tl
2618 WHERE style_id = 1
2619 AND language = userenv('LANG');
2620 EXCEPTION
2621 WHEN OTHERS THEN
2622 x_style_id := 1;
2623 END;
2624 /*===================================================================
2625 PROCEDURE: maintain_clm_relations PUBLIC
2626 PARAMETERS:
2627 p_api_version version of the api
2628 x_return_status OUT FND_API.G_RET_STS_SUCCESS or FND_API.G_RET_STS_ERROR
2629 x_msg_count OUT Internal code for error
2630 x_msg_data OUT Displayable error message
2631 P_DOCUMENT_NUMBER IN Negotiation Document number
2632 COMMENT: -- After the lines have been autocreated. Set the CLM clin slin and option relations appropriately.
2633 -- Currently the reqline ids have been copied over as it is.
2634 -- Now map the correct reqlineids with the neg line numbers and store
2635 -- in the negotiation lines table.
2636 =====================================================================*/
2637 PROCEDURE maintain_clm_relations
2638 (
2639 p_api_version IN NUMBER,
2640 x_return_status OUT NOCOPY VARCHAR2,
2641 x_msg_count OUT NOCOPY NUMBER,
2642 x_msg_data OUT NOCOPY VARCHAR2,
2643 P_DOCUMENT_NUMBER IN NUMBER)
2644 IS
2645 l_log_module VARCHAR2(100) := 'maintain_clm_relations';
2646 CURSOR req_based_lines
2647 IS
2648 SELECT requisition_line_id ,
2649 line_number
2650 FROM pon_backing_requisitions
2651 WHERE auction_header_id = P_DOCUMENT_NUMBER;
2652 BEGIN
2653 logme(l_log_module, 'in maintain_clm_relations');
2654 FOR oneline IN req_based_lines
2655 LOOP
2656 UPDATE pon_auction_item_prices_all
2657 SET group_line_id = oneline.line_number
2658 WHERE auction_header_id = P_DOCUMENT_NUMBER
2659 AND group_line_id = oneline.requisition_line_id;
2660 UPDATE pon_auction_item_prices_all
2661 SET clm_base_line_num = oneline.line_number
2662 WHERE auction_header_id = P_DOCUMENT_NUMBER
2663 AND clm_base_line_num = oneline.requisition_line_id;
2664 END LOOP;
2665 EXCEPTION
2666 WHEN NO_DATA_FOUND THEN
2667 x_return_status := FND_API.G_RET_STS_SUCCESS;
2668 x_msg_count := 0;
2669 x_msg_data := NULL;
2670 logme(l_log_module, 'in NO_DATA_FOUND');
2671 RETURN;
2672 WHEN OTHERS THEN
2673 x_return_status := FND_API.G_RET_STS_ERROR ;
2674 x_msg_count := 1;
2675 fnd_message.set_name('PON', 'PON_AUC_PLSQL_ERR');
2676 fnd_message.set_token('PACKAGE','pon_auction_interface_pkg');
2677 fnd_message.set_token('PROCEDURE','maintain_clm_relations');
2678 fnd_message.set_token('ERROR', ' [' || SQLERRM || ']');
2679 --APP_EXCEPTION.RAISE_EXCEPTION;
2680 fnd_message.retrieve(x_msg_data);
2681 logme(l_log_module, 'in OTHERS' || x_msg_data);
2682 RETURN;
2683 END maintain_clm_relations;
2684 PROCEDURE maintain_clm_relations
2685 (
2686 p_api_version IN NUMBER,
2687 x_return_status OUT NOCOPY VARCHAR2,
2688 x_msg_count OUT NOCOPY NUMBER,
2689 x_msg_data OUT NOCOPY VARCHAR2,
2690 P_DOCUMENT_NUMBER IN NUMBER,
2691 P_line_number IN NUMBER,
2692 p_req_line_clin IN NUMBER,
2693 p_req_line_opt IN NUMBER)
2694 IS
2695 l_line_number NUMBER;
2696 BEGIN
2697 BEGIN
2698 IF ( p_req_line_clin IS NOT NULL ) THEN
2699 --first update group_line_id
2700 SELECT line_number
2701 INTO l_line_number
2702 FROM pon_backing_requisitions
2703 WHERE auction_header_id = p_document_number
2704 AND requisition_line_id = p_req_line_clin;
2705 UPDATE pon_auction_item_prices_all
2706 SET group_line_id = l_line_number
2707 WHERE auction_header_id = p_document_number
2708 AND line_number = p_line_number;
2709 END IF;
2710 EXCEPTION
2711 WHEN NO_DATA_FOUND THEN
2712 x_return_status := FND_API.G_RET_STS_SUCCESS;
2713 x_msg_count := 0;
2714 x_msg_data := NULL;
2715 END;
2716 BEGIN
2717 IF ( p_req_line_opt IS NOT NULL ) THEN
2718 --update base line for option
2719 SELECT line_number
2720 INTO l_line_number
2721 FROM pon_backing_requisitions
2722 WHERE auction_header_id = p_document_number
2723 AND requisition_line_id = p_req_line_opt;
2724 UPDATE pon_auction_item_prices_all
2725 SET clm_base_line_num = l_line_number
2726 WHERE auction_header_id = p_document_number
2727 AND line_number = p_line_number;
2728 END IF;
2729 EXCEPTION
2730 WHEN NO_DATA_FOUND THEN
2731 x_return_status := FND_API.G_RET_STS_SUCCESS;
2732 x_msg_count := 0;
2733 x_msg_data := NULL;
2734 END;
2735 x_return_status := FND_API.G_RET_STS_SUCCESS;
2736 x_msg_count := 0;
2737 x_msg_data := NULL;
2738 RETURN;
2739 END maintain_clm_relations;
2740 --
2741 END PON_AUCTION_INTERFACE_PKG;