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