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” 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169

/****************************************************************
*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;

For Further Reading

  1. API approach to find On Hand Quantity of an Item
  2. OPM Quality – Query to List Samples Information
  3. OPM Quality – Query to List Test Codes Information
  4. Oracle Script to Create User Login Account
  5. PL/SQL Script to Add a Concurrent Program to a Request Group from backend
  6. PL/SQL Script to Create an Executable from backend
  7. PL/SQL Script to Create a Concurrent Program Parameter from backend
OracleAppsDNA.com