DECLARE CURSOR C IS SELECT A.BROKER_CODE, A.BROKER_NAME, A.ADDR FROM MREL.MREL_SALES_BROKER_MAST A WHERE AC_HEAD IS NULL ORDER BY 2; v_SUB_CODE VARCHAR2(13); BEGIN FOR R IN C LOOP SELECT LPAD(TO_CHAR(NVL(MAX(TO_NUMBER(NVL(PARTY_CODE,0))),0) + 1),13,'0') INTO v_SUB_CODE FROM ACCOUNTS.ACCOUNTS_PARTY_MASTER; INSERT INTO ACCOUNTS.ACCOUNTS_PARTY_MASTER VALUES(v_SUB_CODE, R.BROKER_CODE, 'R', R.BROKER_NAME, R.ADDR, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'AMOL', SYSDATE, 'AMOL', SYSDATE, '0000000000379', 'Y', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL); INSERT INTO ACCOUNTS.ACCOUNTS_PARTY_OPENING_BALANCE VALUES(v_SUB_CODE, 0, 'C', '09-10', 1, 'AMOL', SYSDATE, 'AMOL', SYSDATE, '0000000000379'); FOR R1 IN (SELECT ACC_CODE FROM ACCOUNTS.ACCOUNTS_ACCOUNT_MAST WHERE P_ACC_CODE = '0000000000196' AND ACC_CODE <> '0000000000379') LOOP INSERT INTO ACCOUNTS.ACCOUNTS_PARTY_GL_DTLS VALUES(v_SUB_CODE, R1.ACC_CODE, 'AMOL', SYSDATE, 'AMOL', SYSDATE); INSERT INTO ACCOUNTS.ACCOUNTS_PARTY_OPENING_BALANCE VALUES(v_SUB_CODE, 0, 'C', '09-10', 3, 'AMOL', SYSDATE, 'AMOL', SYSDATE, R1.ACC_CODE); END LOOP; UPDATE MREL.MREL_SALES_BROKER_MAST SET AC_HEAD = v_SUB_CODE WHERE BROKER_CODE = R.BROKER_CODE; END LOOP; END; SELECT SUM(AMOUNT) FROM MREL.MREL_SALES_BROKER_MAST A, MREL.MREL_SALES_BROKER_DTLS B WHERE A.BROKER_CODE = B.BROKER_CODE AND B.COMP_CODE = :COMP_CODE AND B.ACC_CODE = :ACC_CODE ORDER BY A.BROKER_NAME DECLARE CURSOR C IS SELECT A.BROKER_NAME, A.AC_HEAD, DECODE(SIGN(B.AMOUNT), -1, 'D', 'C') DR_CR, ABS(B.AMOUNT) AMOUNT FROM MREL.MREL_SALES_BROKER_MAST A, MREL.MREL_SALES_BROKER_DTLS B WHERE A.BROKER_CODE = B.BROKER_CODE AND B.COMP_CODE = :COMP_CODE AND B.ACC_CODE = :ACC_CODE ORDER BY A.BROKER_NAME; v_FOUND NUMBER(9); BEGIN FOR R IN C LOOP SELECT COUNT(1) INTO v_FOUND FROM ACCOUNTS.ACCOUNTS_PARTY_OPENING_BALANCE WHERE PARTY_CODE = R.AC_HEAD AND COMP_CODE = :COMP_CODE AND FIN_YEAR = '09-10' AND P_ACC_CODE = :ACC_CODE; IF v_FOUND <> 0 THEN UPDATE ACCOUNTS.ACCOUNTS_PARTY_OPENING_BALANCE SET OPENING_BALANCE = R.AMOUNT, DR_CR = R.DR_CR WHERE PARTY_CODE = R.AC_HEAD AND COMP_CODE = :COMP_CODE AND FIN_YEAR = '09-10' AND P_ACC_CODE = :ACC_CODE; ELSE INSERT INTO ACCOUNTS.ACCOUNTS_PARTY_OPENING_BALANCE VALUES(R.AC_HEAD, R.AMOUNT, R.DR_CR, '09-10', :COMP_CODE, 'AMOL', SYSDATE, 'AMOL', SYSDATE, :ACC_CODE); END IF; END LOOP; END;