Fixed Asset - Basic

As Salamo Aalaikum wa Rahmatullah

In simple terms, an asset is a resource having future economic benefit owned by an entity. Future benefit could mean in terms of contributing to the profit for profit oriented organizations and providing services for non-profit oriented organizations.

Assets are formally controlled and managed within larger organizations via the use of asset tracking tools. These monitor the purchasing, upgrading, servicing, licensing, disposal etc., of both physical and non-physical assets.

Assets may be classified in many ways. 

1. Current assets (Current deposits, Inventory)
2. Long-term investments (Investments in securities)
3. Fixed assets (land,buildings, machinery, furniture, tools)
4. Intangible assets (patents, copyrights, franchises, goodwill, trademarks)


Fixed assets are those that are expected to keep on providing benefit for more than one year. Some common examples are: scientific equipment, office equipment, photo copiers, land and buildings, vehicles, Machinery, Property held for Investment purpose.

--
Ma Asalaam
Passion 4 Oracle

Oracle Inventory - Basic

As salamo alaikum wa rahmatullah

Oracle Applications uses multiple types of organizations to build the business

execution structure. At the top of the structure is the accounting set of books

SOB), defined in the General Ledger. Next, different types of organizations are

used to further define the organization structure and relationships. All organizations are defined and updated with the Define Organization form.

Set of Books: A General Ledger SOB, linked to the inventory organization,

controls the financial accounting of inventory transactions. A SOB is made up

of a chart of accounts, a financial calendar, and a currency. 
The general ledger secures transactions (journal entries, balances) by SOB.

Legal Entity. A legal entity organization defines the tax and fiscal reporting

level. The legal entity represents the legal company.

Operating Unit: An operating unit organization defines the Purchasing, Order

Entry, Accounts Payable and Accounts Receivable level of operation. An operating unit may span multiple manufacturing facilities, distribution points and sales offices, or it may be limited to a single site.

Inventory Organization: Two flavors of inventory organizations are found in

Oracle Applications. They are defined the same, and both are assigned a set

of books, a legal entity organization, an operating unit organization, and a

location. An item master organization is used for item number maintenance and

validation. This master organization serves as a data repository storing items

and item attributes, master level categories and category sets, master level

cross references, and numerous data defaults. On-hand balances, inventory

movements, and other on-going inventory activities are not performed in an item master organization. Generally, the master organization is used as the

validation organization for Purchasing and Order Entry. It is recommended

that a single item master organization be defined, even in multiple organization,

multiple sets of books environments.

In addition to the item master organization there are one or more non-master

inventory organizations. Like the item master inventory organization, the

non-master organizations are assigned a set of books, a legal entity organization and an operating unit organization. The non-master inventory organization points to a master organization and looks to the master organization for master level item attributes, master level categories, and other master level controlled data.

Note that each organization has its own set of books/legal entity/operating unit

relationship, so inventory organizations with differing SOB’s or operating units

may share the same master organization.

These non-master inventory organizations are the execution level organizations.

They hold on-hand balances and transaction history. Here is where inventory

users execute their daily activities, such as receiving and issuing material,

performing cycle counts, and viewing material availability and transaction

history. A single organization therefore generally represents a single

manufacturing site or distribution center.

Locations: A location code is an address. Each inventory organization must

be assigned at least one location code.

Subinventories: A subinventory is used as a holding point for on-hand

inventory and generally represents a stockroom, stocking area or cage used

for storing material. Subinventories are defined within inventory

organizations. An inventory organization may have any number of

sub inventories, and an asset account is assigned to each sub inventory.

Since the subinventory entity is logical, as there is not an address or

physical location description associated with it, clients may define

sub inventories for any physical or logical grouping of inventory

Stock Locators: Stock locators are an optional entity that may be used to

represent physical locations within a subinventory. You may choose to use

stock locators for selected subinventories or selected items within selected

subinventories. If locators are used, subinventory and locator track on-hand

balances. Therefore, if locators are defined to represent a shelf within a

stockroom, on-hand balances on the system would show the item and quantity

down to the physical location within the facility.

Oracle Inventory uses a key flexfield for stock locators. This presents a few

limitations for its use. Only one locator flexfield definition is allowed per

install. Therefore, if the stockroom (subinventory) wants to track material

by row, bin and shelf, it will likely define a three-segment flexfield with

segments for row, bin, and shelf. If locators are desired for another

sub inventory, even in another organization, the structure will again be 3

segments for row, bin and shelf. In addition to this limitation, locators

must be unique within an organization; you cannot use the same locator in

