create or replace package PLPDF_INVOICE_EXAMPLE_1 is

  v_pdf blob;
  procedure genPDF;

  p_order_id number default 7;

  cursor c_order(p_ord_id number) is
    select
      ord.order_id order_id,
      cus.cust_first_name || ' ' || cus.cust_last_name cust_name,
      cus.cust_street_address1 cust_street_address1,
      cus.cust_city cust_city,
      cus.cust_state cust_state,
      cus.cust_postal_code cust_postal_code,
      to_char(ord.order_total) order_total,
      to_char(ord.order_date,'MM-DD-YYYY') order_date
    from plpdf_demo_orders ord,
         plpdf_demo_customers cus
    where ord.order_id = p_ord_id
      and ord.customer_id = cus.customer_id;

  cursor c_order_items(p_ord_id number) is
   select
      oit.order_item_id order_item_id,
      oit.product_id product_id,
      pro.product_name product_name,
      pro.product_name product_image_name,
      to_char(oit.quantity) quantity,
      to_char(pro.list_price) unit_price,
      to_char(oit.unit_price) extended_price
    from plpdf_demo_order_items oit,
         plpdf_demo_product_info pro
    where oit.order_id = p_ord_id
      and oit.product_id = pro.product_id;

end PLPDF_INVOICE_EXAMPLE_1;