|
SELECT DISTINCT D.BUSINESS_UNIT ,D.CONTRACT_NUM ,E.TREE_NODE ,F.ITEM_TYPE ,L.LANGUAGE_CD ,L.DESCR FROM PSTREEDEFN A ,PSTREENODE B ,PSTREELEAF C ,PS_TP_CONTRACT D ,PS_TP_CHARGES E ,PS_ITEM_TYPE_TBL F ,PS_SET_CNTRL_REC G , PS_ITEM_TYPE_LNG L ,PS_BUS_UNIT_TBL_SF S WHERE D.BUSINESS_UNIT = E.BUSINESS_UNIT AND D.BUSINESS_UNIT = S.BUSINESS_UNIT AND G.SETCNTRLVALUE = S.INSTITUTION AND G.RECNAME = 'ITEM_TYPE_TBL' AND F.SETID = G.SETID AND (F.ITEM_TYPE_CD = 'C' OR F.ITEM_TYPE_CD = 'A') AND F.EFFDT = ( SELECT MAX(F1.effdt) FROM ps_item_type_tbl F1 WHERE F1.setid = F.setid AND F1.item_type = F.item_type AND F1.effdt <= %CurrentDateIn) AND F.SETID = L.SETID AND F.ITEM_TYPE = L.ITEM_TYPE AND F.EFFDT = L.EFFDT AND D.CONTRACT_NUM=E.CONTRACT_NUM AND A.SETID=B.SETID AND B.SETID=C.SETID AND A.SETID=( SELECT A1.SETID FROM PS_SET_CNTRL_TREE A1 WHERE A1.SETCNTRLVALUE=S.INSTITUTION AND A1.TREE_NAME=A.TREE_NAME) AND F.ITEM_TYPE >= C.RANGE_FROM AND F.ITEM_TYPE <= C.RANGE_TO AND E.TREE_NODE=B.TREE_NODE AND A.EFFDT=( SELECT MAX(A1.EFFDT) FROM PSTREEDEFN A1 WHERE A1.SETID = A.SETID AND A1.TREE_NAME = A.TREE_NAME AND A1.EFFDT <= %CurrentDateIn ) AND A.VALID_TREE='Y' AND A.TREE_NAME=B.TREE_NAME AND A.TREE_NAME=C.TREE_NAME AND D.TREE_NAME=C.TREE_NAME AND A.EFFDT=B.EFFDT AND A.EFFDT=C.EFFDT AND C.TREE_NODE_NUM >= B.TREE_NODE_NUM AND C.TREE_NODE_NUM <= B.TREE_NODE_NUM_END AND F.EFF_STATUS = 'A'
|
BUSINESS_UNIT