different sub inventories within an organization, but you can use the same locator in sub inventories in a different organization.

--
Ma Asalaam
Passion 4 Oracle

Create Database manually in SQL SERVER

As salamo alaikum wa rahmatullah


Create database Food
ON
(
 Name=FoodData1,
 Filename='C:\ManualDB\SQLSERVER12.0\DATA\FoodData1.mdf',
 Size=10MB,
 MaxSize=unlimited,
 filegrowth=1GB
)
Log ON
(
 Name=FoodLog1,
 Filename='C:\ManualDB\SQLSERVER12.0\DATA\FoodData1_log.ldf',
 Size=10MB,
 MaxSize=unlimited,
 filegrowth=1024MB
)

--

Ma Asalaam

Passion 4 Oracle

Creating Data Block using Package and Procedure in Oracle 10g

As salamo alaikum wa rahmatullah

create table tbl_mst_student (roll number(3),
name varchar2(50),course varchar2(50))

create or replace package mypack
is
type student_rec is record (stud_id tbl_mst_student.roll%type,sname tbl_mst_student.name%type,scourse tbl_mst_student.course%type);
type stub is table of student_rec index by binary_integer;
procedure stuqry(data IN OUT stub);
procedure stuins(data IN stub);
end mypack;


create or replace package body mypack
is
procedure stuqry(data IN OUT stub)
is 
i number;
cursor cstudent is select * from tbl_mst_student;
begin
open cstudent;
i:=1;
loop
fetch cstudent into data(i).stud_id,data(i).sname,data(i).scourse;
exit when cstudent%notfound;
end loop;
close cstudent;
end stuqry;

procedure stuins(data IN stub)
is
i number;
ct number;
begin
ct:=data.count;
for i in 1..ct loop 
insert into tbl_mst_student values(data(i).stud_id,data(i).sname,data(i).scourse);
commit;
end loop;
end stuins;
end mypack;
/



select * from tbl_mst_student

Ma Asalaam
---------------
Passion 4 Oracle

TYPE EXAMPLE IN ORACLE 11G

As salamo alaikum wa rahmatullah


CREATE OR REPLACE TYPE PersonObj AS OBJECT (
  first_name  VARCHAR2(50),
  last_name   VARCHAR2(50),
  date_of_birth  DATE,
  MEMBER FUNCTION getAge RETURN NUMBER
)

create or replace type body PersonObj AS
Member Function getAge Return Number AS
Begin
    Return Trunc(Months_Between(Sysdate,date_of_birth)/12);
    End getAge;
End;

create table people
(
id number(2), person PersonObj);



INSERT INTO people
VALUES (1, PersonObj('John','Doe',
        TO_DATE('01/01/1999','DD/MM/YYYY')))
        
COMMIT

DECLARE
  v_person  PersonObj;
BEGIN
  v_person := PersonObj('Jane','Doe',
              TO_DATE('01/01/1999','DD/MM/YYYY'));
  INSERT INTO people VALUES (2, v_person);
  COMMIT;
END;


SELECT p.id,
       p.person.first_name,
       p.person.getAge() age
FROM   people p;

----------------

output 

------------

--

Ma asalaam

Passion 4 Oracle

EXAMPLE OF AUTO INCREMENT COLUMN IN ORACLE

AS SALAMO ALAIKUM WA RAHMATULLAH



CREATE TABLE TBL_ITEM
(
ITM_ID NUMBER(5),
ITM_CODE VARCHAR2(30) UNIQUE NOT NULL,
ITM_DESC VARCHAR2(250),
ITM_PRICE NUMBER(10,3),
ITM_QTY NUMBER(3),
ITM_CRT_DT DATE,
ITM_UPD_DT DATE,
ITM_IS_DEL CHAR(1),
PRIMARY KEY(ITM_ID),
CHECK (ITM_IS_DEL IN ('Y','N'))
)


CREATE SEQUENCE SEQ_TBL_ITM_ID START WITH 1 INCREMENT BY 1


CREATE OR REPLACE TRIGGER TRG_ITM_ID
BEFORE INSERT ON TBL_ITEM
FOR EACH ROW
BEGIN
    SELECT SEQ_TBL_ITM_ID.NEXTVAL INTO :NEW.ITM_ID FROM DUAL;
END;
/

INSERT INTO TBL_ITEM (ITM_CODE,ITM_DESC,ITM_PRICE,ITM_QTY,ITM_CRT_DT,ITM_UPD_DT,ITM_IS_DEL)
VALUES('LS-GC17103','GRIFFIN FLAT AUX CABLE CONNECT + PLAY',5.000,10,SYSDATE,SYSDATE,'N')

