APPS.POS_ASN_XML SQL Statements

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

Line: 24

 select count(*)
 into v_temp
 from rcv_headers_interface
 where
   shipment_num = p_shipment_num  and
   vendor_id = p_vendor_id and
   nvl(vendor_site_id, -9999) = nvl(p_vendor_site_id, -9999);

Line: 35

 select count(*)
 into  p_count
 from  rcv_shipment_headers
 where
     shipment_num = p_shipment_num and
     vendor_id = p_vendor_id and
     nvl(vendor_site_id, -9999) = nvl(p_vendor_site_id, -9999);

Line: 48

    can be called only at post_insert stage in the root-post level
 */

 if (p_count > 0  OR  v_temp > 1)  then
   p_error_code := 1;

Line: 165

  select count(*)
  into l_count
  from ORG_FREIGHT
  where
    freight_code = p_freight_code;

Line: 190

  select count(*)
  into l_count
  from po_lookup_codes
  where lookup_type = 'FREIGHT TERMS'
  and lookup_code = p_freight_terms
  and sysdate < nvl(inactive_date, sysdate + 1);

Line: 231

  select
    poh.segment1,
    pol.line_num,
    poll.shipment_num,
    pie.error_message
  into
   p_po_num,
   p_line_num,
   p_po_shipment_line_num,
   p_error_message
  from
     rcv_transactions_interface rti, po_interface_errors pie, po_headers_all poh, po_lines_all pol,
     po_line_locations_all poll
  where
    pie.interface_header_id = rti.header_interface_id and
    pie.interface_type in ('RECEIVING','RCV-856')  and
    rti.po_header_id = poh.po_header_id  and
    rti.po_line_id = pol.po_line_id   and
    rti.po_line_location_id = poll.line_location_id and
    rti.group_id = p_group_id;

Line: 253

select count(*)
into l_count
from
  rcv_transactions_interface rti, po_interface_errors pie
where
  pie.interface_header_id = rti.header_interface_id and
 -- pie.interface_type in ('RECEIVING','RCV-856')  and
  rti.group_id = p_group_id;

Line: 267

     select
       min(pie.error_message)
     into
       p_error_message
     from
       rcv_transactions_interface rti, po_interface_errors pie
     where
       pie.interface_header_id = rti.header_interface_id and
       -- pie.interface_type in ('RECEIVING','RCV-856')  and
       rti.group_id = p_group_id;

Line: 318

    SELECT count(*)
    INTO l_loc_count
    FROM hz_locations
    WHERE
      address1 = p_address1 and
      nvl(address2, 99) = nvl(p_address2, 99) and
      city = p_city and
      postal_code = p_postal_code and
      country = p_country;

Line: 330

     SELECT min(location_id)
     INTO  p_ship_to_location_id
     FROM hz_locations
     WHERE
      address1 = p_address1 and
      nvl(address2, 99) = nvl(p_address2, 99) and
      city = p_city  and
      postal_code = p_postal_code and
      country = p_country;

Line: 340

      select count(*)
	  into   x_pla_count
	  from   po_location_associations_all pla
	  where pla.org_id = p_org_id
               and pla.location_id = p_ship_to_location_id
               and pla.vendor_id is not null
               and pla.vendor_site_id is not null;

Line: 389

   SELECT count(*)
   INTO l_count_num
   FROM hr_locations_all
   WHERE ece_tp_location_code = p_ship_to_partner_id;

Line: 402

       SELECT min(location_id)
       INTO  p_ship_to_location_id
       FROM hr_locations_all
       WHERE ece_tp_location_code = p_ship_to_partner_id;

Line: 408

        select count(*)
	  into   x_pla_count
	  from   po_location_associations_all pla
	  where pla.org_id = p_org_id
               and pla.location_id = p_ship_to_location_id
               and pla.vendor_id is not null
               and pla.vendor_site_id is not null;

Line: 432

    SELECT count(*)
    INTO l_loc_count
    FROM
     hz_locations loc,
     hz_party_sites party,
     hz_cust_acct_sites_all cust
    WHERE
      cust.ece_tp_location_code = p_ship_to_partner_id
      and cust.org_id = p_org_id
      and cust.party_site_id = party.party_site_id
      and party.location_id = loc.location_id;

Line: 449

     SELECT min(loc.location_id)
     INTO  p_ship_to_location_id
     FROM
       hz_locations loc,
       hz_party_sites party,
       hz_cust_acct_sites_all cust
     WHERE
      cust.ece_tp_location_code = p_ship_to_partner_id
      and cust.org_id = p_org_id
      and cust.party_site_id = party.party_site_id
      and party.location_id = loc.location_id;

