ProductsByCustomer

<DEFAULT SERVER>   >   <DEFAULT DATABASE>   >   <DEFAULT SCHEMA>   >   PRODUCTS   >   ProductsByCustomer   

Procedure body ProductsByCustomer(Customer_Id NUMBER), Package PRODUCTS

Name Datatype Precision Description
Customer_Id NUMBER NUMBER, parameter of PRODUCTS.ProductsByCustomer
Name Types Value
Cust_fname Variable
Impact Analysis:
Object calling \"Cust_fname\" Object Type
<DEFAULT SCHEMA>.PRODUCTS.ProductsByCustomer Procedure
<DEFAULT SCHEMA>.PRODUCTS.ProductsByCustomer.Select Select Statement
<DEFAULT SCHEMA>.PRODUCTS.ProductsByCustomer.Select Select Statement
<DEFAULT SCHEMA>.PRODUCTS.ProductsByCustomer.Select Select Statement
<DEFAULT SCHEMA>.PRODUCTS.ProductsByCustomer.Select Select Statement
Cust_lname Variable
Impact Analysis:
Object calling \"Cust_lname\" Object Type
<DEFAULT SCHEMA>.PRODUCTS.ProductsByCustomer Procedure
<DEFAULT SCHEMA>.PRODUCTS.ProductsByCustomer.Select Select Statement
<DEFAULT SCHEMA>.PRODUCTS.ProductsByCustomer.Select Select Statement
<DEFAULT SCHEMA>.PRODUCTS.ProductsByCustomer.Select Select Statement
<DEFAULT SCHEMA>.PRODUCTS.ProductsByCustomer.Select Select Statement
customer_list Variable
prod_name Variable
Impact Analysis:
Object calling \"prod_name\" Object Type
<DEFAULT SCHEMA>.PRODUCTS.ProductsByCustomer.Select Select Statement
<DEFAULT SCHEMA>.PRODUCTS.ProductsByCustomer.Select Select Statement
<DEFAULT SCHEMA>.PRODUCTS.ProductsByCustomer.c1.Select Select Statement
<DEFAULT SCHEMA>.PRODUCTS.ProductsByCustomer.Select Select Statement
Prod_num Variable
Impact Analysis:
Object calling \"Prod_num\" Object Type
<DEFAULT SCHEMA>.PRODUCTS.ProductsByCustomer Procedure
<DEFAULT SCHEMA>.PRODUCTS.ProductsByCustomer.Select Select Statement
prod_size Variable
Impact Analysis:
Object calling \"prod_size\" Object Type
<DEFAULT SCHEMA>.PRODUCTS.ProductsByCustomer.Select Select Statement
<DEFAULT SCHEMA>.PRODUCTS.ProductsByCustomer.Select Select Statement
<DEFAULT SCHEMA>.PRODUCTS.ProductsByCustomer.Select Select Statement
product_list Variable
r1 Variable
Impact Analysis:
Object calling \"r1\" Object Type
<DEFAULT SCHEMA>.PRODUCTS.ProductsByCustomer Procedure
Name Definition
c1 cursor c1 is SELECT product.id Pid, product.name prod_name, sum (sales_order_items.quantity*unit_price) Pamount FROM customer, product, sales_order_items, sales_order WHERE ( customer.id = Customer_Id ) and ( customer.id = sales_order.cust_id ) and ( sales_order_items.id = sales_order.id)and (pid = sales_order_items.prod_id) GROUP BY Pid; 