SELECT * FROM TBL_ITEM


--
MA ASALAAM
PASSION 4 ORACLE

The Power of Inline Views

As Salamo Alaikum Wa Rahmatullah

The inline view is a construct in Oracle SQL where you can place a query in the SQL FROM, clause, just as if the query was a table name.
OK, so why use the complicated select in the first place?  Why not just create the view? Well, one good reason is that creating a view gives you another database object to maintain, and adds more complexity to your system.  By placing the view "inside" your main select, you have all of the code needed to support the query in one place.
If you have a query as the following ...
SELECT a
  FROM table
 WHERE id = :id
   AND b = (SELECT MAX (b)
                       FROM table
                      WHERE id = :id)
... it can be worth to check if an inline view, instead of the subquery will be faster.
Example 1 (Replace Subquery for MAX)
With Subquery
CREATE TABLE test (id INT, height INT, acc_date DATE);

INSERT INTO test (id, height, acc_date)
  SELECT MOD(ROWNUM,1000), DBMS_RANDOM.RANDOM,
  SYSDATE-1000+DBMS_RANDOM.VALUE(0,1000)
  FROM all_objects;

6357 rows created.

COMMIT;

CREATE INDEX test_idx on test (id, acc_date, height);

Index created.

ANALYZE TABLE test COMPUTE STATISTICS
    FOR TABLE
    FOR ALL INDEXES
    FOR ALL INDEXED COLUMNS;

Table analyzed.

alter session set timed_statistics=true;
alter session set sql_trace=true;

VARIABLE b1 NUMBER
exec :b1 := 10

ALTER SESSION SET TIMED_STATISTICS=TRUE;
ALTER SESSION SET SQL_TRACE=TRUE;

SELECT max(height)
  from test
 WHERE id = :b1
   AND acc_date = (SELECT MAX(acc_date)
                    FROM test
                   WHERE id = :b1);
MAX(HEIGHT)
-----------
 1480603530

Elapsed: 00:00:00.12

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=17)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (RANGE SCAN) OF 'TEST_IDX' (NON-UNIQUE) (Cost=2 Card=1 Bytes=17)
   3    2       SORT (AGGREGATE)
   4    3         FIRST ROW (Cost=2 Card=6 Bytes=60)
   5    4           INDEX (RANGE SCAN (MIN/MAX)) OF 'TEST_IDX' (NON-UNIQUE) (Cost=2 Card=1060)

tkprof gek1_ora_16520.trc gek1_ora_16520.out explain=scott/tiger sort=exeela sys=no

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          2          0           0
Fetch        2      0.00       0.00          0          2          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          4          0           1
With Inline View
VARIABLE b1 NUMBER
exec :b1 := 10

SELECT height
  FROM (SELECT height
          FROM test
         WHERE id = :b1
      ORDER BY id DESC, acc_date DESC, height DESC)
WHERE ROWNUM = 1;

    HEIGHT
----------
1480603530

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=13)
   1    0   COUNT (STOPKEY)
   2    1     VIEW (Cost=2 Card=6 Bytes=78)
   3    2       INDEX (RANGE SCAN DESCENDING) OF 'TEST_IDX' (NON-UNIQUE) (Cost=2 Card=6 Bytes=102)
tkprof gek1_ora_16521.trc gek1_ora_16521.out explain=scott/tiger sort=exeela sys=no

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.06          2         41          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          2          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.03       0.06          2         43          0           1
Example 2 (Replace Subquery for MAX)
Original Query from a trace session:
SELECT switch_time,rat_id
 FROM  tariff
 WHERE effdate = (SELECT MAX(effdate)
                    FROM tariff
                   WHERE effdate <= TRUNC(:b1)
                     AND weekday = :b2
                     AND t_id = :b3)
  AND TO_CHAR(switch_time,'HH24:MI') <= TO_CHAR(:b1,'HH24:MI')
  AND weekday = :b2
  AND t_id = :b3
ORDER BY TO_CHAR(switch_time,'HH24:MI') DESC
With Subquery
alter session set timed_statistics=true;
select value from v$parameter where name = 'user_dump_dest';
alter session set sql_trace=true;
VARIABLE b1 VARCHAR2(19)
exec :b1 := '07.04.1999:13:30:31'

VARIABLE b2 NUMBER
exec :b2 := 2

VARIABLE b3 NUMBER
exec :b3 := 317

