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';

 

image_pdf