1 PACKAGE GMD_SPEC_MATCH_GRP AUTHID CURRENT_USER AS
2 /* $Header: GMDGSPMS.pls 120.7 2006/03/16 04:28:29 ragsriva ship $ */
3
4 -- Start of comments
5 --+==========================================================================+
6 --| Copyright (c) 1998 Oracle Corporation |
7 --| Redwood Shores, CA, USA |
8 --| All rights reserved. |
9 --+==========================================================================+
10 --| File Name : GMDGSPMS.pls |
11 --| Package Name : GMD_SPEC_MATCH_GRP |
12 --| Type : Group |
13 --| |
14 --| Notes |
15 --| This package contains group layer APIs for Specification Match |
16 --| |
17 --| HISTORY |
18 --| Mahesh Chandak 6-Aug-2002 Created. |
19 --| Saikiran Vankadari 20-May-2005 Convergence Changes |
20 --| Joe DiIorio 19-Jul-2005 Put back lot_id, whse_code, orgn_code,|
21 --| item_id, location into record TYPEs |
22 --| as these are needed by GMDQCMJB.pls. |
23 --+==========================================================================+
24 -- End of comments
25
26
27 -- adding test_id. This is to support production requirement.
28 -- test_id would be passed only by the production team.
29 -- note that item_id, lot_id, orgn_code, whse_code, location are obsolete but
30 -- required for migration.
31 TYPE INVENTORY_SPEC_REC_TYPE IS RECORD (
32 inventory_item_id NUMBER
33 ,revision VARCHAR2(3)
34 ,grade_code VARCHAR2(150)
35 ,organization_id NUMBER
36 ,subinventory VARCHAR2(10)
37 ,parent_lot_number VARCHAR2(80)
38 ,lot_number VARCHAR2(80)
39 ,locator_id NUMBER
40 ,date_effective DATE
41 ,exact_match VARCHAR2(1)
42 ,test_id NUMBER
43 , item_id NUMBER
44 , lot_id NUMBER
45 , orgn_code VARCHAR2(4)
46 , whse_code VARCHAR2(4)
47 , location VARCHAR2(16)
48 );
49
50
51 -- note that item_id, lot_id, orgn_code, whse_code are obsolete but
52 -- required for migration.
53 TYPE CUSTOMER_SPEC_REC_TYPE IS RECORD
54 (inventory_item_id NUMBER
55 ,revision VARCHAR2(3)
56 ,organization_id NUMBER
57 ,subinventory VARCHAR2(10)
58 ,grade_code VARCHAR2(150) -- Bug# 4723077
59 ,cust_id NUMBER
60 ,date_effective DATE
61 ,org_id NUMBER
62 ,ship_to_site_id NUMBER
63 ,order_id NUMBER
64 ,order_line NUMBER
65 ,order_line_id NUMBER
66 ,look_in_other_orgn VARCHAR2(1)
67 ,exact_match VARCHAR2(1)
68 ,lot_number VARCHAR2(80)
69 ,parent_lot_number VARCHAR2(80)
70 , item_id NUMBER
71 , lot_id NUMBER
72 , orgn_code VARCHAR2(4)
73 , whse_code VARCHAR2(4)
74 ) ;
75
76 -- note that item_id, lot_id, orgn_code, whse_code are obsolete but
77 -- required for migration.
78 TYPE SUPPLIER_SPEC_REC_TYPE IS RECORD
79 (inventory_item_id NUMBER
80 ,revision VARCHAR2(3)
81 ,organization_id NUMBER
82 ,subinventory VARCHAR2(10)
83 ,grade_code VARCHAR2(150) -- Bug# 4723077
84 ,locator_id NUMBER
85 ,supplier_id NUMBER
86 ,supplier_site_id NUMBER
87 ,po_header_id NUMBER
88 ,po_line_id NUMBER
89 ,date_effective DATE
90 ,org_id NUMBER
91 ,exact_match VARCHAR2(1)
92 ,lot_number VARCHAR2(80)
93 ,parent_lot_number VARCHAR2(80)
94 , item_id NUMBER
95 , lot_id NUMBER
96 , orgn_code VARCHAR2(4)
97 , whse_code VARCHAR2(4)
98 );
99
100 -- note that item_id, lot_id, orgn_code are obsolete but
101 -- required for migration.
102 -- Bug 4640143: added material detail id
103 TYPE WIP_SPEC_REC_TYPE IS RECORD
104 (inventory_item_id NUMBER
105 ,revision VARCHAR2(3)
106 ,grade_code VARCHAR2(150) -- Bug# 4723077
107 ,organization_id NUMBER
108 ,batch_id NUMBER
109 ,recipe_id NUMBER
110 ,recipe_no VARCHAR2(32)
111 ,recipe_version NUMBER(5)
112 ,formula_id NUMBER
113 ,formulaline_id NUMBER
114 ,material_detail_id NUMBER
115 ,formula_no VARCHAR2(32)
116 ,formula_vers NUMBER(5)
117 ,routing_id NUMBER
118 ,routing_no VARCHAR2(32)
119 ,routing_vers NUMBER(5)
120 ,step_id NUMBER
121 ,step_no NUMBER
122 ,oprn_id NUMBER
123 ,oprn_no VARCHAR2(16)
124 ,oprn_vers NUMBER(5)
125 ,charge NUMBER
126 ,date_effective DATE
127 ,exact_match VARCHAR2(1)
128 ,lot_number VARCHAR2(80)
129 ,parent_lot_number VARCHAR2(80)
130 ,find_spec_with_step VARCHAR2(1)
131 ,item_id NUMBER
132 ,lot_id NUMBER
133 ,orgn_code VARCHAR2(4)
134 );
135
136 --MCHANDAK bug# 2645698
137 -- created additional paramater find_spec_with_step which will be set to 'Y' when
138 -- calling from batch step creation workflow.Also changed the main select.
139
140 TYPE MATCH_RESULT_LOT_REC_TYPE IS RECORD
141 ( inventory_item_id NUMBER -- IN
142 ,organization_id NUMBER -- IN
143 ,lot_number VARCHAR2(80) -- IN
144 ,subinventory VARCHAR2(10) -- IN
145 ,locator_id NUMBER -- IN
146 ,sample_id NUMBER -- OUT
147 ,spec_match_type VARCHAR2(1) -- OUT
148 ,result_type VARCHAR2(1) -- OUT
149 ,event_spec_disp_id NUMBER -- OUT
150 );
151
152 TYPE result_lot_match_tbl IS TABLE OF MATCH_RESULT_LOT_REC_TYPE INDEX BY BINARY_INTEGER;
153
154
155 TYPE LOCATION_SPEC_REC_TYPE IS RECORD (
156 locator_organization_id NUMBER
157 ,subinventory VARCHAR2(10)
158 ,locator_id NUMBER
159 ,date_effective DATE
160 );
161
162
163 TYPE RESOURCE_SPEC_REC_TYPE IS RECORD (
164 resource_organization_id NUMBER
165 ,resources VARCHAR2(16)
166 ,resource_instance_id NUMBER
167 ,date_effective DATE
168 );
169
170
171
172 --Start of comments
173 --+========================================================================+
174 --| API Name : find_inventory_spec |
175 --| TYPE : Group |
176 --| Notes : This function RETURN TRUE if matching inventory |
177 --| spec. is found else it RETURN FALSE. |
178 --| If matching inventory spec is found,then |
179 --| it returns matching spec_id and spec_vr_id |
180 --| Calling Program : - Samples form |
181 --| - Subscriber for the Receiving Event(if |
182 --| matching supplier spec is not found) (Workflow) |
183 --| - Inventory Transaction Event |
184 --| - Lot Expiration Transcation Event |
185 --| - Lot Retest Transcation Event |
186 --| HISTORY |
187 --| Mahesh Chandak 6-Aug-2002 Created. |
188 --| |
189 --+========================================================================+
190 -- End of comments
191
192 FUNCTION FIND_INVENTORY_SPEC(p_inventory_spec_rec IN inventory_spec_rec_type,
193 x_spec_id OUT NOCOPY NUMBER,
194 x_spec_vr_id OUT NOCOPY NUMBER,
195 x_return_status OUT NOCOPY VARCHAR2,
196 x_message_data OUT NOCOPY VARCHAR2 )
197 RETURN BOOLEAN ;
198
199
200 --Start of comments
201 --+========================================================================+
202 --| API Name : find_customer_spec |
203 --| TYPE : Group |
204 --| Notes : This function RETURN TRUE if matching customer |
205 --| spec. is found else it RETURN FALSE. |
206 --| If matching customer spec is found,then |
207 --| it returns matching spec_id and spec_vr_id |
208 --| Calling Program : - Spec matching in Order Management(Pick lots form) |
209 --| - Shipment screen in OM (in Future ) |
210 --| HISTORY |
211 --| Mahesh Chandak 6-Aug-2002 Created. |
212 --| |
213 --+========================================================================+
214 -- End of comments
215
216 FUNCTION FIND_CUSTOMER_SPEC(p_customer_spec_rec IN customer_spec_rec_type,
217 x_spec_id OUT NOCOPY NUMBER,
218 x_spec_vr_id OUT NOCOPY NUMBER,
219 x_return_status OUT NOCOPY VARCHAR2,
220 x_message_data OUT NOCOPY VARCHAR2 )
221 RETURN BOOLEAN ;
222
223 --Start of comments
224 --+========================================================================+
225 --| API Name : find_cust_or_inv_spec |
226 --| TYPE : Group |
227 --| Notes : This function first looks for a matching customer |
228 --| spec.If found,then returns that spec else looks for a |
229 --| matching inventory spec and returns that spec. |
230 --| Calling Program : - Samples form |
231 --| - Quality Migration Script |
232 --| HISTORY |
233 --| Mahesh Chandak 1-Oct-2002 Created. |
234 --| |
235 --+========================================================================+
236 -- End of comments
237
238 FUNCTION FIND_CUST_OR_INV_SPEC(p_customer_spec_rec IN customer_spec_rec_type,
239 x_spec_id OUT NOCOPY NUMBER,
240 x_spec_vr_id OUT NOCOPY NUMBER,
241 x_spec_type OUT NOCOPY VARCHAR2,
242 x_return_status OUT NOCOPY VARCHAR2,
243 x_message_data OUT NOCOPY VARCHAR2 )
244 RETURN BOOLEAN ;
245
246 --Start of comments
247 --+========================================================================+
248 --| API Name : find_wip_spec |
249 --| TYPE : Group |
250 --| Notes : This function RETURN TRUE if matching WIP |
251 --| spec. is found else it RETURN FALSE. |
252 --| If matching WIP spec is found,then |
253 --| it returns matching spec_id and spec_vr_id |
254 --| HISTORY |
255 --| Mahesh Chandak 6-Aug-2002 Created. |
256 --| |
257 --| Calling Program : - Samples form |
258 --| - Batch Creation |
259 --+========================================================================+
260 -- End of comments
261
262 FUNCTION FIND_WIP_SPEC(p_wip_spec_rec IN wip_spec_rec_type,
263 x_spec_id OUT NOCOPY NUMBER,
264 x_spec_vr_id OUT NOCOPY NUMBER,
265 x_return_status OUT NOCOPY VARCHAR2,
266 x_message_data OUT NOCOPY VARCHAR2 )
267 RETURN BOOLEAN ;
268
269 --Start of comments
270 --+========================================================================+
271 --| API Name : find_wip_or_inv_spec |
272 --| TYPE : Group |
273 --| Notes : This function first looks for a matching WIP |
274 --| spec.If found,then returns that spec else looks for a |
275 --| matching inventory spec and returns that spec. |
276 --| Calling Program : - Samples form |
277 --| - Quality Migration Script |
278 --| HISTORY |
279 --| Mahesh Chandak 1-Oct-2002 Created. |
280 --| |
281 --+========================================================================+
282 -- End of comments
283
284 FUNCTION FIND_WIP_OR_INV_SPEC(p_wip_spec_rec IN wip_spec_rec_type,
285 x_spec_id OUT NOCOPY NUMBER,
286 x_spec_vr_id OUT NOCOPY NUMBER,
287 x_spec_type OUT NOCOPY VARCHAR2,
288 x_return_status OUT NOCOPY VARCHAR2,
289 x_message_data OUT NOCOPY VARCHAR2 )
290 RETURN BOOLEAN ;
294 --+========================================================================+
291
292
293 --Start of comments
295 --| API Name : find_supplier_spec |
296 --| TYPE : Group |
297 --| Notes : This function RETURN TRUE if matching supplier |
298 --| spec. is found else it RETURN FALSE. |
299 --| If matching supplier spec is found,then |
300 --| it returns matching spec_id and spec_vr_id |
301 --| HISTORY |
302 --| Mahesh Chandak 6-Aug-2002 Created. |
303 --| Calling Program : Samples form |
304 --| Receiving Transaction Event(Workflow) |
305 --+========================================================================+
306 -- End of comments
307
308 FUNCTION FIND_SUPPLIER_SPEC(p_supplier_spec_rec IN supplier_spec_rec_type,
309 x_spec_id OUT NOCOPY NUMBER,
310 x_spec_vr_id OUT NOCOPY NUMBER,
311 x_return_status OUT NOCOPY VARCHAR2,
312 x_message_data OUT NOCOPY VARCHAR2 )
313
314 RETURN BOOLEAN ;
315
316 --Start of comments
317 --+========================================================================+
318 --| API Name : find_supplier_or_inv_spec |
319 --| TYPE : Group |
320 --| Notes : This function first looks for a matching supplier |
321 --| spec.If found,then returns that spec else looks for a |
322 --| matching inventory spec and returns that spec. |
323 --| Calling Program : - Samples form |
324 --| - Quality Migration Script |
325 --| HISTORY |
326 --| Mahesh Chandak 1-Oct-2002 Created. |
327 --| Susan Feinstein 19-Sep-2003 added whse_code and location for |
328 --| Bug 3143796 |
329 --| |
330 --+========================================================================+
331 -- End of comments
332
333 FUNCTION FIND_SUPPLIER_OR_INV_SPEC(
334 p_supplier_spec_rec IN supplier_spec_rec_type,
335 x_spec_id OUT NOCOPY NUMBER,
336 x_spec_vr_id OUT NOCOPY NUMBER,
337 x_spec_type OUT NOCOPY VARCHAR2,
338 x_return_status OUT NOCOPY VARCHAR2,
339 x_message_data OUT NOCOPY VARCHAR2)
340
341 RETURN BOOLEAN ;
342
343 PROCEDURE get_result_match_for_spec
344 ( p_spec_id IN NUMBER
345 , p_lots IN OUT NOCOPY result_lot_match_tbl
346 , x_return_status OUT NOCOPY VARCHAR2
347 , x_message_data OUT NOCOPY VARCHAR2 ) ;
348
349
350 FUNCTION find_location_spec(p_location_spec_rec IN LOCATION_SPEC_REC_TYPE,
351 x_spec_id OUT NOCOPY NUMBER,
352 x_spec_vr_id OUT NOCOPY NUMBER,
353 x_return_status OUT NOCOPY VARCHAR2,
354 x_message_data OUT NOCOPY VARCHAR2 )
355 RETURN BOOLEAN ;
356
357
358 FUNCTION find_resource_spec(p_resource_spec_rec IN RESOURCE_SPEC_REC_TYPE,
359 x_spec_id OUT NOCOPY NUMBER,
360 x_spec_vr_id OUT NOCOPY NUMBER,
361 x_return_status OUT NOCOPY VARCHAR2,
362 x_message_data OUT NOCOPY VARCHAR2 )
363 RETURN BOOLEAN ;
364
365
366 --+========================================================================+
367 --| API Name : get_inv_spec_or_vr_id |
368 --| |
369 --| Notes Returns spec_id or spec_vr_id depending on parameter |
370 --| p_spec_or_vr_ind passed. Returns 0 if not able to get |
371 --| the matching spec or p_spec_or_vr_ind is invalid or if |
372 --| GMD_SPEC_MATCH_GRP.FIND_INVENTORY_SPEC raises any error. |
373 --| Pass 'SPECID' to p_spec_or_vr_ind to get spec_id. |
374 --| Pass 'SPECVRID' to get spec_vr_id. |
375 --| |
376 --| HISTORY |
377 --| Saikiran Vankadari 25-Nov-2005 Bug 4538523 Created. |
378 --| Calling Program : Item/Location Required Analysis Report |
379 --+========================================================================+
380 -- End of comments
381
382 FUNCTION GET_INV_SPEC_OR_VR_ID( p_inventory_item_id IN NUMBER
383 ,p_revision IN VARCHAR2
384 ,p_grade_code IN VARCHAR2
385 ,p_organization_id IN VARCHAR2
386 ,p_subinventory IN VARCHAR2
387 ,p_parent_lot_number IN VARCHAR2
388 ,p_lot_number IN VARCHAR2
389 ,p_locator_id IN NUMBER
390 ,p_date_effective IN DATE
391 ,p_exact_match IN VARCHAR2
392 ,p_test_id IN NUMBER
393 ,p_spec_or_vr_ind IN VARCHAR2 )
394 RETURN NUMBER;
395
396
397
398 END gmd_spec_match_grp;
[Home] [Help]