Get Rolling 6 Months in SQL-Server

SELECT x.months, [YYYYMM] = CAST(CONVERT(CHAR(6),Months,112) AS INT)

FROM (VALUES (0),(1),(2),(3),(4),(5),(6)) r (n)

CROSS APPLY (SELECT Months = DATEADD(mm,-n,GETDATE())) x

Introduction to JD Edwards EnterpriseOne Tools: APIs and Business Functions

JD Edwards EnterpriseOne Tools APIs and business functions are used to create

complex, reusable routines in C. Business functions can call APIs directly and can in

turn be invoked from event rules (ER).

http://docs.oracle.com/cd/E17984_01/doc.898/e14700.pdf

JD Edwards main Development Topics

Objects of Interactive Development

Object Management Workbench

User Defined Codes (UDC)

Data Dictionary

Understanding Table Design Aid

Understanding Business View Design Aid

Understanding Form Design Aid (FDA)

Debug Interactive Application

Understanding Asynchronous Processing

Understanding Processing Options

Business Function Overview

Data Structures Design Aid

Create and Code C Business Function

Create and Code NER Business Function

Debugging C Code.

Power Form & Subforms

Cache

Master Business Functions

While Loop with temp table in SQL Server

declare @i int

set @i=1

WHILE (@i < 10)

Begin

set @i=@i+1

--select  '1'

drop table #temp32

select  * into  #temp32   from #temp31 

update #temp32 set RemainingLife=(RemainingLife-1), Accum=Accum+DeprPerMonth, NetBookValue=cast(NetCost+(Accum+DeprPerMonth) as decimal(15,2)),PastLife=1+PastLife,[month]=@i --where RemainingLife>0

update #temp32 set DeprPerMonth=0 --where RemainingLife>0

-- for IN N I

update  #temp32  set DeprPerMonth= -(isnull(NetCost,0)+ isnull(Accum,0))

where DeprMeth<>'00' and (cast((isnull(NetCost,0)/AssetLife)* -1 *(PastLife)- isnull(Accum,0) as decimal(15,2))-(isnull(NetCost,0)+ isnull(Accum,0)))>0

 

update  #temp32  set DeprPerMonth= cast((NetCost/AssetLife)* -1 *(PastLife)-isnull(Accum,0) as decimal(15,2))

where DeprMeth<>'00' and (cast((isnull(NetCost,0)/AssetLife)* -1 *(PastLife)- isnull(Accum,0) as decimal(15,2))-(isnull(NetCost,0)+ isnull(Accum,0)))<0

 

update  #temp32  set DeprPerMonth=0 where NetBookValue<=0 

update  #temp32  set Accum=0,NetBookValue=0,DeprPerMonth=0 where  Accum is null

update  #temp32  set PastLife=AssetLife where  PastLife>AssetLife

update  #temp32  set NetBookValue=NetCost where  Accum =0

-- IN '' 'I'

update #temp32 set DeprPerMonth =

(case when (cast((isnull(NetCost,0)/AssetLife)* -1 *(PastLife-1)- isnull(Accum,0) as decimal(15,2))-(isnull(NetCost,0)+ isnull(Accum,0)))>0 then -(isnull(NetCost,0)+ isnull(Accum,0))

Else cast((NetCost/AssetLife)* -1 *(PastLife-1)-isnull(Accum,0) as decimal(15,2))  end )*-1

where  DeprMeth='IN' and DeprInfo='' and CompMeth='I'  

-- 01 'N' 'I'

-- For disposable vale

update #temp32 set DeprPerMonth =cast(isnull(NetBookValue,0)as decimal(15,2))*-1 where  DeprMeth='01' and DeprInfo='N' and CompMeth='P'   and RemainingLife=-1 

update #temp32 set DeprPerMonth =case when RemainingLife+1 >0 then -1*(NetBookValue/(RemainingLife+1)) else 0 end where  DeprMeth='01' and DeprInfo='N' and CompMeth='P'  and AssetLife=84 

and   RemainingLife<>-1 

update #temp32 set DeprPerMonth =cast(isnull(NetCost/AssetLife,0)as decimal(15,2))*-1   where  DeprMeth='01' and DeprInfo='N' and CompMeth='P' and   AssetLife<>84 and NetBookValue>0  and   RemainingLife<>-1 

update #temp32 set DeprPerMonth =cast(isnull(NetBookValue,0)as decimal(15,2))*-1   where  DeprMeth='01' and DeprInfo='N' and CompMeth='P' and   AssetLife<>84 and NetBookValue>0 and   RemainingLife<>-1 

and NetCost/AssetLife>NetBookValue

-- Make it zero after straight line deprication calculation

