Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Non plan Sy$(1)
$(Non_I2_Projection) =
sum(if(TY_Non_I2_Proj_Sales_Type = 'S',
if(TY_Non_I2_Proj_CMI_RPLN_IND<>'Y',
if(TY_Non_I2_Proj_Obsolete_IND <> 'Y',
if(TY_Non_I2_Proj_Generic ='No',
if(TY_Non_I2_Proj_Sales_TY_Proj_IND='Y',TY_Non_I2_Proj_Sales_Qty*TY_Non_I2_Proj_EMMA_Price,0),
if(TY_Non_I2_Proj_Sales_TY_Proj_IND='Y',TY_Non_I2_Proj_Sales_Value,0)),0),0),0))
NYNONI2ProjDataLoad:
Load
SALES_SOS_3M_ID as L_NY_Non_I2_Proj_SOS_3M_ID
,if(Sales_YTD_IND='Y',date(monthstart(addmonths(Sales_Date,+12)),'YYYY-MM-DD'),
date(monthstart(addmonths(Sales_Date,+24)),'YYYY-MM-DD')) as L_NY_Non_I2_Projection_Date
,SALES_CTRY_ID & Sales_Commodity_Id & Sales_Date as L_NY_Non_I2_Proj_CTRY_COMM_DATE_KEY
,SALES_CTRY_ID as L_NY_Non_I2_Proj_CTRY_ID
,Sales_Commodity_Id as L_NY_Non_I2_Proj_Commodity_Id
,Sales_CMI_RPLN_IND as L_NY_Non_I2_Proj_CMI_RPLN_IND
,Sales_Obsolete_IND as L_NY_Non_I2_Proj_Obsolete_IND
,Sales_Local_Obsolete_IND as L_NY_Non_I2_Proj_Local_Obsolete_IND
,Sales_Prod_Generic_Ind as L_NY_Non_I2_Proj_Generic
,Sales_Date as L_NY_Non_I2_Proj_Orig_Sales_Date
,Sales_Value as L_NY_Non_I2_Proj_Sales_Value
,Sales_Qty as L_NY_Non_I2_Proj_Sales_Qty
,Sales_Fig_Qty as L_NY_Non_I2_Proj_Sales_Fig_Qty
,Sales_NY_Proj_IND as L_NY_Non_I2_Proj_Sales_TY_Proj_IND
,Rebates_NY_Proj_IND as L_NY_Non_I2_Proj_Rebates_TY_Proj_IND
,Sales_EMMA_Price as L_NY_Non_I2_Proj_EMMA_Price
,Sales_EMMA_Price_Net as L_NY_Non_I2_Proj_EMMA_Price_Net
,Sales_Type as L_NY_Non_I2_Proj_Sales_Type
,Sales_AT_LCL_COST_FDR as L_NY_Non_I2_Proj_Sales_LCL_COST_AMT
,Sales_SC_UNIT_AMT_FDR as L_NY_Non_I2_Proj_Sales_COST_Unit_AMT
Resident ESISData
where Sales_CMI_RPLN_IND <> 'Y' and Sales_Local_Obsolete_IND = 'N' and Sales_Type ='S' and
Sales_NY_Proj_IND = 'Y' ;
NYNONI2ProjData:
Load
L_NY_Non_I2_Proj_SOS_3M_ID as NY_Non_I2_Proj_SOS_3M_ID
,L_NY_Non_I2_Proj_SOS_3M_ID &
L_NY_Non_I2_Proj_CTRY_ID &
L_NY_Non_I2_Projection_Date as NY_Non_I2_Proj_SOS_3M_ID_CTRY_DATE_KEY
,L_NY_Non_I2_Proj_CTRY_ID &
L_NY_Non_I2_Proj_Commodity_Id &
L_NY_Non_I2_Proj_Orig_Sales_Date as NY_Non_I2_Proj_CTRY_COMM_DATE_KEY
,L_NY_Non_I2_Proj_CTRY_ID as NY_Non_I2_Proj_CTRY_ID
,L_NY_Non_I2_Projection_Date as NY_Non_I2_Projection_Date
,L_NY_Non_I2_Proj_Commodity_Id as NY_Non_I2_Proj_Commodity_Id
,L_NY_Non_I2_Proj_CMI_RPLN_IND as NY_Non_I2_Proj_CMI_RPLN_IND
,L_NY_Non_I2_Proj_Obsolete_IND as NY_Non_I2_Proj_Obsolete_IND
,L_NY_Non_I2_Proj_Local_Obsolete_IND as NY_Non_I2_Proj_Local_Obsolete_IND
,L_NY_Non_I2_Proj_Generic as NY_Non_I2_Proj_Generic
,L_NY_Non_I2_Proj_Orig_Sales_Date as NY_Non_I2_Proj_Orig_Sales_Date
,L_NY_Non_I2_Proj_Sales_Value as NY_Non_I2_Proj_Sales_Value_Orig
,L_NY_Non_I2_Proj_Sales_Qty as NY_Non_I2_Proj_Sales_Qty_Orig
,L_NY_Non_I2_Proj_Sales_Fig_Qty as NY_Non_I2_Proj_Sales_Fig_Qty_Orig
,L_NY_Non_I2_Proj_Sales_TY_Proj_IND as NY_Non_I2_Proj_Sales_TY_Proj_IND
,L_NY_Non_I2_Proj_Rebates_TY_Proj_IND as NY_Non_I2_Proj_Rebates_TY_Proj_IND
,L_NY_Non_I2_Proj_EMMA_Price as NY_Non_I2_Proj_EMMA_Price
,L_NY_Non_I2_Proj_EMMA_Price_Net as NY_Non_I2_Proj_EMMA_Price_Net
,L_NY_Non_I2_Proj_Sales_Type as NY_Non_I2_Proj_Sales_Type
,L_NY_Non_I2_Proj_Sales_LCL_COST_AMT as NY_Non_I2_Proj_Sales_LCL_COST_AMT
,L_NY_Non_I2_Proj_Sales_COST_Unit_AMT as NY_Non_I2_Proj_Sales_COST_Unit_AMT
,rowno() as NY_Non_I2_Proj_Row_Nbr
Resident NYNONI2ProjDataLoad;
Drop Table NYNONI2ProjDataLoad;
//----------------------------------------------------------------------------------------------------
// Exclude Projection for Non I2 items defined ind Excel sheet
//----------------------------------------------------------------------------------------------------
//-------------------------------------------------------------
// Step 1 - Add 1 Comm & 1 Ctry Indicators
//-------------------------------------------------------------
left Join (NYNONI2ProjData)
load
COMM_CODE as NY_Non_I2_Proj_Commodity_Id
, CTRY_CODE as NY_Non_I2_Proj_CTRY_ID
, EXCL_PROJ_LVL_1_IND as NY_Non_I2_Proj_Excl_Lvl_1
Resident SOP_STRUCTURE
Where COMM_CODE <> 'ALL' and LEN(CTRY_CODE) = 2
;
//-------------------------------------------------------------
// Step 2 - Add 1 Comm & All Countries Indicators
//-------------------------------------------------------------
left Join (NYNONI2ProjData)
load
COMM_CODE as NY_Non_I2_Proj_Commodity_Id
, DIV_CODE as NY_Non_I2_Proj_DIV_CODE
, BUS_CODE as NY_Non_I2_Proj_BUS_CODE
, EXCL_PROJ_LVL_2_IND as NY_Non_I2_Proj_Excl_Lvl_2
, EXCL_PROJ_LVL_5_IND as NY_Non_I2_Proj_Excl_Lvl_5
, EXCL_PROJ_LVL_6_IND as NY_Non_I2_Proj_Excl_Lvl_6
Resident SOP_STRUCTURE
Where COMM_CODE <> 'ALL'
;
//-------------------------------------------------------------
// Step 3 - Add indicator for all Comm & 1 Country (1 divsions)
//-------------------------------------------------------------
left Join (NYNONI2ProjData)
load
DIV_CODE as NY_Non_I2_Proj_DIV_CODE
, CTRY_CODE as NY_Non_I2_Proj_CTRY_ID
, EXCL_PROJ_LVL_3_IND as NY_Non_I2_Proj_Excl_Lvl_3
Resident SOP_STRUCTURE
Where COMM_CODE = 'ALL' and LEN(DIV_CODE)> 0
;
//-------------------------------------------------------------
// Step 4 - Add indicator for all Comm & 1 Country (1 Business)
//-------------------------------------------------------------
left Join (NYNONI2ProjData)
load
BUS_CODE as NY_Non_I2_Proj_BUS_CODE
, CTRY_CODE as NY_Non_I2_Proj_CTRY_ID
, EXCL_PROJ_LVL_4_IND as NY_Non_I2_Proj_Excl_Lvl_4
Resident SOP_STRUCTURE
Where COMM_CODE = 'ALL' and LEN(BUS_CODE)> 0
;
//-------------------------------------------------------------
// Step 5 - Set line Excl indicator
//-------------------------------------------------------------
left Join (NYNONI2ProjData)
load
NY_Non_I2_Proj_Row_Nbr
, if(NY_Non_I2_Proj_Excl_Lvl_1 <> null(),NY_Non_I2_Proj_Excl_Lvl_1,
if(NY_Non_I2_Proj_Excl_Lvl_2 <> null(),NY_Non_I2_Proj_Excl_Lvl_2,
if(NY_Non_I2_Proj_Excl_Lvl_3 <> null(),NY_Non_I2_Proj_Excl_Lvl_3,
if(NY_Non_I2_Proj_Excl_Lvl_4 <> null(),NY_Non_I2_Proj_Excl_Lvl_4,
if(NY_Non_I2_Proj_Excl_Lvl_5 <> null(),NY_Non_I2_Proj_Excl_Lvl_5,
if(NY_Non_I2_Proj_Excl_Lvl_6 <> null(),NY_Non_I2_Proj_Excl_Lvl_6,'N'))))))
as NY_Non_I2_Proj_Excl_Ind
Resident NYNONI2ProjData;
//-------------------------------------------------------------
// Step 6 - Reset values & qty depending on Line Excl indicator
//-------------------------------------------------------------
left Join (NYNONI2ProjData)
load
NY_Non_I2_Proj_Row_Nbr
, if(NY_Non_I2_Proj_Excl_Ind='Y',0,NY_Non_I2_Proj_Sales_Value_Orig) as NY_Non_I2_Proj_Sales_Value
, if(NY_Non_I2_Proj_Excl_Ind='Y',0,NY_Non_I2_Proj_Sales_Qty_Orig) as NY_Non_I2_Proj_Sales_Qty
, if(NY_Non_I2_Proj_Excl_Ind='Y',0,NY_Non_I2_Proj_Sales_Fig_Qty_Orig) as NY_Non_I2_Proj_Sales_Fig_Qty
Resident NYNONI2ProjData;
//--------------------------------------------------------------------------