DECLARE CURSOR C IS select 'HESSIAN WEAVING' MC_TYPE, LOOM1 MAC_NO, RSP RS, 18 MC_TYPE_SRLNO, 10 DEPT_CODE, 5 COMP_CODE, 'H-LOOM/'||LOOM1 MC_SHORT_DESC, LINE LINE_NO from gjmpay_prod_loom_mast where section ='H' UNION ALL select 'HESSIAN WEAVING' MC_TYPE, LOOM2 MAC_NO, RSP RS, 18 MC_TYPE_SRLNO, 10 DEPT_CODE, 5 COMP_CODE, 'H-LOOM/'||LOOM2 MC_SHORT_DESC, LINE LINE_NO from gjmpay_prod_loom_mast where section ='H' ORDER BY 2; v_INT_MC_NO VARCHAR2(10); BEGIN FOR R IN C LOOP SELECT LPAD(NVL(MAX(TO_NUMBER(INT_MC_NO)), 0)+1, 10, 0) INTO v_INT_MC_NO FROM MREL.MACDET WHERE COMP_CODE = 5; INSERT INTO MREL.MACDET(MC_TYPE, MAC_NO, RS, MC_TYPE_SRLNO, DEPT_CODE, COMP_CODE, MC_SHORT_DESC, INT_MC_NO, LINE_NO) VALUES(R.MC_TYPE, R.MAC_NO, R.RS, R.MC_TYPE_SRLNO, R.DEPT_CODE, R.COMP_CODE, R.MC_SHORT_DESC, v_INT_MC_NO, R.LINE_NO); END LOOP; END;