DECLARE CURSOR C IS SELECT A.BUYER_CODE, A.BUYER_NAME, B.ADDR, B.VAT_NO, B.CST_NO, B.PAN_NO, B.TIN_NO, A.SALE_TYPE_CODE FROM MREL.MREL_SALES_BUYER_MAST A, MREL.MREL_SALES_BUYER_DET B WHERE A.BUYER_CODE = B.BUYER_CODE(+) AND A.SALE_TYPE_CODE NOT IN(5) AND A.BUYER_CODE NOT IN (SELECT VENDOR_CODE FROM ACCOUNTS.ACCOUNTS_PARTY_MASTER WHERE FROM_MODULE = 'S') ORDER BY 1; v_SUB_CODE VARCHAR2(13); v_SUB VARCHAR2(13); v_COUNT NUMBER(9); BEGIN FOR R IN C LOOP SELECT COUNT(1) INTO V_COUNT FROM ACCOUNTS.ACCOUNTS_PARTY_MASTER WHERE VENDOR_CODE = R.BUYER_CODE AND FROM_MODULE = 'S'; IF v_COUNT = 0 THEN 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.BUYER_CODE, 'S', R.BUYER_NAME, R.ADDR, NULL, NULL, NULL, NULL, R.PAN_NO, R.VAT_NO, R.CST_NO, NULL, 'AMOL', SYSDATE, 'AMOL', SYSDATE, DECODE(R.SALE_TYPE_CODE, 1, '0000000000172', 2, '0000000000174', 3,'0000000000169', '0000000000173'), 'Y', R.TIN_NO, NULL, NULL, NULL, NULL, NULL, NULL, NULL); INSERT INTO ACCOUNTS.ACCOUNTS_PARTY_OPENING_BALANCE VALUES(v_SUB_CODE, 0, 'D', '09-10', 1, 'AMOL', SYSDATE, 'AMOL', SYSDATE, DECODE(R.SALE_TYPE_CODE, 1, '0000000000172', 2, '0000000000174', 3,'0000000000169', '0000000000173')); INSERT INTO ACCOUNTS.ACCOUNTS_PARTY_OPENING_BALANCE VALUES(v_SUB_CODE, 0, 'D', '09-10', 2, 'AMOL', SYSDATE, 'AMOL', SYSDATE, DECODE(R.SALE_TYPE_CODE, 1, '0000000000172', 2, '0000000000174', 3,'0000000000169', '0000000000173')); INSERT INTO ACCOUNTS.ACCOUNTS_PARTY_OPENING_BALANCE VALUES(v_SUB_CODE, 0, 'D', '09-10', 3, 'AMOL', SYSDATE, 'AMOL', SYSDATE, DECODE(R.SALE_TYPE_CODE, 1, '0000000000172', 2, '0000000000174', 3,'0000000000169', '0000000000173')); UPDATE MREL.MREL_SALES_BUYER_MAST SET ACC_CODE = v_SUB_CODE WHERE BUYER_CODE = R.BUYER_CODE; ELSE SELECT PARTY_CODE INTO v_SUB FROM ACCOUNTS.ACCOUNTS_PARTY_MASTER WHERE VENDOR_CODE = R.BUYER_CODE AND FROM_MODULE = 'S'; UPDATE MREL.MREL_SALES_BUYER_MAST SET ACC_CODE = v_SUB WHERE BUYER_CODE = R.BUYER_CODE; END IF; END LOOP; END;