Line: 517

    SELECT count(*)
    INTO l_count_num
    FROM
     po_headers_all poh,
     po_lines_all pol,
     po_line_locations_all pll,
     Mtl_system_items_kfv msi
    WHERE
     poh.SEGMENT1 = p_po_number AND
     poh.Vendor_Site_ID IN
       (SELECT Vendor_Site_ID
        FROM PO_Vendor_Sites_All
        WHERE  ECE_TP_LOCATION_CODE = p_supplier_code)    AND
     nvl(msi.concatenated_segments, -99) = nvl(NVL(p_item_num, msi.concatenated_segments), -99) AND
     nvl(pol.VENDOR_PRODUCT_NUM, -99) = nvl(NVL(p_supplier_item_num, pol.VENDOR_PRODUCT_NUM), -99) AND
     pol.po_header_id =  poh.po_header_id AND
     pol.line_num = p_document_line_num AND
     pol.po_line_id = pll.po_line_id AND
     pll.shipment_num = p_document_shipment_line_num AND
     pll.ship_to_organization_id = nvl(msi.organization_id, pll.ship_to_organization_id) AND
     pol.item_id = msi.inventory_item_id (+);

Line: 541

    SELECT count(*)
    INTO l_count_num
    FROM
     po_headers_all poh,
     po_lines_all pol,
     po_line_locations_all pll,
     po_releases_all prl,
     Mtl_system_items_kfv msi
    WHERE
     poh.SEGMENT1 = p_po_number AND
     poh.Vendor_Site_ID IN
       (SELECT Vendor_Site_ID
        FROM PO_Vendor_Sites_All
        WHERE  ECE_TP_LOCATION_CODE = p_supplier_code)    AND
     nvl(msi.concatenated_segments, -99) = nvl(NVL(p_item_num, msi.concatenated_segments), -99) AND
     nvl(pol.VENDOR_PRODUCT_NUM, -99) = nvl(NVL(p_supplier_item_num, pol.VENDOR_PRODUCT_NUM), -99) AND
     pol.po_header_id =  poh.po_header_id AND
     pol.line_num = p_document_line_num AND
     pol.po_line_id = pll.po_line_id AND
     pll.shipment_num = p_document_shipment_line_num AND
     pll.PO_RELEASE_ID = prl.PO_RELEASE_ID AND
     prl.release_num = p_release_num AND
     pll.ship_to_organization_id = nvl(msi.organization_id, pll.ship_to_organization_id) AND
     pol.item_id = msi.inventory_item_id (+);

Line: 598

     SELECT min(poh.ORG_ID)
     INTO p_org_id
     FROM
     po_headers_all poh,
     po_lines_all pol,
     po_line_locations_all pll,
     Mtl_system_items_kfv msi
    WHERE
     poh.SEGMENT1 = p_po_number AND
     poh.Vendor_Site_ID IN
       (SELECT Vendor_Site_ID
        FROM PO_Vendor_Sites_All
        WHERE  ECE_TP_LOCATION_CODE = p_supplier_code)    AND
     nvl(msi.concatenated_segments, -99) = nvl(NVL(p_item_num, msi.concatenated_segments), -99) AND
     nvl(pol.VENDOR_PRODUCT_NUM, -99) = nvl(NVL(p_supplier_item_num, pol.VENDOR_PRODUCT_NUM), -99) AND
     pol.po_header_id =  poh.po_header_id AND
     pol.line_num = p_document_line_num AND
     pol.po_line_id = pll.po_line_id AND
     pll.shipment_num = p_document_shipment_line_num AND
     pll.ship_to_organization_id = nvl(msi.organization_id, pll.ship_to_organization_id) AND
     pol.item_id = msi.inventory_item_id (+);

Line: 622

     SELECT min(poh.ORG_ID)
     INTO p_org_id
     FROM
     po_headers_all poh,
     po_lines_all pol,
     po_line_locations_all pll,
     po_releases_all prl,
     Mtl_system_items_kfv msi
    WHERE
     poh.SEGMENT1 = p_po_number AND
     poh.Vendor_Site_ID IN
       (SELECT Vendor_Site_ID
        FROM PO_Vendor_Sites_All
        WHERE  ECE_TP_LOCATION_CODE = p_supplier_code)    AND
     nvl(msi.concatenated_segments, -99) = nvl(NVL(p_item_num, msi.concatenated_segments), -99) AND
     nvl(pol.VENDOR_PRODUCT_NUM, -99) = nvl(NVL(p_supplier_item_num, pol.VENDOR_PRODUCT_NUM), -99) AND
     pol.po_header_id =  poh.po_header_id AND
     pol.line_num = p_document_line_num AND
     pol.po_line_id = pll.po_line_id AND
     pll.shipment_num = p_document_shipment_line_num AND
     pll.PO_RELEASE_ID = prl.PO_RELEASE_ID AND
     prl.release_num = p_release_num AND
     pll.ship_to_organization_id = nvl(msi.organization_id, pll.ship_to_organization_id) AND
     pol.item_id = msi.inventory_item_id (+);

