Oracle EBS Quality Management: Automate QC Sample Creation Using GMD_SAMPLES_PUB API

This blog explains how to use an API in Oracle EBS to create Quality (QC) samples along with their corresponding test results.

Introduction / Issue

This blog contains an API that can be used in Oracle EBS to create Quality samples and the corresponding results.

Why We Need To Do / Cause of the Issue

The business requires creating a large number of quality samples and their corresponding results using an API, without any manual effort.

How Do We Solve

Create an Oracle package to create Quality samples and the results.

CREATE OR REPLACE PACKAGE      xx_gmd_qc_sample_result_pkg AS
/***********************************************************************************************
    Who                When                 Version          Description
    Mohan G         04-Nov-2025            1.0              To create Sample and assign test results

************************************************************************************************/
g_conc_request_id number;
PROCEDURE xx_validate_samples (
    errbuf   OUT VARCHAR2,
    retcode  OUT NUMBER
);
  -- Create QC Samples
  PROCEDURE xx_create_samples (
      errbuf   OUT VARCHAR2,
      retcode  OUT NUMBER
  );

  -- Load QC Sample Results
  PROCEDURE xx_load_qc_sample_results (
           errbuf   OUT VARCHAR2,
    retcode  OUT NUMBER, p_sample_id IN NUMBER, p_sample_no IN VARCHAR2
  );

END xx_gmd_qc_sample_result_pkg;
/


CREATE OR REPLACE PACKAGE BODY xx_gmd_qc_sample_result_pkg IS

/***********************************************************************************************
    Who                When                 Version          Description
    Mohan G         04-Nov-2025            1.0              To create Sample and assign test results
************************************************************************************************/
 PROCEDURE xx_validate_samples (
    errbuf   OUT VARCHAR2,
    retcode  OUT NUMBER
) IS
    CURSOR c_validate IS
        SELECT staging_id,
               sample_no,
               item_no,
               whse_code,
               lot_no,
               specification,
               specification_vers
          FROM xx.xx_gmd_qc_samples_stg
         WHERE NVL(process_flag, 'N') = 'N';

    l_inv_id      mtl_system_items_b.inventory_item_id%TYPE;
    l_lot_count   NUMBER;
    l_spec_count  NUMBER;
    l_error_msg   VARCHAR2(2000);
    l_user_id     NUMBER;
    l_username    VARCHAR2(30) := 'DATA_LOAD_USER';
    l_sample_exists NUMBER :=0;
    l_errbuf  VARCHAR2(2000);
    l_retcode NUMBER;
    l_err_message VARCHAR2(2000);
       
BEGIN
        SELECT user_id
        INTO l_user_id
        FROM fnd_user
        WHERE user_name = l_username;
    dbms_output.put_line('Before Update1'); 
    UPDATE XX.XX_GMD_QC_SAMPLES_STG
        SET creation_Date = sysdate,
                   Created_by = l_user_id,
                   request_id = g_conc_request_id
                   where request_id is null and nvl(process_flag,'N') ='N' ;
    UPDATE XX.XX_GMD_QC_SAMPLES_STG
         SET sample_qty = 0
                   where request_id is null and nvl(process_flag,'N') ='N'
                   and sample_qty is null;
           
   UPDATE XX.XX_GMD_QC_SAMPLE_RESULTS_STG
               SET creation_Date = sysdate,
                   Created_by = l_user_id,
                   request_id = g_conc_request_id
                   where request_id is null and nvl(process_flag,'N') ='N' ;
   commit;
    retcode := 0;
    errbuf  := NULL;

    FOR rec IN c_validate LOOP
        l_error_msg := NULL;
