– 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;
)
Recent Comments