APPS.INVIDIT1 SQL Statements

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

Line: 12

,  X_updateable_item            OUT  NOCOPY varchar2
,  X_default_status             OUT  NOCOPY varchar2
,  x_default_uom_b              OUT  NOCOPY VARCHAR2
,  x_default_uom                OUT  NOCOPY VARCHAR2
,  x_default_uom_code           OUT  NOCOPY VARCHAR2
,  x_default_uom_class          OUT  NOCOPY VARCHAR2
,  x_time_uom_class             OUT  NOCOPY VARCHAR2
,  x_default_lot_status_id      OUT  NOCOPY NUMBER
,  x_default_lot_status         OUT  NOCOPY VARCHAR2
,  x_default_serial_status_id   OUT  NOCOPY NUMBER
,  x_default_serial_status      OUT  NOCOPY VARCHAR2
,  x_Item_Category_Set_id       OUT  NOCOPY NUMBER
,  x_Item_Category_Structure_id OUT  NOCOPY NUMBER
,  x_Item_Category_Validate_Flag OUT NOCOPY VARCHAR2--Bug:3578024
,  x_Item_Category_Set_Ctrl_level OUT NOCOPY VARCHAR2--Bug:3723668
,  x_Default_Template_id        OUT  NOCOPY NUMBER
,  x_Default_Template_Name      OUT  NOCOPY VARCHAR2
,  X_icgd_option                OUT NOCOPY varchar2
,  X_allow_item_desc_update_flag OUT NOCOPY varchar2
,  X_rfq_required_flag          OUT NOCOPY varchar2
,  X_receiving_flag             OUT NOCOPY varchar2
,  X_taxable_flag               OUT NOCOPY varchar2
,  X_org_locator_control        OUT NOCOPY number
,  X_org_expense_account        OUT NOCOPY number
,  X_org_encumbrance_account    OUT NOCOPY number
,  X_org_cost_of_sales_account  OUT NOCOPY number
,  X_org_sales_account          OUT NOCOPY number
,  X_serial_generation          OUT NOCOPY number
,  X_lot_generation             OUT NOCOPY number
,  X_cost_method                OUT NOCOPY number
,  X_category_flex_structure    OUT NOCOPY number
,  X_bom_enabled_status         OUT NOCOPY number
,  X_purchasable_status         OUT NOCOPY number
,  X_transactable_status        OUT NOCOPY number
,  X_stockable_status           OUT NOCOPY number
,  X_wip_status                 OUT NOCOPY number
,  X_cust_ord_status            OUT NOCOPY number
,  X_int_ord_status             OUT NOCOPY number
,  X_invoiceable_status         OUT NOCOPY number
,  X_order_by_segments          OUT NOCOPY varchar2
,  X_product_family_templ_id    OUT NOCOPY number
,  X_encumbrance_reversal_flag  OUT NOCOPY NUMBER --* Added for Bug #3818342
/* Start Bug 3713912 */
,X_recipe_enabled_status OUT NOCOPY number,
X_process_exec_enabled_status OUT NOCOPY number
/* End Bug 3713912 */
/* Adding attributes for R12 */
,  X_tp_org                  OUT NOCOPY VARCHAR2

)
IS

  master_org    number;

Line: 84

        select attribute_name, status_control_code
        from mtl_item_attributes
        where status_control_code is not null;

Line: 95

        select application_column_name
        from fnd_id_flex_segments
        where application_id = 401
        and id_flex_code = 'MSTK'
        and id_flex_num = 101
        and enabled_flag = 'Y'
        order by segment_num;

Line: 107

  select a.master_organization_id, b.organization_code
   , DECODE(X_mode,'DEFINE',NVL(b.encumbrance_reversal_flag,2),NVL(a.encumbrance_reversal_flag,2)) --* Added for Bug #3818342
  into master_org, X_master_org_code, X_encumbrance_reversal_flag
  from mtl_parameters a, mtl_parameters b
  where a.organization_id = X_org_id
  and a.master_organization_id = b.organization_id;

Line: 125

  SELECT lgr.CHART_OF_ACCOUNTS_ID
  into X_master_chart_of_accounts
  FROM   gl_ledgers lgr,
         hr_organization_information hoi
  where hoi.organization_id = master_org
    and (HOI.ORG_INFORMATION_CONTEXT|| '') ='Accounting Information'
    and TO_NUMBER(DECODE(RTRIM(TRANSLATE(HOI.ORG_INFORMATION1,'0123456789',' ')), NULL, HOI.ORG_INFORMATION1,-99999)) = LGR.LEDGER_ID
    and lgr.object_type_code = 'L'
    and rownum = 1;