dbms_output.put_line('Inside Loop'); 
        BEGIN
          -- Validate Sample number
            BEGIN
            dbms_output.put_line('number 1'); 
                SELECT count(*) 
                  INTO l_sample_exists
                  FROM gmd_samples
                  where sample_no = rec.sample_no;
            IF l_sample_exists >=1 THEN
            l_error_msg := 'Given sample number is already available in the system';
            END IF;
            EXCEPTION
                WHEN OTHERS THEN
                    l_error_msg := 'Error while validating the sample number';
            END;
        dbms_output.put_line('l_sample_exists 1 '|| l_sample_exists); 
        IF l_error_msg IS NULL THEN
            -- Validate Item & Organization
            dbms_output.put_line('l_error_msg 1 '|| l_error_msg); 
            BEGIN
                SELECT msib.inventory_item_id
                  INTO l_inv_id
                  FROM mtl_system_items_b msib,
                       org_organization_definitions ood
                 WHERE msib.organization_id = ood.organization_id
                   AND msib.segment1 = rec.item_no
                   AND ood.organization_code = rec.whse_code;
            EXCEPTION
                WHEN NO_DATA_FOUND THEN
                    l_error_msg := 'Invalid Item number / Organization';
              WHEN OTHERS THEN
                    l_error_msg := 'Invalid Item number / Organization';
            
            END;
            END IF;
      dbms_output.put_line('l_inv_id 1 '|| l_inv_id);
            -- Validate Lot
            IF l_error_msg IS NULL THEN
                SELECT COUNT(*)
                  INTO l_lot_count
                  FROM mtl_lot_numbers mln
                 WHERE mln.lot_number = rec.lot_no
                   AND mln.inventory_item_id = l_inv_id;
                IF l_lot_count = 0 THEN
                    l_error_msg := 'Given lot is not defined in the system';
                END IF;
            END IF;

            -- Validate Specification
            IF l_error_msg IS NULL THEN
                BEGIN
                    SELECT spec_id
                      INTO l_spec_count
                      FROM gmd_specifications gs
                     WHERE gs.SPEC_VERS = rec.SPECIFICATION_VERS
                     AND gs.spec_name = rec.specification;
                EXCEPTION
                    WHEN NO_DATA_FOUND THEN
                        l_error_msg := 'Given specification not defined in the system';
                END;
            END IF;
dbms_output.put_line('Before Update'); 
            -- Update flag based on validation outcome
            IF l_error_msg IS NULL THEN
                UPDATE xx.xx_gmd_qc_samples_stg
                   SET process_flag  = 'V',
                       error_message = NULL
                 WHERE staging_id = rec.staging_id;
                 xx_create_samples(errbuf      => l_errbuf,
                        retcode     => l_retcode);
            ELSE
            dbms_output.put_line('Else Part'); 
                UPDATE xx.xx_gmd_qc_samples_stg
                   SET process_flag  = 'E',
                       error_message = l_error_msg
                 WHERE staging_id = rec.staging_id;
            END IF;

        EXCEPTION
            WHEN OTHERS THEN
            l_err_message := SQLERRM;
                UPDATE xx.xx_gmd_qc_samples_stg
                   SET process_flag  = 'E',
                       error_message = l_err_message
                 WHERE staging_id = rec.staging_id;
                 dbms_output.put_line('Exception Part'); 
                 commit;
        END;
    END LOOP;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        retcode := 2;
        errbuf  := 'Error in xx_validate_samples: ' || SQLERRM;
        ROLLBACK;
        DBMS_OUTPUT.put_line(errbuf);
