[Home] [Help]
224:
225: v_bidders_currency_rate NUMBER := 1;
226: v_att_category_id fnd_document_categories.category_id%TYPE;
227: v_seq_num fnd_attached_documents.seq_num%TYPE;
228: v_uom_code pon_auction_item_prices_all.uom_code%TYPE;
229: v_amount_based_lines NUMBER;
230:
231: v_contracts_doctype VARCHAR2(60);
232: v_return_status VARCHAR2(1);
236: v_supplier_site_id pon_bidding_parties.vendor_site_id%TYPE;
237: v_supplier_site_code pon_bidding_parties.vendor_site_code%TYPE;
238:
239: v_price_break_response pon_auction_headers_all.price_break_response%type;
240: v_price_break_type pon_auction_item_prices_all.price_break_type%type;
241: v_price_break_neg_flag pon_auction_item_prices_all.price_break_neg_flag%type;
242: v_price_tiers_indicator pon_auction_headers_all.price_tiers_indicator%type;
243:
244: l_is_super_large_neg VARCHAR2(1) := 'N';
237: v_supplier_site_code pon_bidding_parties.vendor_site_code%TYPE;
238:
239: v_price_break_response pon_auction_headers_all.price_break_response%type;
240: v_price_break_type pon_auction_item_prices_all.price_break_type%type;
241: v_price_break_neg_flag pon_auction_item_prices_all.price_break_neg_flag%type;
242: v_price_tiers_indicator pon_auction_headers_all.price_tiers_indicator%type;
243:
244: l_is_super_large_neg VARCHAR2(1) := 'N';
245: l_number_of_lines NUMBER := -1;
1305:
1306: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1307: fnd_log.string(fnd_log.level_statement,
1308: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1309: 'inserting into pon_auction_item_prices_all for auction_header_id (x_document_number) : ' || x_document_number);
1310: end if;
1311:
1312: -- Then insert into the items transaction table.
1313: -- Here we will initially set the has_shipments flag to 'N'
1311:
1312: -- Then insert into the items transaction table.
1313: -- Here we will initially set the has_shipments flag to 'N'
1314: -- and then do an update by checking if shipments are present
1315: INSERT INTO pon_auction_item_prices_all
1316: (auction_header_id,
1317: line_number,
1318: disp_line_number,
1319: last_amendment_update,
1451: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1452: ' Updating the shipments flag based on if there are shipments');
1453: end if;
1454:
1455: UPDATE pon_auction_item_prices_all
1456: SET has_shipments_flag = 'Y'
1457: WHERE (line_number) IN
1458: (SELECT interface_line_number
1459: FROM pon_auc_shipments_interface
1469: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1470: ' Updating the price differntials flag based on if their are price differentials at item level');
1471: end if;
1472:
1473: UPDATE pon_auction_item_prices_all
1474: SET has_price_differentials_flag = 'Y',
1475: differential_response_type = 'OPTIONAL'
1476: WHERE (line_number) IN
1477: (SELECT interface_line_number
1482: AND interface_shipment_number = -1)
1483: AND auction_header_id = x_document_number;
1484:
1485:
1486: --Update the attachment_flag in pon_auction_item_prices_all table for all the
1487: --items that have an attachment.
1488: -- Since attachments can come from either po line or item itself
1489: -- update the flag after attachments have been inserted.
1490: -- UPDATE pon_auction_item_prices_all
1486: --Update the attachment_flag in pon_auction_item_prices_all table for all the
1487: --items that have an attachment.
1488: -- Since attachments can come from either po line or item itself
1489: -- update the flag after attachments have been inserted.
1490: -- UPDATE pon_auction_item_prices_all
1491: -- SET attachment_flag = 'Y'
1492: -- WHERE auction_header_id = x_document_number
1493: -- AND line_number IN (SELECT interface_line_number
1494: -- FROM pon_attachments_interface
1554: SELECT order_type_lookup_code INTO v_order_type_lookup_code from pon_auc_items_interface where
1555: interface_auction_header_id = p_interface_id and interface_line_number = l_lineno_list(i);
1556: IF(v_order_type_lookup_code <> 'QUANTITY') THEN
1557: BEGIN
1558: UPDATE pon_auction_item_prices_all paip
1559: SET clm_amount =
1560: (SELECT N_EXT_ATTR20
1561: FROM pon_auction_item_prices_ext_b ext,po_uda_ag_template_usages usage
1562: WHERE ext.auction_header_id = paip.auction_header_id
1575: 'Exception in updating complex pricing extended price ');
1576: END IF;
1577: END;
1578: ELSE
1579: UPDATE pon_auction_Item_prices_all
1580: SET clm_unit_price = current_price,
1581: clm_amount = (Nvl(current_price,0)*Nvl(quantity,0))
1582: WHERE auction_header_id = x_document_number
1583: AND line_number = l_lineno_list(i);
1841: Sysdate, -- last_update_date
1842: g_header_rec.user_id, -- last_updated_by
1843: NULL, -- last_update_login
1844: seq_num, -- seq_num
1845: 'PON_AUCTION_ITEM_PRICES_ALL', -- entity_name
1846: x_document_number, -- pk1_value
1847: interface_line_number, -- pk2_value
1848: NULL, -- pk3_value
1849: NULL, -- pk4_value
1855: AND interface_line_number IS NOT NULL
1856: AND interface_line_number >= l_batch_start
1857: AND interface_line_number <= l_batch_end;
1858:
1859: --Update the attachment_flag in pon_auction_item_prices_all
1860: --table for all the items that have attachments.
1861:
1862: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1863: fnd_log.string(fnd_log.level_statement,
1861:
1862: if (fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
1863: fnd_log.string(fnd_log.level_statement,
1864: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1865: 'Updating the attachment_flag in pon_auction_item_prices_all table for all the items that have attachments.' );
1866: end if;
1867:
1868: UPDATE pon_auction_item_prices_all
1869: SET attachment_flag = 'Y'
1864: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1865: 'Updating the attachment_flag in pon_auction_item_prices_all table for all the items that have attachments.' );
1866: end if;
1867:
1868: UPDATE pon_auction_item_prices_all
1869: SET attachment_flag = 'Y'
1870: WHERE auction_header_id = x_document_number
1871: AND line_number IN (SELECT to_number(pk2_value)
1872: FROM fnd_attached_documents
1869: SET attachment_flag = 'Y'
1870: WHERE auction_header_id = x_document_number
1871: AND line_number IN (SELECT to_number(pk2_value)
1872: FROM fnd_attached_documents
1873: WHERE entity_name = 'PON_AUCTION_ITEM_PRICES_ALL'
1874: AND pk1_value = to_char(x_document_number))
1875: AND line_number >= l_batch_start
1876: AND line_number <= l_batch_end;
1877:
1926: 'pon.plsql.pon_sourcing_openapi_grp.create_draft_neg_interface_pvt',
1927: ' Before updating exhibit numbers');
1928: end if;
1929:
1930: UPDATE pon_auction_item_prices_all ai
1931: SET exhibit_number =
1932: (SELECT clm_exhibit_name FROM po_lines pl
1933: WHERE pl.po_header_id = ai.source_doc_id
1934: AND pl.po_line_id = ai.source_line_id)
1964: SYSDATE,
1965: paip.created_by,
1966: SYSDATE,
1967: paip.created_by
1968: FROM pon_auction_item_prices_all paip,
1969: po_exhibit_details_merge_v ped
1970: WHERE paip.auction_header_id = x_document_number
1971: AND ped.po_header_id = paip.source_doc_id
1972: AND ped.reference_line_id = paip.source_line_id
2277:
2278: SELECT
2279: COUNT(LINE_NUMBER) number_of_lines, MAX (DECODE (GROUP_TYPE, 'LOT_LINE', 0, 'GROUP_LINE', 0, SUB_LINE_SEQUENCE_NUMBER)) last_line_number
2280: INTO l_number_of_lines, l_last_line_number
2281: FROM PON_AUCTION_ITEM_PRICES_ALL
2282: WHERE
2283: AUCTION_HEADER_ID = x_document_number;
2284:
2285:
2987: document_disp_line_number
2988: into
2989: x_display_line_number
2990: from
2991: pon_auction_item_prices_all
2992: where
2993: auction_header_id = p_auction_header_id and
2994: line_number = p_auction_line_number;
2995:
2999: line_num_display
3000: into
3001: x_display_line_number
3002: from
3003: pon_auction_item_prices_all
3004: where
3005: auction_header_id = p_auction_header_id and
3006: line_number = p_auction_line_number;
3007: