Posting Script

AP



"Usage: ap_post company transid dbtrans doctype display DB=dbname"

        DISPLAY "    where company     - company_id"

        DISPLAY "          transid     - transaction id"

        DISPLAY "          dbtrans     - 0=OFF, 1=ON"

        DISPLAY "          doctype     - ADJ/CRN/INV/PAY/DRN"

        DISPLAY "          display     - 0=OFF, 1=ON"

        DISPLAY "          dbname      - database name"



e.g

         i.      AP Inv à run ap_post UPM 000324 1 INV 0 DB=umplive

        ii.      CRN à run ap_post UPM 000324 1 CRN 0 DB=umplive

      iii.      AP Pay à run ap_post UPM 000324 1 PAY 0 DB=umplive

      iv.      AP Adj à run ap_post UPM 000324 1 ADJ 0 DB=umplive

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



AR



"Usage: ar_post company transid dbtrans doctype display DB=dbname"

        DISPLAY "    where company     - company_id"

        DISPLAY "          transid     - transaction id"

        DISPLAY "          dbtrans     - 0=OFF, 1=ON"

        DISPLAY "          doctype     - ADJ/CRN/INV/CSH/DRN/DEP"

        DISPLAY "          display     - 0=OFF, 1=ON"

        DISPLAY "          dbname      - database name"

        SLEEP 4

        CALL gp_exitprog("ar_post")



e.g

                     i.      AR Csh – run ar_post UPM 000324 1 CSH 0 DB=umplive



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



BI



"Usage: bi_post company postid dbtrans doctype display DB=dbname"

        DISPLAY "    where company     - company_id"

        DISPLAY "          postid      - postid id or \"ALL\" "

        DISPLAY "          dbtrans     - 0=OFF, 1=ON"

        DISPLAY "          doctype     - INV/CRN"

        DISPLAY "          display     - 0=OFF, 1=ON"

        DISPLAY "          dbname      - database name"

        SLEEP 4

        CALL gp_exitprog("bi_post")



e.g

                     i.      AR Billing Invoice à run bi_post UPM 0001432 1 INV 0 DB=upmlive



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



GL



"Usage: gl_post journal dbtrans display DB=dbname"

        DISPLAY "    where journal     - [journal id|YREND|RECLOSE|ALL]"

        DISPLAY "          dbtrans     - 0=OFF, 1=ON"

        DISPLAY "          display     - 0=OFF, 1=ON"

        DISPLAY "          approved    - 0=OFF, 1=ON (optional)"

        DISPLAY "          dbname      - database name"

        SLEEP 4

        CALL gp_exitprog("gl_post")

grun gl_post <ID> 1 0 0 DB=lzslive

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



CB



"Usage: cb_post company_code transid dbtrans doctype display DB=dbname"

        DISPLAY "    where company     - company code"

        DISPLAY "          batchid     - [transaction id|ALL]"

        DISPLAY "          dbtrans     - 0=OFF, 1=ON"

        DISPLAY "          doctype     - PAY/CSH/ADJ"

        DISPLAY "          display     - 0=OFF, 1=ON"

        DISPLAY "          dbname      - database name"

        SLEEP 4

        CALL gp_exitprog("cb_post")



e.g

                     i.      CB Receipt à run cb_post 0032 0001234 1 CSH 0 DB=upmlive

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


"Usage: pu_ordcmt company ordid dbtrans display DB=dbname"
            DISPLAY "    where company     - company_id"
            DISPLAY "          ordid       - purchase order"
            DISPLAY "          dbtrans     - 0=OFF, 1=ON"
            DISPLAY "          display     - 0=OFF, 1=ON"
            DISPLAY "          DB=dbname   - database name"


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

usage of bc_adjustment post

run bc_adjpost batchid ADJ 1 0 DB=uji74


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

run mj_post AGI ALL MYR 1 0 DB=lzslive

DISPLAY "Usage: mj_post company journal currency dbtrans display DB=dbname"
DISPLAY "    where company     - company_id"
DISPLAY "          journal     - journal id"
DISPLAY "          curcode     - currency code"
DISPLAY "          dbtrans     - 0=OFF, 1=ON"
DISPLAY "          display     - 0=OFF, 1=ON"
DISPLAY "          dbname      - database name"

While Loop Sample in Jitterbit

<trans>

