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 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156
| Sub TOTAL_VIEW()
Application.DisplayAlerts = False
On Error Resume Next
Sheets("TOTAL VIEW").Activate
ActiveSheet.Cells.ClearContents
ActiveSheet.Replace
'Declare variables as objects
Dim OraSession As Object
Dim OraDatabase As Object
Dim EmpDynaset As Object
Dim flds() As Object
Dim fldcount As Integer
Dim sqlstring As String
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
On Error GoTo ExceptionHandler ' disable this if you want to Debug
'-- Disable output to improve performance
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
With Oracle_Connection
.Do_Show
If .isOK Then
Set OraDatabase = OraSession.OpenDatabase(.getTNSName, .getUserName & "/" & .getPassword, 0&)
Else
MsgBox "Cancelled by operator."
GoTo Finish ' to skip the unwanted processing
End If
.Do_Unload
End With
'Set OraDatabase = OraSession.OpenDatabase("database", "dbupload/dbupload", 0&)
sqlstring = "select"
sqlstring = sqlstring + " trim(substr(division,9,50)) BU,trim(substr(subdivision,9,50)) SBU, upper(substr(blvl1,5,50)) Level1" ', T7.LVL1NAME Group"
'============================================================================
'============================================================================
'============================================================================
sqlstring = sqlstring + " , Sum(Fdmd1*UNIT_CONVFACT)+sum(Fdmd2*UNIT_CONVFACT)+sum(FDMD3*UNIT_CONVFACT)+sum(FDMD4*UNIT_CONVFACT)+sum(FDMD5*UNIT_CONVFACT)+sum(FDMD6*UNIT_CONVFACT)+sum(FDMD7*UNIT_CONVFACT)+sum(FDMD8*UNIT_CONVFACT)+sum(FDMD9*UNIT_CONVFACT)+sum(Fdmd10*UNIT_CONVFACT)+sum(Fdmd11*UNIT_CONVFACT)+sum(Fdmd12*UNIT_CONVFACT) YTG_ORDERS"
sqlstring = sqlstring + " , sum(Salest*UNIT_CONVFACT) YTD_SALES"
sqlstring = sqlstring + " , ROUND(sum(BUDT*UNIT_CONVFACT),0) BUDGET"
sqlstring = sqlstring + " , sum(roy_fcstt*UNIT_CONVFACT) YTG_FCST"
sqlstring = sqlstring + " , sum(roy_fcstt*UNIT_CONVFACT)+sum(Salest*UNIT_CONVFACT) FCST_CAR"
sqlstring = sqlstring + " , sum(roy_fcstt*UNIT_CONVFACT)+sum(dmdt*UNIT_CONVFACT) FCST_MOTO"
'================================================================================================================
sqlstring = sqlstring + " , Sum(Fdmd1*UNIT_CONVFACT)+sum(FDMD2*UNIT_CONVFACT)+sum(FDMD3*UNIT_CONVFACT) ORDERS_Q1"
sqlstring = sqlstring + " , sum(Sales1*UNIT_CONVFACT) +sum(SALES2*UNIT_CONVFACT) +sum(SALES3*UNIT_CONVFACT) SALES_Q1"
sqlstring = sqlstring + " , sum(BUD1*UNIT_CONVFACT) +sum(BUD2*UNIT_CONVFACT) +sum(BUD3*UNIT_CONVFACT) BUD_Q1"
sqlstring = sqlstring + " , sum(ROY_FCST1*UNIT_CONVFACT) +sum(ROY_FCST2*UNIT_CONVFACT) +sum(ROY_FCST3*UNIT_CONVFACT) FCST_Q1"
'=================================================================================================================
sqlstring = sqlstring + " , Sum(FDMD4*UNIT_CONVFACT)+sum(FDMD5*UNIT_CONVFACT)+sum(FDMD6*UNIT_CONVFACT) ORDERS_Q2"
sqlstring = sqlstring + " , sum(SALES4*UNIT_CONVFACT)+sum(SALES5*UNIT_CONVFACT)+sum(SALES6*UNIT_CONVFACT) SALES_Q2"
sqlstring = sqlstring + " , sum(BUD4*UNIT_CONVFACT)+sum(BUD5*UNIT_CONVFACT)+sum(BUD6*UNIT_CONVFACT) BUD_Q2"
sqlstring = sqlstring + " , sum(ROY_FCST4*UNIT_CONVFACT)+sum(ROY_FCST5*UNIT_CONVFACT)+sum(ROY_FCST6*UNIT_CONVFACT) FCST_Q2"
'================================================================================================================
sqlstring = sqlstring + " , Sum(FDMD7*UNIT_CONVFACT)+sum(FDMD8*UNIT_CONVFACT)+sum(FDMD9*UNIT_CONVFACT) ORDERS_Q3"
sqlstring = sqlstring + " , sum(SALES7*UNIT_CONVFACT)+sum(SALES8*UNIT_CONVFACT)+sum(SALES9*UNIT_CONVFACT) SALES_Q3"
sqlstring = sqlstring + " , sum(BUD7*UNIT_CONVFACT)+sum(BUD8*UNIT_CONVFACT)+sum(BUD9*UNIT_CONVFACT) BUD_Q3"
sqlstring = sqlstring + " , sum(ROY_FCST7*UNIT_CONVFACT)+sum(ROY_FCST8*UNIT_CONVFACT)+sum(ROY_FCST9*UNIT_CONVFACT) FCST_Q3"
'================================================================================================================
sqlstring = sqlstring + " , Sum(Fdmd10*UNIT_CONVFACT)+sum(Fdmd11*UNIT_CONVFACT)+sum(Fdmd12*UNIT_CONVFACT) ORDERS_Q4"
sqlstring = sqlstring + " , sum(Sales10*UNIT_CONVFACT)+sum(Sales11*UNIT_CONVFACT)+sum(Sales12*UNIT_CONVFACT) SALES_Q4"
sqlstring = sqlstring + " , sum(BUD10*UNIT_CONVFACT)+sum(BUD11*UNIT_CONVFACT)+sum(BUD12*UNIT_CONVFACT) BUD_Q4"
sqlstring = sqlstring + " , sum(ROY_FCST10*UNIT_CONVFACT)+sum(ROY_FCST11*UNIT_CONVFACT)+sum(ROY_FCST12*UNIT_CONVFACT) FCST_Q4"
'============================================================================
'============================================================================
'============================================================================
'DEFINE TABLES USED FOR THE QUERY
sqlstring = sqlstring + " from"
sqlstring = sqlstring + " fcst t0"
sqlstring = sqlstring + " , fcst_map t1"
sqlstring = sqlstring + " , fcst_cnty t2"
sqlstring = sqlstring + " , fcst_prod t3"
sqlstring = sqlstring + " , fcst_period t4"
sqlstring = sqlstring + " , fcst_demand t5"
sqlstring = sqlstring + " , product t6"
sqlstring = sqlstring + " , product_detail t7"
' DEFINE LINKS BETWEEN TABLES
sqlstring = sqlstring + " where t0.fnclyear = t4.fnclyear"
sqlstring = sqlstring + " and t2.cntylvl3 in(‘Middle East and Africa') “
sqlstring = sqlstring + " and t0.cntycode = t1.cntycode and t0.salestype = t1.salestype"
sqlstring = sqlstring + " and t0.afflcode = t1.afflcode and t1.afflcode = t2.afflcode"
sqlstring = sqlstring + " and t1.r_cntycode = t2.cntycode"
sqlstring = sqlstring + " and t1.r_salestype = t2.salestype and t0.uniqpdctcode = substr(t3.xlvl1code,5,50)"
sqlstring = sqlstring + " and t0.uniqpdctcode = t6.uniqpdctcode and t6.uniqpdctcode = t7.uniqpdctcode "
sqlstring = sqlstring + " and substr(t3.xlvl2code,5,50) = t6.lvl2code and t6.lvl2code = t7.lvl2code"
sqlstring = sqlstring + " and t0.fnclyear||t0.b_uniqpdctcode||t0.b_afflcode||t0.b_cntycode||t0.b_salestype = t5.fcstkey (+)"
sqlstring = sqlstring + " group by division,subdivision,blvl1"
sqlstring = sqlstring + " order by division,subdivision,blvl1"
Set EmpDynaset = OraDatabase.CreateDynaset(sqlstring, 0&)
'Declare and create an object for each column
'This will reduce objects references and speed up your application
fldcount = EmpDynaset.fields.Count
ReDim flds(0 To fldcount - 1)
For colnum = 0 To fldcount - 1
Set flds(colnum) = EmpDynaset.fields(colnum)
Next
'Insert column headings
For colnum = 0 To EmpDynaset.fields.Count - 1
ActiveSheet.Cells(1, colnum + 1) = flds(colnum).Name
Next
'Display data
For rownum = 2 To EmpDynaset.RecordCount + 1
For colnum = 0 To fldcount - 1
ActiveSheet.Cells(rownum, colnum + 1) = flds(colnum).Value
Next
EmpDynaset.movenext
Next
Range("B1:A1").Select
GoTo Finish ' clean-up and terminate
' ======================================================
' Exception processing
ExceptionHandler:
If Err.Number <> 0 Then
MsgBox Err.Description
End If
' ======================================================
' Housekeeping
Finish:
'-- Re-enable output to improve performance
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub |
Partager