END xx_validate_samples;

 PROCEDURE xx_create_samples (
    errbuf   OUT VARCHAR2,
    retcode  OUT NUMBER
) IS
    CURSOR c_sample_data IS
        SELECT *
          FROM xx.xx_gmd_qc_samples_stg
         WHERE process_flag = 'V';

    l_inv_id                mtl_system_items_b.inventory_item_id%TYPE;
    l_sample_rec            gmd_samples%ROWTYPE;
    x_qc_samples_rec        gmd_samples%ROWTYPE;
    x_sampling_events_rec   gmd_sampling_events%ROWTYPE;
    x_sample_spec_disp      gmd_sample_spec_disp%ROWTYPE;
    x_event_spec_disp_rec   gmd_event_spec_disp%ROWTYPE;
    x_results_tab           gmd_api_pub.gmd_results_tab;
    x_spec_results_tab      gmd_api_pub.gmd_spec_results_tab;
    x_return_status         VARCHAR2(10);
    x_msg_data              VARCHAR2(2000);
    x_msg_count             NUMBER := 0;
    l_user_id               NUMBER;
    l_username              VARCHAR2(30) := 'DATA_LOAD_USER';
    g_conc_request_id       NUMBER := fnd_profile.VALUE('CONC_REQUEST_ID');
    l_dummy                 VARCHAR2(1000);
    l_org_id                NUMBER := 0;
    l_errbuf  VARCHAR2(2000);
    l_retcode NUMBER;
BEGIN
    retcode := 0;
    errbuf  := NULL;

    SELECT user_id
      INTO l_user_id
      FROM fnd_user
     WHERE user_name = l_username;

    fnd_global.apps_initialize(user_id      => l_user_id,
                               resp_id      => 23805,
                               resp_appl_id => 552);
    l_errbuf  := NULL;
    l_retcode := 0;
    FOR rec IN c_sample_data LOOP
        BEGIN
        DBMS_OUTPUT.put_line('Loop Started');
            -- Get inventory item id again (validated already, but safe)
            SELECT msib.inventory_item_id, ood.organization_id
              INTO l_inv_id, l_org_id
              FROM mtl_system_items_b msib,
                   org_organization_definitions ood
             WHERE msib.organization_id = ood.organization_id
               AND msib.segment1 = rec.item_no
               AND ood.organization_code = rec.whse_code;
DBMS_OUTPUT.put_line('Inv Id' || l_inv_id);
            -- Prepare record
            l_sample_rec.sample_no            := rec.sample_no;
            l_sample_rec.sample_desc          := rec.sample_desc;
            l_sample_rec.source               := rec.source;
            l_sample_rec.sample_qty           := rec.sample_qty;
            l_sample_rec.sample_qty_uom       := rec.sample_uom;
            l_sample_rec.sampler_id           := l_user_id;
            l_sample_rec.lab_organization_id  := NVL(rec.lab_org,l_org_id);
            l_sample_rec.organization_id      := l_org_id;
            l_sample_rec.inventory_item_id    := l_inv_id;
            l_sample_rec.sample_disposition   := rec.sample_disposition;
            l_sample_rec.delete_mark          := 0;
            l_sample_rec.sample_type          := rec.sample_type;
            l_sample_rec.priority             := rec.priority;
            l_sample_rec.attribute2           := rec.attribute2;
            l_sample_rec.lot_number           := rec.lot_no;
            l_sample_rec.date_drawn           := rec.date_drawn;

            -- Call API
            gmd_samples_pub.create_samples(
                p_api_version             => 3.0,
                p_init_msg_list           => fnd_api.g_false,
                p_commit                  => fnd_api.g_false,
                p_validation_level        => fnd_api.g_valid_level_full,
                p_qc_samples_rec          => l_sample_rec,
                p_user_name               => l_username,
                p_find_matching_spec      => 'Y',
                p_grade                   => NULL,
                p_lpn                     => NULL,
                p_create_new_sample_group => 'N',
                x_qc_samples_rec          => x_qc_samples_rec,
                x_sampling_events_rec     => x_sampling_events_rec,
                x_sample_spec_disp        => x_sample_spec_disp,
                x_event_spec_disp_rec     => x_event_spec_disp_rec,
                x_results_tab             => x_results_tab,
                x_spec_results_tab        => x_spec_results_tab,
                x_return_status           => x_return_status,
                x_msg_count               => x_msg_count,
                x_msg_data                => x_msg_data
            );

            IF x_return_status = fnd_api.g_ret_sts_success THEN
                DBMS_OUTPUT.put_line('Sample Created: ' || x_qc_samples_rec.sample_no);
                UPDATE xx.xx_gmd_qc_samples_stg
                   SET process_flag  = 'S',
                       error_message = NULL
                       --sample_id     = x_qc_samples_rec.sample_id
                 WHERE staging_id = rec.staging_id;
                 UPDATE XX.XX_GMD_QC_SAMPLE_RESULTS_STG xgsr
                 set xgsr.sample_id = x_qc_samples_rec.sample_id
                 where xgsr.sample_no = rec.sample_no;
                 xx_load_qc_sample_results(l_errbuf,l_retcode,x_qc_samples_rec.sample_id, rec.sample_no);
            ELSE
                -- Collect API messages
                FOR i IN 1 .. fnd_msg_pub.count_msg LOOP
                    fnd_msg_pub.get(i, fnd_api.g_false, x_msg_data, l_dummy);
                    DBMS_OUTPUT.put_line('API Error: ' || x_msg_data);
                END LOOP;

                UPDATE xx.xx_gmd_qc_samples_stg
                   SET process_flag  = 'E',
                       error_message = SUBSTR(x_msg_data, 1, 2000)
                 WHERE staging_id = rec.staging_id;
            END IF;

        EXCEPTION
            WHEN OTHERS THEN
                UPDATE xx.xx_gmd_qc_samples_stg
                   SET process_flag  = 'E',
                       error_message = SUBSTR('Unexpected error during creation: ' || 1, 2000)
                 WHERE staging_id = rec.staging_id;
                DBMS_OUTPUT.put_line('Unexpected error: ' || SQLERRM);
        END;
    END LOOP;

    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
        retcode := 2;
        errbuf  := 'Error in xx_create_samples: ' || SQLERRM;
        ROLLBACK;
        DBMS_OUTPUT.put_line(errbuf);
