|
SELECT P.SETID , KP.CHARTFIELD , KP.SETID1 , KP.LEDGER_GROUP , P.TREE_LEVEL_NUM , P.TREE_NODE , LC.LEDGER_GROUP , KC.SUBTYPE , C.TREE_LEVEL_NUM , C.TREE_NODE FROM PS_KK_KEY_CF KC , PS_KK_TREE_VW C , PS_KK_TREE_VW P , PS_KK_KEY_CF KP , PS_KK_BUDGET_TYPE LP , PS_KK_BUDGET_TYPE LC WHERE LC.PARENT_BUDGET <> ' ' AND LC.LEDGER_GROUP=KC.LEDGER_GROUP AND LC.SETID1=KC.SETID1 AND LC.EFFDT=KC.EFFDT AND LC.EFFDT=( SELECT MAX(M.EFFDT) FROM PS_KK_BUDGET_TYPE M WHERE M.SETID1 = LC.SETID1 AND M.LEDGER_GROUP = LC.LEDGER_GROUP AND M.EFFDT <= %CurrentDateIn) AND LC.EFF_STATUS='A' AND LP.LEDGER_GROUP=KP.LEDGER_GROUP AND LP.EFFDT=KP.EFFDT AND LP.EFF_STATUS='A' AND LP.SETID1=KP.SETID1 AND LC.PARENT_BUDGET=LP.LEDGER_GROUP AND LP.SETID1=LC.SETID1 AND LP.EFFDT=( SELECT MAX(F.EFFDT) FROM PS_KK_BUDGET_TYPE F WHERE F.SETID1 = LP.SETID1 AND F.LEDGER_GROUP = LP.LEDGER_GROUP AND F.EFFDT <= %CurrentDateIn) AND KC.CHARTFIELD=KP.CHARTFIELD AND P.TREE_NAME=KC.TREE_NAME AND P.EFFDT=( SELECT MAX(Q.EFFDT) FROM PS_KK_TREE_VW Q WHERE Q.SETID = P.SETID AND Q.TREE_NAME = P.TREE_NAME AND Q.EFFDT <= %CurrentDateIn) AND C.SETID=P.SETID AND C.TREE_NAME=P.TREE_NAME AND C.EFFDT=P.EFFDT AND C.TREE_LEVEL_NUM <=( SELECT DISTINCT TREE_LEVEL_NUM FROM PS_KK_TREE_VW XC WHERE C.SETID=XC.SETID AND C.TREE_NAME=XC.TREE_NAME AND C.EFFDT=XC.EFFDT AND XC.TREE_LEVEL=KC.TREE_LEVEL AND XC.TREE_NAME=KC.TREE_NAME) AND C.TREE_NODE_NUM >= P.TREE_NODE_NUM AND C.TREE_NODE_NUM <=P.TREE_NODE_NUM_END AND ((C.TREE_LEVEL_NUM>P.TREE_LEVEL_NUM AND P.TREE_LEVEL_NUM =( SELECT DISTINCT TREE_LEVEL_NUM FROM PS_KK_TREE_VW XP WHERE P.SETID=XP.SETID AND P.TREE_NAME=XP.TREE_NAME AND P.EFFDT=XP.EFFDT AND XP.TREE_LEVEL=KP.TREE_LEVEL AND XP.TREE_NAME=KP.TREE_NAME) ) OR (C.TREE_LEVEL_NUM=P.TREE_LEVEL_NUM AND P.TREE_LEVEL_NUM <=( SELECT DISTINCT TREE_LEVEL_NUM FROM PS_KK_TREE_VW XP WHERE P.SETID=XP.SETID AND P.TREE_NAME=XP.TREE_NAME AND P.EFFDT=XP.EFFDT AND XP.TREE_LEVEL=KP.TREE_LEVEL AND XP.TREE_NAME=KP.TREE_NAME)))
|