user :=  dbo/dbo@hyd

Main Query
==========

select a.customer_code customer, c.name name, c.place place, a.product_code product, b.quantity ord_qty, nvl(b.delivery_district_code,c.district_code) district, nvl(b.delivery_town_code,c.town_code) town
  from order_book a, order_book_detail b, customer c
where a.region_code        = b.region_code
  and a.order_book_form_no = b.order_book_form_no
  and a.customer_code      = c.customer_code
  and c.division_code      = 34 
  and a.region_code        = 10
  and c.state_code         = 1
  and a.order_book_form_date = '18-OCT-2007' ==> input date :p_date
  and nvl(c.classification_code,'N') = 'S'
 order by 1;

after perameter
===============

p_fin_st number(6) := 0;

if to_number(to_char(:p_date,'MM')) >= 4 then
  :p_fin_st := to_number(to_char(:p_date,'YYYY'))||04;
elsif to_number(to_char(:p_date,'MM')) <= 3 then
  :p_fin_st := (to_number(to_char(:p_date,'YYYY'))-1)||04;
end if;

formula column
==============

:cp_order_amount       := null;
:cp_deposit_amount     := null;
:cp_ledger_os          := null;
:cp_available_cr_limit := null;
:cp_tag                := null;
:cp_amount_payable     := null;

begin
  select ((bag_rate * 20) - nvl(freight,0)) * ord_qty
    into :cp_order_amount
    from cement_rate
   where depot_code    = 10
     and state_code    = 1
     and district_code = :district
     and town_code     = :town
     and product_code  = decode(:product_code,21,21,26,21,22,22,27,22,23,23,28,23,
  	                                      31,31,36,31,32,32,37,32,33,33,38,33,
  	                                      41,41,46,41,42,42,47,42,43,43,48,43,
  	                                      51,51,56,51,52,52,57,52,53,53,58,53,:product_code)
     and ((:p_date between from_date and to_date) 
      or  (:p_date >= from_date and to_date is null))
     and nvl(authorisation_tag,'N') = 'Y';

select nvl(sum(credit_amount),0) - nvl(sum(debit_amount),0) 
  into :cp_deposit_amount
  from sl_balance
 where division_code = 39
   and (financial_year||financial_month = substr(:p_fin_st,1,4)||00
    or  financial_year||financial_month between :p_fin_st and to_char(:p_date,'YYYYMM')
   and sl_code = :customer
   and and gl_code in (341101, 341102)

select nvl(sum(debit_amount),0) - nvl(sum(credit_amount),0) 
  into :cp_ledger_os
  from sl_balance
 where division_code = 39
   and (financial_year||financial_month = substr(:p_fin_st,1,4)||00
    or  financial_year||financial_month between :p_fin_st and to_char(:p_date,'YYYYMM')
   and sl_code = :customer
   and and gl_code = 441101;

  :cp_available_cr_limit := nvl(:cp_deposit_amount,0) - nvl(:cp_ledger_os,0);

if nvl(:cp_ledger_os,0) < 0 then

  :cp_ledger_os := abs(:cp_ledger_os);
  :cp_tag       := 'CR';  	

end if;

:cp_amount_payable := nvl(:cp_available_cr_limit,0) + nvl(:cp_order_amount,0);