Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

PROCEDURE PROCESS_ASSETS AS
        BEGIN
            INSERT
                ALL 
    
        -- 1
                 WHEN OP_TYPE = 'I' THEN
                    INTO XXCCS_UEE_SRVC_ES_ACT_KEY (
                        COVERED_LINE_ID,
                        INSTANCE_ID,
                        SEQ_ID,
                        OP_TYPE,
                        SOT_TABLE,
                        SOT_CREATE_DATE,
                        SOT_LAST_UPDATE_DATE,
                        GG_CREATE_DATE,
                        CONTRACT_ID,
                        ITEM_TYPE_FLAG
                    )
                    VALUES (
                        COVERED_LINE_ID,
                        INSTANCE_ID,
                        SEQ_ID,
                        OP_TYPE,
                        'L_SOT_TABLE_NAME',
                        NRT_CREATION_DATE,
                        NRT_LAST_UPDATE_DATE,
                        GG_CREATE_DATE,
                        CONTRACT_ID,
                        ITEM_TYPE_FLAG
                    )
    
            --2
                    WHEN OP_TYPE = 'I' THEN
                        INTO XXCCS_UEE_DEVC_ES_CVD_KEY (
                            INSTANCE_ID,
                            OP_TYPE,
                            SOT_TABLE,
                            SOT_CREATE_DATE,
                            SOT_LAST_UPDATE_DATE,
                            GG_CREATE_DATE,
                            SEQ_ID,
                            ITEM_TYPE_FLAG
                        )
                        VALUES (
                            INSTANCE_ID,
                            OP_TYPE,
                            'L_SOT_TABLE_NAME',
                            NRT_CREATION_DATE,
                            NRT_LAST_UPDATE_DATE,
                            GG_CREATE_DATE,
                            SEQ_ID,
                            ITEM_TYPE_FLAG
                        )
    
            --3
                    WHEN OP_TYPE = 'U' THEN
                        INTO XXCCS_UEE_SRVC_ES_ACT_KEY (
                            COVERED_LINE_ID,
                            INSTANCE_ID,
                            SEQ_ID,
                            OP_TYPE,
                            SOT_TABLE,
                            SOT_CREATE_DATE,
                            SOT_LAST_UPDATE_DATE,
                            GG_CREATE_DATE,
                            CONTRACT_ID,
                            ITEM_TYPE_FLAG
                        )
                        VALUES (
                            COVERED_LINE_ID,
                            INSTANCE_ID,
                            SEQ_ID,
                            OP_TYPE,
                            'L_SOT_TABLE_NAME',
                            NRT_CREATION_DATE,
                            NRT_LAST_UPDATE_DATE,
                            GG_CREATE_DATE,
                            CONTRACT_ID,
                            ITEM_TYPE_FLAG
                        )
    
            -- 4
                    WHEN OP_TYPE = 'U' THEN
                        INTO XXCCS_UEE_DEVC_ES_CVD_KEY (
                            INSTANCE_ID,
                            OP_TYPE,
                            SOT_TABLE,
                            SOT_CREATE_DATE,
                            SOT_LAST_UPDATE_DATE,
                            GG_CREATE_DATE,
                            SEQ_ID,
                            ITEM_TYPE_FLAG
                        )
                        VALUES (
                            INSTANCE_ID,
                            OP_TYPE,
                            'L_SOT_TABLE_NAME',
                            NRT_CREATION_DATE,
                            NRT_LAST_UPDATE_DATE,
                            GG_CREATE_DATE,
                            SEQ_ID,
                            ITEM_TYPE_FLAG
                        )
    
            -- 5
                    WHEN OP_TYPE = 'U'
                         AND ( ( ITEM_TYPE_FLAG IN (
                        'P',
                        'S'
                    )
                                 AND OLD_ITEM_TYPE_FLAG IN (
                        'C'
                    ) )
                               OR ( ITEM_TYPE_FLAG IN (
                        'C'
                    )
                                    AND OLD_ITEM_TYPE_FLAG IN (
                        'P',
                        'S'
                    ) ) ) THEN
                        INTO XXCCS_UEE_SRVC_ES_ACT_KEY (
                            COVERED_LINE_ID,
                            INSTANCE_ID,
                            SEQ_ID,
                            OP_TYPE,
                            SOT_TABLE,
                            SOT_CREATE_DATE,
                            SOT_LAST_UPDATE_DATE,
                            GG_CREATE_DATE,
                            CONTRACT_ID,
                            ITEM_TYPE_FLAG
                        )
                        VALUES (
                            COVERED_LINE_ID,
                            INSTANCE_ID,
                            SEQ_ID,
                            'D',
                            'L_SOT_TABLE_NAME',
                            NRT_CREATION_DATE,
                            NRT_LAST_UPDATE_DATE,
                            GG_CREATE_DATE,
                            CONTRACT_ID,
                            OLD_ITEM_TYPE_FLAG
                        )
    
            -- 6
                    WHEN OP_TYPE = 'U'
                         AND ( ( ITEM_TYPE_FLAG IN (
                        'P',
                        'S'
                    )
                                 AND OLD_ITEM_TYPE_FLAG IN (
                        'C'
                    ) )
                               OR ( ITEM_TYPE_FLAG IN (
                        'C'
                    )
                                    AND OLD_ITEM_TYPE_FLAG IN (
                        'P',
                        'S'
                    ) ) ) THEN
                        INTO XXCCS_UEE_DEVC_ES_CVD_KEY (
                            INSTANCE_ID,
                            OP_TYPE,
                            SOT_TABLE,
                            SOT_CREATE_DATE,
                            SOT_LAST_UPDATE_DATE,
                            GG_CREATE_DATE,
                            SEQ_ID,
                            ITEM_TYPE_FLAG
                        )
                        VALUES (
                            INSTANCE_ID,
                            'D',
                            'L_SOT_TABLE_NAME',
                            NRT_CREATION_DATE,
                            NRT_LAST_UPDATE_DATE,
                            GG_CREATE_DATE,
                            SEQ_ID,
                            OLD_ITEM_TYPE_FLAG
                        )
                        
    
            -- 7
            -- CONVERTING ELSE INTO REVERSE WHEN CONDITION
                    WHEN OP_TYPE <> 'I'
                         AND NOT ( OP_TYPE = 'U'
                                   AND ( ( ITEM_TYPE_FLAG IN (
                        'P',
                        'S'
                    )
                                           AND OLD_ITEM_TYPE_FLAG IN (
                        'C'
                    ) )
                                         OR ( ITEM_TYPE_FLAG IN (
                        'C'
                    )
                                              AND OLD_ITEM_TYPE_FLAG IN (
                        'P',
                        'S'
                    ) ) ) ) THEN
                        INTO XXCCS_UEE_SRVC_ES_ACT_KEY (
                            COVERED_LINE_ID,
                            INSTANCE_ID,
                            SEQ_ID,
                            OP_TYPE,
                            SOT_TABLE,
                            SOT_CREATE_DATE,
                            SOT_LAST_UPDATE_DATE,
                            GG_CREATE_DATE,
                            CONTRACT_ID,
                            ITEM_TYPE_FLAG
                        )
                        VALUES (
                            COVERED_LINE_ID,
                            INSTANCE_ID,
                            SEQ_ID,
                            OP_TYPE,
                            'L_SOT_TABLE_NAME',
                            NRT_CREATION_DATE,
                            NRT_LAST_UPDATE_DATE,
                            GG_CREATE_DATE,
                            CONTRACT_ID,
                            ITEM_TYPE_FLAG
                        )
                
            -- 8
            -- NEWLY ADDED
                    WHEN OP_TYPE <> 'I'
                         AND NOT ( OP_TYPE = 'U'
                                   AND ( ( ITEM_TYPE_FLAG IN (
                        'P',
                        'S'
                    )
                                           AND OLD_ITEM_TYPE_FLAG IN (
                        'C'
                    ) )
                                         OR ( ITEM_TYPE_FLAG IN (
                        'C'
                    )
                                              AND OLD_ITEM_TYPE_FLAG IN (
                        'P',
                        'S'
                    ) ) ) )
                         AND CNT = 0 THEN
                        INTO XXCCS_UEE_DEVC_ES_CVD_KEY_V3 (
                            INSTANCE_ID,
                            OP_TYPE,
                            SOT_TABLE,
                            SOT_CREATE_DATE,
                            SOT_LAST_UPDATE_DATE,
                            GG_CREATE_DATE,
                            SEQ_ID,
                            ITEM_TYPE_FLAG
                        )
                        VALUES (
                            INSTANCE_ID,
                            OP_TYPE,
                            'L_SOT_TABLE_NAME',
                            NRT_CREATION_DATE,
                            NRT_LAST_UPDATE_DATE,
                            GG_CREATE_DATE,
                            SEQ_ID,
                            ITEM_TYPE_FLAG
                        )
                    
            -- 9
            -- NEWLY ADDED
                    WHEN OP_TYPE <> 'I'
                         AND NOT ( OP_TYPE = 'U'
                                   AND ( ( ITEM_TYPE_FLAG IN (
                        'P',
                        'S'
                    )
                                           AND OLD_ITEM_TYPE_FLAG IN (
                        'C'
                    ) )
                                         OR ( ITEM_TYPE_FLAG IN (
                        'C'
                    )
                                              AND OLD_ITEM_TYPE_FLAG IN (
                        'P',
                        'S'
                    ) ) ) )
                         AND CNT <> 0 THEN
                        INTO XXCCS_UEE_DEVC_ES_CVD_KEY_V3 (
                            INSTANCE_ID,
                            OP_TYPE,
                            SOT_TABLE,
                            SOT_CREATE_DATE,
                            SOT_LAST_UPDATE_DATE,
                            GG_CREATE_DATE,
                            SEQ_ID,
                            ITEM_TYPE_FLAG
                        )
                        VALUES (
                            INSTANCE_ID,
                            'U',
                            'L_SOT_TABLE_NAME',
                            NRT_CREATION_DATE,
                            NRT_LAST_UPDATE_DATE,
                            GG_CREATE_DATE,
                            SEQ_ID,
                            ITEM_TYPE_FLAG
                        )
            SELECT
                ES.ROWID,
                ES.COVERED_LINE_ID,
                ES.OP_TYPE,
                ES.SEQ_ID,
                ES.NRT_CREATION_DATE,
                ES.NRT_LAST_UPDATE_DATE,
                ES.CONTRACT_ID,
                ES.SERVICE_LINE_ID,
                ES.GG_CREATE_DATE,
                ES.INSTANCE_ID,
                ES.ITEM_TYPE_FLAG,
                NVL(ES.OLD_ITEM_TYPE_FLAG, ES.ITEM_TYPE_FLAG) OLD_ITEM_TYPE_FLAG,
                CNT.CNT
            FROM
                XXCCS_DS_CVDLIB_DTLEB_ES ES
                
                -- 10
                -- ADDED THIS JOIN HERE
                
                LEFT JOIN (
                    SELECT
                        INSTANCE_ID,
                        COUNT(1) CNT
                    INTO L_CVD_EXISTS
                    FROM
                        XXCCS_DS_CVDLINEIB_DETAILEB
                    --WHERE
                    --    INSTANCE_ID = ES.INSTANCE_ID
                    GROUP BY
                        INSTANCE_ID
                ) CNT ON ES.INSTANCE_ID = CNT.INSTANCE_ID;
    
        END PROCESS_ASSETS;
    
    /

--See section 7, 8, 9, 10 from the above code which is added/modified according to your last comment.

ORA-00900: invalid SQL statement