SELECT switch_time, rat_id
  FROM tariff
 WHERE effdate =  (SELECT MAX(effdate)
                     FROM tariff
                    WHERE effdate <= TRUNC(TO_DATE(:b1,'DD.MM.YYYY:HH24:MI:SS'))
                      AND weekday = :b2
                      AND T_ID = :b3)
   AND TO_CHAR(switch_time,'HH24:MI') <= TO_CHAR(TO_DATE(:b1,'DD.MM.YYYY:HH24:MI:SS'),'HH24:MI')
   AND weekday = :b2
   AND t_id = :b3
ORDER BY TO_CHAR(switch_time,'HH24:MI') DESC;

SWITCH_TI     RAT_ID
--------- ----------
01-JAN-98          3
01-JAN-98          1

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=21)
   1    0   SORT (ORDER BY) (Cost=4 Card=1 Bytes=21)
   2    1     FILTER
   3    2       TABLE ACCESS (FULL) OF 'TARIFF' (Cost=2 Card=1 Bytes=21)
   4    3         SORT (AGGREGATE)
   5    4           FILTER
   6    5             INDEX (RANGE SCAN) OF 'PK_TARIFF' (UNIQUE) (Cost=2 Card=1 Bytes=12)
TKPROF:
tkprof xyz.trc xyz.out explain=user/pwd sort=exeela sys=no
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.00          0         38          8           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.00          0         38          8           4

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
With Inline View
VARIABLE b1 VARCHAR2(19)
exec :b1 := '07.04.2005:13:30:31'

VARIABLE b2 NUMBER
exec :b2 := 2

VARIABLE b3 NUMBER
exec :b3 := 317

SELECT switch_time, rat_iD
  FROM (SELECT switch_time, rat_id
          FROM tariff
         WHERE effdate <= TRUNC(TO_DATE(:b1,'DD.MM.YYYY:HH24:MI:SS'))
           AND weekday = :b2
           AND t_id = :b3
         ORDER BY effdate DESC)
 WHERE TO_CHAR(switch_time,'HH24:MI') <= TO_CHAR(TO_DATE(:b1,'DD.MM.YYYY:HH24:MI:SS'),'HH24:MI');SWITCH_TI     RAT_ID
--------- ----------
01-JAN-98          3
01-JAN-98          1

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=22)
   1    0   VIEW (Cost=4 Card=1 Bytes=22)
   2    1     SORT (ORDER BY) (Cost=4 Card=1 Bytes=21)
   3    2       FILTER
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'TARIFF' (Cost=2 Card=1 Bytes=21)
   5    4           INDEX (RANGE SCAN) OF 'PK_TARIFF' (UNIQUE) (Cost=2 Card=1)
TKPROF:
tkprof xyz.trc xyz.out explain=user/pwd sort=exeela sys=no
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         19          4           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0         19          4           4

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Example 3 (cannot have join with CONNECT BY)
Have you ever tried to join to a hierarchical query (a query using CONNECT BY and PRIOR) only to get this message:
ORA-01437: cannot have join with CONNECT BY
One of the limitations of hierarchical queries is that you cannot join to them. However, there are often times you would like to join to them anyway. For instance, if the hierarchy table only has surrogate keys, and you would like to display the real value. This tip shows how you can use "Inline Views" to join tables to a hierarchical query.
SELECT level, LPAD(' ',2*level-2)||ename ename, empno, mgr, dept.deptno, dept.dname
FROM emp, dept
WHERE emp.deptno = dept.deptno
CONNECT BY PRIOr empno = mgr
START WITH empno = 7839;
ORA-01437: cannot have join with CONNECT BY
SELECT E.emplevel, SUBSTR(E.ename,1,15) "ENAME", E.empno, dept.deptno, dept.dname
 FROM dept, (SELECT level emplevel, LPAD('   ',2*level-2)||ename ename, empno, mgr, deptno
               FROM emp
               CONNECT BY PRIOR empno = mgr
               START WITH empno = 7839) E
WHERE E.deptno = dept.deptno
/

  EMPLEVEL ENAME                EMPNO     DEPTNO DNAME
---------- --------------- ---------- ---------- --------------
         1 KING                  7839         10 ACCOUNTING
         2   CLARK               7782         10 ACCOUNTING
         3     MILLER            7934         10 ACCOUNTING
         2   JONES               7566         20 RESEARCH
         3     SCOTT             7788         20 RESEARCH
         4       ADAMS           7876         20 RESEARCH
         3     FORD              7902         20 RESEARCH
         4       SMITH           7369         20 RESEARCH
         2   BLAKE               7698         30 SALES
         3     ALLEN             7499         30 SALES
         3     WARD              7521         30 SALES
         3     MARTIN            7654         30 SALES
         3     TURNER            7844         30 SALES
         3     JAMES             7900         30 SALES
