SELECT B.COMP_CODE, SUM(DECODE(B.DR_CR, 'D', B.ADJ_AMOUNT)) DR_AMOUNT, SUM(DECODE(B.DR_CR, 'C', B.ADJ_AMOUNT)) CR_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.VCH_TYPE = B.VCH_TYPE AND A.FIN_YEAR = B.FIN_YEAR AND A.STATUS <> 'C' AND B.ACC_CODE = :ACC_CODE AND A.VCH_DATE > '31-MAR-09' GROUP BY B.COMP_CODE SELECT B.COMP_CODE, SUM(DECODE(B.DR_CR, 'D', B.AMOUNT)) DR_AMOUNT, SUM(DECODE(B.DR_CR, 'C', B.AMOUNT)) CR_AMOUNT FROM ACCOUNTS.ACCOUNTS_VOUCHER_MAST A, ACCOUNTS.ACCOUNTS_VOUCHER_DTLS B WHERE A.FIN_YEAR = B.FIN_YEAR AND A.VCH_NO = B.VCH_NO AND A.VCH_TYPE = B.VCH_TYPE AND A.COMP_CODE = B.COMP_CODE AND B.ACC_CODE IN :ACC_CODE AND A.VCH_DATE > '31-MAR-09' AND A.STATUS <> 'C' GROUP BY B.COMP_CODE SELECT A.VCH_TYPE, A.VCH_DATE, A.DAY_SERIAL, A.VCH_NO, A.COMP_CODE, DECODE(B.DR_CR, 'D', B.AMOUNT) DR_AMOUNT, DECODE(B.DR_CR, 'C', B.AMOUNT) CR_AMOUNT FROM ACCOUNTS.ACCOUNTS_VOUCHER_MAST A, ACCOUNTS.ACCOUNTS_VOUCHER_DTLS B, ACCOUNTS.ACCOUNTS_BILL_DTLS C WHERE A.VCH_NO = B.VCH_NO AND A.COMP_CODE = B.COMP_CODE AND A.VCH_TYPE = B.VCH_TYPE AND B.ACC_CODE = C.ACC_CODE AND B.VCH_NO = C.VCH_NO AND B.COMP_CODE = C.COMP_CODE AND B.VCH_TYPE = C.VCH_TYPE AND A.STATUS <> 'C' AND A.VCH_DATE > '31-MAR-09' AND B.ACC_CODE = :ACC_CODE AND C.SUB_CODE IS NULL SELECT A.VCH_DATE, A.DAY_SERIAL, A.VCH_NO, A.COMP_CODE, B.AMOUNT, SUM(C.ADJ_AMOUNT) FROM ACCOUNTS.ACCOUNTS_VOUCHER_MAST A, ACCOUNTS.ACCOUNTS_VOUCHER_DTLS B, ACCOUNTS.ACCOUNTS_BILL_DTLS C WHERE A.VCH_NO = B.VCH_NO AND A.COMP_CODE = B.COMP_CODE AND A.VCH_TYPE = B.VCH_TYPE AND B.ACC_CODE = C.ACC_CODE AND B.VCH_NO = C.VCH_NO AND B.COMP_CODE = C.COMP_CODE AND B.VCH_TYPE = C.VCH_TYPE AND A.STATUS <> 'C' AND A.VCH_DATE > '31-MAR-09' AND B.ACC_CODE = :ACC_CODE AND B.DR_CR = :DR_CR HAVING NVL(B.AMOUNT, 0) <> NVL(SUM(C.ADJ_AMOUNT), 0) GROUP BY A.VCH_DATE, A.DAY_SERIAL, A.VCH_NO, A.COMP_CODE, B.AMOUNT SELECT B.VCH_NO, B.COMP_CODE, B.VCH_TYPE FROM ACCOUNTS.ACCOUNTS_VOUCHER_MAST A, ACCOUNTS.ACCOUNTS_VOUCHER_DTLS B WHERE A.VCH_NO = B.VCH_NO AND A.COMP_CODE = B.COMP_CODE AND A.VCH_TYPE = B.VCH_TYPE AND A.STATUS <> 'C' AND A.VCH_DATE > '31-MAR-09' AND B.ACC_CODE =:ACC_CODE AND B.DR_CR = :DR_CR MINUS SELECT C.VCH_NO, C.COMP_CODE, C.VCH_TYPE FROM ACCOUNTS.ACCOUNTS_VOUCHER_MAST A, ACCOUNTS.ACCOUNTS_VOUCHER_DTLS B, ACCOUNTS.ACCOUNTS_BILL_DTLS C WHERE A.VCH_NO = B.VCH_NO AND A.COMP_CODE = B.COMP_CODE AND A.VCH_TYPE = B.VCH_TYPE AND B.ACC_CODE = C.ACC_CODE AND B.VCH_NO = C.VCH_NO AND B.COMP_CODE = C.COMP_CODE AND B.VCH_TYPE = C.VCH_TYPE AND A.STATUS <> 'C' AND A.VCH_DATE > '31-MAR-09' AND B.ACC_CODE =:ACC_CODE AND B.DR_CR = :DR_CR DECLARE CURSOR C IS SELECT DESPATCH_CODE, COMP_CODE, OLD_INVOICE_NO, DESPATCH_DATE, BUYER_CODE, INSERTED_BY FROM SALES.SALES_EXPORT_INVOICE_MASTER WHERE FINAL_INVOICE_AMT IS NOT NULL; BEGIN FOR R IN C LOOP SALES.SALES_FBILL_EXP_ACC_POST_NEW(R.DESPATCH_CODE, R.COMP_CODE, R.OLD_INVOICE_NO, R.DESPATCH_DATE, R.BUYER_CODE, R.INSERTED_BY); END LOOP; END;