|
SELECT A.BUSINESS_UNIT ,A.COMMON_ID ,B.PAYMENT_ID_NBR ,K.INSTITUTION_SET ,A.ITEM_TYPE ,A.ITEM_TERM ,A.CLASS_NBR ,B.XREF_AMT ,F.DESCR ,D.DESCR ,B.SF_REVERSAL_IND FROM PS_ITEM_SF A ,PS_ITEM_XREF B ,PS_TERM_VAL_TBL D ,PS_BUS_UNIT_TBL_SF E ,PS_ITEM_TYPE_TBL F ,PS_ISET_BU_DTL K WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.COMMON_ID = B.COMMON_ID AND A.SA_ID_TYPE = B.SA_ID_TYPE AND A.ITEM_NBR = B.ITEM_NBR_CHARGE AND A.ITEM_TERM = D.STRM AND A.BUSINESS_UNIT = E.BUSINESS_UNIT AND A.BUSINESS_UNIT = K.BUSINESS_UNIT AND A.ITEM_TYPE = F.ITEM_TYPE AND F.EFFDT=( SELECT MAX(G.EFFDT) FROM PS_ITEM_TYPE_TBL G WHERE F.SETID=G.SETID AND F.ITEM_TYPE=G.ITEM_TYPE AND F.EFFDT <= %CurrentDateIn) AND F.SETID=( SELECT H.SETID FROM PS_SET_CNTRL_REC H WHERE H.SETCNTRLVALUE=E.INSTITUTION AND H.RECNAME='ITEM_TYPE_TBL') AND B.EFFDT = ( SELECT MAX(J.EFFDT) FROM PS_ITEM_XREF J WHERE J.BUSINESS_UNIT = B.BUSINESS_UNIT AND J.COMMON_ID = B.COMMON_ID AND J.SA_ID_TYPE = B.SA_ID_TYPE AND J.ITEM_NBR_CHARGE = B.ITEM_NBR_CHARGE AND J.ITEM_NBR_PAYMENT = B.ITEM_NBR_PAYMENT) AND B.EFF_STATUS = 'A' AND A.SA_ID_TYPE = 'P' AND B.XREF_AMT > 0
|