END xx_create_samples;


  ---------------------------------------------------------------------
  -- Procedure: xx_load_qc_sample_results
  -- Purpose  : Record QC test results for given sample
  ---------------------------------------------------------------------
  PROCEDURE xx_load_qc_sample_results (
     errbuf   OUT VARCHAR2,
    retcode  OUT NUMBER, p_sample_id IN NUMBER, p_sample_no IN VARCHAR2
  ) IS
      CURSOR c_test_results IS
          SELECT xgqs.staging_id,
                 xgqs.sample_no,
                 xgqs.seq,
                 xgqs.test_code,
                -- xgqs.test_id,
                 xgqs.result_value_num,
                 xgqs.result_value_char,
                 xgqs.result_date,
                 xgqs.test_replicate_cnt,
                 gr.sample_id,
                 gr.test_id
                 FROM xx.XX_GMD_QC_SAMPLE_results_STG xgqs,gmd_results gr 
                 WHERE xgqs.sample_id = gr.sample_id
                 and xgqs.seq = gr.seq
                 AND NVL(xgqs.process_flag, 'N') = 'N'
                 and gr.sample_id = p_sample_id
                 and xgqs.sample_no = p_sample_no;
      l_results_rec        gmd_results_pub.results_rec;
      l_results_out        gmd_results%ROWTYPE;
      l_spec_results_out   gmd_spec_results%ROWTYPE;
      l_org_id             NUMBER;
      l_status             VARCHAR2(1);
      l_msg_data           VARCHAR2(4000);
      l_msg_count          NUMBER := 0;
      l_username           VARCHAR2(30) := 'DATA_LOAD_USER';
      l_user_id            NUMBER;
      l_errbuf  VARCHAR2(2000);
      l_retcode NUMBER;
      l_dummy    VARCHAR2(1000);
  BEGIN
      SELECT user_id
      INTO l_user_id
      FROM fnd_user
     WHERE user_name = l_username;
          fnd_global.apps_initialize(user_id      => l_user_id,
                               resp_id      => 23805,
                               resp_appl_id => 552);
  UPDATE XX.XX_GMD_QC_SAMPLE_results_STG
        SET creation_Date = sysdate,
                   Created_by = l_user_id,
                   request_id = g_conc_request_id
                   where request_id is null and nvl(process_flag,'N') ='N';
                   
   UPDATE XX.XX_GMD_QC_SAMPLE_RESULTS_STG
               SET creation_Date = sysdate,
                   Created_by = l_user_id,
                   request_id = g_conc_request_id
                   where request_id is null and nvl(process_flag,'N') ='N' ;
   commit;
      SELECT organization_id
        INTO l_org_id
        FROM gmd_samples
       WHERE sample_id = p_sample_id;
    retcode := 0;
    errbuf  := NULL;
      FOR rec IN c_test_results LOOP
          l_results_rec.sample_id := p_sample_id;
          l_results_rec.test_id := rec.test_id;
          l_results_rec.test_code := rec.test_code;
          l_results_rec.result_value := NVL(rec.result_value_num, rec.result_value_char);
          l_results_rec.result_date := rec.result_date;
          l_results_rec.organization_id := l_org_id;
          l_results_rec.seq       := rec.seq;
         -- l_results_rec.lab_organization_id := l_org_id;
          l_results_rec.test_replicate_cnt := rec.test_replicate_cnt;

          gmd_results_pub.record_results (
              p_api_version   => 3.0,
              p_init_msg_list => fnd_api.g_false,
              p_commit        => fnd_api.g_false,
              p_validation_level => fnd_api.g_valid_level_full,
              p_results_rec   => l_results_rec,
              p_user_name     => l_username,
              x_results_rec   => l_results_out,
              x_spec_results_rec => l_spec_results_out,
              x_return_status => l_status,
              x_msg_count     => l_msg_count,
              x_msg_data      => l_msg_data,
              p_user_responsibility_id => 0
          );