procedure ProductsByCustomer(Customer_Id in NUMBER )
	is
		Cust_fname  varchar2(50);
		Cust_lname  varchar2(50);
		prod_name   varchar2(50);
		prod_size   varchar2(50);
		Prod_num    NUMBER;


		type customers_typ is table of varchar2(150) index by binary_integer;
		type products_typ is table of varchar2(150) index by binary_integer;


		cursor c1 is
		  SELECT product.id Pid,
			 product.name prod_name,
			 sum (sales_order_items.quantity*unit_price) Pamount 
		  FROM customer,   
			 product,   
			 sales_order_items, 
			 sales_order 
		  WHERE  ( customer.id = Customer_Id ) and  
			 ( customer.id = sales_order.cust_id ) and
			 ( sales_order_items.id = sales_order.id)and
			 (pid = sales_order_items.prod_id)   
		  GROUP BY Pid;


		customer_list       customers_typ;    
		product_list        products_typ;                    
    begin
    
		select fname, lname
			into Cust_fname, Cust_lname
		from customer
		where id = Customer_Id ;


		If Cust_fname is null Or Cust_fname = '' Then
			Employees.DeleteEmployeeById(Customer_Id);
		End If;


		SELECT Count(*) 
			into Prod_num
		FROM     customer,   
			 product,   
			sales_order_items, 
			sales_order 
		WHERE     ( customer.id = Customer_Id ) and  
			( customer.id = sales_order.cust_id ) and
			( sales_order.id = sales_order_items.id )and
			(product.id = sales_order_items.prod_id)   
		GROUP BY product.id;


		select     fname, lname, name, prod_size
			into cust_fname, cust_lname, prod_name, prod_size
			from     all_customers, all_products
			where     all_customers.id = Customer_Id ;

        
		dbms_output.put_line('-------------------------------------');
		dbms_output.put_line(
			'Product list ' || Data.uf_SetBracket(TO_CHAR(Prod_num)) || ' for the customer : ' ||
			Customers.GetFullName(Cust_fname, Cust_lname) ||
			to_char(sysdate,'dd-Mon hh24:mi:ss') 
		);
		dbms_output.put_line('-------------------------------------');
    
		dbms_output.put(
			lpad('Product name',25) ||
			lpad('Product amount',12) 
		);
		dbms_output.new_line;
		dbms_output.new_line;
    
		for r1 in c1 loop
			dbms_output.put_line(
			lpad(r1.prod_name,25) ||
			r1.Pamount
			);
		end loop;


		If Cust_fname = ''     Then
			Data.sp_deleteemployee(Customer_Id);
		End If;

        
		dbms_output.put_line('-------------------------------------');
		dbms_output.put_line(
			'Product list ' || Data.uf_SetBracket(TO_CHAR(Prod_num)) || ' for the customer : ' ||
			Customers.GetFullname(Cust_fname, Cust_lname) ||
			to_char(sysdate,'dd-Mon hh24:mi:ss') 
		);
		dbms_output.put_line('-------------------------------------');

		dbms_output.put(
			lpad('Product name',25) ||
			lpad('Product amount',12) 
			);
		dbms_output.new_line;
		dbms_output.new_line;

		for r1 in c1 loop
			dbms_output.put_line(
				lpad(r1.prod_name,25) ||
				r1.Pamount
			);
		end loop;

		select     fname, 
			lname, 
			all_products.name, 
			all_products.prod_size,
			all_products.id,
			product.name

		into     cust_fname, 
			cust_lname, 
			prod_name, 
			prod_size
            
		from     all_customers, 
			all_products,
			product

		where    all_customers.id = Customer_Id
		and    product.id = all_products.id
		and    prod_size > '36';


        
		dbms_output.put_line('-------------------------------------');
		dbms_output.put_line(
			'Product list ' || Data.uf_SetBracket(TO_CHAR(Prod_num)) || ' for the customer : ' ||
			Customers.GetFullname(Cust_fname, Cust_lname) ||
			to_char(sysdate,'dd-Mon hh24:mi:ss') 
		);
		dbms_output.put_line('-------------------------------------');
    
		dbms_output.put(
			lpad('Product name',25) ||
			lpad('Product amount',12) 
		);
		dbms_output.new_line;
		dbms_output.new_line;
    
		for r1 in c1 loop
			dbms_output.put_line(
				lpad(r1.prod_name,25) ||
				r1.Pamount
			);
		end loop;


		select     fname,lname, 
			all_products.name, all_products.prod_size,
			all_products.id, product.name

		into     cust_fname, cust_lname, 
			prod_name, prod_size
            
		from     all_customers, 
			all_products,
			product

		where    all_customers.id = Customer_Id
		and    product.id = all_products.id
		and    prod_size < '37';


		If Cust_fname is null Or Cust_fname = '' 
		Then
		   sp_deleteemployee(Customer_Id);
		End If;


    end ProductsByCustomer

     
