1 PACKAGE GMD_SPEC_MATCH_MIG_GRP AUTHID CURRENT_USER AS
2 /* $Header: GMDGSMMS.pls 120.0 2005/05/25 19:12:36 appldev noship $ */
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 : GMDGSMMS.pls |
11 --| Package Name : GMD_SPEC_MATCH_MIG_GRP |
12 --| Type : Group |
13 --| |
14 --| Notes |
15 --| This package contains group layer APIs for Specification Match for |
16 --| migration. The spec_status conditions are removed from the WHERE |
17 --| clause, this is the only difference from the spec matching used by |
18 --| QM. |
19 --| |
20 --| HISTORY |
21 --| B. Stone 13-Oct_2004 Created. Bug 3934121. |
22 --| Removed find_location_spec and |
23 --| find_resource_spec, these are not used by |
24 --| migration. |
25 --| |
26 --+==========================================================================+
27 -- End of comments
28
29
30 -- adding test_id. This is to support production requirement.
31 -- test_id would be passed only by the production team.
32 TYPE INVENTORY_SPEC_REC_TYPE IS RECORD (
33 item_id NUMBER
34 ,grade VARCHAR2(4)
35 ,orgn_code VARCHAR2(4)
36 ,lot_id NUMBER
37 ,lot_no VARCHAR2(32)
38 ,sublot_no VARCHAR2(32)
39 ,whse_code VARCHAR2(4)
40 ,location VARCHAR2(16)
41 ,date_effective DATE
42 ,exact_match VARCHAR2(1)
43 ,test_id NUMBER
44 );
45
46
47 TYPE CUSTOMER_SPEC_REC_TYPE IS RECORD
48 (item_id NUMBER
49 ,grade VARCHAR2(4)
50 ,orgn_code VARCHAR2(4)
51 ,whse_code VARCHAR2(4)
52 ,cust_id NUMBER
53 ,date_effective DATE
54 ,org_id NUMBER
55 ,ship_to_site_id NUMBER
56 ,order_id NUMBER
57 ,order_line NUMBER
58 ,order_line_id NUMBER
59 ,look_in_other_orgn VARCHAR2(1)
60 ,exact_match VARCHAR2(1)
61 ,lot_id NUMBER
62 ,lot_no VARCHAR2(32)
63 ,sublot_no VARCHAR2(32)
64 ) ;
65
66 TYPE SUPPLIER_SPEC_REC_TYPE IS RECORD
67 (item_id NUMBER
68 ,grade VARCHAR2(4)
69 ,orgn_code VARCHAR2(4)
70 ,whse_code VARCHAR2(4)
71 ,location VARCHAR2(16)
72 ,supplier_id NUMBER
73 ,supplier_site_id NUMBER
74 ,po_header_id NUMBER
75 ,po_line_id NUMBER
76 ,date_effective DATE
77 ,exact_match VARCHAR2(1)
78 ,lot_id NUMBER
79 ,lot_no VARCHAR2(32)
80 ,sublot_no VARCHAR2(32)
81 );
82
83 TYPE WIP_SPEC_REC_TYPE IS RECORD
84 (item_id NUMBER
85 ,grade VARCHAR2(4)
86 ,orgn_code VARCHAR2(4)
87 ,batch_id NUMBER
88 ,recipe_id NUMBER
89 ,recipe_no VARCHAR2(32)
90 ,recipe_version NUMBER(5)
91 ,formula_id NUMBER
92 ,formulaline_id NUMBER
93 ,formula_no VARCHAR2(32)
94 ,formula_vers NUMBER(5)
95 ,routing_id NUMBER
96 ,routing_no VARCHAR2(32)
97 ,routing_vers NUMBER(5)
98 ,step_id NUMBER
99 ,step_no NUMBER
100 ,oprn_id NUMBER
101 ,oprn_no VARCHAR2(16)
102 ,oprn_vers NUMBER(5)
103 ,charge NUMBER
104 ,date_effective DATE
105 ,exact_match VARCHAR2(1)
106 ,lot_id NUMBER
107 ,lot_no VARCHAR2(32)
108 ,sublot_no VARCHAR2(32)
109 ,find_spec_with_step VARCHAR2(1)
110 );
111
112 --MCHANDAK bug# 2645698
113 -- created additional paramater find_spec_with_step which will be set to 'Y' when
114 -- calling from batch step creation workflow.Also changed the main select.
115
116 TYPE MATCH_RESULT_LOT_REC_TYPE IS RECORD
117 ( item_id NUMBER -- IN
118 ,lot_id NUMBER -- IN
119 ,whse_code VARCHAR2(4) -- IN
120 ,location VARCHAR2(16) -- IN
121 ,sample_id NUMBER -- OUT
122 ,spec_match_type VARCHAR2(1) -- OUT
123 ,result_type VARCHAR2(1) -- OUT
124 ,event_spec_disp_id NUMBER -- OUT
125 );
126
127 TYPE result_lot_match_tbl IS TABLE OF MATCH_RESULT_LOT_REC_TYPE INDEX BY BINARY_INTEGER;
128
129
130 TYPE LOCATION_SPEC_REC_TYPE IS RECORD (
131 loct_orgn_code VARCHAR2(4)
132 ,whse_code VARCHAR2(4)
133 ,location VARCHAR2(16)
134 ,date_effective DATE
135 );
136
137
138 TYPE RESOURCE_SPEC_REC_TYPE IS RECORD (
139 resource_orgn_code VARCHAR2(4)
140 ,resources VARCHAR2(16)
141 ,resource_instance_id NUMBER
142 ,date_effective DATE
143 );
144
145
146
147 --Start of comments
148 --+========================================================================+
149 --| API Name : find_inventory_spec |
150 --| TYPE : Group |
151 --| Notes : This function RETURN TRUE if matching inventory |
152 --| spec. is found else it RETURN FALSE. |
153 --| If matching inventory spec is found,then |
154 --| it returns matching spec_id and spec_vr_id |
155 --| Calling Program : - Samples form |
156 --| - Subscriber for the Receiving Event(if |
157 --| matching supplier spec is not found) (Workflow) |
158 --| - Inventory Transaction Event |
159 --| - Lot Expiration Transcation Event |
160 --| - Lot Retest Transcation Event |
161 --| HISTORY |
162 --| Mahesh Chandak 6-Aug-2002 Created. |
163 --| |
164 --+========================================================================+
165 -- End of comments
166
167 FUNCTION FIND_INVENTORY_SPEC(p_inventory_spec_rec IN inventory_spec_rec_type,
168 x_spec_id OUT NOCOPY NUMBER,
169 x_spec_vr_id OUT NOCOPY NUMBER,
170 x_return_status OUT NOCOPY VARCHAR2,
171 x_message_data OUT NOCOPY VARCHAR2 )
172 RETURN BOOLEAN ;
173
174
175 --Start of comments
176 --+========================================================================+
177 --| API Name : find_customer_spec |
178 --| TYPE : Group |
179 --| Notes : This function RETURN TRUE if matching customer |
180 --| spec. is found else it RETURN FALSE. |
181 --| If matching customer spec is found,then |
182 --| it returns matching spec_id and spec_vr_id |
183 --| Calling Program : - Spec matching in Order Management(Pick lots form) |
184 --| - Shipment screen in OM (in Future ) |
185 --| HISTORY |
186 --| Mahesh Chandak 6-Aug-2002 Created. |
187 --| |
188 --+========================================================================+
189 -- End of comments
190
191 FUNCTION FIND_CUSTOMER_SPEC(p_customer_spec_rec IN customer_spec_rec_type,
192 x_spec_id OUT NOCOPY NUMBER,
193 x_spec_vr_id OUT NOCOPY NUMBER,
194 x_return_status OUT NOCOPY VARCHAR2,
195 x_message_data OUT NOCOPY VARCHAR2 )
196 RETURN BOOLEAN ;
197
198 --Start of comments
199 --+========================================================================+
200 --| API Name : find_cust_or_inv_spec |
201 --| TYPE : Group |
202 --| Notes : This function first looks for a matching customer |
203 --| spec.If found,then returns that spec else looks for a |
204 --| matching inventory spec and returns that spec. |
205 --| Calling Program : - Samples form |
206 --| - Quality Migration Script |
207 --| HISTORY |
208 --| Mahesh Chandak 1-Oct-2002 Created. |
209 --| |
210 --+========================================================================+
211 -- End of comments
212
213 FUNCTION FIND_CUST_OR_INV_SPEC(p_customer_spec_rec IN customer_spec_rec_type,
214 x_spec_id OUT NOCOPY NUMBER,
215 x_spec_vr_id OUT NOCOPY NUMBER,
216 x_spec_type OUT NOCOPY VARCHAR2,
217 x_return_status OUT NOCOPY VARCHAR2,
218 x_message_data OUT NOCOPY VARCHAR2 )
219 RETURN BOOLEAN ;
220
221 --Start of comments
222 --+========================================================================+
223 --| API Name : find_wip_spec |
224 --| TYPE : Group |
225 --| Notes : This function RETURN TRUE if matching WIP |
226 --| spec. is found else it RETURN FALSE. |
227 --| If matching WIP spec is found,then |
228 --| it returns matching spec_id and spec_vr_id |
229 --| HISTORY |
230 --| Mahesh Chandak 6-Aug-2002 Created. |
231 --| |
232 --| Calling Program : - Samples form |
233 --| - Batch Creation |
234 --+========================================================================+
235 -- End of comments
236
237 FUNCTION FIND_WIP_SPEC(p_wip_spec_rec IN wip_spec_rec_type,
238 x_spec_id OUT NOCOPY NUMBER,
239 x_spec_vr_id OUT NOCOPY NUMBER,
240 x_return_status OUT NOCOPY VARCHAR2,
241 x_message_data OUT NOCOPY VARCHAR2 )
242 RETURN BOOLEAN ;
243
244 --Start of comments
245 --+========================================================================+
246 --| API Name : find_wip_or_inv_spec |
247 --| TYPE : Group |
248 --| Notes : This function first looks for a matching WIP |
249 --| spec.If found,then returns that spec else looks for a |
250 --| matching inventory spec and returns that spec. |
251 --| Calling Program : - Samples form |
252 --| - Quality Migration Script |
253 --| HISTORY |
254 --| Mahesh Chandak 1-Oct-2002 Created. |
255 --| |
256 --+========================================================================+
257 -- End of comments
258
259 FUNCTION FIND_WIP_OR_INV_SPEC(p_wip_spec_rec IN wip_spec_rec_type,
260 x_spec_id OUT NOCOPY NUMBER,
261 x_spec_vr_id OUT NOCOPY NUMBER,
262 x_spec_type OUT NOCOPY VARCHAR2,
263 x_return_status OUT NOCOPY VARCHAR2,
264 x_message_data OUT NOCOPY VARCHAR2 )
265 RETURN BOOLEAN ;
266
267
268 --Start of comments
269 --+========================================================================+
270 --| API Name : find_supplier_spec |
271 --| TYPE : Group |
272 --| Notes : This function RETURN TRUE if matching supplier |
273 --| spec. is found else it RETURN FALSE. |
274 --| If matching supplier spec is found,then |
275 --| it returns matching spec_id and spec_vr_id |
276 --| HISTORY |
277 --| Mahesh Chandak 6-Aug-2002 Created. |
278 --| Calling Program : Samples form |
279 --| Receiving Transaction Event(Workflow) |
280 --+========================================================================+
281 -- End of comments
282
283 FUNCTION FIND_SUPPLIER_SPEC(p_supplier_spec_rec IN supplier_spec_rec_type,
284 x_spec_id OUT NOCOPY NUMBER,
285 x_spec_vr_id OUT NOCOPY NUMBER,
286 x_return_status OUT NOCOPY VARCHAR2,
287 x_message_data OUT NOCOPY VARCHAR2 )
288
289 RETURN BOOLEAN ;
290
291 --Start of comments
292 --+========================================================================+
293 --| API Name : find_supplier_or_inv_spec |
294 --| TYPE : Group |
295 --| Notes : This function first looks for a matching supplier |
296 --| spec.If found,then returns that spec else looks for a |
297 --| matching inventory spec and returns that spec. |
298 --| Calling Program : - Samples form |
299 --| - Quality Migration Script |
300 --| HISTORY |
301 --| Mahesh Chandak 1-Oct-2002 Created. |
302 --| Susan Feinstein 19-Sep-2003 added whse_code and location for |
303 --| Bug 3143796 |
304 --| |
305 --+========================================================================+
306 -- End of comments
307
308 FUNCTION FIND_SUPPLIER_OR_INV_SPEC(
309 p_supplier_spec_rec IN supplier_spec_rec_type,
310 x_spec_id OUT NOCOPY NUMBER,
311 x_spec_vr_id OUT NOCOPY NUMBER,
312 x_spec_type OUT NOCOPY VARCHAR2,
313 x_return_status OUT NOCOPY VARCHAR2,
314 x_message_data OUT NOCOPY VARCHAR2)
315
316 RETURN BOOLEAN ;
317
318 PROCEDURE get_result_match_for_spec
319 ( p_spec_id IN NUMBER
320 , p_lots IN OUT NOCOPY result_lot_match_tbl
321 , x_return_status OUT NOCOPY VARCHAR2
325
322 , x_message_data OUT NOCOPY VARCHAR2 ) ;
323
324
326 END gmd_spec_match_mig_grp;
[Home] [Help]