Sunday, July 2, 2017

Sales Upload F47011/F47012

//
VA rpt_DateTransactionJulian = BC Date - Actual Ship Date (F554784B)(ADDJ)
VA rpt_ProgramId = "ER554787"
VA rpt_OrderType = "SO"
// Next Number
If BC Document (Voucher, Invoice, etc.) (F554784B)(DOC) is equal to PC Document (Voucher, Invoice, etc.) (F554784B) (DOC)
VA rpt_LineNo = [VA rpt_LineNo]+1
Else
F0002 Get F4201 Next Number
RV DOCO = VA rpt_DocumentOrderInvoiceE
VA rpt_LineNo = 1
End If
//
// Sales Header
F47011.Fetch Single
If SV File_IO_Status is equal to CO SUCCESS
Else
F47011.Insert
End If
//
//
// Detail File
//
F4101.Fetch Single
VA rpt_StatusCodeLast = "520"
VA rpt_StatusCodeNext = "527"
VA rpt_AmountExtendedPrice = [BC Amount - Price per Unit (F554784B)(UPRC)]*[BC Quantity Shipped (F554784B)(SOQS)]
VA rpt_AmountExtendedPrice = [BC Amount - Extended Price (F554784B)(AEXP)]
VA rpt_AmtPricePerUnit2 = [BC Amount - Extended Price (F554784B)(AEXP)]/[BC Quantity Shipped (F554784B)(SOQS)]
//
//
F4101.Fetch Single
VA rpt_LineType = [VA rpt_StockingType]
If BC Description (F554784B)(DSC1) is equal to <Blank>
Else
VA rpt_DescriptionLine1 = BC Description (F554784B)(DSC1)
End If
VA rpt_UnitOfMeasure = "EA"
VA rpt_PriceOverrideCode = "1"
VA rpt_UniqID = [BC Unique Key ID (Internal) (F554784B)(UKID)]
F47012.Insert
F47012.Insert
// Update Uploaded data
VA rpt_EdiSuccessfullyProcess = "Y"
VA rpt_ContractNumber_01 = [BC Unique Key ID (Internal) (F554784B)(UKID)]
F47012.Fetch Single
If SV File_IO_Status is equal to CO SUCCESS
F554084A.Update
F554784B.Update
End If
RV testvar = [BC Unique Key ID (Internal) (F554784B)(UKID)]

Tuesday, June 20, 2017

Top 20 Amount by Vendor SQL server

declare @fdate  int =201605, @tdate  int =201704

drop table #temp3
drop table #temp1
drop table #temp2

select YEAR_MO, count(*) MBRMTH  into #temp3 from [VW_MEMBMTHS]     group by YEAR_MO order by 1




SELECT  a.LOB, a.PROD_NAME, a.HCG_MR_line HCG_Detail, a.YEAR_MO,  PROV_LNAME BillingProviderName,MBRMTH MemberMonth,sum(AMT_PAID) ClaimsAmtPaid ,
sum(MR_procs_raw)Utilization  into #temp1
FROM [XXXXXXX].[dbo].[Table1] a with(nolock)
left outer join [XXXXXXX].[dbo].[DERS] b on PROV_ID=PROV_BILL
left outer join #temp3 c on  a.YEAR_MO=c.YEAR_MO
where a.YEAR_MO between @fdate and @tdate and HCG_MR_line in('P84','P85')
group by a.HCG_MR_line, a.LOB, a.PROD_NAME, a.YEAR_MO,PROV_LNAME ,MBRMTH
order by a.YEAR_MO,PROV_LNAME


--select * from #temp1 order by cntprovd desc

select * ,ROW_NUMBER()    over (        PARTITION BY   year_MO       order by year_MO,ClaimsAmtPaid desc ) AS RowNo into #temp2
from #temp1  order by year_MO,ClaimsAmtPaid desc

select * from #temp2 where RowNo<21






Inventory Adjustment Z file

Thursday, March 30, 2017

Convert Julian date to Calendar date PL SQL Oracle


select YADOB DOB,
CASE
when YADOB = 0 Then  TO_CHAR(TO_DATE(SUBSTR (YADOB,2,5),'YYDDD'),'YYYY-MM-DD')
when YADOB < 100000 Then   TO_CHAR(TO_DATE(YADOB+1900000, 'YYDDD'), 'MM/DD/YYYY')
when YADOB > 100000 Then  TO_CHAR(TO_DATE(YADOB+1900000, 'YYDDD'), 'MM/DD/YYYY')
ELSE   '0'   end JulianDT
   from proddta.F060116

By Shyamini Nair
 

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                   

Thursday, January 5, 2017

Convert JDE Julian date to Calendar date PL/SQL


convert Julian date to Calendar date Oracle


select to_char(to_date(GLDGJ+1900000,'YYYYDDD'), 'MM/DD/YYYY') as adate , gldgj from  PRODDTA.F0911 where gldgj>116360

Monday, January 2, 2017

Update GLPT in F4101/F4102/F41021 - JD Edwards SQL

     
   
declare @glpt varchar(4)
declare @mcu varchar(12)
set @glpt='XX12'
set @mcu='ZZZZWFFF'

--select ibglpt,* from proddta.f4102 where ltrim(ibmcu)=@mcu
--select liglpt,* from proddta.f41021 where ltrim(LIMCU)=@mcu
--select * from proddta.f4101 where imitm in(select distinct IBLITM from proddta.f4102 where ltrim(ibmcu)=@mcu)

update proddta.f4102 set ibglpt=@glpt where ltrim(ibmcu)=@mcu
update proddta.f41021 set LIGLPT=@glpt where ltrim(LIMCU)=@mcu
update proddta.f4101 set imglpt=@glpt where imitm in(select distinct IBITM from proddta.f4102 where ltrim(ibmcu)=@mcu)

Tuesday, November 1, 2016

Oracle Output Result Copy and paste in Excel

Run the query after that :-  

1. Ctrl + A
2. Ctrl + Shift + C 
3. Paste to Excel




Monday, October 17, 2016

maintain scroll position javascript



http://stackoverflow.com/questions/17642872/refresh-page-and-keep-scroll-position


http://www.redips.net/javascript/maintain-scroll-position/?param1=a&param2=b&param3=c&scroll=606

Sunday, September 25, 2016

Gridview update code behind c#

protected void TaskGridView_RowUpdating(object sender, GridViewUpdateEventArgs e)
  {    
    //Retrieve the table from the session object.
    DataTable dt = (DataTable)Session["TaskTable"];

    //Update the values.
    GridViewRow row = TaskGridView.Rows[e.RowIndex];
    dt.Rows[row.DataItemIndex]["Id"] = ((TextBox)(row.Cells[1].Controls[0])).Text;
    dt.Rows[row.DataItemIndex]["Description"] = ((TextBox)(row.Cells[2].Controls[0])).Text;
    dt.Rows[row.DataItemIndex]["IsComplete"] = ((CheckBox)(row.Cells[3].Controls[0])).Checked;

    //Reset the edit index.
    TaskGridView.EditIndex = -1;

    //Bind data to the GridView control.
    BindData();
  }
https://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.gridview.rowupdating(v=vs.110).aspx