DBA Data[Home] [Help]

APPS.INV_MWB_TREE SQL Statements

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

Line: 12

        insert into amintemp1 VALUES (b || a);
Line: 121

      query_str  := 'SELECT mp.organization_id, mp.organization_code  ';
Line: 123

      query_str  := query_str || '(select organization_id ';
Line: 175

      query_str  := ' SELECT mp.organization_id, mp.organization_code FROM mtl_parameters mp ';
Line: 201

          query_str  := query_str || 'SELECT organization_id from ' || table_required;
Line: 206

             || ' SELECT organization_id from'
             || ' (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
             || p_lot_attr_query
             || ' ) mln, '
             || table_required;
Line: 386

          query_str  := query_str || ' and EXISTS ( SELECT 1 ' ;
Line: 421

          query_str  := query_str || ' SELECT organization_id from ' || table_required;
Line: 427

             || ' SELECT organization_id from'
             || ' (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
             || p_serial_attr_query
             || ') msn, '
             || table_required;
Line: 437

             || ' SELECT organization_id from'
             || ' (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
             || p_lot_attr_query
             || ' ) mln, '
             || table_required;
Line: 447

             || ' SELECT organization_id from'
             || ' (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
             || p_lot_attr_query
             || ' ) mln, '
             || ' (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
             || p_serial_attr_query
             || ' ) msn, '
             || table_required;
Line: 596

           query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
Line: 646

          query_str  := query_str || ' and EXISTS ( SELECT 1 ' ;
Line: 675

          query_str  := query_str || 'SELECT organization_id from ' || table_required;
Line: 680

             || 'SELECT organization_id from'
             || '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
             || p_lot_attr_query
             || ') mln, '
             || table_required;
Line: 824

           query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
Line: 883

          query_str  := query_str || 'SELECT organization_id from ' || table_required;
Line: 889

             || 'SELECT organization_id from'
             || '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
             || p_lot_attr_query
             || ') mln, '
             || table_required;
Line: 1023

          query_str  := query_str || ' and EXISTS ( SELECT 1 ' ;
Line: 1058

        query_str  := query_str || ' SELECT organization_id from ' || table_required;
Line: 1062

          query_str  := query_str || ', (select outermost_lpn_id from wms_license_plate_numbers wlpn ';
Line: 1119

                     query_str || ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 ' || p_serial_attr_query
                     || ') msn ';
Line: 1124

          query_str  := query_str || ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 ' || p_lot_attr_query || ') mln ';
Line: 1130

             || ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
             || p_lot_attr_query
             || ') mln '
             || ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
             || p_serial_attr_query
             || ') msn ';
Line: 1301

           query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
Line: 1351

          query_str  := query_str || ' and EXISTS ( SELECT 1 ' ;
Line: 1604

    query_str       := 'SELECT mms.status_id, mms.status_code ';
Line: 1610

      SELECT serial_number_control_code
        INTO serial_control
        FROM mtl_system_items
       WHERE organization_id = p_organization_id
         AND inventory_item_id = p_inventory_item_id;
Line: 1625

        query_str  := query_str || 'SELECT organization_id from mtl_onhand_serial_v mos ';
Line: 1631

           || 'SELECT organization_id from'
           || '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
           || p_serial_attr_query
           || ') msn, mtl_onhand_serial_v mos ';
Line: 1640

           || 'SELECT organization_id from'
           || '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
           || p_lot_attr_query
           || ') mln, mtl_onhand_serial_v mos ';
Line: 1649

           || 'SELECT organization_id from'
           || '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
           || p_lot_attr_query
           || ') mln, '
           || '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
           || p_serial_attr_query
           || ') msn, mtl_onhand_serial_v mos ';
Line: 1768

         query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
Line: 1814

        query_str  := query_str || ' AND  EXISTS ( SELECT 1 ' ;
Line: 1836

        query_str  := query_str || 'SELECT organization_id from mtl_onhand_total_v mot ';
Line: 1841

           || 'SELECT organization_id from'
           || '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
           || p_lot_attr_query
           || ') mln, mtl_onhand_total_v mot ';
Line: 1948

         query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
Line: 2018

        query_str  := query_str || ' AND  EXISTS ( SELECT 1 ' ;
Line: 2036

        query_str  := query_str || 'SELECT organization_id from mtl_onhand_serial_v mos ';
Line: 2042

           || 'SELECT organization_id from'
           || '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
           || p_lot_attr_query
           || ') mln, mtl_onhand_serial_v mos  ';
