OrdersByEmployee2

<DEFAULT SERVER>   >   <DEFAULT DATABASE>   >   <DEFAULT SCHEMA>   >   EMPLOYEES   >   OrdersByEmployee2

Procedure body OrdersByEmployee2(employee_Id NUMBER,p_recordset SYS_REFCURSOR), Package EMPLOYEES

Name	Datatype	Precision	Description
employee_Id	NUMBER		NUMBER, parameter of EMPLOYEES.OrdersByEmployee2
p_recordset	SYS_REFCURSOR		SYS_REFCURSOR, parameter of EMPLOYEES.OrdersByEmployee2

procedure OrdersByEmployee2(employee_Id in NUMBER,p_recordset  OUT SYS_REFCURSOR )
    Is 
    Begin

		OPEN p_recordset FOR
			   SELECT sales_order.id Oid,   
							 sales_order.order_date Odate,   
							 DECODE(sales_order.fin_code_id,
							'r1','Revenue','e1','Expense'),   
							 sales_order.region Oregion,   
							 customer.fname Cfname,
							 customer.lname Clname,
							 sum (sales_order_items.quantity*unit_price) Oamount 
						FROM customer,   
							 employee,   
							 sales_order_items, 
							 sales_order,
							 ALL_PRODUCTS
						 WHERE ( employee.emp_id = employee_Id ) and  
							 ( employee.emp_id = sales_order.sales_rep ) and
							 ( customer.id = sales_order.cust_id ) and
							 ( sales_order_items.id = sales_order.id)   
						 GROUP BY Oid,Odate,   
							 sales_order.fin_code_id,   
							 Oregion,   
							 customer.lname;


    end OrdersByEmployee2

Name	Types	Description
CUSTOMER	Table	Table
<DEFAULT SCHEMA>.EMPLOYEE	Table	Table
<DEFAULT SCHEMA>.SALES_ORDER	Table	Table
<DEFAULT SCHEMA>.SALES_ORDER_ITEMS	Table	Table
<DEFAULT SCHEMA>.ALL_PRODUCTS	View	View
<DEFAULT SCHEMA>.EMPLOYEES.OrdersByEmployee2	Procedure	Procedure interface OrdersByEmployee2(employee_Id NUMBER,p_recordset SYS_REFCURSOR), Package EMPLOYEES
DECODE	Function	PL/SQL System Function
SUM	Function	PL/SQL System Function
CFNAME	Alias	Column alias
CLNAME	Alias	Column alias
OAMOUNT	Alias	Column alias
ODATE	Alias	Column alias
OID	Alias	Column alias
OREGION	Alias	Column alias
CUST_ID	Column	Virtual column
FIN_CODE_ID	Column	Virtual column
FNAME	Column	Virtual column
ID	Column	Virtual column
LNAME	Column	Virtual column
ORDER_DATE	Column	Virtual column
QUANTITY	Column	Virtual column
REGION	Column	Virtual column
<DEFAULT SCHEMA>.ALL_PRODUCTS.UNIT_PRICE	Column	View column
NUMBER	PL/SQL System type	PL/SQL System Object
SYS_REFCURSOR	PL/SQL System type	PL/SQL System Object
<DEFAULT SCHEMA>.EMPLOYEE.EMP_ID	Primary Key	Column
<DEFAULT SCHEMA>.SALES_ORDER_ITEMS.ID	Primary Key	Column
<DEFAULT SCHEMA>.SALES_ORDER.ID	Primary Key	Column
<DEFAULT SCHEMA>.SALES_ORDER.SALES_REP	Foreign Key	Column

Name	Types	Description
<DEFAULT SCHEMA>.DATA.Ordersbyemployee	Procedure	Procedure body Ordersbyemployee(employee_Id NUMBER), Package DATA