APPS.QA_SKIPLOT_UTILITY SQL Statements

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

Line: 11

        select min(rule_seq)
        from qa_skiplot_process_plan_rules
        where process_plan_id = pp_id;

Line: 38

        select qsrr.receipt_date
        from   qa_skiplot_rcv_results qsrr
        where  insp_lot_id = (select max(qsrr2.insp_lot_id)
               from qa_skiplot_rcv_results qsrr2
               where qsrr2.criteria_id = p_criteria_id and
               qsrr2.receipt_date < p_receipt_date);

Line: 111

        select q1.insp_lot_id, q1.receipt_date
        from qa_skiplot_rcv_results q1
        where q1.insp_lot_id = (select min(q2.insp_lot_id)
                                from qa_skiplot_rcv_results q2
                                where q2.insp_lot_id > x_rule_start_lotid and
                                q2.criteria_id = x_criteria_id and
                                q2.process_id = x_process_id);

Line: 146

                update qa_skiplot_plan_states
                set rule_start_lot_id = x_next_lotid,
                rule_start_date = x_next_receipt_date
                where process_plan_id = p_plan_state.process_plan_id and
                criteria_id = p_plan_state.criteria_id;

Line: 155

                insert_error_log (
                p_module_name =>'QA_SKIPLOT_UTILITY.check_date_span',
                p_error_message => 'Next lotid or next receipt date is null');

Line: 220

            insert_error_log (
            p_module_name =>'QA_SKIPLOT_UTILITY.CHECK_SKIPLOT_AVAILABILITY',
            p_error_message => 'QA_SKIPLOT_CHECK_AVALIABLITY_ERR',
            p_comments => SUBSTR (SQLERRM , 1 , 240));

Line: 234

        select qa_skipping_insp_flag
        from mtl_parameters
        where organization_id = x_org_id;

Line: 264

    select count(*)
    from qa_skiplot_rcv_criteria_val_v qsrc
    where qsrc.organization_id = x_org_id and
    trunc(sysdate) between nvl(trunc(qsrc.effective_from), trunc(sysdate))
    and nvl(trunc(qsrc.effective_to), trunc(sysdate));

Line: 297

        select frequency_num, frequency_denom
        from qa_skiplot_process_plan_rules
        where process_plan_id = x_pp_id and
        rule_seq = x_rule_seq;

Line: 323

            'select qspp.process_plan_id
            from qa_skiplot_association qsa,
            qa_skiplot_process_plans qspp
            where qsa.criteria_id = :1 and
            qsa.process_id = :2 and
            qsa.process_id = qspp.process_id and
            qspp.plan_id = :3'
            using p_criteria_id, p_process_id, p_plan_id;

Line: 375

            'select
            qspp.plan_id,
            qspp.process_plan_id,
            qsa.process_id,
            qsp.disqualification_days,
            qsa.criteria_id,
            qspp.alternate_plan_id,
            qsps.current_rule,
            qsppr.rounds,
            qsppr.days_span,
            qsppr.frequency_num,
            qsppr.frequency_denom,
            qsps.current_round,
            qsps.current_lot,
            qsps.lot_accepted,
            qsps.rule_start_lot_id,
            qsps.rule_start_date,
            qsps.last_receipt_lot_id,
            qsps.last_receipt_date
            from qa_skiplot_association qsa,
            qa_skiplot_processes qsp,
            qa_skiplot_process_plans qspp,
            qa_skiplot_process_plan_rules qsppr,
            qa_skiplot_plan_states qsps
            where qsa.criteria_id = :1 and
            qsp.process_id = qsa.process_id and
            qspp.process_plan_id = :2 and
            qsppr.process_plan_id = qspp.process_plan_id and
            qsps.process_plan_id = qspp.process_plan_id and
            qsps.criteria_id = qsa.criteria_id and
            qsps.current_rule = qsppr.rule_seq'

            using p_criteria_id, pp_id;

Line: 414

            insert_error_log (
            p_module_name => 'QA_SKIPLOT_UTILITY.FETCH_PLAN_STATE',
            p_error_message => 'fail to fetch plan state',
            p_comments => SUBSTR (SQLERRM , 1 , 240));

Line: 428

        select qspp.process_plan_id
        from qa_skiplot_process_plans qspp
        where process_id = x_pid;

Line: 441

        update_insp_stage(
        p_txn => p_txn,
        p_stage => 'QUALIFICATION',
        p_criteria_id =>p_criteria_id,
        p_process_id => p_process_id);

