![](/wp-content/uploads/2009/12/martin-300x271.jpg)
After the Customer PO number has been entered, the script runs which executes a query of the SOP work, open, and history tables to see if that particular Customer PO Number combination has been entered previously. If it finds it, it will open a dialog box asking if you want to “Open Inquiry, Continue, or Delete”.
If the user selects “Open Inquiry” it will open the Sop Document Inquiry window with the Invoice number that contains the duplicate PO Number. If the user clicks “Continue” it will allow the user to continue to enter the duplicate Customer PO Number. If the user hits delete, it will actually delete the current SOP document being entered.
The code window looks like this.
The actual code is posted below so you can see exactly what happens. Again I repeat that if you do not understand the code below – then don’t try and write logic scipts. If this makes perfect sense then there is so much you can do within GP with eXtender logic. The best part is you can deploy this code by sending a script to be imported, and you never need to cnk up or upgrade dictionaries again for small customisations.
local string ls_po_number, ls_customer, ls_sop_number;
local integer li_sop_type;
local text lt_sql;
local long ll_sql_connection, ll_status;
ls_customer = ‘Customer Number’ of window SOP_Entry of form SOP_Entry;
ls_po_number = ‘Customer PO Number’ of window SOP_Entry of form SOP_Entry;
if empty(ls_po_number) or empty(ls_customer) then
abort script;
end if;
li_sop_type = ‘SOP Type’ of window SOP_Entry of form SOP_Entry;
ls_sop_number = ‘SOP Number’ of window SOP_Entry of form SOP_Entry;
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(ls_customer) + ” and “;
lt_sql = lt_sql + “CSTPONBR = ” + SQL_FormatStrings(ls_po_number) + ” and “;
lt_sql = lt_sql + “not (SOPNUMBE = ” + SQL_FormatStrings(ls_sop_number) + ” and SOPTYPE = ” + str(li_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);
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 = 0;
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;
abort script;
end if;
end if;
{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(ls_customer) + ” and “;
lt_sql = lt_sql + “CSTPONBR = ” + SQL_FormatStrings(ls_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);
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;
abort script;
end if;
end if;
SQL_Terminate(ll_sql_connection);