1 PACKAGE BODY INV_RCV_TXN_INTERFACE AS
2 /* $Header: INVTISVB.pls 120.7.12010000.6 2009/02/06 08:08:08 vssrivat ship $*/
3
4 x_interface_type varchar2(25) := 'RCV-856';
5 x_dummy_flag varchar2(1) := 'Y';
6
7 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_RCV_TXN_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 pll,
73 po_lines pl,
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 pod
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 pll,
138 po_lines pl,
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 pod
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 pll,
216 po_lines pl,
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 pod
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 pll,
286 po_lines pl,
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 pod
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
366 FROM
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
367 po_line_locations pll,
368 po_lines pl,
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 pod
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 pll,
433 po_lines pl,
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 pod
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)
478 CURSOR lcm_shipments_w_po
475 and pod.po_header_id = pll.po_header_id
476 );
477
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 pll,
511 po_lines pl,
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 pod
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 pll,
581 po_lines pl,
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
601 AND NVL(pll.APPROVED_FLAG,'N') = 'Y'
598 OR ( v_item_id IS NULL
599 AND pl.item_id IS NULL
600 AND pl.item_description = v_item_desc))
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 pod
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 is
647 select
648 pll.line_location_id
649 , pll.unit_meas_lookup_code
650 , nvl(pll.promised_date,pll.need_by_date) promised_date
651 , pll.quantity_shipped
652 , pll.receipt_days_exception_code
653 , pll.qty_rcv_exception_code
654 , pll.days_early_receipt_allowed
655 , pll.days_late_receipt_allowed
656 , 0 po_distribution_id
657 , pll.ship_to_location_id
658 , nvl(pll.enforce_ship_to_location_code,'NONE') enforce_ship_to_location_code
659 , 0 rcv_transaction_id -- only need it for std_deliver
660 , pl.item_revision --only needed for std_deliver
661 from po_line_locations_all pll,
662 po_lines_all pl
663 -- For Bug 7440217
664 , mtl_parameters mp,
665 rcv_parameters rp
666 -- End for Bug 7440217
667 -- Bug 3444226 The Join with po_headers is unnecessary
668 -- po_headers_all ph
669 -- where ph.po_header_id = header_id
670 where pll.po_header_id = header_id
671 -- For Bug 7440217
672 AND mp.organization_id = v_organization_id
673 AND rp.organization_id = v_organization_id
674 AND ( (NVL(mp.lcm_enabled_flag,'N') = 'N') -- Org is non-lcm enabled
675 OR (NVL(rp.pre_receive,'N') = 'N') -- Org is lcm enabled and it is post-receiving
676 OR (NVL(pll.lcm_flag,'N') = 'N') -- Org is lcm enabled, pre-receiving and non-lcm enabled shipment
677 )
678 -- End for Bug 7440217
679 AND pll.po_line_id = Nvl(v_po_line_id, pll.po_line_id)
680 AND pll.line_location_id = Nvl(v_po_line_location_id, pll.line_location_id)
681 and nvl(pll.po_release_id,0) = nvl(v_po_release_id,nvl(pll.po_release_id,0))
682 and pll.po_line_id = pl.po_line_id
683 -- change for non item master receipts.
684 and ( pl.item_id = v_item_id
685 OR ( v_item_id IS NULL
686 AND pl.item_id IS NULL
687 AND pl.item_description = v_item_desc))
688 AND ((v_revision IS NOT NULL
689 AND Nvl(pl.item_revision, v_revision) = v_revision)
690 OR (v_revision IS NULL))
691 and NVL(pll.APPROVED_FLAG,'N') = 'Y'
692 and NVL(pll.CANCEL_FLAG, 'N') = 'N'
693 and NVL(pll.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING','CLOSED')
694 and pll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
695 and pll.ship_to_organization_id = nvl(v_ship_to_org_id,pll.ship_to_organization_id)
696 and pll.ship_to_location_id = nvl(v_ship_to_location_id,pll.ship_to_location_id)
697 AND Nvl(pll.receiving_routing_id,1) = Decode(l_allow_routing_override,
698 'Y',
699 Nvl(pll.receiving_routing_id,1),
700 Decode(v_inspection_status_code,
701 'Y',
702 2,
703 1)
704 )
705 AND pll.line_location_id in
706 ( select pod.line_location_id
707 from po_distributions pod
708 where (v_project_id is null
709 or ((v_project_id = -9999 and pod.project_id is null)--Bug# 2669021
710 or (nvl(pod.project_id,-99) = v_project_id )))
714 order by pl.item_revision, nvl(pll.promised_date,pll.need_by_date);
711 and (v_task_id is null or nvl(pod.task_id,-9999) = v_task_id)
712 and pod.po_header_id = pll.po_header_id
713 )
715
716
717 CURSOR count_shipments
718 ( header_id NUMBER
719 , v_item_id NUMBER
720 , v_revision VARCHAR2
721 , v_po_line_id NUMBER
722 , v_po_line_location_id NUMBER
723 , v_po_release_id NUMBER
724 , v_ship_to_org_id NUMBER
725 , v_ship_to_location_id NUMBER
726 , v_item_desc VARCHAR2
727 , v_project_id NUMBER
728 , v_task_id NUMBER
729 , v_inspection_status_code VARCHAR2
730 , v_organization_id NUMBER)
731
732 IS
733 SELECT COUNT(*)
734 from po_line_locations_all pll,
735 po_lines_all pl
736 -- For Bug 7440217
737 , mtl_parameters mp,
738 rcv_parameters rp
739 -- End for Bug 7440217
740 WHERE pll.po_header_id = header_id
741 -- For Bug 7440217
742 AND mp.organization_id = v_organization_id
743 AND rp.organization_id = v_organization_id
744 AND ( (NVL(mp.lcm_enabled_flag,'N') = 'N') -- Org is non-lcm enabled
745 OR (NVL(rp.pre_receive,'N') = 'N') -- Org is lcm enabled and it is post-receiving
746 OR (NVL(pll.lcm_flag,'N') = 'N') -- Org is lcm enabled, pre-receiving and non-lcm enabled shipment
747 )
748 -- End for Bug 7440217
749 AND pll.po_line_id = Nvl(v_po_line_id, pll.po_line_id)
750 AND pll.line_location_id = Nvl(v_po_line_location_id, pll.line_location_id)
751 and nvl(pll.po_release_id,0) = nvl(v_po_release_id,nvl(pll.po_release_id,0))
752 and pll.po_line_id = pl.po_line_id
753 -- change for non item master receipts.
754 and ( pl.item_id = v_item_id
755 OR ( v_item_id IS NULL
756 AND pl.item_id IS NULL
757 AND pl.item_description = v_item_desc))
758 -- and pl.item_id = v_item_id
759 AND ((v_revision IS NOT NULL
760 AND Nvl(pl.item_revision, v_revision) = v_revision)
761 OR (v_revision IS NULL))
762 and NVL(pll.APPROVED_FLAG,'N') = 'Y'
763 and NVL(pll.CANCEL_FLAG, 'N') = 'N'
764 and NVL(pll.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING','CLOSED')
765 and pll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
766 and pll.ship_to_organization_id = nvl(v_ship_to_org_id,pll.ship_to_organization_id)
767 and pll.ship_to_location_id = nvl(v_ship_to_location_id,pll.ship_to_location_id)
768 AND Nvl(pll.receiving_routing_id,1) = Decode(l_allow_routing_override,
769 'Y',
770 Nvl(pll.receiving_routing_id,1),
771 Decode(v_inspection_status_code,
772 'Y',
773 2,
774 1)
775 )
776 AND pll.line_location_id in
777 ( select pod.line_location_id
778 from po_distributions pod
779 where (v_project_id is null
780 or ((v_project_id = -9999 and pod.project_id is null)--Bug# 2669021
781 or (nvl(pod.project_id,-99) = v_project_id )))
782 and (v_task_id is null or nvl(pod.task_id,-9999) = v_task_id)
783 and pod.po_header_id = pll.po_header_id
784 );
785
786 cursor distributions (
787 header_id number
788 ,v_item_id number
789 ,v_revision VARCHAR2
790 ,v_po_line_id NUMBER
791 ,v_po_line_location_id NUMBER
792 ,v_po_distribution_id NUMBER
793 ,v_po_release_id number
794 ,v_ship_to_org_id number
795 ,v_ship_to_location_id NUMBER
796 ,v_item_desc VARCHAR2
797 , v_project_id NUMBER
798 , v_task_id NUMBER
799 , v_organization_id NUMBER) is
800 select
801 pll.line_location_id
802 ,pll.unit_meas_lookup_code
803 ,nvl(pll.promised_date,pll.need_by_date) promised_date
804 ,pll.quantity_shipped
805 ,pll.receipt_days_exception_code
806 ,pll.qty_rcv_exception_code
807 ,pll.days_early_receipt_allowed
808 ,pll.days_late_receipt_allowed
809 ,pod.po_distribution_id
810 ,pll.ship_to_location_id
811 ,nvl(pll.enforce_ship_to_location_code,'NONE') enforce_ship_to_location_code
812 ,0 rcv_transaction_id -- only need it for std_deliver
813 ,pl.item_revision --only needed for std_deliver
814 from po_distributions pod,
815 po_line_locations pll,
816 po_lines pl,
817 po_headers ph
818 where ph.po_header_id = header_id
819 and pod.po_header_id = header_id
820 AND pod.po_line_id = Nvl(v_po_line_id, pod.po_line_id)
821 AND pod.line_location_id = Nvl(v_po_line_location_id, pod.line_location_id)
822 AND pod.po_distribution_id = Nvl(v_po_distribution_id, pod.po_distribution_id)
823 and ph.po_header_id = pl.po_header_id
824 and nvl(pll.po_release_id,0) = nvl(v_po_release_id,nvl(pll.po_release_id,0))
825 and pll.po_line_id = pl.po_line_id
826 --and pl.item_id = v_item_id
827 -- change for non item master receipts.
828 and ( pl.item_id = v_item_id
832 AND ((v_revision IS NOT NULL
829 OR ( v_item_id IS NULL
830 AND pl.item_id IS NULL
831 AND pl.item_description = v_item_desc))
833 AND Nvl(pl.item_revision, v_revision) = v_revision)
834 OR (v_revision IS NULL))
835 and NVL(pll.APPROVED_FLAG,'N') = 'Y'
836 and NVL(pll.CANCEL_FLAG, 'N') = 'N'
837 and NVL(pll.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING','CLOSED')
838 and pll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
839 and pll.line_location_id = pod.line_location_id
840 and pll.ship_to_organization_id = nvl(v_ship_to_org_id,pll.ship_to_organization_id)
841 and pll.ship_to_location_id = nvl(v_ship_to_location_id,pll.ship_to_location_id)
842 AND Nvl(pll.receiving_routing_id,1) = Decode(l_allow_routing_override,
843 'Y',
844 Nvl(pll.receiving_routing_id,1),
845 3)
846 and (v_project_id is null or
847 ( v_project_id = -9999 and pod.project_id is null ) or --Bug# 2669021
848 pod.project_id = v_project_id)
849 and (v_task_id is null or pod.task_id = v_task_id)
850 order by pl.item_revision, nvl(pll.promised_date,pll.need_by_date);
851
852 cursor count_distributions (
853 header_id number
854 , v_item_id number
855 , v_revision VARCHAR2
856 , v_po_line_id NUMBER
857 , v_po_line_location_id NUMBER
858 , v_po_distribution_id NUMBER
859 , v_po_release_id number
860 , v_ship_to_org_id number
861 , v_ship_to_location_id number
862 , v_item_desc VARCHAR2
863 , v_project_id NUMBER
864 , v_task_id NUMBER
865 , v_organization_id NUMBER) is
866 select count(*)
867 from po_distributions pod,
868 po_line_locations pll,
869 po_lines pl
870 where pll.po_header_id = header_id
871 AND pod.po_line_id = Nvl(v_po_line_id, pod.po_line_id)
872 AND pod.line_location_id = Nvl(v_po_line_location_id, pod.line_location_id)
873 AND pod.po_distribution_id = Nvl(v_po_distribution_id, pod.po_distribution_id)
874 and nvl(pll.po_release_id,0) = nvl(v_po_release_id,nvl(pll.po_release_id,0))
875 and pll.po_line_id = pl.po_line_id
876 --and pl.item_id = v_item_id
877 -- change for non item master receipts.
878 and ( pl.item_id = v_item_id
879 OR ( v_item_id IS NULL
880 AND pl.item_id IS NULL
881 AND pl.item_description = v_item_desc))
882 AND ((v_revision IS NOT NULL
883 AND Nvl(pl.item_revision, v_revision) = v_revision)
884 OR (v_revision IS NULL))
885 and NVL(pll.APPROVED_FLAG,'N') = 'Y'
886 and NVL(pll.CANCEL_FLAG, 'N') = 'N'
887 and NVL(pll.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING','CLOSED')
888 and pll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
889 and pll.line_location_id = pod.line_location_id
890 and pll.ship_to_organization_id = nvl(v_ship_to_org_id,pll.ship_to_organization_id)
891 and pll.ship_to_location_id = nvl(v_ship_to_location_id,pll.ship_to_location_id)
892 AND Nvl(pll.receiving_routing_id,1) = Decode(l_allow_routing_override,
893 'Y',
894 Nvl(pll.receiving_routing_id,1),
895 3)
896 and (v_project_id is null or
897 ( v_project_id = -9999 and pod.project_id is null ) or --Bug# 2669021
898 pod.project_id = v_project_id)
899 and (v_task_id is null or pod.task_id = v_task_id) ;
900
901 ----
902 /* FP-J Lot/Serial Support Enhancement
903 * Added two new arguments to the cursor, v_lot_number and v_lpn_id_to_match
904 * Added conditions to match the lot number with that in RCV_LOTS_SUPPLY
905 * and the LPN with that in RCV_SUPPLY for the parent trasnaction
906 * This would be done only if WMS and PO patchset levels are J or higher
907 */
908 CURSOR std_distributions (
909 header_id NUMBER
910 ,v_item_id NUMBER
911 ,v_revision VARCHAR2
912 ,v_po_line_id NUMBER
913 ,v_po_line_location_id NUMBER
914 ,v_po_distribution_id NUMBER
915 ,v_po_release_id NUMBER
916 ,v_ship_to_org_id NUMBER
917 ,v_ship_to_location_id NUMBER
918 ,v_receipt_num VARCHAR2
919 ,v_txn_date DATE
920 ,v_inspection_status VARCHAR2
921 ,v_lpn_id NUMBER
922 ,v_lot_number VARCHAR2
923 ,v_lpn_id_to_match NUMBER
924 ,v_parent_txn_id_to_match NUMBER
925 , v_organization_id NUMBER) IS
926 SELECT
927 pll.line_location_id
928 ,pll.unit_meas_lookup_code
929 ,nvl(pll.promised_date,pll.need_by_date) promised_date
930 ,0 --pll.quantity_shipped
931 ,pll.receipt_days_exception_code
932 ,pll.qty_rcv_exception_code
933 ,pll.days_early_receipt_allowed
934 ,pll.days_late_receipt_allowed
935 ,pod.po_distribution_id
936 ,pll.ship_to_location_id
937 ,nvl(pll.enforce_ship_to_location_code,'NONE') enforce_ship_to_location_code
938 ,rs.rcv_transaction_id
939 ,rs.item_revision
940 from po_distributions pod,
941 po_line_locations pll,
942 po_lines pl,
943 -- Bug 3444226 The Join with po_headers is unnecessary
944 -- po_headers ph,
945 rcv_supply rs,
946 rcv_shipment_headers rsh,
950 -- Bug 3444226 The Join with po_headers is unnecessary
947 -- rcv_shipment_lines rsl,
948 rcv_transactions rt
949 where rsh.receipt_source_code = 'VENDOR'
951 -- AND ph.po_header_id = header_id
952 AND pod.po_line_id = Nvl(v_po_line_id, pod.po_line_id)
953 AND pod.line_location_id = Nvl(v_po_line_location_id, pod.line_location_id)
954 AND pod.po_distribution_id = Nvl(v_po_distribution_id, pod.po_distribution_id)
955 and pl.po_line_id = rs.po_line_id
956 and pll.line_location_id = rs.po_line_location_id
957 and pod.line_location_id = rs.po_line_location_id
958 -- and pl.item_id = v_item_id
959 and NVL(pll.APPROVED_FLAG,'N') = 'Y'
960 and NVL(pll.CANCEL_FLAG, 'N') = 'N'
961 and NVL(pll.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED')
962 and pll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
963 and pll.ship_to_organization_id = nvl(v_ship_to_org_id,pll.ship_to_organization_id)
964 and pll.ship_to_location_id = nvl(v_ship_to_location_id,pll.ship_to_location_id)
965 AND RT.TRANSACTION_TYPE <> 'UNORDERED'
966 -- for all the transactions in rt for which we can putaway, the
967 -- transfer_lpn_id should match the lpn being putaway.
968 --AND Nvl(rt.transfer_lpn_id,-1) = Nvl(v_lpn_id,-1)
969 -- Fix for 1865886. Commented the above and added the following for lpn
970 AND Nvl(v_lpn_id,-1) IN (select nvl(rt2.transfer_lpn_id,-1)
971 from rcv_transactions rt2
972 where rt2.transaction_type <> 'DELIVER'
973 start with rt2.transaction_id = rs.supply_source_id
974 connect by prior rt2.transaction_id = rt2.parent_transaction_id
975 union all
976 select nvl(rt2.lpn_id,-1)
977 from rcv_transactions rt2
978 where rt2.transaction_type not in ('RECEIVE', 'DELIVER')
979 start with rt2.transaction_id = rs.supply_source_id
980 connect by prior rt2.transaction_id = rt2.parent_transaction_id
981 )
982 --
983 and rs.supply_type_code = 'RECEIVING'
984 --and rsl.shipment_line_id = rs.shipment_line_id
985 and rsh.shipment_header_id = rs.shipment_header_id
986 AND (Nvl(rsh.receipt_num,'@@@')) = Nvl(v_receipt_num,Nvl(rsh.receipt_num,'@@@'))
987 and rt.transaction_id = rs.rcv_transaction_id
988 AND rt.transaction_date < Nvl(v_txn_date,(rt.transaction_date + 1))
989 --and rt.transaction_type <> 'UNORDERED'
990 --
991 and rs.po_header_id = header_id
992 and rs.item_id = v_item_id
993 AND ((v_revision IS NOT NULL
994 AND Nvl(rs.item_revision, v_revision) = v_revision)
995 OR (v_revision IS NULL))
996 AND (rt.routing_header_id IS NULL OR
997 rt.routing_header_id <> 2 OR
998 (rt.routing_header_id = 2
999 AND rt.inspection_status_code <> 'NOT INSPECTED'
1000 AND rt.inspection_status_code = Nvl(v_inspection_status,rt.inspection_status_code)))
1001 AND (
1002 v_lot_number IS NULL OR EXISTS
1003 (
1004 SELECT lot_num
1005 FROM rcv_lots_supply rls
1006 WHERE rls.transaction_id = rs.supply_source_id
1007 AND rls.lot_num = v_lot_number
1008 )
1009 )
1010 AND (
1011 v_parent_txn_id_to_match IS NULL
1012 OR v_parent_txn_id_to_match = rs.supply_source_id
1013 )
1014 AND (
1015 v_lpn_id_to_match IS NULL
1016 OR (rs.lpn_id = v_lpn_id_to_match)
1017 )
1018 --
1019 order by rs.item_revision, nvl(pll.promised_date,pll.need_by_date);
1020
1021 CURSOR count_std_distributions (
1022 header_id NUMBER
1023 , v_item_id NUMBER
1024 , v_revision VARCHAR2
1025 , v_po_line_id NUMBER
1026 , v_po_line_location_id NUMBER
1027 , v_po_distribution_id NUMBER
1028 , v_po_release_id NUMBER
1029 , v_ship_to_org_id NUMBER
1030 , v_ship_to_location_id NUMBER
1031 , v_receipt_num VARCHAR2
1032 , v_txn_date DATE
1033 , v_inspection_status VARCHAR2
1034 , v_lpn_id NUMBER
1035 , v_lot_number VARCHAR2
1036 , v_lpn_id_to_match NUMBER
1037 ,v_parent_txn_id_to_match NUMBER
1038 , v_organization_id NUMBER) IS
1039 SELECT count(*)
1040 FROM po_distributions pod,
1041 po_line_locations pll,
1042 po_lines pl,
1043 -- Bug 3444226 The Join with po_headers is unnecessary
1044 -- po_headers ph,
1045 rcv_supply rs,
1046 rcv_shipment_headers rsh,
1047 -- rcv_shipment_lines rsl,
1048 rcv_transactions rt
1049 where rsh.receipt_source_code = 'VENDOR'
1050 -- Bug 3444226 The Join with po_headers is unnecessary
1051 -- AND ph.po_header_id = header_id
1052 AND pod.po_line_id = Nvl(v_po_line_id, pod.po_line_id)
1053 AND pod.line_location_id = Nvl(v_po_line_location_id, pod.line_location_id)
1054 AND pod.po_distribution_id = Nvl(v_po_distribution_id, pod.po_distribution_id)
1055 and pl.po_line_id = rs.po_line_id
1056 and pll.line_location_id = rs.po_line_location_id
1057 and pod.line_location_id = rs.po_line_location_id
1058 -- and NVL(pl.item_id,0) = nvl(v_item_id,nvl(pl.item_id,0))
1059 and NVL(pll.APPROVED_FLAG,'N') = 'Y'
1060 and NVL(pll.CANCEL_FLAG, 'N') = 'N'
1064 and pll.ship_to_location_id = nvl(v_ship_to_location_id,pll.ship_to_location_id)
1061 and NVL(pll.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED')
1062 and pll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
1063 and pll.ship_to_organization_id = nvl(v_ship_to_org_id,pll.ship_to_organization_id)
1065 AND RT.TRANSACTION_TYPE <> 'UNORDERED'
1066 -- for all the transactions in rt for which we can putaway, the
1067 -- transfer_lpn_id should match the lpn being putaway.
1068 --AND Nvl(rt.transfer_lpn_id,-1) = Nvl(v_lpn_id,-1)
1069 -- Fix for 1865886. Commented the above and added the following for lpn
1070 AND Nvl(v_lpn_id,-1) IN (select nvl(rt2.transfer_lpn_id,-1)
1071 from rcv_transactions rt2
1072 where rt2.transaction_type <> 'DELIVER'
1073 start with rt2.transaction_id = rs.supply_source_id
1074 connect by prior rt2.transaction_id = rt2.parent_transaction_id
1075 union all
1076 select nvl(rt2.lpn_id,-1)
1077 from rcv_transactions rt2
1078 where rt2.transaction_type not in ('RECEIVE', 'DELIVER')
1079 start with rt2.transaction_id = rs.supply_source_id
1080 connect by prior rt2.transaction_id = rt2.parent_transaction_id
1081 )
1082 --
1083 and rs.supply_type_code = 'RECEIVING'
1084 --and rsl.shipment_line_id = rs.shipment_line_id
1085 and rsh.shipment_header_id = rs.shipment_header_id
1086 AND (Nvl(rsh.receipt_num,'@@@')) = Nvl(v_receipt_num,Nvl(rsh.receipt_num,'@@@'))
1087 and rt.transaction_id = rs.rcv_transaction_id
1088 AND rt.transaction_date < Nvl(v_txn_date,(rt.transaction_date + 1))
1089 --and rt.transaction_type <> 'UNORDERED'
1090 --
1091 and rs.po_header_id = header_id
1092 and rs.item_id = v_item_id
1093 AND ((v_revision IS NOT NULL
1094 AND Nvl(rs.item_revision, v_revision) = v_revision)
1095 OR (v_revision IS NULL))
1096 AND (rt.routing_header_id IS NULL OR
1097 rt.routing_header_id <> 2 OR
1098 (rt.routing_header_id = 2
1099 AND rt.inspection_status_code <> 'NOT INSPECTED'
1100 AND rt.inspection_status_code = Nvl(v_inspection_status,rt.inspection_status_code)))
1101 AND (
1102 v_lot_number IS NULL OR EXISTS
1103 (
1104 SELECT lot_num
1105 FROM rcv_lots_supply rls
1106 WHERE rls.transaction_id = rs.supply_source_id
1107 AND rls.lot_num = v_lot_number
1108 )
1109 )
1110 AND (
1111 v_parent_txn_id_to_match IS NULL
1112 OR v_parent_txn_id_to_match = rs.supply_source_id
1113 )
1114 AND (
1115 v_lpn_id_to_match IS NULL
1116 OR (rs.lpn_id = v_lpn_id_to_match)
1117 );
1118 --4364407
1119 CURSOR std_distributions_exp (
1120 header_id NUMBER
1121 ,v_item_id NUMBER
1122 ,v_revision VARCHAR2
1123 ,v_po_line_id NUMBER
1124 ,v_po_line_location_id NUMBER
1125 ,v_po_distribution_id NUMBER
1126 ,v_po_release_id NUMBER
1127 ,v_ship_to_org_id NUMBER
1128 ,v_ship_to_location_id NUMBER
1129 ,v_receipt_num VARCHAR2
1130 ,v_txn_date DATE
1131 ,v_inspection_status VARCHAR2
1132 ,v_lpn_id NUMBER
1133 ,v_lot_number VARCHAR2
1134 ,v_lpn_id_to_match NUMBER
1135 ,v_parent_txn_id_to_match NUMBER
1136 , v_organization_id NUMBER) IS
1137 SELECT
1138 pll.line_location_id
1139 ,pll.unit_meas_lookup_code
1140 ,nvl(pll.promised_date,pll.need_by_date) promised_date
1141 ,0 --pll.quantity_shipped
1142 ,pll.receipt_days_exception_code
1143 ,pll.qty_rcv_exception_code
1144 ,pll.days_early_receipt_allowed
1145 ,pll.days_late_receipt_allowed
1146 ,pod.po_distribution_id
1147 ,pll.ship_to_location_id
1148 ,nvl(pll.enforce_ship_to_location_code,'NONE') enforce_ship_to_location_code
1149 ,rs.rcv_transaction_id
1150 ,rs.item_revision
1151 from po_distributions pod,
1152 po_line_locations pll,
1153 po_lines pl,
1154 rcv_supply rs,
1155 rcv_shipment_headers rsh,
1156 rcv_transactions rt
1157 where rsh.receipt_source_code = 'VENDOR'
1158 AND pod.po_line_id = Nvl(v_po_line_id, pod.po_line_id)
1159 AND pod.line_location_id = Nvl(v_po_line_location_id, pod.line_location_id)
1160 AND pod.po_distribution_id = Nvl(v_po_distribution_id, pod.po_distribution_id)
1161 and pl.po_line_id = rs.po_line_id
1162 and pll.line_location_id = rs.po_line_location_id
1163 and pod.line_location_id = rs.po_line_location_id
1164 and NVL(pll.APPROVED_FLAG,'N') = 'Y'
1165 and NVL(pll.CANCEL_FLAG, 'N') = 'N'
1166 --and NVL(pll.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING','CLOSED')
1167 and NVL(pll.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED')
1168 and pll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
1169 and pll.ship_to_organization_id = nvl(v_ship_to_org_id,pll.ship_to_organization_id)
1170 and pll.ship_to_location_id = nvl(v_ship_to_location_id,pll.ship_to_location_id)
1171 AND RT.TRANSACTION_TYPE <> 'UNORDERED'
1172 AND Nvl(v_lpn_id,-1) IN (select nvl(rt2.transfer_lpn_id,-1)
1173 from rcv_transactions rt2
1174 where rt2.transaction_type <> 'DELIVER'
1178 select nvl(rt2.lpn_id,-1)
1175 start with rt2.transaction_id = rs.supply_source_id
1176 connect by prior rt2.transaction_id = rt2.parent_transaction_id
1177 union all
1179 from rcv_transactions rt2
1180 where rt2.transaction_type not in ('RECEIVE', 'DELIVER')
1181 start with rt2.transaction_id = rs.supply_source_id
1182 connect by prior rt2.transaction_id = rt2.parent_transaction_id
1183 )
1184 and rs.supply_type_code = 'RECEIVING'
1185 --and rsl.shipment_line_id = rs.shipment_line_id
1186 and rsh.shipment_header_id = rs.shipment_header_id
1187 AND (Nvl(rsh.receipt_num,'@@@')) = Nvl(v_receipt_num,Nvl(rsh.receipt_num,'@@@'))
1188 and rt.transaction_id = rs.rcv_transaction_id
1189 AND rt.transaction_date < Nvl(v_txn_date,(rt.transaction_date + 1))
1190 and rs.po_header_id = header_id
1191 --and rs.item_id = v_item_id
1192 AND ((v_revision IS NOT NULL
1193 AND Nvl(rs.item_revision, v_revision) = v_revision)
1194 OR (v_revision IS NULL))
1195 AND (rt.routing_header_id IS NULL OR
1196 rt.routing_header_id <> 2 OR
1197 (rt.routing_header_id = 2
1198 AND rt.inspection_status_code <> 'NOT INSPECTED'
1199 AND rt.inspection_status_code = Nvl(v_inspection_status,rt.inspection_status_code)))
1200 AND (
1201 v_lot_number IS NULL OR EXISTS
1202 (
1203 SELECT lot_num
1204 FROM rcv_lots_supply rls
1205 WHERE rls.transaction_id = rs.supply_source_id
1206 AND rls.lot_num = v_lot_number
1207 )
1208 )
1209 AND (
1210 v_parent_txn_id_to_match IS NULL
1211 OR v_parent_txn_id_to_match = rs.supply_source_id
1212 )
1213 AND (
1214 v_lpn_id_to_match IS NULL
1215 OR (rs.lpn_id = v_lpn_id_to_match)
1216 )
1217 order by rs.item_revision, nvl(pll.promised_date,pll.need_by_date);
1218
1219 CURSOR count_std_distributions_exp (
1220 header_id NUMBER
1221 , v_item_id NUMBER
1222 , v_revision VARCHAR2
1223 , v_po_line_id NUMBER
1224 , v_po_line_location_id NUMBER
1225 , v_po_distribution_id NUMBER
1226 , v_po_release_id NUMBER
1227 , v_ship_to_org_id NUMBER
1228 , v_ship_to_location_id NUMBER
1229 , v_receipt_num VARCHAR2
1230 , v_txn_date DATE
1231 , v_inspection_status VARCHAR2
1232 , v_lpn_id NUMBER
1233 , v_lot_number VARCHAR2
1234 , v_lpn_id_to_match NUMBER
1235 ,v_parent_txn_id_to_match NUMBER
1236 , v_organization_id NUMBER) IS
1237 SELECT count(*)
1238 FROM po_distributions pod,
1239 po_line_locations pll,
1240 po_lines pl,
1241 rcv_supply rs,
1242 rcv_shipment_headers rsh,
1243 rcv_transactions rt
1244 where rsh.receipt_source_code = 'VENDOR'
1245 AND pod.po_line_id = Nvl(v_po_line_id, pod.po_line_id)
1246 AND pod.line_location_id = Nvl(v_po_line_location_id, pod.line_location_id)
1247 AND pod.po_distribution_id = Nvl(v_po_distribution_id, pod.po_distribution_id)
1248 and pl.po_line_id = rs.po_line_id
1249 and pll.line_location_id = rs.po_line_location_id
1250 and pod.line_location_id = rs.po_line_location_id
1251 and NVL(pll.APPROVED_FLAG,'N') = 'Y'
1252 and NVL(pll.CANCEL_FLAG, 'N') = 'N'
1253 --and NVL(pll.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING','CLOSED')
1254 and NVL(pll.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED')
1255 and pll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
1256 and pll.ship_to_organization_id = nvl(v_ship_to_org_id,pll.ship_to_organization_id)
1257 and pll.ship_to_location_id = nvl(v_ship_to_location_id,pll.ship_to_location_id)
1258 AND RT.TRANSACTION_TYPE <> 'UNORDERED'
1259 AND Nvl(v_lpn_id,-1) IN (select nvl(rt2.transfer_lpn_id,-1)
1260 from rcv_transactions rt2
1261 where rt2.transaction_type <> 'DELIVER'
1262 start with rt2.transaction_id = rs.supply_source_id
1263 connect by prior rt2.transaction_id = rt2.parent_transaction_id
1264 union all
1265 select nvl(rt2.lpn_id,-1)
1266 from rcv_transactions rt2
1267 where rt2.transaction_type not in ('RECEIVE', 'DELIVER')
1268 start with rt2.transaction_id = rs.supply_source_id
1269 connect by prior rt2.transaction_id = rt2.parent_transaction_id
1270 )
1271 and rs.supply_type_code = 'RECEIVING'
1272 --and rsl.shipment_line_id = rs.shipment_line_id
1273 and rsh.shipment_header_id = rs.shipment_header_id
1274 AND (Nvl(rsh.receipt_num,'@@@')) = Nvl(v_receipt_num,Nvl(rsh.receipt_num,'@@@'))
1275 and rt.transaction_id = rs.rcv_transaction_id
1276 AND rt.transaction_date < Nvl(v_txn_date,(rt.transaction_date + 1))
1277 and rs.po_header_id = header_id
1278 --and rs.item_id = v_item_id
1279 AND ((v_revision IS NOT NULL
1280 AND Nvl(rs.item_revision, v_revision) = v_revision)
1281 OR (v_revision IS NULL))
1282 AND (rt.routing_header_id IS NULL OR
1283 rt.routing_header_id <> 2 OR
1284 (rt.routing_header_id = 2
1285 AND rt.inspection_status_code <> 'NOT INSPECTED'
1286 AND rt.inspection_status_code = Nvl(v_inspection_status,rt.inspection_status_code)))
1287 AND (
1288 v_lot_number IS NULL OR EXISTS
1292 WHERE rls.transaction_id = rs.supply_source_id
1289 (
1290 SELECT lot_num
1291 FROM rcv_lots_supply rls
1293 AND rls.lot_num = v_lot_number
1294 )
1295 )
1296 AND (
1297 v_parent_txn_id_to_match IS NULL
1298 OR v_parent_txn_id_to_match = rs.supply_source_id
1299 )
1300 AND (
1301 v_lpn_id_to_match IS NULL
1302 OR (rs.lpn_id = v_lpn_id_to_match)
1303 );
1304 --Bug 4364407
1305 ----
1306
1307 /*
1308 ** Debug: had to change this to the distribution record
1309 ** Might be a compatibility issue between the two record definitions
1310 */
1311 x_ShipmentDistributionRec distributions%rowtype;
1312 x_record_count number;
1313
1314 x_remaining_quantity number := 0;
1315 x_remaining_qty_po_uom number := 0;
1316 x_bkp_qty number := 0;
1317 x_progress varchar2(3);
1318 x_converted_trx_qty number := 0;
1319 transaction_ok boolean := FALSE;
1320 x_expected_date rcv_transactions_interface.expected_receipt_date%TYPE;
1321 high_range_date DATE;
1322 low_range_date DATE;
1323 rows_fetched number := 0;
1324 x_tolerable_qty number := 0;
1325 x_first_trans boolean := TRUE;
1326 x_sysdate DATE := sysdate;
1327 current_n binary_integer := 0;
1328 insert_into_table boolean := FALSE;
1329 x_qty_rcv_exception_code po_line_locations.qty_rcv_exception_code%type;
1330 tax_amount_factor number;
1331 lastrecord boolean := FALSE;
1332 -- Bug# 5739706
1333 l_date_reject boolean := FALSE;
1334
1335 po_asn_uom_qty number;
1336 po_primary_uom_qty number;
1337
1338 already_allocated_qty number := 0;
1339
1340 x_item_id number;
1341 x_approved_flag varchar(1);
1342 x_cancel_flag varchar(1);
1343 x_closed_code varchar(25);
1344 x_shipment_type varchar(25);
1345 x_ship_to_location_id number;
1346 x_vendor_product_num varchar(25);
1347 x_temp_count number;
1348 l_asn_received_qty NUMBER := 0;
1349 l_poll_qty NUMBER := 0;
1350 l_poll_tolerance_pct NUMBER := 0;
1351 l_poll_tolerance_qty NUMBER := 0;
1352
1353
1354 l_api_name CONSTANT VARCHAR2(30) := 'matching_logic';
1355
1356 l_progress VARCHAR2(5) := '10';
1357
1358 l_print_debug NUMBER := nvl(fnd_profile.value('INV_DEBUG_TRACE'), 2);
1359
1360 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1361
1362 --New variables for FP-J Lot/Serial Support
1363 l_lot_number_to_match mtl_lot_numbers.lot_number%TYPE;
1364 l_lpn_id_to_match NUMBER;
1365 l_parent_txn_id_to_match NUMBER;
1366 l_passed_parent_txn_id NUMBER;
1367 l_wms_po_j_or_higher BOOLEAN;
1368
1369 --Bug 4004656-Added the local variables for the quantity fields.
1370 l_rem_qty_trans_uom NUMBER := 0; -- Remaining quantity to be received in transaction uom
1371 l_rcv_qty_trans_uom NUMBER := 0; -- Quantity received in transaction uom
1372 l_rcv_qty_po_uom NUMBER := 0; -- Quantity received in uom of the po.
1373 l_bkp_qty_trans_uom NUMBER := 0;
1374 l_trx_qty_po_uom NUMBER := 0; -- Transaction quantity in the uom of the po.
1375 l_trx_qty_trans_uom NUMBER := 0; -- Transaction quantity in the transaction uom.
1376 l_tol_qty_po_uom NUMBER := 0; -- Tolerable quantity in the uom of the po.
1377 --End of fix for Bug 4004656
1378
1379 l_blind_receiving_flag VARCHAR2(1) := 'N'; -- Bug 6365270
1380
1381 -- For Bug 7440217
1382 l_asn_type VARCHAR2(40);
1383 -- End for Bug 7440217
1384
1385 BEGIN
1386
1387 x_return_status := fnd_api.g_ret_sts_success;
1388
1389 SAVEPOINT rcv_transactions_sa;
1390
1391 l_allow_routing_override := fnd_profile.value('OVERRIDE_ROUTING');
1392 IF l_allow_routing_override IS NULL THEN
1393 l_allow_routing_override := 'N';
1394 end IF;
1395
1396 -- the following steps will create a set of rows linking the line_record with
1397 -- its corresponding po_line_location rows until the quantity value from
1398 -- the asn is consumed. (Cascade)
1399 IF l_print_debug = 1 THEN
1400 IF (l_debug = 1) THEN
1401 print_debug('po_header_id in matching logic is :'||x_cascaded_table(n).po_header_id);
1402 print_debug('item_id in matching logic is :'||x_cascaded_table(n).item_id);
1403 print_debug('revision in matching logic is :'||x_cascaded_table(n).revision);
1404 print_debug('item_desc in matching logic is :'||x_cascaded_table(n).item_desc);
1405 print_debug('transaction_type in matching logic is :'||x_cascaded_table(n).transaction_type);
1406 print_debug('parent txn id = ' || x_cascaded_table(n).parent_transaction_id);
1407 print_debug('l_allow_routing_override = ' || l_allow_routing_override);
1408 END IF;
1409 END IF;
1410
1411 -- storing the passed value to match with the parent_txn_id in a local
1412 -- var and nulling out the variable in the record to simulate the
1413 -- behavior as was prior to patchset J changes.
1414 l_passed_parent_txn_id := x_cascaded_table(n).parent_transaction_id;
1415 x_cascaded_table(n).parent_transaction_id := NULL;
1416
1417 /* FP-J Lot/Serial Support Enhancement
1421 IF ((inv_rcv_common_apis.g_wms_patch_level >= inv_rcv_common_apis.g_patchset_j) AND
1418 * Read the currentand PO patch levels and set the flag (that would be used to
1419 * match the Lot Number and the LPN) accordingly
1420 */
1422 (inv_rcv_common_apis.g_po_patch_level >= inv_rcv_common_apis.g_patchset_j_po)) THEN
1423 l_wms_po_j_or_higher := TRUE;
1424 IF (l_debug = 1) THEN
1425 print_debug('WMS and PO patch levels are J or higher', 4);
1426 END IF;
1427 ELSE
1428 l_wms_po_j_or_higher := FALSE;
1429 IF (l_debug = 1) THEN
1430 print_debug('Either WMS or/and PO patch level(s) are lower than J', 4);
1431 END IF;
1432 END IF;
1433 if ((x_cascaded_table(n).po_header_id is not null) AND
1434 ((x_cascaded_table(n).item_id is not NULL
1435 OR (x_cascaded_table(n).item_desc IS NOT NULL
1436 AND x_cascaded_table(n).transaction_type in ('DELIVER','RECEIVE','STD_DELIVER')))) AND --4364407 Added STD_DELIVER also
1437 (x_cascaded_table(n).error_status in ('S','W'))) then
1438 -- Copy record from main table to temp table
1439 current_n := 1;
1440 temp_cascaded_table(current_n) := x_cascaded_table(n);
1441 -- Get all rows which meet this condition
1442 IF (x_cascaded_table(n).transaction_type = 'RECEIVE') THEN
1443 -- For Bug 7440217 Checking if doc is not LCM
1444 IF (p_shipment_header_id IS NOT NULL) THEN -- matching is called from ASN shipment matching
1445 SELECT ASN_TYPE
1446 INTO l_asn_type
1447 FROM RCV_SHIPMENT_HEADERS
1448 WHERE SHIPMENT_HEADER_ID = p_shipment_header_id;
1449 END IF;
1450 -- End for Bug 7440217
1451 IF l_asn_type = 'ASN' THEN
1452 -- End for Bug 7440217
1453 l_progress := '20';
1454 IF l_print_debug = 1 THEN
1455 IF (l_debug = 1) THEN
1456 print_debug('Opening for ASN with parameters:',4);
1457 print_debug('po_header_id:'||temp_cascaded_table(current_n).po_header_id,4);
1458 print_debug('item_id:'||temp_cascaded_table(current_n).item_id,4);
1459 print_debug('po_line_id:'||temp_cascaded_table(current_n).po_line_id,4);
1460 print_debug('po_line_location_id:'||temp_cascaded_table(current_n).po_line_location_id,4);
1461 print_debug('po_release_id:'||temp_cascaded_table(current_n).po_release_id,4);
1462 print_debug('to_organization_id:'||temp_cascaded_table(current_n).to_organization_id,4);
1463 print_debug('p_shipment_header_id:'||p_shipment_header_id,4);
1464 print_debug('p_lpn_id:'||p_lpn_id,4);
1465 print_debug('item_desc:'||temp_cascaded_table(current_n).item_desc,4);
1466 print_debug('inspection_status_code:'||temp_cascaded_table(current_n).inspection_status_code,4);
1467 END IF;
1468 END IF;
1469 IF temp_cascaded_table(current_n).po_header_id IS NULL THEN
1470 OPEN asn_shipments
1471 (temp_cascaded_table(current_n).item_id,
1472 temp_cascaded_table(current_n).po_line_id,
1473 temp_cascaded_table(current_n).po_line_location_id,
1474 temp_cascaded_table(current_n).po_release_id,
1475 temp_cascaded_table(current_n).to_organization_id,
1476 NULL,--temp_cascaded_table(current_n).ship_to_location_id,
1477 p_shipment_header_id,
1478 p_lpn_id,
1479 temp_cascaded_table(current_n).item_desc,
1480 temp_cascaded_table(current_n).project_id,
1481 temp_cascaded_table(current_n).task_id,
1482 temp_cascaded_table(current_n).inspection_status_code,
1483 temp_cascaded_table(current_n).to_organization_id);
1484
1485 OPEN count_asn_shipments
1486 (temp_cascaded_table(current_n).item_id,
1487 temp_cascaded_table(current_n).po_line_id,
1488 temp_cascaded_table(current_n).po_line_location_id,
1489 temp_cascaded_table(current_n).po_release_id,
1490 temp_cascaded_table(current_n).to_organization_id,
1491 NULL,--temp_cascaded_table(current_n).ship_to_location_id,
1492 p_shipment_header_id,
1493 p_lpn_id,
1494 temp_cascaded_table(current_n).item_desc,
1495 temp_cascaded_table(current_n).project_id,
1496 temp_cascaded_table(current_n).task_id,
1497 temp_cascaded_table(current_n).inspection_status_code,
1498 temp_cascaded_table(current_n).to_organization_id);
1499 ELSE
1500 OPEN asn_shipments_w_po
1501 (temp_cascaded_table(current_n).po_header_id,
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 OPEN count_asn_shipments_w_po
1516 (temp_cascaded_table(current_n).po_header_id,
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,
1530 END IF;
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);
1531
1532 -- For Bug 7440217 if doc type is LCM
1533 ELSIF l_asn_type = 'LCM' THEN
1534 l_progress := '20a';
1535 IF l_print_debug = 1 THEN
1536 IF (l_debug = 1) THEN
1537 print_debug('Opening for LCM with parameters:',4);
1538 print_debug('po_header_id:'||temp_cascaded_table(current_n).po_header_id,4);
1539 print_debug('item_id:'||temp_cascaded_table(current_n).item_id,4);
1540 print_debug('po_line_id:'||temp_cascaded_table(current_n).po_line_id,4);
1541 print_debug('po_line_location_id:'||temp_cascaded_table(current_n).po_line_location_id,4);
1542 print_debug('po_release_id:'||temp_cascaded_table(current_n).po_release_id,4);
1543 print_debug('to_organization_id:'||temp_cascaded_table(current_n).to_organization_id,4);
1544 print_debug('p_shipment_header_id:'||p_shipment_header_id,4);
1545 print_debug('p_lpn_id:'||p_lpn_id,4);
1546 print_debug('item_desc:'||temp_cascaded_table(current_n).item_desc,4);
1547 print_debug('inspection_status_code:'||temp_cascaded_table(current_n).inspection_status_code,4);
1548 END IF;
1549 END IF;
1550 IF temp_cascaded_table(current_n).po_header_id IS NULL THEN
1551 OPEN lcm_shipments
1552 (temp_cascaded_table(current_n).item_id,
1553 temp_cascaded_table(current_n).po_line_id,
1554 temp_cascaded_table(current_n).po_line_location_id,
1555 temp_cascaded_table(current_n).po_release_id,
1556 temp_cascaded_table(current_n).to_organization_id,
1557 NULL,--temp_cascaded_table(current_n).ship_to_location_id,
1558 p_shipment_header_id,
1559 p_lpn_id,
1560 temp_cascaded_table(current_n).item_desc,
1561 temp_cascaded_table(current_n).project_id,
1562 temp_cascaded_table(current_n).task_id,
1563 temp_cascaded_table(current_n).inspection_status_code,
1564 temp_cascaded_table(current_n).to_organization_id);
1565 print_debug('IN SVSVSVSV LCM 01010101010a');
1566 OPEN count_lcm_shipments
1567 (temp_cascaded_table(current_n).item_id,
1568 temp_cascaded_table(current_n).po_line_id,
1569 temp_cascaded_table(current_n).po_line_location_id,
1570 temp_cascaded_table(current_n).po_release_id,
1571 temp_cascaded_table(current_n).to_organization_id,
1572 NULL,--temp_cascaded_table(current_n).ship_to_location_id,
1573 p_shipment_header_id,
1574 p_lpn_id,
1575 temp_cascaded_table(current_n).item_desc,
1576 temp_cascaded_table(current_n).project_id,
1577 temp_cascaded_table(current_n).task_id,
1578 temp_cascaded_table(current_n).inspection_status_code,
1579 temp_cascaded_table(current_n).to_organization_id);
1580 print_debug('IN SVSVSVSV LCM 121212121212a');
1581 ELSE
1582 OPEN lcm_shipments_w_po
1583 (temp_cascaded_table(current_n).po_header_id,
1584 temp_cascaded_table(current_n).item_id,
1585 temp_cascaded_table(current_n).po_line_id,
1586 temp_cascaded_table(current_n).po_line_location_id,
1587 temp_cascaded_table(current_n).po_release_id,
1588 temp_cascaded_table(current_n).to_organization_id,
1589 NULL,--temp_cascaded_table(current_n).ship_to_location_id,
1590 p_shipment_header_id,
1591 p_lpn_id,
1592 temp_cascaded_table(current_n).item_desc,
1593 temp_cascaded_table(current_n).project_id,
1594 temp_cascaded_table(current_n).task_id,
1595 temp_cascaded_table(current_n).inspection_status_code,
1596 temp_cascaded_table(current_n).to_organization_id);
1597 print_debug('IN SVSVSVSV LCM 141414514141414a');
1598 OPEN count_lcm_shipments_w_po
1599 (temp_cascaded_table(current_n).po_header_id,
1600 temp_cascaded_table(current_n).item_id,
1601 temp_cascaded_table(current_n).po_line_id,
1602 temp_cascaded_table(current_n).po_line_location_id,
1603 temp_cascaded_table(current_n).po_release_id,
1604 temp_cascaded_table(current_n).to_organization_id,
1605 NULL,--temp_cascaded_table(current_n).ship_to_location_id,
1606 p_shipment_header_id,
1607 p_lpn_id,
1608 temp_cascaded_table(current_n).item_desc,
1609 temp_cascaded_table(current_n).project_id,
1610 temp_cascaded_table(current_n).task_id,
1611 temp_cascaded_table(current_n).inspection_status_code,
1612 temp_cascaded_table(current_n).to_organization_id);
1613 print_debug('IN SVSVSVSV LCM 564563634564356a');
1614 END IF;
1615 -- End for Bug 7440217
1616
1617 ELSE -- normal PO receipt
1618 l_progress := '30';
1619 IF l_print_debug = 1 THEN
1620 IF (l_debug = 1) THEN
1621 print_debug('Opening for PO with parameters:',4);
1622 print_debug('po_header_id:'||temp_cascaded_table(current_n).po_header_id,4);
1623 print_debug('item_id:'||temp_cascaded_table(current_n).item_id,4);
1624 print_debug('revision:'||temp_cascaded_table(current_n).revision,4);
1625 print_debug('po_line_id:'||temp_cascaded_table(current_n).po_line_id,4);
1626 print_debug('po_line_location_id:'||temp_cascaded_table(current_n).po_line_location_id,4);
1627 print_debug('po_release_id:'||temp_cascaded_table(current_n).po_release_id,4);
1628 print_debug('to_organization_id:'||temp_cascaded_table(current_n).to_organization_id,4);
1629 print_debug('item_desc:'||temp_cascaded_table(current_n).item_desc,4);
1630 END IF;
1631 END IF;
1632 OPEN shipments (temp_cascaded_table(current_n).po_header_id,
1633 temp_cascaded_table(current_n).item_id,
1637 temp_cascaded_table(current_n).po_release_id,
1634 temp_cascaded_table(current_n).revision,
1635 temp_cascaded_table(current_n).po_line_id,
1636 temp_cascaded_table(current_n).po_line_location_id,
1638 temp_cascaded_table(current_n).to_organization_id,
1639 NULL, --temp_cascaded_table(current_n).ship_to_location_id,
1640 temp_cascaded_table(current_n).item_desc,
1641 temp_cascaded_table(current_n).project_id,
1642 temp_cascaded_table(current_n).task_id,
1643 temp_cascaded_table(current_n).inspection_status_code,
1644 temp_cascaded_table(current_n).to_organization_id);
1645
1646 -- count_shipments just gets the count of rows found in shipments
1647
1648 OPEN count_shipments (temp_cascaded_table(current_n).po_header_id,
1649 temp_cascaded_table(current_n).item_id,
1650 temp_cascaded_table(current_n).revision,
1651 temp_cascaded_table(current_n).po_line_id,
1652 temp_cascaded_table(current_n).po_line_location_id,
1653 temp_cascaded_table(current_n).po_release_id,
1654 temp_cascaded_table(current_n).to_organization_id,
1655 NULL, --temp_cascaded_table(current_n).ship_to_location_id,
1656 temp_cascaded_table(current_n).item_desc,
1657 temp_cascaded_table(current_n).project_id,
1658 temp_cascaded_table(current_n).task_id,
1659 temp_cascaded_table(current_n).inspection_status_code,
1660 temp_cascaded_table(current_n).to_organization_id);
1661 END IF;
1662
1663 ELSIF (x_cascaded_table(n).transaction_type = 'DELIVER') THEN
1664
1665 l_progress := '40';
1666 IF l_print_debug = 1 THEN
1667 IF (l_debug = 1) THEN
1668 print_debug('Opening for PO distribution with parameters:',4);
1669 print_debug('po_header_id:'||temp_cascaded_table(current_n).po_header_id,4);
1670 print_debug('item_id:'||temp_cascaded_table(current_n).item_id,4);
1671 print_debug('revision:'||temp_cascaded_table(current_n).revision,4);
1672 print_debug('po_line_id:'||temp_cascaded_table(current_n).po_line_id,4);
1673 print_debug('po_line_location_id:'||temp_cascaded_table(current_n).po_line_location_id,4);
1674 print_debug('po_distribution_id:'||temp_cascaded_table(current_n).po_distribution_id,4);
1675 print_debug('po_release_id:'||temp_cascaded_table(current_n).po_release_id,4);
1676 print_debug('to_organization_id:'||temp_cascaded_table(current_n).to_organization_id,4);
1677 print_debug('item_desc:'||temp_cascaded_table(current_n).item_desc,4);
1678 END IF;
1679 END IF;
1680 open distributions (temp_cascaded_table(current_n).po_header_id,
1681 temp_cascaded_table(current_n).item_id,
1682 temp_cascaded_table(current_n).revision,
1683 temp_cascaded_table(current_n).po_line_id,
1684 temp_cascaded_table(current_n).po_line_location_id,
1685 temp_cascaded_table(current_n).po_distribution_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).to_organization_id);
1693
1694 -- count_distributions just gets the count of rows found in distributions
1695
1696 open count_distributions (temp_cascaded_table(current_n).po_header_id,
1697 temp_cascaded_table(current_n).item_id,
1698 temp_cascaded_table(current_n).revision,
1699 temp_cascaded_table(current_n).po_line_id,
1700 temp_cascaded_table(current_n).po_line_location_id,
1701 temp_cascaded_table(current_n).po_distribution_id,
1702 temp_cascaded_table(current_n).po_release_id,
1703 temp_cascaded_table(current_n).to_organization_id,
1704 NULL, --temp_cascaded_table(current_n).ship_to_location_id,
1705 temp_cascaded_table(current_n).item_desc,
1706 temp_cascaded_table(current_n).project_id,
1707 temp_cascaded_table(current_n).task_id,
1708 temp_cascaded_table(current_n).to_organization_id);
1709
1710 ELSIF (x_cascaded_table(n).transaction_type = 'STD_DELIVER') THEN
1711 l_progress := '50';
1712 -- If (x_cascaded_table(n).item_id IS not NULL ) THEN --4364407, moved it down.
1713 IF l_print_debug = 1 THEN
1714 IF (l_debug = 1) THEN
1715 print_debug('Opening for PO std distribution with parameters:',4);
1716 print_debug('po_header_id:'||temp_cascaded_table(current_n).po_header_id,4);
1717 print_debug('item_id:'||temp_cascaded_table(current_n).item_id,4);
1718 print_debug('revision:'||temp_cascaded_table(current_n).revision,4);
1719 print_debug('po_line_id:'||temp_cascaded_table(current_n).po_line_id,4);
1720 print_debug('po_line_location_id:'||temp_cascaded_table(current_n).po_line_location_id,4);
1721 print_debug('po_distribution_id:'||temp_cascaded_table(current_n).po_distribution_id,4);
1722 print_debug('po_release_id:'||temp_cascaded_table(current_n).po_release_id,4);
1723 print_debug('to_organization_id:'||temp_cascaded_table(current_n).to_organization_id,4);
1724 print_debug('item_desc:'||temp_cascaded_table(current_n).item_desc,4);
1725 print_debug('p_receipt_num:'||p_receipt_num,4);
1729 END IF;
1726 print_debug('expected_receipt_date:'||temp_cascaded_table(current_n).expected_receipt_date,4);
1727 print_debug('inspection_status_code:'||temp_cascaded_table(current_n).inspection_status_code,4);
1728 print_debug('p_lpn_id:'||temp_cascaded_table(current_n).p_lpn_id,4);
1730 END IF;
1731
1732 /* FP-J Lot/Serial Support Enhancement
1733 * If WMS and PO patch levels are J or higher, then we need to:
1734 * a) Match the lot number with that in RCV_LOTS_SUPPLY for the
1735 * the parent transaction in RCV_SUPPLY if lot number is not NULL
1736 * b) Match the LPN being delivered with the LPN in the RCV_SUPPLY for
1737 * the parent transaction
1738 * c) Set the value of lot_number to the cursor from that in the cascaded table
1739 * Set the value of lpn_id_to_match to the lpn being delivered (p_lpn_id)
1740 * If either of these are not at J, we should retain the original functionality
1741 * and so explictily set these values to NULL
1742 * Nevertheless, we should pass these two new values to the cursors
1743 * std_distributions and count_std_distributions
1744 */
1745 IF (l_wms_po_j_or_higher) THEN
1746 l_lot_number_to_match := temp_cascaded_table(current_n).lot_number;
1747 l_lpn_id_to_match := temp_cascaded_table(current_n).p_lpn_id;
1748 l_parent_txn_id_to_match := l_passed_parent_txn_id;
1749 ELSE
1750 l_lot_number_to_match := NULL;
1751 l_lpn_id_to_match := NULL;
1752 l_parent_txn_id_to_match := NULL;
1753 END IF;
1754
1755 IF (l_debug = 1) THEN
1756 print_debug('l_lot_number_to_match: ' || l_lot_number_to_match, 4);
1757 print_debug('l_lpn_id_to_match: ' || l_lpn_id_to_match, 4);
1758 print_debug('l_parent_txn_id_to_match: ' || l_parent_txn_id_to_match, 4);
1759 END IF;
1760
1761 If (x_cascaded_table(n).item_id IS not NULL ) THEN --4364407
1762
1763 OPEN std_distributions (
1764 temp_cascaded_table(current_n).po_header_id,
1765 temp_cascaded_table(current_n).item_id,
1766 temp_cascaded_table(current_n).revision,
1767 temp_cascaded_table(current_n).po_line_id,
1768 temp_cascaded_table(current_n).po_line_location_id,
1769 temp_cascaded_table(current_n).po_distribution_id,
1770 temp_cascaded_table(current_n).po_release_id,
1771 temp_cascaded_table(current_n).to_organization_id,
1772 NULL,--temp_cascaded_table(current_n).ship_to_location_id,
1773 p_receipt_num,
1774 temp_cascaded_table(current_n).expected_receipt_date,
1775 temp_cascaded_table(current_n).inspection_status_code,
1776 temp_cascaded_table(current_n).p_lpn_id,
1777 l_lot_number_to_match,
1778 l_lpn_id_to_match,
1779 l_parent_txn_id_to_match,
1780 temp_cascaded_table(current_n).to_organization_id);
1781
1782 -- count_distributions just gets the count of rows found in distributions
1783
1784 OPEN count_std_distributions (
1785 temp_cascaded_table(current_n).po_header_id,
1786 temp_cascaded_table(current_n).item_id,
1787 temp_cascaded_table(current_n).revision,
1788 temp_cascaded_table(current_n).po_line_id,
1789 temp_cascaded_table(current_n).po_line_location_id,
1790 temp_cascaded_table(current_n).po_distribution_id,
1791 temp_cascaded_table(current_n).po_release_id,
1792 temp_cascaded_table(current_n).to_organization_id,
1793 NULL,--temp_cascaded_table(current_n).ship_to_location_id,
1794 p_receipt_num,
1795 temp_cascaded_table(current_n).expected_receipt_date,
1796 temp_cascaded_table(current_n).inspection_status_code,
1797 temp_cascaded_table(current_n).p_lpn_id,
1798 l_lot_number_to_match,
1799 l_lpn_id_to_match,
1800 l_parent_txn_id_to_match,
1801 temp_cascaded_table(current_n).to_organization_id);
1802
1803 --4364407
1804 Else
1805 OPEN std_distributions_exp (
1806 temp_cascaded_table(current_n).po_header_id,
1807 temp_cascaded_table(current_n).item_id,
1808 temp_cascaded_table(current_n).revision,
1809 temp_cascaded_table(current_n).po_line_id,
1810 temp_cascaded_table(current_n).po_line_location_id,
1811 temp_cascaded_table(current_n).po_distribution_id,
1812 temp_cascaded_table(current_n).po_release_id,
1813 temp_cascaded_table(current_n).to_organization_id,
1814 NULL,--temp_cascaded_table(current_n).ship_to_location_id,
1815 p_receipt_num,
1816 temp_cascaded_table(current_n).expected_receipt_date,
1817 temp_cascaded_table(current_n).inspection_status_code,
1818 temp_cascaded_table(current_n).p_lpn_id,
1819 l_lot_number_to_match,
1820 l_lpn_id_to_match,
1821 l_parent_txn_id_to_match,
1822 temp_cascaded_table(current_n).to_organization_id);
1823
1824 -- count_distributions just gets the count of rows found in distributions
1825
1826 OPEN count_std_distributions_exp (
1827 temp_cascaded_table(current_n).po_header_id,
1828 temp_cascaded_table(current_n).item_id,
1829 temp_cascaded_table(current_n).revision,
1830 temp_cascaded_table(current_n).po_line_id,
1834 temp_cascaded_table(current_n).to_organization_id,
1831 temp_cascaded_table(current_n).po_line_location_id,
1832 temp_cascaded_table(current_n).po_distribution_id,
1833 temp_cascaded_table(current_n).po_release_id,
1835 NULL,--temp_cascaded_table(current_n).ship_to_location_id,
1836 p_receipt_num,
1837 temp_cascaded_table(current_n).expected_receipt_date,
1838 temp_cascaded_table(current_n).inspection_status_code,
1839 temp_cascaded_table(current_n).p_lpn_id,
1840 l_lot_number_to_match,
1841 l_lpn_id_to_match,
1842 l_parent_txn_id_to_match,
1843 temp_cascaded_table(current_n).to_organization_id);
1844 END IF;
1845 --4364407
1846
1847 END IF;
1848
1849 l_progress := '60';
1850 -- Assign shipped quantity to remaining quantity
1851
1852 --x_remaining_quantity := temp_cascaded_table(current_n).quantity; --Bug 4004656
1853 l_rem_qty_trans_uom := temp_cascaded_table(current_n).quantity; --Bug 4004656
1854
1855
1856 -- used for decrementing cum qty for first record
1857
1858
1859 --x_bkp_qty := x_remaining_quantity; --Bug 4004656
1860 --x_remaining_qty_po_uom := 0; --Bug 4004656
1861 l_bkp_qty_trans_uom := l_rem_qty_trans_uom; --Bug 4004656
1862 l_rcv_qty_trans_uom := 0; --Bug 4004656
1863 l_rcv_qty_po_uom := 0; --Bug 4004656
1864 -- Calculate tax_amount_factor for calculating tax_amount for
1865 -- each cascaded line
1866
1867 if nvl(temp_cascaded_table(current_n).tax_amount,0) <> 0 THEN
1868 /*Bug 4004656 -Commented out the below statement
1869 tax_amount_factor := temp_cascaded_table(current_n).tax_amount/x_remaining_quantity; */
1870 tax_amount_factor := temp_cascaded_table(current_n).tax_amount/l_rem_qty_trans_uom;
1871 --End of fix for Bug 4004656
1872 else
1873 tax_amount_factor := 0;
1874 end if;
1875
1876 x_first_trans := TRUE;
1877 transaction_ok := FALSE;
1878
1879
1880 l_progress := '70';
1881 -- Get the count of the number of records depending on the
1882 -- the transaction type
1883
1884 IF (x_cascaded_table(n).transaction_type = 'RECEIVE') THEN
1885 l_progress := '80';
1886
1887 IF p_shipment_header_id IS NOT NULL THEN
1888
1889 -- For Bug 7440217
1890 IF l_asn_type = 'ASN' THEN
1891 IF temp_cascaded_table(current_n).po_header_id IS NULL THEN
1892 FETCH count_asn_shipments INTO x_record_count;
1893 ELSE
1894 FETCH count_asn_shipments_w_po INTO x_record_count;
1895 END IF;
1896 ELSIF l_asn_type = 'LCM' THEN
1897 IF temp_cascaded_table(current_n).po_header_id IS NULL THEN
1898 FETCH count_lcm_shipments INTO x_record_count;
1899 ELSE
1900 FETCH count_lcm_shipments_w_po INTO x_record_count;
1901 END IF;
1902 ELSE
1903 FETCH count_shipments INTO x_record_count;
1904 END IF;
1905 END IF;
1906 -- End for Bug 7440217
1907
1908 ELSIF (x_cascaded_table(n).transaction_type = 'DELIVER') THEN
1909 l_progress := '90';
1910
1911 FETCH count_distributions INTO x_record_count;
1912
1913 ELSIF (x_cascaded_table(n).transaction_type = 'STD_DELIVER') THEN
1914 l_progress := '100';
1915
1916 If (x_cascaded_table(n).item_id is NOT NULL ) THEN --4364407
1917 FETCH count_std_distributions INTO x_record_count;
1918 else
1919 FETCH count_std_distributions_exp INTO x_record_count; --4364407
1920 end if;
1921
1922 END IF;
1923
1924 IF l_print_debug = 1 THEN
1925 IF (l_debug = 1) THEN
1926 print_debug('Rows fetched into matching cursor :'||x_record_count,4);
1927 END IF;
1928 END IF;
1929
1930 LOOP
1931
1932 -- Fetch the appropriate record
1933 l_progress := '110';
1934
1935 IF (l_debug = 1) THEN
1936 print_debug('Inside the loop',4);
1937 END IF;
1938
1939 IF (x_cascaded_table(n).transaction_type = 'RECEIVE') THEN
1940 l_progress := '120';
1941
1942 IF (l_debug = 1) THEN
1943 print_debug('Inside loop for condition of RECEIVE',4);
1944 END IF;
1945
1946 IF p_shipment_header_id IS NOT NULL THEN
1947
1948 IF (l_debug = 1) THEN
1949 print_debug('Inside shipment_header_id not null case, value of po_header_id:'|| x_cascaded_table(n).po_header_id ,4);
1950 END IF;
1951
1952 /* Bug 4901154- When looping, for current_n=2, no value has been
1953 assigned to temp_cascaded_table(2), hence NO_DATA_FOUND
1954 exception is raised.
1955 Inside the loop, using x_cascaded_table instead of
1956 temp_cascaded_table.
1957
1958 IF temp_cascaded_table(current_n).po_header_id IS NULL THEN */
1959
1960 -- For Bug 7440217
1961 IF l_asn_type = 'ASN' THEN
1962 IF x_cascaded_table(n).po_header_id IS NULL THEN
1963 /* End of fix for Bug 4901154 */
1964
1965 FETCH asn_shipments INTO x_ShipmentDistributionRec;
1966 -- Check if this is the last record
1967 IF (asn_shipments%NOTFOUND) THEN
1968 lastrecord := TRUE;
1969 END IF;
1973 -- Check if this is the last record
1970 rows_fetched := asn_shipments%rowcount;
1971 ELSE
1972 FETCH asn_shipments_w_po INTO x_shipmentdistributionrec;
1974 IF (asn_shipments_w_po%NOTFOUND) THEN
1975 lastrecord := TRUE;
1976 END IF;
1977 rows_fetched := asn_shipments_w_po%rowcount;
1978 END IF;
1979 ELSIF l_asn_type = 'LCM' THEN
1980 IF x_cascaded_table(n).po_header_id IS NULL THEN
1981 /* End of fix for Bug 4901154 */
1982
1983 FETCH lcm_shipments INTO x_ShipmentDistributionRec;
1984 -- Check if this is the last record
1985 IF (lcm_shipments%NOTFOUND) THEN
1986 lastrecord := TRUE;
1987 END IF;
1988 rows_fetched := lcm_shipments%rowcount;
1989 ELSE
1990 FETCH lcm_shipments_w_po INTO x_shipmentdistributionrec;
1991 -- Check if this is the last record
1992 IF (lcm_shipments_w_po%NOTFOUND) THEN
1993 lastrecord := TRUE;
1994 END IF;
1995 rows_fetched := lcm_shipments_w_po%rowcount;
1996 END IF;
1997 END IF;
1998 -- End for Bug 7440217
1999
2000 ELSE
2001
2002 FETCH shipments INTO x_ShipmentDistributionRec;
2003 -- Check if this is the last record
2004 IF (shipments%NOTFOUND) THEN
2005 lastrecord := TRUE;
2006 END IF;
2007 rows_fetched := shipments%rowcount;
2008 END IF;
2009
2010
2011 ELSIF (x_cascaded_table(n).transaction_type = 'DELIVER') THEN
2012 l_progress := '130';
2013
2014 fetch distributions into x_ShipmentDistributionRec;
2015
2016 -- Check if this is the last record
2017 if (distributions%NOTFOUND) THEN
2018 lastrecord := TRUE;
2019 END IF;
2020
2021 rows_fetched := distributions%rowcount;
2022 ELSIF (x_cascaded_table(n).transaction_type = 'STD_DELIVER') THEN
2023 l_progress := '140';
2024 If (x_cascaded_table(n).item_id IS NOT NULL )THEN --4364407
2025 fetch std_distributions into x_ShipmentDistributionRec;
2026
2027 -- Check if this is the last record
2028 if (std_distributions%NOTFOUND) THEN
2029 lastrecord := TRUE;
2030 END IF;
2031
2032 rows_fetched := std_distributions%rowcount;
2033 --4364407
2034 else
2035 fetch std_distributions_exp into x_ShipmentDistributionRec;
2036 -- Check if this is the last record
2037 if (std_distributions_exp%NOTFOUND) THEN
2038 lastrecord := TRUE;
2039 END IF;
2040
2041 rows_fetched := std_distributions_exp%rowcount;
2042 end if;
2043 --4364407
2044
2045 END IF;
2046 IF l_print_debug = 1 THEN
2047 IF (l_debug = 1) THEN
2048 print_debug('Rows fetched from matching cursor :'||rows_fetched,4);
2049 --print_debug('Remaining qty:'||x_remaining_quantity,4);--Bug 4004656
2050 print_debug('Remaining qty:'||l_rem_qty_trans_uom,4); --Bug 4004656
2051 print_debug('current_n:'||current_n,4);
2052 END IF;
2053 IF x_first_trans THEN
2054 IF (l_debug = 1) THEN
2055 print_debug('x_first_trans:TRUE',4);
2056 END IF;
2057 ELSE
2058 IF (l_debug = 1) THEN
2059 print_debug('x_first_trans:FLASE',4);
2060 END IF;
2061 END IF;
2062 IF lastrecord THEN
2063 IF (l_debug = 1) THEN
2064 print_debug('lastrecord:TRUE',4);
2065 END IF;
2066 ELSE
2067 IF (l_debug = 1) THEN
2068 print_debug('lastrecord:FLASE',4);
2069 END IF;
2070 END IF;
2071 END IF;
2072 l_progress := '150';
2073 /* Bug 4004656 -Commented the following statement
2074 if (lastrecord or x_remaining_quantity <= 0) then */
2075 /* Bug 4747997: We have to compare the rounded off values by 5 decimal places,
2076 as the value hold by this variable is non-rounded value returned
2077 from the API rcv_transactions_interface_sv.convert_into_correct_qty() */
2078
2079 if (lastrecord or round(l_rem_qty_trans_uom,5) <= 0) then --Bug 4747997
2080 --End of fix for Bug 4004656
2081 IF l_print_debug = 1 THEN
2082 IF (l_debug = 1) THEN
2083 print_debug('No more rows or the remaining qty is less than zero',4);
2084 END IF;
2085 END IF;
2086
2087 if not x_first_trans then
2088 -- x_first_trans has been reset which means some cascade has
2089 -- happened. Otherwise current_n = 1
2090 current_n := current_n -1 ;
2091 end if;
2092
2093 -- do the tolerance act here
2094
2095 -- lastrecord...we have run out of rows and
2096 -- we still have quantity to allocate
2097
2098 /* Bug 4004656 -Commented the following statement
2099 if x_remaining_quantity > 0 then */
2100 if round(l_rem_qty_trans_uom,5) > 0 then --Bug 4747997
2101 IF l_print_debug = 1 THEN
2102 IF (l_debug = 1) THEN
2103 print_debug('No more recs but still qty left',4);
2104 END IF;
2105 END IF;
2106 if not x_first_trans then
2107 IF l_print_debug = 1 THEN
2108 IF (l_debug = 1) THEN
2109 print_debug('Atleast one row returned in matching cursor',4);
2110 END IF;
2111 END IF;
2112 -- we had got atleast some rows from our shipments cursor
2113 -- we have atleast one row cascaded (not null line_location_id)
2117 x_qty_rcv_exception_code := 'REJECT';
2114 IF x_cascaded_table(n).transaction_type IN ('RECEIVE', 'DELIVER') THEN
2115 x_qty_rcv_exception_code := temp_cascaded_table(current_n).qty_rcv_exception_code;
2116 ELSE
2118 END IF;
2119 /* 3126097 */
2120 IF x_qty_rcv_exception_code IN ('REJECT','WARNING') then
2121 BEGIN
2122 select quantity, nvl(qty_rcv_tolerance,0)
2123 into l_poll_qty, l_poll_tolerance_pct
2124 from po_line_locations_all
2125 where line_location_id = temp_cascaded_table(current_n).po_line_location_id ;
2126 EXCEPTION
2127 when others then
2128 NULL;
2129 END;
2130 l_poll_tolerance_qty := (l_poll_qty*l_poll_tolerance_pct)/100 ;
2131 -- print_debug('Remaining PO qty:'||x_remaining_qty_po_uom,4); --Bug 4004656
2132 print_debug('Remaining PO qty:'||l_rcv_qty_po_uom,4); --Bug 4004656
2133 print_debug('po tolerance qty:'||l_poll_tolerance_qty,4);
2134
2135 -- The following needs not to be done for STD_DELIVER TXN.
2136
2137 --BUG #3325627
2138 --IF x_cascaded_table(n).transaction_type IN ('STD_DELIVER') THEN
2139 -- print_debug('No need to change Exception code -- STD DELIVER Txn ',4);
2140 --ELSE
2141 -- IF (x_remaining_qty_po_uom <= l_poll_tolerance_qty) THEN
2142 -- print_debug('setting x_qty_rcv_exception_code to NONE',4);
2143 -- x_qty_rcv_exception_code := 'NONE';
2144 -- END IF;
2145 --END IF;
2146 END IF;
2147
2148 IF l_print_debug = 1 THEN
2149 IF (l_debug = 1) THEN
2150 print_debug('x_qty_rcv_exception_code:'||x_qty_rcv_exception_code,4);
2151 END IF;
2152 END IF;
2153
2154 -- Start of fix for Bug 6365270
2155 BEGIN
2156 SELECT blind_receiving_flag
2157 INTO l_blind_receiving_flag
2158 FROM rcv_parameters
2159 WHERE organization_id = temp_cascaded_table(current_n).to_organization_id;
2160 EXCEPTION
2161 when others then
2162 NULL;
2163 END;
2164
2165 IF l_print_debug = 1 THEN
2166 IF (l_debug = 1) THEN
2167 print_debug('Current Organization id = '|| temp_cascaded_table(current_n).to_organization_id,4);
2168 print_debug('Blind Receiving Flag = '|| l_blind_receiving_flag,4);
2169 END IF;
2170 END IF;
2171 -- End of fix for Bug 6365270
2172
2173 -- The following if condition is commented out as part of bug 6365270
2174 -- if x_qty_rcv_exception_code IN ('NONE','WARNING') then
2175 temp_cascaded_table(current_n).quantity :=
2176 temp_cascaded_table(current_n).quantity +
2177 --x_remaining_quantity; --Bug 4004656
2178 l_rem_qty_trans_uom; --Bug 4004656
2179
2180 temp_cascaded_table(current_n).quantity_shipped :=
2181 temp_cascaded_table(current_n).quantity_shipped +
2182 --x_remaining_quantity; --Bug 4004656
2183 l_rem_qty_trans_uom; --Bug 4004656
2184
2185 temp_cascaded_table(current_n).source_doc_quantity :=
2186 temp_cascaded_table(current_n).source_doc_quantity +
2187 --x_remaining_qty_po_uom; --Bug 4004656
2188 l_rcv_qty_po_uom; --Bug 4004656
2189 IF temp_cascaded_table(1).primary_unit_of_measure IS
2190 NULL THEN
2191 temp_cascaded_table(1).primary_unit_of_measure :=
2192 x_ShipmentDistributionRec.unit_meas_lookup_code;
2193 END IF;
2194 temp_cascaded_table(current_n).primary_quantity :=
2195 temp_cascaded_table(current_n).primary_quantity +
2196 /* Bug 4004656
2197 rcv_transactions_interface_sv.convert_into_correct_qty(
2198 x_remaining_quantity,
2199 temp_cascaded_table(1).unit_of_measure,
2200 temp_cascaded_table(1).item_id,
2201 temp_cascaded_table(1).primary_unit_of_measure); */
2202 rcv_transactions_interface_sv.convert_into_correct_qty(
2203 l_rem_qty_trans_uom,
2204 temp_cascaded_table(1).unit_of_measure,
2205 temp_cascaded_table(1).item_id,
2206 temp_cascaded_table(1).primary_unit_of_measure);
2207 --End of fix for Bug 4004656
2208
2209 temp_cascaded_table(current_n).tax_amount :=
2210 round(temp_cascaded_table(current_n).quantity * tax_amount_factor,6);
2211
2212 -- Bug 6365270 - Modified the if condition to check for blind receiving flag
2213 if (x_qty_rcv_exception_code = 'WARNING' AND NVL(l_blind_receiving_flag,'N') <> 'Y') then
2214 -- bug 2787530
2215 IF temp_cascaded_table(current_n).error_status = 'W' THEN
2216 temp_cascaded_table(current_n).error_message :=
2217 'INV_RCV_GEN_TOLERANCE_EXCEED';
2218 ELSE
2219 temp_cascaded_table(current_n).error_status := 'W';
2220 temp_cascaded_table(current_n).error_message :=
2221 'INV_RCV_QTY_OVER_TOLERANCE';
2222 END IF;
2226 -- and the transaction type
2223 -- end if; -- Commented out as part of bug 6365270
2224
2225 -- Bug 6365270 - Modified the elsif condition to check for blind receiving flag
2227 elsif ((x_qty_rcv_exception_code = 'REJECT' AND NVL(l_blind_receiving_flag,'N') <> 'Y') OR
2228 (x_qty_rcv_exception_code = 'REJECT' AND x_cascaded_table(n).transaction_type ='STD_DELIVER')) then
2229 x_cascaded_table(n).error_status := 'E';
2230 x_cascaded_table(n).error_message := 'INV_RCV_QTY_OVER_TOLERANCE';
2231
2232 if temp_cascaded_table.count > 0 then
2233 for i in 1..temp_cascaded_table.count loop
2234 temp_cascaded_table.delete(i);
2235 end loop;
2236 end if;
2237 end if;
2238
2239 ELSE -- for if remaining_qty > 0 and not x_first_trans
2240 IF l_print_debug = 1 THEN
2241 IF (l_debug = 1) THEN
2242 print_debug('first transaction and qty remains so over tolerance',4);
2243 END IF;
2244 END IF;
2245
2246 x_cascaded_table(n).error_status := 'E';
2247 x_cascaded_table(n).error_message := 'INV_RCV_QTY_OVER_TOLERANCE';
2248
2249 if rows_fetched = 0 then
2250 x_cascaded_table(n).error_message := 'INV_RCV_NO_ROWS';
2251 IF l_print_debug = 1 THEN
2252 IF (l_debug = 1) THEN
2253 print_debug('matching_logic - No rows were retrieved from cursor ', 4);
2254 END IF;
2255 END IF;
2256 elsif x_first_trans then
2257 -- Bug# 5739706
2258 -- Tolerance could also fail because of Receipt date being breached
2259 -- Hence we need to display the appropriate message.
2260 if (l_date_reject) then
2261 x_cascaded_table(n).error_message := 'INV_RCV_DATE_OVER_TOLERANCE';
2262 else
2263 x_cascaded_table(n).error_message := 'INV_RCV_GEN_TOLERANCE_EXCEED';
2264 end if;
2265
2266 IF l_print_debug = 1 THEN
2267 IF (l_debug = 1) THEN
2268 print_debug('matching_logic - No rows were cascaded', 4);
2269 END IF;
2270 END IF;
2271 end if;
2272
2273 -- Delete the temp_cascaded_table just to be sure
2274 if temp_cascaded_table.count > 0 then
2275 for i in 1..temp_cascaded_table.count loop
2276 temp_cascaded_table.delete(i);
2277 end loop;
2278 end if;
2279 END IF;
2280 else
2281 null;
2282
2283 end if; -- x_remaining_qty > 0
2284 -- close cursors
2285 if shipments%isopen then
2286 close shipments;
2287 end if;
2288
2289 if count_shipments%isopen then
2290 close count_shipments;
2291 end if;
2292
2293 IF asn_shipments%isopen THEN
2294 CLOSE asn_shipments;
2295 END IF;
2296
2297 IF count_asn_shipments%isopen THEN
2298 CLOSE count_asn_shipments;
2299 END IF;
2300
2301 IF asn_shipments_w_po%isopen THEN
2302 CLOSE asn_shipments_w_po;
2303 END IF;
2304
2305 IF count_asn_shipments_w_po%isopen THEN
2306 CLOSE count_asn_shipments_w_po;
2307 END IF;
2308
2309
2310 -- For Bug 7440217 Closing the cursors
2311 IF lcm_shipments%isopen THEN
2312 CLOSE lcm_shipments;
2313 END IF;
2314
2315 IF count_lcm_shipments%isopen THEN
2316 CLOSE count_lcm_shipments;
2317 END IF;
2318
2319 IF lcm_shipments_w_po%isopen THEN
2320 CLOSE lcm_shipments_w_po;
2321 END IF;
2322
2323 IF count_lcm_shipments_w_po%isopen THEN
2324 CLOSE count_lcm_shipments_w_po;
2325 END IF;
2326 -- End for Bug 7440217
2327
2328 if distributions%isopen then
2329 close distributions;
2330 end if;
2331
2332 if count_distributions%isopen then
2333 close count_distributions;
2334 end if;
2335
2336 IF std_distributions%isopen THEN
2337 CLOSE std_distributions;
2338 END IF;
2339
2340 IF count_std_distributions%isopen THEN
2341 CLOSE count_std_distributions;
2342 END IF;
2343
2344 --4364407
2345 IF std_distributions_exp%isopen THEN
2346 CLOSE std_distributions_exp;
2347 END IF;
2348 IF count_std_distributions_exp%isopen THEN
2349 CLOSE count_std_distributions_exp;
2350 END IF;
2351 --4364407
2352 exit;
2353
2354 end if; -- if (lastrecord or x_remaining_quantity <= 0)
2355
2356 -- eliminate the row if it fails the date check
2357 IF l_print_debug = 1 THEN
2358 IF (l_debug = 1) THEN
2359 print_debug('expected_receipt_date:'||temp_cascaded_table(1).expected_receipt_date,4);
2360 print_debug('ship_to_location_id:'||temp_cascaded_table(1).ship_to_location_id,4);
2361 print_debug('promised_date:'||x_shipmentdistributionrec.promised_date,4);
2362 print_debug('days_early_receipt_allowed:'||x_shipmentdistributionrec.days_early_receipt_allowed,4);
2363 print_debug('days_late_receipt_allowed:'||x_shipmentdistributionrec.days_late_receipt_allowed,4);
2364 print_debug('enforce_ship_to_location_code:'||x_shipmentdistributionrec.enforce_ship_to_location_code,4);
2368 if ((temp_cascaded_table(1).expected_receipt_date is not null) and
2365 print_debug('ship_to_location_id:'||x_shipmentdistributionrec.ship_to_location_id,4);
2366 END IF;
2367 END IF;
2369 (x_cascaded_table(n).transaction_type <> 'STD_DELIVER')) then --BUG3210820
2370 if (x_ShipmentDistributionRec.promised_date is not null) then
2371 -- bug 2750081
2372 -- the null days early allowed and days late allowed should
2373 -- be interpreted as infinite and not zero.
2374 IF x_ShipmentDistributionRec.days_early_receipt_allowed IS NULL THEN
2375 low_range_date := Trunc(temp_cascaded_table(1).expected_receipt_date);
2376 else
2377 low_range_date := x_ShipmentDistributionRec.promised_date -
2378 nvl(x_ShipmentDistributionRec.days_early_receipt_allowed,0);
2379 END IF;
2380 IF x_ShipmentDistributionRec.days_late_receipt_allowed IS NULL THEN
2381 high_range_date := Trunc(temp_cascaded_table(1).expected_receipt_date);
2382 else
2383 high_range_date := x_ShipmentDistributionRec.promised_date +
2384 nvl(x_ShipmentDistributionRec.days_late_receipt_allowed,0);
2385 END IF;
2386 else
2387 IF x_ShipmentDistributionRec.days_early_receipt_allowed IS NULL THEN
2388 low_range_date := Trunc(temp_cascaded_table(1).expected_receipt_date);
2389 else
2390 low_range_date := x_sysdate -
2391 nvl(x_ShipmentDistributionRec.days_early_receipt_allowed,0);
2392 END IF;
2393 IF x_ShipmentDistributionRec.days_late_receipt_allowed IS NULL THEN
2394 high_range_date := Trunc(temp_cascaded_table(1).expected_receipt_date);
2395 else
2396 high_range_date := x_sysdate +
2397 nvl(x_ShipmentDistributionRec.days_late_receipt_allowed,0);
2398 END IF;
2399 end if;
2400 print_debug('Low date:'||low_range_date,4);
2401 print_debug('High date:'||high_range_date,4);
2402 if (Trunc(temp_cascaded_table(1).expected_receipt_date) >= low_range_date and
2403 Trunc(temp_cascaded_table(1).expected_receipt_date) <= high_range_date) then
2404 x_ShipmentDistributionRec.receipt_days_exception_code := 'NONE';
2405 end if;
2406 else
2407 x_ShipmentDistributionRec.receipt_days_exception_code := 'NONE';
2408 end if;
2409 if x_ShipmentDistributionRec.receipt_days_exception_code is null then
2410 x_ShipmentDistributionRec.receipt_days_exception_code := 'NONE';
2411 end if;
2412
2413 -- if the row does not fall within the date tolerance we just
2414 -- leave it aside and then take the next row. If the date
2415 -- tolerance is just set to warning then we continue with this
2416 -- row. The same applies to the ship to location check too.
2417
2418
2419 -- Check ship_to_location enforcement
2420 IF x_shipmentdistributionrec.enforce_ship_to_location_code <> 'NONE' THEN
2421 IF nvl(temp_cascaded_table(1).ship_to_location_id,x_ShipmentDistributionRec.ship_to_location_id) = x_ShipmentDistributionRec.ship_to_location_id THEN
2422 x_shipmentdistributionrec.enforce_ship_to_location_code := 'NONE';
2423 END IF;
2424 END IF;
2425
2426 IF l_print_debug = 1 THEN
2427 IF (l_debug = 1) THEN
2428 print_debug('receipt_days_exception_code:'||x_shipmentdistributionrec.receipt_days_exception_code,4);
2429 print_debug('enforce_ship_to_location_code:'||x_shipmentdistributionrec.enforce_ship_to_location_code,4);
2430 END IF;
2431 END IF;
2432 -- Bug# 5739706
2433 -- Need to add the appropriate error message if the receipt date tolerance has been
2434 -- breached and the receipt_days_exception_code = 'REJECT'
2435 if (x_ShipmentDistributionRec.receipt_days_exception_code = 'REJECT') then
2436 l_date_reject := TRUE;
2437 elsif (x_ShipmentDistributionRec.receipt_days_exception_code IN ('NONE', 'WARNING')) and
2438 (x_ShipmentDistributionRec.enforce_ship_to_location_code IN ('NONE','WARNING')) THEN
2439 -- derived by the date tolerance procedure
2440 -- derived by shipto_enforcement
2441
2442 insert_into_table := FALSE;
2443 already_allocated_qty := 0;
2444 IF l_print_debug = 1 THEN
2445 IF insert_into_table THEN
2446 IF (l_debug = 1) THEN
2447 print_debug('insert_into_table:TRUE',4);
2448 END IF;
2449 ELSE
2450 IF (l_debug = 1) THEN
2451 print_debug('insert_into_table:FLASE',4);
2452 END IF;
2453 END IF;
2454 IF (l_debug = 1) THEN
2455 print_debug('already_allocated_qty:'||already_allocated_qty,4);
2456 END IF;
2457 END IF;
2458 -- Get the available quantity for the shipment or distribution
2459 -- that is available for allocation by this interface transaction
2460 IF (x_cascaded_table(n).transaction_type = 'RECEIVE') THEN
2461 IF l_print_debug = 1 THEN
2462 IF (l_debug = 1) THEN
2463 print_debug('Receive',4);
2464 print_debug('line_location_id:'||x_ShipmentDistributionRec.line_location_id,4);
2465 END IF;
2466 END IF;
2467 rcv_quantities_s.get_available_quantity(
2468 'RECEIVE',
2469 x_ShipmentDistributionRec.line_location_id,
2470 'VENDOR',
2471 null,
2472 null,
2473 null,
2474 x_converted_trx_qty,
2475 x_tolerable_qty,
2476 x_ShipmentDistributionRec.unit_meas_lookup_code);
2477
2478 -- Bug 4004656-Added the following two assignment statements
2482
2479 l_trx_qty_po_uom := x_converted_trx_qty;
2480 l_tol_qty_po_uom := x_tolerable_qty;
2481 -- End of fix for Bug 4004656
2483 IF l_print_debug = 1 THEN
2484 IF (l_debug = 1) THEN
2485 --print_debug('x_converted_trx_qty:'||x_converted_trx_qty,4); --Bug 4004656
2486 print_debug('l_trx_qty_po_uom:'||l_trx_qty_po_uom,4); --Bug 4004656
2487 --print_debug('x_remaining_quantity:'||x_remaining_quantity,4);--Bug 4004656
2488 print_debug('l_rem_qty_trans_uom:'||l_rem_qty_trans_uom,4); --Bug 4004656
2489 --print_debug('x_tolerable_qty:'||x_tolerable_qty,4); --Bug 4004656
2490 print_debug('l_tol_qty_po_uom:'||l_tol_qty_po_uom,4); --Bug 4004656
2491 print_debug('unit_of_measure:'||x_ShipmentDistributionRec.unit_meas_lookup_code,4);
2492 END IF;
2493 END IF;
2494 -- If qtys have already been allocated for this po_line_location_id
2495 -- during a cascade process which has not been written to the db yet,
2496 -- we need to decrement it from the total available quantity
2497 -- We traverse the actual pl/sql table and accumulate the quantity by
2498 -- matching the po_line_location_id
2499
2500 l_asn_received_qty := 0;
2501 IF n > 1 THEN -- We will do this for all rows except the 1st
2502 FOR i in 1..(n-1) LOOP
2503 IF x_cascaded_table(i).po_line_location_id =
2504 x_ShipmentDistributionRec.line_location_id THEN
2505 IF l_print_debug = 1 THEN
2506 IF (l_debug = 1) THEN
2507 print_debug('Already allocated some qty for this po',4);
2508 END IF;
2509 END IF;
2510 already_allocated_qty := already_allocated_qty +
2511 x_cascaded_table(i).source_doc_quantity;
2512 IF p_shipment_header_id IS NOT NULL THEN
2513 l_asn_received_qty := already_allocated_qty;
2514 END IF;
2515 END IF;
2516 END LOOP;
2517 END IF;
2518
2519 ELSIF (x_cascaded_table(n).transaction_type = 'DELIVER') THEN
2520 IF l_print_debug = 1 THEN
2521 IF (l_debug = 1) THEN
2522 print_debug('Direct delivery',4);
2523 print_debug('po_distribution_id:'||x_ShipmentDistributionRec.po_distribution_id,4);
2524 END IF;
2525 END IF;
2526 rcv_quantities_s.get_available_quantity(
2527 'DIRECT RECEIPT',
2528 x_ShipmentDistributionRec.po_distribution_id,
2529 'VENDOR',
2530 null,
2531 null,
2532 null,
2533 x_converted_trx_qty,
2534 x_tolerable_qty,
2535 x_ShipmentDistributionRec.unit_meas_lookup_code);
2536 --BUG #3325627
2537 -- x_tolerable_qty := x_converted_trx_qty;
2538
2539 -- Bug 4004656-Added the following two assignment statements
2540 l_trx_qty_po_uom := x_converted_trx_qty;
2541 l_tol_qty_po_uom := x_tolerable_qty;
2542 -- End of fix for Bug 4004656
2543
2544 IF l_print_debug = 1 THEN
2545 IF (l_debug = 1) THEN
2546 -- print_debug('x_converted_trx_qty:'||x_converted_trx_qty,4); --Bug 4004656
2547 print_debug('l_trx_qty_po_uom:'||l_trx_qty_po_uom,4); --Bug 4004656
2548 -- print_debug('x_remaining_quantity:'||x_remaining_quantity,4);--Bug 4004656
2549 print_debug('l_rem_qty_trans_uom:'||l_rem_qty_trans_uom,4); --Bug 4004656
2550 -- print_debug('x_tolerable_qty:'||x_tolerable_qty,4); --Bug 4004656
2551 print_debug('l_tol_qty_po_uom:'||l_tol_qty_po_uom,4); --Bug 4004656
2552 print_debug('unit_of_measure:'||x_ShipmentDistributionRec.unit_meas_lookup_code,4);
2553 END IF;
2554 END IF;
2555 -- If qtys have already been allocated for this po_distribution_id
2556 -- during
2557 -- a cascade process which has not been written to the db yet, we need to
2558 -- decrement it from the total available quantity
2559 -- We traverse the actual pl/sql table and accumulate the quantity by
2560 -- matching the
2561 -- po_distribution_id
2562 IF n > 1 THEN -- We will do this for all rows except the 1st
2563 FOR i in 1..(n-1) LOOP
2564 IF x_cascaded_table(i).po_distribution_id =
2565 x_ShipmentDistributionRec.po_distribution_id THEN
2566 IF l_print_debug = 1 THEN
2567 IF (l_debug = 1) THEN
2568 print_debug('Already allocated some qty for this po',4);
2569 END IF;
2570 END IF;
2571 already_allocated_qty := already_allocated_qty +
2572 x_cascaded_table(i).source_doc_quantity;
2573 END IF;
2574 END LOOP;
2575 END IF;
2576
2577 ELSIF (x_cascaded_table(n).transaction_type = 'STD_DELIVER') THEN
2578 IF l_print_debug = 1 THEN
2579 IF (l_debug = 1) THEN
2580 print_debug('standard delivery',4);
2581 print_debug('po_distribution_id:'||x_ShipmentDistributionRec.po_distribution_id,4);
2582 print_debug('rcv_transaction_id:'||x_ShipmentDistributionRec.rcv_transaction_id,4);
2583 END IF;
2584 END IF;
2585 rcv_quantities_s.get_available_quantity(
2586 'STANDARD DELIVER',
2587 x_ShipmentDistributionRec.po_distribution_id,
2588 'VENDOR',
2589 null,
2590 x_ShipmentDistributionRec.rcv_transaction_id,
2591 null,
2592 x_converted_trx_qty,
2596 -- Bug 4004656-Added the following two assignment statements
2593 x_tolerable_qty,
2594 x_ShipmentDistributionRec.unit_meas_lookup_code);
2595 --x_tolerable_qty := x_converted_trx_qty; --Bug 4004656
2597 l_trx_qty_po_uom := x_converted_trx_qty;
2598 l_tol_qty_po_uom := x_tolerable_qty;
2599 l_tol_qty_po_uom := l_trx_qty_po_uom;
2600 -- End of fix for Bug 4004656
2601
2602 IF l_print_debug = 1 THEN
2603 IF (l_debug = 1) THEN
2604 -- print_debug('x_converted_trx_qty:'||x_converted_trx_qty,4); --Bug 4004656
2605 print_debug('l_trx_qty_po_uom:'||l_trx_qty_po_uom,4); --Bug 4004656
2606 -- print_debug('x_remaining_quantity:'||x_remaining_quantity,4);--Bug 4004656
2607 print_debug('l_rem_qty_trans_uom:'||l_rem_qty_trans_uom,4); --Bug 4004656
2608 -- print_debug('x_tolerable_qty:'||x_tolerable_qty,4); --Bug 4004656
2609 print_debug('l_tol_qty_po_uom:'||l_tol_qty_po_uom,4); --Bug 4004656
2610 print_debug('unit_of_measure:'||x_ShipmentDistributionRec.unit_meas_lookup_code,4);
2611 END IF;
2612 END IF;
2613 -- If qtys have already been allocated for this po_distribution_id
2614 -- during
2615 -- a cascade process which has not been written to the db yet, we need to
2616 -- decrement it from the total available quantity
2617 -- We traverse the actual pl/sql table and accumulate the quantity by
2618 -- matching the
2619 -- po_distribution_id
2620 IF n > 1 THEN -- We will do this for all rows except the 1st
2621 FOR i in 1..(n-1) LOOP
2622 IF x_cascaded_table(i).po_distribution_id =
2623 x_ShipmentDistributionRec.po_distribution_id AND
2624 x_cascaded_table(i).parent_transaction_id =
2625 x_ShipmentDistributionRec.rcv_transaction_id THEN
2626 IF l_print_debug = 1 THEN
2627 IF (l_debug = 1) THEN
2628 print_debug('Already allocated some qty for this po',4);
2629 END IF;
2630 END IF;
2631 already_allocated_qty := already_allocated_qty +
2632 x_cascaded_table(i).source_doc_quantity;
2633 END IF;
2634 END LOOP;
2635 END IF;
2636 END IF;
2637 IF l_print_debug = 1 THEN
2638 IF (l_debug = 1) THEN
2639 print_debug('Total already allocated qty:'||already_allocated_qty,4);
2640 END IF;
2641 END IF;
2642 -- if qty has already been allocated then reduce available and tolerable
2643 -- qty by the allocated amount
2644
2645 /* Bug 4004656-Modified the block with the new quantity fileds.
2646 IF nvl(already_allocated_qty,0) > 0 THEN
2647 x_converted_trx_qty := x_converted_trx_qty - already_allocated_qty;
2648 x_tolerable_qty := x_tolerable_qty - already_allocated_qty;
2649 IF x_converted_trx_qty < 0 THEN
2650 x_converted_trx_qty := 0;
2651 END IF;
2652 IF x_tolerable_qty < 0 THEN
2653 x_tolerable_qty := 0;
2654 END IF;
2655 END IF; */
2656
2657 IF nvl(already_allocated_qty,0) > 0 THEN
2658 l_trx_qty_po_uom := l_trx_qty_po_uom - already_allocated_qty;
2659 l_tol_qty_po_uom := l_tol_qty_po_uom - already_allocated_qty;
2660 IF l_trx_qty_po_uom < 0 THEN
2661 l_trx_qty_po_uom := 0;
2662 END IF;
2663 IF l_tol_qty_po_uom < 0 THEN
2664 l_tol_qty_po_uom := 0;
2665 END IF;
2666 END IF;
2667 -- End of fix for Bug 4004656
2668
2669 -- We can use the first record since the item_id and uom are not going to
2670 -- change
2671 -- Check that we can convert between ASN-> PO uom
2672 -- PO -> ASN uom
2673 -- PO -> PRIMARY uom
2674 -- If any of the conversions fail then we cannot use that record
2675
2676 --x_remaining_qty_po_uom := 0; -- initialize --Bug 4004656
2677 l_rcv_qty_trans_uom := 0; --Bug 4004656
2678 l_rcv_qty_po_uom := 0; --Bug 4004656
2679 po_asn_uom_qty := 0; -- initialize
2680 po_primary_uom_qty := 0; -- initialize
2681
2682 -- converts from temp_cascaded_table(1).unit_of_measure to
2683 -- x_ShipmentDistributionRec.unit_meas_lookup_code
2684 /* Bug 4004656 -Commented out the conversion that is being done
2685 for the received quantity to the uom on the PO.
2686 Retained it in the transaction uom through the variable l_rcv_qty_trans_uom
2687 by assigning the value of the remaining quantity l_rem_qty_trans_uom
2688 which is already in the transaciton uom */
2689 /* x_remaining_qty_po_uom :=
2690 rcv_transactions_interface_sv.convert_into_correct_qty(x_remaining_quantity,
2691 temp_cascaded_table(1).unit_of_measure,
2692 temp_cascaded_table(1).item_id,
2693 x_ShipmentDistributionRec.unit_meas_lookup_code); */
2694
2695 l_rcv_qty_trans_uom := l_rem_qty_trans_uom ; --Bug 4004656
2696
2697 IF l_print_debug = 1 THEN
2698 /* Bug 4004656 - Printed debug messages for the quantities in the
2699 new quantity variables
2700 IF (l_debug = 1) THEN
2701 print_debug('x_remaining_qty_po_uom:'||x_remaining_qty_po_uom,4);
2702 print_debug('x_converted_trx_qty:'||x_converted_trx_qty,4);
2703 print_debug('x_tolerable_qty:'||x_tolerable_qty,4);
2704 END IF; */
2705 IF (l_debug = 1) THEN
2706 print_debug('l_rcv_qty_trans_uom:'||l_rcv_qty_trans_uom,4);
2707 print_debug('l_trx_qty_po_uom:'||l_trx_qty_po_uom,4);
2708 print_debug('l_tol_qty_po_uom:'||l_tol_qty_po_uom,4);
2709 END IF;
2710 --End of fix for Bug 4004656
2711 END IF;
2712
2713 -- IF x_remaining_qty_po_uom <> 0 THEN --Bug 4004656
2714 IF round(l_rcv_qty_trans_uom,5) <> 0 THEN --Bug 4004656, 4747997
2715 -- If last row set available = tolerable - shipped
2719 -- has the shipment line id then we should take the quantity from
2716 -- else = available - shipped
2717 -- Debug: Were a bit troubled here. How do we know if the shipment
2718 -- is taken into account here. I guess if the transaction
2720 -- the shipped quantity. Need to walk through the different
2721 -- scenarios
2722 IF p_shipment_header_id IS NULL THEN
2723 l_asn_received_qty := 0;
2724 END IF;
2725 IF l_print_debug = 1 THEN
2726 IF (l_debug = 1) THEN
2727 print_debug('p_shipment_header_id:'||p_shipment_header_id,4);
2728 print_debug('ASN Receipt qty:'||l_asn_received_qty,4);
2729 END IF;
2730 END IF;
2731
2732 if rows_fetched = x_record_count then
2733 -- Bug 2496230
2734 -- For asn receipts, the shipped quantity also includes
2735 -- the current quantity being received. So the converted
2736 -- and the tolerable quantity should add the
2737 -- l_asn_received_qty as already_allocated_qty has been
2738 -- reduced from the converted and tolerable qty above.
2739 -- Otherwise it will resuly in double decrementing.
2740 -- x_converted_trx_qty := x_tolerable_qty - --Bug 4004656
2741 l_trx_qty_po_uom := l_tol_qty_po_uom -
2742 nvl(x_ShipmentDistributionRec.quantity_shipped,0) +
2743 l_asn_received_qty;
2744 else
2745 -- x_converted_trx_qty := x_converted_trx_qty - --Bug 4004656
2746 l_trx_qty_po_uom := l_trx_qty_po_uom -
2747 nvl(x_ShipmentDistributionRec.quantity_shipped,0) +
2748 l_asn_received_qty;
2749 end if;
2750
2751 /* Bug 4004656 -Modified the condition with the new quantity field variable values
2752 if x_converted_trx_qty > 0 then
2753 if (x_converted_trx_qty < x_remaining_qty_po_uom) then
2754 -- compare like uoms
2755
2756 x_remaining_qty_po_uom := x_remaining_qty_po_uom -
2757 x_converted_trx_qty;
2758
2759 -- change asn uom qty so both qtys are in sync
2760
2761 x_remaining_quantity :=
2762 rcv_transactions_interface_sv.convert_into_correct_qty(x_remaining_qty_po_uom,
2763 x_ShipmentDistributionRec.unit_meas_lookup_code,
2764 temp_cascaded_table(1).item_id,
2765 temp_cascaded_table(1).unit_of_measure);
2766
2767 insert_into_table := TRUE;
2768 else
2769 x_converted_trx_qty := x_remaining_qty_po_uom;
2770 insert_into_table := TRUE;
2771 x_remaining_qty_po_uom := 0;
2772 x_remaining_quantity := 0;
2773
2774 end if;
2775 IF l_print_debug = 1 THEN
2776 IF (l_debug = 1) THEN
2777 print_debug('x_remaining_qty_po_uom:'||x_remaining_qty_po_uom,4);
2778 print_debug('x_converted_trx_qty:'||x_converted_trx_qty,4);
2779 print_debug('x_remaining_quantity:'||x_remaining_quantity,4);
2780 END IF;
2781 END IF;
2782
2783 else -- no qty for this record but if last row we need it
2784 IF l_print_debug = 1 THEN
2785 IF (l_debug = 1) THEN
2786 print_debug('no qty for this record but if last row we need it',4);
2787 END IF;
2788 END IF;
2789 if rows_fetched = x_record_count then
2790 -- last row needs to be inserted anyway
2791 -- so that the row can be used based on qty tolerance
2792 -- checks
2793
2794 insert_into_table := TRUE;
2795 x_converted_trx_qty := 0;
2796
2797 else
2798 x_remaining_qty_po_uom := 0;
2799 -- we may have a diff uom on the next iteration
2800 insert_into_table := FALSE;
2801 end if;
2802 IF l_print_debug = 1 THEN
2803 IF insert_into_table THEN
2804 IF (l_debug = 1) THEN
2805 print_debug('insert_into_table:TRUE',4);
2806 END IF;
2807 ELSE
2808 IF (l_debug = 1) THEN
2809 print_debug('insert_into_table:FLASE',4);
2810 END IF;
2811 END IF;
2812 END IF;
2813
2814 end if; */
2815 if l_trx_qty_po_uom > 0 then
2816 --Added the following code
2817 l_trx_qty_trans_uom:=
2818 rcv_transactions_interface_sv.convert_into_correct_qty
2819 (l_trx_qty_po_uom,
2820 x_ShipmentDistributionRec.unit_meas_lookup_code,
2821 temp_cascaded_table(1).item_id,
2822 temp_cascaded_table(1).unit_of_measure);
2823 IF l_print_debug = 1 THEN
2824
2825 IF (l_debug = 1) THEN
2826 print_debug('l_trx_qty_trans_uom:'||l_trx_qty_trans_uom,4);
2827 END IF;
2828
2829 END IF;
2830
2831 if (round(l_trx_qty_trans_uom,5) < round(l_rcv_qty_trans_uom,5)) then --Bug 4747997
2832 -- compare like uoms which is the transaction uom
2833 l_rcv_qty_trans_uom := l_rcv_qty_trans_uom - l_trx_qty_trans_uom;
2834 l_rcv_qty_po_uom:= rcv_transactions_interface_sv.convert_into_correct_qty(l_rcv_qty_trans_uom,
2835 temp_cascaded_table(1).unit_of_measure,
2836 temp_cascaded_table(1).item_id,
2837 x_ShipmentDistributionRec.unit_meas_lookup_code);
2838
2839
2840 -- change asn uom qty so both qtys are in sync
2841 l_rem_qty_trans_uom := l_rcv_qty_trans_uom ;
2842 insert_into_table := TRUE;
2843 else
2844 l_trx_qty_trans_uom := l_rcv_qty_trans_uom;
2845 insert_into_table := TRUE;
2846 l_rcv_qty_trans_uom := 0;
2847 l_rcv_qty_po_uom := 0;
2848 l_rem_qty_trans_uom := 0;
2849
2850 end if;
2851 IF l_print_debug = 1 THEN
2852 IF (l_debug = 1) THEN
2853 print_debug('l_rcv_qty_trans_uom:'||l_rcv_qty_trans_uom,4);
2854 print_debug('l_rcv_qty_po_uom:'||l_rcv_qty_po_uom,4);
2858 END IF;
2855 print_debug('l_trx_qty_trans_uom:'||l_trx_qty_trans_uom,4);
2856 print_debug('l_rem_qty_trans_uom:'||l_rem_qty_trans_uom,4);
2857 END IF;
2859
2860 else -- no qty for this record but if last row we need it
2861 IF l_print_debug = 1 THEN
2862 IF (l_debug = 1) THEN
2863 print_debug('no qty for this record but if last row we need it',4);
2864 END IF;
2865 END IF;
2866 if rows_fetched = x_record_count then
2867 -- last row needs to be inserted anyway
2868 -- so that the row can be used based on qty tolerance
2869 -- checks
2870
2871 insert_into_table := TRUE;
2872 l_trx_qty_trans_uom := 0;
2873
2874 else
2875 l_rcv_qty_trans_uom := 0;
2876 l_rcv_qty_po_uom := 0;
2877 -- we may have a diff uom on the next iteration
2878 insert_into_table := FALSE;
2879
2880 end if;
2881
2882 IF l_print_debug = 1 THEN
2883 IF insert_into_table THEN
2884 IF (l_debug = 1) THEN
2885 print_debug('insert_into_table:TRUE',4);
2886 END IF;
2887 ELSE
2888 IF (l_debug = 1) THEN
2889 print_debug('insert_into_table:FLASE',4);
2890 END IF;
2891 END IF;
2892 END IF;
2893
2894 end if;
2895 --End of fix for bug 4004656
2896
2897 end if; -- remaining_qty_po_uom <> 0
2898
2899
2900
2901 if insert_into_table then
2902 if (x_first_trans) then
2903 x_first_trans := FALSE;
2904 else
2905 temp_cascaded_table(current_n) := temp_cascaded_table(current_n - 1);
2906 end if;
2907
2908 -- source_doc_quantity -> in po_uom
2909 -- primary_quantity -> in primary_uom
2910 -- cum_qty -> in primary_uom
2911 -- quantity,quantity_shipped -> in ASN uom
2912
2913 temp_cascaded_table(current_n).source_doc_quantity :=
2914 -- x_converted_trx_qty; -- in po uom --Bug 4004656
2915 rcv_transactions_interface_sv.convert_into_correct_qty(
2916 l_trx_qty_trans_uom ,
2917 temp_cascaded_table(current_n).unit_of_measure,
2918 temp_cascaded_table(current_n).item_id,
2919 x_ShipmentDistributionRec.unit_meas_lookup_code);
2920 -- End of fix for bug4004656
2921
2922 temp_cascaded_table(current_n).source_doc_unit_of_measure :=
2923 x_ShipmentDistributionRec.unit_meas_lookup_code;
2924
2925 temp_cascaded_table(current_n).quantity :=
2926 /* Bug 4004656
2927 rcv_transactions_interface_sv.convert_into_correct_qty(
2928 x_converted_trx_qty,
2929 x_ShipmentDistributionRec.unit_meas_lookup_code,
2930 temp_cascaded_table(current_n).item_id,
2931 temp_cascaded_table(current_n).unit_of_measure); -- in asn uom */
2932 l_trx_qty_trans_uom ; --Bug 4004656
2933
2934 temp_cascaded_table(current_n).quantity_shipped :=
2935 temp_cascaded_table(current_n).quantity; -- in asn uom
2936
2937 -- Primary qty in Primary UOM
2938 IF temp_cascaded_table(current_n).primary_unit_of_measure IS
2939 NULL THEN
2940 temp_cascaded_table(current_n).primary_unit_of_measure :=
2941 x_ShipmentDistributionRec.unit_meas_lookup_code;
2942 END IF;
2943 temp_cascaded_table(current_n).primary_quantity :=
2944 /* Bug 4004656
2945 rcv_transactions_interface_sv.convert_into_correct_qty(
2946 x_converted_trx_qty,
2947 x_ShipmentDistributionRec.unit_meas_lookup_code,
2948 temp_cascaded_table(current_n).item_id,
2949 temp_cascaded_table(current_n).primary_unit_of_measure); */
2950
2951 rcv_transactions_interface_sv.convert_into_correct_qty(
2952 l_trx_qty_trans_uom,
2953 temp_cascaded_table(current_n).unit_of_measure,
2954 temp_cascaded_table(current_n).item_id,
2955 temp_cascaded_table(current_n).primary_unit_of_measure);
2956
2957 IF l_print_debug = 1 THEN
2958 IF (l_debug = 1) THEN
2959 print_debug('source_doc_quantity:'||temp_cascaded_table(current_n).source_doc_quantity,4);
2960 print_debug('source_doc_unit_of_measure:'||temp_cascaded_table(current_n).source_doc_unit_of_measure,4);
2961 print_debug('quantity:'||temp_cascaded_table(current_n).quantity,4);
2962 print_debug('quantity_shipped:'||temp_cascaded_table(current_n).quantity_shipped,4);
2963 print_debug('primary_unit_of_measure:'||temp_cascaded_table(current_n).primary_unit_of_measure,4);
2964 print_debug('primary_quantity:'||temp_cascaded_table(current_n).primary_quantity,4);
2965 END IF;
2966 END IF;
2967
2968 temp_cascaded_table(current_n).tax_amount :=
2969 round(temp_cascaded_table(current_n).quantity * tax_amount_factor,4);
2970
2971 IF l_print_debug = 1 THEN
2972 IF (l_debug = 1) THEN
2973 print_debug('transaction_type:'||x_cascaded_table(n).transaction_type,4);
2974 print_debug('Selected record information',4);
2975 print_debug('qty_rcv_exception_code:'||x_shipmentdistributionrec.qty_rcv_exception_code,4);
2976 print_debug('enforce_ship_to_location_code:'||x_shipmentdistributionrec.enforce_ship_to_location_code,4);
2977 print_debug('receipt_days_exception_code:'||x_shipmentdistributionrec.receipt_days_exception_code,4);
2978 END IF;
2979 END IF;
2980 IF x_shipmentdistributionrec.qty_rcv_exception_code IS NULL THEN
2981 temp_cascaded_table(current_n).qty_rcv_exception_code := 'NONE';
2982 ELSE
2986
2983 temp_cascaded_table(current_n).qty_rcv_exception_code :=
2984 x_shipmentdistributionrec.qty_rcv_exception_code;
2985 END IF;
2987 temp_cascaded_table(current_n).po_line_location_id :=
2988 x_ShipmentDistributionRec.line_location_id;
2989
2990 IF x_ShipmentDistributionRec.enforce_ship_to_location_code =
2991 'WARNING' AND (x_cascaded_table(n).transaction_type IN
2992 ('RECEIVE', 'DELIVER')) THEN
2993 -- bug 2787530
2994 IF temp_cascaded_table(current_n).error_status = 'W' THEN
2995 temp_cascaded_table(current_n).error_message :=
2996 'INV_RCV_GEN_TOLERANCE_EXCEED';
2997 ELSE
2998 temp_cascaded_table(current_n).error_status := 'W';
2999 temp_cascaded_table(current_n).error_message := 'INV_RCV_WARN_SHIP_TO_LOC';
3000 END IF;
3001 END IF;
3002
3003 IF x_ShipmentDistributionRec.receipt_days_exception_code =
3004 'WARNING' AND (x_cascaded_table(n).transaction_type IN
3005 ('RECEIVE', 'DELIVER')) THEN
3006 -- bug 2787530
3007 IF temp_cascaded_table(current_n).error_status = 'W' THEN
3008 temp_cascaded_table(current_n).error_message :=
3009 'INV_RCV_GEN_TOLERANCE_EXCEED';
3010 ELSE
3011 temp_cascaded_table(current_n).error_status := 'W';
3012 temp_cascaded_table(current_n).error_message := 'INV_RCV_WARN_RECEIPT_DATE';
3013 END IF;
3014 END IF;
3015 IF l_print_debug = 1 THEN
3016 IF (l_debug = 1) THEN
3017 print_debug('line_location_id:'||temp_cascaded_table(current_n).po_line_location_id,4);
3018 print_debug('qty_rcv_exception_code:'||temp_cascaded_table(current_n).qty_rcv_exception_code,4);
3019 print_debug('tax_amount:'||temp_cascaded_table(current_n).tax_amount,4);
3020 print_debug('error_status:'||temp_cascaded_table(current_n).error_status,4);
3021 print_debug('error_message:'||temp_cascaded_table(current_n).error_message,4);
3022 END IF;
3023 END IF;
3024 -- Copy the distribution specific information only if this is a
3025 -- direct receipt.
3026 IF (x_cascaded_table(n).transaction_type in ('DELIVER','STD_DELIVER')) THEN
3027
3028 temp_cascaded_table(current_n).po_distribution_id :=
3029 x_ShipmentDistributionRec.po_distribution_id;
3030 temp_cascaded_table(current_n).parent_transaction_id :=
3031 x_ShipmentDistributionRec.rcv_transaction_id;
3032 IF l_print_debug = 1 THEN
3033 IF (l_debug = 1) THEN
3034 print_debug('po_distribution_id:'||temp_cascaded_table(current_n).po_distribution_id,4);
3035 print_debug('parent_transaction_id:'||temp_cascaded_table(current_n).parent_transaction_id,4);
3036 END IF;
3037 END IF;
3038 END IF;
3039
3040 current_n := current_n + 1;
3041
3042 end if;
3043 end if;
3044 end loop;
3045
3046 -- current_n := current_n - 1;
3047 -- point to the last row in the record structure before going back
3048
3049 else
3050 -- error_status and error_message are set after validate_quantity_shipped
3051
3052 if x_cascaded_table(n).error_status in ('S','W','F') then
3053 x_cascaded_table(n).error_status := 'E';
3054
3055 if (x_cascaded_table(n).error_message IS NULL) THEN
3056
3057 -- For Bug 7440217 Error Message for LCM doc failure
3058 IF l_asn_type = 'ASN' THEN
3059 x_cascaded_table(n).error_message := 'RCV_ASN_NO_PO_LINE_LOCATION_ID';
3060 ELSE
3061 x_cascaded_table(n).error_message := 'RCV_LCM_NO_PO_LINE_LOCATION_ID';
3062 END IF;
3063 -- End for Bug 7440217
3064
3065 END IF;
3066 end if;
3067 return;
3068 end if; -- of (asn quantity_shipped was valid)
3069
3070 if shipments%isopen then
3071 close shipments;
3072 end if;
3073
3074 if count_shipments%isopen then
3075 close count_shipments;
3076 end if;
3077
3078 IF asn_shipments%isopen THEN
3079 CLOSE asn_shipments;
3080 END IF;
3081
3082 IF count_asn_shipments%isopen THEN
3083 CLOSE count_asn_shipments;
3084 END IF;
3085
3086 IF asn_shipments_w_po%isopen THEN
3087 CLOSE asn_shipments_w_po;
3088 END IF;
3089
3090 IF count_asn_shipments_w_po%isopen THEN
3091 CLOSE count_asn_shipments_w_po;
3092 END IF;
3093
3094 -- For Bug 7440217 Closing the cursors
3095 IF lcm_shipments%isopen THEN
3096 CLOSE lcm_shipments;
3097 END IF;
3098
3099 IF count_lcm_shipments%isopen THEN
3100 CLOSE count_lcm_shipments;
3101 END IF;
3102
3103 IF lcm_shipments_w_po%isopen THEN
3104 CLOSE lcm_shipments_w_po;
3105 END IF;
3106
3107 IF count_lcm_shipments_w_po%isopen THEN
3108 CLOSE count_lcm_shipments_w_po;
3109 END IF;
3110 -- End for Bug 7440217
3111
3112 if distributions%isopen then
3113 close distributions;
3114 end if;
3115
3116 if count_distributions%isopen then
3117 close count_distributions;
3118 end if;
3119
3120 IF std_distributions%isopen THEN
3121 CLOSE std_distributions;
3122 END IF;
3123
3124 IF count_std_distributions%isopen THEN
3125 CLOSE count_std_distributions;
3126 END IF;
3127
3128 --4364407
3129 IF std_distributions_exp%isopen THEN
3130 CLOSE std_distributions_exp;
3131 END IF;
3132 IF count_std_distributions_exp%isopen THEN
3133 CLOSE count_std_distributions_exp;
3134 END IF;
3135 --4364407
3136
3137 exception
3138 WHEN fnd_api.g_exc_error THEN
3139 ROLLBACK TO rcv_transactions_sa;
3140 x_return_status := fnd_api.g_ret_sts_error;
3141 -- Get message count and data
3145 );
3142 fnd_msg_pub.count_and_get
3143 ( p_count => x_msg_count
3144 , p_data => x_msg_data
3146
3147 if shipments%isopen then
3148 close shipments;
3149 end if;
3150
3151 if count_shipments%isopen then
3152 close count_shipments;
3153 end if;
3154
3155 IF asn_shipments%isopen THEN
3156 CLOSE asn_shipments;
3157 END IF;
3158
3159 IF count_asn_shipments%isopen THEN
3160 CLOSE count_asn_shipments;
3161 END IF;
3162
3163 IF asn_shipments_w_po%isopen THEN
3164 CLOSE asn_shipments_w_po;
3165 END IF;
3166
3167 IF count_asn_shipments_w_po%isopen THEN
3168 CLOSE count_asn_shipments_w_po;
3169 END IF;
3170
3171 -- For Bug 7440217 Closing the cursors
3172 IF lcm_shipments%isopen THEN
3173 CLOSE lcm_shipments;
3174 END IF;
3175
3176 IF count_lcm_shipments%isopen THEN
3177 CLOSE count_lcm_shipments;
3178 END IF;
3179
3180 IF lcm_shipments_w_po%isopen THEN
3181 CLOSE lcm_shipments_w_po;
3182 END IF;
3183
3184 IF count_lcm_shipments_w_po%isopen THEN
3185 CLOSE count_lcm_shipments_w_po;
3186 END IF;
3187 -- End for Bug 7440217
3188
3189 if distributions%isopen then
3190 close distributions;
3191 end if;
3192
3193 if count_distributions%isopen then
3194 close count_distributions;
3195 end if;
3196
3197 IF std_distributions%isopen THEN
3198 CLOSE std_distributions;
3199 END IF;
3200
3201 IF count_std_distributions%isopen THEN
3202 CLOSE count_std_distributions;
3203 END IF;
3204 --4364407
3205 IF std_distributions_exp%isopen THEN
3206 CLOSE std_distributions_exp;
3207 END IF;
3208 IF count_std_distributions_exp%isopen THEN
3209 CLOSE count_std_distributions_exp;
3210 END IF;
3211 --4364407
3212
3213 WHEN fnd_api.g_exc_unexpected_error THEN
3214 ROLLBACK TO rcv_transactions_sa;
3215 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3216
3217 -- Get message count and data
3218 fnd_msg_pub.count_and_get
3219 ( p_count => x_msg_count
3220 , p_data => x_msg_data
3221 );
3222
3223 if shipments%isopen then
3224 close shipments;
3225 end if;
3226
3227 if count_shipments%isopen then
3228 close count_shipments;
3229 end if;
3230
3231 IF asn_shipments%isopen THEN
3232 CLOSE asn_shipments;
3233 END IF;
3234
3235 IF count_asn_shipments%isopen THEN
3236 CLOSE count_asn_shipments;
3237 END IF;
3238
3239 IF asn_shipments_w_po%isopen THEN
3240 CLOSE asn_shipments_w_po;
3241 END IF;
3242
3243 IF count_asn_shipments_w_po%isopen THEN
3244 CLOSE count_asn_shipments_w_po;
3245 END IF;
3246
3247 -- For Bug 7440217 Closing the cursors
3248 IF lcm_shipments%isopen THEN
3249 CLOSE lcm_shipments;
3250 END IF;
3251
3252 IF count_lcm_shipments%isopen THEN
3253 CLOSE count_lcm_shipments;
3254 END IF;
3255
3256 IF lcm_shipments_w_po%isopen THEN
3257 CLOSE lcm_shipments_w_po;
3258 END IF;
3259
3260 IF count_lcm_shipments_w_po%isopen THEN
3261 CLOSE count_lcm_shipments_w_po;
3262 END IF;
3263 -- End for Bug 7440217
3264
3265
3266 if distributions%isopen then
3267 close distributions;
3268 end if;
3269
3270 if count_distributions%isopen then
3271 close count_distributions;
3272 end if;
3273
3274 IF std_distributions%isopen THEN
3275 CLOSE std_distributions;
3276 END IF;
3277
3278 IF count_std_distributions%isopen THEN
3279 CLOSE count_std_distributions;
3280 END IF;
3281 --4364407
3282 IF std_distributions_exp%isopen THEN
3283 CLOSE std_distributions_exp;
3284 END IF;
3285 IF count_std_distributions_exp%isopen THEN
3286 CLOSE count_std_distributions_exp;
3287 END IF;
3288 --4364407
3289
3290 WHEN OTHERS THEN
3291 ROLLBACK TO rcv_transactions_sa;
3292 x_return_status := fnd_api.g_ret_sts_unexp_error ;
3293 IF SQLCODE IS NOT NULL THEN
3294 inv_mobile_helper_functions.sql_error('INV_RCV_TXN_INTERFACE.matching_logic', l_progress, SQLCODE);
3295 END IF;
3296 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
3297 THEN
3298 fnd_msg_pub.add_exc_msg
3299 ( g_pkg_name
3300 , l_api_name
3301 );
3302 END IF;
3303 -- Get message count and data
3304 fnd_msg_pub.count_and_get
3305 ( p_count => x_msg_count
3306 , p_data => x_msg_data
3307 );
3308
3309 if shipments%isopen then
3310 close shipments;
3311 end if;
3312
3313 if count_shipments%isopen then
3314 close count_shipments;
3315 end if;
3316
3317 IF asn_shipments%isopen THEN
3318 CLOSE asn_shipments;
3319 END IF;
3320
3321 IF count_asn_shipments%isopen THEN
3322 CLOSE count_asn_shipments;
3323 END IF;
3324
3325 IF asn_shipments_w_po%isopen THEN
3326 CLOSE asn_shipments_w_po;
3327 END IF;
3328
3329 IF count_asn_shipments_w_po%isopen THEN
3330 CLOSE count_asn_shipments_w_po;
3331 END IF;
3332
3333 -- For Bug 7440217 Closing the cursors
3334 IF lcm_shipments%isopen THEN
3335 CLOSE lcm_shipments;
3336 END IF;
3337
3338 IF count_lcm_shipments%isopen THEN
3339 CLOSE count_lcm_shipments;
3340 END IF;
3341
3342 IF lcm_shipments_w_po%isopen THEN
3343 CLOSE lcm_shipments_w_po;
3344 END IF;
3345
3346 IF count_lcm_shipments_w_po%isopen THEN
3347 CLOSE count_lcm_shipments_w_po;
3348 END IF;
3349 -- End for Bug 7440217
3350
3351
3352 if distributions%isopen then
3353 close distributions;
3354 end if;
3355
3356 if count_distributions%isopen then
3357 close count_distributions;
3358 end if;
3359
3360 IF std_distributions%isopen THEN
3361 CLOSE std_distributions;
3362 END IF;
3363
3364 IF count_std_distributions%isopen THEN
3365 CLOSE count_std_distributions;
3366 END IF;
3367 --4364407
3368 IF std_distributions_exp%isopen THEN
3369 CLOSE std_distributions_exp;
3370 END IF;
3371 IF count_std_distributions_exp%isopen THEN
3372 CLOSE count_std_distributions_exp;
3373 END IF;
3374 --4364407
3375
3376 x_cascaded_table(n).error_status := 'E';
3377
3378 END matching_logic;
3379 END INV_rcv_txn_interface;
[Home] [Help]