Dear all,
saya buat package dan prosedure pemanggilnya seperti ini..
=======package head===========
create or replace package report_ap_0043a is
-- Author : ADMINISTRATOR
-- Created : 25/03/2004 10:09:05 AM
-- Purpose :
-- Public type declarations
type CursorDummy is REF CURSOR;
Procedure ap_0043a(TglAwal IN DATE,TglAkhir IN DATE,NamaSchema IN
Varchar2,CurOut OUT CursorDummy);
end report_ap_0043a;
======package body===========
create or replace package body report_ap_0043a is
Procedure ap_0043a(TglAwal IN DATE,TglAkhir IN DATE,NamaSchema IN
Varchar2,CurOut OUT CursorDummy)
Is
Begin
Open CurOut For
'select
a.mata_uang,
a.tanggal_lpb tgl_lpb,
a.nomor_lpb no_lpb,
b.nomor_spp no_spp,
(select g.nama from finance.relasi g where g.kode = a.kode_supplier)
supplier,
e.nama_barang spesifikasi_barang,
c.kode_rekening,
b.qty jumlah_barang,
e.satuan,
c.harga harga_satuan,
--c.potongan diskon,
0 diskon,
decode(a.mata_uang,''IDR'',0,c.harga * b.qty) jumlah_valas,
decode(a.mata_uang,''IDR'',c.harga * b.qty,c.harga * b.qty *
decode((select kurs_mkeu from finance.kurs where kurs.kode_mata_uang =
a.mata_uang and kurs.tanggal = a.tanggal_kurs),null,0,(select kurs_mkeu from
finance.kurs where kurs.kode_mata_uang = a.mata_uang and kurs.tanggal =
a.tanggal_kurs))) jumlah_rupiah,
(select max(f.tanggal_kirim) from '||NamaSchema||'.spp_rencana_kirim
f where f.nomor_spp = b.nomor_spp and f.nomor_sip = b.nomor_sip and f.item_sip
= b.item_sip) tanggal_kiriman,
a.jenis_ppn ppn,
decode(a.jenis_ppn,''I'',
(decode(a.mata_uang,''IDR'',0,c.harga * b.qty) -
(decode(a.mata_uang,''IDR'',0,c.harga * b.qty) * 0.1)),
--decode(a.mata_uang,''IDR'',0,c.harga * c.qty) +
(decode(a.mata_uang,''IDR'',0,c.harga * c.qty)/11)
decode(a.mata_uang,''IDR'',0,c.harga * b.qty )
) total_valas,
decode(a.jenis_ppn,''I'',
decode(
a.mata_uang,
''IDR'',
((c.harga * b.qty) - ((c.harga * b.qty) * 0.1)),
( ((c.harga * b.qty) * ((decode((select kurs_mkeu from
finance.kurs where kurs.kode_mata_uang = a.mata_uang and kurs.tanggal =
a.tanggal_kurs),null,0,(select kurs_mkeu from finance.kurs where
kurs.kode_mata_uang = a.mata_uang and kurs.tanggal = a.tanggal_kurs))))) -
(((c.harga * b.qty) * ((decode((select kurs_mkeu from finance.kurs where
kurs.kode_mata_uang = a.mata_uang and kurs.tanggal =
a.tanggal_kurs),null,0,(select kurs_mkeu from finance.kurs where
kurs.kode_mata_uang = a.mata_uang and kurs.tanggal = a.tanggal_kurs))))) * 0.1)
) ),
--(c.harga * b.qty * decode((select
kurs_tengah_bi from finance.kurs where kurs.kode_mata_uang = a.mata_uang and
kurs.tanggal = a.tanggal_kurs),null,0,(select kurs_tengah_bi from finance.kurs
where kurs.kode_mata_uang = a.mata_uang and kurs.tanggal = a.tanggal_kurs)))) +
--(decode(a.mata_uang,''IDR'',c.harga * b.qty,c.harga
* b.qty * decode((select kurs_tengah_bi from finance.kurs where
kurs.kode_mata_uang = a.mata_uang and kurs.tanggal =
a.tanggal_kurs),null,0,(select kurs_tengah_bi from finance.kurs where
kurs.kode_mata_uang = a.mata_uang and kurs.tanggal = a.tanggal_kurs))) * 0.1),
decode(
a.mata_uang,
''IDR'',
c.harga * b.qty,
c.harga * b.qty * decode((select kurs_mkeu from
finance.kurs where kurs.kode_mata_uang = a.mata_uang and kurs.tanggal =
a.tanggal_kurs),null,0,(select kurs_mkeu from finance.kurs where
kurs.kode_mata_uang = a.mata_uang and kurs.tanggal = a.tanggal_kurs)))
) total_rupiah
from '||NamaSchema||'.lpb_head a
join '||NamaSchema||'.lpb_item b on (a.nomor_lpb = b.nomor_lpb)
left join '||NamaSchema||'.spp_item c on (b.nomor_spp = c.nomor_spp
and b.nomor_sip = c.nomor_sip and b.item_sip = c.item_sip)
left join '||NamaSchema||'.sip_item e on (b.nomor_sip = e.nomor_sip
and b.item_sip = e.item_sip)
where a.barang_jasa = ''B'' and a.tanggal_lpb >= '''||TglAwal||''' and
a.tanggal_lpb <= '''||TglAkhir||'''';
End ap_0043a;
end report_ap_0043a;
-> aku bikin prosedure pemanggil dari package itu seperti ini
=====================================================================
create or replace procedure pemanggil_package_AP is
type curOut is REF CURSOR;
cur curOut;
table_ap TEMP_AP_0043A%rowtype;
--TEMP_AP_0043A = table temporary yang field nya sama dengan hasil kursor
begin
report_ap_0043a.ap_0043a('01/11/2008','05/11/2008','POF',cur);
loop
fetch cur into table_ap;
dbms_output.put_line(cur%rowcount);
dbms_output.put_line(table_ap.no_lpb);
exit when cur%notfound;
end loop;
end pemanggil_package_AP;
=====================================================================
tapi pas aku jalankan lewat muncul error seperti ini ...
exec pemanggil_package_AP;
begin pemanggil_package_AP; end;
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "FINANCE.PEMANGGIL_PACKAGE_AP", line 13
ORA-06512: at line 1
kira2 apa ya?
mohon bantuannya..
Makasih..:)
[Non-text portions of this message have been removed]
