[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT poll.price_override
, round(poll.price_override * v_conversion_rate, l_base_curr_ext_precision )
, poh.rate_date
, poh.rate
, poh.currency_code
, poh.rate_type
, poll.price_discount
, poll.price_override
, decode( poll.line_location_id,
null, pol.unit_meas_lookup_code,
poll.unit_meas_lookup_code)
, poll.line_location_id -- SERVICES FPJ
FROM po_headers_all poh -- FPI GA
, po_lines_all pol -- FPI GA
, po_line_locations_all poll -- FPI GA
WHERE poh.po_header_id = p_source_document_header_id
and poh.po_header_id = pol.po_header_id
and pol.line_num = p_source_document_line_num
and pol.po_line_id = poll.po_line_id -- bug4043100
and ( p_required_currency is null
or poh.currency_code = p_required_currency )
and ( p_required_rate_type is null
or poh.rate_type = p_required_rate_type )
and nvl(poll.unit_meas_lookup_code, nvl(p_unit_of_measure,
pol.unit_meas_lookup_code))
= nvl(p_unit_of_measure, pol.unit_meas_lookup_code)
/* */
/*
Change sysdate to l_pricing_date in order to use the Need By Date
to determine the price.
*/
and (trunc(nvl(l_pricing_date, trunc(sysdate))) >= trunc(poll.start_date) -- FPJ Custom Price
OR
poll.start_date is null)
and (trunc(nvl(l_pricing_date, trunc(sysdate))) <= trunc(poll.end_date) -- FPJ Custom Price
OR
poll.end_date is null)
/* */
--Bug #2693408: added nvl clause to quantity check
and nvl(poll.quantity, 0) <= nvl(p_in_quantity, 0)
/* */
/*
Determining the price based on ship-to-location and destination organization
*/
and ((poll.ship_to_location_id = v_ship_to_location_id OR poll.ship_to_location_id is null)
AND
(poll.ship_to_organization_id = p_destination_org_id OR poll.ship_to_organization_id is null))
/* */
and poll.shipment_type in ('PRICE BREAK', 'QUOTATION')
-- <2721775 START>: Make sure Quotation Price Breaks are Approved.
--
-- bug4043100 - remove poll.shipment_type is null check
AND ( ( poll.shipment_type = 'PRICE BREAK' )
OR ( ( poll.shipment_type = 'QUOTATION' )
AND ( ( poh.approval_required_flag <> 'Y' )
OR ( EXISTS ( SELECT ('Price Break is Approved')
FROM po_quotation_approvals pqa
WHERE pqa.line_location_id = poll.line_location_id
AND pqa.approval_type IN ('ALL ORDERS', 'REQUISITIONS')
AND (start_date_active is null
OR trunc(nvl(l_pricing_date, sysdate)) >= start_date_active)
AND (end_date_active is null
OR trunc(nvl(l_pricing_date, sysdate)) <= end_date_active)
)))))
-- <2721775 END>
order by poll.ship_to_organization_id ASC, poll.ship_to_location_id ASC,
NVL(poll.quantity, 0) DESC,
trunc(poll.creation_date) DESC, poll.price_override ASC; /* */
SELECT nvl(FND.extended_precision,5)
INTO l_base_curr_ext_precision
FROM FND_CURRENCIES FND,
FINANCIALS_SYSTEM_PARAMETERS FSP,
GL_SETS_OF_BOOKS GSB
WHERE FSP.set_of_books_id = GSB.set_of_books_id AND
FND.currency_code = GSB.currency_code;
SELECT ph.type_lookup_code,
pl.po_line_id
INTO l_source_document_type,
l_source_document_line_id
FROM po_headers_all ph,
po_lines_all pl
WHERE ph.po_header_id = p_source_document_header_id
AND pl.po_header_id(+) = ph.po_header_id
AND pl.line_num(+) = p_source_document_line_num;
SELECT pri.rowid, pri.autosource_doc_header_id, pri.autosource_doc_line_num,
pri.quantity, pri.deliver_to_location_id, pri.currency_code,
pri.rate_type, pri.need_by_date, pri.destination_organization_id,
pri.unit_of_measure,
--
pri.org_id,
NULL requisition_header_id,
NULL requisition_line_id,
pri.creation_date,
pri.item_id,
pri.item_revision,
pri.category_id,
pri.line_type_id,
pri.suggested_vendor_item_num,
pri.suggested_vendor_id,
pri.suggested_vendor_site_id,
-- Bug 3343892
pri.base_unit_price
--
FROM po_requisitions_interface pri
WHERE pri.autosource_flag in ('Y', 'P')
AND pri.item_id is not NULL
AND pri.source_type_code = 'VENDOR'
AND pri.autosource_doc_header_id is not NULL
-- Bug 3417479
-- AND pri.autosource_doc_line_num is not NULL
AND pri.request_id = p_request_id;
UPDATE po_requisitions_interface pri
SET -- Bug 3417479, only set NOT NULL price
-- pri.unit_price = l_base_price_out,
-- pri.base_unit_price = l_base_unit_price_out, --
-- pri.currency_unit_price = l_currency_price_out,
pri.unit_price = NVL(l_base_price_out, pri.unit_price),
pri.base_unit_price = NVL(l_base_unit_price_out, pri.base_unit_price),
pri.currency_unit_price = NVL(l_currency_price_out, pri.currency_unit_price),
pri.currency_code = l_currency_code_out,
pri.rate_type = l_rate_type_out,
pri.rate_date = l_rate_date_out,
pri.rate = l_rate_out
WHERE pri.rowid = l_rowid;
SELECT currency_code ,
nvl(global_agreement_flag, 'N') ,
nvl(rate, 1) --
INTO l_currency_code ,
l_ga_flag ,
l_po_rate
FROM po_headers_all
WHERE po_header_id = p_po_header_id;
SELECT set_of_books_id
INTO l_sob_id
FROM financials_system_parameters;
SELECT default_rate_type
INTO l_rate_type
FROM po_system_parameters;
SELECT unit_price
INTO x_unit_price
FROM po_lines_all
WHERE po_header_id = p_po_header_id
AND line_num = p_po_line_num;