USE [SBO_CARMINA]
GO
/****** Object: StoredProcedure [dbo].[SBO_SP_TransactionNotification] Script Date: 14/11/2017 12:49:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[SBO_SP_TransactionNotification]
@object_type nvarchar(20), -- SBO Object Type
@transaction_type nchar(1), -- [A]dd, [U]pdate, [D]elete, [C]ancel, C[L]ose
@num_of_cols_in_key int,
@list_of_key_cols_tab_del nvarchar(255),
@list_of_cols_val_tab_del nvarchar(255)
AS
begin
-- Return values
declare @error int -- Result (0 for no error)
declare @error_message nvarchar (200) -- Error string to be displayed
select @error = 0
select @error_message = N'Ok'
-- [INTAREX.INICI]
declare @transid int
declare @comentari as nvarchar(50) -- Els camps de comentaris tenen màxim 50 caràcters
-- COBRAMENTS
if @object_type='24' and (@transaction_type='U' or @transaction_type='A')
begin
set @transid=(select transid from OJDT where CreatedBy=@list_of_cols_val_tab_del and TransType=24)
set @comentari=(select N'Cobro '+ cast(t1.DocNum as nvarchar(10)) + N' de ' + t1.CardName from orct t1 where t1.DocEntry=@list_of_cols_val_tab_del)
begin
update OJDT set Memo=@comentari where TransId=@transid
update JDT1 set LineMemo=@comentari where TransId=@transid
end
end
-- PAGAMENTS
if @object_type='46' and (@transaction_type='U' or @transaction_type='A')
begin
set @transid=(select transid from OJDT where CreatedBy=@list_of_cols_val_tab_del and TransType=46)
set @comentari=(select N'Pago '+ cast(t1.DocNum as nvarchar(10)) + N' a ' + t1.CardName from ovpm t1 where t1.DocEntry=@list_of_cols_val_tab_del)
begin
update OJDT set Memo=@comentari where TransId=@transid
update JDT1 set LineMemo=@comentari where TransId=@transid
end
end
-- FACTURES DE VENDA
if @object_type='13' and (@transaction_type='U' or @transaction_type='A')
begin
set @transid=(select transid from OJDT where CreatedBy=@list_of_cols_val_tab_del and TransType=13)
set @comentari=(select N'Factura ' + cast(t1.DocNum as nvarchar(10)) + N' de cliente ' + left(t1.CardName,29) from oinv t1 where t1.DocEntry=@list_of_cols_val_tab_del)
begin
update OINV set JrnlMemo=@comentari where DocEntry=@list_of_cols_val_tab_del
update OJDT set Memo=@comentari where TransId=@transid
update JDT1 set LineMemo=@comentari where TransId=@transid
end
end
-- ABONAMENT DE FACTURES VENDA
if @object_type='14' and (@transaction_type='U' or @transaction_type='A')
begin
set @transid=(select transid from OJDT where CreatedBy=@list_of_cols_val_tab_del and TransType=14)
set @comentari=(select N'Abono ' + cast(t1.DocNum as nvarchar(10)) + N' de cliente ' + left(t1.CardName,29) from orin t1 where t1.DocEntry=@list_of_cols_val_tab_del)
begin
update OINV set JrnlMemo=@comentari where DocEntry=@list_of_cols_val_tab_del
update OJDT set Memo=@comentari where TransId=@transid
update JDT1 set LineMemo=@comentari where TransId=@transid
end
end
-- FACTURES DE PROVEIDORS
if @object_type='18' and (@transaction_type='U' or @transaction_type='A')
begin
set @transid=(select transid from OJDT where CreatedBy=@list_of_cols_val_tab_del and TransType='18')
set @comentari=(select N'Factura ' + cast(t1.DocNum as nvarchar(10)) + N' de Proveedor ' + left(t1.CardName,32) from opch t1 where t1.DocEntry=@list_of_cols_val_tab_del)
begin
update OPCH set JrnlMemo=@comentari where DocEntry=@list_of_cols_val_tab_del
update OJDT set Memo=@comentari where TransId=@transid
update JDT1 set LineMemo=@comentari where TransId=@transid
end
end
--[INTAREX.FINAL]
--actualización de centros de costes para gestion de gastos bancarios en cobros
declare @valor int
declare @costcenter1 as nvarchar(5)
declare @costcenter2 as nvarchar(5)
declare @datasource as nvarchar(1)
if @object_type='24' and (@transaction_type='U' or @transaction_type='A')
begin
set @datasource=(select datasource from orct where docentry=@list_of_cols_val_tab_del)
if @datasource='I'
begin
set @valor=(select transid from OJDT where CreatedBy=@list_of_cols_val_tab_del and TransType='24')
set @costcenter1=(select u_intrx_estruc from orct t1 where t1.DocEntry=@list_of_cols_val_tab_del)
set @costcenter2=(select u_intrx_tien from orct t1 where t1.DocEntry=@list_of_cols_val_tab_del)
update JDT1 set OcrCode2=@costcenter2, profitcode=@costcenter1 where TransId=@valor and TransType='24' and (Account like '6%' or Account like '7%')
end
end
--actualización de centros de costes para gestion de gastos bancarios en pagos
if @object_type='46' and (@transaction_type='U' or @transaction_type='A')
begin
set @datasource=(select datasource from ovpm where docentry=@list_of_cols_val_tab_del)
if @datasource='I'
begin
set @valor=(select transid from OJDT where CreatedBy=@list_of_cols_val_tab_del and TransType='46')
set @costcenter1=(select u_intrx_estruc from ovpm t1 where t1.DocEntry=@list_of_cols_val_tab_del)
set @costcenter2=(select u_intrx_tien from ovpm t1 where t1.DocEntry=@list_of_cols_val_tab_del)
update JDT1 set OcrCode2=@costcenter2, profitcode=@costcenter1 where TransId=@valor and TransType='46' and (Account like '6%' or Account like '7%')
end
end
--controles de facturacion
--imputacion a centros de coste
IF @object_type='13' AND @transaction_type IN ('A','U')
BEGIN
if exists(select distinct docnum from oinv t0
inner join nnm1 t1 on t0.series=t1.Series
inner join ocrd t2 on t0.cardcode=t2.cardcode
where t0.docentry=@list_of_cols_val_tab_del and isnull(t2.U_INTRX_TIPOSERIE,'9999')<>t1.Remark)
begin
set @error=1200
set @error_message='La serie de numeracion del cliente no corresponde a la de la factura'
end
end
IF @object_type='14' AND @transaction_type IN ('A','U')
begin
if exists(select distinct docnum from orin t0
inner join nnm1 t1 on t0.series=t1.Series
inner join ocrd t2 on t0.cardcode=t2.cardcode
where t0.docentry=@list_of_cols_val_tab_del and isnull(t2.U_INTRX_TIPOSERIE,'9999')<>t1.Remark)
begin
set @error=1200
set @error_message='La serie de numeracion del cliente no corresponde a la de la Factura rectificativa'
end
end
IF @object_type='13' AND @transaction_type IN ('A','U')
BEGIN
IF exists(SELECT distinct T0.OcrCode2+t0.OcrCode FROM inv1 T0 WHERE T0.docentry=@list_of_cols_val_tab_del and
(OcrCode2 is null or OcrCode2='') and (OcrCode is null or OcrCode='') )
BEGIN
set @error=1301
set @error_message='Es obligatorio imputar una norma de reparto en la factura'
END
END
--controles de abonos
--imputacion a centros de coste
IF @object_type='14' AND @transaction_type IN ('A','U')
BEGIN
IF exists(SELECT distinct T0.OcrCode2+t0.OcrCode FROM rin1 T0 WHERE T0.docentry=@list_of_cols_val_tab_del and
(OcrCode2 is null or OcrCode2='') and (OcrCode is null or OcrCode='') )
BEGIN
set @error=1401
set @error_message='Es obligatorio imputar una norma de reparto en el abono'
END
END
--[INTAREX.FINAL]
-- =============================================
-- =============================================
-- Author: INTAREX
-- Create date: 28/10/2014
-- Modify date: 28/10/2014
-- Description: <Validacion campos intarex de OF>
-- Version: 1.1
-- =============================================
if @transaction_type='A' and @object_type='202'
begin
declare @ItemCodeCab as nvarchar(50);
declare @LineNum as int;
declare @ItemCode as nvarchar(50);
DECLARE FabricacionCursor CURSOR FOR
SELECT T0.ItemCode,T1.LineNum,T1.ItemCode FROM OWOR T0 JOIN WOR1 T1 on T0.DocEntry=T1.DocEntry WHERE T0.DocEntry=@list_of_cols_val_tab_del;
OPEN FabricacionCursor
FETCH NEXT FROM FabricacionCursor
INTO @ItemCodeCab, @LineNum,@ItemCode
WHILE @@FETCH_STATUS = 0
BEGIN
update WOR1 set U_ARGNS_LinkOper=(SELECT T1.U_ARGNS_LinkOper FROM OITT T0 JOIN ITT1 T1 on T0.Code=T1.Father WHERE T1.Father=@ItemCodeCab and T1.ChildNum=@LineNum and T1.Code=@ItemCode),U_INTRX_FM_E=( SELECT T1.U_INTRX_FM_E FROM OITT T0 JOIN ITT1 T1 on T0.Code=T1.Father WHERE T1.Father=@ItemCodeCab and T1.ChildNum=@LineNum and T1.Code=@ItemCode),U_INTRX_FM_R=( SELECT T1.U_INTRX_FM_R FROM OITT T0 JOIN ITT1 T1 on T0.Code=T1.Father WHERE T1.Father=@ItemCodeCab and T1.ChildNum=@LineNum and T1.Code=@ItemCode) WHERE DocEntry=@list_of_cols_val_tab_del and LineNum=@LineNum
FETCH NEXT FROM FabricacionCursor
INTO @ItemCodeCab, @LineNum,@ItemCode
end
CLOSE FabricacionCursor
DEALLOCATE FabricacionCursor
end
-- Select the return values
select @error, @error_message
end
Visites: 25
