[Home] [Help]
PACKAGE BODY: APPS.PO_NEGOTIATIONS_SV1
Source
1 PACKAGE BODY po_negotiations_sv1 AS
2 /* $Header: POXNEG1B.pls 120.47.12020000.2 2013/02/11 01:06:29 vegajula 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_neg_line_id_tbl IN PO_TBL_NUMBER,
73 p_neg_line_num_disp_tbl IN PO_TBL_VARCHAR100,
74 /*p_line_type_id_tbl IN PO_TBL_NUMBER,
75 p_item_id_tbl IN PO_TBL_NUMBER,
76 p_item_revision_tbl IN PO_TBL_VARCHAR5, -- <ACHTML R12>
77 p_category_id_tbl IN PO_TBL_NUMBER,
78 p_quantity_tbl IN PO_TBL_NUMBER,
79 p_unit_meas_lookup_code_tbl IN PO_TBL_VARCHAR30,-- <ACHTML R12>
80 p_job_id_tbl IN PO_TBL_NUMBER,*/
81 -- some more input params
82 p_neg_outcome IN varchar2,
83 p_document_org_id IN number,
84 p_neg_style_id IN NUMBER, -- <ACHTML R12>
85 p_outcome_style_id IN NUMBER, -- <ACHTML R12>
86
87 --umbrella program
88 p_fair_opp_notice_flag IN VARCHAR2 DEFAULT NULL,
89
90 -- output params
91 x_negotiation_id IN OUT NOCOPY number,
92 x_doc_url_params IN OUT NOCOPY varchar2
93 )
94 IS
95 l_api_name CONSTANT VARCHAR2(30) := 'create_negotiation_bulk';
96 l_api_version CONSTANT NUMBER := 1.0;
97 l_error_code VARCHAR2(2000);
98 -- l_req_lines_tbl REQ_LINES_TABLE_TYPE; --Bug5841426
99 l_num_lines NUMBER;
100 BEGIN
101 -- Standard start of API savepoint
102 SAVEPOINT create_negotiation_bulk_SP;
103 -- Standard call to check for call compatibility
104 IF NOT FND_API.Compatible_API_Call( l_api_version ,
105 p_api_version ,
106 l_api_name ,
107 G_PKG_NAME)
108 THEN
109 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
110 END IF;
111
112 -- API Body
113 -- Get the number of lines being passed in.
114 l_num_lines := p_req_line_id_tbl.COUNT;
115
116 -- Go through the arrays and create the table to pass into
117 -- create_negotiation
118
119 --bug5841426 commented the following loop because the l_req_lines_tbl is
120 -- not used instead p_req_line_id_tbl is directly used to create the negotiation.
121 /*for i in 1..l_num_lines loop
122 l_req_lines_tbl(i).requisition_line_id := p_req_line_id_tbl(i);
123 l_req_lines_tbl(i).line_type_id := p_line_type_id_tbl(i);
124 l_req_lines_tbl(i).item_id := p_item_id_tbl(i);
125 l_req_lines_tbl(i).item_revision := p_item_revision_tbl(i);
126 l_req_lines_tbl(i).category_id := p_category_id_tbl(i);
127 l_req_lines_tbl(i).quantity := p_quantity_tbl(i);
128 l_req_lines_tbl(i).unit_meas_lookup_code := p_unit_meas_lookup_code_tbl(i);
129 l_req_lines_tbl(i).job_id := p_job_id_tbl(i);
130 end loop;*/
131
132 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
133 fnd_log.string(fnd_log.level_statement,'po.plsql.po_negotiations_sv1.create_negotiation_bulk',
134 'Before create_negotiation');
135 END IF;
136
137 -- Finally, make the call to create_negotiation
138 create_negotiation(
139 x_negotiation_type => p_negotiation_type,
140 x_grouping_method => p_grouping_method,
141 p_neg_style_id => p_neg_style_id,
142 p_outcome_style_id => p_outcome_style_id,
143 t_req_lines => p_req_line_id_tbl, -- bug5841426
144 t_neg_lines => p_neg_line_id_tbl,
145 t_neg_line_num => p_neg_line_num_disp_tbl,
146 x_negotiation_id => x_negotiation_id,
147 x_doc_url_params => x_doc_url_params,
148 x_result => x_result,
149 x_error_code => l_error_code,
150 x_error_message => x_error_message,
151 p_neg_outcome => p_neg_outcome,
152 p_document_org_id => p_document_org_id,
153 p_fair_opp_notice_flag => p_fair_opp_notice_flag);
154
155 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
156 fnd_log.string(fnd_log.level_statement,'po.plsql.po_negotiations_sv1.create_negotiation_bulk',
157 'After create_negotiation');
158 END IF;
159
160 -- End of API Body
161 -- Standard check to see if there is any error
162 IF(x_result = -1) THEN
163 RAISE FND_API.G_EXC_ERROR;
164 END IF;
165 EXCEPTION
166 WHEN FND_API.G_EXC_ERROR THEN
167 ROLLBACK TO create_negotiation_bulk_SP;
168 x_result := -1;
169 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
170 ROLLBACK TO create_negotiation_bulk_SP;
171 x_result := -1;
172 x_error_message := 'Unexpected error in create_negotiaton_bulk';
173 WHEN OTHERS THEN
174 ROLLBACK TO create_negotiation_bulk_SP;
175 x_result := -1;
176 x_error_message := 'Unhandled exception in create_negotiation_bulk';
177 END create_negotiation_bulk;
178 -- <HTMLAC END>
179
180 -- Overloaded method
181 PROCEDURE create_negotiation_bulk
182 (
183 -- standard API params
184 p_api_version IN NUMBER,
185 x_result IN OUT NOCOPY NUMBER,
186 x_error_message IN OUT NOCOPY VARCHAR2,
187 -- input params
188 p_negotiation_type IN varchar2,
189 p_grouping_method IN varchar2,
190 -- table params in
191 p_req_line_id_tbl IN PO_TBL_NUMBER,
192 p_neg_line_id_tbl IN PO_TBL_NUMBER,
193 p_neg_line_num_disp_tbl IN PO_TBL_VARCHAR100,
194 /*p_line_type_id_tbl IN PO_TBL_NUMBER,
195 p_item_id_tbl IN PO_TBL_NUMBER,
196 p_item_revision_tbl IN PO_TBL_VARCHAR5, -- <ACHTML R12>
197 p_category_id_tbl IN PO_TBL_NUMBER,
198 p_quantity_tbl IN PO_TBL_NUMBER,
199 p_unit_meas_lookup_code_tbl IN PO_TBL_VARCHAR30,-- <ACHTML R12>
200 p_job_id_tbl IN PO_TBL_NUMBER,
201 p_line_num_display IN PO_TBL_VARCHAR30,
202 p_group_line_id IN PO_TBL_NUMBER,
203 p_clm_info_flag IN PO_TBL_VARCHAR5,
204 p_clm_option_indicator IN PO_TBL_VARCHAR5,
205 p_clm_option_num IN PO_TBL_NUMBER,
206 p_clm_option_from_date IN PO_TBL_DATE,
207 p_clm_option_to_date IN PO_TBL_DATE,
208 p_clm_funded_flag IN PO_TBL_VARCHAR5,
209 p_clm_base_line_num IN PO_TBL_NUMBER,*/
210
211 -- some more input params
212 p_neg_outcome IN varchar2,
213 p_document_org_id IN number,
214 p_neg_style_id IN NUMBER, -- <ACHTML R12>
215 p_outcome_style_id IN NUMBER, -- <ACHTML R12>
216 -- output params
217 x_negotiation_id IN OUT NOCOPY number,
218 x_doc_url_params IN OUT NOCOPY varchar2
219 )
220 IS
221 l_api_name CONSTANT VARCHAR2(30) := 'create_negotiation_bulk';
222 l_api_version CONSTANT NUMBER := 1.0;
223 l_error_code VARCHAR2(2000);
224 -- l_req_lines_tbl REQ_LINES_TABLE_TYPE; --Bug5841426
225 l_num_lines NUMBER;
226 BEGIN
227 -- Standard start of API savepoint
228 SAVEPOINT create_negotiation_bulk_SP;
229 -- Standard call to check for call compatibility
230 IF NOT FND_API.Compatible_API_Call( l_api_version ,
231 p_api_version ,
232 l_api_name ,
233 G_PKG_NAME)
234 THEN
235 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
236 END IF;
237
238 -- API Body
239 -- Get the number of lines being passed in.
240 l_num_lines := p_req_line_id_tbl.COUNT;
241
242 -- Go through the arrays and create the table to pass into
243 -- create_negotiation
244
245 --bug5841426 commented the following loop because the l_req_lines_tbl is
246 -- not used instead p_req_line_id_tbl is directly used to create the negotiation.
247 /*for i in 1..l_num_lines loop
248 l_req_lines_tbl(i).requisition_line_id := p_req_line_id_tbl(i);
249 l_req_lines_tbl(i).line_type_id := p_line_type_id_tbl(i);
250 l_req_lines_tbl(i).item_id := p_item_id_tbl(i);
251 l_req_lines_tbl(i).item_revision := p_item_revision_tbl(i);
252 l_req_lines_tbl(i).category_id := p_category_id_tbl(i);
253 l_req_lines_tbl(i).quantity := p_quantity_tbl(i);
254 l_req_lines_tbl(i).unit_meas_lookup_code := p_unit_meas_lookup_code_tbl(i);
255 l_req_lines_tbl(i).job_id := p_job_id_tbl(i);
256 end loop;*/
257
258 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
259 fnd_log.string(fnd_log.level_statement,'po.plsql.po_negotiations_sv1.create_negotiation_bulk1',
260 'Before create_negotiation');
261 END IF;
262
263 -- Finally, make the call to create_negotiation
264 create_negotiation(
265 x_negotiation_type => p_negotiation_type,
266 x_grouping_method => p_grouping_method,
267 p_neg_style_id => p_neg_style_id,
268 p_outcome_style_id => p_outcome_style_id,
269 t_req_lines => p_req_line_id_tbl, -- bug5841426
270 t_neg_lines => p_neg_line_id_tbl,
271 t_neg_line_num => p_neg_line_num_disp_tbl,
272 x_negotiation_id => x_negotiation_id,
273 x_doc_url_params => x_doc_url_params,
274 x_result => x_result,
275 x_error_code => l_error_code,
276 x_error_message => x_error_message,
277 p_neg_outcome => p_neg_outcome,
278 p_document_org_id => p_document_org_id);
279
280 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
281 fnd_log.string(fnd_log.level_statement,'po.plsql.po_negotiations_sv1.create_negotiation_bulk1',
282 'After create_negotiation');
283 END IF;
284
285 -- End of API Body
286 -- Standard check to see if there is any error
287 IF(x_result = -1) THEN
288 RAISE FND_API.G_EXC_ERROR;
289 END IF;
290 EXCEPTION
291 WHEN FND_API.G_EXC_ERROR THEN
292 ROLLBACK TO create_negotiation_bulk_SP;
293 x_result := -1;
294 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
295 ROLLBACK TO create_negotiation_bulk_SP;
296 x_result := -1;
297 x_error_message := 'Unexpected error in create_negotiaton_bulk';
298 WHEN OTHERS THEN
299 ROLLBACK TO create_negotiation_bulk_SP;
300 x_result := -1;
301 x_error_message := 'Unhandled exception in create_negotiation_bulk';
302 END create_negotiation_bulk;
303
304
305
306
307
308
309
310
311
312 /*============================================================================
313 Name: CREATE_NEGOTIATION
314 DESC: Create document from requisition data in autocreate
315 dreddy : sourcing project
316 ==============================================================================*/
317
318 PROCEDURE create_negotiation(x_negotiation_type IN varchar2 ,
319 x_grouping_method IN varchar2 ,
320 t_req_lines IN PO_TBL_NUMBER, /* Changed the po_tbl_number to upper case for uniformity - bug 6631173 */ -- bug5841426
321 p_neg_style_id IN NUMBER, -- <ACHTML R12>
322 p_outcome_style_id IN NUMBER, -- <ACHTML R12>
323 x_negotiation_id IN OUT NOCOPY number,
324 x_doc_url_params IN OUT NOCOPY varchar2,
325 x_result IN OUT NOCOPY number,
326 x_error_code IN OUT NOCOPY varchar2,
327 x_error_message IN OUT NOCOPY varchar2,
328 --<RENEG BLANKET FPI>
329 p_neg_outcome IN varchar2,
330 --<HTMLAC>
331 p_document_org_id IN number DEFAULT null,
332 p_fair_opp_notice_flag in varchar2 default null
333 , t_neg_lines IN PO_TBL_NUMBER default null
334 , t_neg_line_num IN PO_TBL_VARCHAR100 DEFAULT NULL
335 )
336 IS
337
338
339 x_org_id number := null;
340 api_result number := 0;
341 api_error_code varchar2(2000) := null;
342 api_error_msg varchar2(2000) := null;
343 x_ship_to_location_id number;
344 x_deliver_to_location_id number;
345 x_negotiation_line_num number;
346 x_num_records number;
347 x_req_header_id number;
348 x_note_to_vendor po_requisition_lines_all.note_to_vendor%TYPE;
349 x_need_by_date date := null;
350 x_req_num varchar2(30);
351 x_item_num varchar2(80);
352 x_item_desc varchar2(240);
353 x_unit_price number;
354 x_uom_code varchar2(3);
355
356 -- <SERVICES FPJ START>
357 --
358 l_value_basis PO_REQUISITION_LINES.order_type_lookup_code%TYPE;
359 l_req_line_id PO_REQUISITION_LINES.requisition_line_id%TYPE;
360 l_amount PO_REQUISITION_LINES.amount%TYPE;
361 l_job_name PER_JOBS_VL.name%TYPE;
362 l_job_long_description PO_REQUISITION_LINES.job_long_description%TYPE;
363 l_has_price_diff_flag VARCHAR2(1);
364 l_bid_start_price PO_REQUISITION_LINES.unit_price%TYPE;
365 l_po_agreed_amount PO_REQUISITION_LINES.amount%TYPE;
366
367 -- <ACHTML R12 START>
368 l_return_status VARCHAR2(1);
369 l_msg_count NUMBER;
370 l_msg_data VARCHAR2(2000);
371 x_document_number pon_auction_headers_all.document_number%TYPE;
372 -- <ACHTML R12 END>
373
374 /* --<R12 STYLES PHASE II START>
375 l_req_line_id_table PO_DOC_STYLE_PVT.g_po_tbl_num;
376 l_source_doc_id_table PO_DOC_STYLE_PVT.g_po_tbl_num;
377 l_line_type_id_table PO_DOC_STYLE_PVT.g_po_tbl_num;
378 l_destination_type_table PO_DOC_STYLE_PVT.g_po_tbl_char30;
379 l_purchase_basis_table PO_DOC_STYLE_PVT.g_po_tbl_char30;
380 l_style_id PO_DOC_STYLE_HEADERS.style_id%type;
381 l_neg_style_id PO_DOC_STYLE_HEADERS.style_id%type;
382 */ --<R12 STYLES PHASE II END>
383
384 CURSOR l_price_diff_csr ( p_req_line_id NUMBER ) IS
385 SELECT *
386 FROM po_price_differentials
387 WHERE entity_type = 'REQ LINE'
388 AND entity_id = p_req_line_id;
389 --
390 -- <SERVICES FPJ END>
391
392 --<HTMLAC START>
393 -- The conversion rate from the current org to the destination org
394 l_conv_rate NUMBER;
395 -- The organization of the req line.
396 l_from_org_id PO_REQUISITION_LINES_ALL.org_id%TYPE;
397 --<HTMLAC END>
398
399 x_progress varchar2(3);
400
401
402 -- bug5841426 <start>
403 TYPE t_crs is ref cursor;
404 l_cursor t_crs;
405 l_string VARCHAR2(10000);
406
407 x_line_type po_requisition_lines_all.LINE_TYPE_ID%type;
408 x_item_id po_requisition_lines_all.ITEM_ID%type;
409 x_item_rev po_requisition_lines_all.ITEM_REVISION%type;
410 x_category_id po_requisition_lines_all.CATEGORY_ID%type;
411 x_quantity po_requisition_lines_all.QUANTITY%type;
412 x_uom po_requisition_lines_all.UNIT_MEAS_LOOKUP_CODE%type ;
413 x_job_id po_requisition_lines_all.JOB_ID%type;
414 x_req_line_id po_requisition_lines_all.requisition_line_id%TYPE;
415 x_line_num po_requisition_lines_all.line_num%TYPE;
416 x_neg_line_num_disp pon_auction_item_prices_all.line_num_display%TYPE;
417
418 -- added for CLIN SLIN Project
419 x_line_num_display po_requisition_lines_all.line_num_display%TYPE;
420 x_group_line_id po_requisition_lines_all.group_line_id%TYPE;
421 x_clm_info_flag po_requisition_lines_all.clm_info_flag%TYPE;
422 x_clm_option_indicator po_requisition_lines_all.clm_option_indicator%TYPE;
423 x_clm_option_num po_requisition_lines_all.clm_option_num%TYPE;
424 x_clm_option_from_date po_requisition_lines_all.clm_option_from_date%TYPE;
425 x_clm_option_to_date po_requisition_lines_all.clm_option_to_date%TYPE;
426 x_clm_funded_flag po_requisition_lines_all.clm_funded_flag%TYPE;
427 x_clm_base_line_num po_requisition_lines_all.clm_base_line_num%TYPE;
428 x_CONTRACT_TYPE po_requisition_lines_all.CONTRACT_TYPE%TYPE;
429 x_COST_CONSTRAINT po_requisition_lines_all.COST_CONSTRAINT%TYPE;
430 x_CLM_IDC_TYPE po_lines_all.CLM_IDC_TYPE%TYPE;
431 x_req_line_temp_id po_requisition_lines_all.UDA_TEMPLATE_ID%TYPE; --<Sol Project>
432
433 --bug5841426<end>
434
435 --bug6131913<start>
436
437 x_req_in_pool_flag VARCHAR2(1) :=NULL;
438 x_cancel_flag varchar2(1);
439 x_closed_code varchar2(25);
440
441 --bug6131913<end>
442
443
444 --<Sol Project>
445 l_line_uda_temp_id NUMBER;
446
447
448 --<Bug : 11071489 REQ_AUTOCREATE Start>--
449 l_parameter_list PO_CORE_S4.p_parameter_list;
450 l_event_name VARCHAR2(100);
451 --<REQ_AUTOCREATE END>--
452
453 BEGIN
454 /* get the current org_id */
455 x_progress := '000';
456 -- logme('create_negotiation ' || x_progress);
457 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
458 fnd_log.string(fnd_log.level_statement,g_log_head,'Procedure Create_Negotiation Start');
459 fnd_log.string(fnd_log.level_statement,g_log_head,'x_negotiation_type : '||x_negotiation_type);
460 fnd_log.string(fnd_log.level_statement,g_log_head,'x_grouping_method : '||x_grouping_method);
461 fnd_log.string(fnd_log.level_statement,g_log_head,'p_neg_style_id : '||p_neg_style_id);
462 fnd_log.string(fnd_log.level_statement,g_log_head,'p_outcome_style_id : '||p_outcome_style_id);
463 fnd_log.string(fnd_log.level_statement,g_log_head,'x_negotiation_id : '||x_negotiation_id);
464 fnd_log.string(fnd_log.level_statement,g_log_head,'x_doc_url_params : '||x_doc_url_params);
465 fnd_log.string(fnd_log.level_statement,g_log_head,'x_result : '||x_result);
466 fnd_log.string(fnd_log.level_statement,g_log_head,'x_error_code : '||x_error_code);
467 fnd_log.string(fnd_log.level_statement,g_log_head,'x_error_message : '||x_error_message);
468 fnd_log.string(fnd_log.level_statement,g_log_head,'p_neg_outcome : '||p_neg_outcome);
469 fnd_log.string(fnd_log.level_statement,g_log_head,'p_document_org_id : '||p_document_org_id);
470 END IF;
471
472 --<HTMLAC START>
473 IF(p_document_org_id IS NULL) THEN
474 --<HTMLAC END>
475 -- You can pass in a null, or leave out this parameter, in which case
476 -- we need to get the org from the po_system_parameters table.
477 begin
478 select org_id
479 into x_org_id
480 from po_system_parameters;
481 exception
482 when others then
483 po_message_s.sql_error('In Exception of create_negotiation()', x_progress, sqlcode);
484 end;
485 --<HTMLAC START>
486 ELSE
487 x_org_id := p_document_org_id;
488 END IF;
489
490 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
491 fnd_log.string(fnd_log.level_statement,g_log_head,'x_org_id : '||x_org_id);
492 END IF;
493 --<HTMLAC END>
494
495 --<R12 STYLES PHASE II START>
496 /* count the number of records in the plsql table */
497 /*
498 x_num_records := t_req_lines.COUNT;
499 for i in 1..x_num_records loop
500
501 l_req_line_id_table(i) := t_req_lines(i).requisition_line_id;
502 l_source_doc_id_table(i):= t_req_lines(i).blanket_po_header_id;
503 l_line_type_id_table(i):= t_req_lines(i).line_type_id;
504 l_destination_type_table(i):= t_req_lines(i).destination_type_code;
505 l_purchase_basis_table(i):= t_req_lines(i).purchase_basis;
506 end loop;
507 PO_DOC_STYLE_PVT.populate_gt_and_validate(p_api_version => 1.0,
508 p_init_msg_list => FND_API.G_TRUE,
509 X_return_status => l_return_status,
510 X_msg_count => l_msg_count,
511 x_msg_data => l_msg_data,
512 p_req_line_id_table => l_req_line_id_table,
513 p_source_doc_id_table => l_source_doc_id_table,
514 p_line_type_id_table => l_line_type_id_table,
515 p_destination_type_table => l_destination_type_table,
516 p_purchase_basis_table => l_purchase_basis_table,
517 p_po_header_id => NULL,
518 x_style_id => l_style_id);
519
520 IF l_return_status <> 'S' THEN
521
522 x_result := -1;
523 x_error_message := FND_MESSAGE.get;
524 return;
525
526 END IF;
527 */
528 --<R12 STYLES PHASE II END>
529
530 /* Call the sourcing Header API to create the draft negotiation */
531 /* Adding parameter p_neg_outcome - RENEG BLANKET FPI */
532 -- <STYLES R12> Changed API call to use binding parameters.
533 x_progress := '001';
534
535 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
536 fnd_log.string(fnd_log.level_statement,g_log_head,'Before create_draft_negotiation');
537 END IF;
538 PON_AUCTION_INTERFACE_PKG.create_draft_negotiation
539 ( p_document_title => null -- IN
540 , p_document_type => x_negotiation_type --<RENEG BLANKET FPI> -- IN
541 , p_contract_type => p_neg_outcome -- IN
542 , p_origination_code => 'REQUISITION' -- IN
543 , p_org_id => x_org_id -- IN
544 , p_buyer_id => to_number(FND_PROFILE.VALUE('user_id')) -- IN
545 --<R12 STYLES PHASE II START>
546 , p_neg_style_id => p_neg_style_id -- <ACHTML R12> -- IN
547 , p_po_style_id => p_outcome_style_id -- <ACHTML R12> -- IN
548 , p_fair_opp_notice_flag => p_fair_opp_notice_flag -- IN
549 --<R12 STYLES PHASE II END>
550 , p_document_number => x_negotiation_id -- OUT
551 , p_document_url => x_doc_url_params -- OUT
552 , p_result => api_result -- OUT
553 , p_error_code => api_error_code -- OUT
554 , p_error_message => api_error_msg -- OUT
555 );
556
557
558
559 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
560 fnd_log.string(fnd_log.level_statement,g_log_head,'After create_draft_negotiation');
561 END IF;
562
563
564 if api_result <> 0 THEN
565 /* If unsuccessful return the error code to the form. */
566 x_progress := '002';
567 x_result := -1;
568 x_error_code := api_error_code;
569 x_error_message := api_error_msg;
570 return ;
571
572 end if;
573 -- logme('create_negotiation ' || x_progress);
574
575
576 --bug 6131913<start>
577 /*****
578 This Code is to check if the Requisition Line which we are trying to AutoCreate has already been locked or deleted or cancelled.
579 If so, simply return to the caller and show a message saying Requisition Line can't be auto created.
580 *****/
581
582 BEGIN
583
584 SAVEPOINT CHECK_LOCK;
585
586 --<Sol Project>
587 l_line_uda_temp_id := NULL;
588 IF ( PON_CLM_UTIL_PKG.IS_NEG_DOCUMENT_FEDERAL(x_negotiation_id) = 1 ) THEN
589 l_line_uda_temp_id := po_uda_data_util.get_template_id (
590 'SOURCING',
591 'SOLICITATION',
592 null,
593 'LINE',
594 sysdate,
595 l_return_status,
596 l_msg_data);
597 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
598 fnd_log.string(fnd_log.level_statement,g_log_head,'l_line_uda_temp_id : '||l_line_uda_temp_id);
599 fnd_log.string(fnd_log.level_statement,g_log_head,'l_return_status : '||l_return_status);
600 END IF;
601
602 IF ( l_return_status = 'E' ) THEN
603 x_progress := '002';
604 x_result := -1;
605 x_error_code := api_error_code;
606 x_error_message := l_msg_data;
607 return ;
608 END IF;
609 END IF;
610
611
612
613 for i in 1..(t_req_lines.count)-1
614 loop
615
616 x_req_in_pool_flag:=NULL;
617
618 BEGIN
619
620 SELECT Nvl(reqs_in_pool_flag,'Y'),cancel_flag,closed_code
621 INTO x_req_in_pool_flag,
622 x_cancel_flag,
623 x_closed_code
624 FROM po_requisition_lines WHERE requisition_line_id=t_req_lines(i)
625 FOR UPDATE OF auction_header_id NOWAIT;
626
627
628 EXCEPTION
629
630 WHEN NO_DATA_FOUND then
631 /* The req line has been deleted since it was queried up. */
632
633 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
634 fnd_log.string(fnd_log.level_statement,g_log_head,'Exception1 : '||SQLERRM);
635 END IF;
636
637 x_result:=-1;
638 fnd_message.set_name('PO', 'PO_ALL_REQ_LINE_DLTD_CANT_AC');
639 /*
640 SELECT prh.segment1,prl.line_num INTO x_req_num,x_line_num FROM po_requisition_headers_all prh,po_requisition_lines_all prl
641 WHERE prh.requisition_header_id = prl.requisition_header_id
642 AND prl.requisition_line_id = t_req_lines(i);
643
644 fnd_message.set_token('REQ_NUM', x_req_num);
645 fnd_message.set_token('REQ_LINE_NUM',x_line_num);
646 */
647 rollback to CHECK_LOCK;
648 x_error_message := fnd_message.get;
649 RETURN;
650
651 WHEN OTHERS THEN
652 /* This is to see if req line is locked */
653 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
654 fnd_log.string(fnd_log.level_statement,g_log_head,'Exception2 : '||SQLERRM);
655 END IF;
656
657 if (SQLCODE=-54)
658 THEN
659 x_result:=-1;
660 fnd_message.set_name('PO', 'PO_ALL_RQ_LINE_LOCKED_CANT_AC');
661 SELECT prh.segment1,prl.line_num INTO x_req_num,x_line_num FROM po_requisition_headers_all prh,po_requisition_lines_all prl
662 WHERE prh.requisition_header_id = prl.requisition_header_id
663 AND prl.requisition_line_id = t_req_lines(i);
664
665 fnd_message.set_token('REQ_NUM', x_req_num);
666 fnd_message.set_token('REQ_LINE_NUM',x_line_num);
667
668 x_error_message := fnd_message.get;
669 rollback to CHECK_LOCK;
670 RETURN;
671 else
672 x_result:=-1;
673 raise;
674 end if;
675
676 END;
677
678 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
679 fnd_log.string(fnd_log.level_statement,g_log_head,'x_req_in_pool_flag : '||x_req_in_pool_flag);
680 fnd_log.string(fnd_log.level_statement,g_log_head,'x_cancel_flag : '||x_cancel_flag);
681 fnd_log.string(fnd_log.level_statement,g_log_head,'x_closed_code : '||x_closed_code);
682 END IF;
683
684 if (x_req_in_pool_flag='N')
685 then
686 /* The req line has been auto created already. */
687 x_result:=-1;
688 fnd_message.set_name('PO', 'PO_ALL_RQ_LINE_ALREADY_AC');
689 SELECT prh.segment1,prl.line_num INTO x_req_num,x_line_num FROM po_requisition_headers_all prh,po_requisition_lines_all prl
690 WHERE prh.requisition_header_id = prl.requisition_header_id
691 AND prl.requisition_line_id = t_req_lines(i);
692
693 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
694 fnd_log.string(fnd_log.level_statement,g_log_head,'x_req_num : '||x_req_num);
695 fnd_log.string(fnd_log.level_statement,g_log_head,'x_line_num : '||x_line_num);
696 END IF;
697
698
699 fnd_message.set_token('REQ_NUM', x_req_num);
700 fnd_message.set_token('REQ_LINE_NUM',x_line_num);
701
702
703 x_error_message := fnd_message.get;
704 ROLLBACK TO CHECK_LOCK;
705 RETURN;
706
707 elsif (x_cancel_flag = 'Y') then
708 /* The req line has been cancelled. */
709 x_result:=-1;
710 fnd_message.set_name('PO', 'PO_ALL_RQ_LINE_CNCLD_CANT_AC');
711 SELECT prh.segment1,prl.line_num INTO x_req_num,x_line_num FROM po_requisition_headers_all prh,po_requisition_lines_all prl
712 WHERE prh.requisition_header_id = prl.requisition_header_id
713 AND prl.requisition_line_id = t_req_lines(i);
714
715 fnd_message.set_token('REQ_NUM', x_req_num);
716 fnd_message.set_token('REQ_LINE_NUM',x_line_num);
717
718
719 x_error_message := fnd_message.get;
720 ROLLBACK TO CHECK_LOCK;
721 RETURN;
722
723 elsif (x_closed_code = 'FINALLY CLOSED') then
724 /* The req line has been auto created already. */
725 x_result:=-1;
726 fnd_message.set_name('PO', 'PO_ALL_RQ_LINE_FCLSD_CANT_AC');
727 SELECT prh.segment1,prl.line_num INTO x_req_num,x_line_num FROM po_requisition_headers_all prh,po_requisition_lines_all prl
728 WHERE prh.requisition_header_id = prl.requisition_header_id
729 AND prl.requisition_line_id = t_req_lines(i);
730
731 fnd_message.set_token('REQ_NUM', x_req_num);
732 fnd_message.set_token('REQ_LINE_NUM',x_line_num);
733
734
735 x_error_message := fnd_message.get;
736 ROLLBACK TO CHECK_LOCK;
737 RETURN;
738
739 end if;
740
741
742
743 END LOOP;
744
745 END;
746
747 --bug 6131913<end>
748
749 x_progress := '003';
750 /* count the number of records in the plsql table */
751 x_num_records := t_req_lines.COUNT;
752
753 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
754 fnd_log.string(fnd_log.level_statement,g_log_head,'x_num_records : '||x_num_records);
755 END IF;
756 --bug 6131913
757 -- logme('create_negotiation ' || x_progress);
758
759 DELETE FROM po_session_gt WHERE index_char1='PO_NEGOTIATIONS_SV1';
760
761
762
763 --bug 5841426<start>
764 --Bug 13627281
765 IF(t_neg_lines IS NULL OR t_neg_line_num IS null) then
766 FORALL i IN 1..t_req_lines.Count
767 INSERT INTO po_session_gt (KEY, index_char1, char1, num1, num2, num3, date1)
768 SELECT prl.requisition_line_id,
769 'PO_NEGOTIATIONS_SV1',
770 prh.segment1,
771 prl.line_num,
772 prl.item_id,
773 prl.category_id,
774 decode (prl.purchase_basis , 'TEMP LABOR' , prl.assignment_start_date , prl.need_by_date )
775 FROM po_requisition_lines_all prl,
776 po_requisition_headers_all prh
777 WHERE prh.requisition_header_id = prl.requisition_header_id
778 AND prl.requisition_line_id = t_req_lines(i);
779 else
780 FORALL i IN 1..t_req_lines.Count
781 INSERT INTO po_session_gt (KEY, index_char1, char1, num1, num2, num3, num4, char2, date1)
782 SELECT prl.requisition_line_id,
783 'PO_NEGOTIATIONS_SV1',
784 prh.segment1,
785 prl.line_num,
786 prl.item_id,
787 prl.category_id,
788 t_neg_lines(i),
789 t_neg_line_num(i),
790 decode (prl.purchase_basis , 'TEMP LABOR' , prl.assignment_start_date , prl.need_by_date )
791 FROM po_requisition_lines_all prl,
792 po_requisition_headers_all prh
793 WHERE prh.requisition_header_id = prl.requisition_header_id
794 AND prl.requisition_line_id = t_req_lines(i);
795 end if;
796
797 /* For each req line in the plsql table we call the sourcing line API */
798
799 -- for i in 1..x_num_records loop --Bug5841426
800
801 --l_req_line_id := t_req_lines(i).requisition_line_id;-- <SERVICES FPJ>
802
803 /* get the info from the req line which is not available in the client side */
804 -- begin
805 -- x_progress := '005';
806 -- logme('create_negotiation ' || x_progress);
807
808 l_string := ' select rl.requisition_line_id,
809 Decode(Nvl(rl.clm_info_flag,''N''),''Y'',NULL,rl.LINE_TYPE_ID), -- line_type_id
810 rl.ITEM_ID, -- item_id
811 rl.ITEM_REVISION, -- item_revision
812 rl.CATEGORY_ID, -- category id
813 rl.QUANTITY, -- quantity
814 Decode(Nvl(rl.clm_info_flag,''N''),''Y'',NULL,RL.UNIT_MEAS_LOOKUP_CODE), -- unit meas lookup code
815 rl.JOB_ID, -- job id
816 rl.requisition_header_id,
817 psg.char1,
818 RL.order_type_lookup_code, -- <SERVICES FPJ>
819 rl.note_to_vendor,
820 rl.need_by_date,
821 RL.amount, -- <SERVICES FPJ>
822 msi.concatenated_segments,
823 rl.deliver_to_location_id,
824 rl.unit_price,
825 rl.item_description,
826 PJ.name, -- <SERVICES FPJ>
827 RL.job_long_description, -- <SERVICES FPJ>
828 RL.org_id, -- <HTMLAC>
829
830 -- added for CLIN SLIN Project
831 rl.LINE_NUM_DISPLAY,
832 rl.GROUP_LINE_ID,
833 rl.CLM_INFO_FLAG,
834 rl.CLM_OPTION_INDICATOR,
835 rl.CLM_OPTION_NUM,
836 rl.CLM_OPTION_FROM_DATE,
837 rl.CLM_OPTION_TO_DATE,
838 rl.CLM_FUNDED_FLAG,
839 rl.CLM_BASE_LINE_NUM,
840 rl.CONTRACT_TYPE,
841 rl.COST_CONSTRAINT,
842 decode(rl.CONTRACT_TYPE, null, null, ''IDC_NA''),
843 rl.UDA_TEMPLATE_ID, --<Sol Project>
844 psg.num4,
845 psg.char2
846
847
848 /*into x_req_header_id,
849 l_value_basis, -- <SERVICES FPJ>
850 x_note_to_vendor,
851 x_need_by_date,
852 l_amount, -- <SERVICES FPJ>
853 x_item_num,
854 x_deliver_to_location_id,
855 x_unit_price,
856 x_item_desc,
857 l_job_name, -- <SERVICES FPJ>
858 l_job_long_description, -- <SERVICES FPJ>
859 l_from_org_id */ -- <HTMLAC>
860 from po_requisition_lines_all rl, -- <HTMLAC>
861 mtl_system_items_kfv msi,
862 per_jobs_vl PJ,
863 PO_SESSION_GT psg -- <SERVICES FPJ>
864 where requisition_line_id = psg.key -- <SERVICES FPJ>
865 and rl.item_id = msi.inventory_item_id(+)
866 and coalesce(msi.organization_id, rl.destination_organization_id,-1) =
867 nvl(rl.destination_organization_id,-1)
868 AND RL.job_id = PJ.job_id(+) -- <SERVICES FPJ>
869 AND psg.index_char1 = ''PO_NEGOTIATIONS_SV1''' ;
870
871 if (x_grouping_method = 'REQUISITION') then
872 l_string := l_string || ' ORDER BY psg.char1, psg.num1';
873 ELSE
874 l_string := l_string || ' order by psg.num2, psg.num3, psg.date1, psg.key';
875 END IF;
876 -- logme('create_negotiation before cursor' || x_progress);
877
878 OPEN l_cursor FOR l_string;
879
880 -- logme('cursor returned ' || l_cursor%ROWCOUNT || ' rows');
881
882 LOOP
883 -- logme('start of loop');
884 FETCH l_cursor INTO x_req_line_id ,
885 x_line_type ,
886 x_item_id ,
887 x_item_rev ,
888 x_category_id ,
889 x_quantity ,
890 x_uom ,
891 x_job_id ,
892 x_req_header_id ,
893 x_req_num ,
894 l_value_basis,
895 x_note_to_vendor,
896 x_need_by_date,
897 l_amount,
898 x_item_num,
899 x_deliver_to_location_id,
900 x_unit_price,
901 x_item_desc,
902 l_job_name,
903 l_job_long_description,
904 l_from_org_id,
905 x_line_num_display,
906 x_group_line_id,
907 x_clm_info_flag,
908 x_clm_option_indicator,
909 x_clm_option_num,
910 x_clm_option_from_date,
911 x_clm_option_to_date,
912 x_clm_funded_flag,
913 x_clm_base_line_num,
914 x_CONTRACT_TYPE,
915 x_COST_CONSTRAINT,
916 x_CLM_IDC_TYPE,
917 x_req_line_temp_id, --<Sol Project>
918 x_negotiation_line_num,
919 x_neg_line_num_disp;
920
921 IF l_cursor%NOTFOUND THEN
922 EXIT;
923 END IF;
924
925 /* exception
926 when others then
927 po_message_s.sql_error('In Exception of create_negotiation()', x_progress, sqlcode);
928 end;*/
929
930 /* begin
931 x_progress := '006';
932 select segment1
933 into x_req_num
934 from po_requisition_headers_all -- <HTMLAC>
935 where requisition_header_id=x_req_header_id;
936 exception
937 when others then
938 po_message_s.sql_error('In Exception of create_negotiation()', x_progress, sqlcode);
939 end;*/
940 --Bug 5841426<end>
941
942 /*
943 ** Get the ship to location id associated with the deliver to location.
944 ** This may then used to get the tax name, if the tax system parameters are
945 ** set up to retrieve the tax code based on ship-to location. */
946 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
947 fnd_log.string(fnd_log.level_statement,g_log_head,'x_req_line_id : '||x_req_line_id);
948 fnd_log.string(fnd_log.level_statement,g_log_head,'x_line_type : '||x_line_type);
949 fnd_log.string(fnd_log.level_statement,g_log_head,'x_item_id : '||x_item_id);
950 fnd_log.string(fnd_log.level_statement,g_log_head,'x_item_rev : '||x_item_rev);
951 fnd_log.string(fnd_log.level_statement,g_log_head,'x_category_id : '||x_category_id);
952 fnd_log.string(fnd_log.level_statement,g_log_head,'x_quantity : '||x_quantity);
953 fnd_log.string(fnd_log.level_statement,g_log_head,'x_uom : '||x_uom);
954 fnd_log.string(fnd_log.level_statement,g_log_head,'x_job_id : '||x_job_id);
955 fnd_log.string(fnd_log.level_statement,g_log_head,'x_req_header_id : '||x_req_header_id);
956 fnd_log.string(fnd_log.level_statement,g_log_head,'x_req_num : '||x_req_num);
957 fnd_log.string(fnd_log.level_statement,g_log_head,'l_value_basis : '||l_value_basis);
958 fnd_log.string(fnd_log.level_statement,g_log_head,'x_note_to_vendor : '||x_note_to_vendor);
959 fnd_log.string(fnd_log.level_statement,g_log_head,'x_need_by_date : '||x_need_by_date);
960 fnd_log.string(fnd_log.level_statement,g_log_head,'l_amount : '||l_amount);
961 fnd_log.string(fnd_log.level_statement,g_log_head,'x_item_num : '||x_item_num);
962 fnd_log.string(fnd_log.level_statement,g_log_head,'x_deliver_to_location_id : '||x_deliver_to_location_id);
963 fnd_log.string(fnd_log.level_statement,g_log_head,'x_unit_price : '||x_unit_price);
964 fnd_log.string(fnd_log.level_statement,g_log_head,'x_item_desc : '||x_item_desc);
965 fnd_log.string(fnd_log.level_statement,g_log_head,'l_job_name : '||l_job_name);
966 fnd_log.string(fnd_log.level_statement,g_log_head,'l_job_long_description : '||l_job_long_description);
967 fnd_log.string(fnd_log.level_statement,g_log_head,'l_from_org_id : '||l_from_org_id);
968 fnd_log.string(fnd_log.level_statement,g_log_head,'x_line_num_display : '||x_line_num_display);
969 fnd_log.string(fnd_log.level_statement,g_log_head,'x_group_line_id : '||x_group_line_id);
970 fnd_log.string(fnd_log.level_statement,g_log_head,'x_clm_info_flag : '||x_clm_info_flag);
971 fnd_log.string(fnd_log.level_statement,g_log_head,'x_clm_option_indicator : '||x_clm_option_indicator);
972 fnd_log.string(fnd_log.level_statement,g_log_head,'x_clm_option_num : '||x_clm_option_num);
973 fnd_log.string(fnd_log.level_statement,g_log_head,'x_clm_option_from_date : '||x_clm_option_from_date);
974 fnd_log.string(fnd_log.level_statement,g_log_head,'x_clm_option_to_date : '||x_clm_option_to_date);
975 fnd_log.string(fnd_log.level_statement,g_log_head,'x_clm_funded_flag : '||x_clm_funded_flag);
976 fnd_log.string(fnd_log.level_statement,g_log_head,'x_clm_base_line_num : '||x_clm_base_line_num);
977 fnd_log.string(fnd_log.level_statement,g_log_head,'x_CONTRACT_TYPE : '||x_CONTRACT_TYPE);
978 fnd_log.string(fnd_log.level_statement,g_log_head,'x_COST_CONSTRAINT : '||x_COST_CONSTRAINT);
979 fnd_log.string(fnd_log.level_statement,g_log_head,'x_CLM_IDC_TYPE : '||x_CLM_IDC_TYPE);
980 fnd_log.string(fnd_log.level_statement,g_log_head,'x_req_line_temp_id : '||x_req_line_temp_id);
981 END IF;
982
983 BEGIN
984 x_progress := '004';
985 SELECT nvl(ship_to_location_id,location_id)
986 INTO x_ship_to_location_id
987 FROM hr_locations
988 WHERE location_id = x_deliver_to_location_id;
989
990 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
991 fnd_log.string(fnd_log.level_statement,g_log_head,'x_ship_to_location_id : '||x_ship_to_location_id);
992 END IF;
993
994 EXCEPTION
995 WHEN NO_DATA_FOUND THEN
996 x_ship_to_location_id := x_deliver_to_location_id;
997 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
998 fnd_log.string(fnd_log.level_statement,g_log_head,'Excep x_ship_to_location_id1 : '||x_ship_to_location_id);
999 END IF;
1000 END;
1001
1002 /* Sourcing expects the uom_code whereas req lines stores unit_of_measure .
1003 need to get the correct value */
1004 begin
1005 select mum.uom_code
1006 into x_uom_code
1007 from mtl_units_of_measure mum
1008 where mum.unit_of_measure = x_uom; --bug 5841426
1009
1010 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1011 fnd_log.string(fnd_log.level_statement,g_log_head,'x_uom_code : '||x_uom_code);
1012 END IF;
1013
1014 exception
1015 when others then
1016 x_uom_code := null;
1017 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1018 fnd_log.string(fnd_log.level_statement,g_log_head,'Excep x_uom_code : '||x_uom_code);
1019 END IF;
1020 end;
1021
1022 -- <SERVICES FPJ START>
1023
1024 -- Determine if this Requisition Line has Price Differentials.
1025 --
1026 IF ( PO_PRICE_DIFFERENTIALS_PVT.has_price_differentials
1027 ( p_entity_type => 'REQ LINE'
1028 , p_entity_id => x_req_line_id) --Bug 5841426
1029 )
1030 THEN
1031 l_has_price_diff_flag := 'Y';
1032 ELSE
1033 l_has_price_diff_flag := 'N';
1034 END IF;
1035
1036 --<HTMLAC START>
1037 -- Get The Conversion rate from the from_ou to the to_ou
1038 -- If the l_conv_rate is null, means there is no conversion
1039 -- rate between the different OUs, then we pass in null for
1040 -- the amount and the unit_price
1041 l_conv_rate := PO_CURRENCY_SV.get_cross_ou_rate(
1042 l_from_org_id,
1043 x_org_id);
1044
1045 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1046 fnd_log.string(fnd_log.level_statement,g_log_head,'l_has_price_diff_flag : '||l_has_price_diff_flag);
1047 fnd_log.string(fnd_log.level_statement,g_log_head,'l_conv_rate : '||l_conv_rate);
1048 END IF;
1049
1050 --<HTMLAC END>
1051
1052 -- Determine which value to pass in for BID_START_PRICE and
1053 -- PO_AGREED_AMOUNT. For Fixed Price lines, we will pass the
1054 -- Req Line Amount into the Bid Start Price.
1055 --
1056 IF ( l_value_basis = 'FIXED PRICE' )
1057 THEN
1058 l_bid_start_price := l_amount * l_conv_rate; --<HTMLAC>
1059 l_po_agreed_amount := NULL;
1060 ELSE
1061 l_bid_start_price := x_unit_price * l_conv_rate; --<HTMLAC>
1062 l_po_agreed_amount := l_amount * l_conv_rate; --<HTMLAC>
1063 END IF;
1064
1065 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1066 fnd_log.string(fnd_log.level_statement,g_log_head,'l_bid_start_price : '||l_bid_start_price);
1067 fnd_log.string(fnd_log.level_statement,g_log_head,'l_po_agreed_amount : '||l_po_agreed_amount);
1068 fnd_log.string(fnd_log.level_statement,g_log_head,'Before PON_AUCTION_INTERFACE_PKG.add_negotiation_line');
1069 END IF;
1070
1071 -- <SERVICES FPJ END>
1072
1073 /* call the sourcing api to add the requisition lines to the above negotiation */
1074 /* Adding parameter p_neg_outcome - RENEG BLANKET FPI */
1075 x_progress := '007';
1076
1077
1078 PON_AUCTION_INTERFACE_PKG.add_negotiation_line
1079 ( p_document_number => x_negotiation_id
1080 , p_contract_type => p_neg_outcome
1081 , p_origination_code => 'REQUISITION'
1082 , p_org_id => x_org_id
1083 , p_buyer_id => to_number(FND_PROFILE.VALUE('user_id'))
1084 , p_grouping_type => x_grouping_method
1085 , p_requisition_header_id => x_req_header_id
1086 , p_requisition_number => x_req_num
1087 , p_requisition_line_id => x_req_line_id
1088 , p_line_type_id => x_line_type --Bug5841426
1089 , p_category_id => x_category_id
1090 , p_item_description => x_item_desc
1091 , p_item_id => x_item_id --Bug5841426
1092 , p_item_number => x_item_num --Bug5841426
1093 , p_item_revision => x_item_rev
1094
1095 , p_uom_code => x_uom_code
1096 , p_quantity => x_quantity
1097
1098 , p_need_by_date => x_need_by_date
1099 , p_ship_to_location_id => x_ship_to_location_id
1100 , p_note_to_vendor => x_note_to_vendor
1101 , p_price => l_bid_start_price -- <SERVICES FPJ>
1102 , p_job_id => x_job_id-- <SERVICES FPJ> --Bug5841426
1103
1104 , p_job_details => l_job_long_description-- <SERVICES FPJ>
1105 , p_po_agreed_amount => l_po_agreed_amount -- <SERVICES FPJ>
1106 , p_has_price_diff_flag => l_has_price_diff_flag -- <SERVICES FPJ>
1107 -- fields added for CLIN SLIN Project
1108 , p_line_num_display => x_line_num_display
1109 , p_group_line_id => x_group_line_id
1110 , p_clm_info_flag => x_clm_info_flag
1111 , p_clm_option_indicator => x_clm_option_indicator
1112 , p_clm_option_num => x_clm_option_num
1113 , p_clm_option_from_date => x_clm_option_from_date
1114 , p_clm_option_to_date => x_clm_option_to_date
1115 , p_clm_funded_flag => x_clm_funded_flag
1116 , p_clm_base_line_num => x_clm_base_line_num
1117 , p_clm_contract_type => x_CONTRACT_TYPE
1118 , p_clm_cost_constraint => x_COST_CONSTRAINT
1119 , p_clm_idc_type => x_CLM_IDC_TYPE
1120 , p_uda_template_id => l_line_uda_temp_id --<Sol Project>
1121 , p_req_line_temp_id => x_req_line_temp_id --<Sol Project>
1122 , p_line_number => x_negotiation_line_num
1123 , p_neg_line_num_disp => x_neg_line_num_disp
1124 , p_result => api_result
1125 , p_error_code => api_error_code
1126 , p_error_message => api_error_msg
1127 );
1128
1129 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1130 fnd_log.string(fnd_log.level_statement,g_log_head,'After PON_AUCTION_INTERFACE_PKG.add_negotiation_line');
1131 END IF;
1132
1133 -- logme(' Add Line for item ' || x_item_desc || ' api_result = ' || api_result || ' error code = ' || api_error_code || ' error_msg = ' || api_error_msg );
1134 -- logme ( ' The above line autocreated to ' || x_negotiation_line_num);
1135 If api_result = 0 then
1136
1137 begin
1138
1139 x_progress := '008';
1140 -- logme( '008 : x_negotiation_id = '|| x_negotiation_id || ' to_char(x_negotiation_id) = ' || to_char(x_negotiation_id)
1141 -- || ' x_negotiation_line_num = '|| x_negotiation_line_num || ' sysdate = '|| SYSDATE ||
1142 -- ' to_number(FND_PROFILE.VALUE(user_id)) = ' || to_number(FND_PROFILE.VALUE('user_id'))
1143 -- || ' x_req_line_id = ' || x_req_line_id);
1144 update po_requisition_lines_all --<HTMLAC>
1145 set on_rfq_flag = 'Y',
1146 auction_header_id = x_negotiation_id,
1147 auction_display_number = to_char(x_negotiation_id) ,
1148 auction_line_number = x_negotiation_line_num,
1149 at_sourcing_flag = 'Y', -- <REQINPOOL>
1150 reqs_in_pool_flag = NULL, -- <REQINPOOL>
1151 last_update_date = sysdate,
1152 last_updated_by = to_number(FND_PROFILE.VALUE('user_id')),
1153 last_update_login = to_number(FND_PROFILE.VALUE('user_id'))
1154 where requisition_line_id = x_req_line_id ; --5841426
1155
1156 -- bug# 9887326
1157 IF ( PON_CLM_UTIL_PKG.IS_NEG_DOCUMENT_FEDERAL(x_negotiation_id) = 1 ) THEN
1158 SELECT document_number INTO x_document_number FROM pon_auction_headers_all WHERE auction_header_id=x_negotiation_id;
1159 update po_requisition_lines_all
1160 SET auction_display_number = x_document_number
1161 where requisition_line_id = x_req_line_id ;
1162 END IF;
1163
1164 exception
1165 when others then
1166 po_message_s.sql_error('In Exception of create_negotiation()', x_progress, sqlcode);
1167 end;
1168 -- logme(' after update');
1169 -- <SERVICES FPJ START> For each Price Differential,
1170 -- call Sourcing API to add it.
1171 --
1172 IF ( l_has_price_diff_flag = 'Y' ) THEN
1173 -- logme( ' in price differentials');
1174 FOR l_price_diff_rec IN l_price_diff_csr(x_req_line_id) LOOP --Bug5841426
1175
1176
1177 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1178 fnd_log.string(fnd_log.level_statement,g_log_head,'Before PON_AUCTION_INTERFACE_PKG.add_price_differential');
1179 END IF;
1180
1181 PON_AUCTION_INTERFACE_PKG.add_price_differential
1182 ( p_document_number => x_negotiation_id
1183 , p_line_number => x_negotiation_line_num
1184 , p_shipment_number => -1
1185 , p_price_type => l_price_diff_rec.price_type
1186 , p_multiplier => l_price_diff_rec.min_multiplier
1187 , p_buyer_id => FND_PROFILE.value('user_id')
1188 , p_price_differential_number => l_price_diff_rec.price_differential_num
1189 , p_result => api_result
1190 , p_error_code => api_error_code
1191 , p_error_message => api_error_msg
1192 );
1193
1194 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1195 fnd_log.string(fnd_log.level_statement,g_log_head,'After PON_AUCTION_INTERFACE_PKG.add_price_differential');
1196 END IF;
1197
1198 IF ( api_result <> 0 ) -- API failure
1199 THEN
1200 x_result := api_result;
1201 x_error_code := api_error_code;
1202 x_error_message := api_error_msg;
1203 rollback;
1204 return;
1205 END IF;
1206
1207 END LOOP;
1208 -- logme( ' after price differentials');
1209
1210 END IF;
1211 --
1212 -- <SERVICES FPJ END>
1213
1214 x_result := api_result;
1215 x_error_code := api_error_code;
1216 x_error_message := api_error_msg;
1217
1218 else
1219 /* If unsuccessful return the error code to the form. */
1220 x_progress := '009';
1221 x_result := api_result;
1222 x_error_code := api_error_code;
1223 x_error_message := api_error_msg;
1224
1225 /* before returning we rollback and return */
1226 rollback;
1227 return ;
1228 end if;
1229 -- logme('end of loop');
1230 end loop;
1231 -- logme('after cursor loop');
1232 -- After the lines have been autocreated. Set the CLM clin slin and option relations appropriately.
1233 -- Currently the reqline ids have been copied over as it is.
1234 -- Now map the correct reqlineids with the neg line numbers and store
1235 -- in the negotiation lines table.
1236 x_progress := '010';
1237
1238 IF ( PON_CLM_UTIL_PKG.IS_NEG_DOCUMENT_FEDERAL(x_negotiation_id) = 1 ) THEN --Bug 12605930
1239
1240 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1241 fnd_log.string(fnd_log.level_statement,g_log_head,'Before PON_AUCTION_INTERFACE_PKG.maintain_clm_relations');
1242 END IF;
1243
1244 PON_AUCTION_INTERFACE_PKG.maintain_clm_relations(
1245 p_api_version => 1.0 -- IN
1246 , x_return_status => l_return_status -- OUT
1247 , x_msg_count => l_msg_count -- OUT
1248 , x_msg_data => l_msg_data -- OUT
1249 , p_document_number => x_negotiation_id -- IN
1250 );
1251
1252 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1253 fnd_log.string(fnd_log.level_statement,g_log_head,'After PON_AUCTION_INTERFACE_PKG.maintain_clm_relations');
1254 fnd_log.string(fnd_log.level_statement,g_log_head,'l_return_status : '||l_return_status);
1255 END IF;
1256
1257 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1258
1259 x_result := -1;
1260 x_error_message := l_msg_data;
1261
1262 -- before returning we rollback and return
1263 rollback;
1264 return ;
1265 end if;
1266
1267 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1268 fnd_log.string(fnd_log.level_statement,g_log_head,'Before PON_CLO_RENUMBER_PKG.RenumberAllLines');
1269 END IF;
1270
1271 -- Renumber the lines in the document.
1272 PON_CLO_RENUMBER_PKG.RenumberAllLines(
1273 p_Document_ID => x_negotiation_id -- IN
1274 ,p_product_code => 'PON' -- IN
1275 , x_status => l_return_status -- OUT
1276 );
1277
1278 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1279 fnd_log.string(fnd_log.level_statement,g_log_head,'After PON_CLO_RENUMBER_PKG.RenumberAllLines');
1280 END IF;
1281 END IF ;
1282
1283 /* if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1284
1285 x_result := -1;
1286 x_error_message := l_msg_data;
1287
1288 -- before returning we rollback and return
1289 rollback;
1290 return ;
1291 end if;
1292 */
1293
1294 x_progress := '020';
1295
1296 -- <ACHTML R12 START>
1297
1298 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1299 fnd_log.string(fnd_log.level_statement,g_log_head,'Before PON_AUCTION_INTERFACE_PKG.add_negotiation_invitees');
1300 END IF;
1301
1302 PON_AUCTION_INTERFACE_PKG.add_negotiation_invitees
1303 ( p_api_version => 1.0 -- IN
1304 , x_return_status => l_return_status -- OUT
1305 , x_msg_count => l_msg_count -- OUT
1306 , x_msg_data => l_msg_data -- OUT
1307 , p_document_number => x_negotiation_id -- IN
1308 , p_buyer_id => to_number(FND_PROFILE.VALUE('user_id')) -- IN
1309 );
1310
1311 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1312 fnd_log.string(fnd_log.level_statement,g_log_head,'After PON_AUCTION_INTERFACE_PKG.add_negotiation_invitees');
1313 fnd_log.string(fnd_log.level_statement,g_log_head,'l_return_status : '||l_return_status);
1314 END IF;
1315
1316
1317 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1318 x_progress := '030';
1319 x_result := -1;
1320 x_error_message := l_msg_data;
1321
1322 -- before returning we rollback and return
1323 rollback;
1324 return ;
1325 end if;
1326 -- <ACHTML R12 END>
1327
1328 -- <Catalog Convergence R12 START>
1329
1330 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1331 fnd_log.string(fnd_log.level_statement,g_log_head,'Before PON_AUCTION_INTERFACE_PKG.add_catalog_descriptors');
1332 END IF;
1333
1334
1335 PON_AUCTION_INTERFACE_PKG.add_catalog_descriptors
1336 ( p_api_version => 1.0
1337 , p_document_number => x_negotiation_id
1338 , x_return_status => l_return_status
1339 , x_msg_count => l_msg_count
1340 , x_msg_data => l_msg_data
1341 );
1342
1343 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1344 fnd_log.string(fnd_log.level_statement,g_log_head,'After PON_AUCTION_INTERFACE_PKG.add_catalog_descriptors');
1345 fnd_log.string(fnd_log.level_statement,g_log_head,'l_return_status : '||l_return_status);
1346 END IF;
1347
1348
1349 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1350 x_progress := '040';
1351 x_result := -1;
1352 x_error_message := l_msg_data;
1353
1354 -- before returning we rollback and return
1355 ROLLBACK;
1356 return ;
1357 END IF;
1358 -- <Catalog Convergence R12 END>
1359
1360 /* Bug 9881440 - Start */
1361 /* Call COPY_REQ_UDA_TO_SOL procedure to copy UDA data from REQ to SOL in case of CLM docs. */
1362
1363 IF ( PON_CLM_UTIL_PKG.IS_NEG_DOCUMENT_FEDERAL(x_negotiation_id) = 1 ) THEN
1364 COPY_REQ_UDA_TO_SOL(p_req_lines => t_req_lines,
1365 p_auction_header_id => x_negotiation_id);
1366 END IF;
1367
1368 /* Bug 9881440 - End */
1369
1370 --<Bug : 11071489 REQ_AUTOCREATE Start>--
1371 --Raise business event when negotiation created from Requisition
1372 -- from autocreate process
1373 x_progress := '040';
1374 l_event_name := 'oracle.apps.po.autocreate.negcreated';
1375 l_parameter_list(1).name := 'Auction_Header_id' ;
1376 l_parameter_list(1).value := x_negotiation_id;
1377 po_core_s4.raise_business_event(l_event_name,l_parameter_list);
1378 --<REQ_AUTOCREATE end>--
1379
1380 /* issue commit */
1381 commit;
1382
1383 EXCEPTION
1384 WHEN OTHERS THEN
1385 rollback;
1386 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1387 fnd_log.string(fnd_log.level_statement,g_log_head,'Exception : '||SQLERRM);
1388 END IF;
1389
1390 x_result := -1;
1391 --<HTMLAC START>
1392 IF (x_error_message IS NULL) THEN
1393 x_error_message := 'In Exception of create_negotiation:' || x_progress || ':' || fnd_message.get;
1394 END IF;
1395 --<HTMLAC END>
1396 po_message_s.sql_error('In Exception of create_negotiation()', x_progress, sqlcode);
1397 -- logme( 'In Exception of create_negotiation() - progress is ' || x_progress || ' sqlcode is ' || SQLCODE);
1398 END;
1399
1400 /*============================================================================
1401 Name: DELETE_NEGOTIATION_REF
1402 DESC: Delete negotiation reference from the backing requisition
1403 ==============================================================================*/
1404
1405 PROCEDURE DELETE_NEGOTIATION_REF (x_negotiation_id in number,
1406 x_negotiation_line_num in number,
1407 x_error_code out NOCOPY varchar2) is
1408
1409 BEGIN
1410 if x_negotiation_line_num is null then
1411
1412 -- <REQINPOOL>: added update of at_sourcing_flag and of
1413 -- WHO columns.
1414 update po_requisition_lines_all prla --Bug 4001965: use _all
1415 set auction_header_id = null,
1416 auction_display_number = null,
1417 auction_line_number = null,
1418 at_sourcing_flag = null, --<REQINPOOL>
1419 on_rfq_flag = null, -- bug 5370213
1420 --<Begin Bug#: 5203799> We don't want to set the reqs_in_pool_flag to 'Y'
1421 --if any of the following conditions are met.
1422 reqs_in_pool_flag =
1423 (CASE
1424 WHEN (nvl(modified_by_agent_flag,'N') = 'Y'
1425 or NVL(cancel_flag,'N') IN ('Y', 'I')
1426 or NVL(closed_code,'OPEN') = 'FINALLY CLOSED'
1427 or source_type_code = 'INVENTORY'
1428 or NVL(line_location_id, -999) <> -999
1429 or exists
1430 (select 'Req Header auth_status is not approved or contractor_status is pending'
1431 from po_requisition_headers_all prha
1432 where prha.requisition_header_id = prla.requisition_header_id
1433 and (NVL(prha.authorization_status,'INCOMPLETE') <> 'APPROVED'
1434 or NVL(prha.contractor_status,'NOT_APPLICABLE') = 'PENDING')))
1435 THEN null
1436 ELSE 'Y'
1437 END
1438 ), --<End Bug#: 5203799>
1439 last_update_date = SYSDATE,
1440 last_updated_by = FND_GLOBAL.USER_ID,
1441 last_update_login = FND_GLOBAL.LOGIN_ID
1442 where auction_header_id = x_negotiation_id;
1443
1444 else
1445 -- <REQINPOOL>: added update of at_sourcing_flag and of
1446 -- WHO columns.
1447 update po_requisition_lines_all prla --Bug 4001965: use _all
1448 set auction_header_id = null,
1449 auction_display_number = null,
1450 auction_line_number = null,
1451 at_sourcing_flag = null, --<REQINPOOL>
1452 on_rfq_flag = null, -- bug 5370213
1453 --<Begin Bug#: 5203799> We don't want to set the reqs_in_pool_flag to 'Y'
1454 --if any of the following conditions are met.
1455 reqs_in_pool_flag =
1456 (CASE
1457 WHEN (nvl(modified_by_agent_flag,'N') = 'Y'
1458 or NVL(cancel_flag,'N') IN ('Y', 'I')
1459 or NVL(closed_code,'OPEN') = 'FINALLY CLOSED'
1460 or source_type_code = 'INVENTORY'
1461 or NVL(line_location_id, -999) <> -999
1462 or exists
1463 (select 'Req Header auth_status is not approved or contractor_status is pending'
1464 from po_requisition_headers_all prha
1465 where prha.requisition_header_id = prla.requisition_header_id
1466 and (NVL(prha.authorization_status,'INCOMPLETE') <> 'APPROVED'
1467 or NVL(prha.contractor_status,'NOT_APPLICABLE') = 'PENDING')))
1468 THEN null
1469 ELSE 'Y'
1470 END
1471 ), --<End Bug#: 5203799>
1472 last_update_date = SYSDATE,
1473 last_updated_by = FND_GLOBAL.USER_ID,
1474 last_update_login = FND_GLOBAL.LOGIN_ID
1475 where auction_header_id = x_negotiation_id
1476 and auction_line_number = x_negotiation_line_num;
1477 end if;
1478
1479 x_error_code := 'SUCCESS';
1480
1481 EXCEPTION
1482 WHEN OTHERS THEN
1483 x_error_code := 'FAILURE';
1484 END;
1485
1486 /*============================================================================
1487 Name: UPDATE_NEGOTIATION_REF
1488 DESC: Update negotiation reference in the backing requisition
1489 ==============================================================================*/
1490
1491 PROCEDURE UPDATE_NEGOTIATION_REF (x_old_negotiation_id in number ,
1492 x_new_negotiation_id in number ,
1493 x_new_negotiation_num in varchar2 ,
1494 x_error_code out NOCOPY varchar2) is
1495 BEGIN
1496 update po_requisition_lines_all --Bug 4001965: use _all
1497 set auction_header_id = x_new_negotiation_id,
1498 auction_display_number = x_new_negotiation_num
1499 where auction_header_id = x_old_negotiation_id;
1500
1501 x_error_code := 'SUCCESS';
1502
1503 EXCEPTION
1504 WHEN OTHERS THEN
1505 x_error_code := 'FAILURE';
1506 END;
1507
1508 --<Bug 2440254 mbhargav START>
1509 --Provide Sourcing with an API which Given two negotiation lines,
1510 --update all requisition line from one negotiation line to
1511 --point to another negotiation line.
1512 /*============================================================================
1513 Name: UPDATE_NEGOTIATION_LINE_REF
1514 DESC: Update negotiation reference in the backing requisition line to
1515 point to another negotiation line.
1516 Input parameters :
1517 p_api_version: Version of the API expected by caller. Current value 1.0
1518 p_old_negotiation_id : negotiation whose reference has to be replaced
1519 p_old_negotiation_line_num : negotiation line whose reference has to be replaced
1520 p_new_negotiation_num/id : new negotiation reference
1521 p_new_negotiation_line_num : new negotiation line where reference has
1522 to be added
1523 Output parameters :
1524 x_return_status: The return status of the API. Valid values are:
1525 FND_API.G_RET_STS_SUCCESS
1526 FND_API.G_RET_STS_ERROR
1527 FND_API.G_RET_STS_UNEXP_ERROR
1528 x_error_message: Contain translated error message in case the return status
1529 is G_RET_STS_ERROR or G_RET_STS_UNEXP_ERROR
1530 Version: Current Version 1.0
1531 Changed: Initial design 1/27/2003
1532 Previous Version 1.0
1533 ==============================================================================*/
1534
1535 PROCEDURE UPDATE_NEGOTIATION_LINE_REF (
1536 p_api_version IN NUMBER,
1537 p_old_negotiation_id IN NUMBER,
1538 p_old_negotiation_line_num IN NUMBER,
1539 p_new_negotiation_id IN NUMBER,
1540 p_new_negotiation_line_num IN NUMBER,
1541 p_new_negotiation_num IN varchar2,
1542 x_return_status OUT NOCOPY varchar2,
1543 x_error_message OUT NOCOPY varchar2) is
1544
1545 l_api_name CONSTANT varchar2(30) := 'UPDATE_NEGOTIATION_LINE_REF';
1546 l_api_version CONSTANT NUMBER := 1.0;
1547
1548 l_progress varchar2(3);
1549 BEGIN
1550 l_progress := '000';
1551
1552 -- Standard call to check for call compatibility
1553 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
1554 THEN
1555 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1556 END IF;
1557
1558 l_progress := '001';
1559
1560 update po_requisition_lines_all --Bug 4001965: use _all
1561 set auction_header_id = p_new_negotiation_id,
1562 auction_display_number = p_new_negotiation_num,
1563 auction_line_number = p_new_negotiation_line_num
1564 where auction_header_id = p_old_negotiation_id and
1565 auction_line_number = p_old_negotiation_line_num;
1566
1567 x_return_status := FND_API.G_RET_STS_SUCCESS;
1568
1569 EXCEPTION
1570 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1571 x_error_message := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,
1572 p_encoded => 'F');
1573 x_return_status := FND_API.G_RET_STS_ERROR;
1574 WHEN OTHERS THEN
1575 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1576 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_api_name,
1577 SUBSTRB (SQLERRM , 1 , 200) || ' at location ' || l_progress);
1578 END IF;
1579
1580 x_error_message := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,
1581 p_encoded => 'F');
1582 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1583
1584 END UPDATE_NEGOTIATION_LINE_REF;
1585 --<Bug 2440254 mbhargav END>
1586
1587 /*============================================================================
1588 Name: UPDATE_REQ_POOL
1589 DESC: Update requisition pool flag in the backing requisition
1590 ==============================================================================*/
1591
1592 PROCEDURE UPDATE_REQ_POOL (x_negotiation_id in number,
1593 x_negotiation_line_num in number,
1594 x_flag_value in varchar2,
1595 x_error_code out NOCOPY varchar2) is
1596
1597 x_sourcing_flag_value PO_REQUISITION_LINES_ALL.at_sourcing_flag%TYPE; -- <REQINPOOL>
1598 x_new_pool_value PO_REQUISITION_LINES_ALL.reqs_in_pool_flag%TYPE; -- <REQINPOOL>
1599 BEGIN
1600 -- <REQINPOOL>: check sourcing flag value and convert to new Y/NULL
1601 -- domain for reqs_in_pool_flag and new col at_sourcing_flag
1602 IF(x_flag_value = 'N') THEN
1603 x_sourcing_flag_value := 'Y';
1604 x_new_pool_value := NULL;
1605 ELSE
1606 x_sourcing_flag_value := NULL;
1607 x_new_pool_value := 'Y';
1608 END IF;
1609
1610
1611 if x_negotiation_line_num is null then
1612 -- <REQINPOOL>: added update of at_sourcing_flag and of
1613 -- WHO columns.
1614 update po_requisition_lines_all prla --Bug 4001965: use _all
1615 set reqs_in_pool_flag = x_new_pool_value,
1616 at_sourcing_flag = x_sourcing_flag_value, --<REQINPOOL>
1617 last_update_date = SYSDATE,
1618 last_updated_by = FND_GLOBAL.USER_ID,
1619 last_update_login = FND_GLOBAL.LOGIN_ID
1620 where auction_header_id = x_negotiation_id
1621 --<Begin Bug#: 5203799> We only want to set the reqs_in_pool_flag to 'Y'
1622 --if all of the following conditions are met.
1623 and nvl(modified_by_agent_flag,'N') <> 'Y' --<BUG#: 5067460 ,BUG#:4957635>
1624 and NVL(cancel_flag,'N') NOT IN ('Y', 'I')
1625 and NVL(closed_code,'OPEN') <> 'FINALLY CLOSED'
1626 and source_type_code <> 'INVENTORY'
1627 and NVL(line_location_id, -999) = -999
1628 and not exists
1629 (select 'Req Header auth_status is not approved or contractor_status is pending'
1630 from po_requisition_headers_all prha
1631 where prha.requisition_header_id = prla.requisition_header_id
1632 and (NVL(prha.authorization_status,'INCOMPLETE') <> 'APPROVED'
1633 or NVL(prha.contractor_status,'NOT_APPLICABLE') = 'PENDING'));
1634 --<End Bug#: 5203799>
1635
1636 else
1637 -- <REQINPOOL>: added update of at_sourcing_flag and of
1638 -- WHO columns.
1639 update po_requisition_lines_all prla --Bug 4001965: use _all
1640 set reqs_in_pool_flag = x_new_pool_value,
1641 at_sourcing_flag = x_sourcing_flag_value, --<REQINPOOL>
1642 last_update_date = SYSDATE,
1643 last_updated_by = FND_GLOBAL.USER_ID,
1644 last_update_login = FND_GLOBAL.LOGIN_ID
1645 where auction_header_id = x_negotiation_id
1646 and auction_line_number = x_negotiation_line_num
1647 --<Begin Bug#: 5203799> We only want to set the reqs_in_pool_flag to 'Y'
1648 --if all of the following conditions are met.
1649 and nvl(modified_by_agent_flag,'N') <> 'Y' --<BUG#: 5067460 ,BUG#:4957635>
1650 and NVL(cancel_flag,'N') NOT IN ('Y', 'I')
1651 and NVL(closed_code,'OPEN') <> 'FINALLY CLOSED'
1652 and source_type_code <> 'INVENTORY'
1653 and NVL(line_location_id, -999) = -999
1654 and not exists
1655 (select 'Req Header auth_status is not approved or contractor_status is pending'
1656 from po_requisition_headers_all prha
1657 where prha.requisition_header_id = prla.requisition_header_id
1658 and (NVL(prha.authorization_status,'INCOMPLETE') <> 'APPROVED'
1659 or NVL(prha.contractor_status,'NOT_APPLICABLE') = 'PENDING'));
1660 --<End Bug#: 5203799>
1661 end if;
1662
1663 x_error_code := 'SUCCESS';
1664
1665 EXCEPTION
1666 WHEN OTHERS THEN
1667 x_error_code := 'FAILURE';
1668 END;
1669
1670 /*============================================================================
1671 Name: check_negotiation_ref
1672 DESC: checks if a req line/header has negotiation reference
1673 ==============================================================================*/
1674
1675 PROCEDURE check_negotiation_ref(x_doc_level IN VARCHAR2,
1676 x_doc_id IN NUMBER,
1677 x_negotiation_ref_flag IN OUT NOCOPY varchar2) is
1678
1679 cursor c1(x_doc_line_id in number) is
1680 select at_sourcing_flag --<REQINPOOL>
1681 from po_requisition_lines_all --Bug 4001965: use _all
1682 where requisition_line_id = X_doc_line_id;
1683
1684 cursor c2(x_doc_header_id in number) is
1685 select at_sourcing_flag --<REQINPOOL>
1686 from po_requisition_lines_all --Bug 4001965: use _all
1687 where requisition_header_id = X_doc_header_id;
1688
1689 x_doc_line_id number;
1690 x_doc_header_id number;
1691 x_sourcing_flag PO_REQUISITION_LINES_ALL.at_sourcing_flag%TYPE; --<REQINPOOL>
1692 x_sourcing_install_status varchar2(1);
1693
1694 BEGIN
1695
1696 if x_doc_level = 'REQ LINE' then
1697
1698 x_doc_line_id := x_doc_id;
1699 open c1(x_doc_line_id);
1700 loop
1701 fetch c1 into x_sourcing_flag;
1702 EXIT WHEN c1%NOTFOUND;
1703
1704 if x_sourcing_flag = 'Y' then
1705 x_negotiation_ref_flag := 'Y';
1706 end if;
1707
1708 end loop;
1709 close c1;
1710
1711 elsif x_doc_level = 'REQ HEADER' then
1712
1713 x_doc_header_id := x_doc_id;
1714 open c2(x_doc_header_id);
1715 loop
1716 fetch c2 into x_sourcing_flag;
1717 EXIT WHEN c2%NOTFOUND;
1718
1719 if x_sourcing_flag ='Y' then
1720 x_negotiation_ref_flag := 'Y';
1721 exit;
1722 end if;
1723 end loop;
1724 close c2;
1725
1726 end if;
1727
1728
1729 END;
1730
1731 PROCEDURE renegotiate_blanket( p_api_version IN NUMBER,
1732 p_commit IN varchar2,
1733 p_po_header_id IN NUMBER,
1734 p_negotiation_type IN varchar2,
1735 x_negotiation_id OUT NOCOPY NUMBER,
1736 x_doc_url_params OUT NOCOPY varchar2,
1737 x_return_status OUT NOCOPY varchar2,
1738 x_error_code OUT NOCOPY varchar2,
1739 x_error_message OUT NOCOPY varchar2) IS
1740 l_large_negotiation VARCHAR2(1);
1741 l_large_neg_request_id NUMBER;
1742
1743 BEGIN
1744 renegotiate_blanket( p_api_version ,
1745 p_commit ,
1746 p_po_header_id ,
1747 p_negotiation_type ,
1748 x_negotiation_id ,
1749 x_doc_url_params ,
1750 x_return_status ,
1751 x_error_code ,
1752 x_error_message ,
1753 l_large_negotiation ,
1754 l_large_neg_request_id ) ;
1755 END;
1756
1757 --<RENEG BLANKET FPI START>
1758 /*============================================================================
1759 Name : RENEGOTIATE_BLANKET
1760 Type : Private
1761 Function : This procedure
1762 a. populates the Sourcing Interface tables
1763 b. Calls Sourcing APIs for creating draft_negotiation and purging interface tables
1764 Pre-req : None
1765 Parameters:
1766 IN : p_api_version IN NUMBER REQUIRED
1767 p_commit IN varchar2 REQUIRED
1768 p_po_header_id IN NUMBER REQUIRED
1769 p_negotiation_type IN varchar2 REQUIRED
1770 OUT para : x_negotiation_id OUT NOCOPY NUMBER
1771 x_doc_url_params OUT NOCOPY varchar2
1772 x_return_status OUT NOCOPY varchar2
1773 x_error_code OUT NOCOPY varchar2
1774 x_error_message OUT NOCOPY varchar2
1775 Version : Current Version 1.0
1776 Changed: Initial design 10/1/2002
1777 Previous Version 1.0
1778 ==============================================================================*/
1779 PROCEDURE renegotiate_blanket( p_api_version IN NUMBER,
1780 p_commit IN varchar2,
1781 p_po_header_id IN NUMBER,
1782 p_negotiation_type IN varchar2,
1783 x_negotiation_id OUT NOCOPY NUMBER,
1784 x_doc_url_params OUT NOCOPY varchar2,
1785 x_return_status OUT NOCOPY varchar2,
1786 x_error_code OUT NOCOPY varchar2,
1787 x_error_message OUT NOCOPY varchar2,
1788 x_large_negotiation OUT NOCOPY varchar2,
1789 x_large_neg_request_id OUT NOCOPY NUMBER) IS
1790
1791 l_api_name CONSTANT varchar2(30) := 'RENEGOTIATE_BLANKET';
1792 l_api_version CONSTANT NUMBER := 1.0;
1793
1794 l_po_num po_headers.segment1%type := NULL;
1795 l_interface_id NUMBER;
1796
1797 l_create_api_result varchar2(30) :=NULL;
1798 l_create_api_err_code varchar2(100) := NULL;
1799 l_create_api_err_msg varchar2(400) := NULL;
1800
1801 l_ret_sts_success varchar2(30) := 'SUCCESS';
1802 l_ret_sts_error varchar2(30) := 'FAILURE';
1803
1804 l_progress varchar2(3);
1805 l_user_id NUMBER := -1;
1806
1807 l_result VARCHAR2(10);
1808 l_error_code VARCHAR2(100);
1809 l_error_message VARCHAR2(1000);
1810
1811 --<Catalog Convergence 12.0 START>
1812 l_po_created_language PO_HEADERS_ALL.created_language%TYPE;
1813
1814 --<Catalog Convergence 12.0 END>
1815 BEGIN
1816 l_progress := '000';
1817
1818 -- Standard start of API savepoint
1819 SAVEPOINT renegotiate_blanket_grp;
1820
1821 -- Standard call to check for call compatibility
1822 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
1823 THEN
1824 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1825 END IF;
1826
1827 l_progress := '001';
1828
1829 --Bug 2694722 : Getting user id
1830 l_user_id := FND_GLOBAL.USER_ID;
1831
1832 --Populate PON_AUC_HEADERS_INTERFACE table
1833 insert into pon_auc_headers_interface(
1834 interface_auction_header_id,
1835 neg_type,
1836 contract_type,
1837 ship_to_location_id,
1838 bill_to_location_id,
1839 payment_terms_id,
1840 freight_terms_code,
1841 fob_code,
1842 carrier_code,
1843 note_to_bidders,
1844 creation_date,
1845 created_by,
1846 last_update_date,
1847 last_updated_by,
1848 allow_other_bid_currency_flag,
1849 po_agreed_amount,
1850 origination_code,
1851 global_agreement_flag,
1852 po_min_rel_amount,
1853 currency_code,
1854 rate_type,
1855 rate_date,
1856 rate,
1857 user_id,
1858 org_id,
1859 supplier_id,
1860 source_doc_id,
1861 source_doc_number,
1862 source_doc_msg,
1863 source_doc_line_msg,
1864 source_doc_msg_app,
1865 po_style_id, -- BUG#5532470
1866 language_code, --Bug#4911361
1867 supplier_site_id)
1868 select PON_AUC_HEADERS_INTERFACE_S.nextval,
1869 p_negotiation_type,
1870 'BLANKET',
1871 ship_to_location_id,
1872 bill_to_location_id,
1873 terms_id,
1874 freight_terms_lookup_code,
1875 fob_lookup_code,
1876 ship_via_lookup_code,
1877 note_to_vendor,
1878 creation_date,
1879 created_by,
1880 last_update_date,
1881 last_updated_by,
1882 'Y',
1883 blanket_total_amount,
1884 type_lookup_code,
1885 global_agreement_flag,
1886 min_release_amount,
1887 currency_code,
1888 rate_type,
1889 rate_date,
1890 rate,
1891 l_user_id,
1892 org_id,
1893 vendor_id,
1894 po_header_id,
1895 segment1,
1896 --<Bug 2917962 mbhargav START>
1897 --Sourcing team wants the name of the messages and
1898 --not the message text. So inserting the names of messages
1899 --for 'Blanket Agreement' and 'Line' respectively
1900 'PO_POTYPE_BLKT',
1901 'PO_SOURCING_LINE_NUMBER',
1902 --<Bug 2917962 mbhargav END>
1903 'PO',
1904 style_id, -- BUG#5532470
1905 created_language, --Bug#4911361
1906 vendor_site_id --<Bug 3325876>
1907 from po_headers
1908 where po_header_id=p_po_header_id;
1909
1910 l_progress := '002';
1911
1912 -- Get the interface_id into local variable
1913 select PON_AUC_HEADERS_INTERFACE_S.currval
1914 into l_interface_id
1915 from dual;
1916
1917 l_progress := '003';
1918 -- Get the segment1 from p_po_header_id
1919 select segment1
1920 into l_po_num
1921 from po_headers
1922 where po_header_id = p_po_header_id;
1923
1924
1925 l_progress := '004';
1926 --Bug #2737797
1927 --Amount agreed needs to passed instead of unit_price to
1928 --bid_start_price column in pon_auc_items_interface for
1929 -- amount based lines.
1930
1931 l_progress := '005';
1932 --Populate PON_AUC_ITEMS_INTERFACE table
1933 insert into pon_auc_items_interface(
1934 interface_auction_header_id,
1935 interface_line_number,
1936 line_type_id,
1937 item_description,
1938 org_id,
1939 category_id,
1940 quantity,
1941 current_price, --Bug#4915340
1942 note_to_bidders,
1943 uom_code,
1944 creation_date,
1945 created_by,
1946 last_update_date,
1947 last_updated_by,
1948 origination_code,
1949 po_min_rel_amount,
1950 price_break_type,
1951 item_id,
1952 item_number,
1953 item_revision,
1954 source_doc_number,
1955 source_line_number,
1956 source_doc_id,
1957 source_line_id,
1958 job_id, -- <SERVICES FPJ>
1959 po_agreed_amount, -- <SERVICES FPJ>
1960 purchase_basis, -- <SERVICES FPJ>
1961 ip_category_id, -- <Catalog Convergence 12.0>
1962 -- Clin Slin Changes
1963 line_num_display,
1964 group_line_id,
1965 clm_info_flag,
1966 clm_option_indicator,
1967 clm_option_num,
1968 clm_option_from_date,
1969 clm_option_to_date,
1970 clm_funded_flag,
1971 clm_base_line_num,
1972 clm_contract_type,
1973 clm_cost_constraint,
1974 clm_idc_type
1975 )
1976 select l_interface_id,
1977 rownum, --bug 2714549: renumbers lines
1978 pl.line_type_id,
1979 item_description,
1980 pl.org_id,
1981 pl.category_id,
1982 quantity_committed, --Bug #2706156
1983 --Bug #2737797
1984 decode ( PL.order_type_lookup_code -- <SERVICES FPJ>
1985 , 'AMOUNT' , PL.committed_amount
1986 , 'FIXED PRICE' , PL.amount
1987 , PL.unit_price
1988 ),
1989 note_to_vendor,
1990 mum.uom_code,
1991 pl.creation_date,
1992 pl.created_by,
1993 pl.last_update_date,
1994 pl.last_updated_by,
1995 'BLANKET',
1996 min_release_amount,
1997 price_break_lookup_code,
1998 item_id,
1999 msi.concatenated_segments,
2000 item_revision,
2001 l_po_num,
2002 line_num, --original (non-renumbered) line num
2003 po_header_id,
2004 po_line_id,
2005 PL.job_id, -- <SERVICES FPJ>
2006 decode ( PL.order_type_lookup_code -- <SERVICES FPJ>
2007 , 'FIXED PRICE' , PL.committed_amount
2008 , 'RATE' , PL.committed_amount
2009 , NULL),
2010 PL.purchase_basis, -- <SERVICES FPJ>
2011 pl.ip_category_id, -- <Catalog Convergence 12.0>
2012
2013 -- Clin Slin Changes
2014 pl.line_num_display,
2015 pl.group_line_id,
2016 pl.clm_info_flag,
2017 pl.clm_option_indicator,
2018 pl.clm_option_num,
2019 pl.clm_option_from_date,
2020 pl.clm_option_to_date,
2021 pl.clm_funded_flag,
2022 pl.clm_base_line_num,
2023 pl.contract_type,
2024 pl.cost_constraint,
2025 pl.clm_idc_type
2026
2027 from po_lines pl, mtl_units_of_measure mum, mtl_system_items_kfv msi,
2028 financials_system_parameters fsp --<Bug 3274272,3330235>
2029 where po_header_id=p_po_header_id and
2030 mum.unit_of_measure (+) = pl.unit_meas_lookup_code and -- <BUG 3211566>
2031 --bug #2716412: made pl/msi join an outer join
2032 pl.item_id = msi.inventory_item_id(+) and
2033 --<Bug 3274272, 3330235>
2034 (pl.item_id IS NULL OR fsp.inventory_organization_id = msi.organization_id);
2035
2036 --<Catalog Convergence 12.0 START>
2037 --call API to insert descriptor values for each PO line
2038
2039 SELECT created_language into l_po_created_language
2040 FROM po_headers_all
2041 WHERE po_header_id = p_po_header_id;
2042
2043 PO_ATTRIBUTE_VALUES_PVT.handle_attributes(p_interface_header_id => l_interface_id,
2044 p_po_header_id => p_po_header_id,
2045 p_language => l_po_created_language);
2046 --<Catalog Convergence 12.0 END>
2047
2048 -- Clin Slin Changes
2049 -- Correcting the Clin Slin fields. They contain the references to the po Line ids.
2050 -- convert them to the interface line numbers
2051
2052 PON_SOURCING_OPENAPI_GRP.maintain_clm_relations(l_interface_id,
2053 l_result,
2054 l_error_code ,
2055 l_error_message);
2056
2057
2058
2059 --<Bug 2699631 mbhargav START>
2060 --Earlier the Release line locations for this Blanket were also getting copied over
2061 --to Sourcing. Added the pll.shipment_type = 'PRICE BREAK' in WHERE clause below
2062 --so that only price breaks of Blanket are copied over
2063 --<Bug 2699631 mbhargav END>
2064
2065 l_progress := '006';
2066 -- Populate PON_AUC_SHIPMENTS_INTERFACE table
2067 insert into pon_auc_shipments_interface(
2068 interface_auction_header_id,
2069 interface_line_number,
2070 interface_ship_number,
2071 shipment_type,
2072 ship_to_organization_id,
2073 ship_to_location_id,
2074 quantity,
2075 price,
2076 org_id,
2077 creation_date,
2078 created_by,
2079 last_update_date,
2080 last_updated_by)
2081 select l_interface_id,
2082 --bug 2714549: get renumbered line#
2083 paii.interface_line_number,
2084 pll.shipment_num,
2085 pll.shipment_type,
2086 pll.ship_to_organization_id,
2087 pll.ship_to_location_id,
2088 pll.quantity,
2089 pll.price_override,
2090 pll.org_id,
2091 pll.creation_date,
2092 pll.created_by,
2093 pll.last_update_date,
2094 pll.last_updated_by
2095 from po_line_locations pll,
2096 -- bug 2714549: added paii to join; removed join to po_lines
2097 pon_auc_items_interface paii
2098 where pll.po_header_id = p_po_header_id and
2099 --bug 2714549 start: changed join conditions from po_lines
2100 -- to paii and added auction_header cond
2101 -- to ensure unique doc_id/line_id from paii
2102 paii.source_doc_id = p_po_header_id and
2103 paii.source_line_id = pll.po_line_id and
2104 paii.interface_auction_header_id = l_interface_id and
2105 --bug 2714549 end
2106 pll.shipment_type = 'PRICE BREAK';
2107
2108
2109 -- <SERVICES FPJ START>
2110
2111 -- Populate PON_PRICE_DIFFERENTIALS_INTERFACE Table
2112 -- with Line-level Price Differentials
2113
2114 INSERT INTO pon_price_differ_interface
2115 ( interface_auction_header_id
2116 , interface_line_number
2117 , interface_shipment_number
2118 , interface_price_differ_number
2119 , price_type
2120 , multiplier
2121 , process_status
2122 , creation_date
2123 , created_by
2124 , last_update_date
2125 , last_updated_by
2126 , last_update_login
2127 )
2128 SELECT l_interface_id
2129 , POL.line_num
2130 , -1 -- <BUG 3212055> Insert -1 when shipment not present.
2131 , PD.price_differential_num
2132 , PD.price_type
2133 , PD.min_multiplier
2134 , NULL
2135 , PD.creation_date
2136 , PD.created_by
2137 , PD.last_update_date
2138 , PD.last_updated_by
2139 , PD.last_update_login
2140 FROM po_price_differentials PD
2141 , po_lines_all POL
2142 WHERE PD.entity_type = 'BLANKET LINE'
2143 AND PD.entity_id = POL.po_line_id
2144 AND POL.po_header_id = p_po_header_id;
2145
2146
2147 -- Populate PON_PRICE_DIFFERENTIALS_INTERFACE Table
2148 -- with Price Break-level Price Differentials
2149
2150 INSERT INTO pon_price_differ_interface
2151 ( interface_auction_header_id
2152 , interface_line_number
2153 , interface_shipment_number
2154 , interface_price_differ_number
2155 , price_type
2156 , multiplier
2157 , process_status
2158 , creation_date
2159 , created_by
2160 , last_update_date
2161 , last_updated_by
2162 , last_update_login
2163 )
2164 SELECT l_interface_id
2165 , POL.line_num
2166 , POLL.shipment_num
2167 , PD.price_differential_num
2168 , PD.price_type
2169 , PD.min_multiplier
2170 , NULL
2171 , PD.creation_date
2172 , PD.created_by
2173 , PD.last_update_date
2174 , PD.last_updated_by
2175 , PD.last_update_login
2176 FROM po_price_differentials PD
2177 , po_lines_all POL
2178 , po_line_locations_all POLL
2179 WHERE PD.entity_type = 'PRICE BREAK'
2180 AND PD.entity_id = POLL.line_location_id
2181 AND POLL.po_line_id = POL.po_line_id
2182 AND POL.po_header_id = p_po_header_id;
2183
2184 -- <SERVICES FPJ END>
2185
2186
2187 l_progress := '007';
2188 --Populate PON_ATTACHMENTS_INTERFACE with header level attachments
2189 insert into pon_attachments_interface(
2190 interface_auction_header_id,
2191 interface_line_number,
2192 document_id,
2193 seq_num,
2194 last_update_date,
2195 last_updated_by,
2196 creation_date,
2197 created_by)
2198 select l_interface_id,
2199 NULL,
2200 fad.document_id,
2201 fad.seq_num,
2202 fad.last_update_date,
2203 fad.last_updated_by,
2204 fad.creation_date,
2205 fad.created_by
2206 from fnd_attached_documents fad,
2207 fnd_documents fd,
2208 fnd_documents_tl fdtl
2209 where fad.document_id = fd.document_id AND
2210 fd.document_id = fdtl.document_id AND
2211 fdtl.language = userenv('LANG') AND
2212 fad.entity_name = 'PO_HEADERS' AND
2213 fad.pk1_value = to_char(p_po_header_id) AND
2214 fd.category_id <> 39;
2215
2216 l_progress := '008';
2217 --Populate PON_ATTACHMENTS_INTERFACE with line level attachments
2218 insert into pon_attachments_interface(
2219 interface_auction_header_id,
2220 interface_line_number,
2221 document_id,
2222 seq_num,
2223 last_update_date,
2224 last_updated_by,
2225 creation_date,
2226 created_by)
2227 select l_interface_id,
2228 --bug 2714549: get renumbered line#
2229 paii.interface_line_number,
2230 fad.document_id,
2231 fad.seq_num,
2232 fad.last_update_date,
2233 fad.last_updated_by,
2234 fad.creation_date,
2235 fad.created_by
2236 from fnd_attached_documents fad,
2237 fnd_documents fd,
2238 fnd_documents_tl fdtl,
2239 --bug 2714549: replaced join to po_lines
2240 -- with join to paii.
2241 pon_auc_items_interface paii
2242 where fad.document_id = fd.document_id AND
2243 fd.document_id = fdtl.document_id AND
2244 fdtl.language = userenv('LANG') AND
2245 fad.entity_name = 'PO_LINES' AND
2246 --bug 2714549 start: changed join conditions from po_lines
2247 -- to paii and added auction_header cond
2248 -- to ensure unique doc_id/line_id from paii
2249 paii.source_doc_id = p_po_header_id AND
2250 fad.pk1_value = to_char(paii.source_line_id) AND
2251 paii.interface_auction_header_id = l_interface_id and
2252 --bug 2714549 end
2253 fd.category_id <> 39;
2254
2255 l_progress := '009';
2256 -- Call Sourcing API to create draft negotiation
2257 -- Catalog Convergence 12.0 - new signature to support
2258 -- large negotiations where sourcing could launch a concurrent program
2259 -- and return the request id to PO for display. Also changed call to
2260 -- use parameter name/value convention
2261 PON_SOURCING_OPENAPI_GRP.CREATE_DRAFT_NEG_INTERFACE(p_interface_id => l_interface_id,
2262 x_document_number => x_negotiation_id,
2263 x_document_url => x_doc_url_params,
2264 x_concurrent_program_started => x_large_negotiation,
2265 x_request_id => x_large_neg_request_id,
2266 x_result => l_create_api_result,
2267 x_error_code => l_create_api_err_code,
2268 x_error_message => l_create_api_err_msg);
2269 l_progress := '010';
2270
2271 --<Catalog Convergence 12.0 START>
2272 -- PO no longer needs to make this call. Sourcing will handle this.
2273 -- With large negotiation support, it would be incorrect to call purge
2274
2275 -- Call Sourcing API to purge interface tables
2276 --PON_SOURCING_OPENAPI_GRP.PURGE_INTERFACE_TABLE(l_interface_id,
2277 -- l_purge_api_result,
2278 -- l_purge_api_err_code,
2279 -- l_purge_api_err_msg);
2280
2281 --<Catalog Convergence 12.0 END>
2282
2283 l_progress := '011';
2284
2285 /* Return appropriate error message. In case of failure of both APIs then
2286 error messsage corresponding to create_draft_neg is returned */
2287 --<Catalog Convergence 12.0 START>
2288 -- Since Purge call is no longer required removed checks for return status
2289 -- from that call
2290 if (l_create_api_result = l_ret_sts_success) then
2291 x_return_status := l_ret_sts_success;
2292 x_error_code := l_create_api_err_code;
2293 x_error_message := l_create_api_err_msg;
2294 else
2295 x_return_status := l_ret_sts_error;
2296 x_error_code := l_create_api_err_code;
2297 x_error_message := l_create_api_err_msg;
2298 end if;
2299 --<Catalog Convergence 12.0. END>
2300
2301 -- Committing the changes to the database
2302 if FND_API.To_Boolean(p_commit) then
2303 commit;
2304 end if;
2305
2306 EXCEPTION
2307 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2308 ROLLBACK TO renegotiate_blanket_grp;
2309 x_error_message := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,
2310 p_encoded => 'F');
2311 x_return_status := l_ret_sts_error;
2312 WHEN OTHERS THEN
2313 ROLLBACK TO renegotiate_blanket_grp;
2314 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2315 FND_MSG_PUB.add_exc_msg(G_PKG_NAME, l_api_name,
2316 SUBSTRB (SQLERRM , 1 , 200) || ' at location ' || l_progress);
2317 END IF;
2318
2319 x_error_message := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,
2320 p_encoded => 'F');
2321 x_return_status := l_ret_sts_error;
2322
2323 END RENEGOTIATE_BLANKET;
2324 --<RENEG BLANKET FPI END>
2325
2326 -- Bug 3780359 Start
2327 -------------------------------------------------------------------------------
2328 --Start of Comments
2329 --Name: get_auction_display_line_num
2330 --Pre-reqs:
2331 -- None
2332 --Modifies:
2333 -- None
2334 --Locks:
2335 -- None
2336 --Function:
2337 -- This procedure retrieves the display number of an auction line, given
2338 -- the auction header ID and auction line number
2339 --Parameters:
2340 --IN:
2341 --p_auction_header_id
2342 -- Auction Header ID, unique identifier of an auction
2343 --p_auction_line_number
2344 -- Auction Line Number, unique identifier of a line of a particular auction
2345 --OUT:
2346 --x_auction_display_line_num
2347 -- Display number of an auction line for display purpose only
2348 --Notes:
2349 -- None
2350 --Testing:
2351 -- None
2352 --End of Comments
2353 -------------------------------------------------------------------------------
2354 PROCEDURE get_auction_display_line_num(
2355 p_auction_header_id IN NUMBER,
2356 p_auction_line_number IN NUMBER,
2357 x_auction_display_line_num OUT NOCOPY VARCHAR2)
2358 IS
2359
2360 l_api_name CONSTANT VARCHAR2(30):= 'GET_AUCTION_DISPLAY_LINE_NUM';
2361 l_progress VARCHAR2(3);
2362
2363 l_return_status VARCHAR2(1);
2364 l_msg_count NUMBER;
2365 l_msg_data VARCHAR2(2000);
2366
2367 BEGIN
2368 l_progress := '000';
2369 IF g_debug_stmt THEN
2370 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2371 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, g_log_head || l_api_name||
2372 '.' || l_progress, ' Begin');
2373 END IF;
2374 END IF;
2375
2376 PON_SOURCING_OPENAPI_GRP.get_display_line_number(
2377 p_api_version => 1.0,
2378 p_init_msg_list => 'F',
2379 p_auction_header_id => p_auction_header_id,
2380 p_auction_line_number => p_auction_line_number,
2381 x_display_line_number => x_auction_display_line_num,
2382 x_return_status => l_return_status,
2383 x_msg_count => l_msg_count,
2384 x_msg_data => l_msg_data);
2385
2386 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2387 l_progress := '010';
2388 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2389 END IF;
2390
2391 l_progress := '020';
2392 IF g_debug_stmt THEN
2393 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2394 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, g_log_head || l_api_name||
2395 '.' || l_progress, ' End');
2396 END IF;
2397 END IF;
2398
2399 EXCEPTION
2400 WHEN OTHERS THEN
2401 IF g_debug_stmt THEN
2402 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2403 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,g_log_head ||
2404 l_api_name||'.' || l_progress, ' Exception has occured.' ||
2405 ' l_msg_data: ' || l_msg_data || ' l_msg_count: ' || l_msg_count);
2406 END IF;
2407 END IF;
2408
2409 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2410 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name,
2411 SUBSTRB (SQLERRM , 1 , 200) || ' at location ' || l_progress);
2412 END IF;
2413
2414 x_auction_display_line_num := NULL;
2415
2416 END get_auction_display_line_num;
2417 -- Bug 3780359 End
2418
2419
2420 PROCEDURE add_line_to_negotiation(p_auction_header_id IN Number,
2421 p_req_line_id IN NUMBER,
2422 x_result IN OUT NOCOPY number,
2423 x_error_code IN OUT NOCOPY varchar2,
2424 x_error_message IN OUT NOCOPY varchar2)
2425 IS
2426
2427
2428 x_org_id number := null;
2429 api_result number := 0;
2430 api_error_code varchar2(2000) := null;
2431 api_error_msg varchar2(2000) := null;
2432 x_ship_to_location_id number;
2433 x_deliver_to_location_id number;
2434 x_negotiation_line_num number;
2435 x_req_header_id number;
2436 x_note_to_vendor po_requisition_lines_all.note_to_vendor%TYPE;
2437 x_need_by_date date := null;
2438 x_req_num varchar2(30);
2439 x_item_num varchar2(80);
2440 x_item_desc varchar2(240);
2441 x_unit_price number;
2442 x_uom_code varchar2(3);
2443 l_neg_outcome Varchar2(40);
2444 l_value_basis PO_REQUISITION_LINES.order_type_lookup_code%TYPE;
2445 l_req_line_id PO_REQUISITION_LINES.requisition_line_id%TYPE;
2446 l_amount PO_REQUISITION_LINES.amount%TYPE;
2447 l_job_name PER_JOBS_VL.name%TYPE;
2448 l_job_long_description PO_REQUISITION_LINES.job_long_description%TYPE;
2449 l_has_price_diff_flag VARCHAR2(1);
2450 l_bid_start_price PO_REQUISITION_LINES.unit_price%TYPE;
2451 l_po_agreed_amount PO_REQUISITION_LINES.amount%TYPE;
2452
2453 l_return_status VARCHAR2(1);
2454 l_msg_count NUMBER;
2455 l_msg_data VARCHAR2(2000);
2456
2457 CURSOR l_price_diff_csr ( p_req_line_id NUMBER ) IS
2458 SELECT *
2459 FROM po_price_differentials
2460 WHERE entity_type = 'REQ LINE'
2461 AND entity_id = p_req_line_id;
2462
2463 l_conv_rate NUMBER;
2464 l_from_org_id PO_REQUISITION_LINES_ALL.org_id%TYPE;
2465
2466 x_progress varchar2(3);
2467 TYPE t_crs is ref cursor;
2468 l_cursor t_crs;
2469 l_string VARCHAR2(10000);
2470
2471 x_line_type po_requisition_lines_all.LINE_TYPE_ID%type;
2472 x_item_id po_requisition_lines_all.ITEM_ID%type;
2473 x_item_rev po_requisition_lines_all.ITEM_REVISION%type;
2474 x_category_id po_requisition_lines_all.CATEGORY_ID%type;
2475 x_quantity po_requisition_lines_all.QUANTITY%type;
2476 x_uom po_requisition_lines_all.UNIT_MEAS_LOOKUP_CODE%type ;
2477 x_job_id po_requisition_lines_all.JOB_ID%type;
2478 x_req_line_id po_requisition_lines_all.requisition_line_id%TYPE;
2479 x_line_num po_requisition_lines_all.line_num%TYPE;
2480
2481 -- added for CLIN SLIN Project
2482 x_line_num_display po_requisition_lines_all.line_num_display%TYPE;
2483 x_group_line_id po_requisition_lines_all.group_line_id%TYPE;
2484 x_clm_info_flag po_requisition_lines_all.clm_info_flag%TYPE;
2485 x_clm_option_indicator po_requisition_lines_all.clm_option_indicator%TYPE;
2486 x_clm_option_num po_requisition_lines_all.clm_option_num%TYPE;
2487 x_clm_option_from_date po_requisition_lines_all.clm_option_from_date%TYPE;
2488 x_clm_option_to_date po_requisition_lines_all.clm_option_to_date%TYPE;
2489 x_clm_funded_flag po_requisition_lines_all.clm_funded_flag%TYPE;
2490 x_clm_base_line_num po_requisition_lines_all.clm_base_line_num%TYPE;
2491 x_CONTRACT_TYPE po_requisition_lines_all.CONTRACT_TYPE%TYPE;
2492 x_COST_CONSTRAINT po_requisition_lines_all.COST_CONSTRAINT%TYPE;
2493 x_CLM_IDC_TYPE po_lines_all.CLM_IDC_TYPE%TYPE;
2494 x_req_line_temp_id po_requisition_lines_all.UDA_TEMPLATE_ID%TYPE; --<Sol Project>
2495 x_req_in_pool_flag VARCHAR2(1) :=NULL;
2496 x_cancel_flag varchar2(1);
2497 x_closed_code varchar2(25);
2498 x_grouping_method varchar2(24);
2499
2500 --<Sol Project>
2501 l_line_uda_temp_id NUMBER;
2502 l_contract_type VARCHAR2(40);
2503 x_document_number pon_auction_headers_all.document_number%TYPE;
2504
2505 BEGIN
2506 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2507 fnd_log.string(fnd_log.level_statement,g_log_head,'Procedure add_line_to_negotiation1 Start ');
2508 fnd_log.string(fnd_log.level_statement,g_log_head,'p_auction_header_id : '||p_auction_header_id);
2509 fnd_log.string(fnd_log.level_statement,g_log_head,'p_req_line_id : '||p_req_line_id);
2510 END IF;
2511
2512 /* get the current org_id from auction header*/
2513 x_progress := '000';
2514 x_grouping_method := 'REQUISITION';
2515 select org_id into x_org_id from pon_auction_headers_all where auction_header_id = p_auction_header_id;
2516
2517 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2518 fnd_log.string(fnd_log.level_statement,g_log_head,'x_grouping_method : '||x_grouping_method);
2519 fnd_log.string(fnd_log.level_statement,g_log_head,'x_org_id : '||x_org_id);
2520 END IF;
2521
2522
2523 BEGIN
2524
2525 SAVEPOINT CHECK_LOCK;
2526
2527 --Get Uda Template Id and Contract Type from exisiting Auction
2528 select contract_type into l_contract_type from pon_auction_headers_all where auction_header_id = p_auction_header_id;
2529 l_line_uda_temp_id := NULL;
2530
2531 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2532 fnd_log.string(fnd_log.level_statement,g_log_head,'l_contract_type : '||l_contract_type);
2533 END IF;
2534
2535 begin
2536 select uda_template_id into l_line_uda_temp_id from pon_auction_item_prices_all
2537 where auction_header_id = p_auction_header_id and rownum <2;
2538
2539 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2540 fnd_log.string(fnd_log.level_statement,g_log_head,'l_line_uda_temp_id : '||l_line_uda_temp_id);
2541 END IF;
2542
2543 exception
2544 when no_data_found then
2545 l_line_uda_temp_id := po_uda_data_util.get_template_id (
2546 'SOURCING',
2547 'SOLICITATION',
2548 null,
2549 'LINE',
2550 sysdate,
2551 l_return_status,
2552 l_msg_data);
2553
2554 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2555 fnd_log.string(fnd_log.level_statement,g_log_head,'l_line_uda_temp_id1 : '||l_line_uda_temp_id);
2556 fnd_log.string(fnd_log.level_statement,g_log_head,'l_return_status : '||l_return_status);
2557 END IF;
2558
2559 IF ( l_return_status = 'E' ) THEN
2560 x_progress := '002';
2561 x_result := -1;
2562 x_error_code := api_error_code;
2563 x_error_message := l_msg_data;
2564 return ;
2565 END IF;
2566 end;
2567
2568
2569
2570 --Check if Req is in Pool
2571 x_req_in_pool_flag:=NULL;
2572
2573 BEGIN
2574
2575 SELECT Nvl(reqs_in_pool_flag,'Y'),cancel_flag,closed_code
2576 INTO x_req_in_pool_flag,
2577 x_cancel_flag,
2578 x_closed_code
2579 FROM po_requisition_lines WHERE requisition_line_id=p_req_line_id
2580 FOR UPDATE OF auction_header_id NOWAIT;
2581
2582 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2583 fnd_log.string(fnd_log.level_statement,g_log_head,'x_req_in_pool_flag : '||x_req_in_pool_flag);
2584 fnd_log.string(fnd_log.level_statement,g_log_head,'x_cancel_flag : '||x_cancel_flag);
2585 fnd_log.string(fnd_log.level_statement,g_log_head,'x_closed_code : '||x_closed_code);
2586 END IF;
2587
2588
2589 EXCEPTION
2590 WHEN NO_DATA_FOUND THEN
2591 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2592 fnd_log.string(fnd_log.level_statement,g_log_head,'Excep 1 : '||SQLERRM);
2593 END IF;
2594
2595 x_result:=-1;
2596 fnd_message.set_name('PO', 'PO_ALL_REQ_LINE_DLTD_CANT_AC');
2597 rollback to CHECK_LOCK;
2598 x_error_message := fnd_message.get;
2599 RETURN;
2600
2601 WHEN OTHERS THEN
2602 /* This is to see if req line is locked */
2603 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2604 fnd_log.string(fnd_log.level_statement,g_log_head,'Excep 2 : '||SQLERRM);
2605 END IF;
2606
2607 if (SQLCODE=-54)
2608 THEN
2609 x_result:=-1;
2610 fnd_message.set_name('PO', 'PO_ALL_RQ_LINE_LOCKED_CANT_AC');
2611 SELECT prh.segment1,prl.line_num INTO x_req_num,x_line_num FROM po_requisition_headers_all prh,po_requisition_lines_all prl
2612 WHERE prh.requisition_header_id = prl.requisition_header_id
2613 AND prl.requisition_line_id = p_req_line_id;
2614
2615 fnd_message.set_token('REQ_NUM', x_req_num);
2616 fnd_message.set_token('REQ_LINE_NUM',x_line_num);
2617
2618 x_error_message := fnd_message.get;
2619 rollback to CHECK_LOCK;
2620 RETURN;
2621 else
2622 x_result:=-1;
2623 raise;
2624 end if;
2625
2626 END;
2627
2628 if (x_req_in_pool_flag='N')
2629 then
2630 /* The req line has been auto created already. */
2631 x_result:=-1;
2632 fnd_message.set_name('PO', 'PO_ALL_RQ_LINE_ALREADY_AC');
2633 SELECT prh.segment1,prl.line_num INTO x_req_num,x_line_num FROM po_requisition_headers_all prh,po_requisition_lines_all prl
2634 WHERE prh.requisition_header_id = prl.requisition_header_id
2635 AND prl.requisition_line_id = p_req_line_id;
2636
2637 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2638 fnd_log.string(fnd_log.level_statement,g_log_head,'x_req_num : '||x_req_num);
2639 fnd_log.string(fnd_log.level_statement,g_log_head,'x_line_num : '||x_line_num);
2640 END IF;
2641
2642 fnd_message.set_token('REQ_NUM', x_req_num);
2643 fnd_message.set_token('REQ_LINE_NUM',x_line_num);
2644
2645
2646 x_error_message := fnd_message.get;
2647 ROLLBACK TO CHECK_LOCK;
2648 RETURN;
2649
2650 elsif (x_cancel_flag = 'Y') then
2651 /* The req line has been cancelled. */
2652 x_result:=-1;
2653 fnd_message.set_name('PO', 'PO_ALL_RQ_LINE_CNCLD_CANT_AC');
2654 SELECT prh.segment1,prl.line_num INTO x_req_num,x_line_num FROM po_requisition_headers_all prh,po_requisition_lines_all prl
2655 WHERE prh.requisition_header_id = prl.requisition_header_id
2656 AND prl.requisition_line_id = p_req_line_id;
2657
2658 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2659 fnd_log.string(fnd_log.level_statement,g_log_head,'x_req_num1 : '||x_req_num);
2660 fnd_log.string(fnd_log.level_statement,g_log_head,'x_line_num1 : '||x_line_num);
2661 END IF;
2662
2663 fnd_message.set_token('REQ_NUM', x_req_num);
2664 fnd_message.set_token('REQ_LINE_NUM',x_line_num);
2665
2666
2667 x_error_message := fnd_message.get;
2668 ROLLBACK TO CHECK_LOCK;
2669 RETURN;
2670
2671 elsif (x_closed_code = 'FINALLY CLOSED') then
2672 /* The req line has been auto created already. */
2673 x_result:=-1;
2674 fnd_message.set_name('PO', 'PO_ALL_RQ_LINE_FCLSD_CANT_AC');
2675 SELECT prh.segment1,prl.line_num INTO x_req_num,x_line_num FROM po_requisition_headers_all prh,po_requisition_lines_all prl
2676 WHERE prh.requisition_header_id = prl.requisition_header_id
2677 AND prl.requisition_line_id = p_req_line_id;
2678
2679 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2680 fnd_log.string(fnd_log.level_statement,g_log_head,'x_req_num2 : '||x_req_num);
2681 fnd_log.string(fnd_log.level_statement,g_log_head,'x_line_num2 : '||x_line_num);
2682 END IF;
2683
2684 fnd_message.set_token('REQ_NUM', x_req_num);
2685 fnd_message.set_token('REQ_LINE_NUM',x_line_num);
2686
2687
2688 x_error_message := fnd_message.get;
2689 ROLLBACK TO CHECK_LOCK;
2690 RETURN;
2691
2692 end if;
2693
2694 END;
2695
2696 x_progress := '003';
2697 DELETE FROM po_session_gt WHERE index_char1='PO_NEGOTIATIONS_SV1';
2698
2699
2700 INSERT INTO po_session_gt (KEY, index_char1, char1, num1, num2, num3, date1)
2701 SELECT prl.requisition_line_id,
2702 'PO_NEGOTIATIONS_SV1',
2703 prh.segment1,
2704 prl.line_num,
2705 prl.item_id,
2706 prl.category_id,
2707 decode (prl.purchase_basis , 'TEMP LABOR' , prl.assignment_start_date , prl.need_by_date )
2708 FROM po_requisition_lines_all prl,
2709 po_requisition_headers_all prh
2710 WHERE prh.requisition_header_id = prl.requisition_header_id
2711 AND prl.requisition_line_id = p_req_line_id;
2712
2713
2714
2715 l_string := ' select rl.requisition_line_id,
2716 Decode(Nvl(rl.clm_info_flag,''N''),''Y'',NULL,rl.LINE_TYPE_ID), -- line_type_id
2717 rl.ITEM_ID, -- item_id
2718 rl.ITEM_REVISION, -- item_revision
2719 rl.CATEGORY_ID, -- category id
2720 rl.QUANTITY, -- quantity
2721 rl.UNIT_MEAS_LOOKUP_CODE, -- unit meas lookup code
2722 rl.JOB_ID, -- job id
2723 rl.requisition_header_id,
2724 psg.char1,
2725 Decode(Nvl(rl.clm_info_flag,''N''),''Y'',NULL,RL.UNIT_MEAS_LOOKUP_CODE), -- <SERVICES FPJ>
2726 rl.note_to_vendor,
2727 rl.need_by_date,
2728 RL.amount, -- <SERVICES FPJ>
2729 msi.concatenated_segments,
2730 rl.deliver_to_location_id,
2731 rl.unit_price,
2732 rl.item_description,
2733 PJ.name, -- <SERVICES FPJ>
2734 RL.job_long_description, -- <SERVICES FPJ>
2735 RL.org_id, -- <HTMLAC>
2736
2737 -- added for CLIN SLIN Project
2738 rl.LINE_NUM_DISPLAY,
2739 rl.GROUP_LINE_ID,
2740 rl.CLM_INFO_FLAG,
2741 rl.CLM_OPTION_INDICATOR,
2742 rl.CLM_OPTION_NUM,
2743 rl.CLM_OPTION_FROM_DATE,
2744 rl.CLM_OPTION_TO_DATE,
2745 rl.CLM_FUNDED_FLAG,
2746 rl.CLM_BASE_LINE_NUM,
2747 rl.CONTRACT_TYPE,
2748 rl.COST_CONSTRAINT,
2749 decode(rl.CONTRACT_TYPE, null, null, ''IDC_NA''),
2750 rl.UDA_TEMPLATE_ID --<Sol Project>
2751 from po_requisition_lines_all rl, -- <HTMLAC>
2752 mtl_system_items_kfv msi,
2753 per_jobs_vl PJ,
2754 PO_SESSION_GT psg -- <SERVICES FPJ>
2755 where requisition_line_id = psg.key -- <SERVICES FPJ>
2756 and rl.item_id = msi.inventory_item_id(+)
2757 and coalesce(msi.organization_id, rl.destination_organization_id,-1) =
2758 nvl(rl.destination_organization_id,-1)
2759 AND RL.job_id = PJ.job_id(+) -- <SERVICES FPJ>
2760 AND psg.index_char1 = ''PO_NEGOTIATIONS_SV1''' ;
2761
2762 -- logme('create_negotiation before cursor' || x_progress);
2763
2764 OPEN l_cursor FOR l_string;
2765
2766 -- logme('cursor returned ' || l_cursor%ROWCOUNT || ' rows');
2767
2768 LOOP
2769 -- logme('start of loop');
2770 FETCH l_cursor INTO x_req_line_id ,
2771 x_line_type ,
2772 x_item_id ,
2773 x_item_rev ,
2774 x_category_id ,
2775 x_quantity ,
2776 x_uom ,
2777 x_job_id ,
2778 x_req_header_id ,
2779 x_req_num ,
2780 l_value_basis,
2781 x_note_to_vendor,
2782 x_need_by_date,
2783 l_amount,
2784 x_item_num,
2785 x_deliver_to_location_id,
2786 x_unit_price,
2787 x_item_desc,
2788 l_job_name,
2789 l_job_long_description,
2790 l_from_org_id,
2791 x_line_num_display,
2792 x_group_line_id,
2793 x_clm_info_flag,
2794 x_clm_option_indicator,
2795 x_clm_option_num,
2796 x_clm_option_from_date,
2797 x_clm_option_to_date,
2798 x_clm_funded_flag,
2799 x_clm_base_line_num,
2800 x_CONTRACT_TYPE,
2801 x_COST_CONSTRAINT,
2802 x_CLM_IDC_TYPE,
2803 x_req_line_temp_id; --<Sol Project>
2804
2805 IF l_cursor%NOTFOUND THEN
2806 EXIT;
2807 END IF;
2808
2809 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2810 fnd_log.string(fnd_log.level_statement,g_log_head,'x_req_line_id1 : '||x_req_line_id);
2811 fnd_log.string(fnd_log.level_statement,g_log_head,'x_line_type1 : '||x_line_type);
2812 fnd_log.string(fnd_log.level_statement,g_log_head,'x_item_id1 : '||x_item_id);
2813 fnd_log.string(fnd_log.level_statement,g_log_head,'x_item_rev1 : '||x_item_rev);
2814 fnd_log.string(fnd_log.level_statement,g_log_head,'x_category_id1 : '||x_category_id);
2815 fnd_log.string(fnd_log.level_statement,g_log_head,'x_quantity1 : '||x_quantity);
2816 fnd_log.string(fnd_log.level_statement,g_log_head,'x_uom1 : '||x_uom);
2817 fnd_log.string(fnd_log.level_statement,g_log_head,'x_job_id1 : '||x_job_id);
2818 fnd_log.string(fnd_log.level_statement,g_log_head,'x_req_header_id1 : '||x_req_header_id);
2819 fnd_log.string(fnd_log.level_statement,g_log_head,'x_req_num1 : '||x_req_num);
2820 fnd_log.string(fnd_log.level_statement,g_log_head,'l_value_basis1 : '||l_value_basis);
2821 fnd_log.string(fnd_log.level_statement,g_log_head,'x_note_to_vendor1 : '||x_note_to_vendor);
2822 fnd_log.string(fnd_log.level_statement,g_log_head,'x_need_by_date1 : '||x_need_by_date);
2823 fnd_log.string(fnd_log.level_statement,g_log_head,'l_amount1 : '||l_amount);
2824 fnd_log.string(fnd_log.level_statement,g_log_head,'x_item_num1 : '||x_item_num);
2825 fnd_log.string(fnd_log.level_statement,g_log_head,'x_deliver_to_location_id1 : '||x_deliver_to_location_id);
2826 fnd_log.string(fnd_log.level_statement,g_log_head,'x_unit_price1 : '||x_unit_price);
2827 fnd_log.string(fnd_log.level_statement,g_log_head,'x_item_desc1 : '||x_item_desc);
2828 fnd_log.string(fnd_log.level_statement,g_log_head,'l_job_name1 : '||l_job_name);
2829 fnd_log.string(fnd_log.level_statement,g_log_head,'l_job_long_description1 : '||l_job_long_description);
2830 fnd_log.string(fnd_log.level_statement,g_log_head,'l_from_org_id1 : '||l_from_org_id);
2831 fnd_log.string(fnd_log.level_statement,g_log_head,'x_line_num_display1 : '||x_line_num_display);
2832 fnd_log.string(fnd_log.level_statement,g_log_head,'x_group_line_id1 : '||x_group_line_id);
2833 fnd_log.string(fnd_log.level_statement,g_log_head,'x_clm_info_flag1 : '||x_clm_info_flag);
2834 fnd_log.string(fnd_log.level_statement,g_log_head,'x_clm_option_indicator1 : '||x_clm_option_indicator);
2835 fnd_log.string(fnd_log.level_statement,g_log_head,'x_clm_option_num1 : '||x_clm_option_num);
2836 fnd_log.string(fnd_log.level_statement,g_log_head,'x_clm_option_from_date1 : '||x_clm_option_from_date);
2837 fnd_log.string(fnd_log.level_statement,g_log_head,'x_clm_option_to_date1 : '||x_clm_option_to_date);
2838 fnd_log.string(fnd_log.level_statement,g_log_head,'x_clm_funded_flag1 : '||x_clm_funded_flag);
2839 fnd_log.string(fnd_log.level_statement,g_log_head,'x_clm_base_line_num1 : '||x_clm_base_line_num);
2840 fnd_log.string(fnd_log.level_statement,g_log_head,'x_CONTRACT_TYPE1 : '||x_CONTRACT_TYPE);
2841 fnd_log.string(fnd_log.level_statement,g_log_head,'x_COST_CONSTRAINT1 : '||x_COST_CONSTRAINT);
2842 fnd_log.string(fnd_log.level_statement,g_log_head,'x_CLM_IDC_TYPE1 : '||x_CLM_IDC_TYPE);
2843 fnd_log.string(fnd_log.level_statement,g_log_head,'x_req_line_temp_id1 : '||x_req_line_temp_id);
2844 END IF;
2845
2846 --get to ship_to_location_id
2847 BEGIN
2848 x_progress := '004';
2849 SELECT nvl(ship_to_location_id,location_id)
2850 INTO x_ship_to_location_id
2851 FROM hr_locations
2852 WHERE location_id = x_deliver_to_location_id;
2853
2854 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2855 fnd_log.string(fnd_log.level_statement,g_log_head,'x_ship_to_location_id : '||x_ship_to_location_id);
2856 END IF;
2857
2858 EXCEPTION
2859 WHEN NO_DATA_FOUND THEN
2860 x_ship_to_location_id := x_deliver_to_location_id;
2861 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2862 fnd_log.string(fnd_log.level_statement,g_log_head,'Excep x_ship_to_location_id : '||x_ship_to_location_id);
2863 END IF;
2864 END;
2865
2866 /* Sourcing expects the uom_code whereas req lines stores unit_of_measure .
2867 need to get the correct value */
2868 begin
2869 select mum.uom_code
2870 into x_uom_code
2871 from mtl_units_of_measure mum
2872 where mum.unit_of_measure = x_uom; --bug 5841426
2873
2874 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2875 fnd_log.string(fnd_log.level_statement,g_log_head,'x_uom_code : '||x_uom_code);
2876 END IF;
2877
2878 exception
2879 when others then
2880 x_uom_code := null;
2881 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2882 fnd_log.string(fnd_log.level_statement,g_log_head,'Excep x_uom_code : '||x_uom_code);
2883 END IF;
2884 end;
2885
2886 -- <SERVICES FPJ START>
2887
2888 -- Determine if this Requisition Line has Price Differentials.
2889 --
2890 IF ( PO_PRICE_DIFFERENTIALS_PVT.has_price_differentials
2891 ( p_entity_type => 'REQ LINE'
2892 , p_entity_id => x_req_line_id) --Bug 5841426
2893 )
2894 THEN
2895 l_has_price_diff_flag := 'Y';
2896 ELSE
2897 l_has_price_diff_flag := 'N';
2898 END IF;
2899
2900 l_conv_rate := PO_CURRENCY_SV.get_cross_ou_rate(
2901 l_from_org_id,
2902 x_org_id);
2903 IF ( l_value_basis = 'FIXED PRICE' )
2904 THEN
2905 l_bid_start_price := l_amount * l_conv_rate; --<HTMLAC>
2906 l_po_agreed_amount := NULL;
2907 ELSE
2908 l_bid_start_price := x_unit_price * l_conv_rate; --<HTMLAC>
2909 l_po_agreed_amount := l_amount * l_conv_rate; --<HTMLAC>
2910 END IF;
2911
2912 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2913 fnd_log.string(fnd_log.level_statement,g_log_head,'l_has_price_diff_flag : '||l_has_price_diff_flag);
2914 fnd_log.string(fnd_log.level_statement,g_log_head,'l_conv_rate : '||l_conv_rate);
2915 fnd_log.string(fnd_log.level_statement,g_log_head,'l_value_basis : '||l_value_basis);
2916 fnd_log.string(fnd_log.level_statement,g_log_head,'l_bid_start_price : '||l_bid_start_price);
2917 fnd_log.string(fnd_log.level_statement,g_log_head,'l_po_agreed_amount : '||l_po_agreed_amount);
2918 END IF;
2919
2920 -- <SERVICES FPJ END>
2921 x_progress := '007';
2922
2923 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2924 fnd_log.string(fnd_log.level_statement,g_log_head,'Before PON_AUCTION_INTERFACE_PKG.add_negotiation_line');
2925 END IF;
2926
2927 PON_AUCTION_INTERFACE_PKG.add_negotiation_line
2928 ( p_document_number => p_auction_header_id
2929 , p_contract_type => l_contract_type
2930 , p_origination_code => 'REQUISITION'
2931 , p_org_id => x_org_id
2932 , p_buyer_id => to_number(FND_PROFILE.VALUE('user_id'))
2933 , p_grouping_type => x_grouping_method
2934 , p_requisition_header_id => x_req_header_id
2935 , p_requisition_number => x_req_num
2936 , p_requisition_line_id => x_req_line_id
2937 , p_line_type_id => x_line_type --Bug5841426
2938 , p_category_id => x_category_id
2939 , p_item_description => x_item_desc
2940 , p_item_id => x_item_id --Bug5841426
2941 , p_item_number => x_item_num --Bug5841426
2942 , p_item_revision => x_item_rev
2943
2944 , p_uom_code => x_uom_code
2945 , p_quantity => x_quantity
2946
2947 , p_need_by_date => x_need_by_date
2948 , p_ship_to_location_id => x_ship_to_location_id
2949 , p_note_to_vendor => x_note_to_vendor
2950 , p_price => l_bid_start_price -- <SERVICES FPJ>
2951 , p_job_id => x_job_id-- <SERVICES FPJ> --Bug5841426
2952
2953 , p_job_details => l_job_long_description-- <SERVICES FPJ>
2954 , p_po_agreed_amount => l_po_agreed_amount -- <SERVICES FPJ>
2955 , p_has_price_diff_flag => l_has_price_diff_flag -- <SERVICES FPJ>
2956 -- fields added for CLIN SLIN Project
2957 , p_line_num_display => x_line_num_display
2958 , p_group_line_id => x_group_line_id
2959 , p_clm_info_flag => x_clm_info_flag
2960 , p_clm_option_indicator => x_clm_option_indicator
2961 , p_clm_option_num => x_clm_option_num
2962 , p_clm_option_from_date => x_clm_option_from_date
2963 , p_clm_option_to_date => x_clm_option_to_date
2964 , p_clm_funded_flag => x_clm_funded_flag
2965 , p_clm_base_line_num => x_clm_base_line_num
2966 , p_clm_contract_type => x_CONTRACT_TYPE
2967 , p_clm_cost_constraint => x_COST_CONSTRAINT
2968 , p_clm_idc_type => x_CLM_IDC_TYPE
2969 , p_uda_template_id => l_line_uda_temp_id --<Sol Project>
2970 , p_req_line_temp_id => x_req_line_temp_id --<Sol Project>
2971 , p_line_number => x_negotiation_line_num
2972 , p_result => api_result
2973 , p_error_code => api_error_code
2974 , p_error_message => api_error_msg
2975 );
2976
2977 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2978 fnd_log.string(fnd_log.level_statement,g_log_head,'After PON_AUCTION_INTERFACE_PKG.add_negotiation_line');
2979 END IF;
2980
2981 -- logme(' Add Line for item ' || x_item_desc || ' api_result = ' || api_result || ' error code = ' || api_error_code || ' error_msg = ' || api_error_msg );
2982 -- logme ( ' The above line autocreated to ' || x_negotiation_line_num);
2983 If api_result = 0 then
2984
2985 begin
2986
2987 x_progress := '008';
2988 -- logme( '008 : x_negotiation_id = '|| x_negotiation_id || ' to_char(x_negotiation_id) = ' || to_char(x_negotiation_id)
2989 -- || ' x_negotiation_line_num = '|| x_negotiation_line_num || ' sysdate = '|| SYSDATE ||
2990 -- ' to_number(FND_PROFILE.VALUE(user_id)) = ' || to_number(FND_PROFILE.VALUE('user_id'))
2991 -- || ' x_req_line_id = ' || x_req_line_id);
2992 update po_requisition_lines_all --<HTMLAC>
2993 set on_rfq_flag = 'Y',
2994 auction_header_id = p_auction_header_id,
2995 auction_display_number = to_char(p_auction_header_id) ,
2996 auction_line_number = x_negotiation_line_num,
2997 at_sourcing_flag = 'Y', -- <REQINPOOL>
2998 reqs_in_pool_flag = NULL, -- <REQINPOOL>
2999 last_update_date = sysdate,
3000 last_updated_by = to_number(FND_PROFILE.VALUE('user_id')),
3001 last_update_login = to_number(FND_PROFILE.VALUE('user_id'))
3002 where requisition_line_id = x_req_line_id ;
3003
3004 -- bug# 9887326
3005 IF ( PON_CLM_UTIL_PKG.IS_NEG_DOCUMENT_FEDERAL(p_auction_header_id) = 1 ) THEN
3006 SELECT document_number INTO x_document_number FROM pon_auction_headers_all WHERE auction_header_id=p_auction_header_id;
3007 update po_requisition_lines_all
3008 SET auction_display_number = x_document_number
3009 where requisition_line_id = x_req_line_id ;
3010 END IF;
3011 exception
3012 when others then
3013 po_message_s.sql_error('In Exception of create_negotiation()', x_progress, sqlcode);
3014 end;
3015
3016 IF ( l_has_price_diff_flag = 'Y' ) THEN
3017 -- logme( ' in price differentials');
3018 FOR l_price_diff_rec IN l_price_diff_csr(x_req_line_id) LOOP
3019
3020 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3021 fnd_log.string(fnd_log.level_statement,g_log_head,'Before PON_AUCTION_INTERFACE_PKG.add_price_differential');
3022 END IF;
3023
3024 PON_AUCTION_INTERFACE_PKG.add_price_differential
3025 ( p_document_number => p_auction_header_id
3026 , p_line_number => x_negotiation_line_num
3027 , p_shipment_number => -1
3028 , p_price_type => l_price_diff_rec.price_type
3029 , p_multiplier => l_price_diff_rec.min_multiplier
3030 , p_buyer_id => FND_PROFILE.value('user_id')
3031 , p_price_differential_number => l_price_diff_rec.price_differential_num
3032 , p_result => api_result
3033 , p_error_code => api_error_code
3034 , p_error_message => api_error_msg
3035 );
3036
3037 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3038 fnd_log.string(fnd_log.level_statement,g_log_head,'After PON_AUCTION_INTERFACE_PKG.add_price_differential');
3039 END IF;
3040
3041
3042 IF ( api_result <> 0 ) -- API failure
3043 THEN
3044 x_result := api_result;
3045 x_error_code := api_error_code;
3046 x_error_message := api_error_msg;
3047 rollback;
3048 return;
3049 END IF;
3050
3051 END LOOP;
3052 -- logme( ' after price differentials');
3053
3054 END IF;
3055 --
3056 -- <SERVICES FPJ END>
3057
3058 x_result := api_result;
3059 x_error_code := api_error_code;
3060 x_error_message := api_error_msg;
3061
3062 else
3063 /* If unsuccessful return the error code to the form. */
3064 x_progress := '009';
3065 x_result := api_result;
3066 x_error_code := api_error_code;
3067 x_error_message := api_error_msg;
3068
3069 /* before returning we rollback and return */
3070 rollback;
3071 return ;
3072 end if;
3073 -- logme('end of loop');
3074 END LOOP; --loop for the cursor will loop only once
3075 -- logme('after cursor loop');
3076
3077
3078 -- After the lines have been autocreated. Set the CLM clin slin and option relations appropriately.
3079 -- Currently the reqline ids have been copied over as it is.
3080 -- Now map the correct reqlineids with the neg line numbers and store
3081 -- in the negotiation lines table.
3082 x_progress := '010';
3083
3084 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3085 fnd_log.string(fnd_log.level_statement,g_log_head,'Before PON_AUCTION_INTERFACE_PKG.maintain_clm_relations');
3086 END IF;
3087
3088 PON_AUCTION_INTERFACE_PKG.maintain_clm_relations(
3089 p_api_version => 1.0 -- IN
3090 , x_return_status => l_return_status -- OUT
3091 , x_msg_count => l_msg_count -- OUT
3092 , x_msg_data => l_msg_data -- OUT
3093 , p_document_number => p_auction_header_id -- IN
3094 , p_line_number => x_negotiation_line_num
3095 , p_req_line_clin => x_group_line_id
3096 , p_req_line_opt => x_clm_base_line_num
3097 );
3098
3099 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3100 fnd_log.string(fnd_log.level_statement,g_log_head,'After PON_AUCTION_INTERFACE_PKG.maintain_clm_relations');
3101 fnd_log.string(fnd_log.level_statement,g_log_head,'l_return_status : '||l_return_status);
3102 END IF;
3103
3104 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3105
3106 x_result := -1;
3107 x_error_message := l_msg_data;
3108
3109 -- before returning we rollback and return
3110 rollback;
3111 return ;
3112 end if;
3113
3114 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3115 fnd_log.string(fnd_log.level_statement,g_log_head,'Before PON_CLO_RENUMBER_PKG.RenumberSlin');
3116 END IF;
3117
3118 -- Renumber the lines in the document.
3119 PON_CLO_RENUMBER_PKG.RenumberSlin(
3120 p_auction_header_id => p_auction_header_id
3121 ,p_line_number => x_negotiation_line_num
3122 , x_status => l_return_status
3123 );
3124
3125 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3126 fnd_log.string(fnd_log.level_statement,g_log_head,'After PON_CLO_RENUMBER_PKG.RenumberSlin');
3127 END IF;
3128
3129 x_progress := '020';
3130
3131 /* issue commit */
3132 commit;
3133 EXCEPTION
3134 WHEN OTHERS THEN
3135 rollback;
3136
3137 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3138 fnd_log.string(fnd_log.level_statement,g_log_head,'Exception : '||SQLERRM);
3139 END IF;
3140
3141 x_result := -1;
3142
3143 IF (x_error_message IS NULL) THEN
3144 x_error_message := 'In Exception of create_negotiation:' || x_progress || ':' || fnd_message.get;
3145 END IF;
3146 po_message_s.sql_error('In Exception of create_negotiation()', x_progress, sqlcode);
3147 -- logme( 'In Exception of create_negotiation() - progress is ' || x_progress || ' sqlcode is ' || SQLCODE);
3148 END add_line_to_negotiation;
3149
3150 /* Bug 9881440 - Start */
3151 /* Added the procedure COPY_REQ_UDA_TO_SOL to copy UDA data from
3152 Requisition to Solicitation. This API copies the following UDA
3153 data from Requisition to Solicitation.
3154 Requesting Office => PR -> SOL
3155 Suggested COTR Office => PR -> SOL
3156 Suggested Property Administration Office => PR -> SOL
3157 Issuing Office => User Preferences / PR -> SOL
3158 Business Priority and Project Information => PR -> SOL
3159 */
3160
3161 /* FOLLOWING LOGIC IS USED TO COPY THE UDA DATA FROM REQ to SOLICITATION.
3162
3163 STEP 1) Take Targer Document Id (auction_header_id) and Source Line Id (requisition_line_id) and
3164 derive Source Document Id (requisition_header_id), Source & Target UDA Template Ids.
3165 STEP 2) If any of the values in auction_header_id, requisition_header_id, solicitation_uda_template_id,
3166 and requisition_uda_template_id are null, exit from the procedure.
3167 STEP 3) If all the above values are present, then call PON_UDA_PKG.DEFAULT_NON_RENDER_UDA procedure
3168 to copy addresses from user preferences. This procedure copies Requesting Office,
3169 Suggested COTR Office, Suggested Property Administration Office, and Issuing Office from User Preferences.
3170 STEP 4) Delete Requesting Office, Suggested COTR Office, and Suggested Property Administration Office addresses
3171 from UDA tables (PON_AUCTION_HEADERS_EXT_B, PON_AUCTION_HEADERS_EXT_TL) because these addresses should
3172 come from Requisition.
3173 STEP 5) If the Issuing Office copied from User Preferences has null data then delete that record, else take the
3174 backup of the record by updating it to ISSUING_OFFICE_BACKUP.
3175 STEP 6) Call PON_COPY_UDAS_GRP.COPY_REQ_HEADER_UDA procedure to copy addresses (Requesting Office,
3176 Suggested COTR Office, Suggested Property Administration Office, and Issuing Office) and
3177 Business Priority and Project Information UDA from Requisition to Solicitation.
3178 STEP 7) After Step 5, if the data copied for Issuing Office is valid, then delete the records related to Issuing
3179 Office which were copied in STEP 6 i.e., Issuing Office Copied from Requisition to Solicitaion. Also update
3180 the record modified in STEP 5 back to ISSUING_OFFICE.
3181 STEP 8) After Step 5, if the data copied for Issuing Office is not valid, then don't perform any logic
3182 i.e., Issuing Office Copied from Requisition to Solicitaion should be kept as is.
3183 */
3184 PROCEDURE COPY_REQ_UDA_TO_SOL(p_req_lines IN PO_TBL_NUMBER,
3185 p_auction_header_id IN NUMBER)
3186 IS
3187 l_req_line_id NUMBER;
3188 l_req_header_id NUMBER;
3189 l_sol_uda_temp_id NUMBER;
3190 l_req_uda_temp_id NUMBER;
3191 l_return_status VARCHAR2(1);
3192 l_extn_id NUMBER;
3193 l_iss_off_extn_id NUMBER;
3194 l_user_pref_addr_cnt NUMBER;
3195 l_address_val VARCHAR2(4000);
3196 l_msg_count NUMBER;
3197 l_msg_data VARCHAR2(2000);
3198
3199 CURSOR l_extn_id_csr ( auc_hdr_id NUMBER ) IS
3200 SELECT EXTENSION_ID
3201 FROM PON_AUCTION_HEADERS_EXT_B
3202 WHERE C_EXT_ATTR1 IN ('PRO_ADMIN_OFFICE','COTR_OFFICE','REQ_OFFICE') AND
3203 AUCTION_HEADER_ID = auc_hdr_id;
3204 BEGIN
3205
3206 /* To Understand Steps from 1 to 8, Please look at the comments given above the procedure.
3207 Logic for each step is explained there. */
3208
3209 /* STEP 1 - START */
3210
3211 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3212 fnd_log.string(fnd_log.level_statement,g_log_head,'Entering COPY_REQ_UDA_TO_SOL Procudure ');
3213 fnd_log.string(fnd_log.level_statement,g_log_head,'p_auction_header_id : '||p_auction_header_id);
3214 END IF;
3215
3216 for i in 1..1
3217 loop
3218 l_req_line_id := p_req_lines(i);
3219 END LOOP;
3220
3221 BEGIN
3222 SELECT requisition_header_id INTO l_req_header_id FROM po_requisition_lines_all WHERE requisition_line_id = l_req_line_id;
3223 SELECT uda_template_id INTO l_sol_uda_temp_id FROM pon_auction_headers_all WHERE auction_header_id = p_auction_header_id;
3224 SELECT uda_template_id INTO l_req_uda_temp_id FROM po_requisition_headers_all WHERE REQUISITION_HEADER_ID= l_req_header_id;
3225
3226 EXCEPTION WHEN OTHERS THEN
3227 l_req_header_id := NULL;
3228 l_sol_uda_temp_id := NULL;
3229 l_req_uda_temp_id := NULL;
3230 END;
3231
3232 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3233 fnd_log.string(fnd_log.level_statement,g_log_head,'l_req_line_id : '||l_req_line_id);
3234 fnd_log.string(fnd_log.level_statement,g_log_head,'l_req_header_id : '||l_req_header_id);
3235 fnd_log.string(fnd_log.level_statement,g_log_head,'l_sol_uda_temp_id : '||l_sol_uda_temp_id);
3236 fnd_log.string(fnd_log.level_statement,g_log_head,'l_req_uda_temp_id : '||l_req_uda_temp_id);
3237 END IF;
3238
3239 /* STEP 1 - END */
3240
3241 /* STEP 2 - START */
3242
3243 IF p_auction_header_id IS NOT NULL AND l_req_header_id IS NOT NULL AND
3244 l_sol_uda_temp_id IS NOT NULL AND l_req_uda_temp_id IS NOT NULL THEN
3245
3246 /* STEP 3 - START */
3247
3248 PON_UDA_PKG.DEFAULT_NON_RENDER_UDA( p_auction_header_id => p_auction_header_id,
3249 x_return_status => l_return_status);
3250 /* STEP 3 - END */
3251
3252 /* STEP 4 - START */
3253
3254 BEGIN
3255 OPEN l_extn_id_csr(p_auction_header_id);
3256 LOOP
3257 FETCH l_extn_id_csr INTO l_extn_id;
3258 EXIT WHEN l_extn_id_csr%NOTFOUND;
3259
3260 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3261 fnd_log.string(fnd_log.level_statement,g_log_head,'l_extn_id : '||l_extn_id);
3262 END IF;
3263
3264 DELETE FROM PON_AUCTION_HEADERS_EXT_TL WHERE EXTENSION_ID = l_extn_id;
3265
3266 END LOOP;
3267 CLOSE l_extn_id_csr;
3268
3269 DELETE FROM PON_AUCTION_HEADERS_EXT_B
3270 WHERE C_EXT_ATTR1 IN ('PRO_ADMIN_OFFICE','COTR_OFFICE','REQ_OFFICE') AND
3271 AUCTION_HEADER_ID = p_auction_header_id;
3272
3273 EXCEPTION WHEN OTHERS THEN
3274 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3275 fnd_log.string(fnd_log.level_statement,g_log_head,'Exception 1 : '||SQLERRM);
3276 END IF;
3277 END;
3278
3279 /* STEP 4 - END */
3280
3281 /* STEP 5 - START */
3282
3283 SELECT Count(*) INTO l_user_pref_addr_cnt FROM PON_AUCTION_HEADERS_EXT_B
3284 WHERE C_EXT_ATTR1 = 'ISSUING_OFFICE' AND AUCTION_HEADER_ID = p_auction_header_id;
3285
3286 IF l_user_pref_addr_cnt > 0 THEN
3287 SELECT EXTENSION_ID, C_EXT_ATTR5 INTO l_iss_off_extn_id, l_address_val FROM PON_AUCTION_HEADERS_EXT_B
3288 WHERE C_EXT_ATTR1 = 'ISSUING_OFFICE' AND AUCTION_HEADER_ID = p_auction_header_id;
3289
3290 IF l_address_val IS NOT NULL THEN
3291 UPDATE PON_AUCTION_HEADERS_EXT_B SET C_EXT_ATTR1='ISSUING_OFFICE_BACKUP' WHERE EXTENSION_ID = l_iss_off_extn_id;
3292
3293 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3294 fnd_log.string(fnd_log.level_statement,g_log_head,'Taking backup of Issuing Office Record');
3295 END IF;
3296 ELSE
3297 BEGIN
3298 SELECT EXTENSION_ID INTO l_extn_id FROM PON_AUCTION_HEADERS_EXT_B
3299 WHERE C_EXT_ATTR1 = 'ISSUING_OFFICE' AND AUCTION_HEADER_ID = p_auction_header_id;
3300
3301 DELETE FROM PON_AUCTION_HEADERS_EXT_B WHERE C_EXT_ATTR1 = 'ISSUING_OFFICE' AND AUCTION_HEADER_ID = p_auction_header_id;
3302
3303 DELETE FROM PON_AUCTION_HEADERS_EXT_TL WHERE EXTENSION_ID = l_extn_id;
3304 EXCEPTION WHEN OTHERS THEN
3305 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3306 fnd_log.string(fnd_log.level_statement,g_log_head,'Exception 4 : '||SQLERRM);
3307 END IF;
3308 END;
3309 l_user_pref_addr_cnt := 0;
3310 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3311 fnd_log.string(fnd_log.level_statement,g_log_head,'Deleted issuing office record copied from user preferences.');
3312 END IF;
3313 END IF;
3314 END IF;
3315
3316 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3317 fnd_log.string(fnd_log.level_statement,g_log_head,'l_user_pref_addr_cnt : '||l_user_pref_addr_cnt);
3318 fnd_log.string(fnd_log.level_statement,g_log_head,'l_iss_off_extn_id : '||l_iss_off_extn_id);
3319 END IF;
3320
3321 /* STEP 5 - END */
3322
3323 /* STEP 6 - START */
3324
3325 PON_COPY_UDAS_GRP.COPY_REQ_HEADER_UDA(p_auction_header_id => p_auction_header_id,
3326 p_req_header_id => l_req_header_id,
3327 p_src_template_id => l_req_uda_temp_id,
3328 p_dest_template_id => l_sol_uda_temp_id,
3329 x_return_status => l_return_status,
3330 x_msg_count => l_msg_count,
3331 x_msg_data => l_msg_data);
3332
3333 /* STEP 6 - END */
3334
3335 /* STEP 7,8 - START */
3336
3337 IF l_user_pref_addr_cnt > 0 THEN
3338 BEGIN
3339 SELECT EXTENSION_ID INTO l_extn_id FROM PON_AUCTION_HEADERS_EXT_B
3340 WHERE C_EXT_ATTR1 = 'ISSUING_OFFICE' AND AUCTION_HEADER_ID = p_auction_header_id;
3341
3342 DELETE FROM PON_AUCTION_HEADERS_EXT_B WHERE C_EXT_ATTR1 = 'ISSUING_OFFICE' AND AUCTION_HEADER_ID = p_auction_header_id;
3343
3344 DELETE FROM PON_AUCTION_HEADERS_EXT_TL WHERE EXTENSION_ID = l_extn_id;
3345 EXCEPTION WHEN OTHERS THEN
3346 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3347 fnd_log.string(fnd_log.level_statement,g_log_head,'Exception 5 : '||SQLERRM);
3348 END IF;
3349 END;
3350
3351 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3352 fnd_log.string(fnd_log.level_statement,g_log_head,'l_extn_id 4 : '||l_extn_id);
3353 fnd_log.string(fnd_log.level_statement,g_log_head,'l_iss_off_extn_id 2 : '||l_iss_off_extn_id);
3354 END IF;
3355
3356 UPDATE PON_AUCTION_HEADERS_EXT_B SET C_EXT_ATTR1='ISSUING_OFFICE' WHERE EXTENSION_ID = l_iss_off_extn_id;
3357 END IF;
3358
3359 /* STEP 7,8 - END */
3360
3361 END IF;
3362
3363 /* STEP 2 - END */
3364
3365 EXCEPTION WHEN OTHERS THEN
3366 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
3367 fnd_log.string(fnd_log.level_statement,g_log_head,'Exception 6 : '||SQLERRM);
3368 END IF;
3369 END COPY_REQ_UDA_TO_SOL;
3370 /* Bug 9881440 - End */
3371
3372
3373 --the procedure to remove the requisitions from the req pool
3374 PROCEDURE REMOVE_FROM_REQ_POOL(p_line_type IN VARCHAR2,
3375 p_auction_header_id IN NUMBER,
3376 p_requisition_line_id IN PO_TBL_NUMBER,
3377 x_return_status OUT NOCOPY VARCHAR2,
3378 x_error_msg OUT NOCOPY VARCHAR2) IS
3379
3380 l_progress VARCHAR2(10);
3381 l_requisition_header_id NUMBER;
3382
3383 BEGIN
3384 x_return_status := FND_API.G_RET_STS_SUCCESS;
3385
3386 l_progress := '0010';
3387 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3388 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Begin proc: remove_from_req_pool');
3389 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||'auction_header_id:' || p_auction_header_id );
3390 END IF;
3391
3392 --looping thru the req line_id array and updating the auction references for teh req lines being added.
3393 FOR i in 1..p_requisition_line_id.COUNT LOOP
3394
3395 IF p_line_type = 'SOLICITATION' THEN --solicited line
3396
3397 UPDATE po_requisition_lines_all
3398 SET auction_header_id = p_auction_header_id,
3399 auction_line_number = -1,
3400 auction_display_number = -1,
3401 last_update_date = SYSDATE,
3402 last_updated_by = FND_GLOBAL.USER_ID,
3403 last_update_login = FND_GLOBAL.LOGIN_ID
3404 WHERE requisition_line_id = p_requisition_line_id(i)
3405 AND auction_header_id IS NULL
3406 returning requisition_header_id INTO l_requisition_header_id;
3407
3408 l_progress := '0020';
3409 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3410 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' ReqLineId: '|| p_requisition_line_id(i) || ' Updated no. of rows: '|| sql%rowcount);
3411 END IF;
3412
3413 ELSIF p_line_type = 'UNSOLICITED' THEN --unsolicited line
3414
3415 UPDATE po_requisition_lines_all
3416 SET auction_header_id = p_auction_header_id,
3417 auction_line_number = -1,
3418 auction_display_number = -1,
3419 bid_number = -1,
3420 bid_line_number = -1,
3421 last_update_date = SYSDATE,
3422 last_updated_by = FND_GLOBAL.USER_ID,
3423 last_update_login = FND_GLOBAL.LOGIN_ID
3424 WHERE requisition_line_id = p_requisition_line_id(i)
3425 AND auction_header_id IS NULL
3426 returning requisition_header_id INTO l_requisition_header_id;
3427
3428 l_progress := '0030';
3429 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3430 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' ReqLineId: '|| p_requisition_line_id(i) || ' Updated no. of rows: '|| sql%rowcount);
3431 END IF;
3432 END IF;
3433
3434 --looping thru the line_id array and updating the reqs_in_pool_flag and at_sourcing_flag for the entire structure(including slins and options) of the selected line
3435 --removing the slins and options of all the selected clins from the requisition pool
3436
3437 UPDATE po_requisition_lines_all
3438 SET reqs_in_pool_flag = null,
3439 at_sourcing_flag = 'Y',
3440 last_update_date = SYSDATE,
3441 last_updated_by = FND_GLOBAL.USER_ID,
3442 last_update_login = FND_GLOBAL.LOGIN_ID
3443 WHERE requisition_header_id = l_requisition_header_id
3444 AND Nvl(reqs_in_pool_flag, 'N') = 'Y'
3445 AND ((requisition_line_id = p_requisition_line_id(i))
3446 OR (group_line_id = p_requisition_line_id(i))
3447 OR (requisition_line_id IN (SELECT group_line_id from po_requisition_lines_all
3448 WHERE requisition_line_id = p_requisition_line_id(i)
3449 AND requisition_header_id = l_requisition_header_id))
3450 OR (group_line_id IN (SELECT group_line_id from po_requisition_lines_all
3451 WHERE requisition_line_id = p_requisition_line_id(i)
3452 AND requisition_header_id = l_requisition_header_id))
3453 OR (clm_base_line_num = p_requisition_line_id(i))
3454 OR (clm_base_line_num IN (SELECT group_line_id from po_requisition_lines_all
3455 WHERE requisition_line_id = p_requisition_line_id(i)
3456 AND requisition_header_id = l_requisition_header_id))
3457 OR (group_line_id IN (SELECT requisition_line_id FROM po_requisition_lines_all
3458 WHERE clm_base_line_num = p_requisition_line_id(i)
3459 AND requisition_header_id = l_requisition_header_id))
3460 OR (group_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
3461 WHERE clm_base_line_num = p_requisition_line_id(i)
3462 AND requisition_header_id = l_requisition_header_id))
3463 OR (requisition_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
3464 WHERE clm_base_line_num = p_requisition_line_id(i)
3465 AND requisition_header_id = l_requisition_header_id))
3466 OR (clm_base_line_num IN (SELECT requisition_line_id FROM po_requisition_lines_all
3467 WHERE group_line_id IN (SELECT group_line_id from po_requisition_lines_all
3468 WHERE requisition_line_id = p_requisition_line_id(i)
3469 AND requisition_header_id = l_requisition_header_id )
3470 AND requisition_header_id = l_requisition_header_id))
3471 OR (requisition_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
3472 WHERE clm_base_line_num IN (SELECT requisition_line_id FROM po_requisition_lines_all
3473 WHERE group_line_id IN (SELECT group_line_id from po_requisition_lines_all
3474 WHERE requisition_line_id = p_requisition_line_id(i)
3475 AND requisition_header_id = l_requisition_header_id)
3476 AND requisition_header_id = l_requisition_header_id)
3477 AND requisition_header_id = l_requisition_header_id))
3478 OR (group_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
3479 WHERE clm_base_line_num IN (SELECT requisition_line_id FROM po_requisition_lines_all
3480 WHERE group_line_id IN (SELECT group_line_id from po_requisition_lines_all
3481 WHERE requisition_line_id = p_requisition_line_id(i)
3482 AND requisition_header_id = l_requisition_header_id)
3483 AND requisition_header_id = l_requisition_header_id)
3484 AND requisition_header_id = l_requisition_header_id)));
3485 END LOOP;
3486
3487 l_progress := '0040';
3488 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3489 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Update the selected requisitions reqs_in_pool_flag and at_sourcing_flag for the selected lines');
3490 END IF;
3491
3492 l_progress := '0050';
3493 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3494 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' End proc: remove_from_req_pool');
3495 END IF;
3496
3497 EXCEPTION
3498 WHEN OTHERS THEN
3499 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3500 x_error_msg := 'Unexpectional error occured. sqlerrm: '||sqlerrm;
3501
3502 l_progress := '0060';
3503 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3504 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Unexpected error occured');
3505 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' sqlerrm: '||sqlerrm);
3506 END IF;
3507
3508 raise; --raise exception
3509
3510 END REMOVE_FROM_REQ_POOL;
3511
3512 PROCEDURE return_reqs_to_pool(p_req_line_ids IN number_table,
3513 x_return_status OUT NOCOPY VARCHAR2,
3514 x_error_msg OUT NOCOPY VARCHAR2,
3515 x_error_code OUT NOCOPY VARCHAR2) IS
3516 l_group_line_id NUMBER;
3517 l_req_line_to_update number_table;
3518
3519 y NUMBER := 0;
3520 l_req_id NUMBER;
3521 l_priced_clin_YN VARCHAR2(1);
3522 l_progress VARCHAR2(10);
3523 l_requisition_header_id NUMBER;
3524
3525 BEGIN
3526
3527 --find if the PR ref line being deleted is a priced CLIN or priced SLIN
3528
3529 FOR i IN 1..p_req_line_ids.Count LOOP
3530
3531 SELECT requisition_header_id, Decode(Nvl(group_line_id, -999), -999, 'Y','N') INTO l_requisition_header_id, l_priced_clin_YN
3532 FROM po_requisition_lines_all
3533 WHERE requisition_line_id = p_req_line_ids(i);
3534
3535 IF (l_priced_clin_YN = 'Y') THEN
3536
3537 UPDATE po_requisition_lines_all prla SET at_sourcing_flag = NULL,
3538 reqs_in_pool_flag = (CASE
3539 WHEN (nvl(modified_by_agent_flag,'N') = 'Y'
3540 or NVL(cancel_flag,'N') IN ('Y', 'I')
3541 or NVL(closed_code,'OPEN') = 'FINALLY CLOSED'
3542 or source_type_code = 'INVENTORY'
3543 or NVL(line_location_id, -999) <> -999
3544 or exists
3545 (select 'Req Header auth_status is not approved or contractor_status is pending'
3546 from po_requisition_headers_all prha
3547 where prha.requisition_header_id = prla.requisition_header_id
3548 and (NVL(prha.authorization_status,'INCOMPLETE') <> 'APPROVED'
3549 or NVL(prha.contractor_status,'NOT_APPLICABLE') = 'PENDING')))
3550 THEN null
3551 ELSE 'Y'
3552 END),
3553 last_update_date = SYSDATE,
3554 last_updated_by = FND_GLOBAL.USER_ID,
3555 last_update_login = FND_GLOBAL.LOGIN_ID
3556 WHERE requisition_header_id = l_requisition_header_id
3557 AND ((requisition_line_id = p_req_line_ids(i) )
3558 OR (group_line_id = p_req_line_ids(i))
3559 OR (clm_base_line_num = p_req_line_ids(i))
3560 OR (group_line_id IN (SELECT requisition_line_id FROM po_requisition_lines_all
3561 WHERE clm_base_line_num = p_req_line_ids(i)
3562 AND requisition_header_id = l_requisition_header_id)));
3563
3564 ELSE
3565
3566 BEGIN
3567
3568
3569 SELECT requisition_line_id INTO l_req_id
3570 FROM po_requisition_lines_all
3571 WHERE requisition_header_id = l_requisition_header_id
3572 AND ((requisition_line_id = p_req_line_ids(i) )
3573 OR (requisition_line_id = (SELECT group_line_id from po_requisition_lines_all
3574 WHERE requisition_line_id = p_req_line_ids(i)
3575 AND requisition_header_id = l_requisition_header_id))
3576 OR (group_line_id IN (SELECT group_line_id from po_requisition_lines_all
3577 WHERE requisition_line_id = p_req_line_ids(i)
3578 AND requisition_header_id = l_requisition_header_id))
3579 OR (clm_base_line_num = p_req_line_ids(i))
3580 OR (clm_base_line_num IN (SELECT group_line_id from po_requisition_lines_all
3581 WHERE requisition_line_id = p_req_line_ids(i)
3582 AND requisition_header_id = l_requisition_header_id))
3583 OR (group_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
3584 WHERE clm_base_line_num = p_req_line_ids(i)
3585 AND requisition_header_id = l_requisition_header_id))
3586 OR (requisition_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
3587 WHERE clm_base_line_num = p_req_line_ids(i)
3588 AND requisition_header_id = l_requisition_header_id))
3589 OR (clm_base_line_num IN (SELECT requisition_line_id FROM po_requisition_lines_all
3590 WHERE group_line_id IN (SELECT group_line_id from po_requisition_lines_all
3591 WHERE requisition_line_id = p_req_line_ids(i)
3592 AND requisition_header_id = l_requisition_header_id)
3593 AND requisition_header_id = l_requisition_header_id))
3594 OR (requisition_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
3595 WHERE clm_base_line_num IN (SELECT requisition_line_id FROM po_requisition_lines_all
3596 WHERE group_line_id IN (SELECT group_line_id from po_requisition_lines_all
3597 WHERE requisition_line_id = p_req_line_ids(i)
3598 AND requisition_header_id = l_requisition_header_id)
3599 AND requisition_header_id = l_requisition_header_id)
3600 AND requisition_header_id = l_requisition_header_id))
3601 OR (group_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
3602 WHERE clm_base_line_num IN (SELECT requisition_line_id FROM po_requisition_lines_all
3603 WHERE group_line_id IN (SELECT group_line_id from po_requisition_lines_all
3604 WHERE requisition_line_id = p_req_line_ids(i)
3605 AND requisition_header_id = l_requisition_header_id)
3606 AND requisition_header_id = l_requisition_header_id)
3607 AND requisition_header_id = l_requisition_header_id)))
3608
3609 AND auction_header_id IS NOT NULL ;
3610
3611 EXCEPTION
3612 WHEN No_Data_Found THEN
3613
3614 UPDATE po_requisition_lines_all prla SET at_sourcing_flag = NULL,
3615 reqs_in_pool_flag = (CASE
3616 WHEN (nvl(modified_by_agent_flag,'N') = 'Y'
3617 or NVL(cancel_flag,'N') IN ('Y', 'I')
3618 or NVL(closed_code,'OPEN') = 'FINALLY CLOSED'
3619 or source_type_code = 'INVENTORY'
3620 or NVL(line_location_id, -999) <> -999
3621 or exists
3622 (select 'Req Header auth_status is not approved or contractor_status is pending'
3623 from po_requisition_headers_all prha
3624 where prha.requisition_header_id = prla.requisition_header_id
3625 and (NVL(prha.authorization_status,'INCOMPLETE') <> 'APPROVED'
3626 or NVL(prha.contractor_status,'NOT_APPLICABLE') = 'PENDING')))
3627 THEN null
3628 ELSE 'Y'
3629 END),
3630 last_update_date = SYSDATE,
3631 last_updated_by = FND_GLOBAL.USER_ID,
3632 last_update_login = FND_GLOBAL.LOGIN_ID
3633 WHERE requisition_header_id = l_requisition_header_id
3634 AND ((requisition_line_id = p_req_line_ids(i) )
3635 OR (requisition_line_id = (SELECT group_line_id from po_requisition_lines_all
3636 WHERE requisition_line_id = p_req_line_ids(i)
3637 AND requisition_header_id = l_requisition_header_id))
3638 OR (group_line_id IN (SELECT group_line_id from po_requisition_lines_all
3639 WHERE requisition_line_id = p_req_line_ids(i)
3640 AND requisition_header_id = l_requisition_header_id))
3641 OR (clm_base_line_num = p_req_line_ids(i))
3642 OR (clm_base_line_num IN (SELECT group_line_id from po_requisition_lines_all
3643 WHERE requisition_line_id = p_req_line_ids(i)
3644 AND requisition_header_id = l_requisition_header_id))
3645 OR (group_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
3646 WHERE clm_base_line_num = p_req_line_ids(i)
3647 AND requisition_header_id = l_requisition_header_id))
3648 OR (requisition_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
3649 WHERE clm_base_line_num = p_req_line_ids(i)
3650 AND requisition_header_id = l_requisition_header_id))
3651 OR (clm_base_line_num IN (SELECT requisition_line_id FROM po_requisition_lines_all
3652 WHERE group_line_id IN (SELECT group_line_id from po_requisition_lines_all
3653 WHERE requisition_line_id = p_req_line_ids(i)
3654 AND requisition_header_id = l_requisition_header_id)))
3655 OR (requisition_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
3656 WHERE clm_base_line_num IN (SELECT requisition_line_id FROM po_requisition_lines_all
3657 WHERE group_line_id IN (SELECT group_line_id from po_requisition_lines_all
3658 WHERE requisition_line_id = p_req_line_ids(i)
3659 AND requisition_header_id = l_requisition_header_id)
3660 AND requisition_header_id = l_requisition_header_id )
3661 AND requisition_header_id = l_requisition_header_id))
3662 OR (group_line_id IN (SELECT group_line_id FROM po_requisition_lines_all
3663 WHERE clm_base_line_num IN (SELECT requisition_line_id FROM po_requisition_lines_all
3664 WHERE group_line_id IN (SELECT group_line_id from po_requisition_lines_all
3665 WHERE requisition_line_id = p_req_line_ids(i)
3666 AND requisition_header_id = l_requisition_header_id)
3667 AND requisition_header_id = l_requisition_header_id)
3668 AND requisition_header_id = l_requisition_header_id)) );
3669
3670 END;
3671
3672 END IF;
3673 END LOOP;
3674
3675 EXCEPTION
3676 WHEN OTHERS THEN
3677 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3678 x_error_msg := 'Unexpectional error occured. sqlerrm: '||sqlerrm;
3679
3680 l_progress := '0060';
3681 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3682 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Unexpected error occured');
3683 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' sqlerrm: '||sqlerrm);
3684 END IF;
3685
3686 raise; --raise exception
3687
3688 END return_reqs_to_pool;
3689
3690
3691 --Delete All Lines
3692
3693 PROCEDURE update_sol_ref_delete_all(p_auction_header_id IN NUMBER,
3694 p_delete_pbr_yn IN VARCHAR2 DEFAULT 'Y',
3695 x_return_status OUT NOCOPY VARCHAR2,
3696 x_error_msg OUT NOCOPY VARCHAR2,
3697 x_error_code OUT NOCOPY VARCHAR2)
3698 IS
3699
3700 CURSOR c_get_req_line_id_csr IS
3701 SELECT DISTINCT(requisition_line_id)
3702 FROM pon_backing_requisitions
3703 WHERE auction_header_id = p_auction_header_id;
3704
3705 l_req_line_ids PO_TBL_NUMBER := PO_TBL_NUMBER();
3706
3707 l_progress VARCHAR2(10);
3708
3709
3710 BEGIN
3711
3712 OPEN c_get_req_line_id_csr;
3713 FETCH c_get_req_line_id_csr BULK collect INTO l_req_line_ids;
3714 CLOSE c_get_req_line_id_csr;
3715
3716 --delete pon_backing_requisitions
3717 IF p_delete_pbr_yn = 'Y' THEN
3718 DELETE FROM pon_backing_requisitions WHERE auction_header_id = p_auction_header_id;
3719 END IF;
3720
3721 --update sol ref in po_requisition_lines_all
3722
3723 update po_requisition_lines_all prla
3724 set auction_header_id = null,
3725 auction_display_number = null,
3726 auction_line_number = null,
3727 at_sourcing_flag = null,
3728 on_rfq_flag = null,
3729 reqs_in_pool_flag = (CASE
3730 WHEN (nvl(modified_by_agent_flag,'N') = 'Y'
3731 or NVL(cancel_flag,'N') IN ('Y', 'I')
3732 or NVL(closed_code,'OPEN') = 'FINALLY CLOSED'
3733 or source_type_code = 'INVENTORY'
3734 or NVL(line_location_id, -999) <> -999
3735 or exists
3736 (select 'Req Header auth_status is not approved or contractor_status is pending'
3737 from po_requisition_headers_all prha
3738 where prha.requisition_header_id = prla.requisition_header_id
3739 and (NVL(prha.authorization_status,'INCOMPLETE') <> 'APPROVED'
3740 or NVL(prha.contractor_status,'NOT_APPLICABLE') = 'PENDING')))
3741 THEN null
3742 ELSE 'Y'
3743 END),
3744 last_update_date = SYSDATE,
3745 last_updated_by = FND_GLOBAL.USER_ID,
3746 last_update_login = FND_GLOBAL.LOGIN_ID
3747 where auction_header_id = p_auction_header_id;
3748
3749 x_return_status := FND_API.G_RET_STS_SUCCESS;
3750
3751 EXCEPTION
3752 WHEN OTHERS THEN
3753 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3754 x_error_msg := 'Unexpectional error occured. sqlerrm: '||sqlerrm;
3755
3756 l_progress := '0060';
3757 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3758 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Unexpected error occured');
3759 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' sqlerrm: '||sqlerrm);
3760 END IF;
3761
3762 raise; --raise exception
3763
3764
3765 END update_sol_ref_delete_all;
3766
3767 PROCEDURE update_sol_ref_delete_line(p_auction_header_id IN NUMBER,
3768 p_auction_line_number IN NUMBER,
3769 p_bid_number IN NUMBER DEFAULT NULL,
3770 p_bid_line_number IN NUMBER DEFAULT NULL,
3771 p_requisition_line_id IN NUMBER DEFAULT NULL,
3772 p_table_name IN VARCHAR2 DEFAULT 'PON_BACKING_REQUISITIONS',
3773 x_return_status OUT NOCOPY VARCHAR2,
3774 x_error_msg OUT NOCOPY VARCHAR2,
3775 x_error_code OUT NOCOPY VARCHAR2)
3776 IS
3777
3778 CURSOR c_get_req_line_id_csr IS
3779 SELECT requisition_line_id
3780 FROM pon_backing_requisitions
3781 WHERE auction_header_id = p_auction_header_id
3782 AND EXISTS ( SELECT line_number
3783 FROM pon_auction_item_prices_ALL
3784 WHERE auction_header_id = p_auction_header_id
3785 AND (line_number = p_auction_line_number
3786 OR group_line_id = p_auction_line_number))
3787 GROUP BY requisition_line_id;
3788
3789 l_req_line_ids number_table;
3790 l_progress VARCHAR2(10);
3791
3792 BEGIN
3793
3794 IF p_requisition_line_id IS NOT NULL THEN
3795
3796 l_req_line_ids(1) := p_requisition_line_id;
3797
3798 IF (p_table_name = 'PON_BACKING_REQUISITIONS') THEN
3799 DELETE FROM pon_backing_requisitions
3800 WHERE auction_header_id = p_auction_header_id
3801 AND line_number = p_auction_line_number
3802 AND requisition_line_id = p_requisition_line_id;
3803 else
3804 DELETE FROM pon_bid_backing_requisitions
3805 WHERE auction_header_id = p_auction_header_id
3806 AND auction_line_number = p_auction_line_number
3807 and bid_number = p_bid_number
3808 and bid_line_number = p_bid_line_number
3809 AND requisition_line_id = p_requisition_line_id;
3810 end if;
3811
3812 ELSE
3813
3814 --delete pon_backing_requisitions for that line and all its sublines if its a clin.
3815 DELETE FROM pon_backing_requisitions
3816 WHERE auction_header_id = p_auction_header_id
3817 AND line_number = p_auction_line_number;
3818
3819 END IF;
3820
3821 /*IF the deleted req line id is not referred anywhere other than in this line for the
3822 current auction_header_id then update sol ref in po_requisition_lines_all*/
3823
3824 IF (p_table_name = 'PON_BACKING_REQUISITIONS') THEN
3825
3826 UPDATE po_requisition_lines_all prl SET auction_header_id = NULL,
3827 auction_line_number = NULL,
3828 auction_display_number = NULL,
3829 last_update_date = SYSDATE,
3830 last_updated_by = FND_GLOBAL.USER_ID,
3831 last_update_login = FND_GLOBAL.LOGIN_ID
3832 WHERE auction_header_id = p_auction_header_id
3833 AND NOT EXISTS (SELECT requisition_line_id
3834 FROM pon_backing_requisitions pbr
3835 WHERE auction_header_id = p_auction_header_id
3836 AND prl.requisition_line_id = pbr.requisition_line_id
3837 GROUP BY requisition_line_id )
3838 RETURNING requisition_line_id BULK COLLECT INTO l_req_line_ids;
3839
3840 ELSE
3841 UPDATE po_requisition_lines_all prl SET auction_header_id = NULL,
3842 auction_line_number = NULL,
3843 auction_display_number = NULL,
3844 bid_number = NULL,
3845 bid_line_number = NULL,
3846 last_update_date = SYSDATE,
3847 last_updated_by = FND_GLOBAL.USER_ID,
3848 last_update_login = FND_GLOBAL.LOGIN_ID
3849 WHERE auction_header_id = p_auction_header_id
3850 AND NOT EXISTS (SELECT requisition_line_id
3851 FROM pon_bid_backing_requisitions pbr
3852 WHERE auction_header_id = p_auction_header_id
3853 AND prl.requisition_line_id = pbr.requisition_line_id
3854 GROUP BY requisition_line_id )
3855 RETURNING requisition_line_id BULK COLLECT INTO l_req_line_ids;
3856
3857 END IF;
3858
3859 IF(l_req_line_ids.Count > 0) THEN
3860 return_reqs_to_pool( p_req_line_ids =>l_req_line_ids,
3861 x_return_status => x_return_status,
3862 x_error_msg => x_error_msg,
3863 x_error_code => x_error_code);
3864 END IF;
3865
3866 x_return_status := FND_API.G_RET_STS_SUCCESS;
3867
3868 EXCEPTION
3869 WHEN OTHERS THEN
3870 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3871 x_error_msg := 'Unexpectional error occured. sqlerrm: '||sqlerrm;
3872
3873 l_progress := '0060';
3874 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3875 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Unexpected error occured');
3876 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' sqlerrm: '||sqlerrm);
3877 END IF;
3878
3879 raise; --raise exception
3880
3881
3882 END update_sol_ref_delete_line;
3883
3884 /*Amendment conform case*/
3885 PROCEDURE update_sol_ref_amend(p_old_auction_id IN NUMBER,
3886 p_new_auction_id IN NUMBER,
3887 x_return_status OUT NOCOPY VARCHAR2,
3888 x_error_msg OUT NOCOPY VARCHAR2,
3889 x_error_code OUT NOCOPY VARCHAR2)
3890 IS
3891
3892 CURSOR c_deleted_pr_ref_csr IS
3893 SELECT DISTINCT(requisition_line_id)
3894 FROM pon_backing_requisitions
3895 WHERE auction_header_id = p_old_auction_id
3896 MINUS (SELECT requisition_line_id
3897 FROM pon_backing_requisitions
3898 WHERE auction_header_id = p_new_auction_id);
3899
3900 l_req_line_ids number_table;
3901
3902 l_progress VARCHAR2(10);
3903
3904
3905 BEGIN
3906
3907 OPEN c_deleted_pr_ref_csr;
3908 FETCH c_deleted_pr_ref_csr BULK COLLECT INTO l_req_line_ids;
3909 CLOSE c_deleted_pr_ref_csr;
3910
3911 /*Update ref of auction in po_requisition_lines_all table only for those lines copied to the new auction*/
3912
3913 UPDATE po_requisition_lines_all prl SET auction_header_id = p_new_auction_id
3914 WHERE EXISTS (SELECT DISTINCT(requisition_line_id)
3915 FROM pon_backing_requisitions pbr
3916 WHERE auction_header_id = p_new_auction_id
3917 AND prl.requisition_line_id = pbr.requisition_line_id);
3918
3919 /* For deleted PR references remove sol references in po_requisition_lines_all*/
3920 UPDATE po_requisition_lines_all SET auction_header_id = NULL,
3921 auction_line_number = NULL,
3922 auction_display_number = NULL,
3923 last_update_date = SYSDATE,
3924 last_updated_by = FND_GLOBAL.USER_ID,
3925 last_update_login = FND_GLOBAL.LOGIN_ID
3926 WHERE EXISTS (SELECT DISTINCT(requisition_line_id)
3927 FROM pon_backing_requisitions
3928 WHERE auction_header_id = p_old_auction_id
3929 MINUS (SELECT requisition_line_id
3930 FROM pon_backing_requisitions
3931 WHERE auction_header_id = p_new_auction_id))
3932 RETURNING requisition_line_id BULK COLLECT INTO l_req_line_ids ;
3933
3934 /*Put the lines back to pool if the entire structure is out of pool*/
3935 IF l_req_line_ids.Count > 0 THEN
3936 return_reqs_to_pool( p_req_line_ids =>l_req_line_ids,
3937 x_return_status => x_return_status,
3938 x_error_msg => x_error_msg,
3939 x_error_code => x_error_code);
3940 END IF;
3941
3942 x_return_status := FND_API.G_RET_STS_SUCCESS;
3943
3944
3945 EXCEPTION
3946 WHEN OTHERS THEN
3947 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3948 x_error_msg := 'Unexpectional error occured. sqlerrm: '||sqlerrm;
3949
3950 l_progress := '0060';
3951 IF ( fnd_log.level_statement >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
3952 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' Unexpected error occured');
3953 FND_LOG.string(fnd_log.level_statement, g_log_module_name, 'progress:='||l_progress||' sqlerrm: '||sqlerrm);
3954 END IF;
3955
3956 raise; --raise exception
3957 END update_sol_ref_amend;
3958
3959
3960 END po_negotiations_sv1;