posting-penjualan.sql

SET TERM ^ ;

create or alter procedure POSTINGPENJUALAN (
    INDEXJURNAL integer,
    ISFITURSERIAL varchar(1),
    ISFITURLOT varchar(1),
    ISUSEBIAYAANTAR varchar(1),
    ISPINDAHGUDANG varchar(1),
    INVTRANSFER varchar(1))
returns (
    RESULT varchar(1),
    VS_MESSAGE varchar(500))
as
declare variable HASILCEKVOUCHER varchar(1);
declare variable HASILCEKVOUCHER2 varchar(1);
declare variable HASILCEKVOUCHER3 varchar(1);
declare variable PERSENKOMISI double precision;
declare variable SELISIHRETUR double precision;
declare variable BIAYAANTAR double precision;
declare variable UANGMUKA double precision;
declare variable HARGAPOKOK double precision;
declare variable SALDOPIUTANG double precision;
declare variable TANDA double precision;
declare variable SALDOPIUTANGLAMA double precision;
declare variable TOTALBIAYALAINTERBAYAR double precision;
declare variable TOTALBIAYALAINTERBAYAR_A double precision;
declare variable NAMAKELOMPOKJURNALLAMA varchar(200);
declare variable KETERANGANJURNAL varchar(200);
declare variable KETERANGANUANGMUKA varchar(200);
declare variable NOREF varchar(200);
declare variable ISADABIAYADIANTARTERBAYAR varchar(1);
declare variable ISADAPENYESUAIANBARANG varchar(1);
declare variable ISTELAHMEMBUATJURNALSELISIHRETUR varchar(1);
declare variable ISRETURPEMBELIANSELISIH varchar(1);
declare variable NOINDEX integer;
declare variable KELOMPOKJURNALLAMA integer;
declare variable NOINDEXJURNALSELISIHRETUR integer;
declare variable KODEHPP1 integer;
declare variable I integer;
declare variable GUDANGALTERNATIF integer;
declare variable JUMLAHKOLOMPAJAK integer;
declare variable NOINDEXJURNAL integer;
declare variable NOINDEXFAKTUR integer;
declare variable KODEDEPARTMEN integer;
declare variable KODEPROYEK integer;
declare variable ISADASERIALLOTKURANG varchar(1);
declare variable USEREDIT varchar(35);
declare variable ISMASTERMEMOPOSTED varchar(1);
declare variable ISEDITED varchar(1);
declare variable JAMEDIT timestamp;
declare variable IRETURN integer;
declare variable ISSERIALMISMATCH varchar(1);
declare variable RESULT_ITMLOTNEEDTOUPDATE varchar(1);
declare variable RESULT_POSTITEMLOTMASUK varchar(1);
declare variable RESULT_POSTITEMLOTKELUAR varchar(1);
declare variable RESULT_ISTRANSAKSIRETUR varchar(1);
declare variable RESULT_POSTINGITEMJURNALKHUSUS varchar(1);
declare variable MESSAGE_POSTINGITEMJURNALKHUSUS varchar(200);
declare variable RESULT_CEKSALESSTATUSISNULL varchar(1);
declare variable RESULT_POSTINGJURNALTRANSAKSI varchar(1);
declare variable MESSAGE_POSTINGJURNALTRANSAKSI varchar(200);
declare variable RESULT_POSTINGBUKUPP double precision;
declare variable MESSAGE_POSTINGBUKUPP varchar(200);
declare variable IDPAJAK1 integer;
declare variable IDPAJAK2 integer;
declare variable IDPAJAK3 integer;
declare variable IDPAJAK4 integer;
declare variable IDPAJAK5 integer;
declare variable AKUNPAJAK integer;
declare variable TOTALPAJAK1 double precision;
declare variable TOTALPAJAK2 double precision;
declare variable TOTALPAJAK3 double precision;
declare variable TOTALPAJAK4 double precision;
declare variable TOTALPAJAK5 double precision;
declare variable GJURNAL_NOINDEX integer;
declare variable GJURNAL_KELOMPOK integer;
declare variable GJURNAL_TANGGAL timestamp;
declare variable GJURNAL_KETERANGAN varchar(75);
declare variable GJURNAL_NOJURNAL varchar(35);
declare variable GJURNAL_ISEDITED varchar(1);
declare variable GJURNAL_USEREDIT varchar(25);
declare variable GJURNAL_JAMEDIT timestamp;
declare variable GJURNAL_DEPT integer;
declare variable GJURNAL_IDJOB integer;
declare variable GJURNAL_IDNOTE integer;
declare variable GJURNAL_NAMAKELOMPOK varchar(2);
declare variable JURNAL_NOINDEX_JURNAL integer;
declare variable JURNAL_KODE integer;
declare variable JURNAL_DEBIT double precision;
declare variable JURNAL_DEBIT_A double precision;
declare variable JURNAL_KURS varchar(3);
declare variable JURNAL_NILAIKURS double precision;
declare variable JURNAL_KREDIT double precision;
declare variable JURNAL_KREDIT_A double precision;
declare variable JUAL_NOINDEX integer;
declare variable JUAL_IDPEMESAN integer;
declare variable JUAL_KURS varchar(3);
declare variable JUAL_NILAIKURS double precision;
declare variable JUAL_BIAYAANTAR_A double precision;
declare variable JUAL_BIAYAANTAR double precision;
declare variable JUAL_KODEBIAYAANTAR integer;
declare variable JUAL_NILAIDISCOUNT double precision;
declare variable JUAL_KODEDISCOUNT integer;
declare variable JUAL_UANGMUKA double precision;
declare variable JUAL_UANGMUKA_A double precision;
declare variable JUAL_KODEBANK integer;
declare variable JUAL_KODEPIUTANG integer;
declare variable JUAL_SALDO double precision;
declare variable JUAL_SALDO_A double precision;
declare variable JUAL_TUNAI varchar(1);
declare variable JUAL_NILAIPENJUALAN double precision;
declare variable JUAL_NILAIPENJUALAN_A double precision;
declare variable JUAL_GUDANG integer;
declare variable JUAL_JASA varchar(1);
declare variable JUAL_NOMORPO integer;
declare variable JUAL_KURSBANK varchar(3);
declare variable JUAL_NILAIKURSBANK double precision;
declare variable JUAL_NOJURNAL_MEMO integer;
declare variable JUAL_PEMESAN varchar(100);
declare variable JUAL_POSTED varchar(1);
declare variable JUAL_IDBINAUSAHA integer;
declare variable JUAL_BINAUSAHA varchar(100);
declare variable JUAL_BATASKREDIT double precision;
declare variable JUAL_BATASKREDIT_B double precision;
declare variable JUAL_KURS_PEMESAN varchar(3);
declare variable JUAL_NILAIKURS_PEMESAN double precision;
declare variable JUAL_DISCOUNTDAYS integer;
declare variable JUAL_DUEDAYS integer;
declare variable JUAL_EARLYDISCOUNT double precision;
declare variable JUAL_LATECHARGE double precision;
declare variable GET_ITEM_PENJUALAN_NOINDEX integer;
declare variable GET_ITEM_PENJUALAN_KODEBARANG integer;
declare variable GET_ITEM_PENJUALAN_JUMLAH double precision;
declare variable GET_ITEM_PENJUALAN_HARGA double precision;
declare variable GET_ITEM_PENJUALAN_KODE integer;
declare variable GET_ITEM_PENJUALAN_NOJOB integer;
declare variable GET_ITEM_PENJUALAN_DEPT integer;
declare variable GET_ITEM_PENJUALAN_HARGAJUAL double precision;
declare variable GET_ITEM_PENJUALAN_TOTAL double precision;
declare variable GET_ITEM_PENJUALAN_IDNOTE integer;
declare variable GET_ITEM_PENJUALAN_INDEXITEMPO integer;
declare variable GET_ITEM_PENJUALAN_TOTALDISC double precision;
declare variable GET_ITEM_PENJUALAN_ISUSELOT varchar(1);
declare variable GET_ITEM_PENJUALAN_KODEHARGAPOKOK integer;
declare variable GET_ITEM_PENJUALAN_DIINVENTORI varchar(1);
declare variable GET_ITEM_PENJUALAN_ISKONSINYASI varchar(1);
declare variable GET_ITEM_PENJUALAN_KODEKONSINYASI integer;
declare variable GET_ITEM_PENJUALAN_KODEINVENTORI integer;
declare variable A_ID integer;
declare variable A_USE_COMMISSION_TABLE varchar(1);
declare variable A_GOLONGAN integer;
declare variable A_KOMISI float;
declare variable B_KODEBARANG integer;
declare variable B_JUMLAHDIKIRIM double precision;
declare variable C_NOINDEX integer;
declare variable C_INDEXJURNAL integer;
declare variable C_KODEBIAYA integer;
declare variable C_HARGA_A double precision;
declare variable C_HARGA double precision;
declare variable C_JUMLAH double precision;
declare variable C_KODE integer;
declare variable C_DEPT integer;
declare variable C_NOJOB integer;
declare variable C_KURS varchar(3);
declare variable C_ISPAID varchar(1);
declare variable C_IDNOTE integer;
declare variable C_ISDISTRIBUTED varchar(1);
declare variable C_KODEAKUNJUAL integer;
declare variable C_KODEAKUNBELI integer;
declare variable C_NILAIKURS double precision;
declare variable D_IDGOLONGAN integer;
declare variable D_SALESAMOUNT double precision;
declare variable D_COMMISSION float;
declare variable ITMLOT_QTY double precision;
declare variable ITMLOT_LOT integer;
declare variable KODEKOMISIPENJUALAN integer;
declare variable KODEHUTANGKOMISIPENJUALAN integer;
declare variable ISINPUTNILAIJURNALOTOMATIK varchar(1);
declare variable SEDANGREKAM varchar(1);
declare variable ISSEDANGPOSTINGWRITEOFF varchar(1);
declare variable ISUSECEKMAXPIUTANG varchar(1);
declare variable KELOMPOKJURNAL integer;
declare variable TANGGALTRANSAKSI timestamp;
declare variable ISSEDANGBATCHPOSTING varchar(1);
declare variable ISUSECEKMAXPIUTANGOPSIMUTLAK varchar(1);
declare variable ISCREATECOMMISSIONJOURNAL varchar(1);
declare variable ISTOTALDISCOUNTAFTERTAX varchar(1);
declare variable ISPOSTINGLOTSERIALPAKAIREALCOST varchar(1);
declare variable ISUANGMUKAPENJUALANUSENOTEDARIPENJUALAN varchar(1);
declare variable ISUSETOTALDISCOUNT varchar(1);
declare variable ISAUTOJURNALNUMBER varchar(1);
declare variable ISPERPETUALBASIS varchar(1);
declare variable MATAUANGFUNGSIONAL varchar(3);
declare variable ISKOMISITOTALSALES varchar(1);
declare variable TOTALVOLUME double precision;
declare variable TOTALBERAT double precision;
declare variable TOTALSALES double precision;
declare variable TOTALPAJAK double precision;
declare variable TOTALQUANTITY double precision;
declare variable PERSENPAJAK double precision;
declare variable TOTALDISCOUNTAKHIR double precision;
declare variable TOTALDISCOUNTAKHIR_A double precision;
declare variable ETOTAL double precision;
declare variable EPAJAK double precision;
declare variable EPIUTANG double precision;
declare variable ETOTAL_A double precision;
declare variable EPAJAK_A double precision;
declare variable EPIUTANG_A double precision;
BEGIN
    SELECT COALESCE(BOOLEANRESULT, 'T') FROM INIFILES WHERE CONSTANTA = 'PENJUALAN-ISUSECEKMAXPIUTANG' INTO :ISUSECEKMAXPIUTANG;
    SELECT COALESCE(BOOLEANRESULT, 'F') FROM INIFILES WHERE CONSTANTA = 'PENJUALAN-ISUSECEKMAXPIUTANGOPSIMUTLAK' INTO :ISUSECEKMAXPIUTANGOPSIMUTLAK;
    SELECT COALESCE(BOOLEANRESULT, 'F') FROM INIFILES WHERE CONSTANTA = 'PENJUALAN-ISUANGMUKAPENJUALANUSENOTEDARIPENJUALAN' INTO :ISUANGMUKAPENJUALANUSENOTEDARIPENJUALAN;
    SELECT COALESCE(BOOLEANRESULT, 'F') FROM INIFILES WHERE CONSTANTA = 'PENJUALAN-ISCREATECOMMISSIONJOURNAL' INTO :ISCREATECOMMISSIONJOURNAL;
    SELECT COALESCE (BOOLEANRESULT, 'F') FROM INIFILES WHERE CONSTANTA = 'PENJUALAN-ISKOMISITOTALSALES' INTO :ISKOMISITOTALSALES;
    SELECT COALESCE(STRINGRESULT, 'USD') FROM INIFILES WHERE CONSTANTA = 'VALAS-MATAUANGFUNGSIONAL' INTO :MATAUANGFUNGSIONAL;
    SELECT COALESCE(BOOLEANRESULT, 'F') FROM INIFILES WHERE CONSTANTA = 'JURNAL-ISTOTALDISCOUNTAFTERTAX' INTO :ISTOTALDISCOUNTAFTERTAX;
    SELECT COALESCE(BOOLEANRESULT, 'F') FROM INIFILES WHERE CONSTANTA = 'JURNAL-ISUSETOTALDISCOUNT' INTO :ISUSETOTALDISCOUNT;
    SELECT COALESCE(BOOLEANRESULT, 'T') FROM INIFILES WHERE CONSTANTA = 'INFO-ISPERPETUALBASIS' INTO :ISPERPETUALBASIS;
    SELECT COALESCE(BOOLEANRESULT, 'F') FROM INIFILES WHERE CONSTANTA = 'PERSEDIAAN-ISPOSTINGLOTSERIALPAKAIREALCOST' INTO :ISPOSTINGLOTSERIALPAKAIREALCOST;
    
    SELECT NOINDEX, KELOMPOK, TANGGAL, KETERANGAN, NOJURNAL, ISEDITED, USEREDIT, JAMEDIT, DEPT, IDJOB, IDNOTE FROM G_JURNAL WHERE NOINDEX = :INDEXJURNAL
    INTO :GJURNAL_NOINDEX, :GJURNAL_KELOMPOK, :GJURNAL_TANGGAL, :GJURNAL_KETERANGAN, :GJURNAL_NOJURNAL, :GJURNAL_ISEDITED, :GJURNAL_USEREDIT, :GJURNAL_JAMEDIT, :GJURNAL_DEPT, :GJURNAL_IDJOB, :GJURNAL_IDNOTE;
    
    SELECT NOINDEX, IDPEMESAN, KURS, NILAIKURS, BIAYAANTAR_A, BIAYAANTAR, KODEBIAYAANTAR, NILAIDISCOUNT, KODEDISCOUNT, UANGMUKA, UANGMUKA_A, KODEBANK, KODEPIUTANG, SALDO, SALDO_A, TUNAI, NILAIPENJUALAN, NILAIPENJUALAN_A, GUDANG, JASA, NOMORPO, KURSBANK, NILAIKURSBANK, NOJURNAL_MEMO, POSTED, IDBINAUSAHA FROM SALES WHERE NOJURNAL = :INDEXJURNAL 
    INTO :JUAL_NOINDEX, :JUAL_IDPEMESAN, :JUAL_KURS, :JUAL_NILAIKURS, :JUAL_BIAYAANTAR_A, :JUAL_BIAYAANTAR, :JUAL_KODEBIAYAANTAR, :JUAL_NILAIDISCOUNT, :JUAL_KODEDISCOUNT, :JUAL_UANGMUKA, :JUAL_UANGMUKA_A, :JUAL_KODEBANK, :JUAL_KODEPIUTANG, :JUAL_SALDO, :JUAL_SALDO_A, :JUAL_TUNAI, :JUAL_NILAIPENJUALAN, :JUAL_NILAIPENJUALAN_A, :JUAL_GUDANG, :JUAL_JASA, :JUAL_NOMORPO, :JUAL_KURSBANK, :JUAL_NILAIKURSBANK, :JUAL_NOJURNAL_MEMO, :JUAL_POSTED, :JUAL_IDBINAUSAHA;
    
    --LookUp Fields untuk tabel jual
    SELECT PERUSAHAAN, BATASKREDIT, KURS FROM CARDFILE WHERE ID = :JUAL_IDPEMESAN INTO :JUAL_PEMESAN, :JUAL_BATASKREDIT, :JUAL_KURS_PEMESAN;
    SELECT PERUSAHAAN FROM CARDFILE WHERE ID = :JUAL_IDBINAUSAHA INTO :JUAL_BINAUSAHA;
    SELECT NILAI FROM KURSMSTR WHERE KURS = :JUAL_KURS_PEMESAN INTO :JUAL_NILAIKURS_PEMESAN;
    
    --Calculated Fields untuk tabel jual
    JUAL_BATASKREDIT_B = JUAL_BATASKREDIT * JUAL_NILAIKURS_PEMESAN;
    
    --Hitung Total Nilai Transaksi <-- Diambil dari procedure TotalNilaiTransaksi; (Delphi) start
    EXECUTE PROCEDURE TOTALNILAITRANSAKSI (:GJURNAL_NOINDEX, 'F')
    RETURNING_VALUES (:TOTALVOLUME, :TOTALBERAT, :TOTALSALES, :TOTALPAJAK, :TOTALQUANTITY, :PERSENPAJAK, :TOTALDISCOUNTAKHIR, :TOTALDISCOUNTAKHIR_A, :ETOTAL, :EPAJAK, :EPIUTANG, :ETOTAL_A, :EPAJAK_A, :EPIUTANG_A);
    --Hitung Total Nilai Transaksi <-- Diambil dari procedure TotalNilaiTransaksi; (Delphi) end

    RESULT = 'F';
    ISADASERIALLOTKURANG = 'F';
    GUDANGALTERNATIF = -1;
    ISINPUTNILAIJURNALOTOMATIK = 'T';
    SEDANGREKAM = 'T';
    ISSEDANGPOSTINGWRITEOFF = 'T';
    
    IF ((KELOMPOKJURNAL = 18) OR (KELOMPOKJURNAL = 19) OR (KELOMPOKJURNAL = 17)) THEN
        IRETURN = -1;
    ELSE
        IRETURN = 1;

    UANGMUKA = 0;
    ISADAPENYESUAIANBARANG = 'F';
    BIAYAANTAR = 0;
    TOTALBIAYALAINTERBAYAR = 0;
    TOTALBIAYALAINTERBAYAR_A = 0;
    ISADABIAYADIANTARTERBAYAR = 'F';
    PERSENKOMISI = 0;
    ISTELAHMEMBUATJURNALSELISIHRETUR = 'F';
    ISRETURPEMBELIANSELISIH = 'F';

    KODEDEPARTMEN = GJURNAL_DEPT;
    IF (GJURNAL_IDJOB IS NULL) THEN
        KODEPROYEK = 0;
    ELSE
        KODEPROYEK = GJURNAL_IDJOB;

    TANGGALTRANSAKSI = GJURNAL_TANGGAL;
    NOREF = GJURNAL_NOJURNAL;
    NOINDEXJURNAL = GJURNAL_NOINDEX;
    KETERANGANJURNAL = GJURNAL_KETERANGAN;
    KELOMPOKJURNALLAMA = GJURNAL_KELOMPOK;
    NAMAKELOMPOKJURNALLAMA = GJURNAL_NAMAKELOMPOK;
    USEREDIT = GJURNAL_USEREDIT;
    ISEDITED = GJURNAL_ISEDITED;
    JAMEDIT = GJURNAL_JAMEDIT;
    --HasilCekVoucher := PeriksaVoucher(qryJurnalUmumTANGGAL.AsDateTime, True);
    
    IF ((ISUSECEKMAXPIUTANG = 'T') AND (JUAL_TUNAI = 'F')) THEN
    BEGIN
        IF (JUAL_BATASKREDIT > 0) THEN
        BEGIN
            EXECUTE PROCEDURE GETSALDOPIUTANG (JUAL_IDPEMESAN) RETURNING_VALUES (:SALDOPIUTANGLAMA);
            IF (((JUAL_NILAIPENJUALAN - JUAL_UANGMUKA) * IRETURN > JUAL_BATASKREDIT_B) OR ((SALDOPIUTANGLAMA + (JUAL_NILAIPENJUALAN - JUAL_UANGMUKA) * IRETURN) > JUAL_BATASKREDIT_B)) THEN
            BEGIN
                IF ((ISUSECEKMAXPIUTANGOPSIMUTLAK = 'T') OR (ISSEDANGBATCHPOSTING = 'T')) THEN
                BEGIN
                    VS_MESSAGE = 'Transaksi Melewati Batas Kredit!';
                    EXIT;
                END
                ELSE
                IF (ISSEDANGBATCHPOSTING = 'F') THEN
                BEGIN
                    VS_MESSAGE = 'Transaksi ini menyebabkan piutang usaha pelanggan melebihi batas kredit yang telah ditentukan.';
                    EXIT;
                END
            END
        END
    END

    JUAL_POSTED = 'T';
    UPDATE SALES SET POSTED = 'T' WHERE NOINDEX = :JUAL_NOINDEX;
          
    IF (ISFITURSERIAL = 'T') THEN
    BEGIN
        EXECUTE PROCEDURE INSERT_SERIAL (GJURNAL_NOINDEX, JUAL_GUDANG);
        EXECUTE PROCEDURE UPDATE_SERIAL_JUAL (GJURNAL_NOINDEX, JUAL_GUDANG);
    END
    
    IF (ISFITURLOT = 'T') THEN
    BEGIN
        EXECUTE PROCEDURE ITMLOTNEEDTOUPDATE (GJURNAL_NOINDEX) RETURNING_VALUES (:RESULT_ITMLOTNEEDTOUPDATE);
        IF (RESULT_ITMLOTNEEDTOUPDATE = 'T') THEN 
        BEGIN
            EXECUTE PROCEDURE REPOSTITMLOT (GJURNAL_NOINDEX);
        END
        EXECUTE PROCEDURE POSTITEMLOTMASUK (GJURNAL_NOINDEX, -1, 'F') RETURNING_VALUES (:RESULT_POSTITEMLOTMASUK);
        EXECUTE PROCEDURE POSTITEMLOTKELUAR (GJURNAL_NOINDEX, -1) RETURNING_VALUES (:RESULT_POSTITEMLOTKELUAR);
        EXECUTE PROCEDURE ISTRANSAKSIRETUR (GJURNAL_NOINDEX) RETURNING_VALUES (:RESULT_ISTRANSAKSIRETUR);
        IF ((IRETURN < 0) OR (RESULT_ISTRANSAKSIRETUR = 'T')) THEN
        BEGIN
            EXECUTE PROCEDURE SETSTATUSRETURONLOTLEDGR (GJURNAL_NOINDEX, 'T');
        END
    END
    
    RESULT = 'T';
    VS_MESSAGE = 'SUKSES';