Line: 2148

         query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
Line: 2194

        query_str  := query_str || ' and EXISTS ( SELECT 1 ' ;
Line: 2220

      query_str       := query_str || 'SELECT organization_id from ' || table_required;
Line: 2224

        query_str  := query_str || ', (select outermost_lpn_id from wms_license_plate_numbers wlpn ';
Line: 2269

                     query_str || ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 ' || p_serial_attr_query
                     || ') msn ';
Line: 2274

        query_str  := query_str || ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 ' || p_lot_attr_query || ') mln ';
Line: 2280

           || ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
           || p_lot_attr_query
           || ') mln '
           || ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
           || p_serial_attr_query
           || ') msn ';
Line: 2394

         query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
Line: 2440

        query_str  := query_str || ' and EXISTS ( SELECT 1 ' ;
Line: 2711

      query_str  := 'select subinventory_code ';
Line: 2760

      query_str  := 'SELECT msi.secondary_inventory_name subinventory_code FROM mtl_secondary_inventories msi ';
Line: 2785

          query_str  := query_str || ' SELECT subinventory_code from ' || table_required;
Line: 2790

             || ' SELECT subinventory_code from '
             || '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
             || p_lot_attr_query
             || ') mln, '
             || table_required;
Line: 2831

           query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
Line: 2929

          query_str  := query_str || 'SELECT subinventory_code from ' || table_required;
Line: 2935

             || 'SELECT subinventory_code from'
             || '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
             || p_serial_attr_query
             || ') msn, '
             || table_required;
Line: 2945

             || 'SELECT subinventory_code from'
             || '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
             || p_lot_attr_query
             || ') mln, '
             || table_required;
Line: 2955

             || 'SELECT subinventory_code from'
             || '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
             || p_lot_attr_query
             || ') mln, '
             || '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
             || p_serial_attr_query
             || ') msn, '
             || table_required;
Line: 2985

           query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
Line: 3101

          query_str  := query_str || 'SELECT subinventory_code from ' || table_required;
Line: 3106

             || 'SELECT subinventory_code from'
             || '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
             || p_lot_attr_query
             || ') mln, '
             || table_required;
Line: 3128

           query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
Line: 3216

          query_str  := query_str || 'SELECT subinventory_code from ' || table_required;
Line: 3222

             || 'SELECT subinventory_code from'
             || '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
             || p_lot_attr_query
             || ') mln, '
             || table_required;
Line: 3309

        query_str  := query_str || 'SELECT subinventory_code from ' || table_required;
Line: 3313

          query_str  := query_str || ', (select outermost_lpn_id from wms_license_plate_numbers wlpn ';
Line: 3367

                     query_str || ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 ' || p_serial_attr_query
                     || ') msn ';
Line: 3372

          query_str  := query_str || ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 ' || p_lot_attr_query || ') mln ';
Line: 3378

             || ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
             || p_lot_attr_query
             || ') mln '
             || ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
             || p_serial_attr_query
             || ') msn ';
Line: 3406

           query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
Line: 3711

      SELECT stock_locator_control_code
        INTO stock_loc_code
        FROM mtl_parameters
       WHERE organization_id = p_organization_id;
Line: 3722

          SELECT locator_type
            INTO loc_type
            FROM mtl_secondary_inventories
           WHERE secondary_inventory_name = p_subinventory_code
             AND organization_id = p_organization_id;
Line: 3755

      query_str  := 'SELECT wlpn.locator_id, mil.concatenated_segments ';
Line: 3825

          query_str  := query_str || 'SELECT locator_id, locator from ' || table_required;
Line: 3830

             || 'SELECT locator_id, locator from'
             || '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
             || p_lot_attr_query
             || ') mln, '
             || table_required;
Line: 3856

           query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
Line: 3962

          query_str  := query_str || 'SELECT locator_id, locator from ' || table_required;
Line: 3968

             || 'SELECT locator_id, locator from'
             || '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
             || p_serial_attr_query
             || ') msn, '
             || table_required;
Line: 3978

             || 'SELECT locator_id, locator from'
             || '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
             || p_lot_attr_query
             || ') mln, '
             || table_required;
Line: 3988

             || 'SELECT locator_id, locator from'
             || '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
             || p_lot_attr_query
             || ') mln, '
             || '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
             || p_serial_attr_query
             || ') msn, '
             || table_required;
Line: 4097

        query_str  := 'SELECT locator_id, locator from (';
Line: 4100

          query_str  := query_str || 'SELECT locator_id, locator from ' || table_required;