Line: 143

  fnd_profile.get('INV_UPDATEABLE_ITEM', X_updateable_item);

Line: 166

     SELECT structure_id, validate_flag, control_level --Bug:3578024
       INTO x_Item_Category_Structure_id, x_Item_Category_Validate_Flag, x_Item_Category_Set_Ctrl_level
     FROM mtl_category_sets_b
     WHERE category_set_id = l_Item_Category_Set_id;

Line: 190

     SELECT template_name
       INTO x_Default_Template_Name
     FROM mtl_item_templates
     WHERE template_id = l_Default_Template_id;

Line: 210

    select category_flex_structure
    into X_category_flex_structure
    from fa_system_controls;

Line: 224

    select
       unit_of_measure_tl, uom_code, uom_class
    into
       x_default_uom, x_default_uom_code, x_default_uom_class
    from
       mtl_units_of_measure_vl
    where
       unit_of_measure = uom_default;

Line: 248

        SELECT  status_code
          INTO  x_default_lot_status
        FROM  mtl_material_statuses_vl
        WHERE  status_id = c_default_lot_status_id
          AND  lot_control = 1;

Line: 264

        SELECT  status_code
          INTO  x_default_serial_status
        FROM  mtl_material_statuses_vl
        WHERE  status_id = c_default_serial_status_id
          AND  serial_control = 1;

Line: 283

    select DECODE(ORG_INFORMATION_CONTEXT,
                          'Accounting Information',
                           TO_NUMBER(ORG_INFORMATION3),
                           TO_NUMBER(NULL)) operating_unit
    into   V_operating_unit
    from   hr_organization_information
    where  organization_id = X_org_id
    and (org_information_context|| '') ='Accounting Information';

Line: 293

    select allow_item_desc_update_flag,
           rfq_required_flag,
           receiving_flag,
           taxable_flag
    into   X_allow_item_desc_update_flag,
           X_rfq_required_flag,
           X_receiving_flag,
           X_taxable_flag
    from po_system_parameters_all
    where nvl(org_id, -11) = nvl(v_operating_unit, -11);

Line: 306

      X_allow_item_desc_update_flag := null;

Line: 319

  select mp.cost_of_sales_account,
         mp.encumbrance_account,
         mp.sales_account,
         mp.expense_account,
         hr.name
  into   X_org_cost_of_sales_account,
         X_org_encumbrance_account,
         X_org_sales_account,
         X_org_expense_account,
         X_master_org_name
  from   mtl_parameters mp, hr_organization_units hr
  where  mp.organization_id = master_org
  and    mp.organization_id = hr.organization_id;

Line: 335

  select decode(mp.stock_locator_control_code, '5', '1',
                                            '4', '1',
                mp.stock_locator_control_code),
         mp.primary_cost_method,
         mp.lot_number_generation,
         mp.serial_number_generation,
	 mp.trading_partner_org_flag
  into   X_org_locator_control,
         X_cost_method,
         X_lot_generation,
         X_serial_generation,
	 X_tp_org
  from   mtl_parameters mp
  where  mp.organization_id = X_org_id;

Line: 651

      SELECT  category_id, NULL
      FROM  mtl_item_categories
      WHERE
              inventory_item_id = X_item_id
         AND  organization_id   = X_org_id
         AND  category_set_id = p_Folder_Category_Set_id;

Line: 660

    SELECT subinventory_code, default_type
    FROM   mtl_item_sub_defaults
    WHERE  inventory_item_id = X_Item_Id
      AND  organization_id   = X_org_id; --Bug:2791548

Line: 678

      	SELECT full_name INTO X_buyer
	FROM   per_people_f
	WHERE  person_id = X_buyer_id
	  AND  trunc(sysdate) between effective_start_date and effective_end_date;

Line: 685

               SELECT full_name INTO X_buyer
	       FROM   per_people_f
	       WHERE  person_id = X_buyer_id;

Line: 698

      select hazard_class
      into X_hazard_class
      from po_hazard_classes
      where hazard_class_id = X_hazard_class_id;

Line: 711

      select un_number, description
      into X_un_number, X_un_description
      from po_un_numbers
      where un_number_id = X_un_number_id;

Line: 729

      select picking_rule_name
      into X_picking_rule
      from mtl_picking_rules
      where picking_rule_id = X_picking_rule_id;

Line: 741

      select rule_name
      into X_atp_rule
      from mtl_atp_rules
      where rule_id = X_atp_rule_id;

