APPS.CE_UK_VALIDATIONS_DATA_LDR SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 123

        SELECT INTERFACE_LINE_ID, SOURCE_VALUE1, SOURCE_VALUE2, SOURCE_VALUE3
        FROM   CE_UK_VALIDATIONS_INTERFACE
        ORDER BY INTERFACE_LINE_ID)
    LOOP
        /* Check if ORIG_SORT_CODE is numeric and is of correct length*/
        IF  (NOT regexp_like(c_sub_codes.SOURCE_VALUE1,'^[0-9]*$') OR
            LENGTH(NVL(c_sub_codes.SOURCE_VALUE1,'0')) <> 6)
        THEN
            log_msg('--> ORIG_SORT_CODE is invalid -Rec no:'||c_sub_codes.INTERFACE_LINE_ID);

Line: 172

    DELETE FROM CE_UK_VAL_SUBS_CODES;

Line: 173

    log_msg(SQL%Rowcount||' record(s) deleted.');

Line: 176

    log_msg('Inserting new data into table');

Line: 177

    INSERT INTO CE_UK_VAL_SUBS_CODES(
        SUBSTITUTION_ID,
        ORIG_SORT_CODE,
        SUBS_SORT_CODE,
        REQUEST_ID,
        CREATED_BY,
        CREATION_DATE,
        LAST_UPDATED_BY,
        LAST_UPDATE_DATE,
        LAST_UPDATE_LOGIN)
    SELECT
        INTERFACE_LINE_ID,
        SOURCE_VALUE1,
        SOURCE_VALUE2,
        FND_GLOBAL.conc_request_id,
        FND_GLOBAL.user_id,
        sysdate,
        FND_GLOBAL.user_id,
        sysdate,
        FND_GLOBAL.user_id
    FROM
        CE_UK_VALIDATIONS_INTERFACE;

Line: 199

    log_msg(SQL%Rowcount||' record(s) inserted.');

Line: 224

        (SELECT
            SUBSTITUTION_ID,
            ORIG_SORT_CODE,
            SUBS_SORT_CODE,
            LAST_UPDATE_DATE
         FROM CE_UK_VAL_SUBS_CODES ORDER BY SUBSTITUTION_ID)
    LOOP
        log_msg('Listing SUBSTITUTION_ID='||subsCodeRec.SUBSTITUTION_ID);

Line: 236

        put_element('loadDate',to_char(subsCodeRec.LAST_UPDATE_DATE,
                                        FND_PROFILE.value('ICX_DATE_FORMAT_MASK')));

Line: 262

        (SELECT
            INTERFACE_LINE_ID,
            SOURCE_VALUE1,
            SOURCE_VALUE2,
            SOURCE_VALUE3,
            SOURCE_VALUE4,
            SOURCE_VALUE5,
            SOURCE_VALUE6,
            SOURCE_VALUE7,
            SOURCE_VALUE8,
            SOURCE_VALUE9,
            SOURCE_VALUE10,
            SOURCE_VALUE11,
            SOURCE_VALUE12,
            SOURCE_VALUE13,
            SOURCE_VALUE14,
            SOURCE_VALUE15,
            SOURCE_VALUE16,
            SOURCE_VALUE17,
            SOURCE_VALUE18
        FROM
            CE_UK_VALIDATIONS_INTERFACE
        ORDER BY
            INTERFACE_LINE_ID)
    LOOP
        /* Check if FROM_SORT_CODE is numeric and length is ok */
        IF    (NOT regexp_like(c_modulus_weights.SOURCE_VALUE1,'^[0-9]*$')
            OR LENGTH(nvl(c_modulus_weights.SOURCE_VALUE1,'0')) <> 6)
        THEN
            log_msg('--> FROM_SORT_CODE is invalid -Rec no:'||c_modulus_weights.INTERFACE_LINE_ID);

Line: 366

    DELETE FROM CE_UK_VAL_MODULUS_WEIGHTS;

Line: 367

    log_msg(SQL%Rowcount||' record(s) deleted.');

Line: 372

    log_msg('Inserting new data into table');

Line: 373

    INSERT INTO CE_UK_VAL_MODULUS_WEIGHTS(
        MODULUS_CHECK_ID,
        FROM_SORT_CODE ,
        TO_SORT_CODE,
        CHECK_TYPE  ,
        WEIGHT_U  ,
        WEIGHT_V  ,
        WEIGHT_W  ,
        WEIGHT_X  ,
        WEIGHT_Y  ,
        WEIGHT_Z  ,
        WEIGHT_A  ,
        WEIGHT_B  ,
        WEIGHT_C  ,
        WEIGHT_D  ,
        WEIGHT_E  ,
        WEIGHT_F  ,
        WEIGHT_G  ,
        WEIGHT_H  ,
        EXCEPTION_CODE,
        REQUEST_ID,
        CREATED_BY,
        CREATION_DATE,
        LAST_UPDATED_BY,
        LAST_UPDATE_DATE,
        LAST_UPDATE_LOGIN)
    SELECT
        INTERFACE_LINE_ID,
        SOURCE_VALUE1,
        SOURCE_VALUE2 ,
        SOURCE_VALUE3 ,
        SOURCE_VALUE4 ,
        SOURCE_VALUE5 ,
        SOURCE_VALUE6 ,
        SOURCE_VALUE7 ,
        SOURCE_VALUE8 ,
        SOURCE_VALUE9 ,
        SOURCE_VALUE10,
        SOURCE_VALUE11,
        SOURCE_VALUE12,
        SOURCE_VALUE13,
        SOURCE_VALUE14,
        SOURCE_VALUE15,
        SOURCE_VALUE16,
        SOURCE_VALUE17,
        SOURCE_VALUE18,
        FND_GLOBAL.conc_request_id,
        FND_GLOBAL.user_id,
        sysdate,
        FND_GLOBAL.user_id,
        sysdate,
        FND_GLOBAL.user_id
    FROM
        CE_UK_VALIDATIONS_INTERFACE;

Line: 427

    log_msg(SQL%Rowcount||' record(s) inserted.');

Line: 451

        (SELECT
            MODULUS_CHECK_ID,
            FROM_SORT_CODE,
            TO_SORT_CODE,
            CHECK_TYPE,
            WEIGHT_U, WEIGHT_V, WEIGHT_W, WEIGHT_X,
            WEIGHT_Y, WEIGHT_Z, WEIGHT_A, WEIGHT_B,
            WEIGHT_C, WEIGHT_D, WEIGHT_E, WEIGHT_F,
            WEIGHT_G, WEIGHT_H,
            EXCEPTION_CODE,
            LAST_UPDATE_DATE
        FROM
            CE_UK_VAL_MODULUS_WEIGHTS ORDER BY MODULUS_CHECK_ID)
    LOOP
        log_msg('Listing MODULUS_CHECK_ID='||modWeightRec.MODULUS_CHECK_ID);

Line: 486

        put_element('loadDate',to_char(modWeightRec.LAST_UPDATE_DATE,
                                       FND_PROFILE.value('ICX_DATE_FORMAT_MASK')));

Line: 556

            SELECT  meaning
            INTO    l_file_type_meaning
            FROM    ce_lookups
            WHERE   lookup_type = 'CE_MODULUS_FILETYPE_UK'
            AND     lookup_code = X_File_Type;

Line: 655

        SELECT 	count(*)
        INTO   	l_cnt
        FROM   	CE_UK_VALIDATIONS_INTERFACE;

Line: 675

        SELECT MAX(INTERFACE_LINE_ID) - COUNT(*)
        INTO   l_cnt
        FROM   CE_UK_VALIDATIONS_INTERFACE;