1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71
|
-- Inserts all the DK to export in a temporary keys table.
-- Temporary Table Creation
IF exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DKP_DK_EXPORT_TMP]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DKP_DK_EXPORT_TMP]
CREATE TABLE DKP_DK_EXPORT_TMP
([CUSTOMER_CODE] [varchar] (36) NOT NULL,
[EXTRACTION_DATE] [char] (6) NOT NULL,
[OPE_RF_CODE] [varchar] (15) NOT NULL,
[START_DATE] [char] (6) NOT NULL,
[OPC_CODE] [varchar] (10) NULL,
[DK_VALUE] [int] NULL)
DECLARE @completeDate char(8)
DECLARE @truncDate char(6)
SET @completeDate = CONVERT(char(8), GETDATE(), 112)
SET @truncDate = SUBSTRING(@completeDate, 3, 6)
/* REAL CUSTOMERS, PHYSICAL ORIGIN */
INSERT INTO DKP_DK_EXPORT_TMP
SELECT DK_SET.CUSTOMER_CODE, @truncDate, DK_SET.OPE_RF_CODE, @truncDate, ITEMS.OPC_CODE, CUR.CURRENT_VALUE
FROM DKP_DK_SET DK_SET, DKP_OPE_ITEMS ITEMS, DKP_DK_CURRENT CUR
WHERE DK_SET.CONTINENT_CODE = ?
AND DK_SET.CURRENT_ORIGIN_TYPE = 0 -- physical origin
AND DK_SET.CUSTOMER_CODE IN
(SELECT CUSTOMER_CODE FROM DKP_UF_CUSTOMERS CUSTOMERS
WHERE CONTINENT_CODE = ?
AND CUSTOMER_TYPE = 0) -- real customer
AND DK_SET.LAST_OPE >
(SELECT DATE_VALUE
FROM DKP_PROPERTIES
WHERE PROPERTY_ID = 'LAST_DK_EXPORT_' + ?)
AND ITEMS.OPE_RF_CODE = DK_SET.OPE_RF_CODE
AND CUR.CONTINENT_CODE = ?
AND CUR.CUSTOMER_CODE = DK_SET.CUSTOMER_CODE
AND CUR.OPC_CODE = ITEMS.OPC_CODE
/* REAL CUSTOMERS, VIRTUAL ORIGIN */
INSERT INTO DKP_DK_EXPORT_TMP
SELECT DK_SET.CUSTOMER_CODE, @truncDate, DK_SET.OPE_RF_CODE, @truncDate, ITEMS.OPC_CODE, CUR.CURRENT_VALUE
FROM DKP_DK_SET DK_SET, DKP_UF_CUSTOMERS CUST, DKP_DK_CURRENT CUR, DKP_OPE_ITEMS ITEMS
WHERE DK_SET.CONTINENT_CODE = ?
AND DK_SET.CUSTOMER_CODE = CUST.CUSTOMER_CODE
AND DK_SET.CURRENT_ORIGIN_TYPE = 1 -- virtual origin
AND DK_SET.LAST_OPE >
(SELECT DATE_VALUE
FROM DKP_PROPERTIES
WHERE PROPERTY_ID = 'LAST_DK_EXPORT_' + ?)
AND ITEMS.OPE_RF_CODE = DK_SET.OPE_RF_CODE
AND CUR.CONTINENT_CODE = DK_SET.CONTINENT_CODE
AND CUR.CUSTOMER_CODE = DK_SET.CURRENT_ORIGIN_CODE
AND CUR.OPC_CODE = ITEMS.OPC_CODE
AND CUST.CUSTOMER_TYPE = 0 -- real customer |
Partager