execute block as
begin
IF (EXISTS(SELECT RDB$INDEX_NAME FROM RDB$INDICES WHERE RDB$RELATION_NAME='ITEMINVOICE' and RDB$INDEX_NAME = 'ITEMINVOICE_NOORDER')) THEN
  execute statement 'drop index ITEMINVOICE_NOORDER';
end;

create index ITEMINVOICE_NOORDER ON ITEMINVOICE (NOORDER);


update RDB$FIELDS set
RDB$FIELD_TYPE = 27,
RDB$FIELD_LENGTH = 8,
RDB$CHARACTER_LENGTH = NULL
where RDB$FIELD_NAME = 'RDB$16505';



update RDB$FIELDS set
RDB$FIELD_TYPE = 27,
RDB$FIELD_LENGTH = 8,
RDB$CHARACTER_LENGTH = NULL
where RDB$FIELD_NAME = 'RDB$16502';


--update sql pajak in / pajak out



EXECUTE PROCEDURE TBL_ADD_COL('INVENTOR', 'ISSELFBILLED', 'VARCHAR(1) ');


EXECUTE PROCEDURE DROP_EXISTING_VIEW('VW_ACCPAY');

create view VW_ACCPAY as
SELECT Cardfile.ID , Cardfile.KODE,  G_jurnal.*, Cardfile.KURS, Cardfile.WEBPAGES as GSTNO,
itembeli.noindex as indexbeli, beli.ISGST1, beli.GSTNO1, beli.ISGST2, beli.GSTNO2,
((beli.nilaipenjualan_a - beli.uangmuka_a) * Kursmstr.NILAI ) as saldoakhir,
(case g_jurnal.isinclusivetax
when 'T' then (itembeli.jumlah * itembeli.hargaf1) - (itembeli.jumlah * itembeli.harga)
else ((itembeli.jumlah * itembeli.hargaf1) * (tax.persentase/100))  end) nilaipajak
, saldo, saldo_a
FROM Beli Beli , Cardfile Cardfile , Kursmstr Kursmstr, G_jurnal, itembeli, tax
WHERE  
(Beli.Posted = 'T') and (beli.TIPE <> '8')
and Beli.IDPEMESAN = Cardfile.ID
and Kursmstr.KURS = Beli.KURS
and beli.noindex = itembeli.indexjurnal  and itembeli.idpajak1 = tax.noindex
and ( G_jurnal.NOINDEX = beli.NOJURNAL ) and g_jurnal.kelompok = 5 and beli.tipe=3;




EXECUTE PROCEDURE DROP_EXISTING_VIEW('VW_ACCREC');

create view VW_ACCREC as
SELECT Cardfile.ID , Cardfile.KODE,  G_jurnal.*, Cardfile.KURS, Cardfile.WEBPAGES as GSTNO,
itemsale.noindex as indexsale, sales.ISGST1, sales.GSTNO1, sales.ISGST2, sales.GSTNO2,
((sales.nilaipenjualan_a - sales.uangmuka_a) * Kursmstr.NILAI ) as saldoakhir,
(case g_jurnal.isinclusivetax
when 'T' then (itemsale.jumlah * itemsale.hargaf1) - (itemsale.jumlah * itemsale.harga)
else ((itemsale.jumlah * itemsale.hargaf1) * (tax.persentase/100))  end) nilaipajak
, saldo, saldo_a
FROM sales sales , Cardfile Cardfile , Kursmstr Kursmstr, G_jurnal, itemsale, tax
WHERE 
(sales.Posted = 'T') and (sales.TIPE <> '7')
and sales.IDPEMESAN = Cardfile.ID
and Kursmstr.KURS = sales.KURS
and sales.noindex = itemsale.indexjurnal  and itemsale.idpajak1 = tax.noindex
and ( G_jurnal.NOINDEX = sales.NOJURNAL ) and g_jurnal.kelompok = 4 and sales.tipe=3;

delete from taxmaster where noindex = 17;
update taxmaster set pajak = 'TX-IES' where noindex = 9;

