SELECT   sfa.ID c01, p.totalcontract c02, p.expectedsigndate c03,
         '00580000001nB1dAAE' c04, p.weeklycomment c05,
         NVL (p.protocolname, 'Unknown') c06,
         (CASE
             WHEN p.dealowner IS NOT NULL
                THEN NVL (u_owner.ID, '00580000001nB1dAAE')
          END
         ) c07,
         pat.recordid c08,
         DECODE (p.salesstatus,
                 'Awarded', 'Award',
                 'Cancelled', 'Closed Lost',
                 'Contract On Hold', 'On Hold',
                 'Contract Sent', 'Contract Sent',
                 'Contract Signed', 'Closed Signed',
                 'Contract in Prep', 'Contract Request',
                 'Lead', 'Prospect',
                 'Lost', 'Closed Lost',
                 'Not Applicable', 'Prospect',
                 'Proposal Sent', 'Proposal Sent',
                 'Proposal in Prep', 'Proposal Request',
                 'Prospect'
                ) c09,
         p.contractedsites c10, p.contractedsubjects c11,
         (CASE
             WHEN p.am IS NOT NULL
                THEN NVL (u_am.ID, '00580000001nB1dAAE')
          END
         ) c12,
         p.bookingdate c13, pat.sf_agreement c14,
         NVL (sfc.ID, 'a0E80000002LczXEAS') c15,
         (CASE
             WHEN sfc.ID IS NULL
                THEN p.compound
          END) c16, p.contractedfsfv c17, p.contractedlslv c18,
         p.projectduration c19,
         (CASE
             WHEN p.cpa IS NOT NULL
                THEN NVL (u_cpa.ID, '00580000001nB1dAAE')
          END
         ) c20,
         (CASE
             WHEN p.primarybd IS NOT NULL
                THEN NVL (u_bd.ID, '00580000001nB1dAAE')
          END
         ) c21,
         (CASE
             WHEN p.pm IS NOT NULL
                THEN NVL (u_pm.ID, '00580000001nB1dAAE')
          END
         ) c22,
         sfp.phase_sf c23, sfp.subphase_sf c24,
         (CASE
             WHEN p.therapeuticgroup IS NOT NULL
                THEN NVL (sft.tg_sf, 'Unknown')
          END
         ) c25,
         (CASE
             WHEN p.salesregion IS NOT NULL
                THEN DECODE
                       (p.salesregion,
                        'Cardiac Safety - Americas', 'US',
                        'Cardiac Safety - Americas|Cardiac Safety - Covance', 'US',
                        'Cardiac Safety - International', 'UK',
                        'Cardiac Safety - International|Outsourcing Partnerships', 'UK'
                       )
          END
         ) c26,
         (CASE
             WHEN p.indication IS NOT NULL
                THEN (SELECT ID
                        FROM sf_indication
                       WHERE NAME = NVL (sfi.sf_indication, 'Unknown'))
          END
         ) c27,
         p.contract_id c28, p.familyid c29, p.contractid c30,
         p.transactioncost c31, p.equipmentcost c32, p.equipmentpurchase c33,
         p.supplycost c34, p.assurancecost c35, p.sitemanagementcost c36,
         p.communitycost c37, p.consultingcost c38, p.additionalcost c39,
         p.passthroughcost c40, p.totalcontract c41,
         DECODE (p.salesregion,
                 'Cardiac Safety - Americas|Cardiac Safety - Covance', 'Partner - Covance',
                 'Cardiac Safety - Covance', 'Partner - Covance',
                 'Outsourcing Partnerships', 'Partner'
                ) c42,                                  --p.contractgroup c43,
         DECODE (p.cstatus, -1, 'Active', -3, 'In Process', 0,
                 'Inactive') c44,
         DECODE (p.covancebusinessunit,
                 'CCLS-EU', 'CLS-EU',
                 'CCLS-US', 'CLS-NA',
                 'CDS-US', 'CDS-NA',
                 'CRU-NA', 'GCP-NA',
                 'GCP-US', 'GCP-NA',
                 'LSDS', 'LSDS'
                ) c45,
         DECODE (p.covanceleadorigin,
                 'Lead Integrated - CO', 'Lead - Integrated CO',
                 p.covanceleadorigin
                ) c46,
         p.originalexpirationdate c47, p.externalcontract p48,
         (CASE
             WHEN p.bdmanager IS NOT NULL
                THEN NVL (u_cpa.ID, '00580000001nB1dAAE')
          END
         ) c49,
         (CASE
             WHEN p.backuppm IS NOT NULL
                THEN NVL (u_cpa.ID, '00580000001nB1dAAE')
          END
         ) c50,
         (CASE
             WHEN p.secondbd IS NOT NULL
                THEN NVL (u_cpa.ID, '00580000001nB1dAAE')
          END
         ) c51,
         p.noticeperiod c52, p.dateclientsigned c53, p.effectivedate c54,
         p.advancedpaymentpct c55, p.advancepayment c56,
         p.ps_ecgpersubject c57, p.ps_cpu c58, p.bd_cro c59,
         DECODE (p.contractlocation,
                 'Iron Mountain - USA', 'Iron Mountain - US',
                 p.contractlocation
                ) c60,
         p.addldetail c61,
         DECODE (p.dealcategory,
                 'Cardiac Safety', 'TRUE',
                 'Cardiac Safety - Pre-pipeline', 'TRUE',
                 'FALSE'
                ) c62,
         DECODE (p.dealcategory, 'ePRO', 'TRUE', 'FALSE') c63,
         (CASE
             WHEN p.clientsignatory IS NOT NULL
                THEN (SELECT (ID)
                        FROM sf_contacts
                       WHERE accountid = sfa.ID
                         AND lastname = p.clientsignatory)
          END
         ) c64,
         (CASE
             WHEN p.ertsignatory IS NOT NULL
                THEN NVL ((SELECT ID
                             FROM sf_users
                            WHERE NAME = p.ertsignatory),
                          '00580000001nB1dAAE')
          END
         ) c65,
         'TRUE' c98, (SELECT sf_id
                        FROM sf_contracts
                       WHERE procuri_id = p.agreement_link) c99
    FROM procuri p,
         procuri_agreement_type pat,
         sf_accounts sfa,
         sf_users u_owner,
         sf_users u_am,
         sf_users u_pm,
         sf_users u_cpa,
         sf_users u_bd,
         sf_compound sfc,
         sf_phase_map sfp,
         sf_tg_map sft,
         sf_indication_map sfi,
         sf_users u_bdm,
         sf_users u_pm2,
         sf_users u_bd2
   WHERE p.agreementtype = pat.agt_name
     AND p.internalcompanyid = sfa.atype
     AND p.dealowner = u_owner.match_name(+)
     AND p.am = u_am.match_name(+)
     AND p.pm = u_pm.match_name(+)
     AND p.cpa = u_cpa.match_name(+)
     AND p.primarybd = u_bd.match_name(+)
     AND p.compound = sfc.NAME(+)
     AND p.studyphase = sfp.phase_procuri(+)
     AND p.therapeuticgroup = sft.tg_procuri(+)
     AND p.indication = sfi.procuri_indication(+)
     AND p.bdmanager = u_bdm.match_name(+)
     AND p.backuppm = u_pm2.match_name(+)
     AND p.secondbd = u_bd2.match_name(+)
     AND pat.agt_type = 2
     AND EXISTS (SELECT sf_id
                   FROM sf_contracts
                  WHERE procuri_id = p.agreement_link)
ORDER BY c30