Line: 496

            insert_error_log (
            p_module_name =>'QA_SKIPLOT_UTILITY.INIT_PLAN_STATE',
            p_error_message => 'QA_SKIPLOT_INIT_STATE_FAILURE',
            p_comments => 'process_plan_id or criteria_id not available');

Line: 506

        delete qa_skiplot_plan_states where
        process_plan_id = p_process_plan_id and
        criteria_id = p_criteria_id
        returning current_rule, last_receipt_date, last_receipt_lot_id into
        old_rule, last_date, last_receipt_lot;

Line: 516

        insert into qa_skiplot_plan_states(
        PROCESS_PLAN_ID,
        CRITERIA_ID,
        CURRENT_RULE,
        CURRENT_ROUND,
        CURRENT_LOT,
        LOT_ACCEPTED,
        RULE_START_LOT_ID,
        RULE_START_DATE,
        LAST_RECEIPT_LOT_ID,
        LAST_RECEIPT_DATE,
        LAST_UPDATE_DATE,
        LAST_UPDATED_BY,
        CREATION_DATE,
        CREATED_BY,
        LAST_UPDATE_LOGIN)
        values(
        p_process_plan_id,
        p_criteria_id,
        first_rule,
        1,
        0,
        0,
        p_lot_id,
        decode(p_lot_id, null, null, sysdate),
        nvl(p_lot_id,last_receipt_lot),
        nvl(last_date,sysdate),
        sysdate,
        fnd_global.user_id,
        sysdate,
        fnd_global.user_id,
        fnd_global.login_id);

Line: 549

        update_state_history (
        p_process_plan_id => p_process_plan_id,
        p_criteria_id => p_criteria_id,
        p_old_rule => old_rule,
        p_new_rule => first_rule,
        p_txn => p_txn);

Line: 558

            insert_error_log (
            p_module_name => 'QA_SKIPLOT_UTILITY.INIT_PLAN_STATE',
            p_error_message => 'fail to delete and insert initial plan state',
            p_comments => SUBSTR (SQLERRM , 1 , 240));

Line: 569

        select qspp.process_plan_id
        from qa_skiplot_association qsa,
        qa_skiplot_process_plans qspp
        where qsa.criteria_id = x_cid and
        qsa.process_id = qspp.process_id;

Line: 577

            update_plan_state (
            p_process_plan_id => ps.process_plan_id,
            p_criteria_id => p_criteria_id,
            p_next_rule => 0,
            p_next_round => 1,
            p_next_lot => 0,
            p_lot_accepted => 0);

Line: 588

            insert_error_log (
            p_module_name => 'QA_SKIPLOT_UTILITY.INIT_PLAN_STATES',
            p_error_message => 'fail to update qa_skiplot_plan_states',
            p_comments => SUBSTR (SQLERRM , 1 , 240));

Line: 606

        UPDATE qa_skiplot_plan_states
          SET last_receipt_date = SYSDATE,
              last_update_date  = SYSDATE,
              last_updated_by   = fnd_global.user_id,
              last_update_login = fnd_global.login_id
         WHERE process_plan_id  = p_process_plan_id AND
               criteria_id      = p_criteria_id;

Line: 620

        select qspp.process_plan_id,
        qsa.criteria_id
        from qa_skiplot_process_plans qspp,
        qa_skiplot_association qsa
        where qspp.process_id = x_pid and
        qspp.process_id = qsa.process_id;

Line: 630

            update_plan_state (
            p_process_plan_id => ps.process_plan_id,
            p_criteria_id => ps.criteria_id,
            p_next_rule => 0,
            p_next_round => 1,
            p_next_lot => 0,
            p_lot_accepted => 0);

Line: 641

            insert_error_log (
            p_module_name => 'QA_SKIPLOT_UTILITY.RESET_PLAN_STATES',
            p_error_message => 'fail to update qa_skiplot_plan_states',
            p_comments => SUBSTR (SQLERRM , 1 , 240));

Line: 723

        select min(rule_seq)
        from qa_skiplot_process_plan_rules
        where process_plan_id = pp_id and
        rule_seq > current_rule;

Line: 803

    PROCEDURE UPDATE_INSP_STAGE (
    p_txn IN NUMBER,
    p_stage IN VARCHAR2,
    p_criteria_id IN NUMBER,
    p_process_id IN NUMBER)IS

    BEGIN

        --
        -- removed the if statement as it's really unnecessary.
        -- Reference bug 2137211
        -- jezheng
        -- Wed Mar 17 15:41:02 PST 2004
        --
        --if p_txn = RCV then
            update qa_skiplot_association
            set insp_stage = p_stage
            where criteria_id = p_criteria_id and
            process_id = p_process_id;

