[E-Bussiness Suite] Query Invoice Prepayment with Accountability Invoice


Deskripsi
      To display Accountability Invoice based on Invoice Prepayment, you can modified the filter.

Howto
select DISTINCT aia1.invoice_num
                ,pv.VENDOR_NAME
                ,aia1.amount_paid
                ,aia1.DESCRIPTION as Deskripsi_Prepayment
                ,aia1.GL_DATE as GL_Prepayment
                ,aia2.invoice_num as Invoice_PJ
                ,aia2.DESCRIPTION as Deskripsi_PJ
                ,(aida2.AMOUNT * -1) as Amount_PJ
                ,aia2.GL_DATE as GL_PJ
             
from ap_invoices_all aia1
    ,ap_invoice_distributions_all aida1
    ,ap_invoices_all aia2
    ,ap_invoice_distributions_all aida2
    ,po_vendors PV
 
where aia1.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
and aia1.GL_DATE >= to_date('01/01/2013','DD/MM/YYYY')
and aia1.GL_DATE <= to_date('31/12/2013','DD/MM/YYYY')
--and aia1.INVOICE_NUM = 'xxx'
--and aia1.VENDOR_ID = 'xxx'
and aia1.INVOICE_ID(+) = aida1.INVOICE_ID
and pv.VENDOR_ID = aia1.VENDOR_ID
and (aida1.REVERSAL_FLAG is null or aida1.REVERSAL_FLAG = 'N')
and aida2.PREPAY_DISTRIBUTION_ID(+) = aida1.INVOICE_DISTRIBUTION_ID
and aia2.INVOICE_ID(+) = aida2.INVOICE_ID
and (aida2.REVERSAL_FLAG is null or aida2.REVERSAL_FLAG = 'N')
--and aia2.vendor_id = 'xxx'
order by to_date(aia1.GL_DATE,'DD/MM/YYYY'),aia1.INVOICE_NUM;

[VB.NET] About Resource


Description
     Normally if you need some file or resource file from external source to use in VB.NET , you just calling the path of external file but it will create a little delay when load if the file oversize.
    Sometimes you not only need the resource but the speed of execution too, VB.NET has added feature to solve this issue : RESOURCES.
    Resource will be embed you file into your application, and you don't have to be bothered in calling location path file and the speed of execution will increase , but the size of file executable will increase too.

Howto
   Project > Properties > Select Menu Resource (you can import , or remove resources safely without make the settings error ).
 
   Syntax to call resource :
   My.resources.Name_Resource


[VB.NET] Textbox hanya menerima input angka


Deskripsi
     Textbox hanya menrima input angka / numerik pada VB.NET

Howto

1. Use event KEYPRESS

2. Write code below :
If Asc(e.KeyChar) <> 8 Then
         If Asc(e.KeyChar) < 48 Or Asc(e.KeyChar) > 57 Then
                e.Handled = True
         End If
End If

[E-Bussiness Suite] Query Data Vendor (Supplier)


Deskripsi
     Display all data master vendor (supplier)

Howto
1. Running Show Hidden View , here this link 
2. Running Query below :
select distinct pvs.ORG_ID
               ,pv.vendor_name
               ,pv.VENDOR_NAME_ALT
               ,pvs.ADDRESS_LINE1
               ,pvs.ADDRESS_LINE2
               ,pvs.VENDOR_SITE_CODE
               ,pvs.CITY
               ,pvs.ZIP
               ,pvs.PROVINCE
               ,pvs.PAYMENT_METHOD_LOOKUP_CODE
               ,pvs.TERMS_DATE_BASIS
               ,pvs.VAT_CODE
               ,pvs.VAT_REGISTRATION_NUM
               ,pvs.DISTRIBUTION_SET_ID
               ,(gcc.SEGMENT1 || '.' || gcc.SEGMENT2 || '.' || gcc.SEGMENT3 || '.' || gcc.segment4) as ACCTS_PAY_CODE_COMBINATION
               ,(gcc2.SEGMENT1 || '.' || gcc2.SEGMENT2 || '.' || gcc2.SEGMENT3 || '.' || gcc2.segment4) as PREPAY_CODE_COMBINATION
               ,pvs.ACCTS_PAY_CODE_COMBINATION_ID
               ,pvs.PREPAY_CODE_COMBINATION_ID
               ,pvs.TERMS_ID
               ,pvs.ALLOW_AWT_FLAG
               ,pvs.PURCHASING_SITE_FLAG
               ,pvc.first_name
               ,pvc.last_name
               ,pvc.prefix
               ,pvc.title
               ,pvc.area_code
               ,pvc.phone
               ,pvc.contact_name_alt
               ,pvc.email_address
               ,pvc.alt_area_code
               ,pvc.fax

