Revaluation in Oracle Fixed Assets

REVALUATION IN ORACLE FIXED ASSETS
The asset cost is $10,000, the life is 5 years, and using straight-line depreciation.
In Year 2, Quarter 1 you revalue the asset using a revaluation rate of 5%.
Then in Year 4, Quarter 1 you revalue the asset again using a revaluation rate of -10%
Period(Yr,Qtr)	Asset Cost	Depreciation Exp.	Acc. Depreciation Exp.	Revaluation Reserve
Yr1, Qtr1	10,000	500	500	0
Qtr2	10,000	500	1000	0
Qtr3	10,000	500	1500	0
Qtr4	10,000	500	2000	0
Sum		2000
Revaluation by 10%... => Cost * 10% = 500
Reval.1 => 5%	10,500	0.00	2100	400
Period(Yr,Qtr)	Asset Cost	Depreciation Exp.	Acc. Depreciation Exp.	Revaluation Reserve
Yr2, Qtr1	10,500	525	2625	400
Qtr2	10,500	525	3150	400
Qtr3	10,500	525	3675	400
Qtr4	10,500	525	4200	400
Sum		2100
Period(Yr,Qtr)	Asset Cost	Depreciation Exp.	Acc. Depreciation Exp.	Revaluation Reserve
Yr3, Qtr1	10,500	525	4725	400
Qtr2	10,500	525	5250	400
Qtr3	10,500	525	5775	400
Qtr4	10,500	525	6300	400
Sum		2100
Revaluation by -10%... => Cost * -10% = -1050
Reval.1 => 5%	9,450	0.00	5670	-20
Period(Yr,Qtr)	Asset Cost	Depreciation Exp.	Acc. Depreciation Exp.	Revaluation Reserve
Yr4, Qtr1	9,450	472.5	6142.5	-20
Qtr2	9,450	472.5	6615	-20
Qtr3	9,450	472.5	7087.5	-20
Qtr4	9,450	472.5	7560	-20
Sum		1890
Period(Yr,Qtr)	Asset Cost	Depreciation Exp.	Acc. Depreciation Exp.	Revaluation Reserve
Yr5, Qtr1	9,450	472.5	8032.5	-20
Qtr2	9,450	472.5	8505	-20
Qtr3	9,450	472.5	8977.5	-20
Qtr4	9,450	472.5	9450	-20
Sum		1890
Retirement
	0	0	0	-20
Oracle Assets Revaluation		Year2, Revaluation by 10%
			Dr	Cr
	Asset Cost		500
		Revaluation Reserve		400
		Acc.Depr.Expense		100
Oracle Assets Revaluation		Year4, Revaluation by -10%
			Dr	Cr
	Revaluation Reserve		420
	Acc.Depr.Expense		630
		Asset Cost		1050

Acc. Depre.Exp =  Existing Acc. Depre.Exp + [Existing Acc.Depr.Exp * (Revaluation Rate/100)]
Acc. Depre.Exp =  2000 + [2000 * (5/100)]

Revaluation Reserve = Existing Revaluation Reserve + (Change in Note Book Value)
Revaluation Reserve = 0 + (8400-8000)
Change in Note Book Value = [Reval1 => 10,500 - 2100] - [Asset Cost - Acc. Depre. Expense]
New Depre. Exp. = New Asset Cost[10,500]/20
[20= Total Life * Total Quarters in Single Year]

Oracle EBS- Assets

Net Book Value = Original Cost - Accumulated Depreciation

You cannot retire an asset if it is added in current period

You cannot transfer an asset to a future period.

Gain/Loss = Proceeds of Sale - Cost of Removal - NBV + Revaluation Reserved

Asset Addition through Oracle Payables (Procure to Pay)

ORACLE PRIVILEGES

PRIVILEGE
"PRIVILEGE" is a type of authority to execute particular SQL statement or right access another user's objects.

Oracle define two types of privileges

• System Privilege and
• Object Privilege

System Privilege
Each user needs some system privilege to perform particular database operations.
You can see all system privileges by query

select privilege, name from system_privilege_map


Some Commonly used System privileges are:-

System Privilege	Capability
ALTER DATABASE	Make changes to the database
CREATE ANY INDEX	Create an index in any schema
CREATE PROCEDURE	Create a function, procedure, or package in your own schema.
CREATE SESSION	Connect to the database.
CREATE SYNONYM	Create a private synonym in your own schema.
CREATE PUBLIC SYNONYM	Create a public synonym.
CREATE VIEW	Create a view in your Schema
CREATE TABLE	Create a table in your own schema.
CREATE TABLESPACE	Create a new tablespace in the database.
CREATE USER	Create a user account/schema.
ALTER USER	Make changes to a user account/schema.