Line: 651

     select min(po_header_id)
     into p_po_header_id
     from po_headers_all
     where segment1 = p_po_number
     and org_id = p_org_id;

Line: 660

          select
            count(*)
          into
            x_ship_org_num
          from
            po_headers_all poh,
            po_lines_all pol,
            po_line_locations_all pll
          where
            poh.po_header_id = p_po_header_id and
            poh.po_header_id = pol.po_header_id and
            pol.line_num = p_document_line_num and
            pol.po_line_id = pll.po_line_id and
            pll.shipment_num = p_document_shipment_line_num;

Line: 677

           select
            count(*)
          into
            x_ship_org_num
          from
            po_headers_all poh,
            po_lines_all pol,
            po_line_locations_all pll,
            po_releases_all prl
          where
            poh.po_header_id = p_po_header_id and
            poh.po_header_id = pol.po_header_id and
            pol.line_num = p_document_line_num and
            pol.po_line_id = pll.po_line_id and
            pll.shipment_num = p_document_shipment_line_num and
            pll.PO_RELEASE_ID = prl.PO_RELEASE_ID AND
            prl.release_num = p_release_num;

Line: 703

          select
            min(pll.ship_to_organization_id)
          into
            p_ship_to_org_id
          from
            po_headers_all poh,
            po_lines_all pol,
            po_line_locations_all pll
          where
            poh.po_header_id = p_po_header_id and
            poh.po_header_id = pol.po_header_id and
            pol.line_num = p_document_line_num and
            pol.po_line_id = pll.po_line_id and
            pll.shipment_num = p_document_shipment_line_num;

Line: 720

          select
            min(pll.ship_to_organization_id)
          into
            p_ship_to_org_id
          from
            po_headers_all poh,
            po_lines_all pol,
            po_line_locations_all pll,
            po_releases_all prl
          where
            poh.po_header_id = p_po_header_id and
            poh.po_header_id = pol.po_header_id and
            pol.line_num = p_document_line_num and
            pol.po_line_id = pll.po_line_id and
            pll.shipment_num = p_document_shipment_line_num and
            pll.PO_RELEASE_ID = prl.PO_RELEASE_ID AND
            prl.release_num = p_release_num;

Line: 786

      SELECT
       vendor_site_id,
       vendor_id
      INTO
        p_vendor_site_id,
        p_vendor_id
      FROM   po_vendor_sites_all
      WHERE  ece_tp_location_code = p_supplier_code
      AND   org_id = p_org_id;

Line: 876

   select count(*)
   into l_count
   from fnd_user
   where user_name = upper(p_user_name);

Line: 890

   select user_id
   into p_user_id
   from fnd_user
   where user_name = upper(p_user_name);

Line: 913

   select count(*)
   into x_ship_org_count
   from (select distinct to_organization_id
         from rcv_transactions_interface
         where header_interface_id = p_header_interface_id);

Line: 924

    select
     min(to_organization_id),
     min(vendor_id),
     min(vendor_site_id)
   into
     p_ship_to_org_id,
     p_vendor_id,
     p_vendor_site_id
   from
    rcv_transactions_interface
   where
     header_interface_id = p_header_interface_id;

Line: 937

   update rcv_headers_interface
   set vendor_id = p_vendor_id,
       vendor_site_id = p_vendor_site_id,
       ship_to_organization_id = p_ship_to_org_id
   where header_interface_id = p_header_interface_id;

Line: 997

  select segment1 into x_po_num from po_headers_all where po_header_id=p_po_header_id;

Line: 1001

 SELECT
   count(*)
 INTO
   l_count
 FROM
  po_headers_all poh,
  po_lines_all pol,
  po_line_locations_all pll,
  MTL_SYSTEM_ITEMS_KFV MSI
 WHERE
  POH.PO_HEADER_ID = POL.PO_HEADER_ID
  and POL.PO_LINE_ID = PLL.PO_LINE_ID
  and pol.item_id = msi.inventory_item_id (+)
  and nvl(msi.ORGANIZATION_ID, pll.SHIP_TO_ORGANIZATION_ID) = pll.SHIP_TO_ORGANIZATION_ID
  and poh.PO_HEADER_ID = p_po_header_id
  and pol.LINE_NUM = p_line_num
  and pll.shipment_num = p_document_shipment_line_num;

