|
SELECT H.EMPLID ,A.BUSINESS_UNIT ,A.SSF_PUR_ITEM_CODE ,A.DESCR ,A.DESCRLONG ,B.SSF_PUR_CATEGORY ,D.DESCR ,B.ACCOUNT_TYPE_SF ,B.ITEM_TYPE ,B.FLAT_AMT ,E.INSTITUTION_SET ,G.INSTITUTION FROM PS_SSF_PUR_ITEM_CD A ,PS_SSF_PURCH_ITEM B ,PS_SSF_PUR_ITEM_AT C ,PS_SSF_PUR_CATGRY D ,PS_STDNT_GRPS_HIST H ,PS_ISET_BU_DTL E ,PS_BUS_UNIT_TBL_SF G WHERE B.BUSINESS_UNIT = A.BUSINESS_UNIT AND B.SSF_PUR_ITEM_CODE = A.SSF_PUR_ITEM_CODE AND B.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PS_SSF_PURCH_ITEM A1 WHERE A1.BUSINESS_UNIT = A.BUSINESS_UNIT AND A1.SSF_PUR_ITEM_CODE = A.SSF_PUR_ITEM_CODE AND A1.EFFDT <= %CurrentDateIn) AND B.EFF_STATUS = 'A' AND B.SSF_EXP_DATE > %CurrentDateIn AND C.BUSINESS_UNIT = A.BUSINESS_UNIT AND C.SSF_PUR_ITEM_CODE = A.SSF_PUR_ITEM_CODE AND C.EFFDT = B.EFFDT AND A.SSF_PUR_ITM_OPTION = 'G' AND H.STDNT_GROUP = C.STDNT_GROUP AND H.INSTITUTION = G.INSTITUTION AND H.EFFDT = ( SELECT MAX(H1.EFFDT) FROM PS_STDNT_GRPS_HIST H1 WHERE H1.EMPLID = H.EMPLID AND H1.INSTITUTION = H.INSTITUTION AND H1.STDNT_GROUP = H.STDNT_GROUP AND H1.EFFDT <= %CurrentDateIn) AND H.EFF_STATUS = 'A' AND D.BUSINESS_UNIT = B.BUSINESS_UNIT AND D.SSF_PUR_CATEGORY = B.SSF_PUR_CATEGORY AND E.BUSINESS_UNIT = A.BUSINESS_UNIT AND G.BUSINESS_UNIT = A.BUSINESS_UNIT
|