[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT * FROM ( -- 14776842
SELECT rsl.shipment_line_id
, rsl.unit_of_measure
, rsl.item_id
, 'VENDOR' receipt_source_code -- 14776842
, rsl.to_organization_id
, 0 rcv_transaction_id -- 14776842
, To_number(NULL) oe_order_line_id -- 14776842
, poll.po_header_id
, poll.po_line_id
, poll.line_location_id
, pod.po_distribution_id
, rsl.item_description
, Nvl(rsh.expected_receipt_date, Sysdate) expected_receipt_date
FROM rcv_shipment_lines rsl
, rcv_shipment_headers rsh
, po_line_locations poll
, po_distributions pod
, (SELECT DISTINCT source_line_id
FROM wms_lpn_contents
WHERE parent_lpn_id = v_lpn_id) wlc
WHERE rsl.shipment_header_id = rsh.shipment_header_id
AND rsl.shipment_header_id = v_shipment_header_id
AND rsl.shipment_line_id = Nvl(v_shipment_line_id, rsl.shipment_line_id)
AND poll.po_header_id = Nvl(v_po_header_id, poll.po_header_id)
-- Bug 3213241
AND poll.po_line_id = wlc.source_line_id (+)
AND v_wms_po_j_or_higher = 'N'
AND pod.line_location_id = poll.line_location_id
AND (rsl.item_id = v_item_id
OR (v_item_id IS NULL
AND rsl.item_id IS NULL
AND rsl.item_description = v_item_desc))
AND poll.approved_flag = 'Y'
AND Nvl(poll.cancel_flag, 'N') = 'N'
AND Nvl(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND poll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
AND poll.line_location_id = rsl.po_line_location_id
AND rsh.asn_type IN ('ASN', 'ASBN')
-- bug 2752051
AND rsl.shipment_line_status_code IN ('EXPECTED','PARTIALLY RECEIVED') --<> 'CANCELLED'
AND ( v_project_id is null or
(v_project_id = -9999 and pod.project_id is null) or -- bug2669021
pod.project_id = v_project_id
)
and (v_task_id is null or pod.task_id = v_task_id)
UNION ALL
SELECT rsl.shipment_line_id
, rsl.unit_of_measure
, rsl.item_id
, 'VENDOR' receipt_source_code -- 14776842
, rsl.to_organization_id
, 0 -- rcv_transaction_id rcv_transaction_id -- 14776842
, To_number(NULL) oe_order_line_id -- 14776842
, poll.po_header_id
, poll.po_line_id
, poll.line_location_id
, pod.po_distribution_id
, rsl.item_description
, Nvl(rsh.expected_receipt_date, Sysdate) expected_receipt_date
FROM rcv_shipment_lines rsl
, rcv_shipment_headers rsh
, po_line_locations poll
, po_distributions pod
WHERE rsl.shipment_header_id = rsh.shipment_header_id
AND rsl.shipment_header_id = v_shipment_header_id
AND rsl.shipment_line_id = Nvl(v_shipment_line_id, rsl.shipment_line_id)
AND poll.po_header_id = Nvl(v_po_header_id, poll.po_header_id)
-- Bug 3213241
AND v_wms_po_j_or_higher = 'Y'
AND (((rsl.asn_lpn_id IS NOT NULL
AND rsl.asn_lpn_id = v_lpn_id
)
)
OR (rsl.asn_lpn_id IS NULL)
OR (v_lpn_id IS NULL)
)
AND pod.line_location_id = poll.line_location_id
AND (rsl.item_id = v_item_id
OR (v_item_id IS NULL
AND rsl.item_id IS NULL
AND rsl.item_description = v_item_desc))
AND poll.approved_flag = 'Y'
AND Nvl(poll.cancel_flag, 'N') = 'N'
AND Nvl(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND poll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
AND poll.line_location_id = rsl.po_line_location_id
AND rsh.asn_type IN ('ASN', 'ASBN')
-- bug 2752051
AND rsl.shipment_line_status_code IN ('EXPECTED','PARTIALLY RECEIVED') --<> 'CANCELLED'
AND ( v_project_id is null or
(v_project_id = -9999 and pod.project_id is null) or -- bug2669021
pod.project_id = v_project_id
)
and (v_task_id is null or pod.task_id = v_task_id)
)ta --14776842
ORDER BY expected_receipt_date, Decode(unit_of_measure,v_uom_code,0,1); --14776842
SELECT COUNT(*) FROM
( SELECT 1
FROM rcv_shipment_lines rsl
, rcv_shipment_headers rsh
, po_line_locations poll
, po_distributions pod
, (SELECT DISTINCT source_line_id
FROM wms_lpn_contents
WHERE parent_lpn_id = v_lpn_id) wlc
WHERE rsl.shipment_header_id = rsh.shipment_header_id
AND rsl.shipment_header_id = v_shipment_header_id
AND rsl.shipment_line_id = Nvl(v_shipment_line_id, rsl.shipment_line_id)
AND poll.po_header_id = Nvl(v_po_header_id, poll.po_header_id)
-- Bug 3213241
--AND poll.po_line_id = wlc.source_line_id (+)
AND poll.po_line_id = wlc.source_line_id (+)
AND v_wms_po_j_or_higher = 'N'
AND pod.line_location_id = poll.line_location_id
AND (rsl.item_id = v_item_id
OR (v_item_id IS NULL
AND rsl.item_id IS NULL
AND rsl.item_description = v_item_desc))
AND poll.approved_flag = 'Y'
AND Nvl(poll.cancel_flag, 'N') = 'N'
AND Nvl(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND poll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
AND poll.line_location_id = rsl.po_line_location_id
AND rsh.asn_type IN ('ASN', 'ASBN')
-- bug 2752051
AND rsl.shipment_line_status_code IN ('EXPECTED','PARTIALLY RECEIVED') --<> 'CANCELLED'
AND ( v_project_id is null or
(v_project_id = -9999 and pod.project_id is null) or --bug 2669021
pod.project_id = v_project_id
)
and (v_task_id is null or pod.task_id = v_task_id)
UNION ALL
SELECT 1
FROM rcv_shipment_lines rsl
, rcv_shipment_headers rsh
, po_line_locations poll
, po_distributions pod
WHERE rsl.shipment_header_id = rsh.shipment_header_id
AND rsl.shipment_header_id = v_shipment_header_id
AND rsl.shipment_line_id = Nvl(v_shipment_line_id, rsl.shipment_line_id)
AND poll.po_header_id = Nvl(v_po_header_id, poll.po_header_id)
-- Bug 3213241
AND v_wms_po_j_or_higher = 'Y'
AND (((rsl.asn_lpn_id IS NOT NULL
AND rsl.asn_lpn_id = v_lpn_id
)
)
OR (rsl.asn_lpn_id IS NULL)
OR (v_lpn_id IS NULL)
)
AND pod.line_location_id = poll.line_location_id
AND (rsl.item_id = v_item_id
OR (v_item_id IS NULL
AND rsl.item_id IS NULL
AND rsl.item_description = v_item_desc))
AND poll.approved_flag = 'Y'
AND Nvl(poll.cancel_flag, 'N') = 'N'
AND Nvl(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND poll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
AND poll.line_location_id = rsl.po_line_location_id
AND rsh.asn_type IN ('ASN', 'ASBN')
-- bug 2752051
AND rsl.shipment_line_status_code IN ('EXPECTED','PARTIALLY RECEIVED') --<> 'CANCELLED'
AND ( v_project_id is null or
(v_project_id = -9999 and pod.project_id is null) or -- bug2669021
pod.project_id = v_project_id
)
and (v_task_id is null or pod.task_id = v_task_id) ) ;
SELECT shipment_line_id, -- 14776842
unit_of_measure,
item_id,
receipt_source_code,
to_organization_id,
rcv_transaction_id,
oe_order_line_id,
po_header_id,
po_line_id,
line_location_id,
po_distribution_id,
item_description,
expected_receipt_date
FROM ( -- 14776842
SELECT shipment_line_id /*Bug 13598673 modified the cursor by adding outer query to add order by*/
, unit_of_measure
, item_id
, 'VENDOR' receipt_source_code -- 14776842
, to_organization_id
, 0 rcv_transaction_id -- 14776842
, To_number(NULL) oe_order_line_id -- 14776842
, po_header_id
, po_line_id
, line_location_id
, To_number(NULL) po_distribution_id -- 14776842
, item_description
, expected_receipt_date
, revision, quantity_received -- 14776842
FROM( SELECT rsl.shipment_line_id shipment_line_id
, rsl.unit_of_measure unit_of_measure
, rsl.item_id item_id
, 'VENDOR'
, rsl.to_organization_id to_organization_id
, 0 -- rcv_transaction_id
, To_number(NULL)
, poll.po_header_id po_header_id
, poll.po_line_id po_line_id
, poll.line_location_id line_location_id
, To_number(NULL)
, rsl.item_description item_description
, Nvl(rsh.expected_receipt_date, Sysdate) expected_receipt_date
, (rsl.QUANTITY_SHIPPED - nvl(rsl.quantity_received, 0)) quantity_received --Bug 13598673
, decode(nvl(rsl.item_revision,'@@'), nvl(v_item_revision, '@@'),0,1) revision --Bug 13598673
FROM rcv_shipment_lines rsl
, rcv_shipment_headers rsh
, po_line_locations poll
, (SELECT DISTINCT source_line_id
FROM wms_lpn_contents
WHERE parent_lpn_id = v_lpn_id) wlc
WHERE rsl.shipment_header_id = rsh.shipment_header_id
AND rsl.shipment_header_id = v_shipment_header_id
AND rsl.shipment_line_id = Nvl(v_shipment_line_id, rsl.shipment_line_id)
AND poll.po_header_id = Nvl(v_po_header_id, poll.po_header_id)
-- Bug 3213241
--AND poll.po_line_id = wlc.source_line_id (+)
AND poll.po_line_id = wlc.source_line_id (+)
AND v_wms_po_j_or_higher = 'N'
-- change to receive non-item master lines
--AND rsl.item_id = v_item_id
AND ( rsl.item_id = v_item_id
OR ( v_item_id IS NULL
AND rsl.item_id IS NULL
AND rsl.item_description = v_item_desc))
AND poll.approved_flag = 'Y'
AND Nvl(poll.cancel_flag, 'N') = 'N'
AND Nvl(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND poll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
AND poll.line_location_id = rsl.po_line_location_id
AND rsh.asn_type IN ('ASN', 'ASBN')
-- bug 2752051
AND rsl.shipment_line_status_code IN ('EXPECTED','PARTIALLY RECEIVED') --<> 'CANCELLED'
AND exists
( select '1'
from po_distributions_all pod
where pod.line_location_id = poll.line_location_id
and (v_project_id is null or
(v_project_id = -9999 and pod.project_id is null) or -- bug2669021
pod.project_id = v_project_id
)
and (v_task_id is null or pod.task_id = v_task_id)
)
AND (NVL(rsl.quantity_received,0)=0 OR NVL(rsl.item_revision, '@@') = NVL(v_item_revision, '@@')) --Bug 13598673
UNION ALL
SELECT rsl.shipment_line_id shipment_line_id
, rsl.unit_of_measure unit_of_measure
, rsl.item_id item_id
, 'VENDOR' receipt_source_code -- 14776842
, rsl.to_organization_id to_organization_id
, 0 rcv_transaction_id -- 14776842
, To_number(NULL) oe_order_line_id -- 14776842
, poll.po_header_id po_header_id
, poll.po_line_id po_line_id
, poll.line_location_id line_location_id
, To_number(NULL) po_distribution_id -- 14776842
, rsl.item_description item_description
, Nvl(rsh.expected_receipt_date, Sysdate) expected_receipt_date
, (rsl.QUANTITY_SHIPPED - nvl(rsl.quantity_received, 0)) quantity_received --Bug 13598673
, decode(nvl(rsl.item_revision,'@@'), nvl(v_item_revision, '@@'),0,1) revision --Bug 13598673
FROM rcv_shipment_lines rsl
, rcv_shipment_headers rsh
, po_line_locations poll
WHERE rsl.shipment_header_id = rsh.shipment_header_id
AND rsl.shipment_header_id = v_shipment_header_id
AND rsl.shipment_line_id = Nvl(v_shipment_line_id, rsl.shipment_line_id)
AND poll.po_header_id = Nvl(v_po_header_id, poll.po_header_id)
-- Bug 3213241
AND v_wms_po_j_or_higher = 'Y'
AND (((rsl.asn_lpn_id IS NOT NULL
AND rsl.asn_lpn_id = v_lpn_id
)
)
OR (rsl.asn_lpn_id IS NULL)
OR (v_lpn_id IS NULL)
)
-- change to receive non-item master lines
--AND rsl.item_id = v_item_id
AND ( rsl.item_id = v_item_id
OR ( v_item_id IS NULL
AND rsl.item_id IS NULL
AND rsl.item_description = v_item_desc))
AND poll.approved_flag = 'Y'
AND Nvl(poll.cancel_flag, 'N') = 'N'
AND Nvl(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND poll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
AND poll.line_location_id = rsl.po_line_location_id
AND rsh.asn_type IN ('ASN', 'ASBN')
-- bug 2752051
AND rsl.shipment_line_status_code IN ('EXPECTED','PARTIALLY RECEIVED') --<> 'CANCELLED'
--Added for Bug 12732516
AND (
v_lot_number IS NULL
OR
EXISTS
(
SELECT lot_num
FROM rcv_lots_supply rls
WHERE rls.shipment_line_id = rsl.shipment_line_id
AND rls.lot_num = v_lot_number
AND rls.supply_type_code = 'SHIPMENT'
)
)
AND exists
( select '1'
from po_distributions_all pod
where pod.line_location_id = poll.line_location_id
and (v_project_id is null or
(v_project_id = -9999 and pod.project_id is null) or -- bug2669021
pod.project_id = v_project_id
)
and (v_task_id is null or pod.task_id = v_task_id)
)
AND (NVL(rsl.quantity_received,0)=0 OR NVL(rsl.item_revision, '@@') = NVL(v_item_revision, '@@'))) --Bug 13598673
)ta -- 14776842
ORDER BY expected_receipt_date, revision, quantity_received , Decode(unit_of_measure,v_uom_code,0,1); --14776842
SELECT COUNT(*) FROM
( SELECT 1
FROM rcv_shipment_lines rsl
, rcv_shipment_headers rsh
, po_line_locations poll
, (SELECT DISTINCT source_line_id
FROM wms_lpn_contents
WHERE parent_lpn_id = v_lpn_id) wlc
WHERE rsl.shipment_header_id = rsh.shipment_header_id
AND rsl.shipment_header_id = v_shipment_header_id
AND rsl.shipment_line_id = Nvl(v_shipment_line_id, rsl.shipment_line_id)
AND poll.po_header_id = Nvl(v_po_header_id, poll.po_header_id)
-- Bug 3213241
--AND poll.po_line_id = wlc.source_line_id (+)
AND poll.po_line_id = wlc.source_line_id (+)
AND v_wms_po_j_or_higher = 'N'
-- change to receive non-item master lines
--AND rsl.item_id = v_item_id
AND ( rsl.item_id = v_item_id
OR ( v_item_id IS NULL
AND rsl.item_id IS NULL
AND rsl.item_description = v_item_desc))
AND poll.approved_flag = 'Y'
AND Nvl(poll.cancel_flag, 'N') = 'N'
AND Nvl(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND poll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
AND poll.line_location_id = rsl.po_line_location_id
AND rsh.asn_type IN ('ASN', 'ASBN')
-- bug 2752051
AND rsl.shipment_line_status_code IN ('EXPECTED','PARTIALLY RECEIVED') --<> 'CANCELLED'
AND exists
( select '1'
from po_distributions_all pod
where pod.line_location_id = poll.line_location_id
and (v_project_id is null or
(v_project_id = -9999 and pod.project_id is null) or -- bug2669021
pod.project_id = v_project_id
)
and (v_task_id is null or pod.task_id = v_task_id)
)
AND (NVL(rsl.quantity_received,0)=0 OR NVL(rsl.item_revision, '@@') = NVL(v_item_revision, '@@')) --Bug 13598673
UNION ALL
SELECT 1
FROM rcv_shipment_lines rsl
, rcv_shipment_headers rsh
, po_line_locations poll
WHERE rsl.shipment_header_id = rsh.shipment_header_id
AND rsl.shipment_header_id = v_shipment_header_id
AND rsl.shipment_line_id = Nvl(v_shipment_line_id, rsl.shipment_line_id)
AND poll.po_header_id = Nvl(v_po_header_id, poll.po_header_id)
-- Bug 3213241
AND v_wms_po_j_or_higher = 'Y'
AND (((rsl.asn_lpn_id IS NOT NULL
AND rsl.asn_lpn_id = v_lpn_id
)
)
OR (rsl.asn_lpn_id IS NULL)
OR (v_lpn_id IS NULL)
)
-- change to receive non-item master lines
--AND rsl.item_id = v_item_id
AND ( rsl.item_id = v_item_id
OR ( v_item_id IS NULL
AND rsl.item_id IS NULL
AND rsl.item_description = v_item_desc))
AND poll.approved_flag = 'Y'
AND Nvl(poll.cancel_flag, 'N') = 'N'
AND Nvl(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND poll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
AND poll.line_location_id = rsl.po_line_location_id
AND rsh.asn_type IN ('ASN', 'ASBN')
-- bug 2752051
AND rsl.shipment_line_status_code IN ('EXPECTED','PARTIALLY RECEIVED') --<> 'CANCELLED'
--Added for Bug 12732516
AND (
v_lot_number IS NULL
OR
EXISTS
(
SELECT lot_num
FROM rcv_lots_supply rls
WHERE rls.shipment_line_id = rsl.shipment_line_id
AND rls.lot_num = v_lot_number
AND rls.supply_type_code = 'SHIPMENT'
)
)
AND exists
( select '1'
from po_distributions_all pod
where pod.line_location_id = poll.line_location_id
and (v_project_id is null or
(v_project_id = -9999 and pod.project_id is null) or -- bug2669021
pod.project_id = v_project_id
)
and (v_task_id is null or pod.task_id = v_task_id)
)
AND (NVL(rsl.quantity_received,0)=0 OR NVL(rsl.item_revision, '@@') = NVL(v_item_revision, '@@')) --Bug 13598673
);
SELECT rsl.shipment_line_id
, rsl.unit_of_measure
, rsl.item_id
, 'VENDOR'
, rsl.to_organization_id
, 0 -- rcv_transaction_id
, To_number(NULL)
, poll.po_header_id
, poll.po_line_id
, poll.line_location_id
, pod.po_distribution_id
, rsl.item_description
, Nvl(rsh.expected_receipt_date, Sysdate) expected_receipt_date
FROM rcv_shipment_lines rsl
, rcv_shipment_headers rsh
, po_line_locations poll
, po_distributions pod
, (SELECT DISTINCT source_line_id
FROM wms_lpn_contents
WHERE parent_lpn_id = v_lpn_id) wlc
WHERE rsl.shipment_header_id = rsh.shipment_header_id
AND rsl.shipment_header_id = v_shipment_header_id
AND rsl.shipment_line_id = Nvl(v_shipment_line_id, rsl.shipment_line_id)
AND poll.po_header_id = Nvl(v_po_header_id, poll.po_header_id)
-- Bug 3213241
AND poll.po_line_id = wlc.source_line_id (+)
AND v_wms_po_j_or_higher = 'N'
AND pod.line_location_id = poll.line_location_id
AND (rsl.item_id = v_item_id
OR (v_item_id IS NULL
AND rsl.item_id IS NULL
AND rsl.item_description = v_item_desc))
AND poll.approved_flag = 'Y'
AND Nvl(poll.cancel_flag, 'N') = 'N'
AND Nvl(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND poll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
AND poll.line_location_id = rsl.po_line_location_id
AND rsh.asn_type IN ('LCM')
-- bug 2752051
AND rsl.shipment_line_status_code IN ('EXPECTED','PARTIALLY RECEIVED') --<> 'CANCELLED'
AND ( v_project_id is null or
(v_project_id = -9999 and pod.project_id is null) or -- bug2669021
pod.project_id = v_project_id
)
and (v_task_id is null or pod.task_id = v_task_id)
UNION ALL
SELECT rsl.shipment_line_id
, rsl.unit_of_measure
, rsl.item_id
, 'VENDOR'
, rsl.to_organization_id
, 0 -- rcv_transaction_id
, To_number(NULL)
, poll.po_header_id
, poll.po_line_id
, poll.line_location_id
, pod.po_distribution_id
, rsl.item_description
, Nvl(rsh.expected_receipt_date, Sysdate) expected_receipt_date
FROM rcv_shipment_lines rsl
, rcv_shipment_headers rsh
, po_line_locations poll
, po_distributions pod
WHERE rsl.shipment_header_id = rsh.shipment_header_id
AND rsl.shipment_header_id = v_shipment_header_id
AND rsl.shipment_line_id = Nvl(v_shipment_line_id, rsl.shipment_line_id)
AND poll.po_header_id = Nvl(v_po_header_id, poll.po_header_id)
-- Bug 3213241
AND v_wms_po_j_or_higher = 'Y'
AND pod.line_location_id = poll.line_location_id
AND (rsl.item_id = v_item_id
OR (v_item_id IS NULL
AND rsl.item_id IS NULL
AND rsl.item_description = v_item_desc))
AND poll.approved_flag = 'Y'
AND Nvl(poll.cancel_flag, 'N') = 'N'
AND Nvl(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND poll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
AND poll.line_location_id = rsl.po_line_location_id
AND rsh.asn_type IN ('LCM')
-- bug 2752051
AND rsl.shipment_line_status_code IN ('EXPECTED','PARTIALLY RECEIVED') --<> 'CANCELLED'
AND ( v_project_id is null or
(v_project_id = -9999 and pod.project_id is null) or -- bug2669021
pod.project_id = v_project_id
)
and (v_task_id is null or pod.task_id = v_task_id)
ORDER BY expected_receipt_date;
SELECT COUNT(*) FROM
( SELECT 1
FROM rcv_shipment_lines rsl
, rcv_shipment_headers rsh
, po_line_locations poll
, po_distributions pod
, (SELECT DISTINCT source_line_id
FROM wms_lpn_contents
WHERE parent_lpn_id = v_lpn_id) wlc
WHERE rsl.shipment_header_id = rsh.shipment_header_id
AND rsl.shipment_header_id = v_shipment_header_id
AND rsl.shipment_line_id = Nvl(v_shipment_line_id, rsl.shipment_line_id)
AND poll.po_header_id = Nvl(v_po_header_id, poll.po_header_id)
-- Bug 3213241
--AND poll.po_line_id = wlc.source_line_id (+)
AND poll.po_line_id = wlc.source_line_id (+)
AND v_wms_po_j_or_higher = 'N'
AND pod.line_location_id = poll.line_location_id
AND (rsl.item_id = v_item_id
OR (v_item_id IS NULL
AND rsl.item_id IS NULL
AND rsl.item_description = v_item_desc))
AND poll.approved_flag = 'Y'
AND Nvl(poll.cancel_flag, 'N') = 'N'
AND Nvl(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND poll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
AND poll.line_location_id = rsl.po_line_location_id
AND rsh.asn_type IN ('LCM')
-- bug 2752051
AND rsl.shipment_line_status_code IN ('EXPECTED','PARTIALLY RECEIVED') --<> 'CANCELLED'
AND ( v_project_id is null or
(v_project_id = -9999 and pod.project_id is null) or --bug 2669021
pod.project_id = v_project_id
)
and (v_task_id is null or pod.task_id = v_task_id)
UNION ALL
SELECT 1
FROM rcv_shipment_lines rsl
, rcv_shipment_headers rsh
, po_line_locations poll
, po_distributions pod
WHERE rsl.shipment_header_id = rsh.shipment_header_id
AND rsl.shipment_header_id = v_shipment_header_id
AND rsl.shipment_line_id = Nvl(v_shipment_line_id, rsl.shipment_line_id)
AND poll.po_header_id = Nvl(v_po_header_id, poll.po_header_id)
-- Bug 3213241
AND v_wms_po_j_or_higher = 'Y'
AND pod.line_location_id = poll.line_location_id
AND (rsl.item_id = v_item_id
OR (v_item_id IS NULL
AND rsl.item_id IS NULL
AND rsl.item_description = v_item_desc))
AND poll.approved_flag = 'Y'
AND Nvl(poll.cancel_flag, 'N') = 'N'
AND Nvl(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND poll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
AND poll.line_location_id = rsl.po_line_location_id
AND rsh.asn_type IN ('LCM')
-- bug 2752051
AND rsl.shipment_line_status_code IN ('EXPECTED','PARTIALLY RECEIVED') --<> 'CANCELLED'
AND ( v_project_id is null or
(v_project_id = -9999 and pod.project_id is null) or -- bug2669021
pod.project_id = v_project_id
)
and (v_task_id is null or pod.task_id = v_task_id) ) ;
SELECT rsl.shipment_line_id
, rsl.unit_of_measure
, rsl.item_id
, 'VENDOR'
, rsl.to_organization_id
, 0 -- rcv_transaction_id
, To_number(NULL)
, poll.po_header_id
, poll.po_line_id
, poll.line_location_id
, To_number(NULL)
, rsl.item_description
, Nvl(rsh.expected_receipt_date, Sysdate) expected_receipt_date
FROM rcv_shipment_lines rsl
, rcv_shipment_headers rsh
, po_line_locations poll
, (SELECT DISTINCT source_line_id
FROM wms_lpn_contents
WHERE parent_lpn_id = v_lpn_id) wlc
WHERE rsl.shipment_header_id = rsh.shipment_header_id
AND rsl.shipment_header_id = v_shipment_header_id
AND rsl.shipment_line_id = Nvl(v_shipment_line_id, rsl.shipment_line_id)
AND poll.po_header_id = Nvl(v_po_header_id, poll.po_header_id)
-- Bug 3213241
--AND poll.po_line_id = wlc.source_line_id (+)
AND poll.po_line_id = wlc.source_line_id (+)
AND v_wms_po_j_or_higher = 'N'
-- change to receive non-item master lines
--AND rsl.item_id = v_item_id
AND ( rsl.item_id = v_item_id
OR ( v_item_id IS NULL
AND rsl.item_id IS NULL
AND rsl.item_description = v_item_desc))
AND poll.approved_flag = 'Y'
AND Nvl(poll.cancel_flag, 'N') = 'N'
AND Nvl(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND poll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
AND poll.line_location_id = rsl.po_line_location_id
AND rsh.asn_type IN ('LCM')
-- bug 2752051
AND rsl.shipment_line_status_code IN ('EXPECTED','PARTIALLY RECEIVED') --<> 'CANCELLED'
AND exists
( select '1'
from po_distributions_all pod
where pod.line_location_id = poll.line_location_id
and (v_project_id is null or
(v_project_id = -9999 and pod.project_id is null) or -- bug2669021
pod.project_id = v_project_id
)
and (v_task_id is null or pod.task_id = v_task_id)
)
UNION ALL
SELECT rsl.shipment_line_id
, rsl.unit_of_measure
, rsl.item_id
, 'VENDOR'
, rsl.to_organization_id
, 0 -- rcv_transaction_id
, To_number(NULL)
, poll.po_header_id
, poll.po_line_id
, poll.line_location_id
, To_number(NULL)
, rsl.item_description
, Nvl(rsh.expected_receipt_date, Sysdate) expected_receipt_date
FROM rcv_shipment_lines rsl
, rcv_shipment_headers rsh
, po_line_locations poll
WHERE rsl.shipment_header_id = rsh.shipment_header_id
AND rsl.shipment_header_id = v_shipment_header_id
AND rsl.shipment_line_id = Nvl(v_shipment_line_id, rsl.shipment_line_id)
AND poll.po_header_id = Nvl(v_po_header_id, poll.po_header_id)
-- Bug 3213241
AND v_wms_po_j_or_higher = 'Y'
-- change to receive non-item master lines
--AND rsl.item_id = v_item_id
AND ( rsl.item_id = v_item_id
OR ( v_item_id IS NULL
AND rsl.item_id IS NULL
AND rsl.item_description = v_item_desc))
AND poll.approved_flag = 'Y'
AND Nvl(poll.cancel_flag, 'N') = 'N'
AND Nvl(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND poll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
AND poll.line_location_id = rsl.po_line_location_id
AND rsh.asn_type IN ('LCM')
-- bug 2752051
AND rsl.shipment_line_status_code IN ('EXPECTED','PARTIALLY RECEIVED') --<> 'CANCELLED'
AND exists
( select '1'
from po_distributions_all pod
where pod.line_location_id = poll.line_location_id
and (v_project_id is null or
(v_project_id = -9999 and pod.project_id is null) or -- bug2669021
pod.project_id = v_project_id
)
and (v_task_id is null or pod.task_id = v_task_id)
)
ORDER BY expected_receipt_date;
SELECT COUNT(*) FROM
( SELECT 1
FROM rcv_shipment_lines rsl
, rcv_shipment_headers rsh
, po_line_locations poll
, (SELECT DISTINCT source_line_id
FROM wms_lpn_contents
WHERE parent_lpn_id = v_lpn_id) wlc
WHERE rsl.shipment_header_id = rsh.shipment_header_id
AND rsl.shipment_header_id = v_shipment_header_id
AND rsl.shipment_line_id = Nvl(v_shipment_line_id, rsl.shipment_line_id)
AND poll.po_header_id = Nvl(v_po_header_id, poll.po_header_id)
-- Bug 3213241
--AND poll.po_line_id = wlc.source_line_id (+)
AND poll.po_line_id = wlc.source_line_id (+)
AND v_wms_po_j_or_higher = 'N'
-- change to receive non-item master lines
--AND rsl.item_id = v_item_id
AND ( rsl.item_id = v_item_id
OR ( v_item_id IS NULL
AND rsl.item_id IS NULL
AND rsl.item_description = v_item_desc))
AND poll.approved_flag = 'Y'
AND Nvl(poll.cancel_flag, 'N') = 'N'
AND Nvl(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND poll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
AND poll.line_location_id = rsl.po_line_location_id
AND rsh.asn_type IN ('LCM')
-- bug 2752051
AND rsl.shipment_line_status_code IN ('EXPECTED','PARTIALLY RECEIVED') --<> 'CANCELLED'
AND exists
( select '1'
from po_distributions_all pod
where pod.line_location_id = poll.line_location_id
and (v_project_id is null or
(v_project_id = -9999 and pod.project_id is null) or -- bug2669021
pod.project_id = v_project_id
)
and (v_task_id is null or pod.task_id = v_task_id)
)
UNION ALL
SELECT 1
FROM rcv_shipment_lines rsl
, rcv_shipment_headers rsh
, po_line_locations poll
WHERE rsl.shipment_header_id = rsh.shipment_header_id
AND rsl.shipment_header_id = v_shipment_header_id
AND rsl.shipment_line_id = Nvl(v_shipment_line_id, rsl.shipment_line_id)
AND poll.po_header_id = Nvl(v_po_header_id, poll.po_header_id)
-- Bug 3213241
AND v_wms_po_j_or_higher = 'Y'
-- change to receive non-item master lines
--AND rsl.item_id = v_item_id
AND ( rsl.item_id = v_item_id
OR ( v_item_id IS NULL
AND rsl.item_id IS NULL
AND rsl.item_description = v_item_desc))
AND poll.approved_flag = 'Y'
AND Nvl(poll.cancel_flag, 'N') = 'N'
AND Nvl(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND poll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
AND poll.line_location_id = rsl.po_line_location_id
AND rsh.asn_type IN ('LCM')
-- bug 2752051
AND rsl.shipment_line_status_code IN ('EXPECTED','PARTIALLY RECEIVED') --<> 'CANCELLED'
AND exists
( select '1'
from po_distributions_all pod
where pod.line_location_id = poll.line_location_id
and (v_project_id is null or
(v_project_id = -9999 and pod.project_id is null) or -- bug2669021
pod.project_id = v_project_id
)
and (v_task_id is null or pod.task_id = v_task_id)
) );
SELECT rsl.shipment_line_id
, rsl.unit_of_measure
, rsl.item_id
, Decode(rsl.source_document_code,'INVENTORY','INVENTORY','REQ','INTERNAL ORDER')
, rsl.to_organization_id
, 0 -- rcv_transaction_id
, To_number(NULL)
, To_number(NULL)
, To_number(NULL)
, To_number(NULL)
, To_number(NULL)
, rsl.item_description
, Nvl(rsh.expected_receipt_date, Sysdate) expected_receipt_date
FROM rcv_shipment_lines rsl
, rcv_shipment_headers rsh
, mtl_supply ms
WHERE rsl.shipment_header_id = v_shipment_header_id
AND rsh.shipment_header_id = rsl.shipment_header_id
AND rsl.shipment_line_id = Nvl(v_shipment_line_id, rsl.shipment_line_id)
AND rsl.item_id = v_item_id
AND rsh.receipt_source_code <> 'VENDOR'
AND ms.supply_type_code(+) = 'SHIPMENT'
AND ms.shipment_header_id = rsh.shipment_header_id
and ms.shipment_line_id = rsl.shipment_line_id
AND rsl.to_organization_id = v_org_id
AND (((rsl.asn_lpn_id IS NOT NULL
AND rsl.asn_lpn_id = v_lpn_id
)
)
OR (rsl.asn_lpn_id IS NULL)
OR (v_lpn_id IS NULL)
)--bug 4608033: Should join on rsl.asn_lpn_id so that
--the correct rsl can be picked up
AND Nvl(rsh.shipped_date,Nvl(v_txn_date-1,Sysdate-1)) < Nvl(v_txn_date,Sysdate)
AND ( (
rsl.source_document_code = 'REQ'
and exists
( select '1'
from po_req_distributions_all prd
where rsl.requisition_line_id = prd.requisition_line_id
and Nvl(rsl.req_distribution_id,prd.distribution_id) = prd.distribution_id--BUG4930681
and (v_project_id is null or
(v_project_id = -9999 and prd.project_id is null) or -- bug2669021
prd.project_id = v_project_id
)
and (v_task_id is null or prd.task_id = v_task_id)
)
)
or rsl.source_document_code <> 'REQ'
)
-- 12546176 added condition for requistion
AND ( (
(
rsl.source_document_code = 'REQ'
and exists
( select '1'
from po_requisition_lines prl
where prl.requisition_line_id = rsl.requisition_line_id
AND prl.requisition_header_id = v_req_header_id
)
or v_req_header_id is null
)
)
or rsl.source_document_code <> 'REQ'
)
-- 12546176 added condition for requistion
AND ( --9229228-Added this AND condition for lot.
v_lot_number IS NULL
-- 12814511 Added OR condition in cursor for plain item in source org
OR ( exists (SELECT 1 FROM mtl_system_items
WHERE inventory_item_id = v_item_id
AND LOT_CONTROL_CODE = 1
AND ORGANIZATION_ID = rsl.FROM_ORGANIZATION_ID )
OR EXISTS (SELECT lot_num
FROM rcv_lots_supply rls
WHERE rls.shipment_line_id = rsl.shipment_line_id
AND rls.lot_num = v_lot_number
AND rls.supply_type_code = 'SHIPMENT')
)
)
AND ( --9651496,9764650-Added this AND condition for serial.
v_serial_number IS NULL
OR
EXISTS
(
SELECT serial_num
FROM rcv_serials_supply rss
WHERE rss.shipment_line_id = rsl.shipment_line_id
AND rss.serial_num = v_serial_number
AND rss.supply_type_code = 'SHIPMENT'
)
)
ORDER BY Nvl(rsh.expected_receipt_date,Sysdate), Decode(unit_of_measure,v_uom_code,0,1),rsl.shipment_line_id; --Bug 8374257 and 14776842
SELECT COUNT(*)
FROM rcv_shipment_lines rsl
, rcv_shipment_headers rsh
, mtl_supply ms
WHERE rsl.shipment_header_id = v_shipment_header_id
AND rsh.shipment_header_id = rsl.shipment_header_id
AND rsl.shipment_line_id = Nvl(v_shipment_line_id, rsl.shipment_line_id)
AND rsl.item_id = v_item_id
AND rsh.receipt_source_code <> 'VENDOR'
AND ms.supply_type_code(+) = 'SHIPMENT'
AND ms.shipment_header_id = rsh.shipment_header_id
and ms.shipment_line_id = rsl.shipment_line_id
AND rsl.to_organization_id = v_org_id
AND Nvl(rsh.shipped_date,Nvl(v_txn_date-1,Sysdate-1)) < Nvl(v_txn_date,Sysdate)
AND (((rsl.asn_lpn_id IS NOT NULL
AND rsl.asn_lpn_id = v_lpn_id
)
)
OR (rsl.asn_lpn_id IS NULL)
OR (v_lpn_id IS NULL)
)--bug 4608033: Should join on rsl.asn_lpn_id so that
--the correct rsl can be picked up
AND ( (
rsl.source_document_code = 'REQ'
and exists
( select '1'
from po_req_distributions_all prd
where rsl.requisition_line_id = prd.requisition_line_id
and Nvl(rsl.req_distribution_id,prd.distribution_id) = prd.distribution_id--BUG4930681
and (v_project_id is null or
(v_project_id = -9999 and prd.project_id is null) or -- bug2669021
prd.project_id = v_project_id
)
and (v_task_id is null or prd.task_id = v_task_id)
)
)
or rsl.source_document_code <> 'REQ'
)
-- 12546176 added condition for requistion
AND ( (
(rsl.source_document_code = 'REQ'
and exists
( select '1'
from po_requisition_lines prl
where prl.requisition_line_id = rsl.requisition_line_id
AND prl.requisition_header_id = v_req_header_id
)
or v_req_header_id is null
)
)
or rsl.source_document_code <> 'REQ'
)
-- 12546176 added condition for requistion
AND ( --9229228-Added this AND condition for lot.
v_lot_number IS NULL
-- 12814511 Added OR condition in the count cursor for plain item in source org
OR ( exists (SELECT 1 FROM mtl_system_items
WHERE inventory_item_id = v_item_id
AND LOT_CONTROL_CODE = 1
AND ORGANIZATION_ID = rsl.FROM_ORGANIZATION_ID )
OR EXISTS (SELECT lot_num
FROM rcv_lots_supply rls
WHERE rls.shipment_line_id = rsl.shipment_line_id
AND rls.lot_num = v_lot_number
AND rls.supply_type_code = 'SHIPMENT')
)
)
AND ( --9651496,9764650-Added this AND condition for serial.
v_serial_number IS NULL
OR
EXISTS
(
SELECT serial_num
FROM rcv_serials_supply rss
WHERE rss.shipment_line_id = rsl.shipment_line_id
AND rss.serial_num = v_serial_number
AND rss.supply_type_code = 'SHIPMENT'
)
);
SELECT To_number(NULL)
,oel.order_quantity_uom --bug3592116-- v_primary_uom unit_of_measure -- the view was selecting the primary uom so just selected the same FROM the value passed TO avoid one more join.
, oel.inventory_item_id item_id
, 'CUSTOMER'
, Nvl(oel.ship_from_org_id, oeh.ship_from_org_id) to_organization_id
, 0 -- rcv_transaction_id
, oel.line_id oe_order_line_id
, To_number(NULL)
, To_number(NULL)
, To_number(NULL)
, To_number(NULL)
, To_char(NULL)
, To_char(NULL)
FROM oe_order_lines_all oel
, oe_order_headers_all oeh
--, wf_item_activity_statuses wf
--, wf_process_activities wpa
WHERE oel.header_id = oeh.header_id
AND oel.header_id = v_oe_order_header_id
AND oel.line_id = Nvl(v_oe_order_line_id, oel.line_id)
AND oel.inventory_item_id = v_item_id
AND oel.line_category_code = 'RETURN'
AND oel.booked_flag = 'Y'
AND oel.ordered_quantity > Nvl(oel.shipped_quantity,0)
-- Date tolerance fix.
AND (Trunc(Sysdate) >=
Nvl(Trunc(oel.earliest_acceptable_date),Trunc(Sysdate)))
AND (Trunc(Sysdate) <=
Nvl(Trunc(oel.latest_acceptable_date),Trunc(Sysdate)))
AND OEL.FLOW_STATUS_CODE = 'AWAITING_RETURN'
-- performance fix
--AND wpa.activity_item_type = 'OEOL'
--AND wpa.activity_name = 'RMA_WAIT_FOR_RECEIVING'
--AND wf.item_type = 'OEOL'
--AND wf.process_activity = wpa.instance_id
--AND wf.activity_status = 'NOTIFIED'
--AND oel.line_id = To_number(wf.item_key)
AND ( v_project_id is null
or (v_project_id = -9999 and oel.project_id is null) -- bug2669021
or (oel.project_id = v_project_id)
)
AND (v_task_id is null or oel.task_id= v_task_id)
AND Nvl(oel.ship_from_org_id, oeh.ship_from_org_id) = v_org_id /*added for bug 3578489*/
ORDER BY Nvl(Nvl(oel.promise_date,oel.request_date),Sysdate),Decode(ORDER_QUANTITY_UOM,v_uom_code,0,1); --14776842
SELECT COUNT(*)
FROM oe_order_lines_all oel
, oe_order_headers_all oeh
--, wf_item_activity_statuses wf
--, wf_process_activities wpa
WHERE oel.header_id = oeh.header_id
AND oel.header_id = v_oe_order_header_id
AND oel.line_id = Nvl(v_oe_order_line_id, oel.line_id)
AND oel.inventory_item_id = v_item_id
AND oel.line_category_code = 'RETURN'
AND oel.booked_flag = 'Y'
AND oel.ordered_quantity > Nvl(oel.shipped_quantity,0)
-- Date tolerance fix.
AND (Trunc(Sysdate) >=
Nvl(Trunc(oel.earliest_acceptable_date),Trunc(Sysdate)))
AND (Trunc(Sysdate) <=
Nvl(Trunc(oel.latest_acceptable_date),Trunc(Sysdate)))
AND OEL.FLOW_STATUS_CODE = 'AWAITING_RETURN'
-- performance fix
--AND wpa.activity_item_type = 'OEOL'
--AND wpa.activity_name = 'RMA_WAIT_FOR_RECEIVING'
--AND wf.item_type = 'OEOL'
--AND wf.process_activity = wpa.instance_id
--AND wf.activity_status = 'NOTIFIED'
--AND oel.line_id = To_number(wf.item_key)
AND ( v_project_id is null
or (v_project_id = -9999 and oel.project_id is null) -- bug2669021
or (oel.project_id = v_project_id)
)
AND (v_task_id is null or oel.task_id= v_task_id)
AND Nvl(oel.ship_from_org_id, oeh.ship_from_org_id) = v_org_id; --added for bug 3578489
SELECT rsup.shipment_line_id
, rt.unit_of_measure
-- Dont know if it is really correct, should be
-- rsl.unit_of_measure but this actually IS
-- rt.unit_of_measure....depends ON the transaction processor
-- ON what it puts FOR this column.
, rsup.item_id
, rsh.receipt_source_code
, rsup.to_organization_id
, rsup.rcv_transaction_id
, To_number(NULL) oe_order_line_id
, To_number(NULL) po_header_id
, To_number(NULL) po_line_id
, To_number(NULL) po_line_location_id
, To_number(NULL) po_distribution_id
, rsl.item_description item_description
, Nvl(rsh.expected_receipt_date, Sysdate) expected_receipt_date
FROM rcv_supply rsup
, rcv_transactions rt
, rcv_shipment_headers rsh
, rcv_shipment_lines rsl
WHERE rsl.shipment_header_id = v_shipment_header_id
AND rsl.shipment_line_id = Nvl(v_shipment_line_id,rsl.shipment_line_id)
AND rsup.item_id = v_item_id
AND Nvl(rsh.receipt_num,'@@@') = Nvl(v_receipt_num, Nvl(rsh.receipt_num,'@@@'))
AND rt.transaction_date < Nvl(v_txn_date, (rt.transaction_date +1))
AND (RT.ROUTING_HEADER_ID IS NULL OR
RT.ROUTING_HEADER_ID <> 2 OR
(rt.routing_header_id = 2
AND rt.inspection_status_code <> 'NOT INSPECTED'
AND rt.inspection_status_code = Nvl(v_inspection_status,rt.inspection_status_code)))
--(RT.ROUTING_HEADER_ID = 2 AND rt.inspection_status_code <> 'NOT INSPECTED'))
AND RSUP.SUPPLY_TYPE_CODE = 'RECEIVING'
AND RSL.SHIPMENT_LINE_ID = RSUP.SHIPMENT_LINE_ID
AND RT.TRANSACTION_ID = RSUP.RCV_TRANSACTION_ID
AND RT.TRANSACTION_TYPE <> 'UNORDERED'
-- for all the transactions in rt for which we can putaway, the
-- transfer_lpn_id should match the lpn being putaway.
--AND Nvl(rt.transfer_lpn_id,-1) = Nvl(v_lpn_id,-1)
-- Fix for 1865886. Commented the above and added the following for lpn
AND Nvl(v_lpn_id,-1) IN (select nvl(rt2.transfer_lpn_id,-1)
from rcv_transactions rt2
where rt2.transaction_type <> 'DELIVER'
start with rt2.transaction_id = rsup.supply_source_id
connect by prior rt2.transaction_id = rt2.parent_transaction_id
union all
select nvl(rt2.lpn_id,-1)
from rcv_transactions rt2
/* Changes made for bug #4926987 -- added ACCEPT and REJECT */
where rt2.transaction_type not in ('RECEIVE', 'DELIVER','ACCEPT','REJECT')
/* End of changes made for bug #4926987 -- added ACCEPT and REJECT */
start with rt2.transaction_id = rsup.supply_source_id
connect by prior rt2.transaction_id = rt2.parent_transaction_id
)
AND RSUP.to_organization_id = v_org_id
AND RSH.SHIPMENT_HEADER_ID = RSUP.SHIPMENT_HEADER_ID
AND (
v_lot_number IS NULL
OR(
exists (SELECT 1 FROM mtl_system_items
WHERE inventory_item_id = v_item_id
AND LOT_CONTROL_CODE = 1
AND ORGANIZATION_ID = rsup.FROM_ORGANIZATION_ID )
OR EXISTS
(
--Bug 13400589 Modified the below condition which will handle the case for non wms orgs.
SELECT lot_num
FROM rcv_lots_supply rls
WHERE ( rls.transaction_id = rsup.supply_source_id
OR (rls.transaction_id IS NULL
AND rls.shipment_Line_Id=rsup.shipment_line_id))
AND rls.lot_num = v_lot_number
))
)
AND (
v_parent_txn_id_to_match IS NULL
OR v_parent_txn_id_to_match = rsup.supply_source_id
)
AND (
v_lpn_id_to_match IS NULL
OR (rsup.lpn_id = v_lpn_id_to_match)
)
ORDER BY rt.transaction_date;
SELECT COUNT(*)
FROM rcv_supply rsup
, rcv_transactions rt
, rcv_shipment_headers rsh
, rcv_shipment_lines rsl
WHERE rsl.shipment_header_id = v_shipment_header_id
AND rsl.shipment_line_id = Nvl(v_shipment_line_id,rsl.shipment_line_id)
AND rsup.item_id = v_item_id
AND Nvl(rsh.receipt_num,'@@@') = Nvl(v_receipt_num, Nvl(rsh.receipt_num,'@@@'))
AND rt.transaction_date < Nvl(v_txn_date, (rt.transaction_date +1))
AND (RT.ROUTING_HEADER_ID IS NULL OR
RT.ROUTING_HEADER_ID <> 2 OR
(rt.routing_header_id = 2
AND rt.inspection_status_code <> 'NOT INSPECTED'
AND rt.inspection_status_code = Nvl(v_inspection_status,rt.inspection_status_code)))
--(RT.ROUTING_HEADER_ID = 2 AND rt.inspection_status_code <> 'NOT INSPECTED'))
AND RSUP.SUPPLY_TYPE_CODE = 'RECEIVING'
AND RSL.SHIPMENT_LINE_ID = RSUP.SHIPMENT_LINE_ID
AND RT.TRANSACTION_ID = RSUP.RCV_TRANSACTION_ID
AND RT.TRANSACTION_TYPE <> 'UNORDERED'
-- for all the transactions in rt for which we can putaway, the
-- transfer_lpn_id should match the lpn being putaway.
--AND Nvl(rt.transfer_lpn_id,-1) = Nvl(v_lpn_id,-1)
-- Fix for 1865886. Commented the above and added the following for lpn
AND Nvl(v_lpn_id,-1) IN (select nvl(rt2.transfer_lpn_id,-1)
from rcv_transactions rt2
where rt2.transaction_type <> 'DELIVER'
start with rt2.transaction_id = rsup.supply_source_id
connect by prior transaction_id = parent_transaction_id
union all
select nvl(lpn_id,-1)
from rcv_transactions
/* Changes made for bug #4926987 -- added ACCEPT and REJECT */
where transaction_type not in ('RECEIVE', 'DELIVER', 'ACCEPT', 'REJECT')
/* End Changes made for bug #4926987 -- added ACCEPT and REJECT */
start with transaction_id = rsup.supply_source_id
connect by prior transaction_id = parent_transaction_id
)
AND RSUP.to_organization_id = v_org_id
AND RSH.SHIPMENT_HEADER_ID = RSUP.shipment_header_id
AND (
v_lot_number IS NULL
OR(
exists (SELECT 1 FROM mtl_system_items
WHERE inventory_item_id = v_item_id
AND LOT_CONTROL_CODE = 1
AND ORGANIZATION_ID = rsup.FROM_ORGANIZATION_ID )
OR EXISTS
(
--Bug 13400589 Modified the below condition which will handle the case for non wms orgs.
SELECT lot_num
FROM rcv_lots_supply rls
WHERE ( rls.transaction_id = rsup.supply_source_id
OR (rls.transaction_id IS NULL
AND rls.shipment_Line_Id=rsup.shipment_line_id))
AND rls.lot_num = v_lot_number
)
)
)
AND (
v_parent_txn_id_to_match IS NULL
OR v_parent_txn_id_to_match = rsup.supply_source_id
)
AND (
v_lpn_id_to_match IS NULL
OR (rsup.lpn_id = v_lpn_id_to_match)
) ;
SELECT To_number(NULL)
, rt.unit_of_measure
-- Dont know if it is really correct, should be
-- rsl.unit_of_measure but this actually IS
-- rt.unit_of_measure....depends ON the transaction processor
-- ON what it puts FOR this column.
, rsup.item_id
, rsh.receipt_source_code
, rsup.to_organization_id
, rsup.rcv_transaction_id
, rsup.oe_order_line_id
, To_number(NULL)
, To_number(NULL)
, To_number(NULL)
, To_number(NULL)
, To_char(NULL)
, To_char(NULL)
FROM rcv_supply rsup
, rcv_transactions rt
, rcv_shipment_headers rsh
WHERE rsh.receipt_source_code = 'CUSTOMER'
AND rsup.item_id = v_item_id
AND rsup.oe_order_header_id = v_oe_order_header_id
AND rsup.oe_order_line_id = Nvl(v_oe_order_line_id,Nvl(rsup.oe_order_line_id,-1))
AND Nvl(rsh.receipt_num,'@@@') = Nvl(v_receipt_num, Nvl(rsh.receipt_num,'@@@'))
AND rt.transaction_date < Nvl(v_txn_date, (rt.transaction_date + 1))
AND (RT.ROUTING_HEADER_ID IS NULL OR
RT.ROUTING_HEADER_ID <> 2 OR
(rt.routing_header_id = 2
AND rt.inspection_status_code <> 'NOT INSPECTED'
AND rt.inspection_status_code = Nvl(v_inspection_status,rt.inspection_status_code)))
--(RT.ROUTING_HEADER_ID = 2 AND rt.inspection_status_code <> 'NOT INSPECTED'))
AND RSUP.SUPPLY_TYPE_CODE = 'RECEIVING'
AND RT.TRANSACTION_ID = RSUP.RCV_TRANSACTION_ID
AND RT.TRANSACTION_TYPE <> 'UNORDERED'
-- for all the transactions in rt for which we can putaway, the
-- transfer_lpn_id should match the lpn being putaway.
-- AND Nvl(rt.transfer_lpn_id,-1) = Nvl(v_lpn_id,-1)
-- Fix for 1865886. Commented the above and added the following for lpn
AND Nvl(v_lpn_id,-1) IN (select nvl(rt2.transfer_lpn_id,-1)
from rcv_transactions rt2
where rt2.transaction_type <> 'DELIVER'
start with rt2.transaction_id = rsup.supply_source_id
connect by prior transaction_id = parent_transaction_id
union all
select nvl(lpn_id,-1)
from rcv_transactions
where transaction_type not in ('RECEIVE', 'DELIVER')
start with transaction_id = rsup.supply_source_id
connect by prior transaction_id = parent_transaction_id
)
AND RSUP.to_organization_id = v_org_id
AND RSH.SHIPMENT_HEADER_ID = RSUP.shipment_header_id
AND (
v_lot_number IS NULL OR EXISTS
(
SELECT lot_num
FROM rcv_lots_supply rls
WHERE rls.transaction_id = rsup.supply_source_id
AND rls.lot_num = v_lot_number
)
)
AND (
v_parent_txn_id_to_match IS NULL
OR v_parent_txn_id_to_match = rsup.supply_source_id
)
AND (
v_lpn_id_to_match IS NULL
OR (rsup.lpn_id = v_lpn_id_to_match)
)
ORDER BY rt.transaction_date;
SELECT COUNT(*)
FROM rcv_supply rsup
, rcv_transactions rt
, rcv_shipment_headers rsh
WHERE rsh.receipt_source_code = 'CUSTOMER'
AND rsup.item_id = v_item_id
AND rsup.oe_order_header_id = v_oe_order_header_id
AND rsup.oe_order_line_id = Nvl(v_oe_order_line_id,Nvl(rsup.oe_order_line_id,-1))
AND Nvl(rsh.receipt_num,'@@@') = Nvl(v_receipt_num, Nvl(rsh.receipt_num,'@@@'))
AND rt.transaction_date < Nvl(v_txn_date, (rt.transaction_date + 1))
AND (RT.ROUTING_HEADER_ID IS NULL OR
RT.ROUTING_HEADER_ID <> 2 OR
(rt.routing_header_id = 2
AND rt.inspection_status_code <> 'NOT INSPECTED'
AND rt.inspection_status_code = Nvl(v_inspection_status,rt.inspection_status_code)))
--(RT.ROUTING_HEADER_ID = 2 AND rt.inspection_status_code <> 'NOT INSPECTED'))
AND RSUP.SUPPLY_TYPE_CODE = 'RECEIVING'
AND RT.TRANSACTION_ID = RSUP.RCV_TRANSACTION_ID
AND RT.TRANSACTION_TYPE <> 'UNORDERED'
-- for all the transactions in rt for which we can putaway, the
-- transfer_lpn_id should match the lpn being putaway.
--AND Nvl(rt.transfer_lpn_id,-1) = Nvl(v_lpn_id,-1)
-- Fix for 1865886. Commented the above and added the following for lpn
AND Nvl(v_lpn_id,-1) IN (select nvl(rt2.transfer_lpn_id,-1)
from rcv_transactions rt2
where rt2.transaction_type <> 'DELIVER'
start with rt2.transaction_id = rsup.supply_source_id
connect by prior transaction_id = parent_transaction_id
union all
select nvl(lpn_id,-1)
from rcv_transactions
where transaction_type not in ('RECEIVE', 'DELIVER')
start with transaction_id = rsup.supply_source_id
connect by prior transaction_id = parent_transaction_id
)
AND RSUP.to_organization_id = v_org_id
AND RSH.SHIPMENT_HEADER_ID = RSUP.shipment_header_id
AND (
v_lot_number IS NULL OR EXISTS
(
SELECT lot_num
FROM rcv_lots_supply rls
WHERE rls.transaction_id = rsup.supply_source_id
AND rls.lot_num = v_lot_number
)
)
AND (
v_parent_txn_id_to_match IS NULL
OR v_parent_txn_id_to_match = rsup.supply_source_id
)
AND (
v_lpn_id_to_match IS NULL
OR (rsup.lpn_id = v_lpn_id_to_match)
) ;
SELECT rsup.shipment_line_id
, rt.unit_of_measure
-- Dont know if it is really correct, should be
-- rsl.unit_of_measure but this actually IS
-- rt.unit_of_measure....depends ON the transaction processor
-- ON what it puts FOR this column.
, rsup.item_id
, rsh.receipt_source_code
, rsup.to_organization_id
, rsup.rcv_transaction_id
, To_number(NULL) oe_order_line_id
, rsup.po_header_id po_header_id
, rsup.po_line_id po_line_id
, rsup.po_line_location_id po_line_location_id
, pod.po_distribution_id po_distribution_id
, rsl.item_description item_description
, to_char(null)
FROM rcv_supply rsup
, rcv_transactions rt
, rcv_shipment_headers rsh
, rcv_shipment_lines rsl
, po_line_locations poll
, po_distributions pod
WHERE rsl.shipment_header_id = v_shipment_header_id
AND rsl.shipment_line_id = Nvl(v_shipment_line_id,rsl.shipment_line_id)
AND rsup.item_id = v_item_id
AND rsup.po_header_id = pod.po_header_id
AND rsup.PO_LINE_ID = pod.PO_LINE_ID
AND rsup.PO_LINE_LOCATION_ID = pod.LINE_LOCATION_ID
AND rsup.po_header_id = poll.po_header_id
AND rsup.PO_LINE_ID = poll.PO_LINE_ID
AND rsup.PO_LINE_LOCATION_ID = poll.LINE_LOCATION_ID
and NVL(poll.APPROVED_FLAG,'N') = 'Y'
and NVL(poll.CANCEL_FLAG, 'N') = 'N'
and NVL(poll.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED'
and poll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
AND rt.transaction_date < Nvl(v_txn_date, (rt.transaction_date +1))
AND (RT.ROUTING_HEADER_ID IS NULL OR
RT.ROUTING_HEADER_ID <> 2 OR
(rt.routing_header_id = 2
AND rt.inspection_status_code <> 'NOT INSPECTED'
AND rt.inspection_status_code = Nvl(v_inspection_status,rt.inspection_status_code)))
--(RT.ROUTING_HEADER_ID = 2 AND rt.inspection_status_code <> 'NOT INSPECTED'))
AND RSUP.SUPPLY_TYPE_CODE = 'RECEIVING'
AND RSL.SHIPMENT_LINE_ID = RSUP.SHIPMENT_LINE_ID
AND RT.TRANSACTION_ID = RSUP.RCV_TRANSACTION_ID
AND RT.TRANSACTION_TYPE <> 'UNORDERED'
-- for all the transactions in rt for which we can putaway, the
-- transfer_lpn_id should match the lpn being putaway.
--AND Nvl(rt.transfer_lpn_id,-1) = Nvl(v_lpn_id,-1)
-- Fix for 1865886. Commented the above and added the following for lpn
AND Nvl(v_lpn_id,-1) IN (select nvl(rt2.transfer_lpn_id,-1)
from rcv_transactions rt2
where rt2.transaction_type <> 'DELIVER'
start with rt2.transaction_id = rsup.supply_source_id
connect by prior rt2.transaction_id = rt2.parent_transaction_id
union all
select nvl(rt2.lpn_id,-1)
from rcv_transactions rt2
where rt2.transaction_type not in ('RECEIVE', 'DELIVER')
start with rt2.transaction_id = rsup.supply_source_id
connect by prior rt2.transaction_id = rt2.parent_transaction_id
)
AND RSUP.to_organization_id = v_org_id
AND RSH.SHIPMENT_HEADER_ID = RSUP.SHIPMENT_HEADER_ID
AND RT.TRANSACTION_ID = NVL(v_parent_transaction_id, rt.transaction_id) -- 9879753
AND (
v_lot_number IS NULL OR EXISTS
(
SELECT lot_num
FROM rcv_lots_supply rls
WHERE rls.transaction_id = rsup.supply_source_id
AND rls.lot_num = v_lot_number
)
)
AND (
v_lpn_id_to_match IS NULL
OR (rsup.lpn_id = v_lpn_id_to_match)
)
ORDER BY rt.transaction_date,
rsup.rcv_transaction_id; -- Added for bug# 8931640
SELECT COUNT(*)
FROM rcv_supply rsup
, rcv_transactions rt
, rcv_shipment_headers rsh
, rcv_shipment_lines rsl
, po_line_locations poll
, po_distributions pod
WHERE rsl.shipment_header_id = v_shipment_header_id
AND rsl.shipment_line_id = Nvl(v_shipment_line_id,rsl.shipment_line_id)
AND rsup.item_id = v_item_id
AND rsup.po_header_id = pod.po_header_id
AND rsup.PO_LINE_ID = pod.PO_LINE_ID
AND rsup.PO_LINE_LOCATION_ID = pod.LINE_LOCATION_ID
AND rsup.po_header_id = poll.po_header_id
AND rsup.PO_LINE_ID = poll.PO_LINE_ID
AND rsup.PO_LINE_LOCATION_ID = poll.LINE_LOCATION_ID
and NVL(poll.APPROVED_FLAG,'N') = 'Y'
and NVL(poll.CANCEL_FLAG, 'N') = 'N'
and NVL(poll.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED'
and poll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
AND rt.transaction_date < Nvl(v_txn_date, (rt.transaction_date +1))
AND (RT.ROUTING_HEADER_ID IS NULL OR
RT.ROUTING_HEADER_ID <> 2 OR
(rt.routing_header_id = 2
AND rt.inspection_status_code <> 'NOT INSPECTED'
AND rt.inspection_status_code = Nvl(v_inspection_status,rt.inspection_status_code)))
--(RT.ROUTING_HEADER_ID = 2 AND rt.inspection_status_code <> 'NOT INSPECTED'))
AND RSUP.SUPPLY_TYPE_CODE = 'RECEIVING'
AND RSL.SHIPMENT_LINE_ID = RSUP.SHIPMENT_LINE_ID
AND RT.TRANSACTION_ID = RSUP.RCV_TRANSACTION_ID
AND RT.TRANSACTION_TYPE <> 'UNORDERED'
-- for all the transactions in rt for which we can putaway, the
-- transfer_lpn_id should match the lpn being putaway.
--AND Nvl(rt.transfer_lpn_id,-1) = Nvl(v_lpn_id,-1)
-- Fix for 1865886. Commented the above and added the following for lpn
AND Nvl(v_lpn_id,-1) IN (select nvl(rt2.transfer_lpn_id,-1)
from rcv_transactions rt2
where rt2.transaction_type <> 'DELIVER'
start with rt2.transaction_id = rsup.supply_source_id
connect by prior rt2.transaction_id = rt2.parent_transaction_id
union all
select nvl(rt2.lpn_id,-1)
from rcv_transactions rt2
where rt2.transaction_type not in ('RECEIVE', 'DELIVER')
start with rt2.transaction_id = rsup.supply_source_id
connect by prior rt2.transaction_id = rt2.parent_transaction_id
)
AND RSUP.to_organization_id = v_org_id
AND RSH.SHIPMENT_HEADER_ID = RSUP.SHIPMENT_HEADER_ID
AND RT.TRANSACTION_ID = NVL(v_parent_transaction_id, rt.transaction_id) -- 9879753
AND (
v_lot_number IS NULL OR EXISTS
(
SELECT lot_num
FROM rcv_lots_supply rls
WHERE rls.transaction_id = rsup.supply_source_id
AND rls.lot_num = v_lot_number
)
)
AND (
v_lpn_id_to_match IS NULL
OR (rsup.lpn_id = v_lpn_id_to_match)
)
ORDER BY rt.transaction_date;
SELECT rsup.shipment_line_id
, rt.unit_of_measure
-- Dont know if it is really correct, should be
-- rsl.unit_of_measure but this actually IS
-- rt.unit_of_measure....depends ON the transaction processor
-- ON what it puts FOR this column.
, rsup.item_id
, rsh.receipt_source_code
, rsup.to_organization_id
, rsup.rcv_transaction_id
, To_number(NULL) oe_order_line_id
, rsup.po_header_id po_header_id
, rsup.po_line_id po_line_id
, rsup.po_line_location_id po_line_location_id
, pod.po_distribution_id po_distribution_id
, rsl.item_description item_description
, to_char(null)
FROM rcv_supply rsup
, rcv_transactions rt
, rcv_shipment_headers rsh
, rcv_shipment_lines rsl
, po_line_locations poll
, po_distributions pod
WHERE rsl.shipment_header_id = v_shipment_header_id
AND rsl.shipment_line_id = Nvl(v_shipment_line_id,rsl.shipment_line_id)
AND rsup.item_id = v_item_id
AND rsup.po_header_id = pod.po_header_id
AND rsup.PO_LINE_ID = pod.PO_LINE_ID
AND rsup.PO_LINE_LOCATION_ID = pod.LINE_LOCATION_ID
AND rsup.po_header_id = poll.po_header_id
AND rsup.PO_LINE_ID = poll.PO_LINE_ID
AND rsup.PO_LINE_LOCATION_ID = poll.LINE_LOCATION_ID
and NVL(poll.APPROVED_FLAG,'N') = 'Y'
and NVL(poll.CANCEL_FLAG, 'N') = 'N'
and NVL(poll.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED'
and poll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
AND rt.transaction_date < Nvl(v_txn_date, (rt.transaction_date +1))
AND (RT.ROUTING_HEADER_ID IS NULL OR
RT.ROUTING_HEADER_ID <> 2 OR
(rt.routing_header_id = 2
AND rt.inspection_status_code <> 'NOT INSPECTED'
AND rt.inspection_status_code = Nvl(v_inspection_status,rt.inspection_status_code)))
--(RT.ROUTING_HEADER_ID = 2 AND rt.inspection_status_code <> 'NOT INSPECTED'))
AND RSUP.SUPPLY_TYPE_CODE = 'RECEIVING'
AND RSL.SHIPMENT_LINE_ID = RSUP.SHIPMENT_LINE_ID
AND RT.TRANSACTION_ID = RSUP.RCV_TRANSACTION_ID
AND RT.TRANSACTION_TYPE <> 'UNORDERED'
-- for all the transactions in rt for which we can putaway, the
-- transfer_lpn_id should match the lpn being putaway.
--AND Nvl(rt.transfer_lpn_id,-1) = Nvl(v_lpn_id,-1)
-- Fix for 1865886. Commented the above and added the following for lpn
AND Nvl(v_lpn_id,-1) IN (select nvl(rt2.transfer_lpn_id,-1)
from rcv_transactions rt2
where rt2.transaction_type <> 'DELIVER'
start with rt2.transaction_id = rsup.supply_source_id
connect by prior rt2.transaction_id = rt2.parent_transaction_id
union all
select nvl(rt2.lpn_id,-1)
from rcv_transactions rt2
where rt2.transaction_type not in ('RECEIVE', 'DELIVER')
start with rt2.transaction_id = rsup.supply_source_id
connect by prior rt2.transaction_id = rt2.parent_transaction_id
)
AND RSUP.to_organization_id = v_org_id
AND RSH.SHIPMENT_HEADER_ID = RSUP.SHIPMENT_HEADER_ID
AND (
v_lot_number IS NULL OR EXISTS
(
SELECT lot_num
FROM rcv_lots_supply rls
WHERE rls.transaction_id = rsup.supply_source_id
AND rls.lot_num = v_lot_number
)
)
AND (
v_lpn_id_to_match IS NULL
OR (rsup.lpn_id = v_lpn_id_to_match)
)
ORDER BY rt.transaction_date;
SELECT COUNT(*)
FROM rcv_supply rsup
, rcv_transactions rt
, rcv_shipment_headers rsh
, rcv_shipment_lines rsl
, po_line_locations poll
, po_distributions pod
WHERE rsl.shipment_header_id = v_shipment_header_id
AND rsl.shipment_line_id = Nvl(v_shipment_line_id,rsl.shipment_line_id)
AND rsup.item_id = v_item_id
AND rsup.po_header_id = pod.po_header_id
AND rsup.PO_LINE_ID = pod.PO_LINE_ID
AND rsup.PO_LINE_LOCATION_ID = pod.LINE_LOCATION_ID
AND rsup.po_header_id = poll.po_header_id
AND rsup.PO_LINE_ID = poll.PO_LINE_ID
AND rsup.PO_LINE_LOCATION_ID = poll.LINE_LOCATION_ID
and NVL(poll.APPROVED_FLAG,'N') = 'Y'
and NVL(poll.CANCEL_FLAG, 'N') = 'N'
and NVL(poll.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED'
and poll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
AND rt.transaction_date < Nvl(v_txn_date, (rt.transaction_date +1))
AND (RT.ROUTING_HEADER_ID IS NULL OR
RT.ROUTING_HEADER_ID <> 2 OR
(rt.routing_header_id = 2
AND rt.inspection_status_code <> 'NOT INSPECTED'
AND rt.inspection_status_code = Nvl(v_inspection_status,rt.inspection_status_code)))
--(RT.ROUTING_HEADER_ID = 2 AND rt.inspection_status_code <> 'NOT INSPECTED'))
AND RSUP.SUPPLY_TYPE_CODE = 'RECEIVING'
AND RSL.SHIPMENT_LINE_ID = RSUP.SHIPMENT_LINE_ID
AND RT.TRANSACTION_ID = RSUP.RCV_TRANSACTION_ID
AND RT.TRANSACTION_TYPE <> 'UNORDERED'
-- for all the transactions in rt for which we can putaway, the
-- transfer_lpn_id should match the lpn being putaway.
--AND Nvl(rt.transfer_lpn_id,-1) = Nvl(v_lpn_id,-1)
-- Fix for 1865886. Commented the above and added the following for lpn
AND Nvl(v_lpn_id,-1) IN (select nvl(rt2.transfer_lpn_id,-1)
from rcv_transactions rt2
where rt2.transaction_type <> 'DELIVER'
start with rt2.transaction_id = rsup.supply_source_id
connect by prior rt2.transaction_id = rt2.parent_transaction_id
union all
select nvl(rt2.lpn_id,-1)
from rcv_transactions rt2
where rt2.transaction_type not in ('RECEIVE', 'DELIVER')
start with rt2.transaction_id = rsup.supply_source_id
connect by prior rt2.transaction_id = rt2.parent_transaction_id
)
AND RSUP.to_organization_id = v_org_id
AND RSH.SHIPMENT_HEADER_ID = RSUP.SHIPMENT_HEADER_ID
AND (
v_lot_number IS NULL OR EXISTS
(
SELECT lot_num
FROM rcv_lots_supply rls
WHERE rls.transaction_id = rsup.supply_source_id
AND rls.lot_num = v_lot_number
)
)
AND (
v_lpn_id_to_match IS NULL
OR (rsup.lpn_id = v_lpn_id_to_match)
)
ORDER BY rt.transaction_date;
insert_into_table BOOLEAN := FALSE;
SELECT uom_code INTO l_rma_uom -- 14776842 In oe_order_lines_all, it only keep uom code
FROM mtl_units_of_measure WHERE unit_of_measure=temp_cascaded_table(current_n).unit_of_measure AND ROWNUM=1;
l_po_out_cascaded_table.DELETE(i);
temp_cascaded_table.DELETE(i);
select ordered_qty
into x_oe_line_qty
from oe_po_enter_receipts_v
where oe_order_line_id =
temp_cascaded_table(l_sh_result_count).oe_order_line_id;
temp_cascaded_table.DELETE(i);
-- Delete the temp_cascaded_table just to be sure
IF temp_cascaded_table.COUNT > 0 THEN
FOR i IN 1..temp_cascaded_table.COUNT LOOP
temp_cascaded_table.DELETE(i);
SELECT primary_unit_of_measure
INTO temp_cascaded_table(current_n).primary_unit_of_measure
FROM mtl_system_items
WHERE mtl_system_items.inventory_item_id =
temp_cascaded_table(current_n).item_id
AND mtl_system_items.organization_id =
temp_cascaded_table(current_n).to_organization_id;
insert_into_table := FALSE;
IF insert_into_table THEN
IF (l_debug = 1) THEN
print_debug('insert_into_table:TRUE',4);
print_debug('insert_into_table:FLASE',4);
SELECT unit_of_measure INTO l_rma_uom
FROM mtl_units_of_measure
WHERE uom_code = x_MatchedRec.unit_of_measure;
SELECT rsl.quantity_shipped, rsl.unit_of_measure
INTO l_rsl_qty, l_rsl_uom
FROM rcv_shipment_lines rsl
WHERE rsl.shipment_line_id = l_parent_id;
temp_cascaded_table.delete(i);
temp_cascaded_table.delete(i);
insert_into_table := TRUE;
insert_into_table := TRUE;
-- last row needs to be inserted anyway
-- so that the row can be used based on qty tolerance
-- checks
insert_into_table := TRUE;
insert_into_table := FALSE;
IF insert_into_table THEN
IF (l_debug = 1) THEN
print_debug('insert_into_table:TRUE',4);
print_debug('insert_into_table:FLASE',4);
insert_into_table := TRUE;
insert_into_table := TRUE;
-- last row needs to be inserted anyway
-- so that the row can be used based on qty tolerance
-- checks
insert_into_table := TRUE;
insert_into_table := FALSE;
IF insert_into_table THEN
IF (l_debug = 1) THEN
print_debug('insert_into_table:TRUE',4);
print_debug('insert_into_table:FLASE',4);
IF insert_into_table THEN
IF (x_first_trans) THEN
x_first_trans := FALSE;