Technical Corner: February 2017

Tuesday, February 14, 2017

Oracle VBA Working ConnectionString OraOLEDB

Dim cnt As New adodb.Connection
Set cnt = New adodb.Connection

connStr = "Provider=OraOLEDB.Oracle;Data Source=HIBUDP1;User Id=Budget;Password=xxxx#xx;"
cnt.Open connStr

Set rst = cnt.Execute(SQL_Str)

How to enable textbox on dropdown

function myFunction() {
  document.getElementById("myText").disabled = false;
}

Name:
 type="text" id="myText" disabled="true">
Click the button to disable the text field.

2 3
http://stackoverflow.com/questions/31647425/how-to-enable-text-box-on-drop-down-value-change-in-javascript

Friday, February 10, 2017

F1602 Data recalss

--select right(rtrim(CBABR1),2) FY ,left(rtrim(CBABR1),2) PN,CBAID,CBLT,CBSBL,CBSBLT,cban11/100 Amt into #temp_11 from oracleF1602 where cbuser = 'CHUCHRIS' and CBCFY=@fy order by 1,2
--select right(rtrim(CBABR1),2) FY ,left(rtrim(CBABR1),2) PN,CBAID,CBLT,CBSBL,CBSBLT,cban11/100 Amt from oracleF1602 where cbuser = 'CHUCHRIS' and CBCFY=2016 order by 1,2
--select * from oracleF1602 where cbuser = 'CHUCHRIS' and CBCFY=2016 order by 1,2
declare @fy int=2016

--PN 10
drop table #temp_10
select right(rtrim(CBABR1),2) FY ,left(rtrim(CBABR1),2) PN,CBAID,CBLT,CBSBL,CBSBLT,cban10/100 Amt into #temp_10 from oracleF1602 where cbuser = 'CHUCHRIS' and CBCFY=@fy order by 1,2
drop table #temp_10_2
select FY,CBAID,CBLT,CBSBL,CBSBLT,
ISNULL([01],0) Period_01, ISNULL([02],0) Period_02, ISNULL([03],0) Period_03,ISNULL([04],0) Period_04, ISNULL([05],0) Period_05, ISNULL([06],0) Period_06,
ISNULL([07],0) Period_07, ISNULL([08],0) Period_08, ISNULL([09],0) Period_09,ISNULL([10],0) Period_10, ISNULL([11],0) Period_11, ISNULL([12],0) Period_12 into #temp_10_2
from
(
select FY,PN,CBAID,CBLT,CBSBL,CBSBLT,amt
from #temp_10
) src
pivot
(
sum(Amt)
for pn in ([01], [02], [03],[04], [05], [06],[07], [08], [09],[10], [11], [12])
) piv
order by 2;
select '10' ForPN, * from #temp_10_2

--PN 11
drop table #temp_11
select right(rtrim(CBABR1),2) FY ,left(rtrim(CBABR1),2) PN,CBAID,CBLT,CBSBL,CBSBLT,cban11/100 Amt into #temp_11 from oracleF1602 where cbuser = 'CHUCHRIS' and CBCFY=@fy order by 1,2
drop table #temp_11_2
select FY,CBAID,CBLT,CBSBL,CBSBLT,
ISNULL([01],0) Period_01, ISNULL([02],0) Period_02, ISNULL([03],0) Period_03,ISNULL([04],0) Period_04, ISNULL([05],0) Period_05, ISNULL([06],0) Period_06,
ISNULL([07],0) Period_07, ISNULL([08],0) Period_08, ISNULL([09],0) Period_09,ISNULL([10],0) Period_10, ISNULL([11],0) Period_11, ISNULL([12],0) Period_12 into #temp_11_2
from
(
select FY,PN,CBAID,CBLT,CBSBL,CBSBLT,amt
from #temp_11
) src
pivot
(
sum(Amt)
for pn in ([01], [02], [03],[04], [05], [06],[07], [08], [09],[10], [11], [12])
) piv
order by 2;
select '11' ForPN, * from #temp_11_2

--for 12
drop table #temp111
select right(rtrim(CBABR1),2) FY ,left(rtrim(CBABR1),2) PN,CBAID,CBLT,CBSBL,CBSBLT,cban12/100 Amt into #temp111 from oracleF1602 where cbuser = 'CHUCHRIS' and CBCFY=@fy order by 1,2

drop table #temp2
select FY,CBAID,CBLT,CBSBL,CBSBLT,
ISNULL([01],0) Period_01, ISNULL([02],0) Period_02, ISNULL([03],0) Period_03,ISNULL([04],0) Period_04, ISNULL([05],0) Period_05, ISNULL([06],0) Period_06,
ISNULL([07],0) Period_07, ISNULL([08],0) Period_08, ISNULL([09],0) Period_09,ISNULL([10],0) Period_10, ISNULL([11],0) Period_11, ISNULL([12],0) Period_12 into #temp2
from
(
select FY,PN,CBAID,CBLT,CBSBL,CBSBLT,amt
from #temp111
) src
pivot
(
sum(Amt)
for pn in ([01], [02], [03],[04], [05], [06],[07], [08], [09],[10], [11], [12])
) piv
order by 2;

