|
SELECT H.VENDOR_SETID , H.VENDOR_ID , ' ' , H.BUSINESS_UNIT , S.CURRENCY_CD , H.PO_DT , SUM(S.MERCHANDISE_AMT) , ' ' FROM PS_PO_LINE_SHIP S , PS_PO_LINE L , PS_PO_HDR H WHERE L.BUSINESS_UNIT = H.BUSINESS_UNIT AND L.PO_ID = H.PO_ID AND L.BUSINESS_UNIT = S.BUSINESS_UNIT AND L.PO_ID = S.PO_ID AND L.LINE_NBR = S.LINE_NBR AND H.PO_STATUS = 'D' AND L.CNTRCT_ID = ' ' AND NOT EXISTS ( SELECT 'Y' FROM PS_CNTRCT_HDR CH WHERE CH.SETID = L.CNTRCT_SETID AND CH.VERSION_STATUS = 'C' AND CH.CNTRCT_STATUS = 'A' AND CH.VENDOR_ID = H.VENDOR_ID AND CH.CNTRCT_BEGIN_DT <= H.PO_DT AND (CH.CNTRCT_EXPIRE_DT IS NULL OR CH.CNTRCT_EXPIRE_DT >= H.PO_DT) AND (CH.ALLOW_OPEN_ITEM = 'Y' OR EXISTS ( SELECT 'Y' FROM PS_CNTRCT_LINE CL WHERE CL.SETID = CH.SETID AND CL.CNTRCT_ID = CH.CNTRCT_ID AND CL.VERSION_NBR = CH.VERSION_NBR AND CL.ITM_SETID = L.ITM_SETID AND CL.INV_ITEM_ID = L.INV_ITEM_ID) OR EXISTS ( SELECT 'Y' FROM PS_CNTRCT_CATEGORY CC WHERE CC.SETID = CH.SETID AND CC.CNTRCT_ID = CH.CNTRCT_ID AND CC.VERSION_NBR = CH.VERSION_NBR AND CC.CATEGORY_SETID = L.ITM_SETID AND CC.CATEGORY_ID = L.CATEGORY_ID))) GROUP BY H.VENDOR_SETID, H.VENDOR_ID, H.BUSINESS_UNIT, S.CURRENCY_CD, H.PO_DT
|