DBA Data[Home] [Help]

APPS.INVIDSCS SQL Statements

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

Line: 27

  select 'X'  into temp
    from MTL_DEFAULT_CATEGORY_SETS MDCS
   where MDCS.category_set_id = current_cat_set_id
     and MDCS.functional_area_id = 1;
Line: 39

  select 'X'  into temp
    from MTL_DEFAULT_CATEGORY_SETS MDCS
   where MDCS.category_set_id = current_cat_set_id
     and MDCS.functional_area_id = 2;
Line: 51

  select 'X'  into temp
    from MTL_DEFAULT_CATEGORY_SETS MDCS
   where MDCS.category_set_id = current_cat_set_id
     and MDCS.functional_area_id = 3;
Line: 63

  select 'X'  into temp
    from MTL_DEFAULT_CATEGORY_SETS MDCS
   where MDCS.category_set_id = current_cat_set_id
     and MDCS.functional_area_id = 4;
Line: 75

  select 'X'  into temp
    from MTL_DEFAULT_CATEGORY_SETS MDCS
   where MDCS.category_set_id = current_cat_set_id
     and MDCS.functional_area_id = 5;
Line: 87

  select 'X'  into temp
    from MTL_DEFAULT_CATEGORY_SETS MDCS
   where MDCS.category_set_id = current_cat_set_id
     and MDCS.functional_area_id = 6;
Line: 99

  select 'X'  into temp
    from MTL_DEFAULT_CATEGORY_SETS MDCS
   where MDCS.category_set_id = current_cat_set_id
     and MDCS.functional_area_id = 7;
Line: 111

  select 'X'  into temp
    from MTL_DEFAULT_CATEGORY_SETS MDCS
   where MDCS.category_set_id = current_cat_set_id
     and MDCS.functional_area_id = 8;
Line: 123

  select 'X'  into temp
    from MTL_DEFAULT_CATEGORY_SETS MDCS
   where MDCS.category_set_id = current_cat_set_id
     and MDCS.functional_area_id = 9;
Line: 135

  select 'X'  into temp
    from MTL_DEFAULT_CATEGORY_SETS MDCS
   where MDCS.category_set_id = current_cat_set_id
     and MDCS.functional_area_id = 10;
Line: 147

  select 'X'  into temp
    from MTL_DEFAULT_CATEGORY_SETS MDCS
   where MDCS.category_set_id = current_cat_set_id
     and MDCS.functional_area_id = 11;
Line: 178

 select INVENTORY_ITEM_FLAG, PURCHASING_ITEM_FLAG,
        INTERNAL_ORDER_FLAG, decode(SERVICE_ITEM_FLAG,'Y',SERVICE_ITEM_FLAG,
	SERVICEABLE_PRODUCT_FLAG),
        COSTING_ENABLED_FLAG, ENG_ITEM_FLAG, CUSTOMER_ORDER_FLAG,
        MRP_PLANNING_CODE,EAM_ITEM_TYPE,CONTRACT_ITEM_TYPE_CODE	--Bug: 2527058
   into inv_item_flag, purch_item_flag,
        int_order_flag, serv_item_flag,
        cost_enab_flag, engg_item_flag, cust_order_flag,
        mrp_plan_code, eam_item_type, contract_item_type  --Bug: 2527058
   from MTL_SYSTEM_ITEMS MSI
  where MSI.inventory_item_id = current_item_id
    and MSI.organization_id = current_org_id;
Line: 193

PROCEDURE INSERT_CATSET_CHILD_ORGS(
current_inv_item_id      IN    NUMBER,
current_org_id           IN    NUMBER,
current_master_org_id    IN    NUMBER,
current_cat_set_id       IN    NUMBER,
current_cat_id           IN    NUMBER,
cat_set_control_level    IN    NUMBER,
current_created_by       IN    NUMBER := NULL -- Added Bug-6045867
)
IS

l_organizations_rec    ORG_LISTS;
Line: 210

   select
        p.organization_id
   BULK COLLECT INTO
       l_organizations_rec
   from    mtl_parameters p
    where   p.master_organization_id = current_master_org_id
    and     p.organization_id <> current_master_org_id
    and exists
       (select  'x'
        from    mtl_system_items i
        where   i.inventory_item_id = current_inv_item_id
        and     i.organization_id = p.organization_id)
    /* Bug: 4932378    and exists
	(select 'x'
	 from org_organization_definitions ood
	 where  ood.organization_id = p.organization_id
	 and    ood.inventory_enabled_flag = 'Y')*/;
Line: 229

 insert into mtl_item_categories
        (inventory_item_id,
         category_set_id,
         category_id,
         last_update_date,
         last_updated_by,
         creation_date,
         created_by,
         last_update_login,
         program_application_id,
         program_id,
         program_update_date,
         request_id,
         organization_id)
  VALUES( current_inv_item_id,
        current_cat_set_id,
        current_cat_id,
        sysdate,
        -- current_created_by,  -- bug 6045867   -- Commented for bug-6782351
        NVL(current_created_by, FND_GLOBAL.USER_ID), -- NVL added for bug-6782351
        sysdate,
        -- current_created_by,  -- bug 6045867   -- Commented for bug-6782351
        NVL(current_created_by, FND_GLOBAL.USER_ID), -- NVL added for bug-6782351
        -1,
        -1,
        -1,
        sysdate,
        -1,
        l_organizations_rec(i));
