ALTER PROCEDURE [SYSADM].[Creation_Opportunites_ProspectsLagoon]

AS
BEGIN

    DECLARE @P_NRID DECIMAL(15,0)
    DECLARE @PE0_NRID DECIMAL(15,0)
    DECLARE @P_RID VARCHAR(32)
    DECLARE @P_RMOD VARCHAR(32)
    DECLARE @P_DMOD DATETIME
    DECLARE @REF VARCHAR(50)



    DECLARE cur_Insertion CURSOR FOR
    SELECT distinct pe0.nrid
    FROM sysadm.do0 do0
    LEFT OUTER JOIN sysadm.pe0 pe0 ON do0.pe0_nrid = pe0.nrid
    INNER JOIN sysadm.cal0 cal0 ON cal0.pe0_nrid = pe0.nrid
    INNER JOIN sysadm.sp0 sp0 ON sp0.pe0_nrid = pe0.nrid
    LEFT OUTER JOIN sysadm.coli0 coli0 ON coli0.pe0_nrid = pe0.nrid
    WHERE (cal0.xCommentaire LIKE '%lagoon%' OR cal0.xSource LIKE 'SD%' OR cal0.xSource LIKE 'US-SD%')
    AND pe0.template IS NULL
    AND NOT (do0.xMarque = 'Lagoon')
    AND coli0.nrid IS NULL
    AND sp0.xTypePersonne not in (2, 3)
    --and sp0.e_mail = 'q.boiveau@gmail.com'



    BEGIN
        OPEN cur_Insertion
        FETCH cur_Insertion INTO @PE0_NRID

        WHILE @@FETCH_STATUS <> -1
            BEGIN

                SELECT @REF = MAX(REF) FROM sysadm.do0
                SET @REF = @REF + 1

                EXECUTE     SYSADM.fctRID 'do0', @nNRID = @P_NRID output, @strRID = @P_RID output, @strRMOD = @P_RMOD output, @dtDMOD = @P_DMOD  output

                INSERT INTO SYSADM.do0 (nrid, rid, dmod, rmod, pe0_nrid, xMarque, ref)
                    VALUES (@P_NRID, @P_RID, @P_DMOD, @P_RMOD, @PE0_NRID, 'Lagoon', @REF)

                FETCH cur_Insertion INTO @P_NRID

            END

        CLOSE cur_Insertion
        DEALLOCATE cur_Insertion
    END

END