[Home] [Help]
PACKAGE: APPS.GMD_OUTBOUND_APIS_PUB
Source
1 PACKAGE GMD_OUTBOUND_APIS_PUB AUTHID CURRENT_USER AS
2 /* $Header: GMDOAPIS.pls 120.14 2011/05/05 17:53:25 rnalla ship $ */
3 /*#
4 * This is Public level outbound GMD Quality API package
5 * This package defines and implements the procedures and datatypes
6 * required to fetch Results, Composite Results, Samples, Test Methods,
7 * Tests, Specification Validity Rules and Sample Groups.
8 * @rep:scope public
9 * @rep:product GMD
10 * @rep:lifecycle active
11 * @rep:displayname Public level outbound GMD Quality API package
12 * @rep:compatibility S
13 * @rep:category BUSINESS_ENTITY GMD_OUTBOUND_APIS_PUB
14 */
15
16 --****************************************************************************************
17 --* FILE: GMDOAPIS.pls *
21 --* AUTHOR: Paul Schofield, OPM Development EMEA *
18 --* *
19 --* CONTENTS: Public level outbound GMD Quality API package *
20 --* *
22 --* *
23 --* DATE: May 20th 2003 *
24 --* *
25 --* VERSION CHANGED BY CHANGE *
26 --* ======= ========== ====== *
27 --* 20May03 P.J.Schofield New file *
28 --* 17Jun03 K.Y.Hunt Merged in 3 new APIs *
29 --* 04Jul03 P.J.Schofield Added user_name parameters *
30 --* 28Aug03 Chetan Nagar Added mini-pack K specific features. *
31 --* 15Jan04 Brenda Stone Added mini-pack L specific features. *
32 --* 24Feb04 Brenda Stone Bug 3394055; added L columns to Result a Result *
33 --* 02May05 Saikiran Vankadari Convergence changes done for fetch_spec_vrs() procedure.*
34 --* Changed all references of OPM Inventory tables to Discrete inventory tables*
35 --* 10Oct05 RLNAGARA Bug # 4548546 -- Added from and to revision variables *
36 --* 10Nov05 RLNAGARA Bug # 4616835 -- Changed all the references of TYPE *
37 --* objects to the SYSTEM schema. *
38 --* 02Jun08 PLOWE Bug # 7027149 support for LPN *
39 --* *
40 --****************************************************************************************
41 --* *
42 --* COPYRIGHT (c) Oracle Corporation 2003 *
43 --* *
44 --****************************************************************************************
45
46
47 api_version NUMBER := 2.0;
48
49 g_from_test_method_code VARCHAR2(80);
50 g_to_test_method_code VARCHAR2(80);
51 g_test_method_code VARCHAR2(80);
52 g_test_method_id NUMBER(10);
53 g_test_kit_organization_id NUMBER(10);
54 g_test_kit_inv_item_id NUMBER(10);
55 g_resource VARCHAR2(16);
56 g_delete_mark NUMBER(5);
57 g_from_last_update_date DATE;
58 g_to_last_update_date DATE;
59 g_test_id NUMBER(10);
60 g_test_code VARCHAR2(80);
61 g_priority VARCHAR2(2);
62 g_test_type VARCHAR2(1);
63 g_test_class VARCHAR2(8);
64 g_from_test_code VARCHAR2(240);
65 g_to_test_code VARCHAR2(240);
66 g_from_spec_name VARCHAR2(240);
67 g_to_spec_name VARCHAR2(240);
68 g_spec_id NUMBER(10);
69 g_spec_version NUMBER(5);
70 g_spec_delete_mark NUMBER(5);
71 g_from_grade_code VARCHAR2(150);
72 g_to_grade_code VARCHAR2(150);
73 g_from_item_number VARCHAR2(240); /*--NSRIVAST, INVCONV*/
74 g_to_item_number VARCHAR2(240); /*--NSRIVAST, INVCONV*/
75 g_from_inventory_item_id NUMBER; /*NSRIVAST, INVCONV*/
76 g_to_inventory_item_id NUMBER; /*NSRIVAST, INVCONV*/
77 g_from_revision VARCHAR2(3); -- RLNAGARA Bug # 4548546
78 g_to_revision VARCHAR2(3); -- RLNAGARA Bug # 4548546
79 g_inventory_item_id NUMBER;
80 g_item_id NUMBER;
81 g_from_spec_last_update DATE;
82 g_to_spec_last_update DATE;
83 g_spec_type VARCHAR2(2);
84
85 g_owner_organization_code VARCHAR2(3); /*--NSRIVAST, INVCONV*/
86 g_owner_orgn_id MTL_ORGANIZATIONS.ORGANIZATION_ID%TYPE ; /*NSRIVAST, INVCONV*/
87
88 g_spec_status NUMBER;
89 g_test_qty_uom VARCHAR2(3);
90 g_from_test_last_update DATE;
91 g_to_test_last_update DATE;
92 g_test_priority VARCHAR2(2);
93 g_test_delete_mark NUMBER(5);
94
95 -- START B3124291 Incorporated Mini-Pack K Features to Outboud APIs
96 -- Spec Related
97 g_overlay_ind VARCHAR2(1);
98 g_base_spec_id NUMBER;
99 g_base_spec_name VARCHAR2(240);
100 g_base_spec_version NUMBER;
101
102 -- Spec Test Related
103 g_from_base_ind VARCHAR2(1);
104 g_exclude_ind VARCHAR2(1);
105 g_modified_ind VARCHAR2(1);
106 g_calc_uom_conv_ind VARCHAR2(1);
107 g_to_qty_uom VARCHAR2(3);
108
109 -- END B3124291 Incorporated Mini-Pack K Features to Outboud APIs
110
111
112 g_wip_vr_status NUMBER;
113 g_wip_vr_organization_code VARCHAR2(3); /*--NSRIVAST, INVCONV*/
114 g_wip_vr_batch_orgn_code VARCHAR2(3); /*--NSRIVAST, INVCONV*/
115 g_wip_vr_orgn_id MTL_ORGANIZATIONS.ORGANIZATION_ID%TYPE ; /*NSRIVAST, INVCONV*/
116 g_wip_vr_batch_orgn_id MTL_ORGANIZATIONS.ORGANIZATION_ID%TYPE ; /*NSRIVAST, INVCONV*/
117
118 g_wip_vr_batch_no VARCHAR2(32);
119 g_wip_vr_batch_id NUMBER;
120 g_wip_vr_recipe_no VARCHAR2(80);
121 g_wip_vr_recipe_version NUMBER;
122 g_wip_vr_recipe_id NUMBER;
123 g_wip_vr_formula_no VARCHAR2(32);
124 g_wip_vr_formula_version NUMBER;
125 g_wip_vr_formula_id NUMBER;
126 g_wip_vr_formulaline_no NUMBER;
127 g_wip_vr_formulaline_id NUMBER;
128 g_wip_vr_line_type NUMBER;
132 g_wip_vr_step_no NUMBER;
129 g_wip_vr_routing_no VARCHAR2(32);
130 g_wip_vr_routing_version NUMBER;
131 g_wip_vr_routing_id NUMBER;
133 g_wip_vr_step_id NUMBER;
134 g_wip_vr_operation_no VARCHAR2(16);
135 g_wip_vr_operation_version NUMBER;
136 g_wip_vr_operation_id NUMBER;
137 g_wip_vr_start_date DATE;
138 g_wip_vr_end_date DATE;
139 g_wip_vr_coa_type VARCHAR2(1);
140 g_wip_vr_sampling_plan VARCHAR2(80);
141 g_wip_vr_sampling_plan_id NUMBER;
142 g_wip_vr_delete_mark NUMBER;
143 g_wip_vr_from_last_update DATE;
144 g_wip_vr_to_last_update DATE;
145
146 g_cust_vr_start_date DATE;
147 g_cust_vr_end_date DATE;
148 g_cust_vr_status NUMBER;
149 g_cust_vr_organization_code VARCHAR2(3); /*--NSRIVAST, INVCONV*/
150 g_cust_vr_orgn_id MTL_ORGANIZATIONS.ORGANIZATION_ID%TYPE ; /*NSRIVAST, INVCONV*/
151
152 g_cust_vr_org_id NUMBER;
153 g_cust_vr_coa_type VARCHAR2(1);
154 g_cust_vr_customer VARCHAR2(240);
155 g_cust_vr_customer_id NUMBER;
156 g_cust_vr_order_number NUMBER;
157 g_cust_vr_order_id NUMBER;
158 g_cust_vr_order_type NUMBER;
159 g_cust_vr_order_line_no NUMBER;
160 g_cust_vr_order_line_id NUMBER;
161 g_cust_vr_ship_to_location VARCHAR2(240);
162 g_cust_vr_ship_to_site_id NUMBER;
163 g_cust_vr_operating_unit VARCHAR(240);
164 g_cust_vr_delete_mark NUMBER;
165 g_cust_vr_from_last_update DATE;
166 g_cust_vr_to_last_update DATE;
167
168 g_supl_vr_start_date DATE;
169 g_supl_vr_end_date DATE;
170 g_supl_vr_status NUMBER;
171 g_supl_vr_organization_code VARCHAR2(3); /*--NSRIVAST, INVCONV*/
172 g_supl_vr_orgn_id MTL_ORGANIZATIONS.ORGANIZATION_ID%TYPE ; /*NSRIVAST, INVCONV*/
173
174 g_supl_vr_org_id NUMBER;
175 g_supl_vr_coa_type VARCHAR2(1);
176 g_supl_vr_supplier VARCHAR2(240);
177 g_supl_vr_supplier_id NUMBER;
178 g_supl_vr_po_number NUMBER;
179 g_supl_vr_po_id NUMBER;
180 g_supl_vr_po_line_no NUMBER;
181 g_supl_vr_po_line_id NUMBER;
182 g_supl_vr_supplier_site VARCHAR2(240);
183 g_supl_vr_supplier_site_id NUMBER;
184 g_supl_vr_operating_unit VARCHAR(240);
185 g_supl_vr_delete_mark NUMBER;
186 g_supl_vr_from_last_update DATE;
187 g_supl_vr_to_last_update DATE;
188
189 g_inv_vr_start_date DATE;
190 g_inv_vr_end_date DATE;
191 g_inv_vr_status NUMBER;
192 g_inv_vr_organization_code VARCHAR2(3); /*--NSRIVAST, INVCONV*/
193 g_inv_vr_orgn_id MTL_ORGANIZATIONS.ORGANIZATION_ID%TYPE ; /*NSRIVAST, INVCONV*/
194
195 g_inv_vr_org_id NUMBER;
196 g_inv_vr_coa_type VARCHAR2(1);
197 g_inv_vr_item_number VARCHAR2(40);
198 g_inv_vr_inventory_item_id NUMBER;
199 g_inv_vr_parent_lot_number VARCHAR2(80);
200 g_inv_vr_lot_number VARCHAR2(80);
201 g_inv_vr_subinventory MTL_SECONDARY_INVENTORIES.SECONDARY_INVENTORY_NAME%TYPE ;/*NSRIVAST, INVCONV*/
202 g_inv_vr_locator VARCHAR2(204);
203 g_inv_vr_locator_id NUMBER;
204 g_inv_vr_sampling_plan VARCHAR2(80);
205 g_inv_vr_sampling_plan_id NUMBER;
206 g_inv_vr_delete_mark NUMBER;
207 g_inv_vr_from_last_update DATE;
208 g_inv_vr_to_last_update DATE;
209 -- START B3124291 Incorporated Mini-Pack K Features to Outboud APIs
210 g_mon_vr_status NUMBER(5);
211 g_mon_vr_rule_type VARCHAR2(2);
212 g_mon_vr_lct_organization_code VARCHAR2(3); /*--NSRIVAST, INVCONV*/
213 g_mon_vr_loct_orgn_id MTL_ORGANIZATIONS.ORGANIZATION_ID%TYPE ; /*NSRIVAST, INVCONV*/
214
215 g_mon_vr_subinventory MTL_SECONDARY_INVENTORIES.SECONDARY_INVENTORY_NAME%TYPE ;/*NSRIVAST, INVCONV*/
216 g_mon_vr_locator_id NUMBER;
217 g_mon_vr_locator VARCHAR2(204);
218 g_mon_vr_rsr_organization_code VARCHAR2(3); /*--NSRIVAST, INVCONV*/
219 g_mon_vr_resource_orgn_id MTL_ORGANIZATIONS.ORGANIZATION_ID%TYPE ; /*NSRIVAST, INVCONV*/
220
221 g_mon_vr_resources VARCHAR2(16);
222 g_mon_vr_resource_instance_id NUMBER;
223 g_mon_vr_sampling_plan VARCHAR2(80);
224 g_mon_vr_sampling_plan_id NUMBER;
225 g_mon_vr_start_date DATE;
226 g_mon_vr_end_date DATE;
227 g_mon_vr_from_last_update_date DATE;
228 g_mon_vr_to_last_update_date DATE;
229 g_mon_vr_delete_mark NUMBER(5);
230 -- END B3124291 Incorporated Mini-Pack K Features to Outboud APIs
231
232
233
234
235 g_orgn_code VARCHAR2(4); /*--NSRIVAST, INVCONV*/
236 g_qc_lab_orgn_code VARCHAR2(4); /*--NSRIVAST, INVCONV*/
237 g_orgn_id MTL_ORGANIZATIONS.ORGANIZATION_ID%TYPE ; /*NSRIVAST, INVCONV*/
238 g_lab_organization_id MTL_ORGANIZATIONS.ORGANIZATION_ID%TYPE ; /*NSRIVAST, INVCONV*/
239 g_ss_organization_id MTL_ORGANIZATIONS.ORGANIZATION_ID%TYPE ; /*sxfeinst, INVCONV*/
240
241 g_from_sample_no VARCHAR2(80);
242 g_to_sample_no VARCHAR2(80);
243 g_sample_id NUMBER;
244 g_from_result_date DATE;
245 g_to_result_date DATE;
246 --g_sample_disposition VARCHAR2(3);
247 g_in_spec_ind VARCHAR2(1);
248 g_evaluation_ind VARCHAR2(2);
249 g_tester VARCHAR2(30);
250 g_tester_id NUMBER;
251 g_test_provider_id NUMBER;
252 g_planned_resource VARCHAR2(16);
253 g_actual_resource VARCHAR2(16);
254 g_planned_resource_instance NUMBER;
255 g_actual_resource_instance NUMBER;
256 g_from_test_by_date DATE;
257 g_to_test_by_date DATE;
258 --g_from_last_update_date DATE;
259 --g_to_last_update_date DATE;
260 -- START B3124291 Incorporated Mini-Pack K Features to Outboud APIs
264 --g_actual_resource_instance NUMBER;
261 --g_planned_resource VARCHAR2(16);
262 --g_planned_resource_instance NUMBER;
263 --g_actual_resource VARCHAR2(16);
265 g_from_planned_result_date DATE;
266 g_to_planned_result_date DATE;
267 --g_from_test_by_date DATE;
268 --g_to_test_by_date DATE;
269 -- END B3124291 Incorporated Mini-Pack K Features to Outboud APIs
270
271
272 g_sampling_event_id NUMBER;
273 g_from_lot_number VARCHAR2(80);
274 g_to_lot_number VARCHAR2(80);
275 g_lot_number VARCHAR2(80); /*NSRIVAST, INVCONV*/
276 g_lot_id NUMBER; /*--NSRIVAST, INVCONV*/
277
278 g_sample_type VARCHAR2(2);
279 g_sublot_no VARCHAR2(32); /*NSRIVAST, INVCONV*/
280 --g_priority VARCHAR2(2);
281 g_spec_name VARCHAR2(80);
282 g_spec_vers NUMBER(10);
283 g_source VARCHAR2(1);
284 g_from_date_drawn DATE;
285 g_to_date_drawn DATE;
286 g_from_expiration_date DATE;
287 g_to_expiration_date DATE;
288
289 g_source_whse VARCHAR2(4); /*--NSRIVAST, INVCONV*/
290 g_source_subinventory MTL_SECONDARY_INVENTORIES.SECONDARY_INVENTORY_NAME%TYPE ;/*NSRIVAST, INVCONV*/
291
292 g_source_location VARCHAR2(16);
293 g_grade VARCHAR2(4);
294 g_sample_disposition VARCHAR2(3);
295 g_storage_whse VARCHAR2(4); /*--NSRIVAST, INVCONV*/
296 g_storage_subinventory MTL_SECONDARY_INVENTORIES.SECONDARY_INVENTORY_NAME%TYPE ;/*NSRIVAST, INVCONV*/
297
298 g_storage_location VARCHAR2(16);
299 --g_qc_lab_orgn_code VARCHAR2(4);
300 g_external_id VARCHAR2(32);
301 g_sampler VARCHAR2(30);
302 g_from_date_required DATE;
303 g_to_date_required DATE;
304 g_from_date_received DATE;
305 g_to_date_received DATE;
306 g_lot_retest_ind VARCHAR2(1);
307 g_whse_code VARCHAR2(4); /*--NSRIVAST, INVCONV*/
308 g_subinventory MTL_SECONDARY_INVENTORIES.SECONDARY_INVENTORY_NAME%TYPE ;/*NSRIVAST, INVCONV*/
309
310 g_location VARCHAR(16);
311 g_location_id NUMBER;
312 g_locator_id GMD_SAMPLES.locator_id%TYPE; /*--SXFEINST, INVCONV*/
313 g_source_locator_id GMD_SAMPLES.locator_id%TYPE; /*--SXFEINST, INVCONV*/
314 g_storage_locator_id GMD_SAMPLES.locator_id%TYPE; /*--SXFEINST, INVCONV*/
315
316 g_wip_plant_code VARCHAR2(4);
317 g_wip_batch_no VARCHAR2(32);
318 g_wip_batch_id NUMBER;
319 g_wip_recipe_no VARCHAR2(32);
320 g_wip_recipe_version NUMBER;
321 g_wip_recipe_id NUMBER;
322 g_wip_formula_no VARCHAR2(32);
323 g_wip_formula_id NUMBER;
324 g_wip_formulaline NUMBER(5);
325 g_wip_formulaline_id NUMBER;
326 g_wip_line_type NUMBER(5);
327 g_wip_routing_no VARCHAR2(32);
328 g_wip_routing_vers NUMBER(5);
329 g_wip_routing_id NUMBER;
330 g_wip_batchstep_no NUMBER(10);
331 g_wip_batchstep_id NUMBER(15);
332 g_wip_oprn_no VARCHAR2(16);
333 g_wip_oprn_vers NUMBER(5);
334 g_wip_oprn_id NUMBER;
335 g_cust_name VARCHAR2(240);
336 g_cust_id NUMBER;
337 --g_cust_org_id NUMBER; /* INVCONV,SXFEINST */
338 g_org_id NUMBER;
339 g_cust_ship_to_site_id NUMBER;
340 g_cust_order NUMBER;
341 g_cust_order_id NUMBER;
342 g_cust_order_type VARCHAR2(30);
343 g_cust_order_line NUMBER;
344 g_cust_order_line_id NUMBER;
345 g_supplier VARCHAR2(30);
346 g_supplier_id NUMBER;
347 g_supplier_site_id NUMBER;
348 g_supplier_po VARCHAR2(20);
349 g_supplier_po_id NUMBER;
350 g_supplier_po_line NUMBER;
351 g_supplier_po_line_id NUMBER;
352 g_non_item_resource VARCHAR2(16);
353 g_non_item_resource_instance NUMBER;
354
355 g_from_lot_no VARCHAR2(80);
356 g_to_lot_no VARCHAR2(80);
357 g_from_parent_lot_number VARCHAR2(80); /*--sxfeinst, INVCONV*/
358 g_to_parent_lot_number VARCHAR2(80); /*--sxfeinst, INVCONV*/
359 g_parent_lot_number VARCHAR2(80); /*--sxfeinst, INVCONV*/
360 g_from_sublot_no VARCHAR2(32); /*--NSRIVAST, INVCONV*/
361 g_to_sublot_no VARCHAR2(32); /*--NSRIVAST, INVCONV*/
362 g_sample_event_id NUMBER;
363 g_disposition VARCHAR2(80);
364
365 g_wip_orgn_code VARCHAR2(4); /*--NSRIVAST, INVCONV*/
366 g_wip_orgn_id MTL_ORGANIZATIONS.ORGANIZATION_ID%TYPE ; /*NSRIVAST, INVCONV*/
367
368 g_wip_recipe_vers NUMBER;
369 g_wip_formula_vers NUMBER;
370 g_wip_formulaline_no NUMBER;
371 g_wip_formulaline_type NUMBER;
372 --g_wip_routing_no VARCHAR2(32);
373 --g_wip_routing_vers NUMBER;
374 --g_wip_routing_id NUMBER;
375 g_wip_step_no NUMBER;
376 g_wip_step_id NUMBER;
377 --g_wip_oprn_vers NUMBER;
378 g_customer VARCHAR2(240);
379 g_customer_id NUMBER;
380 g_customer_org_id NUMBER;
381 g_customer_ship_to_location VARCHAR2(240);
382 g_customer_ship_to_location_id NUMBER;
383 g_customer_order_number NUMBER;
384 g_customer_order_id NUMBER;
385 g_customer_order_type NUMBER;
386 g_customer_order_line NUMBER;
387 g_customer_order_line_id NUMBER;
388 g_supplier_site VARCHAR2(240);
389 g_supplier_po_number NUMBER;
390 -- START B3124291 Incorporated Mini-Pack K Features to Outboud APIs
391 --g_from_date_received DATE;
392 --g_to_date_received DATE;
393 --g_from_date_required DATE;
394 --g_to_date_required DATE;
395 g_resources VARCHAR2(16);
396 g_instance_id NUMBER;
397 g_from_retrieval_date DATE;
398 g_to_retrieval_date DATE;
402 g_ss_no VARCHAR2(30);
399 --g_sample_type VARCHAR2(2);
400 g_ss_id NUMBER;
401 --g_ss_orgn_code VARCHAR2(4);
403 g_variant_id NUMBER;
404 g_variant_no NUMBER;
405 g_time_point_id NUMBER;
406 --g_sg_orgn_code VARCHAR2(4);/*sxfeinst, INVCONV*/
407 g_sg_organization_id MTL_ORGANIZATIONS.ORGANIZATION_ID%TYPE ; /*sxfeinst, INVCONV*/
408 -- END B3124291 Incorporated Mini-Pack K Features to Outboud APIs
409 -- Start Incorporated Mini-Pack L Features to Outboud APIs
410 g_reserve_sample_id NUMBER;
411 g_retain_as VARCHAR2(3);
412 -- END Incorporated Mini-Pack L Features to Outboud APIs
413
414 /*These variables need to be deleted once all the procedures in this package
415 are changed for convergence*/
416 g_from_item_no VARCHAR2(240); /*--NSRIVAST, INVCONV*/
417 g_to_item_no VARCHAR2(240); /*--NSRIVAST, INVCONV*/
418 g_from_inventory_item_id NUMBER; /*NSRIVAST, INVCONV*/
419 g_to_inventory_item_id NUMBER; /*NSRIVAST, INVCONV*/
420
421 -- PLOWE Bug # 7027149 support for LPN
422 g_lpn_id NUMBER;
423 g_lpn VARCHAR2(30);
424
425
426 g_test_methods_table system.gmd_test_methods_tab_type;
427 g_tests_table system.gmd_qc_tests_tab_type;
428 g_specifications_table system.gmd_specifications_tab_type;
429 g_results_table system.gmd_results_tab_type;
430 g_composite_results_table system.gmd_composite_results_tab_type;
431 g_samples_table system.gmd_samples_tab_type;
432 g_sample_groups_table system.gmd_sampling_events_tab_type;
433
434 /*#
435 * Fetches Sample Test Results
436 * This is a PL/SQL procedure to fetch Sample Test Results satisfying
437 * the query criterion passed through parameters.
438 * @param p_api_version API version field
439 * @param p_init_msg_list Flag to check if message list is initialized
440 * @param p_user_name Login User Name
441 * @param p_orgn_code Organization Code
442 * @param p_from_sample_no Starting Sample number
443 * @param p_to_sample_no Ending Sample number
444 * @param p_sample_id Sample Identifier
445 * @param p_from_result_date From Result Date
446 * @param p_to_result_date To Result Date
447 * @param p_sample_disposition Sample Disposition
448 * @param p_in_spec_ind In Specification Indicator
449 * @param p_qc_lab_orgn_code Lab Organization Code
450 * @param p_evaluation_ind Evaluation Indicator
451 * @param p_tester Tester
452 * @param p_tester_id Tester Identifier
453 * @param p_test_provider_id Test Provider Identifier
454 * @param p_delete_mark Delete Mark
455 * @param p_from_last_update_date Starting Last Update Date
456 * @param p_to_last_update_date Ending Last Update Date
457 * @param p_planned_resource Planned Resource
458 * @param p_planned_resource_instance Planned Resource Instance
459 * @param p_actual_resource Actual Resource
460 * @param p_actual_resource_instance Actual Resource Instance
461 * @param p_from_planned_result_date Starting Planned Result Date
462 * @param p_to_planned_result_date Ending Planned Result Date
463 * @param p_from_test_by_date Starting Test By Date
464 * @param p_to_test_by_date Ending Test By Date
465 * @param p_reserve_sample_id Reserve Sample Identifier
466 * @param x_results_table Table Structure of Results table
467 * @param x_return_status 'S'-Success, 'E'-Error, 'U'-Unexpected Error
468 * @param x_msg_count Number of messages on message stack
469 * @param x_msg_data Actual message data on message stack
470 * @rep:scope public
471 * @rep:lifecycle active
472 * @rep:displayname Fetch Sample Test Results procedure
473 * @rep:compatibility S
474 */
475 PROCEDURE fetch_results
476 ( p_api_version IN NUMBER
477 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
478 , p_user_name IN VARCHAR2
479 , p_orgn_code IN VARCHAR2 DEFAULT NULL
480 , p_from_sample_no IN VARCHAR2 DEFAULT NULL
481 , p_to_sample_no IN VARCHAR2 DEFAULT NULL
482 , p_sample_id IN NUMBER DEFAULT NULL
483 , p_from_result_date IN DATE DEFAULT NULL
484 , p_to_result_date IN DATE DEFAULT NULL
485 , p_sample_disposition IN VARCHAR2 DEFAULT NULL
486 , p_in_spec_ind IN VARCHAR2 DEFAULT NULL
487 , p_qc_lab_orgn_code IN VARCHAR2 DEFAULT NULL
488 , p_evaluation_ind IN VARCHAR2 DEFAULT NULL
489 , p_tester IN VARCHAR2 DEFAULT NULL
490 , p_tester_id IN NUMBER DEFAULT NULL
491 , p_test_provider_id IN NUMBER DEFAULT NULL
492 , p_delete_mark IN NUMBER DEFAULT NULL
493 , p_from_last_update_date IN DATE DEFAULT NULL
494 , p_to_last_update_date IN DATE DEFAULT NULL
495 , p_planned_resource IN VARCHAR2 DEFAULT NULL
496 , p_planned_resource_instance IN NUMBER DEFAULT NULL
497 , p_actual_resource IN VARCHAR2 DEFAULT NULL
498 , p_actual_resource_instance IN NUMBER DEFAULT NULL
499 , p_from_planned_result_date IN DATE DEFAULT NULL
500 , p_to_planned_result_date IN DATE DEFAULT NULL
501 , p_from_test_by_date IN DATE DEFAULT NULL
502 , p_to_test_by_date IN DATE DEFAULT NULL
503 , p_reserve_sample_id IN NUMBER DEFAULT NULL
504 , x_results_table OUT NOCOPY system.gmd_results_tab_type
505 , x_return_status OUT NOCOPY VARCHAR2
506 , x_msg_count OUT NOCOPY NUMBER
507 , x_msg_data OUT NOCOPY VARCHAR2
508 );
509
510 /*#
511 * Fetches Composite Results
512 * This is a PL/SQL procedure to fetch Composite Results satisfying
513 * the query criterion passed through parameters.
514 * @param p_api_version API version field
518 * @param p_composite_result_disposition Composite Result Disposition
515 * @param p_init_msg_list Flag to check if message list is initialized
516 * @param p_user_name Login User Name
517 * @param p_sampling_event_id Sampling Event Identifier
519 * @param p_from_item_number Starting Item Number
520 * @param p_to_item_number Ending Item Number
521 * @param p_inventory_item_id Item Identifier
522 * @param p_from_lot_number Starting Lot Number
523 * @param p_lot_number Ending Lot Number
524 * @param p_from_last_update_date Starting Last Update Date
525 * @param p_to_last_update_date Ending Last Update Date
526 * @param p_delete_mark Delete Mark
527 * @param x_composite_results_table Table Structure of Composite Results
528 * @param x_return_status 'S'-Success, 'E'-Error, 'U'-Unexpected Error
529 * @param x_msg_count Number of messages on message stack
530 * @param x_msg_data Actual message data on message stack
531 * @rep:scope public
532 * @rep:lifecycle active
533 * @rep:displayname Fetch Composite Results procedure
534 * @rep:compatibility S
535 */
536 PROCEDURE fetch_composite_results
537 ( p_api_version IN NUMBER
538 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
539 , p_user_name IN VARCHAR2
540 , p_sampling_event_id IN NUMBER DEFAULT NULL
541 , p_composite_result_disposition IN VARCHAR2 DEFAULT NULL
542 , p_from_item_number IN VARCHAR2 DEFAULT NULL /*NSRIVAST, INVCONV*/
543 , p_to_item_number IN VARCHAR2 DEFAULT NULL /*NSRIVAST, INVCONV*/
544 , p_inventory_item_id IN NUMBER DEFAULT NULL /*NSRIVAST, INVCONV*/
545 , p_from_lot_number IN VARCHAR2 DEFAULT NULL /*NSRIVAST, INVCONV*/
546 , p_to_lot_number IN VARCHAR2 DEFAULT NULL /*NSRIVAST, INVCONV*/
547 , p_lot_number IN VARCHAR2 DEFAULT NULL /*NSRIVAST, INVCONV*/
548 , p_from_last_update_date IN DATE DEFAULT NULL
549 , p_to_last_update_date IN DATE DEFAULT NULL
550 , p_delete_mark IN NUMBER DEFAULT NULL
551 , x_composite_results_table OUT NOCOPY system.gmd_composite_results_tab_type
552 , x_return_status OUT NOCOPY VARCHAR2
553 , x_msg_count OUT NOCOPY NUMBER
554 , x_msg_data OUT NOCOPY VARCHAR2
555 );
556
557 /*#
558 * Fetches Samples
559 * This is a PL/SQL procedure to fetch Samples satisfying
560 * the query criterion passed through parameters.
561 * @param p_api_version API version field
562 * @param p_init_msg_list Flag to check if message list is initialized
563 * @param p_user_name Login User Name
564 * @param p_organization_id Organization
565 * @param p_from_sample_no Starting Sample Number
566 * @param p_to_sample_no Ending Sample Number
567 * @param p_sample_id Sample Identifier
568 * @param p_sampling_event_id Sampling Event Identifier
569 * @param p_from_item_number Starting Item Number
570 * @param p_to_item_number Ending Item Number
571 * @param p_inventory_item_id Item Identifier
572 * @param p_from_lot_number Starting Lot Number
573 * @param p_to_lot_number Ending Lot Number
574 * @param p_parent_lot_number Sub lot Number
575 * @param p_priority Priority
576 * @param p_spec_name Specification Name
577 * @param p_spec_vers Specification Version
578 * @param p_spec_id Specification Identifier
579 * @param p_source Source
580 * @param p_from_date_drawn Starting Date Drawn
581 * @param p_to_date_drawn Ending Date Drawn
582 * @param p_from_expiration_date Starting Expiration Date
583 * @param p_to_expiration_date Ending Expiration Date
584 * @param p_source_subinventory Source Subinventory
585 * @param p_source_locator_id Source Locator_id
586 * @param p_grade_code Grade
587 * @param p_sample_disposition Sample Disposition
588 * @param p_storage_subinventory Storage Subinventory
589 * @param p_storage_locator_id Storage Locator
590 * @param p_lab_organization_id Lab Organization Code
591 * @param p_external_id External Identifer
592 * @param p_sampler Sampler
593 * @param p_lot_retest_ind Lot Retest Identifier
594 * @param p_subinventory Subinventory Code
595 * @param p_locator_id Locator Identifier
596 * @param p_wip_plant_code WIP Plant Code
597 * @param p_wip_batch_no WIP Batch Number
598 * @param p_wip_batch_id WIP Batch Identifier
599 * @param p_wip_recipe_no WIP Recipe Number
600 * @param p_wip_recipe_version WIP Recipe Version
601 * @param p_wip_recipe_id WIP Recipe Identifier
602 * @param p_wip_formula_no WIP Formula Number
603 * @param p_wip_formula_version WIP Formula Version
604 * @param p_wip_formula_id WIP Formula Identifier
605 * @param p_wip_formulaline WIP Formula Line
606 * @param p_wip_formulaline_id WIP Formula Line Identifier
607 * @param p_wip_line_type WIP Line Type
608 * @param p_wip_routing_no WIP Routing Number
609 * @param p_wip_routing_vers WIP Routing Version
610 * @param p_wip_routing_id WIP Routing Identifier
611 * @param p_wip_batchstep_no WIP Batch Step Number
612 * @param p_wip_batchstep_id WIP Batch Step Identifier
613 * @param p_wip_oprn_no WIP Operation Number
614 * @param p_wip_oprn_vers WIP Operation Version
615 * @param p_wip_oprn_id WIP Operation Identifier
616 * @param p_cust_name Customer Name
617 * @param p_cust_id Customer Identifier
618 * @param p_org_id Organization Identifier for Customer or Supplier
619 * @param p_cust_ship_to_site_id Customer Ship to Site Identifier
620 * @param p_cust_order Customer Order
621 * @param p_cust_order_id Customer Order Identifier
622 * @param p_cust_order_type Customer Order Type
623 * @param p_cust_order_line Customer Order Line
624 * @param p_cust_order_line_id Customer Order Line Identifier
625 * @param p_supplier Supplier
626 * @param p_supplier_id Supplier Identifier
627 * @param p_supplier_site_id Supplier Site Identifier
628 * @param p_supplier_po Supplier Purchase Order
632 * @param p_from_date_received Starting Date Received
629 * @param p_supplier_po_id Supplier Purchase Order Identifier
630 * @param p_supplier_po_line Supplier Purchase Order Line
631 * @param p_supplier_po_line_id Supplier Purchase Order Line Identifier
633 * @param p_to_date_received Ending Date Received
634 * @param p_from_date_required Starting Date Required
635 * @param p_to_date_required Ending Date Required
636 * @param p_resources Resource
637 * @param p_instance_id Resource Instance Identifier
638 * @param p_from_retrieval_date Starting Retrieval Date
639 * @param p_to_retrieval_date Ending Retrieval Date
640 * @param p_sample_type Sample Type
641 * @param p_ss_id Stability Study Identifier
642 * @param p_ss_organization_id Stability Study Organization
643 * @param p_ss_no Stability Study Number
644 * @param p_variant_id Variant Identifier
645 * @param p_variant_no Variant Number
646 * @param p_time_point_id Time Point Identifier
647 * @param p_from_last_update_date Starting Last Update Date
648 * @param p_to_last_update_date Ending Last Update Date
649 * @param p_retain_as Retain As
650 * @param p_delete_mark Delete Mark
651 * @param p_lpn License Plate Number
652 * @param p_lpn_id License Plate Number Identifier
653 * @param x_samples_table Table Structure of Samples
654 * @param x_return_status 'S'-Success, 'E'-Error, 'U'-Unexpected Error
655 * @param x_msg_count Number of messages on message stack
656 * @param x_msg_data Actual message data on message stack
657 * @rep:scope public
658 * @rep:lifecycle active
659 * @rep:displayname Fetch Samples procedure
660 * @rep:compatibility S
661 */
662 PROCEDURE fetch_samples
663 ( p_api_version IN NUMBER
664 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
665 , p_user_name IN VARCHAR2
666 , p_organization_id IN NUMBER DEFAULT NULL
667 , p_from_sample_no IN VARCHAR2 DEFAULT NULL
668 , p_to_sample_no IN VARCHAR2 DEFAULT NULL
669 , p_sample_id IN NUMBER DEFAULT NULL
670 , p_sampling_event_id IN NUMBER DEFAULT NULL
671 , p_from_item_number IN VARCHAR2 DEFAULT NULL /*bug 4165704, INVCONV*/
672 , p_to_item_number IN VARCHAR2 DEFAULT NULL /*bug 4165704, INVCONV*/
673 , p_inventory_item_id IN NUMBER DEFAULT NULL /*bug 4165704, INVCONV*/
674 , p_revision IN VARCHAR2 DEFAULT NULL /*bug 4165704, INVCONV*/
675 , p_from_lot_number IN VARCHAR2 DEFAULT NULL /*bug 4165704, INVCONV*/
676 , p_to_lot_number IN VARCHAR2 DEFAULT NULL /*bug 4165704, INVCONV*/
677 , p_parent_lot_number IN VARCHAR2 DEFAULT NULL /*bug 4165704, INVCONV*/
678 , p_priority IN VARCHAR2 DEFAULT NULL
679 , p_spec_name IN VARCHAR2 DEFAULT NULL
680 , p_spec_vers IN VARCHAR2 DEFAULT NULL
681 , p_spec_id IN NUMBER DEFAULT NULL
682 , p_source IN VARCHAR2 DEFAULT NULL
683 , p_from_date_drawn IN DATE DEFAULT NULL
684 , p_to_date_drawn IN DATE DEFAULT NULL
685 , p_from_expiration_date IN DATE DEFAULT NULL
686 , p_to_expiration_date IN DATE DEFAULT NULL
687 , p_from_date_received IN DATE DEFAULT NULL
688 , p_to_date_received IN DATE DEFAULT NULL
689 , p_from_date_required IN DATE DEFAULT NULL
690 , p_to_date_required IN DATE DEFAULT NULL
691 , p_resources IN VARCHAR2 DEFAULT NULL
692 , p_instance_id IN NUMBER DEFAULT NULL
693 , p_from_retrieval_date IN DATE DEFAULT NULL
694 , p_to_retrieval_date IN DATE DEFAULT NULL
695 , p_sample_type IN VARCHAR2 DEFAULT NULL
696 , p_ss_id IN NUMBER DEFAULT NULL
697 , p_ss_organization_id IN VARCHAR2 DEFAULT NULL
698 , p_ss_no IN VARCHAR2 DEFAULT NULL
699 , p_variant_id IN NUMBER DEFAULT NULL
700 , p_variant_no IN NUMBER DEFAULT NULL
701 , p_time_point_id IN NUMBER DEFAULT NULL
702 , p_source_subinventory IN VARCHAR2 DEFAULT NULL
703 , p_source_locator_id IN NUMBER DEFAULT NULL
704 , p_grade_code IN VARCHAR2 DEFAULT NULL
705 , p_sample_disposition IN VARCHAR2 DEFAULT NULL
706 , p_storage_subinventory IN VARCHAR2 DEFAULT NULL
707 , p_storage_locator_id IN NUMBER DEFAULT NULL
708 , p_lab_organization_id IN VARCHAR2 DEFAULT NULL
709 , p_external_id IN VARCHAR2 DEFAULT NULL
710 , p_sampler IN VARCHAR2 DEFAULT NULL
711 , p_lot_retest_ind IN VARCHAR2 DEFAULT NULL
712 , p_subinventory IN VARCHAR2 DEFAULT NULL
713 , p_locator_id IN NUMBER DEFAULT NULL
714 , p_wip_plant_code IN VARCHAR2 DEFAULT NULL
715 , p_wip_batch_no IN VARCHAR2 DEFAULT NULL
716 , p_wip_batch_id IN NUMBER DEFAULT NULL
717 , p_wip_recipe_no IN VARCHAR2 DEFAULT NULL
718 , p_wip_recipe_version IN NUMBER DEFAULT NULL
719 , p_wip_recipe_id IN NUMBER DEFAULT NULL
720 , p_wip_formula_no IN VARCHAR2 DEFAULT NULL
721 , p_wip_formula_version IN NUMBER DEFAULT NULL
722 , p_wip_formula_id IN NUMBER DEFAULT NULL
723 , p_wip_formulaline IN NUMBER DEFAULT NULL
724 , p_wip_formulaline_id IN NUMBER DEFAULT NULL
725 , p_wip_line_type IN NUMBER DEFAULT NULL
726 , p_wip_routing_no IN VARCHAR2 DEFAULT NULL
727 , p_wip_routing_vers IN NUMBER DEFAULT NULL
728 , p_wip_routing_id IN NUMBER DEFAULT NULL
729 , p_wip_batchstep_no IN NUMBER DEFAULT NULL
730 , p_wip_batchstep_id IN NUMBER DEFAULT NULL
731 , p_wip_oprn_no IN VARCHAR2 DEFAULT NULL
735 , p_cust_id IN NUMBER DEFAULT NULL
732 , p_wip_oprn_vers IN NUMBER DEFAULT NULL
733 , p_wip_oprn_id IN NUMBER DEFAULT NULL
734 , p_cust_name IN VARCHAR2 DEFAULT NULL
736 , p_org_id IN NUMBER DEFAULT NULL
737 , p_cust_ship_to_site_id IN NUMBER DEFAULT NULL
738 , p_cust_order IN VARCHAR2 DEFAULT NULL
739 , p_cust_order_id IN NUMBER DEFAULT NULL
740 , p_cust_order_type IN VARCHAR2 DEFAULT NULL
741 , p_cust_order_line IN NUMBER DEFAULT NULL
742 , p_cust_order_line_id IN NUMBER DEFAULT NULL
743 , p_supplier IN VARCHAR2 DEFAULT NULL
744 , p_supplier_id IN NUMBER DEFAULT NULL
745 , p_supplier_site_id IN NUMBER DEFAULT NULL
746 , p_supplier_po IN VARCHAR2 DEFAULT NULL
747 , p_supplier_po_id IN NUMBER DEFAULT NULL
748 , p_supplier_po_line IN NUMBER DEFAULT NULL
749 , p_supplier_po_line_id IN NUMBER DEFAULT NULL
750 , p_from_last_update_date IN DATE DEFAULT NULL
751 , p_to_last_update_date IN DATE DEFAULT NULL
752 , p_retain_as IN VARCHAR2 DEFAULT NULL
753 , p_delete_mark IN NUMBER DEFAULT NULL
754 , p_lpn IN VARCHAR2 DEFAULT NULL -- 7027149
755 , p_lpn_id IN NUMBER DEFAULT NULL-- 7027149
756 , x_samples_table OUT NOCOPY system.gmd_samples_tab_type
757 , x_return_status OUT NOCOPY VARCHAR2
758 , x_msg_count OUT NOCOPY NUMBER
759 , x_msg_data OUT NOCOPY VARCHAR2
760 );
761
762 /*#
763 * Fetches Test Methods
764 * This is a PL/SQL procedure to fetch Test Methods satisfying
765 * the query criterion passed through parameters.
766 * @param p_api_version API version field
767 * @param p_init_msg_list Flag to check if message list is initialized
768 * @param p_user_name Login User Name
769 * @param p_from_test_method_code Starting Test Method Code
770 * @param p_to_test_method_code Ending Test Code
771 * @param p_test_method_id Test Method Identifier
772 * @param p_test_kit_organization_id Test Kit Item Number
773 * @param p_test_kit_inv_item_id Test Kit Item Identifier
774 * @param p_resource Resource
775 * @param p_delete_mark Delete Mark
776 * @param p_from_last_update_date Starting Last Update Date
777 * @param p_to_last_update_date Ending Last Update Date
778 * @param x_test_methods_table Test Methods Table
779 * @param x_return_status 'S'-Success, 'E'-Error, 'U'-Unexpected Error
780 * @param x_msg_count Number of messages on message stack
781 * @param x_msg_data Actual message data on message stack
782 * @rep:scope public
783 * @rep:lifecycle active
784 * @rep:displayname Fetch Test Methods procedure
785 * @rep:compatibility S
786 */
787 PROCEDURE fetch_test_methods
788 ( p_api_version IN NUMBER
789 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
790 , p_user_name IN VARCHAR2
791 , p_from_test_method_code IN VARCHAR2 DEFAULT NULL
792 , p_to_test_method_code IN VARCHAR2 DEFAULT NULL
793 , p_test_method_id IN NUMBER DEFAULT NULL
794 , p_test_kit_organization_id IN NUMBER DEFAULT NULL
795 , p_test_kit_inv_item_id IN NUMBER DEFAULT NULL
796 , p_resource IN VARCHAR2 DEFAULT NULL
797 , p_delete_mark IN NUMBER DEFAULT NULL
798 , p_from_last_update_date IN DATE DEFAULT NULL
799 , p_to_last_update_date IN DATE DEFAULT NULL
800 , x_test_methods_table OUT NOCOPY system.gmd_test_methods_tab_type
801 , x_return_status OUT NOCOPY VARCHAR2
802 , x_msg_count OUT NOCOPY NUMBER
803 , x_msg_data OUT NOCOPY VARCHAR2
804 );
805
806 /*#
807 * Fetches Tests
808 * This is a PL/SQL procedure to fetch Tests satisfying
809 * the query criterion passed through parameters.
810 * @param p_api_version API version field
811 * @param p_init_msg_list Flag to check if message list is initialized
812 * @param p_user_name Login User Name
813 * @param p_from_test_code Starting Test Code
814 * @param p_to_test_code Ending Test Code
815 * @param p_from_test_method_code Starting Test Method Code
816 * @param p_to_test_method_code Ending Test Method Code
817 * @param p_test_id Testing Identifier
818 * @param p_test_method_id Test Method Identifier
819 * @param p_test_class Test Class
820 * @param p_test_type Test Type
821 * @param p_priority Priority
822 * @param p_delete_mark Delete Mark
823 * @param p_from_last_update_date Starting Last Update Date
824 * @param p_to_last_update_date Ending Last Update Date
825 * @param x_tests_table Table Structure of Tests
826 * @param x_return_status 'S'-Success, 'E'-Error, 'U'-Unexpected Error
827 * @param x_msg_count Number of messages on message stack
828 * @param x_msg_data Actual message data on message stack
829 * @rep:scope public
830 * @rep:lifecycle active
831 * @rep:displayname Fetch Tests procedure
832 * @rep:compatibility S
833 */
834 PROCEDURE fetch_tests
835 ( p_api_version IN NUMBER
836 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
837 , p_user_name IN VARCHAR2
838 , p_from_test_code IN VARCHAR2 DEFAULT NULL
839 , p_to_test_code IN VARCHAR2 DEFAULT NULL
840 , p_from_test_method_code IN VARCHAR2 DEFAULT NULL
841 , p_to_test_method_code IN VARCHAR2 DEFAULT NULL
842 , p_test_id IN NUMBER DEFAULT NULL
843 , p_test_method_id IN NUMBER DEFAULT NULL
844 , p_test_class IN VARCHAR2 DEFAULT NULL
845 , p_test_type IN VARCHAR2 DEFAULT NULL
846 , p_priority IN VARCHAR2 DEFAULT NULL
847 , p_delete_mark IN NUMBER DEFAULT NULL
848 , p_from_last_update_date IN DATE DEFAULT NULL
849 , p_to_last_update_date IN DATE DEFAULT NULL
850 , x_tests_table OUT NOCOPY system.gmd_qc_tests_tab_type
851 , x_return_status OUT NOCOPY VARCHAR2
852 , x_msg_count OUT NOCOPY NUMBER
853 , x_msg_data OUT NOCOPY VARCHAR2
854 );
855
856 /*#
857 * Fetches Specification Validity Rules
858 * This is a PL/SQL procedure to fetch Tests satisfying
859 * the query criterion passed through parameters.
860 * @param p_api_version API version field
861 * @param p_init_msg_list Flag to check if message list is initialized
862 * @param p_user_name Login User Name
863 * @param p_from_spec_name Starting Specification Name
864 * @param p_to_spec_name Ending Specification Name
865 * @param p_spec_id Specification Identifier
866 * @param p_spec_version Specification Version
867 * @param p_from_grade_code Starting Grade Code
868 * @param p_to_grade_code Ending Grade Code
869 * @param p_from_item_number Starting Item Number
870 * @param p_to_item_number Ending Item Number
871 * @param p_inventory_item_id Item Identifier
872 * @param p_from_spec_last_update Starting Specification Last Update Date
873 * @param p_to_spec_last_update Ending Specification Last Update Date
874 * @param p_spec_status Specification Status
875 * @param p_owner_organization_code Owner Organization Code
876 * @param p_spec_delete_mark Specification Delete Mark
877 * @param p_overlay_ind Overlay Flag
878 * @param p_spec_type Specification Type
879 * @param p_base_spec_id Base Specification Identifier
880 * @param p_base_spec_name Base Specification Name
881 * @param p_base_spec_version Base Specification Version
882 * @param p_test_code Test Code
883 * @param p_test_id Test Identifier
884 * @param p_test_method_code Test Method Code
885 * @param p_test_method_id Test Method Identifier
886 * @param p_test_qty_uom Test Unit Of Measure
887 * @param p_test_priority Test Priority
888 * @param p_from_test_last_update Starting Test Last Update Date
889 * @param p_to_test_last_update Ending Test Last Update Date
890 * @param p_test_delete_mark Test Delete Mark
891 * @param p_from_base_ind From Base Indicator
892 * @param p_exclude_ind Exclude Indicator
893 * @param p_modified_ind Modified Indicator
894 * @param p_calc_uom_conv_ind Calculate uom Conversion Indicator
895 * @param p_to_qty_uom To uom
896 * @param p_wip_vr_status WIP Validity Rule Status
897 * @param p_wip_vr_organization_code WIP Validity Rule Organization
898 * @param p_wip_vr_batch_orgn_code WIP Validity Rule Batch Organization
899 * @param p_wip_vr_batch_no WIP Validity Rule Batch Number
900 * @param p_wip_vr_batch_id WIP Validity Rule Batch Identifier
901 * @param p_wip_vr_recipe_no WIP Validity Rule Recipe Number
902 * @param p_wip_vr_recipe_version WIP Validity Rule Recipe Version
903 * @param p_wip_vr_recipe_id WIP Validity Rule Recipe Identifier
904 * @param p_wip_vr_formula_no WIP Validity Rule Formula Number
905 * @param p_wip_vr_formula_version WIP Validity Rule Formula Version
906 * @param p_wip_vr_formula_id WIP Validity Rule Formula Identifier
907 * @param p_wip_vr_formulaline_no WIP Validity Rule Formula Line Number
908 * @param p_wip_vr_formulaline_id WIP Validity Rule Formula Line Identifier
909 * @param p_wip_vr_line_type WIP Validity Rule Line Type
910 * @param p_wip_vr_routing_no WIP Validity Rule Routing Number
911 * @param p_wip_vr_routing_version WIP Validity Rule Routing Version
912 * @param p_wip_vr_routing_id WIP Validity Rule Routing Identifier
913 * @param p_wip_vr_step_no WIP Validity Rule Step Number
914 * @param p_wip_vr_step_id WIP Validity Rule Step Identifier
915 * @param p_wip_vr_operation_no WIP Validity Rule Operation Number
916 * @param p_wip_vr_operation_version WIP Validity Rule Operation Version
917 * @param p_wip_vr_operation_id WIP Validity Rule Operation Identifier
918 * @param p_wip_vr_start_date WIP Validity Rule Start Date
919 * @param p_wip_vr_end_date WIP Validity Rule End Date
920 * @param p_wip_vr_coa_type WIP Validity Rule Certificate Type
921 * @param p_wip_vr_sampling_plan WIP Validity Rule Sampling Plan
922 * @param p_wip_vr_sampling_plan_id WIP Validity Rule Sampling Plan Identifier
923 * @param p_wip_vr_delete_mark WIP Validity Rule Delete Mark
924 * @param p_wip_vr_from_last_update Starting WIP Validity Rule Last Update Date
925 * @param p_wip_vr_to_last_update Ending WIP Validity Rule Last Update Date
926 * @param p_cust_vr_start_date Customer Validity Rule Start Date
927 * @param p_cust_vr_end_date Customer Validity Rule End Date
928 * @param p_cust_vr_status Customer Validity Rule Status
929 * @param p_cust_vr_organization_code Customer Validity Rule Organization Code
930 * @param p_cust_vr_org_id Customer Validity Rule Operating unit Identifier
931 * @param p_cust_vr_coa_type Customer Validity Rule Certificate Type
932 * @param p_cust_vr_customer Customer Validity Customer
933 * @param p_cust_vr_customer_id Customer Validity Rule Customer Identifier
934 * @param p_cust_vr_order_number Customer Validity Rule Order Number
935 * @param p_cust_vr_order_id Customer Validity Rule Order Identifier
936 * @param p_cust_vr_order_type Customer Validity Rule Order Type
937 * @param p_cust_vr_order_line_no Customer Validity Rule Order Line Number
938 * @param p_cust_vr_order_line_id Customer Validity Rule Order Line Identifier
939 * @param p_cust_vr_ship_to_location Customer Validity Rule Ship to Location
940 * @param p_cust_vr_ship_to_site_id Customer Validity Rule Ship to Site Identifier
941 * @param p_cust_vr_operating_unit Customer Validity Rule Operating Unit
942 * @param p_cust_vr_delete_mark Customer Validity Rule Delete Mark
943 * @param p_cust_vr_from_last_update Starting Customer Validity Rule Last Update Date
944 * @param p_cust_vr_to_last_update Ending Customer Validity Rule Last Update Date
945 * @param p_supl_vr_start_date Supplier Validity Rule Start Date
946 * @param p_supl_vr_end_date Supplier Validity Rule End Date
947 * @param p_supl_vr_status Supplier Validity Rule Status
948 * @param p_supl_vr_organization_code Supplier Validity Rule Organization Code
949 * @param p_supl_vr_org_id Supplier Validity Rule Organization Identifier
950 * @param p_supl_vr_coa_type Supplier Validity Rule Certificate Type
951 * @param p_supl_vr_supplier Supplier Validity Rule Supplier
952 * @param p_supl_vr_supplier_id Supplier Validity Rule Supplier Identifier
953 * @param p_supl_vr_po_number Supplier Validity Rule Purchase Order Number
954 * @param p_supl_vr_po_id Supplier Validity Rule Purchase Order Identifier
955 * @param p_supl_vr_po_line_no Supplier Validity Rule Purchase Order Line Number
956 * @param p_supl_vr_po_line_id Supplier Validity Rule Purchase Order Line Identifier
957 * @param p_supl_vr_supplier_site Supplier Validity Rule Supplier Site
958 * @param p_supl_vr_supplier_site_id Supplier Validity Rule Supplier Site Identifier
959 * @param p_supl_vr_operating_unit Supplier Validity Rule Operating unit
960 * @param p_supl_vr_delete_mark Supplier Validity Rule Delete Mark
961 * @param p_supl_vr_from_last_update Starting Supplier Validity Rule Last Update Date
962 * @param p_supl_vr_to_last_update Ending Supplier Validity Rule Last Update Date
963 * @param p_inv_vr_start_date Inventory Validity Rule Start Date
964 * @param p_inv_vr_end_date Inventory Validity Rule End Date
965 * @param p_inv_vr_status Inventory Validity Rule Status
966 * @param p_inv_vr_organization_code Inventory Validity Rule Organization Code
967 * @param p_inv_vr_coa_type Inventory Validity Rule Certificate Type
968 * @param p_inv_vr_item_number Inventory Validity Rule Item Number
969 * @param p_inv_vr_inventory_item_id Inventory Validity Rule Item Identifier
970 * @param p_inv_vr_parent_lot_number Inventory Validity Rule Parent Lot Number
971 * @param p_inv_vr_lot_number Inventory Validity Rule Lot Number
972 * @param p_inv_vr_subinventory Inventory Validity Rule Subinventory
973 * @param p_inv_vr_locator Inventory Validity Rule Locator
974 * @param p_inv_vr_locator_id Inventory Validity Rule Locator Identifier
975 * @param p_inv_vr_sampling_plan Inventory Validity Rule Sampling Plan
976 * @param p_inv_vr_sampling_plan_id Inventory Validity Rule Sampling Plan Identifier
977 * @param p_inv_vr_delete_mark Inventory Validity Rule Delete Mark
978 * @param p_inv_vr_from_last_update Starting Inventory Validity Rule Last Update Date
979 * @param p_inv_vr_to_last_update Ending Inventory Validity Rule Last Update Date
980 * @param p_mon_vr_status Monitoring Validity Rule Status
981 * @param p_mon_vr_rule_type Monitoring Validity Rule Type
982 * @param p_mon_vr_lct_organization_code Monitoring Validity Rule Location Organization Code
983 * @param p_mon_vr_subinventory Monitoring Validity Rule Subinventory
984 * @param p_mon_vr_locator_id Monitoring Validity Rule Locator Identifier
985 * @param p_mon_vr_locator Monitoring Validity Rule Locator
986 * @param p_mon_vr_rsr_organization_code Monitoring Validity Rule Resource Organization Code
987 * @param p_mon_vr_resources Monitoring Validity Rule Resources
988 * @param p_mon_vr_resource_instance_id Monitoring Validity Rule Resource Instance Identifier
989 * @param p_mon_vr_sampling_plan Monitoring Validity Rule Sampling Plan
990 * @param p_mon_vr_sampling_plan_id Monitoring Validity Rule Sampling Plan Identifier
991 * @param p_mon_vr_start_date Monitoring Validity Rule Start Date
992 * @param p_mon_vr_end_date Monitoring Validity Rule End Date
993 * @param p_mon_vr_from_last_update_date Starting Monitoring Validity Rule Last Update Date
994 * @param p_mon_vr_to_last_update_date Ending Monitoring Validity Rule Last Update Date
995 * @param p_mon_vr_delete_mark Monitoring Validity Rule Delete Mark
996 * @param x_specifications_tbl Table Structure of Specifications
997 * @param x_return_status 'S'-Success, 'E'-Error, 'U'-Unexpected Error
998 * @param x_msg_count Number of messages on message stack
999 * @param x_msg_data Actual message data on message stack
1000 * @rep:scope public
1001 * @rep:lifecycle active
1002 * @rep:displayname Fetch Specification Validity Rules procedure
1003 * @rep:compatibility S
1004 */
1005 PROCEDURE fetch_spec_vrs
1006 ( p_api_version IN NUMBER
1010 -- Parameters relating to specifications
1007 , p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
1008 , p_user_name IN VARCHAR2
1009
1011
1012 , p_from_spec_name IN VARCHAR2 DEFAULT NULL
1013 , p_to_spec_name IN VARCHAR2 DEFAULT NULL
1014 , p_spec_id IN NUMBER DEFAULT NULL
1015 , p_spec_version IN NUMBER DEFAULT NULL
1016 , p_from_grade_code IN VARCHAR2 DEFAULT NULL
1017 , p_to_grade_code IN VARCHAR2 DEFAULT NULL
1018 , p_from_item_number IN VARCHAR2 DEFAULT NULL
1019 , p_to_item_number IN VARCHAR2 DEFAULT NULL
1020 , p_inventory_item_id IN NUMBER DEFAULT NULL
1021 , p_from_revision IN VARCHAR2 DEFAULT NULL -- RLNAGARA Bug # 4548546
1022 , p_to_revision IN VARCHAR2 DEFAULT NULL -- RLNAGARA Bug # 4548546
1023 , p_from_spec_last_update IN DATE DEFAULT NULL
1024 , p_to_spec_last_update IN DATE DEFAULT NULL
1025 , p_spec_status IN NUMBER DEFAULT NULL
1026 , p_owner_organization_code IN VARCHAR2 DEFAULT NULL
1027 , p_spec_delete_mark IN NUMBER DEFAULT NULL
1028
1029 -- START B3124291 Incorporated Mini-Pack K Features to Outboud APIs
1030 , p_overlay_ind IN VARCHAR2 DEFAULT NULL
1031 , p_spec_type IN VARCHAR2 DEFAULT NULL
1032 , p_base_spec_id IN NUMBER DEFAULT NULL
1033 , p_base_spec_name IN VARCHAR2 DEFAULT NULL
1034 , p_base_spec_version IN NUMBER DEFAULT NULL
1035 -- END B3124291 Incorporated Mini-Pack K Features to Outboud APIs
1036
1037 -- Parameters relating to spec tests
1038
1039 , p_test_code IN VARCHAR2 DEFAULT NULL
1040 , p_test_id IN NUMBER DEFAULT NULL
1041 , p_test_method_code IN VARCHAR2 DEFAULT NULL
1042 , p_test_method_id IN NUMBER DEFAULT NULL
1043 , p_test_qty_uom IN VARCHAR2 DEFAULT NULL
1044 , p_test_priority IN VARCHAR2 DEFAULT NULL
1045 , p_from_test_last_update IN DATE DEFAULT NULL
1046 , p_to_test_last_update IN DATE DEFAULT NULL
1047 , p_test_delete_mark IN NUMBER DEFAULT NULL
1048 -- START B3124291 Incorporated Mini-Pack K Features to Outboud APIs
1049 , p_from_base_ind IN VARCHAR2 DEFAULT NULL
1050 , p_exclude_ind IN VARCHAR2 DEFAULT NULL
1051 , p_modified_ind IN VARCHAR2 DEFAULT NULL
1052 , p_calc_uom_conv_ind IN VARCHAR2 DEFAULT NULL
1053 , p_to_qty_uom IN VARCHAR2 DEFAULT NULL
1054 -- END B3124291 Incorporated Mini-Pack K Features to Outboud APIs
1055
1056 -- Parameters relating to wip spec validity rules
1057
1058 , p_wip_vr_status IN NUMBER DEFAULT NULL
1059 , p_wip_vr_organization_code IN VARCHAR2 DEFAULT NULL
1060 , p_wip_vr_batch_orgn_code IN VARCHAR2 DEFAULT NULL
1061 , p_wip_vr_batch_no IN VARCHAR2 DEFAULT NULL
1062 , p_wip_vr_batch_id IN NUMBER DEFAULT NULL
1063 , p_wip_vr_recipe_no IN VARCHAR2 DEFAULT NULL
1064 , p_wip_vr_recipe_version IN NUMBER DEFAULT NULL
1065 , p_wip_vr_recipe_id IN NUMBER DEFAULT NULL
1066 , p_wip_vr_formula_no IN VARCHAR2 DEFAULT NULL
1067 , p_wip_vr_formula_version IN NUMBER DEFAULT NULL
1068 , p_wip_vr_formula_id IN NUMBER DEFAULT NULL
1069 , p_wip_vr_formulaline_no IN NUMBER DEFAULT NULL
1070 , p_wip_vr_formulaline_id IN NUMBER DEFAULT NULL
1071 , p_wip_vr_line_type IN NUMBER DEFAULT NULL
1072 , p_wip_vr_routing_no IN VARCHAR2 DEFAULT NULL
1073 , p_wip_vr_routing_version IN NUMBER DEFAULT NULL
1074 , p_wip_vr_routing_id IN NUMBER DEFAULT NULL
1075 , p_wip_vr_step_no IN NUMBER DEFAULT NULL
1076 , p_wip_vr_step_id IN NUMBER DEFAULT NULL
1077 , p_wip_vr_operation_no IN VARCHAR2 DEFAULT NULL
1078 , p_wip_vr_operation_version IN NUMBER DEFAULT NULL
1079 , p_wip_vr_operation_id IN NUMBER DEFAULT NULL
1080 , p_wip_vr_start_date IN DATE DEFAULT NULL
1081 , p_wip_vr_end_date IN DATE DEFAULT NULL
1082 , p_wip_vr_coa_type IN VARCHAR2 DEFAULT NULL
1083 , p_wip_vr_sampling_plan IN VARCHAR2 DEFAULT NULL
1084 , p_wip_vr_sampling_plan_id IN NUMBER DEFAULT NULL
1085 , p_wip_vr_delete_mark IN NUMBER DEFAULT NULL
1086 , p_wip_vr_from_last_update IN DATE DEFAULT NULL
1087 , p_wip_vr_to_last_update IN DATE DEFAULT NULL
1088
1089 -- Parameters relating to customer spec validity rules
1090 , p_cust_vr_start_date IN DATE DEFAULT NULL
1091 , p_cust_vr_end_date IN DATE DEFAULT NULL
1092 , p_cust_vr_status IN NUMBER DEFAULT NULL
1093 , p_cust_vr_organization_code IN VARCHAR2 DEFAULT NULL
1094 , p_cust_vr_org_id IN NUMBER DEFAULT NULL
1095 , p_cust_vr_coa_type IN VARCHAR2 DEFAULT NULL
1096 , p_cust_vr_customer IN VARCHAR2 DEFAULT NULL
1097 , p_cust_vr_customer_id IN NUMBER DEFAULT NULL
1098 , p_cust_vr_order_number IN NUMBER DEFAULT NULL
1099 , p_cust_vr_order_id IN NUMBER DEFAULT NULL
1100 , p_cust_vr_order_type IN NUMBER DEFAULT NULL
1101 , p_cust_vr_order_line_no IN NUMBER DEFAULT NULL
1102 , p_cust_vr_order_line_id IN NUMBER DEFAULT NULL
1103 , p_cust_vr_ship_to_location IN VARCHAR2 DEFAULT NULL
1104 , p_cust_vr_ship_to_site_id IN NUMBER DEFAULT NULL
1105 , p_cust_vr_operating_unit IN VARCHAR
1106 , p_cust_vr_delete_mark IN NUMBER DEFAULT NULL
1107 , p_cust_vr_from_last_update IN DATE DEFAULT NULL
1108 , p_cust_vr_to_last_update IN DATE DEFAULT NULL
1109
1110 -- Parameters relating to supplier spec validity rules
1111 , p_supl_vr_start_date IN DATE DEFAULT NULL
1112 , p_supl_vr_end_date IN DATE DEFAULT NULL
1113 , p_supl_vr_status IN NUMBER DEFAULT NULL
1114 , p_supl_vr_organization_code IN VARCHAR2 DEFAULT NULL
1115 , p_supl_vr_org_id IN NUMBER DEFAULT NULL
1116 , p_supl_vr_coa_type IN VARCHAR2 DEFAULT NULL
1117 , p_supl_vr_supplier IN VARCHAR2 DEFAULT NULL
1118 , p_supl_vr_supplier_id IN NUMBER DEFAULT NULL
1119 , p_supl_vr_po_number IN NUMBER DEFAULT NULL
1120 , p_supl_vr_po_id IN NUMBER DEFAULT NULL
1121 , p_supl_vr_po_line_no IN NUMBER DEFAULT NULL
1122 , p_supl_vr_po_line_id IN NUMBER DEFAULT NULL
1123 , p_supl_vr_supplier_site IN VARCHAR2 DEFAULT NULL
1124 , p_supl_vr_supplier_site_id IN NUMBER DEFAULT NULL
1125 , p_supl_vr_operating_unit IN VARCHAR2 DEFAULT NULL
1126 , p_supl_vr_delete_mark IN NUMBER DEFAULT NULL
1127 , p_supl_vr_from_last_update IN DATE DEFAULT NULL
1128 , p_supl_vr_to_last_update IN DATE DEFAULT NULL
1129
1130 -- Parameters relating to inventory spec validity rules
1131 , p_inv_vr_start_date IN DATE DEFAULT NULL
1132 , p_inv_vr_end_date IN DATE DEFAULT NULL
1133 , p_inv_vr_status IN NUMBER DEFAULT NULL
1134 , p_inv_vr_organization_code IN VARCHAR2 DEFAULT NULL
1135 , p_inv_vr_coa_type IN VARCHAR2 DEFAULT NULL
1136 , p_inv_vr_item_number IN VARCHAR2 DEFAULT NULL
1137 , p_inv_vr_inventory_item_id IN NUMBER DEFAULT NULL
1138 , p_inv_vr_parent_lot_number IN VARCHAR2 DEFAULT NULL
1139 , p_inv_vr_lot_number IN VARCHAR2 DEFAULT NULL
1140 , p_inv_vr_subinventory IN VARCHAR2 DEFAULT NULL
1141 , p_inv_vr_locator IN VARCHAR2 DEFAULT NULL
1142 , p_inv_vr_locator_id IN NUMBER DEFAULT NULL
1143 , p_inv_vr_sampling_plan IN VARCHAR2 DEFAULT NULL
1144 , p_inv_vr_sampling_plan_id IN NUMBER DEFAULT NULL
1145 , p_inv_vr_delete_mark IN NUMBER DEFAULT NULL
1146 , p_inv_vr_from_last_update IN DATE DEFAULT NULL
1147 , p_inv_vr_to_last_update IN DATE DEFAULT NULL
1148
1149 -- START B3124291 Incorporated Mini-Pack K Features to Outboud APIs
1150 -- Parameters relating to monitor spec
1151 , p_mon_vr_status IN NUMBER DEFAULT NULL
1152 , p_mon_vr_rule_type IN VARCHAR2 DEFAULT NULL
1153 , p_mon_vr_lct_organization_code IN VARCHAR2 DEFAULT NULL
1154 , p_mon_vr_subinventory IN VARCHAR2 DEFAULT NULL
1155 , p_mon_vr_locator_id IN NUMBER DEFAULT NULL
1156 , p_mon_vr_locator IN VARCHAR2 DEFAULT NULL
1157 , p_mon_vr_rsr_organization_code IN VARCHAR2 DEFAULT NULL
1158 , p_mon_vr_resources IN VARCHAR2 DEFAULT NULL
1159 , p_mon_vr_resource_instance_id IN NUMBER DEFAULT NULL
1160 , p_mon_vr_sampling_plan IN VARCHAR2 DEFAULT NULL
1161 , p_mon_vr_sampling_plan_id IN NUMBER DEFAULT NULL
1162 , p_mon_vr_start_date IN DATE DEFAULT NULL
1163 , p_mon_vr_end_date IN DATE DEFAULT NULL
1164 , p_mon_vr_from_last_update_date IN DATE DEFAULT NULL
1165 , p_mon_vr_to_last_update_date IN DATE DEFAULT NULL
1166 , p_mon_vr_delete_mark IN NUMBER DEFAULT NULL
1167 -- END B3124291 Incorporated Mini-Pack K Features to Outboud APIs
1168
1169 -- Return parameters
1170
1171 , x_specifications_tbl OUT NOCOPY system.gmd_specifications_tab_type
1172 , x_return_status OUT NOCOPY VARCHAR2
1173 , x_msg_count OUT NOCOPY NUMBER
1174 , x_msg_data OUT NOCOPY VARCHAR2
1175 );
1176
1177 end gmd_outbound_apis_pub;