Line: 4105

             || 'SELECT locator_id, locator from'
             || '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
             || p_lot_attr_query
             || ') mln, '
             || table_required;
Line: 4127

           query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
Line: 4221

          query_str  := query_str || 'SELECT locator_id, locator from ' || table_required;
Line: 4227

             || 'SELECT locator_id, locator from'
             || '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
             || p_lot_attr_query
             || ') mln, '
             || table_required;
Line: 4308

        query_str  := 'SELECT locator_id, locator from ' || table_required;
Line: 4312

          query_str  := query_str || ', (select outermost_lpn_id from wms_license_plate_numbers wlpn ';
Line: 4366

                     query_str || ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 ' || p_serial_attr_query
                     || ') msn ';
Line: 4371

          query_str  := query_str || ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 ' || p_lot_attr_query || ') mln ';
Line: 4377

             || ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
             || p_lot_attr_query
             || ') mln '
             || ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
             || p_serial_attr_query
             || ') msn ';
Line: 4405

           query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
Line: 4731

        query_str  := 'SELECT DISTINCT mot.cost_group_id, ccg.cost_group ';
Line: 4737

           || 'SELECT mot.cost_group_id, ccg.cost_group from'
           || '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
           || p_lot_attr_query
           || ') mln, cst_cost_groups ccg, '
           || table_required;
Line: 4846

         query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
Line: 4880

        query_str  := query_str || ' and EXISTS ( SELECT 1 ' ;
Line: 4925

        query_str  := 'SELECT DISTINCT mos.cost_group_id, ccg.cost_group ';
Line: 4932

           || 'SELECT DISTINCT mos.cost_group_id, ccg.cost_group from'
           || '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
           || p_serial_attr_query
           || ') msn, cst_cost_groups ccg, '
           || table_required;
Line: 4943

           || 'SELECT DISTINCT mos.cost_group_id, ccg.cost_group from'
           || '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
           || p_lot_attr_query
           || ') mln, cst_cost_groups ccg, '
           || table_required;
Line: 4954

           || 'SELECT DISTINCT mos.cost_group_id, ccg.cost_group from'
           || '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
           || p_lot_attr_query
           || ') mln, '
           || '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
           || p_serial_attr_query
           || ') msn, cst_cost_groups ccg, '
           || table_required;
Line: 5077

        query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
Line: 5111

        query_str  := query_str || ' and EXISTS ( SELECT 1 ' ;
Line: 5152

      query_str  := 'SELECT DISTINCT cost_group_id, cost_group from (';
Line: 5155

        query_str  := query_str || 'SELECT mot.cost_group_id, ccg.cost_group ';
Line: 5161

           || 'SELECT mot.cost_group_id, ccg.cost_group FROM '
           || '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
           || p_lot_attr_query
           || ') mln, cst_cost_groups ccg, '
           || table_required;
Line: 5273

         query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
Line: 5329

        query_str  := query_str || 'SELECT mos.cost_group_id, ccg.cost_group ';
Line: 5336

           || 'SELECT mos.cost_group_id, ccg.cost_group from'
           || '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
           || p_lot_attr_query
           || ') mln, cst_cost_groups ccg, mtl_onhand_serial_v mos ';
Line: 5417

        query_str  := query_str || ' and EXISTS ( SELECT 1 ' ;
Line: 5459

      query_str  := 'SELECT DISTINCT mol.cost_group_id, ccg.cost_group ';
Line: 5464

        query_str  := query_str || ', (select outermost_lpn_id from wms_license_plate_numbers wlpn ';
Line: 5518

                     query_str || ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 ' || p_serial_attr_query
                     || ') msn ';
Line: 5523

        query_str  := query_str || ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 ' || p_lot_attr_query || ') mln ';
Line: 5529

           || ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
           || p_lot_attr_query
           || ') mln '
           || ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
           || p_serial_attr_query
           || ') msn ';
Line: 5654

         query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
Line: 5908

  , p_inserted_under_org  IN            VARCHAR2 DEFAULT 'N'
  --ER(3338592) Changes
  , p_item_description    IN            VARCHAR2 DEFAULT NULL
  --ER(3338592) Changes
  , x_node_value          IN OUT NOCOPY NUMBER
  , x_node_tbl            IN OUT NOCOPY fnd_apptree.node_tbl_type
  , x_tbl_index           IN OUT NOCOPY NUMBER
   -- NSRIVAST, INVCONV, Start
  , p_grade_from           IN             VARCHAR2 DEFAULT NULL

  , p_grade_code           IN             VARCHAR2 DEFAULT NULL
  , p_grade_controlled     IN             NUMBER DEFAULT 0
  -- NSRIVAST, INVCONV, End
  ) IS
    query_str        VARCHAR2(10000);
