DBA Data[Home] [Help]

APPS.OKC_XPRT_OM_INT_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 159

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;
Line: 170

  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.deliver_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;
Line: 212

  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.deliver_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
      pl.name price_list_name                --6899074
   FROM
	    oe_blanket_headers_all bh,
  	    oe_order_headers_all oh,
        qp_list_headers_tl pl
  WHERE
         oh.blanket_number = bh.order_number(+)
  AND    bh.sales_document_type_code(+) = 'B'
  AND    oh.header_id = p_doc_id
  AND  oh.price_list_id = pl.list_header_id(+)
  AND  pl.language(+) = USERENV('LANG');
Line: 275

  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;
Line: 299

  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;
Line: 319

	SELECT
			 su.location,
			 loc.province,
	   	     loc.address1,
			 loc.address2,
			 loc.address3,
			 loc.address4,
			 loc.city,
			 loc.postal_code,
			 loc.state,
			 loc.county
  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;
Line: 345

  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;
Line: 359

  SELECT
     	pc.agreement_type_code
  FROM
        oe_pricing_contracts_v pc
  WHERE
    	pc.agreement_id = p_agreement_id;
Line: 1342

  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;
Line: 1377

  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;
Line: 1396

  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
Line: 1413

  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;
Line: 1703

  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;
Line: 1724

  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;
Line: 1759

  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;
Line: 1778

  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
Line: 1795

  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;