from po_vendors PV,
     po_vendor_sites PVS,
     po_vendor_contacts PVC,
     gl_code_combinations GCC,
     gl_code_combinations GCC2
   
 
where pv.VENDOR_ID(+) = pvs.VENDOR_ID
and pv.VENDOR_ID = pvc.vendor_contact_id(+)
and pvs.ACCTS_PAY_CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID(+)
and pvs.PREPAY_CODE_COMBINATION_ID = gcc2.CODE_COMBINATION_ID(+)
--and pvs.org_id = 0
order by vendor_name asc;

[VB.NET] Create DLL (Class Library) and Link to Main Application


Deskripsi
     Create a Class Library (.dll) and link to Main Application using Early Binding or Late Binding.

Howto

1. Create class library File :
- New Project > Class Library ( give name TestDLL, it will use as namespace)
- create simple class like this :

Imports System.Windows.Forms 'import this class , you will need any method

Public Class Myclass
    Public Sub MyMethod()
        MessageBox.Show("Hello World DLL")
    End Sub
End Class

save project and then Build > Build TestDLL , you can find the file TestDLL.dll at folder bin/release/


2. Create Main Application
- New Project > Windows Form Application (give name MyForm)
- to use Class Library , we have 2 method : Early Binding and Late Binding
   - Early binding means , we load the dll resource when Main Application Run , it will increase perfomance (speed) when access the Dll.
   - Late Binding means , we load the dll resource as we need so we use the memory as needed too.

* Early Binding
   - to use early binding just Project > Add Preference > find TestDLL.dll
   - Import the namespace after add refference :  import TestDLL
   - Dll are now linked , you can create an object now.
      Dim obj = New Myclass()
      obj.MyMethod()

* Late Binding
   - Late Binding we don't use refference , but we need the path of file dll. don't forget to drop the dll files into 1 folder with Main Application.
   - Linking to DLL  
     'Dim RefAssembly As Reflection.Assembly = Reflection.Assembly.LoadFrom("Filename.dll")
     Dim RefAssembly As Reflection.Assembly = Reflection.Assembly.LoadFrom("TestDLL.dll")
     'Dim obj As Object = RefAssembly.CreateInstance("Namespace.Class", IgnoreCase)
     Dim obj As Object = RefAssembly.CreateInstance("TestDLL.Myclass", True)
     obj.MyMethod()

Simple right ? hope it's help you :D

[VB.NET] Memory Leak from Object


Deskripsi
    Memory leak always to be enemy of ours (as a programer). it will increasing memory up up up and then slow computers client performance and last think freeze LoL -_- , .NET provide feature GarbageCollector or we called GC to clean unmanaged resource.

Howto
    1. Create class with implements IDisposable
    2. Create sub with name Dispose Implements IDisposable.Dispose
    3.  Add GC.SuppressFinalize(Me), to not execute Finalize sub when dispose the object , normally every object closing always execute Finalize() , if you want to always execute sub Finalize you must not use this method.

example class :

Public Class test
           Implements IDisposable
 
    Public Sub New()
         ' Always executed when object created
         ' you add paramater like another sub
    End Sub

    public sub HelloWorld()
         MessageBox.show("test memory")
    end sub

    Protected Overrides Sub Finalize()
          'Always executed when object destroy
   End Sub

    Sub Dispose() Implements IDisposable.Dispose
           GC.SuppressFinalize(Me) 'not must
    End Sub
end class



in some function or event (for example click)

MessageBox.Show("Current Memory : " & GC.GetTotalMemory(False))
dim obj = new test()
obj.HelloWorld()
MessageBox.Show("Total Memory after add object : " & GC.GetTotalMemory(False))
obj.dispose()
obj = nothing
GC.Collect() ' GC is Garbage Collector
MessageBox.Show("Total memory after object dispose : " & GC.GetTotalMemory(False))

you will know the different every messagebox :D , don't focus the memory value on taskmanager.

[VB.NET] Inheritance Class


Deskripsi
     example Inheritance vb.NET and the relation between (access level)

Howto
Access property : 1. Public : can access anywhere , any assembly
                            2. Private : only current class can use it, inheritance not allowed , current assembly
                            3. Protect : only current class and inheritance allowed
                            4. Friend : can access anywhere , but only current assembly
                            5. Friend Protect : only current class, current assembly and inheritance allowed