Line: 824

    END UPDATE_INSP_STAGE;

Line: 826

    PROCEDURE UPDATE_PLAN_STATE(
    p_process_plan_id IN NUMBER,
    p_criteria_id IN NUMBER,
    p_next_rule IN NUMBER DEFAULT NULL,
    p_next_round IN NUMBER DEFAULT NULL,
    p_next_lot IN NUMBER DEFAULT NULL,
    p_rule_start_lotid IN NUMBER DEFAULT NULL,
    p_last_receipt_lot_id IN NUMBER DEFAULT NULL,
    p_lot_accepted IN NUMBER DEFAULT NULL,
    p_txn IN NUMBER DEFAULT NULL) IS

    old_plan_state plan_state_rec;

Line: 857

        update qa_skiplot_plan_states
        set current_rule = nvl(p_next_rule, current_rule),
        current_round = nvl(p_next_round, current_round),
        current_lot = nvl(p_next_lot, current_lot),
        lot_accepted = nvl(p_lot_accepted, lot_accepted),
        last_receipt_lot_id = nvl(p_last_receipt_lot_id, last_receipt_lot_id),
        last_receipt_date = decode(p_last_receipt_lot_id, null, last_receipt_date, sysdate),
        rule_start_lot_id = nvl(p_rule_start_lotid, rule_start_lot_id),
        rule_start_date = x_rule_start_date,
        last_update_date = sysdate,
        last_updated_by = fnd_global.user_id,
        last_update_login = fnd_global.login_id
        where process_plan_id = p_process_plan_id and
        criteria_id = p_criteria_id;

Line: 873

            update_state_history (
            p_old_plan_state => old_plan_state,
            p_next_rule => p_next_rule,
            p_txn => p_txn);

Line: 884

                update_insp_stage(
                p_txn => p_txn,
                p_stage => 'SKIPPING',
                p_criteria_id => p_criteria_id,
                p_process_id => get_process_id (p_process_plan_id));

Line: 890

                update_insp_stage(
                p_txn => p_txn,
                p_stage => 'QUALIFICATION',
                p_criteria_id =>p_criteria_id,
                p_process_id => get_process_id(p_process_plan_id));

Line: 900

            insert_error_log (
            p_module_name => 'QA_SKIPLOT_UTILITY.UPDATE_PLAN_STATE',
            p_error_message => 'QA_SKIPLOT_UPDATE_STATE_FAILURE',
            p_comments => SUBSTR (SQLERRM , 1 , 240));

Line: 905

    END UPDATE_PLAN_STATE;

Line: 916

        select qsp.process_id, qsp.process_code, qsp.description
        from qa_skiplot_processes qsp, qa_skiplot_process_plans qspp
        where qspp.process_plan_id = x_ppid and
        qspp.process_id = qsp.process_id;

Line: 934

        select vendor_name,
        vendor_site_code,
        item,
        item_revision,
        category_desc,
        manufacturer_name,
        project_number,
        task_number,
        wf_role_name
        from qa_skiplot_rcv_criteria_v
        where criteria_id = x_criteria_id;

Line: 947

        select name
        from qa_chars
        where char_id in (10, 11, 13, 26, 121, 122, 130)
        order by char_id;

Line: 1053

        select qp.name
        from qa_plans qp, qa_skiplot_process_plans qspp
        where qspp.process_plan_id = x_process_plan_id and
        qspp.plan_id = qp.plan_id;

Line: 1124

            insert_error_log (
            p_module_name => 'QA_SKIPLOT_UTILITY.LAUNCH_WORKFLOW',
            p_error_message => 'QA_SKIPLOT_WORKFLOW_FAILURE',
            p_comments => SUBSTR (SQLERRM , 1 , 240));

Line: 1132

    PROCEDURE UPDATE_STATE_HISTORY(
    p_old_plan_state IN plan_state_rec,
    p_next_rule IN NUMBER,
    p_txn IN NUMBER DEFAULT NULL) IS

    new_freq_num number;

Line: 1148

        update_state_history (
        p_process_plan_id => p_old_plan_state.process_plan_id,
        p_criteria_id => p_old_plan_state.criteria_id,
        p_old_freq_num => p_old_plan_state.current_freq_num,
        p_old_freq_denom => p_old_plan_state.current_freq_denom,
        p_new_freq_num => new_freq_num,
        p_new_freq_denom => new_freq_denom,
        p_txn => p_txn);

