[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_allow_item_desc_update mtl_system_items.allow_item_desc_update_flag%TYPE;
Select datatype_id, media_id
from fnd_attached_docs_form_vl
where entity_name = v_entity_name
and pk1_value = to_char(v_document_id) /* Bug 5964375 */
and function_name = 'PO_PRINTPO'
and datatype_id in (1,2)
and media_id is not null
order by seq_num;
Select datatype_id, media_id
from fnd_attached_docs_form_vl fad, financials_system_parameters fsp
where entity_name = v_entity_name
and pk1_value = to_char(v_document_id) /* Bug 5964375 */
and function_name = 'PO_PRINTPO'
and datatype_id in (1,2)
and media_id is not null
AND (publish_flag = 'Y'
--Security level is Organization
OR (security_type = 1 AND security_id = fsp.org_id)
--Security level is Set Of Books
OR (security_type = 2 AND security_id = fsp.set_of_books_id)
--Security level is NONE
OR (security_type = 4)
)
order by fad.seq_num;
SELECT datatype_id,
media_id
FROM fnd_attached_docs_form_vl
WHERE entity_name = 'MTL_SYSTEM_ITEMS' AND
pk1_value = to_char(v_org_id) AND
pk2_value = to_char(v_item_id) AND
function_name = 'PO_PRINTPO' and
datatype_id in (1,2) and
media_id is not null
order by seq_num;
SELECT pol.line_num,
pll.po_line_id,
msi.concatenated_segments,
pol.item_revision,
pol.vendor_product_num,
pol.item_description,
umvl.unit_of_measure_tl, /* Bug 2701946 */
pll.quantity,
pol.unit_price,
pll.need_by_date,
pll.promised_date,
pll.taxable_flag,
pol.note_to_vendor,
pol.un_number_id,
pol.hazard_class_id,
pll.cancel_flag,
pll.cancel_date,
pll.quantity_cancelled,
pol.item_id,
fsp.inventory_organization_id org_id, /* Bug 3064519 */
pol.contract_num,
pll.line_location_id,
pll.ship_to_location_id,
pll.consigned_flag, --< SUP_CON FPI>
null, /* */
null, /* */
--
pol.from_header_id,
pol.from_line_id
--
FROM po_lines_all pol, -- --po_lines pol,
po_line_locations pll,
mtl_system_items_kfv msi,
mtl_units_of_measure_vl umvl,
financials_system_params_all fsp -- --financials_system_parameters fsp
where PLL.PO_HEADER_ID = v_document_id
and PLL.po_line_id = POL.po_line_id
and PLL.po_release_id is NULL /* Bug 4513703 */
and pol.item_id = msi.inventory_item_id(+)
and NVL(msi.organization_id, fsp.inventory_organization_id) = fsp.inventory_organization_id
and nvl(pol.cancel_flag,'N') = 'N'
and nvl(pll.unit_meas_lookup_code, pol.unit_meas_lookup_code) = umvl.unit_of_measure
and POL.org_id = FSP.org_id --
order by line_num asc;
SELECT pol.line_num,
pll.po_line_id,
msi.concatenated_segments,
pol.item_revision,
pol.vendor_product_num,
pol.item_description,
umvl.unit_of_measure_tl, /* Bug 2701946 */
pll.quantity,
nvl(pll.price_override, pol.unit_price) unit_price,
pll.need_by_date,
pll.promised_date,
pll.taxable_flag,
pol.note_to_vendor,
pol.un_number_id,
pol.hazard_class_id,
pll.cancel_flag,
pll.cancel_date,
pll.quantity_cancelled,
pol.item_id,
fsp.inventory_organization_id org_id, /* Bug 3064519 */
pol.contract_num,
pll.line_location_id,
pll.ship_to_location_id,
NULL, --
pll.start_date, /* */
pll.end_date, /* */
--
null,
null
--
FROM po_lines_all pol, -- --po_lines pol,
po_line_locations pll,
mtl_system_items_kfv msi,
mtl_units_of_measure_vl umvl,
financials_system_params_all fsp -- --financials_system_parameters fsp
where POL.PO_HEADER_ID = v_document_id
and POL.po_line_id = PLL.po_line_id(+)
and pol.item_id = msi.inventory_item_id(+)
and pll.shipment_type(+) = 'PRICE BREAK'
and NVL(msi.organization_id, fsp.inventory_organization_id) = fsp.inventory_organization_id
and nvl(pol.cancel_flag,'N') = 'N'
and nvl(pll.unit_meas_lookup_code, pol.unit_meas_lookup_code) = umvl.unit_of_measure
and POL.org_id = FSP.org_id --
order by line_num asc;
SELECT pol.line_num,
pol.po_line_id,
msi.concatenated_segments,
pol.item_revision,
pol.vendor_product_num,
pol.item_description,
umvl.unit_of_measure_tl, /* Bug 2701946 */
pol.quantity,
pol.unit_price,
null, --pol.need_by_date,
null, -- pol.promised_date,
null, --pll.taxable_flag,
pol.note_to_vendor,
pol.un_number_id,
pol.hazard_class_id,
pol.cancel_flag,
pol.cancel_date,
null, --pll.quantity_cancelled,
pol.item_id,
fsp.inventory_organization_id org_id, /* Bug 3064519 */
pol.contract_num,
null, --pll.line_location_id,
poh.ship_to_location_id,
NULL, --
null, /* */
null, /* */
--
null,
null
--
FROM po_lines pol,
po_headers_all poh, -- --po_headers poh,
mtl_system_items_kfv msi,
mtl_units_of_measure_vl umvl,
financials_system_params_all fsp -- --financials_system_parameters fsp
where POL.PO_HEADER_ID = v_document_id
and POL.po_header_id = POH.po_header_id
and pol.item_id = msi.inventory_item_id(+)
and NVL(msi.organization_id, fsp.inventory_organization_id) = fsp.inventory_organization_id
and nvl(pol.cancel_flag,'N') = 'N'
and pol.unit_meas_lookup_code = umvl.unit_of_measure
and POL.org_id = FSP.org_id --
order by line_num asc;
SELECT pol.line_num,
pll.po_line_id,
msi.concatenated_segments,
pol.item_revision,
pol.vendor_product_num,
pol.item_description,
umvl.unit_of_measure_tl, /* Bug 2701946 */
pll.quantity,
pll.price_override,
pll.need_by_date,
pll.promised_date,
pll.taxable_flag,
pol.note_to_vendor,
pol.un_number_id,
pol.hazard_class_id,
pll.cancel_flag,
pll.cancel_date,
pll.quantity_cancelled,
pol.item_id,
fsp.inventory_organization_id org_id, /* Bug 3064519 */
pol.contract_num,
pll.line_location_id,
pll.ship_to_location_id,
NULL, --
null, /* */
null, /* */
--
null,
null
--
FROM po_lines_all pol, -- --po_lines pol,
po_line_locations pll,
mtl_system_items_kfv msi,
mtl_units_of_measure_vl umvl,
financials_system_params_all fsp -- --financials_system_parameters fsp
where PLL.PO_RELEASE_ID = v_document_id
and PLL.po_line_id = POL.po_line_id
and pol.item_id = msi.inventory_item_id(+)
and NVL(msi.organization_id, fsp.inventory_organization_id) =
fsp.inventory_organization_id
and nvl(pol.cancel_flag,'N') = 'N'
and nvl(pll.unit_meas_lookup_code, pol.unit_meas_lookup_code) = umvl.unit_of_measure
and PLL.org_id = FSP.org_id --
order by line_num asc;
select distinct deliver_to_person_id
FROM po_distributions pdp
WHERE pdp.line_location_id = v_line_location_id;
select authorization_status
into l_document_status
from po_headers_all
where po_header_id = l_document_id;
select po_header_id into l_po_header_id from po_releases_all
where po_release_id = l_document_id;
select authorization_status
into l_document_status
from po_headers_all
where po_header_id = l_po_header_id;
select type_lookup_code
into x_subtype
from po_headers
where po_header_id = l_document_id;
select count(distinct pll.ship_to_location_id) into l_ship_to_count
from
po_lines_all pol, -- --po_lines pol,
po_line_locations pll
where PLL.PO_HEADER_ID = l_document_id
and PLL.po_line_id = POL.po_line_id;
select count(distinct pll.ship_to_location_id) into l_ship_to_count
FROM po_lines_all pol, -- --po_lines pol,
po_line_locations pll
where PLL.PO_RELEASE_ID = l_document_id
and PLL.po_line_id = POL.po_line_id;
select nvl(count(distinct pll.ship_to_location_id),0)
into l_ship_to_count
FROM po_lines_all pol, -- --po_lines pol,
po_line_locations pll
where PLL.PO_HEADER_ID = l_document_id
and PLL.po_line_id = POL.po_line_id
and PLL.shipment_type = 'PRICE BREAK';
select count(*) into x_pb_count
from po_line_locations
where po_header_id = l_document_id;
select org_id, note_to_vendor ,currency_code
into l_org_id, l_header_note_to_vendor , l_currency_code
from po_headers
where po_header_id = to_number(l_document_id);
select org_id, note_to_vendor ,po_header_id
into l_org_id, l_header_note_to_vendor , l_po_header_id
from po_releases
where po_release_id = to_number(l_document_id);
select currency_code into l_currency_code
from po_headers
where po_header_id = l_po_header_id;
select org_id, note_to_vendor ,currency_code, blanket_total_amount
into l_org_id, l_header_note_to_vendor ,l_currency_code, l_blanket_total_amount
from po_headers
where po_header_id = to_number(l_document_id);
select short_text into l_text from fnd_documents_short_text
where media_id = l_media_id;
select long_text into l_long_text from fnd_documents_long_text
where media_id = l_media_id;
select po_header_id into l_po_header_id from po_releases
where po_release_id = l_document_id;
select short_text into l_text from fnd_documents_short_text
where media_id = l_media_id;
select long_text into l_long_text from fnd_documents_long_text
where media_id = l_media_id;
the select statement */
-- Bug 3574886: Query from base tables in case the session context is not set correctly
-- when this SQL is executed; fetch translated columns from hr_locations_all_tl
select distinct
hlt.location_code,
hlt.description,
hrl.address_line_1,
hrl.address_line_2,
hrl.address_line_3,
hrl.town_or_city,
hrl.postal_code,
ftv.territory_short_name,
nvl(decode(hrl.region_1, null,
hrl.region_2, decode(flv1.meaning,null,
decode(flv2.meaning, null, flv3.meaning, flv2.lookup_code),
flv1.lookup_code)),
hrl.region_2)
into l_hrl_location,
l_hrl_description,
l_hrl_address_line_1,
l_hrl_address_line_2,
l_hrl_address_line_3,
l_hrl_town_or_city,
l_hrl_postal_code,
l_hrl_country,
l_hrl_to_region1
from hr_locations_all hrl,
hr_locations_all_tl hlt,
fnd_territories_vl ftv,
fnd_lookup_values_vl flv1,
fnd_lookup_values_vl flv2,
fnd_lookup_values_vl flv3
where hrl.region_1 = flv1.lookup_code (+)
and hrl.country || '_PROVINCE' = flv1.lookup_type (+)
and hrl.location_id = hlt.location_id and hlt.language = USERENV('LANG')
and hrl.region_2 = flv2.lookup_code (+)
and hrl.country || '_STATE' = flv2.lookup_type (+)
and hrl.region_1 = flv3.lookup_code (+)
and hrl.country || '_COUNTY' = flv3.lookup_type (+)
and hrl.country = ftv.territory_code (+)
and hrl.location_id = l_line_loc.ship_to_location_id;
select description, address1,
address2, address3, city, postal_code,
country
into l_hrl_description,
l_hrl_address_line_1, l_hrl_address_line_2,
l_hrl_address_line_3, l_hrl_town_or_city,
l_hrl_postal_code, l_hrl_country
from hz_locations
where location_id = l_line_loc.ship_to_location_id;
select un_number into l_un_number from po_un_numbers pun
where pun.un_number_id = l_line_loc.un_number_id;
select hazard_class into l_hazard_class
from po_hazard_classes phc
where phc.hazard_class_id = l_line_loc.hazard_class_id;
select allow_item_desc_update_flag into l_allow_item_desc_update
from mtl_system_items_vl
where inventory_item_id = l_line_loc.item_id
and organization_id = l_org_id;
if (l_allow_item_desc_update = 'N') then
select description into l_mtl_system_items_desc
from mtl_system_items_vl
where inventory_item_id = l_line_loc.item_id
and organization_id = l_org_id;
select short_text into l_item_short_text from fnd_documents_short_text
where media_id = l_media_id;
select long_text into l_item_long_text from fnd_documents_long_text
where media_id = l_media_id;
select short_text into l_text from fnd_documents_short_text
where media_id = l_media_id;
select long_text into l_long_text from fnd_documents_long_text
where media_id = l_media_id;
select nvl(global_agreement_flag, 'N') into l_global_flag
from po_headers_all where po_header_id = l_line_loc.from_header_id;
select short_text into l_text from fnd_documents_short_text
where media_id = l_media_id;
select long_text into l_long_text from fnd_documents_long_text
where media_id = l_media_id;
select short_text into l_text from fnd_documents_short_text
where media_id = l_media_id;
select long_text into l_long_text from fnd_documents_long_text
where media_id = l_media_id;
select vendor_quote_num into l_vendor_quote_num
from po_lines_print
where po_line_id = l_line_loc.po_line_id
and po_header_id = l_document_id;
select vendor_quote_num into l_vendor_quote_num
from po_lines_print
where po_line_id = l_line_loc.po_line_id
and po_release_id = l_document_id;
select po_quote_num, src_ga_flag
into l_po_quote_num, l_src_ga_flag
from po_lines_print
where po_line_id = l_line_loc.po_line_id
and po_header_id = l_document_id;
select po_quote_num into l_po_quote_num
from po_lines_print
where po_line_id = l_line_loc.po_line_id
and po_release_id = l_document_id;
select short_text into l_text from fnd_documents_short_text
where media_id = l_media_id;
select long_text into l_long_text from fnd_documents_long_text
where media_id = l_media_id;
select full_name,work_telephone,email_address
into l_requestor_name,l_phone,l_email_address
from per_all_people_f
where person_id = l_requestor_id
and effective_start_date <= sysdate
and effective_end_date >= sysdate;
select full_name,work_telephone,email_address
into l_requestor_name,l_phone,l_email_address
from per_all_people_f
where person_id = l_requestor_id
and effective_start_date <= sysdate
and effective_end_date >= sysdate;
select short_text into l_text from fnd_documents_short_text
where media_id = l_media_id;
select long_text into l_long_text from fnd_documents_long_text
where media_id = l_media_id;
select org_id, note_to_vendor,currency_code
into l_org_id, l_header_note_to_vendor ,l_currency_code
from po_headers
where po_header_id = to_number(l_document_id);
select org_id, note_to_vendor
into l_org_id, l_header_note_to_vendor
from po_releases where po_release_id = to_number(l_document_id);
select org_id, note_to_vendor,currency_code
into l_org_id, l_header_note_to_vendor ,l_currency_code
from po_headers
where po_header_id = to_number(l_document_id);
select short_text into l_text from fnd_documents_short_text
where media_id = l_media_id;
select long_text into l_long_text from fnd_documents_long_text
where media_id = l_media_id;
select po_header_id into l_po_header_id from po_releases
where po_release_id = l_document_id;
select short_text into l_text from fnd_documents_short_text
where media_id = l_media_id;
select long_text into l_long_text from fnd_documents_long_text
where media_id = l_media_id;
select un_number into l_un_number from po_un_numbers pun
where pun.un_number_id = l_line_loc.un_number_id;
select hazard_class into l_hazard_class
from po_hazard_classes phc
where phc.hazard_class_id = l_line_loc.hazard_class_id;
select short_text into l_item_short_text from fnd_documents_short_text
where media_id = l_media_id;
select long_text into l_item_long_text from fnd_documents_long_text
where media_id = l_media_id;
select short_text into l_text from fnd_documents_short_text
where media_id = l_media_id;
select long_text into l_long_text from fnd_documents_long_text
where media_id = l_media_id;
select nvl(global_agreement_flag, 'N') into l_global_flag
from po_headers_all where po_header_id = l_line_loc.from_header_id;
select short_text into l_text from fnd_documents_short_text
where media_id = l_media_id;
select long_text into l_long_text from fnd_documents_long_text
where media_id = l_media_id;
select short_text into l_text from fnd_documents_short_text
where media_id = l_media_id;
select long_text into l_long_text from fnd_documents_long_text
where media_id = l_media_id;
select vendor_quote_num into l_vendor_quote_num
from po_lines_print
where po_line_id = l_line_loc.po_line_id;
select po_quote_num into l_po_quote_num
from po_lines_print
where po_line_id = l_line_loc.po_line_id;
select short_text into l_text from fnd_documents_short_text
where media_id = l_media_id;
select long_text into l_long_text from fnd_documents_long_text
where media_id = l_media_id;
select full_name,work_telephone,email_address
into l_requestor_name,l_phone,l_email_address
from per_all_people_f
where person_id = l_requestor_id
and effective_start_date <= sysdate
and effective_end_date >= sysdate;
select distinct deliver_to_person_id
FROM po_distributions
WHERE po_header_id = v_po_header_id
AND distribution_type <> 'AGREEMENT'; --
select distinct deliver_to_person_id
FROM po_distributions
WHERE po_release_id = v_po_release_id;
select distinct PLL.ship_to_location_id
FROM po_lines_all pol, -- --po_lines pol,
po_line_locations pll
where PLL.PO_RELEASE_ID = to_number(v_po_release_id)
and PLL.po_line_id = POL.po_line_id;
select nvl(count(distinct pll.ship_to_location_id), 0)
FROM po_lines_all pol, --
po_line_locations pll
where PLL.PO_HEADER_ID = to_number(v_po_header_id)
and PLL.po_line_id = POL.po_line_id
and PLL.shipment_type = 'PRICE BREAK';
select ship_to_location_id
FROM po_headers
where PO_HEADER_ID = to_number(v_po_header_id);
select distinct pll.ship_to_location_id
FROM po_lines_all pol, --
po_line_locations pll
where PLL.PO_HEADER_ID = to_number(v_po_header_id)
and PLL.po_line_id = POL.po_line_id
and PLL.shipment_type = 'PRICE BREAK';
select distinct PLL.ship_to_location_id
FROM po_lines_all pol, --
po_line_locations pll
where PLL.PO_HEADER_ID = to_number(v_po_header_id)
and PLL.po_line_id = POL.po_line_id;
select area_code || phone
from po_vendor_contacts
where vendor_contact_id = v_vendor_contact_id;
select authorization_status
into l_document_status
from po_headers_all
where po_header_id = l_document_id;
select po_header_id into l_po_header_id from po_releases_all
where po_release_id = l_document_id;
select authorization_status
into l_document_status
from po_headers_all
where po_header_id = l_po_header_id;
select type_lookup_code
into x_subtype
from po_headers
where po_header_id = l_document_id;
select org_id into l_operating_unit_id from po_headers_all
where po_header_id = l_document_id;
select full_name,work_telephone,email_address
into l_deliver_to_person,l_phone,l_email_address
from per_all_people_f
where person_id = l_deliver_to_id
and effective_start_date <= sysdate
and effective_end_date >= sysdate;
the select statement. */
-- Bug 3574886: Query from base tables in case the session context is not set correctly
-- when this SQL is executed; fetch translated columns from hr_locations_all_tl
select distinct hlt.description,
hrl.address_line_1,
hrl.address_line_2,
hrl.address_line_3,
hrl.town_or_city,
hrl.postal_code,
ftv.territory_short_name,
nvl(decode(hrl.region_1, null,
hrl.region_2, decode(flv1.meaning,null,
decode(flv2.meaning,null,flv3.meaning,flv2.lookup_code),
flv1.lookup_code)),
hrl.region_2)
into
l_ship_to_desc,
l_ship_to_add1,
l_ship_to_add2,
l_ship_to_add3,
l_ship_to_city,
l_ship_to_postal_code,
l_ship_to_country,
l_ship_to_region2
FROM hr_locations_all hrl,
hr_locations_all_tl hlt,
fnd_territories_vl ftv,
fnd_lookup_values_vl flv1,
fnd_lookup_values_vl flv2,
fnd_lookup_values_vl flv3
where hrl.region_1 = flv1.lookup_code (+)
and hrl.country || '_PROVINCE' = flv1.lookup_type (+)
and hrl.location_id = hlt.location_id
and hlt.language = USERENV('LANG')
and hrl.region_2 = flv2.lookup_code (+)
and hrl.country || '_STATE' = flv2.lookup_type (+)
and hrl.region_1 = flv3.lookup_code (+)
and hrl.country || '_COUNTY' = flv3.lookup_type (+)
and hrl.country = ftv.territory_code(+)
and HRL.location_id = l_location_id;
select distinct
hrl.description,
hrl.address1,
hrl.address2,
hrl.address3,
hrl.city,
hrl.postal_code,
hrl.country
into
l_ship_to_desc,
l_ship_to_add1,
l_ship_to_add2,
l_ship_to_add3,
l_ship_to_city,
l_ship_to_postal_code,
l_ship_to_country
FROM hz_locations hrl
where HRL.location_id = l_location_id;
select po_header_id into l_po_header_id from po_releases
where po_release_id = l_document_id;
select org_id into l_operating_unit_id from po_headers
where po_header_id = l_po_header_id;
select full_name,work_telephone,email_address
into l_deliver_to_person,l_phone,l_email_address
from per_all_people_f
where person_id = l_deliver_to_id
and effective_start_date <= sysdate
and effective_end_date >= sysdate;
the select statement. */
-- Bug 3574886: Query from base tables in case the session context is not set correctly
-- when this SQL is executed; fetch translated columns from hr_locations_all_tl
select distinct hlt.description,
hrl.address_line_1,
hrl.address_line_2,
hrl.address_line_3,
hrl.town_or_city,
hrl.postal_code,
ftv.territory_short_name,
nvl(decode(hrl.region_1, null,
hrl.region_2, decode(flv1.meaning,null,
decode(flv2.meaning,null,flv3.meaning,flv2.lookup_code),
flv1.lookup_code)),
hrl.region_2)
into
l_ship_to_desc,
l_ship_to_add1,
l_ship_to_add2,
l_ship_to_add3,
l_ship_to_city,
l_ship_to_postal_code,
l_ship_to_country,
l_ship_to_region2
FROM hr_locations_all hrl,
hr_locations_all_tl hlt,
fnd_territories_vl ftv,
fnd_lookup_values_vl flv1,
fnd_lookup_values_vl flv2,
fnd_lookup_values_vl flv3
where hrl.region_1 = flv1.lookup_code (+)
and hrl.country || '_PROVINCE' = flv1.lookup_type (+)
and hrl.location_id = hlt.location_id
and hlt.language = USERENV('LANG')
and hrl.region_2 = flv2.lookup_code (+)
and hrl.country || '_STATE' = flv2.lookup_type (+)
and hrl.region_1 = flv3.lookup_code (+)
and hrl.country || '_COUNTY' = flv3.lookup_type (+)
and hrl.country = ftv.territory_code(+)
and hrl.location_id = l_location_id;
select distinct
hrl.description,
hrl.address1,
hrl.address2,
hrl.address3,
hrl.city,
hrl.postal_code,
hrl.country
into
l_ship_to_desc,
l_ship_to_add1,
l_ship_to_add2,
l_ship_to_add3,
l_ship_to_city,
l_ship_to_postal_code,
l_ship_to_country
FROM hz_locations hrl
where HRL.location_id = l_location_id;
select bill_to_location_id, vendor_site_id, vendor_id, segment1,
revision_num, to_char(creation_date), to_char(revised_date),
ship_via_lookup_code, freight_terms_lookup_code, FOB_LOOKUP_CODE,
nvl(vendor_contact_id, -99), terms_id,start_date,end_date,blanket_total_amount
into l_bill_to_id, l_vendor_site_id, l_vendor_id,
l_po_number, l_po_revision, l_date_of_order, l_date_of_revision,
l_ship_via_lookup_code, l_freight_terms_lc, l_fob_lookup_code, l_vendor_contact_id,
l_payment_terms_id,l_start_date,l_end_date,l_blanket_amt_agreed
from po_headers
where po_header_id = to_number(l_po_header_id);
select freight_code_tl into l_ship_via_lookup_desc
from org_freight_vl
where organization_id = x_orgid
and freight_code = l_ship_via_lookup_code;
select last_name || ',' || first_name into l_vendor_contact_name from
po_vendor_contacts where vendor_contact_id = l_vendor_contact_id;
select displayed_field
into l_fob_lookup_desc
from po_lookup_codes
where lookup_type = 'FOB'
and lookup_code = l_fob_lookup_code;
select release_num, revised_date, revision_num
into l_release_num, l_date_of_revision, l_po_revision
from po_releases
where po_release_id = l_document_id;
select to_char(release_date)
into l_date_of_order
from po_releases
where po_release_id = l_document_id;
select name
into l_company_name
from hr_all_organization_units
where organization_id = (select to_number(org_information2)
from hr_organization_information
where org_information_context = 'Operating Unit Information'
and organization_id = l_operating_unit_id);
the select statement. */
select distinct houv.name, houv.address_line_1, houv.address_line_2, houv.address_line_3,
houv.town_or_city,
nvl(decode(houv.region_1,
null, houv.region_2,
decode(flv1.meaning,null, decode(flv2.meaning,null,flv3.meaning,flv2.lookup_code),flv1.lookup_code))
,houv.region_2),
houv.postal_code, ftv.territory_short_name
into
l_operating_unit_desc, l_operating_unit_add1,
l_operating_unit_add2, l_operating_unit_add3, l_operating_unit_city,
l_operating_unit_state,l_operating_unit_postal_code, l_operating_unit_country
from hr_organization_units_v houv, fnd_territories_vl ftv, fnd_lookup_values_vl flv1, fnd_lookup_values_vl flv2,
fnd_lookup_values_vl flv3
where
houv.region_1 = flv1.lookup_code (+) and houv.country || '_PROVINCE' = flv1.lookup_type (+)
and houv.region_2 = flv2.lookup_code (+) and houv.country || '_STATE' = flv2.lookup_type (+)
and houv.region_1 = flv3.lookup_code (+) and houv.country || '_COUNTY' = flv3.lookup_type (+)
and houv.country = ftv.territory_code(+) and organization_id = l_operating_unit_id;
the select statement. */
begin
-- Bug 3574886: Query from base tables in case the session context is not set correctly
-- when this SQL is executed; fetch translated columns from hr_locations_all_tl
select distinct hlt.description,
hrl.address_line_1,
hrl.address_line_2,
hrl.address_line_3,
hrl.town_or_city,
ftv.territory_short_name,
hrl.postal_code,
nvl(decode(hrl.region_1, null,
hrl.region_2, decode(flv1.meaning, null,
decode(flv2.meaning,null,flv3.meaning,flv2.lookup_code),
flv1.lookup_code)),
hrl.region_2)
into l_bill_to_desc,
l_bill_to_add1,
l_bill_to_add2,
l_bill_to_add3,
l_bill_to_city,
l_bill_to_country,
l_bill_to_postal_code,
l_bill_to_region2
from hr_locations_all hrl,
hr_locations_all_tl hlt,
fnd_territories_vl ftv,
fnd_lookup_values_vl flv1,
fnd_lookup_values_vl flv2,
fnd_lookup_values_vl flv3
where hrl.region_1 = flv1.lookup_code (+)
and hrl.country || '_PROVINCE' = flv1.lookup_type (+)
and hrl.location_id = hlt.location_id
and hlt.language = USERENV('LANG')
and hrl.region_2 = flv2.lookup_code (+)
and hrl.country || '_STATE' = flv2.lookup_type (+)
and hrl.region_1 = flv3.lookup_code (+)
and hrl.country || '_COUNTY' = flv3.lookup_type (+)
and hrl.country = ftv.territory_code(+)
and hrl.location_id = l_bill_to_id;
the select statement. */
begin
select pvs.address_line1, pvs.address_line2, pvs.address_line3, pvs.city, nvl(nvl(pvs.state, pvs.county), pvs.province),
ftv.territory_short_name, zip
into
l_vendor_add1, l_vendor_add2, l_vendor_add3, l_vendor_city, l_vendor_state,
l_vendor_country, l_vendor_zip
from po_vendor_sites pvs , fnd_territories_vl ftv
where
ftv.territory_code(+) = nvl(pvs.country_of_origin_code, pvs.country)
and vendor_site_id = l_vendor_site_id;
select full_name into l_buyer
from per_all_people_f --
where person_id = (select agent_id from po_headers
where po_header_id = to_number(l_po_header_id))
and trunc(sysdate) between trunc(effective_start_date) and trunc(effective_end_date); --Bug#5161502
select full_name into l_buyer
from per_all_people_f --
where person_id = (select agent_id from po_headers_archive
where po_header_id = to_number(l_po_header_id) and
revision_num = 0)
and trunc(sysdate) between trunc(effective_start_date) and trunc(effective_end_date); --Bug#5161502
select full_name into l_revision_buyer
from per_all_people_f --
where person_id = (select agent_id from po_headers_archive
where po_header_id = to_number(l_po_header_id) and
revision_num = l_po_revision)
and trunc(sysdate) between trunc(effective_start_date) and trunc(effective_end_date); --Bug#5161502
select full_name into l_buyer
from per_all_people_f --
where person_id = (select agent_id from po_releases_archive
where po_release_id = to_number(l_document_id) and
revision_num = 0)
and trunc(sysdate) between trunc(effective_start_date) and trunc(effective_end_date); --Bug#5161502
select full_name into l_revision_buyer
from per_all_people_f --
where person_id = (select agent_id from po_releases_archive
where po_release_id = to_number(l_document_id) and
revision_num = l_po_revision)
and trunc(sysdate) between trunc(effective_start_date) and trunc(effective_end_date); --Bug#5161502
select nvl(pvs.customer_num, pov.customer_num) into l_customer_acct_num
from po_vendor_sites pvs, po_vendors pov
where pvs.vendor_site_id = l_vendor_site_id
and pov.vendor_id = l_vendor_id;
select segment1, vendor_name
into l_vendor_num, l_vendor_desc
from po_vendors pov
where pov.vendor_id = l_vendor_id;
select name into l_payment_terms
from ap_terms_val_v apv
where apv.TERM_ID = l_payment_terms_id;
select type_lookup_code
into x_subtype
from po_headers
where po_header_id = l_document_id;
select authorization_status
into l_document_status
from po_headers_all
where po_header_id = l_document_id;
select po_header_id into l_po_header_id from po_releases
where po_release_id = l_document_id;
select authorization_status
into l_document_status
from po_headers_all
where po_header_id = l_po_header_id;