select distinct
                dma.memo_type,
                dma.memo_alert_id,
                dup.Project_Name,
                dup.Project_Description,
                dup.Activity_Name,
                dup.Activity_Description,
                dup.Report_or_Study_No,
                dup.Start_Date,
                dup.End_Date,
                dup.Reg_Due_Date,
                dup.Activity_status,
                dup.Activity_OBS,
                dup.Memo_sent,
                dup.ver,
                ADM.PKG_MEMO10.F_GET_FULL_NAME(e2.resp1) as Functional_mgr,
                ADM.PKG_MEMO10.F_GET_FULL_NAME(dup.Responsible_person) as Responsible_person,
                ADM.PKG_MEMO10.F_GET_FULL_NAME(rq.rn) as Resource_name,
                dup.Tst_Subst_code,
                nvl(doc.DREQ_OUT2,'NO SIF_LINK'),
                nvl(ADM.PKG_MEMO10.F_COA_URL_NEW(dup.Tst_Subst_code, coa.expiration_date),'NO COA_LINK')
   BULK COLLECT INTO    memo_types, memo_alert_ids, Project_Names, Project_Descriptions,
                        Activity_Names, Activity_Descriptions, Report_or_Study_Nos, Start_Dates,
                        End_Dates, Reg_Due_Dates, Activity_statuss, Activity_OBSs, Memo_sents,
                        vers, resp1s, Responsible_persons, rns, Tst_Subst_codes,
                        sif_links, coa_links
         from   ADM.MEMO_MV dup,
                av_element e1,
                av_element e2,
                av_resreq rq,
                adm.memo_alerts dma,
                adm.dup_docbase_requests doc,
                ADM.DUP_TESTSUB_COAEXP_NRM coa
        where       dup.Project_Name    = dma.project
            and     dup.Activity_Name   = dma.activity
            and     dup.Project_Name    = rq.proj
            and     dup.Activity_Name   = rq.act
            and     rq.ver in ( 0,97 )
            and     e1.resp1<>e2.resp1
            and     (e2.category = 'GT OBS' AND e2.element = dup.Activity_OBS)
            and     nvl(dma.sent_flag,'N') = 'N'
            and     doc.DREQ_IN7(+) = dup.Project_Name
            and     doc.DREQ_IN8(+) = dup.Activity_Name
            and     dma.memo_type > 10
            and     dup.memo_sent > 10
            and     ADM.PKG_MEMO10.F_NORMALIZE_DASH(dup.Tst_Subst_code) = test_substance_code(+);