Line: 753

      select name
      into X_payment_terms
      from ra_terms
      where term_id = X_payment_terms_id;

Line: 765

      select name
      into X_default_shipping_org_dsp
      from hr_organization_units
      where organization_id = X_default_shipping_org;

Line: 781

      select name
      into X_accounting_rule
      from ra_rules
      where rule_id = X_accounting_rule_id;

Line: 793

      select name
      into X_invoicing_rule
      from ra_rules
      where rule_id = X_invoicing_rule_id;

Line: 809

      select rule_name
      into X_atp_rule
      from mtl_atp_rules
      where rule_id = X_atp_rule_id;

Line: 821

      select mp.organization_code,hou.name
      into X_source_organization, X_source_org_name
      from hr_organization_units hou
          ,mtl_parameters mp
      where hou.organization_id = mp.organization_id
      and   mp.organization_id  = X_source_organization_id;

Line: 841

      select unit_of_measure_tl
        into X_weight_uom
      from mtl_units_of_measure_vl
      where uom_code = X_weight_uom_code;

Line: 853

      select unit_of_measure_tl
        into X_volume_uom
      from mtl_units_of_measure_vl
      where uom_code = X_volume_uom_code;

Line: 872

      select meaning
      into X_item_type_dsp
      from fnd_common_lookups
      where lookup_code = X_item_type
      and lookup_type = 'ITEM_TYPE';

Line: 885

      select meaning
      into X_conversion_dsp
      from mfg_lookups
      where lookup_type = 'MTL_CONVERSION_TYPE'
      and lookup_code = X_conversion;

Line: 902

    select unit_of_measure_tl
      into X_service_duration_period
    from mtl_units_of_measure_vl
    where uom_code = X_service_duration_per_code;

Line: 916

    select unit_of_measure_tl, uom_class
      into x_primary_uom, x_uom_class
    from  mtl_units_of_measure_vl
    where uom_code = p_primary_uom_code;

Line: 931

         select  name
           into  X_coverage_schedule
         from  oks_coverage_templts_v
         where  id = X_coverage_schedule_id;

Line: 945

      select meaning
      into X_container_type_dsp
      from fnd_common_lookups
      where lookup_code = X_container_type
      and lookup_type = 'CONTAINER_TYPE';

Line: 960

        SELECT  unit_of_measure_tl
          INTO  x_dimension_uom
        FROM  mtl_units_of_measure_vl
        WHERE  uom_code = p_dimension_uom_code;

Line: 979

        SELECT  status_code
          INTO  x_default_lot_status
        FROM  mtl_material_statuses_vl
        WHERE  status_id = p_default_lot_status_id
          AND  lot_control = 1;

Line: 993

         SELECT  status_code
         INTO  x_default_material_status
         FROM  mtl_material_statuses_vl
         WHERE  status_id = p_default_material_status_id
         AND  onhand_control = 1;

Line: 1006

        SELECT  status_code
          INTO  x_default_serial_status
        FROM  mtl_material_statuses_vl
        WHERE  status_id = p_default_serial_status_id
          AND  serial_control = 1;

Line: 1021

        select meaning
          into x_eam_activity_type
        from  mfg_lookups
        where lookup_type = 'MTL_EAM_ACTIVITY_TYPE'
                and lookup_code = p_eam_activity_type_code;

Line: 1034

        select meaning
          into x_eam_activity_cause
        from  mfg_lookups
        where lookup_type = 'MTL_EAM_ACTIVITY_CAUSE'
          and lookup_code = p_eam_activity_cause_code;

Line: 1047

        select meaning
          into x_eam_act_shutdown_status_dsp
        from  mfg_lookups
        where lookup_type = 'BOM_EAM_SHUTDOWN_TYPE'
          and lookup_code = p_eam_act_shutdown_status;

Line: 1060

        select meaning
          into x_eam_activity_source
        from fnd_lookup_values_vl
        where lookup_type = 'MTL_EAM_ACTIVITY_SOURCE'
          and lookup_code = p_eam_activity_source_code;

Line: 1073

        SELECT  unit_of_measure_tl, uom_class
          INTO  x_secondary_uom, x_secondary_uom_class
        FROM  mtl_units_of_measure_vl
        WHERE  uom_code = p_secondary_uom_code;

Line: 1086

         SELECT secondary_uom_code
	   INTO l_sec_uom_code
	   FROM mtl_system_items
          WHERE inventory_item_id = X_item_id
	    AND secondary_uom_code IS NOT NULL
	    AND rownum = 1;