class1
    public sub helloworld()
          messagebox.show "this is class 1"
    end sub
end class

-------------------------------------------------------------

class 2
    inheritance class1
 
    public sub methodclass2()
          messagebox.show "this is class 2"
          helloworld() 'inheritance from class 1
    end sub
end class


[VB.NET] Array Control


Deskripsi
     HELPPP !!! , how to create array control in VB.net ??? and how detect from event's ??  for example clicked ? it's totally different from vb 6. how we do that ??? that's simple :)

Howto
    1. make sure the object must have name with the number. for example telp1, telp2 ,telp3 etc....
    2. to access them together we need create variable array as a collection , look below
       Dim buttonCollection() as button = {telp1,telp2,telp3.......}

       *Legend : - buttonCollection is the name of variable array
                       - telp1,telp2....etc is a button , the type must same as buttonCollection type. if you create textbox type , the collection must textbox type too (textboxCollection as textbox).
                       - Index always from 0
             to use : buttonCollection( i ).method 
           
    3. some case from event , method above not usefull if you need detect object control array from event for
        example from click, to do that look at step below :
         - create function click (free name) , with handles all object button.
         - use CTYPE
         - create control number based on controlname
        look code below :

        Private Sub Detect_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles
        telp1.Click, telp2.Click, telp3.Click, telp4.Click, telp5.Click, telp6.Click ...etc

        Dim controlName As String 'buffer namecontrol
        Dim controlNumber As Double 'buffer nomorcontrol

        controlName = CType(sender, Button).Name
        controlNumber = controlName.Remove(0, 4) 'substring , remove 4 character from index 0

        If CType(sender, Button).BackColor = Color.Red Then
            controlInterface(controlNumber, "green") ' another sub
            CType(sender, Button).BackColor = Color.Green
        Else
            controlInterface(controlNumber, "red") ' another sub
            CType(sender, Button).BackColor = Color.Red
        End If
        End Sub

        Here's the explanation :
        Detect_Click : you can use name instead "detect". free :)
        ByVal sender As Object = always return object which handle.                                                  
        handles telp1.click .... telp6.click = list of an object will be handle.
     
        this is main explanation :
        CType(sender, Button).BackColor 
        if i click telp1.click , the sender will return value telp1 as an object, to direct use we can use Ctype. look at the 2 mark (yellow and blue), we must specify type of Ctype like yellow mark(button or textbox or label dst ....). the blue mark will generate method based on yellow mark. that's the key :)

to more understand  you must try :) , hope's this help !

[E-Bussiness Suite] Query PR - PO - Vendor


Deskripsi
    Untuk mendapatkan data PR - PO + PO Amount dan Nama Vendor berdasarkan range date create PR dan ID Vendor

Howto
select distinct prha.SEGMENT1 as PR
               ,prha.DESCRIPTION as PR_DESC
               ,prha.AUTHORIZATION_STATUS as PR_STATUS
               ,pha.SEGMENT1 as PO
               ,PO_AMOUNT.amount as PO_Amount
               ,pha.AUTHORIZATION_STATUS as PO_STATUS
               ,pv.VENDOR_NAME
               ,prha.CREATION_DATE as PR_DATE
             
from  po_requisition_headers_all PRHA
     ,po_requisition_lines_all PRLA
     ,po_line_locations_all PLLA
     ,po_headers_all PHA
     ,po_lines_all pla
     ,po_vendors PV
     ,(select  PHA.po_header_id
              ,SUM(PLA.UNIT_PRICE*PLA.QUANTITY) as Amount
              ,pv.vendor_id
       from PO_HEADERS_ALL PHA
           ,PO_LINES_ALL PLA
           ,po_vendors PV
       where PHA.po_header_id = PLA.po_header_id
       and pha.VENDOR_ID = pv.VENDOR_ID
       and pv.VENDOR_ID = :VENDOR_ID
       group by PHA.po_header_id,pv.vendor_id) PO_AMOUNT

where prha.CREATION_DATE >= to_date('01/01/2011','DD/MM/YYYY')
and prha.CREATION_DATE <= to_date('31/12/2013','DD/MM/YYYY')
and prha.REQUISITION_HEADER_ID = prla.REQUISITION_HEADER_ID(+)
and prla.LINE_LOCATION_ID = plla.LINE_LOCATION_ID(+)
and plla.PO_LINE_ID = pla.PO_LINE_ID(+)
and pla.PO_HEADER_ID = pha.PO_HEADER_ID(+)
and pha.PO_HEADER_ID = PO_AMOUNT.po_header_id
and pha.VENDOR_ID = pv.VENDOR_ID
and pv.VENDOR_ID = :VENDOR_ID

