[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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 ('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'
, 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 (((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)
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 ('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 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 ('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)
)
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'
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'
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 ('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)
)
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'
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
, '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'
)
ORDER BY Nvl(rsh.expected_receipt_date,Sysdate);
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'
);
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);
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 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
, 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 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 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 (
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;
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;
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;
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;