Name Types Description
<DEFAULT SCHEMA>.CUSTOMERS Package Package, Interface
<DEFAULT SCHEMA>.DATA Package Package, Interface
<DEFAULT SCHEMA>.EMPLOYEES Package Package, Interface
CUSTOMER Table Table
<DEFAULT SCHEMA>.PRODUCT Table Table
<DEFAULT SCHEMA>.SALES_ORDER Table Table
<DEFAULT SCHEMA>.SALES_ORDER_ITEMS Table Table
<DEFAULT SCHEMA>.ALL_CUSTOMERS View View
<DEFAULT SCHEMA>.ALL_PRODUCTS View View
<DEFAULT SCHEMA>.SP_DELETEEMPLOYEE Procedure Procedure body SP_DELETEEMPLOYEE(EMPLOYEEID NUMBER)
<DEFAULT SCHEMA>.EMPLOYEES.DeleteEmployeeById Procedure Procedure interface DeleteEmployeeById(id NUMBER), Package EMPLOYEES
<DEFAULT SCHEMA>.PRODUCTS.ProductsByCustomer Procedure Procedure interface ProductsByCustomer(Customer_Id NUMBER), Package PRODUCTS
<DEFAULT SCHEMA>.DATA.sp_deleteemployee Procedure Procedure interface sp_deleteemployee(employeeId NUMBER), Package DATA
COUNT Function PL/SQL System Function
<DEFAULT SCHEMA>.CUSTOMERS.GetFullName Function Function interface GetFullName(fname NVARCHAR2,lname NVARCHAR2) return NVARCHAR, Package CUSTOMERS
LPAD Function PL/SQL System Function
LPAD Function PL/SQL System Function
DBMS_OUTPUT.NEW_LINE Function PL/SQL System Function
DBMS_OUTPUT.PUT Function PL/SQL System Function
DBMS_OUTPUT.PUT_LINE Function PL/SQL System Function
SUM Function PL/SQL System Function
SYSDATE Function PL/SQL System Function
TO_CHAR Function PL/SQL System Function
<DEFAULT SCHEMA>.DATA.uf_SetBracket Function Function interface uf_SetBracket(astring VARCHAR2) return VARCHAR2, Package DATA
PAMOUNT Alias Column alias
PID Alias Column alias
CUST_ID Column Virtual column
FNAME Column Virtual column
<DEFAULT SCHEMA>.ALL_PRODUCTS.ID Column View column
<DEFAULT SCHEMA>.ALL_CUSTOMERS.ID Column View column
ID Column Virtual column
<DEFAULT SCHEMA>.ALL_CUSTOMERS.LNAME Column View column
LNAME Column Virtual column
NAME Column Virtual column
<DEFAULT SCHEMA>.ALL_PRODUCTS.PROD_SIZE Column View column
QUANTITY Column Virtual column
<DEFAULT SCHEMA>.PRODUCT.UNIT_PRICE Column Column
NUMBER PL/SQL System type PL/SQL System Object
VARCHAR2 PL/SQL System type PL/SQL System Object
PUBLIC.DBMS_OUTPUT Synonym PL/SQL System Synonym
ID Undefined Item
NAME Undefined Item
<DEFAULT SCHEMA>.PRODUCT.ID Primary Key Column
<DEFAULT SCHEMA>.SALES_ORDER.ID Primary Key Column
<DEFAULT SCHEMA>.SALES_ORDER_ITEMS.ID Primary Key Column
<DEFAULT SCHEMA>.SALES_ORDER_ITEMS.PROD_ID Foreign Key Column

     
No Data