SSIS Job

'
' GL8 - SO_SalesOrderDeatil.m4t into SQL
'
' build a one SQL file from multiple MAS90 files 
' create the database and table, delete existing rows, insert rows
'
' Data From: EAGLE SO_SalesOrderDeatil import into MAS90            

'---------------Varibales--------------------------------------------------
' MAS90
    company  =  "ETG"                        'Uses SQL database MAS_###, data from MAS90
    table    =  "SO_SalesOrderDeatil"
    tmptable =  "importSO_SalesOrderDeatil"
    mDSN     =  "DSN=ESOTAMAS90"
    EagleDSN =  "DSN=EagleTrading"
    AccessDSN=  "DSN=AccessData"
    Server   =  "GSSRV011"
    DataBase =  "EagleTrading"
    Database2=  "AccessData"
    ET       =  "E"  ' Interchange Between E or T for Eagle Order or Test Order 

    nDays = 1
    sql_Delete = "Delete from " & tmptable
'--------------------------------------------------------------------------    

'--------------------------------------------------------------------------
'Set Input & Output variables
Dim StdIn, StdOut
Set StdIn = WScript.StdIn
Set StdOut = WScript.StdOut

'--------------------------------------------------------------------------


'Eagle Connection here
    set Econnectiontodatabase = CreateObject("ADODB.Connection")
    Econnectiontodatabase.connectiontimeout=60  
    EConnectiontodatabase.open EagleDSN '(sConnectstring)

'AccessData connection here
    set Aconnectiontodatabase2 = CreateObject("ADODB.Connection")
    Aconnectiontodatabase2.connectiontimeout=60
    AConnectiontodatabase2.open AccessDSN 'Access Database
 
   wscript.echo "Using data from MAS90/200 Company " & company

'Get MAS90 data  MAS90 Connection String                                                              
 
    Set m90=CreateObject("ADODB.Connection")
    m90.ConnectionTimeout=60
    m90.Open mDSN

Econnectiontodatabase.execute(sql_Delete)

StdOut.WriteLine "Please Number of Orders to Export"
OrderNumber = StdIn.ReadLine
OrderNumber = cint(OrderNumber)

ReDim Preserve Order(OrderNumber)
ReDim Preserve whouse(OrderNumber)

i=0
 Do while i < OrderNumber

  StdOut.WriteLine "Please Enter CustomerPO " & i+1 & " of " & OrderNumber
  Order(i) = StdIn.ReadLine

  StdOut.WriteLine "Please Enter Warehouse for " & Order(i) & " (Please Enter Last 2 Digits only 3 is = 03)"
  whouse(i) = StdIn.ReadLine


  i= i+1 
Loop

j=0

Do while j < OrderNumber

     xcommand ="Select PO2_PurchaseOrderEntryLine.PurchaseOrderNumber,PO2_PurchaseOrderEntryLine.LineIndex,PO2_PurchaseOrderEntryLine.ItemNumber, " &_
               "PO2_PurchaseOrderEntryLine.UnitCost,PO2_PurchaseOrderEntryLine.QtyOrdered, " &_          
               "PO1_PurchaseOrderEntryHeader.PurchaseOrderDate, PO1_PurchaseOrderEntryHeader.RequiredExpireDate " &_ 
   	       "from PO2_PurchaseOrderEntryLine , PO1_PurchaseOrderEntryHeader " &_
               "where  PO2_PurchaseOrderEntryLine.PurchaseOrderNumber = PO1_PurchaseOrderEntryHeader.PurchaseOrderNumber " &_
	       "and PO2_PurchaseOrderEntryLine.PurchaseOrderNumber in ('000"& Order(j) &"')"	 

                                                           ' Wscript.echo xcommand 'debug
     Set gl8 = m90.Execute(xcommand)

  if gl8.BOF = True then 
  	if gl8.EOF = True then
  	        wscript.echo "No New Records to Update in last hour for "& table & VbCrLf 
  		wscript.quit		
  	end if	
  end if
     

  '  add data to table
