A simple solution would be to have the ability to specify the execution order of this workflowaction inside the workflowrule like all the other actions actually...

But the order field parameter is not available on this action...

Why ?

Hi,

Why don't you use TLS on CaseProgress? Maybe it could help you.

Tiago

Interesting point...

Problem is TLS are triggered each time the record is being affected...

Must think about it and try...

I will let you know.

Any other idea ?

Hi,

I've tried to change completely my approach, and tested another thing. I've replaced my Run Stored procedure action by a Execute SQL Statement.

Here's the code I use :

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

DECLARE @serv_servicesid int, @serv_SecTerr int, @serv_ChannelId int, @serv_OpportunityId int, @serv_quot_orderquoteid int, @serv_LeadId int, @serv_Name varchar(100), @serv_UserId int, @serv_CompanyId int, @serv_PersonId int, @serv_status varchar(40), @serv_stage varchar(40), @serv_fsq_date_envoi datetime, @serv_fsq_date_receipt datetime, @serv_activation_date datetime, @serv_unpaid_amount numeric(24,6), @serv_unpaid_amount_CID int, @serv_term_effective_date datetime, @serv_deactivation_date datetime, @serv_invoice_status nvarchar(40), @serv_last_invoice_date datetime , @serv_productitem nvarchar(40), @serv_signature_date datetime, @serv_term_reason_details nvarchar(max), @serv_term_reason nvarchar(255), @serv_term_request_date datetime, @serv_ytd_consumption_qty numeric(24,6), @case_problemtype varchar(40), @logon_no int, @serv_contractsid int

SELECT TOP 1 @serv_SecTerr=serv_Secterr, @serv_ChannelId=serv_ChannelId, @serv_OpportunityId=serv_OpportunityId, @serv_UserId=serv_UserId, @serv_CompanyId=serv_companyid, @serv_status=serv_Status, @serv_quot_orderquoteid=serv_quot_orderquoteid, @serv_Name=serv_name, @serv_activation_date=case_fsq_service_active_date, @serv_unpaid_amount=serv_unpaid_amount, @serv_unpaid_amount_CID=serv_unpaid_amount_CID, @serv_term_effective_date=serv_term_effective_date, @serv_deactivation_date=case_fsq_service_deactive_date, @serv_invoice_status=serv_invoice_status, @serv_last_invoice_date=serv_last_invoice_date, @serv_productitem=serv_productitem, @serv_signature_date=serv_signature_date, @serv_term_reason_details=serv_term_reason_details, @serv_term_reason=serv_term_reason, @serv_term_request_date=serv_term_request_date, @serv_ytd_consumption_qty=serv_ytd_consumption_qty, @serv_servicesid=serv_servicesid, @case_problemtype=case_problemtype, @serv_stage=serv_stage, @logon_no = Case_UpdatedBy, @serv_contractsid = serv_contractsid FROM SERVICES INNER JOIN CASES ON serv_servicesid = Case_servicesId and Case_deleted is null WHERE case_caseid = #case_caseid# AND serv_deleted is null AND case_servicesid is not null ;

DECLARE @ServicesProgress_ID int ;

exec @ServicesProgress_ID = dbo.eware_get_identity_id 'ServicesProgress' ;

if @case_problemtype in ('IT_InitialSetup','IT_AdditionalSetup')

BEGIN

SET @serv_status = 'Active';

END

if @case_problemtype = 'Termination'

BEGIN

SET @serv_status = 'Inactive' ;

END

INSERT INTO SERVICESProgress

(serv_SERVICESProgressid,serv_CreatedBy,serv_CreatedDate,serv_UpdatedBy,serv_UpdatedDate,serv_TimeStamp,serv_Deleted,

serv_UserId,serv_ProgressNote,serv_Name,serv_ChannelId,

serv_contractsid,serv_SERVICESid,serv_status,serv_activation_date,

serv_unpaid_amount,serv_unpaid_amount_CID,serv_CompanyId,

serv_term_effective_date,serv_invoice_status,serv_last_invoice_date,

serv_OpportunityId,serv_productitem,serv_quot_orderquoteid,

serv_signature_date,serv_stage,serv_term_reason_details,

serv_term_reason,serv_term_request_date,serv_ytd_consumption_qty)

VALUES

(@ServicesProgress_ID,@logon_no,CURRENT_TIMESTAMP,

@logon_no,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,NULL,

@logon_no,'Automatic update of Service from the Ticket workflow Closure action',@serv_Name,@serv_ChannelId,

@serv_contractsid,@serv_servicesid,@serv_status,@serv_activation_date, @serv_unpaid_amount,@serv_unpaid_amount_CID,

@serv_CompanyId,@serv_term_effective_date,@serv_invoice_status,@serv_last_invoice_date,@serv_OpportunityId,@serv_productitem,

@serv_quot_orderquoteid,@serv_signature_date,@serv_stage,

@serv_term_reason_details,@serv_term_reason,@serv_term_request_date,@serv_ytd_consumption_qty);

UPDATE Services

SET serv_status = @serv_status,

serv_activation_date = @serv_activation_date,

serv_UpdatedBy = @logon_no,

serv_UpdatedDate = CURRENT_TIMESTAMP,

serv_TimeStamp = CURRENT_TIMESTAMP

WHERE serv_servicesID = @serv_servicesid ;

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

Now it seems that there is another issue related with the Execute SQL Statement action.

