OracleAppsDNA

OPM Quality: API to create Samples and Add Test Results to Samples

Here is an API approach to create Samples from backend and add Test results to Samples. Firstly we need to create Sample using API gmd_samples_pub.create_samples and then add Test Results to the Sample by using gmd_results_pub.add_tests_to_sample API.

Points to note

  1. Specification will be automatically picked up based on the item and org combination.
  2. Sample Type code should be picked up from GEM_LOOKUPS with lookup type as “SAMPLE_TYPE
  3. Sample Disposition code should be picked up from GEM_LOOKUPS with lookup type as “GMD_QC_SAMPLE_DISP
/****************************************************************
*PURPOSE: API to create Samples and Add Test Results to Samples *
*AUTHOR: Shailender Thallam                                     *
*****************************************************************/
DECLARE
   --
   l_sample                gmd_samples%ROWTYPE;
   l_username              fnd_user.user_name%TYPE          := 'SYSADMIN';
   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_count             NUMBER;
   x_msg_data              VARCHAR2 (2000);
   --
   --
   l_status_flag           VARCHAR2 (1);
   l_err_msg               VARCHAR2 (3999)                  := NULL;
   l_return_status         VARCHAR2 (10);
   l_msg_data              VARCHAR2 (4000);
   l_msg_count             NUMBER;
   l_number_tab            gmd_api_pub.number_tab;
   l_samples               gmd_samples%ROWTYPE;
   l_sample_id             NUMBER;
   l_sample_no             VARCHAR2 (20);
   l_test_id               NUMBER;
   l_event_spec_disp_id    NUMBER;
--
--Cursor to get specification information
--
BEGIN
   --
   fnd_global.apps_initialize (0, 54476, 552);
   --
   fnd_message.CLEAR;
   --
   l_sample_rec.sample_qty := .01;
   l_sample_rec.sample_qty_uom := 'VAL';
   l_sample_rec.source := 'W'; --WIP Type
   l_sample_rec.sampler_id := 0;
   l_sample_rec.lab_organization_id := 11362;
   l_sample_rec.organization_id := 11362;
   l_sample_rec.inventory_item_id := 296395;
   l_sample_rec.batch_id := 2993332;
   l_sample_rec.sample_disposition := '1P'; --Pending
   l_sample_rec.delete_mark := 0;
   l_sample_rec.sample_type := 'I'; --Item type
   l_sample_rec.lot_number := '6200001';
   --
   --Calling API to create Item Sample
   --
   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
                           );
   --
   DBMS_OUTPUT.put_line (   'Status: '
                         || x_return_status
                         || '-'
                         || x_msg_count
                         || '-'
                         || x_msg_data
                        );
   l_sample_id := x_qc_samples_rec.sample_id;
   DBMS_OUTPUT.put_line ('Sample ID: ' || l_sample_id);
   l_sample_no := x_qc_samples_rec.sample_no;
   DBMS_OUTPUT.put_line ('Sample Number: ' || l_sample_no);

   --
   IF x_msg_count > 1
   THEN
      FOR i IN 1 .. x_msg_count
      LOOP
         DBMS_OUTPUT.put_line
                      (   i
                       || '. '
                       || SUBSTR
                               (fnd_msg_pub.get (p_encoded      => fnd_api.g_false),
                                1,
                                255
                               )
                      );
      END LOOP;
   END IF;

   -- create results
   l_status_flag := 'P';
   l_err_msg := NULL;
   l_test_id := 58;
   l_return_status := NULL;
   l_msg_data := NULL;
   l_msg_count := NULL;
   l_samples.sample_id := l_sample_id;
   l_number_tab (1) := l_test_id;

   SELECT event_spec_disp_id
     INTO l_event_spec_disp_id
     FROM gmd_sample_spec_disp
    WHERE sample_id = l_sample_id;
   --
   --Calling API to add Test Results to Sample
   --
   gmd_results_pub.add_tests_to_sample
                            (p_api_version             => 2.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_user_name               => 'SYSADMIN',
                             p_sample_rec              => l_samples,
                             p_test_id_tab             => l_number_tab,
                             p_event_spec_disp_id      => l_event_spec_disp_id,
                             x_results_tab             => x_results_tab,
                             x_spec_results_tab        => x_spec_results_tab,
                             x_return_status           => l_return_status,
                             x_msg_count               => l_msg_count,
                             x_msg_data                => l_msg_data
                            );
   COMMIT;

   IF (l_return_status <> fnd_api.g_ret_sts_success)
   THEN
      FOR i IN 1 .. fnd_msg_pub.count_msg
      LOOP
         DBMS_OUTPUT.put_line
                      (   i
                       || '. '
                       || SUBSTR
                               (fnd_msg_pub.get (p_encoded      => fnd_api.g_false),
                                1,
                                255
                               )
                      );
      END LOOP;

      l_err_msg := l_err_msg || l_msg_data;
      l_status_flag := 'E';
   END IF;

   DBMS_OUTPUT.put_line ('l_err_msg create result:' || l_err_msg);
EXCEPTION
   WHEN OTHERS
   THEN
      l_status_flag := 'E';
      l_err_msg :=
         l_err_msg || '|' || 'Unknown error ' || '|'
         || SUBSTR (SQLERRM, 1, 250);
      DBMS_OUTPUT.put_line ('l_err_msg:' || l_err_msg);
END;
Exit mobile version