[Home] [Help]
PACKAGE BODY: APPS.PO_NEGOTIATIONS_SV1
Source
1 PACKAGE BODY po_negotiations_sv1 AS
2 /* $Header: POXNEG1B.pls 120.22 2007/12/19 14:15:45 vdurbhak ship $*/
3
4 --<RENEG BLANKET FPI>
5 G_PKG_NAME CONSTANT varchar2(30) := 'po_negotiations_sv1';
6 -- Bug 3780359
7 g_log_head CONSTANT VARCHAR2(50) := 'po.plsql.'||G_PKG_NAME||'.';
8 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
9 g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
10
11 -- <HTMLAC START>
12 -- Start of comments
13 -- API name : create_negotiation_bulk
14 -- Type : Public
15 -- Pre-reqs : None.
16 -- Function : Calls create_negotiation, because from Java
17 -- the req_lines_table_type cannot be seen, as it is
18 -- declared inside a package
19 -- Parameters :
20 -- IN : p_api_version IN NUMBER Required
21 -- p_negotiation_type IN varchar2 Required
22 -- The negotiation type of the document
23 -- p_grouping_method IN varchar2 Required
24 -- The req grouping selected from the UI
25 -- p_req_line_id_tbl IN PO_TBL_NUMBER Required
26 -- The table containing the req_line_id column.
27 -- p_line_type_ids_tbl IN PO_TBL_NUMBER Required
28 -- The table containing the line_type_id column.
29 -- p_item_ids_tbl IN PO_TBL_NUMBER Required
30 -- The table containing the item_id column.
31 -- <ACHTML R12>
32 -- p_item_revisions_tbl IN PO_TBL_VARCHAR5 Required
33 -- The table containing the item_revision column.
34 -- p_category_ids_tbl IN PO_TBL_NUMBER Required
35 -- The table containing the req_line_id column.
36 -- p_quantities_tbl IN PO_TBL_NUMBER Required
37 -- The table containing the quantity column.
38 -- <ACHTML R12>
39 -- p_unit_meas_lookup_codes_tbl IN PO_TBL_VARCHAR30 Required
40 -- The table containing the unit_meas_lookup_code column.
41 -- p_job_ids_tbl IN PO_TBL_NUMBER Required
42 -- The table containing the job_id column.
43 -- p_neg_outcome IN varchar2 Required
44 -- The type of document for the negotiation outcome
45 -- p_document_org_id IN number Not Required
46 -- The org in which the out doc is to be created.
47 -- p_neg_style_id IN NUMBER Required
48 -- Style of Negotiation document to create.
49 -- p_outcome_style_id IN NUMBER Required
50 -- Style of Outcome PO document created from Negotiation.
51 -- IN/OUT: x_result IN/OUT NUMBER Required
52 -- x_error_message IN/OUT VARCHAR2 Required
53 -- x_negotiation_id IN/OUT number Required
54 -- The negotiation id of the doc created.
55 -- x_doc_url_params IN/OUT varchar2 Required
56 -- Any URL params returned by the sourcing call.
57 -- Version : Current version 1.0
58 -- Previous version 1.0
59 -- Initial version 1.0
60 -- End of comments
61 PROCEDURE create_negotiation_bulk
62 (
63 -- standard API params
64 p_api_version IN NUMBER,
65 x_result IN OUT NOCOPY NUMBER,
66 x_error_message IN OUT NOCOPY VARCHAR2,
67 -- input params
68 p_negotiation_type IN varchar2,
69 p_grouping_method IN varchar2,
70 -- table params in
71 p_req_line_id_tbl IN PO_TBL_NUMBER,
72 p_line_type_id_tbl IN PO_TBL_NUMBER,
73 p_item_id_tbl IN PO_TBL_NUMBER,
74 p_item_revision_tbl IN PO_TBL_VARCHAR5, -- <ACHTML R12>
75 p_category_id_tbl IN PO_TBL_NUMBER,
76 p_quantity_tbl IN PO_TBL_NUMBER,
77 p_unit_meas_lookup_code_tbl IN PO_TBL_VARCHAR30,-- <ACHTML R12>
78 p_job_id_tbl IN PO_TBL_NUMBER,
79 -- some more input params
80 p_neg_outcome IN varchar2,
81 p_document_org_id IN number,
82 p_neg_style_id IN NUMBER, -- <ACHTML R12>
83 p_outcome_style_id IN NUMBER, -- <ACHTML R12>
84 -- output params
85 x_negotiation_id IN OUT NOCOPY number,
86 x_doc_url_params IN OUT NOCOPY varchar2
87 )
88 IS
89 l_api_name CONSTANT VARCHAR2(30) := 'create_negotiation_bulk';
90 l_api_version CONSTANT NUMBER := 1.0;
91 l_error_code VARCHAR2(2000);
92 -- l_req_lines_tbl REQ_LINES_TABLE_TYPE; --Bug5841426
93 l_num_lines NUMBER;
94 BEGIN
95 -- Standard start of API savepoint
96 SAVEPOINT create_negotiation_bulk_SP;
97 -- Standard call to check for call compatibility
98 IF NOT FND_API.Compatible_API_Call( l_api_version ,
99 p_api_version ,
100 l_api_name ,
101 G_PKG_NAME)
102 THEN
103 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
104 END IF;
105
106 -- API Body
107 -- Get the number of lines being passed in.
108 l_num_lines := p_req_line_id_tbl.COUNT;
109
110 -- Go through the arrays and create the table to pass into
111 -- create_negotiation
112
113 --bug5841426 commented the following loop because the l_req_lines_tbl is
114 -- not used instead p_req_line_id_tbl is directly used to create the negotiation.
115 /*for i in 1..l_num_lines loop
116 l_req_lines_tbl(i).requisition_line_id := p_req_line_id_tbl(i);
117 l_req_lines_tbl(i).line_type_id := p_line_type_id_tbl(i);
118 l_req_lines_tbl(i).item_id := p_item_id_tbl(i);
119 l_req_lines_tbl(i).item_revision := p_item_revision_tbl(i);
120 l_req_lines_tbl(i).category_id := p_category_id_tbl(i);
121 l_req_lines_tbl(i).quantity := p_quantity_tbl(i);
122 l_req_lines_tbl(i).unit_meas_lookup_code := p_unit_meas_lookup_code_tbl(i);
123 l_req_lines_tbl(i).job_id := p_job_id_tbl(i);
124 end loop;*/
125
126 -- Finally, make the call to create_negotiation
127 create_negotiation(
128 x_negotiation_type => p_negotiation_type,
129 x_grouping_method => p_grouping_method,
130 p_neg_style_id => p_neg_style_id,
131 p_outcome_style_id => p_outcome_style_id,
132 t_req_lines => p_req_line_id_tbl, -- bug5841426
133 x_negotiation_id => x_negotiation_id,
134 x_doc_url_params => x_doc_url_params,
135 x_result => x_result,
136 x_error_code => l_error_code,
137 x_error_message => x_error_message,
138 p_neg_outcome => p_neg_outcome,
139 p_document_org_id => p_document_org_id);
140
141 -- End of API Body
142 -- Standard check to see if there is any error
143 IF(x_result = -1) THEN
144 RAISE FND_API.G_EXC_ERROR;
145 END IF;
146 EXCEPTION
147 WHEN FND_API.G_EXC_ERROR THEN
148 ROLLBACK TO create_negotiation_bulk_SP;
149 x_result := -1;
150 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
151 ROLLBACK TO create_negotiation_bulk_SP;
152 x_result := -1;
153 x_error_message := 'Unexpected error in create_negotiaton_bulk';
154 WHEN OTHERS THEN
155 ROLLBACK TO create_negotiation_bulk_SP;
156 x_result := -1;
157 x_error_message := 'Unhandled exception in create_negotiation_bulk';
158 END create_negotiation_bulk;
159 -- <HTMLAC END>
160
161 /*============================================================================
162 Name: CREATE_NEGOTIATION
163 DESC: Create document from requisition data in autocreate
164 dreddy : sourcing project
165 ==============================================================================*/
166
167 PROCEDURE create_negotiation(x_negotiation_type IN varchar2 ,
168 x_grouping_method IN varchar2 ,
169 t_req_lines IN PO_TBL_NUMBER, /* Changed the po_tbl_number to upper case for uniformity - bug 6631173 */ -- bug5841426
170 p_neg_style_id IN NUMBER, -- <ACHTML R12>
171 p_outcome_style_id IN NUMBER, -- <ACHTML R12>
172 x_negotiation_id IN OUT NOCOPY number,
173 x_doc_url_params IN OUT NOCOPY varchar2,
174 x_result IN OUT NOCOPY number,
175 x_error_code IN OUT NOCOPY varchar2,
176 x_error_message IN OUT NOCOPY varchar2,
177 --<RENEG BLANKET FPI>
178 p_neg_outcome IN varchar2,
179 --<HTMLAC>
180 p_document_org_id IN number DEFAULT null)
181 IS
182
183
184 x_org_id number := null;
185 api_result number := 0;
186 api_error_code varchar2(2000) := null;
187 api_error_msg varchar2(2000) := null;
188 x_ship_to_location_id number;
189 x_deliver_to_location_id number;
190 x_negotiation_line_num number;
191 x_num_records number;
192 x_req_header_id number;
193 x_note_to_vendor po_requisition_lines_all.note_to_vendor%TYPE;
194 x_need_by_date date := null;
195 x_req_num varchar2(30);
196 x_item_num varchar2(80);
197 x_item_desc varchar2(240);
198 x_unit_price number;
199 x_uom_code varchar2(3);
200
201 -- <SERVICES FPJ START>
202 --
203 l_value_basis PO_REQUISITION_LINES.order_type_lookup_code%TYPE;
204 l_req_line_id PO_REQUISITION_LINES.requisition_line_id%TYPE;
205 l_amount PO_REQUISITION_LINES.amount%TYPE;
206 l_job_name PER_JOBS_VL.name%TYPE;
207 l_job_long_description PO_REQUISITION_LINES.job_long_description%TYPE;
208 l_has_price_diff_flag VARCHAR2(1);
209 l_bid_start_price PO_REQUISITION_LINES.unit_price%TYPE;
210 l_po_agreed_amount PO_REQUISITION_LINES.amount%TYPE;
211
212 -- <ACHTML R12 START>
213 l_return_status VARCHAR2(1);
214 l_msg_count NUMBER;
215 l_msg_data VARCHAR2(2000);
216 -- <ACHTML R12 END>
217
218 /* --<R12 STYLES PHASE II START>
219 l_req_line_id_table PO_DOC_STYLE_PVT.g_po_tbl_num;
220 l_source_doc_id_table PO_DOC_STYLE_PVT.g_po_tbl_num;
221 l_line_type_id_table PO_DOC_STYLE_PVT.g_po_tbl_num;
222 l_destination_type_table PO_DOC_STYLE_PVT.g_po_tbl_char30;
223 l_purchase_basis_table PO_DOC_STYLE_PVT.g_po_tbl_char30;
224 l_style_id PO_DOC_STYLE_HEADERS.style_id%type;
225 l_neg_style_id PO_DOC_STYLE_HEADERS.style_id%type;
226 */ --<R12 STYLES PHASE II END>
227
228 CURSOR l_price_diff_csr ( p_req_line_id NUMBER ) IS
229 SELECT *
230 FROM po_price_differentials
231 WHERE entity_type = 'REQ LINE'
232 AND entity_id = p_req_line_id;
233 --
234 -- <SERVICES FPJ END>
235
236 --<HTMLAC START>
237 -- The conversion rate from the current org to the destination org
238 l_conv_rate NUMBER;
239 -- The organization of the req line.
240 l_from_org_id PO_REQUISITION_LINES_ALL.org_id%TYPE;
241 --<HTMLAC END>
242
243 x_progress varchar2(3);
244
245
246 -- bug5841426 <start>
247 TYPE t_crs is ref cursor;
248 l_cursor t_crs;
249 l_string VARCHAR2(10000);
250
251 x_line_type po_requisition_lines_all.LINE_TYPE_ID%type;
252 x_item_id po_requisition_lines_all.ITEM_ID%type;
253 x_item_rev po_requisition_lines_all.ITEM_REVISION%type;
254 x_category_id po_requisition_lines_all.CATEGORY_ID%type;
255 x_quantity po_requisition_lines_all.QUANTITY%type;
256 x_uom po_requisition_lines_all.UNIT_MEAS_LOOKUP_CODE%type ;
257 x_job_id po_requisition_lines_all.JOB_ID%type;
258 x_req_line_id po_requisition_lines_all.requisition_line_id%TYPE;
259 x_line_num po_requisition_lines_all.line_num%TYPE;
260 --bug5841426<end>
261
262 --bug6131913<start>
263
264 x_req_in_pool_flag VARCHAR2(1) :=NULL;
265 x_cancel_flag varchar2(1);
266 x_closed_code varchar2(25);
267
268 --bug6131913<end>
269
270
271
272
273
274 BEGIN
275 /* get the current org_id */
276 x_progress := '000';
277
278 --<HTMLAC START>
279 IF(p_document_org_id IS NULL) THEN
280 --<HTMLAC END>
281 -- You can pass in a null, or leave out this parameter, in which case
282 -- we need to get the org from the po_system_parameters table.
283 begin
284 select org_id
285 into x_org_id
286 from po_system_parameters;
287 exception
288 when others then
289 po_message_s.sql_error('In Exception of create_negotiation()', x_progress, sqlcode);
290 end;
291 --<HTMLAC START>
292 ELSE
293 x_org_id := p_document_org_id;
294 END IF;
295 --<HTMLAC END>
296
297 --<R12 STYLES PHASE II START>
298 /* count the number of records in the plsql table */
299 /*
300 x_num_records := t_req_lines.COUNT;
301 for i in 1..x_num_records loop
302
303 l_req_line_id_table(i) := t_req_lines(i).requisition_line_id;
304 l_source_doc_id_table(i):= t_req_lines(i).blanket_po_header_id;
305 l_line_type_id_table(i):= t_req_lines(i).line_type_id;
306 l_destination_type_table(i):= t_req_lines(i).destination_type_code;
307 l_purchase_basis_table(i):= t_req_lines(i).purchase_basis;
308 end loop;
309 PO_DOC_STYLE_PVT.populate_gt_and_validate(p_api_version => 1.0,
310 p_init_msg_list => FND_API.G_TRUE,
311 X_return_status => l_return_status,
312 X_msg_count => l_msg_count,
313 x_msg_data => l_msg_data,
314 p_req_line_id_table => l_req_line_id_table,
315 p_source_doc_id_table => l_source_doc_id_table,
316 p_line_type_id_table => l_line_type_id_table,
317 p_destination_type_table => l_destination_type_table,
318 p_purchase_basis_table => l_purchase_basis_table,
319 p_po_header_id => NULL,
320 x_style_id => l_style_id);
321
322 IF l_return_status <> 'S' THEN
323
324 x_result := -1;
325 x_error_message := FND_MESSAGE.get;
326 return;
327
328 END IF;
329 */
330 --<R12 STYLES PHASE II END>
331
332 /* Call the sourcing Header API to create the draft negotiation */
333 /* Adding parameter p_neg_outcome - RENEG BLANKET FPI */
334 -- <STYLES R12> Changed API call to use binding parameters.
335 x_progress := '001';
336 PON_AUCTION_INTERFACE_PKG.create_draft_negotiation
337 ( p_document_title => null -- IN
338 , p_document_type => x_negotiation_type --<RENEG BLANKET FPI> -- IN
339 , p_contract_type => p_neg_outcome -- IN
340 , p_origination_code => 'REQUISITION' -- IN
341 , p_org_id => x_org_id -- IN
342 , p_buyer_id => to_number(FND_PROFILE.VALUE('user_id')) -- IN
343 --<R12 STYLES PHASE II START>
344 , p_neg_style_id => p_neg_style_id -- <ACHTML R12> -- IN
345 , p_po_style_id => p_outcome_style_id -- <ACHTML R12> -- IN
346 --<R12 STYLES PHASE II END>
347 , p_document_number => x_negotiation_id -- OUT
348 , p_document_url => x_doc_url_params -- OUT
349 , p_result => api_result -- OUT
350 , p_error_code => api_error_code -- OUT
351 , p_error_message => api_error_msg -- OUT
352 );
353
354 if api_result <> 0 THEN
355 /* If unsuccessful return the error code to the form. */
356 x_progress := '002';
357 x_result := -1;
358 x_error_code := api_error_code;
359 x_error_message := api_error_msg;
360 return ;
361
362 end if;
363
364
365 --bug 6131913<start>
366 /*****
367 This Code is to check if the Requisition Line which we are trying to AutoCreate has already been locked or deleted or cancelled.
368 If so, simply return to the caller and show a message saying Requisition Line can't be auto created.
369 *****/
370
371 BEGIN
372
373 SAVEPOINT CHECK_LOCK;
374
375
376 for i in 1..(t_req_lines.count)-1
377 loop
378
379 x_req_in_pool_flag:=NULL;
380
381 BEGIN
382
383 SELECT Nvl(reqs_in_pool_flag,'Y'),cancel_flag,closed_code
384 INTO x_req_in_pool_flag,
385 x_cancel_flag,
386 x_closed_code
387 FROM po_requisition_lines WHERE requisition_line_id=t_req_lines(i)
388 FOR UPDATE OF auction_header_id NOWAIT;
389
390
391 EXCEPTION
392
393 WHEN NO_DATA_FOUND then
394 /* The req line has been deleted since it was queried up. */
395 x_result:=-1;
396 fnd_message.set_name('PO', 'PO_ALL_REQ_LINE_DLTD_CANT_AC');
397 /*
398 SELECT prh.segment1,prl.line_num INTO x_req_num,x_line_num FROM po_requisition_headers_all prh,po_requisition_lines_all prl
399 WHERE prh.requisition_header_id = prl.requisition_header_id
400 AND prl.requisition_line_id = t_req_lines(i);
401
402 fnd_message.set_token('REQ_NUM', x_req_num);
403 fnd_message.set_token('REQ_LINE_NUM',x_line_num);
404 */
405 rollback to CHECK_LOCK;
406 x_error_message := fnd_message.get;
407 RETURN;
408
409 WHEN OTHERS THEN
410 /* This is to see if req line is locked */
411 if (SQLCODE=-54)
412 THEN
413 x_result:=-1;
414 fnd_message.set_name('PO', 'PO_ALL_RQ_LINE_LOCKED_CANT_AC');
415 SELECT prh.segment1,prl.line_num INTO x_req_num,x_line_num FROM po_requisition_headers_all prh,po_requisition_lines_all prl
416 WHERE prh.requisition_header_id = prl.requisition_header_id
417 AND prl.requisition_line_id = t_req_lines(i);
418
419 fnd_message.set_token('REQ_NUM', x_req_num);
420 fnd_message.set_token('REQ_LINE_NUM',x_line_num);
421
422 x_error_message := fnd_message.get;
423 rollback to CHECK_LOCK;
424 RETURN;
425 else
426 x_result:=-1;
427 raise;
428 end if;
429
430 END;
431
432 if (x_req_in_pool_flag='N')
433 then
434 /* The req line has been auto created already. */
435 x_result:=-1;
436 fnd_message.set_name('PO', 'PO_ALL_RQ_LINE_ALREADY_AC');
437 SELECT prh.segment1,prl.line_num INTO x_req_num,x_line_num FROM po_requisition_headers_all prh,po_requisition_lines_all prl
438 WHERE prh.requisition_header_id = prl.requisition_header_id
439 AND prl.requisition_line_id = t_req_lines(i);
440
441 fnd_message.set_token('REQ_NUM', x_req_num);
442 fnd_message.set_token('REQ_LINE_NUM',x_line_num);
443
444
445 x_error_message := fnd_message.get;
446 ROLLBACK TO CHECK_LOCK;
447 RETURN;
448
449 elsif (x_cancel_flag = 'Y') then
450 /* The req line has been cancelled. */
451 x_result:=-1;
452 fnd_message.set_name('PO', 'PO_ALL_RQ_LINE_CNCLD_CANT_AC');
453 SELECT prh.segment1,prl.line_num INTO x_req_num,x_line_num FROM po_requisition_headers_all prh,po_requisition_lines_all prl
454 WHERE prh.requisition_header_id = prl.requisition_header_id
455 AND prl.requisition_line_id = t_req_lines(i);
456
457 fnd_message.set_token('REQ_NUM', x_req_num);
458 fnd_message.set_token('REQ_LINE_NUM',x_line_num);
459
460
461 x_error_message := fnd_message.get;
462 ROLLBACK TO CHECK_LOCK;
463 RETURN;
464
465 elsif (x_closed_code = 'FINALLY CLOSED') then
466 /* The req line has been auto created already. */
467 x_result:=-1;
468 fnd_message.set_name('PO', 'PO_ALL_RQ_LINE_FCLSD_CANT_AC');
469 SELECT prh.segment1,prl.line_num INTO x_req_num,x_line_num FROM po_requisition_headers_all prh,po_requisition_lines_all prl
470 WHERE prh.requisition_header_id = prl.requisition_header_id
471 AND prl.requisition_line_id = t_req_lines(i);
472
473 fnd_message.set_token('REQ_NUM', x_req_num);
474 fnd_message.set_token('REQ_LINE_NUM',x_line_num);
475
476
477 x_error_message := fnd_message.get;
478 ROLLBACK TO CHECK_LOCK;
479 RETURN;
480
481 end if;
482
483
484
485 END LOOP;
486
487 END;
488
489 --bug 6131913<end>
490
491 x_progress := '003';
492 /* count the number of records in the plsql table */
493 x_num_records := t_req_lines.COUNT;
494
495 --bug 6131913
496
497 DELETE FROM po_session_gt WHERE index_char1='PO_NEGOTIATIONS_SV1';
498
499
500
501 --bug 5841426<start>
502 FORALL i IN 1..t_req_lines.Count
503 INSERT INTO po_session_gt (KEY, index_char1, char1, num1, num2, num3, date1)
504 SELECT prl.requisition_line_id,
505 'PO_NEGOTIATIONS_SV1',
506 prh.segment1,
507 prl.line_num,
508 prl.item_id,
509 prl.category_id,
510 decode (prl.purchase_basis , 'TEMP LABOR' , prl.assignment_start_date , prl.need_by_date )
511 FROM po_requisition_lines_all prl,
512 po_requisition_headers_all prh
513 WHERE prh.requisition_header_id = prl.requisition_header_id
514 AND prl.requisition_line_id = t_req_lines(i);
515
516
517 /* For each req line in the plsql table we call the sourcing line API */
518
519 -- for i in 1..x_num_records loop --Bug5841426
520
521 --l_req_line_id := t_req_lines(i).requisition_line_id;-- <SERVICES FPJ>
522
523 /* get the info from the req line which is not available in the client side */
524 -- begin
525 -- x_progress := '005';
526 l_string := ' select rl.requisition_line_id,
527 rl.LINE_TYPE_ID, -- line_type_id
528 rl.ITEM_ID, -- item_id
529 rl.ITEM_REVISION, -- item_revision
530 rl.CATEGORY_ID, -- category id
531 rl.QUANTITY, -- quantity
532 rl.UNIT_MEAS_LOOKUP_CODE, -- unit meas lookup code
533 rl.JOB_ID, -- job id
534 rl.requisition_header_id,
535 psg.char1,
536 RL.order_type_lookup_code, -- <SERVICES FPJ>
537 rl.note_to_vendor,
538 rl.need_by_date,
539 RL.amount, -- <SERVICES FPJ>
540 msi.concatenated_segments,
541 rl.deliver_to_location_id,
542 rl.unit_price,
543 rl.item_description,
544 PJ.name, -- <SERVICES FPJ>
545 RL.job_long_description, -- <SERVICES FPJ>
546 RL.org_id -- <HTMLAC>
547
548
549 /*into x_req_header_id,
550 l_value_basis, -- <SERVICES FPJ>
551 x_note_to_vendor,
552 x_need_by_date,
553 l_amount, -- <SERVICES FPJ>
554 x_item_num,
555 x_deliver_to_location_id,
556 x_unit_price,
557 x_item_desc,
558 l_job_name, -- <SERVICES FPJ>
559 l_job_long_description, -- <SERVICES FPJ>
560 l_from_org_id */ -- <HTMLAC>
561 from po_requisition_lines_all rl, -- <HTMLAC>
562 mtl_system_items_kfv msi,
563 per_jobs_vl PJ,
564 PO_SESSION_GT psg -- <SERVICES FPJ>
565 where requisition_line_id = psg.key -- <SERVICES FPJ>
566 and rl.item_id = msi.inventory_item_id(+)
567 and nvl(msi.organization_id, rl.destination_organization_id) =
568 rl.destination_organization_id
569 AND RL.job_id = PJ.job_id(+) -- <SERVICES FPJ>
570 AND psg.index_char1 = ''PO_NEGOTIATIONS_SV1''' ;
571
572 if (x_grouping_method = 'REQUISITION') then
573 l_string := l_string || ' ORDER BY psg.char1, psg.num1';
574 ELSE
575 l_string := l_string || ' order by psg.num2, psg.num3, psg.date1, psg.key';
576 END IF;
577
578 OPEN l_cursor FOR l_string;
579
580 loop
581 FETCH l_cursor INTO x_req_line_id ,
582 x_line_type ,
583 x_item_id ,
584 x_item_rev ,
585 x_category_id ,
586 x_quantity ,
587 x_uom ,
588 x_job_id ,
589 x_req_header_id ,
590 x_req_num ,
591 l_value_basis,
592 x_note_to_vendor,
593 x_need_by_date,
594 l_amount,
595 x_item_num,
596 x_deliver_to_location_id,
597 x_unit_price,
598 x_item_desc,
599 l_job_name,
600 l_job_long_description,
601 l_from_org_id ;
602 IF l_cursor%NOTFOUND THEN
603 EXIT;
604 END IF;
605
606 /* exception
607 when others then
608 po_message_s.sql_error('In Exception of create_negotiation()', x_progress, sqlcode);
609 end;*/
610
611 /* begin
612 x_progress := '006';
613 select segment1
614 into x_req_num
615 from po_requisition_headers_all -- <HTMLAC>
616 where requisition_header_id=x_req_header_id;
617 exception
618 when others then
619 po_message_s.sql_error('In Exception of create_negotiation()', x_progress, sqlcode);
620 end;*/
621 --Bug 5841426<end>
622
623 /*
624 ** Get the ship to location id associated with the deliver to location.
625 ** This may then used to get the tax name, if the tax system parameters are
626 ** set up to retrieve the tax code based on ship-to location. */
627
628 BEGIN
629 x_progress := '004';
630 SELECT nvl(ship_to_location_id,location_id)
631 INTO x_ship_to_location_id
632 FROM hr_locations
633 WHERE location_id = x_deliver_to_location_id;
634
635 EXCEPTION
636 WHEN NO_DATA_FOUND THEN
637 x_ship_to_location_id := x_deliver_to_location_id;
638 END;
639
640 /* Sourcing expects the uom_code whereas req lines stores unit_of_measure .
641 need to get the correct value */
642 begin
643 select mum.uom_code
644 into x_uom_code
645 from mtl_units_of_measure mum
646 where mum.unit_of_measure = x_uom; --bug 5841426
647 exception
648 when others then
649 x_uom_code := null;
650 end;
651
652 -- <SERVICES FPJ START>
653
654 -- Determine if this Requisition Line has Price Differentials.
655 --
656 IF ( PO_PRICE_DIFFERENTIALS_PVT.has_price_differentials
657 ( p_entity_type => 'REQ LINE'
658 , p_entity_id => x_req_line_id) --Bug 5841426
659 )
660 THEN
661 l_has_price_diff_flag := 'Y';
662 ELSE
663 l_has_price_diff_flag := 'N';
664 END IF;
665
666 --<HTMLAC START>
667 -- Get The Conversion rate from the from_ou to the to_ou
668 -- If the l_conv_rate is null, means there is no conversion
669 -- rate between the different OUs, then we pass in null for
670 -- the amount and the unit_price
671 l_conv_rate := PO_CURRENCY_SV.get_cross_ou_rate(
672 l_from_org_id,
673 x_org_id);
674 --<HTMLAC END>
675
676 -- Determine which value to pass in for BID_START_PRICE and
677 -- PO_AGREED_AMOUNT. For Fixed Price lines, we will pass the
678 -- Req Line Amount into the Bid Start Price.
679 --
680 IF ( l_value_basis = 'FIXED PRICE' )
681 THEN
682 l_bid_start_price := l_amount * l_conv_rate; --<HTMLAC>
683 l_po_agreed_amount := NULL;
684 ELSE
685 l_bid_start_price := x_unit_price * l_conv_rate; --<HTMLAC>
686 l_po_agreed_amount := l_amount * l_conv_rate; --<HTMLAC>
687 END IF;
688
689 -- <SERVICES FPJ END>
690
691 /* call the sourcing api to add the requisition lines to the above negotiation */
692 /* Adding parameter p_neg_outcome - RENEG BLANKET FPI */
693 x_progress := '007';
694 PON_AUCTION_INTERFACE_PKG.add_negotiation_line
695 ( p_document_number => x_negotiation_id
696 , p_contract_type => p_neg_outcome
697 , p_origination_code => 'REQUISITION'
698 , p_org_id => x_org_id
699 , p_buyer_id => to_number(FND_PROFILE.VALUE('user_id'))
700 , p_grouping_type => x_grouping_method
701 , p_requisition_header_id => x_req_header_id
702 , p_requisition_number => x_req_num
703 , p_requisition_line_id => x_req_line_id
704 , p_line_type_id => x_line_type --Bug5841426
705 , p_category_id => x_category_id
706 , p_item_description => x_item_desc
707 , p_item_id => x_item_id --Bug5841426
708 , p_item_number => x_item_num --Bug5841426
709 , p_item_revision => x_item_rev
710
711 , p_uom_code => x_uom_code
712 , p_quantity => x_quantity
713
714 , p_need_by_date => x_need_by_date
715 , p_ship_to_location_id => x_ship_to_location_id
716 , p_note_to_vendor => x_note_to_vendor
717 , p_price => l_bid_start_price -- <SERVICES FPJ>
718 , p_job_id => x_job_id-- <SERVICES FPJ> --Bug5841426
719
720 , p_job_details => l_job_long_description-- <SERVICES FPJ>
721 , p_po_agreed_amount => l_po_agreed_amount -- <SERVICES FPJ>
722 , p_has_price_diff_flag => l_has_price_diff_flag -- <SERVICES FPJ>
723 , p_line_number => x_negotiation_line_num
724 , p_result => api_result
725 , p_error_code => api_error_code
726 , p_error_message => api_error_msg
727 );
728
729 If api_result = 0 then
730
731 begin
732
733 x_progress := '008';
734 update po_requisition_lines_all --<HTMLAC>
735 set on_rfq_flag = 'Y',
736 auction_header_id = x_negotiation_id,
737 auction_display_number = to_char(x_negotiation_id) ,
738 auction_line_number = x_negotiation_line_num,
739 at_sourcing_flag = 'Y', -- <REQINPOOL>
740 reqs_in_pool_flag = NULL, -- <REQINPOOL>
741 last_update_date = sysdate,
742 last_updated_by = to_number(FND_PROFILE.VALUE('user_id')),
743 last_update_login = to_number(FND_PROFILE.VALUE('user_id'))
744 where requisition_line_id = x_req_line_id; --5841426
745
746 exception
747 when others then
748 po_message_s.sql_error('In Exception of create_negotiation()', x_progress, sqlcode);
749 end;
750
751 -- <SERVICES FPJ START> For each Price Differential,
752 -- call Sourcing API to add it.
753 --
754 IF ( l_has_price_diff_flag = 'Y' ) THEN
755
756 FOR l_price_diff_rec IN l_price_diff_csr(x_req_line_id) LOOP --Bug5841426
757
758 PON_AUCTION_INTERFACE_PKG.add_price_differential
759 ( p_document_number => x_negotiation_id
760 , p_line_number => x_negotiation_line_num
761 , p_shipment_number => -1
762 , p_price_type => l_price_diff_rec.price_type
763 , p_multiplier => l_price_diff_rec.min_multiplier
764 , p_buyer_id => FND_PROFILE.value('user_id')
765 , p_price_differential_number => l_price_diff_rec.price_differential_num
766 , p_result => api_result
767 , p_error_code => api_error_code
768 , p_error_message => api_error_msg
769 );
770
771 IF ( api_result <> 0 ) -- API failure
772 THEN
773 x_result := api_result;
774 x_error_code := api_error_code;
775 x_error_message := api_error_msg;
776 rollback;
777 return;
778 END IF;
779
780 END LOOP;
781
782 END IF;
783 --
784 -- <SERVICES FPJ END>
785
786 x_result := api_result;
787 x_error_code := api_error_code;
788 x_error_message := api_error_msg;
789
790 else
791 /* If unsuccessful return the error code to the form. */
792 x_progress := '009';
793 x_result := api_result;
794 x_error_code := api_error_code;
795 x_error_message := api_error_msg;
796
797 /* before returning we rollback and return */
798 rollback;
799 return ;
800 end if;
801
802 end loop;
803
804 x_progress := '010';
805
806 -- <ACHTML R12 START>
807 PON_AUCTION_INTERFACE_PKG.add_negotiation_invitees
808 ( p_api_version => 1.0 -- IN
809 , x_return_status => l_return_status -- OUT
810 , x_msg_count => l_msg_count -- OUT
811 , x_msg_data => l_msg_data -- OUT
812 , p_document_number => x_negotiation_id -- IN
813 , p_buyer_id => to_number(FND_PROFILE.VALUE('user_id')) -- IN
814 );
815
816 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
817 x_progress := '020';
818 x_result := -1;
819 x_error_message := l_msg_data;
820
821 -- before returning we rollback and return
822 rollback;
823 return ;
824 end if;
825 -- <ACHTML R12 END>
826
827 -- <Catalog Convergence R12 START>
828 PON_AUCTION_INTERFACE_PKG.add_catalog_descriptors
829 ( p_api_version => 1.0
830 , p_document_number => x_negotiation_id
831 , x_return_status => l_return_status
832 , x_msg_count => l_msg_count
833 , x_msg_data => l_msg_data
834 );
835
836 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
837 x_progress := '030';
838 x_result := -1;
839 x_error_message := l_msg_data;
840
841 -- before returning we rollback and return
842 ROLLBACK;
843 return ;
844 END IF;
845 -- <Catalog Convergence R12 END>
846
847 /* issue commit */
848 commit;
849
850 EXCEPTION
851 WHEN OTHERS THEN
852 rollback;
853 x_result := -1;
854 --<HTMLAC START>
855 IF (x_error_message IS NULL) THEN
856 x_error_message := 'In Exception of create_negotiation:' || x_progress || ':' || fnd_message.get;
857 END IF;
858 --<HTMLAC END>
859 po_message_s.sql_error('In Exception of create_negotiation()', x_progress, sqlcode);
860 END;
861
862 /*============================================================================
863 Name: DELETE_NEGOTIATION_REF
864 DESC: Delete negotiation reference from the backing requisition
865 ==============================================================================*/
866
867 PROCEDURE DELETE_NEGOTIATION_REF (x_negotiation_id in number,
868 x_negotiation_line_num in number,
869 x_error_code out NOCOPY varchar2) is
870
871 BEGIN
872 if x_negotiation_line_num is null then
873
874 -- <REQINPOOL>: added update of at_sourcing_flag and of
875 -- WHO columns.
876 update po_requisition_lines_all prla --Bug 4001965: use _all
877 set auction_header_id = null,
878 auction_display_number = null,
879 auction_line_number = null,
880 at_sourcing_flag = null, --<REQINPOOL>
881 on_rfq_flag = null, -- bug 5370213
882 --<Begin Bug#: 5203799> We don't want to set the reqs_in_pool_flag to 'Y'
883 --if any of the following conditions are met.
884 reqs_in_pool_flag =
885 (CASE
886 WHEN (nvl(modified_by_agent_flag,'N') = 'Y'
887 or NVL(cancel_flag,'N') IN ('Y', 'I')
888 or NVL(closed_code,'OPEN') = 'FINALLY CLOSED'
889 or source_type_code = 'INVENTORY'
890 or NVL(line_location_id, -999) <> -999
891 or exists
892 (select 'Req Header auth_status is not approved or contractor_status is pending'
893 from po_requisition_headers_all prha
894 where prha.requisition_header_id = prla.requisition_header_id
895 and (NVL(prha.authorization_status,'INCOMPLETE') <> 'APPROVED'
896 or NVL(prha.contractor_status,'NOT_APPLICABLE') = 'PENDING')))
897 THEN null
898 ELSE 'Y'
899 END
900 ), --<End Bug#: 5203799>
901 last_update_date = SYSDATE,
902 last_updated_by = FND_GLOBAL.USER_ID,
903 last_update_login = FND_GLOBAL.LOGIN_ID
904 where auction_header_id = x_negotiation_id;
905
906 else
907 -- <REQINPOOL>: added update of at_sourcing_flag and of
908 -- WHO columns.
909 update po_requisition_lines_all prla --Bug 4001965: use _all
910 set auction_header_id = null,
911 auction_display_number = null,
912 auction_line_number = null,
913 at_sourcing_flag = null, --<REQINPOOL>
914 on_rfq_flag = null, -- bug 5370213
915 --<Begin Bug#: 5203799> We don't want to set the reqs_in_pool_flag to 'Y'
916 --if any of the following conditions are met.
917 reqs_in_pool_flag =
918 (CASE
919 WHEN (nvl(modified_by_agent_flag,'N') = 'Y'
920 or NVL(cancel_flag,'N') IN ('Y', 'I')
921 or NVL(closed_code,'OPEN') = 'FINALLY CLOSED'
922 or source_type_code = 'INVENTORY'
923 or NVL(line_location_id, -999) <> -999
924 or exists
925 (select 'Req Header auth_status is not approved or contractor_status is pending'
926 from po_requisition_headers_all prha
927 where prha.requisition_header_id = prla.requisition_header_id
928 and (NVL(prha.authorization_status,'INCOMPLETE') <> 'APPROVED'
929 or NVL(prha.contractor_status,'NOT_APPLICABLE') = 'PENDING')))
930 THEN null
931 ELSE 'Y'
932 END
933 ), --<End Bug#: 5203799>
934 last_update_date = SYSDATE,
935 last_updated_by = FND_GLOBAL.USER_ID,
936 last_update_login = FND_GLOBAL.LOGIN_ID
937 where auction_header_id = x_negotiation_id
938 and auction_line_number = x_negotiation_line_num;
939 end if;
940
941 x_error_code := 'SUCCESS';
942
943 EXCEPTION
944 WHEN OTHERS THEN
945 x_error_code := 'FAILURE';
946 END;
947
948 /*============================================================================
949 Name: UPDATE_NEGOTIATION_REF
950 DESC: Update negotiation reference in the backing requisition
951 ==============================================================================*/
952
953 PROCEDURE UPDATE_NEGOTIATION_REF (x_old_negotiation_id in number ,
954 x_new_negotiation_id in number ,
955 x_new_negotiation_num in varchar2 ,
956 x_error_code out NOCOPY varchar2) is
957 BEGIN
958 update po_requisition_lines_all --Bug 4001965: use _all
959 set auction_header_id = x_new_negotiation_id,
960 auction_display_number = x_new_negotiation_num
961 where auction_header_id = x_old_negotiation_id;
962
963 x_error_code := 'SUCCESS';
964
965 EXCEPTION
966 WHEN OTHERS THEN
967 x_error_code := 'FAILURE';
968 END;
969
970 --<Bug 2440254 mbhargav START>
971 --Provide Sourcing with an API which Given two negotiation lines,
972 --update all requisition line from one negotiation line to
973 --point to another negotiation line.
974 /*============================================================================
975 Name: UPDATE_NEGOTIATION_LINE_REF
976 DESC: Update negotiation reference in the backing requisition line to
977 point to another negotiation line.
978 Input parameters :
979 p_api_version: Version of the API expected by caller. Current value 1.0
980 p_old_negotiation_id : negotiation whose reference has to be replaced
981 p_old_negotiation_line_num : negotiation line whose reference has to be replaced
982 p_new_negotiation_num/id : new negotiation reference
983 p_new_negotiation_line_num : new negotiation line where reference has
984 to be added
985 Output parameters :
986 x_return_status: The return status of the API. Valid values are:
987 FND_API.G_RET_STS_SUCCESS
988 FND_API.G_RET_STS_ERROR
989 FND_API.G_RET_STS_UNEXP_ERROR
990 x_error_message: Contain translated error message in case the return status
991 is G_RET_STS_ERROR or G_RET_STS_UNEXP_ERROR
992 Version: Current Version 1.0
993 Changed: Initial design 1/27/2003
994 Previous Version 1.0
995 ==============================================================================*/
996
997 PROCEDURE UPDATE_NEGOTIATION_LINE_REF (
998 p_api_version IN NUMBER,
999 p_old_negotiation_id IN NUMBER,
1000 p_old_negotiation_line_num IN NUMBER,
1001 p_new_negotiation_id IN NUMBER,
1002 p_new_negotiation_line_num IN NUMBER,
1003 p_new_negotiation_num IN varchar2,
1004 x_return_status OUT NOCOPY varchar2,
1005 x_error_message OUT NOCOPY varchar2) is
1006
1007 l_api_name CONSTANT varchar2(30) := 'UPDATE_NEGOTIATION_LINE_REF';
1008 l_api_version CONSTANT NUMBER := 1.0;
1009
1010 l_progress varchar2(3);
1011 BEGIN
1012 l_progress := '000';
1013
1014 -- Standard call to check for call compatibility
1015 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
1016 THEN
1017 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1018 END IF;
1019
1020 l_progress := '001';
1021
1022 update po_requisition_lines_all --Bug 4001965: use _all
1023 set auction_header_id = p_new_negotiation_id,
1024 auction_display_number = p_new_negotiation_num,
1025 auction_line_number = p_new_negotiation_line_num
1026 where auction_header_id = p_old_negotiation_id and
1027 auction_line_number = p_old_negotiation_line_num;
1028
1029 x_return_status := FND_API.G_RET_STS_SUCCESS;
1030
1031 EXCEPTION
1032 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1033 x_error_message := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,
1034 p_encoded => 'F');
1035 x_return_status := FND_API.G_RET_STS_ERROR;
1036 WHEN OTHERS THEN
1037 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1038 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_api_name,
1039 SUBSTRB (SQLERRM , 1 , 200) || ' at location ' || l_progress);
1040 END IF;
1041
1042 x_error_message := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,
1043 p_encoded => 'F');
1044 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1045
1046 END UPDATE_NEGOTIATION_LINE_REF;
1047 --<Bug 2440254 mbhargav END>
1048
1049 /*============================================================================
1050 Name: UPDATE_REQ_POOL
1051 DESC: Update requisition pool flag in the backing requisition
1052 ==============================================================================*/
1053
1054 PROCEDURE UPDATE_REQ_POOL (x_negotiation_id in number,
1055 x_negotiation_line_num in number,
1056 x_flag_value in varchar2,
1057 x_error_code out NOCOPY varchar2) is
1058
1059 x_sourcing_flag_value PO_REQUISITION_LINES_ALL.at_sourcing_flag%TYPE; -- <REQINPOOL>
1060 x_new_pool_value PO_REQUISITION_LINES_ALL.reqs_in_pool_flag%TYPE; -- <REQINPOOL>
1061 BEGIN
1062 -- <REQINPOOL>: check sourcing flag value and convert to new Y/NULL
1063 -- domain for reqs_in_pool_flag and new col at_sourcing_flag
1064 IF(x_flag_value = 'N') THEN
1065 x_sourcing_flag_value := 'Y';
1066 x_new_pool_value := NULL;
1067 ELSE
1068 x_sourcing_flag_value := NULL;
1069 x_new_pool_value := 'Y';
1070 END IF;
1071
1072
1073 if x_negotiation_line_num is null then
1074 -- <REQINPOOL>: added update of at_sourcing_flag and of
1075 -- WHO columns.
1076 update po_requisition_lines_all prla --Bug 4001965: use _all
1077 set reqs_in_pool_flag = x_new_pool_value,
1078 at_sourcing_flag = x_sourcing_flag_value, --<REQINPOOL>
1079 last_update_date = SYSDATE,
1080 last_updated_by = FND_GLOBAL.USER_ID,
1081 last_update_login = FND_GLOBAL.LOGIN_ID
1082 where auction_header_id = x_negotiation_id
1083 --<Begin Bug#: 5203799> We only want to set the reqs_in_pool_flag to 'Y'
1084 --if all of the following conditions are met.
1085 and nvl(modified_by_agent_flag,'N') <> 'Y' --<BUG#: 5067460 ,BUG#:4957635>
1086 and NVL(cancel_flag,'N') NOT IN ('Y', 'I')
1087 and NVL(closed_code,'OPEN') <> 'FINALLY CLOSED'
1088 and source_type_code <> 'INVENTORY'
1089 and NVL(line_location_id, -999) = -999
1090 and not exists
1091 (select 'Req Header auth_status is not approved or contractor_status is pending'
1092 from po_requisition_headers_all prha
1093 where prha.requisition_header_id = prla.requisition_header_id
1094 and (NVL(prha.authorization_status,'INCOMPLETE') <> 'APPROVED'
1095 or NVL(prha.contractor_status,'NOT_APPLICABLE') = 'PENDING'));
1096 --<End Bug#: 5203799>
1097
1098 else
1099 -- <REQINPOOL>: added update of at_sourcing_flag and of
1100 -- WHO columns.
1101 update po_requisition_lines_all prla --Bug 4001965: use _all
1102 set reqs_in_pool_flag = x_new_pool_value,
1103 at_sourcing_flag = x_sourcing_flag_value, --<REQINPOOL>
1104 last_update_date = SYSDATE,
1105 last_updated_by = FND_GLOBAL.USER_ID,
1106 last_update_login = FND_GLOBAL.LOGIN_ID
1107 where auction_header_id = x_negotiation_id
1108 and auction_line_number = x_negotiation_line_num
1109 --<Begin Bug#: 5203799> We only want to set the reqs_in_pool_flag to 'Y'
1110 --if all of the following conditions are met.
1111 and nvl(modified_by_agent_flag,'N') <> 'Y' --<BUG#: 5067460 ,BUG#:4957635>
1112 and NVL(cancel_flag,'N') NOT IN ('Y', 'I')
1113 and NVL(closed_code,'OPEN') <> 'FINALLY CLOSED'
1114 and source_type_code <> 'INVENTORY'
1115 and NVL(line_location_id, -999) = -999
1116 and not exists
1117 (select 'Req Header auth_status is not approved or contractor_status is pending'
1118 from po_requisition_headers_all prha
1119 where prha.requisition_header_id = prla.requisition_header_id
1120 and (NVL(prha.authorization_status,'INCOMPLETE') <> 'APPROVED'
1121 or NVL(prha.contractor_status,'NOT_APPLICABLE') = 'PENDING'));
1122 --<End Bug#: 5203799>
1123 end if;
1124
1125 x_error_code := 'SUCCESS';
1126
1127 EXCEPTION
1128 WHEN OTHERS THEN
1129 x_error_code := 'FAILURE';
1130 END;
1131
1132 /*============================================================================
1133 Name: check_negotiation_ref
1134 DESC: checks if a req line/header has negotiation reference
1135 ==============================================================================*/
1136
1137 PROCEDURE check_negotiation_ref(x_doc_level IN VARCHAR2,
1138 x_doc_id IN NUMBER,
1139 x_negotiation_ref_flag IN OUT NOCOPY varchar2) is
1140
1141 cursor c1(x_doc_line_id in number) is
1142 select at_sourcing_flag --<REQINPOOL>
1143 from po_requisition_lines_all --Bug 4001965: use _all
1144 where requisition_line_id = X_doc_line_id;
1145
1146 cursor c2(x_doc_header_id in number) is
1147 select at_sourcing_flag --<REQINPOOL>
1148 from po_requisition_lines_all --Bug 4001965: use _all
1149 where requisition_header_id = X_doc_header_id;
1150
1151 x_doc_line_id number;
1152 x_doc_header_id number;
1153 x_sourcing_flag PO_REQUISITION_LINES_ALL.at_sourcing_flag%TYPE; --<REQINPOOL>
1154 x_sourcing_install_status varchar2(1);
1155
1156 BEGIN
1157
1158 if x_doc_level = 'REQ LINE' then
1159
1160 x_doc_line_id := x_doc_id;
1161 open c1(x_doc_line_id);
1162 loop
1163 fetch c1 into x_sourcing_flag;
1164 EXIT WHEN c1%NOTFOUND;
1165
1166 if x_sourcing_flag = 'Y' then
1167 x_negotiation_ref_flag := 'Y';
1168 end if;
1169
1170 end loop;
1171 close c1;
1172
1173 elsif x_doc_level = 'REQ HEADER' then
1174
1175 x_doc_header_id := x_doc_id;
1176 open c2(x_doc_header_id);
1177 loop
1178 fetch c2 into x_sourcing_flag;
1179 EXIT WHEN c2%NOTFOUND;
1180
1181 if x_sourcing_flag ='Y' then
1182 x_negotiation_ref_flag := 'Y';
1183 exit;
1184 end if;
1185 end loop;
1186 close c2;
1187
1188 end if;
1189
1190
1191 END;
1192
1193 PROCEDURE renegotiate_blanket( p_api_version IN NUMBER,
1194 p_commit IN varchar2,
1195 p_po_header_id IN NUMBER,
1196 p_negotiation_type IN varchar2,
1197 x_negotiation_id OUT NOCOPY NUMBER,
1198 x_doc_url_params OUT NOCOPY varchar2,
1199 x_return_status OUT NOCOPY varchar2,
1200 x_error_code OUT NOCOPY varchar2,
1201 x_error_message OUT NOCOPY varchar2) IS
1202 l_large_negotiation VARCHAR2(1);
1203 l_large_neg_request_id NUMBER;
1204
1205 BEGIN
1206 renegotiate_blanket( p_api_version ,
1207 p_commit ,
1208 p_po_header_id ,
1209 p_negotiation_type ,
1210 x_negotiation_id ,
1211 x_doc_url_params ,
1212 x_return_status ,
1213 x_error_code ,
1214 x_error_message ,
1215 l_large_negotiation ,
1216 l_large_neg_request_id ) ;
1217 END;
1218
1219 --<RENEG BLANKET FPI START>
1220 /*============================================================================
1221 Name : RENEGOTIATE_BLANKET
1222 Type : Private
1223 Function : This procedure
1224 a. populates the Sourcing Interface tables
1225 b. Calls Sourcing APIs for creating draft_negotiation and purging interface tables
1226 Pre-req : None
1227 Parameters:
1228 IN : p_api_version IN NUMBER REQUIRED
1229 p_commit IN varchar2 REQUIRED
1230 p_po_header_id IN NUMBER REQUIRED
1231 p_negotiation_type IN varchar2 REQUIRED
1232 OUT para : x_negotiation_id OUT NOCOPY NUMBER
1233 x_doc_url_params OUT NOCOPY varchar2
1234 x_return_status OUT NOCOPY varchar2
1235 x_error_code OUT NOCOPY varchar2
1236 x_error_message OUT NOCOPY varchar2
1237 Version : Current Version 1.0
1238 Changed: Initial design 10/1/2002
1239 Previous Version 1.0
1240 ==============================================================================*/
1241 PROCEDURE renegotiate_blanket( p_api_version IN NUMBER,
1242 p_commit IN varchar2,
1243 p_po_header_id IN NUMBER,
1244 p_negotiation_type IN varchar2,
1245 x_negotiation_id OUT NOCOPY NUMBER,
1246 x_doc_url_params OUT NOCOPY varchar2,
1247 x_return_status OUT NOCOPY varchar2,
1248 x_error_code OUT NOCOPY varchar2,
1249 x_error_message OUT NOCOPY varchar2,
1250 x_large_negotiation OUT NOCOPY varchar2,
1251 x_large_neg_request_id OUT NOCOPY NUMBER) IS
1252
1253 l_api_name CONSTANT varchar2(30) := 'RENEGOTIATE_BLANKET';
1254 l_api_version CONSTANT NUMBER := 1.0;
1255
1256 l_po_num po_headers.segment1%type := NULL;
1257 l_interface_id NUMBER;
1258
1259 l_create_api_result varchar2(30) :=NULL;
1260 l_create_api_err_code varchar2(100) := NULL;
1261 l_create_api_err_msg varchar2(400) := NULL;
1262
1263 l_ret_sts_success varchar2(30) := 'SUCCESS';
1264 l_ret_sts_error varchar2(30) := 'FAILURE';
1265
1266 l_progress varchar2(3);
1267 l_user_id NUMBER := -1;
1268
1269 --<Catalog Convergence 12.0 START>
1270 l_po_created_language PO_HEADERS_ALL.created_language%TYPE;
1271
1272 --<Catalog Convergence 12.0 END>
1273 BEGIN
1274 l_progress := '000';
1275
1276 -- Standard start of API savepoint
1277 SAVEPOINT renegotiate_blanket_grp;
1278
1279 -- Standard call to check for call compatibility
1280 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
1281 THEN
1282 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1283 END IF;
1284
1285 l_progress := '001';
1286
1287 --Bug 2694722 : Getting user id
1288 l_user_id := FND_GLOBAL.USER_ID;
1289
1290 --Populate PON_AUC_HEADERS_INTERFACE table
1291 insert into pon_auc_headers_interface(
1292 interface_auction_header_id,
1293 neg_type,
1294 contract_type,
1295 ship_to_location_id,
1296 bill_to_location_id,
1297 payment_terms_id,
1298 freight_terms_code,
1299 fob_code,
1300 carrier_code,
1301 note_to_bidders,
1302 creation_date,
1303 created_by,
1304 last_update_date,
1305 last_updated_by,
1306 allow_other_bid_currency_flag,
1307 po_agreed_amount,
1308 origination_code,
1309 global_agreement_flag,
1310 po_min_rel_amount,
1311 currency_code,
1312 rate_type,
1313 rate_date,
1314 rate,
1315 user_id,
1316 org_id,
1317 supplier_id,
1318 source_doc_id,
1319 source_doc_number,
1320 source_doc_msg,
1321 source_doc_line_msg,
1322 source_doc_msg_app,
1323 po_style_id, -- BUG#5532470
1324 language_code, --Bug#4911361
1325 supplier_site_id)
1326 select PON_AUC_HEADERS_INTERFACE_S.nextval,
1327 p_negotiation_type,
1328 'BLANKET',
1329 ship_to_location_id,
1330 bill_to_location_id,
1331 terms_id,
1332 freight_terms_lookup_code,
1333 fob_lookup_code,
1334 ship_via_lookup_code,
1335 note_to_vendor,
1336 creation_date,
1337 created_by,
1338 last_update_date,
1339 last_updated_by,
1340 'Y',
1341 blanket_total_amount,
1342 type_lookup_code,
1343 global_agreement_flag,
1344 min_release_amount,
1345 currency_code,
1346 rate_type,
1347 rate_date,
1348 rate,
1349 l_user_id,
1350 org_id,
1351 vendor_id,
1352 po_header_id,
1353 segment1,
1354 --<Bug 2917962 mbhargav START>
1355 --Sourcing team wants the name of the messages and
1356 --not the message text. So inserting the names of messages
1357 --for 'Blanket Agreement' and 'Line' respectively
1358 'PO_POTYPE_BLKT',
1359 'PO_SOURCING_LINE_NUMBER',
1360 --<Bug 2917962 mbhargav END>
1361 'PO',
1362 style_id, -- BUG#5532470
1363 created_language, --Bug#4911361
1364 vendor_site_id --<Bug 3325876>
1365 from po_headers
1366 where po_header_id=p_po_header_id;
1367
1368 l_progress := '002';
1369
1370 -- Get the interface_id into local variable
1371 select PON_AUC_HEADERS_INTERFACE_S.currval
1372 into l_interface_id
1373 from dual;
1374
1375 l_progress := '003';
1376 -- Get the segment1 from p_po_header_id
1377 select segment1
1378 into l_po_num
1379 from po_headers
1380 where po_header_id = p_po_header_id;
1381
1382
1383 l_progress := '004';
1384 --Bug #2737797
1385 --Amount agreed needs to passed instead of unit_price to
1386 --bid_start_price column in pon_auc_items_interface for
1387 -- amount based lines.
1388
1389 l_progress := '005';
1390 --Populate PON_AUC_ITEMS_INTERFACE table
1391 insert into pon_auc_items_interface(
1392 interface_auction_header_id,
1393 interface_line_number,
1394 line_type_id,
1395 item_description,
1396 org_id,
1397 category_id,
1398 quantity,
1399 current_price, --Bug#4915340
1400 note_to_bidders,
1401 uom_code,
1402 creation_date,
1403 created_by,
1404 last_update_date,
1405 last_updated_by,
1406 origination_code,
1407 po_min_rel_amount,
1408 price_break_type,
1409 item_id,
1410 item_number,
1411 item_revision,
1412 source_doc_number,
1413 source_line_number,
1414 source_doc_id,
1415 source_line_id,
1416 job_id, -- <SERVICES FPJ>
1417 po_agreed_amount, -- <SERVICES FPJ>
1418 purchase_basis, -- <SERVICES FPJ>
1419 ip_category_id) -- <Catalog Convergence 12.0>
1420 select l_interface_id,
1421 rownum, --bug 2714549: renumbers lines
1422 pl.line_type_id,
1423 item_description,
1424 pl.org_id,
1425 pl.category_id,
1426 quantity_committed, --Bug #2706156
1427 --Bug #2737797
1428 decode ( PL.order_type_lookup_code -- <SERVICES FPJ>
1429 , 'AMOUNT' , PL.committed_amount
1430 , 'FIXED PRICE' , PL.amount
1431 , PL.unit_price
1432 ),
1433 note_to_vendor,
1434 mum.uom_code,
1435 pl.creation_date,
1436 pl.created_by,
1437 pl.last_update_date,
1438 pl.last_updated_by,
1439 'BLANKET',
1440 min_release_amount,
1441 price_break_lookup_code,
1442 item_id,
1443 msi.concatenated_segments,
1444 item_revision,
1445 l_po_num,
1446 line_num, --original (non-renumbered) line num
1447 po_header_id,
1448 po_line_id,
1449 PL.job_id, -- <SERVICES FPJ>
1450 decode ( PL.order_type_lookup_code -- <SERVICES FPJ>
1451 , 'FIXED PRICE' , PL.committed_amount
1452 , 'RATE' , PL.committed_amount
1453 , NULL),
1454 PL.purchase_basis, -- <SERVICES FPJ>
1455 pl.ip_category_id -- <Catalog Convergence 12.0>
1456 from po_lines pl, mtl_units_of_measure mum, mtl_system_items_kfv msi,
1457 financials_system_parameters fsp --<Bug 3274272,3330235>
1458 where po_header_id=p_po_header_id and
1459 mum.unit_of_measure (+) = pl.unit_meas_lookup_code and -- <BUG 3211566>
1460 --bug #2716412: made pl/msi join an outer join
1461 pl.item_id = msi.inventory_item_id(+) and
1462 --<Bug 3274272, 3330235>
1463 (pl.item_id IS NULL OR fsp.inventory_organization_id = msi.organization_id);
1464
1465 --<Catalog Convergence 12.0 START>
1466 --call API to insert descriptor values for each PO line
1467
1468 SELECT created_language into l_po_created_language
1469 FROM po_headers_all
1470 WHERE po_header_id = p_po_header_id;
1471
1472 PO_ATTRIBUTE_VALUES_PVT.handle_attributes(p_interface_header_id => l_interface_id,
1473 p_po_header_id => p_po_header_id,
1474 p_language => l_po_created_language);
1475 --<Catalog Convergence 12.0 END>
1476
1477 --<Bug 2699631 mbhargav START>
1478 --Earlier the Release line locations for this Blanket were also getting copied over
1479 --to Sourcing. Added the pll.shipment_type = 'PRICE BREAK' in WHERE clause below
1480 --so that only price breaks of Blanket are copied over
1481 --<Bug 2699631 mbhargav END>
1482
1483 l_progress := '006';
1484 -- Populate PON_AUC_SHIPMENTS_INTERFACE table
1485 insert into pon_auc_shipments_interface(
1486 interface_auction_header_id,
1487 interface_line_number,
1488 interface_ship_number,
1489 shipment_type,
1490 ship_to_organization_id,
1491 ship_to_location_id,
1492 quantity,
1493 price,
1494 org_id,
1495 creation_date,
1496 created_by,
1497 last_update_date,
1498 last_updated_by)
1499 select l_interface_id,
1500 --bug 2714549: get renumbered line#
1501 paii.interface_line_number,
1502 pll.shipment_num,
1503 pll.shipment_type,
1504 pll.ship_to_organization_id,
1505 pll.ship_to_location_id,
1506 pll.quantity,
1507 pll.price_override,
1508 pll.org_id,
1509 pll.creation_date,
1510 pll.created_by,
1511 pll.last_update_date,
1512 pll.last_updated_by
1513 from po_line_locations pll,
1514 -- bug 2714549: added paii to join; removed join to po_lines
1515 pon_auc_items_interface paii
1516 where pll.po_header_id = p_po_header_id and
1517 --bug 2714549 start: changed join conditions from po_lines
1518 -- to paii and added auction_header cond
1519 -- to ensure unique doc_id/line_id from paii
1520 paii.source_doc_id = p_po_header_id and
1521 paii.source_line_id = pll.po_line_id and
1522 paii.interface_auction_header_id = l_interface_id and
1523 --bug 2714549 end
1524 pll.shipment_type = 'PRICE BREAK';
1525
1526
1527 -- <SERVICES FPJ START>
1528
1529 -- Populate PON_PRICE_DIFFERENTIALS_INTERFACE Table
1530 -- with Line-level Price Differentials
1531
1532 INSERT INTO pon_price_differ_interface
1533 ( interface_auction_header_id
1534 , interface_line_number
1535 , interface_shipment_number
1536 , interface_price_differ_number
1537 , price_type
1538 , multiplier
1539 , process_status
1540 , creation_date
1541 , created_by
1542 , last_update_date
1543 , last_updated_by
1544 , last_update_login
1545 )
1546 SELECT l_interface_id
1547 , POL.line_num
1548 , -1 -- <BUG 3212055> Insert -1 when shipment not present.
1549 , PD.price_differential_num
1550 , PD.price_type
1551 , PD.min_multiplier
1552 , NULL
1553 , PD.creation_date
1554 , PD.created_by
1555 , PD.last_update_date
1556 , PD.last_updated_by
1557 , PD.last_update_login
1558 FROM po_price_differentials PD
1559 , po_lines_all POL
1560 WHERE PD.entity_type = 'BLANKET LINE'
1561 AND PD.entity_id = POL.po_line_id
1562 AND POL.po_header_id = p_po_header_id;
1563
1564
1565 -- Populate PON_PRICE_DIFFERENTIALS_INTERFACE Table
1566 -- with Price Break-level Price Differentials
1567
1568 INSERT INTO pon_price_differ_interface
1569 ( interface_auction_header_id
1570 , interface_line_number
1571 , interface_shipment_number
1572 , interface_price_differ_number
1573 , price_type
1574 , multiplier
1575 , process_status
1576 , creation_date
1577 , created_by
1578 , last_update_date
1579 , last_updated_by
1580 , last_update_login
1581 )
1582 SELECT l_interface_id
1583 , POL.line_num
1584 , POLL.shipment_num
1585 , PD.price_differential_num
1586 , PD.price_type
1587 , PD.min_multiplier
1588 , NULL
1589 , PD.creation_date
1590 , PD.created_by
1591 , PD.last_update_date
1592 , PD.last_updated_by
1593 , PD.last_update_login
1594 FROM po_price_differentials PD
1595 , po_lines_all POL
1596 , po_line_locations_all POLL
1597 WHERE PD.entity_type = 'PRICE BREAK'
1598 AND PD.entity_id = POLL.line_location_id
1599 AND POLL.po_line_id = POL.po_line_id
1600 AND POL.po_header_id = p_po_header_id;
1601
1602 -- <SERVICES FPJ END>
1603
1604
1605 l_progress := '007';
1606 --Populate PON_ATTACHMENTS_INTERFACE with header level attachments
1607 insert into pon_attachments_interface(
1608 interface_auction_header_id,
1609 interface_line_number,
1610 document_id,
1611 seq_num,
1612 last_update_date,
1613 last_updated_by,
1614 creation_date,
1615 created_by)
1616 select l_interface_id,
1617 NULL,
1618 fad.document_id,
1619 fad.seq_num,
1620 fad.last_update_date,
1621 fad.last_updated_by,
1622 fad.creation_date,
1623 fad.created_by
1624 from fnd_attached_documents fad,
1625 fnd_documents fd,
1626 fnd_documents_tl fdtl
1627 where fad.document_id = fd.document_id AND
1628 fd.document_id = fdtl.document_id AND
1629 fdtl.language = userenv('LANG') AND
1630 fad.entity_name = 'PO_HEADERS' AND
1631 fad.pk1_value = p_po_header_id AND
1632 fd.category_id <> 39;
1633
1634 l_progress := '008';
1635 --Populate PON_ATTACHMENTS_INTERFACE with line level attachments
1636 insert into pon_attachments_interface(
1637 interface_auction_header_id,
1638 interface_line_number,
1639 document_id,
1640 seq_num,
1641 last_update_date,
1642 last_updated_by,
1643 creation_date,
1644 created_by)
1645 select l_interface_id,
1646 --bug 2714549: get renumbered line#
1647 paii.interface_line_number,
1648 fad.document_id,
1649 fad.seq_num,
1650 fad.last_update_date,
1651 fad.last_updated_by,
1652 fad.creation_date,
1653 fad.created_by
1654 from fnd_attached_documents fad,
1655 fnd_documents fd,
1656 fnd_documents_tl fdtl,
1657 --bug 2714549: replaced join to po_lines
1658 -- with join to paii.
1659 pon_auc_items_interface paii
1660 where fad.document_id = fd.document_id AND
1661 fd.document_id = fdtl.document_id AND
1662 fdtl.language = userenv('LANG') AND
1663 fad.entity_name = 'PO_LINES' AND
1664 --bug 2714549 start: changed join conditions from po_lines
1665 -- to paii and added auction_header cond
1666 -- to ensure unique doc_id/line_id from paii
1667 paii.source_doc_id = p_po_header_id AND
1668 fad.pk1_value = paii.source_line_id AND
1669 paii.interface_auction_header_id = l_interface_id and
1670 --bug 2714549 end
1671 fd.category_id <> 39;
1672
1673 l_progress := '009';
1674 -- Call Sourcing API to create draft negotiation
1675 -- Catalog Convergence 12.0 - new signature to support
1676 -- large negotiations where sourcing could launch a concurrent program
1677 -- and return the request id to PO for display. Also changed call to
1678 -- use parameter name/value convention
1679 PON_SOURCING_OPENAPI_GRP.CREATE_DRAFT_NEG_INTERFACE(p_interface_id => l_interface_id,
1680 x_document_number => x_negotiation_id,
1681 x_document_url => x_doc_url_params,
1682 x_concurrent_program_started => x_large_negotiation,
1683 x_request_id => x_large_neg_request_id,
1684 x_result => l_create_api_result,
1685 x_error_code => l_create_api_err_code,
1686 x_error_message => l_create_api_err_msg);
1687 l_progress := '010';
1688
1689 --<Catalog Convergence 12.0 START>
1690 -- PO no longer needs to make this call. Sourcing will handle this.
1691 -- With large negotiation support, it would be incorrect to call purge
1692
1693 -- Call Sourcing API to purge interface tables
1694 --PON_SOURCING_OPENAPI_GRP.PURGE_INTERFACE_TABLE(l_interface_id,
1695 -- l_purge_api_result,
1696 -- l_purge_api_err_code,
1697 -- l_purge_api_err_msg);
1698
1699 --<Catalog Convergence 12.0 END>
1700
1701 l_progress := '011';
1702
1703 /* Return appropriate error message. In case of failure of both APIs then
1704 error messsage corresponding to create_draft_neg is returned */
1705 --<Catalog Convergence 12.0 START>
1706 -- Since Purge call is no longer required removed checks for return status
1707 -- from that call
1708 if (l_create_api_result = l_ret_sts_success) then
1709 x_return_status := l_ret_sts_success;
1710 x_error_code := l_create_api_err_code;
1711 x_error_message := l_create_api_err_msg;
1712 else
1713 x_return_status := l_ret_sts_error;
1714 x_error_code := l_create_api_err_code;
1715 x_error_message := l_create_api_err_msg;
1716 end if;
1717 --<Catalog Convergence 12.0. END>
1718
1719 -- Committing the changes to the database
1720 if FND_API.To_Boolean(p_commit) then
1721 commit;
1722 end if;
1723
1724 EXCEPTION
1725 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1726 ROLLBACK TO renegotiate_blanket_grp;
1727 x_error_message := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,
1728 p_encoded => 'F');
1729 x_return_status := l_ret_sts_error;
1730 WHEN OTHERS THEN
1731 ROLLBACK TO renegotiate_blanket_grp;
1732 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1733 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_api_name,
1734 SUBSTRB (SQLERRM , 1 , 200) || ' at location ' || l_progress);
1735 END IF;
1736
1737 x_error_message := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,
1738 p_encoded => 'F');
1739 x_return_status := l_ret_sts_error;
1740
1741 END RENEGOTIATE_BLANKET;
1742 --<RENEG BLANKET FPI END>
1743
1744 -- Bug 3780359 Start
1745 -------------------------------------------------------------------------------
1746 --Start of Comments
1747 --Name: get_auction_display_line_num
1748 --Pre-reqs:
1749 -- None
1750 --Modifies:
1751 -- None
1752 --Locks:
1753 -- None
1754 --Function:
1755 -- This procedure retrieves the display number of an auction line, given
1756 -- the auction header ID and auction line number
1757 --Parameters:
1758 --IN:
1759 --p_auction_header_id
1760 -- Auction Header ID, unique identifier of an auction
1761 --p_auction_line_number
1762 -- Auction Line Number, unique identifier of a line of a particular auction
1763 --OUT:
1764 --x_auction_display_line_num
1765 -- Display number of an auction line for display purpose only
1766 --Notes:
1767 -- None
1768 --Testing:
1769 -- None
1770 --End of Comments
1771 -------------------------------------------------------------------------------
1772 PROCEDURE get_auction_display_line_num(
1773 p_auction_header_id IN NUMBER,
1774 p_auction_line_number IN NUMBER,
1775 x_auction_display_line_num OUT NOCOPY VARCHAR2)
1776 IS
1777
1778 l_api_name CONSTANT VARCHAR2(30):= 'GET_AUCTION_DISPLAY_LINE_NUM';
1779 l_progress VARCHAR2(3);
1780
1781 l_return_status VARCHAR2(1);
1782 l_msg_count NUMBER;
1783 l_msg_data VARCHAR2(2000);
1784
1785 BEGIN
1786 l_progress := '000';
1787 IF g_debug_stmt THEN
1788 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1789 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, g_log_head || l_api_name||
1790 '.' || l_progress, ' Begin');
1791 END IF;
1792 END IF;
1793
1794 PON_SOURCING_OPENAPI_GRP.get_display_line_number(
1795 p_api_version => 1.0,
1796 p_init_msg_list => 'F',
1797 p_auction_header_id => p_auction_header_id,
1798 p_auction_line_number => p_auction_line_number,
1799 x_display_line_number => x_auction_display_line_num,
1800 x_return_status => l_return_status,
1801 x_msg_count => l_msg_count,
1802 x_msg_data => l_msg_data);
1803
1804 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1805 l_progress := '010';
1806 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1807 END IF;
1808
1809 l_progress := '020';
1810 IF g_debug_stmt THEN
1811 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1812 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, g_log_head || l_api_name||
1813 '.' || l_progress, ' End');
1814 END IF;
1815 END IF;
1816
1817 EXCEPTION
1818 WHEN OTHERS THEN
1819 IF g_debug_stmt THEN
1820 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1821 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,g_log_head ||
1822 l_api_name||'.' || l_progress, ' Exception has occured.' ||
1823 ' l_msg_data: ' || l_msg_data || ' l_msg_count: ' || l_msg_count);
1824 END IF;
1825 END IF;
1826
1827 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1828 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name,
1829 SUBSTRB (SQLERRM , 1 , 200) || ' at location ' || l_progress);
1830 END IF;
1831
1832 x_auction_display_line_num := NULL;
1833
1834 END get_auction_display_line_num;
1835 -- Bug 3780359 End
1836
1837 END po_negotiations_sv1;