update  #temp32  set RemainingLife=0 where  RemainingLife<0

--NB N P

update #temp32 set DeprPerMonth =case when RemainingLife+1 >0 then -1*(NetBookValue/(RemainingLife+1)) else 0 end where  DeprMeth='NB' and DeprInfo='N' and CompMeth='P' 

update #temp32 set NetBookValue=cast(NetBookValue as decimal(15,2)),Accum=cast(Accum as decimal(15,2)),DeprPerMonth=cast(DeprPerMonth as decimal(15,2))

drop table #temp31

select  * into  #temp31   from #temp32

insert into testTab  select  *  from #temp32

CONTINUE

end 

JD Edwards - How to create parent child relationship for Account master(F0901) Object AC

Change level of detail with temp table name.

DECLARE

    @db_name SYSNAME,

    @sql VARCHAR(1000),

    @databaseNM varchar(50),

    @ACOBJ int,

    @ACLDA int

    set @ACLDA=4

    drop table #temp1

    create table #temp1 (fld1 int,fld2 int) 

    

DECLARE db_cursor CURSOR FOR 

--SELECT Name FROM sys.databases where Name=@databaseNM

select ACOBJ  obj from  JDE_PRODUCTION.PRODDTA.F0909 where ACLDA=@ACLDA and acobj>00000 and acobj<100000   --and acobj=41110

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @db_name

WHILE (@@FETCH_STATUS = 0)

BEGIN

select @ACOBJ=MAX(ACOBJ)   from  JDE_PRODUCTION.PRODDTA.f0909 where  ACLDA=@ACLDA-1 and ACOBJ <(select MAX(ACOBJ)  from  JDE_PRODUCTION.PRODDTA.f0909 where ACOBJ=@db_name  and ACLDA=@ACLDA)

--select @ACOBJ=MAX(ACOBJ)  from  JDE_PRODUCTION.PRODDTA.f0909 where  ACLDA=6 and ACOBJ <(select MAX(ACOBJ)  from  JDE_PRODUCTION.PRODDTA.f0909 where ACOBJ=@db_name  and ACLDA=7)

Insert into #temp1 select @ACOBJ,@db_name

FETCH NEXT FROM db_cursor INTO @db_name

END

CLOSE db_cursor

DEALLOCATE db_cursor

--drop table #temp5

select * into #temp5  from #temp1

select *  from #temp1

--select * from #temp5

--select * from #temp6

--select * from #temp7

--select * from #temp8

--drop table  #temp4_1

select a7.fld1 A_071,a7.fld2  A_072,a8.fld1 A_081,a8.fld2 A_082  into #temp7_1 from #temp7 a7

left outer join  #temp8 a8 on  a7.fld2=a8.fld1

select fld1 A_061,fld2 A_062,A_071,A_072,A_081,A_082  into #temp6_1 from #temp7_1 a7 

left outer join  #temp6 a6 on  a6.fld2=a7.A_071

select fld1 A_051,fld2 A_052,A_061,A_062,A_071,A_072,A_081,A_082  into #temp5_1

from #temp6_1

left outer join  #temp5 a5 on  a5.fld2=A_061

select A_051,A_061,A_062,A_072, ISNULL(A_082,'')A_082 from #temp5_1

Extract all VBA code from Excel Workbook