Line: 5970

        query_str  := 'SELECT license_plate_number lpn, lpn_id, inventory_item_id ';
Line: 5972

        query_str  := query_str || ' (select outermost_lpn_id ';
Line: 5977

          IF p_inserted_under_org = 'Y' OR p_inserted_under_org = 'y' THEN
            query_str  := query_str || ' AND mol.subinventory_code is null AND mol.locator_id is null ';
Line: 5993

          IF p_inserted_under_org = 'Y' OR p_inserted_under_org = 'y' THEN
            --don't add the below locator id not null check
            NULL;
Line: 6056

        query_str  := 'SELECT license_plate_number lpn, lpn_id, inventory_item_id ';
Line: 6061

          IF p_inserted_under_org = 'Y' OR p_inserted_under_org = 'y' THEN
            query_str  := query_str || ' AND mol.subinventory_code is null AND mol.locator_id is null ';
Line: 6077

          IF p_inserted_under_org = 'Y' OR p_inserted_under_org = 'y' THEN
            --don't add the below locator id not null check
            NULL;
Line: 6173

        query_str  := 'SELECT license_plate_number lpn, lpn_id, inventory_item_id ';
Line: 6175

        query_str  := query_str || ' (select MOL.outermost_lpn_id ';
Line: 6180

          query_str  := query_str || ', (select outermost_lpn_id from wms_license_plate_numbers wlpn ';
Line: 6185

            IF p_inserted_under_org = 'Y' OR p_inserted_under_org = 'y' THEN
              query_str  := query_str || ' AND wlpn.subinventory_code is null AND wlpn.locator_id is null ';
Line: 6201

            IF p_inserted_under_org = 'Y' OR p_inserted_under_org = 'y' THEN
              --don't add the below locator id not null check
              NULL;
Line: 6262

                     query_str || ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 ' || p_serial_attr_query
                     || ') msn ';
Line: 6267

          query_str  := query_str || ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 ' || p_lot_attr_query || ') mln ';
Line: 6273

             || ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
             || p_lot_attr_query
             || ') mln '
             || ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
             || p_serial_attr_query
             || ') msn ';
Line: 6301

           query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
Line: 6376

          IF p_inserted_under_org = 'Y' OR p_inserted_under_org = 'y' THEN
            query_str  := query_str || ' AND subinventory_code is null AND locator_id is null ';
Line: 6401

          IF p_inserted_under_org = 'Y' OR p_inserted_under_org = 'y' THEN
            --don't add the below locator id not null check
            NULL;
Line: 6439

        query_str         := query_str || ' select license_plate_number lpn, lpn_id, inventory_item_id from wms_license_plate_numbers ';
Line: 6444

          IF p_inserted_under_org = 'Y' OR p_inserted_under_org = 'y' THEN
            query_str  := query_str || ' AND subinventory_code is null AND locator_id is null ';
Line: 6614

          SELECT concatenated_segments
            INTO item
            FROM mtl_system_items_kfv
           WHERE organization_id = p_organization_id
             AND inventory_item_id = item_id;
Line: 6688

  , p_inserted_under_org  IN            VARCHAR2 DEFAULT 'N'
  --ER(3338592) Changes
  , p_item_description    IN            VARCHAR2 DEFAULT NULL
  --ER(3338592) Changes
  , p_responsibility_id   IN            NUMBER    DEFAULT NULL  --Bug # 3411938
  , p_resp_application_id IN            NUMBER    DEFAULT NULL
  , p_qty_from            IN            NUMBER    DEFAULT NULL  --Bug # 3539766
  , p_qty_to              IN            NUMBER    DEFAULT NULL
  , x_node_value          IN OUT NOCOPY NUMBER
  , x_node_tbl            IN OUT NOCOPY fnd_apptree.node_tbl_type
  , x_tbl_index           IN OUT NOCOPY NUMBER
   -- NSRIVAST, INVCONV, Start
  , p_grade_from           IN             VARCHAR2 DEFAULT NULL

  , p_grade_code           IN             VARCHAR2 DEFAULT NULL
  , p_grade_controlled     IN             NUMBER DEFAULT 0
  -- NSRIVAST, INVCONV, End
  ) IS
    query_str      VARCHAR2(10000);