END^

SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT SELECT ON INIFILES TO PROCEDURE POSTINGPENJUALAN;
GRANT SELECT ON G_JURNAL TO PROCEDURE POSTINGPENJUALAN;
GRANT SELECT,UPDATE ON SALES TO PROCEDURE POSTINGPENJUALAN;
GRANT SELECT ON CARDFILE TO PROCEDURE POSTINGPENJUALAN;
GRANT SELECT ON KURSMSTR TO PROCEDURE POSTINGPENJUALAN;
GRANT EXECUTE ON PROCEDURE TOTALNILAITRANSAKSI TO PROCEDURE POSTINGPENJUALAN;
GRANT EXECUTE ON PROCEDURE GETSALDOPIUTANG TO PROCEDURE POSTINGPENJUALAN;
GRANT EXECUTE ON PROCEDURE INSERT_SERIAL TO PROCEDURE POSTINGPENJUALAN;
GRANT EXECUTE ON PROCEDURE UPDATE_SERIAL_JUAL TO PROCEDURE POSTINGPENJUALAN;
GRANT EXECUTE ON PROCEDURE ITMLOTNEEDTOUPDATE TO PROCEDURE POSTINGPENJUALAN;
GRANT EXECUTE ON PROCEDURE REPOSTITMLOT TO PROCEDURE POSTINGPENJUALAN;
GRANT EXECUTE ON PROCEDURE POSTITEMLOTMASUK TO PROCEDURE POSTINGPENJUALAN;
GRANT EXECUTE ON PROCEDURE POSTITEMLOTKELUAR TO PROCEDURE POSTINGPENJUALAN;
GRANT EXECUTE ON PROCEDURE ISTRANSAKSIRETUR TO PROCEDURE POSTINGPENJUALAN;
GRANT EXECUTE ON PROCEDURE SETSTATUSRETURONLOTLEDGR TO PROCEDURE POSTINGPENJUALAN;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE POSTINGPENJUALAN TO ZAHIRDBA;