SELECT B.BUSINESS_UNIT , B.REQ_ID , B.COMMENT_ID , B.RANDOM_CMMT_NBR , A.SCM_ATTACH_ID , A.ATT_VERSION , A.ATTACHUSERFILE , A.ATTACHSYSFILENAME FROM PS_REQ_COMMENTS B FULL OUTER JOIN PS_PV_REQ_ATTCH A ON A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.REQ_ID = B.REQ_ID AND B.COMMENT_ID = A.COMMENT_ID AND A.RANDOM_CMMT_NBR = B.RANDOM_CMMT_NBR , PS_REQ_HDR H WHERE H.BUSINESS_UNIT = B.BUSINESS_UNIT AND H.REQ_ID = B.REQ_ID AND B.COMMENT_TYPE = 'HDR' AND B.APPR_JUSTIFY_FLG = 'Y' AND B.STATUS = 'A' AND H.ORIG_TRAN IN ('EPO', 'SPO', 'REQ') AND A.SCM_ATTACH_ID IS NOT NULL UNION ALL SELECT B.BUSINESS_UNIT , B.REQ_ID , B.COMMENT_ID , B.RANDOM_CMMT_NBR , A.SCM_ATTACH_ID , A.ATT_VERSION , A.ATTACHUSERFILE , A.ATTACHSYSFILENAME FROM PS_REQ_COMMENTS B FULL OUTER JOIN PS_PV_ATTACHMENTS A ON A.SCM_ATTACH_ID = B.SCM_ATTACH_ID AND A.ATT_VERSION = B.ATT_VERSION , PS_REQ_HDR H WHERE H.BUSINESS_UNIT = B.BUSINESS_UNIT AND H.REQ_ID = B.REQ_ID AND B.COMMENT_TYPE = 'HDR' AND B.APPR_JUSTIFY_FLG = 'Y' AND B.STATUS = 'A' AND H.ORIG_TRAN IN ('REQ', 'EPO', 'SPO')
|