Function Yang Memangil Functin - PostgreSQL

 CREATE OR REPLACE FUNCTION "public"."fnc_laproa"("user_id" int4, "var_id_bpr" int4, "var_id_bpr_cabang" int4, "tahundate" text, "bulandate" text)

 RETURNS "pg_catalog"."text" AS $BODY$

DECLARE 

 titles TEXT DEFAULT '';

  finalreport   record;

loopneraca "temporary".tmp_proses_roa_awal%ROWTYPE;

BEGIN

---

delete from "temporary".tmp_proses_roa_awal where userid = user_id;

delete from "temporary".tmp_userid;

--create table "temporary".tmp_proses_roa_awal as

insert into "temporary".tmp_proses_roa_awal

(userid, tglawalbulan, tglakhirbulan, totalaset, labasebelumpajak)

select 

user_id userid,

tglawalbulan, (date_trunc('month', tglawalbulan::date) + interval '1 month' - interval '1 day')::date tglakhirbulan,

0 totalaset,

0 labasebelumpajak

from (

select unnest(array[satu, dua, tiga, empat, lima, enam, tujuh, delapan, sembilan, sepuluh, sebelas, duabelas]) tglawalbulan from (

select 

((( ( date_trunc( 'MONTH', CAST ( concat ( tahundate, '-', bulandate, '-', '01' ) AS DATE ) ) ) :: DATE ) - interval '11 month'):: date) satu,

((( ( date_trunc( 'MONTH', CAST ( concat ( tahundate, '-', bulandate, '-', '01' ) AS DATE ) ) ) :: DATE ) - interval '10 month'):: date) dua,

((( ( date_trunc( 'MONTH', CAST ( concat ( tahundate, '-', bulandate, '-', '01' ) AS DATE ) ) ) :: DATE ) - interval '9 month'):: date) tiga,

((( ( date_trunc( 'MONTH', CAST ( concat ( tahundate, '-', bulandate, '-', '01' ) AS DATE ) ) ) :: DATE ) - interval '8 month'):: date) empat,

((( ( date_trunc( 'MONTH', CAST ( concat ( tahundate, '-', bulandate, '-', '01' ) AS DATE ) ) ) :: DATE ) - interval '7 month'):: date) lima,

((( ( date_trunc( 'MONTH', CAST ( concat ( tahundate, '-', bulandate, '-', '01' ) AS DATE ) ) ) :: DATE ) - interval '6 month'):: date) enam,

((( ( date_trunc( 'MONTH', CAST ( concat ( tahundate, '-', bulandate, '-', '01' ) AS DATE ) ) ) :: DATE ) - interval '5 month'):: date) tujuh,

((( ( date_trunc( 'MONTH', CAST ( concat ( tahundate, '-', bulandate, '-', '01' ) AS DATE ) ) ) :: DATE ) - interval '4 month'):: date) delapan,

((( ( date_trunc( 'MONTH', CAST ( concat ( tahundate, '-', bulandate, '-', '01' ) AS DATE ) ) ) :: DATE ) - interval '3 month'):: date) sembilan,

((( ( date_trunc( 'MONTH', CAST ( concat ( tahundate, '-', bulandate, '-', '01' ) AS DATE ) ) ) :: DATE ) - interval '2 month'):: date) sepuluh,

((( ( date_trunc( 'MONTH', CAST ( concat ( tahundate, '-', bulandate, '-', '01' ) AS DATE ) ) ) :: DATE ) - interval '1 month'):: date) sebelas,

( ( date_trunc( 'MONTH', CAST ( concat ( tahundate, '-', bulandate, '-', '01' ) AS DATE ) ) ) :: DATE ) duabelas) xxx) tglawalakhir;

for loopneraca in

select * from "temporary".tmp_proses_roa_awal where userid = user_id

loop

--- CARA PEMANGGILAN FUNCTION ---

insert into "temporary".tmp_userid

select fnc_neraca_atmr(loopneraca.userid, var_id_bpr, var_id_bpr_cabang, to_char(loopneraca.tglakhirbulan,'yyyy-mm-dd'), to_char(loopneraca.tglakhirbulan,'yyyy-mm-dd'), to_char(loopneraca.tglakhirbulan,'yyyymm'));

--- CARA PEMANGGILAN FUNCTION ---

update "temporary".tmp_proses_roa_awal 

set totalaset=(select COALESCE(sum(saldoawal+debet-kredit),0) saldo from "temporary".tmp_neraca_atmr where userid = user_id and left(nomor_akun,1)='1')

where userid = user_id and tglakhirbulan=loopneraca.tglakhirbulan;

end loop;

select userid into finalreport from "temporary".tmp_proses_roa_awal where userid = user_id limit 1;

   RETURN finalreport;

END; $BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100