Dynamics Community Forum Thread Details

Check for duplicated SIN with sanscript and EOne Extender

Hello all,

My company is using Canadian Payroll, in addition to eOne's eXtender product.

I am trying to create a sanscript script which will run under an eXtender action, that will open a prompt whenever our Payroll enters in a SIN that is already in CPY10100 (aka an employee who already exists).

I copied an existing template from eOne found here: https://www.eonesolutions.com/extender/downloads/#Templates or below

local text lt_sql;
local long ll_sql_connection, ll_status;
local integer li_sop_type;
local string ls_sop_number;

if empty(<Customer PO Number>) or empty(<Customer Number>) then
   abort script;
end if;

SQL_Connect(ll_sql_connection);

lt_sql = "USE " + 'Intercompany ID' of globals;
SQL_Execute(ll_sql_connection, lt_sql);

{check if customer and po combination exists in work table}
lt_sql = "select SOPTYPE, SOPNUMBE from SOP10100 where ";
lt_sql = lt_sql + "CUSTNMBR = " + SQL_FormatStrings(<Customer Number>) + " and ";
lt_sql = lt_sql + "CSTPONBR = " + SQL_FormatStrings(<Customer PO Number>) + " and ";
lt_sql = lt_sql + "not (SOPNUMBE = " + SQL_FormatStrings(<SOP Number>) + " and SOPTYPE = " + str(<SOP Type>) + ")";

ll_status = SQL_Execute(ll_sql_connection, lt_sql);
if ll_status = 0 then
   ll_status = SQL_FetchNext(ll_sql_connection);
   if ll_status = 0 then
       SQL_GetData(ll_sql_connection, 1, li_sop_type);
       SQL_GetData(ll_sql_connection, 2, ls_sop_number);
   end if;
end if;

if empty(ls_sop_number) then
   {check if customer and po combination exists in history table}
   lt_sql = "select SOPTYPE, SOPNUMBE from SOP30200 where ";
   lt_sql = lt_sql + "CUSTNMBR = " + SQL_FormatStrings(<Customer Number>) + " and ";
   lt_sql = lt_sql + "CSTPONBR = " + SQL_FormatStrings(<Customer PO Number>);
   ll_status = SQL_Execute(ll_sql_connection, lt_sql);
   if ll_status = 0 then
       ll_status = SQL_FetchNext(ll_sql_connection);
       if ll_status = 0 then
           SQL_GetData(ll_sql_connection, 1, li_sop_type);
           SQL_GetData(ll_sql_connection, 2, ls_sop_number);
       end if;
   end if;
end if;

if not empty(ls_sop_number) then
   case ask("A document has already been entered for this customer with this purchase order number.", "Open Inquiry", "Continue", "Delete")
       in [ASKBUTTON1]
           open form SOP_Document_Inquiry;
           if isopen(form SOP_Document_Inquiry) then
               'Sort By' of window SOP_Document_Inquiry of form SOP_Document_Inquiry = 1;
               run script 'Sort By' of window SOP_Document_Inquiry of form SOP_Document_Inquiry;
               'All Or Range' of window SOP_Document_Inquiry of form SOP_Document_Inquiry = 1;
               run script 'All Or Range' of window SOP_Document_Inquiry of form SOP_Document_Inquiry;
               'Start SOP Number' of window SOP_Document_Inquiry of form SOP_Document_Inquiry = ls_sop_number;
               'End SOP Number' of window SOP_Document_Inquiry of form SOP_Document_Inquiry = ls_sop_number;
               'Include GB' of window SOP_Document_Inquiry of form SOP_Document_Inquiry = 1;
               run script 'Include GB' of window SOP_Document_Inquiry of form SOP_Document_Inquiry;
               run script 'Redisplay Button' of window SOP_Document_Inquiry of form SOP_Document_Inquiry;
           end if;
       in [ASKBUTTON3]
           {run script delayed 'Delete Button' of window SOP_Entry of form SOP_Entry;}
   end case;
end if;

SQL_Terminate(ll_sql_connection);

I modified it to be the following ,and it is 'valid' according to the extender window. However, no popup appears when a duplicate SIN is entered or changed in a test client.

local text lt_sql;
local long ll_sql_connection, ll_status;
local string ls_sin_number;

if empty(<P_Employee_ID>) or empty(<P_Social_Insurance_Number>) then
   abort script;
end if;

SQL_Connect(ll_sql_connection);

lt_sql = "select Distinct PSocialInsuranceNumber from CPY10100 where ";
lt_sql = lt_sql + "PSocialInsuranceNumber = " + SQL_FormatStrings(<P_Social_Insurance_Number>);

ll_status = SQL_Execute(ll_sql_connection, lt_sql);
if ll_status = 0 then
   ll_status = SQL_FetchNext(ll_sql_connection);
   if ll_status = 0 then
       SQL_GetData(ll_sql_connection, 1, ls_sin_number);
   end if;
end if;

if not empty(ls_sin_number) then
   ask("A employee already exists with this SIN.", "Continue","Continue","Continue");
end if;
SQL_Terminate(ll_sql_connection);