ORACLE DATABASE | PL/SQL | PACKAGE | Create Package

create or replace package x_customer_ledger_pkg
as
FUNCTION GET_TOTAL_PRICE ( order_no_ IN VARCHAR2) RETURN NUMBER;
FUNCTION GET_ORDER_NO ( pvoucher_type IN VARCHAR2,pvoucher_no NUMBER) RETURN VARCHAR2;
procedure test_proc (in_val in number);
end;
create or replace package body x_customer_ledger_pkg
as
function get_total_price ( order_no_ in varchar2) return number is
v_total_price number;
begin
select ifsapp.customer_order_api.get_ord_gross_amount(order_no_)+sum(nvl(c.charge_amount,0)) total_price into v_total_price
from customer_order_charge c
where upper(c.company)=upper('IAL')
and c.order_no=order_no_
and upper(c.charge_type)<>upper('INTEREST'); -- price without inrest and add charge
return v_total_price;
exception
when others then
return v_total_price;
END;

FUNCTION get_order_no ( pvoucher_type IN VARCHAR2,pvoucher_no NUMBER) RETURN VARCHAR2 IS
v_order_no VARCHAR2(200);
BEGIN
select l.creators_reference into v_order_no
from payment_per_currency_cu_qry p,
ledger_transaction_cu_qry l
where p.company=l.company
and p.series_id=l.series_id
and p.payment_id=l.payment_id
and p.currency=l.currency
and p.voucher_no=l.voucher_no
and p.voucher_type=l.voucher_type
and l.creators_reference is not null
--and l.invoice_address_id is null
and l.voucher_type=upper(nvl(pvoucher_type,'B'))
and l.voucher_no=pvoucher_no;
RETURN v_order_no;
EXCEPTION
WHEN OTHERS THEN
RETURN v_order_no;
END;

procedure test_proc (in_val in number)
is
begin
dbms_output.put_line (in_val);
exception
when others then
RAISE;
end;
end x_customer_ledger_pkg;

select x_customer_ledger_pkg.get_total_price (1) from dual

execute x_customer_ledger_pkg.get_total_price (2)Drop package x_customer_ledger_pkg