here it is the field i'm referring to [BF-1.A.Amount] can be found in BFF table below
TRACE '//~~~~~~~~~~~~~~~~~~~Cycle 4.1 Started~~~~~~~~~~~~~~~~~~~~~~~//';
//~~~~~~~~~~~~~~4 GL for CE and interest for int allocation~~~~~~~~//
//-----Cycle 4 Sender Company code data filter-------
BUKRSFilter:
LOAD * INLINE [
BUKRSFilter,
2200-ELC1
1500-FRG1
];
QUALIFY *;
UNQUALIFY %KeySalesTotalYear,%KeySalesMonth, %KeySalesMonthPlant, %KeyC4VELPlantRec,%KeyC4VEL4PlantRec;
A:
LOAD
Only(RRCTY) as RRCTY ,
Only(RVERS) as RVERS ,
Only(RYEAR) as RYEAR ,
Sum(Amount) as Amount ,
Only(BUKRS) as BUKRS ,
Only(Month) as Month
//-Only([Acct Code]) as [Acct Code]
Group by
RRCTY ,
RVERS ,
RYEAR ,
BUKRS ,
Month ;
//- [Acct Code] ;
LOAD
// RVERS&'-'&RYEAR as %KeySalesTotalYear,
// RVERS&'-'&RYEAR&'-'&Month as %KeySalesMonth,
// RVERS&'-'&RYEAR&'-'&Month&'-'&'Z001' as %KeySalesMonthPlant,
RBUSA ,
RRCTY,
RVERS,
RYEAR ,
Amount ,
// 'Z001' as BUKRS ,
BUKRS,
Month ,
[Acct Code]
Resident GLT0_2 Where not Exists(BUKRSFilter,BUKRS&'-'&RBUSA) and %FlagC4VELPlantNum_C4=1 and %FlagC4VELINTRGLNum=1 and RRCTY=0;
Join (A)
LOAD * INLINE [
Acct Code1, RRCTY1, RVERS1, %FlagReport, FlagCycle, Factor
GM01150, 0, 1, BS, S, 1
GE02054, 0, 1, PL, S, -1
GM01150, 0, 1, BS, R, -1
GE02054, 0, 1, PL, R, 1
];
UNQUALIFY*;
//~~~~~~~~~~~~~Sending Company Code~~~~~~~~~~~~~~~//
C4Sending:
Concatenate(GLT0_2)
LOAD
'C4.11' as %FlagCycle,
1 as %FlagC4VELPlantNum_C4,
If([INLFED.Acct Code1]='GE02054',1,0) as %FlagC4VELINTRGLNum,
If([INLFED.Acct Code1]='GE02054',1,0) as %FlagC10PLGLNum,
A.RRCTY as RRCTY,
A.RVERS as RVERS ,
A.RYEAR as RYEAR ,
INLFED.Factor*A.Amount as Amount,
A.BUKRS as BUKRS ,
A.Month as Month ,
[INLFED.Acct Code1] as [Acct Code]
Resident A Where [INLFED.FlagCycle] ='S';
//~~~~~~~~~~~~~Receiveing Company Code~~~~~~~~~~~~~~~//
C4Receiveing:
Concatenate (GLT0_2)
LOAD
'C4.11' as %FlagCycle,
// 1 as %FlagC4VELPlantNum_C4,
If([INLFED.Acct Code1]='GE02054',1,0) as %FlagC4VELINTRGLNum,
If([INLFED.Acct Code1]='GE02054',1,0) as %FlagC10PLGLNum,
A.RRCTY as RRCTY,
A.RVERS as RVERS ,
A.RYEAR as RYEAR ,
INLFED.Factor*A.Amount as Amount,
'Z001' as BUKRS ,
A.Month as Month ,
[INLFED.Acct Code1] as [Acct Code]
Resident A Where [INLFED.FlagCycle] ='R';
//~~~~~~~~~~~~~Sending Company Code~~~~~~~~~~~~~~~//
Concatenate (GLT0_2)
LOAD
'C4.12' as %FlagCycle,
// 1 as %FlagC4VELPlantNum_C4,
If([INLFED.Acct Code1]='GE02054',1,0) as %FlagC4VELINTRGLNum,
If([INLFED.Acct Code1]='GE02054',1,0) as %FlagC10PLGLNum,
A.RRCTY as RRCTY,
A.RVERS as RVERS ,
A.RYEAR as RYEAR ,
INLFED.Factor*A.Amount as Amount,
'Z001' as BUKRS ,
A.Month as Month ,
[INLFED.Acct Code1] as [Acct Code]
Resident A Where [INLFED.FlagCycle] ='S';
//~~~~~~~~~~~~~Fiter data for 22 Plant and for Capital Employed GL(934)~~~~~~~~~~~~~~~~~//
UNQUALIFY*;
BFilter:
LOAD
BUKRS&'-'&[Acct Code] as %KeyC4VELPlantRec
Resident GLT0_2 Where %FlagC4CEALLPlantGLNum=1 and %FlagC4VELPlantNum_C4=1;
B:
LOAD
// 'All' as Flag,
RVERS&'-'&RYEAR as %KeySalesTotalYear,
RVERS&'-'&RYEAR&'-'&Month as %KeySalesMonth,
RVERS&'-'&RYEAR&'-'&Month&'-'&'Z001' as %KeySalesMonthPlant,
RBUSA ,
RLDNR ,
RRCTY ,
RTCUR ,
RVERS ,
RYEAR ,
Amount ,
BUKRS ,
DRCRK ,
HSLVT ,
Month ,
[Acct Code]
Resident GLT0_2 Where Exists (%KeyC4VELPlantRec,BUKRS&'-'&[Acct Code]) and not Exists(BUKRSFilter,BUKRS&'-'&RBUSA) and RRCTY=0 and %FlagCycle<>'C4.11';
//Fiter data for 4 Plant and for Capital Employed GL(31)
BFilter4:
LOAD
BUKRS&'-'&[Acct Code] as %KeyC4VEL4PlantRec
Resident GLT0_2 Where %FlagC4CE4PlantGLGLNum=1 and %FlagC4VELPlant4Num_C4=1;
Concatenate(B)
B:
LOAD
// '4' as Flag,
RVERS&'-'&RYEAR as %KeySalesTotalYear,
RVERS&'-'&RYEAR&'-'&Month as %KeySalesMonth,
RVERS&'-'&RYEAR&'-'&Month&'-'&'Z001' as %KeySalesMonthPlant,
RBUSA ,
RLDNR ,
RRCTY ,
RTCUR ,
RVERS ,
RYEAR ,
Amount ,
BUKRS ,
DRCRK ,
HSLVT ,
Month ,
[Acct Code]
Resident GLT0_2 Where Exists (%KeyC4VEL4PlantRec,BUKRS&'-'&[Acct Code]) and not Exists(BUKRSFilter,BUKRS&'-'&RBUSA) and RRCTY=0 and %FlagCycle<>'C4.11';
QUALIFY*;
UNQUALIFY %KeySalesTotalYear,%KeySalesMonthPlant, %KeySalesMonthTotal, %KeyC4VELPlantRec,%KeyC4VEL4PlantRec, %JoinKey;
B1:
LOAD
// Flag ,
RBUSA ,
RLDNR ,
RRCTY ,
RTCUR ,
RVERS ,
RYEAR ,
BUKRS ,
DRCRK ,
Month ,
[Acct Code],
HSLVT,
Amount
Resident B;
DROP Table B;
UNQUALIFY *;
//~~~~~~~~~~~~YTD Joins~~~~~~~~~~~~~~~//
B2:
LOAD
*,
1 as B1.MonthNew,
If(B1.Month=1, B1.Amount,Null()) as Amount1
Resident B1 Where B1.Month=1;
Concatenate(B2)
LOAD
*,
2 as B1.MonthNew,
If(B1.Month=1 or B1.Month=2, B1.Amount,Null()) as Amount2
Resident B1 Where B1.Month=1 or B1.Month=2;
Concatenate(B2)
LOAD
*,
3 as B1.MonthNew,
If(B1.Month=1 or B1.Month=2 or B1.Month=3,B1.Amount,Null()) as Amount3
Resident B1 Where B1.Month=1 or B1.Month=2 or B1.Month=3;
Concatenate(B2)
LOAD
*,
4 as B1.MonthNew,
If(B1.Month=1 or B1.Month=2 or B1.Month=3 or B1.Month=4, B1.Amount,Null()) as Amount4
Resident B1 Where B1.Month=1 or B1.Month=2 or B1.Month=3 or B1.Month=4;
Concatenate(B2)
LOAD
*,
5 as B1.MonthNew,
If(B1.Month=1 or B1.Month=2 or B1.Month=3 or B1.Month=4 or B1.Month=5, B1.Amount,Null()) as Amount5
Resident B1 Where B1.Month=1 or B1.Month=2 or B1.Month=3 or B1.Month=4 or B1.Month=5;
Concatenate(B2)
LOAD
*,
6 as B1.MonthNew,
If(B1.Month=1 or B1.Month=2 or B1.Month=3 or B1.Month=4 or B1.Month=5 or B1.Month=6, B1.Amount,Null()) as Amount6
Resident B1 Where B1.Month=1 or B1.Month=2 or B1.Month=3 or B1.Month=4 or B1.Month=5 or B1.Month=6;
Concatenate(B2)
LOAD
*,
7 as B1.MonthNew,
If(B1.Month=1 or B1.Month=2 or B1.Month=3 or B1.Month=4 or B1.Month=5 or B1.Month=6 or B1.Month=7, B1.Amount,Null()) as Amount7
Resident B1 Where B1.Month=1 or B1.Month=2 or B1.Month=3 or B1.Month=4 or B1.Month=5 or B1.Month=6 or B1.Month=7;
Concatenate(B2)
LOAD
*,
8 as B1.MonthNew,
If(B1.Month=1 or B1.Month=2 or B1.Month=3 or B1.Month=4 or B1.Month=5 or B1.Month=6 or B1.Month=7 or B1.Month=8, B1.Amount,Null()) as Amount8
Resident B1 Where B1.Month=1 or B1.Month=2 or B1.Month=3 or B1.Month=4 or B1.Month=5 or B1.Month=6 or B1.Month=7 or B1.Month=8;
Concatenate(B2)
LOAD
*,
9 as B1.MonthNew,
If(B1.Month=1 or B1.Month=2 or B1.Month=3 or B1.Month=4 or B1.Month=5 or B1.Month=6 or B1.Month=7 or B1.Month=8 or B1.Month=9, B1.Amount,Null()) as Amount9
Resident B1 Where B1.Month=1 or B1.Month=2 or B1.Month=3 or B1.Month=4 or B1.Month=5 or B1.Month=6 or B1.Month=7 or B1.Month=8 or B1.Month=9;
Concatenate(B2)
LOAD
*,
10 as B1.MonthNew,
If(B1.Month=1 or B1.Month=2 or B1.Month=3 or B1.Month=4 or B1.Month=5 or B1.Month=6 or B1.Month=7 or B1.Month=8 or B1.Month=9 or B1.Month=10, B1.Amount,Null()) as Amount10
Resident B1 Where B1.Month=1 or B1.Month=2 or B1.Month=3 or B1.Month=4 or B1.Month=5 or B1.Month=6 or B1.Month=7 or B1.Month=8 or B1.Month=9 or B1.Month=10;
Concatenate(B2)
LOAD
*,
11 as B1.MonthNew,
If(B1.Month=1 or B1.Month=2 or B1.Month=3 or B1.Month=4 or B1.Month=5 or B1.Month=6 or B1.Month=7 or B1.Month=8 or B1.Month=9 or B1.Month=10 or B1.Month=11,B1.Amount,Null()) as Amount11
Resident B1 Where B1.Month=1 or B1.Month=2 or B1.Month=3 or B1.Month=4 or B1.Month=5 or B1.Month=6 or B1.Month=7 or B1.Month=8 or B1.Month=9 or B1.Month=10 or B1.Month=11;
Concatenate(B2)
LOAD
*,
12 as B1.MonthNew,
If(B1.Month=1 or B1.Month=2 or B1.Month=3 or B1.Month=4 or B1.Month=5 or B1.Month=6 or B1.Month=7 or B1.Month=8 or B1.Month=9 or B1.Month=10 or B1.Month=11 or B1.Month=12,B1.Amount,Null()) as Amount12
Resident B1 Where B1.Month=1 or B1.Month=2 or B1.Month=3 or B1.Month=4 or B1.Month=5 or B1.Month=6 or B1.Month=7 or B1.Month=8 or B1.Month=9 or B1.Month=10 or B1.Month=11 or B1.Month=12;
DROP Table B1;
NoConcatenate
B1:
LOAD
B1.RVERS&'-'&B1.RYEAR as %KeySalesTotalYear,
B1.RVERS&'-'&B1.RYEAR&'-'&B1.MonthNew as %KeySalesMonthTotal,
B1.RVERS&'-'&B1.RYEAR&'-'&B1.MonthNew&'-'&B1.BUKRS as %KeySalesMonthPlant,
*,
If(B1.Month=1,Alt(B1.HSLVT,0)+B1.Amount,B1.Amount) as AmountNew
Resident B2;
DROP Table B2;
QUALIFY*;
UNQUALIFY %KeySalesTotalYear,%KeySalesMonthPlant, %KeySalesMonthTotal, %KeyC4VELPlantRec,%KeyC4VEL4PlantRec, %JoinKey;
B2:
LOAD
%KeySalesMonthPlant,
Only(%KeySalesMonthTotal) as %KeySalesMonthTotal,
// Sum(B1.Amount) as SalesPlantTotal_Old,
Sum(AmountNew) as SalesPlantTotal
Resident B1 Group by %KeySalesMonthPlant;
//All Plant Sum
Left Join (B2)
B3:
LOAD
%KeySalesMonthTotal as %KeySalesMonthTotal,
// Sum(B1.Amount) as SalesTotal_Old,
Sum(AmountNew) as SalesTotal
Resident B1 Group by %KeySalesMonthTotal;
//Weightage avg Plant wise:
BF:
LOAD
B2.SalesPlantTotal,
B3.SalesTotal,
Num((B2.SalesPlantTotal/B3.SalesTotal)) As SalesWeghtAvg,
// B2.SalesPlantTotal_Old,
// B3.SalesTotal_Old,
// Num((B2.SalesPlantTotal/B3.SalesTotal)) As SalesWeghtAvg_Old,
%KeySalesMonthPlant,
%KeySalesMonthTotal
Resident B2;
DROP Table B2;
Left Join (BF)
LOAD
%KeySalesMonthTotal,
Sum(A.Amount) As A.Amount
Group by %KeySalesMonthTotal;
LOAD
A.RVERS&'-'&A.RYEAR&'-'&A.Month as %KeySalesMonthTotal,
A.Amount
Resident A Where [INLFED.FlagCycle] ='R' and INLFED.Factor=1;
//DROP Field %KeySalesMonthTotal;
BFF:
LOAD
%KeySalesMonthPlant,
SubField(%KeySalesMonthPlant,'-',3) as Month,
BF.B2.SalesPlantTotal,
BF.B3.SalesTotal,
BF.SalesWeghtAvg,
// BF.%KeySalesMonthTotalTemp,
// %KeySalesMonthTotal,
[BF-22.A.Amount],
(BF.SalesWeghtAvg*[BF-22.A.Amount]) as MonthlyRecevingTotalAmt
//- [BF-1.A.Acct Code]
Resident BF;
DROP Table BF;
UNQUALIFY*;
Join (BFF)
LOAD * INLINE [
Acct Code, RRCTY, RVERS, %FlagReport, FlagCycle, Factor
GM01150, 0, 1, BS, C4, -1
GE02054, 0, 1, PL, C4, 1
];
Concatenate (GLT0_2)
LOAD
'C4.12' as %FlagCycle,
1 as %FlagC4VELPlantNum_C4,
If([Acct Code]='GE02054',1,0) as %FlagC4VELINTRGLNum,
If([Acct Code]='GE02054',1,0) as %FlagC10PLGLNum,
0 as RLDNR,
SubField(%KeySalesMonthPlant,'-',2) as RYEAR,
SubField(%KeySalesMonthPlant,'-',3) as Month,
SubField(%KeySalesMonthPlant,'-',4) as BUKRS,
Factor*BFF.MonthlyRecevingTotalAmt as Amount,
[Acct Code] as [Acct Code],
RRCTY as RRCTY,
RVERS as RVERS
Resident BFF;
DROP Tables A, BFF, B1, BFilter, BFilter4, BUKRSFilter;
TRACE '//~~~~~~~~~~~~~~~~~~~Cycle 4.1 Finished~~~~~~~~~~~~~~~~~~~~~~~//';
