| ROBERT77 |  |
|
2005-09-01 16:04 - Respuestas: 0 - Tema nº: 41628
ALGUIEN PODRIA AYUDARME A DOCUMENTAR ESTE PROCEDIMIENTO ES DECIR A COMENTARIARLO.
GRACIAS
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.OrdersContractsBoxDocDetermine Script Date: 5/7/05 4:21:21 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OrdersContractsBoxDocDetermine]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[OrdersContractsBoxDocDetermine]
GO
/****** Object: Stored Procedure dbo.OrdersContractsBoxDocDetermine Script Date: 12/13/2001 3:09:50 PM ******/
CREATE PROC dbo.OrdersContractsBoxDocDetermine
AS
BEGIN
/**********************************************************
*Procedure Name: OrdersContractsBoxDocDetermine
*Database: ASBPT
*Server: EBVBRSQL05
*SQL-BuilderR3.8
*
*Business Function : Determine box document via OrderContractDocumentSelection and insert the results into OrderContractBoxDoc
*Author BF: JHU Date BF: 10/07/2000
*
*Author:JF Date: 4/27/2003
* Comment: creation
*********************************************************/
SET ANSI_DEFAULTS ON
SET NOCOUNT ON
SET IMPLICIT_TRANSACTIONS OFF
DECLARE @Err int
DELETE OrderContractBoxDoc
SET @Err = @@ERROR
IF @Err 0 GOTO Error
INSERT INTO OrderContractBoxDoc ( DocumentNr,
BoxDocument,
Plant,
UpdateFlag,
ShippingConditions,
CreditCheckStatus,
Currency )
SELECT DISTINCT OCS.DocumentNr,
OCDS.BoxDocument,
OCIS.Plant,
'N' AS UpdateFlag,
OCS.ShippingConditions,
OCS.CreditCheckStatus,
OCS.Currency
FROM OrderContractSummary AS OCS
LEFT OUTER JOIN OrderContractItemSummary AS OCIS
ON OCS.DocumentNr = OCIS.DocumentNr
INNER JOIN OrderContractDocumentSelection AS OCDS
ON OCS.DocumentCategory = OCDS.DocumentCategory
AND OCIS.Plant = OCDS.Plant
WHERE NOT EXISTS (SELECT OCBD.DocumentNr
FROM OrderContractBoxDoc AS OCBD
WHERE OCS.DocumentNr = OCBD.DocumentNr )
SET @Err = @@ERROR
IF @Err 0 GOTO Error
INSERT INTO OrderContractBoxDoc ( DocumentNr,
BoxDocument,
Plant,
UpdateFlag,
ShippingConditions,
CreditCheckStatus,
Currency )
SELECT DISTINCT OCS.DocumentNr,
'NO' AS BoxDocument,
OCIS.Plant,
'N' AS UpdateFlag,
OCS.ShippingConditions,
OCS.CreditCheckStatus,
OCS.Currency
FROM OrderContractSummary AS OCS
LEFT OUTER JOIN OrderContractItemSummary AS OCIS
ON OCS.DocumentNr = OCIS.DocumentNr
WHERE OCS.DocumentCategory IN ('C','G')
AND NOT EXISTS (SELECT OCBD.DocumentNr
FROM OrderContractBoxDoc AS OCBD
WHERE OCS.DocumentNr = OCBD.DocumentNr )
SET @Err = @@ERROR
IF @Err 0 GOTO Error
Get the destination DB
UPDATE OrderContractBoxDoc
SET DestinationDB = PDDB.DestinationDB
FROM OrderContractBoxDoc AS OCBD
INNER JOIN PlantDestinationDB AS PDDB
ON OCBD.Plant = PDDB.Plant
SET @Err = @@ERROR
IF @Err 0 GOTO Error
INSERT INTO ValidationError ( DocumentNr,
Error,
ProcessingTime )
SELECT DISTINCT OCS.DocumentNr,
69 AS Error,
GETDATE() AS ProcessingTime
FROM OrderContractSummary AS OCS
WHERE NOT EXISTS (SELECT OCBD.DocumentNr
FROM OrderContractBoxDoc AS OCBD
WHERE OCS.DocumentNr = OCBD.DocumentNr )
SET @Err = @@ERROR
IF @Err 0 GOTO Error
INSERT INTO ValidationError ( DocumentNr,
Error,
ProcessingTime )
SELECT DISTINCT OCBD.DocumentNr,
79 AS Error,
GETDATE() AS ProcessingTime
FROM OrderContractBoxDoc AS OCBD
WHERE OCBD.DestinationDB IS NULL
SET @Err = @@ERROR
IF @Err 0 GOTO Error
RETURN @@ERROR
Error:
RETURN @Err
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
| |
|
|
|
