[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pll.price_override,
pll.line_location_id -- SERVICES FPJ
FROM po_line_locations_all pll, -- GA FPI
po_headers_all poh -- 5684820
/*
Bug fix for 2687718.
Added QUOTATION in the WHERE clause to ensure that pricing works when
a Standard PO is sourced to a Quotation through the Supplier Catalog.
*/
WHERE pll.shipment_type IN ('PRICE BREAK', 'QUOTATION')
AND pll.po_line_id = p_po_line_id
AND pll.po_header_id = poh.po_header_id -- 5684820
--bug #2696731 arusingh
AND nvl(pll.quantity, 0) <= nvl(test_quantity, 0)
-- bug #2696731: modified org/loc checks to remove match_type
AND ((p_ship_to_org = pll.ship_to_organization_id) OR
(pll.ship_to_organization_id IS NULL))
AND ((p_ship_to_loc = pll.ship_to_location_id) OR
(pll.ship_to_location_id IS NULL))
AND (nvl(trunc(l_pricing_date), trunc(SYSDATE)) >= trunc(pll.start_date) -- FPJ Custom Price
OR
pll.start_date IS NULL)
AND (nvl(trunc(l_pricing_date), trunc(SYSDATE)) <= trunc(pll.end_date) -- FPJ Custom Price
OR
pll.end_date IS NULL)
-- Begin 5684820
AND ((pll.shipment_type = 'QUOTATION' AND
(EXISTS (SELECT 1
FROM po_quotation_approvals_all
WHERE line_location_id = pll.line_location_id
AND SYSDATE BETWEEN nvl(start_date_active, SYSDATE - 1)
AND nvl(end_date_active, SYSDATE + 1)
)
AND nvl(poh.APPROVAL_REQUIRED_FLAG, 'N') = 'Y'
)
OR
nvl(poh.APPROVAL_REQUIRED_FLAG, 'N') = 'N'
)
OR pll.shipment_type ='PRICE BREAK')
-- End 5684820
ORDER BY pll.ship_to_organization_id ASC, pll.ship_to_location_id ASC,
NVL(pll.quantity, 0) DESC, -- to obtain the best exact matches
trunc(pll.creation_date) DESC, pll.price_override ASC; -- to sort the matches by creation date and then by price
SELECT ph.type_lookup_code,
pl.po_header_id
INTO l_source_document_type,
l_source_document_header_id
FROM po_headers_all ph,
po_lines_all pl
WHERE ph.po_header_id = pl.po_header_id
AND pl.po_line_id = p_po_line_id;
SELECT ph.type_lookup_code,
ph.po_header_id
INTO l_source_document_type,
l_source_document_header_id
FROM po_headers_all ph
WHERE ph.po_header_id = p_contract_id;
/* Call get_release_quantity to a) determine how to select
** the correct break price and b) the quantity released against
** the corresponding shipment/organization combination.
*/
release_to_date := PO_SOURCING2_SV.get_release_quantity(p_ship_to_org,
p_ship_to_loc,
p_po_line_id,
match_type);
SELECT nvl(quantity, 0)
INTO old_quantity
FROM po_line_locations
WHERE line_location_id = p_line_location_id;
/* Select the next applicable price break for the designated
** quantity if we know there is a matching price break.
*/
IF (NVL(p_cum_flag, FALSE) = FALSE) --
OR (match_type <> 'NONE') THEN
l_progress := '100';
SELECT pl.unit_price
INTO l_price /* */
FROM po_lines_all pl -- GA FPI
WHERE pl.po_line_id = p_po_line_id;
SELECT 'Y' INTO x_from_advanced_pricing
FROM qp_ldets_v
WHERE line_index = 1
AND LIST_LINE_TYPE_CODE = 'PLL';
/* Define a cursor to select the distinct ship-to
** organization and location combinations in the
** price breaks for the designated agreement
** line.
*/
CURSOR C1 IS
SELECT DISTINCT nvl(pll.ship_to_organization_id, - 1),
nvl(pll.ship_to_location_id, - 1)
FROM po_line_locations_all pll -- GA FPI
/*
Bug fix for 2687718.
Added QUOTATION in the WHERE clause to ensure that pricing works when
a Standard PO is sourced to a Quotation through the Supplier Catalog.
*/
WHERE pll.shipment_type IN ('PRICE BREAK', 'QUOTATION')
AND pll.po_line_id = x_po_line_id;
SELECT SUM(pll.quantity - nvl(pll.quantity_cancelled, 0))
INTO temp_quantity
FROM po_line_locations pll
WHERE decode(price_break_org, -1, pll.ship_to_organization_id,
price_break_org) = pll.ship_to_organization_id
AND decode(price_break_loc, -1, pll.ship_to_location_id,
price_break_loc) = pll.ship_to_location_id
AND pll.shipment_type <> 'PRICE BREAK'
AND pll.po_line_id = x_po_line_id;
SELECT decode(msi.mrp_planning_code, 3, 'Y', 4, 'Y', 7, 'Y', 8, 'Y', 9, 'Y',
decode(msi.inventory_planning_code, 1, 'Y', 2, 'Y', 'N'))
, msi.list_price_per_unit
, msi.primary_unit_of_measure
INTO X_planned_item_flag
, X_list_price
, X_primary_uom
FROM mtl_system_items msi
WHERE msi.inventory_item_id = X_item_id
AND msi.organization_id = X_org_id;
PROCEDURE NAME: update_line_price()
This procedure updates the line price of a document.
===========================================================================*/
--
PROCEDURE update_line_price
(
p_po_line_id IN NUMBER
, p_price IN NUMBER
, p_from_line_location_id IN NUMBER --
)
IS
BEGIN
update_line_price
(p_po_line_id => p_po_line_id
, p_price => p_price
, p_base_unit_price => p_price
, p_from_line_location_id => p_from_line_location_id
);
END update_line_price;
PROCEDURE update_line_price
(
p_po_line_id IN NUMBER
, p_price IN NUMBER
, p_base_unit_price IN NUMBER --
, p_from_line_location_id IN NUMBER --
)
IS
g_user_id NUMBER := fnd_global.user_id;
UPDATE po_lines_all
SET unit_price = p_price,
base_unit_price = p_base_unit_price, --
from_line_location_id = p_from_line_location_id, --
last_update_date = SYSDATE,
last_updated_by = g_user_id
WHERE po_line_id = p_po_line_id;
po_message_s.sql_error('update_line_price', '', SQLCODE);
END update_line_price;
PROCEDURE NAME: update_shipment_price()
This procedure updates the shipment price of a document.
===========================================================================*/
PROCEDURE update_shipment_price(p_price IN NUMBER,
p_line_location_id IN NUMBER) IS
g_user_id NUMBER := fnd_global.user_id;
UPDATE po_line_locations
SET price_override = p_price,
last_update_date = SYSDATE,
last_updated_by = g_user_id
WHERE line_location_id = p_line_location_id;
po_message_s.sql_error('update_shipment_price', '', SQLCODE);
END update_shipment_price;
SELECT MIN(shipment_num)
INTO x_min_shipment_num
FROM po_line_locations_all
WHERE po_line_id = p_po_line_id
AND nvl(cancel_flag, 'N') = 'N'
AND nvl(closed_code, 'OPEN') <> 'FINALLY CLOSED';
SELECT poll.ship_to_location_id, poll.ship_to_organization_id, poll.need_by_date, poll.line_location_id
INTO l_ship_to_location_id, l_ship_to_organization_id, l_need_by_date, l_line_location_id
FROM po_line_locations_all poll
WHERE poll.po_line_id = p_po_line_id
AND poll.shipment_num = p_min_shipment_num;