March 23, 2009 at 7:00 am
Hi,
Transaction Logs(.ldf) size increasing upto 50 GB while a job is running
having loads of Insert,Update,delete,Create commands.
How to optimize the Log size?
Recovery Plan-Simple .
March 23, 2009 at 7:06 am
It's a misunderstanding that the logfile is small under SIMPLE recovery. It seems a large transaction is running in a job. As long as there's no commit, undo information is written to the logfile. After the transaction commits, the claimed data is marked for "ready to be overwritten" (not automatically shrinked).
If this job is running frequently, don't shrink the logfile. That's wasting time and CPU resources (it will grow again the next time the job is running).
So, to answer your question: you have to optimize the job/queries inside the job.
Wilfred
The best things in life are the simple things
March 23, 2009 at 7:07 am
One potential cause is an open transaction which will prevent truncation of the log even in simple recovery mode. DBCC OPENTRAN will tell you if this is the problem.
.
March 23, 2009 at 9:40 am
Ok fine ,
can you pls tell me 3 or 4 general steps to optimize the Job
Can we break the code thru SavePoints or can we braek the job in 5 or 6 separate jobs .
Thanks in advance!!
March 23, 2009 at 9:44 am
Thanks!,
I have checked DBCC OPENTRAN but there is no opened transaction .
Any other thoughts?
March 23, 2009 at 10:07 am
You would need to do this whilst the job was running rather than afterwards.
Can you please post the results of DBCC SQLPERF(LOGSPACE) for the relevant database.
.
March 24, 2009 at 3:46 am
Hi,
DB LogSize(MB) LogspaceUsed(%) Status
SVIDB4.984375 21.37539 0
This is the output of
DBCC SQLPERF(logspace)
but at this time job had been run.
do you want this at the time of running the job.?
March 24, 2009 at 4:04 am
I'm confused! This says to me that your log size is 4.984375 MB and you are using 21% of it.
Where do you get the fact that it has grown to 50GB from?
.
March 24, 2009 at 4:14 am
In our installations, to reduce the needs of LOG space, we encourage the developers to have the transactions as short as be posible.
For example, if you have to delete a table with 1.000.000 records, instead of doing:
BEGIN TRAN
DELETE FROM TABLE
COMMIT TRAN
They make something like this:
DECLARE @NumRecords integer
DECLARE @Error integer
SET @NumRecords = 10000
SET @Error = 0
SET ROWCOUNT TO @NUMRECORDS
WHILE (@NumRecords <> 0) and (@Error = 0)
BEGIN
BEGIN TRAN
DELETE FROM TABLE
SET @NumRecords = @@ROWCOUNT, @Error = @@ERROR
IF @Error <> 0
ROLLBACK TRAN
ELSE
COMMIT TRAN
END
To sum up, the transactions must be as shorter as possible to minimize the impact on the LOG.
March 24, 2009 at 4:34 am
It would probably help if I explain my thought process.
50GB is a very big log on a system in SIMPLE recovery mode, because it usually only has to be big enough to contain a large transaction plus a bit.
Usually (implicit or explicit) transactions cause it to grow, then it checkpoints itself, truncates the log and reaches an equilibrium. Obviously it depends what you are doing but less than 100MB is often the case.
Therefore I was trying to establish whether it just keeps growing for some reason or whether it does truncate on checkpoint. From the figures you gave it is not only truncated but now shrunk as well.
Can you post the code that you think causes this growth?
Also, how big is the database itself?
.
March 24, 2009 at 8:02 am
actually when job is run after that when we see on server there are 2 .ldf files one is around 45 GB and other is 5 GB
and surprisingly it creates tempdb.mdf which is 45 GB in itself
may be the log size I have sent to you is different thing from these transcation logs(I am not sure)
March 24, 2009 at 8:10 am
sorry but I can't understand!
March 24, 2009 at 8:15 am
Size- 108874.88 MB
Code-
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
/********************************************************************************
ALTER PROCEDURE [dbo].[sprFinData_Batch]
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRY
BEGIN TRAN
--REMOVE OLD DATA BEFORE INSETING FRESH DATA/Added For deleting the Aggregated table
/*
DELETE dbo.FinData_Batch_BKP
DELETE dbo.[FinData_Batch_PG_SUPP_BKP]
DELETE dbo.[FinData_Batch_PG_BRD_BKP]
DELETE dbo.[FinData_Batch_SPG_SUPP_BKP]
DELETE dbo.[FinData_Batch_SPG_BRD_BKP]
DELETE dbo.[FinData_Batch_MA_SUPP_BKP]
DELETE dbo.[FinData_Batch_MA_BRD_BKP]
DELETE dbo.[CEP_DM_WEEKSCOVER_TA_BKP]
DELETE dbo.[CEP_DM_WEEKSCOVER_PG_BRD_BKP]
DELETE dbo.[CEP_DM_WEEKSCOVER_SPG_BRD_BKP]
DELETE dbo.[CEP_DM_WEEKSCOVER_MA_BRD_BKP]
DELETE dbo.[CEP_DM_WEEKSCOVER_SPG_SUPP_BKP]
DELETE dbo.[CEP_DM_WEEKSCOVER_PG_SUPP_BKP]
DELETE dbo.[CEP_DM_WEEKSCOVER_MA_SUPP_BKP]
*/
TRUNCATE TABLE FINDATA_BATCH_BKP
TRUNCATE TABLE FINDATA_BATCH_PG_SUPP_BKP
TRUNCATE TABLE FINDATA_BATCH_PG_BRD_BKP
TRUNCATE TABLE FINDATA_BATCH_SPG_SUPP_BKP
TRUNCATE TABLE FINDATA_BATCH_SPG_BRD_BKP
TRUNCATE TABLE FINDATA_BATCH_MA_SUPP_BKP
TRUNCATE TABLE FINDATA_BATCH_MA_BRD_BKP
TRUNCATE TABLE CEP_DM_WEEKSCOVER_TA
TRUNCATE TABLE CEP_DM_WEEKSCOVER_PG_BRD
TRUNCATE TABLE CEP_DM_WEEKSCOVER_SPG_BRD
TRUNCATE TABLE CEP_DM_WEEKSCOVER_MA_BRD
TRUNCATE TABLE CEP_DM_WEEKSCOVER_SPG_SUPP
TRUNCATE TABLE CEP_DM_WEEKSCOVER_PG_SUPP
TRUNCATE TABLE CEP_DM_WEEKSCOVER_MA_SUPP
TRUNCATE TABLE APVENMAST_TERMS1
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('MAT PROCEDURES START',GETDATE())
exec FIN_RTS_MAT --24 sec
exec FIN_SAL_MAT --3:17 Sec
exec FIN_TAR_MAT -- 0:2 sec
INSERT INTO FINDATA_BATCH_TRACE VALUES ('MAT PROCEDURES FINISH',GETDATE())
--INSERT FRESH DATA INTO FinData_Batch TABLE
INSERT INTO FINDATA_BATCH_TRACE VALUES ('DROP INDEX START',GETDATE())
BEGIN
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[FinData_Batch_bkp]') AND name = N'idx_FinData_Batch_bkp_test')
DROP INDEX idx_FinData_Batch_bkp_test ON FinData_Batch_BKP
END
BEGIN
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[FinData_Batch_bkp]') AND name = N'IK_FINDATA_FNCL_PRD')
DROP INDEX IK_FINDATA_FNCL_PRD ON FinData_Batch_BKP
END
----Index issue 447
BEGIN
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[FinData_Batch_bkp]') AND name = N'idx_FinData_Batch_ma_bd')
DROP INDEX idx_FinData_Batch_ma_bd ON FinData_Batch_BKP
END
-------------------
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FINDATA INSERT START',GETDATE())
INSERT INTO
dbo.FinData_Batch_bkp(
FNCL_PD,
PROD_CD,
PROD_TYPE,
PROD_SGMT_CD,
TRD_ARM_CD,
BRND_CD,
PREF_PRCH_SUPP_CD,
MER_ARA_CD,
PLAN_GRP_CD,
SUB_PLAN_GRP_CD,
PNM_VAL,
TRD_MGN_VAL,
TRADING_ACCT_REV,
ADV_PROMOTION,
REBATES,
OTHERS,
EURO_OVERRIDER,
ACHD_RTL_VAL,
SAL_QTY,
PLU_RTL_VAL,
CST_OF_SAL_VAL,
TAB_MGN_RVN_VAL,
TAB_MGN_VAL,
COP_ADJ_VAL,
UBC_ADJ_VAL,
CVPN_ADJ_VAL,
DSCT_VAL,
SAL_TAX_VAL,
SAL_TAX_NEW_VAL,
CO_QTY_DLVY_ON_TM,
CO_INIT_CALL_OFF_QTY,
CO_ORIG_CNT_DLVY,
CO_ORIG_ON_TM,
CO_IN_FULL_DLVY_QTY,
CO_IN_FULL_CALL_OFF_QTY,
STM_MAT_CST_OF_SAL_VAL,
STM_AVG_STK_CST_VAL,
LDC_AVAIL_CNT,
LDC_CNT,
Smoothed_TAR,
DPP_TAR,
APPOR_BRANCH_COST,
APPOR_SPACE,
APPOR_FIXED_DC,
Smoothed_DPP,
Actual_DPP,
WKLY_IN_STK,
WKLY_BR_PRD_CNT,
WKLY_F1_UBX,
WKLY_SS_UBX,
SFA_FCAST_5_QTY,
SFA_ABS_DIFF_5_QTY,
QUASI_UBC,
CPR_UBC,
SMOOTHED_MAT_TAR
)
--FETCH DESIRED DATA FROM JOINING ALL THE REQUIRED TABLES JOINING WITH PROD TABLE
SELECT FIN.FNCL_PD,
FIN.PROD_CD,
CASE WHEN (PDST_ARA_CD = '02'
AND PDST_TYP_CD = '01' AND PDST_CD = '0001')
THEN 'OEM' ELSE 'BRND' END AS PROD_TYPE,
PRD.PROD_SGMT_CD,
FIN.TRD_ARM_CD,
PRD.BRND_CD,
PRD.PREF_PRCH_SUPP_CD,
PRD.MER_ARA_CD,
PRD.PLAN_GRP_CD,
PRD.SUB_PLAN_GRP_CD,
FIN.PNM_VAL,
FIN.TRD_MGN_VAL,
FIN.TRADING_ACCT_REV,
FIN.ADV_PROMOTION,
FIN.REBATES,
FIN.OTHERS,
FIN.EURO_OVERRIDER,
FIN.ACHD_RTL_VAL,
FIN.SAL_QTY,
FIN.PLU_RTL_VAL,
FIN.CST_OF_SAL_VAL,
FIN.TAB_MGN_RVN_VAL,
FIN.TAB_MGN_VAL,
FIN.COP_ADJ_VAL,
FIN.UBC_ADJ_VAL,
FIN.CVPN_ADJ_VAL,
FIN.DSCT_VAL,
FIN.SAL_TAX_VAL,
FIN.SAL_TAX_NEW_VAL,
FIN.CO_QTY_DLVY_ON_TM,
FIN.CO_INIT_CALL_OFF_QTY,
FIN.CO_ORIG_CNT_DLVY,
FIN.CO_ORIG_ON_TM,
FIN.CO_IN_FULL_DLVY_QTY,
FIN.CO_IN_FULL_CALL_OFF_QTY,
FIN.STM_MAT_CST_OF_SAL_VAL,
FIN.STM_AVG_STK_CST_VAL,
FIN.LDC_AVAIL_CNT,
FIN.LDC_CNT,
FIN.Smoothed_TAR,
FIN.DPP_TAR,
FIN.APPOR_BRANCH_COST,
FIN.APPOR_SPACE,
FIN.APPOR_FIXED_DC,
FIN.Smoothed_DPP,
FIN.Actual_DPP,
FIN.WKLY_IN_STK,
FIN.WKLY_BR_PRD_CNT,
FIN.WKLY_F1_UBX,
FIN.WKLY_SS_UBX,
FIN.SFA_FCAST_5_QTY,
FIN.SFA_ABS_DIFF_5_QTY,
FIN.QUASI_UBC,
FIN.CPR_UBC,
FIN.SMOOTHED_MAT_TAR
FROM
(
SELECT
FININ.FNCL_PD FNCL_PD,
FININ.PROD_CD PROD_CD,
FININ.TRD_ARM_CD TRD_ARM_CD,
SUM(ISNULL(FININ.PNM_VAL,0)) PNM_VAL,
SUM(ISNULL(FININ.TRD_MGN_VAL,0)) TRD_MGN_VAL,
SUM(ISNULL(FININ.TRADING_ACCT_REV,0)) TRADING_ACCT_REV,
SUM(ISNULL(FININ.ADV_PROMOTION,0)) ADV_PROMOTION,
SUM(ISNULL(FININ.REBATES,0)) REBATES,
SUM(ISNULL(FININ.OTHERS,0)) OTHERS,
SUM(ISNULL(FININ.EURO_OVERRIDER,0)) EURO_OVERRIDER,
SUM(ISNULL(FININ.ACHD_RTL_VAL,0)) ACHD_RTL_VAL,
SUM(ISNULL(FININ.SAL_QTY,0)) SAL_QTY,
SUM(ISNULL(FININ.PLU_RTL_VAL,0)) PLU_RTL_VAL,
SUM(ISNULL(FININ.CST_OF_SAL_VAL,0)) CST_OF_SAL_VAL,
SUM(ISNULL(FININ.TAB_MGN_RVN_VAL,0)) TAB_MGN_RVN_VAL,
SUM(ISNULL(FININ.TAB_MGN_VAL,0)) TAB_MGN_VAL,
SUM(ISNULL(FININ.COP_ADJ_VAL,0)) COP_ADJ_VAL,
SUM(ISNULL(FININ.UBC_ADJ_VAL,0)) UBC_ADJ_VAL,
SUM(ISNULL(FININ.CVPN_ADJ_VAL,0)) CVPN_ADJ_VAL,
SUM(ISNULL(FININ.DSCT_VAL,0)) DSCT_VAL,
SUM(ISNULL(FININ.SAL_TAX_VAL,0)) SAL_TAX_VAL,
SUM(ISNULL(FININ.SAL_TAX_NEW_VAL,0)) SAL_TAX_NEW_VAL,
SUM(ISNULL(FININ.CO_QTY_DLVY_ON_TM,0)) CO_QTY_DLVY_ON_TM,
SUM(ISNULL(FININ.CO_INIT_CALL_OFF_QTY,0)) CO_INIT_CALL_OFF_QTY,
SUM(ISNULL(FININ.CO_ORIG_CNT_DLVY,0)) CO_ORIG_CNT_DLVY,
SUM(ISNULL(FININ.CO_ORIG_ON_TM,0)) CO_ORIG_ON_TM,
SUM(ISNULL(FININ.CO_IN_FULL_DLVY_QTY,0)) CO_IN_FULL_DLVY_QTY,
SUM(ISNULL(FININ.CO_IN_FULL_CALL_OFF_QTY,0)) CO_IN_FULL_CALL_OFF_QTY,
SUM(ISNULL(FININ.STM_MAT_CST_OF_SAL_VAL,0)) STM_MAT_CST_OF_SAL_VAL,
SUM(ISNULL(FININ.STM_AVG_STK_CST_VAL,0)) STM_AVG_STK_CST_VAL,
SUM(ISNULL(FININ.LDC_AVAIL_CNT,0)) LDC_AVAIL_CNT,
SUM(ISNULL(FININ.LDC_CNT,0)) LDC_CNT,
SUM(ISNULL(FININ.Smoothed_TAR,0)) Smoothed_TAR,
SUM(ISNULL(FININ.DPP_TAR,0)) DPP_TAR,
SUM(ISNULL(FININ.APPOR_BRANCH_COST,0)) APPOR_BRANCH_COST,
SUM(ISNULL(FININ.APPOR_SPACE,0)) APPOR_SPACE,
SUM(ISNULL(FININ.APPOR_FIXED_DC,0)) APPOR_FIXED_DC,
SUM(ISNULL(FININ.Smoothed_DPP,0)) Smoothed_DPP,
SUM(ISNULL(FININ.Actual_DPP,0)) Actual_DPP,
SUM(ISNULL(FININ.WKLY_IN_STK,0))WKLY_IN_STK,
SUM(ISNULL(FININ.WKLY_BR_PRD_CNT,0))WKLY_BR_PRD_CNT,
SUM(ISNULL(FININ.WKLY_F1_UBX,0))WKLY_F1_UBX,
SUM(ISNULL(FININ.WKLY_SS_UBX,0))WKLY_SS_UBX,
SUM(ISNULL(FININ.SFA_FCAST_5_QTY,0))SFA_FCAST_5_QTY,
SUM(ISNULL(FININ.SFA_ABS_DIFF_5_QTY,0))SFA_ABS_DIFF_5_QTY,
SUM(ISNULL(FININ.QUASI_UBC,0)) QUASI_UBC,
SUM(ISNULL(FININ.CPR_UBC,0)) CPR_UBC,
SUM(ISNULL(FININ.SMOOTHED_MAT_TAR,0))SMOOTHED_MAT_TAR
FROM
--FETCH DATA FROM ALL THE REQUIRED TABLES
(
SELECT COALESCE(SALSQ.FNCL_PD,PNMSQ.FNCL_PD,CEPSQ.FNCL_PD,DPPSQ.FNCL_PD,CEP.FNCL_PD,SALUBCSQ.FNCL_PD,CDS.FNCL_PD,STC.FNCL_PD) FNCL_PD,
COALESCE(SALSQ.PROD_CD,PNMSQ.PROD_CD,CEPSQ.PROD_CD,DPPSQ.PROD_CD,CEP.PROD_CD,SALUBCSQ.PROD_CD,CDS.PROD_CD,STC.PROD_CD) PROD_CD,
COALESCE(SALSQ.TRD_ARM_CD,PNMSQ.TRD_ARM_CD,CEPSQ.TRD_ARM_CD,DPPSQ.TRD_ARM_CD,CEP.TRD_ARM_CD,SALUBCSQ.TRD_ARM_CD,CDS.TRD_ARM_CD,STC.TRD_ARM_CD) TRD_ARM_CD,
PNM_VAL,
TRD_MGN_VAL,
TRADING_ACCT_REV,
ADV_PROMOTION,
REBATES,
OTHERS,
EURO_OVERRIDER,
ACHD_RTL_VAL,
SAL_QTY,
PLU_RTL_VAL,
CST_OF_SAL_VAL,
TAB_MGN_RVN_VAL,
TAB_MGN_VAL,
COP_ADJ_VAL,
UBC_ADJ_VAL,
CVPN_ADJ_VAL,
DSCT_VAL,
SAL_TAX_VAL,
SAL_TAX_NEW_VAL,
CO_QTY_DLVY_ON_TM,
CO_INIT_CALL_OFF_QTY,
CO_ORIG_CNT_DLVY,
CO_ORIG_ON_TM,
CO_IN_FULL_DLVY_QTY,
CO_IN_FULL_CALL_OFF_QTY,
STM_MAT_CST_OF_SAL_VAL,
STM_AVG_STK_CST_VAL,
LDC_AVAIL_CNT,
LDC_CNT,
Smoothed_TAR,
DPP_TAR,
APPOR_BRANCH_COST,
APPOR_SPACE,
APPOR_FIXED_DC,
Smoothed_DPP,
Actual_DPP,
WKLY_IN_STK,
WKLY_BR_PRD_CNT,
WKLY_F1_UBX,
WKLY_SS_UBX,
SFA_ABS_DIFF_5_QTY,
SFA_FCAST_5_QTY,
QUASI_UBC,
CPR_UBC,
SMOOTHED_MAT_TAR
FROM
(
--FETCH DATA FROM SAL_UBC_PCW
SELECT
SALUBC.FNCL_PD AS FNCL_PD,
SALUBC.PROD_CD AS PROD_CD ,
CH.TRD_ARM_CD AS TRD_ARM_CD,
SUM(ISNULL(SALUBC.QUASI_UBC,0)) AS QUASI_UBC,
SUM(ISNULL(SALUBC.CPR_UBC,0)) AS CPR_UBC
FROM
SAL_UBC_PCW SALUBC,CHAIN CH
WHERE
SALUBC.CHN_CD=CH.CHN_CD
GROUP BY
SALUBC.FNCL_PD,
SALUBC.PROD_CD,
CH.TRD_ARM_CD
) SALUBCSQ
--FULL OUTER JOIN WITH SAL_PCW
FULL OUTER JOIN
(
--FETCH DATA FROM SAL_PCW
SELECT
SAL.FNCL_PD AS FNCL_PD,
SAL.PROD_CD AS PROD_CD ,
CH.TRD_ARM_CD AS TRD_ARM_CD,
SUM(ISNULL(SAL.ACHD_RTL_VAL,0)) AS ACHD_RTL_VAL,
SUM(ISNULL(SAL.SAL_QTY,0)) AS SAL_QTY,
SUM(ISNULL(SAL.PLU_RTL_VAL,0)) AS PLU_RTL_VAL,
SUM(ISNULL(SAL.CST_OF_SAL_VAL,0)) AS CST_OF_SAL_VAL,
SUM(ISNULL(SAL.TAB_MGN_RVN_VAL,0)) AS TAB_MGN_RVN_VAL,
SUM(ISNULL(SAL.TAB_MGN_VAL,0)) AS TAB_MGN_VAL,
SUM(ISNULL(SAL.COP_ADJ_VAL,0)) AS COP_ADJ_VAL,
SUM(ISNULL(SAL.UBC_ADJ_VAL,0)) AS UBC_ADJ_VAL,
SUM(ISNULL(SAL.CVPN_ADJ_VAL,0)) AS CVPN_ADJ_VAL,
SUM(ISNULL(SAL.DSCT_VAL,0)) AS DSCT_VAL,
SUM(ISNULL(SAL.SAL_TAX_VAL,0)) AS SAL_TAX_VAL,
SUM(ISNULL(SAL.SAL_TAX_NEW_VAL,0)) AS SAL_TAX_NEW_VAL
FROM
SAL_PCW SAL,CHAIN CH
WHERE
SAL.CHN_CD=CH.CHN_CD
GROUP BY
SAL.FNCL_PD,
SAL.PROD_CD,
CH.TRD_ARM_CD
) SALSQ
ON (SALUBCSQ.FNCL_PD=SALSQ.FNCL_PD
AND SALUBCSQ.PROD_CD=SALSQ.PROD_CD
AND SALUBCSQ.TRD_ARM_CD=SALSQ.TRD_ARM_CD
)
--FULL OUTER JOIN1 WITH PNM_DATAMART
FULL OUTER JOIN
(
--FETCH DATA FROM PNM_DATAMART
SELECT
PNM.FNCL_PD AS FNCL_PD,
PNM.PROD_CD AS PROD_CD ,
PNM.TRD_ARM_CD AS TRD_ARM_CD,
SUM(ISNULL(PNM.PNM_VAL,0)) AS PNM_VAL,
SUM(ISNULL(PNM.TRD_MGN_VAL,0)) AS TRD_MGN_VAL,
SUM(CASE WHEN PNM.SUB_CPNT_CD IN ('070101','070103','070201','070203','070204','070301','070303','070304','070401','070403','070404','070102','070202','070302','070402','070205','070305','070405','070501','070502') THEN ISNULL(PNM.PNM_VAL,0) ELSE 0 END ) AS TRADING_ACCT_REV,
SUM(CASE WHEN PNM.SUB_CPNT_CD IN ('070101','070103','070201','070203','070204','070301','070303','070304','070401','070403','070404') THEN ISNULL(PNM.PNM_VAL,0) ELSE 0 END ) AS ADV_PROMOTION,
SUM(CASE WHEN PNM.SUB_CPNT_CD IN ('070102','070202','070302','070402') THEN ISNULL(PNM.PNM_VAL,0) ELSE 0 END ) AS REBATES,
SUM(CASE WHEN PNM.SUB_CPNT_CD IN ('070205','070305','070405','070501','070502') THEN ISNULL(PNM.PNM_VAL,0) ELSE 0 END ) AS OTHERS,
SUM(CASE WHEN PNM.SUB_CPNT_CD IN ('070205') THEN ISNULL(PNM.PNM_VAL,0) ELSE 0 END ) AS EURO_OVERRIDER
FROM
PNM_DATAMART PNM
GROUP BY
PNM.FNCL_PD,
PNM.PROD_CD,
PNM.TRD_ARM_CD
) PNMSQ
ON (
--FULL OUTER JOIN CONDITION1 WITH PNM_DATAMART
SALSQ.FNCL_PD=PNMSQ.FNCL_PD
AND SALSQ.PROD_CD=PNMSQ.PROD_CD
AND SALSQ.TRD_ARM_CD=PNMSQ.TRD_ARM_CD )
--FULL OUTER JOIN3 WITH CEP_DM
FULL OUTER JOIN
(
--FETCH DATA FROM CEP_DM
SELECT CEP.FNCL_PD AS FNCL_PD,
CEP.PROD_CD AS PROD_CD ,
CH.TRD_ARM_CD AS TRD_ARM_CD,
SUM(ISNULL(CEP.CO_QTY_DLVY_ON_TM,0)) AS CO_QTY_DLVY_ON_TM,
SUM(ISNULL(CEP.CO_INIT_CALL_OFF_QTY,0)) AS CO_INIT_CALL_OFF_QTY,
SUM(ISNULL(CEP.CO_ORIG_CNT_DLVY,0)) AS CO_ORIG_CNT_DLVY ,
SUM(ISNULL(CEP.CO_ORIG_ON_TM,0)) AS CO_ORIG_ON_TM ,
SUM(ISNULL(CEP.CO_IN_FULL_DLVY_QTY,0)) AS CO_IN_FULL_DLVY_QTY ,
SUM(ISNULL(CEP.CO_IN_FULL_CALL_OFF_QTY,0)) AS CO_IN_FULL_CALL_OFF_QTY,
SUM(ISNULL(CEP.STM_MAT_CST_OF_SAL_VAL,0)) AS STM_MAT_CST_OF_SAL_VAL,
SUM(ISNULL(CEP.STM_AVG_STK_CST_VAL,0)) AS STM_AVG_STK_CST_VAL,
SUM(ISNULL(CEP.LDC_AVAIL_CNT,0)) AS LDC_AVAIL_CNT ,
SUM(ISNULL(CEP.LDC_CNT,0)) AS LDC_CNT
FROM
CEP_DM CEP INNER JOIN CHAIN CH
ON CEP.CHN_CD=CH.CHN_CD
GROUP BY
CEP.FNCL_PD,
CEP.PROD_CD,
CH.TRD_ARM_CD
) CEPSQ
ON (
--FULL OUTER JOIN CONDITION3 WITH CEP_DM
PNMSQ.FNCL_PD=CEPSQ.FNCL_PD
AND PNMSQ.PROD_CD=CEPSQ.PROD_CD
AND PNMSQ.TRD_ARM_CD=CEPSQ.TRD_ARM_CD )
--FULL OUTER JOIN4 WITH DPP_DATA
FULL OUTER JOIN
(
--FETCH DATA FROM DPP_DATA
SELECT DPP.FNCL_PD AS FNCL_PD,
DPP.PROD_CD AS PROD_CD,
DPP.TRD_ARM_CD AS TRD_ARM_CD,
ISNULL(DPP.Smoothed_TAR,0) AS Smoothed_TAR,
ISNULL(DPP.TAR,0) AS DPP_TAR,
ISNULL(DPP.APPOR_BRANCH_COST,0) AS APPOR_BRANCH_COST,
ISNULL(DPP.APPOR_SPACE,0) AS APPOR_SPACE,
ISNULL(DPP.APPOR_FIXED_DC,0) AS APPOR_FIXED_DC,
ISNULL(DPP.Smoothed_DPP,0) AS Smoothed_DPP,
ISNULL(DPP.Actual_DPP,0) AS Actual_DPP
FROM
DPP_DATA DPP
) DPPSQ
ON (
--FULL OUTER JOIN CONDITION4 WITH DPP_DATA
PNMSQ.FNCL_PD=DPPSQ.FNCL_PD
AND PNMSQ.PROD_CD=DPPSQ.PROD_CD
AND PNMSQ.TRD_ARM_CD=DPPSQ.TRD_ARM_CD )
--FULL OUTER JOIN5 WITH CEP_BCA
FULL OUTER JOIN
(
--FETCH DATA FROM CEP_BCA
SELECT
CEP.FNCL_PD AS FNCL_PD,
CEP.PROD_CD AS PROD_CD,
CH.TRD_ARM_CD AS TRD_ARM_CD,
SUM(ISNULL(CEP.WKLY_IN_STK,0)) AS WKLY_IN_STK,
SUM(ISNULL(CEP.WKLY_BR_PRD_CNT,0))AS WKLY_BR_PRD_CNT,
SUM(ISNULL(CEP.WKLY_F1_UBX,0)) AS WKLY_F1_UBX,
SUM(ISNULL(CEP.WKLY_SS_UBX,0)) AS WKLY_SS_UBX
FROM
CEP_BCA CEP INNER JOIN CHAIN CH
ON CEP.CHN_CD=CH.CHN_CD
GROUP BY
CEP.FNCL_PD,
CEP.PROD_CD,
CH.TRD_ARM_CD
) CEP
ON (
--FULL OUTER JOIN CONDITION5 WITH CEP_BCA
PNMSQ.FNCL_PD=CEP.FNCL_PD
AND PNMSQ.PROD_CD=CEP.PROD_CD
AND PNMSQ.TRD_ARM_CD=CEP.TRD_ARM_CD )
--FULL OUTER JOIN6 WITH CEP_DM_SFE
FULL OUTER JOIN
(
--FETCH DATA FROM CEP_DM_SFE
SELECT
CDS.FNCL_PD AS FNCL_PD,
CDS.PROD_CD AS PROD_CD,
CH.TRD_ARM_CD AS TRD_ARM_CD,
SUM(ISNULL(SFA_FCAST_5_QTY,0)) AS SFA_FCAST_5_QTY,
SUM(ISNULL(SFA_ABS_DIFF_5_QTY,0)) AS SFA_ABS_DIFF_5_QTY
FROM
CEP_DM_SFE CDS INNER JOIN CHAIN CH
ON CDS.CHN_CD=CH.CHN_CD
GROUP BY
CDS.FNCL_PD,
CDS.PROD_CD,
CH.TRD_ARM_CD
) CDS
ON (
--FULL OUTER JOIN CONDITION6 WITH CEP_DM_SFE
PNMSQ.FNCL_PD=CDS.FNCL_PD
AND PNMSQ.PROD_CD=CDS.PROD_CD
AND PNMSQ.TRD_ARM_CD=CDS.TRD_ARM_CD )
--FULL OUTER JOIN7 WITH SMOOTHED_MAT_TAR_RESULT FOr Smoothed Tar Calculation
FULL OUTER JOIN
(
--FETCH DATA FROM SMOOTHED_MAT_TAR_RESULT
SELECT
ST.FNCL_PD AS FNCL_PD,
ST.PROD_CD AS PROD_CD,
ST.TRD_ARM_CD AS TRD_ARM_CD,
SUM(ISNULL(FACT_APPOR,0)) AS SMOOTHED_MAT_TAR
FROM
SMOOTHED_MAT_TAR_RESULT ST
GROUP BY
ST.FNCL_PD,
ST.PROD_CD,
ST.TRD_ARM_CD
) STC
ON (
--FULL OUTER JOIN CONDITION6 WITH CEP_DM_SFE
PNMSQ.FNCL_PD=STC.FNCL_PD
AND PNMSQ.PROD_CD=STC.PROD_CD
AND PNMSQ.TRD_ARM_CD=STC.TRD_ARM_CD )
)FININ
GROUP BY
FININ.FNCL_PD,
FININ.PROD_CD,
FININ.TRD_ARM_CD
) FIN
--INNER JOIN WITH PROD TABLE
INNER JOIN Prod PRD ON
FIN.PROD_CD=PRD.PROD_CD
LEFT OUTER JOIN PRODUCT_SET_MEMBER P ON
FIN.PROD_CD=P.PROD_CD
END --58 min (13396241 row(s) affected)
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FINDATA INSERT FINISHED',GETDATE())
BEGIN
CREATE NONCLUSTERED INDEX [idx_FinData_Batch_bkp_test] ON [dbo].[FinData_Batch_BKP]
(
[PREF_PRCH_SUPP_CD] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
END
BEGIN
CREATE NONCLUSTERED INDEX [IK_FINDATA_FNCL_PRD] ON [dbo].[FinData_Batch_BKP]
(
[FNCL_PD] ASC,
[PROD_CD] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
END
INSERT INTO FINDATA_BATCH_TRACE VALUES ('INDEX CREATED',GETDATE())
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN UPDATE FROM OBS STARTED',GETDATE())
UPDATE FinData_Batch_bkp
SET RCV_CST_PRC = ORD.RCV_CST_PRC,
QTY_RCV = ORD.QTY_RCV,
PUR_CST_INTAKE = ORD.PUR_CST_INTAKE
FROM
FinData_Batch_bkp FIN1,
(SELECT
OBS.FNCL_PD,
OBS.PROD_CD,
SUM(ISNULL(OBS.RCV_CST_PRC,0)) RCV_CST_PRC,
SUM(ISNULL(OBS.QTY_RCV,0)) QTY_RCV,
SUM(ISNULL(OBS.PUR_CST_INTAKE,0)) PUR_CST_INTAKE
FROM
ORDER_BOOK_SNAPSHOT OBS
GROUP BY
OBS.fncl_pd,
OBS.prod_cd) ORD
WHERE
FIN1.fncl_pd = ORD.fncl_pd
and FIN1.prod_cd=ORD.prod_cd
and FIN1.trd_arm_cd=(SELECT
MAX(FIN2.trd_arm_cd)
FROM
FinData_Batch_bkp FIN2
WHERE
FIN2.fncl_pd=ORD.fncl_pd
and FIN2.prod_cd=ORD.prod_cd)
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN UPDATE FROM OBS FINISHED',GETDATE())
END--16:30 second (436264 row(s) affected)
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN INSERT FROM OBS',GETDATE())
INSERT INTO
FinData_Batch_bkp
(
FNCL_PD,
PROD_CD,
PROD_TYPE,
PROD_SGMT_CD,
BRND_CD,
PREF_PRCH_SUPP_CD,
MER_ARA_CD,
PLAN_GRP_CD,
SUB_PLAN_GRP_CD,
RCV_CST_PRC,
QTY_RCV,
PUR_CST_INTAKE
)
SELECT
OBS.FNCL_PD FNCL_PD,
OBS.PROD_CD PROD_CD,
CASE
WHEN (PDST_ARA_CD = '02' and PDST_TYP_CD = '01' and PDST_CD = '0001')
THEN 'OEM'
ELSE 'BRND'
END AS PROD_TYPE,
PRD.PROD_SGMT_CD AS PROD_SGMT_CD,
PRD.BRND_CD BRND_CD,
PRD.PREF_PRCH_SUPP_CD PREF_PRCH_SUPP_CD,
PRD.MER_ARA_CD MER_ARA_CD,
PRD.PLAN_GRP_CD PLAN_GRP_CD,
PRD.SUB_PLAN_GRP_CD SUB_PLAN_GRP_CD,
OBS.RCV_CST_PRC RCV_CST_PRC,
OBS.QTY_RCV QTY_RCV,
OBS.PUR_CST_INTAKE PUR_CST_INTAKE
FROM
(
SELECT
ORD.FNCL_PD FNCL_PD,
ORD.PROD_CD PROD_CD,
SUM(ISNULL(ORD.RCV_CST_PRC,0)) RCV_CST_PRC,
SUM(ISNULL(ORD.QTY_RCV,0)) QTY_RCV,
SUM(ISNULL(ORD.PUR_CST_INTAKE,0)) PUR_CST_INTAKE
FROM
ORDER_BOOK_SNAPSHOT ORD
WHERE
NOT EXISTS (SELECT 1
FROM
FinData_Batch_bkp FIN
WHERE
FIN.FNCL_PD=ORD.FNCL_PD
AND FIN.PROD_CD=ORD.PROD_CD)
GROUP BY
ORD.FNCL_PD,
ORD.PROD_CD
) OBS
INNER JOIN PROD PRD
ON OBS.PROD_CD=PRD.PROD_CD
LEFT OUTER JOIN PRODUCT_SET_MEMBER P ON
PRD.PROD_CD=P.PROD_CD
--INSERT FROM MASTER_CARE TABLE
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN INSERT FROM OBS FINISHED',GETDATE())
END--1:24 sec (1742 row(s) affected)
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN UPDATE FROM MASTERCARE_PRODUCT ',GETDATE())
UPDATE FinData_Batch_bkp
SET PRED_FR = MAC.PRED_FR ,
RETURNS_RATE = MAC.RETURNS_RATE
FROM
FinData_Batch_bkp FIN1,
(SELECT
MC.FNCL_PD,
MC.PROD_CD,
SUM(ISNULL(MC.PRED_FR ,0)) PRED_FR ,
SUM(ISNULL(MC.RETURNS_RATE ,0)) RETURNS_RATE
FROM
MASTERCARE_PRODUCT MC
GROUP BY
MC.fncl_pd,
MC.prod_cd) MAC
WHERE
FIN1.fncl_pd = MAC.fncl_pd
and FIN1.prod_cd=MAC.prod_cd
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN UPDATE FROM MASTERCARE_PRODUCT FINISH',GETDATE())
END--13 sec (31087 row(s) affected)
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN INSERT FROM MASTERCARE_PRODUCT ',GETDATE())
INSERT INTO
FinData_Batch_bkp
(
FNCL_PD,
PROD_CD,
PROD_TYPE,
PROD_SGMT_CD,
BRND_CD,
PREF_PRCH_SUPP_CD,
MER_ARA_CD,
PLAN_GRP_CD,
SUB_PLAN_GRP_CD,
PRED_FR ,
RETURNS_RATE
)
SELECT
MAC.FNCL_PD FNCL_PD,
MAC.PROD_CD PROD_CD,
CASE
WHEN (PDST_ARA_CD = '02' and PDST_TYP_CD = '01' and PDST_CD = '0001')
THEN 'OEM'
ELSE 'BRND'
END AS PROD_TYPE,
PRD.PROD_SGMT_CD AS PROD_SGMT_CD,
PRD.BRND_CD BRND_CD,
PRD.PREF_PRCH_SUPP_CD PREF_PRCH_SUPP_CD,
PRD.MER_ARA_CD MER_ARA_CD,
PRD.PLAN_GRP_CD PLAN_GRP_CD,
PRD.SUB_PLAN_GRP_CD SUB_PLAN_GRP_CD,
MAC.PRED_FR,
MAC.RETURNS_RATE
FROM
(
SELECT
MC.FNCL_PD FNCL_PD,
MC.PROD_CD PROD_CD,
SUM(ISNULL(MC.PRED_FR,0)) PRED_FR ,
SUM(ISNULL(MC.RETURNS_RATE ,0)) RETURNS_RATE
FROM
MASTERCARE_PRODUCT MC
WHERE
NOT EXISTS (SELECT 1
FROM
FinData_Batch_bkp FIN
WHERE
FIN.FNCL_PD=MC.FNCL_PD
AND FIN.PROD_CD=MC.PROD_CD)
GROUP BY
MC.FNCL_PD,
MC.PROD_CD
) MAC
INNER JOIN PROD PRD
ON MAC.PROD_CD=PRD.PROD_CD
LEFT OUTER JOIN PRODUCT_SET_MEMBER P ON
PRD.PROD_CD=P.PROD_CD
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN INSERT FROM MASTERCARE_PRODUCT FINISHED',GETDATE())
END --40 seconds 0 rows
--INSERT FROM WORK_NETDELEV TABLE
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN UPDATE FROM WORK_NETDELEV ',GETDATE())
UPDATE FinData_Batch_bkp
SET NET_DELIV = PDS.NET_DELIV
FROM
FinData_Batch_bkp FIN1,
(SELECT
PD.FNCL_PD,
PD.PROD_CD,
SUM(ISNULL(PD.NET_DELIV ,0)) NET_DELIV
FROM
WORK_NETDELEV PD
GROUP BY
PD.fncl_pd,
PD.prod_cd) PDS
WHERE
FIN1.fncl_pd = PDS.fncl_pd
and FIN1.prod_cd=PDS.prod_cd
and FIN1.trd_arm_cd=(SELECT
MAX(FIN2.trd_arm_cd)
FROM
FinData_Batch_bkp FIN2
WHERE
FIN2.fncl_pd=PDS.fncl_pd
and FIN2.prod_cd=PDS.prod_cd)
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN UPDATE FROM WORK_NETDELEV DONE',GETDATE())
END--373570 rows 12:40 MINS
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN INSERT FROM WORK_NETDELEV ',GETDATE())
INSERT INTO
FinData_Batch_bkp
(
FNCL_PD,
PROD_CD,
PROD_TYPE,
PROD_SGMT_CD,
BRND_CD,
PREF_PRCH_SUPP_CD,
MER_ARA_CD,
PLAN_GRP_CD,
SUB_PLAN_GRP_CD,
NET_DELIV
)
SELECT
PDS.FNCL_PD FNCL_PD,
PDS.PROD_CD PROD_CD,
CASE
WHEN (PDST_ARA_CD = '02' and PDST_TYP_CD = '01' and PDST_CD = '0001')
THEN 'OEM'
ELSE 'BRND'
END AS PROD_TYPE,
PRD.PROD_SGMT_CD AS PROD_SGMT_CD,
PRD.BRND_CD BRND_CD,
PRD.PREF_PRCH_SUPP_CD PREF_PRCH_SUPP_CD,
PRD.MER_ARA_CD MER_ARA_CD,
PRD.PLAN_GRP_CD PLAN_GRP_CD,
PRD.SUB_PLAN_GRP_CD SUB_PLAN_GRP_CD,
PDS.NET_DELIV
FROM
(
SELECT
PD.FNCL_PD FNCL_PD,
PD.PROD_CD PROD_CD,
SUM(ISNULL(PD.NET_DELIV,0)) NET_DELIV
FROM
WORK_NETDELEV PD
WHERE
NOT EXISTS (SELECT 1
FROM
FinData_Batch_bkp FIN
WHERE
FIN.FNCL_PD=PD.FNCL_PD
AND FIN.PROD_CD=PD.PROD_CD)
GROUP BY
PD.FNCL_PD,
PD.PROD_CD
) PDS
INNER JOIN PROD PRD
ON PDS.PROD_CD=PRD.PROD_CD
LEFT OUTER JOIN PRODUCT_SET_MEMBER P ON
PRD.PROD_CD=P.PROD_CD
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN INSERT FROM WORK_NETDELEV DONE',GETDATE())
END--9259 rows 1:53 seconds
--Updation of term_cd and Days
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN UPDATE FROM APVENMAST AND TERMS ',GETDATE())
UPDATE FinData_Batch_bkp
SET TERMS_CD = PDS.TERMS_CD ,
DAYS = PDS.DAYS
FROM
FinData_Batch_bkp FIN1 INNER JOIN apvenmast_terms PDS
/*(SELECT DISTINCT
AP.FNCL_PD,
AP.VENDOR,
PD.TERMS_CD TERMS_CD,
PD.DAYS DAYS
FROM
TERMS PD JOIN APVENMAST AP
ON (PD.TERMS_CD=AP.TERMS_CD
and PD.FNCL_PD=AP.FNCL_PD)
-- GROUP BY
--AP.fncl_pd,
--AP.VENDOR
) PDS
WHERE*/
ON (FIN1.fncl_pd = PDS.fncl_pd
and FIN1.PREF_PRCH_SUPP_CD=PDS.VENDOR);
--and FIN1.trd_arm_cd=(SELECT
-- MAX(FIN2.trd_arm_cd)
-- FROM
-- FinData_Batch_bkp FIN2
-- WHERE
-- FIN2.fncl_pd=PDS.fncl_pd
-- and FIN2.prod_cd=PDS.prod_cd)
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN UPDATE FROM APVENMAST AND TERMS DONE',GETDATE())
END--(2422974 row(s) affected) 01 hrs 23 mins
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN UPDATE FROM WORK_TFER_QTY_MAT ',GETDATE())
UPDATE FinData_Batch_bkp
SET TFER_QTY_MAT = RS.tfer_qty_mat
FROM
FinData_Batch_bkp FIN1 INNER JOIN WORK_TFER_QTY_MAT RS
/*,
(SELECT fncl_pd AS FNCL_PD,
tfer_item_cd AS TFER_ITEM_CD,
ISNULL(TFER_QTY_MAT,0) AS TFER_QTY_MAT
FROM WORK_TFER_QTY_MAT
) RS
WHERE */
ON(FIN1.fncl_pd = RS.fncl_pd
and FIN1.prod_cd = RS.TFER_ITEM_CD)
and FIN1.trd_arm_cd=(SELECT
MAX(FIN2.trd_arm_cd)
FROM
FinData_Batch_bkp FIN2
WHERE
FIN2.fncl_pd=RS.fncl_pd
and FIN2.prod_cd=RS.TFER_ITEM_CD);
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN UPDATE FROM WORK_TFER_QTY_MAT DONE',GETDATE())
END--30:28 sec (689935 row(s) affected)
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN UPDATE FROM WORK_SAL_QTY_MAT ',GETDATE())
UPDATE FinData_Batch_bkp
SET SAL_QTY_MAT = RS.SAL_QTY_MAT
FROM
FinData_Batch_bkp FIN1,/* INNER JOIN WORK_SAL_QTY_MAT RS*/
(
SELECT FNCL_PD AS FNCL_PD,
PROD_CD AS PROD_CD,
ISNULL(SAL_QTY_MAT,0) AS SAL_QTY_MAT
FROM WORK_SAL_QTY_MAT
) RS
WHERE
/*ON(*/FIN1.fncl_pd = RS.fncl_pd
and FIN1.prod_cd = RS.PROD_CD--)
and FIN1.trd_arm_cd=(SELECT
MAX(FIN2.trd_arm_cd)
FROM
FinData_Batch_bkp FIN2
WHERE
FIN2.fncl_pd=RS.fncl_pd
and FIN2.prod_cd=RS.PROD_CD)
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN UPDATE FROM WORK_SAL_QTY_MAT DONE',GETDATE())
END--(3426347 row(s) affected) 1hrs 33 min
--RDC/CHDC
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN UPDATE FROM CEP_DCA ',GETDATE())
UPDATE FinData_Batch_bkp
SET WKLY_IN_STK_RDC = CDC.WKLY_IN_STK_RDC,
WKLY_DC_PRD_CNT_RDC = CDC.WKLY_DC_PRD_CNT_RDC,
WKLY_IN_STK_CHDC = CDC.WKLY_IN_STK_CHDC,
WKLY_DC_PRD_CNT_CHDC = CDC.WKLY_DC_PRD_CNT_CHDC
FROM
FinData_Batch_bkp FIN1,
(SELECT
CD.FNCL_PD,
CD.PROD_CD,
SUM(CASE WHEN CD.DC_TYPE='RDC' THEN ISNULL(CD.WKLY_IN_STK,0) ELSE 0 END ) WKLY_IN_STK_RDC,
SUM(CASE WHEN CD.DC_TYPE='RDC' THEN ISNULL(CD.WKLY_DC_PRD_CNT,0) ELSE 0 END ) WKLY_DC_PRD_CNT_RDC,
SUM(CASE WHEN CD.DC_TYPE='CHDC' THEN ISNULL(CD.WKLY_IN_STK,0) ELSE 0 END ) WKLY_IN_STK_CHDC,
SUM(CASE WHEN CD.DC_TYPE='CHDC' THEN ISNULL(CD.WKLY_DC_PRD_CNT,0) ELSE 0 END ) WKLY_DC_PRD_CNT_CHDC
FROM
CEP_DCA CD
GROUP BY
CD.FNCL_PD,
CD.PROD_CD
) CDC
WHERE
FIN1.fncl_pd = CDC.fncl_pd
and FIN1.prod_cd=CDC.prod_cd
and FIN1.trd_arm_cd=(SELECT
MAX(FIN2.trd_arm_cd)
FROM
FinData_Batch_bkp FIN2
WHERE
FIN2.fncl_pd=CDC.fncl_pd
and FIN2.prod_cd=CDC.prod_cd)
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN UPDATE FROM CEP_DCA DONE',GETDATE())
END--42114 rows 5 min 44 sec
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN INSERT FROM WORK_TFER_QTY_MAT ',GETDATE())
INSERT INTO
FinData_Batch_bkp
(
FNCL_PD,
PROD_CD,
PROD_TYPE,
PROD_SGMT_CD,
BRND_CD,
PREF_PRCH_SUPP_CD,
MER_ARA_CD,
PLAN_GRP_CD,
SUB_PLAN_GRP_CD,
TFER_QTY_MAT
)
SELECT
RTS.FNCL_PD FNCL_PD,
RTS.TFER_ITEM_CD PROD_CD,
CASE
WHEN (PDST_ARA_CD = '02' and PDST_TYP_CD = '01' and PDST_CD = '0001')
THEN 'OEM'
ELSE 'BRND'
END AS PROD_TYPE,
PRD.PROD_SGMT_CD AS PROD_SGMT_CD,
PRD.BRND_CD BRND_CD,
PRD.PREF_PRCH_SUPP_CD PREF_PRCH_SUPP_CD,
PRD.MER_ARA_CD MER_ARA_CD,
PRD.PLAN_GRP_CD PLAN_GRP_CD,
PRD.SUB_PLAN_GRP_CD SUB_PLAN_GRP_CD,
RTS.TFER_QTY_MAT TFER_QTY_MAT
FROM
(
SELECT
WTM.FNCL_PD FNCL_PD,
WTM.TFER_ITEM_CD TFER_ITEM_CD,
SUM(TFER_QTY_MAT)TFER_QTY_MAT
FROM
WORK_TFER_QTY_MAT WTM
WHERE
NOT EXISTS (SELECT 1
FROM
FinData_Batch_bkp FIN
WHERE
FIN.FNCL_PD=WTM.FNCL_PD
AND FIN.PROD_CD=WTM.TFER_ITEM_CD )
GROUP BY
WTM.FNCL_PD,
WTM.TFER_ITEM_CD
) RTS
INNER JOIN PROD PRD
ON RTS.TFER_ITEM_CD=PRD.PROD_CD
LEFT OUTER JOIN PRODUCT_SET_MEMBER P
ON PRD.PROD_CD=P.PROD_CD
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN INSERT FROM WORK_TFER_QTY_MAT DONE',GETDATE())
END --40719 rows 12 seconds
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN INSERT FROM WORK_SAL_QTY_MAT ',GETDATE())
INSERT INTO
FinData_Batch_bkp
(
FNCL_PD,
PROD_CD,
PROD_TYPE,
PROD_SGMT_CD,
BRND_CD,
PREF_PRCH_SUPP_CD,
MER_ARA_CD,
PLAN_GRP_CD,
SUB_PLAN_GRP_CD,
SAL_QTY_MAT
)
SELECT
SAL.FNCL_PD FNCL_PD,
SAL.PROD_CD PROD_CD,
CASE
WHEN (PDST_ARA_CD = '02' and PDST_TYP_CD = '01' and PDST_CD = '0001')
THEN 'OEM'
ELSE 'BRND'
END AS PROD_TYPE,
PRD.PROD_SGMT_CD AS PROD_SGMT_CD,
PRD.BRND_CD BRND_CD,
PRD.PREF_PRCH_SUPP_CD PREF_PRCH_SUPP_CD,
PRD.MER_ARA_CD MER_ARA_CD,
PRD.PLAN_GRP_CD PLAN_GRP_CD,
PRD.SUB_PLAN_GRP_CD SUB_PLAN_GRP_CD,
SAL.SAL_QTY_MAT SAL_QTY_MAT
FROM
(
SELECT
WSM.FNCL_PD FNCL_PD,
WSM.PROD_CD PROD_CD,
SUM(SAL_QTY_MAT)SAL_QTY_MAT
FROM
WORK_SAL_QTY_MAT WSM
WHERE
NOT EXISTS (SELECT 1
FROM
FinData_Batch_bkp FIN
WHERE
FIN.FNCL_PD=WSM.FNCL_PD
AND FIN.PROD_CD=WSM.PROD_CD )
GROUP BY
WSM.FNCL_PD,
WSM.PROD_CD
) SAL
INNER JOIN PROD PRD
ON SAL.PROD_CD=PRD.PROD_CD
LEFT OUTER JOIN PRODUCT_SET_MEMBER P
ON PRD.PROD_CD=P.PROD_CD
END--99821 rows 25 SECONDS
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN INSERT FROM WORK_SAL_QTY_MAT DONE',GETDATE())
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN INSERT FROM CEP_DCA ',GETDATE())
INSERT INTO
FinData_Batch_bkp
(
FNCL_PD,
PROD_CD,
PROD_TYPE,
PROD_SGMT_CD,
BRND_CD,
PREF_PRCH_SUPP_CD,
MER_ARA_CD,
PLAN_GRP_CD,
SUB_PLAN_GRP_CD,
WKLY_IN_STK_RDC,
WKLY_DC_PRD_CNT_RDC,
WKLY_IN_STK_CHDC,
WKLY_DC_PRD_CNT_CHDC
)
SELECT
CDC.FNCL_PD FNCL_PD,
CDC.PROD_CD PROD_CD,
CASE
WHEN (PDST_ARA_CD = '02' and PDST_TYP_CD = '01' and PDST_CD = '0001')
THEN 'OEM'
ELSE 'BRND'
END AS PROD_TYPE,
PRD.PROD_SGMT_CD AS PROD_SGMT_CD,
PRD.BRND_CD BRND_CD,
PRD.PREF_PRCH_SUPP_CD PREF_PRCH_SUPP_CD,
PRD.MER_ARA_CD MER_ARA_CD,
PRD.PLAN_GRP_CD PLAN_GRP_CD,
PRD.SUB_PLAN_GRP_CD SUB_PLAN_GRP_CD,
CDC.WKLY_IN_STK_RDC,
CDC.WKLY_DC_PRD_CNT_RDC,
CDC.WKLY_IN_STK_CHDC,
CDC.WKLY_DC_PRD_CNT_CHDC
FROM
(
SELECT
CDA.FNCL_PD FNCL_PD,
CDA.PROD_CD PROD_CD,
SUM(CASE WHEN CDA.DC_TYPE='RDC' THEN ISNULL(CDA.WKLY_IN_STK,0) ELSE 0 END ) WKLY_IN_STK_RDC,
SUM(CASE WHEN CDA.DC_TYPE='RDC' THEN ISNULL(CDA.WKLY_DC_PRD_CNT,0) ELSE 0 END ) WKLY_DC_PRD_CNT_RDC,
SUM(CASE WHEN CDA.DC_TYPE='CHDC' THEN ISNULL(CDA.WKLY_IN_STK,0) ELSE 0 END ) WKLY_IN_STK_CHDC,
SUM(CASE WHEN CDA.DC_TYPE='CHDC' THEN ISNULL(CDA.WKLY_DC_PRD_CNT,0) ELSE 0 END ) WKLY_DC_PRD_CNT_CHDC
FROM
CEP_DCA CDA
WHERE
NOT EXISTS (SELECT 1
FROM
FinData_Batch_bkp FIN
WHERE
FIN.FNCL_PD=CDA.FNCL_PD
AND FIN.PROD_CD=CDA.PROD_CD)
GROUP BY
CDA.FNCL_PD,
CDA.PROD_CD
) CDC
INNER JOIN PROD PRD
ON CDC.PROD_CD=PRD.PROD_CD
LEFT OUTER JOIN PRODUCT_SET_MEMBER P
ON PRD.PROD_CD=P.PROD_CD
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN INSERT FROM CEP_DCA DONE',GETDATE())
END--161 rows 13 seconds
--Def#2219
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN INSERT FOR 2219 ',GETDATE())
INSERT INTO APVENMAST_TERMS1
SELECT
AP.VENDOR,
PD.TERMS_CD TERMS_CD,
PD.DAYS DAYS
FROM TERMS PD JOIN APVENMAST AP
ON (PD.TERMS_CD=AP.TERMS_CD and PD.FNCL_PD=AP.FNCL_PD)
INNER JOIN
(
SELECT AP.VENDOR,MAX(AP.FNCL_PD) FNCL_PD
FROM APVENMAST AP WHERE AP.VENDOR IN
(
SELECT DISTINCT PREF_PRCH_SUPP_CD FROM FinData_Batch_bkp
WHERE PREF_PRCH_SUPP_CD <>''
AND DAYS IS NULL
AND FNCL_PD >='200706'
)
GROUP BY AP.VENDOR
)PDS
ON (AP.VENDOR=PDS.VENDOR AND AP.FNCL_PD =PDS.FNCL_PD)
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN INSERT FOR 2219 DONE',GETDATE())
END
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN UPDATE FROM TERMS AND APVENMAST2 ',GETDATE())
UPDATE FinData_Batch_bkp
SET TERMS_CD = PDS1.TERMS_CD ,
DAYS = PDS1.DAYS
FROM
FinData_Batch_bkp FIN1,APVENMAST_TERMS1 PDS1
/*(
SELECT
AP.VENDOR,
PD.TERMS_CD TERMS_CD,
PD.DAYS DAYS
FROM TERMS PD JOIN APVENMAST AP
ON (PD.TERMS_CD=AP.TERMS_CD and PD.FNCL_PD=AP.FNCL_PD)
INNER JOIN
(
SELECT AP.VENDOR,MAX(AP.FNCL_PD) FNCL_PD
FROM APVENMAST AP WHERE AP.VENDOR IN
(
SELECT DISTINCT PREF_PRCH_SUPP_CD FROM FinData_Batch_bkp
WHERE PREF_PRCH_SUPP_CD <>''
AND DAYS IS NULL
AND FNCL_PD >='200706'
)
GROUP BY AP.VENDOR
)PDS
ON (AP.VENDOR=PDS.VENDOR AND AP.FNCL_PD =PDS.FNCL_PD)
)PDS1*/
WHERE
FIN1.PREF_PRCH_SUPP_CD=PDS1.VENDOR AND FNCL_PD >='200706'
AND FIN1.DAYS IS NULL
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN UPDATE FROM TERMS AND APVENMAST2 COMPLETED',GETDATE())
END
/*
--ENHANCEMENT 8 --SMOOTHED_TAR
BEGIN
UPDATE findata_batch
SET SMOOTHED_MAT_TAR = ST.FACT_APPOR
FROM
FINDATA_BATCH FIN1,
(
SELECT FNCL_PD AS FNCL_PD,
PROD_CD AS PROD_CD,
ISNULL(FACT_APPOR,0) AS FACT_APPOR
FROM workT4_T3RESULT
) ST
WHERE
FIN1.fncl_pd = ST.fncl_pd
and FIN1.prod_cd = ST.PROD_CD
and FIN1.trd_arm_cd=(SELECT
MAX(FIN2.trd_arm_cd)
FROM
FinData_Batch FIN2
WHERE
FIN2.fncl_pd=ST.fncl_pd
and FIN2.prod_cd=ST.PROD_CD)
END*/
-- Insert in SUPPLIER/PG/FP/MG/TA/aggregated Table
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('PG_SUPP INSERT FROM FIN',GETDATE())
INSERT INTO [FinData_Batch_PG_SUPP_BKP]
(
FNCL_PD,
PROD_CD,
TRD_ARM_CD,
BRND_CD,
PREF_PRCH_SUPP_CD,
MER_ARA_CD,
PLAN_GRP_CD,
SUB_PLAN_GRP_CD,
PNM_VAL,
TRD_MGN_VAL,
TRADING_ACCT_REV,
ADV_PROMOTION,
REBATES,
OTHERS,
EURO_OVERRIDER,
ACHD_RTL_VAL,
SAL_QTY,
PLU_RTL_VAL,
CST_OF_SAL_VAL,
TAB_MGN_RVN_VAL,
TAB_MGN_VAL,
COP_ADJ_VAL,
UBC_ADJ_VAL,
CVPN_ADJ_VAL,
DSCT_VAL,
SAL_TAX_VAL,
SAL_TAX_NEW_VAL,
CO_QTY_DLVY_ON_TM,
CO_INIT_CALL_OFF_QTY,
CO_ORIG_CNT_DLVY,
CO_ORIG_ON_TM,
CO_IN_FULL_DLVY_QTY,
CO_IN_FULL_CALL_OFF_QTY,
STM_MAT_CST_OF_SAL_VAL,
STM_AVG_STK_CST_VAL,
LDC_AVAIL_CNT,
LDC_CNT,
RCV_CST_PRC,
QTY_RCV,
PUR_CST_INTAKE,
Smoothed_TAR,
DPP_TAR,
APPOR_BRANCH_COST,
APPOR_SPACE,
APPOR_FIXED_DC,
Smoothed_DPP,
Actual_DPP,
PROD_TYPE,
PAID_DAYS_STOCK,
WKLY_IN_STK,
WKLY_BR_PRD_CNT,
WKLY_F1_UBX,
WKLY_SS_UBX,
SFA_FCAST_5_QTY,
SFA_ABS_DIFF_5_QTY,
PROD_SGMT_CD,
QUASI_UBC,
CPR_UBC,
TERMS_CD,
DAYS,
NET_DELIV,
TFER_QTY_MAT,
SAL_QTY_MAT,
WKLY_IN_STK_RDC,
WKLY_DC_PRD_CNT_RDC,
WKLY_IN_STK_CHDC,
WKLY_DC_PRD_CNT_CHDC,
SMOOTHED_MAT_TAR
)
SELECT
FNCL_PD,
NULL,
TRD_ARM_CD,
NULL,
PREF_PRCH_SUPP_CD,
MER_ARA_CD,
PLAN_GRP_CD ,
NULL,
SUM(ISNULL(PNM_VAL,0)),
SUM(ISNULL(TRD_MGN_VAL,0)),
SUM(ISNULL(TRADING_ACCT_REV,0)),
SUM(ISNULL(ADV_PROMOTION,0)),
SUM(ISNULL(REBATES,0)),
SUM(ISNULL(OTHERS,0)),
SUM(ISNULL(EURO_OVERRIDER,0)),
SUM(ISNULL(ACHD_RTL_VAL,0)),
SUM(ISNULL(SAL_QTY,0)),
SUM(ISNULL(PLU_RTL_VAL,0)),
SUM(ISNULL(CST_OF_SAL_VAL,0)),
SUM(ISNULL(TAB_MGN_RVN_VAL,0)),
SUM(ISNULL(TAB_MGN_VAL,0)),
SUM(ISNULL(COP_ADJ_VAL,0)),
SUM(ISNULL(UBC_ADJ_VAL,0)),
SUM(ISNULL(CVPN_ADJ_VAL,0)),
SUM(ISNULL(DSCT_VAL,0)),
SUM(ISNULL(SAL_TAX_VAL,0)),
SUM(ISNULL(SAL_TAX_NEW_VAL,0)),
SUM(ISNULL(CO_QTY_DLVY_ON_TM,0)),
SUM(ISNULL(CO_INIT_CALL_OFF_QTY,0)),
SUM(ISNULL(CO_ORIG_CNT_DLVY,0)),
SUM(ISNULL(CO_ORIG_ON_TM,0)),
SUM(ISNULL(CO_IN_FULL_DLVY_QTY,0)),
SUM(ISNULL(CO_IN_FULL_CALL_OFF_QTY,0)),
SUM(ISNULL(STM_MAT_CST_OF_SAL_VAL,0)),
SUM(ISNULL(STM_AVG_STK_CST_VAL,0)),
SUM(ISNULL(LDC_AVAIL_CNT,0)),
SUM(ISNULL(LDC_CNT,0)),
SUM(ISNULL(RCV_CST_PRC,0)),
SUM(ISNULL(QTY_RCV,0)),
SUM(ISNULL(PUR_CST_INTAKE,0)),
SUM(ISNULL(Smoothed_TAR,0)),
SUM(ISNULL(DPP_TAR,0)),
SUM(ISNULL(APPOR_BRANCH_COST,0)),
SUM(ISNULL(APPOR_SPACE,0)),
SUM(ISNULL(APPOR_FIXED_DC,0)),
SUM(ISNULL(Smoothed_DPP,0)),
SUM(ISNULL(Actual_DPP,0)),
PROD_TYPE,
SUM(ISNULL(PAID_DAYS_STOCK,0)) ,
SUM(ISNULL(WKLY_IN_STK,0)) ,
SUM(ISNULL(WKLY_BR_PRD_CNT,0)),
SUM(ISNULL(WKLY_F1_UBX,0)) ,
SUM(ISNULL(WKLY_SS_UBX,0)) ,
SUM(ISNULL(SFA_FCAST_5_QTY,0)) ,
SUM(ISNULL(SFA_ABS_DIFF_5_QTY,0)) ,
PROD_SGMT_CD,
SUM(ISNULL(QUASI_UBC,0)),
SUM(ISNULL(CPR_UBC,0)),
TERMS_CD,
DAYS,
SUM(ISNULL(NET_DELIV,0)),
SUM(ISNULL(TFER_QTY_MAT,0)),
SUM(ISNULL(SAL_QTY_MAT,0)),
SUM(ISNULL(WKLY_IN_STK_RDC,0)),
SUM(ISNULL(WKLY_DC_PRD_CNT_RDC,0)),
SUM(ISNULL(WKLY_IN_STK_CHDC,0)),
SUM(ISNULL(WKLY_DC_PRD_CNT_CHDC,0)),
SUM(ISNULL(SMOOTHED_MAT_TAR,0))
from findata_batch_BKP
GROUP BY
PLAN_GRP_CD ,
MER_ARA_CD,
FNCL_PD,
TRD_ARM_CD,
PREF_PRCH_SUPP_CD,
PROD_TYPE,
PROD_SGMT_CD,
TERMS_CD,
DAYS
INSERT INTO FINDATA_BATCH_TRACE VALUES ('PG_SUPP INSERT FROM FIN DONE',GETDATE())
END--(1386619 row(s) affected) 8 min 48 sec
--Insert In BRAND/MA/PG/FP/TA/aggregated Table
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('PG_BRD INSERT FROM FIN',GETDATE())
INSERT INTO [FinData_Batch_PG_BRD_BKP]
(
FNCL_PD,
PROD_CD,
TRD_ARM_CD,
BRND_CD,
PREF_PRCH_SUPP_CD,
MER_ARA_CD,
PLAN_GRP_CD,
SUB_PLAN_GRP_CD,
PNM_VAL,
TRD_MGN_VAL,
TRADING_ACCT_REV,
ADV_PROMOTION,
REBATES,
OTHERS,
EURO_OVERRIDER,
ACHD_RTL_VAL,
SAL_QTY,
PLU_RTL_VAL,
CST_OF_SAL_VAL,
TAB_MGN_RVN_VAL,
TAB_MGN_VAL,
COP_ADJ_VAL,
UBC_ADJ_VAL,
CVPN_ADJ_VAL,
DSCT_VAL,
SAL_TAX_VAL,
SAL_TAX_NEW_VAL,
CO_QTY_DLVY_ON_TM,
CO_INIT_CALL_OFF_QTY,
CO_ORIG_CNT_DLVY,
CO_ORIG_ON_TM,
CO_IN_FULL_DLVY_QTY,
CO_IN_FULL_CALL_OFF_QTY,
STM_MAT_CST_OF_SAL_VAL,
STM_AVG_STK_CST_VAL,
LDC_AVAIL_CNT,
LDC_CNT,
RCV_CST_PRC,
QTY_RCV,
PUR_CST_INTAKE,
Smoothed_TAR,
DPP_TAR,
APPOR_BRANCH_COST,
APPOR_SPACE,
APPOR_FIXED_DC,
Smoothed_DPP,
Actual_DPP,
PROD_TYPE,
PAID_DAYS_STOCK,
WKLY_IN_STK,
WKLY_BR_PRD_CNT,
WKLY_F1_UBX,
WKLY_SS_UBX,
SFA_FCAST_5_QTY,
SFA_ABS_DIFF_5_QTY,
PROD_SGMT_CD,
QUASI_UBC,
CPR_UBC,
TERMS_CD,
DAYS,
NET_DELIV,
TFER_QTY_MAT,
SAL_QTY_MAT,
WKLY_IN_STK_RDC,
WKLY_DC_PRD_CNT_RDC,
WKLY_IN_STK_CHDC,
WKLY_DC_PRD_CNT_CHDC,
SMOOTHED_MAT_TAR
)
SELECT
FNCL_PD,
NULL,
TRD_ARM_CD,
BRND_CD,
NULL,
MER_ARA_CD,
PLAN_GRP_CD ,
NULL,
SUM(ISNULL(PNM_VAL,0)),
SUM(ISNULL(TRD_MGN_VAL,0)),
SUM(ISNULL(TRADING_ACCT_REV,0)),
SUM(ISNULL(ADV_PROMOTION,0)),
SUM(ISNULL(REBATES,0)),
SUM(ISNULL(OTHERS,0)),
SUM(ISNULL(EURO_OVERRIDER,0)),
SUM(ISNULL(ACHD_RTL_VAL,0)),
SUM(ISNULL(SAL_QTY,0)),
SUM(ISNULL(PLU_RTL_VAL,0)),
SUM(ISNULL(CST_OF_SAL_VAL,0)),
SUM(ISNULL(TAB_MGN_RVN_VAL,0)),
SUM(ISNULL(TAB_MGN_VAL,0)),
SUM(ISNULL(COP_ADJ_VAL,0)),
SUM(ISNULL(UBC_ADJ_VAL,0)),
SUM(ISNULL(CVPN_ADJ_VAL,0)),
SUM(ISNULL(DSCT_VAL,0)),
SUM(ISNULL(SAL_TAX_VAL,0)),
SUM(ISNULL(SAL_TAX_NEW_VAL,0)),
SUM(ISNULL(CO_QTY_DLVY_ON_TM,0)),
SUM(ISNULL(CO_INIT_CALL_OFF_QTY,0)),
SUM(ISNULL(CO_ORIG_CNT_DLVY,0)),
SUM(ISNULL(CO_ORIG_ON_TM,0)),
SUM(ISNULL(CO_IN_FULL_DLVY_QTY,0)),
SUM(ISNULL(CO_IN_FULL_CALL_OFF_QTY,0)),
SUM(ISNULL(STM_MAT_CST_OF_SAL_VAL,0)),
SUM(ISNULL(STM_AVG_STK_CST_VAL,0)),
SUM(ISNULL(LDC_AVAIL_CNT,0)),
SUM(ISNULL(LDC_CNT,0)),
SUM(ISNULL(RCV_CST_PRC,0)),
SUM(ISNULL(QTY_RCV,0)),
SUM(ISNULL(PUR_CST_INTAKE,0)),
SUM(ISNULL(Smoothed_TAR,0)),
SUM(ISNULL(DPP_TAR,0)),
SUM(ISNULL(APPOR_BRANCH_COST,0)),
SUM(ISNULL(APPOR_SPACE,0)),
SUM(ISNULL(APPOR_FIXED_DC,0)),
SUM(ISNULL(Smoothed_DPP,0)),
SUM(ISNULL(Actual_DPP,0)),
PROD_TYPE,
SUM(ISNULL(PAID_DAYS_STOCK,0)) ,
SUM(ISNULL(WKLY_IN_STK,0)) ,
SUM(ISNULL(WKLY_BR_PRD_CNT,0)),
SUM(ISNULL(WKLY_F1_UBX,0)) ,
SUM(ISNULL(WKLY_SS_UBX,0)) ,
SUM(ISNULL(SFA_FCAST_5_QTY,0)) ,
SUM(ISNULL(SFA_ABS_DIFF_5_QTY,0)) ,
PROD_SGMT_CD,
SUM(ISNULL(QUASI_UBC,0)),
SUM(ISNULL(CPR_UBC,0)),
TERMS_CD,
DAYS,
SUM(ISNULL(NET_DELIV,0)),
SUM(ISNULL(TFER_QTY_MAT,0)),
SUM(ISNULL(SAL_QTY_MAT,0)),
SUM(ISNULL(WKLY_IN_STK_RDC,0)),
SUM(ISNULL(WKLY_DC_PRD_CNT_RDC,0)),
SUM(ISNULL(WKLY_IN_STK_CHDC,0)),
SUM(ISNULL(WKLY_DC_PRD_CNT_CHDC,0)),
SUM(ISNULL(SMOOTHED_MAT_TAR,0))
from findata_batch_BKP
GROUP BY
PLAN_GRP_CD ,
MER_ARA_CD,
FNCL_PD,
TRD_ARM_CD,
BRND_CD,
PROD_TYPE,
PROD_SGMT_CD,
TERMS_CD,
DAYS
INSERT INTO FINDATA_BATCH_TRACE VALUES ('PG_BRD INSERT FROM FIN DONE',GETDATE())
END--(1944439 row(s) affected) 8 min 02 sec
BEGIN--Insert In SUPPLIER/MA/PG/SPG/FP/TA aggregated Table
INSERT INTO FINDATA_BATCH_TRACE VALUES ('SPG_SUPP INSERT FROM FIN',GETDATE())
INSERT INTO [FinData_Batch_SPG_SUPP_BKP]
(
FNCL_PD,
PROD_CD,
TRD_ARM_CD,
BRND_CD,
PREF_PRCH_SUPP_CD,
MER_ARA_CD,
PLAN_GRP_CD,
SUB_PLAN_GRP_CD,
PNM_VAL,
TRD_MGN_VAL,
TRADING_ACCT_REV,
ADV_PROMOTION,
REBATES,
OTHERS,
EURO_OVERRIDER,
ACHD_RTL_VAL,
SAL_QTY,
PLU_RTL_VAL,
CST_OF_SAL_VAL,
TAB_MGN_RVN_VAL,
TAB_MGN_VAL,
COP_ADJ_VAL,
UBC_ADJ_VAL,
CVPN_ADJ_VAL,
DSCT_VAL,
SAL_TAX_VAL,
SAL_TAX_NEW_VAL,
CO_QTY_DLVY_ON_TM,
CO_INIT_CALL_OFF_QTY,
CO_ORIG_CNT_DLVY,
CO_ORIG_ON_TM,
CO_IN_FULL_DLVY_QTY,
CO_IN_FULL_CALL_OFF_QTY,
STM_MAT_CST_OF_SAL_VAL,
STM_AVG_STK_CST_VAL,
LDC_AVAIL_CNT,
LDC_CNT,
RCV_CST_PRC,
QTY_RCV,
PUR_CST_INTAKE,
Smoothed_TAR,
DPP_TAR,
APPOR_BRANCH_COST,
APPOR_SPACE,
APPOR_FIXED_DC,
Smoothed_DPP,
Actual_DPP,
PROD_TYPE,
PAID_DAYS_STOCK,
WKLY_IN_STK,
WKLY_BR_PRD_CNT,
WKLY_F1_UBX,
WKLY_SS_UBX,
SFA_FCAST_5_QTY,
SFA_ABS_DIFF_5_QTY,
PROD_SGMT_CD,
QUASI_UBC,
CPR_UBC,
TERMS_CD,
DAYS,
NET_DELIV,
TFER_QTY_MAT,
SAL_QTY_MAT,
WKLY_IN_STK_RDC,
WKLY_DC_PRD_CNT_RDC,
WKLY_IN_STK_CHDC,
WKLY_DC_PRD_CNT_CHDC,
SMOOTHED_MAT_TAR
)
SELECT
FNCL_PD,
NULL,
TRD_ARM_CD,
NULL,
PREF_PRCH_SUPP_CD,
MER_ARA_CD,
PLAN_GRP_CD ,
SUB_PLAN_GRP_CD,
SUM(ISNULL(PNM_VAL,0)),
SUM(ISNULL(TRD_MGN_VAL,0)),
SUM(ISNULL(TRADING_ACCT_REV,0)),
SUM(ISNULL(ADV_PROMOTION,0)),
SUM(ISNULL(REBATES,0)),
SUM(ISNULL(OTHERS,0)),
SUM(ISNULL(EURO_OVERRIDER,0)),
SUM(ISNULL(ACHD_RTL_VAL,0)),
SUM(ISNULL(SAL_QTY,0)),
SUM(ISNULL(PLU_RTL_VAL,0)),
SUM(ISNULL(CST_OF_SAL_VAL,0)),
SUM(ISNULL(TAB_MGN_RVN_VAL,0)),
SUM(ISNULL(TAB_MGN_VAL,0)),
SUM(ISNULL(COP_ADJ_VAL,0)),
SUM(ISNULL(UBC_ADJ_VAL,0)),
SUM(ISNULL(CVPN_ADJ_VAL,0)),
SUM(ISNULL(DSCT_VAL,0)),
SUM(ISNULL(SAL_TAX_VAL,0)),
SUM(ISNULL(SAL_TAX_NEW_VAL,0)),
SUM(ISNULL(CO_QTY_DLVY_ON_TM,0)),
SUM(ISNULL(CO_INIT_CALL_OFF_QTY,0)),
SUM(ISNULL(CO_ORIG_CNT_DLVY,0)),
SUM(ISNULL(CO_ORIG_ON_TM,0)),
SUM(ISNULL(CO_IN_FULL_DLVY_QTY,0)),
SUM(ISNULL(CO_IN_FULL_CALL_OFF_QTY,0)),
SUM(ISNULL(STM_MAT_CST_OF_SAL_VAL,0)),
SUM(ISNULL(STM_AVG_STK_CST_VAL,0)),
SUM(ISNULL(LDC_AVAIL_CNT,0)),
SUM(ISNULL(LDC_CNT,0)),
SUM(ISNULL(RCV_CST_PRC,0)),
SUM(ISNULL(QTY_RCV,0)),
SUM(ISNULL(PUR_CST_INTAKE,0)),
SUM(ISNULL(Smoothed_TAR,0)),
SUM(ISNULL(DPP_TAR,0)),
SUM(ISNULL(APPOR_BRANCH_COST,0)),
SUM(ISNULL(APPOR_SPACE,0)),
SUM(ISNULL(APPOR_FIXED_DC,0)),
SUM(ISNULL(Smoothed_DPP,0)),
SUM(ISNULL(Actual_DPP,0)),
PROD_TYPE,
SUM(ISNULL(PAID_DAYS_STOCK,0)),
SUM(ISNULL(WKLY_IN_STK,0)) ,
SUM(ISNULL(WKLY_BR_PRD_CNT,0)),
SUM(ISNULL(WKLY_F1_UBX,0)) ,
SUM(ISNULL(WKLY_SS_UBX,0)) ,
SUM(ISNULL(SFA_FCAST_5_QTY,0)) ,
SUM(ISNULL(SFA_ABS_DIFF_5_QTY,0)) ,
PROD_SGMT_CD,
SUM(ISNULL(QUASI_UBC,0)),
SUM(ISNULL(CPR_UBC,0)),
TERMS_CD,
DAYS,
SUM(ISNULL(NET_DELIV,0)),
SUM(ISNULL(TFER_QTY_MAT,0)),
SUM(ISNULL(SAL_QTY_MAT,0)),
SUM(ISNULL(WKLY_IN_STK_RDC,0)),
SUM(ISNULL(WKLY_DC_PRD_CNT_RDC,0)),
SUM(ISNULL(WKLY_IN_STK_CHDC,0)),
SUM(ISNULL(WKLY_DC_PRD_CNT_CHDC,0)),
SUM(ISNULL(SMOOTHED_MAT_TAR,0))
from findata_batch_BKP
GROUP BY
PLAN_GRP_CD ,
MER_ARA_CD,
SUB_PLAN_GRP_CD,
FNCL_PD,
TRD_ARM_CD,
PREF_PRCH_SUPP_CD,
PROD_TYPE,
PROD_SGMT_CD,
TERMS_CD,
DAYS
INSERT INTO FINDATA_BATCH_TRACE VALUES ('SPG_SUPP INSERT FROM FIN DONE',GETDATE())
END--(2179803 row(s) affected) 8 min 16 seconds
-- Insert in BRAND/MA/PG/SPG/FP/TA aggregated Table
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('SPG_BRD INSERT FROM FIN',GETDATE())
INSERT INTO [FinData_Batch_SPG_BRD_BKP]
(
FNCL_PD,
PROD_CD,
TRD_ARM_CD,
BRND_CD,
PREF_PRCH_SUPP_CD,
MER_ARA_CD,
PLAN_GRP_CD,
SUB_PLAN_GRP_CD,
PNM_VAL,
TRD_MGN_VAL,
TRADING_ACCT_REV,
ADV_PROMOTION,
REBATES,
OTHERS,
EURO_OVERRIDER,
ACHD_RTL_VAL,
SAL_QTY,
PLU_RTL_VAL,
CST_OF_SAL_VAL,
TAB_MGN_RVN_VAL,
TAB_MGN_VAL,
COP_ADJ_VAL,
UBC_ADJ_VAL,
CVPN_ADJ_VAL,
DSCT_VAL,
SAL_TAX_VAL,
SAL_TAX_NEW_VAL,
CO_QTY_DLVY_ON_TM,
CO_INIT_CALL_OFF_QTY,
CO_ORIG_CNT_DLVY,
CO_ORIG_ON_TM,
CO_IN_FULL_DLVY_QTY,
CO_IN_FULL_CALL_OFF_QTY,
STM_MAT_CST_OF_SAL_VAL,
STM_AVG_STK_CST_VAL,
LDC_AVAIL_CNT,
LDC_CNT,
RCV_CST_PRC,
QTY_RCV,
PUR_CST_INTAKE,
Smoothed_TAR,
DPP_TAR,
APPOR_BRANCH_COST,
APPOR_SPACE,
APPOR_FIXED_DC,
Smoothed_DPP,
Actual_DPP,
PROD_TYPE,
PAID_DAYS_STOCK,
WKLY_IN_STK,
WKLY_BR_PRD_CNT,
WKLY_F1_UBX,
WKLY_SS_UBX,
SFA_FCAST_5_QTY,
SFA_ABS_DIFF_5_QTY,
PROD_SGMT_CD,
QUASI_UBC,
CPR_UBC,
TERMS_CD,
DAYS,
NET_DELIV,
TFER_QTY_MAT,
SAL_QTY_MAT,
WKLY_IN_STK_RDC,
WKLY_DC_PRD_CNT_RDC,
WKLY_IN_STK_CHDC,
WKLY_DC_PRD_CNT_CHDC,
SMOOTHED_MAT_TAR
)
SELECT
FNCL_PD,
NULL,
TRD_ARM_CD,
BRND_CD,
NULL,
MER_ARA_CD,
PLAN_GRP_CD ,
SUB_PLAN_GRP_CD,
SUM(ISNULL(PNM_VAL,0)),
SUM(ISNULL(TRD_MGN_VAL,0)),
SUM(ISNULL(TRADING_ACCT_REV,0)),
SUM(ISNULL(ADV_PROMOTION,0)),
SUM(ISNULL(REBATES,0)),
SUM(ISNULL(OTHERS,0)),
SUM(ISNULL(EURO_OVERRIDER,0)),
SUM(ISNULL(ACHD_RTL_VAL,0)),
SUM(ISNULL(SAL_QTY,0)),
SUM(ISNULL(PLU_RTL_VAL,0)),
SUM(ISNULL(CST_OF_SAL_VAL,0)),
SUM(ISNULL(TAB_MGN_RVN_VAL,0)),
SUM(ISNULL(TAB_MGN_VAL,0)),
SUM(ISNULL(COP_ADJ_VAL,0)),
SUM(ISNULL(UBC_ADJ_VAL,0)),
SUM(ISNULL(CVPN_ADJ_VAL,0)),
SUM(ISNULL(DSCT_VAL,0)),
SUM(ISNULL(SAL_TAX_VAL,0)),
SUM(ISNULL(SAL_TAX_NEW_VAL,0)),
SUM(ISNULL(CO_QTY_DLVY_ON_TM,0)),
SUM(ISNULL(CO_INIT_CALL_OFF_QTY,0)),
SUM(ISNULL(CO_ORIG_CNT_DLVY,0)),
SUM(ISNULL(CO_ORIG_ON_TM,0)),
SUM(ISNULL(CO_IN_FULL_DLVY_QTY,0)),
SUM(ISNULL(CO_IN_FULL_CALL_OFF_QTY,0)),
SUM(ISNULL(STM_MAT_CST_OF_SAL_VAL,0)),
SUM(ISNULL(STM_AVG_STK_CST_VAL,0)),
SUM(ISNULL(LDC_AVAIL_CNT,0)),
SUM(ISNULL(LDC_CNT,0)),
SUM(ISNULL(RCV_CST_PRC,0)),
SUM(ISNULL(QTY_RCV,0)),
SUM(ISNULL(PUR_CST_INTAKE,0)),
SUM(ISNULL(Smoothed_TAR,0)),
SUM(ISNULL(DPP_TAR,0)),
SUM(ISNULL(APPOR_BRANCH_COST,0)),
SUM(ISNULL(APPOR_SPACE,0)),
SUM(ISNULL(APPOR_FIXED_DC,0)),
SUM(ISNULL(Smoothed_DPP,0)),
SUM(ISNULL(Actual_DPP,0)),
PROD_TYPE,
SUM(ISNULL(PAID_DAYS_STOCK,0)),
SUM(ISNULL(WKLY_IN_STK,0)) ,
SUM(ISNULL(WKLY_BR_PRD_CNT,0)),
SUM(ISNULL(WKLY_F1_UBX,0)) ,
SUM(ISNULL(WKLY_SS_UBX,0)) ,
SUM(ISNULL(SFA_FCAST_5_QTY,0)) ,
SUM(ISNULL(SFA_ABS_DIFF_5_QTY,0)) ,
PROD_SGMT_CD,
SUM(ISNULL(QUASI_UBC,0)),
SUM(ISNULL(CPR_UBC,0)),
TERMS_CD,
DAYS,
SUM(ISNULL(NET_DELIV,0)),
SUM(ISNULL(TFER_QTY_MAT,0)),
SUM(ISNULL(SAL_QTY_MAT,0)),
SUM(ISNULL(WKLY_IN_STK_RDC,0)),
SUM(ISNULL(WKLY_DC_PRD_CNT_RDC,0)),
SUM(ISNULL(WKLY_IN_STK_CHDC,0)),
SUM(ISNULL(WKLY_DC_PRD_CNT_CHDC,0)),
SUM(ISNULL(SMOOTHED_MAT_TAR,0))
from findata_batch_BKP
GROUP BY
PLAN_GRP_CD ,
MER_ARA_CD,
SUB_PLAN_GRP_CD,
FNCL_PD,
TRD_ARM_CD,
BRND_CD,
PROD_TYPE,
PROD_SGMT_CD,
TERMS_CD,
DAYS
INSERT INTO FINDATA_BATCH_TRACE VALUES ('SPG_BRD INSERT FROM FIN DONE',GETDATE())
END--(2870812 row(s) affected) 08 min 31 seconds
-- Insert in BRAND/MA/PG/SPG/FP/TA aggregated Table
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('MA_BRD INSERT FROM FIN',GETDATE())
INSERT INTO [FinData_Batch_MA_BRD_BKP]
(
FNCL_PD,
PROD_CD,
TRD_ARM_CD,
BRND_CD,
PREF_PRCH_SUPP_CD,
MER_ARA_CD,
PLAN_GRP_CD,
SUB_PLAN_GRP_CD,
PNM_VAL,
TRD_MGN_VAL,
TRADING_ACCT_REV,
ADV_PROMOTION,
REBATES,
OTHERS,
EURO_OVERRIDER,
ACHD_RTL_VAL,
SAL_QTY,
PLU_RTL_VAL,
CST_OF_SAL_VAL,
TAB_MGN_RVN_VAL,
TAB_MGN_VAL,
COP_ADJ_VAL,
UBC_ADJ_VAL,
CVPN_ADJ_VAL,
DSCT_VAL,
SAL_TAX_VAL,
SAL_TAX_NEW_VAL,
CO_QTY_DLVY_ON_TM,
CO_INIT_CALL_OFF_QTY,
CO_ORIG_CNT_DLVY,
CO_ORIG_ON_TM,
CO_IN_FULL_DLVY_QTY,
CO_IN_FULL_CALL_OFF_QTY,
STM_MAT_CST_OF_SAL_VAL,
STM_AVG_STK_CST_VAL,
LDC_AVAIL_CNT,
LDC_CNT,
RCV_CST_PRC,
QTY_RCV,
PUR_CST_INTAKE,
Smoothed_TAR,
DPP_TAR,
APPOR_BRANCH_COST,
APPOR_SPACE,
APPOR_FIXED_DC,
Smoothed_DPP,
Actual_DPP,
PROD_TYPE,
PAID_DAYS_STOCK,
WKLY_IN_STK,
WKLY_BR_PRD_CNT,
WKLY_F1_UBX,
WKLY_SS_UBX,
SFA_FCAST_5_QTY,
SFA_ABS_DIFF_5_QTY,
PROD_SGMT_CD,
QUASI_UBC,
CPR_UBC,
TERMS_CD,
DAYS,
NET_DELIV,
TFER_QTY_MAT,
SAL_QTY_MAT,
WKLY_IN_STK_RDC,
WKLY_DC_PRD_CNT_RDC,
WKLY_IN_STK_CHDC,
WKLY_DC_PRD_CNT_CHDC,
SMOOTHED_MAT_TAR
)
SELECT
FNCL_PD,
NULL,
TRD_ARM_CD,
BRND_CD,
NULL,
MER_ARA_CD,
NULL ,
NULL,
SUM(ISNULL(PNM_VAL,0)),
SUM(ISNULL(TRD_MGN_VAL,0)),
SUM(ISNULL(TRADING_ACCT_REV,0)),
SUM(ISNULL(ADV_PROMOTION,0)),
SUM(ISNULL(REBATES,0)),
SUM(ISNULL(OTHERS,0)),
SUM(ISNULL(EURO_OVERRIDER,0)),
SUM(ISNULL(ACHD_RTL_VAL,0)),
SUM(ISNULL(SAL_QTY,0)),
SUM(ISNULL(PLU_RTL_VAL,0)),
SUM(ISNULL(CST_OF_SAL_VAL,0)),
SUM(ISNULL(TAB_MGN_RVN_VAL,0)),
SUM(ISNULL(TAB_MGN_VAL,0)),
SUM(ISNULL(COP_ADJ_VAL,0)),
SUM(ISNULL(UBC_ADJ_VAL,0)),
SUM(ISNULL(CVPN_ADJ_VAL,0)),
SUM(ISNULL(DSCT_VAL,0)),
SUM(ISNULL(SAL_TAX_VAL,0)),
SUM(ISNULL(SAL_TAX_NEW_VAL,0)),
SUM(ISNULL(CO_QTY_DLVY_ON_TM,0)),
SUM(ISNULL(CO_INIT_CALL_OFF_QTY,0)),
SUM(ISNULL(CO_ORIG_CNT_DLVY,0)),
SUM(ISNULL(CO_ORIG_ON_TM,0)),
SUM(ISNULL(CO_IN_FULL_DLVY_QTY,0)),
SUM(ISNULL(CO_IN_FULL_CALL_OFF_QTY,0)),
SUM(ISNULL(STM_MAT_CST_OF_SAL_VAL,0)),
SUM(ISNULL(STM_AVG_STK_CST_VAL,0)),
SUM(ISNULL(LDC_AVAIL_CNT,0)),
SUM(ISNULL(LDC_CNT,0)),
SUM(ISNULL(RCV_CST_PRC,0)),
SUM(ISNULL(QTY_RCV,0)),
SUM(ISNULL(PUR_CST_INTAKE,0)),
SUM(ISNULL(Smoothed_TAR,0)),
SUM(ISNULL(DPP_TAR,0)),
SUM(ISNULL(APPOR_BRANCH_COST,0)),
SUM(ISNULL(APPOR_SPACE,0)),
SUM(ISNULL(APPOR_FIXED_DC,0)),
SUM(ISNULL(Smoothed_DPP,0)),
SUM(ISNULL(Actual_DPP,0)),
PROD_TYPE,
SUM(ISNULL(PAID_DAYS_STOCK,0)),
SUM(ISNULL(WKLY_IN_STK,0)) ,
SUM(ISNULL(WKLY_BR_PRD_CNT,0)),
SUM(ISNULL(WKLY_F1_UBX,0)) ,
SUM(ISNULL(WKLY_SS_UBX,0)) ,
SUM(ISNULL(SFA_FCAST_5_QTY,0)) ,
SUM(ISNULL(SFA_ABS_DIFF_5_QTY,0)) ,
PROD_SGMT_CD,
SUM(ISNULL(QUASI_UBC,0)),
SUM(ISNULL(CPR_UBC,0)),
TERMS_CD,
DAYS,
SUM(ISNULL(NET_DELIV,0)),
SUM(ISNULL(TFER_QTY_MAT,0)),
SUM(ISNULL(SAL_QTY_MAT,0)),
SUM(ISNULL(WKLY_IN_STK_RDC,0)),
SUM(ISNULL(WKLY_DC_PRD_CNT_RDC,0)),
SUM(ISNULL(WKLY_IN_STK_CHDC,0)),
SUM(ISNULL(WKLY_DC_PRD_CNT_CHDC,0)),
SUM(ISNULL(SMOOTHED_MAT_TAR,0))
from findata_batch_BKP
GROUP BY
MER_ARA_CD,
FNCL_PD,
TRD_ARM_CD,
BRND_CD,
PROD_TYPE,
PROD_SGMT_CD,
TERMS_CD,
DAYS
INSERT INTO FINDATA_BATCH_TRACE VALUES ('MA_BRD INSERT FROM FIN DONE',GETDATE())
END--(1108790 row(s) affected) 7 min 54 sec -- to be tuned ,lot of warnings and error msg
--Insert In SUPPLIER/MA/PG/SPG/FP/TA aggregated Table
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('MA_SUPP INSERT FROM FIN',GETDATE())
INSERT INTO [FinData_Batch_MA_SUPP_BKP]
(
FNCL_PD,
PROD_CD,
TRD_ARM_CD,
BRND_CD,
PREF_PRCH_SUPP_CD,
MER_ARA_CD,
PLAN_GRP_CD,
SUB_PLAN_GRP_CD,
PNM_VAL,
TRD_MGN_VAL,
TRADING_ACCT_REV,
ADV_PROMOTION,
REBATES,
OTHERS,
EURO_OVERRIDER,
ACHD_RTL_VAL,
SAL_QTY,
PLU_RTL_VAL,
CST_OF_SAL_VAL,
TAB_MGN_RVN_VAL,
TAB_MGN_VAL,
COP_ADJ_VAL,
UBC_ADJ_VAL,
CVPN_ADJ_VAL,
DSCT_VAL,
SAL_TAX_VAL,
SAL_TAX_NEW_VAL,
CO_QTY_DLVY_ON_TM,
CO_INIT_CALL_OFF_QTY,
CO_ORIG_CNT_DLVY,
CO_ORIG_ON_TM,
CO_IN_FULL_DLVY_QTY,
CO_IN_FULL_CALL_OFF_QTY,
STM_MAT_CST_OF_SAL_VAL,
STM_AVG_STK_CST_VAL,
LDC_AVAIL_CNT,
LDC_CNT,
RCV_CST_PRC,
QTY_RCV,
PUR_CST_INTAKE,
Smoothed_TAR,
DPP_TAR,
APPOR_BRANCH_COST,
APPOR_SPACE,
APPOR_FIXED_DC,
Smoothed_DPP,
Actual_DPP,
PROD_TYPE,
PAID_DAYS_STOCK,
WKLY_IN_STK,
WKLY_BR_PRD_CNT,
WKLY_F1_UBX,
WKLY_SS_UBX,
SFA_FCAST_5_QTY,
SFA_ABS_DIFF_5_QTY,
PROD_SGMT_CD,
QUASI_UBC,
CPR_UBC,
TERMS_CD,
DAYS,
NET_DELIV,
TFER_QTY_MAT,
SAL_QTY_MAT,
WKLY_IN_STK_RDC,
WKLY_DC_PRD_CNT_RDC,
WKLY_IN_STK_CHDC,
WKLY_DC_PRD_CNT_CHDC,
SMOOTHED_MAT_TAR
)
SELECT
FNCL_PD,
NULL,
TRD_ARM_CD,
NULL,
PREF_PRCH_SUPP_CD,
MER_ARA_CD,
NULL ,
NULL,
SUM(ISNULL(PNM_VAL,0)),
SUM(ISNULL(TRD_MGN_VAL,0)),
SUM(ISNULL(TRADING_ACCT_REV,0)),
SUM(ISNULL(ADV_PROMOTION,0)),
SUM(ISNULL(REBATES,0)),
SUM(ISNULL(OTHERS,0)),
SUM(ISNULL(EURO_OVERRIDER,0)),
SUM(ISNULL(ACHD_RTL_VAL,0)),
SUM(ISNULL(SAL_QTY,0)),
SUM(ISNULL(PLU_RTL_VAL,0)),
SUM(ISNULL(CST_OF_SAL_VAL,0)),
SUM(ISNULL(TAB_MGN_RVN_VAL,0)),
SUM(ISNULL(TAB_MGN_VAL,0)),
SUM(ISNULL(COP_ADJ_VAL,0)),
SUM(ISNULL(UBC_ADJ_VAL,0)),
SUM(ISNULL(CVPN_ADJ_VAL,0)),
SUM(ISNULL(DSCT_VAL,0)),
SUM(ISNULL(SAL_TAX_VAL,0)),
SUM(ISNULL(SAL_TAX_NEW_VAL,0)),
SUM(ISNULL(CO_QTY_DLVY_ON_TM,0)),
SUM(ISNULL(CO_INIT_CALL_OFF_QTY,0)),
SUM(ISNULL(CO_ORIG_CNT_DLVY,0)),
SUM(ISNULL(CO_ORIG_ON_TM,0)),
SUM(ISNULL(CO_IN_FULL_DLVY_QTY,0)),
SUM(ISNULL(CO_IN_FULL_CALL_OFF_QTY,0)),
SUM(ISNULL(STM_MAT_CST_OF_SAL_VAL,0)),
SUM(ISNULL(STM_AVG_STK_CST_VAL,0)),
SUM(ISNULL(LDC_AVAIL_CNT,0)),
SUM(ISNULL(LDC_CNT,0)),
SUM(ISNULL(RCV_CST_PRC,0)),
SUM(ISNULL(QTY_RCV,0)),
SUM(ISNULL(PUR_CST_INTAKE,0)),
SUM(ISNULL(Smoothed_TAR,0)),
SUM(ISNULL(DPP_TAR,0)),
SUM(ISNULL(APPOR_BRANCH_COST,0)),
SUM(ISNULL(APPOR_SPACE,0)),
SUM(ISNULL(APPOR_FIXED_DC,0)),
SUM(ISNULL(Smoothed_DPP,0)),
SUM(ISNULL(Actual_DPP,0)),
PROD_TYPE,
SUM(ISNULL(PAID_DAYS_STOCK,0)),
SUM(ISNULL(WKLY_IN_STK,0)) ,
SUM(ISNULL(WKLY_BR_PRD_CNT,0)),
SUM(ISNULL(WKLY_F1_UBX,0)) ,
SUM(ISNULL(WKLY_SS_UBX,0)) ,
SUM(ISNULL(SFA_FCAST_5_QTY,0)) ,
SUM(ISNULL(SFA_ABS_DIFF_5_QTY,0)) ,
PROD_SGMT_CD,
SUM(ISNULL(QUASI_UBC,0)),
SUM(ISNULL(CPR_UBC,0)),
TERMS_CD,
DAYS,
SUM(ISNULL(NET_DELIV,0)),
SUM(ISNULL(TFER_QTY_MAT,0)),
SUM(ISNULL(SAL_QTY_MAT,0)),
SUM(ISNULL(WKLY_IN_STK_RDC,0)),
SUM(ISNULL(WKLY_DC_PRD_CNT_RDC,0)),
SUM(ISNULL(WKLY_IN_STK_CHDC,0)),
SUM(ISNULL(WKLY_DC_PRD_CNT_CHDC,0)),
SUM(ISNULL(SMOOTHED_MAT_TAR,0))
from findata_batch_BKP
GROUP BY
MER_ARA_CD,
FNCL_PD,
TRD_ARM_CD,
PREF_PRCH_SUPP_CD,
PROD_TYPE,
PROD_SGMT_CD,
TERMS_CD,
DAYS
INSERT INTO FINDATA_BATCH_TRACE VALUES ('MA_SUPP INSERT FROM FIN DONE',GETDATE())
END--(709968 row(s) affected) 8 MIN 04 SEC
--WEEKS COVER RELATED INSERTS
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('CEP_DM TA INSERT ',GETDATE())
INSERT INTO CEP_DM_WEEKSCOVER_TA
(
FNCL_PD,
PROD_CD,
TRD_ARM_CD,
DCS_OFFSET_WK,
DCS_CUM_DEMAND,
DCS_WK_DEMAND,
DCS_FREE_STOCK
)
SELECT
CE.FNCL_PD,
CE.PROD_CD,
CH.TRD_ARM_CD,
CE.DCS_OFFSET_WK,
CE.DCS_CUM_DEMAND,
CE.DCS_WK_DEMAND,
CE.DCS_FREE_STOCK
FROM CEP_DM_WEEKSCOVER CE INNER JOIN CHAIN CH
ON CE.CHN_CD = CH.CHN_CD
INSERT INTO FINDATA_BATCH_TRACE VALUES ('CEP_DM TA INSERT DONE',GETDATE())
END--(12329107 row(s) affected) 01 min 04 sec
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('CEP_DM PG_BRD INSERT ',GETDATE())
INSERT INTO CEP_DM_WEEKSCOVER_PG_BRD
(
FNCL_PD,
TRD_ARM_CD,
BRND_CD,
PLAN_GRP_CD,
DCS_OFFSET_WK,
DCS_CUM_DEMAND,
DCS_WK_DEMAND,
DCS_FREE_STOCK
)
SELECT
CW.FNCL_PD,
CW.TRD_ARM_CD,
PD.BRND_CD,
PD.PLAN_GRP_CD,
CW.DCS_OFFSET_WK,
SUM(CW.DCS_CUM_DEMAND),
SUM(CW.DCS_WK_DEMAND),
SUM(CW.DCS_FREE_STOCK)
FROM CEP_DM_WEEKSCOVER_TA CW INNER JOIN PROD PD
ON CW.PROD_CD = PD.PROD_CD
GROUP BY
CW.FNCL_PD,
CW.TRD_ARM_CD,
PD.BRND_CD,
PD.PLAN_GRP_CD,
CW.DCS_OFFSET_WK
INSERT INTO FINDATA_BATCH_TRACE VALUES ('CEP_DM PG_BRD INSERT DONE',GETDATE())
END--(1970106 row(s) affected) 52 sec
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('CEP_DM SPG_BRD INSERT ',GETDATE())
INSERT INTO CEP_DM_WEEKSCOVER_SPG_BRD
(
FNCL_PD,
TRD_ARM_CD,
BRND_CD,
PLAN_GRP_CD,
SUB_PLAN_GRP_CD,
DCS_OFFSET_WK,
DCS_CUM_DEMAND,
DCS_WK_DEMAND,
DCS_FREE_STOCK
)
SELECT
CW.FNCL_PD,
CW.TRD_ARM_CD,
PD.BRND_CD,
PD.PLAN_GRP_CD,
PD.SUB_PLAN_GRP_CD,
CW.DCS_OFFSET_WK,
SUM(CW.DCS_CUM_DEMAND),
SUM(CW.DCS_WK_DEMAND),
SUM(CW.DCS_FREE_STOCK)
FROM CEP_DM_WEEKSCOVER_TA CW INNER JOIN PROD PD
ON CW.PROD_CD = PD.PROD_CD
GROUP BY
CW.FNCL_PD,
CW.TRD_ARM_CD,
PD.BRND_CD,
PD.PLAN_GRP_CD,
PD.SUB_PLAN_GRP_CD,
CW.DCS_OFFSET_WK
INSERT INTO FINDATA_BATCH_TRACE VALUES ('CEP_DM SPG_BRD INSERT DONE',GETDATE())
END--(3038792 row(s) affected) 55 sec
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('CEP_DM PG_SUPP INSERT ',GETDATE())
INSERT INTO CEP_DM_WEEKSCOVER_PG_SUPP
(
FNCL_PD,
TRD_ARM_CD,
SUPP_CD,
PLAN_GRP_CD,
DCS_OFFSET_WK,
DCS_CUM_DEMAND,
DCS_WK_DEMAND,
DCS_FREE_STOCK
)
SELECT
CW.FNCL_PD,
CW.TRD_ARM_CD,
PD.PREF_PRCH_SUPP_CD,
PD.PLAN_GRP_CD,
CW.DCS_OFFSET_WK,
SUM(CW.DCS_CUM_DEMAND),
SUM(CW.DCS_WK_DEMAND),
SUM(CW.DCS_FREE_STOCK)
FROM CEP_DM_WEEKSCOVER_TA CW INNER JOIN PROD PD
ON CW.PROD_CD = PD.PROD_CD
GROUP BY
CW.FNCL_PD,
CW.TRD_ARM_CD,
PD.PREF_PRCH_SUPP_CD,
PD.PLAN_GRP_CD,
CW.DCS_OFFSET_WK
INSERT INTO FINDATA_BATCH_TRACE VALUES ('CEP_DM PG_SUPP INSERT DONE',GETDATE())
END--(1684546 row(s) affected) 40 sec
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('CEP_DM SPG_SUPP INSERT ',GETDATE())
INSERT INTO CEP_DM_WEEKSCOVER_SPG_SUPP
(
FNCL_PD,
TRD_ARM_CD,
SUPP_CD,
PLAN_GRP_CD,
SUB_PLAN_GRP_CD,
DCS_OFFSET_WK,
DCS_CUM_DEMAND,
DCS_WK_DEMAND,
DCS_FREE_STOCK
)
SELECT
CW.FNCL_PD,
CW.TRD_ARM_CD,
PD.PREF_PRCH_SUPP_CD,
PD.PLAN_GRP_CD,
PD.SUB_PLAN_GRP_CD,
CW.DCS_OFFSET_WK,
SUM(CW.DCS_CUM_DEMAND),
SUM(CW.DCS_WK_DEMAND),
SUM(CW.DCS_FREE_STOCK)
FROM CEP_DM_WEEKSCOVER_TA CW INNER JOIN PROD PD
ON CW.PROD_CD = PD.PROD_CD
GROUP BY
CW.FNCL_PD,
CW.TRD_ARM_CD,
PD.PREF_PRCH_SUPP_CD,
PD.PLAN_GRP_CD,
PD.SUB_PLAN_GRP_CD,
CW.DCS_OFFSET_WK
INSERT INTO FINDATA_BATCH_TRACE VALUES ('CEP_DM SPG_SUPP INSERT DONE',GETDATE())
END--(2509279 row(s) affected) 54 sec
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('CEP_DM MA_BRD INSERT ',GETDATE())
INSERT INTO CEP_DM_WEEKSCOVER_MA_BRD
(
FNCL_PD,
TRD_ARM_CD,
BRND_CD,
MER_ARA_CD,
DCS_OFFSET_WK,
DCS_CUM_DEMAND,
DCS_WK_DEMAND,
DCS_FREE_STOCK
)
SELECT
CW.FNCL_PD,
CW.TRD_ARM_CD,
PD.BRND_CD,
PD.MER_ARA_CD,
CW.DCS_OFFSET_WK,
SUM(CW.DCS_CUM_DEMAND),
SUM(CW.DCS_WK_DEMAND),
SUM(CW.DCS_FREE_STOCK)
FROM CEP_DM_WEEKSCOVER_TA CW INNER JOIN PROD PD
ON CW.PROD_CD = PD.PROD_CD
GROUP BY
CW.FNCL_PD,
CW.TRD_ARM_CD,
PD.BRND_CD,
PD.MER_ARA_CD,
CW.DCS_OFFSET_WK
INSERT INTO FINDATA_BATCH_TRACE VALUES ('CEP_DM MA_BRD INSERT DONE',GETDATE())
END--(977049 row(s) affected) 45 sec
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('CEP_DM MA_SUPP INSERT ',GETDATE())
INSERT INTO CEP_DM_WEEKSCOVER_MA_SUPP
(
FNCL_PD,
TRD_ARM_CD,
SUPP_CD,
MER_ARA_CD,
DCS_OFFSET_WK,
DCS_CUM_DEMAND,
DCS_WK_DEMAND,
DCS_FREE_STOCK
)
SELECT
CW.FNCL_PD,
CW.TRD_ARM_CD,
PD.PREF_PRCH_SUPP_CD,
PD.MER_ARA_CD,
CW.DCS_OFFSET_WK,
SUM(CW.DCS_CUM_DEMAND),
SUM(CW.DCS_WK_DEMAND),
SUM(CW.DCS_FREE_STOCK)
FROM CEP_DM_WEEKSCOVER_TA CW INNER JOIN PROD PD
ON CW.PROD_CD = PD.PROD_CD
GROUP BY
CW.FNCL_PD,
CW.TRD_ARM_CD,
PD.PREF_PRCH_SUPP_CD,
PD.MER_ARA_CD,
CW.DCS_OFFSET_WK
INSERT INTO FINDATA_BATCH_TRACE VALUES ('CEP_DM MA_SUPP INSERT DONE',GETDATE())
END--(828399 row(s) affected) 35 seconds
--master care Logic Implemented
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN PG_BRD UPDATE FROM MASTERCARE_BRND_PG',GETDATE())
UPDATE FinData_Batch_PG_BRD_BKP
SET PRED_FR = MAC.PRED_FR ,
RETURNS_RATE = MAC.RETURNS_RATE
FROM
FinData_Batch_PG_BRD_BKP FIN1,
(SELECT
MC.FNCL_PD,
MC.PLAN_GRP_CD,
MC.BRND_CD,
MC.PRED_FR PRED_FR ,
MC.RETURNS_RATE RETURNS_RATE
FROM
MASTERCARE_BRND_PG MC) MAC
WHERE
FIN1.fncl_pd COLLATE SQL_Latin1_General_CP1_CI_AS= MAC.fncl_pdCOLLATE SQL_Latin1_General_CP1_CI_AS
ANDFIN1.PLAN_GRP_CD COLLATE SQL_Latin1_General_CP1_CI_AS= MAC.PLAN_GRP_CD COLLATE SQL_Latin1_General_CP1_CI_AS
AND FIN1.BRND_CD COLLATE SQL_Latin1_General_CP1_CI_AS= MAC.BRND_CD COLLATE SQL_Latin1_General_CP1_CI_AS
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN PG_BRD UPDATE FROM MASTERCARE_BRND_PG DONE',GETDATE())
END--23394 rows 11 sec
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN PG_BRD INSERT FROM MASTERCARE_BRND_PG',GETDATE())
INSERT INTO
FinData_Batch_PG_BRD_BKP
(
FNCL_PD,
BRND_CD,
PLAN_GRP_CD,
PRED_FR ,
RETURNS_RATE
)
SELECT
MAC.FNCL_PD FNCL_PD,
MAC.BRND_CD BRND_CD,
MAC.PLAN_GRP_CD PLAN_GRP_CD,
MAC.PRED_FR,
MAC.RETURNS_RATE
FROM
(
SELECT
MC.FNCL_PD FNCL_PD,
MC.BRND_CD BRND_CD,
MC.PLAN_GRP_CD PLAN_GRP_CD,
MC.PRED_FR PRED_FR ,
MC.RETURNS_RATE RETURNS_RATE
FROM
MASTERCARE_BRND_PG MC
WHERE
NOT EXISTS (SELECT 1
FROM
FinData_Batch_PG_BRD_BKP FIN
WHERE
FIN.FNCL_PD COLLATE SQL_Latin1_General_CP1_CI_AS=MC.FNCL_PD COLLATE SQL_Latin1_General_CP1_CI_AS
AND FIN.PLAN_GRP_CD COLLATE SQL_Latin1_General_CP1_CI_AS=MC.PLAN_GRP_CD COLLATE SQL_Latin1_General_CP1_CI_AS
AND FIN.BRND_CD COLLATE SQL_Latin1_General_CP1_CI_AS = MC.BRND_CD COLLATE SQL_Latin1_General_CP1_CI_AS)
) MAC
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN PG_BRD UPDATE FROM MASTERCARE_BRND_PG DONE',GETDATE())
END --151 rows 1 sec
--INSERT
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN PG_SUPP UPDATE FROM MASTERCARE_SUPP_PG',GETDATE())
UPDATE FinData_Batch_PG_SUPP_BKP
SET PRED_FR = MAC.PRED_FR ,
RETURNS_RATE = MAC.RETURNS_RATE
FROM
FinData_Batch_PG_SUPP_BKP FIN1,
(SELECT
MC.FNCL_PD,
MC.SUPP_CD,
MC.PLAN_GRP_CD,
MC.PRED_FR PRED_FR ,
MC.RETURNS_RATE RETURNS_RATE
FROM
MASTERCARE_SUPP_PG MC
) MAC
WHERE
FIN1.fncl_pd COLLATE SQL_Latin1_General_CP1_CI_AS = MAC.fncl_pd COLLATE SQL_Latin1_General_CP1_CI_AS
AND FIN1.PLAN_GRP_CD COLLATE SQL_Latin1_General_CP1_CI_AS= MAC.PLAN_GRP_CD COLLATE SQL_Latin1_General_CP1_CI_AS
AND FIN1.PREF_PRCH_SUPP_CD COLLATE SQL_Latin1_General_CP1_CI_AS = MAC.SUPP_CD COLLATE SQL_Latin1_General_CP1_CI_AS
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN PG_SUPP UPDATE FROM MASTERCARE_SUPP_PG DONE',GETDATE())
END--16974 rows 8 sec
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN PG_SUPP INSERT FROM MASTERCARE_SUPP_PG',GETDATE())
INSERT INTO
FinData_Batch_PG_SUPP_BKP
(
FNCL_PD,
PLAN_GRP_CD,
PREF_PRCH_SUPP_CD,
PRED_FR ,
RETURNS_RATE
)
SELECT
MAC.FNCL_PD FNCL_PD,
MAC.PLAN_GRP_CD PLAN_GRP_CD,
MAC.SUPP_CD SUPP_CD,
MAC.PRED_FR PRED_FR,
MAC.RETURNS_RATE RETURNS_RATE
FROM
(
SELECT
MC.FNCL_PD FNCL_PD,
MC.PLAN_GRP_CD PLAN_GRP_CD,
MC.SUPP_CD SUPP_CD,
MC.PRED_FR PRED_FR ,
MC.RETURNS_RATE RETURNS_RATE
FROM
MASTERCARE_SUPP_PG MC
WHERE
NOT EXISTS (SELECT 1
FROM
FinData_Batch_PG_SUPP_BKP FIN
WHERE
FIN.FNCL_PD COLLATE SQL_Latin1_General_CP1_CI_AS = MC.FNCL_PD COLLATE SQL_Latin1_General_CP1_CI_AS
AND FIN.PLAN_GRP_CD COLLATE SQL_Latin1_General_CP1_CI_AS = MC.PLAN_GRP_CD COLLATE SQL_Latin1_General_CP1_CI_AS
AND FIN.PREF_PRCH_SUPP_CD COLLATE SQL_Latin1_General_CP1_CI_AS = MC.SUPP_CD COLLATE SQL_Latin1_General_CP1_CI_AS)
) MAC
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN PG_SUPP INSERT FROM MASTERCARE_SUPP_PG DONE',GETDATE())
END--219 ROWS 1 SEC
--INSERT
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN SPG_BRD UPDATE FROM MASTERCARE_BRND_SPG',GETDATE())
UPDATE FinData_Batch_SPG_BRD_BKP
SET PRED_FR = MAC.PRED_FR ,
RETURNS_RATE = MAC.RETURNS_RATE
FROM
FinData_Batch_SPG_BRD_BKP FIN1,
(SELECT
MC.FNCL_PD,
MC.BRND_CD,
MC.PLAN_GRP_CD,
MC.SUB_PLAN_GRP_CD,
MC.PRED_FR PRED_FR ,
MC.RETURNS_RATE RETURNS_RATE
FROM
MASTERCARE_BRND_SPG MC
) MAC
WHERE
FIN1.fncl_pd COLLATE SQL_Latin1_General_CP1_CI_AS = MAC.fncl_pd COLLATE SQL_Latin1_General_CP1_CI_AS
and FIN1.BRND_CD COLLATE SQL_Latin1_General_CP1_CI_AS =MAC.BRND_CD COLLATE SQL_Latin1_General_CP1_CI_AS
AND FIN1.PLAN_GRP_CD COLLATE SQL_Latin1_General_CP1_CI_AS = MAC.PLAN_GRP_CD COLLATE SQL_Latin1_General_CP1_CI_AS
AND FIN1.SUB_PLAN_GRP_CD COLLATE SQL_Latin1_General_CP1_CI_AS = MAC.SUB_PLAN_GRP_CD COLLATE SQL_Latin1_General_CP1_CI_AS
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN SPG_BRD UPDATE FROM MASTERCARE_BRND_SPG DONE',GETDATE())
END--(20410 row(s) affected) 17 Sec
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN SPG_BRD INSERT FROM MASTERCARE_BRND_SPG ',GETDATE())
INSERT INTO
FinData_Batch_SPG_BRD_BKP
(
FNCL_PD,
BRND_CD,
PLAN_GRP_CD,
SUB_PLAN_GRP_CD,
PRED_FR ,
RETURNS_RATE
)
SELECT
MAC.FNCL_PD FNCL_PD,
MAC.BRND_CD BRND_CD,
MAC.PLAN_GRP_CD PLAN_GRP_CD,
MAC.SUB_PLAN_GRP_CD SUB_PLAN_GRP_CD,
MAC.PRED_FR PRED_FR,
MAC.RETURNS_RATE RETURNS_RATE
FROM
(
SELECT
MC.FNCL_PD FNCL_PD,
MC.BRND_CD BRND_CD,
MC.PLAN_GRP_CD PLAN_GRP_CD,
MC.SUB_PLAN_GRP_CD SUB_PLAN_GRP_CD,
MC.PRED_FR PRED_FR ,
MC.RETURNS_RATE RETURNS_RATE
FROM
MASTERCARE_BRND_SPG MC
WHERE
NOT EXISTS (SELECT 1
FROM
FinData_Batch_SPG_BRD_BKP FIN
WHERE
FIN.FNCL_PD COLLATE SQL_Latin1_General_CP1_CI_AS =MC.FNCL_PD COLLATE SQL_Latin1_General_CP1_CI_AS
AND FIN.BRND_CD COLLATE SQL_Latin1_General_CP1_CI_AS=MC.BRND_CD COLLATE SQL_Latin1_General_CP1_CI_AS
AND FIN.PLAN_GRP_CD COLLATE SQL_Latin1_General_CP1_CI_AS=MC.PLAN_GRP_CD COLLATE SQL_Latin1_General_CP1_CI_AS
AND FIN.SUB_PLAN_GRP_CD COLLATE SQL_Latin1_General_CP1_CI_AS=MC.SUB_PLAN_GRP_CD COLLATE SQL_Latin1_General_CP1_CI_AS)
) MAC
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN SPG_BRD INSERT FROM MASTERCARE_BRND_SPG DONE',GETDATE())
END--765 rows 12 sec
--INSERT
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN SPG_SUPP UPDATE FROM MASTERCARE_SUPP_SPG',GETDATE())
UPDATE FinData_Batch_SPG_SUPP_BKP
SET PRED_FR = MAC.PRED_FR ,
RETURNS_RATE = MAC.RETURNS_RATE
FROM
FinData_Batch_SPG_SUPP_BKP FIN1,
(SELECT
MC.FNCL_PD,
MC.SUPP_CD,
MC.PLAN_GRP_CD,
MC.SUB_PLAN_GRP_CD,
MC.PRED_FR,
MC.RETURNS_RATE
FROM
MASTERCARE_SUPP_SPG MC
) MAC
WHERE
FIN1.fncl_pd COLLATE SQL_Latin1_General_CP1_CI_AS= MAC.fncl_pd COLLATE SQL_Latin1_General_CP1_CI_AS
AND FIN1.PREF_PRCH_SUPP_CD COLLATE SQL_Latin1_General_CP1_CI_AS = MAC.SUPP_CD COLLATE SQL_Latin1_General_CP1_CI_AS
and FIN1.PLAN_GRP_CD COLLATE SQL_Latin1_General_CP1_CI_AS = MAC.PLAN_GRP_CD COLLATE SQL_Latin1_General_CP1_CI_AS
AND FIN1.SUB_PLAN_GRP_CD COLLATE SQL_Latin1_General_CP1_CI_AS = MAC.SUB_PLAN_GRP_CD COLLATE SQL_Latin1_General_CP1_CI_AS
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN SPG_SUPP UPDATE FROM MASTERCARE_SUPP_SPG DONE',GETDATE())
END--(15838 row(s) affected) 14 sec
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN SPG_SUPP INSERT FROM MASTERCARE_SUPP_SPG ',GETDATE())
INSERT INTO
FinData_Batch_SPG_SUPP_BKP
(
FNCL_PD,
PREF_PRCH_SUPP_CD,
PLAN_GRP_CD,
SUB_PLAN_GRP_CD,
PRED_FR ,
RETURNS_RATE
)
SELECT
MAC.FNCL_PD FNCL_PD,
MAC.SUPP_CD SUPP_CD,
MAC.PLAN_GRP_CD PLAN_GRP_CD,
MAC.SUB_PLAN_GRP_CD SUB_PLAN_GRP_CD,
MAC.PRED_FR,
MAC.RETURNS_RATE
FROM
(
SELECT
MC.FNCL_PD FNCL_PD,
MC.SUPP_CD SUPP_CD,
MC.PLAN_GRP_CD PLAN_GRP_CD,
MC.SUB_PLAN_GRP_CD SUB_PLAN_GRP_CD,
MC.PRED_FR PRED_FR ,
MC.RETURNS_RATE RETURNS_RATE
FROM
MASTERCARE_SUPP_SPG MC
WHERE
NOT EXISTS (SELECT 1
FROM
FinData_Batch_SPG_SUPP_BKP FIN
WHERE
FIN.FNCL_PD COLLATE SQL_Latin1_General_CP1_CI_AS =MC.FNCL_PD COLLATE SQL_Latin1_General_CP1_CI_AS
AND FIN.PREF_PRCH_SUPP_CD COLLATE SQL_Latin1_General_CP1_CI_AS = MC.SUPP_CD COLLATE SQL_Latin1_General_CP1_CI_AS
AND FIN.PLAN_GRP_CD COLLATE SQL_Latin1_General_CP1_CI_AS =MC.PLAN_GRP_CD COLLATE SQL_Latin1_General_CP1_CI_AS
AND FIN.SUB_PLAN_GRP_CD COLLATE SQL_Latin1_General_CP1_CI_AS=MC.SUB_PLAN_GRP_CD COLLATE SQL_Latin1_General_CP1_CI_AS)
) MAC
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN SPG_SUPP INSERT FROM MASTERCARE_SUPP_SPG DONE',GETDATE())
END--(826 row(s) affected) 9 sec
--INSERT
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN MA_SUPP UPDATE FROM MASTERCARE_SUPP_MA ',GETDATE())
UPDATE FinData_Batch_MA_SUPP_BKP
SET PRED_FR = MAC.PRED_FR ,
RETURNS_RATE = MAC.RETURNS_RATE
FROM
FinData_Batch_MA_SUPP_BKP FIN1,
(SELECT
MC.FNCL_PD,
MC.MER_ARA_CD,
MC.SUPP_CD,
MC.PRED_FR PRED_FR ,
MC.RETURNS_RATE RETURNS_RATE
FROM
MASTERCARE_SUPP_MA MC
) MAC
WHERE
FIN1.fncl_pd COLLATE SQL_Latin1_General_CP1_CI_AS = MAC.fncl_pd COLLATE SQL_Latin1_General_CP1_CI_AS
AND FIN1.MER_ARA_CD COLLATE SQL_Latin1_General_CP1_CI_AS=MAC.MER_ARA_CD COLLATE SQL_Latin1_General_CP1_CI_AS
AND FIN1.PREF_PRCH_SUPP_CD COLLATE SQL_Latin1_General_CP1_CI_AS=MAC.SUPP_CD COLLATE SQL_Latin1_General_CP1_CI_AS
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN MA_SUPP UPDATE FROM MASTERCARE_SUPP_MA DONE',GETDATE())
END--(11843 row(s) affected) 6 sec
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN MA_SUPP INSERT FROM MASTERCARE_SUPP_MA ',GETDATE())
INSERT INTO
FinData_Batch_MA_SUPP_BKP
(
FNCL_PD,
PREF_PRCH_SUPP_CD,
MER_ARA_CD,
PRED_FR ,
RETURNS_RATE
)
SELECT
MAC.FNCL_PD FNCL_PD,
MAC.SUPP_CD SUPP_CD,
MAC.MER_ARA_CD MER_ARA_CD,
MAC.PRED_FR,
MAC.RETURNS_RATE
FROM
(
SELECT
MC.FNCL_PD FNCL_PD,
MC.SUPP_CD SUPP_CD,
MC.MER_ARA_CD MER_ARA_CD,
MC.PRED_FR PRED_FR ,
MC.RETURNS_RATE RETURNS_RATE
FROM
MASTERCARE_SUPP_MA MC
WHERE
NOT EXISTS (SELECT 1
FROM
FinData_Batch_MA_SUPP_BKP FIN
WHERE
FIN.FNCL_PD COLLATE SQL_Latin1_General_CP1_CI_AS =MC.FNCL_PD COLLATE SQL_Latin1_General_CP1_CI_AS
AND FIN.MER_ARA_CD COLLATE SQL_Latin1_General_CP1_CI_AS =MC.MER_ARA_CD COLLATE SQL_Latin1_General_CP1_CI_AS
AND FIN.PREF_PRCH_SUPP_CD COLLATE SQL_Latin1_General_CP1_CI_AS= MC.SUPP_CD COLLATE SQL_Latin1_General_CP1_CI_AS)
) MAC
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN MA_SUPP INSERT FROM MASTERCARE_SUPP_MA DONE',GETDATE())
END --355 ROWS 0 sec
--INSERT
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN MA_BRD UPDATE FROM MASTERCARE_BRND_MA ',GETDATE())
UPDATE FinData_Batch_MA_BRD_BKP
SET PRED_FR = MAC.PRED_FR ,
RETURNS_RATE = MAC.RETURNS_RATE
FROM
FinData_Batch_MA_BRD_BKP FIN1,
(SELECT
MC.FNCL_PD,
MC.MER_ARA_CD,
MC.BRND_CD,
MC.PRED_FR PRED_FR ,
MC.RETURNS_RATE RETURNS_RATE
FROM
MASTERCARE_BRND_MA MC
) MAC
WHERE
FIN1.fncl_pd COLLATE SQL_Latin1_General_CP1_CI_AS= MAC.fncl_pd COLLATE SQL_Latin1_General_CP1_CI_AS
AND FIN1.MER_ARA_CD COLLATE SQL_Latin1_General_CP1_CI_AS=MAC.MER_ARA_CD COLLATE SQL_Latin1_General_CP1_CI_AS
AND FIN1.BRND_CD COLLATE SQL_Latin1_General_CP1_CI_AS=MAC.BRND_CD COLLATE SQL_Latin1_General_CP1_CI_AS
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN MA_BRD UPDATE FROM MASTERCARE_BRND_MA DONE',GETDATE())
END--(20535 row(s) affected) 9 SEC
BEGIN
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN MA_BRD INSERT FROM MASTERCARE_BRND_MA ',GETDATE())
INSERT INTO
FinData_Batch_MA_BRD_BKP
(
FNCL_PD,
BRND_CD,
MER_ARA_CD,
PRED_FR ,
RETURNS_RATE
)
SELECT
MAC.FNCL_PD FNCL_PD,
MAC.BRND_CD BRND_CD,
MAC.MER_ARA_CD MER_ARA_CD,
MAC.PRED_FR,
MAC.RETURNS_RATE
FROM
(
SELECT
MC.FNCL_PD FNCL_PD,
MC.BRND_CD BRND_CD,
MC.MER_ARA_CD MER_ARA_CD,
MC.PRED_FR PRED_FR ,
MC.RETURNS_RATE RETURNS_RATE
FROM
MASTERCARE_BRND_MA MC
WHERE
NOT EXISTS (SELECT 1
FROM
FinData_Batch_MA_BRD_BKP FIN
WHERE
FIN.FNCL_PD COLLATE SQL_Latin1_General_CP1_CI_AS=MC.FNCL_PD COLLATE SQL_Latin1_General_CP1_CI_AS
AND FIN.MER_ARA_CD COLLATE SQL_Latin1_General_CP1_CI_AS=MC.MER_ARA_CD COLLATE SQL_Latin1_General_CP1_CI_AS
AND FIN.BRND_CD COLLATE SQL_Latin1_General_CP1_CI_AS = MC.BRND_CD COLLATE SQL_Latin1_General_CP1_CI_AS)
) MAC
INSERT INTO FINDATA_BATCH_TRACE VALUES ('FIN MA_BRD INSERT FROM MASTERCARE_BRND_MA DONE',GETDATE())
END--395 rows 1 sec
--Truncate Work Table
execute sprTruncateWorkTables
exec sprPNMRowCount
-- exec sprTruncateFinTables
COMMIT TRAN
--RETURN 1 ON SUCCESS
RETURN 1
END TRY
BEGIN CATCH --TRAN_ABORT
ROLLBACK
--RETURN 0 ON FAILURE
RETURN 0
END CATCH
END
--exec dbo.sprFinData_Batch
--SELECT * FROM FINDATA_BATCH
March 24, 2009 at 8:27 am
All this work must be done in one transaction?
Try to cut into several pieces the process, and try to have more shorter transactions.
March 24, 2009 at 8:29 am
Based on your procedure you have everything wrapped up into a single transaction (begin tran at the beginning, commit at the end). You could invariably keep the log size smaller by breaking these up into smaller transactions. Obviously that may not be possible depending on your business rules and the potential of this failing in the middle may require a full rollback of everything. The way you are doing this right now everything has to be stored in the log to guarantee transactional consistency. If you are comfortable with that not being the case break this up into smaller transactions and you should be good to go.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply