--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:

>
> At 01:35 PM 7/05/2009, you wrote:
>
> >Hi helen
> >
> >i gave sone recursive sp
> >this sp(s) do really depth process (up to 4096 level !) and also use some temp table have firebird really power of do it ?
>
> The SPs that you have shown us are not recursive. However, the main proc calls two PRC_SYS$OBJ_DIRECT_GRANT and PRC_SYS$OBJ_GRANT, that you have not shown. Are they recursive? Why don't you show them?
>
> ./hb
>

create or alter procedure PRC_ACC_INT_TREECALC_R3_DDF (
I_SERIES_DOC DM_ID,
I_L2I_START_NODE DM_ID,
I_PARENT_ID DM_ID,
I_WITH_PREDOC DM_ID,
I_START_DOC_ID DM_ID default null,
I_END_DOC_ID DM_ID default null,
I_START_DATE DM_DATE default null,
I_END_DATE DM_DATE default null)
returns (
RET_SUMOFBED DM_MONEY_BIG,
RET_SUMOFBES DM_MONEY_BIG)
AS
declare variable z_bed DM_MONEY_BIG;
declare variable z_calcid DM_ID;
declare variable z_bes DM_MONEY_BIG;
declare variable z_node_id DM_ID;
declare variable z_node_name varchar(50);
declare variable z_node_code DM_MONEY_BIG;
declare variable z_count_parent DM_ID;
declare variable z_pd_nid DM_ID;
declare variable z_pd_series DM_ID;
declare variable z_pd_doc_link DM_ID;
declare variable z_pd_name varchar(40);
declare variable z_pd_bed DM_MONEY_BIG;
declare variable z_pd_bes DM_MONEY_BIG;
declare variable z_pd_row DM_ID;
declare variable z_level_nid DM_ID;
declare variable z_level_level DM_ID;
declare variable z_level_name varchar(15);
DECLARE VARIABLE Z_ICOUNT DM_ID;
DECLARE VARIABLE Z_PARENTREE_ABSSUM DM_MONEY_BIG;
DECLARE VARIABLE Z_START_DOC_NUM DM_ID;
DECLARE VARIABLE Z_END_DOC_NUM DM_ID;
DECLARE VARIABLE Z_ERROR DM_COMMENT_110;
BEGIN
-- Init
RET_SUMOFBED = 0;
RET_SUMOFBES = 0;

IF (:I_START_DATE IS NULL ) THEN
SELECT MIN((CAST(D.DOC_DATE AS DATE)) ) FROM ACC$DOC D WHERE (D.SERIES = :I_SERIES_DOC) INTO :I_START_DATE;
IF (:I_END_DATE IS NULL ) THEN
SELECT MAX((CAST(D.DOC_DATE AS DATE)) ) FROM ACC$DOC D WHERE (D.SERIES = :I_SERIES_DOC) INTO :I_END_DATE;
IF (:I_START_DOC_ID IS NULL ) THEN
SELECT FIRST 1 D.NID, D.DOC_NUM FROM ACC$DOC D
WHERE ((D.SERIES = :I_SERIES_DOC) AND ((CAST(D.DOC_DATE AS DATE)) >= :I_START_DATE) AND
((CAST(D.DOC_DATE AS DATE)) <= :I_END_DATE))
ORDER BY D.DOC_NUM INTO :I_START_DOC_ID, :Z_START_DOC_NUM;
ELSE
SELECT D.DOC_DATE, D.DOC_NUM FROM ACC$DOC D WHERE D.NID = :I_START_DOC_ID INTO :I_START_DATE, :Z_START_DOC_NUM;
IF (:I_END_DOC_ID IS NULL) THEN
SELECT FIRST 1 D.NID, D.DOC_NUM FROM ACC$DOC D
WHERE ((D.SERIES = :I_SERIES_DOC) AND ((CAST(D.DOC_DATE AS DATE)) >= :I_START_DATE) AND
((CAST(D.DOC_DATE AS DATE)) <= :I_END_DATE))
ORDER BY D.DOC_NUM DESC INTO :I_END_DOC_ID, :Z_END_DOC_NUM;
ELSE
SELECT D.DOC_DATE, D.DOC_NUM FROM ACC$DOC D WHERE D.NID = :I_END_DOC_ID INTO :I_END_DATE, :Z_END_DOC_NUM;