EXECUTE PROCEDURE TBL_ADD_COL('BELI', 'SIMPLIFIED', 'varchar(1)');
EXECUTE PROCEDURE TBL_ADD_COL('SALES', 'SIMPLIFIED', 'varchar(1)');
EXECUTE PROCEDURE TBL_ADD_COL('INVENTOR', 'ISSELFBILLED', 'VARCHAR(1)');


update TAX set tax.IN_OUT = (select taxmaster.in_out from taxmaster where taxmaster.pajak = tax.pajak);





EXECUTE PROCEDURE DROP_EXISTING_VIEW('VW_TOTAX');

CREATE VIEW VW_TOTAX(
    KELOMPOK,
    NOJURNAL,
    TANGGAL,
    PAJAK,
    NILAI,
    NILAIPAJAK)
AS
select a.kelompok as kelompok, a.nojurnal as nojurnal, a.tanggal as tanggal, a.pajak as pajak, a.nilai as nilai, a.nilaipajak as nilaipajak from (
select g_jurnal.kelompok, g_jurnal.nojurnal, g_jurnal.tanggal, tax.pajak,
(itemsale.jumlah * itemsale.harga) Nilai,
(case g_jurnal.isinclusivetax
when 'T' then (itemsale.jumlah * itemsale.hargaf1) - (itemsale.jumlah * itemsale.harga)
else ((itemsale.jumlah * itemsale.hargaf1) * (tax.persentase/100))  end) nilaipajak
from g_jurnal, sales, itemsale, tax
where g_jurnal.noindex = sales.nojurnal  and sales.noindex = itemsale.indexjurnal  and itemsale.idpajak1 = tax.noindex
and sales.posted = 'T' and sales.gift = 'F'
union all
select g_jurnal.kelompok, g_jurnal.nojurnal, g_jurnal.tanggal, tax.pajak,
jurnal.kredit_a as Nilai,
(select b.kredit_a from jurnal b where jurnal.noindex = b.refid) as nilaipajak
from g_jurnal, jurnal, tax
left join kasout on g_jurnal.noindex = kasout.nojurnal
where g_jurnal.noindex = jurnal.noindex_jurnal
and jurnal.idpajak1 = tax.noindex
and (ISHIDE IS NULL OR ISHIDE = 'F')
and g_jurnal.kelompok in (1,3)
and kasout.posted = 'T'
union all
select g_jurnal.kelompok, g_jurnal.nojurnal, g_jurnal.tanggal, tax.pajak,
((invjur.jumlah * -1) * invjur.hargasatuan_a) as Nilai,
invjur.nilaipajak as Nilaipajak
from g_jurnal, invjur, tax
--left join kasout on g_jurnal.noindex = kasout.nojurnal
where g_jurnal.noindex = invjur.nojurnal
and invjur.idpajak1 = tax.noindex
and (ISHIDE IS NULL OR ISHIDE = 'F')
and g_jurnal.kelompok in (6)
and invjur.jumlah < 0
) a;




EXECUTE PROCEDURE DROP_EXISTING_VIEW('VW_TITAX');


CREATE VIEW VW_TITAX(
    KELOMPOK,
    NOJURNAL,
    TANGGAL,
    PAJAK,
    NILAI,
    NILAIPAJAK)