Line: 1022

 SELECT
   count(*)
INTO
   l_count
FROM
  po_headers_all poh,
  po_lines_all pol,
  po_line_locations_all pll,
  po_releases_all prl,
  MTL_SYSTEM_ITEMS_KFV MSI
WHERE
  POH.PO_HEADER_ID = POL.PO_HEADER_ID
  and POL.PO_LINE_ID = PLL.PO_LINE_ID
  and pll.PO_RELEASE_ID = prl.PO_RELEASE_ID
  and pol.item_id = msi.inventory_item_id (+)
  and nvl(msi.ORGANIZATION_ID, pll.SHIP_TO_ORGANIZATION_ID) = pll.SHIP_TO_ORGANIZATION_ID
  and poh.PO_HEADER_ID = p_po_header_id
  and pol.LINE_NUM = p_line_num
  and pll.shipment_num = p_document_shipment_line_num
  and prl.release_num = p_release_num;

Line: 1049

 SELECT
   pol.ITEM_ID,
   msi.CONCATENATED_SEGMENTS ITEM_NUM,
   pol.ITEM_REVISION,
   pol.VENDOR_PRODUCT_NUM SUPPLIER_ITEM_NUMBER,
   pll.ship_to_location_id,
   pol.PO_LINE_ID,
   pll.LINE_LOCATION_ID,
   pll.SHIP_TO_ORGANIZATION_ID SHIP_TO_ORG_ID
 INTO
   p_item_id,
   p_item_num,
   p_item_revision,
   p_supplier_item_num,
   p_ship_to_location_id,
   p_po_line_id,
   p_line_location_id,
   p_ship_to_org_id
 FROM
  po_headers_all poh,
  po_lines_all pol,
  po_line_locations_all pll,
  MTL_SYSTEM_ITEMS_KFV MSI
 WHERE
  POH.PO_HEADER_ID = POL.PO_HEADER_ID
  and POL.PO_LINE_ID = PLL.PO_LINE_ID
  and pol.item_id = msi.inventory_item_id (+)
  and nvl(msi.ORGANIZATION_ID, pll.SHIP_TO_ORGANIZATION_ID) = pll.SHIP_TO_ORGANIZATION_ID
  and poh.PO_HEADER_ID = p_po_header_id
  and pol.LINE_NUM = p_line_num
  and pll.shipment_num = p_document_shipment_line_num;

Line: 1084

 SELECT
   pol.ITEM_ID,
   msi.CONCATENATED_SEGMENTS ITEM_NUM,
   pol.ITEM_REVISION,
   pol.VENDOR_PRODUCT_NUM SUPPLIER_ITEM_NUMBER,
   pll.ship_to_location_id,
   pol.PO_LINE_ID,
   pll.LINE_LOCATION_ID,
   pll.SHIP_TO_ORGANIZATION_ID SHIP_TO_ORG_ID,
   prl.PO_RELEASE_ID
INTO
   p_item_id,
   p_item_num,
   p_item_revision,
   p_supplier_item_num,
   p_ship_to_location_id,
   p_po_line_id,
   p_line_location_id,
   p_ship_to_org_id,
   p_po_release_id
FROM
  po_headers_all poh,
  po_lines_all pol,
  po_line_locations_all pll,
  po_releases_all prl,
  MTL_SYSTEM_ITEMS_KFV MSI
WHERE
  POH.PO_HEADER_ID = POL.PO_HEADER_ID
  and POL.PO_LINE_ID = PLL.PO_LINE_ID
  and pll.PO_RELEASE_ID = prl.PO_RELEASE_ID
  and pol.item_id = msi.inventory_item_id (+)
  and nvl(msi.ORGANIZATION_ID, pll.SHIP_TO_ORGANIZATION_ID) = pll.SHIP_TO_ORGANIZATION_ID
  and poh.PO_HEADER_ID = p_po_header_id
  and pol.LINE_NUM = p_line_num
  and pll.shipment_num = p_document_shipment_line_num
  and prl.release_num = p_release_num;

Line: 1165

update rcv_headers_interface
   set bill_of_lading = p_bill_of_lading,
       packing_slip = p_packing_slip,
       waybill_airbill_num = p_waybill_airbill_num,
       location_id = p_location_id
   where header_interface_id = p_header_interface_id;

Line: 1192

  select count(*)
  into l_count
  from mtl_units_of_measure_tl
  where uom_code = p_uom_code
  and language = USERENV('LANG');

Line: 1212

   select unit_of_measure
   into p_unit_of_measure
   from mtl_units_of_measure_tl
   where uom_code = p_uom_code
   and language = USERENV('LANG');