IF l_status = fnd_api.g_ret_sts_success THEN
  DBMS_OUTPUT.put_line('Result Loaded ? Sample: ' || p_sample_id ||
                               ', Test ID: ' || rec.test_id ||
                               ', Test Code: ' || rec.test_code ||
                               ', Status: ' || l_status ||
                               ', Message: ' || l_msg_data);
              UPDATE xx.XX_GMD_QC_SAMPLE_RESULTS_STG
                   SET process_flag  = 'S',
                       error_message = NULL
                       --sample_id     = x_qc_samples_rec.sample_id
                 WHERE staging_id = rec.staging_id;             
            ELSE
                -- Collect API messages
                FOR i IN 1 .. fnd_msg_pub.count_msg LOOP
                    fnd_msg_pub.get(i, fnd_api.g_false, l_msg_data, l_dummy);
                    DBMS_OUTPUT.put_line('API Error: ' || l_msg_data);
                END LOOP;

                UPDATE xx.XX_GMD_QC_SAMPLE_RESULTS_STG
                   SET process_flag  = 'E',
                       error_message = SUBSTR(l_msg_data, 1, 2000)
                 WHERE staging_id = rec.staging_id;
            END IF;

          COMMIT;
      END LOOP;

  EXCEPTION
      WHEN OTHERS THEN
        retcode := 2;
        errbuf := 'Error in xx_load_qc_sample_results: ' || SQLERRM;
        DBMS_OUTPUT.put_line(errbuf);
  END xx_load_qc_sample_results;

END xx_gmd_qc_sample_result_pkg;
/

Comments

Popular posts from this blog

Quick Guide: Activating Excel4apps Plugin in Excel for Oracle Reporting

Oracle R12: A PL/SQL Script to Report Customer OAF Personalizations Instance-Wide

From EBS to Fusion Cloud: 10 Things Every Oracle Technical Developer Should Know