APPS.INV_CG_UPGRADE SQL Statements

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

Line: 24

   select
     rowid
   , secondary_inventory_name
   , material_account
   , material_overhead_account
   , resource_account
   , overhead_account
   , outside_processing_account
   , expense_account
   , encumbrance_account

   from mtl_secondary_inventories
   where (
	  default_cost_group_id is null AND
          organization_id = l_organization_id
         );

Line: 75

	 SELECT default_cost_group_id
	   INTO l_check_cost_group_id
	   FROM
	   mtl_secondary_inventories
	   WHERE
	   organization_id = l_organization_id
	   AND ROWNUM=1;

Line: 94

      SELECT primary_cost_method INTO l_primary_cost_method FROM
	mtl_parameters WHERE organization_id = l_organization_id AND ROWNUM <
	2;

Line: 228

	  update mtl_secondary_inventories
	  set default_cost_group_id = l_cost_group_id
	  where organization_id     	= l_organization_id
	  and   secondary_inventory_name 	= l_secondary_inventory_name;

Line: 323

      SELECT
--      ROWID,
      acct_period_id
   , inventory_type
   , cost_group_id
   , sum(inventory_value) inventory_value
   from mtl_period_summary
   where organization_id = l_organization_id
   group by
     acct_period_id
   , organization_id
   , inventory_type
   , cost_group_id;

Line: 338

   select
     acct_period_id
   , 1 inventory_type
   , cost_group_id
   , sum(NVL(period_end_unit_cost,0)*NVL(period_end_quantity,0)) inventory_value
   from mtl_per_close_dtls
   where organization_id = l_organization_id
   group by
     acct_period_id
   , organization_id
   , cost_group_id;

Line: 358

   select
     rowid
   , secondary_inventory
   from mtl_period_summary
   where
        (
         cost_group_id is null AND
         organization_id = l_organization_id
        );

Line: 396

   l_details_updated          boolean := FALSE;

Line: 424

                        update mtl_period_summary
                        set cost_group_id = l_cost_group_id
                        where rowid = l_rowid;

Line: 427

			if ( l_details_updated = FALSE ) then
                          update mtl_per_close_dtls
                          set cost_group_id = l_cost_group_id
                          where organization_id = l_organization_id
                          and cost_group_id = 1 ;

Line: 432

                          l_details_updated := TRUE ;

Line: 447

                        update mtl_period_summary
                        set cost_group_id = l_cost_group_id
                        where rowid = l_rowid;

Line: 464

                        update mtl_period_summary
                        set cost_group_id = l_cost_group_id
                        where rowid = l_rowid;

Line: 490

   ** Delete data. It will be reloaded from memory in just a second
   */
   delete mtl_period_summary WHERE organization_id = l_organization_id;

Line: 494

   select sysdate into l_date from dual;

Line: 516

	insert into mtl_period_summary(
	  ACCT_PERIOD_ID
        , ORGANIZATION_ID
 	, INVENTORY_TYPE
        , SECONDARY_INVENTORY
        , LAST_UPDATE_DATE
        , LAST_UPDATED_BY
        , CREATION_DATE
        , CREATED_BY
        , LAST_UPDATE_LOGIN
        , INVENTORY_VALUE
        , REQUEST_ID
        , PROGRAM_APPLICATION_ID
        , PROGRAM_ID
        , PROGRAM_UPDATE_DATE
        , COST_GROUP_ID)
	values(
          l_ps_tbl(i).acct_period_id
        , l_ps_tbl(i).organization_id
        , l_ps_tbl(i).inventory_type
        , NULL
        , l_date
        , l_user_id
        , l_date
        , l_user_id
        , l_login_id
        , l_ps_tbl(i).inventory_value
        , l_request_id
        , l_prog_appl_id
        , l_program_id
        , l_date
        , l_ps_tbl(i).cost_group_id);

Line: 566

   delete mtl_period_summary WHERE organization_id = l_organization_id;

Line: 567

   select sysdate into l_date from dual;

Line: 589

	insert into mtl_period_summary(
	  ACCT_PERIOD_ID
        , ORGANIZATION_ID
 	, INVENTORY_TYPE
        , SECONDARY_INVENTORY
        , LAST_UPDATE_DATE
        , LAST_UPDATED_BY
        , CREATION_DATE
        , CREATED_BY
        , LAST_UPDATE_LOGIN
        , INVENTORY_VALUE
        , REQUEST_ID
        , PROGRAM_APPLICATION_ID
        , PROGRAM_ID
        , PROGRAM_UPDATE_DATE
        , COST_GROUP_ID)
	values(
          l_ps_tbl(i).acct_period_id
        , l_ps_tbl(i).organization_id
        , l_ps_tbl(i).inventory_type
        , NULL
        , l_date
        , l_user_id
        , l_date
        , l_user_id
        , l_login_id
        , l_ps_tbl(i).inventory_value
        , l_request_id
        , l_prog_appl_id
        , l_program_id
        , l_date
        , l_ps_tbl(i).cost_group_id);