Line: 281

END INSERT_CATSET_CHILD_ORGS;
Line: 283

PROCEDURE UPDATE_CATSET_CHILD_ORGS(
current_inv_item_id      IN    NUMBER,
current_org_id           IN    NUMBER,
current_master_org_id    IN    NUMBER,
current_cat_set_id       IN    NUMBER,
current_cat_id           IN    NUMBER,
cat_set_control_level    IN    NUMBER,
old_cat_id		          IN    NUMBER,
current_last_updated_by  IN    NUMBER := NULL -- Added Bug-4949084
)
IS

l_organizations_rec    ORG_LISTS;
Line: 300

	update  mtl_item_categories c
	/*+ INDEX MTL_ITEM_CATEGORIES MTL_ITEM_CATEGORIES_U1 */
	set 	  c.category_id = current_cat_id,
		     c.last_update_date = sysdate,
           c.last_updated_by = NVL(current_last_updated_by, FND_GLOBAL.USER_ID)    -- Added Bug-4949084 @ 4886176
	where   c.inventory_item_id = current_inv_item_id
	and     c.category_set_id = current_cat_set_id
	and     c.category_id = old_cat_id
	and     c.organization_id in
		(select p.organization_id from mtl_parameters p
		 where  p.master_organization_id =
		         current_master_org_id
		 and    exists (select 'x' from mtl_system_items i
				where  i.inventory_item_id =
				  current_inv_item_id
				and    i.organization_id = p.organization_id)
		/* Bug: 4932378
		 and    exists (select 'x' from org_organization_definitions ood
				where  ood.organization_id = p.organization_id
				and    ood.inventory_enabled_flag = 'Y')*/)
        RETURNING organization_id
	BULK COLLECT INTO l_organizations_rec;
Line: 328

         ,p_dml_type          => 'UPDATE'
         ,p_inventory_item_id => current_inv_item_id
         ,p_organization_id   => l_organizations_rec(i)
         ,p_category_set_id   => current_cat_set_id
         ,p_category_id       => current_cat_id);
Line: 344

END UPDATE_CATSET_CHILD_ORGS;
Line: 356

current_last_updated_by  IN    NUMBER
)
IS
  CURSOR other_orgs_cur IS
    select organization_id
    from   mtl_system_items
    where
     inventory_item_id = current_inv_item_id and
     organization_id  <> current_org_id      and
     organization_id  in
      ( select organization_id
        from mtl_parameters
        where
          master_organization_id = current_master_org_id
      )
  ;
Line: 386

select category_set_id
into tmp_default_cat_set_id
from mtl_default_category_sets
where functional_area_id = 5;
Line: 412

  current_last_updated_by,
  tmp_cost_method,
  current_cst_item_type,
  tmp_cst_lot_size,
  tmp_cst_shrink_rate,
  tmp_cst_return,
  tmp_cst_error
 );
Line: 442

  current_last_updated_by,
  tmp_cost_method,
  current_cst_item_type,
  tmp_cst_lot_size,
  tmp_cst_shrink_rate,
  tmp_cst_return,
  tmp_cst_error
 );
Line: 471

  select primary_cost_method
  into   tmp_cost_method
  from   mtl_parameters
  where  organization_id = tmp_organization_id;
Line: 477

    select  lot_size, shrinkage_rate
    into    tmp_cst_lot_size, tmp_cst_shrink_rate
    from    cst_item_costs
    where
      inventory_item_id   = tmp_inv_item_id
      and organization_id = tmp_organization_id
      and cost_type_id    = 1;
Line: 508

PROCEDURE DELETE_CATSET_CHILD_ORGS(
current_inv_item_id      IN    NUMBER,
current_master_org_id    IN    NUMBER,
current_cat_set_id       IN    NUMBER,
current_cat_id       IN    NUMBER
)
IS

l_organizations_rec    ORG_LISTS;
Line: 527

	So, during Deleting need to use all these 4 column values to delete
	a single assignment.
   */

	delete  from mtl_item_categories c
		where c.inventory_item_id = current_inv_item_id
		and   c.category_set_id = current_cat_set_id
		and   c.category_id = current_cat_id
                and   c.organization_id in
                              (select  p.organization_id
                               from mtl_parameters p
                               where p.master_organization_id = current_master_org_id)
        RETURNING organization_id
	BULK COLLECT INTO
	l_organizations_rec;
Line: 550

            ,p_dml_type          => 'DELETE'
            ,p_inventory_item_id => current_inv_item_id
            ,p_organization_id   => l_organizations_rec(i)
            ,p_category_set_id   => current_cat_set_id
            ,p_category_id       => current_cat_id);
Line: 565

END DELETE_CATSET_CHILD_ORGS;