1 PACKAGE BODY GML_RCV_TXN_INTERFACE AS
2 /* $Header: GMLTISVB.pls 120.0 2005/05/25 16:27:15 appldev noship $*/
3
4 x_interface_type varchar2(25) := 'RCV-856';
5 x_dummy_flag varchar2(1) := 'Y';
6
7 g_pkg_name CONSTANT VARCHAR2(30) := 'GML_RCV_TXN_INTERFACE';
8
9 PROCEDURE matching_logic
10 (
11 x_return_status OUT nocopy VARCHAR2
12 ,x_msg_count OUT nocopy NUMBER
13 ,x_msg_data OUT nocopy VARCHAR2
14 ,x_cascaded_table IN OUT NOCOPY cascaded_trans_tab_type
15 ,n IN OUT nocopy BINARY_INTEGER
16 ,temp_cascaded_table IN OUT nocopy cascaded_trans_tab_type
17 ,p_receipt_num IN VARCHAR2
18 ,p_shipment_header_id IN NUMBER
19 ,p_lpn_id IN NUMBER)
20 IS
21 CURSOR asn_shipments
22 (
23 v_item_id NUMBER
24 , v_po_line_id NUMBER
25 , v_po_line_location_id NUMBER
26 , v_po_release_id NUMBER
27 , v_ship_to_org_id NUMBER
28 , v_ship_to_location_id NUMBER
29 , v_shipment_header_id NUMBER
30 , v_lpn_id NUMBER
31 , v_item_desc VARCHAR2
32 , v_project_id NUMBER
33 , v_task_id NUMBER)
34 IS
35 SELECT
36 pll.line_location_id
37 , pll.unit_meas_lookup_code
38 , Nvl(pll.promised_date,pll.need_by_date) promised_date
39 , pll.quantity_shipped
40 , pll.receipt_days_exception_code
41 , pll.qty_rcv_exception_code
42 , pll.days_early_receipt_allowed
43 , pll.days_late_receipt_allowed
44 , 0 po_distribution_id
45 , pll.ship_to_location_id
46 , Nvl(pll.enforce_ship_to_location_code,'NONE') enforce_ship_to_location_code
47 , 0 rcv_transaction_id -- only need it for std_deliver
48 , pl.item_revision --only needed for std_deliver
49 FROM
50 po_line_locations pll,
51 po_lines pl,
52 po_headers ph,
53 rcv_shipment_lines rsl,
54 (SELECT DISTINCT source_line_id
55 FROM wms_lpn_contents
56 WHERE parent_lpn_id = v_lpn_id) wlc
57 WHERE pll.po_line_id = Nvl(v_po_line_id, pll.po_line_id)
58 AND pll.line_location_id = Nvl(v_po_line_location_id, pll.line_location_id)
59 AND Nvl(pll.po_release_id,0) = Nvl(v_po_release_id,Nvl(pll.po_release_id,0))
60 AND pll.po_line_id = pl.po_line_id
61 -- change for non item master receipts.
62 --AND pl.item_id = v_item_id
63 and ( pl.item_id = v_item_id
64 OR ( v_item_id IS NULL
65 AND pl.item_id IS NULL
66 AND pl.item_description = v_item_desc))
67 AND NVL(pll.APPROVED_FLAG,'N') = 'Y'
68 AND NVL(pll.CANCEL_FLAG, 'N') = 'N'
69 AND NVL(pll.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED'
70 AND pll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
71 AND pll.ship_to_organization_id = Nvl(v_ship_to_org_id,pll.ship_to_organization_id)
72 AND pll.ship_to_location_id = Nvl(v_ship_to_location_id,pll.ship_to_location_id)
73 AND rsl.shipment_header_id = v_shipment_header_id
74 AND rsl.po_line_location_id = pll.line_location_id
75 AND pll.po_line_id = wlc.source_line_id (+)
76 AND pll.line_location_id in
77 ( select pod.line_location_id from po_distributions pod
78 where (v_project_id is null or
79 (v_project_id = -9999 and pod.project_id is null) or --Bug# 2669021
80 pod.project_id = v_project_id
81 )
82 and (v_task_id is null or pod.task_id = v_task_id)
83 and pod.po_header_id = pll.po_header_id
84 )
85 ORDER BY Nvl(pll.promised_date,pll.need_by_date);
86
87
88 CURSOR count_asn_shipments
89 (
90 v_item_id NUMBER
91 , v_po_line_id NUMBER
92 , v_po_line_location_id NUMBER
93 , v_po_release_id NUMBER
94 , v_ship_to_org_id NUMBER
95 , v_ship_to_location_id NUMBER
96 , v_shipment_header_id NUMBER
97 , v_lpn_id NUMBER
98 , v_item_desc VARCHAR2
99 , v_project_id NUMBER
100 , v_task_id NUMBER)
101 IS
102 SELECT COUNT(*)
103 FROM
104 po_line_locations pll,
105 po_lines pl,
106 po_headers ph,
107 rcv_shipment_lines rsl,
108 (SELECT DISTINCT source_line_id
109 FROM wms_lpn_contents
110 WHERE parent_lpn_id = v_lpn_id) wlc
111 WHERE pll.po_line_id = Nvl(v_po_line_id, pll.po_line_id)
112 AND pll.line_location_id = Nvl(v_po_line_location_id, pll.line_location_id)
113 AND Nvl(pll.po_release_id,0) = Nvl(v_po_release_id,Nvl(pll.po_release_id,0))
114 AND pll.po_line_id = pl.po_line_id
115 -- change for non item master receipts.
116 --AND pl.item_id = v_item_id
117 and ( pl.item_id = v_item_id
118 OR ( v_item_id IS NULL
119 AND pl.item_id IS NULL
120 AND pl.item_description = v_item_desc))
121 AND NVL(pll.APPROVED_FLAG,'N') = 'Y'
122 AND NVL(pll.CANCEL_FLAG, 'N') = 'N'
123 AND NVL(pll.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED'
124 AND pll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
125 AND pll.ship_to_organization_id = Nvl(v_ship_to_org_id,pll.ship_to_organization_id)
126 AND pll.ship_to_location_id = Nvl(v_ship_to_location_id,pll.ship_to_location_id)
127 AND rsl.shipment_header_id = v_shipment_header_id
128 AND rsl.po_line_location_id = pll.line_location_id
129 AND pll.po_line_id = wlc.source_line_id (+)
130 AND pll.line_location_id in
131 ( select pod.line_location_id
132 from po_distributions pod
133 where ( v_project_id is null or
134 (v_project_id = -9999 and pod.project_id is null) or --Bug# 2669021
135 pod.project_id = v_project_id
136 )
137 and (v_task_id is null or pod.task_id = v_task_id)
138 and pod.po_header_id = pll.po_header_id
139 );
140
141 CURSOR asn_shipments_w_po
142 (
143 header_id NUMBER
144 , v_item_id NUMBER
145 , v_po_line_id NUMBER
146 , v_po_line_location_id NUMBER
147 , v_po_release_id NUMBER
148 , v_ship_to_org_id NUMBER
149 , v_ship_to_location_id NUMBER
150 , v_shipment_header_id NUMBER
151 , v_lpn_id NUMBER
152 , v_item_desc VARCHAR2
153 , v_project_id NUMBER
154 , v_task_id NUMBER)
155 IS
156 SELECT
157 pll.line_location_id
158 , pll.unit_meas_lookup_code
159 , Nvl(pll.promised_date,pll.need_by_date) promised_date
160 , pll.quantity_shipped
161 , pll.receipt_days_exception_code
162 , pll.qty_rcv_exception_code
163 , pll.days_early_receipt_allowed
164 , pll.days_late_receipt_allowed
165 , 0 po_distribution_id
166 , pll.ship_to_location_id
167 , Nvl(pll.enforce_ship_to_location_code,'NONE') enforce_ship_to_location_code
168 , 0 rcv_transaction_id -- only need it for std_deliver
169 , pl.item_revision --only needed for std_deliver
170 FROM
171 po_line_locations pll,
172 po_lines pl,
173 po_headers ph,
174 rcv_shipment_lines rsl,
175 (SELECT DISTINCT source_line_id
176 FROM wms_lpn_contents
177 WHERE parent_lpn_id = v_lpn_id) wlc
178 WHERE ph.po_header_id = header_id
179 AND pll.po_header_id = header_id
180 AND pll.po_line_id = Nvl(v_po_line_id, pll.po_line_id)
181 AND pll.line_location_id = Nvl(v_po_line_location_id, pll.line_location_id)
182 AND Nvl(pll.po_release_id,0) = Nvl(v_po_release_id,Nvl(pll.po_release_id,0))
183 AND pll.po_line_id = pl.po_line_id
184 -- change for non item master receipts.
185 --AND pl.item_id = v_item_id
186 and ( pl.item_id = v_item_id
187 OR ( v_item_id IS NULL
188 AND pl.item_id IS NULL
189 AND pl.item_description = v_item_desc))
190 AND NVL(pll.APPROVED_FLAG,'N') = 'Y'
191 AND NVL(pll.CANCEL_FLAG, 'N') = 'N'
192 AND NVL(pll.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED'
193 AND pll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
194 AND pll.ship_to_organization_id = Nvl(v_ship_to_org_id,pll.ship_to_organization_id)
195 AND pll.ship_to_location_id = Nvl(v_ship_to_location_id,pll.ship_to_location_id)
196 AND rsl.shipment_header_id = v_shipment_header_id
197 AND rsl.po_line_location_id = pll.line_location_id
198 AND pll.po_line_id = wlc.source_line_id (+)
199 AND pll.line_location_id in
200 ( select pod.line_location_id
201 from po_distributions pod
202 where ( v_project_id is null or
203 (v_project_id = -9999 and pod.project_id is null) or --Bug# 2669021
204 pod.project_id = v_project_id
205 )
206 and (v_task_id is null or pod.task_id = v_task_id)
207 and pod.po_header_id = pll.po_header_id
208 )
209 ORDER BY Nvl(pll.promised_date,pll.need_by_date);
210
211
212 CURSOR count_asn_shipments_w_po
213 (
214 header_id NUMBER
215 , v_item_id NUMBER
216 , v_po_line_id NUMBER
217 , v_po_line_location_id NUMBER
218 , v_po_release_id NUMBER
219 , v_ship_to_org_id NUMBER
220 , v_ship_to_location_id NUMBER
221 , v_shipment_header_id NUMBER
222 , v_lpn_id NUMBER
223 , v_item_desc VARCHAR2
224 , v_project_id NUMBER
225 , v_task_id NUMBER)
226 IS
227 SELECT COUNT(*)
228 FROM
229 po_line_locations pll,
230 po_lines pl,
231 po_headers ph,
232 rcv_shipment_lines rsl,
233 (SELECT DISTINCT source_line_id
234 FROM wms_lpn_contents
235 WHERE parent_lpn_id = v_lpn_id) wlc
236 WHERE ph.po_header_id = header_id
237 AND pll.po_header_id = header_id
238 AND pll.po_line_id = Nvl(v_po_line_id, pll.po_line_id)
239 AND pll.line_location_id = Nvl(v_po_line_location_id, pll.line_location_id)
240 AND Nvl(pll.po_release_id,0) = Nvl(v_po_release_id,Nvl(pll.po_release_id,0))
241 AND pll.po_line_id = pl.po_line_id
242 -- change for non item master receipts.
243 --AND pl.item_id = v_item_id
244 and ( pl.item_id = v_item_id
245 OR ( v_item_id IS NULL
246 AND pl.item_id IS NULL
247 AND pl.item_description = v_item_desc))
248 AND NVL(pll.APPROVED_FLAG,'N') = 'Y'
249 AND NVL(pll.CANCEL_FLAG, 'N') = 'N'
250 AND NVL(pll.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED'
251 AND pll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
252 AND pll.ship_to_organization_id = Nvl(v_ship_to_org_id,pll.ship_to_organization_id)
253 AND pll.ship_to_location_id = Nvl(v_ship_to_location_id,pll.ship_to_location_id)
254 AND rsl.shipment_header_id = v_shipment_header_id
255 AND rsl.po_line_location_id = pll.line_location_id
256 AND pll.po_line_id = wlc.source_line_id (+)
257 AND pll.line_location_id in
258 ( select pod.line_location_id
259 from po_distributions pod
260 where ( v_project_id is null or
261 (v_project_id = -9999 and pod.project_id is null) or --Bug# 2669021
262 pod.project_id = v_project_id
263 )
264 and (v_task_id is null or pod.task_id = v_task_id)
265 and pod.po_header_id = pll.po_header_id
266 ) ;
267
268 cursor shipments
269 (
270 header_id NUMBER
271 , v_item_id NUMBER
272 , v_revision VARCHAR2
273 , v_po_line_id NUMBER
274 , v_po_line_location_id NUMBER
275 , v_po_release_id NUMBER
276 , v_ship_to_org_id NUMBER
277 , v_ship_to_location_id NUMBER
278 , v_item_desc VARCHAR2
279 , v_project_id NUMBER
280 , v_task_id NUMBER)
281 is
282 select
283 pll.line_location_id
284 , pll.unit_meas_lookup_code
285 , nvl(pll.promised_date,pll.need_by_date) promised_date
286 , pll.quantity_shipped
287 , pll.receipt_days_exception_code
288 , pll.qty_rcv_exception_code
289 , pll.days_early_receipt_allowed
290 , pll.days_late_receipt_allowed
291 , 0 po_distribution_id
292 , pll.ship_to_location_id
293 , nvl(pll.enforce_ship_to_location_code,'NONE') enforce_ship_to_location_code
294 , 0 rcv_transaction_id -- only need it for std_deliver
295 , pl.item_revision --only needed for std_deliver
296 from po_line_locations_all pll,
297 po_lines_all pl,
298 po_headers_all ph
299 where ph.po_header_id = header_id
300 and pll.po_header_id = header_id
301 AND pll.po_line_id = Nvl(v_po_line_id, pll.po_line_id)
302 AND pll.line_location_id = Nvl(v_po_line_location_id, pll.line_location_id)
303 and nvl(pll.po_release_id,0) = nvl(v_po_release_id,nvl(pll.po_release_id,0))
304 and pll.po_line_id = pl.po_line_id
305 -- change for non item master receipts.
306 and ( pl.item_id = v_item_id
307 OR ( v_item_id IS NULL
308 AND pl.item_id IS NULL
312 OR (v_revision IS NULL))
309 AND pl.item_description = v_item_desc))
310 AND ((v_revision IS NOT NULL
311 AND Nvl(pl.item_revision, v_revision) = v_revision)
313 and NVL(pll.APPROVED_FLAG,'N') = 'Y'
314 and NVL(pll.CANCEL_FLAG, 'N') = 'N'
315 and NVL(pll.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED'
316 and pll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
317 and pll.ship_to_organization_id = nvl(v_ship_to_org_id,pll.ship_to_organization_id)
318 and pll.ship_to_location_id = nvl(v_ship_to_location_id,pll.ship_to_location_id)
319 AND pll.line_location_id in
320 ( select pod.line_location_id
321 from po_distributions pod
322 where (v_project_id is null
323 or ((v_project_id = -9999 and pod.project_id is null)--Bug# 2669021
324 or (nvl(pod.project_id,-99) = v_project_id )))
325 and (v_task_id is null or nvl(pod.task_id,-9999) = v_task_id)
326 and pod.po_header_id = pll.po_header_id
327 )
328 order by pl.item_revision, nvl(pll.promised_date,pll.need_by_date);
329
330
331 CURSOR count_shipments
332 ( header_id NUMBER
333 , v_item_id NUMBER
334 , v_revision VARCHAR2
335 , v_po_line_id NUMBER
336 , v_po_line_location_id NUMBER
337 , v_po_release_id NUMBER
338 , v_ship_to_org_id NUMBER
339 , v_ship_to_location_id NUMBER
340 , v_item_desc VARCHAR2
341 , v_project_id NUMBER
342 , v_task_id NUMBER)
343
344 IS
345 SELECT COUNT(*)
346 from po_line_locations_all pll,
347 po_lines_all pl
348 WHERE pll.po_header_id = header_id
349 AND pll.po_line_id = Nvl(v_po_line_id, pll.po_line_id)
350 AND pll.line_location_id = Nvl(v_po_line_location_id, pll.line_location_id)
351 and nvl(pll.po_release_id,0) = nvl(v_po_release_id,nvl(pll.po_release_id,0))
352 and pll.po_line_id = pl.po_line_id
353 -- change for non item master receipts.
354 and ( pl.item_id = v_item_id
355 OR ( v_item_id IS NULL
356 AND pl.item_id IS NULL
357 AND pl.item_description = v_item_desc))
358 -- and pl.item_id = v_item_id
359 AND ((v_revision IS NOT NULL
360 AND Nvl(pl.item_revision, v_revision) = v_revision)
361 OR (v_revision IS NULL))
362 and NVL(pll.APPROVED_FLAG,'N') = 'Y'
363 and NVL(pll.CANCEL_FLAG, 'N') = 'N'
364 and NVL(pll.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED'
365 and pll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
366 and pll.ship_to_organization_id = nvl(v_ship_to_org_id,pll.ship_to_organization_id)
367 and pll.ship_to_location_id = nvl(v_ship_to_location_id,pll.ship_to_location_id)
368 AND pll.line_location_id in
369 ( select pod.line_location_id
370 from po_distributions pod
371 where (v_project_id is null
372 or ((v_project_id = -9999 and pod.project_id is null)--Bug# 2669021
373 or (nvl(pod.project_id,-99) = v_project_id )))
374 and (v_task_id is null or nvl(pod.task_id,-9999) = v_task_id)
375 and pod.po_header_id = pll.po_header_id
376 );
377
378 cursor distributions (
379 header_id number
380 ,v_item_id number
381 ,v_revision VARCHAR2
382 ,v_po_line_id NUMBER
383 ,v_po_line_location_id NUMBER
384 ,v_po_distribution_id NUMBER
385 ,v_po_release_id number
386 ,v_ship_to_org_id number
387 ,v_ship_to_location_id NUMBER
388 ,v_item_desc VARCHAR2
389 , v_project_id NUMBER
390 , v_task_id NUMBER) is
391 select
392 pll.line_location_id
393 ,pll.unit_meas_lookup_code
394 ,nvl(pll.promised_date,pll.need_by_date) promised_date
395 ,pll.quantity_shipped
396 ,pll.receipt_days_exception_code
397 ,pll.qty_rcv_exception_code
398 ,pll.days_early_receipt_allowed
399 ,pll.days_late_receipt_allowed
400 ,pod.po_distribution_id
401 ,pll.ship_to_location_id
402 ,nvl(pll.enforce_ship_to_location_code,'NONE') enforce_ship_to_location_code
403 ,0 rcv_transaction_id -- only need it for std_deliver
404 ,pl.item_revision --only needed for std_deliver
405 from po_distributions pod,
406 po_line_locations pll,
407 po_lines pl,
408 po_headers ph
409 where ph.po_header_id = header_id
410 and pod.po_header_id = header_id
411 AND pod.po_line_id = Nvl(v_po_line_id, pod.po_line_id)
412 AND pod.line_location_id = Nvl(v_po_line_location_id, pod.line_location_id)
413 AND pod.po_distribution_id = Nvl(v_po_distribution_id, pod.po_distribution_id)
414 and ph.po_header_id = pl.po_header_id
415 and nvl(pll.po_release_id,0) = nvl(v_po_release_id,nvl(pll.po_release_id,0))
416 and pll.po_line_id = pl.po_line_id
417 --and pl.item_id = v_item_id
418 -- change for non item master receipts.
419 and ( pl.item_id = v_item_id
420 OR ( v_item_id IS NULL
421 AND pl.item_id IS NULL
425 OR (v_revision IS NULL))
422 AND pl.item_description = v_item_desc))
423 AND ((v_revision IS NOT NULL
424 AND Nvl(pl.item_revision, v_revision) = v_revision)
426 and NVL(pll.APPROVED_FLAG,'N') = 'Y'
427 and NVL(pll.CANCEL_FLAG, 'N') = 'N'
428 and NVL(pll.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED'
429 and pll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
430 and pll.line_location_id = pod.line_location_id
431 and pll.ship_to_organization_id = nvl(v_ship_to_org_id,pll.ship_to_organization_id)
432 and pll.ship_to_location_id = nvl(v_ship_to_location_id,pll.ship_to_location_id)
433 and (v_project_id is null or
434 ( v_project_id = -9999 and pod.project_id is null ) or --Bug# 2669021
435 pod.project_id = v_project_id)
436 and (v_task_id is null or pod.task_id = v_task_id)
437 order by pl.item_revision, nvl(pll.promised_date,pll.need_by_date);
438
439 cursor count_distributions (
440 header_id number
441 , v_item_id number
442 , v_revision VARCHAR2
443 , v_po_line_id NUMBER
444 , v_po_line_location_id NUMBER
445 , v_po_distribution_id NUMBER
446 , v_po_release_id number
447 , v_ship_to_org_id number
448 , v_ship_to_location_id number
449 , v_item_desc VARCHAR2
450 , v_project_id NUMBER
451 , v_task_id NUMBER) is
452 select count(*)
453 from po_distributions pod,
454 po_line_locations pll,
455 po_lines pl
456 where pll.po_header_id = header_id
457 AND pod.po_line_id = Nvl(v_po_line_id, pod.po_line_id)
458 AND pod.line_location_id = Nvl(v_po_line_location_id, pod.line_location_id)
459 AND pod.po_distribution_id = Nvl(v_po_distribution_id, pod.po_distribution_id)
460 and nvl(pll.po_release_id,0) = nvl(v_po_release_id,nvl(pll.po_release_id,0))
461 and pll.po_line_id = pl.po_line_id
462 --and pl.item_id = v_item_id
463 -- change for non item master receipts.
464 and ( pl.item_id = v_item_id
465 OR ( v_item_id IS NULL
466 AND pl.item_id IS NULL
467 AND pl.item_description = v_item_desc))
468 AND ((v_revision IS NOT NULL
469 AND Nvl(pl.item_revision, v_revision) = v_revision)
470 OR (v_revision IS NULL))
471 and NVL(pll.APPROVED_FLAG,'N') = 'Y'
472 and NVL(pll.CANCEL_FLAG, 'N') = 'N'
473 and NVL(pll.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED'
474 and pll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
475 and pll.line_location_id = pod.line_location_id
476 and pll.ship_to_organization_id = nvl(v_ship_to_org_id,pll.ship_to_organization_id)
477 and pll.ship_to_location_id = nvl(v_ship_to_location_id,pll.ship_to_location_id)
478 and (v_project_id is null or
479 ( v_project_id = -9999 and pod.project_id is null ) or --Bug# 2669021
480 pod.project_id = v_project_id)
481 and (v_task_id is null or pod.task_id = v_task_id) ;
482
483 ----
484 cursor std_distributions (
485 header_id number
486 ,v_item_id number
487 ,v_revision varchar2
488 ,v_po_line_id NUMBER
489 ,v_po_line_location_id NUMBER
490 ,v_po_distribution_id NUMBER
491 ,v_po_release_id number
492 ,v_ship_to_org_id number
493 ,v_ship_to_location_id number
494 ,v_receipt_num varchar2
495 ,v_txn_date DATE
496 ,v_inspection_status VARCHAR2
497 ,v_lpn_id NUMBER) is
498 select
499 pll.line_location_id
500 ,pll.unit_meas_lookup_code
501 ,nvl(pll.promised_date,pll.need_by_date) promised_date
502 ,0 --pll.quantity_shipped
506 ,pll.days_late_receipt_allowed
503 ,pll.receipt_days_exception_code
504 ,pll.qty_rcv_exception_code
505 ,pll.days_early_receipt_allowed
507 ,pod.po_distribution_id
508 ,pll.ship_to_location_id
509 ,nvl(pll.enforce_ship_to_location_code,'NONE') enforce_ship_to_location_code
510 ,rs.rcv_transaction_id
511 ,rs.item_revision
512 from po_distributions pod,
513 po_line_locations pll,
514 po_lines pl,
515 po_headers ph,
516 rcv_supply rs,
517 rcv_shipment_headers rsh,
518 -- rcv_shipment_lines rsl,
519 rcv_transactions rt
520 where rsh.receipt_source_code = 'VENDOR'
521 AND ph.po_header_id = header_id
522 AND pod.po_line_id = Nvl(v_po_line_id, pod.po_line_id)
523 AND pod.line_location_id = Nvl(v_po_line_location_id, pod.line_location_id)
524 AND pod.po_distribution_id = Nvl(v_po_distribution_id, pod.po_distribution_id)
525 and pl.po_line_id = rs.po_line_id
526 and pll.line_location_id = rs.po_line_location_id
527 and pod.line_location_id = rs.po_line_location_id
528 -- and pl.item_id = v_item_id
529 and NVL(pll.APPROVED_FLAG,'N') = 'Y'
530 and NVL(pll.CANCEL_FLAG, 'N') = 'N'
531 and NVL(pll.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED'
532 and pll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
533 and pll.ship_to_organization_id = nvl(v_ship_to_org_id,pll.ship_to_organization_id)
534 and pll.ship_to_location_id = nvl(v_ship_to_location_id,pll.ship_to_location_id)
535 AND RT.TRANSACTION_TYPE <> 'UNORDERED'
536 -- for all the transactions in rt for which we can putaway, the
537 -- transfer_lpn_id should match the lpn being putaway.
538 --AND Nvl(rt.transfer_lpn_id,-1) = Nvl(v_lpn_id,-1)
539 -- Fix for 1865886. Commented the above and added the following for lpn
540 AND Nvl(v_lpn_id,-1) IN (select nvl(rt2.transfer_lpn_id,-1)
541 from rcv_transactions rt2
542 where rt2.transaction_type <> 'DELIVER'
543 start with rt2.transaction_id = rs.supply_source_id
544 connect by prior rt2.transaction_id = rt2.parent_transaction_id
545 union all
546 select nvl(rt2.lpn_id,-1)
547 from rcv_transactions rt2
548 where rt2.transaction_type not in ('RECEIVE', 'DELIVER')
549 start with rt2.transaction_id = rs.supply_source_id
550 connect by prior rt2.transaction_id = rt2.parent_transaction_id
551 )
552 --
553 and rs.supply_type_code = 'RECEIVING'
554 --and rsl.shipment_line_id = rs.shipment_line_id
555 and rsh.shipment_header_id = rs.shipment_header_id
556 AND (Nvl(rsh.receipt_num,'@@@')) = Nvl(v_receipt_num,Nvl(rsh.receipt_num,'@@@'))
557 and rt.transaction_id = rs.rcv_transaction_id
558 AND rt.transaction_date < Nvl(v_txn_date,(rt.transaction_date + 1))
559 --and rt.transaction_type <> 'UNORDERED'
560 --
561 and rs.po_header_id = header_id
562 and rs.item_id = v_item_id
563 AND ((v_revision IS NOT NULL
564 AND Nvl(rs.item_revision, v_revision) = v_revision)
565 OR (v_revision IS NULL))
566 AND (rt.routing_header_id IS NULL OR
567 rt.routing_header_id <> 2 OR
568 (rt.routing_header_id = 2
569 AND rt.inspection_status_code <> 'NOT INSPECTED'
570 AND rt.inspection_status_code = Nvl(v_inspection_status,rt.inspection_status_code)))
571 --
572 order by rs.item_revision, nvl(pll.promised_date,pll.need_by_date);
573
574 cursor count_std_distributions (
575 header_id number
576 , v_item_id NUMBER
577 , v_revision varchar2
578 , v_po_line_id NUMBER
579 , v_po_line_location_id NUMBER
580 , v_po_distribution_id NUMBER
581 , v_po_release_id number
582 , v_ship_to_org_id number
583 , v_ship_to_location_id number
584 , v_receipt_num VARCHAR2
585 , v_txn_date DATE
586 , v_inspection_status VARCHAR2
587 , v_lpn_id NUMBER) is
588 select count(*)
589 from po_distributions pod,
590 po_line_locations pll,
591 po_lines pl,
592 po_headers ph,
593 rcv_supply rs,
594 rcv_shipment_headers rsh,
595 -- rcv_shipment_lines rsl,
596 rcv_transactions rt
597 where rsh.receipt_source_code = 'VENDOR'
598 AND ph.po_header_id = header_id
599 AND pod.po_line_id = Nvl(v_po_line_id, pod.po_line_id)
600 AND pod.line_location_id = Nvl(v_po_line_location_id, pod.line_location_id)
601 AND pod.po_distribution_id = Nvl(v_po_distribution_id, pod.po_distribution_id)
602 and pl.po_line_id = rs.po_line_id
603 and pll.line_location_id = rs.po_line_location_id
604 and pod.line_location_id = rs.po_line_location_id
605 -- and NVL(pl.item_id,0) = nvl(v_item_id,nvl(pl.item_id,0))
606 and NVL(pll.APPROVED_FLAG,'N') = 'Y'
607 and NVL(pll.CANCEL_FLAG, 'N') = 'N'
608 and NVL(pll.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED'
609 and pll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
610 and pll.ship_to_organization_id = nvl(v_ship_to_org_id,pll.ship_to_organization_id)
611 and pll.ship_to_location_id = nvl(v_ship_to_location_id,pll.ship_to_location_id)
612 AND RT.TRANSACTION_TYPE <> 'UNORDERED'
616 -- Fix for 1865886. Commented the above and added the following for lpn
613 -- for all the transactions in rt for which we can putaway, the
614 -- transfer_lpn_id should match the lpn being putaway.
615 --AND Nvl(rt.transfer_lpn_id,-1) = Nvl(v_lpn_id,-1)
617 AND Nvl(v_lpn_id,-1) IN (select nvl(rt2.transfer_lpn_id,-1)
618 from rcv_transactions rt2
619 where rt2.transaction_type <> 'DELIVER'
620 start with rt2.transaction_id = rs.supply_source_id
621 connect by prior rt2.transaction_id = rt2.parent_transaction_id
622 union all
623 select nvl(rt2.lpn_id,-1)
624 from rcv_transactions rt2
625 where rt2.transaction_type not in ('RECEIVE', 'DELIVER')
626 start with rt2.transaction_id = rs.supply_source_id
627 connect by prior rt2.transaction_id = rt2.parent_transaction_id
628 )
629 --
630 and rs.supply_type_code = 'RECEIVING'
631 --and rsl.shipment_line_id = rs.shipment_line_id
632 and rsh.shipment_header_id = rs.shipment_header_id
633 AND (Nvl(rsh.receipt_num,'@@@')) = Nvl(v_receipt_num,Nvl(rsh.receipt_num,'@@@'))
634 and rt.transaction_id = rs.rcv_transaction_id
635 AND rt.transaction_date < Nvl(v_txn_date,(rt.transaction_date + 1))
636 --and rt.transaction_type <> 'UNORDERED'
637 --
638 and rs.po_header_id = header_id
639 and rs.item_id = v_item_id
640 AND ((v_revision IS NOT NULL
641 AND Nvl(rs.item_revision, v_revision) = v_revision)
642 OR (v_revision IS NULL))
643 AND (rt.routing_header_id IS NULL OR
644 rt.routing_header_id <> 2 OR
645 (rt.routing_header_id = 2
646 AND rt.inspection_status_code <> 'NOT INSPECTED'
647 AND rt.inspection_status_code = Nvl(v_inspection_status,rt.inspection_status_code)));
648 ----
649
650
651 l_item_no VARCHAR(100);
652
653 CURSOR Get_Item_No (p_item_id NUMBER, p_organization_id NUMBER) IS
654 select segment1
655 from mtl_system_items
656 where inventory_item_id = p_item_id and
657 organization_id=p_organization_id;
658
659 /*
660 ** Debug: had to change this to the distribution record
661 ** Might be a compatibility issue between the two record definitions
662 */
663 x_ShipmentDistributionRec distributions%rowtype;
664 x_record_count number;
665
666 x_remaining_quantity number := 0;
667 x_remaining_qty_po_uom number := 0;
668 x_bkp_qty number := 0;
669 x_progress varchar2(3);
670 x_converted_trx_qty number := 0;
671 transaction_ok boolean := FALSE;
672 x_expected_date rcv_transactions_interface.expected_receipt_date%TYPE;
673 high_range_date DATE;
674 low_range_date DATE;
675 rows_fetched number := 0;
676 x_tolerable_qty number := 0;
677 x_first_trans boolean := TRUE;
678 x_sysdate DATE := sysdate;
679 current_n binary_integer := 0;
680 insert_into_table boolean := FALSE;
681 x_qty_rcv_exception_code po_line_locations.qty_rcv_exception_code%type;
682 tax_amount_factor number;
683 lastrecord boolean := FALSE;
684
685 po_asn_uom_qty number;
686 po_primary_uom_qty number;
687
688 already_allocated_qty number := 0;
689
690 x_item_id number;
691 x_approved_flag varchar(1);
692 x_cancel_flag varchar(1);
693 x_closed_code varchar(25);
694 x_shipment_type varchar(25);
695 x_ship_to_location_id number;
696 x_vendor_product_num varchar(25);
697 x_temp_count number;
698 l_asn_received_qty NUMBER := 0;
699
700 l_api_name CONSTANT VARCHAR2(30) := 'matching_logic';
701
702
703 BEGIN
704
705 x_return_status := fnd_api.g_ret_sts_success;
706
707 SAVEPOINT rcv_transactions_sa;
708 -- the following steps will create a set of rows linking the line_record with
709 -- its corresponding po_line_location rows until the quantity value from
710 -- the asn is consumed. (Cascade)
711 if ((x_cascaded_table(n).po_header_id is not null) AND
712 ((x_cascaded_table(n).item_id is not NULL
713 OR (x_cascaded_table(n).item_desc IS NOT NULL
714 AND x_cascaded_table(n).transaction_type in ('DELIVER','RECEIVE')))) AND
715 (x_cascaded_table(n).error_status in ('S','W'))) then
716
717 -- Copy record from main table to temp table
718
719 current_n := 1;
720 temp_cascaded_table(current_n) := x_cascaded_table(n);
721
722 -- Get all rows which meet this condition
723
724 IF (x_cascaded_table(n).transaction_type = 'RECEIVE') THEN
725
729 (temp_cascaded_table(current_n).item_id,
726 IF (p_shipment_header_id IS NOT NULL) THEN -- matching is called from ASN shipment matching
727 IF temp_cascaded_table(current_n).po_header_id IS NULL THEN
728 OPEN asn_shipments
730 temp_cascaded_table(current_n).po_line_id,
731 temp_cascaded_table(current_n).po_line_location_id,
732 temp_cascaded_table(current_n).po_release_id,
733 temp_cascaded_table(current_n).to_organization_id,
734 NULL,--temp_cascaded_table(current_n).ship_to_location_id,
735 p_shipment_header_id,
736 p_lpn_id,
737 temp_cascaded_table(current_n).item_desc,
738 temp_cascaded_table(current_n).project_id,
739 temp_cascaded_table(current_n).task_id);
740
741 OPEN count_asn_shipments
742 (temp_cascaded_table(current_n).item_id,
743 temp_cascaded_table(current_n).po_line_id,
744 temp_cascaded_table(current_n).po_line_location_id,
745 temp_cascaded_table(current_n).po_release_id,
746 temp_cascaded_table(current_n).to_organization_id,
747 NULL,--temp_cascaded_table(current_n).ship_to_location_id,
748 p_shipment_header_id,
749 p_lpn_id,
750 temp_cascaded_table(current_n).item_desc,
751 temp_cascaded_table(current_n).project_id,
752 temp_cascaded_table(current_n).task_id);
753 ELSE
754 OPEN asn_shipments_w_po
755 (temp_cascaded_table(current_n).po_header_id,
756 temp_cascaded_table(current_n).item_id,
757 temp_cascaded_table(current_n).po_line_id,
758 temp_cascaded_table(current_n).po_line_location_id,
759 temp_cascaded_table(current_n).po_release_id,
760 temp_cascaded_table(current_n).to_organization_id,
761 NULL,--temp_cascaded_table(current_n).ship_to_location_id,
762 p_shipment_header_id,
763 p_lpn_id,
764 temp_cascaded_table(current_n).item_desc,
765 temp_cascaded_table(current_n).project_id,
766 temp_cascaded_table(current_n).task_id);
767
768 OPEN count_asn_shipments_w_po
769 (temp_cascaded_table(current_n).po_header_id,
770 temp_cascaded_table(current_n).item_id,
771 temp_cascaded_table(current_n).po_line_id,
772 temp_cascaded_table(current_n).po_line_location_id,
773 temp_cascaded_table(current_n).po_release_id,
774 temp_cascaded_table(current_n).to_organization_id,
775 NULL,--temp_cascaded_table(current_n).ship_to_location_id,
776 p_shipment_header_id,
777 p_lpn_id,
778 temp_cascaded_table(current_n).item_desc,
779 temp_cascaded_table(current_n).project_id,
780 temp_cascaded_table(current_n).task_id);
781 END IF;
782 ELSE -- normal PO receipt
783 OPEN shipments (temp_cascaded_table(current_n).po_header_id,
784 temp_cascaded_table(current_n).item_id,
785 temp_cascaded_table(current_n).revision,
786 temp_cascaded_table(current_n).po_line_id,
787 temp_cascaded_table(current_n).po_line_location_id,
788 temp_cascaded_table(current_n).po_release_id,
789 temp_cascaded_table(current_n).to_organization_id,
790 NULL, --temp_cascaded_table(current_n).ship_to_location_id,
791 temp_cascaded_table(current_n).item_desc,
792 temp_cascaded_table(current_n).project_id,
793 temp_cascaded_table(current_n).task_id);
794
795 -- count_shipments just gets the count of rows found in shipments
796
797 OPEN count_shipments (temp_cascaded_table(current_n).po_header_id,
798 temp_cascaded_table(current_n).item_id,
799 temp_cascaded_table(current_n).revision,
800 temp_cascaded_table(current_n).po_line_id,
801 temp_cascaded_table(current_n).po_line_location_id,
802 temp_cascaded_table(current_n).po_release_id,
803 temp_cascaded_table(current_n).to_organization_id,
804 NULL, --temp_cascaded_table(current_n).ship_to_location_id,
805 temp_cascaded_table(current_n).item_desc,
806 temp_cascaded_table(current_n).project_id,
807 temp_cascaded_table(current_n).task_id);
808 END IF;
809
810 ELSIF (x_cascaded_table(n).transaction_type = 'DELIVER') THEN
811
812 open distributions (temp_cascaded_table(current_n).po_header_id,
813 temp_cascaded_table(current_n).item_id,
814 temp_cascaded_table(current_n).revision,
815 temp_cascaded_table(current_n).po_line_id,
816 temp_cascaded_table(current_n).po_line_location_id,
817 temp_cascaded_table(current_n).po_distribution_id,
818 temp_cascaded_table(current_n).po_release_id,
819 temp_cascaded_table(current_n).to_organization_id,
820 NULL, --temp_cascaded_table(current_n).ship_to_location_id,
821 temp_cascaded_table(current_n).item_desc,
822 temp_cascaded_table(current_n).project_id,
826
823 temp_cascaded_table(current_n).task_id);
824
825 -- count_distributions just gets the count of rows found in distributions
827 open count_distributions (temp_cascaded_table(current_n).po_header_id,
828 temp_cascaded_table(current_n).item_id,
829 temp_cascaded_table(current_n).revision,
830 temp_cascaded_table(current_n).po_line_id,
831 temp_cascaded_table(current_n).po_line_location_id,
832 temp_cascaded_table(current_n).po_distribution_id,
833 temp_cascaded_table(current_n).po_release_id,
834 temp_cascaded_table(current_n).to_organization_id,
835 NULL, --temp_cascaded_table(current_n).ship_to_location_id,
836 temp_cascaded_table(current_n).item_desc,
837 temp_cascaded_table(current_n).project_id,
838 temp_cascaded_table(current_n).task_id);
839
840 ELSIF (x_cascaded_table(n).transaction_type = 'STD_DELIVER') THEN
841 open std_distributions (
842 temp_cascaded_table(current_n).po_header_id,
843 temp_cascaded_table(current_n).item_id,
844 temp_cascaded_table(current_n).revision,
845 temp_cascaded_table(current_n).po_line_id,
846 temp_cascaded_table(current_n).po_line_location_id,
847 temp_cascaded_table(current_n).po_distribution_id,
848 temp_cascaded_table(current_n).po_release_id,
849 temp_cascaded_table(current_n).to_organization_id,
850 NULL,--temp_cascaded_table(current_n).ship_to_location_id,
851 p_receipt_num,
852 temp_cascaded_table(current_n).expected_receipt_date,
853 temp_cascaded_table(current_n).inspection_status_code,
854 temp_cascaded_table(current_n).p_lpn_id);
855
856 -- count_distributions just gets the count of rows found in distributions
857
858 open count_std_distributions (temp_cascaded_table(current_n).po_header_id,
859 temp_cascaded_table(current_n).item_id,
860 temp_cascaded_table(current_n).revision,
861 temp_cascaded_table(current_n).po_line_id,
862 temp_cascaded_table(current_n).po_line_location_id,
863 temp_cascaded_table(current_n).po_distribution_id,
864 temp_cascaded_table(current_n).po_release_id,
865 temp_cascaded_table(current_n).to_organization_id,
866 NULL,--temp_cascaded_table(current_n).ship_to_location_id,
867 p_receipt_num,
868 temp_cascaded_table(current_n).expected_receipt_date,
869 temp_cascaded_table(current_n).inspection_status_code,
870 temp_cascaded_table(current_n).p_lpn_id);
871
872
873 END IF;
874
875 -- Assign shipped quantity to remaining quantity
876 x_remaining_quantity := temp_cascaded_table(current_n).quantity;
877
878 -- used for decrementing cum qty for first record
879 x_bkp_qty := x_remaining_quantity;
880 x_remaining_qty_po_uom := 0;
881
882 -- Calculate tax_amount_factor for calculating tax_amount for
883 -- each cascaded line
884
885 if nvl(temp_cascaded_table(current_n).tax_amount,0) <> 0 THEN
886 tax_amount_factor := temp_cascaded_table(current_n).tax_amount/x_remaining_quantity;
887 else
888 tax_amount_factor := 0;
889 end if;
890
891 x_first_trans := TRUE;
892 transaction_ok := FALSE;
893
894
895 -- Get the count of the number of records depending on the
896 -- the transaction type
897
898 IF (x_cascaded_table(n).transaction_type = 'RECEIVE') THEN
899
900 IF p_shipment_header_id IS NOT NULL THEN
901 IF temp_cascaded_table(current_n).po_header_id IS NULL THEN
902 FETCH count_asn_shipments INTO x_record_count;
903 ELSE
904 FETCH count_asn_shipments_w_po INTO x_record_count;
905 END IF;
906 ELSE
907 FETCH count_shipments INTO x_record_count;
908 END IF;
909
910 ELSIF (x_cascaded_table(n).transaction_type = 'DELIVER') THEN
911
912 FETCH count_distributions INTO x_record_count;
913
914 ELSIF (x_cascaded_table(n).transaction_type = 'STD_DELIVER') THEN
915
916 FETCH count_std_distributions INTO x_record_count;
917
918 END IF;
919
920
921 LOOP
922 -- Fetch the appropriate record
923 IF (x_cascaded_table(n).transaction_type = 'RECEIVE') THEN
924
925 IF p_shipment_header_id IS NOT NULL THEN
926 IF temp_cascaded_table(current_n).po_header_id IS NULL THEN
927 FETCH asn_shipments INTO x_ShipmentDistributionRec;
928 -- Check if this is the last record
929 IF (asn_shipments%NOTFOUND) THEN
930 lastrecord := TRUE;
931 END IF;
932 rows_fetched := asn_shipments%rowcount;
933 ELSE
934 FETCH asn_shipments_w_po INTO x_shipmentdistributionrec;
938 END IF;
935 -- Check if this is the last record
936 IF (asn_shipments_w_po%NOTFOUND) THEN
937 lastrecord := TRUE;
939 rows_fetched := asn_shipments_w_po%rowcount;
940 END IF;
941 ELSE
942
943 FETCH shipments INTO x_ShipmentDistributionRec;
944 -- Check if this is the last record
945 IF (shipments%NOTFOUND) THEN
946 lastrecord := TRUE;
947 END IF;
948 rows_fetched := shipments%rowcount;
949 END IF;
950
951
952 ELSIF (x_cascaded_table(n).transaction_type = 'DELIVER') THEN
953
954 fetch distributions into x_ShipmentDistributionRec;
955
956 -- Check if this is the last record
957 if (distributions%NOTFOUND) THEN
958 lastrecord := TRUE;
959 END IF;
960
961 rows_fetched := distributions%rowcount;
962 ELSIF (x_cascaded_table(n).transaction_type = 'STD_DELIVER') THEN
963
964 fetch std_distributions into x_ShipmentDistributionRec;
965
966 -- Check if this is the last record
967 if (std_distributions%NOTFOUND) THEN
968 lastrecord := TRUE;
969 END IF;
970
971 rows_fetched := std_distributions%rowcount;
972 END IF;
973 if (lastrecord or x_remaining_quantity <= 0) then
974
975 if not x_first_trans then
976 -- x_first_trans has been reset which means some cascade has
977 -- happened. Otherwise current_n = 1
978 current_n := current_n -1 ;
979 end if;
980
981 -- do the tolerance act here
982
983 -- lastrecord...we have run out of rows and
984 -- we still have quantity to allocate
985
986 if x_remaining_quantity > 0 then
987 if not x_first_trans then
988 -- we had got atleast some rows from our shipments cursor
989 -- we have atleast one row cascaded (not null line_location_id)
990 IF x_cascaded_table(n).transaction_type IN ('RECEIVE', 'DELIVER') THEN
991 x_qty_rcv_exception_code := temp_cascaded_table(current_n).qty_rcv_exception_code;
992 ELSE
993 x_qty_rcv_exception_code := 'REJECT';
994 END IF;
995
996 if x_qty_rcv_exception_code IN ('NONE','WARNING') then
997 temp_cascaded_table(current_n).quantity :=
998 temp_cascaded_table(current_n).quantity +
999 x_remaining_quantity;
1000
1001 temp_cascaded_table(current_n).quantity_shipped :=
1002 temp_cascaded_table(current_n).quantity_shipped +
1003 x_remaining_quantity;
1004
1005 temp_cascaded_table(current_n).source_doc_quantity :=
1006 temp_cascaded_table(current_n).source_doc_quantity +
1007 x_remaining_qty_po_uom;
1008 IF temp_cascaded_table(1).primary_unit_of_measure IS
1009 NULL THEN
1010 temp_cascaded_table(1).primary_unit_of_measure :=
1011 x_ShipmentDistributionRec.unit_meas_lookup_code;
1012 END IF;
1013 temp_cascaded_table(current_n).primary_quantity :=
1014 temp_cascaded_table(current_n).primary_quantity +
1015 rcv_transactions_interface_sv.convert_into_correct_qty(
1016 x_remaining_quantity,
1017 temp_cascaded_table(1).unit_of_measure,
1018 temp_cascaded_table(1).item_id,
1019 temp_cascaded_table(1).primary_unit_of_measure);
1020
1021 temp_cascaded_table(current_n).tax_amount :=
1022 round(temp_cascaded_table(current_n).quantity * tax_amount_factor,6);
1023
1024 if x_qty_rcv_exception_code = 'WARNING' then
1025 -- bug 2787530
1026 IF temp_cascaded_table(current_n).error_status = 'W' THEN
1027 temp_cascaded_table(current_n).error_message :=
1028 'INV_RCV_GEN_TOLERANCE_EXCEED';
1029 ELSE
1030 temp_cascaded_table(current_n).error_status := 'W';
1031 temp_cascaded_table(current_n).error_message :=
1032 'INV_RCV_QTY_OVER_TOLERANCE';
1033 END IF;
1034 end if;
1035
1036 elsif x_qty_rcv_exception_code = 'REJECT' then
1037 x_cascaded_table(n).error_status := 'E';
1038 x_cascaded_table(n).error_message := 'INV_RCV_QTY_OVER_TOLERANCE';
1039
1040 if temp_cascaded_table.count > 0 then
1041 for i in 1..temp_cascaded_table.count loop
1042 temp_cascaded_table.delete(i);
1043 end loop;
1044 end if;
1045 end if;
1046
1047 ELSE -- for if remaining_qty > 0 and not x_first_trans
1048
1049 x_cascaded_table(n).error_status := 'E';
1050 x_cascaded_table(n).error_message := 'INV_RCV_QTY_OVER_TOLERANCE';
1051
1052 if rows_fetched = 0 then
1053 x_cascaded_table(n).error_message := 'INV_RCV_NO_ROWS';
1054 elsif x_first_trans then
1058 -- Delete the temp_cascaded_table just to be sure
1055 x_cascaded_table(n).error_message := 'INV_RCV_GEN_TOLERANCE_EXCEED';
1056 end if;
1057
1059 if temp_cascaded_table.count > 0 then
1060 for i in 1..temp_cascaded_table.count loop
1061 temp_cascaded_table.delete(i);
1062 end loop;
1063 end if;
1064 END IF;
1065 else
1066 null;
1067
1068 end if; -- x_remaining_qty > 0
1069 -- close cursors
1070 if shipments%isopen then
1071 close shipments;
1072 end if;
1073
1074 if count_shipments%isopen then
1075 close count_shipments;
1076 end if;
1077
1078 IF asn_shipments%isopen THEN
1079 CLOSE asn_shipments;
1080 END IF;
1081
1082 IF count_asn_shipments%isopen THEN
1083 CLOSE count_asn_shipments;
1084 END IF;
1085
1086 IF asn_shipments_w_po%isopen THEN
1087 CLOSE asn_shipments_w_po;
1088 END IF;
1089
1090 IF count_asn_shipments_w_po%isopen THEN
1091 CLOSE count_asn_shipments_w_po;
1092 END IF;
1093
1094 if distributions%isopen then
1095 close distributions;
1096 end if;
1097
1098 if count_distributions%isopen then
1099 close count_distributions;
1100 end if;
1101
1102 IF std_distributions%isopen THEN
1103 CLOSE std_distributions;
1104 END IF;
1105
1106 IF count_std_distributions%isopen THEN
1107 CLOSE count_std_distributions;
1108 END IF;
1109
1110 exit;
1111
1112 end if; -- if (lastrecord or x_remaining_quantity <= 0)
1113
1114 -- eliminate the row if it fails the date check
1115 if (temp_cascaded_table(1).expected_receipt_date is not null) then
1116 if (x_ShipmentDistributionRec.promised_date is not null) then
1117 -- bug 2750081
1118 -- the null days early allowed and days late allowed should
1119 -- be interpreted as infinite and not zero.
1120 IF x_ShipmentDistributionRec.days_early_receipt_allowed IS NULL THEN
1121 low_range_date := Trunc(temp_cascaded_table(1).expected_receipt_date);
1122 else
1123 low_range_date := x_ShipmentDistributionRec.promised_date -
1124 nvl(x_ShipmentDistributionRec.days_early_receipt_allowed,0);
1125 END IF;
1126 IF x_ShipmentDistributionRec.days_late_receipt_allowed IS NULL THEN
1127 high_range_date := Trunc(temp_cascaded_table(1).expected_receipt_date);
1128 else
1129 high_range_date := x_ShipmentDistributionRec.promised_date +
1130 nvl(x_ShipmentDistributionRec.days_late_receipt_allowed,0);
1131 END IF;
1132 else
1133 IF x_ShipmentDistributionRec.days_early_receipt_allowed IS NULL THEN
1134 low_range_date := Trunc(temp_cascaded_table(1).expected_receipt_date);
1135 else
1136 low_range_date := x_sysdate -
1137 nvl(x_ShipmentDistributionRec.days_early_receipt_allowed,0);
1138 END IF;
1139 IF x_ShipmentDistributionRec.days_late_receipt_allowed IS NULL THEN
1140 high_range_date := Trunc(temp_cascaded_table(1).expected_receipt_date);
1141 else
1142 high_range_date := x_sysdate +
1143 nvl(x_ShipmentDistributionRec.days_late_receipt_allowed,0);
1144 END IF;
1145 end if;
1146 if (Trunc(temp_cascaded_table(1).expected_receipt_date) >= low_range_date and
1147 Trunc(temp_cascaded_table(1).expected_receipt_date) <= high_range_date) then
1148 x_ShipmentDistributionRec.receipt_days_exception_code := 'NONE';
1149 end if;
1150 else
1151 x_ShipmentDistributionRec.receipt_days_exception_code := 'NONE';
1152 end if;
1153 if x_ShipmentDistributionRec.receipt_days_exception_code is null then
1154 x_ShipmentDistributionRec.receipt_days_exception_code := 'NONE';
1155 end if;
1156
1157 -- if the row does not fall within the date tolerance we just
1158 -- leave it aside and then take the next row. If the date
1159 -- tolerance is just set to warning then we continue with this
1160 -- row. The same applies to the ship to location check too.
1161
1162
1163 -- Check ship_to_location enforcement
1164 IF x_shipmentdistributionrec.enforce_ship_to_location_code <> 'NONE' THEN
1165 IF nvl(temp_cascaded_table(1).ship_to_location_id,x_ShipmentDistributionRec.ship_to_location_id) = x_ShipmentDistributionRec.ship_to_location_id THEN
1166 x_shipmentdistributionrec.enforce_ship_to_location_code := 'NONE';
1167 END IF;
1168 END IF;
1169
1170 if (x_ShipmentDistributionRec.receipt_days_exception_code IN ('NONE', 'WARNING')) and
1171 (x_ShipmentDistributionRec.enforce_ship_to_location_code IN ('NONE','WARNING')) THEN
1172 -- derived by the date tolerance procedure
1173 -- derived by shipto_enforcement
1174
1175 insert_into_table := FALSE;
1176 already_allocated_qty := 0;
1177 -- Get the available quantity for the shipment or distribution
1178 -- that is available for allocation by this interface transaction
1182 x_ShipmentDistributionRec.line_location_id,
1179 IF (x_cascaded_table(n).transaction_type = 'RECEIVE') THEN
1180 rcv_quantities_s.get_available_quantity(
1181 'RECEIVE',
1183 'VENDOR',
1184 null,
1185 null,
1186 null,
1187 x_converted_trx_qty,
1188 x_tolerable_qty,
1189 x_ShipmentDistributionRec.unit_meas_lookup_code);
1190 -- If qtys have already been allocated for this po_line_location_id
1191 -- during a cascade process which has not been written to the db yet,
1192 -- we need to decrement it from the total available quantity
1193 -- We traverse the actual pl/sql table and accumulate the quantity by
1194 -- matching the po_line_location_id
1195
1196 l_asn_received_qty := 0;
1197 IF n > 1 THEN -- We will do this for all rows except the 1st
1198 FOR i in 1..(n-1) LOOP
1199 IF x_cascaded_table(i).po_line_location_id =
1200 x_ShipmentDistributionRec.line_location_id THEN
1201 already_allocated_qty := already_allocated_qty +
1202 x_cascaded_table(i).source_doc_quantity;
1203 IF p_shipment_header_id IS NOT NULL THEN
1204 l_asn_received_qty := already_allocated_qty;
1205 END IF;
1206 END IF;
1207 END LOOP;
1208 END IF;
1209
1210 ELSIF (x_cascaded_table(n).transaction_type = 'DELIVER') THEN
1211 rcv_quantities_s.get_available_quantity(
1212 'DIRECT RECEIPT',
1213 x_ShipmentDistributionRec.po_distribution_id,
1214 'VENDOR',
1215 null,
1216 null,
1217 null,
1218 x_converted_trx_qty,
1219 x_tolerable_qty,
1220 x_ShipmentDistributionRec.unit_meas_lookup_code);
1221
1222 --- Commented out the following line to fix the lolerence checking bug
1223 ---x_tolerable_qty := x_converted_trx_qty;
1224
1225 -- If qtys have already been allocated for this po_distribution_id
1226 -- during
1227 -- a cascade process which has not been written to the db yet, we need to
1228 -- decrement it from the total available quantity
1229 -- We traverse the actual pl/sql table and accumulate the quantity by
1230 -- matching the
1231 -- po_distribution_id
1232 IF n > 1 THEN -- We will do this for all rows except the 1st
1233 FOR i in 1..(n-1) LOOP
1234 IF x_cascaded_table(i).po_distribution_id =
1235 x_ShipmentDistributionRec.po_distribution_id THEN
1236 already_allocated_qty := already_allocated_qty +
1237 x_cascaded_table(i).source_doc_quantity;
1238 END IF;
1239 END LOOP;
1240 END IF;
1241
1242 ELSIF (x_cascaded_table(n).transaction_type = 'STD_DELIVER') THEN
1243 rcv_quantities_s.get_available_quantity(
1244 'STANDARD DELIVER',
1245 x_ShipmentDistributionRec.po_distribution_id,
1246 'VENDOR',
1247 null,
1248 x_ShipmentDistributionRec.rcv_transaction_id,
1249 null,
1250 x_converted_trx_qty,
1251 x_tolerable_qty,
1252 x_ShipmentDistributionRec.unit_meas_lookup_code);
1253 x_tolerable_qty := x_converted_trx_qty;
1254 -- If qtys have already been allocated for this po_distribution_id
1255 -- during
1256 -- a cascade process which has not been written to the db yet, we need to
1257 -- decrement it from the total available quantity
1258 -- We traverse the actual pl/sql table and accumulate the quantity by
1259 -- matching the
1260 -- po_distribution_id
1261 IF n > 1 THEN -- We will do this for all rows except the 1st
1262 FOR i in 1..(n-1) LOOP
1263 IF x_cascaded_table(i).po_distribution_id =
1264 x_ShipmentDistributionRec.po_distribution_id AND
1265 x_cascaded_table(i).parent_transaction_id =
1266 x_ShipmentDistributionRec.rcv_transaction_id THEN
1267 already_allocated_qty := already_allocated_qty +
1268 x_cascaded_table(i).source_doc_quantity;
1269 END IF;
1270 END LOOP;
1271 END IF;
1272 END IF;
1273 -- if qty has already been allocated then reduce available and tolerable
1274 -- qty by the allocated amount
1278 IF x_converted_trx_qty < 0 THEN
1275 IF nvl(already_allocated_qty,0) > 0 THEN
1276 x_converted_trx_qty := x_converted_trx_qty - already_allocated_qty;
1277 x_tolerable_qty := x_tolerable_qty - already_allocated_qty;
1279 x_converted_trx_qty := 0;
1280 END IF;
1281 IF x_tolerable_qty < 0 THEN
1282 x_tolerable_qty := 0;
1283 END IF;
1284 END IF;
1285
1286 -- We can use the first record since the item_id and uom are not going to
1287 -- change
1288 -- Check that we can convert between ASN-> PO uom
1289 -- PO -> ASN uom
1290 -- PO -> PRIMARY uom
1291 -- If any of the conversions fail then we cannot use that record
1292
1293 x_remaining_qty_po_uom := 0; -- initialize
1294 po_asn_uom_qty := 0; -- initialize
1295 po_primary_uom_qty := 0; -- initialize
1296
1297 -- converts from temp_cascaded_table(1).unit_of_measure to
1298 -- x_ShipmentDistributionRec.unit_meas_lookup_code
1299 x_remaining_qty_po_uom :=
1300 rcv_transactions_interface_sv.convert_into_correct_qty(x_remaining_quantity,
1301 temp_cascaded_table(1).unit_of_measure,
1302 temp_cascaded_table(1).item_id,
1303 x_ShipmentDistributionRec.unit_meas_lookup_code);
1304
1305 IF x_remaining_qty_po_uom <> 0 THEN
1306 -- If last row set available = tolerable - shipped
1307 -- else = available - shipped
1308 -- Debug: Were a bit troubled here. How do we know if the shipment
1309 -- is taken into account here. I guess if the transaction
1310 -- has the shipment line id then we should take the quantity from
1311 -- the shipped quantity. Need to walk through the different
1312 -- scenarios
1313 IF p_shipment_header_id IS NULL THEN
1314 l_asn_received_qty := 0;
1315 END IF;
1316
1317 if rows_fetched = x_record_count then
1318 -- Bug 2496230
1319 -- For asn receipts, the shipped quantity also includes
1320 -- the current quantity being received. So the converted
1321 -- and the tolerable quantity should add the
1322 -- l_asn_received_qty as already_allocated_qty has been
1323 -- reduced from the converted and tolerable qty above.
1324 -- Otherwise it will resuly in double decrementing.
1325 x_converted_trx_qty := x_tolerable_qty -
1326 nvl(x_ShipmentDistributionRec.quantity_shipped,0) +
1327 l_asn_received_qty;
1328 else
1329 x_converted_trx_qty := x_converted_trx_qty -
1330 nvl(x_ShipmentDistributionRec.quantity_shipped,0) +
1331 l_asn_received_qty;
1332 end if;
1333
1334 if x_converted_trx_qty > 0 then
1335 if (x_converted_trx_qty < x_remaining_qty_po_uom) then
1336 -- compare like uoms
1337
1338 x_remaining_qty_po_uom := x_remaining_qty_po_uom -
1339 x_converted_trx_qty;
1340
1341 -- change asn uom qty so both qtys are in sync
1342
1343 x_remaining_quantity :=
1344 rcv_transactions_interface_sv.convert_into_correct_qty(x_remaining_qty_po_uom,
1345 x_ShipmentDistributionRec.unit_meas_lookup_code,
1346 temp_cascaded_table(1).item_id,
1347 temp_cascaded_table(1).unit_of_measure);
1348
1349 insert_into_table := TRUE;
1350 else
1351 x_converted_trx_qty := x_remaining_qty_po_uom;
1352 insert_into_table := TRUE;
1353 x_remaining_qty_po_uom := 0;
1354 x_remaining_quantity := 0;
1355
1356 end if;
1357
1358 else -- no qty for this record but if last row we need it
1359 if rows_fetched = x_record_count then
1360 -- last row needs to be inserted anyway
1361 -- so that the row can be used based on qty tolerance
1362 -- checks
1363
1364 insert_into_table := TRUE;
1365 x_converted_trx_qty := 0;
1366
1367 else
1368 x_remaining_qty_po_uom := 0;
1369 -- we may have a diff uom on the next iteration
1370 insert_into_table := FALSE;
1371 end if;
1372
1373 end if;
1374
1375 end if; -- remaining_qty_po_uom <> 0
1376
1377 if insert_into_table then
1378 if (x_first_trans) then
1379 x_first_trans := FALSE;
1380 else
1381 temp_cascaded_table(current_n) := temp_cascaded_table(current_n - 1);
1382 end if;
1383
1384 -- source_doc_quantity -> in po_uom
1385 -- primary_quantity -> in primary_uom
1386 -- cum_qty -> in primary_uom
1387 -- quantity,quantity_shipped -> in ASN uom
1388
1389 temp_cascaded_table(current_n).source_doc_quantity :=
1390 x_converted_trx_qty; -- in po uom
1391 temp_cascaded_table(current_n).source_doc_unit_of_measure :=
1395 rcv_transactions_interface_sv.convert_into_correct_qty(
1392 x_ShipmentDistributionRec.unit_meas_lookup_code;
1393
1394 temp_cascaded_table(current_n).quantity :=
1396 x_converted_trx_qty,
1397 x_ShipmentDistributionRec.unit_meas_lookup_code,
1398 temp_cascaded_table(current_n).item_id,
1399 temp_cascaded_table(current_n).unit_of_measure); -- in asn uom
1400
1401 temp_cascaded_table(current_n).quantity_shipped :=
1402 temp_cascaded_table(current_n).quantity; -- in asn uom
1403
1404 -- Primary qty in Primary UOM
1405 IF temp_cascaded_table(current_n).primary_unit_of_measure IS
1406 NULL THEN
1407 temp_cascaded_table(current_n).primary_unit_of_measure :=
1408 x_ShipmentDistributionRec.unit_meas_lookup_code;
1409 END IF;
1410 temp_cascaded_table(current_n).primary_quantity :=
1411 rcv_transactions_interface_sv.convert_into_correct_qty(
1412 x_converted_trx_qty,
1413 x_ShipmentDistributionRec.unit_meas_lookup_code,
1414 temp_cascaded_table(current_n).item_id,
1415 temp_cascaded_table(current_n).primary_unit_of_measure);
1416
1417 temp_cascaded_table(current_n).tax_amount :=
1418 round(temp_cascaded_table(current_n).quantity * tax_amount_factor,4);
1419
1420 IF x_shipmentdistributionrec.qty_rcv_exception_code IS NULL THEN
1421 temp_cascaded_table(current_n).qty_rcv_exception_code := 'NONE';
1422 ELSE
1423 temp_cascaded_table(current_n).qty_rcv_exception_code :=
1424 x_shipmentdistributionrec.qty_rcv_exception_code;
1425 END IF;
1426
1427 temp_cascaded_table(current_n).po_line_location_id :=
1428 x_ShipmentDistributionRec.line_location_id;
1429
1430 IF x_ShipmentDistributionRec.enforce_ship_to_location_code =
1431 'WARNING' AND (x_cascaded_table(n).transaction_type IN
1432 ('RECEIVE', 'DELIVER')) THEN
1433 -- bug 2787530
1434 IF temp_cascaded_table(current_n).error_status = 'W' THEN
1435 temp_cascaded_table(current_n).error_message :=
1436 'INV_RCV_GEN_TOLERANCE_EXCEED';
1437 ELSE
1438 temp_cascaded_table(current_n).error_status := 'W';
1439 temp_cascaded_table(current_n).error_message := 'INV_RCV_WARN_SHIP_TO_LOC';
1440 END IF;
1441 END IF;
1442
1443 IF x_ShipmentDistributionRec.receipt_days_exception_code =
1444 'WARNING' AND (x_cascaded_table(n).transaction_type IN
1445 ('RECEIVE', 'DELIVER')) THEN
1446 -- bug 2787530
1447 IF temp_cascaded_table(current_n).error_status = 'W' THEN
1448 temp_cascaded_table(current_n).error_message :=
1449 'INV_RCV_GEN_TOLERANCE_EXCEED';
1450 ELSE
1451 temp_cascaded_table(current_n).error_status := 'W';
1452 temp_cascaded_table(current_n).error_message := 'INV_RCV_WARN_RECEIPT_DATE';
1453 END IF;
1454 END IF;
1455 -- Copy the distribution specific information only if this is a
1456 -- direct receipt.
1457 IF (x_cascaded_table(n).transaction_type in ('DELIVER','STD_DELIVER')) THEN
1458
1459 temp_cascaded_table(current_n).po_distribution_id :=
1460 x_ShipmentDistributionRec.po_distribution_id;
1461 temp_cascaded_table(current_n).parent_transaction_id :=
1462 x_ShipmentDistributionRec.rcv_transaction_id;
1463 END IF;
1464
1465 current_n := current_n + 1;
1466
1467 end if;
1468 end if;
1469 end loop;
1470
1471 -- current_n := current_n - 1;
1472 -- point to the last row in the record structure before going back
1473
1474 else
1475 -- error_status and error_message are set after validate_quantity_shipped
1476
1477 if x_cascaded_table(n).error_status in ('S','W','F') then
1478 x_cascaded_table(n).error_status := 'E';
1479
1480 if (x_cascaded_table(n).error_message IS NULL) THEN
1481 x_cascaded_table(n).error_message := 'RCV_ASN_NO_PO_LINE_LOCATION_ID';
1482 END IF;
1483 end if;
1484 return;
1485 end if; -- of (asn quantity_shipped was valid)
1486
1487
1488
1489 -- OPM change.Bug# 3061052
1490 -- if original receiving transaction line is split and secondary quantity is specified then
1491 -- calculate secondary quantity for the split lines.
1492
1493
1494 ---IF x_cascaded_table(n).secondary_quantity IS NOT NULL THEN
1495 ---IF temp_cascaded_table.EXISTS(1) AND temp_cascaded_table.COUNT > 1 THEN
1496 IF temp_cascaded_table.COUNT > 1 THEN
1497 FOR j IN 1 .. temp_cascaded_table.COUNT LOOP
1498
1499
1500 OPEN Get_Item_No (temp_cascaded_table(j).item_id, temp_cascaded_table(j).to_organization_id);
1501 FETCH Get_Item_No INTO l_item_no;
1502 CLOSE Get_Item_No;
1503
1504 GML_MOBILE_RECEIPT.Calculate_Secondary_Qty(
1505 p_item_no => l_item_no,
1506 p_unit_of_measure => temp_cascaded_table(j).unit_of_measure,
1507 p_quantity => temp_cascaded_table(j).quantity,
1508 p_lot_no => NULL,
1509 p_sublot_no => NULL,
1513 END LOOP;
1510 p_secondary_unit_of_measure => temp_cascaded_table(j).secondary_unit_of_measure,
1511 x_secondary_quantity => temp_cascaded_table(j).secondary_quantity);
1512
1514 END IF;
1515 ---END IF;
1516
1517
1518 if shipments%isopen then
1519 close shipments;
1520 end if;
1521
1522 if count_shipments%isopen then
1523 close count_shipments;
1524 end if;
1525
1526 IF asn_shipments%isopen THEN
1527 CLOSE asn_shipments;
1528 END IF;
1529
1530 IF count_asn_shipments%isopen THEN
1531 CLOSE count_asn_shipments;
1532 END IF;
1533
1534 IF asn_shipments_w_po%isopen THEN
1535 CLOSE asn_shipments_w_po;
1536 END IF;
1537
1538 IF count_asn_shipments_w_po%isopen THEN
1539 CLOSE count_asn_shipments_w_po;
1540 END IF;
1541
1542 if distributions%isopen then
1543 close distributions;
1544 end if;
1545
1546 if count_distributions%isopen then
1547 close count_distributions;
1548 end if;
1549
1550 IF std_distributions%isopen THEN
1551 CLOSE std_distributions;
1552 END IF;
1553
1554 IF count_std_distributions%isopen THEN
1555 CLOSE count_std_distributions;
1556 END IF;
1557
1558 exception
1559 WHEN fnd_api.g_exc_error THEN
1560 ROLLBACK TO rcv_transactions_sa;
1561 x_return_status := fnd_api.g_ret_sts_error;
1562 -- Get message count and data
1563 fnd_msg_pub.count_and_get
1564 ( p_count => x_msg_count
1565 , p_data => x_msg_data
1566 );
1567
1568 if shipments%isopen then
1569 close shipments;
1570 end if;
1571
1572 if count_shipments%isopen then
1573 close count_shipments;
1574 end if;
1575
1576 IF asn_shipments%isopen THEN
1577 CLOSE asn_shipments;
1578 END IF;
1579
1580 IF count_asn_shipments%isopen THEN
1581 CLOSE count_asn_shipments;
1582 END IF;
1583
1584 IF asn_shipments_w_po%isopen THEN
1585 CLOSE asn_shipments_w_po;
1586 END IF;
1587
1588 IF count_asn_shipments_w_po%isopen THEN
1589 CLOSE count_asn_shipments_w_po;
1590 END IF;
1591
1592 if distributions%isopen then
1593 close distributions;
1594 end if;
1595
1596 if count_distributions%isopen then
1597 close count_distributions;
1598 end if;
1599
1600 IF std_distributions%isopen THEN
1601 CLOSE std_distributions;
1602 END IF;
1603
1604 IF count_std_distributions%isopen THEN
1605 CLOSE count_std_distributions;
1606 END IF;
1607
1608 WHEN fnd_api.g_exc_unexpected_error THEN
1609 ROLLBACK TO rcv_transactions_sa;
1610 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1611
1612 -- Get message count and data
1613 fnd_msg_pub.count_and_get
1614 ( p_count => x_msg_count
1615 , p_data => x_msg_data
1616 );
1617
1618 if shipments%isopen then
1619 close shipments;
1620 end if;
1621
1622 if count_shipments%isopen then
1623 close count_shipments;
1624 end if;
1625
1626 IF asn_shipments%isopen THEN
1627 CLOSE asn_shipments;
1628 END IF;
1629
1630 IF count_asn_shipments%isopen THEN
1631 CLOSE count_asn_shipments;
1632 END IF;
1633
1634 IF asn_shipments_w_po%isopen THEN
1635 CLOSE asn_shipments_w_po;
1636 END IF;
1637
1638 IF count_asn_shipments_w_po%isopen THEN
1639 CLOSE count_asn_shipments_w_po;
1640 END IF;
1641
1642 if distributions%isopen then
1643 close distributions;
1644 end if;
1645
1646 if count_distributions%isopen then
1647 close count_distributions;
1648 end if;
1649
1650 IF std_distributions%isopen THEN
1651 CLOSE std_distributions;
1652 END IF;
1653
1654 IF count_std_distributions%isopen THEN
1655 CLOSE count_std_distributions;
1656 END IF;
1657
1658 WHEN OTHERS THEN
1659 ROLLBACK TO rcv_transactions_sa;
1660 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1661 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
1662 THEN
1663 fnd_msg_pub.add_exc_msg
1664 ( g_pkg_name
1665 , l_api_name
1666 );
1667 END IF;
1668 -- Get message count and data
1669 fnd_msg_pub.count_and_get
1670 ( p_count => x_msg_count
1671 , p_data => x_msg_data
1672 );
1673
1674 if shipments%isopen then
1675 close shipments;
1676 end if;
1677
1678 if count_shipments%isopen then
1679 close count_shipments;
1680 end if;
1681
1682 IF asn_shipments%isopen THEN
1683 CLOSE asn_shipments;
1684 END IF;
1685
1686 IF count_asn_shipments%isopen THEN
1687 CLOSE count_asn_shipments;
1688 END IF;
1689
1690 IF asn_shipments_w_po%isopen THEN
1691 CLOSE asn_shipments_w_po;
1692 END IF;
1693
1694 IF count_asn_shipments_w_po%isopen THEN
1695 CLOSE count_asn_shipments_w_po;
1696 END IF;
1697
1698 if distributions%isopen then
1699 close distributions;
1700 end if;
1701
1702 if count_distributions%isopen then
1703 close count_distributions;
1704 end if;
1705
1706 IF std_distributions%isopen THEN
1707 CLOSE std_distributions;
1708 END IF;
1709
1710 IF count_std_distributions%isopen THEN
1711 CLOSE count_std_distributions;
1712 END IF;
1713
1714 x_cascaded_table(n).error_status := 'E';
1715
1716 END matching_logic;
1717
1718
1719 END GML_rcv_txn_interface;
[Home] [Help]