Public Sub ExportModules()

    Dim bExport As Boolean

    Dim wkbSource As Excel.Workbook

    Dim szSourceWorkbook As String

    Dim szExportPath As String

    Dim szFileName As String

    'Dim cmpComponent As VBIDE.VBComponent

    ''' The code modules will be exported in a folder named.

    ''' VBAProjectFiles in the Documents folder.

    ''' The code below create this folder if it not exist

    ''' or delete all files in the folder if it exist.

    If FolderWithVBAProjectFiles = "Error" Then

        MsgBox "Export Folder not exist"

        Exit Sub

    End If

    

    On Error Resume Next

        Kill FolderWithVBAProjectFiles & "\*.*"

    On Error GoTo 0

    ''' NOTE: This workbook must be open in Excel.

    szSourceWorkbook = ActiveWorkbook.Name

    Set wkbSource = Application.Workbooks(szSourceWorkbook)

    

    If wkbSource.VBProject.Protection = 1 Then

    MsgBox "The VBA in this workbook is protected," & _

        "not possible to export the code"

    Exit Sub

    End If

    

    szExportPath = FolderWithVBAProjectFiles & "\"

    

    For Each cmpComponent In wkbSource.VBProject.VBComponents

        

        bExport = True

        szFileName = cmpComponent.Name

        ''' Concatenate the correct filename for export.

        Select Case cmpComponent.Type

            Case vbext_ct_ClassModule

                szFileName = szFileName & ".cls"

            Case vbext_ct_MSForm

                szFileName = szFileName & ".frm"

            Case vbext_ct_StdModule

                szFileName = szFileName & ".bas"

            Case vbext_ct_Document

                ''' This is a worksheet or workbook object.

                ''' Don't try to export.

                bExport = False

        End Select

        

        If bExport Then

            ''' Export the component to a text file.

            cmpComponent.Export szExportPath & szFileName

            

        ''' remove it from the project if you want

        '''wkbSource.VBProject.VBComponents.Remove cmpComponent

        

        End If

   

    Next cmpComponent

    MsgBox "Export is ready"

End Sub

Public Sub ImportModules()

    Dim wkbTarget As Excel.Workbook

    Dim objFSO As Scripting.FileSystemObject

    Dim objFile As Scripting.File

    Dim szTargetWorkbook As String

    Dim szImportPath As String

    Dim szFileName As String

    Dim cmpComponents As VBIDE.VBComponents

    If ActiveWorkbook.Name = ThisWorkbook.Name Then

        MsgBox "Select another destination workbook" & _

        "Not possible to import in this workbook "

        Exit Sub

    End If

    'Get the path to the folder with modules

    If FolderWithVBAProjectFiles = "Error" Then

        MsgBox "Import Folder not exist"

        Exit Sub

    End If

    ''' NOTE: This workbook must be open in Excel.

    szTargetWorkbook = ActiveWorkbook.Name

    Set wkbTarget = Application.Workbooks(szTargetWorkbook)

    

    If wkbTarget.VBProject.Protection = 1 Then

    MsgBox "The VBA in this workbook is protected," & _

        "not possible to Import the code"

    Exit Sub

    End If

    ''' NOTE: Path where the code modules are located.

    szImportPath = FolderWithVBAProjectFiles & "\"

        

    Set objFSO = New Scripting.FileSystemObject

    If objFSO.GetFolder(szImportPath).Files.Count = 0 Then

       MsgBox "There are no files to import"

       Exit Sub

    End If

    'Delete all modules/Userforms from the ActiveWorkbook

    Call DeleteVBAModulesAndUserForms

    Set cmpComponents = wkbTarget.VBProject.VBComponents

    

    ''' Import all the code modules in the specified path

    ''' to the ActiveWorkbook.

    For Each objFile In objFSO.GetFolder(szImportPath).Files

    

        If (objFSO.GetExtensionName(objFile.Name) = "cls") Or _

            (objFSO.GetExtensionName(objFile.Name) = "frm") Or _

            (objFSO.GetExtensionName(objFile.Name) = "bas") Then

            cmpComponents.Import objFile.Path

        End If

        

    Next objFile

    

    MsgBox "Import is ready"

End Sub

Function FolderWithVBAProjectFiles() As String

    Dim WshShell As Object

    Dim FSO As Object

    Dim SpecialPath As String

    Set WshShell = CreateObject("WScript.Shell")

    Set FSO = CreateObject("scripting.filesystemobject")

    SpecialPath = WshShell.SpecialFolders("MyDocuments")

    If Right(SpecialPath, 1) <> "\" Then

        SpecialPath = SpecialPath & "\"

    End If

    

    If FSO.FolderExists(SpecialPath & "VBAProjectFiles") = False Then

        On Error Resume Next

        MkDir SpecialPath & "VBAProjectFiles"

        On Error GoTo 0

    End If

    

    If FSO.FolderExists(SpecialPath & "VBAProjectFiles") = True Then

        FolderWithVBAProjectFiles = SpecialPath & "VBAProjectFiles"

    Else

        FolderWithVBAProjectFiles = "Error"

    End If

    

End Function

Function DeleteVBAModulesAndUserForms()

        Dim VBProj As VBIDE.VBProject

        Dim VBComp As VBIDE.VBComponent

        

        Set VBProj = ActiveWorkbook.VBProject

        

        For Each VBComp In VBProj.VBComponents

            If VBComp.Type = vbext_ct_Document Then

                'Thisworkbook or worksheet module

                'We do nothing

            Else

                VBProj.VBComponents.Remove VBComp

            End If

        Next VBComp

End Function

 

source:- http://www.rondebruin.nl/win/s9/win002.htm

How to find UDF value in P6 Primavira

select * from udfvalue
select * from udfcode
select * from udfvalue where udf_text like '%manager%'
select distinct udf_text from udfvalue where udf_text like '%IT Project Manager%'
select * from udfvalue where udf_text like '%Project Manager%'
select * from udfvalue where udf_type_id=330
user_field_330
sp_help projwbs