Sample Store Procedure PostgreSql

– Function: sppostingjournal(character varying, character varying)

– DROP FUNCTION sppostingjournal(character varying, character varying);

CREATE OR REPLACE FUNCTION sppostingjournal(yearprd character varying, monthprd character varying)
RETURNS integer AS
$BODY$

DECLARE journalitem tsacgeneraljournalitem;
count_item int;
query character varying;
count int;
count_ledger int;
acledger tsacledger;
column_name_bb character varying;
column_name_db character varying;
column_name_cd character varying;
column_name_eb character varying;
exist_bb numeric ;
exist_db numeric ;
exist_cd numeric ;
exist_eb numeric ;
coa tschartofaccounts;
coa_parent tschartofaccounts;
coa_level int;
month_prd character varying;

BEGIN
month_prd := $2;

IF (length(month_prd) = 1) THEN
month_prd := ’0′||$2;
END IF;

column_name_bb = ‘bb’||month_prd;
column_name_db = ‘db’||month_prd;
column_name_cd = ‘cd’||month_prd;
column_name_eb = ‘eb’||month_prd;

query = ‘select * from tsacgeneraljournalitem
where gjno in (select gjno from tsacgeneraljournal where yearperiod=”’ || $1 || ”’ and monthperiod=”’ || $2 || ”’
and isposted=”false” and isclosed=”false”
and isvalidated=”true”)
and isposted=”false” and isclosed=”false” and isvalidated=”true”’;

FOR journalitem in EXECUTE query

LOOP

exist_bb := 0;
exist_db := 0;
exist_cd := 0;
exist_eb := 0;

query = ‘select count(*) from tsacledger where yearperiod=”’ || $1 || ”’ and coano=”’||journalitem.coano||”’ ‘;

EXECUTE query INTO count_ledger;

IF (count_ledger = 0) THEN
query = ‘insert into tsacledger (“yearperiod”,”coano”,”‘||column_name_db||’”,”‘||column_name_cd||’”,”lastupdated”, “updater”)
values (”’ || $1 || ”’,”’||journalitem.coano||”’,'||journalitem.amountofdebit||’,'||journalitem.amountofcredit||’,now(),”ADMIN”) ‘;

EXECUTE query;

ELSE

query = ‘select ‘||column_name_db||’ from tsacledger where yearperiod=”’ || $1 || ”’ and coano=”’||journalitem.coano||”’ ‘;
EXECUTE query INTO exist_db;

query = ‘select ‘||column_name_cd||’ from tsacledger where yearperiod=”’ || $1 || ”’ and coano=”’||journalitem.coano||”’ ‘;
EXECUTE query INTO exist_cd;

select into acledger tsacledger.* from tsacledger where yearperiod=” || $1 || ” and coano=”||journalitem.coano||”;

exist_db := exist_db + journalitem.amountofdebit;
exist_cd := exist_cd + journalitem.amountofcredit;

query = ‘update tsacledger set ‘||column_name_db||’ = ‘||exist_db||’, ‘||column_name_cd||’ = ‘||exist_cd||’ where yearperiod=”’ || $1 || ”’ and coano=”’||journalitem.coano||”’ ‘;
EXECUTE query;

END IF;

select into coa tschartofaccounts.* from tschartofaccounts where coano=”||journalitem.coano||”;

count := coa.levelno – 1;

IF (count > 0) THEN

LOOP

exist_bb := 0;
exist_db := 0;
exist_cd := 0;
exist_eb := 0;

select into coa_parent tschartofaccounts.* from tschartofaccounts where coano=coa.coanogroup;

query = ‘select count(*) from tsacledger where yearperiod=”’ || $1 || ”’ and coano=”’||coa_parent.coano||”’ ‘;

EXECUTE query INTO count_ledger;

IF (count_ledger = 0) THEN
query = ‘insert into tsacledger (“yearperiod”,”coano”,”‘||column_name_db||’”,”‘||column_name_cd||’”,”lastupdated”, “updater”)
values (”’ || $1 || ”’,”’||coa_parent.coano||”’,'||journalitem.amountofdebit||’,'||journalitem.amountofcredit||’,now(),”ADMIN”) ‘;

EXECUTE query;

ELSE
query = ‘select ‘||column_name_db||’ from tsacledger where yearperiod=”’ || $1 || ”’ and coano=”’||coa_parent.coano||”’ ‘;
EXECUTE query INTO exist_db;

query = ‘select ‘||column_name_cd||’ from tsacledger where yearperiod=”’ || $1 || ”’ and coano=”’||coa_parent.coano||”’ ‘;
EXECUTE query INTO exist_cd;

select into acledger tsacledger.* from tsacledger where yearperiod=” || $1 || ” and coano=”||coa_parent.coano||”;

exist_db := exist_db + journalitem.amountofdebit;
exist_cd := exist_cd + journalitem.amountofcredit;

query = ‘update tsacledger set ‘||column_name_db||’ = ‘||exist_db||’, ‘||column_name_cd||’ = ‘||exist_cd||’ where yearperiod=”’ || $1 || ”’ and coano=”’||coa_parent.coano||”’ ‘;
EXECUTE query;

END IF;

count:=count-1;

IF(count>0)THEN
select into coa tschartofaccounts.* from tschartofaccounts where coano=”||coa_parent.coano||”;
ELSE
exit;

END IF;

END LOOP;

END IF;

query = ‘update tsacgeneraljournalitem set isposted = true where gjno = ”’||journalitem.gjno||”’ and gjtranslineid=’||journalitem.gjtranslineid||’ ‘;
EXECUTE query;

query = ‘update tsacgeneraljournal set isposted=true where gjno = ”’||journalitem.gjno||”’ ‘;
EXECUTE query;

END LOOP;

RETURN 1;

END;

$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION sppostingjournal(character varying, character varying) OWNER TO postgres;