Hi,
pemanggilnya harusnya seperti ini
report_ap_0043a.ap_0043a(to_date('01/11/2008','dd/mm/yyyy'),to_date('05/11/2008','dd/mm/yyyy'),'POF',cur);
Cheers,
Wadi Achmed
Agustinus Vindy wrote:
> 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..:)
>
>
>
>