--IF NO DEFINE I_With_PREDOC MEAN DONT FILTER IT.
IF (I_With_PREDOC IS NULL) THEN
I_With_PREDOC = 1;
--CHK START NODE IS VALID
z_NODE_ID = NULL;
SELECT T.NID, C.NAME, T.ACC_CODE FROM ACC$TREE$BASE$R3 T LEFT JOIN ACC$CODE$BASE$R2 C ON (C.NID = T.CODE_BASE)
WHERE ((T.NID = :I_L2I_START_NODE) AND (T.SERIES = :I_SERIES_DOC))
INTO :z_NODE_ID,:Z_NODE_NAME,:Z_NODE_CODE;

IF (z_NODE_ID IS NOT NULL) THEN BEGIN
--INSERT CURRENT RECORD TO CALC
EXECUTE PROCEDURE PRC_ACC_GET_TREE_LEVEL_R3(:I_SERIES_DOC, :z_NODE_ID)
RETURNING_VALUES :Z_LEVEL_NID, :Z_LEVEL_LEVEL, :Z_LEVEL_NAME, :Z_ERROR;
INSERT INTO ACC$PRED$TRE$C$TEMP (TREE_ID,NAME,TREE_CODE,TREE_KIND_LEVEL,TREE_KIND_NAME,PARENT,BED_SUM,BES_SUM)
VALUES (:I_L2I_START_NODE,:Z_NODE_NAME,:Z_NODE_CODE,:Z_LEVEL_LEVEL ,:Z_LEVEL_NAME, :I_PARENT_ID,0,0) RETURNING NID INTO :z_CALCID ;

--CHK ACC$TREE$BASE NODE.
z_NODE_ID = NULL;
Z_PARENTREE_ABSSUM = 0;
FOR
SELECT T.NID FROM ACC$TREE$BASE$R3 T
WHERE ((T.PARENT = :I_L2I_START_NODE) AND (T.SERIES = :I_SERIES_DOC))
ORDER BY T.NID
INTO :z_NODE_ID DO BEGIN
EXECUTE PROCEDURE PRC_ACC_INT_TREECALC_R3_DDF(:I_SERIES_DOC,:z_NODE_ID,:z_CALCID,:I_WITH_PREDOC,
:I_START_DOC_ID, :I_END_DOC_ID, :I_START_DATE,:I_END_DATE)
RETURNING_VALUES :z_BED,z_BES;
IF (:z_BED IS NULL) THEN z_BED = 0;
IF (:z_BES IS NULL) THEN z_BES = 0;

RET_SUMOFBED = RET_SUMOFBED + :z_BED;
RET_SUMOFBES = RET_SUMOFBES + :z_BES;
END

--CHK ACC$PREDOC
FOR SELECT P.NID, D.SERIES SERIES,P.DOC_LINK, P.BED, P.BES, P.ROW,P.NAME
FROM ACC$PREDOC P
LEFT JOIN ACC$DOC D ON (D.NID = P.DOC_LINK)
WHERE (P.TREE_BASE_R3 = :I_L2I_START_NODE) AND (D.SERIES = :I_SERIES_DOC) AND
(:I_END_DATE >= (CAST(D.DOC_DATE AS DATE))) AND (:I_START_DATE <= (CAST(D.DOC_DATE AS DATE))) AND
(:Z_END_DOC_NUM >= D.DOC_NUM) AND (:Z_START_DOC_NUM <= D.DOC_NUM)
INTO :Z_PD_NID,:Z_PD_SERIES, :Z_PD_DOC_LINK,:Z_PD_BED, :Z_PD_BES,:Z_PD_ROW,Z_PD_NAME DO BEGIN
IF (:Z_PD_BED IS NULL) THEN Z_PD_BED = 0;
IF (:Z_PD_BES IS NULL) THEN Z_PD_BES = 0;
EXECUTE PROCEDURE PRC_ACC_GET_TREE_LEVEL_R3(:I_SERIES_DOC, :I_L2I_START_NODE)
RETURNING_VALUES :Z_LEVEL_NID, :Z_LEVEL_LEVEL, :Z_LEVEL_NAME, :Z_ERROR;
IF (I_WITH_PREDOC <> 0) THEN BEGIN
INSERT INTO ACC$PRED$TRE$C$TEMP (PREDOC_ID, TREE_ID,TREE_KIND_LEVEL,TREE_KIND_NAME,PARENT, BED_SUM,BES_SUM,DESCRIPTION)
VALUES (:Z_PD_NID,:I_L2I_START_NODE,:Z_LEVEL_LEVEL , :Z_LEVEL_NAME, :z_CALCID,:Z_PD_BED,:Z_PD_BES,:Z_PD_NAME);
END
RET_SUMOFBED = :Z_PD_BED + RET_SUMOFBED;
RET_SUMOFBES = :Z_PD_BES + RET_SUMOFBES;
END
--UPDATE OF CURRENT RECORD.
UPDATE ACC$PRED$TRE$C$TEMP
SET
BED_SUM = :RET_SUMOFBED,
BES_SUM = :RET_SUMOFBES
WHERE (NID = :z_CALCID);
-- END
END
SUSPEND;
END