Actually Execute SQL Statement = ExecSQL in pure T-SQL, plus the fact that each ";" T-SQL instruction separator is interpreted by Sage CRM as separate ExecSQL statements.

It implies that my Sample code is not working because of the scope of variables.

for instance while this sql code works perfectly in SQL Management studio, analyzing the Sage CRM SQL logs with full logging shows that there are several ExecSQL statements that are executed one after the other.

the exec @ServicesProgress_ID = dbo.eware_get_identity_id 'ServicesProgress' ; command can't run because on the previous variable Declaration line there is

DECLARE @ServicesProgress_ID int ;

Thus it doesn't know the @ServiceProgress_Id because the scope & transaction are different.

The solution is then the following code which is working (but I have to do more tests) :

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

DECLARE @serv_servicesid int, @serv_SecTerr int, @serv_ChannelId int, @serv_OpportunityId int, @serv_quot_orderquoteid int, @serv_LeadId int, @serv_Name varchar(100), @serv_UserId int, @serv_CompanyId int, @serv_PersonId int, @serv_status varchar(40), @serv_stage varchar(40), @serv_fsq_date_envoi datetime, @serv_fsq_date_receipt datetime, @serv_activation_date datetime, @serv_unpaid_amount numeric(24,6), @serv_unpaid_amount_CID int, @serv_term_effective_date datetime, @serv_deactivation_date datetime, @serv_invoice_status nvarchar(40), @serv_last_invoice_date datetime , @serv_productitem nvarchar(40), @serv_signature_date datetime, @serv_term_reason_details nvarchar(max), @serv_term_reason nvarchar(255), @serv_term_request_date datetime, @serv_ytd_consumption_qty numeric(24,6), @case_problemtype varchar(40), @logon_no int, @serv_contractsid int

SELECT TOP 1 @serv_SecTerr=serv_Secterr, @serv_ChannelId=serv_ChannelId, @serv_OpportunityId=serv_OpportunityId, @serv_UserId=serv_UserId, @serv_CompanyId=serv_companyid, @serv_status=serv_Status, @serv_quot_orderquoteid=serv_quot_orderquoteid, @serv_Name=serv_name, @serv_activation_date=case_fsq_service_active_date, @serv_unpaid_amount=serv_unpaid_amount, @serv_unpaid_amount_CID=serv_unpaid_amount_CID, @serv_term_effective_date=serv_term_effective_date, @serv_deactivation_date=case_fsq_service_deactive_date, @serv_invoice_status=serv_invoice_status, @serv_last_invoice_date=serv_last_invoice_date, @serv_productitem=serv_productitem, @serv_signature_date=serv_signature_date, @serv_term_reason_details=serv_term_reason_details, @serv_term_reason=serv_term_reason, @serv_term_request_date=serv_term_request_date, @serv_ytd_consumption_qty=serv_ytd_consumption_qty, @serv_servicesid=serv_servicesid, @case_problemtype=case_problemtype, @serv_stage=serv_stage, @logon_no = Case_UpdatedBy, @serv_contractsid = serv_contractsid FROM SERVICES INNER JOIN CASES ON serv_servicesid = Case_servicesId and Case_deleted is null WHERE case_caseid = #case_caseid# AND serv_deleted is null AND case_servicesid is not null

DECLARE @ServicesProgress_ID int

exec @ServicesProgress_ID = dbo.eware_get_identity_id ServicesProgress

if @case_problemtype in ('IT_InitialSetup','IT_AdditionalSetup')

BEGIN

SET @serv_status = 'Active'

END

if @case_problemtype = 'Termination'

BEGIN

SET @serv_status = 'Inactive'

END

INSERT INTO SERVICESProgress

(serv_SERVICESProgressid,serv_CreatedBy,serv_CreatedDate,serv_UpdatedBy,serv_UpdatedDate,serv_TimeStamp,serv_Deleted,

serv_UserId,serv_ProgressNote,serv_Name,serv_ChannelId,

serv_contractsid,serv_SERVICESid,serv_status,serv_activation_date,

serv_unpaid_amount,serv_unpaid_amount_CID,serv_CompanyId,

serv_term_effective_date,serv_invoice_status,serv_last_invoice_date,

serv_OpportunityId,serv_productitem,serv_quot_orderquoteid,

serv_signature_date,serv_stage,serv_term_reason_details,

serv_term_reason,serv_term_request_date,serv_ytd_consumption_qty)

VALUES

(@ServicesProgress_ID,@logon_no,CURRENT_TIMESTAMP,

@logon_no,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,NULL,

@logon_no,'Automatic update of Service from the Ticket workflow Closure action',@serv_Name,@serv_ChannelId,

@serv_contractsid,@serv_servicesid,@serv_status,@serv_activation_date, @serv_unpaid_amount,@serv_unpaid_amount_CID,

@serv_CompanyId,@serv_term_effective_date,@serv_invoice_status,@serv_last_invoice_date,@serv_OpportunityId,@serv_productitem,

@serv_quot_orderquoteid,@serv_signature_date,@serv_stage,

@serv_term_reason_details,@serv_term_reason,@serv_term_request_date,@serv_ytd_consumption_qty)

UPDATE Services

SET serv_status = @serv_status,

serv_activation_date = @serv_activation_date,

serv_UpdatedBy = @logon_no,

serv_UpdatedDate = CURRENT_TIMESTAMP,

serv_TimeStamp = CURRENT_TIMESTAMP

WHERE serv_servicesID = @serv_servicesid ;

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

Hope it will save time for people who find this post in the future...