SELECT A.COMP_CODE, B.ACC_CODE, B.INSTRUMENT_NO, B.DR_CR,COUNT(B.INSTRUMENT_NO) FROM ACCOUNTS.ACCOUNTS_VOUCHER_MAST A, ACCOUNTS.ACCOUNTS_BILL_DTLS B WHERE A.VCH_NO = B.VCH_NO AND A.COMP_CODE = B.COMP_CODE AND A.STATUS <> 'C' AND A.FIN_YEAR = '09-10' AND B.CLEARED <> 'D' AND A.VCH_TYPE <> 'C' HAVING COUNT(B.INSTRUMENT_NO) > 1 GROUP BY A.COMP_CODE, B.ACC_CODE, B.INSTRUMENT_NO, B.DR_CR ORDER BY A.COMP_CODE SELECT COMP_CODE, TO_CHAR(VCH_DATE, 'MMRRRR') MMRR, CR_DR, DAY_SERIAL, COUNT(1) FROM ACCOUNTS.ACCOUNTS_VOUCHER_MAST WHERE STATUS <> 'C' HAVING COUNT(1) > 1 GROUP BY COMP_CODE, TO_CHAR(VCH_DATE, 'MMRRRR'), CR_DR, DAY_SERIAL DECLARE CURSOR C IS SELECT COMP_CODE, TO_CHAR(VCH_DATE, 'MMRRRR') MMRR, CR_DR, DAY_SERIAL, COUNT(1) FROM ACCOUNTS.ACCOUNTS_VOUCHER_MAST WHERE STATUS <> 'C' HAVING COUNT(1) > 1 GROUP BY COMP_CODE, TO_CHAR(VCH_DATE, 'MMRRRR'), CR_DR, DAY_SERIAL ORDER BY DAY_SERIAL; v_DAY_SERIAL NUMBER; BEGIN FOR R IN C LOOP SELECT NVL(MAX(DAY_SERIAL),0) + 1 INTO v_DAY_SERIAL FROM ACCOUNTS.ACCOUNTS_VOUCHER_MAST WHERE COMP_CODE = R.COMP_CODE AND STATUS = 'D' AND CR_DR = R.CR_DR AND TO_CHAR(VCH_DATE, 'MMRRRR') = R.MMRR; UPDATE ACCOUNTS.ACCOUNTS_VOUCHER_MAST SET DAY_SERIAL = v_DAY_SERIAL WHERE DAY_SERIAL = R.DAY_SERIAL AND COMP_CODE = R.COMP_CODE AND CR_DR = R.CR_DR AND TO_CHAR(VCH_DATE, 'MMRRRR') = R.MMRR AND ROWNUM <= 1; END LOOP; END; SELECT X.COMP_CODE, X.INSTRUMENT_NO, X.VCH_NO, X.DAY_SERIAL, X.AMOUNT, Y.AMOUNT_VCH FROM ( SELECT A.COMP_CODE, B.INSTRUMENT_NO, A.VCH_NO, A.DAY_SERIAL, A.AUTO_TRANS, B.ADJ_AMOUNT AMOUNT FROM ACCOUNTS.ACCOUNTS_VOUCHER_MAST A, ACCOUNTS.ACCOUNTS_BILL_DTLS B WHERE A.VCH_NO = B.VCH_NO AND A.COMP_CODE = B.COMP_CODE AND A.STATUS <> 'C' AND A.FIN_YEAR = '09-10' AND B.CLEARED <> 'D' AND A.VCH_TYPE <> 'C' AND (A.COMP_CODE, B.INSTRUMENT_NO) IN ( SELECT COMP_CODE, INSTRUMENT_NO FROM ( SELECT A.COMP_CODE, B.ACC_CODE, B.INSTRUMENT_NO, B.DR_CR,COUNT(B.INSTRUMENT_NO) FROM ACCOUNTS.ACCOUNTS_VOUCHER_MAST A, ACCOUNTS.ACCOUNTS_BILL_DTLS B WHERE A.VCH_NO = B.VCH_NO AND A.COMP_CODE = B.COMP_CODE AND A.STATUS <> 'C' AND A.FIN_YEAR = '09-10' AND B.CLEARED <> 'D' AND A.VCH_TYPE <> 'C' HAVING COUNT(B.INSTRUMENT_NO) > 1 GROUP BY A.COMP_CODE, B.ACC_CODE, B.INSTRUMENT_NO, B.DR_CR ) ) AND A.AUTO_TRANS = 'T' ) X, ( SELECT A.COMP_CODE, B.INSTRUMENT_NO, A.VCH_NO, A.DAY_SERIAL, A.AUTO_TRANS, B.ADJ_AMOUNT AMOUNT_VCH FROM ACCOUNTS.ACCOUNTS_VOUCHER_MAST A, ACCOUNTS.ACCOUNTS_BILL_DTLS B WHERE A.VCH_NO = B.VCH_NO AND A.COMP_CODE = B.COMP_CODE AND A.STATUS <> 'C' AND A.FIN_YEAR = '09-10' AND B.CLEARED <> 'D' AND A.VCH_TYPE <> 'C' AND (A.COMP_CODE, B.INSTRUMENT_NO) IN ( SELECT COMP_CODE, INSTRUMENT_NO FROM ( SELECT A.COMP_CODE, B.ACC_CODE, B.INSTRUMENT_NO, B.DR_CR,COUNT(B.INSTRUMENT_NO) FROM ACCOUNTS.ACCOUNTS_VOUCHER_MAST A, ACCOUNTS.ACCOUNTS_BILL_DTLS B WHERE A.VCH_NO = B.VCH_NO AND A.COMP_CODE = B.COMP_CODE AND A.STATUS <> 'C' AND A.FIN_YEAR = '09-10' AND B.CLEARED <> 'D' AND A.VCH_TYPE <> 'C' HAVING COUNT(B.INSTRUMENT_NO) > 1 GROUP BY A.COMP_CODE, B.ACC_CODE, B.INSTRUMENT_NO, B.DR_CR ) ) AND A.AUTO_TRANS = 'N' ) Y WHERE X.COMP_CODE = Y.COMP_CODE AND X.INSTRUMENT_NO = Y.INSTRUMENT_NO --AND X.VCH_NO = Y.VCH_NO --AND X.DAY_SERIAL = Y.DAY_SERIAL AND X.AMOUNT <> Y.AMOUNT_VCH ORDER BY X.COMP_CODE, X.INSTRUMENT_NO DECLARE CURSOR C IS SELECT A.COMP_CODE, B.ACC_CODE, B.INSTRUMENT_NO, B.DR_CR,COUNT(B.INSTRUMENT_NO) FROM ACCOUNTS.ACCOUNTS_VOUCHER_MAST A, ACCOUNTS.ACCOUNTS_BILL_DTLS B WHERE A.VCH_NO = B.VCH_NO AND A.COMP_CODE = B.COMP_CODE AND A.STATUS <> 'C' AND A.FIN_YEAR = '09-10' AND B.CLEARED <> 'D' AND A.VCH_TYPE <> 'C' HAVING COUNT(B.INSTRUMENT_NO) > 1 GROUP BY A.COMP_CODE, B.ACC_CODE, B.INSTRUMENT_NO, B.DR_CR ORDER BY A.COMP_CODE; v_DAY_SRL NUMBER(9); BEGIN FOR R IN C LOOP SELECT A.DAY_SERIAL INTO v_DAY_SRL FROM ACCOUNTS.ACCOUNTS_VOUCHER_MAST A, ACCOUNTS.ACCOUNTS_BILL_DTLS B WHERE A.VCH_NO = B.VCH_NO AND A.COMP_CODE = B.COMP_CODE AND A.STATUS <> 'C' AND B.CLEARED <> 'D' AND A.FIN_YEAR = '09-10' AND A.VCH_TYPE <> 'C' AND A.AUTO_TRANS = 'N' AND B.INSTRUMENT_NO = R.INSTRUMENT_NO AND A.COMP_CODE = R.COMP_CODE; UPDATE ACCOUNTS.ACCOUNTS_VOUCHER_MAST SET DAY_SERIAL = v_DAY_SRL, CAN_BY = 'DUPCHQ' WHERE (VCH_NO, COMP_CODE) IN (SELECT A.VCH_NO, A.COMP_CODE FROM ACCOUNTS.ACCOUNTS_VOUCHER_MAST A, ACCOUNTS.ACCOUNTS_BILL_DTLS B WHERE A.VCH_NO = B.VCH_NO AND A.COMP_CODE = B.COMP_CODE AND A.STATUS <> 'C' AND B.CLEARED <> 'D' AND A.FIN_YEAR = '09-10' AND A.VCH_TYPE <> 'C' AND A.AUTO_TRANS = 'T' AND B.INSTRUMENT_NO = R.INSTRUMENT_NO AND A.COMP_CODE = R.COMP_CODE) AND STATUS <> 'C' AND FIN_YEAR = '09-10' AND VCH_TYPE <> 'C' AND AUTO_TRANS = 'T'; UPDATE ACCOUNTS.ACCOUNTS_VOUCHER_MAST SET STATUS = 'C', CAN_BY = 'DUPCHQ' WHERE (VCH_NO, COMP_CODE) IN (SELECT A.VCH_NO, A.COMP_CODE FROM ACCOUNTS.ACCOUNTS_VOUCHER_MAST A, ACCOUNTS.ACCOUNTS_BILL_DTLS B WHERE A.VCH_NO = B.VCH_NO AND A.COMP_CODE = B.COMP_CODE AND A.STATUS <> 'C' AND B.CLEARED <> 'D' AND A.FIN_YEAR = '09-10' AND A.VCH_TYPE <> 'C' AND A.AUTO_TRANS = 'N' AND B.INSTRUMENT_NO = R.INSTRUMENT_NO AND A.COMP_CODE = R.COMP_CODE) AND STATUS <> 'C' AND FIN_YEAR = '09-10' AND VCH_TYPE <> 'C' AND AUTO_TRANS = 'N'; END LOOP; END; DECLARE CURSOR C IS SELECT COMP_CODE, TO_CHAR(VCH_DATE, 'MMRRRR') MMRR, CR_DR, DAY_SERIAL, COUNT(1) FROM ACCOUNTS.ACCOUNTS_VOUCHER_MAST WHERE STATUS <> 'C' HAVING COUNT(1) > 1 GROUP BY COMP_CODE, TO_CHAR(VCH_DATE, 'MMRRRR'), CR_DR, DAY_SERIAL ORDER BY DAY_SERIAL; v_DAY_SERIAL NUMBER; BEGIN FOR R IN C LOOP SELECT NVL(MAX(DAY_SERIAL),0) + 1 INTO v_DAY_SERIAL FROM ACCOUNTS.ACCOUNTS_VOUCHER_MAST WHERE COMP_CODE = R.COMP_CODE AND STATUS = 'D' AND CR_DR = R.CR_DR AND TO_CHAR(VCH_DATE, 'MMRRRR') = R.MMRR; UPDATE ACCOUNTS.ACCOUNTS_VOUCHER_MAST SET DAY_SERIAL = v_DAY_SERIAL WHERE DAY_SERIAL = R.DAY_SERIAL AND COMP_CODE = R.COMP_CODE AND CR_DR = R.CR_DR AND TO_CHAR(VCH_DATE, 'MMRRRR') = R.MMRR AND ROWNUM <= 1; END LOOP; END;