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;