[Home] [Help]
PACKAGE BODY: APPS.PON_UNSOL_CREATE_PO_PKG
Source
1 PACKAGE BODY PON_UNSOL_CREATE_PO_PKG AS
2 --$Header: PON_UNSOL_CREATE_PO_PKG.plb 120.1.12020000.2 2013/02/09 07:33:20 hvutukur ship $
3
4 g_module_prefix CONSTANT VARCHAR2(50) := 'pon.plsql.PON_UNSOL_CREATE_PO_PKG.';
5
6 PROCEDURE insert_unsol_lines_spo(p_interface_header_id IN NUMBER,
7 p_auction_header_id IN NUMBER,
8 p_bid_number IN NUMBER,
9 p_document_number IN VARCHAR2,
10 p_is_fed IN VARCHAR2,
11 p_user_id IN NUMBER) IS
12
13 BEGIN
14 INSERT into PO_LINES_INTERFACE (
15 interface_header_id,
16 interface_line_id,
17 requisition_line_id,
18 line_type_id,
19 item_id,
20 item_revision,
21 category_id,
22 item_description,
23 unit_of_measure,
24 quantity,
25 unit_price,
26 min_release_amount,
27 ship_to_location_id,
28 need_by_date,
29 clm_period_perf_start_date,
30 clm_period_perf_end_date,
31 promised_date,
32 last_updated_by,
33 last_update_date,
34 created_by,
35 creation_date,
36 auction_header_id,
37 auction_display_number,
38 auction_line_number,
39 bid_number,
40 bid_line_number,
41 orig_from_req_flag,
42 --job_id,
43 amount,
44 advance_amount,
45 recoupment_rate,
46 progress_payment_rate,
47 retainage_rate,
48 max_retainage_amount,
49 Line_loc_populated_flag,
50 line_num_display,
51 group_line_id,
52 clm_info_flag,
53 clm_option_indicator,
54 clm_option_num,
55 clm_option_from_date,
56 clm_option_to_date,
57 clm_funded_flag,
58 clm_base_line_num,
59 CONTRACT_TYPE,
60 COST_CONSTRAINT,
61 CLM_IDC_TYPE,
62 from_header_id,
63 from_line_id,
64 -- Event Based Delivery Project
65 CLM_DELIVERY_EVENT_CODE,
66 CLM_DELIVERY_PERIOD,
67 CLM_DELIVERY_PERIOD_UOM,
68 CLM_PROMISE_PERIOD,
69 CLM_PROMISE_PERIOD_UOM,
70 CLM_POP_DURATION,
71 CLM_POP_DURATION_UOM
72 )
73 SELECT
74 p_interface_header_id, -- interface_header_id
75 po_lines_interface_s.nextval, -- interface_line_id
76 NULL, -- requisition_line_id
77 pbip.line_type_id, -- line_type_id
78 NULL,--pbip.item_id, -- item_id
79 NULL,--pbip.item_revision, -- item_revision
80 pbip.category_id, -- category_id
81 substrb(pbip.item_description, 1, 240), -- item_description
82 decode(pbip.order_type_lookup_code, 'AMOUNT', null, mtluom.unit_of_measure), -- unit_of_measure
83 decode(pbip.order_type_lookup_code, 'RATE', TO_NUMBER(null),
84 'FIXED PRICE', TO_NUMBER(null),
85 'AMOUNT', pbip.bid_currency_unit_price,
86 pbip.award_quantity), -- QUANTITY
87 decode(pbip.order_type_lookup_code,'AMOUNT', 1,
88 'FIXED PRICE', TO_NUMBER(NULL),pbip.bid_currency_unit_price), --unit_price
89 pbip.po_bid_min_rel_amount, -- min_release_amount
90 pbip.ship_to_location_id, -- ship_to_location_id
91 null, -- need_by_date
92 Decode(Nvl(p_is_fed,'N'),'Y',pbip.promise_pop_start_date,null), -- period_of_performance_start_date
93 Decode(Nvl(p_is_fed,'N'),'Y',pbip.promise_pop_end_date,null), -- period_of_performance_end_date
94 pbip.promised_date, -- promised_date
95 p_user_id, -- last_update_by
96 sysdate, -- last_update_date
97 p_user_id, -- created_by
98 sysdate, -- creation_date
99 p_auction_header_id, -- auction_header_id
100 p_document_number, -- document_number
101 pbip.line_number, -- auction_line_number,
102 pbip.bid_number, -- bid_number
103 pbip.line_number, -- bid_line_number
104 Decode('Y', (SELECT is_linked_pr_line_yn FROM pon_award_allocations paa
105 WHERE paa.bid_number = pbip.bid_number AND paa.bid_line_number = pbip.line_number AND paa.is_linked_pr_line_yn = 'Y' AND ROWNUM <2),
106 'S', 'N'), -- orig_from_req_flag
107 --pbip.job_id, -- job_id
108 decode(pbip.order_type_lookup_code,'FIXED PRICE', pbip.bid_currency_unit_price, TO_NUMBER(NULL)) -- amount
109 , decode(pbip.bid_curr_advance_amount,0,null,pbip.bid_curr_advance_amount)
110 , pbip.recoupment_rate_percent
111 , pbip.progress_pymt_rate_percent
112 , pbip.retainage_rate_percent
113 , pbip.Bid_curr_max_retainage_amt
114 , 'N' --Line_loc_populated
115 , pbip.line_num_display
116 , pbip.group_line_id
117 , pbip.clm_info_flag
118 , pbip.clm_option_indicator
119 , pbip.clm_option_num
120 , pbip.clm_option_from_date
121 , pbip.clm_option_to_date
122 , pbip.clm_funded_flag
123 , pbip.clm_base_line_num
124 , pbip.CLM_CONTRACT_TYPE
125 , pbip.CLM_COST_CONSTRAINT
126 , pbip.CLM_IDC_TYPE
127 --CLM Order Off IDV Project
128 , pbh.idv_header_id
129 , pbip.idv_line_id
130 -- Event Based Delivery Project
131 ,Decode(Nvl(p_is_fed,'N'),'Y',pbip.CLM_DELIVERY_EVENT_CODE,null)
132 ,Decode(Nvl(p_is_fed,'N'),'Y',pbip.CLM_PROMISE_PERIOD,null)
133 ,Decode(Nvl(p_is_fed,'N'),'Y',pbip.CLM_PROMISE_PERIOD_UOM,null)
134 ,Decode(Nvl(p_is_fed,'N'),'Y',pbip.CLM_PROMISE_PERIOD,null)
135 ,Decode(Nvl(p_is_fed,'N'),'Y',pbip.CLM_PROMISE_PERIOD_UOM,null)
136 ,Decode(Nvl(p_is_fed,'N'),'Y',pbip.CLM_PROMISE_POP_DURATION,null)
137 ,Decode(Nvl(p_is_fed,'N'),'Y',pbip.CLM_PROMISE_POP_DURATION_UOM,null)
138 FROM
139 pon_bid_item_prices pbip,
140 mtl_units_of_measure mtluom,
141 pon_bid_headers pbh,
142 fnd_currencies fc
143 WHERE
144 pbh.bid_number = p_bid_number AND
145 pbh.auction_header_id = p_auction_header_id AND
146 pbip.bid_number = pbh.bid_number and
147 pbip.auction_line_number = -1 AND
148 nvl(pbip.award_status, 'NO') = 'AWARDED' and
149 pbip.uom = mtluom.uom_code (+) and
150 fc.currency_code = pbh.bid_currency_code;
151
152
153 EXCEPTION
154 WHEN OTHERS THEN
155 RAISE;
156
157 END insert_unsol_lines_spo;
158
159
160 PROCEDURE INSERT_UNSOL_IP_DESCRIPTORS(p_auction_header_id IN NUMBER,
161 p_bid_number IN NUMBER,
162 p_interface_header_id IN NUMBER,
163 p_user_id IN NUMBER,
164 p_login_id IN NUMBER) IS
165
166
167 l_cursorName NUMBER;
168 l_cursorResult NUMBER;
169
170 TYPE NUMBER_LIST is TABLE of NUMBER
171 INDEX BY BINARY_INTEGER;
172 TYPE VARCHAR_LIST is TABLE of VARCHAR2(32767)
173 INDEX BY BINARY_INTEGER;
174
175
176 -- holds the values to be inserted into the interface tables
177 l_numValues NUMBER_LIST; -- holds descriptor values of number type
178 l_txtValues VARCHAR_LIST; -- holds descriptor values of text type
179 l_transTxtValues VARCHAR_LIST; -- holds descriptor values of translateable text type
180
181 -- empty tables for clearing/resetting above datastructures
182 l_emptyNumValues NUMBER_LIST;
183 l_emptyTxtValues VARCHAR_LIST;
184 l_emptyTransTxtValues VARCHAR_LIST;
185
186 -- keeps track of the size of the tables
187 l_numValuesCount NUMBER;
188 l_txtValuesCount NUMBER;
189 l_transTxtValuesCount NUMBER;
190
191 l_cur_interface_line_id NUMBER;
192 l_cur_attr_values_id NUMBER;
193 l_cur_attr_values_tlp_id NUMBER;
194 l_cur_item_description pon_auction_item_prices_all.item_description%TYPE;
195 l_cur_ip_category_id NUMBER;
196 l_cur_item_id NUMBER;
197 l_cur_org_id NUMBER;
198 l_language_code pon_auction_headers_all.language_code%TYPE;
199
200 l_po_attr_values_stmt VARCHAR2(32767);
201 l_po_attr_values_tlp_stmt VARCHAR2(32767);
202
203 l_po_attr_values_cols VARCHAR2(32767);
204 l_po_attr_values_vals VARCHAR2(32767);
205
206 l_po_attr_values_tlp_cols VARCHAR2(32767);
207 l_po_attr_values_tlp_vals VARCHAR2(32767);
208
209
210 CURSOR c_descriptors IS
211 SELECT pbip.line_number,
212 pli.interface_line_id,
213 pbip.item_description,
214 --nvl(paip.ip_category_id, -2) ip_category_id,
215 -2 ip_category_id, --ip_category_id
216 -2 item_id,--nvl(paip.item_id, -2) item_id,
217 pah.org_id,
218 decode(icx.type, 0, 'TXT', 1, 'NUM', 2, 'TRANS') datatype,
219 icx.stored_in_table,
220 icx.stored_in_column,
221 pbav.value,
222 paa.attribute_name
223 FROM pon_bid_item_prices pbip,
224 pon_auction_headers_all pah,
225 po_lines_interface pli,
226 pon_bid_attribute_values pbav,
227 pon_auction_attributes paa,
228 icx_cat_agreement_attrs_v icx
229 WHERE pbip.auction_header_id = p_auction_header_id AND
230 pbip.bid_number = p_bid_number AND
231 pbip.auction_line_number = -1 AND
232 nvl(pbip.award_status, 'NO') = 'AWARDED' and
233 pah.auction_header_id = pbip.auction_header_id and
234 pli.interface_header_id = p_interface_header_id and
235 pbip.auction_header_id = pli.auction_header_id and
236 pbip.line_number = pli.auction_line_number and
237 pbip.auction_header_id = pbav.auction_header_id (+) and
238 pbip.bid_number = pbav.bid_number (+) and
239 pbip.line_number = pbav.line_number (+) and
240 pbav.auction_header_id = paa.auction_header_id (+) and
241 pbav.line_number = paa.line_number (+) and
242 pbav.sequence_number = paa.sequence_number (+) and
243 paa.ip_category_id (+) is not null and
244 paa.ip_category_id = icx.rt_category_id (+) and
245 paa.ip_descriptor_id = icx.attribute_id (+) and
246 icx.language (+) = userenv('LANG')
247 ORDER BY interface_line_id asc,
248 decode(datatype, 'NUM', 0, 'TXT', 1, 2) asc;
249
250 descriptor c_descriptors%ROWTYPE;
251
252 l_num_txt_offset NUMBER := 11;
253 l_trans_txt_offset NUMBER := 13;
254
255 BEGIN
256
257 select language_code
258 into l_language_code
259 from pon_auction_headers_all
260 where auction_header_id = p_auction_header_id;
261
262
263 l_cursorName := DBMS_SQL.Open_Cursor;
264 l_cur_interface_line_id := -9999;
265
266 OPEN c_descriptors;
267 LOOP
268
269 FETCH c_descriptors INTO descriptor;
270 IF (c_descriptors%NOTFOUND OR
271 descriptor.interface_line_id <> l_cur_interface_line_id) THEN
272
273 -- process number and text descriptors
274 IF (l_cur_interface_line_id <> -9999) THEN
275
276 l_po_attr_values_stmt :=
277 'insert into po_attr_values_interface(' ||
278 'interface_header_id, ' ||
279 'interface_line_id, ' ||
280 'interface_attr_values_id, ' ||
281 'ip_category_id, ' ||
282 'inventory_item_id, ' ||
283 'org_id, ' ||
284 'last_update_login, ' ||
285 'last_updated_by, ' ||
286 'last_update_date, ' ||
287 'created_by, ' ||
288 'creation_date' ||
289 l_po_attr_values_cols ||
290 ') values('||
291 ':1, ' ||
292 ':2, ' ||
293 ':3, ' ||
294 ':4, ' ||
295 ':5, ' ||
296 ':6, ' ||
297 ':7, ' ||
298 ':8, ' ||
299 ':9, ' ||
300 ':10, ' ||
301 ':11' ||
302 l_po_attr_values_vals ||
303 ')';
304
305 --log_message(l_po_attr_values_stmt);
306
307 DBMS_SQL.Parse(l_cursorName, l_po_attr_values_stmt, DBMS_SQL.NATIVE);
308
309 DBMS_SQL.Bind_Variable(l_cursorName, ':1', p_interface_header_id);
310 DBMS_SQL.Bind_Variable(l_cursorName, ':2', l_cur_interface_line_id);
311 DBMS_SQL.Bind_Variable(l_cursorName, ':3', l_cur_attr_values_id);
312 DBMS_SQL.Bind_Variable(l_cursorName, ':4', l_cur_ip_category_id);
313 DBMS_SQL.Bind_Variable(l_cursorName, ':5', l_cur_item_id);
314 DBMS_SQL.Bind_Variable(l_cursorName, ':6', l_cur_org_id);
315 DBMS_SQL.Bind_Variable(l_cursorName, ':7', p_login_id);
316 DBMS_SQL.Bind_Variable(l_cursorName, ':8', p_user_id);
317 DBMS_SQL.Bind_Variable(l_cursorName, ':9', sysdate);
318 DBMS_SQL.Bind_Variable(l_cursorName, ':10', p_user_id);
319 DBMS_SQL.Bind_Variable(l_cursorName, ':11', sysdate);
320
321 FOR i in 1 .. l_numValuesCount
322 LOOP
323 DBMS_SQL.Bind_Variable(l_cursorName, ':' || (i+l_num_txt_offset), l_numValues(i));
324 END LOOP;
325
326 FOR i in 1 ..l_txtValuesCount
327 LOOP
328 DBMS_SQL.Bind_Variable(l_cursorName, ':' || (i+l_num_txt_offset+l_numValuesCount), l_txtValues(i));
329 END LOOP;
330
331 l_cursorResult := DBMS_SQL.Execute(l_cursorName);
332
333 END IF;
334
335 -- process translateable text descriptors
336 IF (l_cur_interface_line_id <> -9999) THEN
337
338 l_po_attr_values_tlp_stmt :=
339 'insert into po_attr_values_tlp_interface(' ||
340 'interface_header_id, ' ||
341 'interface_line_id, ' ||
342 'interface_attr_values_tlp_id, ' ||
343 'ip_category_id, ' ||
344 'inventory_item_id, ' ||
345 'org_id, ' ||
346 'language, ' ||
347 'description, ' ||
348 'last_update_login, ' ||
349 'last_updated_by, ' ||
350 'last_update_date, ' ||
351 'created_by, ' ||
352 'creation_date' ||
353 l_po_attr_values_tlp_cols ||
354 ') values('||
355 ':1, ' ||
356 ':2, ' ||
357 ':3, ' ||
358 ':4, ' ||
359 ':5, ' ||
360 ':6, ' ||
361 ':7, ' ||
362 ':8, ' ||
363 ':9, ' ||
364 ':10, ' ||
365 ':11, ' ||
366 ':12, ' ||
367 ':13' ||
368 l_po_attr_values_tlp_vals ||
369 ')';
370
371 --log_message(l_po_attr_values_tlp_stmt);
372
373 DBMS_SQL.Parse(l_cursorName, l_po_attr_values_tlp_stmt, DBMS_SQL.NATIVE);
374
375 DBMS_SQL.Bind_Variable(l_cursorName, ':1', p_interface_header_id);
376 DBMS_SQL.Bind_Variable(l_cursorName, ':2', l_cur_interface_line_id);
377 DBMS_SQL.Bind_Variable(l_cursorName, ':3', l_cur_attr_values_tlp_id);
378 DBMS_SQL.Bind_Variable(l_cursorName, ':4', l_cur_ip_category_id);
379 DBMS_SQL.Bind_Variable(l_cursorName, ':5', l_cur_item_id);
380 DBMS_SQL.Bind_Variable(l_cursorName, ':6', l_cur_org_id);
381 DBMS_SQL.Bind_Variable(l_cursorName, ':7', l_language_code);
382 DBMS_SQL.Bind_Variable(l_cursorName, ':8', l_cur_item_description);
383 DBMS_SQL.Bind_Variable(l_cursorName, ':9', p_login_id);
384 DBMS_SQL.Bind_Variable(l_cursorName, ':10', p_user_id);
385 DBMS_SQL.Bind_Variable(l_cursorName, ':11', sysdate);
386 DBMS_SQL.Bind_Variable(l_cursorName, ':12', p_user_id);
387 DBMS_SQL.Bind_Variable(l_cursorName, ':13', sysdate);
388
389 FOR i in 1 .. l_transTxtValuesCount
390 LOOP
391 DBMS_SQL.Bind_Variable(l_cursorName, ':' || (i+l_trans_txt_offset), l_transTxtValues(i));
392 END LOOP;
393
394 l_cursorResult := DBMS_SQL.Execute(l_cursorName);
395
396 END IF;
397
398 EXIT WHEN c_descriptors%NOTFOUND;
399
400 -- initialize/reset variables on line change
401
402 l_cur_interface_line_id := descriptor.interface_line_id;
403
404 select po_attr_values_interface_s.nextval
405 into l_cur_attr_values_id
406 from dual;
407
408 select po_attr_values_tlp_interface_s.nextval
409 into l_cur_attr_values_tlp_id
410 from dual;
411
412 l_cur_item_description := descriptor.item_description;
413 l_cur_ip_category_id := descriptor.ip_category_id;
414 l_cur_item_id := descriptor.item_id;
415 l_cur_org_id := descriptor.org_id;
416
417 l_po_attr_values_cols := '';
418 l_po_attr_values_vals := '';
419
420 l_po_attr_values_tlp_cols := '';
421 l_po_attr_values_tlp_vals := '';
422
423 l_numValues := l_emptyNumValues;
424 l_txtValues := l_emptyTxtValues;
425 l_transTxtValues := l_emptyTxtValues;
426
427 l_numValuesCount := 0;
428 l_txtValuesCount := 0;
429 l_transTxtValuesCount := 0;
430
431
432 END IF;
433
434
435 CASE descriptor.datatype
436 WHEN 'NUM' THEN
437 l_numValuesCount := l_numValuesCount + 1;
438 l_numValues(l_numValuesCount) := to_number(descriptor.value);
439 l_po_attr_values_cols := l_po_attr_values_cols || ', ' || descriptor.stored_in_column;
440 l_po_attr_values_vals := l_po_attr_values_vals || ', ' || ':' || to_char(l_numValuesCount + l_num_txt_offset);
441
442 WHEN 'TXT' THEN
443 l_txtValuesCount := l_txtValuesCount + 1;
444 l_txtValues(l_txtValuesCount) := descriptor.value;
445 l_po_attr_values_cols := l_po_attr_values_cols || ', ' || descriptor.stored_in_column;
446 l_po_attr_values_vals := l_po_attr_values_vals || ', ' || ':' || to_char(l_txtValuesCount + l_num_txt_offset + l_numValuesCount);
447
448 WHEN 'TRANS' THEN
449 l_transTxtValuesCount := l_transTxtValuesCount + 1;
450 l_transTxtValues(l_transTxtValuesCount) := descriptor.value;
451 l_po_attr_values_tlp_cols := l_po_attr_values_tlp_cols || ', ' || descriptor.stored_in_column;
452 l_po_attr_values_tlp_vals := l_po_attr_values_tlp_vals || ', ' || ':' || to_char(l_transTxtValuesCount + l_trans_txt_offset);
453 ELSE
454 NULL;
455 END CASE;
456
457
458 END LOOP;
459 CLOSE c_descriptors;
460
461 IF DBMS_SQL.IS_OPEN(l_cursorName) THEN
462 DBMS_SQL.CLOSE_CURSOR(l_cursorName);
463 END IF;
464
465
466 END INSERT_UNSOL_IP_DESCRIPTORS;
467
468
469 PROCEDURE insert_unsol_lines_bpa(p_interface_header_id IN NUMBER,
470 p_auction_header_id IN NUMBER,
471 p_bid_number IN NUMBER,
472 p_document_number IN VARCHAR2,
473 p_is_fed IN VARCHAR2,
474 p_user_id IN NUMBER,
475 x_rows_processed IN OUT NOCOPY NUMBER) IS
476 BEGIN
477 INSERT into PO_LINES_INTERFACE (
478 interface_header_id,
479 interface_line_id,
480 requisition_line_id,
481 line_type_id,
482 line_num,
483 item_id,
484 item_revision,
485 category_id,
486 ip_category_id,
487 item_description,
488 unit_of_measure,
489 price_break_lookup_code,
490 quantity,
491 committed_amount,
492 unit_price,
493 min_release_amount,
494 ship_to_location_id,
495 need_by_date,
496 clm_period_perf_start_date,
497 clm_period_perf_end_date,
498 promised_date,
499 last_updated_by,
500 last_update_date,
501 created_by,
502 creation_date,
503 auction_header_id,
504 auction_display_number,
505 auction_line_number,
506 bid_number,
507 bid_line_number,
508 orig_from_req_flag,
509 --job_id,
510 amount,
511 line_num_display,
512 group_line_id,
513 clm_info_flag,
514 clm_option_indicator,
515 clm_option_num,
516 clm_option_from_date,
517 clm_option_to_date,
518 clm_funded_flag,
519 clm_base_line_num,
520 -- Complex Pricing Changes
521 CONTRACT_TYPE,
522 COST_CONSTRAINT,
523 CLM_IDC_TYPE,
524 from_header_id,
525 from_line_id
526
527 )
528 SELECT
529 p_interface_header_id, -- interface_header_id
530 po_lines_interface_s.nextval, -- interface_line_id
531 NULL, -- requisition_line_id
532 pbip.line_type_id, -- line_type_id
533 x_rows_processed + rownum, -- line num
534 null, -- item_id
535 null, -- item_revision
536 pbip.category_id, -- category_id
537 null, -- ip category id
538 substrb(pbip.item_description, 1, 240),
539 -- item_description
540 decode(pbip.order_type_lookup_code, 'AMOUNT', null, mtluom.unit_of_measure),
541 -- unit_of_measure
542 decode(pbip.price_break_type, 'NONE', null, 'NON-CUMULATIVE', 'NON CUMULATIVE', pbip.price_break_type),
543 -- price_break_type
544 decode(pbip.order_type_lookup_code,
545 'AMOUNT', Decode(Nvl(p_is_fed,'N'),'Y',pbip.bid_currency_unit_price,NULL),
546 'RATE', NULL,
547 'FIXED PRICE', NULL,
548 pbip.award_quantity), -- quantity
549 decode(pbip.order_type_lookup_code,
550 'AMOUNT', pbip.bid_currency_unit_price,null), -- committed_amount
551 decode(pbip.order_type_lookup_code,
552 'AMOUNT', 1,
553 'FIXED PRICE', null, pbip.bid_currency_unit_price), --unit_price
554 decode(pbip.order_type_lookup_code,
555 'AMOUNT', 1,
556 'FIXED PRICE', null,
557 pbip.po_bid_min_rel_amount), -- min_release_amount
558 pbip.ship_to_location_id, -- ship_to_location_id
559 null, -- need_by_date
560 Decode(Nvl(p_is_fed,'N'),'Y',pbip.promise_pop_start_date,null), -- period_of_performance_start_date
561 Decode(Nvl(p_is_fed,'N'),'Y',pbip.promise_pop_end_date,null), -- period_of_performance_end_date
562 pbip.promised_date, -- promised_date
563 p_user_id, -- last_update_by
564 sysdate, -- last_update_date
565 p_user_id, -- created_by
566 sysdate, -- creation_date
567 p_auction_header_id, -- auction_header_id
568 p_document_number, -- document_number
569 pbip.line_number, -- auction_line_number,
570 pbip.bid_number, -- bid_number
571 pbip.line_number, -- bid_line_number
572 'N', -- orig_from_req_flag
573 --paip.job_id, -- job_id
574 decode(pbip.order_type_lookup_code,
575 'FIXED PRICE', round(pbip.bid_currency_unit_price, fc.precision),
576 null) -- amount
577 -- Clin Slin Changes
578 , pbip.line_num_display
579 , pbip.group_line_id
580 , pbip.clm_info_flag
581 , pbip.clm_option_indicator
582 , pbip.clm_option_num
583 , pbip.clm_option_from_date
584 , pbip.clm_option_to_date
585 , pbip.clm_funded_flag
586 , pbip.clm_base_line_num
587 -- Complex Pricing Changes
588 , pbip.CLM_CONTRACT_TYPE
589 , pbip.CLM_COST_CONSTRAINT
590 , pbip.CLM_IDC_TYPE
591 --CLM Order Off IDV Project
592 , pbh.idv_header_id
593 , pbip.idv_line_id
594 FROM pon_bid_headers pbh,
595 pon_bid_item_prices pbip,
596 mtl_units_of_measure mtluom,
597 fnd_currencies fc
598 WHERE
599 pbh.bid_number = p_bid_number AND
600 pbh.auction_header_id = p_auction_header_id AND
601 pbip.bid_number = pbh.bid_number AND
602 pbip.auction_line_number = -1 AND
603 nvl(pbip.award_status, 'NO') = 'AWARDED' and
604 pbip.uom = mtluom.uom_code (+) and
605 fc.currency_code = pbh.bid_currency_code;
606
607 x_rows_processed := SQL%ROWCOUNT;
608
609 -- If unsol lines are inserted, update group_line_id's accordingly.
610 IF Nvl(p_is_fed,'N') = 'Y' AND x_rows_processed > 0 THEN
611 UPDATE po_lines_interface PLI1
612 SET PLI1.group_line_id = (select PLI2.interface_line_id from po_lines_interface PLI2
613 where PLI2.interface_header_id = p_interface_header_id
614 and PLI2.auction_header_id = p_auction_header_id
615 and PLI2.auction_line_number = PLI1.group_line_id
616 AND PLI2.group_line_id IS NULL )
617 where PLI1.group_line_id is not null
618 and PLI1.interface_header_id = p_interface_header_id
619 and PLI1.auction_header_id = p_auction_header_id;
620 END IF;
621
622 IF x_rows_processed > 0 THEN
623 INSERT_UNSOL_IP_DESCRIPTORS(p_auction_header_id,
624 p_bid_number,
625 p_interface_header_id,
626 p_user_id,
627 fnd_global.login_id);
628 END IF;
629
630
631 EXCEPTION
632 WHEN No_Data_Found THEN
633 x_rows_processed := 0;
634 WHEN OTHERS THEN
635 RAISE;
636
637
638 END insert_unsol_lines_bpa;
639
640 END PON_UNSOL_CREATE_PO_PKG;