wscript.echo "Inserting "& j+1 &" of "& i &" Selected Order(s) into Temp table "&tmptable&" then into Access Database from company " & company

    'SQL columns
    
    fields =          "PurchaseOrderNumber,"
    fields = fields & "LineIndex,"
    fields = fields & "EItemNumber,"
    fields = fields & "UnitCost,"
    fields = fields & "QtyOrdered,"
    fields = fields & "PurchaseOrderDate,"
    fields = fields & "RequiredExpireDate,"
    fields = fields & "ItemCode,"
    fields = fields & "WarehouseCode,"
    fields = fields & "TW_Cost"
  
  cnt = 0

  TW_Cost = 0.000
  
  gl8.MoveFirst
    do while not gl8.eof
     
       Item = left(gl8.fields(2),5)&"-"&Right(trim(gl8.fields(2)),5)
       xcommand2 = "Select Top 1 ItemNumber, Quantity, Case_Weight  from Product P inner join Product_Detail PD on " &_
                   "P.Product_ID=PD.Product_ID where CaseUPC in ('"& Item &"') " &_   
                   "group by ItemNumber, Quantity, P_End_Date, Case_Weight  Order by P_End_Date Desc " 
       
      
      Set ar1 = Aconnectiontodatabase2.execute(xcommand2)
      	IF ar1.EOF = True then 
      		
      		wscript.echo "Item "& left(gl8.fields(2),5)&"-"&Right(trim(gl8.fields(2)),5) &" was Skipped Check Order" 
      		gl8.MoveNext
      			Item = left(gl8.fields(2),5)&"-"&Right(trim(gl8.fields(2)),5)
      			xcommand2 = "Select Top 1 ItemNumber, Quantity, Case_Weight  from Product P inner join Product_Detail PD on " &_
		                "P.Product_ID=PD.Product_ID where CaseUPC in ('"& Item &"') " &_   
                   		"group by ItemNumber, Quantity, P_End_Date, Case_Weight  Order by P_End_Date Desc "
      		
      		Set ar1 = Aconnectiontodatabase2.execute(xcommand2)
      	End IF
      	
      
                     
' Data for SQL

       values =           "'" & gl8("PurchaseOrderNumber") & "'"
       values = values & ",'" & gl8("LineIndex") & "'"
       values = values & ",'" & Trim(gl8("ItemNumber")) & "'"
       values = values & ",'" & gl8("UnitCost") & "'"
       values = values & ",'" & gl8("QtyOrdered") & "'"
       values = values & ",'" & gl8("PurchaseOrderDate") & "'"
       values = values & ",'" & gl8("RequiredExpireDate") & "'"
       values = values & ",'" & ar1.Fields(0) & "'"
       Values = Values & ",'" & "0"& whouse(j) & "'"


OQty = CDbl(gl8.Fields(4))
PQty = CDbl(ar1.Fields(1))
Pwieght= CDbl(ar1.Fields(2))
wcost = CInt(whouse(j))

Select Case (wcost)
Case 12
TW_Cost = ((Pwieght*OQty) / 100) * 1.75       ' Jacob Fleishman
Case 14
If (cnt = 0) Then                       'add Query to count records in this Order check for 0
TW_Cost = 300 + (9 * (OQty/PQty))   'Ocasa Logistics
Else
    TW_Cost = (9 * (OQty/PQty) )
End If
Case 11
If (cnt = 0) Then
TW_Cost = 50 + 10 * (OQty/PQty)     ' Americold $50 per load as 5/4/09
Else
    TW_Cost = 10 * (OQty/PQty)
End If
Case 15
If (cnt = 0) Then
TW_Cost = 50 + 10 * (OQty/PQty)     ' Americold $50 per load as 5/4/09
Else
    TW_Cost = 10 * (OQty/PQty)
End If
Case 5
TW_Cost = (19 * (OQty/PQty))  + 3.15  ' US Cold Storage 3.15 cost of item retrival at ware house
Case 2
TW_Cost = 12.5 * (OQty/PQty)          ' US Cold Storage 30 days
Case 9
TW_Cost = 18.5 * (OQty/PQty)          ' Colombia Cold & Dry Storage
Case 10
TW_Cost = 11.6 * (OQty/PQty)          ' Tyler Distribution Center
Case 3
TW_Cost = 16 *  (OQty/PQty)           ' Integrity WareHouse
                                        
Case 16
TW_Cost = 13 * (OQty/PQty) 

Case 17                                 ' L.G Lables mostly lableing purposes $60 in & $60 out
If (cnt = 0) Then
TW_Cost = 120
End If

End Select

cnt = cnt +1
      

       Values = Values & "," & TW_Cost

'wscript.echo "STOP" for Debugging 
    	
            sql_insert = "Insert into " & tmptable & " (" & fields & ") values" & " (" & values & ")"
	  
             Econnectiontodatabase.execute(sql_insert)

         gl8.MoveNext
    loop
j=j+1    
Loop

'Insert into Header Query
xcommand3 = "Insert into AccessData.dbo.SalesOrder (SalesOrder.SO_Number,SalesOrder.Customer_ID, " &_
            "SalesOrder.Order_Date,SalesOrder.Delivery_Date, SalesOrder.WareHouseCode,SalesOrder.EntryDate) " &_
            "Select distinct '"& ET &"' + Right(PurchaseOrderNumber,7),'EAGLE', PurchaseOrderDate, RequiredExpireDate, WarehouseCode,  " &_
            "DATEADD(dd, 0, DATEDIFF(dd, 0,Getdate())) from dbo.importSO_SalesOrderDeatil" 


