pramod.raghuwanshi
New User
Joined: 09 Apr 2013 Posts: 4 Location: India
|
|
|
|
Hi Nic
I am aware that its Z/OS and DB2 forum and my question is also related to Z/OS and DB2.
for more clarification on topic I'm putting my query below.
# PROPERTY.TAB contains parameters, local and remote db2 statements
#
# Parameters:
#
# driver : db2 driver for locale and remote databases
# decimal : number of decimals to round in moltiplication and division, for euro set decimal=2
# euro : euro value in lire
# timeout : number of milliseconds after that the operator stops
# attempts : number of times the operator tries to search a text on a host screen
# timesleep : number of milliseconds to slow the operator for attempt
# cleandaysdel: number of days, between current lts date and runtime date, after that entries with status=D are deleted
# cleandaysins: number of days, between current lts date and runtime date, after that entries with status=I are deleted
# backupdir : directory where the database is saved (backup option), this parameter has to be customize
# be careful to modify this parameter. It is the start point to delete file and directory (previous backups)
# localurl : local database url
# remoteurl : remote database url
#
driver=COM.ibm.db2.jdbc.app.DB2Driver
decimal=2
euro=1936.27
timeout=60000
attempts=10
timesleep=1000
cleandaysdel=120
cleandaysins=30
#backupdir=\\\\w3treasury.italy.ibm.com\\files\\t05backup\\user1
backupdir=c:\\applic\\java\\t05\\backup
workdir=c:\\applic\\java\\t05\\data
#
# Local DB2 Statements
#
localurl=jdbc:db2:t05
qlts=SELECT MAX(LTS_DATE), MAX(REFERENCE) FROM T05.VBANK_ENTRIES1 WHERE LTS_DATE = (SELECT MAX(LTS_DATE) FROM T05.VBANK_ENTRIES1)
qentries0=SELECT BANK_CODE,ENTRY_DATE,VALUE_DATE,AMOUNT,DESCRIPTION1,FINANCIAL_CODE,STATUS,LTS_OPTION,REFERENCE,LTS_DATE,RUNTIME_DATE,PARENT_KEY,KEY FROM T05.BANK_ENTRIES WHERE BANK_CODE IS NULL
qentries1=SELECT BANK_CODE,ENTRY_DATE,VALUE_DATE,AMOUNT,DESCRIPTION1,FINANCIAL_CODE,STATUS,LTS_OPTION,REFERENCE,LTS_DATE,RUNTIME_DATE,PARENT_KEY,KEY FROM T05.BANK_ENTRIES
qentries2=SELECT COUNT(*) FROM T05.VBANK_ENTRIES1 WHERE STATUS='U' AND AMOUNT<>0
qentries3=SELECT DESCRIPTION2 FROM T05.BANK_ENTRIES WHERE RUNTIME_DATE=? AND KEY=?
qentries4=SELECT DESCRIPTION FROM T05.ERRORS WHERE RUNTIME_DATE=? AND KEY=?
uentries0=UPDATE T05.BANK_ENTRIES SET LTS_OPTION=?, FINANCIAL_CODE=?, STATUS=? WHERE RUNTIME_DATE=? AND KEY=?
uentries1=INSERT INTO T05.BANK_ENTRIES (BANK_CODE,BANK_ACCOUNT,ENTRY_DATE,VALUE_DATE,AMOUNT,FOREIGN_AMOUNT,CURRENCY_CODE,DESCRIPTION1,DESCRIPTION2,RUNTIME_DATE) VALUES(?,?,?,?,?,?,?,?,?,?)
uentries2=UPDATE T05.BANK_ENTRIES SET PARENT_KEY=KEY WHERE PARENT_KEY=0
qbanks=SELECT DISTINCT BANK_CODE,BANK_NAME,BANK_ACCOUNT FROM T05.BANKS ORDER BY BANK_CODE
qbanks1=SELECT BANK_CODE,BANK_NAME,BANK_ACCOUNT,ABI,CAB,USERID,DESCRIPTION FROM T05.BANKS ORDER BY BANK_CODE, BANK_ACCOUNT
qbanks2=SELECT BANK_ACCOUNT,ABI,CAB,BANK_CODE,BANK_NAME,DESCRIPTION,USERID FROM T05.BANKS WHERE USERID=?
ubanks0=INSERT INTO T05.BANKS (BANK_CODE,BANK_NAME,BANK_ACCOUNT,ABI,CAB,USERID,DESCRIPTION) VALUES(?,?,?,?,?,?,?)
ubanks1=UPDATE T05.BANKS SET BANK_CODE=?,BANK_NAME=?,BANK_ACCOUNT=?,ABI=?,CAB=?,USERID=?,DESCRIPTION=? WHERE BANK_CODE=? AND BANK_ACCOUNT=?
ubanks2=DELETE FROM T05.BANKS WHERE BANK_CODE=? AND BANK_ACCOUNT=?
qstatus=SELECT STATUS FROM T05.BANK_ENTRIES GROUP BY STATUS
qconv=SELECT A.BANK_CODE, B.BANK_NAME,A.ENTRY_DATE,A.VALUE_DATE,A.AMOUNT,A.CURRENCY_CODE,A.EXCHANGE_RATE,A.FOREIGN_AMOUNT FROM T05.BANK_ENTRIES A INNER JOIN T05.BANKS B ON A.BANK_CODE=B.BANK_CODE AND A.BANK_ACCOUNT=B.BANK_ACCOUNT WHERE A.STATUS='' AND A.CURRENCY_CODE NOT IN ('EUR') AND A.RUNTIME_DATE=? AND A.KEY=?
uconv=UPDATE T05.BANK_ENTRIES SET EXCHANGE_RATE=?, AMOUNT=? WHERE RUNTIME_DATE=? AND KEY=?
qad10=SELECT A.BANK_CODE, A.BANK_NAME, B.ENTRY_DATE, B.VALUE_DATE, B.AMOUNT, B.CURRENCY_CODE, B.EXCHANGE_RATE, B.FOREIGN_AMOUNT, B.DESCRIPTION1, FINANCIAL_CODE FROM T05.BANKS A INNER JOIN T05.BANK_ENTRIES B ON A.BANK_CODE=B.BANK_CODE AND A.BANK_ACCOUNT=B.BANK_ACCOUNT WHERE B.RUNTIME_DATE=? AND B.KEY=?
qad20=SELECT A.BANK_CODE, A.BANK_NAME, B.AMOUNT, B.FOREIGN_AMOUNT, B.EXCHANGE_RATE FROM T05.BANKS A INNER JOIN T05.BANK_ENTRIES B ON A.BANK_CODE=B.BANK_CODE AND A.BANK_ACCOUNT=B.BANK_ACCOUNT WHERE B.RUNTIME_DATE=? AND B.KEY=?
qad21=SELECT GLOBAL_AMOUNT, EXPENSES, CLAIMED_EXPENSES, COMMISSIONS, EXCH_DIFFERENCE, FOREIGN_AMOUNT, CURRENCY_CODE, EXCHANGE_RATE, COMMITMENT FROM T05.COMMITMENTS WHERE RUNTIME_DATE=? AND KEY=?
qcommitm0=SELECT COMMITMENT, FINANCIAL_CODE, VALUE_DATE, GLOBAL_AMOUNT FROM T05.OPEN_COMMITMENTS WHERE BANK_CODE=? AND GLOBAL_AMOUNT=? AND COMMITMENT NOT IN (SELECT COMMITMENT FROM T05.COMMITMENTS WHERE GLOBAL_AMOUNT IS NOT NULL AND COMMITMENT IS NOT NULL)
qcommitm1=SELECT COMMITMENT, FINANCIAL_CODE, VALUE_DATE, GLOBAL_AMOUNT FROM T05.OPEN_COMMITMENTS WHERE COMMITMENT=?
qcommitm3=SELECT RUNTIME_DATE, KEY FROM T05.COMMITMENTS WHERE COMMITMENT=?
qcommitm4=SELECT COMMITMENT, FINANCIAL_CODE, VALUE_DATE, GLOBAL_AMOUNT FROM T05.OPEN_COMMITMENTS WHERE COMMITMENT NOT IN (SELECT COMMITMENT FROM T05.COMMITMENTS WHERE GLOBAL_AMOUNT IS NOT NULL AND COMMITMENT IS NOT NULL)
ucommitm5=DELETE FROM T05.COMMITMENTS WHERE RUNTIME_DATE=? AND KEY=?
ucommitm6=INSERT INTO T05.COMMITMENTS (RUNTIME_DATE,KEY,COMMITMENT,AMOUNT,GLOBAL_AMOUNT,EXPENSES,CLAIMED_EXPENSES,COMMISSIONS,EXCH_DIFFERENCE,FOREIGN_AMOUNT,CURRENCY_CODE,EXCHANGE_RATE) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)
qfincode=SELECT FINANCIAL_CODE, DESCRIPTION FROM T05.FINANCIAL_CODES WHERE LTS_OPTION=?
qfincode1=SELECT FINANCIAL_CODE, LTS_OPTION, DESCRIPTION, PRINT_GROUP FROM T05.FINANCIAL_CODES ORDER BY FINANCIAL_CODE
ufincode0=INSERT INTO T05.FINANCIAL_CODES (FINANCIAL_CODE, LTS_OPTION, DESCRIPTION, PRINT_GROUP) VALUES(?,?,?,?)
ufincode1=UPDATE T05.FINANCIAL_CODES SET FINANCIAL_CODE=?, LTS_OPTION=?, DESCRIPTION=?, PRINT_GROUP=? WHERE FINANCIAL_CODE=? AND LTS_OPTION=?
ufincode2=DELETE FROM T05.FINANCIAL_CODES WHERE FINANCIAL_CODE=? AND LTS_OPTION=?
qab0=SELECT A.BANK_CODE, A.BANK_NAME, B.ENTRY_DATE, B.VALUE_DATE, B.DESCRIPTION1, B.FINANCIAL_CODE, B.AMOUNT FROM T05.BANKS A INNER JOIN T05.BANK_ENTRIES B ON A.BANK_CODE=B.BANK_CODE AND A.BANK_ACCOUNT=B.BANK_ACCOUNT WHERE B.RUNTIME_DATE=? AND B.KEY=?
qab1=SELECT SUM(AMOUNT), COUNT(*) FROM T05.COLLECTIONS_SLIP WHERE RUNTIME_DATE=? AND KEY=?
qab2=SELECT DETAIL_AMOUNT, EXCHANGE_RATE, AMOUNT FROM T05.COLLECTIONS_SLIP WHERE RUNTIME_DATE=? AND KEY=?
ucollect0=DELETE FROM T05.COLLECTIONS_SLIP WHERE RUNTIME_DATE=? AND KEY=?
ucollect1=INSERT INTO T05.COLLECTIONS_SLIP (RUNTIME_DATE,KEY,DETAIL_AMOUNT,EXCHANGE_RATE,AMOUNT) VALUES(?,?,?,?,?)
qsplit0=SELECT A.BANK_CODE,A.BANK_NAME,B.ENTRY_DATE,B.VALUE_DATE,B.DESCRIPTION1,B.FINANCIAL_CODE,B.AMOUNT,B.CURRENCY_CODE,B.EXCHANGE_RATE,B.FOREIGN_AMOUNT,B.BANK_ACCOUNT FROM T05.BANKS A INNER JOIN T05.BANK_ENTRIES B ON A.BANK_CODE=B.BANK_CODE AND A.BANK_ACCOUNT=B.BANK_ACCOUNT WHERE B.RUNTIME_DATE=? AND B.KEY=?
usplit0=UPDATE T05.BANK_ENTRIES SET AMOUNT=?, FOREIGN_AMOUNT=? WHERE RUNTIME_DATE=? AND KEY=?
usplit1=INSERT INTO T05.BANK_ENTRIES (BANK_CODE,BANK_ACCOUNT,ENTRY_DATE,VALUE_DATE,AMOUNT,CURRENCY_CODE,EXCHANGE_RATE,FOREIGN_AMOUNT,DESCRIPTION1,LTS_OPTION,FINANCIAL_CODE,STATUS,RUNTIME_DATE,PARENT_KEY) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)
uocommit0=DELETE FROM T05.OPEN_COMMITMENTS
uocommit1=INSERT INTO T05.OPEN_COMMITMENTS VALUES(?,?,?,?,?,?,?,?,?,?,?,?)
qstart0=SELECT B.RUNTIME_DATE,B.KEY,B.LTS_OPTION,B.BANK_CODE,A.BANK_NAME,B.FINANCIAL_CODE,B.ENTRY_DATE,B.VALUE_DATE,B.AMOUNT,C.PRINT_GROUP FROM T05.BANKS A INNER JOIN T05.VBANK_ENTRIES1 B ON A.BANK_CODE=B.BANK_CODE AND A.BANK_ACCOUNT=B.BANK_ACCOUNT INNER JOIN T05.FINANCIAL_CODES C ON B.FINANCIAL_CODE=C.FINANCIAL_CODE AND B.LTS_OPTION=C.LTS_OPTION WHERE B.STATUS='U' AND B.AMOUNT<>0 ORDER BY C.PRINT_GROUP,B.BANK_CODE,B.FINANCIAL_CODE,B.AMOUNT
qstart1=SELECT A.RUNTIME_DATE,A.KEY,A.AMOUNT FROM T05.COLLECTIONS_SLIP A INNER JOIN T05.VBANK_ENTRIES1 B ON A.RUNTIME_DATE=B.RUNTIME_DATE AND A.KEY=B.KEY WHERE B.STATUS='U' ORDER BY RUNTIME_DATE,KEY
qstart2=SELECT A.RUNTIME_DATE,A.KEY,A.COMMITMENT,A.GLOBAL_AMOUNT,A.EXPENSES,A.EXCHANGE_RATE,A.CURRENCY_CODE,A.FOREIGN_AMOUNT,A.COMMISSIONS,A.CLAIMED_EXPENSES,A.EXCH_DIFFERENCE FROM T05.COMMITMENTS A INNER JOIN T05.VBANK_ENTRIES1 B ON A.RUNTIME_DATE=B.RUNTIME_DATE AND A.KEY=B.KEY WHERE B.STATUS='U' AND LTS_OPTION='AD'
ustart0=UPDATE T05.VBANK_ENTRIES1 SET STATUS=?,REFERENCE=?,LTS_DATE=?,LTS_TIME=? WHERE RUNTIME_DATE=? AND KEY=?
ustart1=INSERT INTO T05.ERRORS (RUNTIME_DATE,KEY,DESCRIPTION) VALUES(?,?,?)
ustart2=UPDATE T05.VBANK_ENTRIES1 SET STATUS='D' WHERE AMOUNT=0
qprint0=SELECT LTS_DATE,LTS_TIME,PRINT_GROUP,MIN(REFERENCE) AS REFERENCE,COUNT(*) AS PAGES FROM T05.VBANK_ENTRIES1 A JOIN T05.FINANCIAL_CODES B ON A.FINANCIAL_CODE=B.FINANCIAL_CODE AND A.LTS_OPTION=B.LTS_OPTION WHERE A.LTS_DATE IS NOT NULL GROUP BY LTS_DATE,LTS_TIME,PRINT_GROUP ORDER BY LTS_DATE DESC,PRINT_GROUP,REFERENCE DESC
qprint1=SELECT DISTINCT A.RUNTIME_DATE,A.KEY,A.REFERENCE,A.LTS_DATE,A.BANK_CODE,B.BANK_NAME,A.FINANCIAL_CODE,A.ENTRY_DATE,A.VALUE_DATE,A.AMOUNT,A.DESCRIPTION1,A.DESCRIPTION2,A.RUNTIME_DATE,D.COMMITMENT FROM T05.VBANK_ENTRIES1 A JOIN T05.BANKS B ON A.BANK_ACCOUNT=B.BANK_ACCOUNT AND A.BANK_CODE=B.BANK_CODE JOIN T05.FINANCIAL_CODES C ON A.FINANCIAL_CODE=C.FINANCIAL_CODE AND A.LTS_OPTION=C.LTS_OPTION LEFT OUTER JOIN T05.COMMITMENTS D ON A.RUNTIME_DATE=D.RUNTIME_DATE AND A.KEY=D.KEY WHERE A.LTS_DATE=? AND A.LTS_TIME=? AND C.PRINT_GROUP=? ORDER BY A.BANK_CODE,A.FINANCIAL_CODE,A.AMOUNT
qprint2=SELECT DISTINCT A.RUNTIME_DATE,A.KEY,A.REFERENCE,A.LTS_DATE,A.BANK_CODE,B.BANK_NAME,A.FINANCIAL_CODE,A.ENTRY_DATE,A.VALUE_DATE,A.AMOUNT,A.DESCRIPTION1,A.DESCRIPTION2,A.RUNTIME_DATE,D.COMMITMENT FROM T05.BANK_ENTRIES A JOIN T05.BANKS B ON A.BANK_ACCOUNT=B.BANK_ACCOUNT AND A.BANK_CODE=B.BANK_CODE LEFT OUTER JOIN T05.COMMITMENTS D ON A.RUNTIME_DATE=D.RUNTIME_DATE AND A.KEY=D.KEY WHERE A.RUNTIME_DATE=? AND A.KEY=?
qprint3=SELECT LTS_DATE,LTS_TIME,PRINT_GROUP,MIN(REFERENCE) AS REFERENCE,COUNT(*) AS PAGES FROM T05.VBANK_ENTRIES2 A JOIN T05.FINANCIAL_CODES B ON A.FINANCIAL_CODE=B.FINANCIAL_CODE AND A.LTS_OPTION=B.LTS_OPTION WHERE A.LTS_DATE IS NOT NULL GROUP BY LTS_DATE,LTS_TIME,PRINT_GROUP ORDER BY LTS_DATE DESC,PRINT_GROUP,REFERENCE DESC
qprint4=SELECT DISTINCT A.RUNTIME_DATE,A.KEY,A.REFERENCE,A.LTS_DATE,A.BANK_CODE,B.BANK_NAME,A.FINANCIAL_CODE,A.ENTRY_DATE,A.VALUE_DATE,A.AMOUNT,A.DESCRIPTION1,A.DESCRIPTION2,A.RUNTIME_DATE,D.COMMITMENT FROM T05.VBANK_ENTRIES2 A JOIN T05.BANKS B ON A.BANK_ACCOUNT=B.BANK_ACCOUNT AND A.BANK_CODE=B.BANK_CODE JOIN T05.FINANCIAL_CODES C ON A.FINANCIAL_CODE=C.FINANCIAL_CODE AND A.LTS_OPTION=C.LTS_OPTION LEFT OUTER JOIN T05.COMMITMENTS D ON A.RUNTIME_DATE=D.RUNTIME_DATE AND A.KEY=D.KEY WHERE A.LTS_DATE=? AND A.LTS_TIME=? AND C.PRINT_GROUP=? ORDER BY A.BANK_CODE,A.FINANCIAL_CODE,A.AMOUNT
qcreate0=SELECT COUNT(*) FROM T05.VBANK_ENTRIES2 WHERE STATUS='U'
qcreate1=SELECT DISTINCT A.CTRY_CD,A.LDGR_CD,A.DESCRIPTION,A.SOURCE,A.USER_ID,A.TOLI,A.ID,A.FILE_NAME FROM T05.COMPANIES A JOIN T05.BANK_ACCOUNTS B ON A.CTRY_CD=B.CTRY_CD AND A.LDGR_CD=B.LDGR_CD JOIN T05.VBANK_ENTRIES2 C ON B.BANK_CODE=C.BANK_CODE AND B.BANK_ACCOUNT=C.BANK_ACCOUNT WHERE C.STATUS='U'
qcreate2=SELECT MAX(REFERENCE)FROM T05.VBANK_ENTRIES2 WHERE SUBSTR(REFERENCE,1,1)=? AND YEAR(LTS_DATE)=? AND MONTH(LTS_DATE)=?
qcreate3=SELECT MIN(ID), MAX(ID) FROM T05.REFERENCES WHERE MONTH=?
qcreate5=SELECT A.BANK_CODE,A.BANK_ACCOUNT,B.ACC,B.SUB_MI,A.BANK_NAME FROM T05.BANKS A JOIN T05.BANK_ACCOUNTS B ON A.BANK_CODE=B.BANK_CODE AND A.BANK_ACCOUNT=B.BANK_ACCOUNT WHERE B.CTRY_CD=? AND B.LDGR_CD=? ORDER BY A.BANK_CODE,A.BANK_ACCOUNT
qcreate6=SELECT A.FINANCIAL_CODE,A.LTS_OPTION,B.ACC,B.SUB_MI FROM T05.FINANCIAL_CODES A JOIN T05.ACCOUNTS B ON A.FINANCIAL_CODE=B.FINANCIAL_CODE AND A.LTS_OPTION=B.LTS_OPTION WHERE B.CTRY_CD=? AND B.LDGR_CD=? ORDER BY A.FINANCIAL_CODE,A.LTS_OPTION
qcreate7=SELECT A.RUNTIME_DATE,A.KEY,A.BANK_CODE,RTRIM(A.BANK_ACCOUNT),A.ENTRY_DATE,A.VALUE_DATE,A.AMOUNT,A.FINANCIAL_CODE,A.LTS_OPTION,B.COMMITMENT FROM T05.VBANK_ENTRIES2 A LEFT OUTER JOIN T05.COMMITMENTS B ON A.RUNTIME_DATE=B.RUNTIME_DATE AND A.KEY=B.KEY JOIN T05.BANK_ACCOUNTS C ON A.BANK_CODE=C.BANK_CODE AND A.BANK_ACCOUNT=C.BANK_ACCOUNT WHERE C.CTRY_CD=? AND C.LDGR_CD=? AND STATUS='U' ORDER BY A.BANK_CODE,A.FINANCIAL_CODE,A.AMOUNT
ucreate1=UPDATE T05.VBANK_ENTRIES2 SET STATUS=?,REFERENCE=?,LTS_DATE=?,LTS_TIME=? WHERE RUNTIME_DATE=? AND KEY=?
ucreate2=UPDATE T05.VBANK_ENTRIES2 SET STATUS='D' WHERE AMOUNT=0
qref1=SELECT MONTH, ID FROM T05.REFERENCES ORDER BY MONTH, ID
uref1=INSERT INTO T05.REFERENCES (MONTH,ID) VALUES(?,?)
uref2=UPDATE T05.REFERENCES SET MONTH=?, ID=? WHERE MONTH=? AND ID=?
uref3=DELETE FROM T05.REFERENCES WHERE MONTH=? AND ID=?
qcompanies=SELECT CTRY_CD,LDGR_CD,DESCRIPTION,SOURCE,USER_ID,TOLI,ID,FILE_NAME FROM T05.COMPANIES ORDER BY CTRY_CD,LDGR_CD
qcompanies1=SELECT CTRY_CD,LDGR_CD FROM T05.COMPANIES ORDER BY CTRY_CD,LDGR_CD
ucompanies0=INSERT INTO T05.COMPANIES (CTRY_CD,LDGR_CD,DESCRIPTION,SOURCE,USER_ID,TOLI,ID,FILE_NAME) VALUES(?,?,?,?,?,?,?,?)
ucompanies1=UPDATE T05.COMPANIES SET CTRY_CD=?,LDGR_CD=?,DESCRIPTION=?,SOURCE=?,USER_ID=?,TOLI=?,ID=?,FILE_NAME=? WHERE CTRY_CD=? AND LDGR_CD=?
ucompanies2=DELETE FROM T05.COMPANIES WHERE CTRY_CD=? AND LDGR_CD=?
qbankaccounts1=SELECT BANK_CODE,BANK_ACCOUNT FROM T05.BANKS ORDER BY BANK_CODE, BANK_ACCOUNT
qbankaccounts2=SELECT CTRY_CD,LDGR_CD,ACC,SUB_MI,BANK_CODE,BANK_ACCOUNT FROM T05.BANK_ACCOUNTS ORDER BY CTRY_CD,LDGR_CD,BANK_CODE,BANK_ACCOUNT
ubankaccounts1=INSERT INTO T05.BANK_ACCOUNTS (CTRY_CD,LDGR_CD,ACC,SUB_MI,BANK_CODE,BANK_ACCOUNT) VALUES(?,?,?,?,?,?)
ubankaccounts2=UPDATE T05.BANK_ACCOUNTS SET CTRY_CD=?,LDGR_CD=?,ACC=?,SUB_MI=?,BANK_CODE=?,BANK_ACCOUNT=? WHERE CTRY_CD=? AND LDGR_CD=? AND BANK_CODE=? AND BANK_ACCOUNT=?
ubankaccounts3=DELETE FROM T05.BANK_ACCOUNTS WHERE CTRY_CD=? AND LDGR_CD=? AND BANK_CODE=? AND BANK_ACCOUNT=?
qaccounts1=SELECT FINANCIAL_CODE, LTS_OPTION FROM T05.FINANCIAL_CODES ORDER BY FINANCIAL_CODE, LTS_OPTION
qaccounts2=SELECT CTRY_CD, LDGR_CD, ACC, SUB_MI, FINANCIAL_CODE, LTS_OPTION FROM T05.ACCOUNTS ORDER BY CTRY_CD, LDGR_CD, FINANCIAL_CODE, LTS_OPTION
uaccounts1=INSERT INTO T05.ACCOUNTS (CTRY_CD, LDGR_CD, ACC, SUB_MI, FINANCIAL_CODE, LTS_OPTION) VALUES(?,?,?,?,?,?)
uaccounts2=UPDATE T05.ACCOUNTS SET CTRY_CD=?, LDGR_CD=?, ACC=?, SUB_MI=?, FINANCIAL_CODE=?, LTS_OPTION=? WHERE CTRY_CD=? AND LDGR_CD=? AND FINANCIAL_CODE=? AND LTS_OPTION=?
uaccounts3=DELETE FROM T05.ACCOUNTS WHERE CTRY_CD=? AND LDGR_CD=? AND FINANCIAL_CODE=? AND LTS_OPTION=?
qretrieve0=SELECT COUNT(*) FROM T05.VBANK_ENTRIES2 WHERE STATUS='I'
qretrieve1=SELECT DISTINCT A.CTRY_CD,A.LDGR_CD,A.DESCRIPTION,A.SOURCE,A.USER_ID,A.TOLI,A.ID,A.FILE_NAME FROM T05.COMPANIES A JOIN T05.BANK_ACCOUNTS B ON A.CTRY_CD=B.CTRY_CD AND A.LDGR_CD=B.LDGR_CD JOIN T05.VBANK_ENTRIES2 C ON B.BANK_CODE=C.BANK_CODE AND B.BANK_ACCOUNT=C.BANK_ACCOUNT WHERE C.STATUS='I'
qretrieve7=SELECT A.BANK_CODE,RTRIM(A.BANK_ACCOUNT),A.ENTRY_DATE,A.VALUE_DATE,A.AMOUNT,A.FINANCIAL_CODE,A.LTS_OPTION,B.COMMITMENT, A.REFERENCE FROM T05.VBANK_ENTRIES2 A LEFT OUTER JOIN T05.COMMITMENTS B ON A.RUNTIME_DATE=B.RUNTIME_DATE AND A.KEY=B.KEY JOIN T05.BANK_ACCOUNTS C ON A.BANK_CODE=C.BANK_CODE AND A.BANK_ACCOUNT=C.BANK_ACCOUNT WHERE C.CTRY_CD=? AND C.LDGR_CD=? AND STATUS='I' AND LTS_DATE=? ORDER BY A.REFERENCE
qretrieve8=SELECT A.BANK_CODE,RTRIM(A.BANK_ACCOUNT),A.ENTRY_DATE,A.VALUE_DATE,A.AMOUNT,A.FINANCIAL_CODE,A.LTS_OPTION,B.COMMITMENT, A.REFERENCE FROM T05.VBANK_ENTRIES2 A LEFT OUTER JOIN T05.COMMITMENTS B ON A.RUNTIME_DATE=B.RUNTIME_DATE AND A.KEY=B.KEY JOIN T05.BANK_ACCOUNTS C ON A.BANK_CODE=C.BANK_CODE AND A.BANK_ACCOUNT=C.BANK_ACCOUNT WHERE C.CTRY_CD=? AND C.LDGR_CD=? AND STATUS='I' AND LTS_DATE=? AND LTS_TIME=? ORDER BY A.REFERENCE
qload0=SELECT MAX(RUNTIME_DATE) FROM T05.BANK_ENTRIES
uload0=DELETE FROM T05.BANK_ENTRIES
#
# Host DB2 Statements
#
#remoteurl=jdbc:db2:tso1sna
remoteurl=jdbc:db2:tso1tcp
qmysaccs0=SELECT USERID FROM TSUBMI.MYS_ACCESS_CONTROL WHERE USERID^=''
umysaccs0=INSERT INTO TSUBMI.MYS_ACCESS_CONTROL VALUES(USER)
umysaccs1=DELETE FROM TSUBMI.MYS_ACCESS_CONTROL
umysbank0=DELETE FROM TSUBMI.MYS_BANKS WHERE USERID=USER
umysbank1=INSERT INTO TSUBMI.MYS_BANKS (BANK_ACCOUNT,ABI,CAB,BANK_CODE,BANK_NAME,DESCRIPTION,USERID) VALUES(?,?,?,?,?,?,?)
qmysentr0=SELECT BANK_ACCOUNT,ENTRY_DATE,VALUE_DATE,AMOUNT,FOREIGN_AMOUNT,CURRENCY_CODE,DESCRIPTION1,DESCRIPTION2,RUNTIME_DATE FROM TSUBMI.MYS_BANK_ENTRIES WHERE BANK_ACCOUNT IN (SELECT BANK_ACCOUNT FROM TSUBMI.MYS_BANKS WHERE USERID=USER) AND OWNER IS NULL FOR UPDATE OF OWNER
umysentr0=UPDATE TSUBMI.MYS_BANK_ENTRIES SET OWNER=USER WHERE CURRENT OF
qmyscomm0=SELECT DISTINCT COMMITMENT,A.BANK_CODE,FINANCIAL_CODE,ENTRY_DATE,VALUE_DATE,AMOUNT,GLOBAL_AMOUNT,EXPENSES,COMMISSIONS,FOREIGN_AMOUNT,CURRENCY_CODE,EXCHANGE_RATE FROM TSUBMI.MYS_BANKS A INNER JOIN TSUBMI.MYS_OPEN_COMMIT B ON A.BANK_CODE=B.BANK_CODE WHERE USERID=USER
qmyserro0=SELECT A.* FROM TSUBMI.MYS_BANK_ERRORS A INNER JOIN TSUBMI.MYS_BANKS B ON A.BANK_ACCOUNT=B.BANK_ACCOUNT WHERE USERID=USER |
|