Line: 6750

        query_str  := query_str || 'SELECT DISTINCT inventory_item_id, item from ' || table_required;
Line: 6755

           || 'SELECT DISTINCT inventory_item_id, item from'
           || '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
           || p_lot_attr_query
           || ') mln, '
           || table_required;
Line: 6864

         query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
Line: 6896

        IF p_inserted_under_org = 'Y' OR p_inserted_under_org = 'y' THEN
          --don't add the below locator id not null check
          NULL;
Line: 6908

        IF p_inserted_under_org = 'Y' OR p_inserted_under_org = 'y' THEN
          query_str  := query_str || ' AND subinventory_code is null AND locator_id is null ';
Line: 6918

        query_str  := query_str || ' and EXISTS ( SELECT 1 ' ;
Line: 6967

        query_str  := query_str || 'SELECT DISTINCT inventory_item_id, item from ' || table_required;
Line: 6973

           || 'SELECT DISTINCT inventory_item_id, item from'
           || '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
           || p_serial_attr_query
           || ') msn, '
           || table_required;
Line: 6983

           || 'SELECT DISTINCT inventory_item_id, item from'
           || '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
           || p_lot_attr_query
           || ') mln, '
           || table_required;
Line: 6993

           || 'SELECT DISTINCT inventory_item_id, item from'
           || '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
           || p_lot_attr_query
           || ') mln, '
           || '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
           || p_serial_attr_query
           || ') msn, '
           || table_required;
Line: 7128

         query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
Line: 7160

        IF p_inserted_under_org = 'Y' OR p_inserted_under_org = 'y' THEN
          --don't add the below locator id not null check
          NULL;
Line: 7172

        IF p_inserted_under_org = 'Y' OR p_inserted_under_org = 'y' THEN
          query_str  := query_str || ' AND subinventory_code is null AND locator_id is null ';
Line: 7182

        query_str  := query_str || ' and EXISTS ( SELECT 1 ' ;
Line: 7217

      query_str  := 'SELECT DISTINCT inventory_item_id, item from( ';
Line: 7228

        query_str  := query_str || 'SELECT inventory_item_id, item from ' || table_required;
Line: 7233

           || 'SELECT inventory_item_id, item from'
           || '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
           || p_lot_attr_query
           || ') mln, mtl_onhand_total_v ';
Line: 7336

              || ' ( select null from mtl_system_items msi WHERE ';
Line: 7350

         query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
Line: 7382

        IF p_inserted_under_org = 'Y' OR p_inserted_under_org = 'y' THEN
          --don't add the below locator id not null check
          NULL;
Line: 7395

        query_str  := query_str || ' and EXISTS ( SELECT 1 ' ;
Line: 7423

        query_str  := query_str || 'SELECT inventory_item_id, item from mtl_onhand_serial_v mos ';
Line: 7429

           || 'SELECT inventory_item_id, item from'
           || '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
           || p_lot_attr_query
           || ') mln, mtl_onhand_serial_v ';
Line: 7554

        IF p_inserted_under_org = 'Y' OR p_inserted_under_org = 'y' THEN
          --don't add the below locator id not null check
          NULL;
Line: 7574

        query_str  := query_str || ' and EXISTS ( SELECT 1 ' ;
Line: 7627

      query_str  := 'SELECT DISTINCT inventory_item_id, item ';
Line: 7633

        query_str  := query_str || ', (select outermost_lpn_id from wms_license_plate_numbers wlpn ';
Line: 7638

          IF p_inserted_under_org = 'Y' OR p_inserted_under_org = 'y' THEN
            query_str  := query_str || ' AND wlpn.subinventory_code is null AND wlpn.locator_id is null ';
Line: 7703

                     query_str || ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 ' || p_serial_attr_query
                     || ') msn ';
Line: 7708

        query_str  := query_str || ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 ' || p_lot_attr_query || ') mln ';
Line: 7714

           || ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
           || p_lot_attr_query
           || ') mln '
           || ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
           || p_serial_attr_query
           || ') msn ';
Line: 7839

        IF p_inserted_under_org = 'Y' OR p_inserted_under_org = 'y' THEN
          query_str  := query_str || ' AND mol.subinventory_code is null AND mol.locator_id is null ';
Line: 7869

         query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
Line: 7901

        IF p_inserted_under_org = 'Y' OR p_inserted_under_org = 'y' THEN
          --don't add the below locator id not null check
          NULL;
Line: 7915

        query_str  := query_str || ' and EXISTS ( SELECT 1 ' ;
