[Home] [Help]
PACKAGE: APPS.GMD_SAMPLES_GRP
Source
1 PACKAGE GMD_SAMPLES_GRP AUTHID CURRENT_USER AS
2 /* $Header: GMDGSMPS.pls 120.10.12020000.1 2012/06/27 07:30:52 appldev ship $ */
3
4 -- Start of comments
5 --+==========================================================================+
6 --| Copyright (c) 1998 Oracle Corporation |
7 --| Redwood Shores, CA, USA |
8 --| All rights reserved. |
9 --+==========================================================================+
10 --| File Name : GMDGSMPS.pls |
11 --| Package Name : GMD_Samples_GRP |
12 --| Type : Group |
13 --| |
14 --| Notes |
15 --| This package contains group layer APIs for Specification Entity |
16 --| |
17 --| HISTORY |
18 --| Chetan Nagar 26-Jul-2002 Created. |
19 --| RLNAGARA 20-Dec-2005 Bug# 4880152 Added the revision variable in the sample_disp_rec
20 --| Joe DiIorio 01/25/2006 Added grade_code to sample_display_rec.
21 --| M. Grosser 02/28/2006: BUG 5016617 -Added supplier_name to |
22 --| sample_display_rec. |
23 --+==========================================================================+
24 -- End of comments
25
26
27
28 -- Bug 2952823: changes for manual spec matching including new spec_vr_id parameter
29 FUNCTION sampling_event_exist
30 (
31 p_sample IN gmd_samples%ROWTYPE
32 , x_sampling_event_id OUT NOCOPY NUMBER
33 , p_spec_vr_id IN NUMBER DEFAULT NULL
34 )
35 RETURN BOOLEAN;
36
37
38 FUNCTION sampling_event_exist_wo_spec
39 (
40 p_sample IN gmd_samples%ROWTYPE
41 , x_sampling_event_id OUT NOCOPY NUMBER
42 )
43 RETURN BOOLEAN;
44
45 FUNCTION sample_exist
46 (
47 p_organization_id NUMBER
48 , p_sample_no VARCHAR2
49 )
50 RETURN BOOLEAN;
51
52 PROCEDURE validate_sample
53 (
54 p_sample IN GMD_SAMPLES%ROWTYPE
55 , p_called_from IN VARCHAR2 DEFAULT 'API'
56 , p_operation IN VARCHAR2
57 , x_return_status OUT NOCOPY VARCHAR2
58 );
59
60
61 TYPE update_disp_rec IS RECORD
62 (
63 sample_id NUMBER
64 ,composite_spec_disp_id NUMBER
65 ,event_spec_disp_id NUMBER
66 ,no_of_samples_for_event NUMBER
67 ,called_from_results VARCHAR2(1)
68 ,sampling_event_id NUMBER
69 ,curr_disposition VARCHAR2(3)
70 );
71
72
73 TYPE update_change_disp_rec IS RECORD
74 (
75 CHANGE_DISP_ID NUMBER
76 ,ORGANIZATION_ID NUMBER
77 ,INVENTORY_ITEM_ID NUMBER
78 ,SAMPLE_ID NUMBER
79 ,SAMPLING_EVENT_ID NUMBER
80 ,CREATION_DATE DATE
81 ,CREATED_BY NUMBER(15)
82 ,LAST_UPDATED_BY NUMBER(15)
83 ,LAST_UPDATE_DATE DATE
84 ,LAST_UPDATE_LOGIN NUMBER(15)
85 ,DISPOSITION_FROM VARCHAR2(3)
86 ,DISPOSITION_TO VARCHAR2(3)
87 ,PARENT_LOT_NUMBER VARCHAR2(80)
88 ,LOT_NUMBER VARCHAR2(80)
89 ,TO_LOT_STATUS_ID NUMBER
90 ,FROM_LOT_STATUS_ID NUMBER
91 ,TO_GRADE_CODE VARCHAR2(150)
92 ,FROM_GRADE_CODE VARCHAR2(150)
93 ,REASON_ID NUMBER
94 ,HOLD_DATE DATE
95 );
96
97 PROCEDURE update_change_disp_table
98 (
99 p_update_change_disp_rec IN UPDATE_CHANGE_DISP_REC
100 , x_return_status OUT NOCOPY VARCHAR2
101 , x_message_data OUT NOCOPY VARCHAR2
102 ) ;
103
104 PROCEDURE update_sample_comp_disp
105 (
106 p_update_disp_rec IN UPDATE_DISP_REC
107 , p_to_disposition IN VARCHAR2
108 , x_return_status OUT NOCOPY VARCHAR2
109 , x_message_data OUT NOCOPY VARCHAR2
110 );
111
112
113 PROCEDURE update_lot_grade_batch
114 (
115 p_sample_id IN NUMBER DEFAULT NULL
116 , p_composite_spec_disp_id IN NUMBER DEFAULT NULL
117 , p_to_lot_status_id IN NUMBER
118 , p_from_lot_status_id IN NUMBER
119 , p_to_grade_code IN VARCHAR2
120 , p_from_grade_code IN VARCHAR2 DEFAULT NULL
121 , p_to_qc_status IN NUMBER
122 , p_reason_id IN NUMBER
123 , p_hold_date IN DATE DEFAULT SYSDATE
124 , x_return_status OUT NOCOPY VARCHAR2
125 , x_message_data OUT NOCOPY VARCHAR2
126 );
127
128
129 PROCEDURE check_for_null_and_fks_in_smpl
130 (
131 p_sample IN GMD_SAMPLES%ROWTYPE
132 , x_return_status OUT NOCOPY VARCHAR2
133 );
134
135
136 PROCEDURE create_inv_txn
137 ( p_sample IN GMD_SAMPLES%ROWTYPE
138 , p_user_name IN NUMBER
139 , x_return_status OUT NOCOPY VARCHAR2
140 , x_message_count OUT NOCOPY NUMBER
141 , x_message_data OUT NOCOPY VARCHAR2
142 );
143
144
145 PROCEDURE create_wip_txn
146 ( p_sample IN GMD_SAMPLES%ROWTYPE
147 , x_return_status OUT NOCOPY VARCHAR2
148 , x_message_count OUT NOCOPY NUMBER
149 , x_message_data OUT NOCOPY VARCHAR2
150 );
151
152
153 PROCEDURE post_wip_txn
154 ( p_batch_id IN NUMBER
155 , x_return_status OUT NOCOPY VARCHAR2
156 );
157
158
159 -- Bug 2752102: added this procedure
160 PROCEDURE get_max_test_method_duration
161 ( p_spec_id IN NUMBER
162 , x_test_dur OUT NOCOPY NUMBER
163 , x_test_code OUT NOCOPY VARCHAR2
164 );
165
166 -- Bug 3088216: added this procedure
167 PROCEDURE update_remaining_qty
168 ( p_result_id IN NUMBER,
169 p_sample_id IN NUMBER default 0,
170 qty IN NUMBER,
171 x_return_status OUT NOCOPY VARCHAR2
172 );
173
174 -- Bug 4165704: added these procedures for inventory convergence
175 -- M. Grosser 02/28/2006: BUG 5016617 -Added supplier_name to
176 -- sample_display_rec.
177 --
178 TYPE sample_display_rec IS RECORD
179 (
180 Inventory_item_id NUMBER
181 ,Organization_id NUMBER
182 ,Sample_req_cnt NUMBER -- Sampling event fields
183 ,Sample_taken_cnt NUMBER
184 ,Archived_taken NUMBER
185 ,Reserved_taken NUMBER
186 ,Sample_active_cnt NUMBER
187 ,Sample_disposition VARCHAR2(3)
188 ,Sample_disposition_desc VARCHAR2(80)
189 ,locator_type NUMBER -- Subinventory fields
190 ,item_number VARCHAR2(2000) -- Item fields
191 ,item_description VARCHAR2(240)
192 ,revision VARCHAR2(3) --RLNAGARA Bug# 4880152
193 ,restrict_subinventories_code NUMBER
194 ,restict_locators_code NUMBER
195 ,revision_qty_control_code NUMBER
196 ,lot_control_code NUMBER
197 ,lot_status_enabled VARCHAR2(1)
198 ,lot_number VARCHAR2(80)
199 ,parent_lot_number VARCHAR2(80)
200 ,grade_control_flag VARCHAR2(1)
201 ,location_control_code NUMBER
202 ,primary_uom_code VARCHAR2(3)
203 ,dual_uom_control NUMBER
204 ,eng_item_flag VARCHAR2(1)
205 ,child_lot_flag VARCHAR2(1)
206 ,indivisible_flag VARCHAR2(1)
207 ,serial_number_control_code NUMBER
208 ,Locator VARCHAR2(2000) -- Inventory fields
209 ,subinventory_desc VARCHAR2(50)
210 ,restrict_locators_code NUMBER
211 ,supplier_no VARCHAR2(80) -- Supplier fields
212 ,supplier_name VARCHAR2(360)
213 ,sup_operating_unit_name VARCHAR2(240)
214 ,supplier_site VARCHAR2(40)
215 ,po_number VARCHAR2(80)
216 ,po_line_no NUMBER
217 ,receipt VARCHAR2(32)
218 ,receipt_line NUMBER
219 ,sup_restrict_locators_code NUMBER
220 ,cust_name VARCHAR2(360) -- Customer fields
221 ,Operating_unit_name VARCHAR2(240)
222 ,Ship_to_name VARCHAR2(40)
223 ,Order_number NUMBER
224 ,Order_type VARCHAR2(30)
225 ,Order_line_no VARCHAR2(80)
226 ,Batch_no VARCHAR2(32) -- WIP fields
227 ,Batch_status NUMBER
228 ,Recipe_no VARCHAR2(32)
229 ,Recipe_version NUMBER
230 ,Formula_no VARCHAR2(32)
231 ,Formula_vers NUMBER
232 ,Formula_type VARCHAR2(80)
233 ,Formula_line NUMBER
234 ,Routing_no VARCHAR2(32)
235 ,Routing_vers NUMBER
236 ,Step_no NUMBER
237 ,Oprn_no VARCHAR2(32)
238 ,Oprn_vers NUMBER
239 ,Source_locator VARCHAR2(2000)
240 ,Source_locator_id NUMBER
241 ,Source_subinventory VARCHAR2(10)
242 ,Ss_organization_code VARCHAR2(3) -- Stability Study fields
243 ,Ss_no VARCHAR2(30)
244 ,Variant_no NUMBER
245 ,Storage_organization_code VARCHAR2(3)
246 ,Storage_locator VARCHAR2(2000)
247 ,Storage_locator_id NUMBER
248 ,Storage_subinventory VARCHAR2(10)
249 ,Variant_resource VARCHAR2(30)
250 ,Variant_instance NUMBER
251 ,Time_point_name VARCHAR2(80)
252 ,Scheduled_date DATE
253 ,Instance_number NUMBER -- Resource field
254 ,Resources VARCHAR2(16)
255 ,Sampler VARCHAR2(100)
256 ,Sampler_name VARCHAR2(240)
257 ,Creation_date DATE
258 ,Lab_organization_code VARCHAR2(3) -- Lab organization code
259 ,sample_no VARCHAR2(80)
260 ,sampling_event_id NUMBER
261 ,retain_as VARCHAR2(3)
262 ,sample_type VARCHAR2(2)
263 ,source VARCHAR2(1)
264 ,subinventory VARCHAR2(10)
265 ,po_header_id NUMBER
266 ,grade_code VARCHAR2(150)
267 ,lpn VARCHAR2(32) --RLNAGARA LPN ME 7027149
268 );
269
270 PROCEDURE Sample_source_display (
271 p_id IN NUMBER
272 , p_type IN VARCHAR2
273 , x_display OUT NOCOPY sample_display_rec
274 , x_return_status OUT NOCOPY VARCHAR2);
275
276 PROCEDURE Inventory_source (
277 p_locator_id IN NUMBER
278 ,p_subinventory IN VARCHAR2
279 ,p_organization_id IN NUMBER
280 , x_display IN OUT NOCOPY sample_display_rec);
281
282 PROCEDURE Supplier_source (
283 p_supplier_id IN NUMBER
284 ,p_po_header_id IN NUMBER
285 ,p_po_line_id IN NUMBER
286 ,p_receipt_id IN NUMBER
287 ,p_receipt_line_id IN NUMBER
288 ,p_supplier_site_id IN NUMBER
289 ,p_org_id IN NUMBER
290 ,p_organization_id IN NUMBER
291 ,p_subinventory IN VARCHAR2
292 , x_display IN OUT NOCOPY sample_display_rec);
293
294 PROCEDURE Customer_source (
295 p_ship_to_site_id IN NUMBER
296 , p_org_id IN NUMBER
297 , p_order_id IN NUMBER
298 , p_order_line_id IN NUMBER
299 , p_cust_id IN NUMBER
300 , x_display IN OUT NOCOPY sample_display_rec);
301
302 PROCEDURE Stability_study_source (
303 p_variant_id IN NUMBER
304 ,p_time_point_id IN NUMBER
305 , x_display IN OUT NOCOPY sample_display_rec);
306
307 PROCEDURE Physical_location_source (
308 p_locator_id IN NUMBER
309 ,p_subinventory IN VARCHAR2
310 ,p_organization_id IN NUMBER
311 , x_display IN OUT NOCOPY sample_display_rec);
312
313 PROCEDURE Resource_source (
314 p_instance_id IN NUMBER
315 , x_display IN OUT NOCOPY sample_display_rec);
316
317 PROCEDURE Wip_source (
318 p_batch_id IN NUMBER
319 , p_step_id IN NUMBER
320 , p_recipe_id IN NUMBER
321 , p_formula_id IN NUMBER
322 , p_formulaline_id IN NUMBER
323 , p_material_detail_id IN NUMBER
324 , p_routing_id IN NUMBER
325 , p_oprn_id IN NUMBER
326 , p_inventory_item_id IN NUMBER
327 , p_organization_id IN NUMBER
328 , x_display IN OUT NOCOPY sample_display_rec);
329
330 PROCEDURE Get_item_values (p_sample_display IN OUT NOCOPY sample_display_rec);
331
332 TYPE sample_source_rec IS RECORD
333 (
334 disposition VARCHAR2(3)
335 ,sample_id NUMBER
336 ,sample_disposition_desc VARCHAR2(80)
337 ,sample_source_desc VARCHAR2(80)
338 ) ;
339
340 PROCEDURE get_sample_spec_disposition (
341 p_sample IN OUT NOCOPY SAMPLE_SOURCE_REC
342 ,x_return_status OUT NOCOPY VARCHAR2
343 );
344
345
346 END GMD_SAMPLES_GRP;
347