Per actualitzar automàticament els comentaris que porta un assentament de vendes o compres.
Al PostTransactionNotice:
SET SCHEMA SBOCOMERC; DROP PROCEDURE SBO_SP_PostTransactionNotice; CREATE PROCEDURE SBO_SP_PostTransactionNotice ( in object_type nvarchar(20), -- SBO Object Type in transaction_type nchar(1), -- [A]dd, [U]pdate, [D]elete, [C]ancel, C[L]ose in num_of_cols_in_key int, in list_of_key_cols_tab_del nvarchar(255), in list_of_cols_val_tab_del nvarchar(255) ) LANGUAGE SQLSCRIPT AS transId int; docNum int; numAtCard nvarchar(255); cardName nvarchar(255); campMemo nvarchar(50); -- Return values error int; -- Result (0 for no error) error_message nvarchar (200); -- Error string to be displayed begin error := 0; error_message := N'Ok'; --- ID 1 --- Autor: David Marti --- Data: 26/7/2018 --- Descripció: Modificació del comentari de l'assentament per les factures de compres i vendes. --- Historial: --- 30/07/2018 Incloure abonaments de vendes i compres --- 02/08/2018 Ajustar el campMemo a 50 posicions, doncs si no, hi ha errors al SAP --- 03/08/2018 Millorar el càlcul de la longitud del campMemo doncs quan NumAtCard es molt gran, desborda. --- 28/08/2018 Millorar el càlcul de la longitud del campMemo, hi havia un error. if :object_type='13' and (:transaction_type='A' or :transaction_type='U') then select "TransId" into transId from OJDT where "CreatedBy"=:list_of_cols_val_tab_del and "TransType"='13'; select "CardName","DocNum" into cardName,DocNum from OINV where "DocEntry"=:list_of_cols_val_tab_del; campMemo:=SUBSTRING(cardName,1,50-LENGTH(docNum)-10) || ' - N/Fra. ' || docNum; update OJDT set "Memo"=:campMemo where "TransId"=:transId; update JDT1 set "LineMemo"=:campMemo where "TransId"=:transId; end if; if :object_type='14' and (:transaction_type='A' or :transaction_type='U') then select "TransId" into transId from OJDT where "CreatedBy"=:list_of_cols_val_tab_del and "TransType"='14'; select "CardName","DocNum" into cardName,DocNum from ORIN where "DocEntry"=:list_of_cols_val_tab_del; campMemo:=SUBSTRING(cardName,1,50-LENGTH(docNum)-10) || ' - N/Abo. ' || docNum; update OJDT set "Memo"=:campMemo where "TransId"=:transId; update JDT1 set "LineMemo"=:campMemo where "TransId"=:transId; end if; if :object_type='18' and (:transaction_type='A' or :transaction_type='U') then select "TransId" into transId from OJDT where "CreatedBy"=:list_of_cols_val_tab_del and "TransType"='18'; select "CardName","NumAtCard" into cardName,numAtCard from OPCH where "DocEntry"=:list_of_cols_val_tab_del; campMemo:=SUBSTRING(cardName,1,50-LENGTH(numAtCard)-10) || ' - S/Fra. ' || numAtCard; update OJDT set "Memo"=:campMemo where "TransId"=:transId; update JDT1 set "LineMemo"=:campMemo where "TransId"=:transId; end if; if :object_type='19' and (:transaction_type='A' or :transaction_type='U') then select "TransId" into transId from OJDT where "CreatedBy"=:list_of_cols_val_tab_del and "TransType"='19'; select "CardName","NumAtCard" into cardName,numAtCard from ORPC where "DocEntry"=:list_of_cols_val_tab_del; campMemo:=SUBSTRING(cardName,1,50-LENGTH(numAtCard)-10) || ' - S/Abo. ' || numAtCard; update OJDT set "Memo"=:campMemo where "TransId"=:transId; update JDT1 set "LineMemo"=:campMemo where "TransId"=:transId; end if; --- FI ID1 -- INICIO DEL CODIGO INSERTADO AUTOMATICAMENTE | INTAREX - IIS | NO MODIFICAR | domingo, 5 de agosto de 2018 CALL IISDB_SP_POSTTRANSACTIONNOTICE (object_type, transaction_type, num_of_cols_in_key, list_of_key_cols_tab_del, list_of_cols_val_tab_del); -- FIN DEL CODIGO INSERTADO AUTOMATICAMENTE (Para cualquier problema con este codigo contactar con david.marti@intarex.com) -- Select the return values select :error, :error_message FROM dummy; end;
Per actualitzar tots els comentaris existents:
SET SCHEMA SBOPIGU; -- CAPSALERA ASSENTAMENT update T0 set "Memo"=SUBSTRING(T1."CardName",1,50-LENGTH("DocNum")-10) || ' - N/Fra. ' || T1."DocNum" from OJDT T0 inner join OINV T1 on T1."DocEntry"=T0."CreatedBy" and T0."TransType"='13'; update T0 set "Memo"=SUBSTRING(T1."CardName",1,50-LENGTH("DocNum")-10) || ' - N/Abo. ' || T1."DocNum" from OJDT T0 inner join ORIN T1 on T1."DocEntry"=T0."CreatedBy" and T0."TransType"='14'; update T0 set "Memo"=SUBSTRING(T1."CardName",1,50-LENGTH("NumAtCard")-10) || ' - S/Fra. ' || T1."NumAtCard" from OJDT T0 inner join OPCH T1 on T1."DocEntry"=T0."CreatedBy" and T0."TransType"='18'; update T0 set "Memo"=SUBSTRING(T1."CardName",1,50-LENGTH("NumAtCard")-10) || ' - S/Abo. ' || T1."NumAtCard" from OJDT T0 inner join ORPC T1 on T1."DocEntry"=T0."CreatedBy" and T0."TransType"='19'; -- LINIES ASSENTAMENT update T2 set "LineMemo"=SUBSTRING(T1."CardName" || ' - N/Fra. ' || T1."DocNum",1,50) from JDT1 T2 inner join OJDT T0 on T0."TransId"=T2."TransId" inner join OINV T1 on T1."DocEntry"=T0."CreatedBy" and T0."TransType"='13'; update T2 set "LineMemo"=SUBSTRING(T1."CardName" || ' - N/Abo. ' || T1 trova."DocNum",1,50) from JDT1 T2 inner join OJDT T0 on T0."TransId"=T2."TransId" inner join ORIN T1 on T1."DocEntry"=T0."CreatedBy" and T0."TransType"='14'; update T2 set "LineMemo"=SUBSTRING(T1."CardName" || ' - S/Fra. ' || T1."NumAtCard",1,50) from JDT1 T2 inner join OJDT T0 on T0."TransId"=T2."TransId" inner join OPCH T1 on T1."DocEntry"=T0."CreatedBy" and T0."TransType"='18'; update T2 set "LineMemo"=SUBSTRING(T1."CardName" || ' - S/Abo. ' || T1."NumAtCard",1,50) from JDT1 T2 inner join OJDT T0 on T0."TransId"=T2."TransId" inner join ORPC T1 on T1."DocEntry"=T0."CreatedBy" and T0."TransType"='19';