create or alter procedure PRC_ACC_GET_TREE_LEVEL_R3 (
I_SERIES DM_ID,
I_TREE_NID DM_ID)
returns (
RET_LEVEL_NID DM_ID,
RET_LEVEL_LEVEL DM_ID,
RET_LEVEL_NAME varchar(15),
RET_ERROR DM_COMMENT_110)
AS
DECLARE VARIABLE z_NODE_ID INTEGER;
DECLARE VARIABLE z_PARENT_ID INTEGER;
DECLARE VARIABLE z_LEVEL_COUNT INTEGER;
BEGIN
-- Init
RET_LEVEL_NID = NULL;
RET_LEVEL_NAME = NULL;
RET_LEVEL_LEVEL = NULL;
IF (I_SERIES IS NULL) THEN BEGIN
EXECUTE PROCEDURE FUN_ACC_GET_CURRENT_SERIES RETURNING_VALUES :I_SERIES, :RET_ERROR;
IF (RET_ERROR IS NOT NULL) THEN BEGIN
SUSPEND;
EXIT;
END
END
--CHK START NODE IS VALID
z_NODE_ID = NULL;
SELECT COUNT(*) FROM ACC$TREE$BASE$R3 T WHERE ((T.NID = :I_TREE_NID) AND (T.SERIES = :I_SERIES)) INTO :z_NODE_ID;
IF ((z_NODE_ID IS NOT NULL) AND (z_NODE_ID <1)) THEN BEGIN
SUSPEND;
EXIT;
END
z_LEVEL_COUNT = 0;
SELECT PARENT FROM ACC$TREE$BASE$R3 T WHERE ((NID = :I_TREE_NID) AND (T.SERIES = :I_SERIES)) INTO :z_PARENT_ID;
WHILE (:z_PARENT_ID IS NOT NULL ) DO BEGIN
z_LEVEL_COUNT = z_LEVEL_COUNT + 1;
SELECT PARENT FROM ACC$TREE$BASE$R3 T WHERE ((NID = :z_PARENT_ID) AND (T.SERIES = :I_SERIES)) INTO :z_PARENT_ID;
if (z_LEVEL_COUNT > 20) then
EXCEPTION EXP_PRC_ACC_GET_TREE_LEVEL_20L;
END
SELECT TL.NID, TL.LNUMBER, TL.NAME FROM ACC$TREE$BASE$LEVEL TL
WHERE TL.LNUMBER = :z_LEVEL_COUNT
INTO :RET_LEVEL_NID, :RET_LEVEL_LEVEL , :RET_LEVEL_NAME;
SUSPEND;
END


CREATE GLOBAL TEMPORARY TABLE ACC$PRED$TRE$C$TEMP (
NID DM_ID NOT NULL /* DM_ID = INTEGER */,
PARENT DM_ID /* DM_ID = INTEGER */,
PREDOC_ID DM_ID /* DM_ID = INTEGER */,
TREE_ID DM_ID /* DM_ID = INTEGER */,
BED_SUM DM_MONEY_BIG /* DM_MONEY_BIG = BIGINT */,
BES_SUM DM_MONEY_BIG /* DM_MONEY_BIG = BIGINT */,
TREE_KIND_LEVEL DM_ID /* DM_ID = INTEGER */,
TREE_KIND_NAME DM_NAME /* DM_NAME = VARCHAR(15) */,
NAME DM_NAME_L2 /* DM_NAME_L2 = VARCHAR(50) */,
DESCRIPTION DM_NAME_L2 /* DM_NAME_L2 = VARCHAR(50) */,
ITEM_VALUE DM_MONEY_BIG /* DM_MONEY_BIG = BIGINT */,
TREE_CODE DM_MONEY_BIG /* DM_MONEY_BIG = BIGINT */,
GROUP_NUM DM_ID /* DM_ID = INTEGER */,
GROUP_SIGN DM_ID /* DM_ID = INTEGER */,
TREE_LEVEL DM_ID /* DM_ID = INTEGER */,
ARTICLE DM_ID /* DM_ID = INTEGER */,
TREE_PARENT DM_ID /* DM_ID = INTEGER */,
GROUP_ID DM_ID /* DM_ID = INTEGER */,
PDATE DM_PDATE /* DM_PDATE = TIMESTAMP */
) ON COMMIT DELETE ROWS;