Line: 1098

           SELECT uom_class
	     INTO x_secondary_uom_class
	     FROM mtl_units_of_measure_vl
	    WHERE uom_code = l_sec_uom_code;

Line: 1113

        SELECT  meaning
          INTO  x_contract_item_type
        FROM  fnd_lookup_values_vl
        WHERE  lookup_type = 'OKB_CONTRACT_ITEM_TYPE'
          AND  lookup_code = p_contract_item_type_code;

Line: 1173

         SELECT UNIT_OF_MEASURE INTO X_charge_unit_of_measure
         from mtl_units_of_measure_vl     --Bug 5174403
         WHERE UOM_CODE = X_charge_periodicity_code;

Line: 1184

       select inventory_item_status_code_tl INTO X_inv_item_status_code_tl
         from mtl_item_status
	where inventory_item_status_code = X_inv_item_status_code;

Line: 1218

  select count(1)
  into source_item
  from dual
  where X_source_org in (
        select organization_id
        from mtl_system_items
        where (inventory_item_id = nvl(X_new_item_id, -11)
        or inventory_item_id = nvl(X_item_id, -11))
        )
  and rownum = 1;

Line: 1233

   select count(1)
   into   source_item
   from   mtl_system_items_b
   where  (inventory_item_id = nvl(X_new_item_id, -11)
           or inventory_item_id = nvl(X_item_id, -11))
   and    organization_id= X_source_org;

Line: 1252

      select count(1)
      into nettable_sub
      from mtl_secondary_inventories
      where secondary_inventory_name=nvl(X_source_sub, secondary_inventory_name)
      and availability_type = 1
      and rownum = 1;

Line: 1266

    select count(1)
    into org_network
    from mtl_interorg_parameters
    where to_organization_id = X_org_id
    and from_organization_id = X_source_org
    and rownum = 1;

Line: 1336

    sql_stmt := 'INSERT INTO MTL_CATALOG_SEARCH_ITEMS ( ' ||
                     ' SELECT :handle,            MSI.INVENTORY_ITEM_ID, '||
		     '        MSI.ORGANIZATION_ID,MSI.DESCRIPTION,       '||
		     '        MSI.PRIMARY_UOM_CODE, MSI.RESERVABLE_TYPE  '||
		     ' FROM  MTL_SYSTEM_ITEMS_VL MSI                     '||
		     ' WHERE 1= 1                                        ';

Line: 1376

       sql_stmt := sql_stmt || ' AND EXISTS (SELECT NULL FROM MTL_MFG_PART_NUMBERS MPN ' ||
                                             ' WHERE MPN.MANUFACTURER_ID   = :p_manufacturer_id '||
					     ' AND   MPN.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID ';

Line: 1439

       l_supplier_stmt := 'SELECT NULL FROM ' || l_sup_tab_list || ' WHERE ' || l_sup_where_clause;

Line: 1446

          l_xref_row_stmt := '(SELECT MCR.INVENTORY_ITEM_ID FROM MTL_CROSS_REFERENCES MCR ' ||
                             ' WHERE MCR.CROSS_REFERENCE_TYPE = :xref_type' ||  l_xref_bind ||
			     '   AND MCR.CROSS_REFERENCE = :xref_val' || l_xref_bind || ')';

Line: 1463

          l_relation_row_stmt := '(SELECT MRI.INVENTORY_ITEM_ID FROM MTL_RELATED_ITEMS_VIEW MRI ' ||
                                 ' WHERE MRI.RELATIONSHIP_TYPE_ID = :relation_type' || l_relation_bind ||
				 '   AND MRI.RELATED_ITEM_ID = :related_item' || l_relation_bind || ')';

Line: 1480

          l_category_row_stmt := '(SELECT MIC.INVENTORY_ITEM_ID FROM MTL_ITEM_CATEGORIES MIC ' ||
                                 ' WHERE MIC.CATEGORY_SET_ID = :category_set' || l_category_bind ||
	                         '   AND MIC.CATEGORY_ID = :category_id' || l_category_bind ||
				 '   AND MIC.ORGANIZATION_ID = MSI.ORGANIZATION_ID)';

Line: 1500

              l_element_row_stmt := '(SELECT DEV.INVENTORY_ITEM_ID FROM MTL_DESCR_ELEMENT_VALUES DEV ' ||
                                  ' WHERE DEV.ELEMENT_NAME = :element_name' || l_element_bind ||
	                                '   AND DEV.ELEMENT_VALUE = :element_val' || l_element_bind || ')';