Line: 1157

    END UPDATE_STATE_HISTORY;

Line: 1160

    PROCEDURE UPDATE_STATE_HISTORY(
    p_process_plan_id IN NUMBER,
    p_criteria_id IN NUMBER,
    p_old_rule IN NUMBER,
    p_new_rule IN NUMBER,
    p_txn IN NUMBER) IS

    old_freq_num number;

Line: 1185

        update_state_history (
        p_process_plan_id => p_process_plan_id,
        p_criteria_id => p_criteria_id,
        p_old_freq_num => old_freq_num,
        p_old_freq_denom => old_freq_denom,
        p_new_freq_num => new_freq_num,
        p_new_freq_denom => new_freq_denom,
        p_txn => p_txn);

Line: 1194

    END UPDATE_STATE_HISTORY;

Line: 1196

    PROCEDURE UPDATE_STATE_HISTORY(
    p_process_plan_id IN NUMBER,
    p_criteria_id IN NUMBER,
    p_old_freq_num IN NUMBER,
    p_old_freq_denom IN NUMBER,
    p_new_freq_num IN NUMBER,
    p_new_freq_denom IN NUMBER,
    p_txn IN NUMBER) IS

    BEGIN

        insert into qa_skiplot_state_history(
        PROCESS_PLAN_ID,
        CRITERIA_ID,
        CHANGE_DATE,
        OLD_FREQ_NUM,
        OLD_FREQ_DENOM,
        NEW_FREQ_NUM,
        NEW_FREQ_DENOM,
        LAST_UPDATE_DATE,
        LAST_UPDATED_BY,
        CREATION_DATE,
        CREATED_BY,
        LAST_UPDATE_LOGIN)
        values(
        p_process_plan_id,
        p_criteria_id,
        sysdate,
        p_old_freq_num,
        p_old_freq_denom,
        p_new_freq_num,
        p_new_freq_denom,
        sysdate,
        fnd_global.user_id,
        sysdate,
        fnd_global.user_id,
        fnd_global.login_id);

Line: 1246

            insert_error_log (
            p_module_name => 'QA_SKIPLOT_UTILITY.UPDATE_STATE_HISTORY',
            p_error_message => 'QA_SKIPLOT_UPDATE_HISTORY_FAILURE',
            p_comments => SUBSTR (SQLERRM , 1 , 240));

Line: 1250

    END UPDATE_STATE_HISTORY;

Line: 1252

    PROCEDURE INSERT_ERROR_LOG (
    p_module_name IN VARCHAR2,
    p_error_message IN VARCHAR2 DEFAULT NULL,
    p_comments IN VARCHAR2 DEFAULT NULL) IS

    PRAGMA AUTONOMOUS_TRANSACTION;

Line: 1261

        select qa_skiplot_log_id_s.nextval
        from dual;

Line: 1265

    select 1 from qa_skiplot_log
    where log_id = x_id;

Line: 1285

            insert into qa_skiplot_log(
            LOG_ID,
            MODULE_NAME,
            ERROR_MESSAGE,
            COMMENTS,
            LAST_UPDATE_DATE,
            LAST_UPDATED_BY,
            CREATION_DATE,
            CREATED_BY,
            LAST_UPDATE_LOGIN)
            values(
            x_logid,
            p_module_name,
            p_error_message,
            p_comments,
            sysdate,
            fnd_global.user_id,
            sysdate,
            fnd_global.user_id,
            fnd_global.login_id);

Line: 1306

            update qa_skiplot_log
            set MODULE_NAME = p_module_name,
            ERROR_MESSAGE = p_error_message,
            COMMENTS = p_comments,
            LAST_UPDATE_DATE = sysdate,
            LAST_UPDATED_BY = fnd_global.user_id,
            CREATION_DATE = sysdate,
            CREATED_BY = fnd_global.user_id,
            LAST_UPDATE_LOGIN = fnd_global.login_id
            where LOG_ID = x_logid;

Line: 1324

    END INSERT_ERROR_LOG;

Line: 1330

        select qa_skiplot_lot_id_s.nextval
        from dual;

Line: 1368

            update_insp_stage(
            p_txn => nvl(p_txn, RCV),
            p_stage => 'SKIPPING',
            p_criteria_id => p_plan_state.criteria_id,
            p_process_id => p_plan_state.process_id);

Line: 1384

        select process_id
        from qa_skiplot_process_plans
        where process_plan_id = x_ppid;