1 PACKAGE wsh_opsm_asn_item_genealogy AUTHID CURRENT_USER AS
2 /* $Header: wshopsmasnigs.pls 120.4 2011/02/02 14:52:50 skaradib noship $ */
3 ---------------------------------------------------------------------------------------
4 --
5 -- Record Type: rec_genealogy
6 -- Description: This is used by the get_genealogy Function
7 --
8 ---------------------------------------------------------------------------------------
9 --bug 10357152 - opsmperf - added DELIVERY_DETAIL_ID, DELIVERY_ID, GENEALOGY_OBJECT_ID
10 TYPE rec_genealogy
11 IS
12 RECORD (item_nbr VARCHAR2(32000) ,
13 primary_uom_code VARCHAR2(100) ,
14 lot_number VARCHAR2(255) ,
15 serial_number VARCHAR2(255) ,
16 organization_id NUMBER ,
17 job_name VARCHAR2(255) ,
18 inventory_item_id NUMBER ,
19 origination_date DATE ,
20 best_by_date DATE ,
21 retest_date DATE ,
22 expiration_date DATE ,
23 organization_code VARCHAR2(4) ,
24 rlevel NUMBER ,
25 parent_rlevel NUMBER ,
26 lot_control_code NUMBER ,
27 serial_number_control_code NUMBER ,
28 cross_reference VARCHAR2(3) ,
29 serial_type VARCHAR2(150) ,
30 parent_lot_number VARCHAR2(255) ,
31 parent_serial_number VARCHAR2(255) ,
32 parent_inventory_item_id NUMBER ,
33 DELIVERY_DETAIL_ID NUMBER,
34 DELIVERY_ID NUMBER,
35 GENEALOGY_OBJECT_ID NUMBER);
36 ---------------------------------------------------------------------------------------
37 --
38 -- Record Type: rec_genealogy
39 -- Description: This is used by the get_genealogy Function
40 --
41 ---------------------------------------------------------------------------------------
42 TYPE rec_sgenealogy
43 IS
44 RECORD (lot_number VARCHAR2(255) ,
45 serial_number VARCHAR2(255) ,
46 item_nbr VARCHAR2(32000) ,
47 primary_uom_code VARCHAR2(100) ,
48 inventory_item_id NUMBER ,
49 job_name VARCHAR2(255) ,
50 origination_date DATE ,
51 best_by_date DATE ,
52 retest_date DATE ,
53 expiration_date DATE ,
54 organization_code VARCHAR2(4) ,
55 transaction_quantity NUMBER ,
56 lot_control_code NUMBER ,
57 serial_number_control_code NUMBER ,
58 cross_reference VARCHAR2(3) ,
59 serial_type VARCHAR2(150) ,
60 parent_lot_number VARCHAR2(255) ,
61 parent_serial_number VARCHAR2(255) ,
62 parent_inventory_item_id NUMBER );
63 ---------------------------------------------------------------------------------------
64 --
65 -- Record Type: rec_genealogy
66 -- Description: This is used by the get_genealogy Function
67 --
68 ---------------------------------------------------------------------------------------
69 TYPE rec_nodes
70 IS
71 RECORD( serial_number VARCHAR2(255),
72 inventory_item_id NUMBER ,
73 lot_number VARCHAR2(255),
74 level NUMBER );
75 ---------------------------------------------------------------------------------------
76 --
77 -- Table Type: tab_nodes
78 -- Description: This is used by the get_genealogy Function
79 --
80 ---------------------------------------------------------------------------------------
81 TYPE tab_nodes
82 IS TABLE OF rec_nodes INDEX BY BINARY_INTEGER;
83 ---------------------------------------------------------------------------------------
84 --
85 -- Table Type: tab_genealogy
86 -- Description: This is used by the get_genealogy Function
87 --
88 ---------------------------------------------------------------------------------------
89 TYPE tab_genealogy
90 IS TABLE OF rec_genealogy;
91 --opsmperf begin
92 ---------------------------------------------------------------------------------------
93 --
94 -- Table Type: tab_genealogy
95 -- Description: This is used by the get_genealogy Function
96 --
97 ---------------------------------------------------------------------------------------
98 TYPE tab_genealogy1
99 IS TABLE OF rec_genealogy INDEX BY VARCHAR2(32000);
100 ---------------------------------------------------------------------------------------
101 --
102 -- Table Type: t_var_idx_num
103 -- Description: This is used by the get_asn_data Function
104 --
105 ---------------------------------------------------------------------------------------
106 TYPE t_var_idx_num
107 IS TABLE OF VARCHAR2(32000) INDEX BY BINARY_INTEGER;
108 --opsmperf end
109 ---------------------------------------------------------------------------------------
110 --
111 -- Table Type: tab_sgenealogy
112 -- Description: This is used by the get_genealogy Function
113 --
114 ---------------------------------------------------------------------------------------
115 TYPE tab_sgenealogy
116 IS TABLE OF rec_sgenealogy INDEX BY BINARY_INTEGER;
117 ---------------------------------------------------------------------------------------
118 --
119 -- Table Type: recur
120 -- Description: This is used by the get_genealogy Function
121 --
122 ---------------------------------------------------------------------------------------
123 TYPE recur
124 IS TABLE OF NUMBER INDEX BY VARCHAR2(32000);
125 ---------------------------------------------------------------------------------------
126 --
127 -- Table Type: t_ser
128 -- Description: This is used by the get_genealogy Function
129 --
130 ---------------------------------------------------------------------------------------
131 TYPE t_ser
132 IS TABLE OF VARCHAR2(32000) INDEX BY VARCHAR2(32000);
133 ---------------------------------------------------------------------------------------
134 --
135 -- Function: get_digits
136 -- Parameters: p_char - Character i.e FROM Serial and TO Serial Numbers
137
138 -- Description: It converts From serial numbers and To Serial Numbers to Alphabets
139 --
140 ---------------------------------------------------------------------------------------
141 FUNCTION get_digits(p_char IN VARCHAR2)
142 RETURN NUMBER;
143 ---------------------------------------------------------------------------------------
144 --
145 -- Function: get_string
146 -- Parameters: p_char - Character i.e FROM Serial and TO Serial Numbers
147
148 -- Description: It converts From serial numbers and To Serial Numbers to Alphabets
149 --
150 ---------------------------------------------------------------------------------------
151 FUNCTION get_string(p_char IN VARCHAR2)
152 RETURN VARCHAR2;
153 ---------------------------------------------------------------------------------------
154 --
155 -- Function: get_next_serial
156 -- Parameters: p_char - Character i.e FROM Serial and TO Serial Numbers
157
158 -- Description: It generates the next Serial Number. Eg: if Serial Number
159 -- passed is SE001 it generates SE0002
160 --
161 ---------------------------------------------------------------------------------------
162 FUNCTION get_next_serial(p_serial_number IN VARCHAR2)
163 RETURN VARCHAR2;
164 ---------------------------------------------------------------------------------------
165 --
166 -- Function: get_genealogy
167 -- Parameters: p_gen_object_id - It takes Genealogy object id has input
168 -- p_delivery_detail_id- It takes delivery_detail Id has input
169 -- p_delivery_id -It takes delivery_id has input
170 -- p_organization_id -It takes organization_id has input
171 -- p_frm_serial- It takes frm_serial has input
172 -- p_to_serial-It takes to_serial has input
173 -- Description: It gives all the Sub Componets, there Lot Numbers, Serial Numbers
174 -- and Hierarchy Levels Present Under the Main Assembly Item
175 -- (For Work Order the components used for making that Item).
176 -- In case if it is not the Assembly Item it takes From Serial
177 -- and TO Serial Numbers and Generates in between numbers.
178 --
179 ---------------------------------------------------------------------------------------
180 FUNCTION get_genealogy( p_gen_object_id IN NUMBER,
181 p_delivery_detail_id IN NUMBER,
182 p_delivery_id IN NUMBER,
183 p_organization_id IN NUMBER,
184 p_frm_serial IN VARCHAR2 DEFAULT NULL,
185 p_to_serial IN VARCHAR2 DEFAULT NULL)
186 RETURN tab_genealogy PIPELINED;
187 ---------------------------------------------------------------------------------------
188 --
189 -- Procedure: get_genealogy
190 -- Parameters: p_gen_object_id - It takes Genealogy object id has input
191 -- p_delivery_detail_id- It takes delivery_detail Id has input
192 -- p_delivery_id -It takes delivery_id has input
193 -- p_organization_id -It takes organization_id has input
194 -- p_frm_serial- This is not currently used
195 -- p_to_serial- This is not currently used
196 -- x_rslt_tab, x_rslt_par_tab - both are identical and have the
197 -- genealogy records generated as output
198 -- Description: It gives all the Sub Componets, their Lot Numbers, Serial Numbers
199 -- and Hierarchy Levels Present Under the Main Assembly Item
200 -- (For Work Order the components used for making that Item).
201 -- This procedure is similar to the PIPELINED FUNCTION get_genealogy
202 -- which is replaced by the current procedure for performance reasons.
203 -- Instead of PIPE ROW returning the output, the output is captured in
204 -- x_rslt_tab, x_rslt_par_tab
205 ---------------------------------------------------------------------------------------
206 PROCEDURE get_genealogy( p_gen_object_id IN NUMBER,
207 p_delivery_detail_id IN NUMBER,
208 p_delivery_id IN NUMBER,
209 p_organization_id IN NUMBER,
210 p_frm_serial IN VARCHAR2 DEFAULT NULL,
211 p_to_serial IN VARCHAR2 DEFAULT NULL,
212 x_rslt_tab OUT NOCOPY tab_genealogy);
213 ---------------------------------------------------------------------------------------
214 --
215 -- Function: check_for_itemgenealogy
216 -- Parameters: p_delivery_detail_id- It takes delivery_detail Id has input
217 -- p_organization_id -It takes organization_id has input
218 -- Description: It checks if Ittem Genealogy exsits for the shiiped item
219 -- If not it returns 'N' otherwise returns 'Y'
220 --
221 ---------------------------------------------------------------------------------------
222 FUNCTION check_for_itemgenealogy(p_delivery_detail_id IN NUMBER ,
223 p_organization_id IN NUMBER)
224 RETURN VARCHAR2;
225 ---------------------------------------------------------------------------------------
226 --
227 -- Global Variable: g_alphabets
228 --
229 ---------------------------------------------------------------------------------------
230 g_alphabets VARCHAR2(500) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz`~!@#$%^&*()_-=+[]{}\|;:",.<>/?';
231
232 CURSOR c_main_sql(r_delivery_detail_id IN NUMBER,
233 r_organization_id IN NUMBER)
234 IS
235 SELECT msi.concatenated_segments item_nbr ,
236 msi.primary_uom_code ,
237 mtln.lot_number ,
238 wdd.serial_number ,
239 mtln.organization_id ,
240 we.wip_entity_name job_name ,
241 msi.inventory_item_id ,
242 mtln.origination_date ,
243 mtln.best_by_date ,
244 mtln.retest_date ,
245 mln.expiration_date ,
246 ood.organization_code ,
247 1 rlevel ,
248 0 v_parent_rlevel ,
249 msi.lot_control_code ,
250 msi.serial_number_control_code ,
251 decode(( SELECT upper(mcr.cross_reference)
252 FROM mtl_cross_references_vl mcr
253 WHERE mcr.inventory_item_id = msi.inventory_item_id+0
254 AND mcr.organization_id = msi.organization_id+0
255 AND to_char(mcr.last_update_date, 'DD/MM/YYYY-HH24.MM.SS') = (SELECT MAX(to_char(mcr1.last_update_date, 'DD/MM/YYYY-HH24.MM.SS'))
256 FROM mtl_cross_references_vl mcr1
257 WHERE mcr1.inventory_item_id = msi.inventory_item_id+0
258 AND mcr1.organization_id = msi.organization_id+0
259 AND mcr1.cross_reference_type ='OPSM INTEGRATED')),'NO',0,1) cross_reference,
260 (SELECT mcr.attribute1
261 FROM mtl_cross_references_vl mcr
262 WHERE mcr.inventory_item_id = msi.inventory_item_id+0
263 AND mcr.organization_id = (SELECT master_organization_id
264 FROM mtl_parameters
265 WHERE organization_id=msi.organization_id+0)
266 AND mcr.cross_reference_type ='OPSM INTEGRATED'
267 AND mcr.cross_reference = 'YES') serial_type ,
268 NULL parent_lot_number ,
269 NULL parent_serial_number ,
270 0 parent_inventory_item_id,
271 --bug 10357152 - opsmperf - added DELIVERY_DETAIL_ID, DELIVERY_ID, GENEALOGY_OBJECT_ID
272 to_number(null) DELIVERY_DETAIL_ID,
273 to_number(null) DELIVERY_ID,
274 to_number(null) GENEALOGY_OBJECT_ID
275 FROM mtl_transaction_lot_numbers mtln ,
276 mtl_serial_numbers msn ,
277 mtl_system_items_vl msi ,
278 wip_entities we ,
279 org_organization_definitions ood ,
280 wsh_delivery_details wdd ,
281 mtl_object_genealogy mog1 ,
282 mtl_object_genealogy mog2 ,
283 mtl_lot_numbers mln ,
284 mtl_material_transactions mmt
285 WHERE wdd.delivery_detail_id = r_delivery_detail_id
286 AND wdd.organization_id = r_organization_id
287 AND msn.serial_number = NVL(wdd.serial_number,NULL)
288 AND msn.inventory_item_id = wdd.inventory_item_id+0
289 AND mtln.inventory_item_id = wdd.inventory_item_id+0
290 AND mtln.lot_number = NVL(wdd.lot_number,NULL)
291 AND mtln.organization_id = wdd.organization_id+0
292 AND msi.inventory_item_id = mtln.inventory_item_id
293 AND msi.organization_id = mtln.organization_id
294 AND ood.organization_id = mtln.organization_id +0
295 AND mog1.object_id = msn.gen_object_id +0
296 AND mog2.object_id = mog1.parent_object_id +0
297 AND mtln.transaction_id = mog2.origin_txn_id +0
298 AND we.wip_entity_id = mtln.transaction_source_id+0
299 AND mln.inventory_item_id = mtln.inventory_item_id +0
300 AND mln.organization_id = mtln.organization_id +0
301 AND mln.lot_number = mtln.lot_number ||''
302 AND mtln.transaction_id+0 = mmt.transaction_id
303 AND mmt.transaction_quantity = 1
304
305 UNION ALL
306
307 SELECT msi.concatenated_segments item_nbr ,
308 msi.primary_uom_code ,
309 mtln.lot_number ,
310 mut.serial_number ,
311 mut.organization_id ,
312 we.wip_entity_name job_name ,
313 msi.inventory_item_id ,
314 mtln.origination_date ,
315 mtln.best_by_date ,
316 mtln.retest_date ,
317 mln.expiration_date ,
318 ood.organization_code ,
319 1 rlevel ,
320 0 v_parent_rlevel ,
321 msi.lot_control_code ,
322 msi.serial_number_control_code ,
323 decode(( SELECT upper(mcr.cross_reference)
324 FROM mtl_cross_references_vl mcr
325 WHERE mcr.inventory_item_id = msi.inventory_item_id+0
326 AND mcr.organization_id = msi.organization_id+0
327 AND to_char(mcr.last_update_date, 'DD/MM/YYYY-HH24.MM.SS') = (SELECT MAX(to_char(mcr1.last_update_date, 'DD/MM/YYYY-HH24.MM.SS'))
328 FROM mtl_cross_references_vl mcr1
329 WHERE mcr1.inventory_item_id = msi.inventory_item_id+0
330 AND mcr1.organization_id = msi.organization_id+0
331 AND mcr1.cross_reference_type ='OPSM INTEGRATED')),'NO',0,1) cross_reference,
332 (SELECT mcr.attribute1
333 FROM mtl_cross_references_vl mcr
334 WHERE mcr.inventory_item_id = msi.inventory_item_id+0
335 AND mcr.organization_id = (SELECT master_organization_id
336 FROM mtl_parameters
337 WHERE organization_id=msi.organization_id+0)
338 AND mcr.cross_reference_type ='OPSM INTEGRATED'
339 AND mcr.cross_reference = 'YES') serial_type,
340 NULL parent_lot_number ,
341 NULL parent_serial_number ,
342 0 parent_inventory_item_id,
343 --bug 10357152 - opsmperf - added DELIVERY_DETAIL_ID, DELIVERY_ID, GENEALOGY_OBJECT_ID
344 to_number(null) DELIVERY_DETAIL_ID,
345 to_number(null) DELIVERY_ID,
346 to_number(null) GENEALOGY_OBJECT_ID
347 FROM mtl_transaction_lot_numbers mtln ,
348 mtl_unit_transactions mut ,
349 mtl_serial_numbers_temp msnt ,
350 wip_entities we ,
351 org_organization_definitions ood ,
352 mtl_system_items_vl msi ,
353 wsh_delivery_details wdd ,
354 mtl_lot_numbers mln ,
355 mtl_material_transactions mmt
356 WHERE wdd.delivery_detail_id = r_delivery_detail_id
357 AND mtln.inventory_item_id = mut.inventory_item_id
358 AND mtln.organization_id = r_organization_id
359 AND mtln.organization_id = mut.organization_id
360 AND mut.transaction_id = mtln.serial_transaction_id
361 AND mut.serial_number BETWEEN msnt.fm_serial_number
362 AND msnt.to_serial_number
363 AND wsh_opsm_asn_item_genealogy.get_digits(mut.serial_number) BETWEEN wsh_opsm_asn_item_genealogy.get_digits(msnt.fm_serial_number)
364 AND wsh_opsm_asn_item_genealogy.get_digits(msnt.to_serial_number)
365 AND wsh_opsm_asn_item_genealogy.get_string(mut.serial_number) = wsh_opsm_asn_item_genealogy.get_string(msnt.to_serial_number)
366 AND wsh_opsm_asn_item_genealogy.get_string(msnt.fm_serial_number) = wsh_opsm_asn_item_genealogy.get_string(msnt.to_serial_number)
367 AND msnt.transaction_temp_id = wdd.transaction_temp_id
368 AND we.wip_entity_id = mtln.transaction_source_id
369 AND ood.organization_id = mtln.organization_id
370 AND msi.inventory_item_id = mut.inventory_item_id
371 AND msi.organization_id = mut.organization_id
372 AND mln.inventory_item_id = mtln.inventory_item_id+0
373 AND mln.organization_id = mtln.organization_id +0
374 AND mln.lot_number = mtln.lot_number ||''
375 AND mtln.transaction_id+0 = mmt.transaction_id
376 AND mmt.transaction_quantity = 1
377
378 UNION ALL
379
380 SELECT msi.concatenated_segments item_nbr ,
381 msi.primary_uom_code ,
382 mtln.lot_number ,
383 mut.serial_number ,
384 mut.organization_id ,
385 we.wip_entity_name job_name ,
386 msi.inventory_item_id ,
387 mtln.origination_date ,
388 mtln.best_by_date ,
389 mtln.retest_date ,
390 mln.expiration_date ,
391 ood.organization_code ,
392 1 rlevel ,
393 0 v_parent_rlevel ,
394 msi.lot_control_code ,
395 msi.serial_number_control_code ,
396 decode(( SELECT upper(mcr.cross_reference)
397 FROM mtl_cross_references_vl mcr
398 WHERE mcr.inventory_item_id = msi.inventory_item_id+0
399 AND mcr.organization_id = msi.organization_id+0
400 AND to_char(mcr.last_update_date, 'DD/MM/YYYY-HH24.MM.SS') = (SELECT MAX(to_char(mcr1.last_update_date, 'DD/MM/YYYY-HH24.MM.SS'))
401 FROM mtl_cross_references_vl mcr1
402 WHERE mcr1.inventory_item_id = msi.inventory_item_id+0
403 AND mcr1.organization_id = msi.organization_id+0
404 AND mcr1.cross_reference_type ='OPSM INTEGRATED')),'NO',0,1) cross_reference,
405 (SELECT mcr.attribute1
406 FROM mtl_cross_references_vl mcr
407 WHERE mcr.inventory_item_id = msi.inventory_item_id+0
408 AND mcr.organization_id = (SELECT master_organization_id
409 FROM mtl_parameters
410 WHERE organization_id=msi.organization_id+0)
411 AND mcr.cross_reference_type ='OPSM INTEGRATED'
412 AND mcr.cross_reference = 'YES') serial_type,
413 NULL parent_lot_number ,
414 NULL parent_serial_number ,
415 0 parent_inventory_item_id,
416 --bug 10357152 - opsmperf - added DELIVERY_DETAIL_ID, DELIVERY_ID, GENEALOGY_OBJECT_ID
417 to_number(null) DELIVERY_DETAIL_ID,
418 to_number(null) DELIVERY_ID,
419 to_number(null) GENEALOGY_OBJECT_ID
420 FROM wsh_serial_numbers wsn ,
421 mtl_unit_transactions mut ,
422 mtl_transaction_lot_numbers mtln ,
423 org_organization_definitions ood ,
424 wip_entities we ,
425 mtl_system_items_vl msi ,
426 mtl_lot_numbers mln ,
427 wsh_delivery_details wdd ,
428 mtl_material_transactions mmt
429 WHERE wsn.delivery_detail_id = r_delivery_detail_id
430 AND wdd.delivery_detail_id = wsn.delivery_detail_id+0
431 AND mut.serial_number BETWEEN wsn.fm_serial_number
432 AND wsn.to_serial_number
433 AND wsh_opsm_asn_item_genealogy.get_digits(mut.serial_number) BETWEEN wsh_opsm_asn_item_genealogy.get_digits(wsn.fm_serial_number)
434 AND wsh_opsm_asn_item_genealogy.get_digits(wsn.to_serial_number)
435 AND wsh_opsm_asn_item_genealogy.get_string(mut.serial_number) = wsh_opsm_asn_item_genealogy.get_string(wsn.to_serial_number)
436 AND wsh_opsm_asn_item_genealogy.get_string(wsn.fm_serial_number) = wsh_opsm_asn_item_genealogy.get_string(wsn.to_serial_number)
437 AND mut.organization_id = r_organization_id
438 AND mtln.serial_transaction_id = mut.transaction_id +0
439 AND mtln.transaction_source_id+0 = we.wip_entity_id
440 AND msi.inventory_item_id = mut.inventory_item_id+0
441 AND wdd.inventory_item_id = mut.inventory_item_id+0
442 AND msi.organization_id = mut.organization_id +0
443 AND ood.organization_id = mtln.organization_id
444 AND NOT EXISTS (SELECT 0
445 FROM wsh_delivery_details
446 WHERE delivery_detail_id <> r_delivery_detail_id
447 AND inventory_item_id = msi.inventory_item_id+0
448 AND organization_id = msi.organization_id+0
449 AND serial_number = mut.serial_number)
450 AND mln.inventory_item_id = mtln.inventory_item_id+0
451 AND mln.organization_id = mtln.organization_id +0
452 AND mln.lot_number = mtln.lot_number ||''
453 AND mtln.transaction_id+0 = mmt.transaction_id
454 AND mmt.transaction_quantity = 1
455 ORDER BY 5 DESC;
456
457 g_serial_number VARCHAR2(32000) := NULL;
458 g_object_id NUMBER;
459 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_OPSM_ASN_ITEM_GENEALOGY';
460 --bug 10357152 - opsmperf
461 genealogy_tab tab_genealogy;
462 END wsh_opsm_asn_item_genealogy;
[Home] [Help]