Line: 736

      select
	ROWID
   , material_account
   , material_overhead_account
   , resource_account
   , overhead_account
   , outside_processing_account
   , expense_account
   , encumbrance_account
   , primary_cost_method
   from mtl_parameters
   where (
          default_cost_group_id is null and
          organization_id = l_organization_id
         );

Line: 785

      SELECT default_cost_group_id
	INTO l_check_cost_group_id
	FROM
	mtl_parameters
	WHERE
	organization_id = l_organization_id
	AND ROWNUM=1;

Line: 924

        update mtl_parameters
        set default_cost_group_id = l_cost_group_id
        where organization_id = l_organization_id;

Line: 1040

   select
     rowid
   , subinventory_code
   , locator_id
   , project_id
   , task_id
   from mtl_onhand_quantities
   where organization_id = l_organization_id
   and  cost_group_id is null;

Line: 1090

      SELECT cost_group_id
	INTO l_check_cost_group_id
	FROM
	mtl_onhand_quantities
	WHERE
	organization_id = l_organization_id
	AND ROWNUM=1;

Line: 1133

			select
			  to_number(nvl(segment19,'0'))
			, to_number(nvl(segment20,'0'))
			into
			  v_project_id
			, v_task_id
			from mtl_item_locations
			where organization_id       = l_organization_id
			and   inventory_location_id = l_locator_id;

Line: 1156

			select costing_group_id
			into l_cost_group_id
			from pjm_project_parameters
			where project_id      = v_project_id
                	and   organization_id = l_organization_id;

Line: 1175

		update mtl_onhand_quantities
		set
		  cost_group_id = l_cost_group_id
		, project_id    = v_project_id
		, task_id       = v_task_id
		where rowid = l_rowid;

Line: 1205

			update mtl_onhand_quantities
			set cost_group_id = l_cost_group_id
			where rowid = l_rowid;

Line: 1332

     select period_start_date,
            schedule_close_date
     from org_acct_periods
     where (organization_id = l_organization_id
            and period_start_date <= sysdate and period_close_date is null
            and p_open_periods_only = 1);

Line: 1346

      select
	ROWID
   , transaction_id
   , subinventory_code
   , transfer_organization_id
   , transfer_subinventory
   , project_id
   , to_project_id
   , cost_group_id
   , transfer_cost_group_id
   , transfer_transaction_id
   , transaction_action_id
   , shipment_number
   , inventory_item_id
   from mtl_material_transactions
   where organization_id = l_organization_id
   and transaction_date >= l_s_date
   and transaction_date <= l_e_date
   and transaction_action_id <> 30;

Line: 1372

      select
	ROWID
   , transaction_id
   , subinventory_code
   , transfer_organization_id
   , transfer_subinventory
   , project_id
   , to_project_id
   , cost_group_id
   , transfer_cost_group_id
   , transfer_transaction_id
   , transaction_action_id
   , shipment_number
   , inventory_item_id
   from mtl_material_transactions
   where organization_id = l_organization_id
   and transaction_action_id <> 30;

Line: 1403

   select
     ms.rowid
   , ms.intransit_owning_org_id
   from mtl_supply 		  ms,
        rcv_shipment_headers 	  rsh
   where rsh.shipment_num         = l_shipment_number
   and   ms.shipment_header_id    = rsh.shipment_header_id
   and   ms.supply_type_code      = 'SHIPMENT'
   and   ms.intransit_owning_org_id is not null
   and   ms.item_id               = l_inventory_item_id
   and   ms.from_organization_id  = l_organization_id
   and   NVL(ms.cost_group_id,1) = 1;

Line: 1443

   l_cost_group_update		boolean;

Line: 1444

   l_transfer_cost_group_update boolean;

Line: 1474

          select 1
            into l_dummy
            from dual
           where exists (SELECT cost_group_id
                           FROM mtl_material_transactions
                          WHERE organization_id = l_organization_id
                            and cost_group_id is not null
                            and transaction_date >= l_s_date
                            and transaction_date <= l_e_date
                            and transaction_action_id <> 30);

Line: 1496

          select 1
            into l_dummy
            from dual
           where exists (SELECT cost_group_id
                           FROM mtl_material_transactions
                          WHERE organization_id = l_organization_id
                            and cost_group_id is not null
                            and transaction_action_id <> 30);

Line: 1566

        l_cost_group_update	     := FALSE;

Line: 1567

        l_transfer_cost_group_update := FALSE;

Line: 1594

        		l_cost_group_update	     := TRUE;

Line: 1609

        		l_cost_group_update	     := TRUE;

Line: 1638

        		l_transfer_cost_group_update := TRUE;

Line: 1644

	  select primary_cost_method into l_transfer_cost_method
	  from mtl_parameters where organization_id = l_transfer_organization_id ;

Line: 1657

        		l_transfer_cost_group_update	     := TRUE;