group by prha.SEGMENT1
        ,po_amount.amount
        ,prha.DESCRIPTION
        ,prha.AUTHORIZATION_STATUS
        ,pha.SEGMENT1
        ,pha.AUTHORIZATION_STATUS
        ,pv.VENDOR_NAME
        ,prha.CREATION_DATE

[E-Bussiness Suite] PR link to Master Item and GL


Deskripsi
     PR - Master Item - GL , based on PR Line and Distribution.

Howto
select distinct --PRHA.REQUISITION_HEADER_ID
              --, prla.line_num
              --, prla.item_description
                msif.segment1 as MASTER_ITEM
              , msif.DESCRIPTION as DESKRIPSI_ITEM
              , (gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4) as COA
           
from po_requisition_headers_all PRHA
    ,po_requisition_lines_all PRLA
    ,po_req_distributions_all PRDA
    ,MTL_SYSTEM_ITEMS_FVL MSIF
    ,GL_CODE_COMBINATIONS GCC
   
where prha.CREATION_DATE >= to_date('01/01/2013','DD/MM/YYYY')
and prha.CREATION_DATE <= to_date('31/12/2013','DD/MM/YYYY')
--and prha.segment1 = 'PR NUMBER'
and prha.requisition_header_id = PRLA.requisition_header_id
and prla.REQUISITION_LINE_ID = prda.REQUISITION_LINE_ID
and prla.ITEM_ID = msif.INVENTORY_ITEM_ID
and gcc.CODE_COMBINATION_ID = prda.CODE_COMBINATION_ID
order by msif.segment1 asc

[VB6] Using Regular Expression in String


Deskripsi
     Using Regular Expression or Regex in VB is very simply , we just need project refference , string we want to get and then the pattern.

Howto
1.  Project > Components > Microsoft VBscript Regular Expressions 5.5
2.  Write code below
'Create variable
Dim html as string
Dim myRegExp As regexp ' for create object
Dim myMatches As MatchCollection ' collection matches string (raw)
Dim myMatch As Match ' matches string

Set myRegExp = New regexp
myRegExp.IgnoreCase = True 'case insensitive
myRegExp.Global = True
' create your pattern , for example i use pattern that grab some string in web page source using regex.
myRegExp.Pattern = "<td style=" & """height:50px;""" & ">(.*)</td>"

'execute raw string, for example in html page source i have raw string
Set myMatches = myRegExp.Execute(html)

'to get string that matches with pattern
MsgBox myMatches.Count

For Each myMatch In myMatches   'get the match string.
      msgbox myMatch.Value
Next

*tips : if you still confuse create the pattern for regex , you can learn from www.regexpal.com to test your regex, just input raw string and create the pattern.
the pattern which match raw string will be highlight with a color, good website i think...
it save my time create a regex :)



[VB6] Write to file text


Deskripsi
    Write to file with format text using VB 6

Howto

    Dim sFileText As String
    Dim iFileNo As Integer
 
    iFileNo = FreeFile
    'open the file for writing
    Open "D:\myfile.txt" For Output As #iFileNo
    'please note, if this file already exists it will be overwritten!

    'write some example text to the file
    Print #iFileNo, "My String 1 Here"
    Print #iFileNo, "My String 2 Here"
 
    'close the file (if you dont do this, you wont be able to open it again!)
    Close #iFileNo

*Tips : you can change the extension file :)

[VB6] Get Page Source Web Using MSXML2.ServerXMLHTTP


Deskripsi
      Get page source website using MSXML2.ServerXMLHTTP without proxy.

Howto
    Dim objHttp As Object, strText, strUrl As String
    strUrl = "http://www.target-web.com" 'url must complete
    Set objHttp = CreateObject("MSXML2.ServerXMLHTTP")
    objHttp.Open "GET", "", False
    objHttp.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.2; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/30.0.1599.17 Safari/537.36"
    objHttp.Send ("")
    strText = objHttp.responseText
    Set objHttp = Nothing
    msgbox strText

