[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index]
[Date Index]
[Thread Index]
[SQR-USERS Info]
[SQRUG Home Page]
RE: [sqr-users] SQR using trees
Yes you can use the logic there to pull only the departments from the CBNA_F
node.
First you need to find the Tree Node Number and Tree Node Num end for the
CBNA_F node
{DEPTTREE} = FUNCTIONAL_ROLLUP
Begin-Select
BN.TREE_NODE_NUM
BN.TREE_NODE_NUM_END
LET #START_TREE_NODE_NUM = &BN.TREE_NODE_NUM
LET #END_TREE_NODE_NUM = &BN.TREE_NODE_NUM_END
FROM PSTREEDEFN AN, PSTREENODE BN
WHERE AN.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PSTREEDEFN A_ED
WHERE AN.SETID = A_ED.SETID
AND AN.TREE_NAME = A_ED.TREE_NAME
AND A_ED.EFFDT <= SYSDATE)
AND AN.SETID = BN.SETID
AND AN.TREE_NAME = BN.TREE_NAME
AND BN.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM PSTREENODE B_ED
WHERE BN.SETID = B_ED.SETID
AND BN.TREE_NAME = B_ED.TREE_NAME
AND B_ED.EFFDT <= AN.EFFDT)
AND AN.TREE_NAME = {DEPTTREE}
AND AN.SETID = 'XXXX'
AND BN.TREE_NODE = 'CBNA_F'
End-Select
Then uses these numbers you can get the Departments that are in the tree
that roll up
Begin-Select
D.DEPTID
#DEBUGD Show ' DEBUG: &D.DEPTID --|' &D.DEPTID '|--'
LET $DEPTID_DATA = &D.DEPTID
do GET-CHARTFIELDS
If #Actuals_total <> 0 or #Encumbrance_total <> 0 or
#PreEncumbrance_total <> 0 or #REV_total <> 0 or #Budget_total <> 0
Graphic (0,1,156) Horz-Line 5
DO GET-DEPT-DESCR
DO PRINT-DEPT_SUBTOTAL
END-IF
FROM PSTREEDEFN A, PSTREENODE B, PSTREELEAF C, PS_DEPT_TBL D
WHERE A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PSTREEDEFN A_ED
WHERE A.SETID = A_ED.SETID
AND A.TREE_NAME = A_ED.TREE_NAME
AND A_ED.EFFDT <= SYSDATE)
AND A.SETID = B.SETID
AND A.TREE_NAME = B.TREE_NAME
AND B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM PSTREENODE B_ED
WHERE B.SETID = B_ED.SETID
AND B.TREE_NAME = B_ED.TREE_NAME
AND B_ED.EFFDT <= A.EFFDT)
AND B.SETID = C.SETID
AND B.TREE_NAME = C.TREE_NAME
AND B.TREE_BRANCH = C.TREE_BRANCH
AND B.TREE_NODE_NUM = C.TREE_NODE_NUM
AND C.EFFDT =
(SELECT MAX(C_ED.EFFDT) FROM PSTREELEAF C_ED
WHERE C.SETID = C_ED.SETID
AND C.TREE_NAME = C_ED.TREE_NAME
AND C_ED.EFFDT <= B.EFFDT)
AND A.TREE_NAME = {DEPTTREE}
AND A.SETID = 'XXXX'
AND C.SETID = D.SETID
AND D.EFFDT =
(SELECT MAX(D_ED.EFFDT) FROM PS_DEPT_TBL D_ED
WHERE D.SETID = D_ED.SETID
AND D.DEPTID = D_ED.DEPTID
AND D_ED.EFFDT <= C.EFFDT)
AND D.DEPTID >= C.RANGE_FROM
AND D.DEPTID <= C.RANGE_TO
AND B.TREE_NODE_NUM BETWEEN #START_TREE_NODE_NUM AND
#END_TREE_NODE_NUM
ORDER BY D.DEPTID
End-Select
Trees can be powerful tools to help getting data organized in an SQR
Robert Stojkovic
robert.stojkovic@io-consulting .com
visit our website www.io-consulting.com
Providing Rocking Innovative Collaboration Every Day
-----Original Message-----
From: sqr-users-bounces+robert.stojkovic=io-consulting.com@sqrug.org
[mailto:sqr-users-bounces+robert.stojkovic=io-consulting.com@sqrug.org] On
Behalf Of Knapp, Richard
Sent: Monday, June 20, 2005 12:46 PM
To: This list is for discussion about the SQR database reportinglanguage
fromHyperion Solutions.
Subject: [WARN] IOSPML RE: [sqr-users] SQR using trees
The short answer is no. At least that is what I discovered some years
ago when I attempted a similar SQR. Could be there are some more clever
folks around these days, who knows? If the tree structure is still
contained in the node and leaf tables, seems to me you are going to have
to use them.
Richard Knapp
Database Programmer/Analyst
Institutional Research and Planning
University of Missouri System
573-882-8856
knappr@umsystem.edu
-----Original Message-----
From: sqr-users-bounces+knappr=umsystem.edu@sqrug.org
[mailto:sqr-users-bounces+knappr=umsystem.edu@sqrug.org] On Behalf Of
Raghvendra Dharwada
Sent: Monday, June 20, 2005 2:35 PM
To: sqr-users@sqrug.org
Subject: [sqr-users] SQR using trees
Hi,
I am trying to develop a query (probably an SQR) that should list all
departments that roll up to 'CBNA_F' (Node) in the FUNCTIONAL_ROLLUP
tree. We need the department number, description, business line and
region
I am working on PS Financials8.8 and Oracle9i
There is an exisiting SQR that loads the data of FUNCTIONAL_ROLLUP
tree in addition to 2 more trees(I am unconcerened with the other 2
trees), into a table called ps_tree_dept_tbl by joining the following
tables: ps_dept_tbl, pstreenode , pstreeleaf ans ps_tree_node_tbl
The flow of the SQR is as follows:
Select all the depts from PS_DEPT_TBL DEPT where DEPT.eff_status =
'A' and DEPT.effdt = SELECT MAX(EFFDT) FROM PS_DEPT_TBL WHERE
DEPT.SETID = SETID AND DEPT.DEPTID = DEPTID
AND EFFDT <= SYSDATE)
For each department get the leaf.tree_node_num from pstreeleaf leaf
where where LEAF.setid = $dept_setid and LEAF.tree_name = $tree_name
and $deptid between LEAF.range_from and LEAF.range_to and LEAF.effdt =
(SELECT MAX(EFFDT) FROM PSTREELEAF
WHERE LEAF.SETID = SETID
AND LEAF.TREE_NAME = TREE_NAME
AND EFFDT <= SYSDATE)
For each leaf.tree_node_num get node.tree_node, node.tree_level_num
from pstreenode node where NODE.setid = $dept_setid
and NODE.tree_name = $tree_name !FUNCTIONAL_ROLLUP
and NODE.tree_node_num = leaf.tree_node_num !ofcourse a variable is
used here
and NODE.effdt = (SELECT MAX(EFFDT) FROM PSTREENODE
WHERE NODE.SETID = SETID
AND NODE.TREE_NAME = TREE_NAME
AND EFFDT <= SYSDATE)
For each node.tree_node_num get the descr from ps_tree_node_tbl from
PS_TREE_NODE_TBL DES
where DES.setid = $dept_setid
and DES.tree_node = $treenode ! node.tree_node_num
and DES.effdt = (SELECT MAX(EFFDT) FROM PS_TREE_NODE_TBL
WHERE DES.SETID = SETID
AND DES.TREE_NODE = TREE_NODE
AND EFFDT <= SYSDATE)
Now , my question is , is there any way that I can get only those
departments that will rollup only to the CBNA_F node in the
FUNCTIONAL_ROLLUP tree by either cloning tbis SQR or using this SQR as
a termplate? I tried some SQLs using pstreenode and pstreeleaf but .
I am unable to figure out how I can grab the required only as per the
requirement
Please let me know if anyone can suggest me regarding this issue.I
appreciate your help. Thanks in advance
Raghav
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users