Example 3 (ROWNUM 1 Problem)
A rownum restriction starting with 1 works:
ROWNUM does not work for ranges that don't start at 1.
A ROWNUM restriction starting with 1 works:
SELECT ROWNUM,ename from emp WHERE ROWNUM BETWEEN 1 and 3
/

    ROWNUM ENAME
---------- ----------
         1 SMITH
         2 ALLEN
         3 WARD
However, if you try to use a range it will not work. For example:
SELECT ROWNUM,ename from emp WHERE ROWNUM BETWEEN 2 and 3
/

no rows selected
Using an Inline View to get around this limitation:
SELECT t1.rn, t1.ename
  FROM (SELECT ROWNUM rn, ename
          FROM emp) t1
 WHERE t1.rn BETWEEN 2 and 3
/
The main trick to this query is the "internal" select statement. This select statement in the from clause, basically does a full query of the table, then returns the values (along with the psuedo-column ROWNUM) to the "outside" query.  The outside query can then operate on the results of the internal query.  In order to access the internal query's columns from the external query, you need to give the internal query an alias ("t1" highlighted below): This allows you to refer to the columns using the "t1" (highlighted below): Since "ROWNUM" is a psuedo-column and therefore a reserved word, you need to alias that column in the internal query in order to refer to it in the outside query:
Example 4 (ROWNUM and ORDER BY Problem, TOP-N Queries)
The following query form is almost wrong:
select * from emp where ROWNUM <= 5 order by sal desc;  /* WRONG! */

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
The users intention was most likely to get the the top-five paid people - a top-N query.  What the will get is five random records (the first five we happen to hit), sorted by salary. If you use an inline view with the ORDER BY inside the inline view, you get the correct result.
select * from (select * from emp order by sal desc) where rownum <= 5;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
Example 5 (Pagination with ROWNUM)
Pagination with ROWNUM can be used to get rows N thru M of a result set. The general form of this is as follows:
SELECT *
  FROM (SELECT a.*, ROWNUM rn
          FROM (enter your query here) a
         WHERE ROWNUM <= :MAX_ROW)
 WHERE rn >= :MIN_ROW;
SELECT *
  FROM (SELECT a.*, ROWNUM rn
          FROM (SELECT * FROM emp) a
         WHERE ROWNUM <= 6)
 WHERE rn >= 2;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO         RN
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30          2
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30          3
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20          4
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30          5
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30          6
Example 6 (Simplify complex Inline View with an Object Type)
Sometimes, complex Inline Views can be simplified by an Object Type.
SELECT dname,
       LTRIM(SUBSTR(IV, 1, 30 )) ename,
       LTRIM(SUBSTR(IV, 31, 10 )) job,
       TO_DATE(SUBSTR(IV, 40),'yyyymmddhh24miss') hiredate
  FROM (
         SELECT dname,
              (
                SELECT RPAD(ename,30)||RPAD(job,30)||TO_CHAR(hiredate,'yyyymmddhh24miss')
                  FROM emp
                 WHERE rownum = 1
                   AND emp.deptno = dept.deptno
              ) IV
           FROM dept
       )
/

DNAME          ENAME                          JOB        HIREDATE
-------------- ------------------------------ ---------- ---------
ACCOUNTING     CLARK                          MANAGER    09-JUN-81
RESEARCH       SMITH                          CLERK      17-DEC-80
SALES          ALLEN                          SALESMAN   20-FEB-81
OPERATIONS
With an Object Type
CREATE OR REPLACE TYPE FormatType AS OBJECT
(
   ename     VARCHAR2(30),
   job       VARCHAR2(10),
   hiredate  DATE)
/

SELECT dname,
       IV1.IV2.ename ename,
       IV1.IV2.job job,
       IV1.IV2.hiredate hiredate
  FROM (
          SELECT dname,
               (
                 SELECT FormatType(ename,job,hiredate)
                   FROM emp
                  WHERE rownum = 1
                    AND emp.deptno = dept.deptno
               ) IV2
            FROM dept
       ) IV1
/

DNAME          ENAME                          JOB        HIREDATE
-------------- ------------------------------ ---------- ---------
ACCOUNTING     CLARK                          MANAGER    09-JUN-81
RESEARCH       SMITH                          CLERK      17-DEC-80
SALES          ALLEN                          SALESMAN   20-FEB-81
OPERATIONS
The select statement within the select statement must always return single row, otherwise we will get the following error
ORA-01427: single-row subquery returns more than one row.

-- 
MA ASALAAM
Passion 4 Oracle