*note : if you want to connect using proxy , you must execute command in Command Prompt :
           proxycfg -d 'if you want to direct connect access to the internet
           proxycfg -p 10.12.xx.xx:8080 "<local>" 'if you want connect using proxy.
                             -d as direct
                             -p as proxy
                             -10.12.xx.xx as proxy server
                             - <local> as local computer 
          
*tips : in VB you can create a file text , so you can can create batch file and then execute from vb.  
     

[E-Bussiness Suite] Query Invoice Details AP


Deskripsi
     Untuk menampilkan nama vendor , deskripsi , no_pajak , invoice number , dpp , ppn 10% , tanggal invoice , tanggal faktur.

Howto
select pv.VENDOR_NAME as Nama_PT
      ,ai.DESCRIPTION as Deskripsi
      ,data_tax.ATTRIBUTE1 as No_Pajak
      ,ai.INVOICE_NUM as Invoice_Number
      ,data_item.amount as DPP
      ,data_tax.amount as PPN
      ,to_date(ai.INVOICE_DATE,'DD/MM/YYYY') as Tanggal_Invoice
      ,data_tax.ATTRIBUTE2 as Tanggal_Faktur
      --,aid.INVOICE_ID
from ap_invoices_all ai
    ,po_vendors pv
    ,(
        select aid.INVOICE_ID, aid.attribute1, aid.attribute2, aid.AMOUNT
        from ap_invoice_distributions aid
        where aid.LINE_TYPE_LOOKUP_CODE = 'TAX'
     ) data_tax
    ,(
        select aid.INVOICE_ID, aid.AMOUNT
        from ap_invoice_distributions aid
        where aid.LINE_TYPE_LOOKUP_CODE = 'ITEM'
     ) data_item

where ai.GL_DATE >= to_date('01/08/2013','DD/MM/YYYY')
and ai.GL_DATE <= to_date('31/08/2013','DD/MM/YYYY')
and ai.INVOICE_NUM = '1130144'
and ai.VENDOR_ID = pv.VENDOR_ID
and ai.INVOICE_ID = data_tax.INVOICE_ID
and ai.INVOICE_ID = data_item.INVOICE_ID
;

[E-Bussiness Suite] Query Receivable


Deskripsi
     Untuk mengambil data Receivable (AR) berupa nomor invoice , no faktur pajak , revenue ammount , 10% ppn dari revenue ammount , dan gl_date berdasarkan nomor invoice

Howto
1. Run this query Here
2. Run query below
select RCTA.TRX_NUMBER as Invoice_Number
      ,RCTA.ATTRIBUTE14 as Faktur_Pajak
      ,HP.PARTY_NAME as Nama_PT
      --,sum(extended_amount) as extended_ammount
      ,sum(revenue_amount) as revenue_ammount
      ,sum(10/100*revenue_amount) as PPN_Ammount
      ,GD.GL_DATE as GL_Invoice
from ra_customer_trx_all RCTA
    ,ra_customer_trx_lines RCTL
    ,hz_cust_accounts HCA
    ,hz_parties HP
    ,ra_cust_trx_line_gl_dist GD
where RCTA.TRX_NUMBER = '3670174'
and line_type = 'LINE'
and RCTA.CUSTOMER_TRX_ID = RCTL.customer_trx_id
and RCTA.BILL_TO_CUSTOMER_ID = HCA.CUST_ACCOUNT_ID
and HCA.PARTY_ID = HP.PARTY_ID
and RCTA.CUSTOMER_TRX_ID = GD.CUSTOMER_TRX_ID
and 'REC' = gd.account_class
and 'Y' = gd.latest_rec_flag
group by RCTA.customer_trx_id  
        ,RCTA.ATTRIBUTE14
        ,HP.PARTY_NAME
        ,GD.GL_DATE
        ,RCTA.TRX_NUMBER
;

*Note : Field attribute14 as No_Pajak / Tax_Number , maybe vary based on oracle settings
     

[E-Bussiness Suite] Query Master Item with Expense Account


Deskripsi
     Berikut adalah query untuk menarik Master item beserta detail deskripsi dan Expense Account.

Howto
SELECT MSIF.segment1 as Master_Items,
               MSIF.description as Deskripsi,
               GCCK.CONCATENATED_SEGMENTS as Expense_Account,
               MSIF.CREATION_DATE
FROM MTL_SYSTEM_ITEMS_FVL MSIF,
             GL_CODE_COMBINATIONS_KFV GCCK  
WHERE GCCK.CODE_COMBINATION_ID = MSIF.EXPENSE_ACCOUNT
ORDER BY MSIF.SEGMENT1 ASC;