AS
select a.kelompok as kelompok, a.nojurnal as nojurnal, a.tanggal as tanggal, a.pajak as pajak, a.nilai as nilai, a.nilaipajak as nilaipajak from (
select g_jurnal.kelompok, g_jurnal.nojurnal, g_jurnal.tanggal, tax.pajak,
(itembeli.jumlah * itembeli.harga) Nilai,
case beli.SIMPLIFIED
  when 'T' then
    case
     when ((case g_jurnal.isinclusivetax
        when 'T' then (itembeli.jumlah * itembeli.hargaf1) - (itembeli.jumlah * itembeli.harga)
        else ((itembeli.jumlah * itembeli.hargaf1) * (tax.persentase/100))  end) > 30) then
       30
     else
        (case g_jurnal.isinclusivetax
        when 'T' then (itembeli.jumlah * itembeli.hargaf1) - (itembeli.jumlah * itembeli.harga)
        else ((itembeli.jumlah * itembeli.hargaf1) * (tax.persentase/100))  end)
     end
  else
      (case g_jurnal.isinclusivetax
    when 'T' then (itembeli.jumlah * itembeli.hargaf1) - (itembeli.jumlah * itembeli.harga)
    else ((itembeli.jumlah * itembeli.hargaf1) * (tax.persentase/100))  end)
end
as nilaipajak
from g_jurnal, beli, itembeli, tax
where g_jurnal.noindex = beli.nojurnal  and beli.noindex = itembeli.indexjurnal  and itembeli.idpajak1 = tax.noindex
and beli.posted = 'T'
union all
select g_jurnal.kelompok, g_jurnal.nojurnal, g_jurnal.tanggal, tax.pajak,
jurnal.debit_a as Nilai,
(select b.debit_a from jurnal b where jurnal.noindex = b.refid) as nilaipajak
from g_jurnal, jurnal, tax, kasout
where g_jurnal.noindex = jurnal.noindex_jurnal
and g_jurnal.noindex = kasout.nojurnal
and jurnal.idpajak1 = tax.noindex
and (ISHIDE IS NULL OR ISHIDE = 'F')
and g_jurnal.kelompok = 2
and kasout.posted = 'T'
union all
select g_jurnal.kelompok, g_jurnal.nojurnal, g_jurnal.tanggal, tax.pajak,
jurnal.debit_a as Nilai,
(select b.debit_a from jurnal b where jurnal.noindex = b.refid) as nilaipajak
from g_jurnal, jurnal, tax
where g_jurnal.noindex = jurnal.noindex_jurnal
and jurnal.idpajak1 = tax.noindex
and (ISHIDE IS NULL OR ISHIDE = 'F')
and g_jurnal.kelompok = 1
and g_jurnal.posting = 'T'
union all
select g_jurnal.kelompok, g_jurnal.nojurnal, g_jurnal.tanggal, tax.pajak,
((invjur.jumlah * -1) * invjur.hargasatuan_a) as Nilai,
invjur.nilaipajak as Nilaipajak
from g_jurnal, invjur, tax
--left join kasin on g_jurnal.noindex = kasin.nojurnal
where g_jurnal.noindex = invjur.nojurnal
and invjur.idpajak1 = tax.noindex
and (ISHIDE IS NULL OR ISHIDE = 'F')
and g_jurnal.kelompok in (6)
and invjur.jumlah > 0
) a;






SET TERM ^ ;

create or alter procedure TBL_SELFBILLED(
    relation_name varchar(100))
as
begin
    
    IF (NOT exists(SELECT * from rdb$relations where RDB$RELATION_NAME = :relation_name)) then
    begin

    execute statement '
    CREATE GENERATOR GEN_SELFBILLED_NOINDEX;
    ';

    execute statement 'CREATE TABLE SELFBILLED (
    NOINDEX     INTEGER NOT NULL,
    IDPEMESAN     INTEGER NOT NULL,
    GST_REFNUM     VARCHAR(100) NOT NULL,
    TANGGAL  TIMESTAMP,
    TANGGALEDIT      TIMESTAMP
);

  ';
  
  execute statement '
    ALTER TABLE SELFBILLED ADD CONSTRAINT PK_SELFBILLED PRIMARY KEY (NOINDEX);
    ';
    
  end

end
^

SET TERM ; ^

COMMIT;

EXECUTE PROCEDURE TBL_SELFBILLED('SELFBILLED');

COMMIT;




SET TERM ^ ;


CREATE OR ALTER TRIGGER TRIG_SELFBILLED_NOINDEX FOR SELFBILLED
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
as
begin
  if (new.NOINDEX is null) then
    new.NOINDEX = gen_id(GEN_SELFBILLED_NOINDEX, 1);
    new.tanggaledit = CURRENT_TIMESTAMP;
end
^


SET TERM ; ^