'Insert into Details
  xcommand4 = "; with Detail as (" &_ 
  "select ItemNumber, ProductNumber, P_END_Date, VendorNo, Case_Weight, Case_CF,Quantity, Description1, List, Contract, ROW_NUMBER() OVER( PARTITION BY P1.PRODUCT_ID Order by P3.Cdate desc) rn " &_
  "from AccessData.dbo.Product_Detail p2 inner Join AccessData.dbo.Product p1 on p1.Product_ID = p2.Product_ID inner join AccessData.dbo.Pricing p3 on p2.Product_Detail_ID = p3.Product_Detail_ID )/*and p3.Cdate = DATEADD(Month, DATEDIFF(Month, 0,getDate()), 0) )*/ " &_
  "insert into AccessData.dbo.SalesOrderDetail(SO_Number,Item_Number,Product_Number,Vendor_Number,Quantity,Sales_Price,Profit,T_List_Cost,T_Con_Cost,NN_Cost,Adj_Cost,Spoils_Allowance,Term_Disc,T_SaleAmt,T_Weight,T_CubicFeet,Pallet_Count,Description,OrderList,OrderContract,OrderUnit,TW_Cost) " &_
  "select '"& ET &"' + Right(ip.PurchaseOrderNumber,7), " &_
          "ip.ItemCode, " &_
          "Detail.ProductNumber, " &_
          "Detail.VendorNo, " &_
          "ip.QtyOrdered, " &_
          "Round(ip.UnitCost,2) as Sales_Price, " &_
          "Round(((ip.UnitCost * ip.QtyOrdered)-(ip.QtyOrdered * Detail.Contract)*.98+.01)/(.01+(Round(ip.UnitCost,2) * ip.QtyOrdered)),4) as Profit, " &_
          "Round((ip.QtyOrdered * Detail.List),2) as  T_List_Cost, " &_
          "Round((ip.QtyOrdered * Detail.Contract),2) as T_Contract_Cost, " &_
          "Round((ip.QtyOrdered * Detail.Contract)*.98,2) as NNCost, " &_
          "Round((ip.QtyOrdered * Detail.List)-(ip.QtyOrdered * Detail.Contract),2) as Adj_Cost, " &_
          "Case Left(Itemcode,2) " &_
              "when '37' then Round((Round(Detail.Contract,2) * ip.QtyOrdered) * 0.005, 2) " &_
              "when '44' then Round((Round(Detail.Contract,2) * ip.QtyOrdered) * 0.005, 2) " &_
              "when '45' then Round((Round(Detail.Contract,2) * ip.QtyOrdered) * 0.005, 2) " &_
              "else Null " &_
              "END as Spoils_Allowance, " &_
          "Round((ip.QtyOrdered * Detail.List)*.02,2) as Term_Disc, " &_
          "Round((Round(ip.UnitCost,2) * ip.QtyOrdered),2) as T_SaleAmt, " &_
          "(Detail.Case_Weight * ip.QtyOrdered) as T_weight, " &_
          "(Detail.Case_CF * ip.QtyOrdered)as T_CubicFeet, " &_
          "Round(((ip.QtyOrdered +.01)/( Detail.Quantity +.01)),1) as Pallet_Count, " &_
          "Detail.Description1, " &_
          "Detail.List, " &_
          "Detail.Contract, " &_
          "Detail.Quantity as OrderUnit,  " &_
          "ip.TW_Cost " &_
  "from dbo.importSO_SalesOrderDeatil ip inner join AccessData.dbo.Product P on ip.ItemCode = p.ItemNumber inner join Detail on ip.ItemCode = Detail.ItemNumber " &_
  "inner join AccessData.dbo.Product_Detail pd on P.PRODUCT_ID = pd.PRODUCT_ID " &_
  "where Detail.rn=1 " &_
  "and pd.P_END_Date > Getdate()" &_
  "Order by LineIndex "
    
  Econnectiontodatabase.execute(xcommand3)  
  Econnectiontodatabase.execute(xcommand4)
      
    EConnectiontodatabase.Close()
    set EConnectiontodatabase = nothing
    
    AConnectiontodatabase2.Close()
    set AConnectiontodatabase2 = nothing
        

    wscript.echo "Complete. Please check Access for the data. " & j & " Record(s) were entered into " & table & VbCrLf

Select allOpen in new window