$BatchNo=DbExecute("<TAG>MFUND-CBRCP/Sources/kwaplive</TAG>",

"Select cbrb_batchid 

from cb_rcptbatch 

where cbrb_batchtot = 1 

and cbrb_batchid matches 'MF*'");

$No_of_Batch = Length($BatchNo);

$i=0;

while($i<$No_of_Batch,

$BatchTotal=DbExecute("<TAG>MFUND-CBRCP/Sources/kwaplive</TAG>",

"Select sum(cbrh_amount) 

from cb_rcpthdr 

where cbrh_batchid '"+trimchars($BatchNo[$i],"{}")+"'");

$SumAmount=trimchars($BatchTotal,"{}");

DbExecute("<TAG>MFUND-CBRCP/Targets/kwaplive</TAG>",

"update cb_rcptbatch  

set cbrb_batchtot = "+$SumAmount+", 

cbrb_usrctltot = "+$SumAmount+" 

where cbrb_batchid = '"+trimchars($BatchNo[$i],"{}")+"'");

$i=$i+1

)

</trans>

Accumulating Count

//If(TMLD_JOURNAL==$jno,

// $count=$count+1

// ,$jno=TMLD_JOURNAL;$count=1)

When the decimal value round up

There is one client that having issue of round up amount. As an example, if the data has been passed as 87652.23 it will be stored as 87652.20. This will be a problem because the it will cause the amount not tally between source and target. You can format the source as double or float. But if this two methods fail, you should try below:

• Format("%.2f",<column>)

Let say you have one column name total_amt. Try do like this:

• <trans>
• $amt=total_amt;
• $amt=Format("%.2f",$amt)
• <\trans>

How to determine port number, service name and server name in Informix

Determine Server Name

• Login to Informix Server
• Type echo $INFORMIXSERVER. Enter.
• From here you can see the your server name.
• e.g ol_myifxdb

Determine Service Name

• Login to Informix server
• Type cd $INFORMIXDIR/etc
• Type cat sqlhosts. Check the line that contain your server  name.
• e.g ol_myifxdb         onsoctcp        192.168.2.200      service_myifxdb
• The line will show you the server name, protocol, server address and service name.

Determine Port Name

• Login to Informix Server
• Type grep <service name> /etc/services
• e.g grep service_myifxdb /etc/services
• The line will show you the service name and port number
• e.g service_myifxdb   60200/tcp

Configure Informix ODBC

These steps are for setting up the Informix odbc in your Windows machine.

• Install Informix SDK Client in your machine.
• Goto C:\Windows\System32\drivers\etc. Open services file. Add service name and port number of the Informix Server that you're connecting to. e.g.

Service Name ad Port Number

• Then goto Control Panel\All Control Panel Items\Administrative Tools. 
• Double click Data Sources (ODBC).
• Under System DSN tab, click Add.
• Select IBM Informix ODBC Driver from the list and click Finish. e.g.

• In General tab, fill in the Data Source Name and Description.
• Fill in the Connection tab as an example as below

• Click Apply & Test Connection

Can't install Jitterbit Server because of error in installing postgres

Usually this issue happen when you try installing Jitterbit Server Community version in Windows Server. You will get an error like 'No USER/postgres found'.

1. Go to Server Manager-User-Add User.
2. Create new user account for postgres. Be sure to remember the user ID and password.
3. Run the setup again.
4. When the wizard asked for postgres credentials, put the username and password that created earlier.
5. Continue with installation.

Oracle JDBC not appear in Driver list in Jitterbit Connection Parameter.

1. Download Oracle Instant Client. Get ojdbc14.jar file.
2. Goto - C:\Program Files\Jitterbit Integration Server\tomcat\webapps\axis\WEB-INF\lib
3. Paste the ojdbc14.jar file here.
4. Goto - C:\Program Files\Jitterbit Integration Server
5. Open up JdbcDrivers.conf file.
6. Edit the file by putting the information as below.

<Driver>

<Name>Oracle</Name>

<Class>oracle.jdbc.OracleDriver</Class>

<Subprotocol>oracle:thin</Subprotocol>

</Driver>

7. Restart Jitterbit services in service.msc
8. Open up the driver list in Jitterbit again, click refresh.
9. You may see the driver is already listed.

Jitterbit Tomcat Server Won't Start

This issue usually happen when you installing the Jitterbit or Java program has been updated. You will realize this error when you fail to login to Jitterbit server although you have supplied correct password.

1. Goto Start-Run. Type services.msc and then hit Enter.

2. Find Jitterbit Tomcat Server. Click Start. If it won't start, try this.

• Goto - C:\Program Files\Java\jdk1.7.0\bin
• Copy msvcr100.dll file.
• Goto - C:\Program Files\Jitterbit Integration Server\tomcat\bin
• Paste it here.

3. Click Start Jitterbit Tomcat Server in services again.

4. If the service can be started, you should be able to login to the Jitterbit Integration Environment.