USP_ADDOPPORTUNITIESPROCESS

CREATE procedure [dbo].[USP_ADDOPPORTUNITIESPROCESS]
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier,
    @NUMBERPROCESSED int output,
    @NUMBEREXCEPTIONS int output,
    @OUTPUTTABLENAME nvarchar(128)
)
as
begin
    set @NUMBERPROCESSED = 0;
    set @NUMBEREXCEPTIONS = 0;

    declare @PROCESSDATE date = getdate();
    declare @IDSETREGISTERID uniqueidentifier;
    declare @OPPORTUNITYTYPECODEID uniqueidentifier;
    declare @OPPORTUNITYSTATUSCODE tinyint;
    declare @EXPECTEDASKAMOUNT money;
    declare @ASKAMOUNT money;
    declare @TRANSACTIONCURRENCYID uniqueidentifier;
    declare @EXPECTEDASKDATE datetime;
    declare @ASKDATE datetime;
    declare @RESPONSEDATE datetime;
    declare @COMMENT nvarchar(255);
    declare @LIKELIHOODTYPECODEID uniqueidentifier;

    declare @SQL nvarchar(250);
    set @SQL = N'insert into dbo.' + @OUTPUTTABLENAME + ' (PROSPECTPLANID, OPPORTUNITYID, RESULTCODE) values (@PPID, @OID, @RC)';
    declare @PARAMDEF nvarchar(75);
    set @PARAMDEF = N'@PPID uniqueidentifier, @OID uniqueidentifier, @RC tinyint';

    ---------------------------------------------------------------------

    -- get parameters

    select
        @IDSETREGISTERID = IDSETREGISTERID,
        @OPPORTUNITYTYPECODEID = OPPORTUNITYTYPECODEID,
        @OPPORTUNITYSTATUSCODE = OPPORTUNITYSTATUSCODE,
        @EXPECTEDASKAMOUNT = EXPECTEDASKAMOUNT,
        @ASKAMOUNT = ASKAMOUNT,
        @TRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID,
        @EXPECTEDASKDATE = EXPECTEDASKDATE,
        @ASKDATE = ASKDATE,        
        @RESPONSEDATE = RESPONSEDATE,        
        @COMMENT = COMMENT,    
        @LIKELIHOODTYPECODEID = LIKELIHOODTYPECODEID    
    from dbo.ADDOPPORTUNITIESPROCESS
    where ID = @ID;

    declare @OPPORTUNITYDESIGNATIONSXML xml =
     (select DESIGNATIONID, AMOUNT, SEQUENCE, TRANSACTIONCURRENCYID, FUNDINGMETHODCODEID,
             CATEGORYCODEID, TYPECODEID, USECODEID
      from dbo.ADDOPPORTUNITIESPROCESSOPPORTUNITYDESIGNATION
      where ADDOPPORTUNITIESPROCESSID = @ID
      for xml raw('ITEM'),type,elements,root('DESIGNATION'),BINARY BASE64);

    ---------------------------------------------------------------------

    -- get prospect plans

    declare @PROSPECTPLANID uniqueidentifier;
    declare @BASECURRENCYID uniqueidentifier;
    declare @COMBOISVALID bit = 0;
    declare @INVALIDCURRENCYPAIRMSG nvarchar(60) = 'Invalid combination of base and transaction currencies';

    declare PROSPECTPLANCURSOR cursor local fast_forward for
    select ID
    from dbo.UFN_IDSETREADER_GETRESULTS(@IDSETREGISTERID)

    open PROSPECTPLANCURSOR;
    begin try
        begin transaction;

        fetch next from PROSPECTPLANCURSOR into @PROSPECTPLANID;

        while (@@FETCH_STATUS = 0)
        begin
            begin try
                save transaction OPPORTUNITYTX;

                -- check for invalid combination of prospect plan base currency id and opportunity transaction currency id

                select @BASECURRENCYID = BASECURRENCYID from dbo.PROSPECTPLAN where ID = @PROSPECTPLANID
                set @COMBOISVALID = dbo.UFN_CURRENCYSET_BASEANDTRANSACTIONCURRENCYCOMBOISVALID(@BASECURRENCYID, @TRANSACTIONCURRENCYID)
                if @COMBOISVALID = 0
                    raiserror(@INVALIDCURRENCYPAIRMSG, 13, 1);

                ---------------------------------------------------------------------

                -- create opportunities


                declare @OPPORTUNITYID uniqueidentifier = NEWID();

                exec dbo.USP_DATAFORMTEMPLATE_OPPORTUNITY_ADD
                    @ID = @OPPORTUNITYID output,
                    @CHANGEAGENTID = @CHANGEAGENTID,
                    @PROSPECTPLANID = @PROSPECTPLANID,
                    @EXPECTEDASKAMOUNT = @EXPECTEDASKAMOUNT,
                    @ASKAMOUNT = @ASKAMOUNT,
                    @DESIGNATION = @OPPORTUNITYDESIGNATIONSXML,
                    @OPPORTUNITYTYPECODEID = @OPPORTUNITYTYPECODEID,
                    @STATUSCODE = @OPPORTUNITYSTATUSCODE,
                    @TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
                    @EXPECTEDASKDATE = @EXPECTEDASKDATE,
                    @ASKDATE = @ASKDATE,
                    @RESPONSEDATE = @RESPONSEDATE,
                    @COMMENT = @COMMENT,
                    @LIKELIHOODTYPECODEID = @LIKELIHOODTYPECODEID

                ---------------------------------------------------------------------

                -- reporting

                exec sp_executesql @SQL, @PARAMDEF, @PROSPECTPLANID, @OPPORTUNITYID, 0
                set @NUMBERPROCESSED = @NUMBERPROCESSED + 1;
            end try
            begin catch
                rollback transaction OPPORTUNITYTX;
                declare @STATUSCODE tinyint;
                if error_message() like '%' + @INVALIDCURRENCYPAIRMSG + '%'
                    set @STATUSCODE = 1;
                if error_message() like '%CK_OPPORTUNITY_QUALIFIEDONLYWHENACTIVE%'
                    set @STATUSCODE = 3;
                if @STATUSCODE in (1,2,3)
                begin
                    -- ignore errors due to invalid combination of plan base currency + opportunity tx currency

                    exec sp_executesql @SQL, @PARAMDEF, @PROSPECTPLANID, null, @STATUSCODE
                    set @NUMBEREXCEPTIONS = @NUMBEREXCEPTIONS + 1;
                end
                else
                    exec dbo.USP_RAISE_ERROR;
            end catch

            fetch next from PROSPECTPLANCURSOR into @PROSPECTPLANID;
        end
        close PROSPECTPLANCURSOR;
        deallocate PROSPECTPLANCURSOR;

        commit transaction;
    end try
    begin catch
        close PROSPECTPLANCURSOR;
        deallocate PROSPECTPLANCURSOR;
        rollback transaction;
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

end