USP_DATAFORMTEMPLATE_EDIT_PROSPECTPLANBASECURRENCY

The save procedure used by the edit dataform template "Prospect Plan Base Currency Edit Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@BASECURRENCYID	uniqueidentifier	IN	Base currency

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_PROSPECTPLANBASECURRENCY (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @BASECURRENCYID uniqueidentifier
)
as

    set nocount on;

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

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

  if exists (
    select top(1) 
      REVENUEOPPORTUNITY.ID
    from
      dbo.OPPORTUNITY
    inner join
      dbo.REVENUEOPPORTUNITY on REVENUEOPPORTUNITY.OPPORTUNITYID = OPPORTUNITY.ID
    where
      OPPORTUNITY.PROSPECTPLANID = @ID
  )
    raiserror('BBERR_OPPORTUNITYHASREVENUE', 13, 1);

    begin try
    declare @ORGCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

      update dbo.PROSPECTPLAN set
          BASECURRENCYID = @BASECURRENCYID,
          CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED = @CURRENTDATE
      where ID = @ID;

    update dbo.OPPORTUNITY
    set
      BASEEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(OPPORTUNITY.TRANSACTIONCURRENCYID, @BASECURRENCYID, OPPORTUNITY.DATEADDED, 0, null)
    where
      PROSPECTPLANID = @ID;

    update dbo.OPPORTUNITY
    set
      BASECURRENCYID = @BASECURRENCYID,
      EXPECTEDASKAMOUNT = EXPECTEDASKCURRENCYVALUES.BASEAMOUNT,
      ASKAMOUNT = ASKCURRENCYVALUES.BASEAMOUNT,
      ACCEPTEDASKAMOUNT = ACCEPTEDASKCURRENCYVALUES.BASEAMOUNT,
      ORGANIZATIONEXPECTEDASKAMOUNT = EXPECTEDASKCURRENCYVALUES.ORGANIZATIONAMOUNT,        
      ORGANIZATIONASKAMOUNT = ASKCURRENCYVALUES.ORGANIZATIONAMOUNT,
      ORGANIZATIONACCEPTEDASKAMOUNT = ACCEPTEDASKCURRENCYVALUES.ORGANIZATIONAMOUNT,
      ORGANIZATIONEXCHANGERATEID = EXPECTEDASKCURRENCYVALUES.ORGANIZATIONEXCHANGERATEID,
      CHANGEDBYID = @CHANGEAGENTID,
      DATECHANGED = @CURRENTDATE
    from
      dbo.OPPORTUNITY
    cross apply
      dbo.UFN_CURRENCY_GETCURRENCYVALUES(TRANSACTIONEXPECTEDASKAMOUNT, OPPORTUNITY.DATEADDED, @BASECURRENCYID, OPPORTUNITY.BASEEXCHANGERATEID, OPPORTUNITY.TRANSACTIONCURRENCYID) EXPECTEDASKCURRENCYVALUES
    cross apply
      dbo.UFN_CURRENCY_GETCURRENCYVALUES(TRANSACTIONASKAMOUNT, OPPORTUNITY.DATEADDED, @BASECURRENCYID, OPPORTUNITY.BASEEXCHANGERATEID, OPPORTUNITY.TRANSACTIONCURRENCYID) ASKCURRENCYVALUES
    cross apply
      dbo.UFN_CURRENCY_GETCURRENCYVALUES(TRANSACTIONACCEPTEDASKAMOUNT, OPPORTUNITY.DATEADDED, @BASECURRENCYID, OPPORTUNITY.BASEEXCHANGERATEID, OPPORTUNITY.TRANSACTIONCURRENCYID) ACCEPTEDASKCURRENCYVALUES
    where
      PROSPECTPLANID = @ID;     

    update dbo.OPPORTUNITYDESIGNATION
    set
      BASECURRENCYID = @BASECURRENCYID,
      AMOUNT = CURRENCYVALUES.BASEAMOUNT,
      ORGANIZATIONAMOUNT = CURRENCYVALUES.ORGANIZATIONAMOUNT,
      BASEEXCHANGERATEID = OPPORTUNITY.BASEEXCHANGERATEID,
      ORGANIZATIONEXCHANGERATEID = OPPORTUNITY.ORGANIZATIONEXCHANGERATEID,
      CHANGEDBYID = @CHANGEAGENTID,
      DATECHANGED = @CURRENTDATE
    from
      dbo.OPPORTUNITYDESIGNATION
    inner join  
      dbo.OPPORTUNITY on OPPORTUNITY.ID = OPPORTUNITYDESIGNATION.OPPORTUNITYID
    cross apply
      dbo.UFN_CURRENCY_GETCURRENCYVALUES(OPPORTUNITYDESIGNATION.TRANSACTIONAMOUNT, OPPORTUNITY.DATEADDED, @BASECURRENCYID, OPPORTUNITY.BASEEXCHANGERATEID, OPPORTUNITY.TRANSACTIONCURRENCYID) CURRENCYVALUES
    where
      OPPORTUNITY.PROSPECTPLANID = @ID;        
  end try
  begin catch
      exec dbo.USP_RAISE_ERROR
      return 1
  end catch

return 0;