Line: 1662

        if (l_cost_group_update	         = TRUE) and
           (l_transfer_cost_group_update = TRUE) then
	        update mtl_material_transactions
		set
                  cost_group_id          = l_cost_group_id
		, transfer_cost_group_id = l_transfer_cost_group_id
		where transaction_id = l_transaction_id;

Line: 1669

        elsif (l_cost_group_update	    = TRUE) then
	        update mtl_material_transactions
		set cost_group_id = l_cost_group_id
		where transaction_id = l_transaction_id;

Line: 1673

	elsif (l_transfer_cost_group_update = TRUE) then
	        update mtl_material_transactions
		set transfer_cost_group_id = l_transfer_cost_group_id
		where transaction_id = l_transaction_id;

Line: 1680

        ** If intransit shipment(action_id =21), we have to update
        ** corresponding record in MTL_SUPPLY too
        */

        if (l_transaction_action_id = 21) and
	  (l_shipment_number is not null) and ( p_cost_method <> 1) THEN

	        l_table_name := 'mmt_supply';

Line: 1734

				update mtl_supply
				set cost_group_id = l_ms_cost_group_id
				where rowid = l_ms_rowid;

Line: 1854

   INSERT INTO COST_UPGR_ERROR_TABLE ( table_name, rowid_value, org_id,
				      error_mesg, proc_name)
     VALUES ( p_table_name, p_rowid, p_org_id, substr(l_msg,1,800), p_proc_name);

Line: 1870

cursor oid_cursor is select organization_id, primary_cost_method
   from mtl_parameters ;

Line: 2013

   select
     rowid
   , subinventory
   , locator_id
   FROM MTL_CYCLE_COUNT_ENTRIES
   where organization_id = l_organization_id
   and  cost_group_id is null;

Line: 2061

      SELECT cost_group_id
	INTO l_check_cost_group_id
	FROM
	MTL_CYCLE_COUNT_ENTRIES
	WHERE
	organization_id = l_organization_id
	AND ROWNUM=1;

Line: 2103

			select
			  to_number(nvl(segment19,'0'))
			, to_number(nvl(segment20,'0'))
			into
			  v_project_id
			, v_task_id
			from mtl_item_locations
			where organization_id       = l_organization_id
			and   inventory_location_id = l_locator_id;

Line: 2126

			select costing_group_id
			into l_cost_group_id
			from pjm_project_parameters
			where project_id      = v_project_id
                	and   organization_id = l_organization_id;

Line: 2145

		update mtl_cycle_count_entries
		set
		  cost_group_id = l_cost_group_id
	          where rowid = l_rowid;

Line: 2173

			update mtl_cycle_count_entries
			set cost_group_id = l_cost_group_id
			where rowid = l_rowid;

Line: 2302

   select
     rowid
   , subinventory_name
   , locator_id
   from mtl_physical_adjustments
   where organization_id = l_organization_id
   and  cost_group_id is null;

Line: 2350

      SELECT cost_group_id
	INTO l_check_cost_group_id
	FROM
	mtl_physical_adjustments
	WHERE
	organization_id = l_organization_id
	AND ROWNUM=1;

Line: 2392

			select
			  to_number(nvl(segment19,'0'))
			, to_number(nvl(segment20,'0'))
			into
			  v_project_id
			, v_task_id
			from mtl_item_locations
			where organization_id       = l_organization_id
			and   inventory_location_id = l_locator_id;

Line: 2415

			select costing_group_id
			into l_cost_group_id
			from pjm_project_parameters
			where project_id      = v_project_id
                	and   organization_id = l_organization_id;

Line: 2434

		update mtl_physical_adjustments
		set
		  cost_group_id = l_cost_group_id
		where rowid = l_rowid;

Line: 2462

			update mtl_physical_adjustments
			set cost_group_id = l_cost_group_id
			where rowid = l_rowid;

Line: 2589

   select
     rowid
   , subinventory
   , locator_id
   from mtl_physical_inventory_tags
   where organization_id = l_organization_id
   and  cost_group_id is null;

Line: 2637

      SELECT cost_group_id
	INTO l_check_cost_group_id
	FROM
	mtl_physical_inventory_tags
	WHERE
	organization_id = l_organization_id
	AND ROWNUM=1;

Line: 2679

			select
			  to_number(nvl(segment19,'0'))
			, to_number(nvl(segment20,'0'))
			into
			  v_project_id
			, v_task_id
			from mtl_item_locations
			where organization_id       = l_organization_id
			and   inventory_location_id = l_locator_id;

Line: 2702

			select costing_group_id
			into l_cost_group_id
			from pjm_project_parameters
			where project_id      = v_project_id
                	and   organization_id = l_organization_id;

Line: 2721

		update mtl_physical_inventory_tags
		set
		  cost_group_id = l_cost_group_id
		where rowid = l_rowid;

Line: 2749

			update mtl_physical_inventory_tags
			set cost_group_id = l_cost_group_id
			where rowid = l_rowid;