[Home] [Help]
PACKAGE BODY: APPS.WSH_OPSM_ASN_BE_PKG
Source
1 PACKAGE body wsh_opsm_asn_be_pkg AS
2 /* $Header: wshopsmbeb.pls 120.4 2011/02/16 18:50:47 skaradib noship $ */
3
4 ---------------------------------------------------------------------------------------
5 --
6 -- Procedure: get_asn_data
7 -- Parameters: p_delivery_id - DELIVERY_ID
8 -- p_out_delivery- It is wsh_opsm_asn_delivery_tbl Type
9 -- p_out_container - It is wsh_opsm_asn_containers_tbl Type
10 -- p_out_items-It is wsh_opsm_asn_items_tbl Type
11 -- p_out_itemdetails - It is wsh_opsm_asn_itemdetails_tbl Type
12 -- p_out_itemgenealogy - It is wsh_opsm_asn_itemgenealogy_tbl Type
13 -- x_return_status-return status of the API
14 -- x_msg_data -return Error Message of the API
15
16 -- Description: This Procedure takes delivery_id has input and
17 -- filters all the OPSM Integrated data present in
18 -- that delivery id(delivery id consists of both OPSM and Non OPSM Items).
19 -- This Procedure also loads the delivery details, OPSM Integrated Items ,
20 -- its corresponding Items details container, order and genealogy
21 -- information to their respective table type objects and sends them
22 -- out in the form Out parameters.
23 --
24 ---------------------------------------------------------------------------------------
25 PROCEDURE get_asn_data ( p_delivery_id IN NUMBER ,
26 p_delivery_detail_id IN NUMBER ,
27 p_out_delivery OUT NOCOPY wsh_opsm_asn_delivery_tbl ,
28 p_out_container OUT NOCOPY wsh_opsm_asn_containers_tbl ,
29 p_out_items OUT NOCOPY wsh_opsm_asn_items_tbl ,
30 p_out_itemdetails OUT NOCOPY wsh_opsm_asn_itemdetails_tbl ,
31 p_out_itemgenealogy OUT NOCOPY wsh_opsm_asn_itemgenealogy_tbl,
32 x_return_status OUT NOCOPY VARCHAR2 ,
33 x_msg_data OUT NOCOPY VARCHAR2)
34 IS
35 l_module_name CONSTANT VARCHAR2(100):= 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_ASN_DATA';
36 l_msg_data VARCHAR2(2000):=NULL;
37 l_debug_on BOOLEAN;
38 v_delivery wsh_opsm_asn_delivery_rec;
39 v_delivery_tab wsh_opsm_asn_delivery_tbl;
40 v_container wsh_opsm_asn_containers_rec;
41 v_container_tab wsh_opsm_asn_containers_tbl;
42 v_items wsh_opsm_asn_items_rec;
43 v_items_tab wsh_opsm_asn_items_tbl;
44 v_itemdetails wsh_opsm_asn_itemdetails_rec;
45 v_itemdetails_tab wsh_opsm_asn_itemdetails_tbl;
46 v_itemgenealogy wsh_opsm_asn_itemgenealogy_rec;
47 v_itemgenealogy_tab wsh_opsm_asn_itemgenealogy_tbl;
48 v_container_level VARCHAR2(200);
49 v_parent_id NUMBER;
50 v_opsm_parent_identification VARCHAR2(1000):=NULL;
51 v_serial_identification VARCHAR2(1000):='0---0';
52 v_parent_inventory_item_id NUMBER;
53 v_parent_serial_number VARCHAR2(200):=NULL;
54 v_parent_lot_number VARCHAR2(200):=NULL;
55 v_parent_hierarchy_level NUMBER;
56 v_opsm_flag VARCHAR2(200):=NULL;
57 v_inventory_item_id NUMBER;
58 v_serial_number VARCHAR2(200):=NULL;
59 v_lot_number VARCHAR2(200):=NULL;
60 v_hierarchy_level NUMBER;
61 v_organization_id NUMBER;
62 i NUMBER:=1;
63 j NUMBER:=1;
64 k NUMBER:=1;
65 l NUMBER:=1;
66 m NUMBER:=1;
67 n NUMBER:=1;
68 l_count NUMBER:=0;
69 l_count1 NUMBER:=0;
70 l_count2 NUMBER:=0;
71 l_stmt_num NUMBER := 0;
72 l_end_time TIMESTAMP;
73 l_start_time TIMESTAMP;
74 TYPE delivery_count IS TABLE OF NUMBER INDEX BY VARCHAR2(255);
75 del_count delivery_count;
76 TYPE container_count IS TABLE OF NUMBER INDEX BY VARCHAR2(255);
77 cont_count container_count;
78 TYPE tab_del IS TABLE OF wsh_opsm_asn_deliveries_v%ROWTYPE INDEX BY BINARY_INTEGER;
79 del_tab tab_del;
80
81 CURSOR cur_item
82 IS
83 SELECT *
84 FROM wsh_opsm_asn_items_v
85 WHERE delivery_id=p_delivery_id
86 AND delivery_detail_id = p_delivery_detail_id;
87
88 CURSOR cur_container(p_delivery_id IN NUMBER,
89 --p_delivery_detail_id IN NUMBER,
90 p_container_instance_id IN NUMBER)
91 IS
92 SELECT *
93 FROM wsh_opsm_asn_containers_v
94 WHERE delivery_id = p_delivery_id
95 --AND delivery_detail_id = p_delivery_detail_id
96 AND DECODE(p_container_instance_id,NULL,1,container_instance_id) = DECODE(p_container_instance_id,NULL,1,p_container_instance_id)
97 ORDER BY container_instance_id;
98
99 CURSOR cur_parent_container(p_delivery_id IN NUMBER,
100 --p_delivery_detail_id IN NUMBER,
101 p_container_instance_id IN NUMBER)
102 IS
103 SELECT *
104 FROM wsh_opsm_asn_containers_v
105 WHERE delivery_id = p_delivery_id
106 --AND delivery_detail_id = p_delivery_detail_id
107 AND DECODE(p_container_instance_id,NULL,1,container_instance_id) = DECODE(p_container_instance_id,NULL,1,p_container_instance_id);
108
109 CURSOR cur_delivery
110 IS
111 SELECT *
112 FROM wsh_opsm_asn_deliveries_v
113 WHERE delivery_id = p_delivery_id
114 AND delivery_detail_id = p_delivery_detail_id;
115
116 CURSOR cur_itemdetails(p_delivery_id IN NUMBER,
117 p_delivery_detail_id IN NUMBER)
118 IS
119 SELECT *
120 FROM wsh_opsm_asn_item_details_v
121 WHERE delivery_id = p_delivery_id
122 AND delivery_detail_id = p_delivery_detail_id;
123
124 --bug 10357152 - opsmperf begin
125 /*
126 CURSOR cur_itemgenealogy(p_delivery_id IN NUMBER,
127 p_delivery_detail_id IN NUMBER)
128 IS
129 SELECT *
130 FROM wsh_opsm_asn_item_genealogy_v
131 WHERE delivery_id = p_delivery_id
132 AND delivery_detail_id = p_delivery_detail_id;
133 */
134 rslt_tab_idx wsh_opsm_asn_item_genealogy.t_var_idx_num;
135 rslt_tab wsh_opsm_asn_item_genealogy.tab_genealogy1;-- := wsh_opsm_asn_item_genealogy.tab_genealogy1();
136 rslt_par_tab wsh_opsm_asn_item_genealogy.tab_genealogy1;-- := wsh_opsm_asn_item_genealogy.tab_genealogy1();
137 rslt_tab_tmp wsh_opsm_asn_item_genealogy.tab_genealogy := wsh_opsm_asn_item_genealogy.tab_genealogy();
138 --rslt_par_tab_tmp wsh_opsm_asn_item_genealogy.tab_genealogy1 := wsh_opsm_asn_item_genealogy.tab_genealogy1();
139 idxpargen_tmp NUMBER;
140 idxgen_tmp NUMBER;
141 idx1 VARCHAR2(255);
142 idx2 NUMBER := 1;
143 idxpargen VARCHAR2(255);
144 idxgen NUMBER;
145 V_CUR_ITEMGENEALOGY wsh_opsm_asn_item_genealogy_v%ROWTYPE;
146 l_start_timec VARCHAR2(200) ;
147 l_end_timec VARCHAR2(200) ;
148 l_time_diff_secs NUMBER;
149 l_get_genealogy BOOLEAN := true;
150 TYPE get_genealogy_called_type IS TABLE OF NUMBER INDEX BY VARCHAR2(255);
151 get_genealogy_called get_genealogy_called_type;
152 --bug 10357152 - opsmperf end
153 BEGIN
154
155 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
156 --DECLARE
157 --l_start_time TIMESTAMP;
158 BEGIN
159 SELECT SYSTIMESTAMP INTO l_start_time FROM dual;
160 l_start_timec := TO_CHAR(l_start_time, 'DD-MON-YYYY HH24:MI:SSxFF');
161
162 FND_LOG.String(
163 LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
164 MESSAGE => 'start time= '||TO_CHAR(l_start_time, 'DD-MON-YYYY HH24:MI:SSxFF'),
165 MODULE => 'opsmperf.begin');
166 END;
167 END IF;
168 --dbms_output.put_line('start time= '||l_start_timec);
169
170 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
171 IF l_debug_on IS NULL
172 THEN
173 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
174 END IF;
175 IF l_debug_on THEN
176 WSH_DEBUG_SV.push(l_module_name);
177 WSH_DEBUG_SV.log(l_module_name,'DELIVERY ID',p_delivery_id);
178 END IF;
179 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
180
181 OPEN cur_delivery;
182 FETCH cur_delivery BULK COLLECT INTO del_tab;
183 CLOSE cur_delivery;
184
185 FOR v_cur_item IN cur_item
186 LOOP
187 BEGIN
188 --To check wheather Item is OPSM Integrated or not
189 SELECT 1
190 INTO l_count
191 FROM mtl_system_items_b_kfv msik,
192 mtl_cross_references_vl mcr,
193 mtl_parameters mp
194 WHERE mp.organization_id = msik.organization_id
195 AND msik.inventory_item_id(+) = mcr.inventory_item_id
196 AND mcr.cross_reference_type = 'OPSM INTEGRATED'
197 AND mcr.cross_reference = 'YES'
198 AND mp.master_organization_id = mcr.organization_id
199 AND mp.opsm_enabled_FLAG = 'Y'
200 AND msik.lot_control_code = 2
201 AND msik.organization_id = v_cur_item.organization_id
202 AND NOT EXISTS (SELECT 'Y'
203 FROM MTL_CROSS_REFERENCES_VL
204 WHERE CROSS_REFERENCE_TYPE = 'OPSM INTEGRATED'
205 AND ORGANIZATION_ID = v_cur_item.organization_id
206 AND INVENTORY_ITEM_ID = msik.inventory_item_id
207 AND UPPER(CROSS_REFERENCE) ='NO')
208 AND msik.inventory_item_id = v_cur_item.item_id;
209 IF l_debug_on THEN
210 WSH_DEBUG_SV.log(l_module_name,'OPSM INTEGRATED',l_count);
211 END IF;
212 EXCEPTION
213 WHEN NO_DATA_FOUND THEN
214 l_count:=0;
215 IF l_debug_on THEN
216 WSH_DEBUG_SV.logmsg(l_module_name,'No Data Found error has occured. Oracle error message is '|| substr(SQLERRM,1,200),WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
217 END IF;
218 END;
219 IF l_count > 0 THEN
220 IF NOT del_count.EXISTS(TO_CHAR(v_cur_item.destination_cont_id)
221 ||'.'||TO_CHAR(v_cur_item.invoice_to_org_id)
222 ||'.'||TO_CHAR(v_cur_item.invoice_to_contact_id)
223 ||'.'||TO_CHAR(v_cur_item.soldto_customer_id)
224 ||'.'||TO_CHAR(v_cur_item.soldto_contact_id)) THEN
225 del_count(TO_CHAR(v_cur_item.destination_cont_id)
226 ||'.'||TO_CHAR(v_cur_item.invoice_to_org_id)
227 ||'.'||TO_CHAR(v_cur_item.invoice_to_contact_id)
228 ||'.'||TO_CHAR(v_cur_item.soldto_customer_id)
229 ||'.'||TO_CHAR(v_cur_item.soldto_contact_id)) := 1;
230 END IF;
231 IF v_cur_item.container_instance_id IS NOT NULL THEN
232 FOR v_cur_container IN cur_container(v_cur_item.delivery_id,
233 --v_cur_item.delivery_detail_id,
234 v_cur_item.container_instance_id)
235 LOOP
236 IF NOT cont_count.EXISTS(TO_CHAR(v_cur_item.container_instance_id)
237 ||'.'||TO_CHAR(v_cur_item.destination_cont_id)
238 ||'.'||TO_CHAR(v_cur_item.invoice_to_org_id)
239 ||'.'||TO_CHAR(v_cur_item.invoice_to_contact_id)
240 ||'.'||TO_CHAR(v_cur_item.soldto_customer_id)
241 ||'.'||TO_CHAR(v_cur_item.soldto_contact_id)) THEN
242 cont_count(TO_CHAR(v_cur_item.container_instance_id)
243 ||'.'||TO_CHAR(v_cur_item.destination_cont_id)
244 ||'.'||TO_CHAR(v_cur_item.invoice_to_org_id)
245 ||'.'||TO_CHAR(v_cur_item.invoice_to_contact_id)
246 ||'.'||TO_CHAR(v_cur_item.soldto_customer_id)
247 ||'.'||TO_CHAR(v_cur_item.soldto_contact_id)) := 1;
248 END IF;
249 IF v_cur_container.parent_container_instance_id IS NOT NULL THEN
250 v_parent_id:=v_cur_container.parent_container_instance_id;
251 WHILE 1=1
252 LOOP
253 FOR v_cur_parent_container IN cur_parent_container(v_cur_item.delivery_id,
254 --v_cur_item.delivery_detail_id,
255 v_parent_id)
256 LOOP
257 IF NOT cont_count.EXISTS(TO_CHAR(v_cur_parent_container.parent_container_instance_id)
258 ||'.'||TO_CHAR(v_cur_parent_container.container_instance_id)
259 ||'.'||TO_CHAR(v_cur_item.destination_cont_id)
260 ||'.'||TO_CHAR(v_cur_item.invoice_to_org_id)
261 ||'.'||TO_CHAR(v_cur_item.invoice_to_contact_id)
262 ||'.'||TO_CHAR(v_cur_item.soldto_customer_id)
263 ||'.'||TO_CHAR(v_cur_item.soldto_contact_id)) THEN
264 cont_count(TO_CHAR(v_cur_parent_container.parent_container_instance_id)
265 ||'.'||TO_CHAR(v_cur_parent_container.container_instance_id)
266 ||'.'||TO_CHAR(v_cur_item.destination_cont_id)
267 ||'.'||TO_CHAR(v_cur_item.invoice_to_org_id)
268 ||'.'||TO_CHAR(v_cur_item.invoice_to_contact_id)
269 ||'.'||TO_CHAR(v_cur_item.soldto_customer_id)
270 ||'.'||TO_CHAR(v_cur_item.soldto_contact_id)) := 1;
271 END IF;
272 v_parent_id:=v_cur_parent_container.parent_container_instance_id;
273 END LOOP;
274 IF v_parent_id IS NULL THEN
275 EXIT;
276 END IF;
277 END LOOP;
278 END IF;
279 END LOOP;
280 END IF;
281 END IF;
282 END LOOP;
283
284 FOR v_cur_item IN cur_item
285 LOOP
286 BEGIN
287 --To check wheather Item is OPSM Integrated or not
288 SELECT 1
289 INTO l_count
290 FROM mtl_system_items_b_kfv msik,
291 mtl_cross_references_vl mcr,
292 mtl_parameters mp
293 WHERE mp.organization_id = msik.organization_id
294 AND msik.inventory_item_id(+) = mcr.inventory_item_id
295 AND mcr.cross_reference_type = 'OPSM INTEGRATED'
296 AND mcr.cross_reference = 'YES'
297 AND mp.master_organization_id = mcr.organization_id
298 AND mp.opsm_enabled_FLAG = 'Y'
299 AND msik.lot_control_code = 2
300 AND msik.organization_id = v_cur_item.organization_id
301 AND NOT EXISTS (SELECT 'Y'
302 FROM MTL_CROSS_REFERENCES_VL
303 WHERE CROSS_REFERENCE_TYPE = 'OPSM INTEGRATED'
304 AND ORGANIZATION_ID = v_cur_item.organization_id
305 AND INVENTORY_ITEM_ID = msik.inventory_item_id
306 AND UPPER(CROSS_REFERENCE) ='NO')
307 AND msik.inventory_item_id = v_cur_item.item_id;
308 IF l_debug_on THEN
309 WSH_DEBUG_SV.log(l_module_name,'OPSM INTEGRATED',l_count);
310 END IF;
311 IF l_count > 0 THEN
312 --To get Item Information into the object type
313 v_items:= wsh_opsm_asn_items_rec ( v_cur_item.delivery_id ,
314 v_cur_item.container_instance_id ,
315 v_cur_item.item_id ,
316 v_cur_item.soldto_customer_id ,
317 v_cur_item.soldto_contact_id ,
318 v_cur_item.order_header_id ,
319 v_cur_item.order_line_id ,
320 v_cur_item.sales_order_number ,
321 v_cur_item.ordertype ,
322 v_cur_item.order_line_number ,
323 v_cur_item.delivery_detail_id ,
324 v_cur_item.customer_item_id ,
325 v_cur_item.requested_date ,
326 v_cur_item.promise_date ,
327 v_cur_item.ordered_quantity ,
328 v_cur_item.order_quantity_uom ,
329 v_cur_item.cancelled_quantity ,
330 v_cur_item.shipped_quantity ,
331 v_cur_item.shipping_quantity_uom ,
332 v_cur_item.hazard_class ,
333 v_cur_item.organization_id ,
334 v_cur_item.destination_cont_id ,
335 v_cur_item.invoice_to_org_id ,
336 v_cur_item.invoice_to_contact_id ,
337 v_cur_item.item ,
338 v_cur_item.description ,
339 v_cur_item.net_weight ,
340 v_cur_item.weight_uom ,
341 v_cur_item.volume ,
342 v_cur_item.volume_uom_code ,
343 v_cur_item.packing_instructions ,
344 v_cur_item.item_description ,
345 v_cur_item.lot_control_code ,
346 v_cur_item.serial_number_control_code ,
347 v_cur_item.opsm_integrated_flag ,
348 v_cur_item.serial_type);
349 IF l=1 THEN
350 v_items_tab :=wsh_opsm_asn_items_tbl(v_items);
351 END IF;
352 IF l>1 THEN
353 v_items_tab.EXTEND;
354 v_items_tab(l):=v_items;
355 END IF;
356 l:=l+1;
357 FOR v_cur_itemdetails IN cur_itemdetails(v_cur_item.delivery_id,
358 v_cur_item.delivery_detail_id)
359 LOOP
360 --To get ItemDetails Information into the object type
361 v_itemdetails:= wsh_opsm_asn_itemdetails_rec( v_cur_itemdetails.delivery_id ,
362 v_cur_itemdetails.organization_id ,
363 v_cur_itemdetails.delivery_assignment_id ,
364 v_cur_itemdetails.delivery_detail_id ,
365 v_cur_itemdetails.container_instance_id ,
366 v_cur_itemdetails.master_container_item_id ,
367 v_cur_itemdetails.detail_container_item_id ,
368 v_cur_itemdetails.load_sequence_number ,
369 v_cur_itemdetails.lot_number ,
370 v_cur_itemdetails.parent_lot_number ,
371 v_cur_itemdetails.shipped_quantity ,
372 v_cur_itemdetails.shipping_quantity_uom ,
373 v_cur_itemdetails.genealogy_object_id ,
374 v_cur_itemdetails.origination_date ,
375 v_cur_itemdetails.best_by_date ,
376 v_cur_itemdetails.retest_date ,
377 v_cur_itemdetails.expiration_date ,
378 v_cur_itemdetails.from_serial_number ,
379 v_cur_itemdetails.to_serial_number ,
380 v_cur_itemdetails.organization_code );
381
382 IF m=1 THEN
383 v_itemdetails_tab :=wsh_opsm_asn_itemdetails_tbl(v_itemdetails);
384 END IF;
385 IF m>1 THEN
386 v_itemdetails_tab.EXTEND;
387 v_itemdetails_tab(m):=v_itemdetails;
388 END IF;
389 m:=m+1;
390 --bug 10357152 - opsmperf begin
391 IF NOT get_genealogy_called.EXISTS(TO_CHAR(v_cur_itemdetails.DELIVERY_DETAIL_ID)
392 || '.' || TO_CHAR(v_cur_itemdetails.GENEALOGY_OBJECT_ID)) THEN
393 get_genealogy_called(TO_CHAR(v_cur_itemdetails.DELIVERY_DETAIL_ID)
394 || '.' || TO_CHAR(v_cur_itemdetails.GENEALOGY_OBJECT_ID)) := 1;
395 wsh_opsm_asn_item_genealogy.get_genealogy(
396 v_cur_itemdetails.GENEALOGY_OBJECT_ID ,
397 v_cur_itemdetails.DELIVERY_DETAIL_ID,
398 v_cur_itemdetails.DELIVERY_ID ,
399 v_cur_itemdetails.ORGANIZATION_ID,
400 v_cur_itemdetails.FROM_SERIAL_NUMBER,
401 v_cur_itemdetails.TO_SERIAL_NUMBER,
402 rslt_tab_tmp
403 );
404 --l_get_genealogy := false;
405 --idx1 := rslt_tab.count;
406
407 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
408 FND_LOG.String(
409 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
410 MESSAGE => 'Rows in rslt_tab_tmp',
411 MODULE => 'wsh.plsql.wsh_opsm_asn_be_pkg.get_asn_data');
412 END IF;
413 IF (rslt_tab_tmp.COUNT > 0) THEN
414 FOR idx IN rslt_tab_tmp.FIRST..rslt_tab_tmp.LAST LOOP
415 idx1 := TO_CHAR(rslt_tab_tmp(idx).delivery_id) || '.' ||
416 TO_CHAR(rslt_tab_tmp(idx).delivery_detail_id) || '.' ||
417 TO_CHAR(rslt_tab_tmp(idx).inventory_item_id) || '.' ||
418 rslt_tab_tmp(idx).lot_number || '.' ||
419 rslt_tab_tmp(idx).serial_number || '.' ||
420 TO_CHAR(rslt_tab_tmp(idx).rlevel);
421 rslt_tab_idx(idx2) := idx1;
422 --rslt_tab.EXTEND;
423 --rslt_par_tab.EXTEND;
424 idx2 := idx2 + 1;
425 rslt_tab(idx1).item_nbr := rslt_tab_tmp(idx).item_nbr ;
426 l_stmt_num := 95.5;
427 rslt_tab(idx1).primary_uom_code := rslt_tab_tmp(idx).primary_uom_code ;
428 rslt_tab(idx1).lot_number := rslt_tab_tmp(idx).lot_number ;
429 rslt_tab(idx1).serial_number := rslt_tab_tmp(idx).serial_number ;
430 rslt_tab(idx1).organization_id := rslt_tab_tmp(idx).organization_id ;
431 rslt_tab(idx1).job_name := rslt_tab_tmp(idx).job_name ;
432 rslt_tab(idx1).inventory_item_id := rslt_tab_tmp(idx).inventory_item_id ;
433 rslt_tab(idx1).origination_date := rslt_tab_tmp(idx).origination_date ;
434 rslt_tab(idx1).best_by_date := rslt_tab_tmp(idx).best_by_date ;
435 rslt_tab(idx1).retest_date := rslt_tab_tmp(idx).retest_date ;
436 rslt_tab(idx1).expiration_date := rslt_tab_tmp(idx).expiration_date ;
437 rslt_tab(idx1).organization_code := rslt_tab_tmp(idx).organization_code ;
438 rslt_tab(idx1).rlevel := rslt_tab_tmp(idx).rlevel ;
439 rslt_tab(idx1).parent_rlevel := rslt_tab_tmp(idx).parent_rlevel ;
440 rslt_tab(idx1).lot_control_code := rslt_tab_tmp(idx).lot_control_code ;
441 rslt_tab(idx1).serial_number_control_code := rslt_tab_tmp(idx).serial_number_control_code;
442 rslt_tab(idx1).cross_reference := rslt_tab_tmp(idx).cross_reference ;
443 rslt_tab(idx1).serial_type := rslt_tab_tmp(idx).serial_type ;
444 rslt_tab(idx1).parent_lot_number := rslt_tab_tmp(idx).parent_lot_number ;
445 rslt_tab(idx1).parent_serial_number := rslt_tab_tmp(idx).parent_serial_number ;
446 rslt_tab(idx1).parent_inventory_item_id := rslt_tab_tmp(idx).parent_inventory_item_id ;
447 rslt_tab(idx1).DELIVERY_DETAIL_ID := rslt_tab_tmp(idx).DELIVERY_DETAIL_ID ;
448 rslt_tab(idx1).DELIVERY_ID := rslt_tab_tmp(idx).DELIVERY_ID ;
449 rslt_tab(idx1).GENEALOGY_OBJECT_ID := rslt_tab_tmp(idx).GENEALOGY_OBJECT_ID ;
450
451 rslt_par_tab(idx1).item_nbr := rslt_tab_tmp(idx).item_nbr ;
452 l_stmt_num := 95.7;
453 rslt_par_tab(idx1).primary_uom_code := rslt_tab_tmp(idx).primary_uom_code ;
454 rslt_par_tab(idx1).lot_number := rslt_tab_tmp(idx).lot_number ;
455 rslt_par_tab(idx1).serial_number := rslt_tab_tmp(idx).serial_number ;
456 rslt_par_tab(idx1).organization_id := rslt_tab_tmp(idx).organization_id ;
457 rslt_par_tab(idx1).job_name := rslt_tab_tmp(idx).job_name ;
458 rslt_par_tab(idx1).inventory_item_id := rslt_tab_tmp(idx).inventory_item_id ;
459 rslt_par_tab(idx1).origination_date := rslt_tab_tmp(idx).origination_date ;
460 rslt_par_tab(idx1).best_by_date := rslt_tab_tmp(idx).best_by_date ;
461 rslt_par_tab(idx1).retest_date := rslt_tab_tmp(idx).retest_date ;
462 rslt_par_tab(idx1).expiration_date := rslt_tab_tmp(idx).expiration_date ;
463 rslt_par_tab(idx1).organization_code := rslt_tab_tmp(idx).organization_code ;
464 rslt_par_tab(idx1).rlevel := rslt_tab_tmp(idx).rlevel ;
465 rslt_par_tab(idx1).parent_rlevel := rslt_tab_tmp(idx).parent_rlevel ;
466 rslt_par_tab(idx1).lot_control_code := rslt_tab_tmp(idx).lot_control_code ;
467 rslt_par_tab(idx1).serial_number_control_code := rslt_tab_tmp(idx).serial_number_control_code;
468 rslt_par_tab(idx1).cross_reference := rslt_tab_tmp(idx).cross_reference ;
469 rslt_par_tab(idx1).serial_type := rslt_tab_tmp(idx).serial_type ;
470 rslt_par_tab(idx1).parent_lot_number := rslt_tab_tmp(idx).parent_lot_number ;
471 rslt_par_tab(idx1).parent_serial_number := rslt_tab_tmp(idx).parent_serial_number ;
472 rslt_par_tab(idx1).parent_inventory_item_id := rslt_tab_tmp(idx).parent_inventory_item_id ;
473 rslt_par_tab(idx1).DELIVERY_DETAIL_ID := rslt_tab_tmp(idx).DELIVERY_DETAIL_ID ;
474 rslt_par_tab(idx1).DELIVERY_ID := rslt_tab_tmp(idx).DELIVERY_ID ;
475 rslt_par_tab(idx1).GENEALOGY_OBJECT_ID := rslt_tab_tmp(idx).GENEALOGY_OBJECT_ID ;
476 END LOOP;
477 END IF;
478 END IF;
479 --bug 10357152 - opsmperf end
480 END LOOP;
481 --bug 10357152 - opsmperf begin
482 /*****
483 FOR v_cur_itemgenealogy IN cur_itemgenealogy(v_cur_item.delivery_id,
484 v_cur_item.delivery_detail_id)
485 LOOP
486 rslt_tab(idx1) := rslt_tab.first;
487
488 loop
489 exit when rslt_tab(idx1) is null;
490 *****/
491 IF rslt_tab_idx.COUNT > 0 THEN
492 FOR idxgen in rslt_tab_idx.FIRST..rslt_tab_idx.LAST LOOP
493 idx1 := rslt_tab_idx(idxgen);
494 v_cur_itemgenealogy.ITEM_NUMBER := rslt_tab(idx1).item_nbr;
495 v_cur_itemgenealogy.ITEM_UOM := rslt_tab(idx1).primary_uom_code ;
496 v_cur_itemgenealogy.LOT_NUMBER := rslt_tab(idx1).lot_number ;
497 v_cur_itemgenealogy.SERIAL_NUMBER := rslt_tab(idx1).serial_number ;
498 v_cur_itemgenealogy.ORGANIZATION_ID := rslt_tab(idx1).organization_id ;
499 v_cur_itemgenealogy.JOB_NAME := rslt_tab(idx1).job_name ;
500 v_cur_itemgenealogy.INVENTORY_ITEM_ID := rslt_tab(idx1).inventory_item_id ;
501 v_cur_itemgenealogy.ORIGINATION_DATE := rslt_tab(idx1).origination_date ;
502 v_cur_itemgenealogy.BEST_BY_DATE := rslt_tab(idx1).best_by_date ;
503 v_cur_itemgenealogy.RETEST_DATE := rslt_tab(idx1).retest_date ;
504 v_cur_itemgenealogy.EXPIRATION_DATE := rslt_tab(idx1).expiration_date ;
505 v_cur_itemgenealogy.ORGANIZATION_CODE := rslt_tab(idx1).organization_code ;
506 v_cur_itemgenealogy.HIERARCHY_LEVEL := rslt_tab(idx1).rlevel ;
507 v_cur_itemgenealogy.PARENT_HIERARCHY_LEVEL := rslt_tab(idx1).parent_rlevel ;
508 v_cur_itemgenealogy.LOT_CONTROL_CODE := rslt_tab(idx1).lot_control_code ;
509 v_cur_itemgenealogy.SERIAL_NUMBER_CONTROL_CODE := rslt_tab(idx1).serial_number_control_code;
510 v_cur_itemgenealogy.OPSM_INTEGRATED_FLAG := rslt_tab(idx1).cross_reference ;
511 v_cur_itemgenealogy.SERIAL_TYPE := rslt_tab(idx1).serial_type ;
512 v_cur_itemgenealogy.PARENT_LOT_NUMBER := rslt_tab(idx1).parent_lot_number ;
513 v_cur_itemgenealogy.PARENT_SERIAL_NUMBER := rslt_tab(idx1).parent_serial_number ;
514 v_cur_itemgenealogy.PARENT_INVENTORY_ITEM_ID := rslt_tab(idx1).parent_inventory_item_id ;
515 v_cur_itemgenealogy.DELIVERY_DETAIL_ID := rslt_tab(idx1).DELIVERY_DETAIL_ID ;
516 v_cur_itemgenealogy.DELIVERY_ID := rslt_tab(idx1).DELIVERY_ID ;
517 v_cur_itemgenealogy.GENEALOGY_OBJECT_ID := rslt_tab(idx1).GENEALOGY_OBJECT_ID;
518
519
520
521 BEGIN
522 --To check wheather Item is OPSM Integrated or not
523 SELECT 1
524 INTO l_count1
525 FROM mtl_system_items_b_kfv msik,
526 mtl_cross_references_vl mcr,
527 mtl_parameters mp
528 WHERE mp.organization_id = msik.organization_id
529 AND msik.inventory_item_id(+) = mcr.inventory_item_id
530 AND mcr.cross_reference_type = 'OPSM INTEGRATED'
531 AND mcr.cross_reference = 'YES'
532 AND mp.master_organization_id = mcr.organization_id
533 AND mp.opsm_enabled_FLAG = 'Y'
534 AND msik.lot_control_code = 2
535 AND msik.organization_id = v_cur_itemgenealogy.organization_id
536 AND NOT EXISTS (SELECT 'Y'
537 FROM MTL_CROSS_REFERENCES_VL
538 WHERE CROSS_REFERENCE_TYPE = 'OPSM INTEGRATED'
539 AND ORGANIZATION_ID = v_cur_itemgenealogy.organization_id
540 AND INVENTORY_ITEM_ID = msik.inventory_item_id
541 AND UPPER(CROSS_REFERENCE) ='NO')
542 AND msik.inventory_item_id = v_cur_itemgenealogy.inventory_item_id;
543
544 EXCEPTION
545 WHEN NO_DATA_FOUND THEN
546 l_count1:=0;
547 END ;
548 IF l_count1 > 0 THEN
549 IF v_cur_itemgenealogy.parent_inventory_item_id=0 THEN
550 v_opsm_parent_identification:= v_cur_itemgenealogy.parent_inventory_item_id||'-'||
551 v_cur_itemgenealogy.parent_serial_number||'-'||
552 v_cur_itemgenealogy.parent_lot_number||'-'||
553 v_cur_itemgenealogy.parent_hierarchy_level;
554 END IF;
555 IF v_cur_itemgenealogy.parent_inventory_item_id||'-'||
556 v_cur_itemgenealogy.parent_serial_number||'-'||
557 v_cur_itemgenealogy.parent_lot_number||'-'||
558 v_cur_itemgenealogy.parent_hierarchy_level <> v_serial_identification THEN
559
560 v_opsm_parent_identification := v_cur_itemgenealogy.parent_inventory_item_id||'-'||
561 v_cur_itemgenealogy.parent_serial_number||'-'||
562 v_cur_itemgenealogy.parent_lot_number||'-'||
563 v_cur_itemgenealogy.parent_hierarchy_level;
564 END IF;
565
566 v_itemgenealogy:= wsh_opsm_asn_itemgenealogy_rec( v_cur_itemgenealogy.item_number ,
567 v_cur_itemgenealogy.item_uom ,
568 v_cur_itemgenealogy.lot_number ,
569 v_cur_itemgenealogy.genealogy_object_id ,
570 v_cur_itemgenealogy.delivery_id ,
571 v_cur_itemgenealogy.serial_number ,
572 v_cur_itemgenealogy.delivery_detail_id ,
573 v_cur_itemgenealogy.job_name ,
574 v_cur_itemgenealogy.hierarchy_level ,
575 v_cur_itemgenealogy.parent_hierarchy_level ,
576 v_cur_itemgenealogy.inventory_item_id ,
577 v_cur_itemgenealogy.origination_date ,
578 v_cur_itemgenealogy.best_by_date ,
579 v_cur_itemgenealogy.retest_date ,
580 v_cur_itemgenealogy.expiration_date ,
581 v_cur_itemgenealogy.organization_code ,
582 v_cur_itemgenealogy.organization_id ,
583 v_cur_itemgenealogy.lot_control_code ,
584 v_cur_itemgenealogy.serial_number_control_code ,
585 v_cur_itemgenealogy.opsm_integrated_flag ,
586 v_cur_itemgenealogy.serial_type ,
587 v_cur_itemgenealogy.inventory_item_id||'-'||
588 v_cur_itemgenealogy.serial_number||'-'||
589 v_cur_itemgenealogy.lot_number||'-'||
590 v_cur_itemgenealogy.hierarchy_level ,
591 v_cur_itemgenealogy.parent_inventory_item_id||'-'||
592 v_cur_itemgenealogy.parent_serial_number||'-'||
593 v_cur_itemgenealogy.parent_lot_number||'-'||
594 v_cur_itemgenealogy.parent_hierarchy_level ,
595 v_opsm_parent_identification);
596
597
598 IF n=1 THEN
599 v_itemgenealogy_tab :=wsh_opsm_asn_itemgenealogy_tbl(v_itemgenealogy);
600 END IF;
601 IF n>1 THEN
602 v_itemgenealogy_tab.EXTEND;
603 v_itemgenealogy_tab(n):=v_itemgenealogy;
604 END IF;
605 n:=n+1;
606 ELSE
607 v_serial_identification:= v_cur_itemgenealogy.inventory_item_id||'-'||
608 v_cur_itemgenealogy.serial_number||'-'||
609 v_cur_itemgenealogy.lot_number||'-'||
610 v_cur_itemgenealogy.hierarchy_level;
611
612 v_parent_inventory_item_id := v_cur_itemgenealogy.parent_inventory_item_id;
613 v_parent_serial_number := v_cur_itemgenealogy.parent_serial_number;
614 v_parent_lot_number := v_cur_itemgenealogy.parent_lot_number;
615 v_parent_hierarchy_level := v_cur_itemgenealogy.parent_hierarchy_level;
616
617
618 WHILE 1=1
619 LOOP
620 --bug 10357152 - opsmperf begin
621 /*
622 SELECT opsm_integrated_flag ,
623 parent_inventory_item_id ,
624 parent_lot_number ,
625 parent_serial_number ,
626 parent_hierarchy_level ,
627 organization_id
628 INTO v_opsm_flag ,
629 v_inventory_item_id ,
630 v_lot_number ,
631 v_serial_number ,
632 v_hierarchy_level ,
633 v_organization_id
634 FROM wsh_opsm_asn_item_genealogy_v
635 WHERE delivery_id = v_cur_itemgenealogy.delivery_id
636 AND delivery_detail_id = v_cur_itemgenealogy.delivery_detail_id
637 AND inventory_item_id = v_parent_inventory_item_id
638 AND lot_number = v_parent_lot_number
639 AND serial_number = v_parent_serial_number
640 AND hierarchy_level = v_parent_hierarchy_level;
641 */
642 v_opsm_flag := null;
643 v_inventory_item_id := null;
644 v_lot_number := null;
645 v_serial_number := null;
646 v_hierarchy_level := null;
647 v_organization_id := null;
648 /*
649 FOR idxpargen in rslt_par_tab.FIRST..rslt_par_tab.LAST LOOP
650 ----dbms_output.put_line('Inside FOR idxpargen in 1..rslt_par_tab.COUNT LOOP');
651 IF ((rslt_par_tab(idxpargen).delivery_id = v_cur_itemgenealogy.delivery_id)
652 AND (rslt_par_tab(idxpargen).delivery_detail_id = v_cur_itemgenealogy.delivery_detail_id)
653 AND (rslt_par_tab(idxpargen).inventory_item_id = v_parent_inventory_item_id)
654 AND (rslt_par_tab(idxpargen).lot_number = v_parent_lot_number)
655 AND (rslt_par_tab(idxpargen).serial_number = v_parent_serial_number)
656 AND (rslt_par_tab(idxpargen).rlevel = v_parent_hierarchy_level)
657 )
658 THEN
659 */
660
661 idxpargen := TO_CHAR(v_cur_itemgenealogy.delivery_id) || '.' ||
662 TO_CHAR(v_cur_itemgenealogy.delivery_detail_id) || '.' ||
663 TO_CHAR(v_parent_inventory_item_id) || '.' ||
664 v_parent_lot_number || '.' ||
665 v_parent_serial_number || '.' ||
666 TO_CHAR(v_parent_hierarchy_level);
667 IF rslt_par_tab.EXISTS(idxpargen) THEN
668 v_opsm_flag := rslt_par_tab(idxpargen).cross_reference;
669 v_inventory_item_id := rslt_par_tab(idxpargen).parent_inventory_item_id;
670 v_lot_number := rslt_par_tab(idxpargen).parent_lot_number;
671 v_serial_number := rslt_par_tab(idxpargen).parent_serial_number;
672 v_hierarchy_level := rslt_par_tab(idxpargen).parent_rlevel;
673 v_organization_id := rslt_par_tab(idxpargen).organization_id;
674 END IF;
675 --END LOOP;
676 IF v_inventory_item_id IS NULL THEN
677 GOTO rslt_par_tab_no_data;
678 END IF;
679 --bug 10357152 - opsmperf end
680
681 --To check wheather Item is OPSM Integrated or not
682 IF v_inventory_item_id=0 THEN
683 l_count2:=1;
684 ELSE
685 BEGIN
686 SELECT 1
687 INTO l_count2
688 FROM mtl_system_items_b_kfv msik,
689 mtl_cross_references_vl mcr,
690 mtl_parameters mp
691 WHERE mp.organization_id = msik.organization_id
692 AND msik.inventory_item_id(+) = mcr.inventory_item_id
693 AND mcr.cross_reference_type = 'OPSM INTEGRATED'
694 AND mcr.cross_reference = 'YES'
695 AND mp.master_organization_id = mcr.organization_id
696 AND mp.opsm_enabled_FLAG = 'Y'
697 AND msik.lot_control_code = 2
698 AND msik.organization_id = v_organization_id
699 AND NOT EXISTS (SELECT 'Y'
700 FROM MTL_CROSS_REFERENCES_VL
701 WHERE CROSS_REFERENCE_TYPE = 'OPSM INTEGRATED'
702 AND ORGANIZATION_ID = v_organization_id
703 AND INVENTORY_ITEM_ID = msik.inventory_item_id
704 AND UPPER(CROSS_REFERENCE) ='NO')
705 AND msik.inventory_item_id = v_parent_inventory_item_id;
706
707 EXCEPTION
708 WHEN NO_DATA_FOUND THEN
709 l_count2:=0;
710 END ;
711 END IF;
712 IF l_count2 > 0 THEN
713 v_opsm_parent_identification := v_parent_inventory_item_id||'-'||
714 v_parent_serial_number||'-'||
715 v_parent_lot_number||'-'||
716 v_parent_hierarchy_level;
717 EXIT;
718 ELSE
719 v_parent_inventory_item_id := v_inventory_item_id;
720 v_parent_serial_number := v_serial_number;
721 v_parent_lot_number := v_lot_number;
722 v_parent_hierarchy_level := v_hierarchy_level;
723 END IF;
724 END LOOP;
725 END IF;
726
727 END LOOP;
728 END IF;
729
730 IF v_cur_item.container_instance_id IS NOT NULL THEN
731 IF cont_count.EXISTS(TO_CHAR(v_cur_item.container_instance_id)
732 ||'.'||TO_CHAR(v_cur_item.destination_cont_id)
733 ||'.'||TO_CHAR(v_cur_item.invoice_to_org_id)
734 ||'.'||TO_CHAR(v_cur_item.invoice_to_contact_id)
735 ||'.'||TO_CHAR(v_cur_item.soldto_customer_id)
736 ||'.'||TO_CHAR(v_cur_item.soldto_contact_id)) THEN
737 cont_count.DELETE(TO_CHAR(v_cur_item.container_instance_id)
738 ||'.'||TO_CHAR(v_cur_item.destination_cont_id)
739 ||'.'||TO_CHAR(v_cur_item.invoice_to_org_id)
740 ||'.'||TO_CHAR(v_cur_item.invoice_to_contact_id)
741 ||'.'||TO_CHAR(v_cur_item.soldto_customer_id)
742 ||'.'||TO_CHAR(v_cur_item.soldto_contact_id));
743 FOR v_cur_container IN cur_container(v_cur_item.delivery_id,
744 --v_cur_item.delivery_detail_id,
745 v_cur_item.container_instance_id)
746 LOOP
747 --To get Container Information into the object type
748 v_container_level:=NULL;
749 IF v_cur_container.parent_container_instance_id IS NOT NULL THEN
750 v_parent_id :=v_cur_container.parent_container_instance_id;
751 WHILE 1=1
752 LOOP
753 v_container_level:='PARENT';
754 FOR v_cur_parent_container IN cur_parent_container(v_cur_item.delivery_id,
755 --v_cur_item.delivery_detail_id,
756 v_parent_id)
757 LOOP
758 IF cont_count.EXISTS(TO_CHAR(v_cur_parent_container.parent_container_instance_id)
759 ||'.'||TO_CHAR(v_cur_parent_container.container_instance_id)
760 ||'.'||TO_CHAR(v_cur_item.destination_cont_id)
761 ||'.'||TO_CHAR(v_cur_item.invoice_to_org_id)
762 ||'.'||TO_CHAR(v_cur_item.invoice_to_contact_id)
763 ||'.'||TO_CHAR(v_cur_item.soldto_customer_id)
764 ||'.'||TO_CHAR(v_cur_item.soldto_contact_id)) THEN
765 cont_count.DELETE(TO_CHAR(v_cur_parent_container.parent_container_instance_id)
766 ||'.'||TO_CHAR(v_cur_parent_container.container_instance_id)
767 ||'.'||TO_CHAR(v_cur_item.destination_cont_id)
768 ||'.'||TO_CHAR(v_cur_item.invoice_to_org_id)
769 ||'.'||TO_CHAR(v_cur_item.invoice_to_contact_id)
770 ||'.'||TO_CHAR(v_cur_item.soldto_customer_id)
771 ||'.'||TO_CHAR(v_cur_item.soldto_contact_id)) ;
772 v_container:=wsh_opsm_asn_containers_rec( v_cur_parent_container.container_instance_id ,
773 v_cur_parent_container.lpn ,
774 v_cur_parent_container.container_item_id ,
775 v_cur_parent_container.master_serial_number ,
776 v_cur_parent_container.container_serial_number ,
777 v_cur_parent_container.lot_number ,
778 v_cur_parent_container.parent_container_instance_id ,
779 v_cur_parent_container.parent_lpn ,
780 v_cur_parent_container.container_type ,
781 v_cur_parent_container.container_gross_weight ,
782 v_cur_parent_container.container_gross_weight_uom ,
783 v_cur_parent_container.container_volume ,
784 v_cur_parent_container.container_volume_uom ,
785 v_cur_parent_container.container_net_weight ,
786 v_cur_parent_container.container_fill_percent ,
787 v_cur_parent_container.container_maximum_volume ,
788 v_cur_parent_container.container_maximum_load_weight ,
789 v_cur_parent_container.container_minimum_fill_percent,
790 v_cur_parent_container.container_tare_weight ,
791 v_cur_parent_container.container_seal_code ,
792 v_cur_parent_container.container_tracking_number ,
793 v_cur_parent_container.item_description ,
794 v_cur_parent_container.delivery_id ,
795 v_cur_parent_container.lpn_type ,
796 v_cur_item.soldto_customer_id ,
797 v_cur_item.soldto_contact_id ,
798 v_cur_item.destination_cont_id ,
799 v_cur_item.invoice_to_org_id ,
800 v_cur_item.invoice_to_contact_id ,
801 v_container_level);
802
803 IF j=1 THEN
804 v_container_tab :=wsh_opsm_asn_containers_tbl(v_container);
805 END IF;
806 IF j>1 THEN
807 v_container_tab.EXTEND;
808 v_container_tab(j):=v_container;
809 END IF;
810 j:=j+1;
811 END IF;
812 v_parent_id:=v_cur_parent_container.parent_container_instance_id;
813 END LOOP;
814 IF v_parent_id IS NULL THEN
815 EXIT;
816 END IF;
817 END LOOP;
818 v_container_level:='CHILD';
819 END IF;
820 v_container:=wsh_opsm_asn_containers_rec( v_cur_container.container_instance_id ,
821 v_cur_container.lpn ,
822 v_cur_container.container_item_id ,
823 v_cur_container.master_serial_number ,
824 v_cur_container.container_serial_number ,
825 v_cur_container.lot_number ,
826 v_cur_container.parent_container_instance_id ,
827 v_cur_container.parent_lpn ,
828 v_cur_container.container_type ,
829 v_cur_container.container_gross_weight ,
830 v_cur_container.container_gross_weight_uom ,
831 v_cur_container.container_volume ,
832 v_cur_container.container_volume_uom ,
833 v_cur_container.container_net_weight ,
834 v_cur_container.container_fill_percent ,
835 v_cur_container.container_maximum_volume ,
836 v_cur_container.container_maximum_load_weight ,
837 v_cur_container.container_minimum_fill_percent,
838 v_cur_container.container_tare_weight ,
839 v_cur_container.container_seal_code ,
840 v_cur_container.container_tracking_number ,
841 v_cur_container.item_description ,
842 v_cur_container.delivery_id ,
843 v_cur_container.lpn_type ,
844 v_cur_item.soldto_customer_id ,
845 v_cur_item.soldto_contact_id ,
846 v_cur_item.destination_cont_id ,
847 v_cur_item.invoice_to_org_id ,
848 v_cur_item.invoice_to_contact_id ,
849 v_container_level);
850
851 IF j=1 THEN
852 v_container_tab :=wsh_opsm_asn_containers_tbl(v_container);
853 END IF;
854 IF j>1 THEN
855 v_container_tab.EXTEND;
856 v_container_tab(j):=v_container;
857 END IF;
858 j:=j+1;
859 END LOOP;
860 END IF;
861 END IF;
862 IF del_count.exists(TO_CHAR(v_cur_item.destination_cont_id)
863 ||'.'||TO_CHAR(v_cur_item.invoice_to_org_id)
864 ||'.'||TO_CHAR(v_cur_item.invoice_to_contact_id)
865 ||'.'||TO_CHAR(v_cur_item.soldto_customer_id)
866 ||'.'||TO_CHAR(v_cur_item.soldto_contact_id)) THEN
867 del_count.DELETE(TO_CHAR(v_cur_item.destination_cont_id)
868 ||'.'||TO_CHAR(v_cur_item.invoice_to_org_id)
869 ||'.'||TO_CHAR(v_cur_item.invoice_to_contact_id)
870 ||'.'||TO_CHAR(v_cur_item.soldto_customer_id)
871 ||'.'||TO_CHAR(v_cur_item.soldto_contact_id));
872
873 FOR l IN 1..del_tab.COUNT
874 LOOP
875
876 IF NVL(del_tab(l).destination_cont_id,-999) = NVL( v_cur_item.destination_cont_id,-999) AND
877 NVL(del_tab(l).invoice_to_org_id,-999) = NVL(v_cur_item.invoice_to_org_id,-999) AND
878 NVL(del_tab(l).invoice_to_contact_id,-999) = NVL(v_cur_item.invoice_to_contact_id,-999) AND
879 NVL(del_tab(l).soldto_customer_id,-999) = NVL(v_cur_item.soldto_customer_id,-999) AND
880 NVL(del_tab(l).soldto_contact_id,-999) = NVL(v_cur_item.soldto_contact_id,-999) THEN
881 --To get delivery Information into the object type
882 v_delivery:= wsh_opsm_asn_delivery_rec( del_tab(l).transaction_date ,
883 del_tab(l).document_code ,
884 del_tab(l).organization_id ,
885 del_tab(l).delivery_id ,
886 del_tab(l).delivery_name ,
887 del_tab(l).organization_code ,
888 del_tab(l).warehouse_location_id ,
889 del_tab(l).warehouse_code ,
890 del_tab(l).warehouse_name ,
891 del_tab(l).warehouse_address1 ,
892 del_tab(l).warehouse_address2 ,
893 del_tab(l).warehouse_address3 ,
894 del_tab(l).warehouse_address4 ,
895 del_tab(l).warehouse_city ,
896 del_tab(l).warehouse_postal_code ,
897 del_tab(l).warehouse_country ,
898 del_tab(l).warehouse_county ,
899 del_tab(l).warehouse_state ,
900 del_tab(l).warehouse_contact_name ,
901 del_tab(l).warehouse_contact_emailid ,
902 del_tab(l).warehouse_contact_phonenumber ,
903 del_tab(l).destination_code ,
904 del_tab(l).destination_name ,
905 del_tab(l).destination_address1 ,
906 del_tab(l).destination_address2 ,
907 del_tab(l).destination_address3 ,
908 del_tab(l).destination_address4 ,
909 del_tab(l).destination_city ,
910 del_tab(l).destination_postal_code ,
911 del_tab(l).destination_country ,
912 del_tab(l).destination_state ,
913 del_tab(l).destination_province ,
914 del_tab(l).destination_county ,
915 del_tab(l).destination_cont_id ,
916 del_tab(l).destination_cont_area_code ,
917 del_tab(l).destination_cont_phone_number ,
918 del_tab(l).destination_cont_name ,
919 del_tab(l).destination_cont_email ,
920 del_tab(l).destination_cont_country_code ,
921 del_tab(l).destination_cont_phone_extn ,
922 del_tab(l).bill_of_lading ,
923 del_tab(l).freight_terms_code ,
924 del_tab(l).fob_point_code ,
925 del_tab(l).initial_pickup_date ,
926 del_tab(l).port_of_loading ,
927 del_tab(l).change_of_ownership ,
928 del_tab(l).dock_code ,
929 del_tab(l).status_code ,
930 del_tab(l).created_by ,
931 del_tab(l).creation_date ,
932 del_tab(l).last_updated_by ,
933 del_tab(l).last_update_date ,
934 del_tab(l).invoice_to_org_id ,
935 del_tab(l).invoice_to_contact_id ,
936 del_tab(l).invoice_name ,
937 del_tab(l).invoice_address1 ,
938 del_tab(l).invoice_address2 ,
939 del_tab(l).invoice_address3 ,
940 del_tab(l).invoice_address4 ,
941 del_tab(l).invoice_city ,
942 del_tab(l).invoice_postal_code ,
943 del_tab(l).invoice_country_int ,
944 del_tab(l).invoice_state_int ,
945 del_tab(l).invoice_province_int ,
946 del_tab(l).invoice_county ,
947 del_tab(l).invoice_cont_name ,
948 del_tab(l).invoice_cont_job_title ,
949 del_tab(l).invoice_cont_email_address ,
950 del_tab(l).invoice_cont_country_code ,
951 del_tab(l).invoice_cont_area_code ,
952 del_tab(l).invoice_cont_phone_number ,
953 del_tab(l).invoice_cont_phone_extn ,
954 del_tab(l).soldto_customer_id ,
955 del_tab(l).soldto_contact_id ,
956 del_tab(l).soldto_address_id ,
957 del_tab(l).soldto_name ,
958 del_tab(l).soldto_address1 ,
959 del_tab(l).soldto_address2 ,
960 del_tab(l).soldto_address3 ,
961 del_tab(l).soldto_address4 ,
962 del_tab(l).soldto_city ,
963 del_tab(l).soldto_postal_code ,
964 del_tab(l).soldto_country ,
965 del_tab(l).soldto_state ,
966 del_tab(l).soldto_province ,
967 del_tab(l).soldto_county ,
968 del_tab(l).soldto_cont_name ,
969 del_tab(l).soldto_cont_job_title ,
970 del_tab(l).soldto_cont_email ,
971 del_tab(l).soldto_cont_country_code ,
972 del_tab(l).soldto_cont_area_code ,
973 del_tab(l).soldto_cont_phone_number ,
974 del_tab(l).soldto_cont_phone_extn ,
975 del_tab(l).soldby_location_code ,
976 del_tab(l).soldby_country ,
977 del_tab(l).soldby_address_line_1 ,
978 del_tab(l).soldby_address_line_2 ,
979 del_tab(l).soldby_address_line_3 ,
980 del_tab(l).soldby_address_line_4 ,
981 del_tab(l).soldby_county ,
982 del_tab(l).soldby_state ,
983 del_tab(l).soldby_postal_code ,
984 del_tab(l).soldby_city ,
985 del_tab(l).soldby_organization_id ,
986 del_tab(l).soldby_name ,
987 del_tab(l).soldby_contact_name ,
988 del_tab(l).soldby_contact_emailid ,
989 del_tab(l).soldby_contact_phonenumber);
990
991 IF i=1 THEN
992 v_delivery_tab :=wsh_opsm_asn_delivery_tbl(v_delivery);
993 END IF;
994 IF i>1 THEN
995 v_delivery_tab.EXTEND;
996 v_delivery_tab(i):=v_delivery;
997 END IF;
998 i:=i+1;
999 END IF;
1000 END LOOP;
1001 END IF;
1002 END IF;
1003 l_count:=0;
1004 << rslt_par_tab_no_data >>
1005 l_count:=0;
1006 IF l_debug_on THEN
1007 WSH_DEBUG_SV.logmsg(l_module_name,'No Data Found error has occured. Oracle error message is '|| substr(SQLERRM,1,200),WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1008 END IF;
1009 EXCEPTION
1010 WHEN NO_DATA_FOUND THEN
1011 l_count:=0;
1012 IF l_debug_on THEN
1013 WSH_DEBUG_SV.logmsg(l_module_name,'No Data Found error has occured. Oracle error message is '|| substr(SQLERRM,1,200),WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1014 END IF;
1015 WHEN OTHERS THEN
1016 IF l_debug_on THEN
1017 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| substr(SQLERRM,1,200),WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1018 WSH_DEBUG_SV.pop(l_module_name,'WHEN OTHERS');
1019 END IF;
1020 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1021 wsh_util_core.default_handler('WSH_OPSM_ASN_BE_PKG.GET_ASN_DATA',l_module_name);
1022 l_msg_data := SQLERRM ||' at WSH_OPSM_ASN_BE_PKG.GET_ASN_DATA';
1023 x_msg_data := l_msg_data;
1024 END;
1025 END LOOP;
1026 x_msg_data := l_msg_data;
1027 p_out_delivery :=v_delivery_tab;
1028 p_out_container :=v_container_tab;
1029 p_out_items :=v_items_tab;
1030 p_out_itemdetails :=v_itemdetails_tab;
1031 p_out_itemgenealogy :=v_itemgenealogy_tab;
1032 IF l_debug_on THEN
1033 WSH_DEBUG_SV.pop(l_module_name);
1034 END IF;
1035 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1036 DECLARE
1037 --l_end_time DATE;
1038 --l_end_time TIMESTAMP;
1039 l_time_diff INTERVAL DAY TO SECOND;
1040 --l_time_diff_secs NUMBER;
1041 BEGIN
1042 SELECT SYSTIMESTAMP INTO l_end_time FROM dual;
1043 l_end_timec := TO_CHAR(l_end_time, 'DD-MON-YYYY HH24:MI:SSxFF');
1044
1045 FND_LOG.String(
1046 LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
1047 MESSAGE => 'end time= '||TO_CHAR(l_end_time, 'DD-MON-YYYY HH24:MI:SSxFF'),
1048 MODULE => 'opsmperf.end');
1049
1050 SELECT (l_end_time-l_start_time) INTO l_time_diff FROM dual;
1051
1052 l_time_diff_secs := to_number(extract(second from l_time_diff)) +
1053 to_number(extract(minute from l_time_diff)) * 60 +
1054 to_number(extract(hour from l_time_diff)) * 60 * 60 +
1055 to_number(extract(day from l_time_diff)) * 60 * 60* 24;
1056
1057 FND_LOG.String(
1058 LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
1059 MESSAGE => 'time difference= '||l_time_diff_secs,
1060 MODULE => 'opsmperf.end');
1061
1062 END;
1063 END IF;
1064 --dbms_output.put_line('end time= '||TO_CHAR(l_end_time, 'DD-MON-YYYY HH24:MI:SSxFF'));
1065 --dbms_output.put_line('time difference= '||l_time_diff_secs);
1066 EXCEPTION
1067 WHEN OTHERS THEN
1068 IF l_debug_on THEN
1069 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| substr(SQLERRM,1,200),WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1070 WSH_DEBUG_SV.pop(l_module_name,'WHEN OTHERS');
1071 END IF;
1072 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1073 wsh_util_core.default_handler('WSH_OPSM_ASN_BE_PKG.GET_ASN_DATA',l_module_name);
1074 l_msg_data := SQLERRM ||' at WSH_OPSM_ASN_BE_PKG.GET_ASN_DATA';
1075 x_msg_data := l_msg_data;
1076 END get_asn_data;
1077
1078 ---------------------------------------------------------------------------------------
1079 --
1080 -- Procedure: get_asn_data
1081 -- Parameters: p_delivery_id - DELIVERY_ID
1082 -- p_out_delivery- It is wsh_opsm_asn_delivery_tbl Type
1083 -- p_out_container - It is wsh_opsm_asn_containers_tbl Type
1084 -- p_out_items-It is wsh_opsm_asn_items_tbl Type
1085 -- p_out_itemdetails - It is wsh_opsm_asn_itemdetails_tbl Type
1086 -- p_out_itemgenealogy - It is wsh_opsm_asn_itemgenealogy_tbl Type
1087 -- x_return_status-return status of the API
1088 -- x_msg_data -return Error Message of the API
1089
1090 -- Description: This Procedure takes delivery_id has input and
1091 -- filters all the OPSM Integrated data present in
1092 -- that delivery id(delivery id consists of both OPSM and Non OPSM Items).
1093 -- This Procedure also loads the delivery details, OPSM Integrated Items ,
1094 -- its corresponding Items details container, order and genealogy
1095 -- information to their respective table type objects and sends them
1096 -- out in the form Out parameters.
1097 --
1098 ---------------------------------------------------------------------------------------
1099 PROCEDURE get_asn_data ( p_delivery_id IN NUMBER ,
1100 p_out_delivery OUT NOCOPY wsh_opsm_asn_delivery_tbl ,
1101 p_out_container OUT NOCOPY wsh_opsm_asn_containers_tbl ,
1102 p_out_items OUT NOCOPY wsh_opsm_asn_items_tbl ,
1103 p_out_itemdetails OUT NOCOPY wsh_opsm_asn_itemdetails_tbl ,
1104 p_out_itemgenealogy OUT NOCOPY wsh_opsm_asn_itemgenealogy_tbl,
1105 x_return_status OUT NOCOPY VARCHAR2 ,
1106 x_msg_data OUT NOCOPY VARCHAR2)
1107 IS
1108 l_module_name CONSTANT VARCHAR2(100):= 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_ASN_DATA';
1109 l_msg_data VARCHAR2(2000):=NULL;
1110 l_debug_on BOOLEAN;
1111 v_delivery wsh_opsm_asn_delivery_rec;
1112 v_delivery_tab wsh_opsm_asn_delivery_tbl;
1113 v_container wsh_opsm_asn_containers_rec;
1114 v_container_tab wsh_opsm_asn_containers_tbl;
1115 v_items wsh_opsm_asn_items_rec;
1116 v_items_tab wsh_opsm_asn_items_tbl;
1117 v_itemdetails wsh_opsm_asn_itemdetails_rec;
1118 v_itemdetails_tab wsh_opsm_asn_itemdetails_tbl;
1119 v_itemgenealogy wsh_opsm_asn_itemgenealogy_rec;
1120 v_itemgenealogy_tab wsh_opsm_asn_itemgenealogy_tbl;
1121 v_container_level VARCHAR2(200);
1122 v_parent_id NUMBER;
1123 v_opsm_parent_identification VARCHAR2(1000):=NULL;
1124 v_serial_identification VARCHAR2(1000):='0---0';
1125 v_parent_inventory_item_id NUMBER;
1126 v_parent_serial_number VARCHAR2(200):=NULL;
1127 v_parent_lot_number VARCHAR2(200):=NULL;
1128 v_parent_hierarchy_level NUMBER;
1129 v_opsm_flag VARCHAR2(200):=NULL;
1130 v_inventory_item_id NUMBER;
1131 v_serial_number VARCHAR2(200):=NULL;
1132 v_lot_number VARCHAR2(200):=NULL;
1133 v_hierarchy_level NUMBER;
1134 v_organization_id NUMBER;
1135 i NUMBER:=1;
1136 j NUMBER:=1;
1137 k NUMBER:=1;
1138 l NUMBER:=1;
1139 m NUMBER:=1;
1140 n NUMBER:=1;
1141 l_count NUMBER:=0;
1142 l_count1 NUMBER:=0;
1143 l_count2 NUMBER:=0;
1144 l_stmt_num NUMBER := 0;
1145 l_end_time TIMESTAMP;
1146 l_start_time TIMESTAMP;
1147 TYPE delivery_count IS TABLE OF NUMBER INDEX BY VARCHAR2(255);
1148 del_count delivery_count;
1149 TYPE container_count IS TABLE OF NUMBER INDEX BY VARCHAR2(255);
1150 cont_count container_count;
1151 TYPE tab_del IS TABLE OF wsh_opsm_asn_deliveries_v%ROWTYPE INDEX BY BINARY_INTEGER;
1152 del_tab tab_del;
1153
1154 CURSOR cur_item
1155 IS
1156 SELECT *
1157 FROM wsh_opsm_asn_items_v
1158 WHERE delivery_id=p_delivery_id;
1159
1160 CURSOR cur_container(p_delivery_id IN NUMBER,
1161 p_container_instance_id IN NUMBER)
1162 IS
1163 SELECT *
1164 FROM wsh_opsm_asn_containers_v
1165 WHERE delivery_id = p_delivery_id
1166 AND DECODE(p_container_instance_id,NULL,1,container_instance_id) = DECODE(p_container_instance_id,NULL,1,p_container_instance_id)
1167 ORDER BY container_instance_id;
1168
1169 CURSOR cur_parent_container(p_delivery_id IN NUMBER,
1170 p_container_instance_id IN NUMBER)
1171 IS
1172 SELECT *
1173 FROM wsh_opsm_asn_containers_v
1174 WHERE delivery_id = p_delivery_id
1175 AND DECODE(p_container_instance_id,NULL,1,container_instance_id) = DECODE(p_container_instance_id,NULL,1,p_container_instance_id);
1176
1177 CURSOR cur_delivery
1178 IS
1179 SELECT *
1180 FROM wsh_opsm_asn_deliveries_v
1181 WHERE delivery_id = p_delivery_id ;
1182
1183 CURSOR cur_itemdetails(p_delivery_id IN NUMBER,
1184 p_delivery_detail_id IN NUMBER)
1185 IS
1186 SELECT *
1187 FROM wsh_opsm_asn_item_details_v
1188 WHERE delivery_id = p_delivery_id
1189 AND delivery_detail_id = p_delivery_detail_id;
1190
1191 --bug 10357152 - opsmperf begin
1192 /*
1193 CURSOR cur_itemgenealogy(p_delivery_id IN NUMBER,
1194 p_delivery_detail_id IN NUMBER)
1195 IS
1196 SELECT *
1197 FROM wsh_opsm_asn_item_genealogy_v
1198 WHERE delivery_id = p_delivery_id
1199 AND delivery_detail_id = p_delivery_detail_id;
1200 */
1201 rslt_tab_idx wsh_opsm_asn_item_genealogy.t_var_idx_num;
1202 rslt_tab wsh_opsm_asn_item_genealogy.tab_genealogy1;-- := wsh_opsm_asn_item_genealogy.tab_genealogy1();
1203 rslt_par_tab wsh_opsm_asn_item_genealogy.tab_genealogy1;-- := wsh_opsm_asn_item_genealogy.tab_genealogy1();
1204 rslt_tab_tmp wsh_opsm_asn_item_genealogy.tab_genealogy := wsh_opsm_asn_item_genealogy.tab_genealogy();
1205 --rslt_par_tab_tmp wsh_opsm_asn_item_genealogy.tab_genealogy1 := wsh_opsm_asn_item_genealogy.tab_genealogy1();
1206 idxpargen_tmp NUMBER;
1207 idxgen_tmp NUMBER;
1208 idx1 VARCHAR2(255);
1209 idx2 NUMBER := 1;
1210 idxpargen VARCHAR2(255);
1211 idxgen NUMBER;
1212 V_CUR_ITEMGENEALOGY wsh_opsm_asn_item_genealogy_v%ROWTYPE;
1213 l_start_timec VARCHAR2(200) ;
1214 l_end_timec VARCHAR2(200) ;
1215 l_time_diff_secs NUMBER;
1216 l_get_genealogy BOOLEAN := true;
1217 TYPE get_genealogy_called_type IS TABLE OF NUMBER INDEX BY VARCHAR2(255);
1218 get_genealogy_called get_genealogy_called_type;
1219 --bug 10357152 - opsmperf end
1220 BEGIN
1221
1222 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1223 --DECLARE
1224 --l_start_time TIMESTAMP;
1225 BEGIN
1226 SELECT SYSTIMESTAMP INTO l_start_time FROM dual;
1227 l_start_timec := TO_CHAR(l_start_time, 'DD-MON-YYYY HH24:MI:SSxFF');
1228
1229 FND_LOG.String(
1230 LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
1231 MESSAGE => 'start time= '||TO_CHAR(l_start_time, 'DD-MON-YYYY HH24:MI:SSxFF'),
1232 MODULE => 'opsmperf.begin');
1233 END;
1234 END IF;
1235 --dbms_output.put_line('start time= '||l_start_timec);
1236
1237 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1238 IF l_debug_on IS NULL
1239 THEN
1240 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1241 END IF;
1242 IF l_debug_on THEN
1243 WSH_DEBUG_SV.push(l_module_name);
1244 WSH_DEBUG_SV.log(l_module_name,'DELIVERY ID',p_delivery_id);
1245 END IF;
1246 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1247
1248 OPEN cur_delivery;
1249 FETCH cur_delivery BULK COLLECT INTO del_tab;
1250 CLOSE cur_delivery;
1251
1252 FOR v_cur_item IN cur_item
1253 LOOP
1254 BEGIN
1255 --To check wheather Item is OPSM Integrated or not
1256 SELECT 1
1257 INTO l_count
1258 FROM mtl_system_items_b_kfv msik,
1259 mtl_cross_references_vl mcr,
1260 mtl_parameters mp
1261 WHERE mp.organization_id = msik.organization_id
1262 AND msik.inventory_item_id(+) = mcr.inventory_item_id
1263 AND mcr.cross_reference_type = 'OPSM INTEGRATED'
1264 AND mcr.cross_reference = 'YES'
1265 AND mp.master_organization_id = mcr.organization_id
1266 AND mp.opsm_enabled_FLAG = 'Y'
1267 AND msik.lot_control_code = 2
1268 AND msik.organization_id = v_cur_item.organization_id
1269 AND NOT EXISTS (SELECT 'Y'
1270 FROM MTL_CROSS_REFERENCES_VL
1271 WHERE CROSS_REFERENCE_TYPE = 'OPSM INTEGRATED'
1272 AND ORGANIZATION_ID = v_cur_item.organization_id
1273 AND INVENTORY_ITEM_ID = msik.inventory_item_id
1274 AND UPPER(CROSS_REFERENCE) ='NO')
1275 AND msik.inventory_item_id = v_cur_item.item_id;
1276 IF l_debug_on THEN
1277 WSH_DEBUG_SV.log(l_module_name,'OPSM INTEGRATED',l_count);
1278 END IF;
1279 EXCEPTION
1280 WHEN NO_DATA_FOUND THEN
1281 l_count:=0;
1282 IF l_debug_on THEN
1283 WSH_DEBUG_SV.logmsg(l_module_name,'No Data Found error has occured. Oracle error message is '|| substr(SQLERRM,1,200),WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1284 END IF;
1285 END;
1286 IF l_count > 0 THEN
1287 IF NOT del_count.EXISTS(TO_CHAR(v_cur_item.destination_cont_id)
1288 ||'.'||TO_CHAR(v_cur_item.invoice_to_org_id)
1289 ||'.'||TO_CHAR(v_cur_item.invoice_to_contact_id)
1290 ||'.'||TO_CHAR(v_cur_item.soldto_customer_id)
1291 ||'.'||TO_CHAR(v_cur_item.soldto_contact_id)) THEN
1292 del_count(TO_CHAR(v_cur_item.destination_cont_id)
1293 ||'.'||TO_CHAR(v_cur_item.invoice_to_org_id)
1294 ||'.'||TO_CHAR(v_cur_item.invoice_to_contact_id)
1295 ||'.'||TO_CHAR(v_cur_item.soldto_customer_id)
1296 ||'.'||TO_CHAR(v_cur_item.soldto_contact_id)) := 1;
1297 END IF;
1298 IF v_cur_item.container_instance_id IS NOT NULL THEN
1299 FOR v_cur_container IN cur_container(v_cur_item.delivery_id,
1300 v_cur_item.container_instance_id)
1301 LOOP
1302 IF NOT cont_count.EXISTS(TO_CHAR(v_cur_item.container_instance_id)
1303 ||'.'||TO_CHAR(v_cur_item.destination_cont_id)
1304 ||'.'||TO_CHAR(v_cur_item.invoice_to_org_id)
1305 ||'.'||TO_CHAR(v_cur_item.invoice_to_contact_id)
1306 ||'.'||TO_CHAR(v_cur_item.soldto_customer_id)
1307 ||'.'||TO_CHAR(v_cur_item.soldto_contact_id)) THEN
1308 cont_count(TO_CHAR(v_cur_item.container_instance_id)
1309 ||'.'||TO_CHAR(v_cur_item.destination_cont_id)
1310 ||'.'||TO_CHAR(v_cur_item.invoice_to_org_id)
1311 ||'.'||TO_CHAR(v_cur_item.invoice_to_contact_id)
1312 ||'.'||TO_CHAR(v_cur_item.soldto_customer_id)
1313 ||'.'||TO_CHAR(v_cur_item.soldto_contact_id)) := 1;
1314 END IF;
1315 IF v_cur_container.parent_container_instance_id IS NOT NULL THEN
1316 v_parent_id:=v_cur_container.parent_container_instance_id;
1317 WHILE 1=1
1318 LOOP
1319 FOR v_cur_parent_container IN cur_parent_container(v_cur_item.delivery_id,
1320 v_parent_id)
1321 LOOP
1322 IF NOT cont_count.EXISTS(TO_CHAR(v_cur_parent_container.parent_container_instance_id)
1323 ||'.'||TO_CHAR(v_cur_parent_container.container_instance_id)
1324 ||'.'||TO_CHAR(v_cur_item.destination_cont_id)
1325 ||'.'||TO_CHAR(v_cur_item.invoice_to_org_id)
1326 ||'.'||TO_CHAR(v_cur_item.invoice_to_contact_id)
1327 ||'.'||TO_CHAR(v_cur_item.soldto_customer_id)
1328 ||'.'||TO_CHAR(v_cur_item.soldto_contact_id)) THEN
1329 cont_count(TO_CHAR(v_cur_parent_container.parent_container_instance_id)
1330 ||'.'||TO_CHAR(v_cur_parent_container.container_instance_id)
1331 ||'.'||TO_CHAR(v_cur_item.destination_cont_id)
1332 ||'.'||TO_CHAR(v_cur_item.invoice_to_org_id)
1333 ||'.'||TO_CHAR(v_cur_item.invoice_to_contact_id)
1334 ||'.'||TO_CHAR(v_cur_item.soldto_customer_id)
1335 ||'.'||TO_CHAR(v_cur_item.soldto_contact_id)) := 1;
1336 END IF;
1337 v_parent_id:=v_cur_parent_container.parent_container_instance_id;
1338 END LOOP;
1339 IF v_parent_id IS NULL THEN
1340 EXIT;
1341 END IF;
1342 END LOOP;
1343 END IF;
1344 END LOOP;
1345 END IF;
1346 END IF;
1347 END LOOP;
1348
1349 FOR v_cur_item IN cur_item
1350 LOOP
1351 BEGIN
1352 --To check wheather Item is OPSM Integrated or not
1353 SELECT 1
1354 INTO l_count
1355 FROM mtl_system_items_b_kfv msik,
1356 mtl_cross_references_vl mcr,
1357 mtl_parameters mp
1358 WHERE mp.organization_id = msik.organization_id
1359 AND msik.inventory_item_id(+) = mcr.inventory_item_id
1360 AND mcr.cross_reference_type = 'OPSM INTEGRATED'
1361 AND mcr.cross_reference = 'YES'
1362 AND mp.master_organization_id = mcr.organization_id
1363 AND mp.opsm_enabled_FLAG = 'Y'
1364 AND msik.lot_control_code = 2
1365 AND msik.organization_id = v_cur_item.organization_id
1366 AND NOT EXISTS (SELECT 'Y'
1367 FROM MTL_CROSS_REFERENCES_VL
1368 WHERE CROSS_REFERENCE_TYPE = 'OPSM INTEGRATED'
1369 AND ORGANIZATION_ID = v_cur_item.organization_id
1370 AND INVENTORY_ITEM_ID = msik.inventory_item_id
1371 AND UPPER(CROSS_REFERENCE) ='NO')
1372 AND msik.inventory_item_id = v_cur_item.item_id;
1373 IF l_debug_on THEN
1374 WSH_DEBUG_SV.log(l_module_name,'OPSM INTEGRATED',l_count);
1375 END IF;
1376 IF l_count > 0 THEN
1377 --To get Item Information into the object type
1378 v_items:= wsh_opsm_asn_items_rec ( v_cur_item.delivery_id ,
1379 v_cur_item.container_instance_id ,
1380 v_cur_item.item_id ,
1381 v_cur_item.soldto_customer_id ,
1382 v_cur_item.soldto_contact_id ,
1383 v_cur_item.order_header_id ,
1384 v_cur_item.order_line_id ,
1385 v_cur_item.sales_order_number ,
1386 v_cur_item.ordertype ,
1387 v_cur_item.order_line_number ,
1388 v_cur_item.delivery_detail_id ,
1389 v_cur_item.customer_item_id ,
1390 v_cur_item.requested_date ,
1391 v_cur_item.promise_date ,
1392 v_cur_item.ordered_quantity ,
1393 v_cur_item.order_quantity_uom ,
1394 v_cur_item.cancelled_quantity ,
1395 v_cur_item.shipped_quantity ,
1396 v_cur_item.shipping_quantity_uom ,
1397 v_cur_item.hazard_class ,
1398 v_cur_item.organization_id ,
1399 v_cur_item.destination_cont_id ,
1400 v_cur_item.invoice_to_org_id ,
1401 v_cur_item.invoice_to_contact_id ,
1402 v_cur_item.item ,
1403 v_cur_item.description ,
1404 v_cur_item.net_weight ,
1405 v_cur_item.weight_uom ,
1406 v_cur_item.volume ,
1407 v_cur_item.volume_uom_code ,
1408 v_cur_item.packing_instructions ,
1409 v_cur_item.item_description ,
1410 v_cur_item.lot_control_code ,
1411 v_cur_item.serial_number_control_code ,
1412 v_cur_item.opsm_integrated_flag ,
1413 v_cur_item.serial_type);
1414 IF l=1 THEN
1415 v_items_tab :=wsh_opsm_asn_items_tbl(v_items);
1416 END IF;
1417 IF l>1 THEN
1418 v_items_tab.EXTEND;
1419 v_items_tab(l):=v_items;
1420 END IF;
1421 l:=l+1;
1422 FOR v_cur_itemdetails IN cur_itemdetails(v_cur_item.delivery_id,
1423 v_cur_item.delivery_detail_id)
1424 LOOP
1425 --To get ItemDetails Information into the object type
1426 v_itemdetails:= wsh_opsm_asn_itemdetails_rec( v_cur_itemdetails.delivery_id ,
1427 v_cur_itemdetails.organization_id ,
1428 v_cur_itemdetails.delivery_assignment_id ,
1429 v_cur_itemdetails.delivery_detail_id ,
1430 v_cur_itemdetails.container_instance_id ,
1431 v_cur_itemdetails.master_container_item_id ,
1432 v_cur_itemdetails.detail_container_item_id ,
1433 v_cur_itemdetails.load_sequence_number ,
1434 v_cur_itemdetails.lot_number ,
1435 v_cur_itemdetails.parent_lot_number ,
1436 v_cur_itemdetails.shipped_quantity ,
1437 v_cur_itemdetails.shipping_quantity_uom ,
1438 v_cur_itemdetails.genealogy_object_id ,
1439 v_cur_itemdetails.origination_date ,
1440 v_cur_itemdetails.best_by_date ,
1441 v_cur_itemdetails.retest_date ,
1442 v_cur_itemdetails.expiration_date ,
1443 v_cur_itemdetails.from_serial_number ,
1444 v_cur_itemdetails.to_serial_number ,
1445 v_cur_itemdetails.organization_code );
1446
1447 IF m=1 THEN
1448 v_itemdetails_tab :=wsh_opsm_asn_itemdetails_tbl(v_itemdetails);
1449 END IF;
1450 IF m>1 THEN
1451 v_itemdetails_tab.EXTEND;
1452 v_itemdetails_tab(m):=v_itemdetails;
1453 END IF;
1454 m:=m+1;
1455 --bug 10357152 - opsmperf begin
1456 IF NOT get_genealogy_called.EXISTS(TO_CHAR(v_cur_itemdetails.DELIVERY_DETAIL_ID)
1457 || '.' || TO_CHAR(v_cur_itemdetails.GENEALOGY_OBJECT_ID)) THEN
1458 get_genealogy_called(TO_CHAR(v_cur_itemdetails.DELIVERY_DETAIL_ID)
1459 || '.' || TO_CHAR(v_cur_itemdetails.GENEALOGY_OBJECT_ID)) := 1;
1460 wsh_opsm_asn_item_genealogy.get_genealogy(
1461 v_cur_itemdetails.GENEALOGY_OBJECT_ID ,
1462 v_cur_itemdetails.DELIVERY_DETAIL_ID,
1463 v_cur_itemdetails.DELIVERY_ID ,
1464 v_cur_itemdetails.ORGANIZATION_ID,
1465 v_cur_itemdetails.FROM_SERIAL_NUMBER,
1466 v_cur_itemdetails.TO_SERIAL_NUMBER,
1467 rslt_tab_tmp
1468 );
1469 --l_get_genealogy := false;
1470 --idx1 := rslt_tab.count;
1471
1472 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1473 FND_LOG.String(
1474 LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1475 MESSAGE => 'Rows in rslt_tab_tmp',
1476 MODULE => 'wsh.plsql.wsh_opsm_asn_be_pkg.get_asn_data');
1477 END IF;
1478 IF (rslt_tab_tmp.COUNT > 0) THEN
1479 FOR idx IN rslt_tab_tmp.FIRST..rslt_tab_tmp.LAST LOOP
1480 idx1 := TO_CHAR(rslt_tab_tmp(idx).delivery_id) || '.' ||
1481 TO_CHAR(rslt_tab_tmp(idx).delivery_detail_id) || '.' ||
1482 TO_CHAR(rslt_tab_tmp(idx).inventory_item_id) || '.' ||
1483 rslt_tab_tmp(idx).lot_number || '.' ||
1484 rslt_tab_tmp(idx).serial_number || '.' ||
1485 TO_CHAR(rslt_tab_tmp(idx).rlevel);
1486 rslt_tab_idx(idx2) := idx1;
1487 --rslt_tab.EXTEND;
1488 --rslt_par_tab.EXTEND;
1489 idx2 := idx2 + 1;
1490 rslt_tab(idx1).item_nbr := rslt_tab_tmp(idx).item_nbr ;
1491 l_stmt_num := 95.5;
1492 rslt_tab(idx1).primary_uom_code := rslt_tab_tmp(idx).primary_uom_code ;
1493 rslt_tab(idx1).lot_number := rslt_tab_tmp(idx).lot_number ;
1494 rslt_tab(idx1).serial_number := rslt_tab_tmp(idx).serial_number ;
1495 rslt_tab(idx1).organization_id := rslt_tab_tmp(idx).organization_id ;
1496 rslt_tab(idx1).job_name := rslt_tab_tmp(idx).job_name ;
1497 rslt_tab(idx1).inventory_item_id := rslt_tab_tmp(idx).inventory_item_id ;
1498 rslt_tab(idx1).origination_date := rslt_tab_tmp(idx).origination_date ;
1499 rslt_tab(idx1).best_by_date := rslt_tab_tmp(idx).best_by_date ;
1500 rslt_tab(idx1).retest_date := rslt_tab_tmp(idx).retest_date ;
1501 rslt_tab(idx1).expiration_date := rslt_tab_tmp(idx).expiration_date ;
1502 rslt_tab(idx1).organization_code := rslt_tab_tmp(idx).organization_code ;
1503 rslt_tab(idx1).rlevel := rslt_tab_tmp(idx).rlevel ;
1504 rslt_tab(idx1).parent_rlevel := rslt_tab_tmp(idx).parent_rlevel ;
1505 rslt_tab(idx1).lot_control_code := rslt_tab_tmp(idx).lot_control_code ;
1506 rslt_tab(idx1).serial_number_control_code := rslt_tab_tmp(idx).serial_number_control_code;
1507 rslt_tab(idx1).cross_reference := rslt_tab_tmp(idx).cross_reference ;
1508 rslt_tab(idx1).serial_type := rslt_tab_tmp(idx).serial_type ;
1509 rslt_tab(idx1).parent_lot_number := rslt_tab_tmp(idx).parent_lot_number ;
1510 rslt_tab(idx1).parent_serial_number := rslt_tab_tmp(idx).parent_serial_number ;
1511 rslt_tab(idx1).parent_inventory_item_id := rslt_tab_tmp(idx).parent_inventory_item_id ;
1512 rslt_tab(idx1).DELIVERY_DETAIL_ID := rslt_tab_tmp(idx).DELIVERY_DETAIL_ID ;
1513 rslt_tab(idx1).DELIVERY_ID := rslt_tab_tmp(idx).DELIVERY_ID ;
1514 rslt_tab(idx1).GENEALOGY_OBJECT_ID := rslt_tab_tmp(idx).GENEALOGY_OBJECT_ID ;
1515
1516 rslt_par_tab(idx1).item_nbr := rslt_tab_tmp(idx).item_nbr ;
1517 l_stmt_num := 95.7;
1518 rslt_par_tab(idx1).primary_uom_code := rslt_tab_tmp(idx).primary_uom_code ;
1519 rslt_par_tab(idx1).lot_number := rslt_tab_tmp(idx).lot_number ;
1520 rslt_par_tab(idx1).serial_number := rslt_tab_tmp(idx).serial_number ;
1521 rslt_par_tab(idx1).organization_id := rslt_tab_tmp(idx).organization_id ;
1522 rslt_par_tab(idx1).job_name := rslt_tab_tmp(idx).job_name ;
1523 rslt_par_tab(idx1).inventory_item_id := rslt_tab_tmp(idx).inventory_item_id ;
1524 rslt_par_tab(idx1).origination_date := rslt_tab_tmp(idx).origination_date ;
1525 rslt_par_tab(idx1).best_by_date := rslt_tab_tmp(idx).best_by_date ;
1526 rslt_par_tab(idx1).retest_date := rslt_tab_tmp(idx).retest_date ;
1527 rslt_par_tab(idx1).expiration_date := rslt_tab_tmp(idx).expiration_date ;
1528 rslt_par_tab(idx1).organization_code := rslt_tab_tmp(idx).organization_code ;
1529 rslt_par_tab(idx1).rlevel := rslt_tab_tmp(idx).rlevel ;
1530 rslt_par_tab(idx1).parent_rlevel := rslt_tab_tmp(idx).parent_rlevel ;
1531 rslt_par_tab(idx1).lot_control_code := rslt_tab_tmp(idx).lot_control_code ;
1532 rslt_par_tab(idx1).serial_number_control_code := rslt_tab_tmp(idx).serial_number_control_code;
1533 rslt_par_tab(idx1).cross_reference := rslt_tab_tmp(idx).cross_reference ;
1534 rslt_par_tab(idx1).serial_type := rslt_tab_tmp(idx).serial_type ;
1535 rslt_par_tab(idx1).parent_lot_number := rslt_tab_tmp(idx).parent_lot_number ;
1536 rslt_par_tab(idx1).parent_serial_number := rslt_tab_tmp(idx).parent_serial_number ;
1537 rslt_par_tab(idx1).parent_inventory_item_id := rslt_tab_tmp(idx).parent_inventory_item_id ;
1538 rslt_par_tab(idx1).DELIVERY_DETAIL_ID := rslt_tab_tmp(idx).DELIVERY_DETAIL_ID ;
1539 rslt_par_tab(idx1).DELIVERY_ID := rslt_tab_tmp(idx).DELIVERY_ID ;
1540 rslt_par_tab(idx1).GENEALOGY_OBJECT_ID := rslt_tab_tmp(idx).GENEALOGY_OBJECT_ID ;
1541 END LOOP;
1542 END IF;
1543 END IF;
1544 --bug 10357152 - opsmperf end
1545 END LOOP;
1546 --bug 10357152 - opsmperf begin
1547 /*****
1548 FOR v_cur_itemgenealogy IN cur_itemgenealogy(v_cur_item.delivery_id,
1549 v_cur_item.delivery_detail_id)
1550 LOOP
1551 rslt_tab(idx1) := rslt_tab.first;
1552
1553 loop
1554 exit when rslt_tab(idx1) is null;
1555 *****/
1556 IF rslt_tab_idx.COUNT > 0 THEN
1557 FOR idxgen in rslt_tab_idx.FIRST..rslt_tab_idx.LAST LOOP
1558 idx1 := rslt_tab_idx(idxgen);
1559 v_cur_itemgenealogy.ITEM_NUMBER := rslt_tab(idx1).item_nbr;
1560 v_cur_itemgenealogy.ITEM_UOM := rslt_tab(idx1).primary_uom_code ;
1561 v_cur_itemgenealogy.LOT_NUMBER := rslt_tab(idx1).lot_number ;
1562 v_cur_itemgenealogy.SERIAL_NUMBER := rslt_tab(idx1).serial_number ;
1563 v_cur_itemgenealogy.ORGANIZATION_ID := rslt_tab(idx1).organization_id ;
1564 v_cur_itemgenealogy.JOB_NAME := rslt_tab(idx1).job_name ;
1565 v_cur_itemgenealogy.INVENTORY_ITEM_ID := rslt_tab(idx1).inventory_item_id ;
1566 v_cur_itemgenealogy.ORIGINATION_DATE := rslt_tab(idx1).origination_date ;
1567 v_cur_itemgenealogy.BEST_BY_DATE := rslt_tab(idx1).best_by_date ;
1568 v_cur_itemgenealogy.RETEST_DATE := rslt_tab(idx1).retest_date ;
1569 v_cur_itemgenealogy.EXPIRATION_DATE := rslt_tab(idx1).expiration_date ;
1570 v_cur_itemgenealogy.ORGANIZATION_CODE := rslt_tab(idx1).organization_code ;
1571 v_cur_itemgenealogy.HIERARCHY_LEVEL := rslt_tab(idx1).rlevel ;
1572 v_cur_itemgenealogy.PARENT_HIERARCHY_LEVEL := rslt_tab(idx1).parent_rlevel ;
1573 v_cur_itemgenealogy.LOT_CONTROL_CODE := rslt_tab(idx1).lot_control_code ;
1574 v_cur_itemgenealogy.SERIAL_NUMBER_CONTROL_CODE := rslt_tab(idx1).serial_number_control_code;
1575 v_cur_itemgenealogy.OPSM_INTEGRATED_FLAG := rslt_tab(idx1).cross_reference ;
1576 v_cur_itemgenealogy.SERIAL_TYPE := rslt_tab(idx1).serial_type ;
1577 v_cur_itemgenealogy.PARENT_LOT_NUMBER := rslt_tab(idx1).parent_lot_number ;
1578 v_cur_itemgenealogy.PARENT_SERIAL_NUMBER := rslt_tab(idx1).parent_serial_number ;
1579 v_cur_itemgenealogy.PARENT_INVENTORY_ITEM_ID := rslt_tab(idx1).parent_inventory_item_id ;
1580 v_cur_itemgenealogy.DELIVERY_DETAIL_ID := rslt_tab(idx1).DELIVERY_DETAIL_ID ;
1581 v_cur_itemgenealogy.DELIVERY_ID := rslt_tab(idx1).DELIVERY_ID ;
1582 v_cur_itemgenealogy.GENEALOGY_OBJECT_ID := rslt_tab(idx1).GENEALOGY_OBJECT_ID;
1583
1584
1585
1586 BEGIN
1587 --To check wheather Item is OPSM Integrated or not
1588 SELECT 1
1589 INTO l_count1
1590 FROM mtl_system_items_b_kfv msik,
1591 mtl_cross_references_vl mcr,
1592 mtl_parameters mp
1593 WHERE mp.organization_id = msik.organization_id
1594 AND msik.inventory_item_id(+) = mcr.inventory_item_id
1595 AND mcr.cross_reference_type = 'OPSM INTEGRATED'
1596 AND mcr.cross_reference = 'YES'
1597 AND mp.master_organization_id = mcr.organization_id
1598 AND mp.opsm_enabled_FLAG = 'Y'
1599 AND msik.lot_control_code = 2
1600 AND msik.organization_id = v_cur_itemgenealogy.organization_id
1601 AND NOT EXISTS (SELECT 'Y'
1602 FROM MTL_CROSS_REFERENCES_VL
1603 WHERE CROSS_REFERENCE_TYPE = 'OPSM INTEGRATED'
1604 AND ORGANIZATION_ID = v_cur_itemgenealogy.organization_id
1605 AND INVENTORY_ITEM_ID = msik.inventory_item_id
1606 AND UPPER(CROSS_REFERENCE) ='NO')
1607 AND msik.inventory_item_id = v_cur_itemgenealogy.inventory_item_id;
1608
1609 EXCEPTION
1610 WHEN NO_DATA_FOUND THEN
1611 l_count1:=0;
1612 END ;
1613 IF l_count1 > 0 THEN
1614 IF v_cur_itemgenealogy.parent_inventory_item_id=0 THEN
1615 v_opsm_parent_identification:= v_cur_itemgenealogy.parent_inventory_item_id||'-'||
1616 v_cur_itemgenealogy.parent_serial_number||'-'||
1617 v_cur_itemgenealogy.parent_lot_number||'-'||
1618 v_cur_itemgenealogy.parent_hierarchy_level;
1619 END IF;
1620 IF v_cur_itemgenealogy.parent_inventory_item_id||'-'||
1621 v_cur_itemgenealogy.parent_serial_number||'-'||
1622 v_cur_itemgenealogy.parent_lot_number||'-'||
1623 v_cur_itemgenealogy.parent_hierarchy_level <> v_serial_identification THEN
1624
1625 v_opsm_parent_identification := v_cur_itemgenealogy.parent_inventory_item_id||'-'||
1626 v_cur_itemgenealogy.parent_serial_number||'-'||
1627 v_cur_itemgenealogy.parent_lot_number||'-'||
1628 v_cur_itemgenealogy.parent_hierarchy_level;
1629 END IF;
1630
1631 v_itemgenealogy:= wsh_opsm_asn_itemgenealogy_rec( v_cur_itemgenealogy.item_number ,
1632 v_cur_itemgenealogy.item_uom ,
1633 v_cur_itemgenealogy.lot_number ,
1634 v_cur_itemgenealogy.genealogy_object_id ,
1635 v_cur_itemgenealogy.delivery_id ,
1636 v_cur_itemgenealogy.serial_number ,
1637 v_cur_itemgenealogy.delivery_detail_id ,
1638 v_cur_itemgenealogy.job_name ,
1639 v_cur_itemgenealogy.hierarchy_level ,
1640 v_cur_itemgenealogy.parent_hierarchy_level ,
1641 v_cur_itemgenealogy.inventory_item_id ,
1642 v_cur_itemgenealogy.origination_date ,
1643 v_cur_itemgenealogy.best_by_date ,
1644 v_cur_itemgenealogy.retest_date ,
1645 v_cur_itemgenealogy.expiration_date ,
1646 v_cur_itemgenealogy.organization_code ,
1647 v_cur_itemgenealogy.organization_id ,
1648 v_cur_itemgenealogy.lot_control_code ,
1649 v_cur_itemgenealogy.serial_number_control_code ,
1650 v_cur_itemgenealogy.opsm_integrated_flag ,
1651 v_cur_itemgenealogy.serial_type ,
1652 v_cur_itemgenealogy.inventory_item_id||'-'||
1653 v_cur_itemgenealogy.serial_number||'-'||
1654 v_cur_itemgenealogy.lot_number||'-'||
1655 v_cur_itemgenealogy.hierarchy_level ,
1656 v_cur_itemgenealogy.parent_inventory_item_id||'-'||
1657 v_cur_itemgenealogy.parent_serial_number||'-'||
1658 v_cur_itemgenealogy.parent_lot_number||'-'||
1659 v_cur_itemgenealogy.parent_hierarchy_level ,
1660 v_opsm_parent_identification);
1661
1662
1663 IF n=1 THEN
1664 v_itemgenealogy_tab :=wsh_opsm_asn_itemgenealogy_tbl(v_itemgenealogy);
1665 END IF;
1666 IF n>1 THEN
1667 v_itemgenealogy_tab.EXTEND;
1668 v_itemgenealogy_tab(n):=v_itemgenealogy;
1669 END IF;
1670 n:=n+1;
1671 ELSE
1672 v_serial_identification:= v_cur_itemgenealogy.inventory_item_id||'-'||
1673 v_cur_itemgenealogy.serial_number||'-'||
1674 v_cur_itemgenealogy.lot_number||'-'||
1675 v_cur_itemgenealogy.hierarchy_level;
1676
1677 v_parent_inventory_item_id := v_cur_itemgenealogy.parent_inventory_item_id;
1678 v_parent_serial_number := v_cur_itemgenealogy.parent_serial_number;
1679 v_parent_lot_number := v_cur_itemgenealogy.parent_lot_number;
1680 v_parent_hierarchy_level := v_cur_itemgenealogy.parent_hierarchy_level;
1681
1682
1683 WHILE 1=1
1684 LOOP
1685 --bug 10357152 - opsmperf begin
1686 /*
1687 SELECT opsm_integrated_flag ,
1688 parent_inventory_item_id ,
1689 parent_lot_number ,
1690 parent_serial_number ,
1691 parent_hierarchy_level ,
1692 organization_id
1693 INTO v_opsm_flag ,
1694 v_inventory_item_id ,
1695 v_lot_number ,
1696 v_serial_number ,
1697 v_hierarchy_level ,
1698 v_organization_id
1699 FROM wsh_opsm_asn_item_genealogy_v
1700 WHERE delivery_id = v_cur_itemgenealogy.delivery_id
1701 AND delivery_detail_id = v_cur_itemgenealogy.delivery_detail_id
1702 AND inventory_item_id = v_parent_inventory_item_id
1703 AND lot_number = v_parent_lot_number
1704 AND serial_number = v_parent_serial_number
1705 AND hierarchy_level = v_parent_hierarchy_level;
1706 */
1707 v_opsm_flag := null;
1708 v_inventory_item_id := null;
1709 v_lot_number := null;
1710 v_serial_number := null;
1711 v_hierarchy_level := null;
1712 v_organization_id := null;
1713 /*
1714 FOR idxpargen in rslt_par_tab.FIRST..rslt_par_tab.LAST LOOP
1715 ----dbms_output.put_line('Inside FOR idxpargen in 1..rslt_par_tab.COUNT LOOP');
1716 IF ((rslt_par_tab(idxpargen).delivery_id = v_cur_itemgenealogy.delivery_id)
1717 AND (rslt_par_tab(idxpargen).delivery_detail_id = v_cur_itemgenealogy.delivery_detail_id)
1718 AND (rslt_par_tab(idxpargen).inventory_item_id = v_parent_inventory_item_id)
1719 AND (rslt_par_tab(idxpargen).lot_number = v_parent_lot_number)
1720 AND (rslt_par_tab(idxpargen).serial_number = v_parent_serial_number)
1721 AND (rslt_par_tab(idxpargen).rlevel = v_parent_hierarchy_level)
1722 )
1723 THEN
1724 */
1725
1726 idxpargen := TO_CHAR(v_cur_itemgenealogy.delivery_id) || '.' ||
1727 TO_CHAR(v_cur_itemgenealogy.delivery_detail_id) || '.' ||
1728 TO_CHAR(v_parent_inventory_item_id) || '.' ||
1729 v_parent_lot_number || '.' ||
1730 v_parent_serial_number || '.' ||
1731 TO_CHAR(v_parent_hierarchy_level);
1732 IF rslt_par_tab.EXISTS(idxpargen) THEN
1733 v_opsm_flag := rslt_par_tab(idxpargen).cross_reference;
1734 v_inventory_item_id := rslt_par_tab(idxpargen).parent_inventory_item_id;
1735 v_lot_number := rslt_par_tab(idxpargen).parent_lot_number;
1736 v_serial_number := rslt_par_tab(idxpargen).parent_serial_number;
1737 v_hierarchy_level := rslt_par_tab(idxpargen).parent_rlevel;
1738 v_organization_id := rslt_par_tab(idxpargen).organization_id;
1739 END IF;
1740 --END LOOP;
1741 IF v_inventory_item_id IS NULL THEN
1742 GOTO rslt_par_tab_no_data;
1743 END IF;
1744 --bug 10357152 - opsmperf end
1745
1746 --To check wheather Item is OPSM Integrated or not
1747 IF v_inventory_item_id=0 THEN
1748 l_count2:=1;
1749 ELSE
1750 BEGIN
1751 SELECT 1
1752 INTO l_count2
1753 FROM mtl_system_items_b_kfv msik,
1754 mtl_cross_references_vl mcr,
1755 mtl_parameters mp
1756 WHERE mp.organization_id = msik.organization_id
1757 AND msik.inventory_item_id(+) = mcr.inventory_item_id
1758 AND mcr.cross_reference_type = 'OPSM INTEGRATED'
1759 AND mcr.cross_reference = 'YES'
1760 AND mp.master_organization_id = mcr.organization_id
1761 AND mp.opsm_enabled_FLAG = 'Y'
1762 AND msik.lot_control_code = 2
1763 AND msik.organization_id = v_organization_id
1764 AND NOT EXISTS (SELECT 'Y'
1765 FROM MTL_CROSS_REFERENCES_VL
1766 WHERE CROSS_REFERENCE_TYPE = 'OPSM INTEGRATED'
1767 AND ORGANIZATION_ID = v_organization_id
1768 AND INVENTORY_ITEM_ID = msik.inventory_item_id
1769 AND UPPER(CROSS_REFERENCE) ='NO')
1770 AND msik.inventory_item_id = v_parent_inventory_item_id;
1771
1772 EXCEPTION
1773 WHEN NO_DATA_FOUND THEN
1774 l_count2:=0;
1775 END ;
1776 END IF;
1777 IF l_count2 > 0 THEN
1778 v_opsm_parent_identification := v_parent_inventory_item_id||'-'||
1779 v_parent_serial_number||'-'||
1780 v_parent_lot_number||'-'||
1781 v_parent_hierarchy_level;
1782 EXIT;
1783 ELSE
1784 v_parent_inventory_item_id := v_inventory_item_id;
1785 v_parent_serial_number := v_serial_number;
1786 v_parent_lot_number := v_lot_number;
1787 v_parent_hierarchy_level := v_hierarchy_level;
1788 END IF;
1789 END LOOP;
1790 END IF;
1791
1792 END LOOP;
1793 END IF;
1794
1795 IF v_cur_item.container_instance_id IS NOT NULL THEN
1796 IF cont_count.EXISTS(TO_CHAR(v_cur_item.container_instance_id)
1797 ||'.'||TO_CHAR(v_cur_item.destination_cont_id)
1798 ||'.'||TO_CHAR(v_cur_item.invoice_to_org_id)
1799 ||'.'||TO_CHAR(v_cur_item.invoice_to_contact_id)
1800 ||'.'||TO_CHAR(v_cur_item.soldto_customer_id)
1801 ||'.'||TO_CHAR(v_cur_item.soldto_contact_id)) THEN
1802 cont_count.DELETE(TO_CHAR(v_cur_item.container_instance_id)
1803 ||'.'||TO_CHAR(v_cur_item.destination_cont_id)
1804 ||'.'||TO_CHAR(v_cur_item.invoice_to_org_id)
1805 ||'.'||TO_CHAR(v_cur_item.invoice_to_contact_id)
1806 ||'.'||TO_CHAR(v_cur_item.soldto_customer_id)
1807 ||'.'||TO_CHAR(v_cur_item.soldto_contact_id));
1808 FOR v_cur_container IN cur_container(v_cur_item.delivery_id,
1809 v_cur_item.container_instance_id)
1810 LOOP
1811 --To get Container Information into the object type
1812 v_container_level:=NULL;
1813 IF v_cur_container.parent_container_instance_id IS NOT NULL THEN
1814 v_parent_id :=v_cur_container.parent_container_instance_id;
1815 WHILE 1=1
1816 LOOP
1817 v_container_level:='PARENT';
1818 FOR v_cur_parent_container IN cur_parent_container(v_cur_item.delivery_id,
1819 v_parent_id)
1820 LOOP
1821 IF cont_count.EXISTS(TO_CHAR(v_cur_parent_container.parent_container_instance_id)
1822 ||'.'||TO_CHAR(v_cur_parent_container.container_instance_id)
1823 ||'.'||TO_CHAR(v_cur_item.destination_cont_id)
1824 ||'.'||TO_CHAR(v_cur_item.invoice_to_org_id)
1825 ||'.'||TO_CHAR(v_cur_item.invoice_to_contact_id)
1826 ||'.'||TO_CHAR(v_cur_item.soldto_customer_id)
1827 ||'.'||TO_CHAR(v_cur_item.soldto_contact_id)) THEN
1828 cont_count.DELETE(TO_CHAR(v_cur_parent_container.parent_container_instance_id)
1829 ||'.'||TO_CHAR(v_cur_parent_container.container_instance_id)
1830 ||'.'||TO_CHAR(v_cur_item.destination_cont_id)
1831 ||'.'||TO_CHAR(v_cur_item.invoice_to_org_id)
1832 ||'.'||TO_CHAR(v_cur_item.invoice_to_contact_id)
1833 ||'.'||TO_CHAR(v_cur_item.soldto_customer_id)
1834 ||'.'||TO_CHAR(v_cur_item.soldto_contact_id)) ;
1835 v_container:=wsh_opsm_asn_containers_rec( v_cur_parent_container.container_instance_id ,
1836 v_cur_parent_container.lpn ,
1837 v_cur_parent_container.container_item_id ,
1838 v_cur_parent_container.master_serial_number ,
1839 v_cur_parent_container.container_serial_number ,
1840 v_cur_parent_container.lot_number ,
1841 v_cur_parent_container.parent_container_instance_id ,
1842 v_cur_parent_container.parent_lpn ,
1843 v_cur_parent_container.container_type ,
1844 v_cur_parent_container.container_gross_weight ,
1845 v_cur_parent_container.container_gross_weight_uom ,
1846 v_cur_parent_container.container_volume ,
1847 v_cur_parent_container.container_volume_uom ,
1848 v_cur_parent_container.container_net_weight ,
1849 v_cur_parent_container.container_fill_percent ,
1850 v_cur_parent_container.container_maximum_volume ,
1851 v_cur_parent_container.container_maximum_load_weight ,
1852 v_cur_parent_container.container_minimum_fill_percent,
1853 v_cur_parent_container.container_tare_weight ,
1854 v_cur_parent_container.container_seal_code ,
1855 v_cur_parent_container.container_tracking_number ,
1856 v_cur_parent_container.item_description ,
1857 v_cur_parent_container.delivery_id ,
1858 v_cur_parent_container.lpn_type ,
1859 v_cur_item.soldto_customer_id ,
1860 v_cur_item.soldto_contact_id ,
1861 v_cur_item.destination_cont_id ,
1862 v_cur_item.invoice_to_org_id ,
1863 v_cur_item.invoice_to_contact_id ,
1864 v_container_level);
1865
1866 IF j=1 THEN
1867 v_container_tab :=wsh_opsm_asn_containers_tbl(v_container);
1868 END IF;
1869 IF j>1 THEN
1870 v_container_tab.EXTEND;
1871 v_container_tab(j):=v_container;
1872 END IF;
1873 j:=j+1;
1874 END IF;
1875 v_parent_id:=v_cur_parent_container.parent_container_instance_id;
1876 END LOOP;
1877 IF v_parent_id IS NULL THEN
1878 EXIT;
1879 END IF;
1880 END LOOP;
1881 v_container_level:='CHILD';
1882 END IF;
1883 v_container:=wsh_opsm_asn_containers_rec( v_cur_container.container_instance_id ,
1884 v_cur_container.lpn ,
1885 v_cur_container.container_item_id ,
1886 v_cur_container.master_serial_number ,
1887 v_cur_container.container_serial_number ,
1888 v_cur_container.lot_number ,
1889 v_cur_container.parent_container_instance_id ,
1890 v_cur_container.parent_lpn ,
1891 v_cur_container.container_type ,
1892 v_cur_container.container_gross_weight ,
1893 v_cur_container.container_gross_weight_uom ,
1894 v_cur_container.container_volume ,
1895 v_cur_container.container_volume_uom ,
1896 v_cur_container.container_net_weight ,
1897 v_cur_container.container_fill_percent ,
1898 v_cur_container.container_maximum_volume ,
1899 v_cur_container.container_maximum_load_weight ,
1900 v_cur_container.container_minimum_fill_percent,
1901 v_cur_container.container_tare_weight ,
1902 v_cur_container.container_seal_code ,
1903 v_cur_container.container_tracking_number ,
1904 v_cur_container.item_description ,
1905 v_cur_container.delivery_id ,
1906 v_cur_container.lpn_type ,
1907 v_cur_item.soldto_customer_id ,
1908 v_cur_item.soldto_contact_id ,
1909 v_cur_item.destination_cont_id ,
1910 v_cur_item.invoice_to_org_id ,
1911 v_cur_item.invoice_to_contact_id ,
1912 v_container_level);
1913
1914 IF j=1 THEN
1915 v_container_tab :=wsh_opsm_asn_containers_tbl(v_container);
1916 END IF;
1917 IF j>1 THEN
1918 v_container_tab.EXTEND;
1919 v_container_tab(j):=v_container;
1920 END IF;
1921 j:=j+1;
1922 END LOOP;
1923 END IF;
1924 END IF;
1925 IF del_count.exists(TO_CHAR(v_cur_item.destination_cont_id)
1926 ||'.'||TO_CHAR(v_cur_item.invoice_to_org_id)
1927 ||'.'||TO_CHAR(v_cur_item.invoice_to_contact_id)
1928 ||'.'||TO_CHAR(v_cur_item.soldto_customer_id)
1929 ||'.'||TO_CHAR(v_cur_item.soldto_contact_id)) THEN
1930 del_count.DELETE(TO_CHAR(v_cur_item.destination_cont_id)
1931 ||'.'||TO_CHAR(v_cur_item.invoice_to_org_id)
1932 ||'.'||TO_CHAR(v_cur_item.invoice_to_contact_id)
1933 ||'.'||TO_CHAR(v_cur_item.soldto_customer_id)
1934 ||'.'||TO_CHAR(v_cur_item.soldto_contact_id));
1935
1936 FOR l IN 1..del_tab.COUNT
1937 LOOP
1938
1939 IF NVL(del_tab(l).destination_cont_id,-999) = NVL( v_cur_item.destination_cont_id,-999) AND
1940 NVL(del_tab(l).invoice_to_org_id,-999) = NVL(v_cur_item.invoice_to_org_id,-999) AND
1941 NVL(del_tab(l).invoice_to_contact_id,-999) = NVL(v_cur_item.invoice_to_contact_id,-999) AND
1942 NVL(del_tab(l).soldto_customer_id,-999) = NVL(v_cur_item.soldto_customer_id,-999) AND
1943 NVL(del_tab(l).soldto_contact_id,-999) = NVL(v_cur_item.soldto_contact_id,-999) THEN
1944 --To get delivery Information into the object type
1945 v_delivery:= wsh_opsm_asn_delivery_rec( del_tab(l).transaction_date ,
1946 del_tab(l).document_code ,
1947 del_tab(l).organization_id ,
1948 del_tab(l).delivery_id ,
1949 del_tab(l).delivery_name ,
1950 del_tab(l).organization_code ,
1951 del_tab(l).warehouse_location_id ,
1952 del_tab(l).warehouse_code ,
1953 del_tab(l).warehouse_name ,
1954 del_tab(l).warehouse_address1 ,
1955 del_tab(l).warehouse_address2 ,
1956 del_tab(l).warehouse_address3 ,
1957 del_tab(l).warehouse_address4 ,
1958 del_tab(l).warehouse_city ,
1959 del_tab(l).warehouse_postal_code ,
1960 del_tab(l).warehouse_country ,
1961 del_tab(l).warehouse_county ,
1962 del_tab(l).warehouse_state ,
1963 del_tab(l).warehouse_contact_name ,
1964 del_tab(l).warehouse_contact_emailid ,
1965 del_tab(l).warehouse_contact_phonenumber ,
1966 del_tab(l).destination_code ,
1967 del_tab(l).destination_name ,
1968 del_tab(l).destination_address1 ,
1969 del_tab(l).destination_address2 ,
1970 del_tab(l).destination_address3 ,
1971 del_tab(l).destination_address4 ,
1972 del_tab(l).destination_city ,
1973 del_tab(l).destination_postal_code ,
1974 del_tab(l).destination_country ,
1975 del_tab(l).destination_state ,
1976 del_tab(l).destination_province ,
1977 del_tab(l).destination_county ,
1978 del_tab(l).destination_cont_id ,
1979 del_tab(l).destination_cont_area_code ,
1980 del_tab(l).destination_cont_phone_number ,
1981 del_tab(l).destination_cont_name ,
1982 del_tab(l).destination_cont_email ,
1983 del_tab(l).destination_cont_country_code ,
1984 del_tab(l).destination_cont_phone_extn ,
1985 del_tab(l).bill_of_lading ,
1986 del_tab(l).freight_terms_code ,
1987 del_tab(l).fob_point_code ,
1988 del_tab(l).initial_pickup_date ,
1989 del_tab(l).port_of_loading ,
1990 del_tab(l).change_of_ownership ,
1991 del_tab(l).dock_code ,
1992 del_tab(l).status_code ,
1993 del_tab(l).created_by ,
1994 del_tab(l).creation_date ,
1995 del_tab(l).last_updated_by ,
1996 del_tab(l).last_update_date ,
1997 del_tab(l).invoice_to_org_id ,
1998 del_tab(l).invoice_to_contact_id ,
1999 del_tab(l).invoice_name ,
2000 del_tab(l).invoice_address1 ,
2001 del_tab(l).invoice_address2 ,
2002 del_tab(l).invoice_address3 ,
2003 del_tab(l).invoice_address4 ,
2004 del_tab(l).invoice_city ,
2005 del_tab(l).invoice_postal_code ,
2006 del_tab(l).invoice_country_int ,
2007 del_tab(l).invoice_state_int ,
2008 del_tab(l).invoice_province_int ,
2009 del_tab(l).invoice_county ,
2010 del_tab(l).invoice_cont_name ,
2011 del_tab(l).invoice_cont_job_title ,
2012 del_tab(l).invoice_cont_email_address ,
2013 del_tab(l).invoice_cont_country_code ,
2014 del_tab(l).invoice_cont_area_code ,
2015 del_tab(l).invoice_cont_phone_number ,
2016 del_tab(l).invoice_cont_phone_extn ,
2017 del_tab(l).soldto_customer_id ,
2018 del_tab(l).soldto_contact_id ,
2019 del_tab(l).soldto_address_id ,
2020 del_tab(l).soldto_name ,
2021 del_tab(l).soldto_address1 ,
2022 del_tab(l).soldto_address2 ,
2023 del_tab(l).soldto_address3 ,
2024 del_tab(l).soldto_address4 ,
2025 del_tab(l).soldto_city ,
2026 del_tab(l).soldto_postal_code ,
2027 del_tab(l).soldto_country ,
2028 del_tab(l).soldto_state ,
2029 del_tab(l).soldto_province ,
2030 del_tab(l).soldto_county ,
2031 del_tab(l).soldto_cont_name ,
2032 del_tab(l).soldto_cont_job_title ,
2033 del_tab(l).soldto_cont_email ,
2034 del_tab(l).soldto_cont_country_code ,
2035 del_tab(l).soldto_cont_area_code ,
2036 del_tab(l).soldto_cont_phone_number ,
2037 del_tab(l).soldto_cont_phone_extn ,
2038 del_tab(l).soldby_location_code ,
2039 del_tab(l).soldby_country ,
2040 del_tab(l).soldby_address_line_1 ,
2041 del_tab(l).soldby_address_line_2 ,
2042 del_tab(l).soldby_address_line_3 ,
2043 del_tab(l).soldby_address_line_4 ,
2044 del_tab(l).soldby_county ,
2045 del_tab(l).soldby_state ,
2046 del_tab(l).soldby_postal_code ,
2047 del_tab(l).soldby_city ,
2048 del_tab(l).soldby_organization_id ,
2049 del_tab(l).soldby_name ,
2050 del_tab(l).soldby_contact_name ,
2051 del_tab(l).soldby_contact_emailid ,
2052 del_tab(l).soldby_contact_phonenumber);
2053
2054 IF i=1 THEN
2055 v_delivery_tab :=wsh_opsm_asn_delivery_tbl(v_delivery);
2056 END IF;
2057 IF i>1 THEN
2058 v_delivery_tab.EXTEND;
2059 v_delivery_tab(i):=v_delivery;
2060 END IF;
2061 i:=i+1;
2062 END IF;
2063 END LOOP;
2064 END IF;
2065 END IF;
2066 l_count:=0;
2067 << rslt_par_tab_no_data >>
2068 l_count:=0;
2069 IF l_debug_on THEN
2070 WSH_DEBUG_SV.logmsg(l_module_name,'No Data Found error has occured. Oracle error message is '|| substr(SQLERRM,1,200),WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2071 END IF;
2072 EXCEPTION
2073 WHEN NO_DATA_FOUND THEN
2074 l_count:=0;
2075 IF l_debug_on THEN
2076 WSH_DEBUG_SV.logmsg(l_module_name,'No Data Found error has occured. Oracle error message is '|| substr(SQLERRM,1,200),WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2077 END IF;
2078 WHEN OTHERS THEN
2079 IF l_debug_on THEN
2080 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| substr(SQLERRM,1,200),WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2081 WSH_DEBUG_SV.pop(l_module_name,'WHEN OTHERS');
2082 END IF;
2083 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2084 wsh_util_core.default_handler('WSH_OPSM_ASN_BE_PKG.GET_ASN_DATA',l_module_name);
2085 l_msg_data := SQLERRM ||' at WSH_OPSM_ASN_BE_PKG.GET_ASN_DATA';
2086 x_msg_data := l_msg_data;
2087 END;
2088 END LOOP;
2089 x_msg_data := l_msg_data;
2090 p_out_delivery :=v_delivery_tab;
2091 p_out_container :=v_container_tab;
2092 p_out_items :=v_items_tab;
2093 p_out_itemdetails :=v_itemdetails_tab;
2094 p_out_itemgenealogy :=v_itemgenealogy_tab;
2095 IF l_debug_on THEN
2096 WSH_DEBUG_SV.pop(l_module_name);
2097 END IF;
2098 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2099 DECLARE
2100 --l_end_time DATE;
2101 --l_end_time TIMESTAMP;
2102 l_time_diff INTERVAL DAY TO SECOND;
2103 --l_time_diff_secs NUMBER;
2104 BEGIN
2105 SELECT SYSTIMESTAMP INTO l_end_time FROM dual;
2106 l_end_timec := TO_CHAR(l_end_time, 'DD-MON-YYYY HH24:MI:SSxFF');
2107
2108 FND_LOG.String(
2109 LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
2110 MESSAGE => 'end time= '||TO_CHAR(l_end_time, 'DD-MON-YYYY HH24:MI:SSxFF'),
2111 MODULE => 'opsmperf.end');
2112
2113 SELECT (l_end_time-l_start_time) INTO l_time_diff FROM dual;
2114
2115 l_time_diff_secs := to_number(extract(second from l_time_diff)) +
2116 to_number(extract(minute from l_time_diff)) * 60 +
2117 to_number(extract(hour from l_time_diff)) * 60 * 60 +
2118 to_number(extract(day from l_time_diff)) * 60 * 60* 24;
2119
2120 FND_LOG.String(
2121 LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
2122 MESSAGE => 'time difference= '||l_time_diff_secs,
2123 MODULE => 'opsmperf.end');
2124
2125 END;
2126 END IF;
2127 --dbms_output.put_line('end time= '||TO_CHAR(l_end_time, 'DD-MON-YYYY HH24:MI:SSxFF'));
2128 --dbms_output.put_line('time difference= '||l_time_diff_secs);
2129 EXCEPTION
2130 WHEN OTHERS THEN
2131 IF l_debug_on THEN
2132 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| substr(SQLERRM,1,200),WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2133 WSH_DEBUG_SV.pop(l_module_name,'WHEN OTHERS');
2134 END IF;
2135 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2136 wsh_util_core.default_handler('WSH_OPSM_ASN_BE_PKG.GET_ASN_DATA',l_module_name);
2137 l_msg_data := SQLERRM ||' at WSH_OPSM_ASN_BE_PKG.GET_ASN_DATA';
2138 x_msg_data := l_msg_data;
2139 END get_asn_data;
2140
2141 ---------------------------------------------------------------------------------------
2142 -- Procedure: submit_request
2143 -- Parameters: p_delivery_id - DELIVERY_ID
2144 -- p_opsmexist- It returns 'Y' or 'N'
2145 -- x_return_status-return status of the API
2146 -- x_msg_data -return Error Message of the API
2147 --
2148 -- Description: This Function Returns 'Y' or 'N'after checking
2149 -- the condition that the Input delivery id has at least one OPSM
2150 -- enabled item or not.
2151 ---------------------------------------------------------------------------------------
2152
2153 PROCEDURE submit_request( p_delivery_id IN VARCHAR2 ,
2154 p_opsmexist OUT NOCOPY VARCHAR2 ,
2155 x_return_status OUT NOCOPY VARCHAR2 ,
2156 x_msg_data OUT NOCOPY VARCHAR2)
2157 IS
2158 l_delivery_id NUMBER:=to_number(p_delivery_id);
2159 l_count NUMBER:=0;
2160 l_count1 NUMBER:=0;
2161 l_module_name CONSTANT VARCHAR2(100):= 'wsh.plsql.' || G_PKG_NAME || '.' || 'SUBMIT_REQUEST';
2162 l_msg_data VARCHAR2(2000):=NULL;
2163 l_debug_on BOOLEAN;
2164 CURSOR c_opsm_items
2165 IS
2166 SELECT item_id ,organization_id
2167 FROM wsh_opsm_asn_items_v
2168 WHERE delivery_id=(l_delivery_id);
2169
2170 BEGIN
2171 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2172 IF l_debug_on IS NULL
2173 THEN
2174 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2175 END IF;
2176 IF l_debug_on THEN
2177 WSH_DEBUG_SV.push(l_module_name);
2178 WSH_DEBUG_SV.log(l_module_name,'DELIVERY ID',p_delivery_id);
2179 END IF;
2180 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2181 FOR v_opsm_items IN c_opsm_items LOOP
2182 --To check wheather Item is OPSM Integrated or not
2183 BEGIN
2184 SELECT 1
2185 INTO l_count
2186 FROM mtl_system_items_b_kfv msik,
2187 mtl_cross_references_vl mcr,
2188 mtl_parameters mp
2189 WHERE mp.organization_id = msik.organization_id
2190 AND msik.inventory_item_id(+) = mcr.inventory_item_id
2191 AND mcr.cross_reference_type = 'OPSM INTEGRATED'
2192 AND mcr.cross_reference = 'YES'
2193 AND mp.master_organization_id = mcr.organization_id
2194 AND mp.opsm_enabled_FLAG = 'Y'
2195 AND msik.lot_control_code = 2
2196 AND msik.organization_id = v_opsm_items.organization_id
2197 AND NOT EXISTS (SELECT 'Y'
2198 FROM MTL_CROSS_REFERENCES_VL
2199 WHERE CROSS_REFERENCE_TYPE = 'OPSM INTEGRATED'
2200 AND ORGANIZATION_ID = v_opsm_items.organization_id
2201 AND INVENTORY_ITEM_ID = msik.inventory_item_id
2202 AND UPPER(CROSS_REFERENCE) ='NO')
2203 AND msik.inventory_item_id = v_opsm_items.item_id;
2204 l_count1:=l_count1+l_count;
2205 IF l_debug_on THEN
2206 WSH_DEBUG_SV.log(l_module_name,'OPSM INTEGRATED SINLGE',l_count);
2207 WSH_DEBUG_SV.log(l_module_name,'OPSM INTEGRATED COUNT',l_count1);
2208 END IF;
2209 IF l_count1 > 0 THEN
2210 EXIT;
2211 END IF;
2212 EXCEPTION
2213 WHEN NO_DATA_FOUND THEN
2214 l_count:=0;
2215 p_opsmexist:='N';
2216 IF l_debug_on THEN
2217 WSH_DEBUG_SV.logmsg(l_module_name,'No Data Found error has occured. Oracle error message is '|| substr(SQLERRM,1,200),WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2218 END IF;
2219 END;
2220 END LOOP;
2221 IF l_count1 > 0 THEN
2222 p_opsmexist:='Y';
2223 IF l_debug_on THEN
2224 WSH_DEBUG_SV.log(l_module_name,'OPSM INTEGRATED EXIST',p_opsmexist);
2225 END IF;
2226 ELSE
2227 p_opsmexist:='N';
2228 IF l_debug_on THEN
2229 WSH_DEBUG_SV.log(l_module_name,'OPSM INTEGRATED EXIST',p_opsmexist);
2230 END IF;
2231 END IF;
2232 x_msg_data := l_msg_data;
2233 IF l_debug_on THEN
2234 WSH_DEBUG_SV.pop(l_module_name);
2235 END IF;
2236 EXCEPTION
2237 WHEN OTHERS THEN
2238 l_count:=0;
2239 p_opsmexist:='N';
2240 IF l_debug_on THEN
2241 WSH_DEBUG_SV.log(l_module_name,'OPSM INTEGRATED EXIST',p_opsmexist);
2242 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| substr(SQLERRM,1,200),WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2243 WSH_DEBUG_SV.pop(l_module_name,'WHEN OTHERS');
2244 END IF;
2245 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2246 wsh_util_core.default_handler('WSH_OPSM_ASN_BE_PKG.SUBMIT_REQUEST',l_module_name);
2247 l_msg_data := SQLERRM ||' at WSH_OPSM_ASN_BE_PKG.SUBMIT_REQUEST';
2248 x_msg_data := l_msg_data;
2249
2250 END submit_request;
2251 END wsh_opsm_asn_be_pkg;