commit;




SET TERM ^ ;

create or alter procedure CEK_INVJUR_QTY (
    KODEGUDANG integer,
    NOINDEX integer)
returns (
    NOINDEXBARANG integer,
    SALDO double precision,
    SISADIGUDANG double precision,
    JUMLAHDIPERLUKAN double precision)
as
Begin
 For Select Invjur.Kodebarang  ,
 ( Avg( Igudang.Jumlah ) + Sum( Invjur.Jumlah ) ) ,
 ( Avg( Igudang.Jumlah ) ),
 ( Sum( Invjur.Jumlah ) )

From Invjur Invjur ,
 Igudang Igudang

Where
 ( Invjur.Kodebarang = Igudang.Kodebarang )
  And
 ( Invjur.nojurnal =  :Noindex )
  And
 ( Igudang.Kodegudang = :Kodegudang )
 and
 ( Igudang.jumlah < 0 )

Group By
 Invjur.Kodebarang

HAVING
      (10000*Avg( Igudang.Jumlah)) - (10000*Sum( Invjur.Jumlah ))  < -0.0001

Into :Noindexbarang, :Saldo,
      :Sisadigudang, :Jumlahdiperlukan

 Do
  Suspend;
End^

SET TERM ; ^


COMMIT;


SET TERM ^ ;

create or alter procedure DUPLICATE_SN(
    relation_name varchar(100))
as
begin

     IF (exists(select * from RDB$INDICES where RDB$RELATION_NAME = :relation_name and RDB$INDEX_NAME = 'DUPLICATE_SERIAL_NUMBER')) then
    begin

    execute statement '
    DROP INDEX DUPLICATE_SERIAL_NUMBER;
    ';
    
  end
  

end
^

SET TERM ; ^

COMMIT;


EXECUTE PROCEDURE DUPLICATE_SN('SERIAL');

COMMIT;

DROP PROCEDURE DUPLICATE_SN;

COMMIT;


--patch on 10a

set term ^ ;

create or alter procedure removequotecardfile( aquote varchar (1), areplacechar varchar (1)) 
as 
begin
  update cardfile set webpages= replace(webpages, :aquote, :areplacechar);
end^

set term ; ^

execute procedure removequotecardfile('-', '');

execute procedure removequotecardfile('.', '');

update cardfile set webpages = null where webpages = ' ';
update cardfile set webpages = null where webpages = '';

commit;

drop procedure removequotecardfile;

COMMIT;

SET TERM ^ ;

create or alter procedure TOTAL_BELI_JASA (
    NOINDEX integer)
returns (
    TOTAL double precision,
    PAJAK double precision,
    TOTALQTY double precision,
    TOTALUM double precision)
as
begin
for SELECT ( sum(itembeli.HARGA * itembeli.JUMLAH*(1-itembeli.DISCOUNT/100)*(1-itembeli.DISCOUNT2/100)*(1-itembeli.DISCOUNT3/100)*(1-itembeli.DISCOUNT4/100)*(1-itembeli.DISCOUNT5/100)) )  as TOTAL ,
 ( sum(itembeli.HARGA * itembeli.JUMLAH*(1-itembeli.DISCOUNT/100)*(1-itembeli.DISCOUNT2/100)*(1-itembeli.DISCOUNT3/100)*(1-itembeli.DISCOUNT4/100)*(1-itembeli.DISCOUNT5/100)*((itembeli.PAJAK/100)+(itembeli.PAJAK2/100)+(itembeli.PAJAK3/100)+(itembeli.PAJAK4/100)+(itembeli.PAJAK5/100))) )  as PAJAK , 
 ( sum(itembeli.JUMLAH) )  as TOTALQTY,
 ( sum( itembeli.NILAIUANGMUKA ) )  as TOTALUM
FROM itembeli itembeli
WHERE ( itembeli.INDEXJURNAL = :NOINDEX )
     INTO :TOTAL, :PAJAK, :TOTALQTY, :TOTALUM

 do
  suspend;
end^

SET TERM ; ^


commit;