private String getQueries(String queryName){
HashMap queries = new HashMap();
StringBuffer insertQuery = new StringBuffer("insert into Cb_VOUCHER_INFO(KEYNO,ACCOUNTNO,FYEAR,TRUSTTYPE");
insertQuery.append(" ,VOUCHERTYPE,PARTYTYPE,EMP_PARTY_CODE,PREPAREDBY,details,preperation_dt,ecpfacno,");
insertQuery.append(" eregion,pfidFlag,EMPLOYEENAME,TRANSACTIONTYPE) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?");
insertQuery.append(" ,?)");
queries.put("insertQuery",insertQuery);
StringBuffer insertDtQuery = new StringBuffer("insert into Cb_VOUCHER_DETAILS(KEYNO,ACCOUNTHEAD,MONTH_YEAR,");
insertDtQuery.append(" credit,Details,debit,chequeno) values (?,?,?,?,?,?,?)");
queries.put("insertDtQuery",insertDtQuery);
StringBuffer selectQuery = new StringBuffer("select VOUCHER_DT voucher_dt,to_char(preperation_dt,");
selectQuery.append(" 'dd/Mon/YYYY') preperation_dt,voucherNo,KEYNO,BANKNAME,FYEAR,Decode(VOUCHERTYPE");
selectQuery.append(" ,'P','Payment','R','Receipt','C','Contra','') VOUCHERTYPE,Decode(PARTYTYPE,'E',");
selectQuery.append(" 'Employee','P','Party','B','Bank','') PARTYTYPE,( select case when ");
selectQuery.append(" voucher.vouchertype='P' then sum(dt.debit-dt.credit)||' Dr.' else ");
selectQuery.append(" sum(dt.credit-dt.debit)||' Cr.' end from Cb_voucher_details dt where dt.keyno = ");
selectQuery.append(" voucher.keyno)Amount from Cb_VOUCHER_INFO voucher,Cb_BANK_INFO bank where bank.ACCOUNTNO");
selectQuery.append(" = voucher.ACCOUNTNO and upper(BANKNAME) like upper(?) and FYEAR like ? and");
selectQuery.append(" VOUCHERTYPE like ? and upper(voucher.ACCOUNTNO) like upper(?) ");
queries.put("selectQuery",selectQuery);
StringBuffer selectAppQuery = new StringBuffer("select to_char(VOUCHER_DT,'dd/Mon/YYYY') voucher_dt,");
selectAppQuery.append(" to_char(preperation_dt,'dd/Mon/YYYY') preperation_dt,voucherNo,KEYNO,BANKNAME,");
selectAppQuery.append(" FYEAR,Decode(VOUCHERTYPE,'P','Payment','R','Receipt','C','Contra','') VOUCHERTYPE");
selectAppQuery.append(" ,Decode(PARTYTYPE,'E','Employee','P','Party','B','Bank','') PARTYTYPE ,( select ");
selectAppQuery.append(" case when voucher.vouchertype='P' then sum(dt.debit-dt.credit)||' Dr.' else ");
selectAppQuery.append(" sum(dt.credit-dt.debit)||' Cr.' end from Cb_voucher_details dt where dt.keyno=");
selectAppQuery.append(" voucher.keyno)Amount from Cb_VOUCHER_INFO voucher,Cb_BANK_INFO bank where ");
selectAppQuery.append(" bank.ACCOUNTNO = voucher.ACCOUNTNO and upper(BANKNAME) like upper(trim(?))and");
selectAppQuery.append(" FYEAR like ? and upper(VOUCHERTYPE) like upper(?) and voucherno is null and ");
selectAppQuery.append(" upper(voucher.ACCOUNTNO) like upper(?) and upper(voucher.keyno) like upper(?) ");
selectAppQuery.append(" order by voucher.ENTEREDDT ");
queries.put("selectAppQuery",selectAppQuery);
StringBuffer reportQuery = new StringBuffer(" SELECT Nvl(TRANSACTIONTYPE,'C') TRANSACTIONTYPE,");
reportQuery.append(" Nvl(voucher.voucherNo,' ') voucherNo,voucher.accountNo accountNo,keyno, bankname,");
reportQuery.append(" fyear,DECODE (vouchertype,'P', 'Payment','R', 'Receipt','C', 'Contra',' ') ");
reportQuery.append(" vouchertype,Nvl(partytype,' ') partytype,DECODE ( voucher.trusttype,'I', ");
reportQuery.append(" 'IAAI ECPF','N', 'NAA ECPF','A', 'AAI EPF',' ') trusttype,(CASE WHEN partytype =");
reportQuery.append(" 'E' and voucher.PFIDFLAG='true' THEN ecpfacno WHEN partytype = 'B' THEN (SELECT ");
reportQuery.append(" bankname FROM Cb_bank_info WHERE accountno = emp_party_code) else emp_party_code END )");
reportQuery.append(" emp_party_code,(CASE WHEN partytype = 'E' and voucher.PFIDFLAG='true' THEN ");
reportQuery.append(" employeename WHEN partytype = 'E' THEN (SELECT employeename FROM ");
reportQuery.append(" employee_personal_info WHERE pensionno = emp_party_code) ELSE emp_party_code ");
reportQuery.append(" END ) partyDetails, Nvl(preparedby,' ') preparedby, Nvl(voucherno,' ') voucherno,");
reportQuery.append(" Nvl(checkedby,' ') checkedby, Nvl(approvedby,' ') approvedby,Nvl(details,' ') details");
reportQuery.append(" ,Nvl(to_char(VOUCHER_DT,'dd/Mon/YYYY'),' ') VOUCHER_DT,APPROVAL,to_char(preperation_dt");
reportQuery.append(" ,'dd/Mon/YYYY') preperation_dt FROM Cb_VOUCHER_INFO voucher, Cb_bank_info bank WHERE ");
reportQuery.append(" bank.accountno = voucher.accountno AND keyno = ? ");
queries.put("reportQuery",reportQuery);
StringBuffer reportDtQuery = new StringBuffer("SELECT voucher.ACCOUNTHEAD ACCOUNTHEAD,acc.PARTICULAR");
reportDtQuery.append(" PARTICULAR,MONTH_YEAR,Nvl(details,' ') details,Nvl(credit,0.0) credit,Nvl(debit,0.0)");
reportDtQuery.append(" debit,Nvl(chequeno,'0') chequeno FROM Cb_voucher_details voucher, Cb_ACCOUNTCODE_INFO acc");
reportDtQuery.append(" WHERE voucher.ACCOUNTHEAD = acc.ACCOUNTHEAD AND keyno = ? ");
queries.put("reportDtQuery",reportDtQuery);
StringBuffer updateQuery = new StringBuffer("update Cb_VOUCHER_INFO set VOUCHER_DT=?,CHECKEDBY=?,APPROVAL=?");
updateQuery.append(" ,APPROVEDBY=?,voucherno=? where KEYNO=?");
queries.put("updateQuery",updateQuery);
StringBuffer bankOpenBalQuery = new StringBuffer("select (case when Nvl((select amountType from ");
bankOpenBalQuery.append(" bankopeningbal_info where accountno = ? and openeddate <= ?),'CR') = 'DR' then");
bankOpenBalQuery.append(" (NVl((select amount from bankopeningbal_info where accountno = ? and openeddate ");
bankOpenBalQuery.append(" <= ?), 0) + NVL(ncredit, 0) - Nvl(ndebit, 0)) else (NVl((select amount from ");
bankOpenBalQuery.append(" bankopeningbal_info where accountno = ? and openeddate <= ?), 0) - NVL(ncredit,");
bankOpenBalQuery.append(" 0) + Nvl(ndebit, 0)) end) OpeningBanace, (select bankname from bank_info where");
bankOpenBalQuery.append(" accountno = ?) bankname, decode((select amountType from bankopeningbal_info");
bankOpenBalQuery.append(" where accountno = ? and openeddate <= ?), 'DR', 'Dr.', 'Cr.') amountType from");
bankOpenBalQuery.append(" (select sum(decode(?||'C',info.accountno||info.vouchertype,nvl(credit, 0), ");
bankOpenBalQuery.append(" nvl(debit, 0))) nDebit,sum(decode(?||'C',info.accountno||info.vouchertype,");
bankOpenBalQuery.append(" nvl(debit, 0), nvl(credit, 0))) nCredit from Cb_voucher_details dt,(select keyno,");
bankOpenBalQuery.append(" info.accountno, vouchertype, voucher_dt,emp_party_code from Cb_VOUCHER_INFO");
bankOpenBalQuery.append(" info,bankopeningbal_info openbal where (info.accountno = ? or ");
bankOpenBalQuery.append(" (info.emp_party_code = ? and info.vouchertype = 'C')) and voucher_dt is not");
bankOpenBalQuery.append(" null and info.voucher_dt < ? and info.accountno = openbal.accountno(+) and ");
bankOpenBalQuery.append("info.voucher_dt >= openbal.openeddate) info where dt.keyno = info.keyno)");
queries.put("bankOpenBalQuery",bankOpenBalQuery);
StringBuffer bankReconcileOpenBalQuery = new StringBuffer("select (case when Nvl((select amountType from ");
bankReconcileOpenBalQuery.append(" cb_bankopeningbal_info where accountno = ? and openeddate <= ?),'CR') = 'DR' then");
bankReconcileOpenBalQuery.append(" (NVl((select amount from cb_bankopeningbal_info where accountno = ? and openeddate ");
bankReconcileOpenBalQuery.append(" <= ?), 0) + NVL(ncredit, 0) - Nvl(ndebit, 0)) else (NVl((select amount from ");
bankReconcileOpenBalQuery.append(" cb_bankopeningbal_info where accountno = ? and openeddate <= ?), 0) - NVL(ncredit,");
bankReconcileOpenBalQuery.append(" 0) + Nvl(ndebit, 0)) end) OpeningBanace, (select bankname from CB_BANK_INFO where");
bankReconcileOpenBalQuery.append(" accountno = ?) bankname, decode((select amountType from cb_bankopeningbal_info");
bankReconcileOpenBalQuery.append(" where accountno = ? and openeddate <= ?), 'DR', 'Dr.', 'Cr.') amountType from");
bankReconcileOpenBalQuery.append(" (select sum(decode(?||'C',info.accountno||info.vouchertype,nvl(credit, 0), ");
bankReconcileOpenBalQuery.append(" nvl(debit, 0))) nDebit,sum(decode(?||'C',info.accountno||info.vouchertype,");
bankReconcileOpenBalQuery.append(" nvl(debit, 0), nvl(credit, 0))) nCredit from Cb_voucher_details dt,(select keyno,");
bankReconcileOpenBalQuery.append(" info.accountno, vouchertype, voucher_dt,emp_party_code from Cb_VOUCHER_INFO");
bankReconcileOpenBalQuery.append(" info,cb_bankopeningbal_info openbal where (info.accountno = ? or ");
bankReconcileOpenBalQuery.append(" (info.emp_party_code = ? and info.vouchertype = 'C')) and voucher_dt is not");
bankReconcileOpenBalQuery.append(" null and info.voucher_dt < ? and info.accountno = openbal.accountno(+) and ");
bankReconcileOpenBalQuery.append("info.voucher_dt >= openbal.openeddate) info where dt.keyno = info.keyno)");
queries.put("bankReconcileOpenBalQuery",bankReconcileOpenBalQuery);
StringBuffer bankBookQuery = new StringBuffer("SELECT info.keyno, dt.accounthead, InitCap(acc.particular)");
bankBookQuery.append(" particular, decode(?,emp_party_code,debit,credit) credit,decode(?,emp_party_code,");
bankBookQuery.append(" credit,debit) debit, InitCap(Nvl(info.DETAILS,' ')) details, vouchertype,");
bankBookQuery.append(" InitCap(partyname) partyname,info.voucherno,to_char(info.voucher_dt,'dd/Mon/YYYY')");
bankBookQuery.append(" voucher_dt,Nvl(chequeno,'0') chequeno FROM Cb_voucher_details dt,(SELECT DETAILS,");
bankBookQuery.append(" keyno, vouchertype,(CASE WHEN partytype = 'E' THEN (SELECT employeename FROM ");
bankBookQuery.append(" employee_personal_info WHERE pensionno = emp_party_code) WHEN vouchertype = 'C'");
bankBookQuery.append(" AND emp_party_code != ? THEN (SELECT bankname FROM bank_info WHERE accountno =");
bankBookQuery.append(" emp_party_code) WHEN partytype = 'B' THEN (SELECT bankname FROM bank_info WHERE ");
bankBookQuery.append(" accountno = info.accountno) ELSE emp_party_code END ) partyname, ? accountno,");
bankBookQuery.append(" voucherno,voucher_dt,emp_party_code,partytype FROM Cb_VOUCHER_INFO info WHERE ");
bankBookQuery.append(" APPROVAL='Y' and (accountno = ? OR (vouchertype = 'C' AND emp_party_code = ?))");
bankBookQuery.append(" and voucherno is not null and voucher_dt between ? and ?) info,accountcode_info");
bankBookQuery.append(" acc WHERE info.keyno = dt.keyno and acc.ACCOUNTHEAD = dt.accounthead order by ");
bankBookQuery.append(" info.voucher_dt,info.voucherno");
queries.put("bankBookQuery",bankBookQuery);
StringBuffer editQuery = new StringBuffer("select *,( select case when voucher.vouchertype='P' then ");
editQuery.append(" sum(dt.debit-dt.credit)||' Dr.' else sum(dt.credit-dt.debit)||' Cr.' end from ");
editQuery.append(" Cb_voucher_details dt where dt.keyno=voucher.keyno)Amount from Cb_VOUCHER_INFO voucher,");
editQuery.append(" BANK_INFO bank where bank.ACCOUNTNO = voucher.ACCOUNTNO and KEYNO=?");
queries.put("editQuery",editQuery);
StringBuffer updateVoucherQry = new StringBuffer("update Cb_VOUCHER_INFO set ACCOUNTNO=?,FYEAR=?,TRUSTTYPE=?");
updateVoucherQry.append(" ,PARTYTYPE=?,EMP_PARTY_CODE=?,PREPAREDBY=?,details=?,TRANSACTIONTYPE=? where KEYNO=?");
queries.put("updateVoucherQry",updateVoucherQry);
StringBuffer deleteDtQuery = new StringBuffer(" delete from Cb_voucher_details where KEYNO=?");
queries.put("deleteDtQuery",deleteDtQuery);
StringBuffer deleteVQuery = new StringBuffer("delete from Cb_VOUCHER_INFO where INSTR(upper(?),upper(KEYNO)) > 0");
queries.put("deleteVQuery",deleteVQuery);
StringBuffer deleteVDTQuery = new StringBuffer(" delete from Cb_voucher_details where INSTR(upper(?),upper(KEYNO)) > 0");
queries.put("deleteVDTQuery",deleteVDTQuery);
StringBuffer voucherNoQuery = new StringBuffer("SELECT voucher.vouchertype|| 'V/'|| vinfo.fyear||");
voucherNoQuery.append(" '/'|| SUBSTR (vinfo.accountno, LENGTH (vinfo.accountno) - 4, 5)|| '/'|| LPAD");
voucherNoQuery.append(" ( (SELECT CASE WHEN (? >= TO_DATE ('01/nov/2009', 'dd-mm-yy') AND hseries<500 )");
voucherNoQuery.append(" THEN 500 WHEN ? >= TO_DATE ('01/nov/2009', 'dd-mm-yy') THEN to_number(hseries)");
voucherNoQuery.append(" WHEN lseries < 500 THEN lseries END series from ( SELECT (SELECT MAX");
voucherNoQuery.append(" (TO_NUMBER (NVL (SUBSTR (voucherno,INSTR (voucherno, '/', -1) + 1 ),0))) no1");
voucherNoQuery.append(" FROM Cb_VOUCHER_INFO info ,(SELECT vouchertype FROM Cb_VOUCHER_INFO info where keyno");
voucherNoQuery.append(" = ?) vinfo WHERE voucherno IS NOT NULL and vinfo.vouchertype = info.vouchertype");
voucherNoQuery.append(" AND NVL (SUBSTR (voucherno, INSTR (voucherno, '/', -1) + 1), 0) < 500) lseries,");
voucherNoQuery.append(" (SELECT Nvl(MAX (NVL (SUBSTR (voucherno, INSTR (voucherno, '/', -1) + 1),0)),0)");
voucherNoQuery.append(" no1 FROM Cb_VOUCHER_INFO info ,(SELECT vouchertype FROM Cb_VOUCHER_INFO info where ");
voucherNoQuery.append(" keyno = ?) vinfo WHERE voucherno IS NOT NULL and vinfo.vouchertype = ");
voucherNoQuery.append(" info.vouchertype AND NVL (SUBSTR (voucherno, INSTR (voucherno, '/', -1) + 1), 0)");
voucherNoQuery.append(" >= 500) hseries FROM DUAL)) + 1, 4, 0) FROM (SELECT SUBSTR (fyear, 3) fyear,");
voucherNoQuery.append(" vouchertype, bankcode,info.accountno accountno FROM Cb_VOUCHER_INFO info, Cb_bank_info");
voucherNoQuery.append(" bank WHERE info.accountno = bank.accountno AND keyno = ?) vinfo, Cb_VOUCHER_INFO");
voucherNoQuery.append(" voucher WHERE voucher.vouchertype = vinfo.vouchertype AND UPPER (NVL ");
voucherNoQuery.append(" (voucher.voucherno, ' ')) != 'REJECTED' GROUP BY voucher.vouchertype, bankcode,");
voucherNoQuery.append(" vinfo.fyear, vinfo.accountno");
queries.put("voucherNoQuery",voucherNoQuery);
StringBuffer keyNoGenQuery = new StringBuffer("select to_char(to_date(?,'dd/mon/yyyy'),'ddmmyy')||");
keyNoGenQuery.append(" Lpad(Nvl(max(substr(KEYNO,7)),0)+1,6,'0') voucherNo from Cb_VOUCHER_INFO where ");
keyNoGenQuery.append(" to_char(to_date(?,'dd/mon/yyyy'),'ddmmyy')=substr(KEYNO,0,6)");
queries.put("keyNoGenQuery",keyNoGenQuery);
StringBuffer ledgerQuery = new StringBuffer("SELECT TO_CHAR (voucher_dt, 'dd/Mon/YYYY') vdate, ");
ledgerQuery.append(" info.accountno accno,bank.bankcode || ' ' || bank.bankname bank, info.details ");
ledgerQuery.append(" particulars,decode(vouchertype,'P','Payment','R','Receipt','Contra') vtype, ");
ledgerQuery.append(" voucherno NO, case when vouchertype='P' then SUM (NVL (dt.debit, 0))-SUM (NVL");
ledgerQuery.append(" (dt.credit, 0)) else 0 end dt,case when vouchertype='R' then SUM (NVL (dt.credit,");
ledgerQuery.append(" 0))-SUM (NVL (dt.debit, 0)) when vouchertype='C' then SUM (NVL (dt.credit, 0)) else");
ledgerQuery.append(" 0 end cr FROM Cb_VOUCHER_INFO info, Cb_voucher_details dt, Cb_bank_info bank WHERE voucherno");
ledgerQuery.append(" IS NOT NULL AND voucherno != 'REJECTED' AND bank.accountno = info.accountno AND");
ledgerQuery.append(" info.keyno = dt.keyno and voucher_dt between decode(?,'','01-JAN-1000',?) and ");
ledgerQuery.append(" decode(?,'','31-JAN-9999',?) and voucherType like ? AND UPPER (bank.bankcode)");
ledgerQuery.append(" LIKE UPPER (?) AND UPPER (bank.trusttype) LIKE UPPER (?) GROUP BY voucher_dt,");
ledgerQuery.append(" info.accountno,bank.bankcode,bank.bankname,info.details,vouchertype,voucherno ");
ledgerQuery.append(" ORDER BY voucher_dt, voucherno");
queries.put("ledgerQuery",ledgerQuery);
StringBuffer accNosQuery = new StringBuffer("SELECT accountno from Cb_bank_info where accountcode = ? ");
queries.put("accNosQuery",accNosQuery);
StringBuffer genLedgerQuery = new StringBuffer("Select decode(vinfo.TRUSTTYPE,'N','NAA ECPF','I',");
genLedgerQuery.append(" 'IAAI ECPF','A','AAI EPF') trusttype,vdt.accounthead || ' ' || ainfo.particular");
genLedgerQuery.append(" Accountcode,to_char(vinfo.voucher_dt, 'dd/Mon/YYYY') vdate,(binfo.accountcode ");
genLedgerQuery.append(" || ' ' || binfo.bankname) bank, trim(vinfo.details) details, decode(VOUCHERTYPE,");
genLedgerQuery.append(" 'R', 'Receipt', 'P', 'Payment', 'C', 'Contra') vtype, vinfo.voucherno,");
genLedgerQuery.append(" decode(vinfo.vouchertype, 'P', sum(vdt.debit) - sum(vdt.credit)) debit,");
genLedgerQuery.append(" decode(vinfo.vouchertype, 'R', sum(vdt.credit) - sum(vdt.debit)) credit,");
genLedgerQuery.append(" (select sum(credit) - sum(debit) from ((select info.accounthead ACCOUNTHEAD,");
genLedgerQuery.append(" TRUSTTYPE trusttype, Nvl(decode(amounttype, 'CR', amount, 'DR', 0), 0) credit, ");
genLedgerQuery.append(" Nvl(decode(amounttype, 'CR', 0, 'DR', amount), 0) debit from Cb_accountcode_info ");
genLedgerQuery.append(" info, Cb_accountcode_details dt where upper(trim(TRUSTTYPE)) like upper(trim(?))");
genLedgerQuery.append(" and info.accounthead = dt.accounthead and info.opendate <= decode(?, '',");
genLedgerQuery.append(" '01/Jan/1000', ?)) union all (select dt.accounthead ACCOUNTHEAD, info.trusttype");
genLedgerQuery.append(" trusttype, sum(credit) credit, sum(debit) debit from Cb_VOUCHER_INFO info,");
genLedgerQuery.append(" Cb_voucher_details dt, accountcode_info ainfo where dt.keyno = info.keyno and");
genLedgerQuery.append(" ainfo.accounthead = dt.accounthead and upper(trim(info.trusttype)) like ");
genLedgerQuery.append(" upper(trim(?)) and info.voucher_dt < decode(?, '', '01/Jan/1000', ?) and ");
genLedgerQuery.append(" info.voucher_dt >= decode(ainfo.opendate,'','01/jan/1000', ainfo.opendate) ");
genLedgerQuery.append(" group by dt.accounthead, info.trusttype, ainfo.particular)) where ACCOUNTHEAD ");
genLedgerQuery.append(" = vdt.accounthead and trusttype = vinfo.TRUSTTYPE ) openingBal,(CASE WHEN ");
genLedgerQuery.append(" partytype = 'E' and vinfo.PFIDFLAG='true' THEN EMPLOYEENAME WHEN partytype = ");
genLedgerQuery.append(" 'E' THEN (SELECT EMPLOYEENAME FROM employee_personal_info WHERE PENSIONNO = ");
genLedgerQuery.append(" emp_party_code) WHEN partytype = 'B' THEN (SELECT bankname FROM Cb_bank_info ");
genLedgerQuery.append(" WHERE accountno = emp_party_code) else emp_party_code END ) partyname,(select");
genLedgerQuery.append(" nvl(nvl(chequeno,'--') from Cb_voucher_details where keyno=''||vinfo.keyno||'' and rownum<2),'--') chequeNo from ");
genLedgerQuery.append(" Cb_VOUCHER_INFO vinfo, Cb_voucher_details vdt, Cb_bank_info binfo, Cb_accountcode_info ");
genLedgerQuery.append(" ainfo where ainfo.accounthead = vdt.accounthead and binfo.accountno = ");
genLedgerQuery.append(" vinfo.ACCOUNTNO and vdt.keyno = vinfo.keyno and vinfo.keyno in (select keyno");
genLedgerQuery.append(" from Cb_VOUCHER_INFO info where info.voucherno is not null) and upper(trim(");
genLedgerQuery.append(" vinfo.trustType)) like upper(trim(?)) and upper(trim(vdt.accounthead)) like");
genLedgerQuery.append(" upper(trim(?)) and upper(trim(vinfo.vouchertype)) like upper(trim(?)) and ");
genLedgerQuery.append(" vinfo.voucher_dt between decode(?, '', '01/Jan/1000', ?) and decode(?, '', ");
genLedgerQuery.append(" '01/Jan/3000', ?) group by vinfo.voucherno, vinfo.voucher_dt, binfo.accountcode,");
genLedgerQuery.append(" binfo.bankname, vinfo.details, vinfo.TRUSTTYPE, VOUCHERTYPE, vdt.accounthead,");
genLedgerQuery.append(" ainfo.particular,partytype,vinfo.PFIDFLAG,EMPLOYEENAME,emp_party_code,vinfo.keyno");
genLedgerQuery.append(" order by vdt.accounthead, vinfo.voucher_dt");
queries.put("genLedgerQuery",genLedgerQuery);
StringBuffer trailBalQuery = new StringBuffer("select decode(trusttype, 'N', 'NAA ECPF', 'I', 'IAAI ECPF'");
trailBalQuery.append(" , 'A', 'AAI EPF') trusttype,Decode(type,'','--','A','Asset','L','Liability','I',");
trailBalQuery.append(" 'Income','E','Expenditure','T','Inter Trust','U','Inter Unit','C','Current Asset'");
trailBalQuery.append(" ,'B','Current Liability','R','Inter Unit Remittance A/C') type, upper(acc) Accountcode,");
trailBalQuery.append(" sum(dr) openBaldebit, sum(cr) openBalcredit, sum(debit) debit, sum(credit) credit");
trailBalQuery.append(" from ((select ainfo.type type, dt.accounthead || ' ' || ainfo.particular acc,");
trailBalQuery.append(" info.trusttype trusttype, 0 cr, 0 dr, sum(credit) credit, sum(debit) debit from");
trailBalQuery.append(" Cb_VOUCHER_INFO info, Cb_voucher_details dt, Cb_accountcode_info ainfo where dt.keyno =");
trailBalQuery.append(" info.keyno and ainfo.accounthead = dt.accounthead and upper(trim(info.trusttype))");
trailBalQuery.append(" like upper(trim(?)) and info.voucher_dt between decode(?, '', '01/jan/1000',?)");
trailBalQuery.append(" and decode(?, '', '01/jan/3000', ?) group by dt.accounthead, info.trusttype,");
trailBalQuery.append(" ainfo.particular, ainfo.type) union all (select type, ACCOUNTHEAD acc,");
trailBalQuery.append(" trusttype trusttype, sum(credit),sum(debit), 0 credit, 0 debit from ((select");
trailBalQuery.append(" info.type type, info.accounthead || ' ' || info.particular ACCOUNTHEAD,");
trailBalQuery.append(" TRUSTTYPE trusttype, Nvl(decode(amounttype, 'CR', amount, 'DR', 0), 0) credit,");
trailBalQuery.append(" Nvl(decode(amounttype, 'CR', 0, 'DR', amount), 0) debit from Cb_accountcode_info");
trailBalQuery.append(" info, Cb_accountcode_details dt where upper(trim(TRUSTTYPE)) like upper(trim(?)) and");
trailBalQuery.append(" info.accounthead = dt.accounthead and info.opendate <= decode(?, '', '01/Jan/1000', ?))");
trailBalQuery.append(" union all (select ainfo.type type, dt.accounthead || ' ' || ainfo.particular ");
trailBalQuery.append(" ACCOUNTHEAD, info.trusttype trusttype, sum(credit) credit, sum(debit) debit ");
trailBalQuery.append(" from Cb_VOUCHER_INFO info,Cb_voucher_details dt,Cb_accountcode_info ainfo where dt.keyno");
trailBalQuery.append(" = info.keyno and ainfo.accounthead = dt.accounthead and upper(trim(info.trusttype))");
trailBalQuery.append(" like upper(trim(?)) and info.voucher_dt < decode(?, '', '01/Jan/1000',?) and ");
trailBalQuery.append(" info.voucher_dt >= decode(ainfo.opendate,'','01/jan/1000',ainfo.opendate) group");
trailBalQuery.append(" by dt.accounthead, info.trusttype,ainfo.particular, ainfo.type)) group by type,");
trailBalQuery.append(" ACCOUNTHEAD,trusttype )) group by trusttype, type, acc order by trusttype, type,");
trailBalQuery.append(" acc");
queries.put("trailBalQuery",trailBalQuery);
StringBuffer ePaymentsSearchQuery = new StringBuffer("select keyno,bankname,FYEAR,decode(vinfo.TRUSTTYPE,");
ePaymentsSearchQuery.append(" 'N','NAA ECPF','I','IAAI ECPF','A','AAI EPF') TRUSTTYPE,decode(PARTYTYPE,'P','Party'");
ePaymentsSearchQuery.append(" ,'E','Employee','B','Bank') PARTYTYPE,DETAILS,VOUCHERTYPE,VOUCHERNO,");
ePaymentsSearchQuery.append(" to_char(voucher_dt,'dd/Mon/YYYY') voucher_dt from Cb_VOUCHER_INFO vinfo,bank_info");
ePaymentsSearchQuery.append(" binfo where binfo.accountno = vinfo.accountno and VOUCHER_DT between decode(?,''");
ePaymentsSearchQuery.append(" ,'01/Jan/1000',?) and decode(?,'','01/Jan/3000',?) and VOUCHERTYPE = 'P' and APPROVAL='Y'");
queries.put("ePaymentsSearchQuery",ePaymentsSearchQuery);
StringBuffer ePaymentsQuery = new StringBuffer(" select * from Cb_VOUCHER_INFO where INSTR(upper(?),");
ePaymentsQuery.append(" upper(KEYNO)) > 0");
queries.put("ePaymentsQuery",ePaymentsQuery);
StringBuffer accountOpenBalQuery = new StringBuffer("select ainfo.ACCOUNTHEAD|| ' ' || ainfo.particular ACCOUNTHEAD ,");
accountOpenBalQuery.append(" decode(TRUSTTYPE,'N','NAA ECPF','I','IAAI ECPF','A','AAI EPF','AIN') TRUSTTYPE,");
accountOpenBalQuery.append(" Nvl((case when OPENDATE <= ? then decode(AMOUNTTYPE, 'CR', AMOUNT, -AMOUNT) ");
accountOpenBalQuery.append(" else 0 end) + Nvl((select sum(det.credit) - sum(det.debit) from Cb_voucher_details ");
accountOpenBalQuery.append(" det,Cb_VOUCHER_INFO info where info.keyno =det.keyno and info.voucher_dt > ? ");
accountOpenBalQuery.append(" and ainfo.accounthead = det.accounthead and details.trusttype = ");
accountOpenBalQuery.append(" info.trusttype),0),0) openbal,particular from accountcode_details details, ");
accountOpenBalQuery.append(" accountcode_info ainfo where ainfo.accounthead = details.accounthead(+) and ");
accountOpenBalQuery.append(" (trusttype ='' or trusttype like ?) and ainfo.ACCOUNTHEAD like ? order by ainfo.accounthead, trusttype ");
queries.put("accountOpenBalQuery",accountOpenBalQuery);
StringBuffer voucherQuery = new StringBuffer("select decode(VOUCHERTYPE,'R','Reciept','P','Payment','C', 'Contra') VOUCHERTYPE,");
voucherQuery.append(" ACCOUNTNO,info.KEYNO KEYNO,TRUSTTYPE,to_char(VOUCHER_DT,'dd/Mon/YYYY') VOUCHER_DT,");
voucherQuery.append(" VOUCHERNO,decode(VOUCHERTYPE, 'R', sum(credit)-sum(debit), 'P', 0,sum(credit)) credit, ");
voucherQuery.append(" decode(VOUCHERTYPE, 'P', sum(debit)-sum(credit), 'R', 0,sum(debit)) debit,(decode(VOUCHERTYPE, 'R', sum(credit)-sum(debit), 'P', 0,sum(credit))+ decode(VOUCHERTYPE, 'P', sum(debit)-sum(credit), 'R', 0,sum(debit))) voucheramt from cb_voucher_info info, cb_voucher_details details where VOUCHER_DT<=? and VOUCHER_DT>='01 Apr 2011' ");
voucherQuery.append(" and info.keyno = details.keyno and RECONCILE_STATUS='N' and ACCOUNTNO=? group by VOUCHER_DT,VOUCHERNO,VOUCHERTYPE, ACCOUNTNO,info.KEYNO,TRUSTTYPE order by to_date(VOUCHER_DT,'dd/mm/yyyy'),VOUCHERNO");
queries.put("voucherQuery",voucherQuery);
StringBuffer unReconcilevoucherQuery = new StringBuffer("select decode(VOUCHERTYPE,'R','Reciept','P','Payment','C', 'Contra') VOUCHERTYPE,");
unReconcilevoucherQuery.append(" decode(?,ACCOUNTNO,ACCOUNTNO,EMP_PARTY_CODE)ACCOUNTNO,info.KEYNO KEYNO,TRUSTTYPE,to_char(VOUCHER_DT,'dd/Mon/YYYY') VOUCHER_DT,");
unReconcilevoucherQuery.append(" VOUCHERNO,(case when (vouchertype='C') then (decode(?, emp_party_code, sum(debit), sum(credit))) else(decode(VOUCHERTYPE, 'R', sum(credit)-sum(debit), 'P', 0,sum(credit))) end) credit, ");
unReconcilevoucherQuery.append(" (case when (vouchertype='C') then decode(?, emp_party_code, sum(credit), sum(debit)) else (decode(VOUCHERTYPE, 'P', sum(debit)-sum(credit), 'R', 0,sum(debit))) end) debit,(decode(VOUCHERTYPE, 'R', sum(credit)-sum(debit), 'P', 0,sum(credit))+ decode(VOUCHERTYPE, 'P', sum(debit)-sum(credit), 'R', 0,sum(debit))) voucheramt from cb_voucher_info info, cb_voucher_details details where VOUCHER_DT<=? and VOUCHER_DT>='01 Apr 2011' ");
unReconcilevoucherQuery.append(" and info.keyno = details.keyno and (RECONCILE_STATUS='N' or(vouchertype='C' and (ACCOUNTFLAG='N' or EMPACCOUNTFLAG='N')) ) and (ACCOUNTNO=? or (vouchertype = 'C' AND emp_party_code =?)) group by VOUCHER_DT,VOUCHERNO,VOUCHERTYPE, ACCOUNTNO,info.KEYNO,TRUSTTYPE,emp_party_code order by to_date(VOUCHER_DT,'dd/mm/yyyy'),VOUCHERNO");
queries.put("unReconcilevoucherQuery",unReconcilevoucherQuery);
StringBuffer ReconcilevoucherQuery = new StringBuffer("select sum(credit)bankbookcredit,sum(debit)bankbookdebit from(select decode(VOUCHERTYPE,'R','Reciept','P','Payment','C', 'Contra') VOUCHERTYPE,");
ReconcilevoucherQuery.append(" ACCOUNTNO,info.KEYNO KEYNO,TRUSTTYPE,to_char(VOUCHER_DT,'dd/Mon/YYYY') VOUCHER_DT,");
ReconcilevoucherQuery.append(" VOUCHERNO,(case when (vouchertype='C') then (decode(?, emp_party_code, sum(debit), sum(credit))) else(decode(VOUCHERTYPE, 'R', sum(credit)-sum(debit), 'P', 0,sum(credit))) end) credit, ");
ReconcilevoucherQuery.append(" (case when (vouchertype='C') then decode(?, emp_party_code, sum(credit), sum(debit)) else (decode(VOUCHERTYPE, 'P', sum(debit)-sum(credit), 'R', 0,sum(debit))) end) debit,(decode(VOUCHERTYPE, 'R', sum(credit)-sum(debit), 'P', 0,sum(credit))+ decode(VOUCHERTYPE, 'P', sum(debit)-sum(credit), 'R', 0,sum(debit))) voucheramt from cb_voucher_info info, cb_voucher_details details where VOUCHER_DT<=? and VOUCHER_DT>='01 Apr 2011' ");
ReconcilevoucherQuery.append(" and info.keyno = details.keyno and (RECONCILE_STATUS='N' or(vouchertype='C' and (ACCOUNTFLAG='N' or EMPACCOUNTFLAG='N')) ) and (ACCOUNTNO=? or (vouchertype = 'C' AND emp_party_code =?)) group by VOUCHER_DT,VOUCHERNO,VOUCHERTYPE, ACCOUNTNO,info.KEYNO,TRUSTTYPE,emp_party_code order by to_date(VOUCHER_DT,'dd/mm/yyyy'),VOUCHERNO)");
queries.put("ReconcilevoucherQuery",ReconcilevoucherQuery);
StringBuffer reportQuery1 = new StringBuffer(" SELECT TRANSACTIONTYPE transtype,decode(TRANSACTIONTYPE,'C','Cheque','D','DD','I','Internal transfer','B',Decode(VOUCHERTYPE,'P','Direct Debit','Direct Credit'),'S','Cash') TRANSACTIONTYPE,");
reportQuery1.append(" Nvl(voucher.voucherNo,' ') voucherNo,voucher.accountNo accountNo,keyno, bankname,bank.accountcode bankaccountcode,nvl(OTHERMODULELINK,'N')OTHERMODULELINK,");
reportQuery1.append(" fyear,VOUCHERTYPE vtype,DECODE (vouchertype,'P', 'Payment','A','Adjustment','R', 'Receipt','C', 'Contra','N','Recovery Note',' ') ");
reportQuery1.append(" vouchertype,Nvl(partytype,' ') partytype,voucher.trusttype trusttype,");
reportQuery1.append(" (CASE WHEN partytype ='E' THEN ecpfacno WHEN partytype = 'B' THEN (SELECT bankname FROM CB_BANK_INFO WHERE accountno = emp_party_code) else emp_party_code END ) emp_party_code,emp_party_code partyDt,nvl(EMPNO,' ')EMPNO,(CASE WHEN partytype='E' and voucher.PFIDFLAG='true' THEN employeename WHEN partytype='E' THEN (SELECT employeename FROM episfin.employeeinfo WHERE EMPLNUMBER= voucher.EMPNO) when partytype='R' then (select employeename from employee_personal_info where medicalno=voucher.medicalno) ELSE emp_party_code end) partyDetails,(case when PARTYTYPE='E' then (SELECT designationdesc FROM episfin.employeeinfo employee,episfin.staffdesignation desig WHERE employee.DESIGNATIONCD=desig.DESIGNATIONCD and EMPLNUMBER= voucher.EMPNO) when PARTYTYPE='R' then (select DESEGNATION from employee_personal_info where MEDICALNO=voucher.medicalno) else '--' end)designation, ");
reportQuery1.append(" Nvl(USERNAME,' ') preparedby, Nvl(voucherno,' ') voucherno, (case when partytype='E' then (select BACNO from episfin.employeeinfo where EMPLNUMBER=voucher.EMPNO) when partytype='R' and voucher.TRUSTTYPE='MEDICAL' then (select ACCOUNTNO from employee_personal_info where MEDICALNO=voucher.MEDICALNO) else '--' end)empbankaccountno, ");
reportQuery1.append(" Nvl(checkedby,' ') checkedby, Nvl(approvedby,' ') approvedby,Nvl(details,' ') details");
reportQuery1.append(" ,Nvl(to_char(VOUCHER_DT,'dd/Mon/YYYY'),' ') VOUCHER_DT,APPROVAL,to_char(preperation_dt");
reportQuery1.append(" ,'dd/Mon/YYYY') preperation_dt,NVL(STATIONNAME,'')STATIONNAME,nvl(UNIT_NAME,' ')fullunitName,nvl(CANCEL_STATUS,'--')CANCEL_STATUS,nvl(to_char(CANCEL_VOUCHER_DATE,'dd/Mon/yyyy'),'--')CANCEL_VOUCHER_DATE,nvl(CANCEL_REMARKS,'--') CANCEL_REMARKS,nvl(TYPEOF_TRAVEL,'')TYPEOF_TRAVEL,nvl(PARTYNAME,' ')HOSPITAL_CODE,nvl(MEDICAL_TYPE,' ')MEDICAL_TYPE,nvl(LIMIT,' ')LIMIT,nvl(PAYABLE_PARTY,'E')PAYABLE_PARTY,decode('Y',(case when RELEASE_DATE is null and VOUCHERTYPE='P' then 'N' when RELEASE_DATE is not null and VOUCHERTYPE='P' then 'Y' end),'Paid',(case when RELEASE_DATE is null and VOUCHERTYPE='R' then 'N' when RELEASE_DATE is not null and VOUCHERTYPE='R' then 'Y' end),'Received', CANCEL_STATUS,'Canceled',APPROVAL,'Approved',(case when CHECKEDDT is null then 'N' else 'Y' end),'Checked','Fresh')voucherstatus,PREPERATION_NO preperationNO FROM Cb_VOUCHER_INFO voucher, CB_BANK_INFO bank,epis_user,epis_unitmaster unit,cb_party_info party WHERE USERID(+) = preparedby and voucher.STATIONNAME=unit.UNITCODE(+) and ");
reportQuery1.append(" bank.accountno = voucher.accountno and voucher.HOSPITAL_CODE=party.PARTYCODE(+) AND upper(trim(keyno)) = upper(trim(?)) ");
queries.put("reportQuery1",reportQuery1);
StringBuffer reportDtQuery1 = new StringBuffer("SELECT voucher.rowid,voucher.ACCOUNTHEAD ACCOUNTHEAD,acc.PARTICULAR");
reportDtQuery1.append(" PARTICULAR,MONTH_YEAR,Nvl(details,' ') details,Nvl(credit,0.0) credit,Nvl(debit,0.0)");
reportDtQuery1.append(" debit,(case when(debit>credit) then (debit-credit) else (credit-debit) end)amount,Nvl(chequeno,'0') chequeno,Nvl(Regard,' ') Regard,NVL(EMPACCOUNTNO,' ')EMPACCOUNTNO,'00000' ifsccode FROM Cb_voucher_details voucher, CB_ACCOUNTCODE_INFO acc");
reportDtQuery1.append(" WHERE voucher.ACCOUNTHEAD = acc.ACCOUNTHEAD AND upper(trim(keyno)) = upper(trim(?)) ");
queries.put("reportDtQuery1",reportDtQuery1);
StringBuffer retiremedicalQuery= new StringBuffer("select nvl(dt.amount,0)amount,nvl(personal.accountno,'--')EMPACCOUNTNO,nvl(personal.ifsccode, '000000')ifsccode from cb_medical_dt dt,cb_voucher_info info,employee_personal_info personal where info.keyno=dt.keyno and dt.medicalno=personal.medicalno and info.partytype='N' and info.keyno=?");
queries.put("retiremedicalQuery",retiremedicalQuery);
return queries.get(queryName).toString();
}
No comments:
Post a Comment