Line: 8197

      SELECT revision_qty_control_code
        INTO rev_control
        FROM mtl_system_items
       WHERE organization_id = p_organization_id
         AND inventory_item_id = p_inventory_item_id;
Line: 8231

        query_str  := query_str || 'SELECT item, revision from ' || table_required;
Line: 8236

           || 'SELECT item, revision from'
           || '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
           || p_lot_attr_query
           || ') mln, '
           || table_required;
Line: 8330

         query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
Line: 8380

        query_str  := query_str || 'SELECT item, revision from mtl_onhand_serial_v ';
Line: 8386

           || 'SELECT item, revision from'
           || '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
           || p_serial_attr_query
           || ') msn, mtl_onhand_serial_v ';
Line: 8395

           || 'SELECT item, revision from'
           || '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
           || p_lot_attr_query
           || ') mln, mtl_onhand_serial_v ';
Line: 8404

           || 'SELECT item, revision from'
           || '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
           || p_lot_attr_query
           || ') mln, '
           || '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
           || p_serial_attr_query
           || ') msn, mtl_onhand_serial_v ';
Line: 8534

         query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
Line: 8578

      query_str  := 'SELECT item, revision from( ';
Line: 8581

        query_str  := query_str || 'SELECT item, revision from ' || table_required;
Line: 8586

           || 'SELECT item, revision from'
           || '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
           || p_lot_attr_query
           || ') mln, '
           || table_required;
Line: 8683

         query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
Line: 8716

        query_str  := query_str || 'SELECT item, revision from ' || table_required;
Line: 8722

           || 'SELECT item, revision from'
           || '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
           || p_lot_attr_query
           || ') mln, mtl_onhand_serial_v ';
Line: 8818

         query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
Line: 8869

      query_str  := 'SELECT item, revision ';
Line: 8875

        query_str  := query_str || ', (select outermost_lpn_id from wms_license_plate_numbers wlpn ';
Line: 8940

                     query_str || ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 ' || p_serial_attr_query
                     || ') msn ';
Line: 8945

        query_str  := query_str || ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 ' || p_lot_attr_query || ') mln ';
Line: 8951

           || ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
           || p_lot_attr_query
           || ') mln '
           || ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
           || p_serial_attr_query
           || ') msn ';
Line: 8988

         query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
Line: 9342

      SELECT lot_control_code
        INTO lot_control
        FROM mtl_system_items
       WHERE organization_id = p_organization_id
         AND inventory_item_id = p_inventory_item_id;
Line: 9377

        query_str  := query_str || 'SELECT lot_number from ' || table_required;
Line: 9382

           || 'SELECT lot_number from'
           || '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
           || p_lot_attr_query
           || ') mln, '
           || table_required;
Line: 9413

         query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
Line: 9540

        query_str  := query_str || 'SELECT lot_number from ' || table_required;
Line: 9546

           || 'SELECT lot_number from'
           || '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
           || p_serial_attr_query
           || ') msn, '
           || table_required;
Line: 9556

           || 'SELECT lot_number from'
           || '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
           || p_lot_attr_query
           || ') mln, '
           || table_required;
Line: 9566

           || 'SELECT lot_number from'
           || '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
           || p_lot_attr_query
           || ') mln, '
           || '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
           || p_serial_attr_query
           || ') msn, '
           || table_required;
Line: 9605

         query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
Line: 9730

      query_str  := 'SELECT lot_number from( ';
Line: 9733

        query_str  := query_str || 'SELECT lot_number from ' || table_required;
Line: 9738

           || 'SELECT lot_number from'
           || '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
           || p_lot_attr_query
           || ') mln, '
           || table_required;
Line: 9769

         query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
Line: 9833

         || ' ( select null from mtl_system_items msi WHERE '
         || ' moq.organization_id = msi.organization_id and '
         || ' moq.inventory_item_id =  msi.inventory_item_id and '
         || ' item_serial_control in (1,6) ) ';
Line: 9876

        query_str  := query_str || 'SELECT lot_number from ' || table_required;
Line: 9882

           || 'SELECT lot_number from'
           || '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
           || p_lot_attr_query
           || ') mln, '
           || table_required;
Line: 9994

      query_str  := 'SELECT lot_number  ';
Line: 10000

        query_str  := query_str || ', (select outermost_lpn_id from wms_license_plate_numbers wlpn ';
Line: 10066

                     query_str || ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 ' || p_serial_attr_query
                     || ') msn ';
