[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;