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;
}
No comments:
Post a Comment