1 PACKAGE BODY INV_RCV_TXN_MATCH AS
2 /* $Header: INVRCVMB.pls 120.17.12020000.5 2013/02/04 02:22:05 jianpyu ship $*/
3
4 x_interface_type varchar2(25) := 'RCV-856';
5 x_dummy_flag varchar2(1) := 'Y';
6
7 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_RCV_TXN_MATCH';
8
9 PROCEDURE print_debug(p_err_msg VARCHAR2,
10 p_level NUMBER DEFAULT 4)
11 IS
12 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
13 BEGIN
14
15 IF (l_debug = 1) THEN
16 inv_mobile_helper_functions.tracelog
17 (p_err_msg => p_err_msg,
18 p_module => 'INV_RCV_TXN_MATCH',
19 p_level => p_level);
20 END IF;
21
22 -- dbms_output.put_line(p_err_msg);
23 END print_debug;
24
25
26 PROCEDURE matching_logic
27 (
28 x_return_status OUT NOCOPY VARCHAR2
29 ,x_msg_count OUT NOCOPY NUMBER
30 ,x_msg_data OUT NOCOPY VARCHAR2
31 ,x_cascaded_table IN OUT NOCOPY INV_RCV_COMMON_APIS.cascaded_trans_tab_type
32 ,n IN OUT NOCOPY BINARY_INTEGER
33 ,temp_cascaded_table IN OUT NOCOPY INV_RCV_COMMON_APIS.cascaded_trans_tab_type
34 ,p_receipt_num IN VARCHAR2
35 ,p_match_type IN VARCHAR2
36 ,p_lpn_id IN NUMBER)
37 IS
38 l_po_in_cascaded_table INV_RCV_COMMON_APIS.cascaded_trans_tab_type;
39 l_po_out_cascaded_table INV_RCV_COMMON_APIS.cascaded_trans_tab_type;
40
41 l_shipment_line_id NUMBER;
42 l_category_id NUMBER;
43 l_unit_of_measure VARCHAR2(30);
44 l_item_description VARCHAR2(300);
45 l_item_id NUMBER;
46 l_receipt_source_code_t VARCHAR2(30);
47 l_to_organization_id NUMBER;
48 l_rcv_transaction_id NUMBER;
49 l_oe_order_line_id NUMBER;
50 l_progress VARCHAR2(5) := '10';
51
52 CURSOR asn_direct_lines
53 ( v_shipment_header_id NUMBER
54 ,v_shipment_line_id NUMBER
55 ,v_item_id NUMBER
56 ,v_po_header_id NUMBER
57 ,v_lpn_id NUMBER
58 ,v_item_desc VARCHAR2
59 ,v_project_id NUMBER
60 ,v_task_id NUMBER
61 ,v_wms_po_j_or_higher VARCHAR2
62 ,v_uom_code VARCHAR2) --14776842
63 IS
64 SELECT * FROM ( -- 14776842
65 SELECT rsl.shipment_line_id
66 , rsl.unit_of_measure
67 , rsl.item_id
68 , 'VENDOR' receipt_source_code -- 14776842
69 , rsl.to_organization_id
70 , 0 rcv_transaction_id -- 14776842
71 , To_number(NULL) oe_order_line_id -- 14776842
72 , poll.po_header_id
73 , poll.po_line_id
74 , poll.line_location_id
75 , pod.po_distribution_id
76 , rsl.item_description
77 , Nvl(rsh.expected_receipt_date, Sysdate) expected_receipt_date
78 FROM rcv_shipment_lines rsl
79 , rcv_shipment_headers rsh
80 , po_line_locations poll
81 , po_distributions pod
82 , (SELECT DISTINCT source_line_id
83 FROM wms_lpn_contents
84 WHERE parent_lpn_id = v_lpn_id) wlc
85 WHERE rsl.shipment_header_id = rsh.shipment_header_id
86 AND rsl.shipment_header_id = v_shipment_header_id
87 AND rsl.shipment_line_id = Nvl(v_shipment_line_id, rsl.shipment_line_id)
88 AND poll.po_header_id = Nvl(v_po_header_id, poll.po_header_id)
89 -- Bug 3213241
90 AND poll.po_line_id = wlc.source_line_id (+)
91 AND v_wms_po_j_or_higher = 'N'
92 AND pod.line_location_id = poll.line_location_id
93 AND (rsl.item_id = v_item_id
94 OR (v_item_id IS NULL
95 AND rsl.item_id IS NULL
96 AND rsl.item_description = v_item_desc))
97 AND poll.approved_flag = 'Y'
98 AND Nvl(poll.cancel_flag, 'N') = 'N'
99 AND Nvl(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
100 AND poll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
101 AND poll.line_location_id = rsl.po_line_location_id
102 AND rsh.asn_type IN ('ASN', 'ASBN')
103 -- bug 2752051
104 AND rsl.shipment_line_status_code IN ('EXPECTED','PARTIALLY RECEIVED') --<> 'CANCELLED'
105 AND ( v_project_id is null or
106 (v_project_id = -9999 and pod.project_id is null) or -- bug2669021
107 pod.project_id = v_project_id
108 )
109 and (v_task_id is null or pod.task_id = v_task_id)
110 UNION ALL
111 SELECT rsl.shipment_line_id
112 , rsl.unit_of_measure
113 , rsl.item_id
114 , 'VENDOR' receipt_source_code -- 14776842
115 , rsl.to_organization_id
116 , 0 -- rcv_transaction_id rcv_transaction_id -- 14776842
117 , To_number(NULL) oe_order_line_id -- 14776842
118 , poll.po_header_id
119 , poll.po_line_id
120 , poll.line_location_id
121 , pod.po_distribution_id
122 , rsl.item_description
123 , Nvl(rsh.expected_receipt_date, Sysdate) expected_receipt_date
124 FROM rcv_shipment_lines rsl
125 , rcv_shipment_headers rsh
126 , po_line_locations poll
127 , po_distributions pod
128 WHERE rsl.shipment_header_id = rsh.shipment_header_id
129 AND rsl.shipment_header_id = v_shipment_header_id
130 AND rsl.shipment_line_id = Nvl(v_shipment_line_id, rsl.shipment_line_id)
131 AND poll.po_header_id = Nvl(v_po_header_id, poll.po_header_id)
132 -- Bug 3213241
133 AND v_wms_po_j_or_higher = 'Y'
134 AND (((rsl.asn_lpn_id IS NOT NULL
135 AND rsl.asn_lpn_id = v_lpn_id
136 )
137 )
138 OR (rsl.asn_lpn_id IS NULL)
139 OR (v_lpn_id IS NULL)
140 )
141 AND pod.line_location_id = poll.line_location_id
142 AND (rsl.item_id = v_item_id
143 OR (v_item_id IS NULL
144 AND rsl.item_id IS NULL
145 AND rsl.item_description = v_item_desc))
146 AND poll.approved_flag = 'Y'
147 AND Nvl(poll.cancel_flag, 'N') = 'N'
148 AND Nvl(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
149 AND poll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
150 AND poll.line_location_id = rsl.po_line_location_id
151 AND rsh.asn_type IN ('ASN', 'ASBN')
152 -- bug 2752051
153 AND rsl.shipment_line_status_code IN ('EXPECTED','PARTIALLY RECEIVED') --<> 'CANCELLED'
154 AND ( v_project_id is null or
155 (v_project_id = -9999 and pod.project_id is null) or -- bug2669021
156 pod.project_id = v_project_id
157 )
158 and (v_task_id is null or pod.task_id = v_task_id)
159 )ta --14776842
160 ORDER BY expected_receipt_date, Decode(unit_of_measure,v_uom_code,0,1); --14776842
161
162
163 CURSOR count_asn_direct_lines
164 ( v_shipment_header_id NUMBER
165 ,v_shipment_line_id NUMBER
166 ,v_item_id NUMBER
167 ,v_po_header_id NUMBER
168 ,v_lpn_id NUMBER
169 ,v_item_desc VARCHAR2
170 ,v_project_id NUMBER
171 ,v_task_id NUMBER
172 ,v_wms_po_j_or_higher VARCHAR2)
173 IS
174
175 --Bug5578552.The COUNT should be outside of UNION.
176 SELECT COUNT(*) FROM
177 ( SELECT 1
178 FROM rcv_shipment_lines rsl
179 , rcv_shipment_headers rsh
180 , po_line_locations poll
181 , po_distributions pod
182 , (SELECT DISTINCT source_line_id
183 FROM wms_lpn_contents
184 WHERE parent_lpn_id = v_lpn_id) wlc
185 WHERE rsl.shipment_header_id = rsh.shipment_header_id
186 AND rsl.shipment_header_id = v_shipment_header_id
187 AND rsl.shipment_line_id = Nvl(v_shipment_line_id, rsl.shipment_line_id)
188 AND poll.po_header_id = Nvl(v_po_header_id, poll.po_header_id)
189 -- Bug 3213241
190 --AND poll.po_line_id = wlc.source_line_id (+)
191 AND poll.po_line_id = wlc.source_line_id (+)
192 AND v_wms_po_j_or_higher = 'N'
193 AND pod.line_location_id = poll.line_location_id
194 AND (rsl.item_id = v_item_id
195 OR (v_item_id IS NULL
196 AND rsl.item_id IS NULL
197 AND rsl.item_description = v_item_desc))
198 AND poll.approved_flag = 'Y'
199 AND Nvl(poll.cancel_flag, 'N') = 'N'
200 AND Nvl(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
201 AND poll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
202 AND poll.line_location_id = rsl.po_line_location_id
203 AND rsh.asn_type IN ('ASN', 'ASBN')
204 -- bug 2752051
205 AND rsl.shipment_line_status_code IN ('EXPECTED','PARTIALLY RECEIVED') --<> 'CANCELLED'
206 AND ( v_project_id is null or
207 (v_project_id = -9999 and pod.project_id is null) or --bug 2669021
208 pod.project_id = v_project_id
209 )
210 and (v_task_id is null or pod.task_id = v_task_id)
211 UNION ALL
212 SELECT 1
213 FROM rcv_shipment_lines rsl
214 , rcv_shipment_headers rsh
215 , po_line_locations poll
216 , po_distributions pod
217 WHERE rsl.shipment_header_id = rsh.shipment_header_id
218 AND rsl.shipment_header_id = v_shipment_header_id
219 AND rsl.shipment_line_id = Nvl(v_shipment_line_id, rsl.shipment_line_id)
220 AND poll.po_header_id = Nvl(v_po_header_id, poll.po_header_id)
221 -- Bug 3213241
222 AND v_wms_po_j_or_higher = 'Y'
223 AND (((rsl.asn_lpn_id IS NOT NULL
224 AND rsl.asn_lpn_id = v_lpn_id
225 )
226 )
227 OR (rsl.asn_lpn_id IS NULL)
228 OR (v_lpn_id IS NULL)
229 )
230 AND pod.line_location_id = poll.line_location_id
231 AND (rsl.item_id = v_item_id
232 OR (v_item_id IS NULL
233 AND rsl.item_id IS NULL
234 AND rsl.item_description = v_item_desc))
235 AND poll.approved_flag = 'Y'
236 AND Nvl(poll.cancel_flag, 'N') = 'N'
237 AND Nvl(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
238 AND poll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
239 AND poll.line_location_id = rsl.po_line_location_id
240 AND rsh.asn_type IN ('ASN', 'ASBN')
241 -- bug 2752051
242 AND rsl.shipment_line_status_code IN ('EXPECTED','PARTIALLY RECEIVED') --<> 'CANCELLED'
243 AND ( v_project_id is null or
244 (v_project_id = -9999 and pod.project_id is null) or -- bug2669021
245 pod.project_id = v_project_id
246 )
247 and (v_task_id is null or pod.task_id = v_task_id) ) ;
248
249 CURSOR asn_receipt_lines
250 ( v_shipment_header_id NUMBER
251 ,v_shipment_line_id NUMBER
252 ,v_item_id NUMBER
253 ,v_po_header_id NUMBER
254 ,v_lpn_id NUMBER
255 ,v_item_desc VARCHAR2
256 ,v_project_id NUMBER
257 ,v_task_id NUMBER
258 ,v_lot_number VARCHAR2 -- Added for Bug 12732516
259 ,v_wms_po_j_or_higher VARCHAR2
260 ,v_item_revision VARCHAR2 --Bug 13598673
261 ,v_uom_code VARCHAR2) --14776842
262 IS
263 SELECT shipment_line_id, -- 14776842
264 unit_of_measure,
265 item_id,
266 receipt_source_code,
267 to_organization_id,
268 rcv_transaction_id,
269 oe_order_line_id,
270 po_header_id,
271 po_line_id,
272 line_location_id,
273 po_distribution_id,
274 item_description,
275 expected_receipt_date
276 FROM ( -- 14776842
277 SELECT shipment_line_id /*Bug 13598673 modified the cursor by adding outer query to add order by*/
278 , unit_of_measure
279 , item_id
280 , 'VENDOR' receipt_source_code -- 14776842
281 , to_organization_id
282 , 0 rcv_transaction_id -- 14776842
283 , To_number(NULL) oe_order_line_id -- 14776842
284 , po_header_id
285 , po_line_id
286 , line_location_id
287 , To_number(NULL) po_distribution_id -- 14776842
288 , item_description
289 , expected_receipt_date
290 , revision, quantity_received -- 14776842
291 FROM( SELECT rsl.shipment_line_id shipment_line_id
292 , rsl.unit_of_measure unit_of_measure
293 , rsl.item_id item_id
294 , 'VENDOR'
295 , rsl.to_organization_id to_organization_id
296 , 0 -- rcv_transaction_id
297 , To_number(NULL)
298 , poll.po_header_id po_header_id
299 , poll.po_line_id po_line_id
300 , poll.line_location_id line_location_id
301 , To_number(NULL)
302 , rsl.item_description item_description
303 , Nvl(rsh.expected_receipt_date, Sysdate) expected_receipt_date
304 , (rsl.QUANTITY_SHIPPED - nvl(rsl.quantity_received, 0)) quantity_received --Bug 13598673
305 , decode(nvl(rsl.item_revision,'@@'), nvl(v_item_revision, '@@'),0,1) revision --Bug 13598673
306 FROM rcv_shipment_lines rsl
307 , rcv_shipment_headers rsh
308 , po_line_locations poll
309 , (SELECT DISTINCT source_line_id
310 FROM wms_lpn_contents
311 WHERE parent_lpn_id = v_lpn_id) wlc
312 WHERE rsl.shipment_header_id = rsh.shipment_header_id
313 AND rsl.shipment_header_id = v_shipment_header_id
314 AND rsl.shipment_line_id = Nvl(v_shipment_line_id, rsl.shipment_line_id)
315 AND poll.po_header_id = Nvl(v_po_header_id, poll.po_header_id)
316 -- Bug 3213241
317 --AND poll.po_line_id = wlc.source_line_id (+)
318 AND poll.po_line_id = wlc.source_line_id (+)
319 AND v_wms_po_j_or_higher = 'N'
320 -- change to receive non-item master lines
321 --AND rsl.item_id = v_item_id
322 AND ( rsl.item_id = v_item_id
323 OR ( v_item_id IS NULL
324 AND rsl.item_id IS NULL
325 AND rsl.item_description = v_item_desc))
326 AND poll.approved_flag = 'Y'
327 AND Nvl(poll.cancel_flag, 'N') = 'N'
328 AND Nvl(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
329 AND poll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
330 AND poll.line_location_id = rsl.po_line_location_id
331 AND rsh.asn_type IN ('ASN', 'ASBN')
332 -- bug 2752051
333 AND rsl.shipment_line_status_code IN ('EXPECTED','PARTIALLY RECEIVED') --<> 'CANCELLED'
334 AND exists
335 ( select '1'
336 from po_distributions_all pod
337 where pod.line_location_id = poll.line_location_id
338 and (v_project_id is null or
339 (v_project_id = -9999 and pod.project_id is null) or -- bug2669021
340 pod.project_id = v_project_id
341 )
342 and (v_task_id is null or pod.task_id = v_task_id)
343 )
344 AND (NVL(rsl.quantity_received,0)=0 OR NVL(rsl.item_revision, '@@') = NVL(v_item_revision, '@@')) --Bug 13598673
345 UNION ALL
346 SELECT rsl.shipment_line_id shipment_line_id
347 , rsl.unit_of_measure unit_of_measure
348 , rsl.item_id item_id
349 , 'VENDOR' receipt_source_code -- 14776842
350 , rsl.to_organization_id to_organization_id
351 , 0 rcv_transaction_id -- 14776842
352 , To_number(NULL) oe_order_line_id -- 14776842
353 , poll.po_header_id po_header_id
354 , poll.po_line_id po_line_id
355 , poll.line_location_id line_location_id
356 , To_number(NULL) po_distribution_id -- 14776842
357 , rsl.item_description item_description
358 , Nvl(rsh.expected_receipt_date, Sysdate) expected_receipt_date
359 , (rsl.QUANTITY_SHIPPED - nvl(rsl.quantity_received, 0)) quantity_received --Bug 13598673
360 , decode(nvl(rsl.item_revision,'@@'), nvl(v_item_revision, '@@'),0,1) revision --Bug 13598673
361 FROM rcv_shipment_lines rsl
362 , rcv_shipment_headers rsh
363 , po_line_locations poll
364 WHERE rsl.shipment_header_id = rsh.shipment_header_id
365 AND rsl.shipment_header_id = v_shipment_header_id
366 AND rsl.shipment_line_id = Nvl(v_shipment_line_id, rsl.shipment_line_id)
367 AND poll.po_header_id = Nvl(v_po_header_id, poll.po_header_id)
368 -- Bug 3213241
369 AND v_wms_po_j_or_higher = 'Y'
370 AND (((rsl.asn_lpn_id IS NOT NULL
371 AND rsl.asn_lpn_id = v_lpn_id
372 )
373 )
374 OR (rsl.asn_lpn_id IS NULL)
375 OR (v_lpn_id IS NULL)
376 )
377 -- change to receive non-item master lines
378 --AND rsl.item_id = v_item_id
379 AND ( rsl.item_id = v_item_id
380 OR ( v_item_id IS NULL
381 AND rsl.item_id IS NULL
382 AND rsl.item_description = v_item_desc))
383 AND poll.approved_flag = 'Y'
384 AND Nvl(poll.cancel_flag, 'N') = 'N'
385 AND Nvl(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
386 AND poll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
387 AND poll.line_location_id = rsl.po_line_location_id
388 AND rsh.asn_type IN ('ASN', 'ASBN')
389 -- bug 2752051
390 AND rsl.shipment_line_status_code IN ('EXPECTED','PARTIALLY RECEIVED') --<> 'CANCELLED'
391 --Added for Bug 12732516
392 AND (
393 v_lot_number IS NULL
394 OR
395 EXISTS
396 (
397 SELECT lot_num
398 FROM rcv_lots_supply rls
399 WHERE rls.shipment_line_id = rsl.shipment_line_id
400 AND rls.lot_num = v_lot_number
401 AND rls.supply_type_code = 'SHIPMENT'
402 )
403 )
404 AND exists
405 ( select '1'
406 from po_distributions_all pod
407 where pod.line_location_id = poll.line_location_id
408 and (v_project_id is null or
409 (v_project_id = -9999 and pod.project_id is null) or -- bug2669021
410 pod.project_id = v_project_id
411 )
412 and (v_task_id is null or pod.task_id = v_task_id)
413 )
414 AND (NVL(rsl.quantity_received,0)=0 OR NVL(rsl.item_revision, '@@') = NVL(v_item_revision, '@@'))) --Bug 13598673
415 )ta -- 14776842
416 ORDER BY expected_receipt_date, revision, quantity_received , Decode(unit_of_measure,v_uom_code,0,1); --14776842
417
418 CURSOR count_asn_receipt_lines
419 ( v_shipment_header_id NUMBER
420 ,v_shipment_line_id NUMBER
421 ,v_item_id NUMBER
422 ,v_po_header_id NUMBER
423 ,v_lpn_id NUMBER
424 ,v_item_desc VARCHAR2
425 ,v_project_id NUMBER
426 ,v_task_id NUMBER
427 ,v_lot_number VARCHAR2 -- Added for Bug 12732516
428 ,v_wms_po_j_or_higher VARCHAR2
429 ,v_item_revision VARCHAR2 ) --Bug 13598673
430 IS
431
432 --Bug5578552.The COUNT should be outside of UNION.
433 SELECT COUNT(*) FROM
434 ( SELECT 1
435 FROM rcv_shipment_lines rsl
436 , rcv_shipment_headers rsh
437 , po_line_locations poll
438 , (SELECT DISTINCT source_line_id
439 FROM wms_lpn_contents
440 WHERE parent_lpn_id = v_lpn_id) wlc
441 WHERE rsl.shipment_header_id = rsh.shipment_header_id
442 AND rsl.shipment_header_id = v_shipment_header_id
443 AND rsl.shipment_line_id = Nvl(v_shipment_line_id, rsl.shipment_line_id)
444 AND poll.po_header_id = Nvl(v_po_header_id, poll.po_header_id)
445 -- Bug 3213241
446 --AND poll.po_line_id = wlc.source_line_id (+)
447 AND poll.po_line_id = wlc.source_line_id (+)
448 AND v_wms_po_j_or_higher = 'N'
449 -- change to receive non-item master lines
450 --AND rsl.item_id = v_item_id
451 AND ( rsl.item_id = v_item_id
452 OR ( v_item_id IS NULL
453 AND rsl.item_id IS NULL
454 AND rsl.item_description = v_item_desc))
455 AND poll.approved_flag = 'Y'
456 AND Nvl(poll.cancel_flag, 'N') = 'N'
457 AND Nvl(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
458 AND poll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
459 AND poll.line_location_id = rsl.po_line_location_id
460 AND rsh.asn_type IN ('ASN', 'ASBN')
461 -- bug 2752051
462 AND rsl.shipment_line_status_code IN ('EXPECTED','PARTIALLY RECEIVED') --<> 'CANCELLED'
463 AND exists
464 ( select '1'
465 from po_distributions_all pod
466 where pod.line_location_id = poll.line_location_id
467 and (v_project_id is null or
468 (v_project_id = -9999 and pod.project_id is null) or -- bug2669021
469 pod.project_id = v_project_id
470 )
471 and (v_task_id is null or pod.task_id = v_task_id)
472 )
473 AND (NVL(rsl.quantity_received,0)=0 OR NVL(rsl.item_revision, '@@') = NVL(v_item_revision, '@@')) --Bug 13598673
474 UNION ALL
475 SELECT 1
476 FROM rcv_shipment_lines rsl
477 , rcv_shipment_headers rsh
478 , po_line_locations poll
479 WHERE rsl.shipment_header_id = rsh.shipment_header_id
480 AND rsl.shipment_header_id = v_shipment_header_id
481 AND rsl.shipment_line_id = Nvl(v_shipment_line_id, rsl.shipment_line_id)
482 AND poll.po_header_id = Nvl(v_po_header_id, poll.po_header_id)
483 -- Bug 3213241
484 AND v_wms_po_j_or_higher = 'Y'
485 AND (((rsl.asn_lpn_id IS NOT NULL
486 AND rsl.asn_lpn_id = v_lpn_id
487 )
488 )
489 OR (rsl.asn_lpn_id IS NULL)
490 OR (v_lpn_id IS NULL)
491 )
492 -- change to receive non-item master lines
493 --AND rsl.item_id = v_item_id
494 AND ( rsl.item_id = v_item_id
495 OR ( v_item_id IS NULL
496 AND rsl.item_id IS NULL
497 AND rsl.item_description = v_item_desc))
498 AND poll.approved_flag = 'Y'
499 AND Nvl(poll.cancel_flag, 'N') = 'N'
500 AND Nvl(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
501 AND poll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
502 AND poll.line_location_id = rsl.po_line_location_id
503 AND rsh.asn_type IN ('ASN', 'ASBN')
504 -- bug 2752051
505 AND rsl.shipment_line_status_code IN ('EXPECTED','PARTIALLY RECEIVED') --<> 'CANCELLED'
506 --Added for Bug 12732516
507 AND (
508 v_lot_number IS NULL
509 OR
510 EXISTS
511 (
512 SELECT lot_num
513 FROM rcv_lots_supply rls
514 WHERE rls.shipment_line_id = rsl.shipment_line_id
515 AND rls.lot_num = v_lot_number
516 AND rls.supply_type_code = 'SHIPMENT'
517 )
518 )
519 AND exists
520 ( select '1'
521 from po_distributions_all pod
522 where pod.line_location_id = poll.line_location_id
523 and (v_project_id is null or
524 (v_project_id = -9999 and pod.project_id is null) or -- bug2669021
525 pod.project_id = v_project_id
526 )
527 and (v_task_id is null or pod.task_id = v_task_id)
528 )
529 AND (NVL(rsl.quantity_received,0)=0 OR NVL(rsl.item_revision, '@@') = NVL(v_item_revision, '@@')) --Bug 13598673
530 );
531
532
533 -- For Bug 7440217 Defining Cursors
534 CURSOR lcm_direct_lines
535 ( v_shipment_header_id NUMBER
536 ,v_shipment_line_id NUMBER
537 ,v_item_id NUMBER
538 ,v_po_header_id NUMBER
539 ,v_lpn_id NUMBER
540 ,v_item_desc VARCHAR2
541 ,v_project_id NUMBER
542 ,v_task_id NUMBER
543 ,v_wms_po_j_or_higher VARCHAR2)
544 IS
545 SELECT rsl.shipment_line_id
546 , rsl.unit_of_measure
547 , rsl.item_id
548 , 'VENDOR'
549 , rsl.to_organization_id
550 , 0 -- rcv_transaction_id
551 , To_number(NULL)
552 , poll.po_header_id
553 , poll.po_line_id
554 , poll.line_location_id
555 , pod.po_distribution_id
556 , rsl.item_description
557 , Nvl(rsh.expected_receipt_date, Sysdate) expected_receipt_date
558 FROM rcv_shipment_lines rsl
559 , rcv_shipment_headers rsh
560 , po_line_locations poll
561 , po_distributions pod
562 , (SELECT DISTINCT source_line_id
563 FROM wms_lpn_contents
564 WHERE parent_lpn_id = v_lpn_id) wlc
565 WHERE rsl.shipment_header_id = rsh.shipment_header_id
566 AND rsl.shipment_header_id = v_shipment_header_id
567 AND rsl.shipment_line_id = Nvl(v_shipment_line_id, rsl.shipment_line_id)
568 AND poll.po_header_id = Nvl(v_po_header_id, poll.po_header_id)
569 -- Bug 3213241
570 AND poll.po_line_id = wlc.source_line_id (+)
571 AND v_wms_po_j_or_higher = 'N'
572 AND pod.line_location_id = poll.line_location_id
573 AND (rsl.item_id = v_item_id
574 OR (v_item_id IS NULL
575 AND rsl.item_id IS NULL
576 AND rsl.item_description = v_item_desc))
577 AND poll.approved_flag = 'Y'
578 AND Nvl(poll.cancel_flag, 'N') = 'N'
579 AND Nvl(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
580 AND poll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
581 AND poll.line_location_id = rsl.po_line_location_id
582 AND rsh.asn_type IN ('LCM')
583 -- bug 2752051
584 AND rsl.shipment_line_status_code IN ('EXPECTED','PARTIALLY RECEIVED') --<> 'CANCELLED'
585 AND ( v_project_id is null or
586 (v_project_id = -9999 and pod.project_id is null) or -- bug2669021
587 pod.project_id = v_project_id
588 )
589 and (v_task_id is null or pod.task_id = v_task_id)
590 UNION ALL
591 SELECT rsl.shipment_line_id
592 , rsl.unit_of_measure
593 , rsl.item_id
594 , 'VENDOR'
595 , rsl.to_organization_id
596 , 0 -- rcv_transaction_id
597 , To_number(NULL)
598 , poll.po_header_id
599 , poll.po_line_id
600 , poll.line_location_id
601 , pod.po_distribution_id
602 , rsl.item_description
603 , Nvl(rsh.expected_receipt_date, Sysdate) expected_receipt_date
604 FROM rcv_shipment_lines rsl
605 , rcv_shipment_headers rsh
606 , po_line_locations poll
607 , po_distributions pod
608 WHERE rsl.shipment_header_id = rsh.shipment_header_id
609 AND rsl.shipment_header_id = v_shipment_header_id
610 AND rsl.shipment_line_id = Nvl(v_shipment_line_id, rsl.shipment_line_id)
611 AND poll.po_header_id = Nvl(v_po_header_id, poll.po_header_id)
612 -- Bug 3213241
613 AND v_wms_po_j_or_higher = 'Y'
614 AND pod.line_location_id = poll.line_location_id
615 AND (rsl.item_id = v_item_id
616 OR (v_item_id IS NULL
617 AND rsl.item_id IS NULL
618 AND rsl.item_description = v_item_desc))
619 AND poll.approved_flag = 'Y'
620 AND Nvl(poll.cancel_flag, 'N') = 'N'
621 AND Nvl(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
622 AND poll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
623 AND poll.line_location_id = rsl.po_line_location_id
624 AND rsh.asn_type IN ('LCM')
625 -- bug 2752051
626 AND rsl.shipment_line_status_code IN ('EXPECTED','PARTIALLY RECEIVED') --<> 'CANCELLED'
627 AND ( v_project_id is null or
628 (v_project_id = -9999 and pod.project_id is null) or -- bug2669021
629 pod.project_id = v_project_id
630 )
631 and (v_task_id is null or pod.task_id = v_task_id)
632 ORDER BY expected_receipt_date;
633
634 CURSOR count_lcm_direct_lines
635 ( v_shipment_header_id NUMBER
636 ,v_shipment_line_id NUMBER
637 ,v_item_id NUMBER
638 ,v_po_header_id NUMBER
639 ,v_lpn_id NUMBER
640 ,v_item_desc VARCHAR2
641 ,v_project_id NUMBER
642 ,v_task_id NUMBER
643 ,v_wms_po_j_or_higher VARCHAR2)
644 IS
645
646 --Bug5578552.The COUNT should be outside of UNION.
647 SELECT COUNT(*) FROM
648 ( SELECT 1
649 FROM rcv_shipment_lines rsl
650 , rcv_shipment_headers rsh
651 , po_line_locations poll
652 , po_distributions pod
653 , (SELECT DISTINCT source_line_id
654 FROM wms_lpn_contents
655 WHERE parent_lpn_id = v_lpn_id) wlc
656 WHERE rsl.shipment_header_id = rsh.shipment_header_id
657 AND rsl.shipment_header_id = v_shipment_header_id
658 AND rsl.shipment_line_id = Nvl(v_shipment_line_id, rsl.shipment_line_id)
659 AND poll.po_header_id = Nvl(v_po_header_id, poll.po_header_id)
660 -- Bug 3213241
661 --AND poll.po_line_id = wlc.source_line_id (+)
662 AND poll.po_line_id = wlc.source_line_id (+)
663 AND v_wms_po_j_or_higher = 'N'
664 AND pod.line_location_id = poll.line_location_id
665 AND (rsl.item_id = v_item_id
666 OR (v_item_id IS NULL
667 AND rsl.item_id IS NULL
668 AND rsl.item_description = v_item_desc))
669 AND poll.approved_flag = 'Y'
670 AND Nvl(poll.cancel_flag, 'N') = 'N'
671 AND Nvl(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
672 AND poll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
673 AND poll.line_location_id = rsl.po_line_location_id
674 AND rsh.asn_type IN ('LCM')
675 -- bug 2752051
676 AND rsl.shipment_line_status_code IN ('EXPECTED','PARTIALLY RECEIVED') --<> 'CANCELLED'
677 AND ( v_project_id is null or
678 (v_project_id = -9999 and pod.project_id is null) or --bug 2669021
679 pod.project_id = v_project_id
680 )
681 and (v_task_id is null or pod.task_id = v_task_id)
682 UNION ALL
683 SELECT 1
684 FROM rcv_shipment_lines rsl
685 , rcv_shipment_headers rsh
686 , po_line_locations poll
687 , po_distributions pod
688 WHERE rsl.shipment_header_id = rsh.shipment_header_id
689 AND rsl.shipment_header_id = v_shipment_header_id
690 AND rsl.shipment_line_id = Nvl(v_shipment_line_id, rsl.shipment_line_id)
691 AND poll.po_header_id = Nvl(v_po_header_id, poll.po_header_id)
692 -- Bug 3213241
693 AND v_wms_po_j_or_higher = 'Y'
694 AND pod.line_location_id = poll.line_location_id
695 AND (rsl.item_id = v_item_id
696 OR (v_item_id IS NULL
697 AND rsl.item_id IS NULL
698 AND rsl.item_description = v_item_desc))
699 AND poll.approved_flag = 'Y'
700 AND Nvl(poll.cancel_flag, 'N') = 'N'
701 AND Nvl(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
702 AND poll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
703 AND poll.line_location_id = rsl.po_line_location_id
704 AND rsh.asn_type IN ('LCM')
705 -- bug 2752051
706 AND rsl.shipment_line_status_code IN ('EXPECTED','PARTIALLY RECEIVED') --<> 'CANCELLED'
707 AND ( v_project_id is null or
708 (v_project_id = -9999 and pod.project_id is null) or -- bug2669021
709 pod.project_id = v_project_id
710 )
711 and (v_task_id is null or pod.task_id = v_task_id) ) ;
712
713 CURSOR lcm_receipt_lines
714 ( v_shipment_header_id NUMBER
715 ,v_shipment_line_id NUMBER
716 ,v_item_id NUMBER
717 ,v_po_header_id NUMBER
718 ,v_lpn_id NUMBER
719 ,v_item_desc VARCHAR2
720 ,v_project_id NUMBER
721 ,v_task_id NUMBER
722 ,v_wms_po_j_or_higher VARCHAR2)
723 IS
724 SELECT rsl.shipment_line_id
725 , rsl.unit_of_measure
726 , rsl.item_id
727 , 'VENDOR'
728 , rsl.to_organization_id
729 , 0 -- rcv_transaction_id
730 , To_number(NULL)
731 , poll.po_header_id
732 , poll.po_line_id
733 , poll.line_location_id
734 , To_number(NULL)
735 , rsl.item_description
736 , Nvl(rsh.expected_receipt_date, Sysdate) expected_receipt_date
737 FROM rcv_shipment_lines rsl
738 , rcv_shipment_headers rsh
739 , po_line_locations poll
740 , (SELECT DISTINCT source_line_id
741 FROM wms_lpn_contents
742 WHERE parent_lpn_id = v_lpn_id) wlc
743 WHERE rsl.shipment_header_id = rsh.shipment_header_id
744 AND rsl.shipment_header_id = v_shipment_header_id
745 AND rsl.shipment_line_id = Nvl(v_shipment_line_id, rsl.shipment_line_id)
746 AND poll.po_header_id = Nvl(v_po_header_id, poll.po_header_id)
747 -- Bug 3213241
748 --AND poll.po_line_id = wlc.source_line_id (+)
749 AND poll.po_line_id = wlc.source_line_id (+)
750 AND v_wms_po_j_or_higher = 'N'
751 -- change to receive non-item master lines
752 --AND rsl.item_id = v_item_id
753 AND ( rsl.item_id = v_item_id
754 OR ( v_item_id IS NULL
755 AND rsl.item_id IS NULL
756 AND rsl.item_description = v_item_desc))
757 AND poll.approved_flag = 'Y'
758 AND Nvl(poll.cancel_flag, 'N') = 'N'
759 AND Nvl(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
760 AND poll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
761 AND poll.line_location_id = rsl.po_line_location_id
762 AND rsh.asn_type IN ('LCM')
763 -- bug 2752051
764 AND rsl.shipment_line_status_code IN ('EXPECTED','PARTIALLY RECEIVED') --<> 'CANCELLED'
765 AND exists
766 ( select '1'
767 from po_distributions_all pod
768 where pod.line_location_id = poll.line_location_id
769 and (v_project_id is null or
770 (v_project_id = -9999 and pod.project_id is null) or -- bug2669021
771 pod.project_id = v_project_id
772 )
773 and (v_task_id is null or pod.task_id = v_task_id)
774 )
775 UNION ALL
776 SELECT rsl.shipment_line_id
777 , rsl.unit_of_measure
778 , rsl.item_id
779 , 'VENDOR'
780 , rsl.to_organization_id
781 , 0 -- rcv_transaction_id
782 , To_number(NULL)
783 , poll.po_header_id
784 , poll.po_line_id
785 , poll.line_location_id
786 , To_number(NULL)
787 , rsl.item_description
788 , Nvl(rsh.expected_receipt_date, Sysdate) expected_receipt_date
789 FROM rcv_shipment_lines rsl
790 , rcv_shipment_headers rsh
791 , po_line_locations poll
792 WHERE rsl.shipment_header_id = rsh.shipment_header_id
793 AND rsl.shipment_header_id = v_shipment_header_id
794 AND rsl.shipment_line_id = Nvl(v_shipment_line_id, rsl.shipment_line_id)
795 AND poll.po_header_id = Nvl(v_po_header_id, poll.po_header_id)
796 -- Bug 3213241
797 AND v_wms_po_j_or_higher = 'Y'
798 -- change to receive non-item master lines
799 --AND rsl.item_id = v_item_id
800 AND ( rsl.item_id = v_item_id
801 OR ( v_item_id IS NULL
802 AND rsl.item_id IS NULL
803 AND rsl.item_description = v_item_desc))
804 AND poll.approved_flag = 'Y'
805 AND Nvl(poll.cancel_flag, 'N') = 'N'
806 AND Nvl(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
807 AND poll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
808 AND poll.line_location_id = rsl.po_line_location_id
809 AND rsh.asn_type IN ('LCM')
810 -- bug 2752051
811 AND rsl.shipment_line_status_code IN ('EXPECTED','PARTIALLY RECEIVED') --<> 'CANCELLED'
812 AND exists
813 ( select '1'
814 from po_distributions_all pod
815 where pod.line_location_id = poll.line_location_id
816 and (v_project_id is null or
817 (v_project_id = -9999 and pod.project_id is null) or -- bug2669021
818 pod.project_id = v_project_id
819 )
820 and (v_task_id is null or pod.task_id = v_task_id)
821 )
822 ORDER BY expected_receipt_date;
823
824 CURSOR count_lcm_receipt_lines
825 ( v_shipment_header_id NUMBER
826 ,v_shipment_line_id NUMBER
827 ,v_item_id NUMBER
828 ,v_po_header_id NUMBER
829 ,v_lpn_id NUMBER
830 ,v_item_desc VARCHAR2
831 ,v_project_id NUMBER
832 ,v_task_id NUMBER
833 ,v_wms_po_j_or_higher VARCHAR2)
834 IS
835
836 --Bug5578552.The COUNT should be outside of UNION.
837 SELECT COUNT(*) FROM
838 ( SELECT 1
839 FROM rcv_shipment_lines rsl
840 , rcv_shipment_headers rsh
841 , po_line_locations poll
842 , (SELECT DISTINCT source_line_id
843 FROM wms_lpn_contents
844 WHERE parent_lpn_id = v_lpn_id) wlc
845 WHERE rsl.shipment_header_id = rsh.shipment_header_id
846 AND rsl.shipment_header_id = v_shipment_header_id
847 AND rsl.shipment_line_id = Nvl(v_shipment_line_id, rsl.shipment_line_id)
848 AND poll.po_header_id = Nvl(v_po_header_id, poll.po_header_id)
849 -- Bug 3213241
850 --AND poll.po_line_id = wlc.source_line_id (+)
851 AND poll.po_line_id = wlc.source_line_id (+)
852 AND v_wms_po_j_or_higher = 'N'
853 -- change to receive non-item master lines
854 --AND rsl.item_id = v_item_id
855 AND ( rsl.item_id = v_item_id
856 OR ( v_item_id IS NULL
857 AND rsl.item_id IS NULL
858 AND rsl.item_description = v_item_desc))
859 AND poll.approved_flag = 'Y'
860 AND Nvl(poll.cancel_flag, 'N') = 'N'
861 AND Nvl(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
862 AND poll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
863 AND poll.line_location_id = rsl.po_line_location_id
864 AND rsh.asn_type IN ('LCM')
865 -- bug 2752051
866 AND rsl.shipment_line_status_code IN ('EXPECTED','PARTIALLY RECEIVED') --<> 'CANCELLED'
867 AND exists
868 ( select '1'
869 from po_distributions_all pod
870 where pod.line_location_id = poll.line_location_id
871 and (v_project_id is null or
872 (v_project_id = -9999 and pod.project_id is null) or -- bug2669021
873 pod.project_id = v_project_id
874 )
875 and (v_task_id is null or pod.task_id = v_task_id)
876 )
877 UNION ALL
878 SELECT 1
879 FROM rcv_shipment_lines rsl
880 , rcv_shipment_headers rsh
881 , po_line_locations poll
882 WHERE rsl.shipment_header_id = rsh.shipment_header_id
883 AND rsl.shipment_header_id = v_shipment_header_id
884 AND rsl.shipment_line_id = Nvl(v_shipment_line_id, rsl.shipment_line_id)
885 AND poll.po_header_id = Nvl(v_po_header_id, poll.po_header_id)
886 -- Bug 3213241
887 AND v_wms_po_j_or_higher = 'Y'
888 -- change to receive non-item master lines
889 --AND rsl.item_id = v_item_id
890 AND ( rsl.item_id = v_item_id
891 OR ( v_item_id IS NULL
892 AND rsl.item_id IS NULL
893 AND rsl.item_description = v_item_desc))
894 AND poll.approved_flag = 'Y'
895 AND Nvl(poll.cancel_flag, 'N') = 'N'
896 AND Nvl(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
897 AND poll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
898 AND poll.line_location_id = rsl.po_line_location_id
899 AND rsh.asn_type IN ('LCM')
900 -- bug 2752051
901 AND rsl.shipment_line_status_code IN ('EXPECTED','PARTIALLY RECEIVED') --<> 'CANCELLED'
902 AND exists
903 ( select '1'
904 from po_distributions_all pod
905 where pod.line_location_id = poll.line_location_id
906 and (v_project_id is null or
907 (v_project_id = -9999 and pod.project_id is null) or -- bug2669021
908 pod.project_id = v_project_id
909 )
910 and (v_task_id is null or pod.task_id = v_task_id)
911 ) );
912 -- End for Bug 7440217
913
914
915
916
917 CURSOR int_req_receipt_lines
918 ( v_shipment_header_id NUMBER
919 ,v_shipment_line_id NUMBER
920 ,v_item_id NUMBER
921 ,v_org_id NUMBER
922 ,v_txn_date DATE
923 ,v_project_id NUMBER
924 ,v_task_id NUMBER
925 ,v_lpn_id NUMBER
926 ,v_lot_number VARCHAR2 --9229228
927 ,v_serial_number VARCHAR2 --9651496
928 ,v_req_header_id NUMBER -- 12546176
929 ,v_uom_code VARCHAR2) --14776842
930 IS
931 SELECT rsl.shipment_line_id
932 , rsl.unit_of_measure
933 , rsl.item_id
934 , Decode(rsl.source_document_code,'INVENTORY','INVENTORY','REQ','INTERNAL ORDER')
935 , rsl.to_organization_id
936 , 0 -- rcv_transaction_id
937 , To_number(NULL)
938 , To_number(NULL)
939 , To_number(NULL)
940 , To_number(NULL)
941 , To_number(NULL)
942 , rsl.item_description
943 , Nvl(rsh.expected_receipt_date, Sysdate) expected_receipt_date
944 FROM rcv_shipment_lines rsl
945 , rcv_shipment_headers rsh
946 , mtl_supply ms
947 WHERE rsl.shipment_header_id = v_shipment_header_id
948 AND rsh.shipment_header_id = rsl.shipment_header_id
949 AND rsl.shipment_line_id = Nvl(v_shipment_line_id, rsl.shipment_line_id)
950 AND rsl.item_id = v_item_id
951 AND rsh.receipt_source_code <> 'VENDOR'
952 AND ms.supply_type_code(+) = 'SHIPMENT'
953 AND ms.shipment_header_id = rsh.shipment_header_id
954 and ms.shipment_line_id = rsl.shipment_line_id
955 AND rsl.to_organization_id = v_org_id
956 AND (((rsl.asn_lpn_id IS NOT NULL
957 AND rsl.asn_lpn_id = v_lpn_id
958 )
959 )
960 OR (rsl.asn_lpn_id IS NULL)
961 OR (v_lpn_id IS NULL)
962 )--bug 4608033: Should join on rsl.asn_lpn_id so that
963 --the correct rsl can be picked up
964 AND Nvl(rsh.shipped_date,Nvl(v_txn_date-1,Sysdate-1)) < Nvl(v_txn_date,Sysdate)
965 AND ( (
966 rsl.source_document_code = 'REQ'
967 and exists
968 ( select '1'
969 from po_req_distributions_all prd
970 where rsl.requisition_line_id = prd.requisition_line_id
971 and Nvl(rsl.req_distribution_id,prd.distribution_id) = prd.distribution_id--BUG4930681
972 and (v_project_id is null or
973 (v_project_id = -9999 and prd.project_id is null) or -- bug2669021
974 prd.project_id = v_project_id
975 )
976 and (v_task_id is null or prd.task_id = v_task_id)
977 )
978 )
979 or rsl.source_document_code <> 'REQ'
980 )
981 -- 12546176 added condition for requistion
982 AND ( (
983 (
984 rsl.source_document_code = 'REQ'
985 and exists
986 ( select '1'
987 from po_requisition_lines prl
988 where prl.requisition_line_id = rsl.requisition_line_id
989 AND prl.requisition_header_id = v_req_header_id
990 )
991 or v_req_header_id is null
992 )
993 )
994 or rsl.source_document_code <> 'REQ'
995 )
996 -- 12546176 added condition for requistion
997 AND ( --9229228-Added this AND condition for lot.
998 v_lot_number IS NULL
999 -- 12814511 Added OR condition in cursor for plain item in source org
1000 OR ( exists (SELECT 1 FROM mtl_system_items
1001 WHERE inventory_item_id = v_item_id
1002 AND LOT_CONTROL_CODE = 1
1003 AND ORGANIZATION_ID = rsl.FROM_ORGANIZATION_ID )
1004 OR EXISTS (SELECT lot_num
1005 FROM rcv_lots_supply rls
1006 WHERE rls.shipment_line_id = rsl.shipment_line_id
1007 AND rls.lot_num = v_lot_number
1008 AND rls.supply_type_code = 'SHIPMENT')
1009 )
1010 )
1011 AND ( --9651496,9764650-Added this AND condition for serial.
1012 v_serial_number IS NULL
1013 OR
1014 EXISTS
1015 (
1016 SELECT serial_num
1017 FROM rcv_serials_supply rss
1018 WHERE rss.shipment_line_id = rsl.shipment_line_id
1019 AND rss.serial_num = v_serial_number
1020 AND rss.supply_type_code = 'SHIPMENT'
1021 )
1022 )
1023 ORDER BY Nvl(rsh.expected_receipt_date,Sysdate), Decode(unit_of_measure,v_uom_code,0,1),rsl.shipment_line_id; --Bug 8374257 and 14776842
1024
1025 CURSOR count_int_req_receipt_lines
1026 ( v_shipment_header_id NUMBER
1027 ,v_shipment_line_id NUMBER
1028 ,v_item_id NUMBER
1029 ,v_org_id NUMBER
1030 ,v_txn_date DATE
1031 ,v_project_id NUMBER
1032 ,v_task_id NUMBER
1033 ,v_lpn_id NUMBER
1034 ,v_lot_number VARCHAR2
1035 ,v_serial_number VARCHAR2
1036 ,v_req_header_id NUMBER -- 12546176
1037 ) --9651496,9764650
1038 IS
1039 SELECT COUNT(*)
1040 FROM rcv_shipment_lines rsl
1041 , rcv_shipment_headers rsh
1042 , mtl_supply ms
1043 WHERE rsl.shipment_header_id = v_shipment_header_id
1044 AND rsh.shipment_header_id = rsl.shipment_header_id
1045 AND rsl.shipment_line_id = Nvl(v_shipment_line_id, rsl.shipment_line_id)
1046 AND rsl.item_id = v_item_id
1047 AND rsh.receipt_source_code <> 'VENDOR'
1048 AND ms.supply_type_code(+) = 'SHIPMENT'
1049 AND ms.shipment_header_id = rsh.shipment_header_id
1050 and ms.shipment_line_id = rsl.shipment_line_id
1051 AND rsl.to_organization_id = v_org_id
1052 AND Nvl(rsh.shipped_date,Nvl(v_txn_date-1,Sysdate-1)) < Nvl(v_txn_date,Sysdate)
1053 AND (((rsl.asn_lpn_id IS NOT NULL
1054 AND rsl.asn_lpn_id = v_lpn_id
1055 )
1056 )
1057 OR (rsl.asn_lpn_id IS NULL)
1058 OR (v_lpn_id IS NULL)
1059 )--bug 4608033: Should join on rsl.asn_lpn_id so that
1060 --the correct rsl can be picked up
1061 AND ( (
1062 rsl.source_document_code = 'REQ'
1063 and exists
1064 ( select '1'
1065 from po_req_distributions_all prd
1066 where rsl.requisition_line_id = prd.requisition_line_id
1067 and Nvl(rsl.req_distribution_id,prd.distribution_id) = prd.distribution_id--BUG4930681
1068 and (v_project_id is null or
1069 (v_project_id = -9999 and prd.project_id is null) or -- bug2669021
1070 prd.project_id = v_project_id
1071 )
1072 and (v_task_id is null or prd.task_id = v_task_id)
1073 )
1074 )
1075 or rsl.source_document_code <> 'REQ'
1076 )
1077 -- 12546176 added condition for requistion
1078 AND ( (
1079 (rsl.source_document_code = 'REQ'
1080
1081 and exists
1082 ( select '1'
1083 from po_requisition_lines prl
1084 where prl.requisition_line_id = rsl.requisition_line_id
1085 AND prl.requisition_header_id = v_req_header_id
1086 )
1087 or v_req_header_id is null
1088 )
1089 )
1090 or rsl.source_document_code <> 'REQ'
1091 )
1092 -- 12546176 added condition for requistion
1093
1094 AND ( --9229228-Added this AND condition for lot.
1095 v_lot_number IS NULL
1096 -- 12814511 Added OR condition in the count cursor for plain item in source org
1097 OR ( exists (SELECT 1 FROM mtl_system_items
1098 WHERE inventory_item_id = v_item_id
1099 AND LOT_CONTROL_CODE = 1
1100 AND ORGANIZATION_ID = rsl.FROM_ORGANIZATION_ID )
1101 OR EXISTS (SELECT lot_num
1102 FROM rcv_lots_supply rls
1103 WHERE rls.shipment_line_id = rsl.shipment_line_id
1104 AND rls.lot_num = v_lot_number
1105 AND rls.supply_type_code = 'SHIPMENT')
1106 )
1107 )
1108 AND ( --9651496,9764650-Added this AND condition for serial.
1109 v_serial_number IS NULL
1110 OR
1111 EXISTS
1112 (
1113 SELECT serial_num
1114 FROM rcv_serials_supply rss
1115 WHERE rss.shipment_line_id = rsl.shipment_line_id
1116 AND rss.serial_num = v_serial_number
1117 AND rss.supply_type_code = 'SHIPMENT'
1118 )
1119 );
1120
1121
1122 CURSOR rma_receipt_lines
1123 ( v_oe_order_header_id NUMBER
1124 ,v_oe_order_line_id NUMBER
1125 ,v_item_id NUMBER
1126 ,v_org_id NUMBER
1127 ,v_primary_uom VARCHAR2
1128 ,v_txn_date DATE
1129 ,v_project_id NUMBER
1130 ,v_task_id NUMBER
1131 ,v_uom_code varchar2) --14776842
1132 IS
1133 SELECT To_number(NULL)
1134 ,oel.order_quantity_uom --bug3592116-- v_primary_uom unit_of_measure -- the view was selecting the primary uom so just selected the same FROM the value passed TO avoid one more join.
1135 , oel.inventory_item_id item_id
1136 , 'CUSTOMER'
1137 , Nvl(oel.ship_from_org_id, oeh.ship_from_org_id) to_organization_id
1138 , 0 -- rcv_transaction_id
1139 , oel.line_id oe_order_line_id
1140 , To_number(NULL)
1141 , To_number(NULL)
1142 , To_number(NULL)
1143 , To_number(NULL)
1144 , To_char(NULL)
1145 , To_char(NULL)
1146 FROM oe_order_lines_all oel
1147 , oe_order_headers_all oeh
1148 --, wf_item_activity_statuses wf
1149 --, wf_process_activities wpa
1150 WHERE oel.header_id = oeh.header_id
1151 AND oel.header_id = v_oe_order_header_id
1152 AND oel.line_id = Nvl(v_oe_order_line_id, oel.line_id)
1153 AND oel.inventory_item_id = v_item_id
1154 AND oel.line_category_code = 'RETURN'
1155 AND oel.booked_flag = 'Y'
1156 AND oel.ordered_quantity > Nvl(oel.shipped_quantity,0)
1157 -- Date tolerance fix.
1158 AND (Trunc(Sysdate) >=
1159 Nvl(Trunc(oel.earliest_acceptable_date),Trunc(Sysdate)))
1160 AND (Trunc(Sysdate) <=
1161 Nvl(Trunc(oel.latest_acceptable_date),Trunc(Sysdate)))
1162 AND OEL.FLOW_STATUS_CODE = 'AWAITING_RETURN'
1163 -- performance fix
1164 --AND wpa.activity_item_type = 'OEOL'
1165 --AND wpa.activity_name = 'RMA_WAIT_FOR_RECEIVING'
1166 --AND wf.item_type = 'OEOL'
1167 --AND wf.process_activity = wpa.instance_id
1168 --AND wf.activity_status = 'NOTIFIED'
1169 --AND oel.line_id = To_number(wf.item_key)
1170 AND ( v_project_id is null
1171 or (v_project_id = -9999 and oel.project_id is null) -- bug2669021
1172 or (oel.project_id = v_project_id)
1173 )
1174 AND (v_task_id is null or oel.task_id= v_task_id)
1175 AND Nvl(oel.ship_from_org_id, oeh.ship_from_org_id) = v_org_id /*added for bug 3578489*/
1176 ORDER BY Nvl(Nvl(oel.promise_date,oel.request_date),Sysdate),Decode(ORDER_QUANTITY_UOM,v_uom_code,0,1); --14776842
1177
1178 CURSOR count_rma_receipt_lines
1179 ( v_oe_order_header_id NUMBER
1180 ,v_oe_order_line_id NUMBER
1181 ,v_item_id NUMBER
1182 ,v_org_id NUMBER
1183 ,v_primary_uom VARCHAR2
1184 ,v_txn_date DATE
1185 ,v_project_id NUMBER
1186 ,v_task_id NUMBER)
1187 IS
1188 SELECT COUNT(*)
1189 FROM oe_order_lines_all oel
1190 , oe_order_headers_all oeh
1191 --, wf_item_activity_statuses wf
1192 --, wf_process_activities wpa
1193 WHERE oel.header_id = oeh.header_id
1194 AND oel.header_id = v_oe_order_header_id
1195 AND oel.line_id = Nvl(v_oe_order_line_id, oel.line_id)
1196 AND oel.inventory_item_id = v_item_id
1197 AND oel.line_category_code = 'RETURN'
1198 AND oel.booked_flag = 'Y'
1199 AND oel.ordered_quantity > Nvl(oel.shipped_quantity,0)
1200 -- Date tolerance fix.
1201 AND (Trunc(Sysdate) >=
1202 Nvl(Trunc(oel.earliest_acceptable_date),Trunc(Sysdate)))
1203 AND (Trunc(Sysdate) <=
1204 Nvl(Trunc(oel.latest_acceptable_date),Trunc(Sysdate)))
1205 AND OEL.FLOW_STATUS_CODE = 'AWAITING_RETURN'
1206 -- performance fix
1207 --AND wpa.activity_item_type = 'OEOL'
1208 --AND wpa.activity_name = 'RMA_WAIT_FOR_RECEIVING'
1209 --AND wf.item_type = 'OEOL'
1210 --AND wf.process_activity = wpa.instance_id
1211 --AND wf.activity_status = 'NOTIFIED'
1212 --AND oel.line_id = To_number(wf.item_key)
1213 AND ( v_project_id is null
1214 or (v_project_id = -9999 and oel.project_id is null) -- bug2669021
1215 or (oel.project_id = v_project_id)
1216 )
1217 AND (v_task_id is null or oel.task_id= v_task_id)
1218 AND Nvl(oel.ship_from_org_id, oeh.ship_from_org_id) = v_org_id; --added for bug 3578489
1219
1220 /* FP-J Lot/Serial Support Enhancement
1221 * Added two new arguments to the cursor, v_lot_number and v_lpn_id_to_match
1222 * Added conditions to match the lot number with that in RCV_LOTS_SUPPLY
1223 * and the LPN with that in RCV_SUPPLY for the parent trasnaction
1224 * This would be done only if WMS and PO patchset levels are J or higher
1225 */
1226
1227 CURSOR int_req_delivery_lines(
1228 v_shipment_header_id NUMBER
1229 ,v_shipment_line_id NUMBER
1230 ,v_item_id NUMBER
1231 ,v_org_id NUMBER
1232 ,v_receipt_num VARCHAR2
1233 ,v_txn_date DATE
1234 ,v_inspection_status VARCHAR2
1235 ,v_lpn_id NUMBER
1236 ,v_lot_number VARCHAR2
1237 ,v_lpn_id_to_match NUMBER
1238 ,v_parent_txn_id_to_match NUMBER)
1239 IS
1240 SELECT rsup.shipment_line_id
1241 , rt.unit_of_measure
1242 -- Dont know if it is really correct, should be
1243 -- rsl.unit_of_measure but this actually IS
1244 -- rt.unit_of_measure....depends ON the transaction processor
1245 -- ON what it puts FOR this column.
1246 , rsup.item_id
1247 , rsh.receipt_source_code
1248 , rsup.to_organization_id
1249 , rsup.rcv_transaction_id
1250 , To_number(NULL) oe_order_line_id
1251 , To_number(NULL) po_header_id
1252 , To_number(NULL) po_line_id
1253 , To_number(NULL) po_line_location_id
1254 , To_number(NULL) po_distribution_id
1255 , rsl.item_description item_description
1256 , Nvl(rsh.expected_receipt_date, Sysdate) expected_receipt_date
1257 FROM rcv_supply rsup
1258 , rcv_transactions rt
1259 , rcv_shipment_headers rsh
1260 , rcv_shipment_lines rsl
1261 WHERE rsl.shipment_header_id = v_shipment_header_id
1262 AND rsl.shipment_line_id = Nvl(v_shipment_line_id,rsl.shipment_line_id)
1263 AND rsup.item_id = v_item_id
1264 AND Nvl(rsh.receipt_num,'@@@') = Nvl(v_receipt_num, Nvl(rsh.receipt_num,'@@@'))
1265 AND rt.transaction_date < Nvl(v_txn_date, (rt.transaction_date +1))
1266 AND (RT.ROUTING_HEADER_ID IS NULL OR
1267 RT.ROUTING_HEADER_ID <> 2 OR
1268 (rt.routing_header_id = 2
1269 AND rt.inspection_status_code <> 'NOT INSPECTED'
1270 AND rt.inspection_status_code = Nvl(v_inspection_status,rt.inspection_status_code)))
1271 --(RT.ROUTING_HEADER_ID = 2 AND rt.inspection_status_code <> 'NOT INSPECTED'))
1272 AND RSUP.SUPPLY_TYPE_CODE = 'RECEIVING'
1273 AND RSL.SHIPMENT_LINE_ID = RSUP.SHIPMENT_LINE_ID
1274 AND RT.TRANSACTION_ID = RSUP.RCV_TRANSACTION_ID
1275 AND RT.TRANSACTION_TYPE <> 'UNORDERED'
1276 -- for all the transactions in rt for which we can putaway, the
1277 -- transfer_lpn_id should match the lpn being putaway.
1278 --AND Nvl(rt.transfer_lpn_id,-1) = Nvl(v_lpn_id,-1)
1279 -- Fix for 1865886. Commented the above and added the following for lpn
1280 AND Nvl(v_lpn_id,-1) IN (select nvl(rt2.transfer_lpn_id,-1)
1281 from rcv_transactions rt2
1282 where rt2.transaction_type <> 'DELIVER'
1283 start with rt2.transaction_id = rsup.supply_source_id
1284 connect by prior rt2.transaction_id = rt2.parent_transaction_id
1285 union all
1286 select nvl(rt2.lpn_id,-1)
1287 from rcv_transactions rt2
1288 /* Changes made for bug #4926987 -- added ACCEPT and REJECT */
1289 where rt2.transaction_type not in ('RECEIVE', 'DELIVER','ACCEPT','REJECT')
1290 /* End of changes made for bug #4926987 -- added ACCEPT and REJECT */
1291 start with rt2.transaction_id = rsup.supply_source_id
1292 connect by prior rt2.transaction_id = rt2.parent_transaction_id
1293 )
1294 AND RSUP.to_organization_id = v_org_id
1295 AND RSH.SHIPMENT_HEADER_ID = RSUP.SHIPMENT_HEADER_ID
1296 AND (
1297 v_lot_number IS NULL
1298 OR(
1299 exists (SELECT 1 FROM mtl_system_items
1300 WHERE inventory_item_id = v_item_id
1301 AND LOT_CONTROL_CODE = 1
1302 AND ORGANIZATION_ID = rsup.FROM_ORGANIZATION_ID )
1303 OR EXISTS
1304 (
1305 --Bug 13400589 Modified the below condition which will handle the case for non wms orgs.
1306 SELECT lot_num
1307 FROM rcv_lots_supply rls
1308 WHERE ( rls.transaction_id = rsup.supply_source_id
1309 OR (rls.transaction_id IS NULL
1310 AND rls.shipment_Line_Id=rsup.shipment_line_id))
1311 AND rls.lot_num = v_lot_number
1312 ))
1313 )
1314 AND (
1315 v_parent_txn_id_to_match IS NULL
1316 OR v_parent_txn_id_to_match = rsup.supply_source_id
1317 )
1318 AND (
1319 v_lpn_id_to_match IS NULL
1320 OR (rsup.lpn_id = v_lpn_id_to_match)
1321 )
1322 ORDER BY rt.transaction_date;
1323
1324 CURSOR count_int_req_delivery_lines(
1325 v_shipment_header_id NUMBER
1326 ,v_shipment_line_id NUMBER
1327 ,v_item_id NUMBER
1328 ,v_org_id NUMBER
1329 ,v_receipt_num VARCHAR2
1330 ,v_txn_date DATE
1331 ,v_inspection_status VARCHAR2
1332 ,v_lpn_id NUMBER
1333 ,v_lot_number VARCHAR2
1334 ,v_lpn_id_to_match NUMBER
1335 ,v_parent_txn_id_to_match NUMBER)
1336 IS
1337 SELECT COUNT(*)
1338 FROM rcv_supply rsup
1339 , rcv_transactions rt
1340 , rcv_shipment_headers rsh
1341 , rcv_shipment_lines rsl
1342 WHERE rsl.shipment_header_id = v_shipment_header_id
1343 AND rsl.shipment_line_id = Nvl(v_shipment_line_id,rsl.shipment_line_id)
1344 AND rsup.item_id = v_item_id
1345 AND Nvl(rsh.receipt_num,'@@@') = Nvl(v_receipt_num, Nvl(rsh.receipt_num,'@@@'))
1346 AND rt.transaction_date < Nvl(v_txn_date, (rt.transaction_date +1))
1347 AND (RT.ROUTING_HEADER_ID IS NULL OR
1348 RT.ROUTING_HEADER_ID <> 2 OR
1349 (rt.routing_header_id = 2
1350 AND rt.inspection_status_code <> 'NOT INSPECTED'
1351 AND rt.inspection_status_code = Nvl(v_inspection_status,rt.inspection_status_code)))
1352 --(RT.ROUTING_HEADER_ID = 2 AND rt.inspection_status_code <> 'NOT INSPECTED'))
1353 AND RSUP.SUPPLY_TYPE_CODE = 'RECEIVING'
1354 AND RSL.SHIPMENT_LINE_ID = RSUP.SHIPMENT_LINE_ID
1355 AND RT.TRANSACTION_ID = RSUP.RCV_TRANSACTION_ID
1356 AND RT.TRANSACTION_TYPE <> 'UNORDERED'
1357 -- for all the transactions in rt for which we can putaway, the
1358 -- transfer_lpn_id should match the lpn being putaway.
1359 --AND Nvl(rt.transfer_lpn_id,-1) = Nvl(v_lpn_id,-1)
1360 -- Fix for 1865886. Commented the above and added the following for lpn
1361 AND Nvl(v_lpn_id,-1) IN (select nvl(rt2.transfer_lpn_id,-1)
1362 from rcv_transactions rt2
1363 where rt2.transaction_type <> 'DELIVER'
1364 start with rt2.transaction_id = rsup.supply_source_id
1365 connect by prior transaction_id = parent_transaction_id
1366 union all
1367 select nvl(lpn_id,-1)
1368 from rcv_transactions
1369 /* Changes made for bug #4926987 -- added ACCEPT and REJECT */
1370 where transaction_type not in ('RECEIVE', 'DELIVER', 'ACCEPT', 'REJECT')
1371 /* End Changes made for bug #4926987 -- added ACCEPT and REJECT */
1372 start with transaction_id = rsup.supply_source_id
1373 connect by prior transaction_id = parent_transaction_id
1374 )
1375 AND RSUP.to_organization_id = v_org_id
1376 AND RSH.SHIPMENT_HEADER_ID = RSUP.shipment_header_id
1377 AND (
1378 v_lot_number IS NULL
1379 OR(
1380 exists (SELECT 1 FROM mtl_system_items
1381 WHERE inventory_item_id = v_item_id
1382 AND LOT_CONTROL_CODE = 1
1383 AND ORGANIZATION_ID = rsup.FROM_ORGANIZATION_ID )
1384 OR EXISTS
1385 (
1386 --Bug 13400589 Modified the below condition which will handle the case for non wms orgs.
1387 SELECT lot_num
1388 FROM rcv_lots_supply rls
1389 WHERE ( rls.transaction_id = rsup.supply_source_id
1390 OR (rls.transaction_id IS NULL
1391 AND rls.shipment_Line_Id=rsup.shipment_line_id))
1392 AND rls.lot_num = v_lot_number
1393 )
1394 )
1395 )
1396 AND (
1397 v_parent_txn_id_to_match IS NULL
1398 OR v_parent_txn_id_to_match = rsup.supply_source_id
1399 )
1400 AND (
1401 v_lpn_id_to_match IS NULL
1402 OR (rsup.lpn_id = v_lpn_id_to_match)
1403 ) ;
1404
1405 /* FP-J Lot/Serial Support Enhancement
1406 * Added two new arguments to the cursor, v_lot_number and v_lpn_id_to_match
1407 * Added conditions to match the lot number with that in RCV_LOTS_SUPPLY
1408 * and the LPN with that in RCV_SUPPLY for the parent trasnaction
1409 * This would be done only if WMS and PO patchset levels are J or higher
1410 */
1411 CURSOR rma_delivery_lines(
1412 v_oe_order_header_id NUMBER
1413 ,v_oe_order_line_id NUMBER
1414 ,v_item_id NUMBER
1415 ,v_org_id NUMBER
1416 ,v_receipt_num VARCHAR2
1417 ,v_txn_date DATE
1418 ,v_inspection_status VARCHAR2
1419 ,v_lpn_id NUMBER
1420 ,v_lot_number VARCHAR2
1421 ,v_lpn_id_to_match NUMBER
1422 ,v_parent_txn_id_to_match NUMBER)
1423 IS
1424 SELECT To_number(NULL)
1425 , rt.unit_of_measure
1426 -- Dont know if it is really correct, should be
1427 -- rsl.unit_of_measure but this actually IS
1428 -- rt.unit_of_measure....depends ON the transaction processor
1429 -- ON what it puts FOR this column.
1430 , rsup.item_id
1431 , rsh.receipt_source_code
1432 , rsup.to_organization_id
1433 , rsup.rcv_transaction_id
1434 , rsup.oe_order_line_id
1435 , To_number(NULL)
1436 , To_number(NULL)
1437 , To_number(NULL)
1438 , To_number(NULL)
1439 , To_char(NULL)
1440 , To_char(NULL)
1441 FROM rcv_supply rsup
1442 , rcv_transactions rt
1443 , rcv_shipment_headers rsh
1444 WHERE rsh.receipt_source_code = 'CUSTOMER'
1445 AND rsup.item_id = v_item_id
1446 AND rsup.oe_order_header_id = v_oe_order_header_id
1447 AND rsup.oe_order_line_id = Nvl(v_oe_order_line_id,Nvl(rsup.oe_order_line_id,-1))
1448 AND Nvl(rsh.receipt_num,'@@@') = Nvl(v_receipt_num, Nvl(rsh.receipt_num,'@@@'))
1449 AND rt.transaction_date < Nvl(v_txn_date, (rt.transaction_date + 1))
1450 AND (RT.ROUTING_HEADER_ID IS NULL OR
1451 RT.ROUTING_HEADER_ID <> 2 OR
1452 (rt.routing_header_id = 2
1453 AND rt.inspection_status_code <> 'NOT INSPECTED'
1454 AND rt.inspection_status_code = Nvl(v_inspection_status,rt.inspection_status_code)))
1455 --(RT.ROUTING_HEADER_ID = 2 AND rt.inspection_status_code <> 'NOT INSPECTED'))
1456 AND RSUP.SUPPLY_TYPE_CODE = 'RECEIVING'
1457 AND RT.TRANSACTION_ID = RSUP.RCV_TRANSACTION_ID
1458 AND RT.TRANSACTION_TYPE <> 'UNORDERED'
1459 -- for all the transactions in rt for which we can putaway, the
1460 -- transfer_lpn_id should match the lpn being putaway.
1461 -- AND Nvl(rt.transfer_lpn_id,-1) = Nvl(v_lpn_id,-1)
1462 -- Fix for 1865886. Commented the above and added the following for lpn
1463 AND Nvl(v_lpn_id,-1) IN (select nvl(rt2.transfer_lpn_id,-1)
1464 from rcv_transactions rt2
1465 where rt2.transaction_type <> 'DELIVER'
1466 start with rt2.transaction_id = rsup.supply_source_id
1467 connect by prior transaction_id = parent_transaction_id
1468 union all
1469 select nvl(lpn_id,-1)
1470 from rcv_transactions
1471 where transaction_type not in ('RECEIVE', 'DELIVER')
1472 start with transaction_id = rsup.supply_source_id
1473 connect by prior transaction_id = parent_transaction_id
1474 )
1475 AND RSUP.to_organization_id = v_org_id
1476 AND RSH.SHIPMENT_HEADER_ID = RSUP.shipment_header_id
1477 AND (
1478 v_lot_number IS NULL OR EXISTS
1479 (
1480 SELECT lot_num
1481 FROM rcv_lots_supply rls
1482 WHERE rls.transaction_id = rsup.supply_source_id
1483 AND rls.lot_num = v_lot_number
1484 )
1485 )
1486 AND (
1487 v_parent_txn_id_to_match IS NULL
1488 OR v_parent_txn_id_to_match = rsup.supply_source_id
1489 )
1490 AND (
1491 v_lpn_id_to_match IS NULL
1492 OR (rsup.lpn_id = v_lpn_id_to_match)
1493 )
1494 ORDER BY rt.transaction_date;
1495
1496 CURSOR count_rma_delivery_lines(
1497 v_oe_order_header_id NUMBER
1498 ,v_oe_order_line_id NUMBER
1499 ,v_item_id NUMBER
1500 ,v_org_id NUMBER
1501 ,v_receipt_num VARCHAR2
1502 ,v_txn_date DATE
1503 ,v_inspection_status VARCHAR2
1504 ,v_lpn_id NUMBER
1505 ,v_lot_number VARCHAR2
1506 ,v_lpn_id_to_match NUMBER
1507 ,v_parent_txn_id_to_match NUMBER)
1508 IS
1509 SELECT COUNT(*)
1510 FROM rcv_supply rsup
1511 , rcv_transactions rt
1512 , rcv_shipment_headers rsh
1513 WHERE rsh.receipt_source_code = 'CUSTOMER'
1514 AND rsup.item_id = v_item_id
1515 AND rsup.oe_order_header_id = v_oe_order_header_id
1516 AND rsup.oe_order_line_id = Nvl(v_oe_order_line_id,Nvl(rsup.oe_order_line_id,-1))
1517 AND Nvl(rsh.receipt_num,'@@@') = Nvl(v_receipt_num, Nvl(rsh.receipt_num,'@@@'))
1518 AND rt.transaction_date < Nvl(v_txn_date, (rt.transaction_date + 1))
1519 AND (RT.ROUTING_HEADER_ID IS NULL OR
1520 RT.ROUTING_HEADER_ID <> 2 OR
1521 (rt.routing_header_id = 2
1522 AND rt.inspection_status_code <> 'NOT INSPECTED'
1523 AND rt.inspection_status_code = Nvl(v_inspection_status,rt.inspection_status_code)))
1524 --(RT.ROUTING_HEADER_ID = 2 AND rt.inspection_status_code <> 'NOT INSPECTED'))
1525 AND RSUP.SUPPLY_TYPE_CODE = 'RECEIVING'
1526 AND RT.TRANSACTION_ID = RSUP.RCV_TRANSACTION_ID
1527 AND RT.TRANSACTION_TYPE <> 'UNORDERED'
1528 -- for all the transactions in rt for which we can putaway, the
1529 -- transfer_lpn_id should match the lpn being putaway.
1530 --AND Nvl(rt.transfer_lpn_id,-1) = Nvl(v_lpn_id,-1)
1531 -- Fix for 1865886. Commented the above and added the following for lpn
1532 AND Nvl(v_lpn_id,-1) IN (select nvl(rt2.transfer_lpn_id,-1)
1533 from rcv_transactions rt2
1534 where rt2.transaction_type <> 'DELIVER'
1535 start with rt2.transaction_id = rsup.supply_source_id
1536 connect by prior transaction_id = parent_transaction_id
1537 union all
1538 select nvl(lpn_id,-1)
1539 from rcv_transactions
1540 where transaction_type not in ('RECEIVE', 'DELIVER')
1541 start with transaction_id = rsup.supply_source_id
1542 connect by prior transaction_id = parent_transaction_id
1543 )
1544 AND RSUP.to_organization_id = v_org_id
1545 AND RSH.SHIPMENT_HEADER_ID = RSUP.shipment_header_id
1546 AND (
1547 v_lot_number IS NULL OR EXISTS
1548 (
1549 SELECT lot_num
1550 FROM rcv_lots_supply rls
1551 WHERE rls.transaction_id = rsup.supply_source_id
1552 AND rls.lot_num = v_lot_number
1553 )
1554 )
1555 AND (
1556 v_parent_txn_id_to_match IS NULL
1557 OR v_parent_txn_id_to_match = rsup.supply_source_id
1558 )
1559 AND (
1560 v_lpn_id_to_match IS NULL
1561 OR (rsup.lpn_id = v_lpn_id_to_match)
1562 ) ;
1563
1564 CURSOR asn_delivery_lines(
1565 v_shipment_header_id NUMBER
1566 ,v_shipment_line_id NUMBER
1567 ,v_item_id NUMBER
1568 ,v_org_id NUMBER
1569 ,v_receipt_num VARCHAR2
1570 ,v_txn_date DATE
1571 ,v_inspection_status VARCHAR2
1572 ,v_lpn_id NUMBER
1573 ,v_lot_number VARCHAR2
1574 ,v_lpn_id_to_match NUMBER
1575 ,v_parent_transaction_id NUMBER) -- Added for bug# 9879753
1576 IS
1577 SELECT rsup.shipment_line_id
1578 , rt.unit_of_measure
1579 -- Dont know if it is really correct, should be
1580 -- rsl.unit_of_measure but this actually IS
1581 -- rt.unit_of_measure....depends ON the transaction processor
1582 -- ON what it puts FOR this column.
1583 , rsup.item_id
1584 , rsh.receipt_source_code
1585 , rsup.to_organization_id
1586 , rsup.rcv_transaction_id
1587 , To_number(NULL) oe_order_line_id
1588 , rsup.po_header_id po_header_id
1589 , rsup.po_line_id po_line_id
1590 , rsup.po_line_location_id po_line_location_id
1591 , pod.po_distribution_id po_distribution_id
1592 , rsl.item_description item_description
1593 , to_char(null)
1594 FROM rcv_supply rsup
1595 , rcv_transactions rt
1596 , rcv_shipment_headers rsh
1597 , rcv_shipment_lines rsl
1598 , po_line_locations poll
1599 , po_distributions pod
1600 WHERE rsl.shipment_header_id = v_shipment_header_id
1601 AND rsl.shipment_line_id = Nvl(v_shipment_line_id,rsl.shipment_line_id)
1602 AND rsup.item_id = v_item_id
1603 AND rsup.po_header_id = pod.po_header_id
1604 AND rsup.PO_LINE_ID = pod.PO_LINE_ID
1605 AND rsup.PO_LINE_LOCATION_ID = pod.LINE_LOCATION_ID
1606 AND rsup.po_header_id = poll.po_header_id
1607 AND rsup.PO_LINE_ID = poll.PO_LINE_ID
1608 AND rsup.PO_LINE_LOCATION_ID = poll.LINE_LOCATION_ID
1609 and NVL(poll.APPROVED_FLAG,'N') = 'Y'
1610 and NVL(poll.CANCEL_FLAG, 'N') = 'N'
1611 and NVL(poll.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED'
1612 and poll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
1613 AND rt.transaction_date < Nvl(v_txn_date, (rt.transaction_date +1))
1614 AND (RT.ROUTING_HEADER_ID IS NULL OR
1615 RT.ROUTING_HEADER_ID <> 2 OR
1616 (rt.routing_header_id = 2
1617 AND rt.inspection_status_code <> 'NOT INSPECTED'
1618 AND rt.inspection_status_code = Nvl(v_inspection_status,rt.inspection_status_code)))
1619 --(RT.ROUTING_HEADER_ID = 2 AND rt.inspection_status_code <> 'NOT INSPECTED'))
1620 AND RSUP.SUPPLY_TYPE_CODE = 'RECEIVING'
1621 AND RSL.SHIPMENT_LINE_ID = RSUP.SHIPMENT_LINE_ID
1622 AND RT.TRANSACTION_ID = RSUP.RCV_TRANSACTION_ID
1623 AND RT.TRANSACTION_TYPE <> 'UNORDERED'
1624 -- for all the transactions in rt for which we can putaway, the
1625 -- transfer_lpn_id should match the lpn being putaway.
1626 --AND Nvl(rt.transfer_lpn_id,-1) = Nvl(v_lpn_id,-1)
1627 -- Fix for 1865886. Commented the above and added the following for lpn
1628 AND Nvl(v_lpn_id,-1) IN (select nvl(rt2.transfer_lpn_id,-1)
1629 from rcv_transactions rt2
1630 where rt2.transaction_type <> 'DELIVER'
1631 start with rt2.transaction_id = rsup.supply_source_id
1632 connect by prior rt2.transaction_id = rt2.parent_transaction_id
1633 union all
1634 select nvl(rt2.lpn_id,-1)
1635 from rcv_transactions rt2
1636 where rt2.transaction_type not in ('RECEIVE', 'DELIVER')
1637 start with rt2.transaction_id = rsup.supply_source_id
1638 connect by prior rt2.transaction_id = rt2.parent_transaction_id
1639 )
1640 AND RSUP.to_organization_id = v_org_id
1641 AND RSH.SHIPMENT_HEADER_ID = RSUP.SHIPMENT_HEADER_ID
1642 AND RT.TRANSACTION_ID = NVL(v_parent_transaction_id, rt.transaction_id) -- 9879753
1643 AND (
1644 v_lot_number IS NULL OR EXISTS
1645 (
1646 SELECT lot_num
1647 FROM rcv_lots_supply rls
1648 WHERE rls.transaction_id = rsup.supply_source_id
1649 AND rls.lot_num = v_lot_number
1650 )
1651 )
1652 AND (
1653 v_lpn_id_to_match IS NULL
1654 OR (rsup.lpn_id = v_lpn_id_to_match)
1655 )
1656 ORDER BY rt.transaction_date,
1657 rsup.rcv_transaction_id; -- Added for bug# 8931640
1658
1659
1660 CURSOR count_asn_delivery_lines(
1661 v_shipment_header_id NUMBER
1662 ,v_shipment_line_id NUMBER
1663 ,v_item_id NUMBER
1664 ,v_org_id NUMBER
1665 ,v_receipt_num VARCHAR2
1666 ,v_txn_date DATE
1667 ,v_inspection_status VARCHAR2
1668 ,v_lpn_id NUMBER
1669 ,v_lot_number VARCHAR2
1670 ,v_lpn_id_to_match NUMBER
1671 ,v_parent_transaction_id NUMBER) -- Added for bug# 9879753
1672
1673 IS
1674 SELECT COUNT(*)
1675 FROM rcv_supply rsup
1676 , rcv_transactions rt
1677 , rcv_shipment_headers rsh
1678 , rcv_shipment_lines rsl
1679 , po_line_locations poll
1680 , po_distributions pod
1681 WHERE rsl.shipment_header_id = v_shipment_header_id
1682 AND rsl.shipment_line_id = Nvl(v_shipment_line_id,rsl.shipment_line_id)
1683 AND rsup.item_id = v_item_id
1684 AND rsup.po_header_id = pod.po_header_id
1685 AND rsup.PO_LINE_ID = pod.PO_LINE_ID
1686 AND rsup.PO_LINE_LOCATION_ID = pod.LINE_LOCATION_ID
1687 AND rsup.po_header_id = poll.po_header_id
1688 AND rsup.PO_LINE_ID = poll.PO_LINE_ID
1689 AND rsup.PO_LINE_LOCATION_ID = poll.LINE_LOCATION_ID
1690 and NVL(poll.APPROVED_FLAG,'N') = 'Y'
1691 and NVL(poll.CANCEL_FLAG, 'N') = 'N'
1692 and NVL(poll.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED'
1693 and poll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
1694 AND rt.transaction_date < Nvl(v_txn_date, (rt.transaction_date +1))
1695 AND (RT.ROUTING_HEADER_ID IS NULL OR
1696 RT.ROUTING_HEADER_ID <> 2 OR
1697 (rt.routing_header_id = 2
1698 AND rt.inspection_status_code <> 'NOT INSPECTED'
1699 AND rt.inspection_status_code = Nvl(v_inspection_status,rt.inspection_status_code)))
1700 --(RT.ROUTING_HEADER_ID = 2 AND rt.inspection_status_code <> 'NOT INSPECTED'))
1701 AND RSUP.SUPPLY_TYPE_CODE = 'RECEIVING'
1702 AND RSL.SHIPMENT_LINE_ID = RSUP.SHIPMENT_LINE_ID
1703 AND RT.TRANSACTION_ID = RSUP.RCV_TRANSACTION_ID
1704 AND RT.TRANSACTION_TYPE <> 'UNORDERED'
1705 -- for all the transactions in rt for which we can putaway, the
1706 -- transfer_lpn_id should match the lpn being putaway.
1707 --AND Nvl(rt.transfer_lpn_id,-1) = Nvl(v_lpn_id,-1)
1708 -- Fix for 1865886. Commented the above and added the following for lpn
1709 AND Nvl(v_lpn_id,-1) IN (select nvl(rt2.transfer_lpn_id,-1)
1710 from rcv_transactions rt2
1711 where rt2.transaction_type <> 'DELIVER'
1712 start with rt2.transaction_id = rsup.supply_source_id
1713 connect by prior rt2.transaction_id = rt2.parent_transaction_id
1714 union all
1715 select nvl(rt2.lpn_id,-1)
1716 from rcv_transactions rt2
1717 where rt2.transaction_type not in ('RECEIVE', 'DELIVER')
1718 start with rt2.transaction_id = rsup.supply_source_id
1719 connect by prior rt2.transaction_id = rt2.parent_transaction_id
1720 )
1721 AND RSUP.to_organization_id = v_org_id
1722 AND RSH.SHIPMENT_HEADER_ID = RSUP.SHIPMENT_HEADER_ID
1723 AND RT.TRANSACTION_ID = NVL(v_parent_transaction_id, rt.transaction_id) -- 9879753
1724 AND (
1725 v_lot_number IS NULL OR EXISTS
1726 (
1727 SELECT lot_num
1728 FROM rcv_lots_supply rls
1729 WHERE rls.transaction_id = rsup.supply_source_id
1730 AND rls.lot_num = v_lot_number
1731 )
1732 )
1733 AND (
1734 v_lpn_id_to_match IS NULL
1735 OR (rsup.lpn_id = v_lpn_id_to_match)
1736 )
1737 ORDER BY rt.transaction_date;
1738
1739 -- For Bug 7440217 Cursors defined
1740 CURSOR lcm_delivery_lines(
1741 v_shipment_header_id NUMBER
1742 ,v_shipment_line_id NUMBER
1743 ,v_item_id NUMBER
1744 ,v_org_id NUMBER
1745 ,v_receipt_num VARCHAR2
1746 ,v_txn_date DATE
1747 ,v_inspection_status VARCHAR2
1748 ,v_lpn_id NUMBER
1749 ,v_lot_number VARCHAR2
1750 ,v_lpn_id_to_match NUMBER)
1751 IS
1752 SELECT rsup.shipment_line_id
1753 , rt.unit_of_measure
1754 -- Dont know if it is really correct, should be
1755 -- rsl.unit_of_measure but this actually IS
1756 -- rt.unit_of_measure....depends ON the transaction processor
1757 -- ON what it puts FOR this column.
1758 , rsup.item_id
1759 , rsh.receipt_source_code
1760 , rsup.to_organization_id
1761 , rsup.rcv_transaction_id
1762 , To_number(NULL) oe_order_line_id
1763 , rsup.po_header_id po_header_id
1764 , rsup.po_line_id po_line_id
1765 , rsup.po_line_location_id po_line_location_id
1766 , pod.po_distribution_id po_distribution_id
1767 , rsl.item_description item_description
1768 , to_char(null)
1769 FROM rcv_supply rsup
1770 , rcv_transactions rt
1771 , rcv_shipment_headers rsh
1772 , rcv_shipment_lines rsl
1773 , po_line_locations poll
1774 , po_distributions pod
1775 WHERE rsl.shipment_header_id = v_shipment_header_id
1776 AND rsl.shipment_line_id = Nvl(v_shipment_line_id,rsl.shipment_line_id)
1777 AND rsup.item_id = v_item_id
1778 AND rsup.po_header_id = pod.po_header_id
1779 AND rsup.PO_LINE_ID = pod.PO_LINE_ID
1780 AND rsup.PO_LINE_LOCATION_ID = pod.LINE_LOCATION_ID
1781 AND rsup.po_header_id = poll.po_header_id
1782 AND rsup.PO_LINE_ID = poll.PO_LINE_ID
1783 AND rsup.PO_LINE_LOCATION_ID = poll.LINE_LOCATION_ID
1784 and NVL(poll.APPROVED_FLAG,'N') = 'Y'
1785 and NVL(poll.CANCEL_FLAG, 'N') = 'N'
1786 and NVL(poll.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED'
1787 and poll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
1788 AND rt.transaction_date < Nvl(v_txn_date, (rt.transaction_date +1))
1789 AND (RT.ROUTING_HEADER_ID IS NULL OR
1790 RT.ROUTING_HEADER_ID <> 2 OR
1791 (rt.routing_header_id = 2
1792 AND rt.inspection_status_code <> 'NOT INSPECTED'
1793 AND rt.inspection_status_code = Nvl(v_inspection_status,rt.inspection_status_code)))
1794 --(RT.ROUTING_HEADER_ID = 2 AND rt.inspection_status_code <> 'NOT INSPECTED'))
1795 AND RSUP.SUPPLY_TYPE_CODE = 'RECEIVING'
1796 AND RSL.SHIPMENT_LINE_ID = RSUP.SHIPMENT_LINE_ID
1797 AND RT.TRANSACTION_ID = RSUP.RCV_TRANSACTION_ID
1798 AND RT.TRANSACTION_TYPE <> 'UNORDERED'
1799 -- for all the transactions in rt for which we can putaway, the
1800 -- transfer_lpn_id should match the lpn being putaway.
1801 --AND Nvl(rt.transfer_lpn_id,-1) = Nvl(v_lpn_id,-1)
1802 -- Fix for 1865886. Commented the above and added the following for lpn
1803 AND Nvl(v_lpn_id,-1) IN (select nvl(rt2.transfer_lpn_id,-1)
1804 from rcv_transactions rt2
1805 where rt2.transaction_type <> 'DELIVER'
1806 start with rt2.transaction_id = rsup.supply_source_id
1807 connect by prior rt2.transaction_id = rt2.parent_transaction_id
1808 union all
1809 select nvl(rt2.lpn_id,-1)
1810 from rcv_transactions rt2
1811 where rt2.transaction_type not in ('RECEIVE', 'DELIVER')
1812 start with rt2.transaction_id = rsup.supply_source_id
1813 connect by prior rt2.transaction_id = rt2.parent_transaction_id
1814 )
1815 AND RSUP.to_organization_id = v_org_id
1816 AND RSH.SHIPMENT_HEADER_ID = RSUP.SHIPMENT_HEADER_ID
1817 AND (
1818 v_lot_number IS NULL OR EXISTS
1819 (
1820 SELECT lot_num
1821 FROM rcv_lots_supply rls
1822 WHERE rls.transaction_id = rsup.supply_source_id
1823 AND rls.lot_num = v_lot_number
1824 )
1825 )
1826 AND (
1827 v_lpn_id_to_match IS NULL
1828 OR (rsup.lpn_id = v_lpn_id_to_match)
1829 )
1830 ORDER BY rt.transaction_date;
1831
1832
1833 CURSOR count_lcm_delivery_lines(
1834 v_shipment_header_id NUMBER
1835 ,v_shipment_line_id NUMBER
1836 ,v_item_id NUMBER
1837 ,v_org_id NUMBER
1838 ,v_receipt_num VARCHAR2
1839 ,v_txn_date DATE
1840 ,v_inspection_status VARCHAR2
1841 ,v_lpn_id NUMBER
1842 ,v_lot_number VARCHAR2
1843 ,v_lpn_id_to_match NUMBER)
1844 IS
1845 SELECT COUNT(*)
1846 FROM rcv_supply rsup
1847 , rcv_transactions rt
1848 , rcv_shipment_headers rsh
1849 , rcv_shipment_lines rsl
1850 , po_line_locations poll
1851 , po_distributions pod
1852 WHERE rsl.shipment_header_id = v_shipment_header_id
1853 AND rsl.shipment_line_id = Nvl(v_shipment_line_id,rsl.shipment_line_id)
1854 AND rsup.item_id = v_item_id
1855 AND rsup.po_header_id = pod.po_header_id
1856 AND rsup.PO_LINE_ID = pod.PO_LINE_ID
1857 AND rsup.PO_LINE_LOCATION_ID = pod.LINE_LOCATION_ID
1858 AND rsup.po_header_id = poll.po_header_id
1859 AND rsup.PO_LINE_ID = poll.PO_LINE_ID
1860 AND rsup.PO_LINE_LOCATION_ID = poll.LINE_LOCATION_ID
1861 and NVL(poll.APPROVED_FLAG,'N') = 'Y'
1862 and NVL(poll.CANCEL_FLAG, 'N') = 'N'
1863 and NVL(poll.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED'
1864 and poll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
1865 AND rt.transaction_date < Nvl(v_txn_date, (rt.transaction_date +1))
1866 AND (RT.ROUTING_HEADER_ID IS NULL OR
1867 RT.ROUTING_HEADER_ID <> 2 OR
1868 (rt.routing_header_id = 2
1869 AND rt.inspection_status_code <> 'NOT INSPECTED'
1870 AND rt.inspection_status_code = Nvl(v_inspection_status,rt.inspection_status_code)))
1871 --(RT.ROUTING_HEADER_ID = 2 AND rt.inspection_status_code <> 'NOT INSPECTED'))
1872 AND RSUP.SUPPLY_TYPE_CODE = 'RECEIVING'
1873 AND RSL.SHIPMENT_LINE_ID = RSUP.SHIPMENT_LINE_ID
1874 AND RT.TRANSACTION_ID = RSUP.RCV_TRANSACTION_ID
1875 AND RT.TRANSACTION_TYPE <> 'UNORDERED'
1876 -- for all the transactions in rt for which we can putaway, the
1877 -- transfer_lpn_id should match the lpn being putaway.
1878 --AND Nvl(rt.transfer_lpn_id,-1) = Nvl(v_lpn_id,-1)
1879 -- Fix for 1865886. Commented the above and added the following for lpn
1880 AND Nvl(v_lpn_id,-1) IN (select nvl(rt2.transfer_lpn_id,-1)
1881 from rcv_transactions rt2
1882 where rt2.transaction_type <> 'DELIVER'
1883 start with rt2.transaction_id = rsup.supply_source_id
1884 connect by prior rt2.transaction_id = rt2.parent_transaction_id
1885 union all
1886 select nvl(rt2.lpn_id,-1)
1887 from rcv_transactions rt2
1888 where rt2.transaction_type not in ('RECEIVE', 'DELIVER')
1889 start with rt2.transaction_id = rsup.supply_source_id
1890 connect by prior rt2.transaction_id = rt2.parent_transaction_id
1891 )
1892 AND RSUP.to_organization_id = v_org_id
1893 AND RSH.SHIPMENT_HEADER_ID = RSUP.SHIPMENT_HEADER_ID
1894 AND (
1895 v_lot_number IS NULL OR EXISTS
1896 (
1897 SELECT lot_num
1898 FROM rcv_lots_supply rls
1899 WHERE rls.transaction_id = rsup.supply_source_id
1900 AND rls.lot_num = v_lot_number
1901 )
1902 )
1903 AND (
1904 v_lpn_id_to_match IS NULL
1905 OR (rsup.lpn_id = v_lpn_id_to_match)
1906 )
1907 ORDER BY rt.transaction_date;
1908 -- End for Bug 7440217
1909
1910
1911 x_MatchedRec int_req_delivery_lines%ROWTYPE;
1912 x_record_count NUMBER;
1913
1914 x_remaining_quantity NUMBER := 0;
1915 x_remaining_qty_po_uom NUMBER := 0;
1916 x_bkp_qty NUMBER := 0;
1917 x_progress VARCHAR2(3);
1918 x_converted_trx_qty NUMBER := 0;
1919 transaction_ok BOOLEAN := FALSE;
1920 x_expected_date rcv_transactions_interface.expected_receipt_date%TYPE;
1921 high_range_date DATE;
1922 low_range_date DATE;
1923 rows_fetched NUMBER := 0;
1924 x_tolerable_qty NUMBER := 0;
1925 x_first_trans BOOLEAN := TRUE;
1926 x_sysdate DATE := Sysdate;
1927 current_n BINARY_INTEGER := 0;
1928 insert_into_table BOOLEAN := FALSE;
1929 x_qty_rcv_exception_code po_line_locations.qty_rcv_exception_code%type;
1930 tax_amount_factor NUMBER;
1931 lastrecord BOOLEAN := FALSE;
1932
1933 po_asn_uom_qty NUMBER;
1934 po_primary_uom_qty NUMBER;
1935
1936 already_allocated_qty NUMBER := 0;
1937
1938 x_item_id NUMBER;
1939 x_approved_flag VARCHAR(1);
1940 x_cancel_flag VARCHAR(1);
1941 x_closed_code VARCHAR(25);
1942 x_shipment_type VARCHAR(25);
1943 x_ship_to_organization_id NUMBER;
1944 x_ship_to_location_id NUMBER;
1945 x_vendor_product_num VARCHAR(25);
1946 x_temp_count NUMBER;
1947
1948 x_under_return_tolerance NUMBER;
1949 x_qty_rcv_tolerance NUMBER;
1950 x_oe_line_qty NUMBER;
1951
1952 l_parent_id NUMBER;
1953 l_receipt_source_code VARCHAR2(14);
1954 l_return_status VARCHAR2(1);
1955 l_msg_count NUMBER;
1956 l_msg_data VARCHAR2(400);
1957 l_sh_result_count NUMBER;
1958 l_rma_uom VARCHAR2(25); --bug3592116
1959
1960 l_api_name CONSTANT VARCHAR2(30) := 'matching_logic';
1961
1962 l_err_message VARCHAR2(100);
1963 l_temp_message VARCHAR2(100);
1964 l_msg_prod VARCHAR2(5);
1965
1966 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1967
1968 --New variables for FP-J Lot/Serial Support
1969 l_lot_number_to_match mtl_lot_numbers.lot_number%TYPE;
1970 l_lpn_id_to_match NUMBER;
1971 l_parent_txn_id_to_match NUMBER;
1972 l_passed_parent_txn_id NUMBER;
1973 l_wms_po_j_or_higher BOOLEAN;
1974
1975 l_patch_j_or_higher VARCHAR2(3) := 'N';
1976 --Bug 12720051
1977 l_rsl_qty NUMBER;
1978 l_rsl_uom rcv_shipment_lines.unit_of_measure%type;
1979 l_pre_rcv_flag VARCHAR2(1);
1980
1981 --Bug 4004656-Added the local variables for the quantity fields.
1982 l_rem_qty_trans_uom NUMBER := 0; -- Remaining quantity to be received in transaction uom
1983 l_rcv_qty_trans_uom NUMBER := 0; -- Quantity received in transaction uom
1984 l_rcv_qty_po_uom NUMBER := 0; -- Quantity received in uom of the po.
1985 l_bkp_qty_trans_uom NUMBER := 0;
1986 l_trx_qty_po_uom NUMBER := 0; -- Transaction quantity in the uom of the po.
1987 l_trx_qty_trans_uom NUMBER := 0; -- Transaction quantity in the transaction uom.
1988 l_tol_qty_po_uom NUMBER := 0; -- Tolerable quantity in the uom of the po.
1989 --End of fix for Bug 4004656
1990
1991 l_rcv_tolerance_qty_rma_uom NUMBER := 0; --Bug 4747997: Tolerance qty in RMA UOM
1992 l_rcv_tolerance_qty_txn_uom NUMBER := 0; --Bug 4747997: Tolerance qty in transaction UOM
1993
1994
1995 BEGIN
1996 IF (l_debug = 1) THEN
1997 print_debug('Enter matching_logic: 1 ' || to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
1998 print_debug('p_receipt_num = ' || p_receipt_num, 4);
1999 print_debug('p_match_type = ' || p_match_type, 4);
2000 print_debug('p_lpn_id = ' || p_lpn_id, 4);
2001 print_debug('n = ' || n, 4);
2002 print_debug('item_id = ' || x_cascaded_table(n).item_id, 4);
2003 print_debug('item_desc = ' || x_cascaded_table(n).item_desc, 4);
2004 print_debug('shipment_header_id = ' || x_cascaded_table(n).shipment_header_id, 4);
2005 print_debug('oe_order_header_id = ' || x_cascaded_table(n).oe_order_header_id, 4);
2006 print_debug('trasnsaction_type = ' || x_cascaded_table(n).transaction_type, 4);
2007 print_debug('error_status = ' || x_cascaded_table(n).error_status, 4);
2008 print_debug('project id = ' || x_cascaded_table(n).project_id);
2009 print_debug('task id = ' || x_cascaded_table(n).task_id);
2010 print_debug('parent txn id = ' || x_cascaded_table(n).parent_transaction_id);
2011 END IF;
2012
2013 x_return_status := fnd_api.g_ret_sts_success;
2014
2015 SAVEPOINT rcv_transactions_gen_sa;
2016
2017 -- storing the passed value to match with the parent_txn_id in a local
2018 -- var and nulling out the variable in the record to simulate the
2019 -- behavior as was prior to patchset J changes.
2020 l_passed_parent_txn_id := x_cascaded_table(n).parent_transaction_id;
2021 x_cascaded_table(n).parent_transaction_id := NULL;
2022
2023 /* FP-J Lot/Serial Support Enhancement
2024 * Read the currentand PO patch levels and set the flag (that would be used to
2025 * match the Lot Number and the LPN) accordingly
2026 */
2027 IF ((inv_rcv_common_apis.g_wms_patch_level >= inv_rcv_common_apis.g_patchset_j) AND
2028 (inv_rcv_common_apis.g_po_patch_level >= inv_rcv_common_apis.g_patchset_j_po)) THEN
2029 l_wms_po_j_or_higher := TRUE;
2030 l_patch_j_or_higher := 'Y';
2031 IF (l_debug = 1) THEN
2032 print_debug('WMS and PO patch levels are J or higher', 4);
2033 END IF;
2034 ELSE
2035 l_wms_po_j_or_higher := FALSE;
2036 l_patch_j_or_higher := 'N';
2037 IF (l_debug = 1) THEN
2038 print_debug('Either WMS or/and PO patch level(s) are lower than J', 4);
2039 END IF;
2040 END IF;
2041
2042 -- the following steps will create a set of rows linking the line_record with
2043 -- its corresponding po_line_location rows until the quantity value from
2044 -- the asn is consumed. (Cascade)
2045 IF (((x_cascaded_table(n).shipment_header_id IS NOT NULL) OR
2046 (x_cascaded_table(n).oe_order_header_id IS NOT NULL)) AND
2047 ((x_cascaded_table(n).item_id IS NOT NULL OR
2048 (x_cascaded_table(n).item_desc IS NOT NULL
2049 -- For Bug 7440217 Added LCM Doc type
2050 AND p_match_type in ('ASN', 'LCM')))) AND
2051 -- End for Bug 7440217
2052 (x_cascaded_table(n).error_status IN ('S','W'))) THEN
2053
2054 -- Copy record from main table to temp table
2055
2056 current_n := 1;
2057 temp_cascaded_table(current_n) := x_cascaded_table(n);
2058 IF (l_debug = 1) THEN
2059 print_debug('copied rec from main table to temp table', 4);
2060 END IF;
2061
2062 -- Get all rows which meet this condition
2063 IF (x_cascaded_table(n).transaction_type = 'RECEIVE')
2064 OR (x_cascaded_table(n).transaction_type = 'DELIVER') THEN
2065 IF (l_debug = 1) THEN
2066 print_debug('Transaction type is receive or deliver with match type:'||p_match_type||':txn_type:'||x_cascaded_table(n).transaction_type, 4);
2067 END IF;
2068
2069 l_progress := '20';
2070 IF p_match_type = 'ASN' THEN
2071 IF (l_debug = 1) THEN
2072 print_debug('parameters for Opening for ASN', 4);
2073 print_debug('shipment_header_id'||temp_cascaded_table(current_n).shipment_header_id,4);
2074 print_debug('shipment_line_id'||temp_cascaded_table(current_n).shipment_line_id, 4);
2075 print_debug('item_id'||temp_cascaded_table(current_n).item_id, 4);
2076 print_debug('po_header_id'||temp_cascaded_table(current_n).po_header_id, 4);
2077 print_debug('p_lpn_id'||p_lpn_id, 4);
2078 print_debug('item_desc'||temp_cascaded_table(current_n).item_desc, 4);
2079 print_debug('unit_of_measure'||temp_cascaded_table(current_n).unit_of_measure, 4); --14776842
2080 END IF;
2081 IF (x_cascaded_table(n).transaction_type = 'RECEIVE') THEN
2082 IF (l_debug = 1) THEN
2083 print_debug('Opening for ASN standard receipt', 4);
2084 END IF;
2085 OPEN asn_receipt_lines
2086 (temp_cascaded_table(current_n).shipment_header_id,
2087 temp_cascaded_table(current_n).shipment_line_id,
2088 temp_cascaded_table(current_n).item_id,
2089 temp_cascaded_table(current_n).po_header_id,
2090 p_lpn_id,
2091 temp_cascaded_table(current_n).item_desc,
2092 temp_cascaded_table(current_n).project_id,
2093 temp_cascaded_table(current_n).task_id,
2094 temp_cascaded_table(current_n).lot_number, -- Added for Bug 12732516
2095 l_patch_j_or_higher,
2096 temp_cascaded_table(current_n).revision, --Bug 13598673
2097 temp_cascaded_table(current_n).unit_of_measure); --14776842
2098
2099
2100 OPEN count_asn_receipt_lines
2101 (temp_cascaded_table(current_n).shipment_header_id,
2102 temp_cascaded_table(current_n).shipment_line_id,
2103 temp_cascaded_table(current_n).item_id,
2104 temp_cascaded_table(current_n).po_header_id,
2105 p_lpn_id,
2106 temp_cascaded_table(current_n).item_desc,
2107 temp_cascaded_table(current_n).project_id,
2108 temp_cascaded_table(current_n).task_id,
2109 temp_cascaded_table(current_n).lot_number, -- Added for Bug 12732516
2110 l_patch_j_or_higher,
2111 temp_cascaded_table(current_n).revision); --Bug 13598673
2112 ELSE -- x_cascaded_table(n).transaction_type = 'DELIVER'
2113 IF (l_debug = 1) THEN
2114 print_debug('Opening for ASN direct receipt', 4);
2115 END IF;
2116 OPEN asn_direct_lines
2117 (temp_cascaded_table(current_n).shipment_header_id,
2118 temp_cascaded_table(current_n).shipment_line_id,
2119 temp_cascaded_table(current_n).item_id,
2120 temp_cascaded_table(current_n).po_header_id,
2121 p_lpn_id,
2122 temp_cascaded_table(current_n).item_desc,
2123 temp_cascaded_table(current_n).project_id,
2124 temp_cascaded_table(current_n).task_id,
2125 l_patch_j_or_higher,
2126 temp_cascaded_table(current_n).unit_of_measure); --14776842
2127
2128 OPEN count_asn_direct_lines
2129 (temp_cascaded_table(current_n).shipment_header_id,
2130 temp_cascaded_table(current_n).shipment_line_id,
2131 temp_cascaded_table(current_n).item_id,
2132 temp_cascaded_table(current_n).po_header_id,
2133 p_lpn_id,
2134 temp_cascaded_table(current_n).item_desc,
2135 temp_cascaded_table(current_n).project_id,
2136 temp_cascaded_table(current_n).task_id,
2137 l_patch_j_or_higher);
2138 END IF;
2139
2140 -- For Bug 7440217 Code for Doc type LCM
2141 ELSIF p_match_type = 'LCM' THEN
2142 IF (l_debug = 1) THEN
2143 print_debug('parameters for Opening for LCM', 4);
2144 print_debug('shipment_header_id'||temp_cascaded_table(current_n).shipment_header_id,4);
2145 print_debug('shipment_line_id'||temp_cascaded_table(current_n).shipment_line_id, 4);
2146 print_debug('item_id'||temp_cascaded_table(current_n).item_id, 4);
2147 print_debug('po_header_id'||temp_cascaded_table(current_n).po_header_id, 4);
2148 print_debug('p_lpn_id'||p_lpn_id, 4);
2149 print_debug('item_desc'||temp_cascaded_table(current_n).item_desc, 4);
2150 END IF;
2151 IF (x_cascaded_table(n).transaction_type = 'RECEIVE') THEN
2152 IF (l_debug = 1) THEN
2153 print_debug('Opening for LCM standard receipt', 4);
2154 END IF;
2155 OPEN lcm_receipt_lines
2156 (temp_cascaded_table(current_n).shipment_header_id,
2157 temp_cascaded_table(current_n).shipment_line_id,
2158 temp_cascaded_table(current_n).item_id,
2159 temp_cascaded_table(current_n).po_header_id,
2160 p_lpn_id,
2161 temp_cascaded_table(current_n).item_desc,
2162 temp_cascaded_table(current_n).project_id,
2163 temp_cascaded_table(current_n).task_id,
2164 l_patch_j_or_higher);
2165
2166 OPEN count_lcm_receipt_lines
2167 (temp_cascaded_table(current_n).shipment_header_id,
2168 temp_cascaded_table(current_n).shipment_line_id,
2169 temp_cascaded_table(current_n).item_id,
2170 temp_cascaded_table(current_n).po_header_id,
2171 p_lpn_id,
2172 temp_cascaded_table(current_n).item_desc,
2173 temp_cascaded_table(current_n).project_id,
2174 temp_cascaded_table(current_n).task_id,
2175 l_patch_j_or_higher);
2176 ELSE -- x_cascaded_table(n).transaction_type = 'DELIVER'
2177 IF (l_debug = 1) THEN
2178 print_debug('Opening for LCM direct receipt', 4);
2179 END IF;
2180 OPEN lcm_direct_lines
2181 (temp_cascaded_table(current_n).shipment_header_id,
2182 temp_cascaded_table(current_n).shipment_line_id,
2183 temp_cascaded_table(current_n).item_id,
2184 temp_cascaded_table(current_n).po_header_id,
2185 p_lpn_id,
2186 temp_cascaded_table(current_n).item_desc,
2187 temp_cascaded_table(current_n).project_id,
2188 temp_cascaded_table(current_n).task_id,
2189 l_patch_j_or_higher);
2190
2191 OPEN count_lcm_direct_lines
2192 (temp_cascaded_table(current_n).shipment_header_id,
2193 temp_cascaded_table(current_n).shipment_line_id,
2194 temp_cascaded_table(current_n).item_id,
2195 temp_cascaded_table(current_n).po_header_id,
2196 p_lpn_id,
2197 temp_cascaded_table(current_n).item_desc,
2198 temp_cascaded_table(current_n).project_id,
2199 temp_cascaded_table(current_n).task_id,
2200 l_patch_j_or_higher);
2201 END IF;
2202 -- End for Bug 7440217
2203
2204 ELSIF p_match_type = 'INTRANSIT SHIPMENT' THEN
2205 l_progress := '30';
2206 IF (l_debug = 1) THEN
2207 print_debug('Opening cursor in matching for intransit shipments FOR parameters:',4);
2208 print_debug('shipment_header_id:'||temp_cascaded_table(current_n).shipment_header_id,4);
2209 print_debug('shipment_line_id:'||temp_cascaded_table(current_n).shipment_line_id,4);
2210 print_debug('item_id:'||temp_cascaded_table(current_n).item_id,4);
2211 print_debug('to_organization_id:'||temp_cascaded_table(current_n).to_organization_id,4);
2212 print_debug('expected_receipt_date:'||temp_cascaded_table(current_n).expected_receipt_date,4);
2213 print_debug('p_lpn_id'||p_lpn_id, 4);
2214 print_debug('serial_number'||temp_cascaded_table(current_n).serial_number,4);
2215 print_debug('unit_of_measure '||temp_cascaded_table(current_n).unit_of_measure,4); --14776842
2216 END IF;
2217 OPEN int_req_receipt_lines
2218 (temp_cascaded_table(current_n).shipment_header_id,
2219 temp_cascaded_table(current_n).shipment_line_id,
2220 temp_cascaded_table(current_n).item_id,
2221 temp_cascaded_table(current_n).to_organization_id,
2222 temp_cascaded_table(current_n).expected_receipt_date,
2223 temp_cascaded_table(current_n).project_id,
2224 temp_cascaded_table(current_n).task_id,
2225 p_lpn_id,
2226 temp_cascaded_table(current_n).lot_number, --9229228
2227 temp_cascaded_table(current_n).serial_number,-- 9651496
2228 temp_cascaded_table(current_n).req_header_id, --12546176
2229 temp_cascaded_table(current_n).unit_of_measure);--14776842
2230
2231 -- count_shipments just gets the count of rows found in shipments
2232 OPEN count_int_req_receipt_lines
2233 (temp_cascaded_table(current_n).shipment_header_id,
2234 temp_cascaded_table(current_n).shipment_line_id,
2235 temp_cascaded_table(current_n).item_id,
2236 temp_cascaded_table(current_n).to_organization_id,
2237 temp_cascaded_table(current_n).expected_receipt_date,
2238 temp_cascaded_table(current_n).project_id,
2239 temp_cascaded_table(current_n).task_id,
2240 p_lpn_id ,
2241 temp_cascaded_table(current_n).lot_number, --9229228
2242 temp_cascaded_table(current_n).serial_number, -- 9651496
2243 temp_cascaded_table(current_n).req_header_id); --12546176
2244 ELSIF p_match_type = 'RMA' THEN
2245 l_progress := '30.1';
2246 IF (l_debug = 1) THEN
2247 print_debug('Opening cursor in matching for rmas FOR parameters:',4);
2248 print_debug('oe_order_header_id:'||temp_cascaded_table(current_n).oe_order_header_id,4);
2249 print_debug('oe_order_line_id:'||temp_cascaded_table(current_n).oe_order_line_id,4);
2250 print_debug('item_id:'||temp_cascaded_table(current_n).item_id,4);
2251 print_debug('to_organization_id:'||temp_cascaded_table(current_n).to_organization_id,4);
2252 print_debug('primary_unit_of_measure:'||temp_cascaded_table(current_n).primary_unit_of_measure,4);
2253 print_debug('expected_receipt_date:'||temp_cascaded_table(current_n).expected_receipt_date,4);
2254 print_debug('project id ' || temp_cascaded_table(current_n).project_id);
2255 print_debug('task_id : ' || temp_cascaded_table(current_n).task_id);
2256 print_debug('unit_of_measure : ' || temp_cascaded_table(current_n).unit_of_measure); --14776842
2257 END IF;
2258
2259 SELECT uom_code INTO l_rma_uom -- 14776842 In oe_order_lines_all, it only keep uom code
2260 FROM mtl_units_of_measure WHERE unit_of_measure=temp_cascaded_table(current_n).unit_of_measure AND ROWNUM=1;
2261 IF (l_debug = 1) THEN
2262 print_debug('l_rma_uom : ' || l_rma_uom);
2263 END IF;
2264
2265 OPEN rma_receipt_lines
2266 (temp_cascaded_table(current_n).oe_order_header_id,
2267 temp_cascaded_table(current_n).oe_order_line_id,
2268 temp_cascaded_table(current_n).item_id,
2269 temp_cascaded_table(current_n).to_organization_id,
2270 temp_cascaded_table(current_n).primary_unit_of_measure,
2271 temp_cascaded_table(current_n).expected_receipt_date,
2272 temp_cascaded_table(current_n).project_id,
2273 temp_cascaded_table(current_n).task_id,
2274 l_rma_uom); --14776842
2275
2276 -- count_shipments just gets the count of rows found in shipments
2277 OPEN count_rma_receipt_lines
2278 (temp_cascaded_table(current_n).oe_order_header_id,
2279 temp_cascaded_table(current_n).oe_order_line_id,
2280 temp_cascaded_table(current_n).item_id,
2281 temp_cascaded_table(current_n).to_organization_id,
2282 temp_cascaded_table(current_n).primary_unit_of_measure,
2283 temp_cascaded_table(current_n).expected_receipt_date,
2284 temp_cascaded_table(current_n).project_id,
2285 temp_cascaded_table(current_n).task_id);
2286 END IF; -- p_match_type = 'ASN'
2287
2288 ELSIF (x_cascaded_table(n).transaction_type = 'STD_DELIVER') THEN
2289 IF (l_debug = 1) THEN
2290 print_debug('Transaction type is std. delivery',4);
2291 END IF;
2292 l_progress := '40';
2293
2294 /* FP-J Lot/Serial Support Enhancement
2295 * If WMS and PO patch levels are J or higher, then we need to:
2296 * a) Match the lot number with that in RCV_LOTS_SUPPLY for the
2297 * the parent transaction in RCV_SUPPLY if lot number is not NULL
2298 * b) Match the LPN being delivered with the LPN in the RCV_SUPPLY for
2299 * the parent transaction
2300 * c) Set the value of lot_number to the cursor from that in the cascaded table
2301 * Set the value of lpn_id_to_match to the lpn being delivered (p_lpn_id)
2302 * If either of these are not at J, we should retain the original functionality
2303 * and so explictily set these values to NULL
2304 * Nevertheless, we should pass these two new values to the cursors
2305 * int_req_delivery_lines and count_std_distributions (for int ship/int req)
2306 * , rma_delivery_lines and count_rma_delivery_lines (for RMA)
2307 */
2308 IF p_match_type = 'INTRANSIT SHIPMENT' THEN
2309 IF (l_debug = 1) THEN
2310 print_debug('Opening cursor in matching for intransit shipments FOR parameters:',4);
2311 print_debug('shipment_header_id:'||temp_cascaded_table(current_n).shipment_header_id,4);
2312 print_debug('shipment_line_id:'||temp_cascaded_table(current_n).shipment_line_id,4);
2313 print_debug('item_id:'||temp_cascaded_table(current_n).item_id,4);
2314 print_debug('to_organization_id:'||temp_cascaded_table(current_n).to_organization_id,4);
2315 print_debug('p_receipt_num:'||p_receipt_num,4);
2316 print_debug('expected_receipt_date:'||temp_cascaded_table(current_n).expected_receipt_date,4);
2317 print_debug('inspection_status_code:'||temp_cascaded_table(current_n).inspection_status_code,4);
2318 print_debug('p_lpn_id:'||temp_cascaded_table(current_n).p_lpn_id,4);
2319 END IF;
2320
2321 IF (l_wms_po_j_or_higher) THEN
2322 l_lot_number_to_match := temp_cascaded_table(current_n).lot_number;
2323 l_lpn_id_to_match := temp_cascaded_table(current_n).p_lpn_id;
2324 l_parent_txn_id_to_match := l_passed_parent_txn_id;
2325 ELSE
2326 l_lot_number_to_match := NULL;
2327 l_lpn_id_to_match := NULL;
2328 l_parent_txn_id_to_match := NULL;
2329 END IF;
2330
2331 IF (l_debug = 1) THEN
2332 print_debug('l_lot_number_to_match: ' || l_lot_number_to_match, 4);
2333 print_debug('l_lpn_id_to_match: ' || l_lpn_id_to_match, 4);
2334 print_debug('l_parent_txn_id_to_match: ' || l_parent_txn_id_to_match, 4);
2335 END IF;
2336
2337 OPEN int_req_delivery_lines(
2338 temp_cascaded_table(current_n).shipment_header_id,
2339 temp_cascaded_table(current_n).shipment_line_id,
2340 temp_cascaded_table(current_n).item_id,
2341 temp_cascaded_table(current_n).to_organization_id,
2342 p_receipt_num,
2343 temp_cascaded_table(current_n).expected_receipt_date,
2344 temp_cascaded_table(current_n).inspection_status_code,
2345 temp_cascaded_table(current_n).p_lpn_id,
2346 l_lot_number_to_match,
2347 l_lpn_id_to_match,
2348 l_parent_txn_id_to_match);
2349
2350 -- count_shipments just gets the count of rows found in shipments
2351 OPEN count_int_req_delivery_lines(
2352 temp_cascaded_table(current_n).shipment_header_id,
2353 temp_cascaded_table(current_n).shipment_line_id,
2354 temp_cascaded_table(current_n).item_id,
2355 temp_cascaded_table(current_n).to_organization_id,
2356 p_receipt_num,
2357 temp_cascaded_table(current_n).expected_receipt_date,
2358 temp_cascaded_table(current_n).inspection_status_code,
2359 temp_cascaded_table(current_n).p_lpn_id,
2360 l_lot_number_to_match,
2361 l_lpn_id_to_match,
2362 l_parent_txn_id_to_match);
2363 ELSIF p_match_type = 'RMA' THEN
2364 IF (l_debug = 1) THEN
2365 print_debug('Opening cursor in matching for rmas FOR parameters:',4);
2366 print_debug('oe_order_header_id:'||temp_cascaded_table(current_n).oe_order_header_id,4);
2367 print_debug('oe_order_line_id:'||temp_cascaded_table(current_n).oe_order_line_id,4);
2368 print_debug('item_id:'||temp_cascaded_table(current_n).item_id,4);
2369 print_debug('to_organization_id:'||temp_cascaded_table(current_n).to_organization_id,4);
2370 print_debug('p_receipt_num:'||p_receipt_num,4);
2371 print_debug('expected_receipt_date:'||temp_cascaded_table(current_n).expected_receipt_date,4);
2372 print_debug('inspection_status_code:'||temp_cascaded_table(current_n).inspection_status_code,4);
2373 print_debug('p_lpn_id:'||temp_cascaded_table(current_n).p_lpn_id,4);
2374 END IF;
2375
2376 IF (l_wms_po_j_or_higher) THEN
2377 l_lot_number_to_match := temp_cascaded_table(current_n).lot_number;
2378 l_lpn_id_to_match := temp_cascaded_table(current_n).p_lpn_id;
2379 l_parent_txn_id_to_match := l_passed_parent_txn_id;
2380 ELSE
2381 l_lot_number_to_match := NULL;
2382 l_lpn_id_to_match := NULL;
2383 l_parent_txn_id_to_match := NULL;
2384 END IF;
2385
2386 IF (l_debug = 1) THEN
2387 print_debug('l_lot_number_to_match: ' || l_lot_number_to_match, 4);
2388 print_debug('l_lpn_id_to_match: ' || l_lpn_id_to_match, 4);
2389 print_debug('l_parent_txn_id_to_match: ' || l_parent_txn_id_to_match, 4);
2390 END IF;
2391
2392 OPEN rma_delivery_lines(
2393 temp_cascaded_table(current_n).oe_order_header_id,
2394 temp_cascaded_table(current_n).oe_order_line_id,
2395 temp_cascaded_table(current_n).item_id,
2396 temp_cascaded_table(current_n).to_organization_id,
2397 p_receipt_num,
2398 temp_cascaded_table(current_n).expected_receipt_date,
2399 temp_cascaded_table(current_n).inspection_status_code,
2400 temp_cascaded_table(current_n).p_lpn_id,
2401 l_lot_number_to_match,
2402 l_lpn_id_to_match,
2403 l_parent_txn_id_to_match);
2404
2405 -- count_shipments just gets the count of rows found in shipments
2406 OPEN count_rma_delivery_lines(
2407 temp_cascaded_table(current_n).oe_order_header_id,
2408 temp_cascaded_table(current_n).oe_order_line_id,
2409 temp_cascaded_table(current_n).item_id,
2410 temp_cascaded_table(current_n).to_organization_id,
2411 p_receipt_num,
2412 temp_cascaded_table(current_n).expected_receipt_date,
2413 temp_cascaded_table(current_n).inspection_status_code,
2414 temp_cascaded_table(current_n).p_lpn_id,
2415 l_lot_number_to_match,
2416 l_lpn_id_to_match,
2417 l_parent_txn_id_to_match);
2418
2419 ELSIF p_match_type = 'ASN' THEN
2420 IF (l_debug = 1) THEN
2421 print_debug('Opening cursor in matching for ASNs FOR parameters:',4);
2422 print_debug('shipment_header_id:'||temp_cascaded_table(current_n).shipment_header_id,4);
2423 print_debug('shipment_line_id:'||temp_cascaded_table(current_n).shipment_line_id,4);
2424 print_debug('item_id:'||temp_cascaded_table(current_n).item_id,4);
2425 print_debug('to_organization_id:'||temp_cascaded_table(current_n).to_organization_id,4);
2426 print_debug('p_receipt_num:'||p_receipt_num,4);
2427 print_debug('expected_receipt_date:'||temp_cascaded_table(current_n).expected_receipt_date,4);
2428 print_debug('inspection_status_code:'||temp_cascaded_table(current_n).inspection_status_code,4);
2429 print_debug('p_lpn_id:'||temp_cascaded_table(current_n).p_lpn_id,4);
2430 END IF;
2431
2432 IF (l_wms_po_j_or_higher) THEN
2433 l_lot_number_to_match := temp_cascaded_table(current_n).lot_number;
2434 l_lpn_id_to_match := temp_cascaded_table(current_n).p_lpn_id;
2435 l_parent_txn_id_to_match := l_passed_parent_txn_id; -- 9879753
2436 ELSE
2437 l_lot_number_to_match := NULL;
2438 l_lpn_id_to_match := NULL;
2439 l_parent_txn_id_to_match := NULL; -- 9879753
2440 END IF;
2441
2442 IF (l_debug = 1) THEN
2443 print_debug('l_lot_number_to_match: ' || l_lot_number_to_match, 4);
2444 print_debug('l_lpn_id_to_match: ' || l_lpn_id_to_match, 4);
2445 print_debug('::bug9879753::l_parent_txn_id_to_match: ' || l_parent_txn_id_to_match, 4); -- 9879753
2446 END IF;
2447 OPEN asn_delivery_lines(
2448 temp_cascaded_table(current_n).shipment_header_id,
2449 temp_cascaded_table(current_n).shipment_line_id,
2450 temp_cascaded_table(current_n).item_id,
2451 temp_cascaded_table(current_n).to_organization_id,
2452 p_receipt_num,
2453 temp_cascaded_table(current_n).expected_receipt_date,
2454 temp_cascaded_table(current_n).inspection_status_code,
2455 temp_cascaded_table(current_n).p_lpn_id,
2456 l_lot_number_to_match,
2457 l_lpn_id_to_match,
2458 l_parent_txn_id_to_match); -- 9879753
2459
2460 -- count_shipments just gets the count of rows found in shipments
2461 OPEN count_asn_delivery_lines(
2462 temp_cascaded_table(current_n).shipment_header_id,
2463 temp_cascaded_table(current_n).shipment_line_id,
2464 temp_cascaded_table(current_n).item_id,
2465 temp_cascaded_table(current_n).to_organization_id,
2466 p_receipt_num,
2467 temp_cascaded_table(current_n).expected_receipt_date,
2468 temp_cascaded_table(current_n).inspection_status_code,
2469 temp_cascaded_table(current_n).p_lpn_id,
2470 l_lot_number_to_match,
2471 l_lpn_id_to_match,
2472 l_parent_txn_id_to_match); -- 9879753
2473
2474 -- For Bug 7440217 Code of rdoc type LCM
2475 -- END IF; -- p_match_type = 'INTRANSIT SHIPMENT'
2476 ELSIF p_match_type = 'LCM' THEN
2477 IF (l_debug = 1) THEN
2478 print_debug('Opening cursor in matching for LCMs FOR parameters:',4);
2479 print_debug('shipment_header_id:'||temp_cascaded_table(current_n).shipment_header_id,4);
2480 print_debug('shipment_line_id:'||temp_cascaded_table(current_n).shipment_line_id,4);
2481 print_debug('item_id:'||temp_cascaded_table(current_n).item_id,4);
2482 print_debug('to_organization_id:'||temp_cascaded_table(current_n).to_organization_id,4);
2483 print_debug('p_receipt_num:'||p_receipt_num,4);
2484 print_debug('expected_receipt_date:'||temp_cascaded_table(current_n).expected_receipt_date,4);
2485 print_debug('inspection_status_code:'||temp_cascaded_table(current_n).inspection_status_code,4);
2486 print_debug('p_lpn_id:'||temp_cascaded_table(current_n).p_lpn_id,4);
2487 END IF;
2488
2489 IF (l_wms_po_j_or_higher) THEN
2490 l_lot_number_to_match := temp_cascaded_table(current_n).lot_number;
2491 l_lpn_id_to_match := temp_cascaded_table(current_n).p_lpn_id;
2492 ELSE
2493 l_lot_number_to_match := NULL;
2494 l_lpn_id_to_match := NULL;
2495 END IF;
2496
2497 IF (l_debug = 1) THEN
2498 print_debug('l_lot_number_to_match: ' || l_lot_number_to_match, 4);
2499 print_debug('l_lpn_id_to_match: ' || l_lpn_id_to_match, 4);
2500 END IF;
2501 OPEN lcm_delivery_lines(
2502 temp_cascaded_table(current_n).shipment_header_id,
2503 temp_cascaded_table(current_n).shipment_line_id,
2504 temp_cascaded_table(current_n).item_id,
2505 temp_cascaded_table(current_n).to_organization_id,
2506 p_receipt_num,
2507 temp_cascaded_table(current_n).expected_receipt_date,
2508 temp_cascaded_table(current_n).inspection_status_code,
2509 temp_cascaded_table(current_n).p_lpn_id,
2510 l_lot_number_to_match,
2511 l_lpn_id_to_match);
2512
2513 -- count_shipments just gets the count of rows found in shipments
2514 OPEN count_lcm_delivery_lines(
2515 temp_cascaded_table(current_n).shipment_header_id,
2516 temp_cascaded_table(current_n).shipment_line_id,
2517 temp_cascaded_table(current_n).item_id,
2518 temp_cascaded_table(current_n).to_organization_id,
2519 p_receipt_num,
2520 temp_cascaded_table(current_n).expected_receipt_date,
2521 temp_cascaded_table(current_n).inspection_status_code,
2522 temp_cascaded_table(current_n).p_lpn_id,
2523 l_lot_number_to_match,
2524 l_lpn_id_to_match);
2525
2526
2527 END IF; -- p_match_type = 'LCM'
2528 -- End for Bug 7440217
2529
2530 END IF;
2531
2532 l_progress := '60';
2533 -- Assign shipped quantity to remaining quantity
2534 -- x_remaining_quantity := temp_cascaded_table(current_n).quantity; --Bug 4004656
2535 l_rem_qty_trans_uom := temp_cascaded_table(current_n).quantity; --Bug 4004656
2536 -- used for decrementing cum qty for first record
2537 -- x_bkp_qty := x_remaining_quantity; --Bug 4004656
2538 -- x_remaining_qty_po_uom := 0; --Bug 4004656
2539 l_bkp_qty_trans_uom := l_rem_qty_trans_uom; --Bug 4004656
2540 l_rcv_qty_trans_uom := 0; --Bug 4004656
2541 l_rcv_qty_po_uom := 0; --Bug 4004656
2542 -- Calculate tax_amount_factor for calculating tax_amount for
2543 -- each cascaded line
2544 IF Nvl(temp_cascaded_table(current_n).tax_amount,0) <> 0 THEN
2545 /* Bug 4567809 -Modified the below assignment */
2546 --tax_amount_factor := temp_cascaded_table(current_n).tax_amount/x_remaining_quantity;
2547 tax_amount_factor := temp_cascaded_table(current_n).tax_amount/l_rem_qty_trans_uom;
2548 /* End of fix for Bug 4567809 */
2549 ELSE
2550 tax_amount_factor := 0;
2551 END IF;
2552
2553 x_first_trans := TRUE;
2554 transaction_ok := FALSE;
2555
2556 l_progress := '70';
2557 -- Get the count of the number of records depending on the
2558 -- the transaction type
2559 IF (x_cascaded_table(n).transaction_type = 'RECEIVE')
2560 OR (x_cascaded_table(n).transaction_type = 'DELIVER') THEN
2561 l_progress := '80';
2562
2563 IF p_match_type = 'ASN' THEN
2564 IF (x_cascaded_table(n).transaction_type = 'RECEIVE') THEN
2565 FETCH count_asn_receipt_lines INTO x_record_count;
2566 ELSE -- (x_cascaded_table(n).transaction_type = 'DELIVER')
2567 FETCH count_asn_direct_lines INTO x_record_count;
2568 END IF;
2569
2570 -- For Bug 7440217 Added the code below for LCM changes
2571 ELSIF p_match_type = 'LCM' THEN
2572 IF (x_cascaded_table(n).transaction_type = 'RECEIVE') THEN
2573 FETCH count_lcm_receipt_lines INTO x_record_count;
2574 ELSE -- (x_cascaded_table(n).transaction_type = 'DELIVER')
2575 FETCH count_lcm_direct_lines INTO x_record_count;
2576 END IF;
2577 -- End for Bug 7440217
2578
2579 ELSIF p_match_type = 'INTRANSIT SHIPMENT' THEN
2580 FETCH count_int_req_receipt_lines INTO x_record_count;
2581 ELSIF p_match_type = 'RMA' THEN
2582 FETCH count_rma_receipt_lines INTO x_record_count;
2583 END IF;
2584 ELSIF (x_cascaded_table(n).transaction_type = 'STD_DELIVER') THEN
2585 l_progress := '90';
2586 IF p_match_type = 'INTRANSIT SHIPMENT' THEN
2587 FETCH count_int_req_delivery_lines INTO x_record_count;
2588 ELSIF p_match_type = 'RMA' THEN
2589 FETCH count_rma_delivery_lines INTO x_record_count;
2590 ELSIF p_match_type = 'ASN' THEN
2591 FETCH count_asn_delivery_lines INTO x_record_count;
2592 -- For Bug 7440217 LCm Changes
2593 ELSIF p_match_type = 'LCM' THEN
2594 FETCH count_lcm_delivery_lines INTO x_record_count;
2595 -- End for Bug 7440217
2596 END IF;
2597 END IF;
2598 l_progress := '100';
2599
2600 IF (l_debug = 1) THEN
2601 print_debug('Initial Rows fetched into matching cursor :'||x_record_count,4);
2602 END IF;
2603
2604 LOOP
2605 -- Fetch the appropriate record
2606 l_progress := '110';
2607 IF (x_cascaded_table(n).transaction_type = 'RECEIVE')
2608 OR (x_cascaded_table(n).transaction_type = 'DELIVER') THEN
2609 l_progress := '120';
2610
2611 IF p_match_type = 'ASN' THEN
2612 IF (x_cascaded_table(n).transaction_type = 'RECEIVE') THEN
2613 FETCH asn_receipt_lines INTO x_MatchedRec;
2614 IF (asn_receipt_lines%NOTFOUND) THEN
2615 lastrecord := TRUE;
2616 END IF;
2617 rows_fetched := asn_receipt_lines%rowcount;
2618
2619 ELSE --IF (x_cascaded_table(n).transaction_type = 'DELIVER') THEN
2620 FETCH asn_direct_lines INTO x_MatchedRec;
2621 IF (asn_direct_lines%NOTFOUND) THEN
2622 lastrecord := TRUE;
2623 END IF;
2624 rows_fetched := asn_direct_lines%rowcount;
2625
2626 END IF;
2627
2628 -- For Bug 7440217 Added for LCM Changes
2629 ELSIF p_match_type = 'LCM' THEN
2630 IF (x_cascaded_table(n).transaction_type = 'RECEIVE') THEN
2631 FETCH lcm_receipt_lines INTO x_MatchedRec;
2632 IF (lcm_receipt_lines%NOTFOUND) THEN
2633 lastrecord := TRUE;
2634 END IF;
2635 rows_fetched := lcm_receipt_lines%rowcount;
2636
2637 ELSE --IF (x_cascaded_table(n).transaction_type = 'DELIVER') THEN
2638 FETCH lcm_direct_lines INTO x_MatchedRec;
2639 IF (lcm_direct_lines%NOTFOUND) THEN
2640 lastrecord := TRUE;
2641 END IF;
2642 rows_fetched := lcm_direct_lines%rowcount;
2643 END IF;
2644 -- End for Bug 7440217
2645
2646
2647 ELSIF p_match_type = 'INTRANSIT SHIPMENT' THEN
2648 FETCH int_req_receipt_lines INTO x_MatchedRec;
2649 IF (int_req_receipt_lines%NOTFOUND) THEN
2650 lastrecord := TRUE;
2651 END IF;
2652 rows_fetched := int_req_receipt_lines%rowcount;
2653 ELSIF p_match_type = 'RMA' THEN
2654 FETCH rma_receipt_lines INTO x_MatchedRec;
2655 IF (rma_receipt_lines%NOTFOUND) THEN
2656 lastrecord := TRUE;
2657 END IF;
2658 rows_fetched := rma_receipt_lines%rowcount;
2659 END IF;
2660 ELSIF (x_cascaded_table(n).transaction_type = 'STD_DELIVER') THEN
2661 l_progress := '130';
2662 IF p_match_type = 'INTRANSIT SHIPMENT' THEN
2663 FETCH int_req_delivery_lines INTO x_MatchedRec;
2664 IF (int_req_delivery_lines%NOTFOUND) THEN
2665 lastrecord := TRUE;
2666 END IF;
2667 rows_fetched := int_req_delivery_lines%rowcount;
2668 ELSIF p_match_type = 'RMA' THEN
2669 FETCH rma_delivery_lines INTO x_MatchedRec;
2670 IF (rma_delivery_lines%NOTFOUND) THEN
2671 lastrecord := TRUE;
2672 END IF;
2673 rows_fetched := rma_delivery_lines%rowcount;
2674 ELSIF p_match_type = 'ASN' THEN
2675 FETCH asn_delivery_lines INTO x_MatchedRec;
2676 IF (asn_delivery_lines%NOTFOUND) THEN
2677 lastrecord := TRUE;
2678 END IF;
2679 rows_fetched := asn_delivery_lines%rowcount;
2680 -- For Bug 7440217 Added for LCM changes
2681 ELSIF p_match_type = 'LCM' THEN
2682 FETCH lcm_delivery_lines INTO x_MatchedRec;
2683 IF (lcm_delivery_lines%NOTFOUND) THEN
2684 lastrecord := TRUE;
2685 END IF;
2686 rows_fetched := lcm_delivery_lines%rowcount;
2687 -- End for Bug 7440217
2688 END IF;
2689 END IF;
2690 IF (l_debug = 1) THEN
2691 print_debug('Row fetched from matching cursor and being processed',4);
2692 --print_debug('Remaining qty:'||x_remaining_quantity,4); --Bug 4004656
2693 print_debug('Remaining qty:'||l_rem_qty_trans_uom,4); --Bug 4004656
2694 print_debug('current_n:'||current_n,4);
2695 END IF;
2696 IF x_first_trans THEN
2697 IF (l_debug = 1) THEN
2698 print_debug('x_first_trans:TRUE',4);
2699 END IF;
2700 ELSE
2701 IF (l_debug = 1) THEN
2702 print_debug('x_first_trans:FLASE',4);
2703 END IF;
2704 END IF;
2705 IF lastrecord THEN
2706 IF (l_debug = 1) THEN
2707 print_debug('lastrecord:TRUE',4);
2708 END IF;
2709 ELSE
2710 IF (l_debug = 1) THEN
2711 print_debug('lastrecord:FLASE',4);
2712 END IF;
2713 END IF;
2714 l_progress := '150';
2715
2716 /* Bug 4004656 -Commented the following statement
2717 IF (lastrecord OR x_remaining_quantity <= 0) THEN */
2718 /* Bug 4747997: We have to compare the rounded off values by 5 decimal places,
2719 as the value hold by this variable is non-rounded value returned
2720 from the API rcv_transactions_interface_sv.convert_into_correct_qty() */
2721 IF (lastrecord OR round(l_rem_qty_trans_uom,5) <=0) THEN --Bug 4004656, Bug:4747997
2722 IF (l_debug = 1) THEN
2723 print_debug('No more rows or the remaining qty is less than zero',4);
2724 END IF;
2725
2726 IF NOT x_first_trans THEN
2727 -- x_first_trans has been reset which means some cascade has
2728 -- happened. Otherwise current_n = 1
2729 current_n := current_n -1 ;
2730 END IF;
2731
2732 -- do the tolerance act here
2733
2734 -- lastrecord...we have run out of rows and
2735 -- we still have quantity to allocate
2736
2737 -- Assuming that one cannot over receive for PO's
2738 -- defaulting the value of x_qty_rcv_exception_code to REJECT
2739 --
2740 -- If one can over receive, then this initialisation should
2741 -- be cahged to include the actual value.
2742 /* Bug 4004656 -Commented the following statement
2743 IF x_remaining_quantity > 0 THEN */
2744 IF round(l_rem_qty_trans_uom,5) > 0 THEN --Bug 4747997
2745 IF (l_debug = 1) THEN
2746 print_debug('No more recs but still qty left',4);
2747 END IF;
2748 IF NOT x_first_trans THEN
2749 IF (l_debug = 1) THEN
2750 print_debug('Atleast one row returned in matching cursor',4);
2751 END IF;
2752 -- we had got atleast some rows from our shipments cursor
2753 -- we have atleast one row cascaded (not null line_location_id)
2754 -- For Bug 7440217 for LCM changes
2755 IF p_match_type IN ('ASN', 'LCM') AND
2756 -- End for Bug 7440217
2757 Nvl(x_cascaded_table(n).transaction_type,'DELIVER') <> 'STD_DELIVER' THEN
2758 -- for ASN, we will deal with the exception status
2759 -- in po matching ????
2760 l_sh_result_count := temp_cascaded_table.COUNT;
2761
2762 -- po matchings input should include the lines
2763 -- already detailed by shipment matching
2764 IF l_sh_result_count > 0 THEN
2765 FOR i in 1..l_sh_result_count LOOP
2766 l_po_in_cascaded_table(i) := temp_cascaded_table(i);
2767 END LOOP;
2768 END IF;
2769
2770 IF temp_cascaded_table(l_sh_result_count).quantity <> 0 THEN
2771 l_sh_result_count := l_sh_result_count + 1;
2772 END IF;
2773
2774 -- put a new line at the end
2775 -- with quantity equals remaining quanitity
2776 l_po_in_cascaded_table(l_sh_result_count) := temp_cascaded_table(1);
2777
2778 /* Bug 4567809 -Modifying the assignment of quantity to l_rem_qty_trans_uom */
2779
2780 --l_po_in_cascaded_table(l_sh_result_count).quantity := x_remaining_quantity
2781 l_po_in_cascaded_table(l_sh_result_count).quantity := l_rem_qty_trans_uom;
2782
2783 /* End of fix for Bug 4567809 */
2784
2785
2786 inv_rcv_txn_interface.matching_logic
2787 (x_return_status => l_return_status,
2788 x_msg_count => l_msg_count,
2789 x_msg_data => l_msg_data,
2790 x_cascaded_table => l_po_in_cascaded_table,
2791 n => l_sh_result_count,
2792 temp_cascaded_table => l_po_out_cascaded_table,
2793 p_receipt_num => p_receipt_num,
2794 p_shipment_header_id => temp_cascaded_table(1).shipment_header_id,
2795 p_lpn_id => p_lpn_id
2796 );
2797
2798 IF (l_debug = 1) THEN
2799 print_debug('after po matching '|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 4);
2800 print_debug('after po matching l_return_status = '|| l_return_status, 4);
2801 print_debug('after po matching l_msg_count = '|| l_msg_count, 4);
2802 print_debug('after po matching l_msg_data = '||l_msg_data , 4);
2803 END IF;
2804
2805 -- Added for Bug 12732516 -- Start
2806
2807 IF (l_return_status = 'S') THEN
2808
2809 IF (p_match_type='ASN' AND Nvl(x_cascaded_table(n).transaction_type,'RECEIVE')='RECEIVE') THEN
2810
2811 FOR i in 1..l_po_out_cascaded_table.COUNT LOOP
2812
2813 IF (temp_cascaded_table(current_n).shipment_line_id=l_po_out_cascaded_table(i).shipment_line_id ) THEN
2814
2815 temp_cascaded_table(current_n).quantity:=temp_cascaded_table(current_n).quantity + l_po_out_cascaded_table(i).quantity;
2816 temp_cascaded_table(current_n).primary_quantity:=temp_cascaded_table(current_n).primary_quantity + l_po_out_cascaded_table(i).primary_quantity;
2817
2818 -- Added for bug 14749862
2819 IF ((temp_cascaded_table(current_n).error_status <> l_po_out_cascaded_table(i).error_status) AND l_po_out_cascaded_table(i).error_status='W') THEN
2820 temp_cascaded_table(current_n).error_status:=l_po_out_cascaded_table(i).error_status;
2821 temp_cascaded_table(current_n).error_message:=l_po_out_cascaded_table(i).error_message;
2822 END IF;
2823 -- End of bug 14749862
2824 l_po_out_cascaded_table.DELETE(i);
2825
2826 END IF;
2827
2828 END LOOP;
2829
2830 END IF;
2831
2832 END IF;
2833
2834 -- Bug 12732516 -- End
2835
2836 IF l_return_status = fnd_api.g_ret_sts_error THEN
2837 RAISE fnd_api.g_exc_error;
2838 END IF;
2839 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2840 RAISE fnd_api.g_exc_unexpected_error;
2841 END IF;
2842 IF l_po_in_cascaded_table(l_sh_result_count).error_status = 'E' THEN
2843 x_cascaded_table(n).error_status := l_po_in_cascaded_table(l_sh_result_count).error_status;
2844 x_cascaded_table(n).error_message := l_po_in_cascaded_table(l_sh_result_count).error_message;
2845 IF (l_debug = 1) THEN
2846 print_debug('error calling po matching'|| to_char(sysdate, 'YYYY-MM-DD HH:DD:SS'), 1);
2847 END IF;
2848 IF temp_cascaded_table.COUNT > 0 THEN
2849 FOR i in 1..temp_cascaded_table.COUNT LOOP
2850 temp_cascaded_table.DELETE(i);
2851 END LOOP;
2852 END IF;
2853 else
2854 -- need to consolidate the qty for each shipment line ???
2855 -- now just append the qty detailed from PO to the end
2856 IF l_po_out_cascaded_table.COUNT > 0 THEN
2857 FOR i in 1..l_po_out_cascaded_table.COUNT LOOP
2858 temp_cascaded_table(l_sh_result_count + i - 1) := l_po_out_cascaded_table(i);
2859 END LOOP;
2860 END IF;
2861 END IF;
2862 -- For Bug 7440217 for LCM changes
2863 x_qty_rcv_exception_code := p_match_type;
2864 -- End for Bug 7440217
2865 ELSIF p_match_type = 'RMA' THEN
2866 l_sh_result_count := temp_cascaded_table.COUNT;
2867 OE_RMA_Receiving.Get_RMA_Tolerances( temp_cascaded_table(l_sh_result_count).oe_order_line_id,
2868 x_under_return_tolerance,
2869 x_qty_rcv_tolerance,
2870 l_return_status,
2871 l_msg_count,
2872 l_msg_data );
2873
2874 IF x_qty_rcv_tolerance IS NOT NULL THEN
2875 select ordered_qty
2876 into x_oe_line_qty
2877 from oe_po_enter_receipts_v
2878 where oe_order_line_id =
2879 temp_cascaded_table(l_sh_result_count).oe_order_line_id;
2880
2881 /* Bug 4004656 - Commented out the following line
2882 IF (x_remaining_quantity > (x_oe_line_qty * (x_qty_rcv_tolerance/100)))*/
2883
2884 /* Bug 4747997: When Over shipment tolerance is set for RMA, the tolerance value
2885 is getting calculated in RMA uom instead of transaction uom.
2886 So, We need to convert the tolerance qty for RMA to transaction uom. */
2887 l_rcv_tolerance_qty_rma_uom := x_oe_line_qty * (x_qty_rcv_tolerance/100);
2888
2889 l_rcv_tolerance_qty_txn_uom := rcv_transactions_interface_sv.convert_into_correct_qty(
2890 l_rcv_tolerance_qty_rma_uom,
2891 x_MatchedRec.unit_of_measure,
2892 temp_cascaded_table(1).item_id,
2893 temp_cascaded_table(1).unit_of_measure);
2894
2895 IF (round(l_rem_qty_trans_uom,5) > round(l_rcv_tolerance_qty_txn_uom,5))--Bug 4004656, Bug 4747997
2896 THEN
2897 x_qty_rcv_exception_code := 'REJECT';
2898 ELSE
2899 /* Bug 4004656 - Commented out the following line
2900 temp_cascaded_table(l_sh_result_count).quantity :=
2901 temp_cascaded_table(l_sh_result_count).quantity + x_remaining_quantity; */
2902 temp_cascaded_table(l_sh_result_count).quantity :=
2903 temp_cascaded_table(l_sh_result_count).quantity + l_rem_qty_trans_uom; --Bug 4004656
2904 /* Bug 4004656 - Commented out the following line
2905 temp_cascaded_table(l_sh_result_count).primary_quantity :=
2906 temp_cascaded_table(l_sh_result_count).primary_quantity + x_remaining_quantity; */
2907 temp_cascaded_table(l_sh_result_count).primary_quantity :=
2908 temp_cascaded_table(l_sh_result_count).primary_quantity + l_rem_qty_trans_uom;
2909 x_qty_rcv_exception_code := 'RMA';
2910 END IF;
2911 ELSE
2912 x_qty_rcv_exception_code := 'REJECT';
2913 END IF;
2914 ELSE -- p_match_type = 'ASN';
2915 x_qty_rcv_exception_code := 'REJECT';
2916 END IF;
2917 IF (l_debug = 1) THEN
2918 print_debug('x_qty_rcv_exception_code:'||x_qty_rcv_exception_code,4);
2919 END IF;
2920
2921 IF x_qty_rcv_exception_code = 'REJECT' THEN
2922 IF (l_debug = 1) THEN
2923 print_debug('rejecting and deleting matched rows since over the tolerance',4);
2924 END IF;
2925 x_cascaded_table(n).error_status := 'E';
2926 x_cascaded_table(n).error_message := 'INV_RCV_QTY_OVER_TOLERANCE';
2927
2928 IF temp_cascaded_table.COUNT > 0 THEN
2929 FOR i in 1..temp_cascaded_table.COUNT LOOP
2930 temp_cascaded_table.DELETE(i);
2931 END LOOP;
2932 END IF;
2933 END IF;
2934 ELSE -- for if remaining_qty > 0 and not x_first_trans
2935 IF (l_debug = 1) THEN
2936 print_debug('first transaction and qty remains so over tolerance',4);
2937 END IF;
2938 x_cascaded_table(n).error_status := 'E';
2939 x_cascaded_table(n).error_message := 'INV_RCV_QTY_OVER_TOLERANCE';
2940
2941 IF rows_fetched = 0 THEN
2942 x_cascaded_table(n).error_message := 'INV_RCV_NO_ROWS';
2943 IF (l_debug = 1) THEN
2944 print_debug('matching_logic - No rows were retrieved from cursor ', 4);
2945 END IF;
2946 ELSIF x_first_trans THEN
2947 x_cascaded_table(n).error_message := 'INV_RCV_GEN_TOLERANCE_EXCEED';
2948 IF (l_debug = 1) THEN
2949 print_debug('matching_logic - No rows were cascaded', 4);
2950 END IF;
2951 END IF;
2952
2953 -- Delete the temp_cascaded_table just to be sure
2954 IF temp_cascaded_table.COUNT > 0 THEN
2955 FOR i IN 1..temp_cascaded_table.COUNT LOOP
2956 temp_cascaded_table.DELETE(i);
2957 END LOOP;
2958 END IF;
2959 END IF;
2960 ELSE -- for if x_remaining_qty > 0
2961 NULL;
2962 END IF;
2963
2964 -- close cursors
2965 IF int_req_receipt_lines%isopen THEN
2966 CLOSE int_req_receipt_lines;
2967 END IF;
2968
2969 IF count_int_req_receipt_lines%isopen THEN
2970 CLOSE count_int_req_receipt_lines;
2971 END IF;
2972
2973 IF rma_receipt_lines%isopen THEN
2974 CLOSE rma_receipt_lines;
2975 END IF;
2976
2977 IF count_rma_receipt_lines%isopen THEN
2978 CLOSE count_rma_receipt_lines;
2979 END IF;
2980
2981 IF asn_receipt_lines%isopen THEN
2982 CLOSE asn_receipt_lines;
2983 END IF;
2984
2985 IF count_asn_receipt_lines%isopen THEN
2986 CLOSE count_asn_receipt_lines;
2987 END IF;
2988
2989 IF asn_direct_lines%isopen THEN
2990 CLOSE asn_direct_lines;
2991 END IF;
2992
2993 IF count_asn_direct_lines%isopen THEN
2994 CLOSE count_asn_direct_lines;
2995 END IF;
2996
2997 -- For Bug 7440217 for LCM changes
2998 IF lcm_receipt_lines%isopen THEN
2999 CLOSE lcm_receipt_lines;
3000 END IF;
3001
3002 IF count_lcm_receipt_lines%isopen THEN
3003 CLOSE count_lcm_receipt_lines;
3004 END IF;
3005
3006 IF lcm_direct_lines%isopen THEN
3007 CLOSE lcm_direct_lines;
3008 END IF;
3009
3010 IF count_lcm_direct_lines%isopen THEN
3011 CLOSE count_lcm_direct_lines;
3012 END IF;
3013
3014 IF lcm_delivery_lines%isopen THEN
3015 CLOSE lcm_delivery_lines;
3016 END IF;
3017
3018 IF count_lcm_delivery_lines%isopen THEN
3019 CLOSE count_lcm_delivery_lines;
3020 END IF;
3021
3022 -- End for Bug 7440217
3023
3024 IF int_req_delivery_lines%isopen THEN
3025 CLOSE int_req_delivery_lines;
3026 END IF;
3027
3028 IF count_int_req_delivery_lines%isopen THEN
3029 CLOSE count_int_req_delivery_lines;
3030 END IF;
3031
3032 IF rma_delivery_lines%isopen THEN
3033 CLOSE rma_delivery_lines;
3034 END IF;
3035
3036 IF count_rma_delivery_lines%isopen THEN
3037 CLOSE count_rma_delivery_lines;
3038 END IF;
3039
3040 IF asn_delivery_lines%isopen THEN
3041 CLOSE asn_delivery_lines;
3042 END IF;
3043
3044 IF count_asn_delivery_lines%isopen THEN
3045 CLOSE count_asn_delivery_lines;
3046 END IF;
3047
3048 EXIT;
3049
3050 END IF;
3051
3052 -- Here should be checking for the date tolerances but, I donot
3053 -- think that date tolerance is to be checked for the intransit
3054 -- shipments.
3055
3056 -- Here should also be checking for the ship_to_location
3057 -- enforcement. But I think for intransit shipment it is not
3058 -- enforced.
3059
3060 -- Changes to accept Vendor_Item_num without ITEM_ID/NUM
3061 -- Item_id could be null if the ASN has the vendor_item_num provided
3062 -- We need to put a value into item_id based on the cursor
3063 -- We need to also figure out the primary unit for the item_id
3064 -- We will do it for the first record only. Subsequent records in the
3065 -- temp_table are copies of the previous one
3066
3067 -- Assuming that vendor_item_num refers to a single item. If the items
3068 -- could be different then we need to move this somewhere below
3069
3070 IF (x_first_trans) AND temp_cascaded_table(current_n).item_id IS NULL THEN
3071 IF (l_debug = 1) THEN
3072 print_debug('First txn and item id is null',4);
3073 END IF;
3074 temp_cascaded_table(current_n).item_id := x_MatchedRec.item_id;
3075 IF x_cascaded_table(n).primary_unit_of_measure IS NULL THEN
3076 IF x_cascaded_table(n).item_id IS NOT NULL THEN
3077 BEGIN
3078 SELECT primary_unit_of_measure
3079 INTO temp_cascaded_table(current_n).primary_unit_of_measure
3080 FROM mtl_system_items
3081 WHERE mtl_system_items.inventory_item_id =
3082 temp_cascaded_table(current_n).item_id
3083 AND mtl_system_items.organization_id =
3084 temp_cascaded_table(current_n).to_organization_id;
3085 EXCEPTION
3086 WHEN no_data_found THEN
3087 temp_cascaded_table(current_n).error_status := 'W';
3088 temp_cascaded_table(current_n).error_message :=
3089 'Need an error message';
3090 END;
3091 ELSE
3092 temp_cascaded_table(current_n).primary_unit_of_measure
3093 := x_MatchedRec.unit_of_measure;
3094 END IF;
3095 END IF;
3096 END IF;
3097
3098 insert_into_table := FALSE;
3099 already_allocated_qty := 0;
3100
3101 -- Get the available quantity for the shipment line
3102 -- that is available for allocation by this transaction
3103 --
3104 -- Direct delivery quantity for intransit shipments should be the
3105 -- same as the receiving quantity.
3106 IF insert_into_table THEN
3107 IF (l_debug = 1) THEN
3108 print_debug('insert_into_table:TRUE',4);
3109 END IF;
3110 ELSE
3111 IF (l_debug = 1) THEN
3112 print_debug('insert_into_table:FLASE',4);
3113 END IF;
3114 END IF;
3115 IF (l_debug = 1) THEN
3116 print_debug('already_allocated_qty:'||already_allocated_qty,4);
3117 END IF;
3118
3119 IF (x_cascaded_table(n).transaction_type = 'RECEIVE')
3120 OR (x_cascaded_table(n).transaction_type = 'DELIVER') THEN
3121 IF p_match_type = 'INTRANSIT SHIPMENT' THEN
3122 l_parent_id := x_MatchedRec.shipment_line_id;
3123 l_receipt_source_code := x_MatchedRec.receipt_source_code;
3124 -- For Bug 7440217 for LCM changes
3125 ELSIF p_match_type IN ('ASN', 'LCM') THEN
3126 -- End for Bug 7440217
3127 -- for ASN, we have to hardcode this value
3128 -- to get the available qty from shipment lines
3129 l_receipt_source_code := 'INVENTORY';
3130 l_parent_id := x_MatchedRec.shipment_line_id;
3131 ELSIF p_match_type = 'RMA' THEN
3132 l_receipt_source_code := x_MatchedRec.receipt_source_code;
3133 l_parent_id := x_MatchedRec.oe_order_line_id;
3134 --bug3592116 for RMA the uom code is got from oe_order_lines_all but we need to get
3135 --unit_of_measure to convert the rma line quantity to user input qty.
3136 SELECT unit_of_measure INTO l_rma_uom
3137 FROM mtl_units_of_measure
3138 WHERE uom_code = x_MatchedRec.unit_of_measure;
3139 x_MatchedRec.unit_of_measure := l_rma_uom;
3140 -- bug3592116
3141
3142 END IF;
3143
3144 IF (l_debug = 1) THEN
3145 print_debug('Receive/deliver l_parent_id:'||l_parent_id,4);
3146 print_debug('l_receipt_source_code:'||l_receipt_source_code,4);
3147 END IF;
3148 rcv_quantities_s.get_available_quantity
3149 ('RECEIVE',
3150 l_parent_id,
3151 l_receipt_source_code,
3152 NULL,
3153 NULL,
3154 NULL,
3155 x_converted_trx_qty,
3156 x_tolerable_qty,
3157 x_MatchedRec.unit_of_measure);
3158
3159 -- Bug 4004656-Added the following two assignment statements
3160 l_trx_qty_po_uom := x_converted_trx_qty;
3161 l_tol_qty_po_uom := x_tolerable_qty;
3162 -- End of fix for Bug 4004656
3163
3164 --Bug 12720051 For LCM lines the quantity and uom should be same as rsl quantity/uom
3165 --and user should not be allowed to change these while creating receipts.
3166 IF (p_match_type='LCM') THEN
3167 l_pre_rcv_flag := rcv_table_functions.is_pre_rcv_org(x_cascaded_table(n).to_organization_id);
3168 IF(x_cascaded_table(n).transaction_type in('RECEIVE','DELIVER')) THEN
3169 IF(l_pre_rcv_flag='Y') THEN
3170 SELECT rsl.quantity_shipped, rsl.unit_of_measure
3171 INTO l_rsl_qty, l_rsl_uom
3172 FROM rcv_shipment_lines rsl
3173 WHERE rsl.shipment_line_id = l_parent_id;
3174 --
3175 IF (l_rsl_qty <> x_cascaded_table(current_n).quantity) THEN
3176 x_cascaded_table(n).error_status := 'E';
3177 x_cascaded_table(n).error_message := 'INV_INVALID_ROI_VALUE';
3178 if temp_cascaded_table.count > 0 then
3179 for i in 1..temp_cascaded_table.count loop
3180 temp_cascaded_table.delete(i);
3181 end loop;
3182 end if;
3183 fnd_message.set_name('INV', 'INV_INVALID_ROI_VALUE');
3184 fnd_message.set_token('COLUMN', 'Quantity');
3185 fnd_message.set_token('ROI_VALUE',x_cascaded_table(current_n).quantity);
3186 fnd_message.set_token('SYS_VALUE',l_rsl_qty);
3187 fnd_msg_pub.ADD;
3188 RAISE fnd_api.g_exc_error;
3189 END IF;
3190 IF (l_rsl_uom <> x_cascaded_table(current_n).unit_of_measure) THEN
3191 x_cascaded_table(n).error_status := 'E';
3192 x_cascaded_table(n).error_message := 'INV_INVALID_ROI_VALUE';
3193 if temp_cascaded_table.count > 0 then
3194 for i in 1..temp_cascaded_table.count loop
3195 temp_cascaded_table.delete(i);
3196 end loop;
3197 end if;
3198 fnd_message.set_name('INV', 'INV_INVALID_ROI_VALUE');
3199 fnd_message.set_token('COLUMN', 'Uom');
3200 fnd_message.set_token('ROI_VALUE',x_cascaded_table(current_n).unit_of_measure);
3201 fnd_message.set_token('SYS_VALUE',l_rsl_uom);
3202 fnd_msg_pub.ADD;
3203 RAISE fnd_api.g_exc_error;
3204 END IF;
3205 END IF;
3206 END IF;
3207 END IF;
3208
3209 IF (l_debug = 1) THEN
3210 --print_debug('x_converted_trx_qty:'||x_converted_trx_qty,4); --Bug 4004656
3211 print_debug('l_trx_qty_po_uom:'||l_trx_qty_po_uom,4); --Bug 4004656
3212 --print_debug('x_tolerable_qty:'||x_tolerable_qty,4); --Bug 4004656
3213 print_debug('l_tol_qty_po_uom:'||l_tol_qty_po_uom,4); --Bug 4004656
3214 print_debug('unit_of_measure:'||x_MatchedRec.unit_of_measure,4);
3215 END IF;
3216
3217 -- this is done because for the deliver transaction there is
3218 -- no concept of tolerance. Tolerances are only applicable at
3219 -- the time of receipt, so does not really matter.
3220 -- x_tolerable_qty := x_converted_trx_qty; --Bug 4004656
3221 l_tol_qty_po_uom := l_trx_qty_po_uom ; --Bug 4004656
3222
3223 -- If qtys have already been allocated for this shipment line
3224 -- during a cascade process which has not been written to the db yet,
3225 -- we need to decrement it from the total available quantity
3226 -- We traverse the actual pl/sql table and accumulate the quantity by
3227 -- matching the shipment line id
3228 IF (l_debug = 1) THEN
3229 print_debug('n:'||n,4);
3230 END IF;
3231 IF n > 1 THEN -- We will do this for all rows except the 1st
3232 FOR i IN 1..(n-1) LOOP
3233 IF p_match_type = 'INTRANSIT SHIPMENT'
3234 -- For Bug 7440217 for LCM changes
3235 OR p_match_type IN ('ASN', 'LCM') THEN
3236 -- End for Bug 7440217
3237 IF x_cascaded_table(i).shipment_line_id =
3238 x_MatchedRec.shipment_line_id THEN
3239 IF (l_debug = 1) THEN
3240 print_debug('Already allocated some qty for this shipment_line',4);
3241 END IF;
3242 already_allocated_qty := already_allocated_qty +
3243 x_cascaded_table(i).source_doc_quantity;
3244 END IF;
3245 ELSIF p_match_type = 'RMA' THEN
3246 IF x_cascaded_table(i).oe_order_line_id =
3247 x_MatchedRec.oe_order_line_id THEN
3248 IF (l_debug = 1) THEN
3249 print_debug('Already allocated some qty for this rma',4);
3250 END IF;
3251 already_allocated_qty := already_allocated_qty +
3252 x_cascaded_table(i).source_doc_quantity;
3253 END IF;
3254 END IF;
3255 END LOOP;
3256 END IF;
3257
3258 ELSIF (x_cascaded_table(n).transaction_type = 'STD_DELIVER') THEN
3259
3260 IF (l_debug = 1) THEN
3261 print_debug('standard delivery rcv_transaction_id:'||x_MatchedRec.rcv_transaction_id,4);
3262 print_debug('receipt_source_code:'||x_MatchedRec.receipt_source_code,4);
3263 END IF;
3264
3265 -- For Bug 7440217 for LCM changes
3266 IF p_match_type IN ('ASN', 'LCM') THEN
3267 -- End for Bug 7440217
3268 print_debug('standard delivery',4);
3269 print_debug('po_distribution_id:'||x_MatchedRec.po_distribution_id,4);
3270 print_debug('rcv_transaction_id:'||x_MatchedRec.rcv_transaction_id,4);
3271 rcv_quantities_s.get_available_quantity(
3272 'STANDARD DELIVER',
3273 x_MatchedRec.po_distribution_id,
3274 'VENDOR',
3275 null,
3276 x_MatchedRec.rcv_transaction_id,
3277 null,
3278 x_converted_trx_qty,
3279 x_tolerable_qty,
3280 x_MatchedRec.unit_of_measure);
3281
3282 -- Bug 4004656-Added the following two assignment statements
3283 l_trx_qty_po_uom := x_converted_trx_qty;
3284 l_tol_qty_po_uom := x_tolerable_qty;
3285 -- End of fix for Bug 4004656
3286
3287 ELSE
3288 rcv_quantities_s.get_available_quantity
3289 ('DELIVER',
3290 x_MatchedRec.rcv_transaction_id,
3291 x_MatchedRec.receipt_source_code,
3292 NULL,
3293 NULL,--x_MatchedRec.rcv_transaction_id,
3294 NULL,
3295 x_converted_trx_qty,
3296 x_tolerable_qty,
3297 x_MatchedRec.unit_of_measure);
3298
3299 -- Bug 4004656-Added the following two assignment statements
3300 l_trx_qty_po_uom := x_converted_trx_qty;
3301 l_tol_qty_po_uom := x_tolerable_qty;
3302 -- End of fix for Bug 4004656
3303
3304
3305 END IF;
3306 IF (l_debug = 1) THEN
3307 -- print_debug('x_converted_trx_qty:'||x_converted_trx_qty,4); --Bug 4004656
3308 print_debug('l_trx_qty_po_uom:'||l_trx_qty_po_uom,4); --Bug 4004656
3309 -- print_debug('x_tolerable_qty:'||x_tolerable_qty,4); --Bug 4004656
3310 print_debug('l_tol_qty_po_uom:'||l_tol_qty_po_uom,4); --Bug 4004656
3311 print_debug('unit_of_measure:'||x_MatchedRec.unit_of_measure,4);
3312 END IF;
3313
3314 -- this is done because for the deliver transaction there is
3315 -- no concept of tolerance. Tolerances are only applicable at
3316 -- the time of receipt, so it does not matter what we assign it.
3317 -- x_tolerable_qty := x_converted_trx_qty; --Bug 4004656
3318 l_tol_qty_po_uom := l_trx_qty_po_uom ; --Bug 4004656
3319
3320 -- If qtys have already been allocated for this po_distribution_id
3321 -- during
3322 -- a cascade process which has not been written to the db yet, we need to
3323 -- decrement it from the total available quantity
3324 -- We traverse the actual pl/sql table and accumulate the quantity by
3325 -- matching the
3326 -- po_distribution_id
3327 IF n > 1 THEN -- We will do this for all rows except the 1st
3328 FOR i IN 1..(n-1) LOOP
3329 IF p_match_type = 'INTRANSIT SHIPMENT'
3330 -- For Bug 7440217 for LCM changes
3331 OR p_match_type in ('ASN', 'LCM') THEN
3332 -- End for Bug 7440217
3333 IF x_cascaded_table(i).shipment_line_id =
3334 x_MatchedRec.shipment_line_id AND
3335 x_cascaded_table(i).parent_transaction_id =
3336 x_MatchedRec.rcv_transaction_id THEN
3337 IF (l_debug = 1) THEN
3338 print_debug('Already allocated some qty for this shipment_line',4);
3339 END IF;
3340 already_allocated_qty := already_allocated_qty +
3341 x_cascaded_table(i).source_doc_quantity;
3342 END IF;
3343 ELSIF p_match_type = 'RMA' THEN
3344 IF x_cascaded_table(i).oe_order_line_id =
3345 x_MatchedRec.oe_order_line_id AND
3346 x_cascaded_table(i).parent_transaction_id =
3347 x_MatchedRec.rcv_transaction_id THEN
3348 IF (l_debug = 1) THEN
3349 print_debug('Already allocated some qty for this rma',4);
3350 END IF;
3351 already_allocated_qty := already_allocated_qty +
3352 x_cascaded_table(i).source_doc_quantity;
3353 END IF;
3354 END IF;
3355 END LOOP;
3356 END IF;
3357 END IF; -- type of transaction STD_DELIVER or RECEIVE or DELIVER
3358 IF (l_debug = 1) THEN
3359 print_debug('Total already allocated qty:'||already_allocated_qty,4);
3360 END IF;
3361
3362 -- if qty has already been allocated then reduce available and tolerable
3363 -- qty by the allocated amount
3364 /* Bug 4004656-Modified the block with the new quantity fileds.
3365 IF Nvl(already_allocated_qty,0) > 0 THEN
3366 x_converted_trx_qty := x_converted_trx_qty - already_allocated_qty;
3367 IF x_converted_trx_qty < 0 THEN
3368 x_converted_trx_qty := 0;
3369 END IF;
3370 END IF; */
3371
3372 IF Nvl(already_allocated_qty,0) > 0 THEN
3373 l_trx_qty_po_uom := l_trx_qty_po_uom - already_allocated_qty;
3374 IF l_trx_qty_po_uom < 0 THEN
3375 l_trx_qty_po_uom := 0;
3376 END IF;
3377 END IF;
3378 --End of fix for Bug 4004656
3379
3380 --x_remaining_qty_po_uom := 0; -- initialize --Bug 4004656
3381 l_rcv_qty_trans_uom := 0; --Bug 4004656
3382 l_rcv_qty_po_uom := 0; --Bug 4004656
3383 po_asn_uom_qty := 0; -- initialize
3384 po_primary_uom_qty := 0; -- initialize
3385
3386 /* Bug 4004656 -Commented out the conversion that is being done
3387 for the received quantity to the uom on the PO.
3388 Retained it in the transaction uom through the variable l_rcv_qty_trans_uom
3389 by assigning the value of the remaining quantity l_rem_qty_trans_uom
3390 which is already in the transaciton uom */
3391 /* x_remaining_qty_po_uom :=
3392 rcv_transactions_interface_sv.convert_into_correct_qty
3393 (x_remaining_quantity,
3394 temp_cascaded_table(1).unit_of_measure,
3395 temp_cascaded_table(1).item_id,
3396 x_MatchedRec.unit_of_measure); */
3397
3398 l_rcv_qty_trans_uom := l_rem_qty_trans_uom ; --Bug 4004656
3399
3400 /* Bug 4004656 - Printed debug messages for the quantities in the
3401 new quantity variables
3402 IF (l_debug = 1) THEN
3403 print_debug('x_remaining_qty_po_uom:'||x_remaining_qty_po_uom,4);
3404 print_debug('x_converted_trx_qty:'||x_converted_trx_qty,4);
3405 END IF; */
3406
3407 IF (l_debug = 1) THEN
3408 print_debug('l_rcv_qty_trans_uom:'||l_rcv_qty_trans_uom,4);
3409 print_debug('l_trx_qty_po_uom:'||l_trx_qty_po_uom,4);
3410 END IF;
3411
3412 /* Bug 4004656 -Modified the condition with the new quantity field variable values
3413 IF x_remaining_qty_po_uom <> 0 THEN -- otherwise no point in going further for this rec
3414
3415 IF x_converted_trx_qty > 0 THEN
3416 IF (x_converted_trx_qty < x_remaining_qty_po_uom) THEN
3417 -- compare like uoms
3418
3419 x_remaining_qty_po_uom := x_remaining_qty_po_uom -
3420 x_converted_trx_qty;
3421
3422 -- change asn uom qty so both qtys are in sync
3423 x_remaining_quantity :=
3424 rcv_transactions_interface_sv.convert_into_correct_qty
3425 (x_remaining_qty_po_uom,
3426 x_MatchedRec.unit_of_measure,
3427 temp_cascaded_table(1).item_id,
3428 temp_cascaded_table(1).unit_of_measure);
3429
3430 insert_into_table := TRUE;
3431 ELSE
3432 x_converted_trx_qty := x_remaining_qty_po_uom;
3433 insert_into_table := TRUE;
3434 x_remaining_qty_po_uom := 0;
3435 x_remaining_quantity := 0;
3436 END IF;
3437 IF (l_debug = 1) THEN
3438 print_debug('x_remaining_qty_po_uom:'||x_remaining_qty_po_uom,4);
3439 print_debug('x_converted_trx_qty:'||x_converted_trx_qty,4);
3440 print_debug('x_remaining_quantity:'||x_remaining_quantity,4);
3441 END IF;
3442
3443 ELSE -- no qty for this record but if last row we need it
3444 IF (l_debug = 1) THEN
3445 print_debug('no qty for this record but if last row we need it',4);
3446 END IF;
3447 IF rows_fetched = x_record_count THEN
3448 -- last row needs to be inserted anyway
3449 -- so that the row can be used based on qty tolerance
3450 -- checks
3451 insert_into_table := TRUE;
3452 x_converted_trx_qty := 0;
3453 ELSE
3454 x_remaining_qty_po_uom := 0;
3455 -- we may have a diff uom on the next iteration
3456 insert_into_table := FALSE;
3457 END IF;
3458 END IF;
3459 IF insert_into_table THEN
3460 IF (l_debug = 1) THEN
3461 print_debug('insert_into_table:TRUE',4);
3462 END IF;
3463 ELSE
3464 IF (l_debug = 1) THEN
3465 print_debug('insert_into_table:FLASE',4);
3466 END IF;
3467 END IF;
3468
3469 END IF; -- remaining_qty_po_uom <> 0 */
3470
3471
3472 IF round(l_rcv_qty_trans_uom,5) <> 0 THEN --Bug 4747997
3473 if l_trx_qty_po_uom > 0 then
3474 --Added the following code
3475 l_trx_qty_trans_uom:=
3476 rcv_transactions_interface_sv.convert_into_correct_qty
3477 (l_trx_qty_po_uom,
3478 x_MatchedRec.unit_of_measure,
3479 temp_cascaded_table(1).item_id,
3480 temp_cascaded_table(1).unit_of_measure);
3481
3482 IF (l_debug = 1) THEN
3483 print_debug('l_trx_qty_trans_uom:'||l_trx_qty_trans_uom,4);
3484 print_debug('l_rcv_qty_trans_uom:'||l_rcv_qty_trans_uom,4);
3485 print_debug('temp_cascaded_table(1).unit_of_measure:'||temp_cascaded_table(1).unit_of_measure,4); --Pounds from primary of item
3486 print_debug('temp_cascaded_table(1).item_id:'||temp_cascaded_table(1).item_id,4);
3487 print_debug('x_MatchedRec.unit_of_measure:'||x_MatchedRec.unit_of_measure,4); --KG from RSL
3488 END IF;
3489
3490 IF ((round(l_rcv_qty_trans_uom,5) - round(l_trx_qty_trans_uom,5)) > 0.00005) THEN --Bug: 4747997 /BUG13591755
3491 -- compare like uoms which is the transaction uom
3492 l_rcv_qty_trans_uom := l_rcv_qty_trans_uom - l_trx_qty_trans_uom;
3493 l_rcv_qty_po_uom:= rcv_transactions_interface_sv.convert_into_correct_qty(l_rcv_qty_trans_uom,
3494 temp_cascaded_table(1).unit_of_measure,
3495 temp_cascaded_table(1).item_id,
3496 x_MatchedRec.unit_of_measure);
3497
3498 -- change asn uom qty so both qtys are in sync
3499 l_rem_qty_trans_uom := l_rcv_qty_trans_uom ;
3500 insert_into_table := TRUE;
3501 ELSE
3502 l_trx_qty_trans_uom := l_rcv_qty_trans_uom;
3503 insert_into_table := TRUE;
3504 l_rcv_qty_trans_uom := 0;
3505 l_rcv_qty_po_uom := 0;
3506 l_rem_qty_trans_uom := 0;
3507 END IF;
3508
3509 IF (l_debug = 1) THEN
3510 print_debug('l_rcv_qty_trans_uom:'||l_rcv_qty_trans_uom,4);
3511 print_debug('l_rcv_qty_po_uom:'||l_rcv_qty_po_uom,4);
3512 print_debug('l_trx_qty_trans_uom:'||l_trx_qty_trans_uom,4);
3513 print_debug('l_rem_qty_trans_uom:'||l_rem_qty_trans_uom,4);
3514 END IF;
3515
3516 ELSE -- no qty for this record but if last row we need it
3517 IF (l_debug = 1) THEN
3518 print_debug('no qty for this record but if last row we need it',4);
3519 END IF;
3520 IF rows_fetched = x_record_count THEN
3521 -- last row needs to be inserted anyway
3522 -- so that the row can be used based on qty tolerance
3523 -- checks
3524
3525 insert_into_table := TRUE;
3526 l_trx_qty_trans_uom := 0;
3527 ELSE
3528 l_rcv_qty_trans_uom := 0;
3529 l_rcv_qty_po_uom := 0;
3530 -- we may have a diff uom on the next iteration
3531 insert_into_table := FALSE;
3532 END IF;
3533
3534 END IF;
3535 IF insert_into_table THEN
3536 IF (l_debug = 1) THEN
3537 print_debug('insert_into_table:TRUE',4);
3538 END IF;
3539 ELSE
3540 IF (l_debug = 1) THEN
3541 print_debug('insert_into_table:FLASE',4);
3542 END IF;
3543 END IF;
3544 END IF; -- remaining_qty_po_uom <> 0 *
3545
3546 IF insert_into_table THEN
3547 IF (x_first_trans) THEN
3548 x_first_trans := FALSE;
3549 ELSE
3550 temp_cascaded_table(current_n) := temp_cascaded_table(current_n - 1);
3551 END IF;
3552
3553 temp_cascaded_table(current_n).source_doc_quantity :=
3554 -- x_converted_trx_qty; -- in po uom --Bug 4004656
3555 rcv_transactions_interface_sv.convert_into_correct_qty(
3556 l_trx_qty_trans_uom ,
3557 temp_cascaded_table(current_n).unit_of_measure,
3558 temp_cascaded_table(current_n).item_id,
3559 x_MatchedRec.unit_of_measure);
3560 -- End of fix for bug4004656
3561
3562 temp_cascaded_table(current_n).source_doc_unit_of_measure :=
3563 x_MatchedRec.unit_of_measure;
3564
3565 IF (l_debug = 1) THEN
3566 print_debug('source_doc_quantity:'||temp_cascaded_table(current_n).source_doc_quantity,4);
3567 print_debug('source_doc_unit_of_measure:'||temp_cascaded_table(current_n).source_doc_unit_of_measure,4);
3568 END IF;
3569
3570 temp_cascaded_table(current_n).quantity :=
3571 /* Bug 4004656
3572 rcv_transactions_interface_sv.convert_into_correct_qty
3573 (x_converted_trx_qty,
3574 x_MatchedRec.unit_of_measure,
3575 temp_cascaded_table(current_n).item_id,
3576 temp_cascaded_table(current_n).unit_of_measure); -- in asn uom */
3577 l_trx_qty_trans_uom ; --Bug 4004656
3578
3579 IF (l_debug = 1) THEN
3580 print_debug('quantity:'||temp_cascaded_table(current_n).quantity,4);
3581 END IF;
3582
3583 -- Primary qty in Primary UOM
3584 temp_cascaded_table(current_n).primary_quantity :=
3585 /* Bug 4004656
3586 rcv_transactions_interface_sv.convert_into_correct_qty
3587 (x_converted_trx_qty,
3588 x_MatchedRec.unit_of_measure,
3589 temp_cascaded_table(current_n).item_id,
3590 temp_cascaded_table(current_n).primary_unit_of_measure); */
3591
3592 rcv_transactions_interface_sv.convert_into_correct_qty(
3593 l_trx_qty_trans_uom,
3594 temp_cascaded_table(current_n).unit_of_measure,
3595 temp_cascaded_table(current_n).item_id,
3596 temp_cascaded_table(current_n).primary_unit_of_measure);
3597 --End of fix for Bug 4004656
3598
3599 IF (l_debug = 1) THEN
3600 print_debug('primary_quantity:'||temp_cascaded_table(current_n).primary_quantity,4);
3601 END IF;
3602
3603 temp_cascaded_table(current_n).tax_amount :=
3604 Round(temp_cascaded_table(current_n).quantity * tax_amount_factor,4);
3605 IF (l_debug = 1) THEN
3606 print_debug('tax_amount:'||temp_cascaded_table(current_n).tax_amount,4);
3607 END IF;
3608 IF temp_cascaded_table(current_n).to_organization_id IS NULL THEN
3609 temp_cascaded_table(current_n).to_organization_id :=
3610 x_MatchedRec.to_organization_id;
3611 END IF;
3612 IF (l_debug = 1) THEN
3613 print_debug('to_organization_id:'||temp_cascaded_table(current_n).to_organization_id,4);
3614 END IF;
3615
3616 temp_cascaded_table(current_n).shipment_line_id := x_MatchedRec.shipment_line_id;
3617 temp_cascaded_table(current_n).oe_order_line_id := x_MatchedRec.oe_order_line_id;
3618 temp_cascaded_table(current_n).parent_transaction_id := x_MatchedRec.rcv_transaction_id;
3619 IF (l_debug = 1) THEN
3620 print_debug('shipment_line_id:'||temp_cascaded_table(current_n).shipment_line_id,4);
3621 print_debug('oe_order_line_id:'||temp_cascaded_table(current_n).oe_order_line_id,4);
3622 print_debug('parent_transaction_id:'||temp_cascaded_table(current_n).parent_transaction_id,4);
3623 END IF;
3624
3625 -- For Bug 7440217 for LCM changes
3626 IF p_match_type in ('ASN', 'LCM') THEN
3627 -- End for Bug 7440217
3628 temp_cascaded_table(current_n).po_header_id := x_MatchedRec.po_header_id;
3629 temp_cascaded_table(current_n).po_line_id := x_MatchedRec.po_line_id;
3630 temp_cascaded_table(current_n).po_line_location_id := x_MatchedRec.po_line_location_id;
3631 temp_cascaded_table(current_n).po_distribution_id := x_MatchedRec.po_distribution_id;
3632 temp_cascaded_table(current_n).item_desc := x_MatchedRec.item_description;
3633 IF (l_debug = 1) THEN
3634 print_debug('po_header_id:'||temp_cascaded_table(current_n).po_header_id,4);
3635 print_debug('po_line_id:'||temp_cascaded_table(current_n).po_line_id,4);
3636 print_debug('po_line_location_id:'||temp_cascaded_table(current_n).po_line_location_id,4);
3637 print_debug('po_distribution_id:'||temp_cascaded_table(current_n).po_distribution_id,4);
3638 print_debug('item_desc:'||temp_cascaded_table(current_n).item_desc,4);
3639 END IF;
3640 END IF;
3641
3642 current_n := current_n + 1;
3643
3644 END IF;
3645 END LOOP;
3646
3647 -- point to the last row in the record structure before going back
3648
3649 ELSE -- for the very first if
3650 -- error_status and error_message are set after validate_quantity_shipped
3651 IF x_cascaded_table(n).error_status IN ('S','W','F') THEN
3652 x_cascaded_table(n).error_status := 'E';
3653 IF (x_cascaded_table(n).error_message IS NULL) THEN
3654 -- For Bug 7440217 for LCM changes
3655 IF p_match_type = 'ASN' THEN
3656 x_cascaded_table(n).error_message := 'RCV_ASN_NO_PO_LINE_LOCATION_ID';
3657 ELSIF p_match_type = 'LCM' THEN
3658 x_cascaded_table(n).error_message := 'RCV_LCM_NO_PO_LINE_LOCATION_ID';
3659 END IF;
3660 -- End for Bug 7440217
3661 END IF;
3662 END IF;
3663 RETURN;
3664 END IF; -- of (asn quantity_shipped was valid)
3665
3666 -- close cursors
3667 IF int_req_receipt_lines%isopen THEN
3668 CLOSE int_req_receipt_lines;
3669 END IF;
3670
3671 IF count_int_req_receipt_lines%isopen THEN
3672 CLOSE count_int_req_receipt_lines;
3673 END IF;
3674
3675 IF rma_receipt_lines%isopen THEN
3676 CLOSE rma_receipt_lines;
3677 END IF;
3678
3679 IF count_rma_receipt_lines%isopen THEN
3680 CLOSE count_rma_receipt_lines;
3681 END IF;
3682
3683 IF asn_receipt_lines%isopen THEN
3684 CLOSE asn_receipt_lines;
3685 END IF;
3686
3687 IF count_asn_receipt_lines%isopen THEN
3688 CLOSE count_asn_receipt_lines;
3689 END IF;
3690
3691 IF asn_direct_lines%isopen THEN
3692 CLOSE asn_direct_lines;
3693 END IF;
3694
3695 IF count_asn_direct_lines%isopen THEN
3696 CLOSE count_asn_direct_lines;
3697 END IF;
3698
3699 -- For Bug 7440217 for LCM changes
3700 IF lcm_receipt_lines%isopen THEN
3701 CLOSE lcm_receipt_lines;
3702 END IF;
3703
3704 IF count_lcm_receipt_lines%isopen THEN
3705 CLOSE count_lcm_receipt_lines;
3706 END IF;
3707
3708 IF lcm_direct_lines%isopen THEN
3709 CLOSE lcm_direct_lines;
3710 END IF;
3711
3712 IF count_lcm_direct_lines%isopen THEN
3713 CLOSE count_lcm_direct_lines;
3714 END IF;
3715
3716 -- End for Bug 7440217
3717
3718
3719 IF int_req_delivery_lines%isopen THEN
3720 CLOSE int_req_delivery_lines;
3721 END IF;
3722
3723 IF count_int_req_delivery_lines%isopen THEN
3724 CLOSE count_int_req_delivery_lines;
3725 END IF;
3726
3727 IF rma_delivery_lines%isopen THEN
3728 CLOSE rma_delivery_lines;
3729 END IF;
3730
3731
3732
3733 EXCEPTION
3734 WHEN fnd_api.g_exc_error THEN
3735 ROLLBACK TO rcv_transactions_gen_sa;
3736 x_return_status := fnd_api.g_ret_sts_error;
3737
3738 -- Get message count and data
3739 fnd_msg_pub.count_and_get
3740 ( p_count => x_msg_count
3741 , p_data => x_msg_data
3742 );
3743 -- close cursors
3744 IF int_req_receipt_lines%isopen THEN
3745 CLOSE int_req_receipt_lines;
3746 END IF;
3747
3748 IF count_int_req_receipt_lines%isopen THEN
3749 CLOSE count_int_req_receipt_lines;
3750 END IF;
3751
3752 IF rma_receipt_lines%isopen THEN
3753 CLOSE rma_receipt_lines;
3754 END IF;
3755
3756 IF count_rma_receipt_lines%isopen THEN
3757 CLOSE count_rma_receipt_lines;
3758 END IF;
3759
3760 IF asn_receipt_lines%isopen THEN
3761 CLOSE asn_receipt_lines;
3762 END IF;
3763
3764 IF count_asn_receipt_lines%isopen THEN
3765 CLOSE count_asn_receipt_lines;
3766 END IF;
3767
3768 IF asn_direct_lines%isopen THEN
3769 CLOSE asn_direct_lines;
3770 END IF;
3771
3772 IF count_asn_direct_lines%isopen THEN
3773 CLOSE count_asn_direct_lines;
3774 END IF;
3775
3776 -- For Bug 7440217 for LCM changes
3777 IF lcm_receipt_lines%isopen THEN
3778 CLOSE lcm_receipt_lines;
3779 END IF;
3780
3781 IF count_lcm_receipt_lines%isopen THEN
3782 CLOSE count_lcm_receipt_lines;
3783 END IF;
3784
3785 IF lcm_direct_lines%isopen THEN
3786 CLOSE lcm_direct_lines;
3787 END IF;
3788
3789 IF count_lcm_direct_lines%isopen THEN
3790 CLOSE count_lcm_direct_lines;
3791 END IF;
3792 -- End for Bug 7440217
3793
3794 IF int_req_delivery_lines%isopen THEN
3795 CLOSE int_req_delivery_lines;
3796 END IF;
3797
3798 IF count_int_req_delivery_lines%isopen THEN
3799 CLOSE count_int_req_delivery_lines;
3800 END IF;
3801
3802 IF rma_delivery_lines%isopen THEN
3803 CLOSE rma_delivery_lines;
3804 END IF;
3805
3806 IF count_rma_delivery_lines%isopen THEN
3807 CLOSE count_asn_direct_lines;
3808 END IF;
3809
3810 IF asn_delivery_lines%isopen THEN
3811 CLOSE int_req_delivery_lines;
3812 END IF;
3813
3814 IF count_asn_delivery_lines%isopen THEN
3815 CLOSE count_int_req_delivery_lines;
3816 END IF;
3817
3818 WHEN fnd_api.g_exc_unexpected_error THEN
3819 ROLLBACK TO rcv_transactions_gen_sa;
3820 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3821
3822 -- Get message count and data
3823 fnd_msg_pub.count_and_get
3824 ( p_count => x_msg_count
3825 , p_data => x_msg_data
3826 );
3827 -- close cursors
3828 IF int_req_receipt_lines%isopen THEN
3829 CLOSE int_req_receipt_lines;
3830 END IF;
3831
3832 IF count_int_req_receipt_lines%isopen THEN
3833 CLOSE count_int_req_receipt_lines;
3834 END IF;
3835
3836 IF rma_receipt_lines%isopen THEN
3837 CLOSE rma_receipt_lines;
3838 END IF;
3839
3840 IF count_rma_receipt_lines%isopen THEN
3841 CLOSE count_rma_receipt_lines;
3842 END IF;
3843
3844 IF asn_receipt_lines%isopen THEN
3845 CLOSE asn_receipt_lines;
3846 END IF;
3847
3848 IF count_asn_receipt_lines%isopen THEN
3849 CLOSE count_asn_receipt_lines;
3850 END IF;
3851
3852 IF asn_direct_lines%isopen THEN
3853 CLOSE asn_direct_lines;
3854 END IF;
3855
3856 IF count_asn_direct_lines%isopen THEN
3857 CLOSE count_asn_direct_lines;
3858 END IF;
3859
3860 -- For Bug 7440217 for LCM changes
3861 IF lcm_receipt_lines%isopen THEN
3862 CLOSE lcm_receipt_lines;
3863 END IF;
3864
3865 IF count_lcm_receipt_lines%isopen THEN
3866 CLOSE count_lcm_receipt_lines;
3867 END IF;
3868
3869 IF lcm_direct_lines%isopen THEN
3870 CLOSE lcm_direct_lines;
3871 END IF;
3872
3873 IF count_lcm_direct_lines%isopen THEN
3874 CLOSE count_lcm_direct_lines;
3875 END IF;
3876 -- End for Bug 7440217
3877
3878 IF int_req_delivery_lines%isopen THEN
3879 CLOSE int_req_delivery_lines;
3880 END IF;
3881
3882 IF count_int_req_delivery_lines%isopen THEN
3883 CLOSE count_int_req_delivery_lines;
3884 END IF;
3885
3886 IF rma_delivery_lines%isopen THEN
3887 CLOSE rma_delivery_lines;
3888 END IF;
3889
3890 WHEN OTHERS THEN
3891 ROLLBACK TO rcv_transactions_gen_sa;
3892 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3893
3894 IF SQLCODE IS NOT NULL THEN
3895 inv_mobile_helper_functions.sql_error('inv_rcv_txn_match.matching_logic', l_progress, SQLCODE);
3896 END IF;
3897 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
3898 THEN
3899 fnd_msg_pub.add_exc_msg
3900 ( g_pkg_name
3901 , l_api_name
3902 );
3903 END IF;
3904
3905 -- Get message count and data
3906 fnd_msg_pub.count_and_get
3907 ( p_count => x_msg_count
3908 , p_data => x_msg_data
3909 );
3910
3911 -- close cursors
3912 IF int_req_receipt_lines%isopen THEN
3913 CLOSE int_req_receipt_lines;
3914 END IF;
3915
3916 IF count_int_req_receipt_lines%isopen THEN
3917 CLOSE count_int_req_receipt_lines;
3918 END IF;
3919
3920 IF rma_receipt_lines%isopen THEN
3921 CLOSE rma_receipt_lines;
3922 END IF;
3923
3924 IF count_rma_receipt_lines%isopen THEN
3925 CLOSE count_rma_receipt_lines;
3926 END IF;
3927
3928 IF asn_receipt_lines%isopen THEN
3929 CLOSE asn_receipt_lines;
3930 END IF;
3931
3932 IF count_asn_receipt_lines%isopen THEN
3933 CLOSE count_asn_receipt_lines;
3934 END IF;
3935
3936 IF asn_direct_lines%isopen THEN
3937 CLOSE asn_direct_lines;
3938 END IF;
3939
3940 IF count_asn_direct_lines%isopen THEN
3941 CLOSE count_asn_direct_lines;
3942 END IF;
3943
3944 -- For Bug 7440217 for LCM changes
3945 IF lcm_receipt_lines%isopen THEN
3946 CLOSE lcm_receipt_lines;
3947 END IF;
3948
3949 IF count_lcm_receipt_lines%isopen THEN
3950 CLOSE count_lcm_receipt_lines;
3951 END IF;
3952
3953 IF lcm_direct_lines%isopen THEN
3954 CLOSE lcm_direct_lines;
3955 END IF;
3956
3957 IF count_lcm_direct_lines%isopen THEN
3958 CLOSE count_lcm_direct_lines;
3959 END IF;
3960 -- End for Bug 7440217
3961
3962 IF int_req_delivery_lines%isopen THEN
3963 CLOSE int_req_delivery_lines;
3964 END IF;
3965
3966 IF count_int_req_delivery_lines%isopen THEN
3967 CLOSE count_int_req_delivery_lines;
3968 END IF;
3969
3970 IF rma_delivery_lines%isopen THEN
3971 CLOSE rma_delivery_lines;
3972 END IF;
3973
3974 x_cascaded_table(n).error_status := 'E';
3975
3976 END matching_logic;
3977
3978 END INV_rcv_txn_match;
[Home] [Help]