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                   

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

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)

Oracle Output Result Copy and paste in Excel

Run the query after that :-  

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

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

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

Read/Display variable in XSLT asp .net

 

                             
                                RadioMemberadd:
                             

Update table with Join PL SQL

MERGE INTO  TBL500_BUSUPACCESS A
     USING (SELECT MCMCU,MCRP06, MCDL01 FROM CRPDTA.F0006@HIJDED1) B
        ON (A.BU = Ltrim(MCMCU))
WHEN MATCHED
THEN
   UPDATE SET
      A.BU_DESC =
         (CASE
             WHEN A.MCCO IN 1  THEN SUBSTR(A.MCCO,-2)||' (PAR) '|| MCDL01
             ELSE SUBSTR(A.MCCO,-2)||' ('||MCRP06||') '|| MCDL01 END);
End  SP_500_SUP_REPORTASSIGN ;

SP_Help in PLSQL

Select * from user_constraints where table_name='TBL500_BUSUPACCESS';

Oracle SQL Convert systemdate to JD Edwards Julian

select
to_number(concat('1',to_char(sysdate,'YYDDD')))
from dual ;

Get data from SQL Server and paste in a Activesheet VBA

SQL_Set_Connection

'Debug.Print SQL_Str
Set rst = cnt.Execute("budget.dbo.SP_PIP_Excel") '''' '" & CoFilter & "' ,'*','" & cbo_LT & "'")

If rst.RecordCount = 0 Then
  MsgBox "No Data available - contact Administrator"
  'GoTo Finish
End If



Dim RptName As String
Dim RptShtName As String

RptName = "\\dc01cxbudpv01\budget_software\MCE_Report1.xlsx"
RptShtName = "Sheet1"

Dim objXL
Set objXL = CreateObject("Excel.Application")

'AppActivate objXL
Workbooks.Open (RptName)

Workbooks("MCE_Report1.xlsx").Activate
ActiveWorkbook.Sheets("Sheet1").Activate
Debug.Print ActiveWorkbook.Name
Debug.Print ActiveSheet.Name
'objXL.Calculation = xlCalculationAutomatic
'Set WB = objXL.ActiveWorkbook

'On Error Resume Next
'WB.Sheets("Results").Range("Data1").EntireRow.Delete
'WB.Sheets("Results").Range("Data1").CopyFromRecordset rst

ActiveSheet.Range("A1:A10000").CopyFromRecordset rst

Oracle Excel Connection anf connectionstring

Data -> From Other Sources->From Data connection Wizard->Other/Advance->Microsoft OLE DB Provider for Oracle-> next-> sever name same as source name -> user name and password and test connection.

get Checked radio button Value

        alert(document.querySelector('input[name = "ProductPermId"]:checked').value);

Javascript populate textbox

    document.getElementById("textbox").innerHTML = "youe teext";




Oracle 12 SQL SERVER link Error JD Edwards

The OLE DB provider "OraOLEDB.Oracle" for linked server "JDED1" supplied inconsistent metadata for a column. The column "MCMCU" (compile-time ordinal 1) of object ""CRPDTA"."F0006"" was reported to have a "LENGTH" of 12 at compile time and 24 at run time.

following is working


Select * from OPENQUERY(JDED1,'SELECT substr(MCMCU,1,12) MCMCU,substr(MCDL01,1,60)MCDL01  FROM CRPDTA.F0006') ORDER BY 1;