import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import com.epis.common.exception.EPISException;
import com.epis.common.exception.ServiceNotAvailableException;
import com.epis.utilities.Log;
public class DBUtility {
private static ResourceBundle bundle = null;
private static Log log = new Log(DBUtility.class);
static {
}
private static Connection getPooledConnection() throws EPISException {
Connection connection = null;
DataSource datasource = null;
try {
InitialContext initialContext = new InitialContext();
datasource = (DataSource) initialContext.lookup(bundle
.getString("jndi"));
if (datasource != null) {
connection = datasource.getConnection();
}
} catch (SQLException sqle) {
log.error(sqle.getMessage());
throw new EPISException(sqle);
} catch (Exception exp) {
log.error(exp.getMessage());
throw new EPISException(exp);
}
return connection;
}
private static Connection getDirectConnection() throws EPISException {
Connection connection = null;
try {
Class.forName(bundle.getString("driver"));
connection = DriverManager.getConnection(bundle.getString("url"),
bundle.getString("username"), bundle.getString("password"));
} catch (SQLException sqle) {
sqle.printStackTrace();
log.error("DBUtility:getDirectConnection:SQLException:"
+ sqle.getErrorCode());
throw new EPISException(sqle);
} catch (Exception exp) {
exp.printStackTrace();
log.error("DBUtility:getDirectConnection:Exception:"
+ exp.getMessage());
throw new EPISException(exp);
}
return connection;
}
public static Connection getConnection() throws EPISException {
Connection connection = null;
String error = "";
try {
log.info("the dept is"+ConnectionInfo.getInfo());
String department=ConnectionInfo.getInfo();
bundle = ResourceBundle.getBundle("com.epis.resource."+department);
} catch (Exception exp) {
log
.error("DBUtility:Exception:Unable to Load com.epis.resource.dbresource propertied file in classpath.");
log.error("DBUtility:Error:" + exp.getMessage());
}
if (bundle == null) {
log
.error("DBUtility:getConnection:Exception: Unable to Locate the DB configuration file with name com.epis.resouce.dbresource.properties ");
throw new EPISException(
"Unable to Locate the DB configuration file ");
}
try {
if ("DriverManager".equals(bundle.getString("connectiontype"))) {
connection = getDirectConnection();
} else if ("DataSource".equals(bundle.getString("connectiontype"))) {
connection = getPooledConnection();
} else {
error = "DB Configuration Error. Check the data base configuration files.";
}
if (!"".equals(error)) {
log.error(error);
throw new ServiceNotAvailableException(error);
}
if (connection == null) {
throw new ServiceNotAvailableException();
}
} catch (ServiceNotAvailableException sne) {
throw new EPISException(sne.getMessage());
} catch (Exception e) {
throw new EPISException(e);
}
return connection;
}
public static Connection getMultipleConnection(String dept) throws EPISException {
Connection connection = null;
String error = "";
try {
log.info("the dept is"+dept);
String department=dept;
bundle = ResourceBundle.getBundle("com.epis.resource."+department);
} catch (Exception exp) {
log
.error("DBUtility:Exception:Unable to Load com.epis.resource.dbresource propertied file in classpath.");
log.error("DBUtility:Error:" + exp.getMessage());
}
if (bundle == null) {
log
.error("DBUtility:getConnection:Exception: Unable to Locate the DB configuration file with name com.epis.resouce.dbresource.properties ");
throw new EPISException(
"Unable to Locate the DB configuration file ");
}
try {
if ("DriverManager".equals(bundle.getString("connectiontype"))) {
connection = getDirectConnection();
} else if ("DataSource".equals(bundle.getString("connectiontype"))) {
connection = getPooledConnection();
} else {
error = "DB Configuration Error. Check the data base configuration files.";
}
if (!"".equals(error)) {
log.error(error);
throw new ServiceNotAvailableException(error);
}
if (connection == null) {
throw new ServiceNotAvailableException();
}
} catch (ServiceNotAvailableException sne) {
throw new EPISException(sne.getMessage());
} catch (Exception e) {
throw new EPISException(e);
}
return connection;
}
public static void closeConnection(ResultSet resultSet,
Statement statement, Connection connection) {
try {
if (resultSet != null) {
resultSet.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException exp) {
log.error(exp.getMessage());
}
}
public static void closeConnection(ResultSet resultSet,
PreparedStatement pstatement, Connection connection) {
try {
if (resultSet != null) {
resultSet.close();
}
if (pstatement != null) {
pstatement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException exp) {
log
.error("DBUtility:closeConnection:Exception:"
+ exp.getMessage());
}
}
public static int executeUpdate(String updateQry) throws EPISException {
int updateCount = 0;
Connection connection = null;
Statement statement = null;
try {
connection = DBUtility.getConnection();
if (connection != null) {
statement = connection.createStatement();
if (statement != null) {
updateCount = statement.executeUpdate(updateQry);
}
}
} catch (SQLException sqlExp) {
log.error(sqlExp.getMessage());
throw new EPISException(sqlExp);
} catch (Exception exp) {
log.error(exp.getMessage());
throw new EPISException(exp);
} finally {
closeConnection(null, statement, connection);
}
return updateCount;
}
public static ResultSet getRecordSet(String selectQry, Statement statement)
throws EPISException {
ResultSet resultSet = null;
try {
if (statement != null) {
resultSet = statement.executeQuery(selectQry);
}
} catch (SQLException sqlExp) {
log.error(sqlExp.getMessage());
throw new EPISException(sqlExp);
} catch (Exception exp) {
log.error(exp.getMessage());
throw new EPISException(exp);
}
return resultSet;
}
public static int getRecordCount(String Qry) throws Exception {
ResultSet resultSet = null;
Connection connection = null;
Statement statement = null;
int count = 0;
try {
connection = DBUtility.getConnection();
if (connection != null) {
statement = connection.createStatement();
if (statement != null) {
resultSet = statement.executeQuery(Qry);
if (resultSet.next()) {
count = resultSet.getInt(1);
}
}
}
} catch (SQLException sqlExp) {
log.error(sqlExp.getMessage());
throw new EPISException(sqlExp);
} catch (Exception exp) {
log.error(exp.getMessage());
throw new EPISException(exp);
} finally {
closeConnection(null, statement, connection);
}
return (count);
}
public static void setAutoCommit(boolean autoCommit, Connection connection)
throws Exception, SQLException {
if (connection != null) {
connection.setAutoCommit(autoCommit);
}
}
public static void commitTrans(Connection connection) throws Exception,
SQLException {
if (connection != null) {
if (!connection.getAutoCommit()) {
connection.commit();
}
}
}
public static void rollbackTrans(Connection connection) throws Exception,
SQLException {
if (connection != null) {
if (!connection.getAutoCommit()) {
connection.rollback();
}
}
}
/* Added on 18-May-2010 By Suresh */
public static void closeConnection(Connection connection,
Statement statement, ResultSet resultSet) {
try {
if (resultSet != null) {
resultSet.close();
}
if (statement != null) {
statement.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException exp) {
log.error(exp.getMessage());
}
}
public static ResultSet prepExecuteQuery(String queyCode,String[] params,Connection con) throws EPISException{
PreparedStatement pst = null;
ResultSet rs = null;
try {
// Retrieve the Query from xml
QueryDetailsRetriver query = new QueryDetailsRetriver(queyCode);
// Creates a PreparedStatement object for sending parameterized SQL
// statements to the database.
pst = con.prepareStatement(query.getQuery());
// Setting the designated parameter to the given Java String value.
int paramLen = params.length;
for(int i=0;i< paramLen ; i++){
pst.setString(i+1, params[i]);
}
// Executes the SQL query
rs = pst.executeQuery();
}catch (SQLException e) {
log.error(e.getMessage());
throw new EPISException(e);
}catch (Exception e) {
log.error(e.getMessage());
throw new EPISException(e);
}
return rs;
}
public static int prepExecuteUpdate(String queyCode,String[] params) throws EPISException{
Connection con = null;
PreparedStatement pst = null;
int count = 0;
// Getting the Database Connection.
String deptarr[]={"COMMON","CHQFIN","MEDICAL","TA","PENSION"};
for(int l=0; l<deptarr.length; l++)
{
log.info("theconnection"+deptarr[l]);
try {
con = DBUtility.getMultipleConnection(deptarr[l]);
// Retrieve the Query from xml
QueryDetailsRetriver query = new QueryDetailsRetriver(queyCode);
// Creates a PreparedStatement object for sending parameterized SQL
// statements to the database.
//log.info("the query is..."+query.getQuery());
pst = con.prepareStatement(query.getQuery());
// Setting the designated parameter to the given Java String value.
int paramLen = params.length;
for(int i=0;i< paramLen ; i++){
pst.setString(i+1, params[i]);
//log.info(params[i]);
}
// Executes the SQL query
count = pst.executeUpdate();
}catch (SQLException e) {
log.error(e.getMessage());
throw new EPISException(e);
}catch (Exception e) {
log.error(e.getMessage());
throw new EPISException(e);
}finally{
closeConnection(con,pst,null);
}
}
return count;
}
public static void closeStatement(Statement statement){
try {
if(statement!=null){
statement.close();
}
} catch (SQLException exp) {
log.error(exp.getMessage());
}
/*try {
if(statement!=null){
statement.close();
}
} finally {
try {
} catch (Exception e) {
// TODO: handle exception
try {
} catch (Exception e1) {
// TODO: handle exception
}
finally {
}
}
}*/
}
}
Thursday, May 9, 2013
StringUtility.java
package com.epis.utilities;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import com.epis.bean.admin.Bean;
public class StringUtility {
public static String checknull(String string) {
if (string == null)
string = "";
return string.trim();
}
public static String checknull(String string,String append) {
if (string == null)
string = "" + append.trim();
return string.trim() + append.trim();
}
public static String checkFlag(String string) {
if (!"Y".equals(string)) {
string = "N";
}
return string;
}
public static String arrayToString(String[] stringArray, String appender) {
StringBuffer buffer = new StringBuffer();
int size = stringArray.length;
for (int arrCnt = 0; arrCnt < size; arrCnt++) {
buffer.append(stringArray[arrCnt]).append(appender);
}
return size > 0 ? buffer.toString().substring(0, buffer.length() - 1)
: "";
}
public static StringBuffer replace(char[] string, char[] replace,
String charletter) {
StringBuffer sb = new StringBuffer();
boolean found;
for (int i = 0; i < string.length; i++) {
if (string[i] == replace[0]) {
found = true;
for (int j = 0; j < replace.length; j++) {
if (!(string[i + j] == replace[j])) {
found = false;
break;
}
}
if (found) {
sb.append(charletter);
i = i + (replace.length - 1);
continue;
}
}
sb.append(string[i]);
}
return sb;
}
public static StringBuffer replaces(char[] string, char[] replace,
String charletter) {
StringBuffer sb = new StringBuffer();
for (int i = 0; i < string.length; i++) {
for (int k = 0; k < replace.length; k++) {
if (string[i] == replace[k]) {
sb.append(charletter);
if (i != (string.length - 1)) {
i++;
}
}
}
sb.append(string[i]);
}
return sb;
}
public static String[] getFinYears(String fromYear,String toYear){
int from = Integer.parseInt(fromYear);
int to = Integer.parseInt(toYear);
String[] years = new String[to-from];
for(int year=from,i=0 ;year<to;year++,i++){
years[i] = String.valueOf(year)+"-"+String.valueOf(year+1).substring(2,4);
}
return years;
}
public static String[] getFinYearsTill(String fromYear){
int toYear = Integer.parseInt((new SimpleDateFormat("yyyy")).format(new Date()));
if(Integer.parseInt((new SimpleDateFormat("MM")).format(new Date()))>3){
toYear ++;
}
return getFinYears(fromYear,String.valueOf(toYear));
}
public static String getDay(String date){
String day = null;
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
con = DBUtility.getConnection();
stmt = con.createStatement();
rs = stmt.executeQuery("select to_char(to_date('"+date+"','dd/mm/yyyy'),'Day') from dual");
if(rs.next()){
day = rs.getString(1);
}
} catch (Exception e) {
e.printStackTrace();
}finally{
DBUtility.closeConnection(con,stmt,rs);
}
return day;
}
public static List getMonths(){
List months = new ArrayList();
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
con = DBUtility.getConnection();
stmt = con.createStatement();
rs = stmt.executeQuery("select to_char(add_months(to_date('01/01/1000', 'DD/MM/RRRR'), rownum-1), 'MON') from tab where rownum<13");
while(rs.next()){
months.add(new Bean(rs.getString(1),rs.getString(1)));
}
} catch (Exception e) {
e.printStackTrace();
}finally{
DBUtility.closeConnection(con,stmt,rs);
}
return months;
}
public static String[] getYearsTill(String fromYear) {
int toYear = Integer.parseInt((new SimpleDateFormat("yyyy")).format(new Date()));
int from = Integer.parseInt(fromYear);
int to = toYear;
String[] years = new String[to-from+1];
for(int year=from,i=0 ;year<=to;year++,i++){
years[i] = String.valueOf(year);
}
return years;
}
public static List getFYearMonths(){
List months = new ArrayList();
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
con = DBUtility.getConnection();
stmt = con.createStatement();
rs = stmt.executeQuery("select to_char(add_months(to_date('01/01/1000', 'DD/MM/RRRR'), rownum+2), 'MON') from tab where rownum<13");
while(rs.next()){
months.add(new Bean(rs.getString(1),rs.getString(1)));
}
} catch (Exception e) {
e.printStackTrace();
}finally{
DBUtility.closeConnection(con,stmt,rs);
}
return months;
}
/*public String removeSpecialCharacters(String s) {
return s.replaceAll("\W", "");
}*/
public static String removeSpecialCharacters(String s) {
return s.replaceAll("[^a-zA-Z 0-9]","");
}
public static final String REG_EXPR = "[!\"#$%&'()*+-./:;<=>?@\\^_{|}~`,.\\[\\]]*";
public static String replaceSpecialChars(String string){
string = string.replaceAll(REG_EXPR, "");
while(string.indexOf(" ") != -1){
string = string.replaceAll(" ", "");
}
return string;
}
public static StringBuffer replaceAllSpecialCharacters(StringBuffer search) {
String[] arr = { "+", "-", "&&", "||", "!", "(", ")", "{", "}",
"[", "]", "^", "\"", "~", "*", "?", ":", "\\", "AND", "OR" };
String searchParam=StringUtility.checknull(search.toString());
StringBuffer sb=new StringBuffer("");
for (int i = 0; i < arr.length; i++) {
if (searchParam.contains(arr[i])) {
searchParam = searchParam.replace(arr[i], "\\" + arr[i]);
sb.append(searchParam);
}
}
return sb;
}
public static void main(String[] args) {
//replaceAllSpecialCharacters
/*StringBuffer sss=StringUtility.replaceAllSpecialCharacters(new StringBuffer("Air!()*@!||#$%&*&Bhurtan"));
System.out.println(" sss : "+sss.toString());
*/
String ss="Air!()*@!||#$%&*&Bhurtan";
/*String newName = ss.replaceAll("[^a-zA-Z 0-9]","");
System.out.println(" newName :"+newName);*/
/*String s1=StringUtility.removeSp(ss);
System.out.println(s1);*/
}
}
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import com.epis.bean.admin.Bean;
public class StringUtility {
public static String checknull(String string) {
if (string == null)
string = "";
return string.trim();
}
public static String checknull(String string,String append) {
if (string == null)
string = "" + append.trim();
return string.trim() + append.trim();
}
public static String checkFlag(String string) {
if (!"Y".equals(string)) {
string = "N";
}
return string;
}
public static String arrayToString(String[] stringArray, String appender) {
StringBuffer buffer = new StringBuffer();
int size = stringArray.length;
for (int arrCnt = 0; arrCnt < size; arrCnt++) {
buffer.append(stringArray[arrCnt]).append(appender);
}
return size > 0 ? buffer.toString().substring(0, buffer.length() - 1)
: "";
}
public static StringBuffer replace(char[] string, char[] replace,
String charletter) {
StringBuffer sb = new StringBuffer();
boolean found;
for (int i = 0; i < string.length; i++) {
if (string[i] == replace[0]) {
found = true;
for (int j = 0; j < replace.length; j++) {
if (!(string[i + j] == replace[j])) {
found = false;
break;
}
}
if (found) {
sb.append(charletter);
i = i + (replace.length - 1);
continue;
}
}
sb.append(string[i]);
}
return sb;
}
public static StringBuffer replaces(char[] string, char[] replace,
String charletter) {
StringBuffer sb = new StringBuffer();
for (int i = 0; i < string.length; i++) {
for (int k = 0; k < replace.length; k++) {
if (string[i] == replace[k]) {
sb.append(charletter);
if (i != (string.length - 1)) {
i++;
}
}
}
sb.append(string[i]);
}
return sb;
}
public static String[] getFinYears(String fromYear,String toYear){
int from = Integer.parseInt(fromYear);
int to = Integer.parseInt(toYear);
String[] years = new String[to-from];
for(int year=from,i=0 ;year<to;year++,i++){
years[i] = String.valueOf(year)+"-"+String.valueOf(year+1).substring(2,4);
}
return years;
}
public static String[] getFinYearsTill(String fromYear){
int toYear = Integer.parseInt((new SimpleDateFormat("yyyy")).format(new Date()));
if(Integer.parseInt((new SimpleDateFormat("MM")).format(new Date()))>3){
toYear ++;
}
return getFinYears(fromYear,String.valueOf(toYear));
}
public static String getDay(String date){
String day = null;
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
con = DBUtility.getConnection();
stmt = con.createStatement();
rs = stmt.executeQuery("select to_char(to_date('"+date+"','dd/mm/yyyy'),'Day') from dual");
if(rs.next()){
day = rs.getString(1);
}
} catch (Exception e) {
e.printStackTrace();
}finally{
DBUtility.closeConnection(con,stmt,rs);
}
return day;
}
public static List getMonths(){
List months = new ArrayList();
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
con = DBUtility.getConnection();
stmt = con.createStatement();
rs = stmt.executeQuery("select to_char(add_months(to_date('01/01/1000', 'DD/MM/RRRR'), rownum-1), 'MON') from tab where rownum<13");
while(rs.next()){
months.add(new Bean(rs.getString(1),rs.getString(1)));
}
} catch (Exception e) {
e.printStackTrace();
}finally{
DBUtility.closeConnection(con,stmt,rs);
}
return months;
}
public static String[] getYearsTill(String fromYear) {
int toYear = Integer.parseInt((new SimpleDateFormat("yyyy")).format(new Date()));
int from = Integer.parseInt(fromYear);
int to = toYear;
String[] years = new String[to-from+1];
for(int year=from,i=0 ;year<=to;year++,i++){
years[i] = String.valueOf(year);
}
return years;
}
public static List getFYearMonths(){
List months = new ArrayList();
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
con = DBUtility.getConnection();
stmt = con.createStatement();
rs = stmt.executeQuery("select to_char(add_months(to_date('01/01/1000', 'DD/MM/RRRR'), rownum+2), 'MON') from tab where rownum<13");
while(rs.next()){
months.add(new Bean(rs.getString(1),rs.getString(1)));
}
} catch (Exception e) {
e.printStackTrace();
}finally{
DBUtility.closeConnection(con,stmt,rs);
}
return months;
}
/*public String removeSpecialCharacters(String s) {
return s.replaceAll("\W", "");
}*/
public static String removeSpecialCharacters(String s) {
return s.replaceAll("[^a-zA-Z 0-9]","");
}
public static final String REG_EXPR = "[!\"#$%&'()*+-./:;<=>?@\\^_{|}~`,.\\[\\]]*";
public static String replaceSpecialChars(String string){
string = string.replaceAll(REG_EXPR, "");
while(string.indexOf(" ") != -1){
string = string.replaceAll(" ", "");
}
return string;
}
public static StringBuffer replaceAllSpecialCharacters(StringBuffer search) {
String[] arr = { "+", "-", "&&", "||", "!", "(", ")", "{", "}",
"[", "]", "^", "\"", "~", "*", "?", ":", "\\", "AND", "OR" };
String searchParam=StringUtility.checknull(search.toString());
StringBuffer sb=new StringBuffer("");
for (int i = 0; i < arr.length; i++) {
if (searchParam.contains(arr[i])) {
searchParam = searchParam.replace(arr[i], "\\" + arr[i]);
sb.append(searchParam);
}
}
return sb;
}
public static void main(String[] args) {
//replaceAllSpecialCharacters
/*StringBuffer sss=StringUtility.replaceAllSpecialCharacters(new StringBuffer("Air!()*@!||#$%&*&Bhurtan"));
System.out.println(" sss : "+sss.toString());
*/
String ss="Air!()*@!||#$%&*&Bhurtan";
/*String newName = ss.replaceAll("[^a-zA-Z 0-9]","");
System.out.println(" newName :"+newName);*/
/*String s1=StringUtility.removeSp(ss);
System.out.println(s1);*/
}
}
Tuesday, May 7, 2013
accommodation at tirumala
http://www.go2india.in/ap/accommodation.php /accommodation at tirumala
http://www.tirumala.org/acco_main.htm
http://www.tirumala.org/acco_tm_srivenk.htm
http://www.ttdsevaonline.com/
http://www.tirumala.org/acco_main.htm
http://www.tirumala.org/acco_tm_srivenk.htm
http://www.ttdsevaonline.com/
Monday, May 6, 2013
some points
Jayamangala.Satyanarayana@ust-global.com
Party Insert Scripts
----------------------------------------------------------------------------------------------------
INSERT INTO CB_PARTY_INFO(PARTYCODE,PARTYNAME,PARTYDETAIL) VALUES(GET_NEXTCODE('CB_PARTY_INFO',
INSERT INTO CB_PARTY_INFO(PARTYCODE,PARTYNAME,PARTYDETAIL) VALUES(GET_NEXTCODE('CB_PARTY_INFO','PARTYCODE',10),'NIT Kurukshetra','NIT Kurukshetra');
INSERT INTO CB_PARTY_BANK_DETAILS (PARTYCODE, BANKNAME, BRANCHNAME, BANKCODE, ADDRESS, PHONENO, BFAXNO, ACCOUNTNO, ACCOUNTTYPE, IFSCCODE, NEFT_RTGSCODE, MICRNO, CONTACTPERSON, BMOBILENO, ENTEREDBY, ENTEREDDT, UNITCODE) Values ('0000001186', 'NA', 'NA', 'NA', 'NA', ' ', ' ', 'NA', 'C', ' ', ' ', ' ', ' ', ' ', '', TO_DATE('08/29/2012 13:15:13', 'MM/DD/YYYY HH24:MI:SS'), '');
INSERT INTO CB_PARTY_INFO (PARTYNAME, PARTYDETAIL, FAXNO, EMAIL, MOBILENO, ENTEREDBY, ENTEREDDT, CONTACTNO, PARTYCODE, EDITEDBY, EDITEDDT, UNITCODE, MODULE_TYPE, INVEST_CASH_PARTYNAME, MAPPING_FLAG, GROUPING_FLAG, GROUP_NAME, GROUP_CODE, PAN_NUMBER, TAN_NUMBER, SERVICE_REGNO, WORK_CONTRACT_PERCENTAGE, TDS_PERCENTAGE, LABOURCESS, SCREEN_TYPE) Values ('KPIT CUMMINS INFOSYSTEMS LTD', '', '', '', '','7', TO_DATE('08/28/2012', 'MM/DD/YYYY'), '', '0000001186', '',TO_DATE('08/28/2012', 'MM/DD/YYYY'), 'VIDP', 'C', '', 'N','N', '', '', 'NA', '', '', '', '', '', 'P');
DELETE FROM CB_PARTY_BANK_DETAILS WHERE PARTYCODE='0000001180';
DELETE FROM CB_PARTY_INFO WHERE PARTYCODE='0000001180';
Deleting a VOUCHER
----------------------------------------------------------------------------------------------------
DELETE FROM CB_VOUCHER_DETAILS WHERE KEYNO='060812000026';
DELETE FROM CB_VOUCHER_INFO WHERE KEYNO='060812000026';
if MEDICAL Retired Bulk
--------
DELETE FROM CB_VOUCHER_DETAILS WHERE KEYNO='090412000017';
DELETE FROM CB_MEDICAL_DT WHERE KEYNO='090412000017';
DELETE FROM CB_VOUCHER_INFO WHERE KEYNO='090412000017';
DELETE FROM CB_VOUCHER_DETAILS WHERE KEYNO=(select keyno from cb_voucher_info where voucherno='CV/HD31/0019');
DELETE FROM CB_MEDICAL_DT WHERE keyno=(select keyno from cb_voucher_info where voucherno='CV/HD31/0019');
DELETE FROM CB_VOUCHER_INFO WHERE voucherno='CV/HD31/0019';
DELETE FROM CB_VOUCHER_DETAILS WHERE KEYNO=(select keyno from cb_voucher_info where PREPERATION_NO='RST39/3002/12-13');
DELETE FROM CB_MEDICAL_DT WHERE keyno=(select keyno from cb_voucher_info where PREPERATION_NO='RST39/3002/12-13');
DELETE FROM CB_VOUCHER_INFO WHERE PREPERATION_NO='RST39/3002/12-13';
DELETE FROM CB_VOUCHER_DETAILS WHERE KEYNO=(select keyno from cb_voucher_info where PREPERATION_NO='PST39/3579/12-13');
DELETE FROM CB_MEDICAL_DT WHERE keyno=(select keyno from cb_voucher_info where PREPERATION_NO='PSY18/3715/12-13');
DELETE FROM CB_VOUCHER_INFO WHERE PREPERATION_NO='PSY18/3715/12-13';
DELETE FROM CB_JOURNALVOUCHER_DETAILS WHERE KEYNO='060912000001';
DELETE FROM CB_JOURNALVOUCHER WHERE KEYNO='060912000001';
DELETE FROM CB_JOURNALVOUCHER_DETAILS WHERE KEYNO in(select keyno from CB_JOURNALVOUCHER where PREPERATION_NO='JTA/3006/12-13');
DELETE FROM CB_JOURNALVOUCHER WHERE PREPERATION_NO='JTA/3006/12-13';
DELETE FROM CB_JOURNALVOUCHER_DETAILS WHERE KEYNO ='011012000001';
DELETE FROM CB_JOURNALVOUCHER WHERE keyno='011012000001';
DELETE FROM CB_VOUCHER_DETAILS WHERE KEYNO=(select keyno from CB_VOUCHER_INFO where
PREPERATION_NO='PHD31/3090/12-13');
DELETE FROM CB_VOUCHER_INFO WHERE PREPERATION_NO='PHD31/3090/12-13';
ADJUSTMENT VOUCHER DELETION
----------------------------------------------------------------------------------------------------
DELETE FROM CB_VOUCHER_DETAILS WHERE KEYNO IN(SELECT KEYNO FROM CB_VOUCHER_INFO WHERE INSTR(UPPER('110912000001'),UPPER(MAPPINGKEYNO)) > 0);
DELETE FROM CB_VOUCHER_INFO WHERE KEYNO IN(SELECT KEYNO FROM CB_VOUCHER_INFO WHERE INSTR(UPPER('110912000001'),UPPER(MAPPINGKEYNO)) > 0);
DELETE FROM CB_JOURNALVOUCHER_DETAILS WHERE KEYNO IN(SELECT KEYNO FROM CB_JOURNALVOUCHER WHERE INSTR(UPPER('110912000001'),UPPER(MAPPINGKEYNO)) > 0);
DELETE FROM CB_JOURNALVOUCHER WHERE KEYNO IN(SELECT KEYNO FROM CB_JOURNALVOUCHER WHERE INSTR(UPPER('110912000001'),UPPER(MAPPINGKEYNO)) > 0);
DELETE FROM CB_AD_VOUCHER_DETAILS WHERE INSTR(UPPER('110912000001'),UPPER(KEYNO)) > 0;
DELETE FROM CB_AD_VOUCHER_INFO WHERE INSTR(UPPER('110912000001'),UPPER(KEYNO)) > 0;
FOR CHANGING AMOUNT
----------------------------------------------------------------------------------------------------
UPDATE CB_VOUCHER_DETAILS SET DEBIT='3064.0000' WHERE KEYNO='100912000021';
UPDATE CB_VOUCHER_DETAILS SET DEBIT='2897.0000' WHERE KEYNO='080812000005' AND DEBIT='3494.0000';
UPDATE CB_VOUCHER_DETAILS SET ACCOUNTHEAD='628.01',DETAILS=' ',CREDIT='5600.0000' WHERE DEBIT='500.0000' AND KEYNO='080812000009';
UPDATE CB_VOUCHER_DETAILS SET DEBIT='2750.0000' WHERE KEYNO='031012000056';
UPDATE CB_VOUCHER_DETAILS SET DEBIT='1360.0000' WHERE KEYNO='250912000002';
UPDATE CB_VOUCHER_DETAILS SET DEBIT='12000.0000' WHERE KEYNO='081012000072';
UPDATE CB_VOUCHER_INFO SET DETAILS='APRIL TO SEPTEMBER' WHERE KEYNO='081012000072';
UPDATE CB_VOUCHER_DETAILS SET DEBIT='27930.0000' WHERE KEYNO='191012000030' AND ACCOUNTHEAD='732.07';
UPDATE CB_VOUCHER_DETAILS SET CREDIT='3730.0000' WHERE KEYNO='191012000030' AND ACCOUNTHEAD='334.06';
UPDATE CB_VOUCHER_DETAILS SET DEBIT='6500.0000' WHERE KEYNO='081012000072';
UPDATE CB_VOUCHER_DETAILS SET DEBIT='6250.0000' WHERE KEYNO='081012000043';
UPDATE CB_VOUCHER_DETAILS SET DEBIT='6500.0000' WHERE KEYNO='081012000037';
UPDATE CB_VOUCHER_DETAILS SET DEBIT='31549.0000' WHERE KEYNO IN(SELECT KEYNO FROM CB_VOUCHER_INFO WHERE PREPERATION_NO='PST39/3810/12-13') AND ACCOUNTHEAD='732.01';
For Narration Updating
----------------------------------------------------------------------------------------------------
update cb_voucher_info set DETAILS='QUARTLY MEDICAL PAYMENT FOR THE PERIOD APR TO JUN 2012' where keyno='031012000062';
update cb_voucher_info set DETAILS='QUARTLY MEDICAL PAYMENT FOR THE PERIOD APRIL TO JUNE 2012' where keyno='031012000062';
For Inserting New Records Into A New Detail Record using keyno
----------------------------------------------------------------------------------------------------
insert into CB_VOUCHER_DETAILS (KEYNO, ACCOUNTHEAD, MONTH_YEAR, DETAILS, ENTEREDBY, ENTEREDDT, CREDIT, DEBIT, CHEQUENO, UNITCODE, REGARD, ACC_KEYNO, AMOUNTTYPE_CD, EMPNO, CHRONICEMPLOYEENAME, EMPBANKNAME, EMPACCOUNTNO)values ('240712000002', '634.04', '', '', '15', to_date('31-08-2012 15:52:48', 'dd-mm-yyyy hh24:mi:ss'), 0, 120389, '', 'VIDP', '', '', '', '', '', '', '');
insert into CB_VOUCHER_DETAILS (KEYNO, ACCOUNTHEAD, MONTH_YEAR, DETAILS, ENTEREDBY, ENTEREDDT, CREDIT, DEBIT, CHEQUENO, UNITCODE, REGARD, ACC_KEYNO, AMOUNTTYPE_CD, EMPNO, CHRONICEMPLOYEENAME, EMPBANKNAME, EMPACCOUNTNO)values ('240712000002', '633.01', '', '', '15', to_date('31-08-2012 15:52:48', 'dd-mm-yyyy hh24:mi:ss'), 0, 778333, '', 'VIDP', '', '', '', '', '', '', '');
FOR UNAPPROVE
----------------------------------------------------------------------------------------------------
UPDATE CB_VOUCHER_INFO SET APPROVAL='N',APPROVEDDDT='',APPROVALBY='',APPROVEDBY='',VOUCHERNO='' ,RELEASE_DATE='',VOUCHER_DT='' WHERE KEYNO='030912000001';
UPDATE CB_VOUCHER_INFO SET APPROVAL='N',APPROVEDDDT='',APPROVALBY='',APPROVEDBY='',VOUCHERNO='' ,RELEASE_DATE='',VOUCHER_DT='' WHERE KEYNO='020412000026';
UPDATE CB_VOUCHER_INFO SET APPROVAL='N',APPROVEDDDT='',APPROVALBY='',APPROVEDBY='',VOUCHERNO='' ,RELEASE_DATE='',VOUCHER_DT='' WHERE KEYNO='020412000027';
UPDATE CB_VOUCHER_INFO SET APPROVAL='N',APPROVEDDDT='',APPROVALBY='',APPROVEDBY='',VOUCHERNO='',RELEASE_DATE='',VOUCHER_DT='' WHERE KEYNO in(select KEYNO from CB_VOUCHER_INFO where PREPERATION_NO='PST39/3080/12-13');
UPDATE CB_VOUCHER_INFO SET APPROVAL='N',APPROVEDDDT='',APPROVALBY='',APPROVEDBY='',VOUCHERNO='',RELEASE_DATE='',VOUCHER_DT=''
WHERE KEYNO in(select KEYNO from CB_VOUCHER_INFO where PREPERATION_NO='PST39/3081/12-13');
select accountno,PREPERATION_DT,release_date from cb_voucher_info a where a.release_date is not null and accountno='049010200029847' and PREPERATION_DT between '01/May/2012' and '31/May/2012';
UPDATE cb_voucher_info SET release_date=PREPERATION_DT a where a.release_date is not null and accountno='049010200029847' and PREPERATION_DT between '01/May/2012' and '31/May/2012';
----------FOR Journal Voucher Un Approve------------
update CB_JOURNALVOUCHER jv set VOUCHERNO='',VOUCHER_DT='',APPROVAL='N',APPROVEDBY='',APPROVEDDDT='' where keyno='010213000001';
----------FOR DISAPPROVE Journal Voucher AND Generate New Journal Voucher Number ----------
UPDATE CB_JOURNALVOUCHER jv set VOUCHERNO='',VOUCHER_DT='',APPROVAL='N',APPROVEDBY='',APPROVEDDDT='' where keyno='240113000003';
UPDATE CB_JOURNALVOUCHER jv set VOUCHERNO='',VOUCHER_DT='',APPROVAL='N',APPROVEDBY='',APPROVEDDDT='' where keyno='301112000011';
UPDATE CB_JOURNALVOUCHER jv set VOUCHERNO=(SELECT 'JV/CHQ/'||DECODE(TRUSTTYPE,'PreCheck','PC','MEDICAL','MED','TA','TA','PENSION','PEN','COMMON','COMP')||'/'|| LPAD((SELECT nvl(MAX(SUBSTR(voucherno,INSTR(voucherno, '/', -1)+1)),3000) + 1 FROM Cb_Journalvoucher vinfo where vinfo.finyear =info.finyear and vinfo.PREPERATION_DT >= TO_DATE('01/Aug/2012')),4,0) voucherno FROM Cb_Journalvoucher info WHERE upper(keyno)= upper('240113000003')),VOUCHER_DT='31/Dec/2012',APPROVAL='Y',APPROVEDBY='3',APPROVEDDDT='31/Dec/2012' where keyno='240113000003';
UPDATE CB_JOURNALVOUCHER jv set VOUCHERNO=(SELECT 'JV/CHQ/'||DECODE(TRUSTTYPE,'PreCheck','PC','MEDICAL','MED','TA','TA','PENSION','PEN','COMMON','COMP')||'/'|| LPAD((SELECT nvl(MAX(SUBSTR(voucherno,INSTR(voucherno, '/', -1)+1)),3000) + 1 FROM Cb_Journalvoucher vinfo where vinfo.finyear =info.finyear and vinfo.PREPERATION_DT >= TO_DATE('01/Aug/2012')),4,0) voucherno FROM Cb_Journalvoucher info WHERE upper(keyno)= upper('301112000011')),VOUCHER_DT='30/Nov/2012',APPROVAL='Y',APPROVEDBY='3',APPROVEDDDT='30/Nov/2012' where keyno='301112000011';
-----ADJUSTMENT voucher UNAPPROVE -------------
UPDATE cb_ad_voucher_info SET VOUCHERNO='',VOUCHER_DT='',APPROVEDBY='',APPROVAL='N' WHERE keyno='190912000001';
---------------AND--------
UPDATE CB_AD_VOUCHER_INFO SET VOUCHER_DT='', APPROVAL='N', APPROVEDBY='', VOUCHERNO='' WHERE KEYNO='190912000001';
UPDATE CB_VOUCHER_INFO SET VOUCHER_DT='', APPROVAL='N', APPROVEDBY='', VOUCHERNO='' WHERE MAPPINGKEYNO ='190912000001';
UPDATE CB_JOURNALVOUCHER SET VOUCHER_DT='', APPROVAL='N', APPROVEDBY='', VOUCHERNO='' WHERE MAPPINGKEYNO ='190912000001';
Updating the Release Dates
----------------------------------------------------------------------------------------------------
UPDATE CB_VOUCHER_INFO SET RELEASE_DATE='05/May/2012', VOUCHER_DT='05/May/2012' WHERE KEYNO='230412000009';
UPDATE CB_VOUCHER_INFO SET RELEASE_DATE='05/Jun/2012', VOUCHER_DT='05/Jun/2012' WHERE KEYNO='160512000020';
UPDATE CB_VOUCHER_INFO SET RELEASE_DATE='24/Jul/2012', VOUCHER_DT='24/Jul/2012' WHERE KEYNO='240712000003';
UPDATE CB_VOUCHER_INFO SET RELEASE_DATE=PREPERATION_DT, VOUCHER_DT=PREPERATION_DT WHERE KEYNO='150512000009';
UPDATE CB_VOUCHER_INFO SET RELEASE_DATE=PREPERATION_DT, VOUCHER_DT=PREPERATION_DT WHERE KEYNO='160512000024';
SCRIPTS for Party Master
----------------------------------------------------------------------------------------------------
conn epis/epis@pension;
select partycode from cb_party_info where partyname='PPPP';
conn pendept/pendept@pension;
select partycode from cb_party_info where partyname='PPPP';
conn ta/ta@pension;
select partycode from cb_party_info where partyname='PPPP';
conn common/common@pension;
select partycode from cb_party_info where partyname='PPPP';
conn epis/epis@pension;
select count(*) from cb_party_info;
conn pendept/pendept@pension;
select count(*) from cb_party_info;
conn ta/ta@pension;
select count(*) from cb_party_info;
conn common/common@pension;
select count(*) from cb_party_info;
conn epis/epis@pension;
select max(partycode) from cb_party_info;
select max(partycode) from cb_party_bank_details;
conn pendept/pendept@pension;
select max(partycode) from cb_party_info;
select max(partycode) from cb_party_bank_details;
conn ta/ta@pension;
select max(partycode) from cb_party_info;
select max(partycode) from cb_party_bank_details;
conn common/common@pension;
select max(partycode) from cb_party_info;
select max(partycode) from cb_party_bank_details;
DUPLICATE PREPARATION NUMBERS QUERY
----------------------------------------------------------------------------------------------------
Select keyno,
accountno,
preperation_no,
to_char(preperation_dt, 'dd/Mon/yyyy') preperation_dt,
to_char(ENTEREDDT, 'dd/Mon/yyyy hh:mi AM') ENTEREDDT
from Cb_VOUCHER_INFO
where preperation_no in (select preperation_no
from Cb_VOUCHER_INFO voucher
group by preperation_no, accountno
having count(*) > 1)
order by preperation_no
Select *
from Cb_VOUCHER_INFO
where preperation_no in (Select preperation_no
from Cb_VOUCHER_INFO voucher
group by preperation_no, accountno
having count(*) > 1) order by preperation_no
DUPLICATE VOUCHER NO. QUERY
----------------------------------------------------------------------------------------------------
select keyno,accountno,voucherno
from cb_voucher_info
where voucherno in (select voucherno
from cb_voucher_info voucher
group by accountno, voucherno
having count(*) > 1) order by voucherno
select *
from cb_voucher_info
where voucherno in (select voucherno
from cb_voucher_info voucher
group by accountno, voucherno
having count(*) > 1) order by voucherno
UPDATE CB_VOUCHER_INFO SET PREPERATION_NO = (select vouchertype || bankcode || '/' || sno || '/' || GET_Current_FINYEAR_VAL(to_char(sysdate,'dd/Mon/yyyy')) preperationno from (SELECT 'P' vouchertype,(select BANKCODE from cb_bank_info bank where bank.accountno = '91103150000018') bankcode,LPAD((SELECT to_number(nvl(max(SUBSTR(PREPERATION_NO, instr(PREPERATION_NO, '/') + 1,instr(reverse(PREPERATION_NO),'/') - 2)),3000) + 1) FROM Cb_VOUCHER_INFO vinfo where vinfo.vouchertype = 'P' and vinfo.fyear = '2012-13' and vinfo.accountno ='91103150000018' and vinfo.PREPERATION_DT >=TO_DATE('01/Aug/2012')),4,0) sno from dual) dual) where keyno = '160812000001';
Insert into CB_VOUCHER_INFO(KEYNO, ACCOUNTNO, FYEAR, TRUSTTYPE, VOUCHERTYPE, PARTYTYPE, DETAILS, EMP_PARTY_CODE, VOUCHERNO, PREPAREDBY,
CHECKEDBY, APPROVEDBY, APPROVAL, VOUCHER_DT, ENTEREDBY, ENTEREDDT, PREPERATION_DT, ECPFACNO, EREGION, PFIDFLAG,
EMPLOYEENAME, TRANSACTIONTYPE, EDITEDBY, EDITEDDT, UNITCODE, APPROVALBY, APPROVEDDDT, EUNITCODE, OTHERMODULELINK, RECONCILE_STATUS,
TRANSID, PURPOSETYPE, ACCOUNTFLAG, EMPACCOUNTFLAG, STATIONNAME,
CHECKEDDT, APPROVAL_CHECK, EMPNO, MEDICALNO, CANCEL_VOUCHER_DATE,
CANCEL_REMARKS, CANCEL_STATUS, MAPPINGKEYNO, TYPEOF_TRAVEL, RELEASE_DATE,
HOSPITAL_CODE, MEDICAL_TYPE, QUARTER1, QUARTER2, QUARTER3,
QUARTER4, LIMIT, ADMISSION_TYPE, PAYABLE_PARTY, RTGSCHECKNO,
PREPERATION_NO)
Values
((
select to_char(to_date('06/Aug/2012','dd/mon/yyyy'),'ddmmyy')|| Lpad(Nvl(max(substr(KEYNO,7)),0)+1,6,'0') voucherNo from Cb_VOUCHER_INFO where to_char(to_date('06/Aug/2012','dd/mon/yyyy'),'ddmmyy')=substr(KEYNO,0,6)
) , '11084236539', '2012-13', 'MEDICAL', 'P',
'R', 'FROM 14/05/2012 TO 14/05/2012,BILL NO 22476 DATED 14/05/2012', '50', '', '9',
'', '4', 'Y', '', '3',
TO_DATE('08/06/2012 11:50:30', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/06/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '--', '', '',
'S C SEKHARI', 'B', '', TO_DATE('08/06/2012 11:50:30', 'MM/DD/YYYY HH24:MI:SS'), 'VIDP',
'4', TO_DATE('08/13/2012 15:14:21', 'MM/DD/YYYY HH24:MI:SS'), '', '', 'N',
'', '', 'N', 'N', '60',
'', 'N', '', '50', '',
'', 'N', '', '', '',
'0000004657', 'I', 'N', 'N', 'N',
'N', '', '', 'H', '',
(select vouchertype || bankcode || '/' || sno || '/' || GET_Current_FINYEAR_VAL(to_char(sysdate, 'dd/Mon/yyyy')) preperationno
from (SELECT 'P' vouchertype,(select BANKCODE from cb_bank_info bank
where bank.accountno = '11084236539') bankcode,
LPAD((SELECT to_number(nvl(max(SUBSTR(PREPERATION_NO,instr(PREPERATION_NO, '/') + 1,
instr(reverse(PREPERATION_NO),'/') - 2)),3000) + 1)
FROM Cb_VOUCHER_INFO vinfo
where vinfo.vouchertype = 'P'
and vinfo.fyear = '2012-13'
and vinfo.accountno = '11084236539'
and vinfo.PREPERATION_DT >= TO_DATE('06/Aug/2012')),
4,
0) sno
from dual) dual));
MEDICAL @ AIMS10 from the attached text file for preparation number AUTO GENERATION
----------------------------------------------------------------------------------------------------
Insert into CB_VOUCHER_INFO (KEYNO, ACCOUNTNO, FYEAR, TRUSTTYPE, VOUCHERTYPE, PARTYTYPE, DETAILS, EMP_PARTY_CODE, VOUCHERNO, PREPAREDBY, CHECKEDBY, APPROVEDBY, APPROVAL, VOUCHER_DT, ENTEREDBY, ENTEREDDT, PREPERATION_DT, ECPFACNO, EREGION, PFIDFLAG, EMPLOYEENAME, TRANSACTIONTYPE, EDITEDBY, EDITEDDT, UNITCODE, APPROVALBY, APPROVEDDDT, EUNITCODE, OTHERMODULELINK, RECONCILE_STATUS, TRANSID, PURPOSETYPE, ACCOUNTFLAG, EMPACCOUNTFLAG, STATIONNAME, CHECKEDDT, APPROVAL_CHECK, EMPNO, MEDICALNO, CANCEL_VOUCHER_DATE, CANCEL_REMARKS, CANCEL_STATUS, MAPPINGKEYNO, TYPEOF_TRAVEL, RELEASE_DATE, HOSPITAL_CODE, MEDICAL_TYPE, QUARTER1, QUARTER2, QUARTER3, QUARTER4, LIMIT, ADMISSION_TYPE, PAYABLE_PARTY, RTGSCHECKNO, PREPERATION_NO) Values ((select to_char(to_date('06/Aug/2012','dd/mon/yyyy'),'ddmmyy')|| Lpad(Nvl(max(substr(KEYNO,7)),0)+1,6,'0') voucherNo from Cb_VOUCHER_INFO where to_char(to_date('06/Aug/2012','dd/mon/yyyy'),'ddmmyy')=substr(KEYNO,0,6)) , '11084236539', '2012-13', 'MEDICAL', 'P', 'R', 'FROM 14/05/2012 TO 14/05/2012,BILL NO 22476 DATED 14/05/2012', '50', '', '9', '', '4', 'Y', '', '3', TO_DATE('08/06/2012 11:50:30', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/06/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '--', '', '', 'S C SEKHARI', 'B', '', TO_DATE('08/06/2012 11:50:30', 'MM/DD/YYYY HH24:MI:SS'), 'VIDP', '4', TO_DATE('08/13/2012 15:14:21', 'MM/DD/YYYY HH24:MI:SS'), '', '', 'N','', '', 'N', 'N', '60', '', 'N', '', '50', '', '', 'N', '', '', '', '0000004657', 'I', 'N', 'N', 'N', 'N', '', '', 'H', '', (select vouchertype || bankcode || '/' || sno || '/' || GET_Current_FINYEAR_VAL(to_char(sysdate, 'dd/Mon/yyyy')) preperationno from (SELECT 'P' vouchertype,(select BANKCODE from cb_bank_info bank where bank.accountno = '11084236539') bankcode, LPAD((SELECT to_number(nvl(max(SUBSTR(PREPERATION_NO,instr(PREPERATION_NO, '/') + 1, instr(reverse(PREPERATION_NO),'/') - 2)),3000) + 1) FROM Cb_VOUCHER_INFO vinfo where vinfo.vouchertype = 'P' and vinfo.fyear = '2012-13' and vinfo.accountno = '11084236539' and vinfo.PREPERATION_DT >= TO_DATE('06/Aug/2012')), 4,0) sno from dual) dual));
Insert into CB_VOUCHER_DETAILS (KEYNO, ACCOUNTHEAD, MONTH_YEAR, DETAILS, ENTEREDBY, ENTEREDDT, CREDIT, DEBIT, CHEQUENO, UNITCODE, REGARD, ACC_KEYNO, AMOUNTTYPE_CD, EMPNO, CHRONICEMPLOYEENAME, EMPBANKNAME, EMPACCOUNTNO) Values ((select to_char(to_date('06/Aug/2012','dd/mon/yyyy'),'ddmmyy')|| Lpad(Nvl(max(substr(KEYNO,7)),0),6,'0') voucherNo from Cb_VOUCHER_INFO where to_char(to_date('06/Aug/2012','dd/mon/yyyy'),'ddmmyy')=substr(KEYNO,0,6)), '732.07', '', 'S C SEKHARI', '3', TO_DATE('08/06/2012 11:50:31', 'MM/DD/YYYY HH24:MI:SS'),0,11836,' ', 'VIDP','','','','','','','');
Insert into CB_VOUCHER_DETAILS (KEYNO, ACCOUNTHEAD, MONTH_YEAR, DETAILS, ENTEREDBY, ENTEREDDT, CREDIT, DEBIT, CHEQUENO, UNITCODE, REGARD, ACC_KEYNO, AMOUNTTYPE_CD, EMPNO, CHRONICEMPLOYEENAME, EMPBANKNAME, EMPACCOUNTNO) Values ((select to_char(to_date('06/Aug/2012','dd/mon/yyyy'),'ddmmyy')|| Lpad(Nvl(max(substr(KEYNO,7)),0),6,'0') voucherNo from Cb_VOUCHER_INFO where to_char(to_date('06/Aug/2012','dd/mon/yyyy'),'ddmmyy')=substr(KEYNO,0,6)), '334.05', '', ' ', '3', TO_DATE('08/06/2012 11:50:31', 'MM/DD/YYYY HH24:MI:SS'), 1578, 0,' ','VIDP','','','','','','','');
Insert into CB_VOUCHER_DETAILS (KEYNO, ACCOUNTHEAD, MONTH_YEAR, DETAILS, ENTEREDBY, ENTEREDDT, CREDIT, DEBIT, CHEQUENO, UNITCODE, REGARD, ACC_KEYNO, AMOUNTTYPE_CD, EMPNO, CHRONICEMPLOYEENAME, EMPBANKNAME, EMPACCOUNTNO)
Values ('030412000034', '334.00', '', ' ', '3', sysdate, 3675.00, 0,' ','VIDP','','','','','','','');
in TA @ AIMS10
----------------------------------------------------------------------------------------------------
UPDATE CB_JOURNALVOUCHER SET approval = 'Y',approvedby = '4',approvedddt = '29/Aug/2012',voucherno ='JV/CHQ/'|| (SELECT NVL (MAX (TO_NUMBER (VOUCHER_MAX)), 0) + 1 FROM COMMON.CB_JOURNALVOUCHER_GENERATION vinfo WHERE keyno = UPPER ('290812000001')),voucher_dt = '29/Aug/2012' WHERE keyno = '290812000001';
In COMMON @ AIMS10
----------------------------------------------------------------------------------------------------
UPDATE CB_JOURNALVOUCHER_GENERATION set VOUCHER_MAX=(SELECT NVL (MAX (TO_NUMBER (voucher_max)), 0) + 1 FROM cb_journalvoucher_generation vinfo) where status='A';
after that Send Output from TA
select * from CB_JOURNALVOUCHER WHERE keyno = '290812000001';
and from Common
select * from cb_journalvoucher_generation;
--------------Preperation Number Generation Queries- to update next Sequence ----------------
---L2000 Pre Query for Payment Voucher
update cb_voucher_info set preperation_no=(select vouchertype || bankcode || '/' || sno || '/' ||GET_CURRENT_FINYEAR_VAL(to_char(sysdate, 'dd/Mon/yyyy')) preperationno from (SELECT 'P' vouchertype,(select BANKCODE from cb_bank_info bank where bank.accountno = '000381400000988') bankcode,LPAD((SELECT to_number(nvl(max(SUBSTR(PREPERATION_NO,instr(PREPERATION_NO, '/') + 1,instr(reverse(PREPERATION_NO),'/') - 2)),0)+1) FROM Cb_VOUCHER_INFO vinfo where vinfo.vouchertype = 'P' and vinfo.fyear ='2012-13' and vinfo.accountno = '000381400000988' and vinfo.PREPERATION_DT<TO_DATE('01/Aug/2012')),4,0) sno from dual) dual)
where keyno='190712000007';
---G2000 Pre Query for Payment Voucher
update cb_voucher_info set preperation_no=(select vouchertype || bankcode || '/' || sno || '/' ||GET_Current_FINYEAR_VAL(to_char(sysdate, 'dd/Mon/yyyy')) preperationno from (SELECT 'P' vouchertype,(select BANKCODE from cb_bank_info bank where bank.accountno = '000381400000988') bankcode,LPAD((SELECT to_number(nvl(max(SUBSTR(PREPERATION_NO,instr(PREPERATION_NO, '/') + 1,instr(reverse(PREPERATION_NO),'/') - 2)),3000)+1) FROM Cb_VOUCHER_INFO vinfo where vinfo.vouchertype = 'P' and vinfo.fyear ='2012-13' and vinfo.accountno = '000381400000988' and vinfo.PREPERATION_DT >= TO_DATE('01/Aug/2012')),4,0) sno from dual) dual)
where keyno='130812000035';
---L2000 Pre Query for Receipt Voucher
update cb_voucher_info set preperation_no=(select vouchertype || bankcode || '/' || sno || '/' ||GET_Current_FINYEAR_VAL(to_char(sysdate, 'dd/Mon/yyyy')) preperationno from (SELECT 'R' vouchertype,(select BANKCODE from cb_bank_info bank where bank.accountno = '049010200029847') bankcode,LPAD((SELECT to_number(nvl(max(SUBSTR(PREPERATION_NO,instr(PREPERATION_NO, '/') + 1,instr(reverse(PREPERATION_NO),'/') - 2)),0)+1) FROM Cb_VOUCHER_INFO vinfo where vinfo.vouchertype = 'R' and vinfo.fyear ='2012-13' and vinfo.accountno = '049010200029847' and vinfo.PREPERATION_DT<TO_DATE('01/Aug/2012')),4,0) sno from dual) dual)
where keyno='110712000004';
---G2000 Pre Query for Receipt Voucher
update cb_voucher_info set preperation_no=(select vouchertype || bankcode || '/' || sno || '/' ||GET_Current_FINYEAR_VAL(to_char(sysdate, 'dd/Mon/yyyy')) preperationno from (SELECT 'R' vouchertype,(select BANKCODE from cb_bank_info bank where bank.accountno = '600400300003033') bankcode,LPAD((SELECT to_number(nvl(max(SUBSTR(PREPERATION_NO,instr(PREPERATION_NO, '/') + 1,instr(reverse(PREPERATION_NO),'/') - 2)),3000)+2) FROM Cb_VOUCHER_INFO vinfo where vinfo.vouchertype = 'R' and vinfo.fyear ='2012-13' and vinfo.accountno = '600400300003033' and keyno='280812000009' and vinfo.PREPERATION_DT >= TO_DATE('01/Aug/2012')),4,0) sno from dual) dual)
where keyno='280812000009';
Single update Preperation Number Auto Generation Query
----------------------------------------------------------------------------------------------------
update Cb_Voucher_Info set preperation_no=(case when to_date('01/Apr/2012','dd/Mon/yyyy')>=to_date('01/Aug/2012','dd/Mon/yyyy') then (select vouchertype || bankcode || '/' || sno || '/' ||GET_Current_FINYEAR_VAL(to_char(sysdate, 'dd/Mon/yyyy')) preperationno from (SELECT 'P' vouchertype,(select BANKCODE from cb_bank_info bank where bank.accountno = '000381400000988') bankcode,LPAD((SELECT to_number(nvl(max(SUBSTR(PREPERATION_NO,instr(PREPERATION_NO, '/') + 1,instr(reverse(PREPERATION_NO),'/') - 2)),3000)+1) FROM Cb_VOUCHER_INFO vinfo where vinfo.vouchertype = 'P' and vinfo.fyear ='2012-13' and vinfo.accountno = '000381400000988' and vinfo.PREPERATION_DT >= TO_DATE('01/Aug/2012')),4,0) sno from dual) dual) else (select vouchertype || bankcode || '/' || sno || '/' ||GET_CURRENT_FINYEAR_VAL(to_char(sysdate, 'dd/Mon/yyyy')) preperationno from (SELECT 'P' vouchertype,(select BANKCODE from cb_bank_info bank where bank.accountno = '000381400000988') bankcode,LPAD((SELECT to_number(nvl(max(SUBSTR(PREPERATION_NO,instr(PREPERATION_NO, '/') + 1,instr(reverse(PREPERATION_NO),'/') - 2)),0)+1) FROM Cb_VOUCHER_INFO vinfo where vinfo.vouchertype = 'P' and vinfo.fyear ='2012-13' and vinfo.accountno = '000381400000988' and vinfo.PREPERATION_DT<TO_DATE('01/Aug/2012')),4,0) sno from dual) dual) end),PREPERATION_DT=to_date(SYSDATE,'dd/Mon/yyyy') where keyno='190712000007';
select keyno,preperation_no,to_char(PREPERATION_DT,'dd/Mon/yyyy')PREPERATION_DT,voucherno,to_char(voucher_dt,'dd/Mon/yyyy')voucher_dt from cb_voucher_info where keyno='190712000007';
----for party name and all fileds Query
Select KEYNO,voucher.preperation_no preperation_no,to_char(preperation_dt, 'dd/Mon/YYYY') preperation_dt,(case when (voucherNo is null and MAPPINGKEYNO is not null) then '--' when (voucherNo is null and MEDICAL_TYPE <>'S') then '--' when (voucherNo is null and VOUCHERTYPE='P' and approval='Y' and RELEASE_DATE is null) then '--' else voucherNo end) voucherNo, to_char(VOUCHER_DT, 'dd/MON/yyyy') voucher_dt,(case when (voucher.PARTYTYPE='E' and voucher.MEDICAL_TYPE='I' and PAYABLE_PARTY='H') then (select nvl(PARTYNAME,'--') from cb_party_info where PARTYCODE=voucher.HOSPITAL_CODE) when (voucher.PARTYTYPE='R' and voucher.MEDICAL_TYPE='I' and PAYABLE_PARTY='H') then (select nvl(PARTYNAME,'--') from cb_party_info where PARTYCODE=voucher.HOSPITAL_CODE) when voucher.PARTYTYPE='E' then (select nvl(employeename,'--') from episfin.employeeinfo where emplnumber=voucher.EMPNO) when voucher.PARTYTYPE='R' then (select nvl(employeename,'--') from employee_personal_info where medicalno=voucher.MEDICALNO) when voucher.PARTYTYPE='N' then 'Bulk' else nvl(EMP_PARTY_CODE,'--') end) partyname ,( select case when voucher.vouchertype='P' then sum(dt.debit-dt.credit)||' Dr.' when voucher.vouchertype='A' then sum(dt.debit-dt.credit)||' Dr.' else sum(dt.credit-dt.debit)||' Cr.' end from Cb_voucher_details dt where dt.keyno = voucher.keyno)Amount,BANKNAME,FYEAR,VOUCHERTYPE vtype,Decode(VOUCHERTYPE ,'P','Payment','A','Adjustment','R','Receipt','C','Contra','N','Recovery Note','') VOUCHERTYPE,Decode(PARTYTYPE,'E', 'Employee','P','Party','C','Cash','I','Invest Party','B','Bank','S','Casual','R','Retire Employee','N','Retired Bulk Payment','M','Salarised Chronic/Diagnostic Bulk','') PARTYTYPE,(case when VOUCHER_DT > '08/Jun/2010' and partytype='E' then TRANSID else 1 end) TRANSID,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') status from Cb_VOUCHER_INFO voucher,CB_BANK_INFO bank where bank.ACCOUNTNO = voucher.ACCOUNTNO and
upper(KEYNO)= upper('190712000007') order by voucher.ENTEREDDT desc
MAX Preperation Number +1 Query
----------------------------------------------------------------------------------------------------
select (case when '01/Apr/2012'>=to_date('01/Aug/2012','dd/Mon/yyyy') then (select vouchertype || bankcode || '/' || sno || '/' ||GET_CURRENT_FINYEAR_VAL(to_char(sysdate, 'dd/Mon/yyyy')) preperationno from (SELECT 'P' vouchertype,(select BANKCODE from cb_bank_info bank where bank.accountno = '000381400000988') bankcode,LPAD((SELECT to_number(nvl(max(SUBSTR(PREPERATION_NO,instr(PREPERATION_NO, '/') + 1,instr(reverse(PREPERATION_NO),'/') - 2)),0)+1) FROM Cb_VOUCHER_INFO vinfo where vinfo.vouchertype = 'P' and vinfo.fyear ='2012-13' and vinfo.accountno = '000381400000988' and vinfo.PREPERATION_DT<TO_DATE('01/Aug/2012')),4,0) sno from dual) dual) else (select vouchertype || bankcode || '/' || sno || '/' ||GET_Current_FINYEAR_VAL(to_char(sysdate, 'dd/Mon/yyyy')) preperationno from (SELECT 'P' vouchertype,(select BANKCODE from cb_bank_info bank where bank.accountno = '000381400000988') bankcode,LPAD((SELECT to_number(nvl(max(SUBSTR(PREPERATION_NO,instr(PREPERATION_NO, '/') + 1,instr(reverse(PREPERATION_NO),'/') - 2)),3000)+1) FROM Cb_VOUCHER_INFO vinfo where vinfo.vouchertype = 'P' and vinfo.fyear ='2012-13' and vinfo.accountno = '000381400000988' and vinfo.PREPERATION_DT >= TO_DATE('01/Aug/2012')),4,0) sno from dual) dual) end) from dual
Voucher Search Query
----------------------------------------------------------------------------------------------------
select KEYNO,to_char(VOUCHER_DT, 'dd/MON/yyyy') voucher_dt,to_char(preperation_dt, 'dd/Mon/YYYY') preperation_dt,(case when (voucher.PARTYTYPE='E' and voucher.MEDICAL_TYPE='I' and PAYABLE_PARTY='H') then (select nvl(PARTYNAME,'--') from cb_party_info where PARTYCODE=voucher.HOSPITAL_CODE) when (voucher.PARTYTYPE='R' and voucher.MEDICAL_TYPE='I' and PAYABLE_PARTY='H') then (select nvl(PARTYNAME,'--') from cb_party_info where PARTYCODE=voucher.HOSPITAL_CODE) when voucher.PARTYTYPE='E' then (select nvl(employeename,'--') from episfin.employeeinfo where emplnumber=voucher.EMPNO) when voucher.PARTYTYPE='R' then (select nvl(employeename,'--') from employee_personal_info where medicalno=voucher.MEDICALNO) when voucher.PARTYTYPE='N' then 'Bulk' else nvl(EMP_PARTY_CODE,'--') end) partyname ,(case when (voucherNo is null and MAPPINGKEYNO is not null) then '--' when (voucherNo is null and MEDICAL_TYPE <>'S') then '--' when (voucherNo is null and VOUCHERTYPE='P' and approval='Y' and RELEASE_DATE is null) then '--' else voucherNo end) voucherNo,( select case when voucher.vouchertype='P' then sum(dt.debit-dt.credit)||' Dr.' when voucher.vouchertype='A' then sum(dt.debit-dt.credit)||' Dr.' else sum(dt.credit-dt.debit)||' Cr.' end from Cb_voucher_details dt where dt.keyno = voucher.keyno)Amount,BANKNAME,FYEAR,VOUCHERTYPE vtype,Decode(VOUCHERTYPE ,'P','Payment','A','Adjustment','R','Receipt','C','Contra','N','Recovery Note','') VOUCHERTYPE,Decode(PARTYTYPE,'E', 'Employee','P','Party','C','Cash','I','Invest Party','B','Bank','S','Casual','R','Retire Employee','N','Retired Bulk Payment','M','Salarised Chronic/Diagnostic Bulk','') PARTYTYPE,(case when VOUCHER_DT > '08/Jun/2010' and partytype='E' then TRANSID else 1 end) TRANSID,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') status,voucher.preperation_no preperation_no from Cb_VOUCHER_INFO voucher,CB_BANK_INFO bank where
bank.ACCOUNTNO = voucher.ACCOUNTNO and upper(BANKNAME) like upper(?)
and FYEAR like ? and VOUCHERTYPE like ? and upper(voucher.ACCOUNTNO) like upper(?)
and upper(voucher.EMP_PARTY_CODE) like upper(?)
and voucher_dt >= to_date('01/APR/2012','dd/Mon/yyyy')
and voucher_dt <= to_date('30/APR/2012','dd/Mon/yyyy')
order by voucher.ENTEREDDT desc
For Voucher Number Auto Generation Query
----------------------------------------------------------------------------------------------------
Update CB_VOUCHER_INFO set VOUCHERNO=(case when PREPERATION_DT>=to_date('01/Aug/2012','dd/Mon/yyyy') then (SELECT vouchertype || 'V/' ||(select BANKCODE BANKNAME from cb_bank_info bank where bank.accountno = info.accountno) || '/' || LPAD((SELECT NVL(MAX(TO_NUMBER(VOUCHER_MAX)),3000) + 1 FROM common.cb_voucherno_generation vinfo where vinfo.vouchertype= info.vouchertype and info.fyear=vinfo.fyear and vinfo.STATUS='A' and info.accountno = vinfo.accountno),4,0) FROM Cb_VOUCHER_INFO info WHERE upper(keyno)= upper('190712000007') ) else (SELECT vouchertype || 'V/' ||(select BANKCODE BANKNAME from cb_bank_info bank where bank.accountno = info.accountno) || '/' || LPAD((SELECT NVL(MAX(TO_NUMBER(VOUCHER_MAX)),0) + 1 FROM common.cb_voucherno_generation vinfo where vinfo.vouchertype= info.vouchertype and info.fyear=vinfo.fyear and vinfo.STATUS='B' and info.accountno = vinfo.accountno),4,0) FROM Cb_VOUCHER_INFO info WHERE upper(keyno)= upper('190712000007') ) end),voucher_dt=to_date(SYSDATE,'dd/Mon/yyyy') where upper(keyno)= upper('190712000007');
select keyno,preperation_no,to_char(PREPERATION_DT,'dd/Mon/yyyy')PREPERATION_DT,voucherno,to_char(voucher_dt,'dd/Mon/yyyy')voucher_dt from cb_voucher_info where keyno='190712000007';
----------------------For Party Name and All Fileds Query------------------------------------
Select KEYNO,voucher.preperation_no preperation_no,to_char(preperation_dt, 'dd/Mon/YYYY') preperation_dt,(case when (voucherNo is null and MAPPINGKEYNO is not null) then '--' when (voucherNo is null and MEDICAL_TYPE <>'S') then '--' when (voucherNo is null and VOUCHERTYPE='P' and approval='Y' and RELEASE_DATE is null) then '--' else voucherNo end) voucherNo, to_char(VOUCHER_DT, 'dd/MON/yyyy') voucher_dt,(case when (voucher.PARTYTYPE='E' and voucher.MEDICAL_TYPE='I' and PAYABLE_PARTY='H') then (select nvl(PARTYNAME,'--') from cb_party_info where PARTYCODE=voucher.HOSPITAL_CODE) when (voucher.PARTYTYPE='R' and voucher.MEDICAL_TYPE='I' and PAYABLE_PARTY='H') then (select nvl(PARTYNAME,'--') from cb_party_info where PARTYCODE=voucher.HOSPITAL_CODE) when voucher.PARTYTYPE='E' then (select nvl(employeename,'--') from episfin.employeeinfo where emplnumber=voucher.EMPNO) when voucher.PARTYTYPE='R' then (select nvl(employeename,'--') from employee_personal_info where medicalno=voucher.MEDICALNO) when voucher.PARTYTYPE='N' then 'Bulk' else nvl(EMP_PARTY_CODE,'--') end) partyname ,( select case when voucher.vouchertype='P' then sum(dt.debit-dt.credit)||' Dr.' when voucher.vouchertype='A' then sum(dt.debit-dt.credit)||' Dr.' else sum(dt.credit-dt.debit)||' Cr.' end from Cb_voucher_details dt where dt.keyno = voucher.keyno)Amount,BANKNAME,FYEAR,VOUCHERTYPE vtype,Decode(VOUCHERTYPE ,'P','Payment','A','Adjustment','R','Receipt','C','Contra','N','Recovery Note','') VOUCHERTYPE,Decode(PARTYTYPE,'E', 'Employee','P','Party','C','Cash','I','Invest Party','B','Bank','S','Casual','R','Retire Employee','N','Retired Bulk Payment','M','Salarised Chronic/Diagnostic Bulk','') PARTYTYPE,(case when VOUCHER_DT > '08/Jun/2010' and partytype='E' then TRANSID else 1 end) TRANSID,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') status from Cb_VOUCHER_INFO voucher,CB_BANK_INFO bank where bank.ACCOUNTNO = voucher.ACCOUNTNO and
upper(KEYNO)= upper('190712000007') order by voucher.ENTEREDDT desc
JOURNAL VOUCHER Auto GENERATION Query
----------------------------------------------------------------------------------------------------
In EPIS@ AIMS10
----------------------
L2000
--------------
UPDATE cb_journalvoucher SET VOUCHERNO=( SELECT 'JV/CHQ/' || LPAD((SELECT NVL(MAX(TO_NUMBER(VOUCHER_MAX)), 0) + 1 FROM common.cb_journalvoucher_generation vinfo where info.finyear = vinfo.fyear and vinfo.STATUS = 'B'), 4,0) FROM cb_journalvoucher info WHERE upper(keyno) = upper('160612000001')) WHERE KEYNO='160612000001';
SELECT VOUCHERNO FROM cb_journalvoucher WHERE KEYNO='160612000001';
G2000
---------
UPDATE cb_journalvoucher SET VOUCHERNO=( SELECT 'JV/CHQ/' || LPAD((SELECT NVL(MAX(TO_NUMBER(VOUCHER_MAX)), 3000) + 1 FROM common.cb_journalvoucher_generation vinfo where info.finyear = vinfo.fyear and vinfo.STATUS = 'B'), 4,0) FROM cb_journalvoucher info WHERE upper(keyno) = upper('160612000001')) WHERE KEYNO='160612000001';
SELECT VOUCHERNO FROM cb_journalvoucher WHERE KEYNO='160612000001';
After that in execute for max voucher no update required in voucherno generation query
IN COMMON@ AIMS10
----------------------------------
L2000--------
UPDATE CB_JOURNALVOUCHER_GENERATION SET VOUCHER_MAX=VOUCHER_MAX+1 WHERE STATUS='B';
G2000----
UPDATE CB_JOURNALVOUCHER_GENERATION SET VOUCHER_MAX=VOUCHER_MAX+1 WHERE STATUS='A';
First and Last Date Query for the current year
----------------------------------------------------------------------------------------------------
select to_char(to_date(to_char((sysdate - to_char(sysdate, 'dd') + 1),
'dd-mon-yyyy')),
'DD/Mon/YYYY') "First Day",
to_char(Last_day(sysdate), 'DD') " Last day"
from dual
select Last_day(sysdate) from dual
select sysdate+5-(sysdate+5) from dual
select to_date('01/Jan/2012') - to_date('01/May/2012') from dual
select numtodsinterval(to_date('31/Mar/2012')-to_date('01/Jan/2012'),'day') time_difference from dual;
Voucher max Number Function
----------------------------------------------------------------------------------------------------
/* Formatted on 2012/09/06 15:25 (Formatter Plus v4.8.8) */
/*SELECT get_nextvoucherno ('210412000005','01/Jun/2012','859937401','P') vouchno FROM DUAL */
CREATE OR REPLACE FUNCTION get_nextvoucherno (
p_keyno varchar,
p_preperation_date VARCHAR
--p_accountno VARCHAR,
-- p_voucherType VARCHAR
)
RETURN VARCHAR
IS
v_query VARCHAR (2000);
v_autocode VARCHAR (50);
v_autonum varchar2(20);
v_autocode_status varchar2(20);
v_voucherformat varchar2(20);
BEGIN
SELECT (CASE WHEN p_preperation_date >= TO_DATE ('01/Aug/2012', 'dd/Mon/yyyy') THEN 'G2000' ELSE 'L2000' END) voucompare into v_autocode_status FROM cb_voucher_info WHERE keyno = p_keyno;
--dbms_output.put_line('v_autocode_status: '||v_autocode_status);
If (v_autocode_status = 'G2000') Then
v_query := 'SELECT vouchertype || ''V/'' ||(select BANKCODE BANKNAME from cb_bank_info bank where bank.accountno = info.accountno) || ''/'' || LPAD((SELECT NVL(MAX(TO_NUMBER(VOUCHER_MAX)),3000) + 1 FROM common.cb_voucherno_generation vinfo where vinfo.vouchertype= info.vouchertype and info.fyear=vinfo.fyear and vinfo.STATUS=''A'' and info.accountno = vinfo.accountno),4,0) FROM Cb_VOUCHER_INFO info WHERE upper(keyno)= upper('''||p_keyno||''')';
-- dbms_output.put_line(v_autocode_status||' IF Cond v_query: '||v_query);
Elsif v_autocode_status = 'L2000' Then
v_query := 'SELECT vouchertype || ''V/'' ||(select BANKCODE BANKNAME from cb_bank_info bank where bank.accountno = info.accountno) || ''/'' || LPAD((SELECT NVL(MAX(TO_NUMBER(VOUCHER_MAX)),0) + 1 FROM common.cb_voucherno_generation vinfo where vinfo.vouchertype= info.vouchertype and info.fyear=vinfo.fyear and vinfo.STATUS=''B'' and info.accountno = vinfo.accountno),4,0) FROM Cb_VOUCHER_INFO info WHERE upper(keyno)= upper('''||p_keyno||''')';
--dbms_output.put_line(v_autocode_status ||'ELSE v_query: '||v_query);
End If;
EXECUTE IMMEDIATE v_query INTO v_autocode;
--dbms_output.put_line('VACD : '||substr(v_autocode,0,length(v_autocode)-4));
v_voucherformat:=substr(v_autocode,0,length(v_autocode)-4);
v_autonum:=lpad(to_number(nvl(substr(v_autocode,length(v_autocode)-3,length(v_autocode)),0)),4,0);
v_autocode:=v_voucherformat||''||v_autonum;
dbms_output.put_line('v_autocode: '||v_autocode);
RETURN v_autocode;
END;
/
AFTER THAT in COMMON Execute the below UPDATE Query for next VoucherNo Generation
----------------------------------------------------------------------------------------------------
UPDATE COMMON.CB_VOUCHERNO_GENERATION
SET voucher_max = voucher_max + 1 WHERE accountno = '' AND status = '' and vouchertype=''
Query for Comparing Preperation Number and Preperation Dates Comparision
----------------------------------------------------------------------------------------------------
SELECT KEYNO,PREPERATION_NO,TO_CHAR(PREPERATION_DT,'DD/MON/YYYY')PREPDATE
FROM CB_VOUCHER_INFO
WHERE VOUCHERTYPE = 'R'
AND ACCOUNTNO = '600400300003033'
AND PREPERATION_DT> '01/AUG/2012'
ORDER BY PREPERATION_NO
select keyno,
preperation_no,
to_char(preperation_dt, 'dd/Mon/yyyy') prepdate,voucherno
from cb_voucher_info
where accountno = '600400300003033'
and preperation_dt > '01 aug 2012'
and vouchertype = 'R'
order by to_number(SUBSTR(PREPERATION_NO,
instr(PREPERATION_NO, '/') + 1,
instr(reverse(PREPERATION_NO), '/') - 2))
Query for Wrong Series generated for Preperation number when preperation_dt<'01/Aug/2012'
----------------------------------------------------------------------------------------------------
select info.keyno,
info.preperation_no,
to_char(info.preperation_dt, 'dd/Mon/yyyy') prepdate
from cb_voucher_info info, cb_bank_info bank
where info.accountno = bank.accountno
-- and info.accountno = '006502000008111'
and info.preperation_dt < '01/Aug/2012'
and info.preperation_no like
(info.vouchertype || bank.ifsccode || '/3%')
order by info.preperation_no, info.preperation_dt
Query for Wrong Series generated for Preperation number when preperation_dt>='01/Aug/2012'
----------------------------------------------------------------------------------------------------
select info.keyno,
info.preperation_no,
to_char(info.preperation_dt, 'dd/Mon/yyyy') prepdate
from cb_voucher_info info, cb_bank_info bank
where info.accountno = bank.accountno
-- and info.accountno = '006502000008111'
and info.preperation_dt > '01/Aug/2012'
and info.preperation_no like
(info.vouchertype || bank.ifsccode || '/0%')
order by info.preperation_no, info.preperation_dt
Foreign Key Constraint adding in a table
----------------------------------------------------------------------------------------------------
UPDATE CB_VOUCHER_INFO SET TRUSTTYPE='';
ALTER TABLE cb_voucher_info ADD CONSTRAINT cb_voucher_fk FOREIGN KEY (trusttype) REFERENCES invest_trusttype (trusttype);
UPDATE CB_VOUCHER_INFO SET TRUSTTYPE='PENSION';
constraint Dropping and Adding
----------------------------------------------------------------------------------------------------
alter table CB_AD_VOUCHER_DETAILS
drop constraint FK_ADVOUCHER_KEYNO;
alter table CB_AD_VOUCHER_DETAILS
add constraint FK_ADVOUCHER_KEYNO foreign key (KEYNO)
references cb_ad_voucher_info (KEYNO);
Some Select Queries for other module related
----------------------------------------------------------------------------------------------------
select keyno,accountno,preperation_no,to_char(preperation_dt, 'dd/Mon/yyyy') preperation_dt,voucherno, othermodulelink from cb_voucher_info where keyno='120413000001';
select keyno,
accountno,
preperation_no,
to_char(preperation_dt, 'dd/Mon/yyyy') preperation_dt,
voucherno,
othermodulelink
from cb_voucher_info
where accountno = '91103150000018'
order by preperation_no, preperation_dt;
------------------
alter table cb_party_bank_details modify IFSCCODE varchar2(35);
update cb_party_bank_details set IFSCCODE='CH68 0024 3243 3687 3760 G' where partycode in( select PARTYCODE from cb_party_info where partyname like '%DKMA SWITZERLAND%');
Update cb_voucher_info set preperation_dt='03/Oct/2012' where keyno='210912000013';
----------EPIS
Delete from cb_voucher_details where keyno='070812000028' and accounthead='635.07' and rownum=1;
Update cb_voucher_details set DEBIT='17960.0000' where KEYNO='070812000028' and accounthead='635.07' and debit='11960';
Insert into CB_VOUCHER_DETAILS (KEYNO, ACCOUNTHEAD, MONTH_YEAR, DETAILS, ENTEREDBY, ENTEREDDT, CREDIT, DEBIT, CHEQUENO, UNITCODE, REGARD, ACC_KEYNO, AMOUNTTYPE_CD, EMPNO, CHRONICEMPLOYEENAME, EMPBANKNAME, EMPACCOUNTNO)values ('070812000028', '640.07', '', '', '15', sysdate, 0, 11839, '', 'VIDP', '', '', '', '', '', '', '');
----------MEDICAL
update employee_personal_info set dateofseperation_reason='Retirement' Where medicalnno='595';
PARTY MASTER SCRIPT FOR ALL DATABASES
----------------------------------------------------------------------------------------------------
Alter table cb_party_bank_details add (SWIFTCODE varchar2(40));
MEDICAL NO SCRIPT NEW in MEDICAL@AIMS10
----------------------------------------------------------------------------------------------------
insert into EMPLOYEE_PERSONAL_INFO (EMPLOYEENO, EMPLOYEENAME, MEDICALNO, DATEOFSEPERATION_REASON, EMPAUTOID)
values ('SC1800', 'SHANKAR CHAUDHARY','DM-SC1800','Retirement', GET_NEXTCODE('employee_personal_info','EMPAUTOID',10));
For User Name Changing and Disable then Enable constraints
----------------------------------------------------------------------------------------------------
Alter Table EPIS_ACCESSRIGHTS DISABLE CONSTRAINT FK_USERID;
update EPIS_USER set USERNAME='JS2082',EMPLOYEENO='JS2082' where USERID='10';
update EPIS_ACCESSRIGHTS set USERID='JS2082' where USERID='JS2028' ;
Alter Table EPIS_ACCESSRIGHTS ENABLE CONSTRAINT FK_USERID;
Deleting Duplicate Account Heads unit wise
----------------------------------------------------------------------------------------------------
Delete from cb_accountcode_details c where accounthead='304.02' and unitcode='70' and rownum=1;
Delete from cb_accountcode_details c where accounthead='377.00' and unitcode='60' and rownum=1;
update CB_ACCOUNTCODE_DETAILS set accounthead=trim(accounthead) where unitcode='60';
update CB_ACCOUNTCODE_DETAILS set unitcode=trim(unitcode) where unitcode='60';
select * from cb_accountcode_details where unitcode='60' order by accounthead
select count(*),accounthead from cb_accountcode_details where unitcode is null group by accounthead having count(*)>1;
Insert into cb_party_info(partycode,partyname,partydetail) values(get_nextcode('cb_party_info','partycode',10),'National Aluminium Company','National Aluminium Company');
Insert into cb_party_info(partycode,partyname,partydetail) values(get_nextcode('cb_party_info','partycode',10),'The Controller (Finance & Accounts)','The Controller (Finance & Accounts)');
Insert into cb_party_info(partycode,partyname,partydetail) values(get_nextcode('cb_party_info','partycode',10),'PAO (MS), Ministry of Information & Technology','PAO (MS), Ministry of Information & Technology');
update cb_voucher_info set partytype='R' where keyno='260412000021';
/*select info.empno,
(select SUM(DEBIT) - SUM(CREDIT)
FROM CB_VOUCHER_DETAILS
WHERE KEYNO = info.keyno) amount
from cb_voucher_info info
where info.voucher_dt = '30 apr 2012'
and info.partytype = 'E'
and accountno = '11084236539';
select info.medicalno,
(select sum(debit) - sum(credit)
from cb_voucher_details
where keyno = info.keyno) amount
from cb_voucher_info info, employee_personal_info personal
where info.medicalno = personal.medicalno
and info.voucher_dt = '30 apr 2012'
and info.partytype = 'R'
and info.accountno = '11084236539';*/
select keyno,accounthead,debit
from cb_voucher_details
where keyno in
(select keyno
from cb_voucher_info
where preperation_dt between '01/Apr/2012' and '30/Apr/2012'
and accountno = '11084236539') order by debit;
update cb_bank_info set UNITNAME='01' where ACCOUNTCODE='628.22';
update cb_voucher_info set STATIONNAME='01' where accountno='CLCA01110002';
UPDATE cb_voucher_info set VOUCHER_DT='01/Jul/2012',RELEASE_DT='01/Jul/2012' WHERE KEYNO='270612000017';
EVENING BANK BOOK DELETION SCRIPTS
=======================================
MEDICAL@AIMS10
---------------
DELETE FROM CB_VOUCHER_DETAILS WHERE KEYNO=(select keyno from cb_voucher_info where payroll_voucherno='SB/0888/12-13');
DELETE FROM CB_VOUCHER_INFO WHERE payroll_voucherno='SB/0888/12-13';
DELETE FROM CB_VOUCHER_DETAILS WHERE KEYNO=(select keyno from cb_voucher_info where payroll_voucherno='SB/0889/12-13');
DELETE FROM CB_VOUCHER_INFO WHERE payroll_voucherno='SB/0889/12-13';
DELETE FROM CB_VOUCHER_DETAILS WHERE KEYNO=(select keyno from cb_voucher_info where payroll_voucherno='SB/0890/12-13');
DELETE FROM CB_VOUCHER_INFO WHERE payroll_voucherno='SB/0890/12-13';
EPIS@AIMS10
---------------
DELETE FROM CB_VOUCHER_DETAILS WHERE KEYNO in(select keyno from cb_voucher_info where payroll_voucherno in( 'SB/0868/12-13');
DELETE FROM CB_VOUCHER_INFO WHERE payroll_voucherno in ('SB/0868/12-13');
DELETE FROM CB_VOUCHER_DETAILS WHERE KEYNO in(select keyno from cb_voucher_info where payroll_voucherno in ('SB/0869/12-13');
DELETE FROM CB_VOUCHER_INFO WHERE payroll_voucherno in('SB/0869/12-13');
VOUCHERNO updation
----------------------------------
Update CB_VOUCHER_INFO set VOUCHERNO=(case when PREPERATION_DT>=to_date('01/Aug/2012','dd/Mon/yyyy') then (SELECT vouchertype || 'V/' ||(select BANKCODE BANKNAME from cb_bank_info bank where bank.accountno = info.accountno) || '/' || LPAD((SELECT NVL(MAX(TO_NUMBER(VOUCHER_MAX)),3000) + 1 FROM common.cb_voucherno_generation vinfo where vinfo.vouchertype= info.vouchertype and info.fyear=vinfo.fyear and vinfo.STATUS='A' and info.accountno = vinfo.accountno),4,0) FROM Cb_VOUCHER_INFO info WHERE upper(keyno)= upper('190712000007') ) else (SELECT vouchertype || 'V/' ||(select BANKCODE BANKNAME from cb_bank_info bank where bank.accountno = info.accountno) || '/' || LPAD((SELECT NVL(MAX(TO_NUMBER(VOUCHER_MAX)),0) + 1 FROM common.cb_voucherno_generation vinfo where vinfo.vouchertype= info.vouchertype and info.fyear=vinfo.fyear and vinfo.STATUS='B' and info.accountno = vinfo.accountno),4,0) FROM Cb_VOUCHER_INFO info WHERE upper(keyno)= upper('190712000007') ) end),voucher_dt=to_date(SYSDATE,'dd/Mon/yyyy') where upper(keyno)= upper('190712000007');
select keyno,preperation_no,to_char(PREPERATION_DT,'dd/Mon/yyyy')PREPERATION_DT,voucherno,to_char(voucher_dt,'dd/Mon/yyyy')voucher_dt from cb_voucher_info where keyno='190712000007';
UPDATE COMMON.CB_VOUCHERNO_GENERATION SET voucher_max = voucher_max + 1 WHERE accountno = '' AND status = '' and vouchertype=''
UPDATE CB_VOUCHER_INFO SET VOUCHERNO = (SELECT vouchertype || 'V/' ||(select BANKCODE BANKNAME from cb_bank_info bank where bank.accountno = info.accountno) || '/' || LPAD((SELECT NVL(MAX(TO_NUMBER(VOUCHER_MAX)), 3000) + 1 FROM COMMON.CB_VOUCHERNO_GENERATION vinfo where vinfo.vouchertype = info.vouchertype and info.fyear = vinfo.fyear and vinfo.STATUS = 'A' and info.accountno = vinfo.accountno),4,0) FROM Cb_VOUCHER_INFO info WHERE upper(keyno) = upper('240712000006')) where upper(keyno) = upper('240712000006');
UPDATE common.CB_VOUCHERNO_GENERATION SET VOUCHER_MAX = VOUCHER_MAX + 1 WHERE accountno = '11084236539' AND status = 'A' and vouchertype='P';
---------------
UPDATE CB_VOUCHER_INFO SET VOUCHERNO = (SELECT vouchertype || 'V/' ||(select BANKCODE BANKNAME from cb_bank_info bank where bank.accountno = info.accountno) || '/' || LPAD((SELECT NVL(MAX(TO_NUMBER(VOUCHER_MAX)), 3000) + 1 FROM COMMON.CB_VOUCHERNO_GENERATION vinfo where vinfo.vouchertype = info.vouchertype and info.fyear = vinfo.fyear and vinfo.STATUS = 'A' and info.accountno = vinfo.accountno),4,0) FROM Cb_VOUCHER_INFO info WHERE upper(keyno) = upper('300712000002')),voucher_dt='02/Aug/2012',RELEASE_DATE='02/Aug/2012' where upper(keyno) = upper('300712000002');
after that immedicately connect to COMMON/AIMS10 and execute the script
UPDATE CB_VOUCHERNO_GENERATION SET voucher_max = voucher_max + 1 WHERE accountno = '11084236539' AND status = 'A' and vouchertype='P';
UPDATE CB_VOUCHER_INFO SET VOUCHERNO = (SELECT vouchertype || 'V/' ||(select BANKCODE BANKNAME from cb_bank_info bank where bank.accountno = info.accountno) || '/' || LPAD((SELECT NVL(MAX(TO_NUMBER(VOUCHER_MAX)), 3000) + 1 FROM COMMON.CB_VOUCHERNO_GENERATION vinfo where vinfo.vouchertype = info.vouchertype and info.fyear = vinfo.fyear and vinfo.STATUS = 'A' and info.accountno = vinfo.accountno),4,0) FROM Cb_VOUCHER_INFO info WHERE upper(keyno) = upper('300712000001')),voucher_dt='02/Aug/2012',RELEASE_DATE='02/Aug/2012' where upper(keyno) = upper('300712000001');
after that immedicately connect to COMMON/AIMS10 and execute the script
UPDATE CB_VOUCHERNO_GENERATION SET voucher_max = voucher_max + 1 WHERE accountno = '11084236539' AND status = 'A' and vouchertype='P';
update cb_journalvoucher set preperation_no=(select 'J' || TRUSTTYPE || '/'|| sno || '/' ||GET_Current_FINYEAR_VAL(to_char(sysdate, 'dd/Mon/yyyy')) preperationno from (select 'COMMON' TRUSTTYPE,LPAD((SELECT nvl(MAX(SUBSTR(PREPERATION_NO,instr(PREPERATION_NO, '/') + 1,instr(reverse(PREPERATION_NO),'/') - 2)),0) + 1 FROM cb_journalvoucher vinfo where vinfo.finyear ='2012-13' and vinfo.PREPERATION_DT < TO_DATE('01/Aug/2012')),4,0) sno from dual) dual) where preperation_no='JCOMMON/0042/12-13';
Preperation Number Sorting inside bank IFSC Code and Fin Years
------------------------------------------------------------------
select SUBSTR(voucher.PREPERATION_NO,instr(PREPERATION_NO, '/') + 1,instr(reverse(voucher.PREPERATION_NO), '/') - 2)vno from cb_voucher_info voucher
select lpad(Nvl(max(keyno), 0) + 1, 12, 0)
from cb_journalvoucher
where deleted_flag = 'Y'
======================================================================================================================
In Control Panel ->File Types->JAR->open-> "C:\Program Files\Java\jdk1.6.0_10\jre\bin\javaw.exe" -jar "%1" %*
======================================================================================================================
CREATE TABLE CB_VOUCHER_LOGS
(
KEYNO VARCHAR2(12),
VKEYNO VARCHAR2(12),
VOUCHERNO VARCHAR2(25),
VOUCHER_DT DATE,
PREPERATION_DT DATE,
PREPERATION_NO VARCHAR2(25),
VOUCHERTOTAL NUMBER(15,4),
ENTEREDDT DATE DEFAULT SYSDATE,
TRUSTTYPE VARCHAR2(20)
);
String voucherLogQuery="Insert Into Cb_Voucher_Logs(KEYNO,VKEYNO,VOUCHERNO,VOUCHER_DT,PREPERATION_DT,PREPERATION_NO,VOUCHERTOTAL,TRUSTTYPE) Values( (select lpad(Nvl(max(keyno), 0)+1,12,0) from cb_voucher_logs),'"+keyno+"','"+voucherNO+"','"+releaseDate+"',(select nvl(preperation_dt, '') preperation_dt from "+department+".CB_VOUCHER_INFO info where keyno = '"+keyno+"'),(select nvl(preperation_no, '') preperation_no from "+department+".CB_VOUCHER_INFO info where keyno = '"+keyno+"'),(select (case when (sum(nvl(DEBIT, 0)) - sum(nvl(CREDIT, 0))) > 0 then (sum(nvl(DEBIT, 0)) - sum(nvl(CREDIT, 0))) when ((sum(nvl(CREDIT, 0)) - sum(nvl(DEBIT, 0)))) > 0 then ((sum(nvl(CREDIT, 0)) - sum(nvl(DEBIT, 0)))) else 0 end) from "+department+".cb_voucher_details where keyno = '"+keyno+"'),(select nvl(trusttype, '') trusttype from "+department+".CB_VOUCHER_INFO info where keyno = '"+keyno+"') )";
Update Cb_voucher_details Set Debit='966184' Where Keyno='010512000058' And Accounthead='736.00';
Insert Into Cb_voucher_details (Keyno, Accounthead, Month_year, Details, Enteredby, Entereddt, Credit, Debit, Chequeno, Unitcode, Regard, Acc_keyno, Amounttype_cd, Empno, Chronicemployeename, Empbankname, Empaccountno)Values ('010512000058', '315.00', '', '', '15', Sysdate, 0, 24, '', 'VIDP', '', '', '', '', '', '', '');
Insert Into Cb_voucher_details (Keyno, Accounthead, Month_year, Details, Enteredby, Entereddt, Credit, Debit, Chequeno, Unitcode, Regard, Acc_keyno, Amounttype_cd, Empno, Chronicemployeename, Empbankname, Empaccountno)Values ('010512000058', '739.50', '', '', '15', Sysdate, 60, 0, '', 'VIDP', '', '', '', '', '', '', '');
UPDATE CB_VOUCHER_DETAILS SET DEBIT='2226' WHERE KEYNO='110313000038' AND ACCOUNTHEAD='732.07';
Update Cb_VOUCHER_INFO set VOUCHER_DT='18/Feb/2013',APPROVAL='Y',APPROVEDBY='6',VOUCHERNO='RV/YE88/3014',APPROVALBY='6',RELEASE_DATE='18/Feb/2013',ApprovedDDT=SYSDATE WHERE KEYNO='180213000011';
Prepration Num :- PSY18/3116/12-13, Amount 262445 280313000003
Prepration Num :- PSY18/4086/12-13 Amount 166223 280313000001
Prepration Num :- PSY18/4087/12-13 Amount 89734 280313000002
DELETE FROM CB_VOUCHER_DETAILS WHERE KEYNO='280313000003';
DELETE FROM CB_VOUCHER_INFO WHERE KEYNO='280313000003';
DELETE FROM CB_VOUCHER_DETAILS WHERE KEYNO='280313000001';
DELETE FROM CB_VOUCHER_INFO WHERE KEYNO='280313000001';
DELETE FROM CB_VOUCHER_DETAILS WHERE KEYNO='280313000002';
DELETE FROM CB_VOUCHER_INFO WHERE KEYNO='280313000002';
Alter Table Tc_arrival_log Add Arr_hopping_status Char(1);
Alter Table Tc_ca12info Add Arr_hopping_status Char(1);
Alter Table Tc_arrival_log Add Deptype Char(1);
Alter Table Tc_ca12info Add Deptype Char(1);
Alter Table Tc_Hopping_Dt Add Status Char(1);
update Tc_Hopping_Dt set status='D' where status is null;
for (var j=0;j<document.forms[1].ArrHoppingDetails.options.length;j++)
{
if(document.forms[1].ArrHoppingDetails.options[j].selected)
{
ArrHoppingDetails+=document.forms[1].ArrHoppingDetails.options[j].value+",";
}
}
if(hoppingA.getElementsByTagName("HopA")[0]){
ArrfrmNew.gcd.value='';
var gcdvalueA='';
for (loop = 0; loop < hoppingA.childNodes.length; loop++) {
hop=hoppingA.childNodes[loop];
arrayresult[loop]=[retNodeValue(hop,'FromLatitude')+'',retNodeValue(hop,'FromLongitude')+'',retNodeValue(hop,'ToLatitude')+'',retNodeValue(hop,'ToLongitude')+'',retNodeValue(hop,'Gcd')+'',retNodeValue(hop,'Rnfc')+'',retNodeValue(hop,'FromPoint')+'',retNodeValue(hop,'ToPoint')+''];
if(loop==hoppingA.childNodes.length-1) {
gcdvalueA=gcdvalueA+retNodeValue(hop,'Gcd');
} else {
gcdvalueA=gcdvalueA+retNodeValue(hop,'Gcd')+",";
}
}
ArrfrmNew.gcd.value=gcdvalueA;
}
Party Insert Scripts
----------------------------------------------------------------------------------------------------
INSERT INTO CB_PARTY_INFO(PARTYCODE,PARTYNAME,PARTYDETAIL) VALUES(GET_NEXTCODE('CB_PARTY_INFO',
INSERT INTO CB_PARTY_INFO(PARTYCODE,PARTYNAME,PARTYDETAIL) VALUES(GET_NEXTCODE('CB_PARTY_INFO','PARTYCODE',10),'NIT Kurukshetra','NIT Kurukshetra');
INSERT INTO CB_PARTY_BANK_DETAILS (PARTYCODE, BANKNAME, BRANCHNAME, BANKCODE, ADDRESS, PHONENO, BFAXNO, ACCOUNTNO, ACCOUNTTYPE, IFSCCODE, NEFT_RTGSCODE, MICRNO, CONTACTPERSON, BMOBILENO, ENTEREDBY, ENTEREDDT, UNITCODE) Values ('0000001186', 'NA', 'NA', 'NA', 'NA', ' ', ' ', 'NA', 'C', ' ', ' ', ' ', ' ', ' ', '', TO_DATE('08/29/2012 13:15:13', 'MM/DD/YYYY HH24:MI:SS'), '');
INSERT INTO CB_PARTY_INFO (PARTYNAME, PARTYDETAIL, FAXNO, EMAIL, MOBILENO, ENTEREDBY, ENTEREDDT, CONTACTNO, PARTYCODE, EDITEDBY, EDITEDDT, UNITCODE, MODULE_TYPE, INVEST_CASH_PARTYNAME, MAPPING_FLAG, GROUPING_FLAG, GROUP_NAME, GROUP_CODE, PAN_NUMBER, TAN_NUMBER, SERVICE_REGNO, WORK_CONTRACT_PERCENTAGE, TDS_PERCENTAGE, LABOURCESS, SCREEN_TYPE) Values ('KPIT CUMMINS INFOSYSTEMS LTD', '', '', '', '','7', TO_DATE('08/28/2012', 'MM/DD/YYYY'), '', '0000001186', '',TO_DATE('08/28/2012', 'MM/DD/YYYY'), 'VIDP', 'C', '', 'N','N', '', '', 'NA', '', '', '', '', '', 'P');
DELETE FROM CB_PARTY_BANK_DETAILS WHERE PARTYCODE='0000001180';
DELETE FROM CB_PARTY_INFO WHERE PARTYCODE='0000001180';
Deleting a VOUCHER
----------------------------------------------------------------------------------------------------
DELETE FROM CB_VOUCHER_DETAILS WHERE KEYNO='060812000026';
DELETE FROM CB_VOUCHER_INFO WHERE KEYNO='060812000026';
if MEDICAL Retired Bulk
--------
DELETE FROM CB_VOUCHER_DETAILS WHERE KEYNO='090412000017';
DELETE FROM CB_MEDICAL_DT WHERE KEYNO='090412000017';
DELETE FROM CB_VOUCHER_INFO WHERE KEYNO='090412000017';
DELETE FROM CB_VOUCHER_DETAILS WHERE KEYNO=(select keyno from cb_voucher_info where voucherno='CV/HD31/0019');
DELETE FROM CB_MEDICAL_DT WHERE keyno=(select keyno from cb_voucher_info where voucherno='CV/HD31/0019');
DELETE FROM CB_VOUCHER_INFO WHERE voucherno='CV/HD31/0019';
DELETE FROM CB_VOUCHER_DETAILS WHERE KEYNO=(select keyno from cb_voucher_info where PREPERATION_NO='RST39/3002/12-13');
DELETE FROM CB_MEDICAL_DT WHERE keyno=(select keyno from cb_voucher_info where PREPERATION_NO='RST39/3002/12-13');
DELETE FROM CB_VOUCHER_INFO WHERE PREPERATION_NO='RST39/3002/12-13';
DELETE FROM CB_VOUCHER_DETAILS WHERE KEYNO=(select keyno from cb_voucher_info where PREPERATION_NO='PST39/3579/12-13');
DELETE FROM CB_MEDICAL_DT WHERE keyno=(select keyno from cb_voucher_info where PREPERATION_NO='PSY18/3715/12-13');
DELETE FROM CB_VOUCHER_INFO WHERE PREPERATION_NO='PSY18/3715/12-13';
DELETE FROM CB_JOURNALVOUCHER_DETAILS WHERE KEYNO='060912000001';
DELETE FROM CB_JOURNALVOUCHER WHERE KEYNO='060912000001';
DELETE FROM CB_JOURNALVOUCHER_DETAILS WHERE KEYNO in(select keyno from CB_JOURNALVOUCHER where PREPERATION_NO='JTA/3006/12-13');
DELETE FROM CB_JOURNALVOUCHER WHERE PREPERATION_NO='JTA/3006/12-13';
DELETE FROM CB_JOURNALVOUCHER_DETAILS WHERE KEYNO ='011012000001';
DELETE FROM CB_JOURNALVOUCHER WHERE keyno='011012000001';
DELETE FROM CB_VOUCHER_DETAILS WHERE KEYNO=(select keyno from CB_VOUCHER_INFO where
PREPERATION_NO='PHD31/3090/12-13');
DELETE FROM CB_VOUCHER_INFO WHERE PREPERATION_NO='PHD31/3090/12-13';
ADJUSTMENT VOUCHER DELETION
----------------------------------------------------------------------------------------------------
DELETE FROM CB_VOUCHER_DETAILS WHERE KEYNO IN(SELECT KEYNO FROM CB_VOUCHER_INFO WHERE INSTR(UPPER('110912000001'),UPPER(MAPPINGKEYNO)) > 0);
DELETE FROM CB_VOUCHER_INFO WHERE KEYNO IN(SELECT KEYNO FROM CB_VOUCHER_INFO WHERE INSTR(UPPER('110912000001'),UPPER(MAPPINGKEYNO)) > 0);
DELETE FROM CB_JOURNALVOUCHER_DETAILS WHERE KEYNO IN(SELECT KEYNO FROM CB_JOURNALVOUCHER WHERE INSTR(UPPER('110912000001'),UPPER(MAPPINGKEYNO)) > 0);
DELETE FROM CB_JOURNALVOUCHER WHERE KEYNO IN(SELECT KEYNO FROM CB_JOURNALVOUCHER WHERE INSTR(UPPER('110912000001'),UPPER(MAPPINGKEYNO)) > 0);
DELETE FROM CB_AD_VOUCHER_DETAILS WHERE INSTR(UPPER('110912000001'),UPPER(KEYNO)) > 0;
DELETE FROM CB_AD_VOUCHER_INFO WHERE INSTR(UPPER('110912000001'),UPPER(KEYNO)) > 0;
FOR CHANGING AMOUNT
----------------------------------------------------------------------------------------------------
UPDATE CB_VOUCHER_DETAILS SET DEBIT='3064.0000' WHERE KEYNO='100912000021';
UPDATE CB_VOUCHER_DETAILS SET DEBIT='2897.0000' WHERE KEYNO='080812000005' AND DEBIT='3494.0000';
UPDATE CB_VOUCHER_DETAILS SET ACCOUNTHEAD='628.01',DETAILS=' ',CREDIT='5600.0000' WHERE DEBIT='500.0000' AND KEYNO='080812000009';
UPDATE CB_VOUCHER_DETAILS SET DEBIT='2750.0000' WHERE KEYNO='031012000056';
UPDATE CB_VOUCHER_DETAILS SET DEBIT='1360.0000' WHERE KEYNO='250912000002';
UPDATE CB_VOUCHER_DETAILS SET DEBIT='12000.0000' WHERE KEYNO='081012000072';
UPDATE CB_VOUCHER_INFO SET DETAILS='APRIL TO SEPTEMBER' WHERE KEYNO='081012000072';
UPDATE CB_VOUCHER_DETAILS SET DEBIT='27930.0000' WHERE KEYNO='191012000030' AND ACCOUNTHEAD='732.07';
UPDATE CB_VOUCHER_DETAILS SET CREDIT='3730.0000' WHERE KEYNO='191012000030' AND ACCOUNTHEAD='334.06';
UPDATE CB_VOUCHER_DETAILS SET DEBIT='6500.0000' WHERE KEYNO='081012000072';
UPDATE CB_VOUCHER_DETAILS SET DEBIT='6250.0000' WHERE KEYNO='081012000043';
UPDATE CB_VOUCHER_DETAILS SET DEBIT='6500.0000' WHERE KEYNO='081012000037';
UPDATE CB_VOUCHER_DETAILS SET DEBIT='31549.0000' WHERE KEYNO IN(SELECT KEYNO FROM CB_VOUCHER_INFO WHERE PREPERATION_NO='PST39/3810/12-13') AND ACCOUNTHEAD='732.01';
For Narration Updating
----------------------------------------------------------------------------------------------------
update cb_voucher_info set DETAILS='QUARTLY MEDICAL PAYMENT FOR THE PERIOD APR TO JUN 2012' where keyno='031012000062';
update cb_voucher_info set DETAILS='QUARTLY MEDICAL PAYMENT FOR THE PERIOD APRIL TO JUNE 2012' where keyno='031012000062';
For Inserting New Records Into A New Detail Record using keyno
----------------------------------------------------------------------------------------------------
insert into CB_VOUCHER_DETAILS (KEYNO, ACCOUNTHEAD, MONTH_YEAR, DETAILS, ENTEREDBY, ENTEREDDT, CREDIT, DEBIT, CHEQUENO, UNITCODE, REGARD, ACC_KEYNO, AMOUNTTYPE_CD, EMPNO, CHRONICEMPLOYEENAME, EMPBANKNAME, EMPACCOUNTNO)values ('240712000002', '634.04', '', '', '15', to_date('31-08-2012 15:52:48', 'dd-mm-yyyy hh24:mi:ss'), 0, 120389, '', 'VIDP', '', '', '', '', '', '', '');
insert into CB_VOUCHER_DETAILS (KEYNO, ACCOUNTHEAD, MONTH_YEAR, DETAILS, ENTEREDBY, ENTEREDDT, CREDIT, DEBIT, CHEQUENO, UNITCODE, REGARD, ACC_KEYNO, AMOUNTTYPE_CD, EMPNO, CHRONICEMPLOYEENAME, EMPBANKNAME, EMPACCOUNTNO)values ('240712000002', '633.01', '', '', '15', to_date('31-08-2012 15:52:48', 'dd-mm-yyyy hh24:mi:ss'), 0, 778333, '', 'VIDP', '', '', '', '', '', '', '');
FOR UNAPPROVE
----------------------------------------------------------------------------------------------------
UPDATE CB_VOUCHER_INFO SET APPROVAL='N',APPROVEDDDT='',APPROVALBY='',APPROVEDBY='',VOUCHERNO='' ,RELEASE_DATE='',VOUCHER_DT='' WHERE KEYNO='030912000001';
UPDATE CB_VOUCHER_INFO SET APPROVAL='N',APPROVEDDDT='',APPROVALBY='',APPROVEDBY='',VOUCHERNO='' ,RELEASE_DATE='',VOUCHER_DT='' WHERE KEYNO='020412000026';
UPDATE CB_VOUCHER_INFO SET APPROVAL='N',APPROVEDDDT='',APPROVALBY='',APPROVEDBY='',VOUCHERNO='' ,RELEASE_DATE='',VOUCHER_DT='' WHERE KEYNO='020412000027';
UPDATE CB_VOUCHER_INFO SET APPROVAL='N',APPROVEDDDT='',APPROVALBY='',APPROVEDBY='',VOUCHERNO='',RELEASE_DATE='',VOUCHER_DT='' WHERE KEYNO in(select KEYNO from CB_VOUCHER_INFO where PREPERATION_NO='PST39/3080/12-13');
UPDATE CB_VOUCHER_INFO SET APPROVAL='N',APPROVEDDDT='',APPROVALBY='',APPROVEDBY='',VOUCHERNO='',RELEASE_DATE='',VOUCHER_DT=''
WHERE KEYNO in(select KEYNO from CB_VOUCHER_INFO where PREPERATION_NO='PST39/3081/12-13');
select accountno,PREPERATION_DT,release_date from cb_voucher_info a where a.release_date is not null and accountno='049010200029847' and PREPERATION_DT between '01/May/2012' and '31/May/2012';
UPDATE cb_voucher_info SET release_date=PREPERATION_DT a where a.release_date is not null and accountno='049010200029847' and PREPERATION_DT between '01/May/2012' and '31/May/2012';
----------FOR Journal Voucher Un Approve------------
update CB_JOURNALVOUCHER jv set VOUCHERNO='',VOUCHER_DT='',APPROVAL='N',APPROVEDBY='',APPROVEDDDT='' where keyno='010213000001';
----------FOR DISAPPROVE Journal Voucher AND Generate New Journal Voucher Number ----------
UPDATE CB_JOURNALVOUCHER jv set VOUCHERNO='',VOUCHER_DT='',APPROVAL='N',APPROVEDBY='',APPROVEDDDT='' where keyno='240113000003';
UPDATE CB_JOURNALVOUCHER jv set VOUCHERNO='',VOUCHER_DT='',APPROVAL='N',APPROVEDBY='',APPROVEDDDT='' where keyno='301112000011';
UPDATE CB_JOURNALVOUCHER jv set VOUCHERNO=(SELECT 'JV/CHQ/'||DECODE(TRUSTTYPE,'PreCheck','PC','MEDICAL','MED','TA','TA','PENSION','PEN','COMMON','COMP')||'/'|| LPAD((SELECT nvl(MAX(SUBSTR(voucherno,INSTR(voucherno, '/', -1)+1)),3000) + 1 FROM Cb_Journalvoucher vinfo where vinfo.finyear =info.finyear and vinfo.PREPERATION_DT >= TO_DATE('01/Aug/2012')),4,0) voucherno FROM Cb_Journalvoucher info WHERE upper(keyno)= upper('240113000003')),VOUCHER_DT='31/Dec/2012',APPROVAL='Y',APPROVEDBY='3',APPROVEDDDT='31/Dec/2012' where keyno='240113000003';
UPDATE CB_JOURNALVOUCHER jv set VOUCHERNO=(SELECT 'JV/CHQ/'||DECODE(TRUSTTYPE,'PreCheck','PC','MEDICAL','MED','TA','TA','PENSION','PEN','COMMON','COMP')||'/'|| LPAD((SELECT nvl(MAX(SUBSTR(voucherno,INSTR(voucherno, '/', -1)+1)),3000) + 1 FROM Cb_Journalvoucher vinfo where vinfo.finyear =info.finyear and vinfo.PREPERATION_DT >= TO_DATE('01/Aug/2012')),4,0) voucherno FROM Cb_Journalvoucher info WHERE upper(keyno)= upper('301112000011')),VOUCHER_DT='30/Nov/2012',APPROVAL='Y',APPROVEDBY='3',APPROVEDDDT='30/Nov/2012' where keyno='301112000011';
-----ADJUSTMENT voucher UNAPPROVE -------------
UPDATE cb_ad_voucher_info SET VOUCHERNO='',VOUCHER_DT='',APPROVEDBY='',APPROVAL='N' WHERE keyno='190912000001';
---------------AND--------
UPDATE CB_AD_VOUCHER_INFO SET VOUCHER_DT='', APPROVAL='N', APPROVEDBY='', VOUCHERNO='' WHERE KEYNO='190912000001';
UPDATE CB_VOUCHER_INFO SET VOUCHER_DT='', APPROVAL='N', APPROVEDBY='', VOUCHERNO='' WHERE MAPPINGKEYNO ='190912000001';
UPDATE CB_JOURNALVOUCHER SET VOUCHER_DT='', APPROVAL='N', APPROVEDBY='', VOUCHERNO='' WHERE MAPPINGKEYNO ='190912000001';
Updating the Release Dates
----------------------------------------------------------------------------------------------------
UPDATE CB_VOUCHER_INFO SET RELEASE_DATE='05/May/2012', VOUCHER_DT='05/May/2012' WHERE KEYNO='230412000009';
UPDATE CB_VOUCHER_INFO SET RELEASE_DATE='05/Jun/2012', VOUCHER_DT='05/Jun/2012' WHERE KEYNO='160512000020';
UPDATE CB_VOUCHER_INFO SET RELEASE_DATE='24/Jul/2012', VOUCHER_DT='24/Jul/2012' WHERE KEYNO='240712000003';
UPDATE CB_VOUCHER_INFO SET RELEASE_DATE=PREPERATION_DT, VOUCHER_DT=PREPERATION_DT WHERE KEYNO='150512000009';
UPDATE CB_VOUCHER_INFO SET RELEASE_DATE=PREPERATION_DT, VOUCHER_DT=PREPERATION_DT WHERE KEYNO='160512000024';
SCRIPTS for Party Master
----------------------------------------------------------------------------------------------------
conn epis/epis@pension;
select partycode from cb_party_info where partyname='PPPP';
conn pendept/pendept@pension;
select partycode from cb_party_info where partyname='PPPP';
conn ta/ta@pension;
select partycode from cb_party_info where partyname='PPPP';
conn common/common@pension;
select partycode from cb_party_info where partyname='PPPP';
conn epis/epis@pension;
select count(*) from cb_party_info;
conn pendept/pendept@pension;
select count(*) from cb_party_info;
conn ta/ta@pension;
select count(*) from cb_party_info;
conn common/common@pension;
select count(*) from cb_party_info;
conn epis/epis@pension;
select max(partycode) from cb_party_info;
select max(partycode) from cb_party_bank_details;
conn pendept/pendept@pension;
select max(partycode) from cb_party_info;
select max(partycode) from cb_party_bank_details;
conn ta/ta@pension;
select max(partycode) from cb_party_info;
select max(partycode) from cb_party_bank_details;
conn common/common@pension;
select max(partycode) from cb_party_info;
select max(partycode) from cb_party_bank_details;
DUPLICATE PREPARATION NUMBERS QUERY
----------------------------------------------------------------------------------------------------
Select keyno,
accountno,
preperation_no,
to_char(preperation_dt, 'dd/Mon/yyyy') preperation_dt,
to_char(ENTEREDDT, 'dd/Mon/yyyy hh:mi AM') ENTEREDDT
from Cb_VOUCHER_INFO
where preperation_no in (select preperation_no
from Cb_VOUCHER_INFO voucher
group by preperation_no, accountno
having count(*) > 1)
order by preperation_no
Select *
from Cb_VOUCHER_INFO
where preperation_no in (Select preperation_no
from Cb_VOUCHER_INFO voucher
group by preperation_no, accountno
having count(*) > 1) order by preperation_no
DUPLICATE VOUCHER NO. QUERY
----------------------------------------------------------------------------------------------------
select keyno,accountno,voucherno
from cb_voucher_info
where voucherno in (select voucherno
from cb_voucher_info voucher
group by accountno, voucherno
having count(*) > 1) order by voucherno
select *
from cb_voucher_info
where voucherno in (select voucherno
from cb_voucher_info voucher
group by accountno, voucherno
having count(*) > 1) order by voucherno
UPDATE CB_VOUCHER_INFO SET PREPERATION_NO = (select vouchertype || bankcode || '/' || sno || '/' || GET_Current_FINYEAR_VAL(to_char(sysdate,'dd/Mon/yyyy')) preperationno from (SELECT 'P' vouchertype,(select BANKCODE from cb_bank_info bank where bank.accountno = '91103150000018') bankcode,LPAD((SELECT to_number(nvl(max(SUBSTR(PREPERATION_NO, instr(PREPERATION_NO, '/') + 1,instr(reverse(PREPERATION_NO),'/') - 2)),3000) + 1) FROM Cb_VOUCHER_INFO vinfo where vinfo.vouchertype = 'P' and vinfo.fyear = '2012-13' and vinfo.accountno ='91103150000018' and vinfo.PREPERATION_DT >=TO_DATE('01/Aug/2012')),4,0) sno from dual) dual) where keyno = '160812000001';
Insert into CB_VOUCHER_INFO(KEYNO, ACCOUNTNO, FYEAR, TRUSTTYPE, VOUCHERTYPE, PARTYTYPE, DETAILS, EMP_PARTY_CODE, VOUCHERNO, PREPAREDBY,
CHECKEDBY, APPROVEDBY, APPROVAL, VOUCHER_DT, ENTEREDBY, ENTEREDDT, PREPERATION_DT, ECPFACNO, EREGION, PFIDFLAG,
EMPLOYEENAME, TRANSACTIONTYPE, EDITEDBY, EDITEDDT, UNITCODE, APPROVALBY, APPROVEDDDT, EUNITCODE, OTHERMODULELINK, RECONCILE_STATUS,
TRANSID, PURPOSETYPE, ACCOUNTFLAG, EMPACCOUNTFLAG, STATIONNAME,
CHECKEDDT, APPROVAL_CHECK, EMPNO, MEDICALNO, CANCEL_VOUCHER_DATE,
CANCEL_REMARKS, CANCEL_STATUS, MAPPINGKEYNO, TYPEOF_TRAVEL, RELEASE_DATE,
HOSPITAL_CODE, MEDICAL_TYPE, QUARTER1, QUARTER2, QUARTER3,
QUARTER4, LIMIT, ADMISSION_TYPE, PAYABLE_PARTY, RTGSCHECKNO,
PREPERATION_NO)
Values
((
select to_char(to_date('06/Aug/2012','dd/mon/yyyy'),'ddmmyy')|| Lpad(Nvl(max(substr(KEYNO,7)),0)+1,6,'0') voucherNo from Cb_VOUCHER_INFO where to_char(to_date('06/Aug/2012','dd/mon/yyyy'),'ddmmyy')=substr(KEYNO,0,6)
) , '11084236539', '2012-13', 'MEDICAL', 'P',
'R', 'FROM 14/05/2012 TO 14/05/2012,BILL NO 22476 DATED 14/05/2012', '50', '', '9',
'', '4', 'Y', '', '3',
TO_DATE('08/06/2012 11:50:30', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/06/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '--', '', '',
'S C SEKHARI', 'B', '', TO_DATE('08/06/2012 11:50:30', 'MM/DD/YYYY HH24:MI:SS'), 'VIDP',
'4', TO_DATE('08/13/2012 15:14:21', 'MM/DD/YYYY HH24:MI:SS'), '', '', 'N',
'', '', 'N', 'N', '60',
'', 'N', '', '50', '',
'', 'N', '', '', '',
'0000004657', 'I', 'N', 'N', 'N',
'N', '', '', 'H', '',
(select vouchertype || bankcode || '/' || sno || '/' || GET_Current_FINYEAR_VAL(to_char(sysdate, 'dd/Mon/yyyy')) preperationno
from (SELECT 'P' vouchertype,(select BANKCODE from cb_bank_info bank
where bank.accountno = '11084236539') bankcode,
LPAD((SELECT to_number(nvl(max(SUBSTR(PREPERATION_NO,instr(PREPERATION_NO, '/') + 1,
instr(reverse(PREPERATION_NO),'/') - 2)),3000) + 1)
FROM Cb_VOUCHER_INFO vinfo
where vinfo.vouchertype = 'P'
and vinfo.fyear = '2012-13'
and vinfo.accountno = '11084236539'
and vinfo.PREPERATION_DT >= TO_DATE('06/Aug/2012')),
4,
0) sno
from dual) dual));
MEDICAL @ AIMS10 from the attached text file for preparation number AUTO GENERATION
----------------------------------------------------------------------------------------------------
Insert into CB_VOUCHER_INFO (KEYNO, ACCOUNTNO, FYEAR, TRUSTTYPE, VOUCHERTYPE, PARTYTYPE, DETAILS, EMP_PARTY_CODE, VOUCHERNO, PREPAREDBY, CHECKEDBY, APPROVEDBY, APPROVAL, VOUCHER_DT, ENTEREDBY, ENTEREDDT, PREPERATION_DT, ECPFACNO, EREGION, PFIDFLAG, EMPLOYEENAME, TRANSACTIONTYPE, EDITEDBY, EDITEDDT, UNITCODE, APPROVALBY, APPROVEDDDT, EUNITCODE, OTHERMODULELINK, RECONCILE_STATUS, TRANSID, PURPOSETYPE, ACCOUNTFLAG, EMPACCOUNTFLAG, STATIONNAME, CHECKEDDT, APPROVAL_CHECK, EMPNO, MEDICALNO, CANCEL_VOUCHER_DATE, CANCEL_REMARKS, CANCEL_STATUS, MAPPINGKEYNO, TYPEOF_TRAVEL, RELEASE_DATE, HOSPITAL_CODE, MEDICAL_TYPE, QUARTER1, QUARTER2, QUARTER3, QUARTER4, LIMIT, ADMISSION_TYPE, PAYABLE_PARTY, RTGSCHECKNO, PREPERATION_NO) Values ((select to_char(to_date('06/Aug/2012','dd/mon/yyyy'),'ddmmyy')|| Lpad(Nvl(max(substr(KEYNO,7)),0)+1,6,'0') voucherNo from Cb_VOUCHER_INFO where to_char(to_date('06/Aug/2012','dd/mon/yyyy'),'ddmmyy')=substr(KEYNO,0,6)) , '11084236539', '2012-13', 'MEDICAL', 'P', 'R', 'FROM 14/05/2012 TO 14/05/2012,BILL NO 22476 DATED 14/05/2012', '50', '', '9', '', '4', 'Y', '', '3', TO_DATE('08/06/2012 11:50:30', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/06/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '--', '', '', 'S C SEKHARI', 'B', '', TO_DATE('08/06/2012 11:50:30', 'MM/DD/YYYY HH24:MI:SS'), 'VIDP', '4', TO_DATE('08/13/2012 15:14:21', 'MM/DD/YYYY HH24:MI:SS'), '', '', 'N','', '', 'N', 'N', '60', '', 'N', '', '50', '', '', 'N', '', '', '', '0000004657', 'I', 'N', 'N', 'N', 'N', '', '', 'H', '', (select vouchertype || bankcode || '/' || sno || '/' || GET_Current_FINYEAR_VAL(to_char(sysdate, 'dd/Mon/yyyy')) preperationno from (SELECT 'P' vouchertype,(select BANKCODE from cb_bank_info bank where bank.accountno = '11084236539') bankcode, LPAD((SELECT to_number(nvl(max(SUBSTR(PREPERATION_NO,instr(PREPERATION_NO, '/') + 1, instr(reverse(PREPERATION_NO),'/') - 2)),3000) + 1) FROM Cb_VOUCHER_INFO vinfo where vinfo.vouchertype = 'P' and vinfo.fyear = '2012-13' and vinfo.accountno = '11084236539' and vinfo.PREPERATION_DT >= TO_DATE('06/Aug/2012')), 4,0) sno from dual) dual));
Insert into CB_VOUCHER_DETAILS (KEYNO, ACCOUNTHEAD, MONTH_YEAR, DETAILS, ENTEREDBY, ENTEREDDT, CREDIT, DEBIT, CHEQUENO, UNITCODE, REGARD, ACC_KEYNO, AMOUNTTYPE_CD, EMPNO, CHRONICEMPLOYEENAME, EMPBANKNAME, EMPACCOUNTNO) Values ((select to_char(to_date('06/Aug/2012','dd/mon/yyyy'),'ddmmyy')|| Lpad(Nvl(max(substr(KEYNO,7)),0),6,'0') voucherNo from Cb_VOUCHER_INFO where to_char(to_date('06/Aug/2012','dd/mon/yyyy'),'ddmmyy')=substr(KEYNO,0,6)), '732.07', '', 'S C SEKHARI', '3', TO_DATE('08/06/2012 11:50:31', 'MM/DD/YYYY HH24:MI:SS'),0,11836,' ', 'VIDP','','','','','','','');
Insert into CB_VOUCHER_DETAILS (KEYNO, ACCOUNTHEAD, MONTH_YEAR, DETAILS, ENTEREDBY, ENTEREDDT, CREDIT, DEBIT, CHEQUENO, UNITCODE, REGARD, ACC_KEYNO, AMOUNTTYPE_CD, EMPNO, CHRONICEMPLOYEENAME, EMPBANKNAME, EMPACCOUNTNO) Values ((select to_char(to_date('06/Aug/2012','dd/mon/yyyy'),'ddmmyy')|| Lpad(Nvl(max(substr(KEYNO,7)),0),6,'0') voucherNo from Cb_VOUCHER_INFO where to_char(to_date('06/Aug/2012','dd/mon/yyyy'),'ddmmyy')=substr(KEYNO,0,6)), '334.05', '', ' ', '3', TO_DATE('08/06/2012 11:50:31', 'MM/DD/YYYY HH24:MI:SS'), 1578, 0,' ','VIDP','','','','','','','');
Insert into CB_VOUCHER_DETAILS (KEYNO, ACCOUNTHEAD, MONTH_YEAR, DETAILS, ENTEREDBY, ENTEREDDT, CREDIT, DEBIT, CHEQUENO, UNITCODE, REGARD, ACC_KEYNO, AMOUNTTYPE_CD, EMPNO, CHRONICEMPLOYEENAME, EMPBANKNAME, EMPACCOUNTNO)
Values ('030412000034', '334.00', '', ' ', '3', sysdate, 3675.00, 0,' ','VIDP','','','','','','','');
in TA @ AIMS10
----------------------------------------------------------------------------------------------------
UPDATE CB_JOURNALVOUCHER SET approval = 'Y',approvedby = '4',approvedddt = '29/Aug/2012',voucherno ='JV/CHQ/'|| (SELECT NVL (MAX (TO_NUMBER (VOUCHER_MAX)), 0) + 1 FROM COMMON.CB_JOURNALVOUCHER_GENERATION vinfo WHERE keyno = UPPER ('290812000001')),voucher_dt = '29/Aug/2012' WHERE keyno = '290812000001';
In COMMON @ AIMS10
----------------------------------------------------------------------------------------------------
UPDATE CB_JOURNALVOUCHER_GENERATION set VOUCHER_MAX=(SELECT NVL (MAX (TO_NUMBER (voucher_max)), 0) + 1 FROM cb_journalvoucher_generation vinfo) where status='A';
after that Send Output from TA
select * from CB_JOURNALVOUCHER WHERE keyno = '290812000001';
and from Common
select * from cb_journalvoucher_generation;
--------------Preperation Number Generation Queries- to update next Sequence ----------------
---L2000 Pre Query for Payment Voucher
update cb_voucher_info set preperation_no=(select vouchertype || bankcode || '/' || sno || '/' ||GET_CURRENT_FINYEAR_VAL(to_char(sysdate, 'dd/Mon/yyyy')) preperationno from (SELECT 'P' vouchertype,(select BANKCODE from cb_bank_info bank where bank.accountno = '000381400000988') bankcode,LPAD((SELECT to_number(nvl(max(SUBSTR(PREPERATION_NO,instr(PREPERATION_NO, '/') + 1,instr(reverse(PREPERATION_NO),'/') - 2)),0)+1) FROM Cb_VOUCHER_INFO vinfo where vinfo.vouchertype = 'P' and vinfo.fyear ='2012-13' and vinfo.accountno = '000381400000988' and vinfo.PREPERATION_DT<TO_DATE('01/Aug/2012')),4,0) sno from dual) dual)
where keyno='190712000007';
---G2000 Pre Query for Payment Voucher
update cb_voucher_info set preperation_no=(select vouchertype || bankcode || '/' || sno || '/' ||GET_Current_FINYEAR_VAL(to_char(sysdate, 'dd/Mon/yyyy')) preperationno from (SELECT 'P' vouchertype,(select BANKCODE from cb_bank_info bank where bank.accountno = '000381400000988') bankcode,LPAD((SELECT to_number(nvl(max(SUBSTR(PREPERATION_NO,instr(PREPERATION_NO, '/') + 1,instr(reverse(PREPERATION_NO),'/') - 2)),3000)+1) FROM Cb_VOUCHER_INFO vinfo where vinfo.vouchertype = 'P' and vinfo.fyear ='2012-13' and vinfo.accountno = '000381400000988' and vinfo.PREPERATION_DT >= TO_DATE('01/Aug/2012')),4,0) sno from dual) dual)
where keyno='130812000035';
---L2000 Pre Query for Receipt Voucher
update cb_voucher_info set preperation_no=(select vouchertype || bankcode || '/' || sno || '/' ||GET_Current_FINYEAR_VAL(to_char(sysdate, 'dd/Mon/yyyy')) preperationno from (SELECT 'R' vouchertype,(select BANKCODE from cb_bank_info bank where bank.accountno = '049010200029847') bankcode,LPAD((SELECT to_number(nvl(max(SUBSTR(PREPERATION_NO,instr(PREPERATION_NO, '/') + 1,instr(reverse(PREPERATION_NO),'/') - 2)),0)+1) FROM Cb_VOUCHER_INFO vinfo where vinfo.vouchertype = 'R' and vinfo.fyear ='2012-13' and vinfo.accountno = '049010200029847' and vinfo.PREPERATION_DT<TO_DATE('01/Aug/2012')),4,0) sno from dual) dual)
where keyno='110712000004';
---G2000 Pre Query for Receipt Voucher
update cb_voucher_info set preperation_no=(select vouchertype || bankcode || '/' || sno || '/' ||GET_Current_FINYEAR_VAL(to_char(sysdate, 'dd/Mon/yyyy')) preperationno from (SELECT 'R' vouchertype,(select BANKCODE from cb_bank_info bank where bank.accountno = '600400300003033') bankcode,LPAD((SELECT to_number(nvl(max(SUBSTR(PREPERATION_NO,instr(PREPERATION_NO, '/') + 1,instr(reverse(PREPERATION_NO),'/') - 2)),3000)+2) FROM Cb_VOUCHER_INFO vinfo where vinfo.vouchertype = 'R' and vinfo.fyear ='2012-13' and vinfo.accountno = '600400300003033' and keyno='280812000009' and vinfo.PREPERATION_DT >= TO_DATE('01/Aug/2012')),4,0) sno from dual) dual)
where keyno='280812000009';
Single update Preperation Number Auto Generation Query
----------------------------------------------------------------------------------------------------
update Cb_Voucher_Info set preperation_no=(case when to_date('01/Apr/2012','dd/Mon/yyyy')>=to_date('01/Aug/2012','dd/Mon/yyyy') then (select vouchertype || bankcode || '/' || sno || '/' ||GET_Current_FINYEAR_VAL(to_char(sysdate, 'dd/Mon/yyyy')) preperationno from (SELECT 'P' vouchertype,(select BANKCODE from cb_bank_info bank where bank.accountno = '000381400000988') bankcode,LPAD((SELECT to_number(nvl(max(SUBSTR(PREPERATION_NO,instr(PREPERATION_NO, '/') + 1,instr(reverse(PREPERATION_NO),'/') - 2)),3000)+1) FROM Cb_VOUCHER_INFO vinfo where vinfo.vouchertype = 'P' and vinfo.fyear ='2012-13' and vinfo.accountno = '000381400000988' and vinfo.PREPERATION_DT >= TO_DATE('01/Aug/2012')),4,0) sno from dual) dual) else (select vouchertype || bankcode || '/' || sno || '/' ||GET_CURRENT_FINYEAR_VAL(to_char(sysdate, 'dd/Mon/yyyy')) preperationno from (SELECT 'P' vouchertype,(select BANKCODE from cb_bank_info bank where bank.accountno = '000381400000988') bankcode,LPAD((SELECT to_number(nvl(max(SUBSTR(PREPERATION_NO,instr(PREPERATION_NO, '/') + 1,instr(reverse(PREPERATION_NO),'/') - 2)),0)+1) FROM Cb_VOUCHER_INFO vinfo where vinfo.vouchertype = 'P' and vinfo.fyear ='2012-13' and vinfo.accountno = '000381400000988' and vinfo.PREPERATION_DT<TO_DATE('01/Aug/2012')),4,0) sno from dual) dual) end),PREPERATION_DT=to_date(SYSDATE,'dd/Mon/yyyy') where keyno='190712000007';
select keyno,preperation_no,to_char(PREPERATION_DT,'dd/Mon/yyyy')PREPERATION_DT,voucherno,to_char(voucher_dt,'dd/Mon/yyyy')voucher_dt from cb_voucher_info where keyno='190712000007';
----for party name and all fileds Query
Select KEYNO,voucher.preperation_no preperation_no,to_char(preperation_dt, 'dd/Mon/YYYY') preperation_dt,(case when (voucherNo is null and MAPPINGKEYNO is not null) then '--' when (voucherNo is null and MEDICAL_TYPE <>'S') then '--' when (voucherNo is null and VOUCHERTYPE='P' and approval='Y' and RELEASE_DATE is null) then '--' else voucherNo end) voucherNo, to_char(VOUCHER_DT, 'dd/MON/yyyy') voucher_dt,(case when (voucher.PARTYTYPE='E' and voucher.MEDICAL_TYPE='I' and PAYABLE_PARTY='H') then (select nvl(PARTYNAME,'--') from cb_party_info where PARTYCODE=voucher.HOSPITAL_CODE) when (voucher.PARTYTYPE='R' and voucher.MEDICAL_TYPE='I' and PAYABLE_PARTY='H') then (select nvl(PARTYNAME,'--') from cb_party_info where PARTYCODE=voucher.HOSPITAL_CODE) when voucher.PARTYTYPE='E' then (select nvl(employeename,'--') from episfin.employeeinfo where emplnumber=voucher.EMPNO) when voucher.PARTYTYPE='R' then (select nvl(employeename,'--') from employee_personal_info where medicalno=voucher.MEDICALNO) when voucher.PARTYTYPE='N' then 'Bulk' else nvl(EMP_PARTY_CODE,'--') end) partyname ,( select case when voucher.vouchertype='P' then sum(dt.debit-dt.credit)||' Dr.' when voucher.vouchertype='A' then sum(dt.debit-dt.credit)||' Dr.' else sum(dt.credit-dt.debit)||' Cr.' end from Cb_voucher_details dt where dt.keyno = voucher.keyno)Amount,BANKNAME,FYEAR,VOUCHERTYPE vtype,Decode(VOUCHERTYPE ,'P','Payment','A','Adjustment','R','Receipt','C','Contra','N','Recovery Note','') VOUCHERTYPE,Decode(PARTYTYPE,'E', 'Employee','P','Party','C','Cash','I','Invest Party','B','Bank','S','Casual','R','Retire Employee','N','Retired Bulk Payment','M','Salarised Chronic/Diagnostic Bulk','') PARTYTYPE,(case when VOUCHER_DT > '08/Jun/2010' and partytype='E' then TRANSID else 1 end) TRANSID,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') status from Cb_VOUCHER_INFO voucher,CB_BANK_INFO bank where bank.ACCOUNTNO = voucher.ACCOUNTNO and
upper(KEYNO)= upper('190712000007') order by voucher.ENTEREDDT desc
MAX Preperation Number +1 Query
----------------------------------------------------------------------------------------------------
select (case when '01/Apr/2012'>=to_date('01/Aug/2012','dd/Mon/yyyy') then (select vouchertype || bankcode || '/' || sno || '/' ||GET_CURRENT_FINYEAR_VAL(to_char(sysdate, 'dd/Mon/yyyy')) preperationno from (SELECT 'P' vouchertype,(select BANKCODE from cb_bank_info bank where bank.accountno = '000381400000988') bankcode,LPAD((SELECT to_number(nvl(max(SUBSTR(PREPERATION_NO,instr(PREPERATION_NO, '/') + 1,instr(reverse(PREPERATION_NO),'/') - 2)),0)+1) FROM Cb_VOUCHER_INFO vinfo where vinfo.vouchertype = 'P' and vinfo.fyear ='2012-13' and vinfo.accountno = '000381400000988' and vinfo.PREPERATION_DT<TO_DATE('01/Aug/2012')),4,0) sno from dual) dual) else (select vouchertype || bankcode || '/' || sno || '/' ||GET_Current_FINYEAR_VAL(to_char(sysdate, 'dd/Mon/yyyy')) preperationno from (SELECT 'P' vouchertype,(select BANKCODE from cb_bank_info bank where bank.accountno = '000381400000988') bankcode,LPAD((SELECT to_number(nvl(max(SUBSTR(PREPERATION_NO,instr(PREPERATION_NO, '/') + 1,instr(reverse(PREPERATION_NO),'/') - 2)),3000)+1) FROM Cb_VOUCHER_INFO vinfo where vinfo.vouchertype = 'P' and vinfo.fyear ='2012-13' and vinfo.accountno = '000381400000988' and vinfo.PREPERATION_DT >= TO_DATE('01/Aug/2012')),4,0) sno from dual) dual) end) from dual
Voucher Search Query
----------------------------------------------------------------------------------------------------
select KEYNO,to_char(VOUCHER_DT, 'dd/MON/yyyy') voucher_dt,to_char(preperation_dt, 'dd/Mon/YYYY') preperation_dt,(case when (voucher.PARTYTYPE='E' and voucher.MEDICAL_TYPE='I' and PAYABLE_PARTY='H') then (select nvl(PARTYNAME,'--') from cb_party_info where PARTYCODE=voucher.HOSPITAL_CODE) when (voucher.PARTYTYPE='R' and voucher.MEDICAL_TYPE='I' and PAYABLE_PARTY='H') then (select nvl(PARTYNAME,'--') from cb_party_info where PARTYCODE=voucher.HOSPITAL_CODE) when voucher.PARTYTYPE='E' then (select nvl(employeename,'--') from episfin.employeeinfo where emplnumber=voucher.EMPNO) when voucher.PARTYTYPE='R' then (select nvl(employeename,'--') from employee_personal_info where medicalno=voucher.MEDICALNO) when voucher.PARTYTYPE='N' then 'Bulk' else nvl(EMP_PARTY_CODE,'--') end) partyname ,(case when (voucherNo is null and MAPPINGKEYNO is not null) then '--' when (voucherNo is null and MEDICAL_TYPE <>'S') then '--' when (voucherNo is null and VOUCHERTYPE='P' and approval='Y' and RELEASE_DATE is null) then '--' else voucherNo end) voucherNo,( select case when voucher.vouchertype='P' then sum(dt.debit-dt.credit)||' Dr.' when voucher.vouchertype='A' then sum(dt.debit-dt.credit)||' Dr.' else sum(dt.credit-dt.debit)||' Cr.' end from Cb_voucher_details dt where dt.keyno = voucher.keyno)Amount,BANKNAME,FYEAR,VOUCHERTYPE vtype,Decode(VOUCHERTYPE ,'P','Payment','A','Adjustment','R','Receipt','C','Contra','N','Recovery Note','') VOUCHERTYPE,Decode(PARTYTYPE,'E', 'Employee','P','Party','C','Cash','I','Invest Party','B','Bank','S','Casual','R','Retire Employee','N','Retired Bulk Payment','M','Salarised Chronic/Diagnostic Bulk','') PARTYTYPE,(case when VOUCHER_DT > '08/Jun/2010' and partytype='E' then TRANSID else 1 end) TRANSID,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') status,voucher.preperation_no preperation_no from Cb_VOUCHER_INFO voucher,CB_BANK_INFO bank where
bank.ACCOUNTNO = voucher.ACCOUNTNO and upper(BANKNAME) like upper(?)
and FYEAR like ? and VOUCHERTYPE like ? and upper(voucher.ACCOUNTNO) like upper(?)
and upper(voucher.EMP_PARTY_CODE) like upper(?)
and voucher_dt >= to_date('01/APR/2012','dd/Mon/yyyy')
and voucher_dt <= to_date('30/APR/2012','dd/Mon/yyyy')
order by voucher.ENTEREDDT desc
For Voucher Number Auto Generation Query
----------------------------------------------------------------------------------------------------
Update CB_VOUCHER_INFO set VOUCHERNO=(case when PREPERATION_DT>=to_date('01/Aug/2012','dd/Mon/yyyy') then (SELECT vouchertype || 'V/' ||(select BANKCODE BANKNAME from cb_bank_info bank where bank.accountno = info.accountno) || '/' || LPAD((SELECT NVL(MAX(TO_NUMBER(VOUCHER_MAX)),3000) + 1 FROM common.cb_voucherno_generation vinfo where vinfo.vouchertype= info.vouchertype and info.fyear=vinfo.fyear and vinfo.STATUS='A' and info.accountno = vinfo.accountno),4,0) FROM Cb_VOUCHER_INFO info WHERE upper(keyno)= upper('190712000007') ) else (SELECT vouchertype || 'V/' ||(select BANKCODE BANKNAME from cb_bank_info bank where bank.accountno = info.accountno) || '/' || LPAD((SELECT NVL(MAX(TO_NUMBER(VOUCHER_MAX)),0) + 1 FROM common.cb_voucherno_generation vinfo where vinfo.vouchertype= info.vouchertype and info.fyear=vinfo.fyear and vinfo.STATUS='B' and info.accountno = vinfo.accountno),4,0) FROM Cb_VOUCHER_INFO info WHERE upper(keyno)= upper('190712000007') ) end),voucher_dt=to_date(SYSDATE,'dd/Mon/yyyy') where upper(keyno)= upper('190712000007');
select keyno,preperation_no,to_char(PREPERATION_DT,'dd/Mon/yyyy')PREPERATION_DT,voucherno,to_char(voucher_dt,'dd/Mon/yyyy')voucher_dt from cb_voucher_info where keyno='190712000007';
----------------------For Party Name and All Fileds Query------------------------------------
Select KEYNO,voucher.preperation_no preperation_no,to_char(preperation_dt, 'dd/Mon/YYYY') preperation_dt,(case when (voucherNo is null and MAPPINGKEYNO is not null) then '--' when (voucherNo is null and MEDICAL_TYPE <>'S') then '--' when (voucherNo is null and VOUCHERTYPE='P' and approval='Y' and RELEASE_DATE is null) then '--' else voucherNo end) voucherNo, to_char(VOUCHER_DT, 'dd/MON/yyyy') voucher_dt,(case when (voucher.PARTYTYPE='E' and voucher.MEDICAL_TYPE='I' and PAYABLE_PARTY='H') then (select nvl(PARTYNAME,'--') from cb_party_info where PARTYCODE=voucher.HOSPITAL_CODE) when (voucher.PARTYTYPE='R' and voucher.MEDICAL_TYPE='I' and PAYABLE_PARTY='H') then (select nvl(PARTYNAME,'--') from cb_party_info where PARTYCODE=voucher.HOSPITAL_CODE) when voucher.PARTYTYPE='E' then (select nvl(employeename,'--') from episfin.employeeinfo where emplnumber=voucher.EMPNO) when voucher.PARTYTYPE='R' then (select nvl(employeename,'--') from employee_personal_info where medicalno=voucher.MEDICALNO) when voucher.PARTYTYPE='N' then 'Bulk' else nvl(EMP_PARTY_CODE,'--') end) partyname ,( select case when voucher.vouchertype='P' then sum(dt.debit-dt.credit)||' Dr.' when voucher.vouchertype='A' then sum(dt.debit-dt.credit)||' Dr.' else sum(dt.credit-dt.debit)||' Cr.' end from Cb_voucher_details dt where dt.keyno = voucher.keyno)Amount,BANKNAME,FYEAR,VOUCHERTYPE vtype,Decode(VOUCHERTYPE ,'P','Payment','A','Adjustment','R','Receipt','C','Contra','N','Recovery Note','') VOUCHERTYPE,Decode(PARTYTYPE,'E', 'Employee','P','Party','C','Cash','I','Invest Party','B','Bank','S','Casual','R','Retire Employee','N','Retired Bulk Payment','M','Salarised Chronic/Diagnostic Bulk','') PARTYTYPE,(case when VOUCHER_DT > '08/Jun/2010' and partytype='E' then TRANSID else 1 end) TRANSID,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') status from Cb_VOUCHER_INFO voucher,CB_BANK_INFO bank where bank.ACCOUNTNO = voucher.ACCOUNTNO and
upper(KEYNO)= upper('190712000007') order by voucher.ENTEREDDT desc
JOURNAL VOUCHER Auto GENERATION Query
----------------------------------------------------------------------------------------------------
In EPIS@ AIMS10
----------------------
L2000
--------------
UPDATE cb_journalvoucher SET VOUCHERNO=( SELECT 'JV/CHQ/' || LPAD((SELECT NVL(MAX(TO_NUMBER(VOUCHER_MAX)), 0) + 1 FROM common.cb_journalvoucher_generation vinfo where info.finyear = vinfo.fyear and vinfo.STATUS = 'B'), 4,0) FROM cb_journalvoucher info WHERE upper(keyno) = upper('160612000001')) WHERE KEYNO='160612000001';
SELECT VOUCHERNO FROM cb_journalvoucher WHERE KEYNO='160612000001';
G2000
---------
UPDATE cb_journalvoucher SET VOUCHERNO=( SELECT 'JV/CHQ/' || LPAD((SELECT NVL(MAX(TO_NUMBER(VOUCHER_MAX)), 3000) + 1 FROM common.cb_journalvoucher_generation vinfo where info.finyear = vinfo.fyear and vinfo.STATUS = 'B'), 4,0) FROM cb_journalvoucher info WHERE upper(keyno) = upper('160612000001')) WHERE KEYNO='160612000001';
SELECT VOUCHERNO FROM cb_journalvoucher WHERE KEYNO='160612000001';
After that in execute for max voucher no update required in voucherno generation query
IN COMMON@ AIMS10
----------------------------------
L2000--------
UPDATE CB_JOURNALVOUCHER_GENERATION SET VOUCHER_MAX=VOUCHER_MAX+1 WHERE STATUS='B';
G2000----
UPDATE CB_JOURNALVOUCHER_GENERATION SET VOUCHER_MAX=VOUCHER_MAX+1 WHERE STATUS='A';
First and Last Date Query for the current year
----------------------------------------------------------------------------------------------------
select to_char(to_date(to_char((sysdate - to_char(sysdate, 'dd') + 1),
'dd-mon-yyyy')),
'DD/Mon/YYYY') "First Day",
to_char(Last_day(sysdate), 'DD') " Last day"
from dual
select Last_day(sysdate) from dual
select sysdate+5-(sysdate+5) from dual
select to_date('01/Jan/2012') - to_date('01/May/2012') from dual
select numtodsinterval(to_date('31/Mar/2012')-to_date('01/Jan/2012'),'day') time_difference from dual;
Voucher max Number Function
----------------------------------------------------------------------------------------------------
/* Formatted on 2012/09/06 15:25 (Formatter Plus v4.8.8) */
/*SELECT get_nextvoucherno ('210412000005','01/Jun/2012','859937401','P') vouchno FROM DUAL */
CREATE OR REPLACE FUNCTION get_nextvoucherno (
p_keyno varchar,
p_preperation_date VARCHAR
--p_accountno VARCHAR,
-- p_voucherType VARCHAR
)
RETURN VARCHAR
IS
v_query VARCHAR (2000);
v_autocode VARCHAR (50);
v_autonum varchar2(20);
v_autocode_status varchar2(20);
v_voucherformat varchar2(20);
BEGIN
SELECT (CASE WHEN p_preperation_date >= TO_DATE ('01/Aug/2012', 'dd/Mon/yyyy') THEN 'G2000' ELSE 'L2000' END) voucompare into v_autocode_status FROM cb_voucher_info WHERE keyno = p_keyno;
--dbms_output.put_line('v_autocode_status: '||v_autocode_status);
If (v_autocode_status = 'G2000') Then
v_query := 'SELECT vouchertype || ''V/'' ||(select BANKCODE BANKNAME from cb_bank_info bank where bank.accountno = info.accountno) || ''/'' || LPAD((SELECT NVL(MAX(TO_NUMBER(VOUCHER_MAX)),3000) + 1 FROM common.cb_voucherno_generation vinfo where vinfo.vouchertype= info.vouchertype and info.fyear=vinfo.fyear and vinfo.STATUS=''A'' and info.accountno = vinfo.accountno),4,0) FROM Cb_VOUCHER_INFO info WHERE upper(keyno)= upper('''||p_keyno||''')';
-- dbms_output.put_line(v_autocode_status||' IF Cond v_query: '||v_query);
Elsif v_autocode_status = 'L2000' Then
v_query := 'SELECT vouchertype || ''V/'' ||(select BANKCODE BANKNAME from cb_bank_info bank where bank.accountno = info.accountno) || ''/'' || LPAD((SELECT NVL(MAX(TO_NUMBER(VOUCHER_MAX)),0) + 1 FROM common.cb_voucherno_generation vinfo where vinfo.vouchertype= info.vouchertype and info.fyear=vinfo.fyear and vinfo.STATUS=''B'' and info.accountno = vinfo.accountno),4,0) FROM Cb_VOUCHER_INFO info WHERE upper(keyno)= upper('''||p_keyno||''')';
--dbms_output.put_line(v_autocode_status ||'ELSE v_query: '||v_query);
End If;
EXECUTE IMMEDIATE v_query INTO v_autocode;
--dbms_output.put_line('VACD : '||substr(v_autocode,0,length(v_autocode)-4));
v_voucherformat:=substr(v_autocode,0,length(v_autocode)-4);
v_autonum:=lpad(to_number(nvl(substr(v_autocode,length(v_autocode)-3,length(v_autocode)),0)),4,0);
v_autocode:=v_voucherformat||''||v_autonum;
dbms_output.put_line('v_autocode: '||v_autocode);
RETURN v_autocode;
END;
/
AFTER THAT in COMMON Execute the below UPDATE Query for next VoucherNo Generation
----------------------------------------------------------------------------------------------------
UPDATE COMMON.CB_VOUCHERNO_GENERATION
SET voucher_max = voucher_max + 1 WHERE accountno = '' AND status = '' and vouchertype=''
Query for Comparing Preperation Number and Preperation Dates Comparision
----------------------------------------------------------------------------------------------------
SELECT KEYNO,PREPERATION_NO,TO_CHAR(PREPERATION_DT,'DD/MON/YYYY')PREPDATE
FROM CB_VOUCHER_INFO
WHERE VOUCHERTYPE = 'R'
AND ACCOUNTNO = '600400300003033'
AND PREPERATION_DT> '01/AUG/2012'
ORDER BY PREPERATION_NO
select keyno,
preperation_no,
to_char(preperation_dt, 'dd/Mon/yyyy') prepdate,voucherno
from cb_voucher_info
where accountno = '600400300003033'
and preperation_dt > '01 aug 2012'
and vouchertype = 'R'
order by to_number(SUBSTR(PREPERATION_NO,
instr(PREPERATION_NO, '/') + 1,
instr(reverse(PREPERATION_NO), '/') - 2))
Query for Wrong Series generated for Preperation number when preperation_dt<'01/Aug/2012'
----------------------------------------------------------------------------------------------------
select info.keyno,
info.preperation_no,
to_char(info.preperation_dt, 'dd/Mon/yyyy') prepdate
from cb_voucher_info info, cb_bank_info bank
where info.accountno = bank.accountno
-- and info.accountno = '006502000008111'
and info.preperation_dt < '01/Aug/2012'
and info.preperation_no like
(info.vouchertype || bank.ifsccode || '/3%')
order by info.preperation_no, info.preperation_dt
Query for Wrong Series generated for Preperation number when preperation_dt>='01/Aug/2012'
----------------------------------------------------------------------------------------------------
select info.keyno,
info.preperation_no,
to_char(info.preperation_dt, 'dd/Mon/yyyy') prepdate
from cb_voucher_info info, cb_bank_info bank
where info.accountno = bank.accountno
-- and info.accountno = '006502000008111'
and info.preperation_dt > '01/Aug/2012'
and info.preperation_no like
(info.vouchertype || bank.ifsccode || '/0%')
order by info.preperation_no, info.preperation_dt
Foreign Key Constraint adding in a table
----------------------------------------------------------------------------------------------------
UPDATE CB_VOUCHER_INFO SET TRUSTTYPE='';
ALTER TABLE cb_voucher_info ADD CONSTRAINT cb_voucher_fk FOREIGN KEY (trusttype) REFERENCES invest_trusttype (trusttype);
UPDATE CB_VOUCHER_INFO SET TRUSTTYPE='PENSION';
constraint Dropping and Adding
----------------------------------------------------------------------------------------------------
alter table CB_AD_VOUCHER_DETAILS
drop constraint FK_ADVOUCHER_KEYNO;
alter table CB_AD_VOUCHER_DETAILS
add constraint FK_ADVOUCHER_KEYNO foreign key (KEYNO)
references cb_ad_voucher_info (KEYNO);
Some Select Queries for other module related
----------------------------------------------------------------------------------------------------
select keyno,accountno,preperation_no,to_char(preperation_dt, 'dd/Mon/yyyy') preperation_dt,voucherno, othermodulelink from cb_voucher_info where keyno='120413000001';
select keyno,
accountno,
preperation_no,
to_char(preperation_dt, 'dd/Mon/yyyy') preperation_dt,
voucherno,
othermodulelink
from cb_voucher_info
where accountno = '91103150000018'
order by preperation_no, preperation_dt;
------------------
alter table cb_party_bank_details modify IFSCCODE varchar2(35);
update cb_party_bank_details set IFSCCODE='CH68 0024 3243 3687 3760 G' where partycode in( select PARTYCODE from cb_party_info where partyname like '%DKMA SWITZERLAND%');
Update cb_voucher_info set preperation_dt='03/Oct/2012' where keyno='210912000013';
----------EPIS
Delete from cb_voucher_details where keyno='070812000028' and accounthead='635.07' and rownum=1;
Update cb_voucher_details set DEBIT='17960.0000' where KEYNO='070812000028' and accounthead='635.07' and debit='11960';
Insert into CB_VOUCHER_DETAILS (KEYNO, ACCOUNTHEAD, MONTH_YEAR, DETAILS, ENTEREDBY, ENTEREDDT, CREDIT, DEBIT, CHEQUENO, UNITCODE, REGARD, ACC_KEYNO, AMOUNTTYPE_CD, EMPNO, CHRONICEMPLOYEENAME, EMPBANKNAME, EMPACCOUNTNO)values ('070812000028', '640.07', '', '', '15', sysdate, 0, 11839, '', 'VIDP', '', '', '', '', '', '', '');
----------MEDICAL
update employee_personal_info set dateofseperation_reason='Retirement' Where medicalnno='595';
PARTY MASTER SCRIPT FOR ALL DATABASES
----------------------------------------------------------------------------------------------------
Alter table cb_party_bank_details add (SWIFTCODE varchar2(40));
MEDICAL NO SCRIPT NEW in MEDICAL@AIMS10
----------------------------------------------------------------------------------------------------
insert into EMPLOYEE_PERSONAL_INFO (EMPLOYEENO, EMPLOYEENAME, MEDICALNO, DATEOFSEPERATION_REASON, EMPAUTOID)
values ('SC1800', 'SHANKAR CHAUDHARY','DM-SC1800','Retirement', GET_NEXTCODE('employee_personal_info','EMPAUTOID',10));
For User Name Changing and Disable then Enable constraints
----------------------------------------------------------------------------------------------------
Alter Table EPIS_ACCESSRIGHTS DISABLE CONSTRAINT FK_USERID;
update EPIS_USER set USERNAME='JS2082',EMPLOYEENO='JS2082' where USERID='10';
update EPIS_ACCESSRIGHTS set USERID='JS2082' where USERID='JS2028' ;
Alter Table EPIS_ACCESSRIGHTS ENABLE CONSTRAINT FK_USERID;
Deleting Duplicate Account Heads unit wise
----------------------------------------------------------------------------------------------------
Delete from cb_accountcode_details c where accounthead='304.02' and unitcode='70' and rownum=1;
Delete from cb_accountcode_details c where accounthead='377.00' and unitcode='60' and rownum=1;
update CB_ACCOUNTCODE_DETAILS set accounthead=trim(accounthead) where unitcode='60';
update CB_ACCOUNTCODE_DETAILS set unitcode=trim(unitcode) where unitcode='60';
select * from cb_accountcode_details where unitcode='60' order by accounthead
select count(*),accounthead from cb_accountcode_details where unitcode is null group by accounthead having count(*)>1;
Insert into cb_party_info(partycode,partyname,partydetail) values(get_nextcode('cb_party_info','partycode',10),'National Aluminium Company','National Aluminium Company');
Insert into cb_party_info(partycode,partyname,partydetail) values(get_nextcode('cb_party_info','partycode',10),'The Controller (Finance & Accounts)','The Controller (Finance & Accounts)');
Insert into cb_party_info(partycode,partyname,partydetail) values(get_nextcode('cb_party_info','partycode',10),'PAO (MS), Ministry of Information & Technology','PAO (MS), Ministry of Information & Technology');
update cb_voucher_info set partytype='R' where keyno='260412000021';
/*select info.empno,
(select SUM(DEBIT) - SUM(CREDIT)
FROM CB_VOUCHER_DETAILS
WHERE KEYNO = info.keyno) amount
from cb_voucher_info info
where info.voucher_dt = '30 apr 2012'
and info.partytype = 'E'
and accountno = '11084236539';
select info.medicalno,
(select sum(debit) - sum(credit)
from cb_voucher_details
where keyno = info.keyno) amount
from cb_voucher_info info, employee_personal_info personal
where info.medicalno = personal.medicalno
and info.voucher_dt = '30 apr 2012'
and info.partytype = 'R'
and info.accountno = '11084236539';*/
select keyno,accounthead,debit
from cb_voucher_details
where keyno in
(select keyno
from cb_voucher_info
where preperation_dt between '01/Apr/2012' and '30/Apr/2012'
and accountno = '11084236539') order by debit;
update cb_bank_info set UNITNAME='01' where ACCOUNTCODE='628.22';
update cb_voucher_info set STATIONNAME='01' where accountno='CLCA01110002';
UPDATE cb_voucher_info set VOUCHER_DT='01/Jul/2012',RELEASE_DT='01/Jul/2012' WHERE KEYNO='270612000017';
EVENING BANK BOOK DELETION SCRIPTS
=======================================
MEDICAL@AIMS10
---------------
DELETE FROM CB_VOUCHER_DETAILS WHERE KEYNO=(select keyno from cb_voucher_info where payroll_voucherno='SB/0888/12-13');
DELETE FROM CB_VOUCHER_INFO WHERE payroll_voucherno='SB/0888/12-13';
DELETE FROM CB_VOUCHER_DETAILS WHERE KEYNO=(select keyno from cb_voucher_info where payroll_voucherno='SB/0889/12-13');
DELETE FROM CB_VOUCHER_INFO WHERE payroll_voucherno='SB/0889/12-13';
DELETE FROM CB_VOUCHER_DETAILS WHERE KEYNO=(select keyno from cb_voucher_info where payroll_voucherno='SB/0890/12-13');
DELETE FROM CB_VOUCHER_INFO WHERE payroll_voucherno='SB/0890/12-13';
EPIS@AIMS10
---------------
DELETE FROM CB_VOUCHER_DETAILS WHERE KEYNO in(select keyno from cb_voucher_info where payroll_voucherno in( 'SB/0868/12-13');
DELETE FROM CB_VOUCHER_INFO WHERE payroll_voucherno in ('SB/0868/12-13');
DELETE FROM CB_VOUCHER_DETAILS WHERE KEYNO in(select keyno from cb_voucher_info where payroll_voucherno in ('SB/0869/12-13');
DELETE FROM CB_VOUCHER_INFO WHERE payroll_voucherno in('SB/0869/12-13');
VOUCHERNO updation
----------------------------------
Update CB_VOUCHER_INFO set VOUCHERNO=(case when PREPERATION_DT>=to_date('01/Aug/2012','dd/Mon/yyyy') then (SELECT vouchertype || 'V/' ||(select BANKCODE BANKNAME from cb_bank_info bank where bank.accountno = info.accountno) || '/' || LPAD((SELECT NVL(MAX(TO_NUMBER(VOUCHER_MAX)),3000) + 1 FROM common.cb_voucherno_generation vinfo where vinfo.vouchertype= info.vouchertype and info.fyear=vinfo.fyear and vinfo.STATUS='A' and info.accountno = vinfo.accountno),4,0) FROM Cb_VOUCHER_INFO info WHERE upper(keyno)= upper('190712000007') ) else (SELECT vouchertype || 'V/' ||(select BANKCODE BANKNAME from cb_bank_info bank where bank.accountno = info.accountno) || '/' || LPAD((SELECT NVL(MAX(TO_NUMBER(VOUCHER_MAX)),0) + 1 FROM common.cb_voucherno_generation vinfo where vinfo.vouchertype= info.vouchertype and info.fyear=vinfo.fyear and vinfo.STATUS='B' and info.accountno = vinfo.accountno),4,0) FROM Cb_VOUCHER_INFO info WHERE upper(keyno)= upper('190712000007') ) end),voucher_dt=to_date(SYSDATE,'dd/Mon/yyyy') where upper(keyno)= upper('190712000007');
select keyno,preperation_no,to_char(PREPERATION_DT,'dd/Mon/yyyy')PREPERATION_DT,voucherno,to_char(voucher_dt,'dd/Mon/yyyy')voucher_dt from cb_voucher_info where keyno='190712000007';
UPDATE COMMON.CB_VOUCHERNO_GENERATION SET voucher_max = voucher_max + 1 WHERE accountno = '' AND status = '' and vouchertype=''
UPDATE CB_VOUCHER_INFO SET VOUCHERNO = (SELECT vouchertype || 'V/' ||(select BANKCODE BANKNAME from cb_bank_info bank where bank.accountno = info.accountno) || '/' || LPAD((SELECT NVL(MAX(TO_NUMBER(VOUCHER_MAX)), 3000) + 1 FROM COMMON.CB_VOUCHERNO_GENERATION vinfo where vinfo.vouchertype = info.vouchertype and info.fyear = vinfo.fyear and vinfo.STATUS = 'A' and info.accountno = vinfo.accountno),4,0) FROM Cb_VOUCHER_INFO info WHERE upper(keyno) = upper('240712000006')) where upper(keyno) = upper('240712000006');
UPDATE common.CB_VOUCHERNO_GENERATION SET VOUCHER_MAX = VOUCHER_MAX + 1 WHERE accountno = '11084236539' AND status = 'A' and vouchertype='P';
---------------
UPDATE CB_VOUCHER_INFO SET VOUCHERNO = (SELECT vouchertype || 'V/' ||(select BANKCODE BANKNAME from cb_bank_info bank where bank.accountno = info.accountno) || '/' || LPAD((SELECT NVL(MAX(TO_NUMBER(VOUCHER_MAX)), 3000) + 1 FROM COMMON.CB_VOUCHERNO_GENERATION vinfo where vinfo.vouchertype = info.vouchertype and info.fyear = vinfo.fyear and vinfo.STATUS = 'A' and info.accountno = vinfo.accountno),4,0) FROM Cb_VOUCHER_INFO info WHERE upper(keyno) = upper('300712000002')),voucher_dt='02/Aug/2012',RELEASE_DATE='02/Aug/2012' where upper(keyno) = upper('300712000002');
after that immedicately connect to COMMON/AIMS10 and execute the script
UPDATE CB_VOUCHERNO_GENERATION SET voucher_max = voucher_max + 1 WHERE accountno = '11084236539' AND status = 'A' and vouchertype='P';
UPDATE CB_VOUCHER_INFO SET VOUCHERNO = (SELECT vouchertype || 'V/' ||(select BANKCODE BANKNAME from cb_bank_info bank where bank.accountno = info.accountno) || '/' || LPAD((SELECT NVL(MAX(TO_NUMBER(VOUCHER_MAX)), 3000) + 1 FROM COMMON.CB_VOUCHERNO_GENERATION vinfo where vinfo.vouchertype = info.vouchertype and info.fyear = vinfo.fyear and vinfo.STATUS = 'A' and info.accountno = vinfo.accountno),4,0) FROM Cb_VOUCHER_INFO info WHERE upper(keyno) = upper('300712000001')),voucher_dt='02/Aug/2012',RELEASE_DATE='02/Aug/2012' where upper(keyno) = upper('300712000001');
after that immedicately connect to COMMON/AIMS10 and execute the script
UPDATE CB_VOUCHERNO_GENERATION SET voucher_max = voucher_max + 1 WHERE accountno = '11084236539' AND status = 'A' and vouchertype='P';
update cb_journalvoucher set preperation_no=(select 'J' || TRUSTTYPE || '/'|| sno || '/' ||GET_Current_FINYEAR_VAL(to_char(sysdate, 'dd/Mon/yyyy')) preperationno from (select 'COMMON' TRUSTTYPE,LPAD((SELECT nvl(MAX(SUBSTR(PREPERATION_NO,instr(PREPERATION_NO, '/') + 1,instr(reverse(PREPERATION_NO),'/') - 2)),0) + 1 FROM cb_journalvoucher vinfo where vinfo.finyear ='2012-13' and vinfo.PREPERATION_DT < TO_DATE('01/Aug/2012')),4,0) sno from dual) dual) where preperation_no='JCOMMON/0042/12-13';
Preperation Number Sorting inside bank IFSC Code and Fin Years
------------------------------------------------------------------
select SUBSTR(voucher.PREPERATION_NO,instr(PREPERATION_NO, '/') + 1,instr(reverse(voucher.PREPERATION_NO), '/') - 2)vno from cb_voucher_info voucher
select lpad(Nvl(max(keyno), 0) + 1, 12, 0)
from cb_journalvoucher
where deleted_flag = 'Y'
======================================================================================================================
In Control Panel ->File Types->JAR->open-> "C:\Program Files\Java\jdk1.6.0_10\jre\bin\javaw.exe" -jar "%1" %*
======================================================================================================================
CREATE TABLE CB_VOUCHER_LOGS
(
KEYNO VARCHAR2(12),
VKEYNO VARCHAR2(12),
VOUCHERNO VARCHAR2(25),
VOUCHER_DT DATE,
PREPERATION_DT DATE,
PREPERATION_NO VARCHAR2(25),
VOUCHERTOTAL NUMBER(15,4),
ENTEREDDT DATE DEFAULT SYSDATE,
TRUSTTYPE VARCHAR2(20)
);
String voucherLogQuery="Insert Into Cb_Voucher_Logs(KEYNO,VKEYNO,VOUCHERNO,VOUCHER_DT,PREPERATION_DT,PREPERATION_NO,VOUCHERTOTAL,TRUSTTYPE) Values( (select lpad(Nvl(max(keyno), 0)+1,12,0) from cb_voucher_logs),'"+keyno+"','"+voucherNO+"','"+releaseDate+"',(select nvl(preperation_dt, '') preperation_dt from "+department+".CB_VOUCHER_INFO info where keyno = '"+keyno+"'),(select nvl(preperation_no, '') preperation_no from "+department+".CB_VOUCHER_INFO info where keyno = '"+keyno+"'),(select (case when (sum(nvl(DEBIT, 0)) - sum(nvl(CREDIT, 0))) > 0 then (sum(nvl(DEBIT, 0)) - sum(nvl(CREDIT, 0))) when ((sum(nvl(CREDIT, 0)) - sum(nvl(DEBIT, 0)))) > 0 then ((sum(nvl(CREDIT, 0)) - sum(nvl(DEBIT, 0)))) else 0 end) from "+department+".cb_voucher_details where keyno = '"+keyno+"'),(select nvl(trusttype, '') trusttype from "+department+".CB_VOUCHER_INFO info where keyno = '"+keyno+"') )";
Update Cb_voucher_details Set Debit='966184' Where Keyno='010512000058' And Accounthead='736.00';
Insert Into Cb_voucher_details (Keyno, Accounthead, Month_year, Details, Enteredby, Entereddt, Credit, Debit, Chequeno, Unitcode, Regard, Acc_keyno, Amounttype_cd, Empno, Chronicemployeename, Empbankname, Empaccountno)Values ('010512000058', '315.00', '', '', '15', Sysdate, 0, 24, '', 'VIDP', '', '', '', '', '', '', '');
Insert Into Cb_voucher_details (Keyno, Accounthead, Month_year, Details, Enteredby, Entereddt, Credit, Debit, Chequeno, Unitcode, Regard, Acc_keyno, Amounttype_cd, Empno, Chronicemployeename, Empbankname, Empaccountno)Values ('010512000058', '739.50', '', '', '15', Sysdate, 60, 0, '', 'VIDP', '', '', '', '', '', '', '');
UPDATE CB_VOUCHER_DETAILS SET DEBIT='2226' WHERE KEYNO='110313000038' AND ACCOUNTHEAD='732.07';
Update Cb_VOUCHER_INFO set VOUCHER_DT='18/Feb/2013',APPROVAL='Y',APPROVEDBY='6',VOUCHERNO='RV/YE88/3014',APPROVALBY='6',RELEASE_DATE='18/Feb/2013',ApprovedDDT=SYSDATE WHERE KEYNO='180213000011';
Prepration Num :- PSY18/3116/12-13, Amount 262445 280313000003
Prepration Num :- PSY18/4086/12-13 Amount 166223 280313000001
Prepration Num :- PSY18/4087/12-13 Amount 89734 280313000002
DELETE FROM CB_VOUCHER_DETAILS WHERE KEYNO='280313000003';
DELETE FROM CB_VOUCHER_INFO WHERE KEYNO='280313000003';
DELETE FROM CB_VOUCHER_DETAILS WHERE KEYNO='280313000001';
DELETE FROM CB_VOUCHER_INFO WHERE KEYNO='280313000001';
DELETE FROM CB_VOUCHER_DETAILS WHERE KEYNO='280313000002';
DELETE FROM CB_VOUCHER_INFO WHERE KEYNO='280313000002';
Alter Table Tc_arrival_log Add Arr_hopping_status Char(1);
Alter Table Tc_ca12info Add Arr_hopping_status Char(1);
Alter Table Tc_arrival_log Add Deptype Char(1);
Alter Table Tc_ca12info Add Deptype Char(1);
Alter Table Tc_Hopping_Dt Add Status Char(1);
update Tc_Hopping_Dt set status='D' where status is null;
for (var j=0;j<document.forms[1].ArrHoppingDetails.options.length;j++)
{
if(document.forms[1].ArrHoppingDetails.options[j].selected)
{
ArrHoppingDetails+=document.forms[1].ArrHoppingDetails.options[j].value+",";
}
}
if(hoppingA.getElementsByTagName("HopA")[0]){
ArrfrmNew.gcd.value='';
var gcdvalueA='';
for (loop = 0; loop < hoppingA.childNodes.length; loop++) {
hop=hoppingA.childNodes[loop];
arrayresult[loop]=[retNodeValue(hop,'FromLatitude')+'',retNodeValue(hop,'FromLongitude')+'',retNodeValue(hop,'ToLatitude')+'',retNodeValue(hop,'ToLongitude')+'',retNodeValue(hop,'Gcd')+'',retNodeValue(hop,'Rnfc')+'',retNodeValue(hop,'FromPoint')+'',retNodeValue(hop,'ToPoint')+''];
if(loop==hoppingA.childNodes.length-1) {
gcdvalueA=gcdvalueA+retNodeValue(hop,'Gcd');
} else {
gcdvalueA=gcdvalueA+retNodeValue(hop,'Gcd')+",";
}
}
ArrfrmNew.gcd.value=gcdvalueA;
}
UTC and IST Timings and File Read write Delete permissions in server.policy file in config of application folder
-----------------------------------------------------------------------------------------------------------------------------------------
UTC/IST TIMINGS
-----------------------------------------------------------------------------------------------------------------------------------------
SELECT fmuid, ata, flight_no,
TO_DATE ( TO_CHAR (arr_date, 'DD-mon-yyyy')
|| ''
|| DECODE (dep.ata, '', '2359', dep.ata),
'DD-mon-yyyy HH24MI'
)+0.2291
FROM tc_arrival_log dep
WHERE fmuid = '0608010026';
/* Copyright (c) 2002 Sun Microsystems, Inc. All rights reserved. */
/* Use is subject to license terms. */
// Core server classes get all permissions by default
grant codeBase "file:${com.sun.aas.installRoot}/lib/-" {
permission java.security.AllPermission;
};
// iMQ classes get all permissions by default
grant codeBase "file:${com.sun.aas.imqLib}/-" {
permission java.security.AllPermission;
};
// Web Services classes get all permissions by default
grant codeBase "file:${com.sun.aas.webServicesLib}/-" {
permission java.security.AllPermission;
};
// Basic set of required permissions granted to all remaining code
grant {
permission java.lang.RuntimePermission "loadLibrary.*";
permission java.lang.RuntimePermission "queuePrintJob";
permission java.net.SocketPermission "*", "connect";
permission java.io.FilePermission "<<ALL FILES>>", "read,write,delete";
permission java.util.PropertyPermission "*", "read";
permission java.lang.RuntimePermission "modifyThreadGroup";
};
// Following grant block is only required by Connectors. If Connectors
// are not in use the recommendation is to remove this grant.
grant {
permission javax.security.auth.PrivateCredentialPermission "javax.resource.spi.security.PasswordCredential com.sun.enterprise.security.PrincipalImpl \"ANONYMOUS\"", "read";
};
// Following grant block is only required for Reflection. If Reflection
// is not in use the recommendation is to remove this section.
grant {
permission java.lang.RuntimePermission "accessDeclaredMembers";
};
REFRESH CODE
auto refresh of jsp page
Posted by: Krishna Pothula on January 19, 2006 in response to Message #196243
You can use the following JS function. All you have to do is write a Servlet that writes the Counter value to the response.
Pass the Servlet url to this function and fill it in a DIV or an Input field.
<code>
function readContent(urltoRead){
var counter = "";
try {
var xmlHttp = null;
try {
xmlHttp = new ActiveXObject( "Msxml2.XMLHTTP" );
} catch (e) {
xmlHttp = new ActiveXObject( "Microsoft.XMLHTTP" );
}
xmlHttp.open( "GET", urltoRead, false );
// Execute the request
xmlHttp.send(null);
if(xmlHttp.status == 200)
counter = xmlHttp.ResponseText;
else
counter = "Unknown";
} catch(errmsg) {
counter = "Unknown Counter";
}
}
</code>
Posted by: Krishna Pothula on January 19, 2006 in response to Message #196243
You can use the following JS function. All you have to do is write a Servlet that writes the Counter value to the response.
Pass the Servlet url to this function and fill it in a DIV or an Input field.
<code>
function readContent(urltoRead){
var counter = "";
try {
var xmlHttp = null;
try {
xmlHttp = new ActiveXObject( "Msxml2.XMLHTTP" );
} catch (e) {
xmlHttp = new ActiveXObject( "Microsoft.XMLHTTP" );
}
xmlHttp.open( "GET", urltoRead, false );
// Execute the request
xmlHttp.send(null);
if(xmlHttp.status == 200)
counter = xmlHttp.ResponseText;
else
counter = "Unknown";
} catch(errmsg) {
counter = "Unknown Counter";
}
}
</code>
Subscribe to:
Posts (Atom)