select '12' ForPN, * from #temp2

Wednesday, February 8, 2017

F4111 VS F0911 Reconciliation..

drop table #temp11
drop table #temp12
drop table #temp13

--select * from proddta.f4111 where ILDGL>117000
--select * from proddta.f0911 where GLDGJ>117000

select ildoc,ildct,ILJELN,ILLITM, ilmcu,iltrdj,ilicu,ildgl, sum(iltrqt)iltrqt,sum(ilpaid)/100ilpaid into #temp11
from proddta.f4111 where ILDGL>117000
group by ildoc,ildct,ILJELN,ILLITM, ilmcu,iltrdj,ilicu,ildgl

select gldoc,gldct,GLJELN,glmcu,gLexr, glsub,glani,sum(glu)/100 glu, sum(glaa)/100 glaa into #temp12
from proddta.f0911 where gLDGj>117000 and gldct in(
select distinct ildct from proddta.f4111 where ILDGL>117000)
group by GLDOC,gldct,glexr,glsub,GLJELN,glani,glmcu

select *,( glaa-ilpaid) amt into #temp13
from #temp11
left outer join #temp12 on ildoc=gldoc and ildct=gldct and GLJELN=iljeln
--where len(glsub) >3 and glaa-ilpaid<>0

select
ildoc DOC#,ildct DcTy,illitm ItemNo,ilmcu BP,iltrqt Inv_Qty,ilpaid Inv_Amt,glani Acno,ilicu BatchNo,
DATEADD(dy, cast(iltrdj as varchar(10)) % 1000, DATEADD(yy, cast(iltrdj as varchar(10)) / 1000,-1)) TransDate,
DATEADD(dy, cast(ildgl as varchar(10)) % 1000, DATEADD(yy, cast(ildgl as varchar(10)) / 1000,-1)) GL_Date,
glu GL_Qty,glaa GL_Amt, (ilpaid-glaa) AmtDiff,
case when (ilpaid-glaa)=0 then 'Recoincile' when glaa is null then 'Not in GL' else 'Not Recoinciled' end RecoincileYN
from #temp13 order by 4,3,2
-- IK

lpad substring in PL SQL

select ltrim(rtrim(CCNAME)) aaa, length(ltrim(rtrim(CCNAME))) fkj,
'000000'|| substr(ltrim(rtrim(CCNAME)),-length(ltrim(rtrim(CCNAME))),30),
LPAD(substr(ltrim(rtrim(CCNAME)),1,6),50-length(ltrim(rtrim(CCNAME))),0),
RPAD('000000'|| substr(ltrim(rtrim(CCNAME)),-length(ltrim(rtrim(CCNAME))),23), 10)
from PRODDTA.F0010

Sunday, February 5, 2017

Month End Inventory-to-G/L Reconciliation - F4111 Vs F0911 Compare

drop table #temp11
drop table #temp12
drop table #temp13

--select * from proddta.f4111 where ILDGL>117000
--select * from proddta.f0911 where GLDGJ>117000

select ildoc,ildct,ILJELN,ILLITM, ilmcu, sum(iltrqt)iltrqt,sum(ilpaid)/100ilpaid into #temp11
from proddta.f4111 where ILDGL>117000
group by ildoc,ildct,ILLITM,ilmcu,ILLITM,ILJELN

select gldoc,gldct,GLJELN,glmcu,gLexr, glsub,glani,sum(glu)/100 glu, sum(glaa)/100 glaa into #temp12
from proddta.f0911 where gLDGj>117000 and gldct in(
select distinct ildct from proddta.f4111 where ILDGL>117000)
group by GLDOC,gldct,glexr,glsub,GLJELN,glani,glmcu

select *,( glaa-ilpaid) amt into #temp13
from #temp11
left outer join #temp12 on ildoc=gldoc and ildct=gldct and GLJELN=iljeln
--where len(glsub) >3 and glaa-ilpaid<>0
select
ildoc,ildct,illitm,ilmcu,iltrqt,ilpaid,glani,glu,glaa, (ilpaid-glaa) AmtDiff,
case when (ilpaid-glaa)=0 then 'Recoincile' when glaa is null then 'Not in GL' else 'Not Recoinciled' end RecoincileYN
from #temp13
--where glaa is null

--select illitm,ilmcu,sum(ilpaid)ilpaid,glsub, sum(glaa) glaa from #temp13
--where ilpaid-glaa<>0 group by illitm,ilmcu,glsub

----select * from proddta.f4111 where illitm='1645618' and ltrim(ilmcu)='GRAYTIF-RT' and ildgl>117000 and
----select * from proddta.f0911 where gldgj>117000 and GLEXR='1649608' and ltrim(glsub) in('NIAGRA','010')
----select * from proddta.f0911 where gldoc=127572
--select * from proddta.f4111 where ildoc=127375 and illitm='1654571' and ilmcu=' SCHAILI-SC2'
--select * from proddta.f0911 where GLDOC=127375 and gldct='IK' --illitm='1654571' and ilmcu=' SCHAILI-SC2'

-- IK