USP_DATAFORMTEMPLATE_OPPORTUNITY_ADD

The save procedure used by the add dataform template "Opportunity Add Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@PROSPECTPLANID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@EXPECTEDASKAMOUNT	money	IN	Expected ask amount
@ASKAMOUNT	money	IN	Ask amount
@ACCEPTEDAMOUNT	money	IN	Accepted amount
@STATUSCODE	tinyint	IN	Status
@EXPECTEDASKDATE	datetime	IN	Expected ask date
@ASKDATE	datetime	IN	Ask date
@RESPONSEDATE	datetime	IN	Response date
@COMMENT	nvarchar(max)	IN	Comments
@DESIGNATION	xml	IN	Designation
@LIKELIHOODTYPECODEID	uniqueidentifier	IN	Likelihood
@OPPORTUNITYTYPECODEID	uniqueidentifier	IN	Opportunity type
@TRANSACTIONCURRENCYID	uniqueidentifier	IN	Transaction currency

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_OPPORTUNITY_ADD
(
  @ID uniqueidentifier = null output,
  @CHANGEAGENTID uniqueidentifier = null,
  @PROSPECTPLANID uniqueidentifier,
  @EXPECTEDASKAMOUNT money = 0,
  @ASKAMOUNT money = 0,
  @ACCEPTEDAMOUNT money = 0,
  @STATUSCODE tinyint = 0,
  @EXPECTEDASKDATE datetime = null,
  @ASKDATE datetime = null,
  @RESPONSEDATE datetime = null,
  @COMMENT nvarchar(max) = '',
  @DESIGNATION xml = null,
  @LIKELIHOODTYPECODEID uniqueidentifier = null,
  @OPPORTUNITYTYPECODEID uniqueidentifier = null,
  @TRANSACTIONCURRENCYID uniqueidentifier = null
) as
begin
  set nocount on;

  if @STATUSCODE = 0
    set @ASKAMOUNT = 0;

  if @STATUSCODE = 4 or @STATUSCODE = 5
    set @LIKELIHOODTYPECODEID = null;

  begin try
    if @ID is null
      set @ID = newid();

    if @CHANGEAGENTID is null
      exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    declare @CHANGEDATE datetime = getdate();

    declare @BASECURRENCYID uniqueidentifier;
    declare @BASEEXPECTEDASKAMOUNT money;
    declare @BASEASKAMOUNT money;
    declare @BASEACCEPTEDASKAMOUNT money;
    declare @BASEEXCHANGERATEID uniqueidentifier;
    declare @ORGANIZATIONCURRENCYID uniqueidentifier;
    declare @ORGANIZATIONEXPECTEDASKAMOUNT money;
    declare @ORGANIZATIONASKAMOUNT money;
    declare @ORGANIZATIONACCEPTEDASKAMOUNT money;
    declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;

    if @TRANSACTIONCURRENCYID is null
        set @TRANSACTIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

    select @BASECURRENCYID = BASECURRENCYID from dbo.PROSPECTPLAN where PROSPECTPLAN.ID = @PROSPECTPLANID;
    set @BASEEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@TRANSACTIONCURRENCYID, @BASECURRENCYID, @CHANGEDATE, 1, null);

    exec dbo.USP_CURRENCY_GETCURRENCYVALUES @ASKAMOUNT, @CHANGEDATE, @BASECURRENCYID, @BASEEXCHANGERATEID output,
      @TRANSACTIONCURRENCYID, @BASEASKAMOUNT output, @ORGANIZATIONCURRENCYID output, @ORGANIZATIONASKAMOUNT output,
      @ORGANIZATIONEXCHANGERATEID output, 1;

    exec dbo.USP_CURRENCY_GETCURRENCYVALUES @EXPECTEDASKAMOUNT, @CHANGEDATE, @BASECURRENCYID, @BASEEXCHANGERATEID,
      @TRANSACTIONCURRENCYID, @BASEEXPECTEDASKAMOUNT output, @ORGANIZATIONCURRENCYID output, @ORGANIZATIONEXPECTEDASKAMOUNT output,
      @ORGANIZATIONEXCHANGERATEID output, 1;   

    exec dbo.USP_CURRENCY_GETCURRENCYVALUES @ACCEPTEDAMOUNT, @CHANGEDATE, @BASECURRENCYID, @BASEEXCHANGERATEID,
      @TRANSACTIONCURRENCYID, @BASEACCEPTEDASKAMOUNT output, @ORGANIZATIONCURRENCYID output, @ORGANIZATIONACCEPTEDASKAMOUNT output,
      @ORGANIZATIONEXCHANGERATEID output, 1;            

    exec dbo.USP_OPPORTUNITY_VALIDATEDESIGNATIONS @DESIGNATION;

    insert into dbo.OPPORTUNITY
    (
      ID,
      PROSPECTPLANID,
      EXPECTEDASKAMOUNT,
      TRANSACTIONEXPECTEDASKAMOUNT,
      ORGANIZATIONEXPECTEDASKAMOUNT,
      ASKAMOUNT,
      TRANSACTIONASKAMOUNT,
      ORGANIZATIONASKAMOUNT,
      ACCEPTEDASKAMOUNT,
      TRANSACTIONACCEPTEDASKAMOUNT,
      ORGANIZATIONACCEPTEDASKAMOUNT,
      STATUSCODE,
      EXPECTEDASKDATE,
      ASKDATE,
      RESPONSEDATE,
      COMMENT,
      ADDEDBYID, 
      CHANGEDBYID,
      LIKELIHOODTYPECODEID,
      OPPORTUNITYTYPECODEID,
      BASECURRENCYID,
      TRANSACTIONCURRENCYID,
      BASEEXCHANGERATEID,
      ORGANIZATIONEXCHANGERATEID,
      DATEADDED,
      DATECHANGED
    ) 
    values
    (
      @ID,
      @PROSPECTPLANID,
      @BASEEXPECTEDASKAMOUNT,
      @EXPECTEDASKAMOUNT,
      @ORGANIZATIONEXPECTEDASKAMOUNT,
      @BASEASKAMOUNT,
      @ASKAMOUNT,
      @ORGANIZATIONASKAMOUNT,
      @BASEACCEPTEDASKAMOUNT,
      @ACCEPTEDAMOUNT,
      @ORGANIZATIONACCEPTEDASKAMOUNT,
      @STATUSCODE,
      @EXPECTEDASKDATE,
      @ASKDATE,
      @RESPONSEDATE,
      @COMMENT,
      @CHANGEAGENTID, 
      @CHANGEAGENTID,
      @LIKELIHOODTYPECODEID,
      @OPPORTUNITYTYPECODEID,
      @BASECURRENCYID,
      @TRANSACTIONCURRENCYID,
      @BASEEXCHANGERATEID,
      @ORGANIZATIONEXCHANGERATEID,
      @CHANGEDATE,
      @CHANGEDATE
    );

    set @DESIGNATION = dbo.UFN_OPPORTUNITYDESIGNATION_CONVERTAMOUNTSINXML(@DESIGNATION, @BASECURRENCYID, @TRANSACTIONCURRENCYID, @BASEEXCHANGERATEID, @CHANGEDATE);

    exec dbo.USP_OPPORTUNITY_DESIGNATION_2_ADDFROMXML @ID, @DESIGNATION, @CHANGEAGENTID;
    exec dbo.USP_OPPORTUNITY_CLEARANDADDCAMPAIGNS @OPPORTUNITYID = @ID, @CHANGEAGENTID = @CHANGEAGENTID, @CHANGEDATE = @CHANGEDATE;

  end try
  begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch

  return 0;

end