Line: 10071

        query_str  := query_str || ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 ' || p_lot_attr_query || ') mln ';
Line: 10077

           || ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
           || p_lot_attr_query
           || ') mln '
           || ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
           || p_serial_attr_query
           || ') msn ';
Line: 10123

         query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
Line: 10185

           || ' ( select null from mtl_system_items msi WHERE '
           || ' mol.organization_id = msi.organization_id and '
           || ' mol.inventory_item_id =  msi.inventory_item_id and '
           || ' item_serial_control in (1,6) ) ';
Line: 10193

           || ' ( select null from mtl_system_items msi WHERE '
           || ' mol.organization_id = msi.organization_id and '
           || ' mol.inventory_item_id =  msi.inventory_item_id and '
           || ' item_serial_control in (2,5) ) ';
Line: 10480

      SELECT serial_number_control_code
        INTO serial_control
        FROM mtl_system_items
       WHERE organization_id = p_organization_id
         AND inventory_item_id = p_inventory_item_id;
Line: 10508

        query_str  := query_str || 'SELECT serial_number from ' || table_required;
Line: 10514

           || 'SELECT serial_number from'
           || '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
           || p_serial_attr_query
           || ') msn, '
           || table_required;
Line: 10524

           || 'SELECT serial_number from'
           || '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
           || p_lot_attr_query
           || ') mln, mtl_onhand_serial_v ';
Line: 10533

           || 'SELECT serial_number from'
           || '(SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
           || p_lot_attr_query
           || ') mln, '
           || '(SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
           || p_serial_attr_query
           || ') msn, mtl_onhand_serial_v ';
Line: 10657

         query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
Line: 10709

      query_str  := 'SELECT serial_number  ';
Line: 10715

        query_str  := query_str || ', (select outermost_lpn_id from wms_license_plate_numbers wlpn ';
Line: 10781

                     query_str || ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 ' || p_serial_attr_query
                     || ') msn ';
Line: 10786

        query_str  := query_str || ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 ' || p_lot_attr_query || ') mln ';
Line: 10792

           || ', (SELECT lot_number lot_num FROM mtl_lot_numbers WHERE 1=1 '
           || p_lot_attr_query
           || ') mln '
           || ', (SELECT serial_number serial_num FROM mtl_serial_numbers WHERE 1=1 '
           || p_serial_attr_query
           || ') msn ';
Line: 10918

         query_str := query_str || ' (select vendor_site_id from po_vendor_sites_all ';
Line: 11181

      SELECT DISTINCT grade_control_flag
        INTO grade_control
        FROM mtl_system_items
       WHERE inventory_item_id = p_inventory_item_id;
Line: 11206

       query_str  := query_str || ' SELECT grade_code from ' || table_required;
Line: 11283

        query_str  := query_str || ' and EXISTS ( SELECT 1 ' ;
Line: 11301

      query_str  := query_str || ' SELECT grade_code from ' || table_required;
Line: 11386

        query_str  := query_str || ' and EXISTS ( SELECT 1 ' ;
Line: 11406

      query_str  := query_str || ' SELECT grade_code from ' || table_required;
Line: 11493

        query_str  := query_str || ' and EXISTS ( SELECT 1 ' ;
Line: 11649

      SELECT   fdfcu.form_left_prompt
             , fdfcu.application_column_name
          FROM fnd_descr_flex_col_usage_vl fdfcu, fnd_application_vl fa
         WHERE fdfcu.application_id = fa.application_id
           AND fa.application_short_name = 'INV'
           AND fdfcu.descriptive_flexfield_name = 'MTL_LOT_NUMBERS'
           AND(
               fdfcu.descriptive_flex_context_code IN(
                 SELECT fdfc.descriptive_flex_context_code
                   FROM fnd_descr_flex_contexts_vl fdfc
                  WHERE fdfc.global_flag = 'Y'
                    AND fdfc.descriptive_flexfield_name = 'MTL_LOT_NUMBERS'
                    AND fdfc.application_id = fa.application_id)
               OR fdfcu.descriptive_flex_context_code = p_mln_context_code
              )
           AND fdfcu.enabled_flag = 'Y'
      ORDER BY fdfcu.column_seq_num;
