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