The following lines contain the word 'select', 'insert', 'update' or 'delete':
select a.FLEX_VALUE_ID
from fnd_flex_values a, fnd_flex_value_sets b
where b.flex_value_set_name = 'OKC_XPRT_YES_NO'
and a.FLEX_VALUE_SET_ID = b.FLEX_VALUE_SET_ID
and a.FLEX_VALUE = p_yes_no;
SELECT
bh.agreement_id,
-- decode(nvl(to_char(bh.agreement_id),'X'),'X','N','Y') price_agr_exist,
decode(nvl(to_char(bh.agreement_id),'X'),'X',l_expert_no,l_expert_yes) price_agr_exist,
bh.sold_to_org_id,
bh.invoice_to_org_id,
bh.ship_to_org_id,
bh.sold_to_site_use_id,
bh.order_type_id,
-- decode(nvl(bh.cust_po_number,'X'),'X','N','Y') cust_po_num_exist,
decode(nvl(bh.cust_po_number,'X'),'X',l_expert_no,l_expert_yes) cust_po_num_exist,
bh.transactional_curr_code,
bh.freight_terms_code,
bh.shipping_method_code,
bh.payment_term_id,
bh.invoicing_rule_id,
bh.tax_exempt_flag,
bh.price_list_id,
bh.org_id,
bhe.blanket_min_amount,
bhe.blanket_max_amount,
-- XY
bh.order_number,
bh.cust_po_number,
bh.version_number,
bh.sold_to_contact_id,
bh.salesrep_id,
bhe.start_date_active,
bhe.end_date_active
-- XY
FROM
oe_blanket_headers_ext bhe,
oe_blanket_headers_all bh
WHERE
bh.order_number = bhe.order_number
AND bh.header_id = p_doc_id;
SELECT
oh.blanket_number,
-- decode(nvl(to_char(oh.blanket_number),'X'),'X','N','Y') blanket_number_exist,
decode(nvl(to_char(oh.blanket_number),'X'),'X',l_expert_no,l_expert_yes) blanket_number_exist,
oh.agreement_id,
-- decode(nvl(to_char(oh.agreement_id),'X'),'X','N','Y') price_agr_exist,
decode(nvl(to_char(oh.agreement_id),'X'),'X',l_expert_no,l_expert_yes) price_agr_exist,
oh.sold_to_org_id,
oh.invoice_to_org_id,
oh.ship_to_org_id,
oh.sold_to_site_use_id,
-- decode(nvl(oh.cust_po_number,'X'),'X','N','Y') cust_po_num_exist,
decode(nvl(oh.cust_po_number,'X'),'X',l_expert_no,l_expert_yes) cust_po_num_exist,
oh.transactional_curr_code,
oh.freight_terms_code,
oh.shipping_method_code,
oh.payment_term_id,
oh.invoicing_rule_id,
oh.org_id,
oh.order_type_id,
oh.fob_point_code,
oh.payment_type_code,
oh.end_customer_id,
-- decode(nvl(to_char(oh.end_customer_id),'X'),'X','N','Y') end_cust_exist,
decode(nvl(to_char(oh.end_customer_id),'X'),'X',l_expert_no,l_expert_yes) end_cust_exist,
oh.price_list_id,
oh.tax_exempt_flag,
oh.sales_channel_code,
oe_oe_totals_summary.prt_order_total(oh.header_id) total,
oe_oe_totals_summary.price_adjustments(oh.header_id) total_adjusted_amount,
oe_oe_totals_summary.get_order_amount(oh.header_id) total_list_price,
bh.header_id blanket_header_id,
-- XY
oh.order_number,
oh.quote_number,
oh.cust_po_number,
oh.version_number,
oh.sold_to_contact_id,
oh.salesrep_id,
-- XY
-- Begin: Added for resolving bug 5300044 and 5299978
oh.shipment_priority_code,
oh.minisite_id
-- End: Added for resolving bug 5300044 and 5299978
FROM
oe_blanket_headers_all bh,
oe_order_headers_all oh
WHERE
oh.blanket_number = bh.order_number(+)
AND bh.sales_document_type_code(+) = 'B'
AND oh.header_id = p_doc_id;
SELECT
hzp.category_code,
hzc.customer_class_code,
hzcp.profile_class_id,
hzcp.credit_rating,
hzcp.credit_classification,
hzcp.risk_code
FROM
hz_customer_profiles hzcp,
hz_cust_accounts hzc,
hz_parties hzp
WHERE
hzc.cust_account_id = hzcp.cust_account_id
AND hzcp.site_use_id is null
AND hzc.party_id = hzcp.party_id
AND hzc.party_id = hzp.party_id
AND hzc.cust_account_id = p_sold_to_org_id;
SELECT
loc.country
FROM
hz_locations loc,
hz_party_sites ps,
hz_cust_acct_sites cas,
hz_cust_site_uses su
WHERE
ps.location_id = loc.location_id
and cas.party_site_id = ps.party_site_id
and su.cust_acct_site_id = cas.cust_acct_site_id
and su.site_use_id = p_site_use_id;
SELECT
cas.cust_account_id
FROM
hz_cust_acct_sites cas,
hz_cust_site_uses su
WHERE
su.cust_acct_site_id = cas.cust_acct_site_id
and su.site_use_id = p_site_use_id;
SELECT
pc.agreement_type_code
FROM
oe_pricing_contracts_v pc
WHERE
pc.agreement_id = p_agreement_id;
SELECT item_identifier_type, --eg. INT
ordered_item, --eg. AS54888
ordered_item_id,
org_id,
inventory_item_id,
sold_to_org_id
FROM oe_blanket_lines_all
WHERE header_id = p_doc_id
AND p_doc_type = G_BSA_DOC_TYPE
AND item_identifier_type <> 'CAT'
AND line_category_code = 'ORDER'
UNION ALL
-- Get all the items of the Sales Order i.e. internal (INT) customer (CUST) etc.
-- Returns non-translatable code eg. AS54888
--
SELECT item_identifier_type, --eg. INT
ordered_item, --eg. AS54888
ordered_item_id,
org_id,
inventory_item_id,
sold_to_org_id
FROM oe_order_lines_all
WHERE header_id = p_doc_id
AND p_doc_type = G_SO_DOC_TYPE
AND item_identifier_type <> 'CAT'
AND line_category_code = 'ORDER'
ORDER BY ordered_item;
SELECT ordered_item
FROM oe_blanket_lines_all
WHERE header_id = p_doc_id
AND p_doc_type = G_BSA_DOC_TYPE
AND item_identifier_type = 'CAT'
AND line_category_code = 'ORDER'
ORDER BY ordered_item;
SELECT category_concat_segs
FROM mtl_item_categories_v
WHERE inventory_item_id = cp_inventory_item_id
AND organization_id = cp_org_id -- should be inventory master org
AND structure_id = 101; -- hardcoded to 101 i.e. Item Categories(Inv.Items) for OM
SELECT org_id
FROM oe_blanket_headers_all
WHERE header_id = p_doc_id
AND p_doc_type = G_BSA_DOC_TYPE
UNION ALL
SELECT org_id
FROM oe_order_headers_all
WHERE header_id = p_doc_id
AND p_doc_type = G_SO_DOC_TYPE;
SELECT
bl.line_number,
NVL(bl.payment_term_id,'-99999') payment_term_id,
NVL(bl.invoicing_rule_id,'-99999') invoicing_rule_id,
bl.inventory_item_id,
bl.org_id,
--AK
bl.item_identifier_type, --eg. INT
bl.ordered_item, --eg. AS54888
bl.ordered_item_id,
bl.sold_to_org_id
--AK
FROM
oe_blanket_lines_all bl
WHERE
bl.header_id = p_doc_id;
SELECT
ol.line_number,
NVL(ol.payment_term_id,'-99999') payment_term_id,
NVL(ol.invoicing_rule_id,'-99999') invoicing_rule_id,
NVL(ol.agreement_id,'-99999') agreement_id,
ol.inventory_item_id,
ol.org_id,
--AK
ol.item_identifier_type, --eg. INT
ol.ordered_item, --eg. AS54888
ol.ordered_item_id,
ol.sold_to_org_id,
--AK
--Bug 4768964
ol.service_number,
ol.option_number,
ol.component_number,
ol.shipment_number
--Bug 4768964
FROM
oe_order_lines_all ol
WHERE ol.header_id = p_doc_id;
SELECT ordered_item
FROM oe_blanket_lines_all
WHERE header_id = p_doc_id
AND p_doc_type = G_BSA_DOC_TYPE
AND item_identifier_type = 'CAT'
AND line_category_code = 'ORDER'
ORDER BY ordered_item;
SELECT category_concat_segs
FROM mtl_item_categories_v
WHERE inventory_item_id = cp_inventory_item_id
AND organization_id = cp_org_id -- should be inventory master org
AND structure_id = 101; -- hardcoded to 101 i.e. Item Categories(Inv.Items) for OM
SELECT org_id
FROM oe_blanket_headers_all
WHERE header_id = p_doc_id
AND p_doc_type = G_BSA_DOC_TYPE
UNION ALL
SELECT org_id
FROM oe_order_headers_all
WHERE header_id = p_doc_id
AND p_doc_type = G_SO_DOC_TYPE;