By Query Examples:-

grant create session, create table, unlimited tablespace to shariq


Here "shariq" is a Table, by above privileges Table "shariq" can connect to Database, creates table with unlimited table space.


However, you can also see how many privileges you have assigned from dba by query.
select * from session_privs;

Object Privilege

Object Privilege allow users to perform certain action on Database objects such as executing DML statements on tables.

Some Commonly used System privileges are:-

Object Privilege Allows a User to SELECT Perform a select INSERT Perform an insert UPDATE Perform an update DELETE Perform a delete EXECUTE Execute a stored procedure

By Query Examples:-

grant select, insert, update on emp to sha

By above query, user "sha" can access "scott's" schema for Table emp selection, insertion as well as updation.

Connect to "sha" user

conn sha/sha

For Selection

select * from scott.emp;

For Updation 

 update scott.emp set ename = 'Sharique' where empno = 7369;

Oracle Data Loader

Oracle Data Loader takes Data from any external file. Here I'll perform Oracle Data Loader from an excel file (CSV Format).
First make an excel file. Check the Image. Kindly save it in CSV format. I have saved the Excel File as 'Emp_Info.csv' as destination 'E:\Ex_Dir\Emp_Info.csv'

After making Excel Sheet, Connect to SysDba to make directory and also to give the privileges to any common user for read and write the directory. Mostly Scott and other Users unable to make directory and read, write it because of less priveleges.

Conn sys/oracle as Sysdba;

Create Directory EXDIR as 'E:\Ex_Dir';

You can give any name as Directory name but make sure the destination would be the same where you have saved the excel sheet like (as 'E:\Ex_Dir')

After that give the rights to any user you want.

Grant All on Directory EXDIR to Scott;

or 

GRANT READ, WRITE ON DIRECTORY EXDIR TO SCOTT;

Now Connect to Scott.

& Write the following Code.

CREATE TABLE EX_ABC

(

ID NUMBER,

FNAME VARCHAR2(20),

JOB VARCHAR2(20)

)

ORGANIZATION EXTERNAL

(

TYPE ORACLE_LOADER DEFAULT DIRECTORY EXDIR

ACCESS PARAMETERS

(

FIELDS TERMINATED BY ','

)

LOCATION ('EMP_INFO.csv'))

The Attributes should be in the same manner/format datatype like your excel sheet. 

Here (FIELDS TERMINATED BY ',')  indicates that you have made comma separated file,

LOCATION ('EMP_INFO.csv')) indicates your excel file name.

 Thank you. . .  !!

Have a Nice Day...!!!! 

SQL Server- Encryption/Decryption (functions, procedures, triggers and views)

I have made some functions, procedures, triggers and views in SQL Server 2008 its level was encrypted by using “WITH ENCRYPTION”. I was looking to secure my functions, procedures, triggers and views secure so that no one would be able to use it.

After having some sort of working, I came to know that if the procedure or user defined function created “WITH ENCRYPTION”, there is no possible ways to decrypt it by SQL Server commands.

Well, if anyone wants to decrypt it then you will have to use the freeware “Optillect SQL Decryptor”. It will decrypt your functions, procedures, triggers and views.

Just download “Optillect SQL Decryptor” , connect it by using your desired connection and decrypt it whatever you want.

Have a nice Day..!

Some Useful Data Dictionary VIEWS

Table spaces


dba_tablespacesuser_tablespaces 

Tablespace Quotas

dba_ts_quotas
user_ts_quotas
Data Files

dba_data_files
v$backup_datafile
v$datafile
v$datafile_copy
v$datafile_header
Free Space

dba_free_space
Segments

dba_segments
v$segment_statistics
Extents

dba_extents
Blocks

v$database_block_corruption

Groups

dba_tablespace_groups
SYSAUX Tablespace

v_$sysaux_occupants
Temp Tablespace

dba_temp_files
Undo Tablespace

dba_rollback_segs
dba_undo_extents
v$rollstat
v$undostat
Transportable Tablespaces

transport_set_violations

Connect Oracle10g Database with DotNet Framework using C#

Namespaces


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;

Button Click Event to Display Data on DataGridView


private void button1_Click(object sender, EventArgs e)
{
string connectionString = "provider=MSDAORA;data source=ORCL;user id=SCOTT;password=TIGER";
OleDbConnection myOleDbConnection = new OleDbConnection(connectionString);
OleDbDataAdapter da = new OleDbDataAdapter("select * from emp",myOleDbConnection );
DataSet ds = new DataSet();
da.Fill(ds, "emp");
dataGridView1.DataSource = ds.Tables["EMP"].DefaultView;
}