Line: 11697

      SELECT   fdfcu.form_left_prompt
             , fdfcu.application_column_name
          FROM fnd_descr_flex_col_usage_vl fdfcu, fnd_application_vl fa
         WHERE fdfcu.application_id = fa.application_id
           AND fa.application_short_name = 'INV'
           AND fdfcu.descriptive_flexfield_name = 'MTL_LOT_NUMBERS'
           AND(
               fdfcu.descriptive_flex_context_code IN(
                 SELECT fdfc.descriptive_flex_context_code
                   FROM fnd_descr_flex_contexts_vl fdfc
                  WHERE fdfc.global_flag = 'Y'
                    AND fdfc.descriptive_flexfield_name = 'MTL_LOT_NUMBERS'
                    AND fdfc.application_id = fa.application_id)
               OR fdfcu.descriptive_flex_context_code = p_mln_context_code
              )
           AND fdfcu.enabled_flag = 'Y'
      ORDER BY fdfcu.column_seq_num;
Line: 11723

    SELECT attribute1
         , attribute2
         , attribute3
         , attribute4
         , attribute5
         , attribute6
         , attribute7
         , attribute8
         , attribute9
         , attribute10
         , attribute11
         , attribute12
         , attribute13
         , attribute14
         , attribute15
         , attribute_category
      INTO l_attribute(1)
         , l_attribute(2)
         , l_attribute(3)
         , l_attribute(4)
         , l_attribute(5)
         , l_attribute(6)
         , l_attribute(7)
         , l_attribute(8)
         , l_attribute(9)
         , l_attribute(10)
         , l_attribute(11)
         , l_attribute(12)
         , l_attribute(13)
         , l_attribute(14)
         , l_attribute(15)
         , l_mln_context_code
      FROM mtl_lot_numbers
     WHERE inventory_item_id = p_inventory_item_id
       AND organization_id = p_organization_id
       AND lot_number = p_lot_number;
Line: 11788

      SELECT   fdfcu.form_left_prompt
             , fdfcu.application_column_name
          FROM fnd_descr_flex_col_usage_vl fdfcu, fnd_application_vl fa
         WHERE fdfcu.application_id = fa.application_id
           AND fa.application_short_name = 'INV'
           AND fdfcu.descriptive_flexfield_name = 'MTL_SERIAL_NUMBERS'
           AND(
               fdfcu.descriptive_flex_context_code IN(
                 SELECT fdfc.descriptive_flex_context_code
                   FROM fnd_descr_flex_contexts_vl fdfc
                  WHERE fdfc.global_flag = 'Y'
                    AND fdfc.descriptive_flexfield_name = 'MTL_SERIAL_NUMBERS'
                    AND fdfc.application_id = fa.application_id)
               OR fdfcu.descriptive_flex_context_code = p_msn_context_code
              )
           AND fdfcu.enabled_flag = 'Y'
      ORDER BY fdfcu.column_seq_num;
Line: 11836

      SELECT   fdfcu.form_left_prompt
             , fdfcu.application_column_name
          FROM fnd_descr_flex_col_usage_vl fdfcu, fnd_application_vl fa
         WHERE fdfcu.application_id = fa.application_id
           AND fa.application_short_name = 'INV'
           AND fdfcu.descriptive_flexfield_name = 'MTL_SERIAL_NUMBERS'
           AND(
               fdfcu.descriptive_flex_context_code IN(
                 SELECT fdfc.descriptive_flex_context_code
                   FROM fnd_descr_flex_contexts_vl fdfc
                  WHERE fdfc.global_flag = 'Y'
                    AND fdfc.descriptive_flexfield_name = 'MTL_SERIAL_NUMBERS'
                    AND fdfc.application_id = fa.application_id)
               OR fdfcu.descriptive_flex_context_code = p_msn_context_code
              )
           AND fdfcu.enabled_flag = 'Y'
      ORDER BY fdfcu.column_seq_num;
Line: 11862

    SELECT attribute1
         , attribute2
         , attribute3
         , attribute4
         , attribute5
         , attribute6
         , attribute7
         , attribute8
         , attribute9
         , attribute10
         , attribute11
         , attribute12
         , attribute13
         , attribute14
         , attribute15
         , attribute_category
      INTO l_attribute(1)
         , l_attribute(2)
         , l_attribute(3)
         , l_attribute(4)
         , l_attribute(5)
         , l_attribute(6)
         , l_attribute(7)
         , l_attribute(8)
         , l_attribute(9)
         , l_attribute(10)
         , l_attribute(11)
         , l_attribute(12)
         , l_attribute(13)
         , l_attribute(14)
         , l_attribute(15)
         , l_msn_context_code
      FROM mtl_serial_numbers
     WHERE inventory_item_id = p_inventory_item_id
       AND current_organization_id = p_organization_id
       AND serial_number = p_serial_number;