Sub Macro_IC_Invoice()
'
' Macro_IC_Invoice Macro
'
'
Range("A2").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
With Selection.ListObject.QueryTable
.Connection = Array(Array( _
"ODBC;DSN=Proddb - Live;Description=Live Production;UID=sa;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=DAVA-BRDG-L;DATABAS" _
), Array("E=proddb;LANGUAGE=us_english"))
.CommandText = Array( _
"Select ad.accounting_period,ad.fiscal_per_nbr,ad.co_code,ad.org_code,ad.acct_code,ad.currency_code,ad.sys_doc_type_code,ad.usr_batch_id,ad.db_amt_ac,ad.db_amt_cc,ad.cr_amt_ac,ad.cr_amt_cc,ad.prj_code," _
, _
"fcs.trn_desc" & Chr(13) & "" & Chr(10) & "from Proddb..gl_acct_detail ad " & Chr(13) & "" & Chr(10) & "left join proddb..fcs_trn_desc fcs ON ad.fcs_desc_skey = fcs.fcs_desc_skey " & Chr(13) & "" & Chr(10) & "Where substring(ad.acct_code,1,1) in ('I','P','R') " & Chr(13) & "" & Chr(10) & "and ad.accounting_perio" _
, _
"d = 201508" & Chr(13) & "" & Chr(10) & "and ad.usr_batch_id not like ('RGL') " & Chr(13) & "" & Chr(10) & "and (ad.acct_code like '%731%' " & Chr(13) & "" & Chr(10) & "or ad.acct_code like '%711%')" & Chr(13) & "" & Chr(10) & "and ad.cr_amt_ac = 0" & Chr(13) & "" & Chr(10) & "" _
)
.Refresh BackgroundQuery:=False
End With
Sheets("IC Allocation Detail Query").Select
Range("A2").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
With Selection.ListObject.QueryTable
.Connection = Array(Array( _
"ODBC;DSN=Proddb - Live;Description=Live Production;UID=sa;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=DAVA-BRDG-L;DATABAS" _
), Array("E=proddb;LANGUAGE=us_english"))
.CommandText = Array( _
"SELECT ad.co_code, ad.fiscal_per_nbr, ad.fiscal_year, ad.org_code, ad.acct_code, ad.db_amt_cc, ad.cr_amt_cc, ad.db_amt_ac, ad.cr_amt_ac, ad.alloc_co_code, ad.gl_alloc_code, ad.posting_date, ad.account" _
, _
"ing_period, ad.mod_date" & Chr(13) & "" & Chr(10) & "FROM Proddb.dbo.gl_alloc_detail ad" & Chr(13) & "" & Chr(10) & "WHERE (ad.acct_code Like '%731%') AND (substring(ad.acct_code,1,1) In ('I','P','R')) AND (ad.accounting_period=201508) OR (ad.acct_code Lik" _
, _
"e '%711%') AND (substring(ad.acct_code,1,1) In ('I','P','R')) AND (ad.accounting_period=201508)" _
)
.Refresh BackgroundQuery:=False
End With
End Sub
Bookmarks