QUERY FOR THE DATA SOURCE DEFINITION PAGE OF THE REPORT WIZARD
--------------------------------------------------------------

SELECT ALL ORDERS.ORDER_ID, TO_CHAR(ORDERS.ORDER_DATE, 'DD-MON-YYYY') 
ORDER_DATE, ORDERS.CUSTOMER_ID, ORDERS.ORDER_TOTAL, 
CUSTOMERS.CUST_FIRST_NAME || ' ' || CUSTOMERS.CUST_LAST_NAME CUSTOMER_NAME, 
CUSTOMERS.CUST_ADDRESS, (ROWNUM + 1000) AS CHECK_NO
FROM ORDERS, CUSTOMERS
WHERE CUSTOMERS.CUSTOMER_ID = ORDERS.CUSTOMER_ID
ORDER BY ORDERS.ORDER_ID ASC


SPELL FUNCTION
--------------
FUNCTION Spell (val number) RETURN CHAR IS
  sp varchar2(100);
BEGIN
  if val > 0 then
  	 return(initcap(to_char(to_date(val, 'SSSSS'), 'SSSSSSP')));
  else
  	 return('');
  end if;
END;

SPELLED_AMOUNT FORMULA COLUMN
-----------------------------
 function SPELLED_AMOUNTFormula return Char is
	cents number;
	c_str varchar2(80);
	val number;
begin
  val := :order_total;
  cents := (val mod 1) * 100;
  if cents > 0 then --creates string for cents
  	c_str := ' and ' || TO_CHAR(cents) || '/100 Dollars******';
 	  else
  	c_str := ' Dollars******';
  end if;
  if val < 1000 and val > 1 then
  	return (initcap(spell(floor(val))) || c_str);
  elsif val > 1000 then
  	return(initcap(spell(floor(val/1000))) || ' Thousand ' ||
  	 spell(floor(val mod 1000)) || c_str);
  else
  		return('Zero'||c_str);
  end if;
end;

ORDER_ITEMS QUERY
-----------------
SELECT ALL
    ORDER_ITEMS.LINE_ITEM_ID,
    ORDER_ITEMS.ORDER_ID,
    ORDER_ITEMS.PRODUCT_ID,
    ORDER_ITEMS.UNIT_PRICE,
    ORDER_ITEMS.QUANTITY,
    PRODUCT_INFORMATION.PRODUCT_NAME
FROM ORDER_ITEMS, PRODUCT_INFORMATION
WHERE PRODUCT_INFORMATION.PRODUCT_ID=ORDER_ITEMS.PRODUCT_ID
ORDER BY ORDER_ITEMS.LINE_ITEM_ID ASC

