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