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;