[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
pll.line_location_id
, pll.unit_meas_lookup_code
, Nvl(pll.promised_date,pll.need_by_date) promised_date
, pll.quantity_shipped
, pll.receipt_days_exception_code
, pll.qty_rcv_exception_code
, pll.days_early_receipt_allowed
, pll.days_late_receipt_allowed
, 0 po_distribution_id
, pll.ship_to_location_id
, Nvl(pll.enforce_ship_to_location_code,'NONE') enforce_ship_to_location_code
, 0 rcv_transaction_id -- only need it for std_deliver
, pl.item_revision --only needed for std_deliver
FROM
po_line_locations pll,
po_lines pl,
-- Bug 3444226 The Join with po_headers is unnecessary
-- po_headers ph,
rcv_shipment_lines rsl,
(SELECT DISTINCT source_line_id
FROM wms_lpn_contents
WHERE parent_lpn_id = v_lpn_id) wlc
WHERE pll.po_line_id = Nvl(v_po_line_id, pll.po_line_id)
AND pll.line_location_id = Nvl(v_po_line_location_id, pll.line_location_id)
AND Nvl(pll.po_release_id,0) = Nvl(v_po_release_id,Nvl(pll.po_release_id,0))
AND pll.po_line_id = pl.po_line_id
-- change for non item master receipts.
--AND pl.item_id = v_item_id
and ( pl.item_id = v_item_id
OR ( v_item_id IS NULL
AND pl.item_id IS NULL
AND pl.item_description = v_item_desc))
AND NVL(pll.APPROVED_FLAG,'N') = 'Y'
AND NVL(pll.CANCEL_FLAG, 'N') = 'N'
AND NVL(pll.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING','CLOSED')
AND pll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
AND pll.ship_to_organization_id = Nvl(v_ship_to_org_id,pll.ship_to_organization_id)
AND pll.ship_to_location_id = Nvl(v_ship_to_location_id,pll.ship_to_location_id)
AND rsl.shipment_header_id = v_shipment_header_id
AND rsl.po_line_location_id = pll.line_location_id
AND pll.po_line_id = wlc.source_line_id (+)
AND Nvl(pll.receiving_routing_id,1) = Decode(l_allow_routing_override,
'Y',
Nvl(pll.receiving_routing_id,1),
Decode(v_inspection_status_code,
'Y',
2,
1)
)
AND pll.line_location_id in
( select pod.line_location_id from po_distributions pod
where (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)
and pod.po_header_id = pll.po_header_id
)
ORDER BY Nvl(pll.promised_date,pll.need_by_date);
SELECT COUNT(*)
FROM
po_line_locations pll,
po_lines pl,
-- Bug 3444226 The Join with po_headers is unnecessary
-- po_headers ph,
rcv_shipment_lines rsl,
(SELECT DISTINCT source_line_id
FROM wms_lpn_contents
WHERE parent_lpn_id = v_lpn_id) wlc
WHERE pll.po_line_id = Nvl(v_po_line_id, pll.po_line_id)
AND pll.line_location_id = Nvl(v_po_line_location_id, pll.line_location_id)
AND Nvl(pll.po_release_id,0) = Nvl(v_po_release_id,Nvl(pll.po_release_id,0))
AND pll.po_line_id = pl.po_line_id
-- change for non item master receipts.
--AND pl.item_id = v_item_id
and ( pl.item_id = v_item_id
OR ( v_item_id IS NULL
AND pl.item_id IS NULL
AND pl.item_description = v_item_desc))
AND NVL(pll.APPROVED_FLAG,'N') = 'Y'
AND NVL(pll.CANCEL_FLAG, 'N') = 'N'
AND NVL(pll.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING','CLOSED')
AND pll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
AND pll.ship_to_organization_id = Nvl(v_ship_to_org_id,pll.ship_to_organization_id)
AND pll.ship_to_location_id = Nvl(v_ship_to_location_id,pll.ship_to_location_id)
AND rsl.shipment_header_id = v_shipment_header_id
AND rsl.po_line_location_id = pll.line_location_id
AND pll.po_line_id = wlc.source_line_id (+)
AND Nvl(pll.receiving_routing_id,1) = Decode(l_allow_routing_override,
'Y',
Nvl(pll.receiving_routing_id,1),
Decode(v_inspection_status_code,
'Y',
2,
1)
)
AND pll.line_location_id in
( select pod.line_location_id
from po_distributions pod
where ( 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)
and pod.po_header_id = pll.po_header_id
);
SELECT
pll.line_location_id
, pll.unit_meas_lookup_code
, Nvl(pll.promised_date,pll.need_by_date) promised_date
, pll.quantity_shipped
, pll.receipt_days_exception_code
, pll.qty_rcv_exception_code
, pll.days_early_receipt_allowed
, pll.days_late_receipt_allowed
, 0 po_distribution_id
, pll.ship_to_location_id
, Nvl(pll.enforce_ship_to_location_code,'NONE') enforce_ship_to_location_code
, 0 rcv_transaction_id -- only need it for std_deliver
, pl.item_revision --only needed for std_deliver
FROM
po_line_locations pll,
po_lines pl,
-- Bug 3444226 The Join with po_headers is unnecessary
-- po_headers ph,
rcv_shipment_lines rsl,
(SELECT DISTINCT source_line_id
FROM wms_lpn_contents
WHERE parent_lpn_id = v_lpn_id) wlc
-- Bug 3444226 The condition with po_headers is unnecessary
-- WHERE ph.po_header_id = header_id
WHERE pll.po_header_id = header_id
AND pll.po_line_id = Nvl(v_po_line_id, pll.po_line_id)
AND pll.line_location_id = Nvl(v_po_line_location_id, pll.line_location_id)
AND Nvl(pll.po_release_id,0) = Nvl(v_po_release_id,Nvl(pll.po_release_id,0))
AND pll.po_line_id = pl.po_line_id
-- change for non item master receipts.
--AND pl.item_id = v_item_id
and ( pl.item_id = v_item_id
OR ( v_item_id IS NULL
AND pl.item_id IS NULL
AND pl.item_description = v_item_desc))
AND NVL(pll.APPROVED_FLAG,'N') = 'Y'
AND NVL(pll.CANCEL_FLAG, 'N') = 'N'
AND NVL(pll.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING','CLOSED')
AND pll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
AND pll.ship_to_organization_id = Nvl(v_ship_to_org_id,pll.ship_to_organization_id)
AND pll.ship_to_location_id = Nvl(v_ship_to_location_id,pll.ship_to_location_id)
AND rsl.shipment_header_id = v_shipment_header_id
AND rsl.po_line_location_id = pll.line_location_id
AND pll.po_line_id = wlc.source_line_id (+)
AND Nvl(pll.receiving_routing_id,1) = Decode(l_allow_routing_override,
'Y',
Nvl(pll.receiving_routing_id,1),
Decode(v_inspection_status_code,
'Y',
2,
1)
)
AND pll.line_location_id in
( select pod.line_location_id
from po_distributions pod
where ( 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)
and pod.po_header_id = pll.po_header_id
)
ORDER BY Nvl(pll.promised_date,pll.need_by_date);
SELECT COUNT(*)
FROM
po_line_locations pll,
po_lines pl,
-- Bug 3444226 The Join with po_headers is unnecessary
-- po_headers ph,
rcv_shipment_lines rsl,
(SELECT DISTINCT source_line_id
FROM wms_lpn_contents
WHERE parent_lpn_id = v_lpn_id) wlc
-- Bug 3444226 The Join with po_headers is unnecessary
-- WHERE ph.po_header_id = header_id
WHERE pll.po_header_id = header_id
AND pll.po_line_id = Nvl(v_po_line_id, pll.po_line_id)
AND pll.line_location_id = Nvl(v_po_line_location_id, pll.line_location_id)
AND Nvl(pll.po_release_id,0) = Nvl(v_po_release_id,Nvl(pll.po_release_id,0))
AND pll.po_line_id = pl.po_line_id
-- change for non item master receipts.
--AND pl.item_id = v_item_id
and ( pl.item_id = v_item_id
OR ( v_item_id IS NULL
AND pl.item_id IS NULL
AND pl.item_description = v_item_desc))
AND NVL(pll.APPROVED_FLAG,'N') = 'Y'
AND NVL(pll.CANCEL_FLAG, 'N') = 'N'
AND NVL(pll.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING','CLOSED')
AND pll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
AND pll.ship_to_organization_id = Nvl(v_ship_to_org_id,pll.ship_to_organization_id)
AND pll.ship_to_location_id = Nvl(v_ship_to_location_id,pll.ship_to_location_id)
AND rsl.shipment_header_id = v_shipment_header_id
AND rsl.po_line_location_id = pll.line_location_id
AND pll.po_line_id = wlc.source_line_id (+)
AND Nvl(pll.receiving_routing_id,1) = Decode(l_allow_routing_override,
'Y',
Nvl(pll.receiving_routing_id,1),
Decode(v_inspection_status_code,
'Y',
2,
1)
)
AND pll.line_location_id in
( select pod.line_location_id
from po_distributions pod
where ( 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)
and pod.po_header_id = pll.po_header_id
) ;
SELECT
pll.line_location_id
, pll.unit_meas_lookup_code
, Nvl(pll.promised_date,pll.need_by_date) promised_date
, pll.quantity_shipped
, pll.receipt_days_exception_code
, pll.qty_rcv_exception_code
, pll.days_early_receipt_allowed
, pll.days_late_receipt_allowed
, 0 po_distribution_id
, pll.ship_to_location_id
, Nvl(pll.enforce_ship_to_location_code,'NONE') enforce_ship_to_location_code
, 0 rcv_transaction_id -- only need it for std_deliver
, pl.item_revision --only needed for std_deliver
FROM
po_line_locations pll,
po_lines pl,
-- Bug 3444226 The Join with po_headers is unnecessary
-- po_headers ph,
rcv_shipment_lines rsl,
(SELECT DISTINCT source_line_id
FROM wms_lpn_contents
WHERE parent_lpn_id = v_lpn_id) wlc
WHERE pll.po_line_id = Nvl(v_po_line_id, pll.po_line_id)
AND pll.line_location_id = Nvl(v_po_line_location_id, pll.line_location_id)
AND Nvl(pll.po_release_id,0) = Nvl(v_po_release_id,Nvl(pll.po_release_id,0))
AND pll.po_line_id = pl.po_line_id
-- change for non item master receipts.
--AND pl.item_id = v_item_id
and ( pl.item_id = v_item_id
OR ( v_item_id IS NULL
AND pl.item_id IS NULL
AND pl.item_description = v_item_desc))
AND NVL(pll.APPROVED_FLAG,'N') = 'Y'
AND NVL(pll.CANCEL_FLAG, 'N') = 'N'
AND NVL(pll.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING','CLOSED')
AND pll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
AND pll.ship_to_organization_id = Nvl(v_ship_to_org_id,pll.ship_to_organization_id)
AND pll.ship_to_location_id = Nvl(v_ship_to_location_id,pll.ship_to_location_id)
AND rsl.shipment_header_id = v_shipment_header_id
AND rsl.po_line_location_id = pll.line_location_id
AND pll.po_line_id = wlc.source_line_id (+)
AND Nvl(pll.receiving_routing_id,1) = Decode(l_allow_routing_override,
'Y',
Nvl(pll.receiving_routing_id,1),
Decode(v_inspection_status_code,
'Y',
2,
1)
)
AND pll.line_location_id in
( select pod.line_location_id from po_distributions pod
where (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)
and pod.po_header_id = pll.po_header_id
)
ORDER BY Nvl(pll.promised_date,pll.need_by_date);
SELECT COUNT(*)
FROM
po_line_locations pll,
po_lines pl,
-- Bug 3444226 The Join with po_headers is unnecessary
-- po_headers ph,
rcv_shipment_lines rsl,
(SELECT DISTINCT source_line_id
FROM wms_lpn_contents
WHERE parent_lpn_id = v_lpn_id) wlc
WHERE pll.po_line_id = Nvl(v_po_line_id, pll.po_line_id)
AND pll.line_location_id = Nvl(v_po_line_location_id, pll.line_location_id)
AND Nvl(pll.po_release_id,0) = Nvl(v_po_release_id,Nvl(pll.po_release_id,0))
AND pll.po_line_id = pl.po_line_id
-- change for non item master receipts.
--AND pl.item_id = v_item_id
and ( pl.item_id = v_item_id
OR ( v_item_id IS NULL
AND pl.item_id IS NULL
AND pl.item_description = v_item_desc))
AND NVL(pll.APPROVED_FLAG,'N') = 'Y'
AND NVL(pll.CANCEL_FLAG, 'N') = 'N'
AND NVL(pll.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING','CLOSED')
AND pll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
AND pll.ship_to_organization_id = Nvl(v_ship_to_org_id,pll.ship_to_organization_id)
AND pll.ship_to_location_id = Nvl(v_ship_to_location_id,pll.ship_to_location_id)
AND rsl.shipment_header_id = v_shipment_header_id
AND rsl.po_line_location_id = pll.line_location_id
AND pll.po_line_id = wlc.source_line_id (+)
AND Nvl(pll.receiving_routing_id,1) = Decode(l_allow_routing_override,
'Y',
Nvl(pll.receiving_routing_id,1),
Decode(v_inspection_status_code,
'Y',
2,
1)
)
AND pll.line_location_id in
( select pod.line_location_id
from po_distributions pod
where ( 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)
and pod.po_header_id = pll.po_header_id
);
SELECT
pll.line_location_id
, pll.unit_meas_lookup_code
, Nvl(pll.promised_date,pll.need_by_date) promised_date
, pll.quantity_shipped
, pll.receipt_days_exception_code
, pll.qty_rcv_exception_code
, pll.days_early_receipt_allowed
, pll.days_late_receipt_allowed
, 0 po_distribution_id
, pll.ship_to_location_id
, Nvl(pll.enforce_ship_to_location_code,'NONE') enforce_ship_to_location_code
, 0 rcv_transaction_id -- only need it for std_deliver
, pl.item_revision --only needed for std_deliver
FROM
po_line_locations pll,
po_lines pl,
-- Bug 3444226 The Join with po_headers is unnecessary
-- po_headers ph,
rcv_shipment_lines rsl,
(SELECT DISTINCT source_line_id
FROM wms_lpn_contents
WHERE parent_lpn_id = v_lpn_id) wlc
-- Bug 3444226 The condition with po_headers is unnecessary
-- WHERE ph.po_header_id = header_id
WHERE pll.po_header_id = header_id
AND pll.po_line_id = Nvl(v_po_line_id, pll.po_line_id)
AND pll.line_location_id = Nvl(v_po_line_location_id, pll.line_location_id)
AND Nvl(pll.po_release_id,0) = Nvl(v_po_release_id,Nvl(pll.po_release_id,0))
AND pll.po_line_id = pl.po_line_id
-- change for non item master receipts.
--AND pl.item_id = v_item_id
and ( pl.item_id = v_item_id
OR ( v_item_id IS NULL
AND pl.item_id IS NULL
AND pl.item_description = v_item_desc))
AND NVL(pll.APPROVED_FLAG,'N') = 'Y'
AND NVL(pll.CANCEL_FLAG, 'N') = 'N'
AND NVL(pll.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING','CLOSED')
AND pll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
AND pll.ship_to_organization_id = Nvl(v_ship_to_org_id,pll.ship_to_organization_id)
AND pll.ship_to_location_id = Nvl(v_ship_to_location_id,pll.ship_to_location_id)
AND rsl.shipment_header_id = v_shipment_header_id
AND rsl.po_line_location_id = pll.line_location_id
AND pll.po_line_id = wlc.source_line_id (+)
AND Nvl(pll.receiving_routing_id,1) = Decode(l_allow_routing_override,
'Y',
Nvl(pll.receiving_routing_id,1),
Decode(v_inspection_status_code,
'Y',
2,
1)
)
AND pll.line_location_id in
( select pod.line_location_id
from po_distributions pod
where ( 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)
and pod.po_header_id = pll.po_header_id
)
ORDER BY Nvl(pll.promised_date,pll.need_by_date);
SELECT COUNT(*)
FROM
po_line_locations pll,
po_lines pl,
-- Bug 3444226 The Join with po_headers is unnecessary
-- po_headers ph,
rcv_shipment_lines rsl,
(SELECT DISTINCT source_line_id
FROM wms_lpn_contents
WHERE parent_lpn_id = v_lpn_id) wlc
-- Bug 3444226 The Join with po_headers is unnecessary
-- WHERE ph.po_header_id = header_id
WHERE pll.po_header_id = header_id
AND pll.po_line_id = Nvl(v_po_line_id, pll.po_line_id)
AND pll.line_location_id = Nvl(v_po_line_location_id, pll.line_location_id)
AND Nvl(pll.po_release_id,0) = Nvl(v_po_release_id,Nvl(pll.po_release_id,0))
AND pll.po_line_id = pl.po_line_id
-- change for non item master receipts.
--AND pl.item_id = v_item_id
and ( pl.item_id = v_item_id
OR ( v_item_id IS NULL
AND pl.item_id IS NULL
AND pl.item_description = v_item_desc))
AND NVL(pll.APPROVED_FLAG,'N') = 'Y'
AND NVL(pll.CANCEL_FLAG, 'N') = 'N'
AND NVL(pll.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING','CLOSED')
AND pll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
AND pll.ship_to_organization_id = Nvl(v_ship_to_org_id,pll.ship_to_organization_id)
AND pll.ship_to_location_id = Nvl(v_ship_to_location_id,pll.ship_to_location_id)
AND rsl.shipment_header_id = v_shipment_header_id
AND rsl.po_line_location_id = pll.line_location_id
AND pll.po_line_id = wlc.source_line_id (+)
AND Nvl(pll.receiving_routing_id,1) = Decode(l_allow_routing_override,
'Y',
Nvl(pll.receiving_routing_id,1),
Decode(v_inspection_status_code,
'Y',
2,
1)
)
AND pll.line_location_id in
( select pod.line_location_id
from po_distributions pod
where ( 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)
and pod.po_header_id = pll.po_header_id
) ;
select
pll.line_location_id
, pll.unit_meas_lookup_code
, nvl(pll.promised_date,pll.need_by_date) promised_date
, pll.quantity_shipped
, pll.receipt_days_exception_code
, pll.qty_rcv_exception_code
, pll.days_early_receipt_allowed
, pll.days_late_receipt_allowed
, 0 po_distribution_id
, pll.ship_to_location_id
, nvl(pll.enforce_ship_to_location_code,'NONE') enforce_ship_to_location_code
, 0 rcv_transaction_id -- only need it for std_deliver
, pl.item_revision --only needed for std_deliver
from po_line_locations_all pll,
po_lines_all pl
-- For Bug 7440217
, mtl_parameters mp,
rcv_parameters rp
-- End for Bug 7440217
-- Bug 3444226 The Join with po_headers is unnecessary
-- po_headers_all ph
-- where ph.po_header_id = header_id
where pll.po_header_id = header_id
-- For Bug 7440217
AND mp.organization_id = v_organization_id
AND rp.organization_id = v_organization_id
AND ( (NVL(mp.lcm_enabled_flag,'N') = 'N') -- Org is non-lcm enabled
OR (NVL(rp.pre_receive,'N') = 'N') -- Org is lcm enabled and it is post-receiving
OR (NVL(pll.lcm_flag,'N') = 'N') -- Org is lcm enabled, pre-receiving and non-lcm enabled shipment
)
-- End for Bug 7440217
AND pll.po_line_id = Nvl(v_po_line_id, pll.po_line_id)
AND pll.line_location_id = Nvl(v_po_line_location_id, pll.line_location_id)
and nvl(pll.po_release_id,0) = nvl(v_po_release_id,nvl(pll.po_release_id,0))
and pll.po_line_id = pl.po_line_id
-- change for non item master receipts.
and ( pl.item_id = v_item_id
OR ( v_item_id IS NULL
AND pl.item_id IS NULL
AND pl.item_description = v_item_desc))
AND ((v_revision IS NOT NULL
AND Nvl(pl.item_revision, v_revision) = v_revision)
OR (v_revision IS NULL))
and NVL(pll.APPROVED_FLAG,'N') = 'Y'
and NVL(pll.CANCEL_FLAG, 'N') = 'N'
and NVL(pll.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING','CLOSED')
and pll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
and pll.ship_to_organization_id = nvl(v_ship_to_org_id,pll.ship_to_organization_id)
and pll.ship_to_location_id = nvl(v_ship_to_location_id,pll.ship_to_location_id)
AND Nvl(pll.receiving_routing_id,1) = Decode(l_allow_routing_override,
'Y',
Nvl(pll.receiving_routing_id,1),
Decode(v_inspection_status_code,
'Y',
2,
1)
)
AND pll.line_location_id in
( select pod.line_location_id
from po_distributions pod
where (v_project_id is null
or ((v_project_id = -9999 and pod.project_id is null)--Bug# 2669021
or (nvl(pod.project_id,-99) = v_project_id )))
and (v_task_id is null or nvl(pod.task_id,-9999) = v_task_id)
and pod.po_header_id = pll.po_header_id
)
order by pl.item_revision, nvl(pll.promised_date,pll.need_by_date);
SELECT COUNT(*)
from po_line_locations_all pll,
po_lines_all pl
-- For Bug 7440217
, mtl_parameters mp,
rcv_parameters rp
-- End for Bug 7440217
WHERE pll.po_header_id = header_id
-- For Bug 7440217
AND mp.organization_id = v_organization_id
AND rp.organization_id = v_organization_id
AND ( (NVL(mp.lcm_enabled_flag,'N') = 'N') -- Org is non-lcm enabled
OR (NVL(rp.pre_receive,'N') = 'N') -- Org is lcm enabled and it is post-receiving
OR (NVL(pll.lcm_flag,'N') = 'N') -- Org is lcm enabled, pre-receiving and non-lcm enabled shipment
)
-- End for Bug 7440217
AND pll.po_line_id = Nvl(v_po_line_id, pll.po_line_id)
AND pll.line_location_id = Nvl(v_po_line_location_id, pll.line_location_id)
and nvl(pll.po_release_id,0) = nvl(v_po_release_id,nvl(pll.po_release_id,0))
and pll.po_line_id = pl.po_line_id
-- change for non item master receipts.
and ( pl.item_id = v_item_id
OR ( v_item_id IS NULL
AND pl.item_id IS NULL
AND pl.item_description = v_item_desc))
-- and pl.item_id = v_item_id
AND ((v_revision IS NOT NULL
AND Nvl(pl.item_revision, v_revision) = v_revision)
OR (v_revision IS NULL))
and NVL(pll.APPROVED_FLAG,'N') = 'Y'
and NVL(pll.CANCEL_FLAG, 'N') = 'N'
and NVL(pll.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING','CLOSED')
and pll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
and pll.ship_to_organization_id = nvl(v_ship_to_org_id,pll.ship_to_organization_id)
and pll.ship_to_location_id = nvl(v_ship_to_location_id,pll.ship_to_location_id)
AND Nvl(pll.receiving_routing_id,1) = Decode(l_allow_routing_override,
'Y',
Nvl(pll.receiving_routing_id,1),
Decode(v_inspection_status_code,
'Y',
2,
1)
)
AND pll.line_location_id in
( select pod.line_location_id
from po_distributions pod
where (v_project_id is null
or ((v_project_id = -9999 and pod.project_id is null)--Bug# 2669021
or (nvl(pod.project_id,-99) = v_project_id )))
and (v_task_id is null or nvl(pod.task_id,-9999) = v_task_id)
and pod.po_header_id = pll.po_header_id
);
select
pll.line_location_id
,pll.unit_meas_lookup_code
,nvl(pll.promised_date,pll.need_by_date) promised_date
,pll.quantity_shipped
,pll.receipt_days_exception_code
,pll.qty_rcv_exception_code
,pll.days_early_receipt_allowed
,pll.days_late_receipt_allowed
,pod.po_distribution_id
,pll.ship_to_location_id
,nvl(pll.enforce_ship_to_location_code,'NONE') enforce_ship_to_location_code
,0 rcv_transaction_id -- only need it for std_deliver
,pl.item_revision --only needed for std_deliver
from po_distributions pod,
po_line_locations pll,
po_lines pl,
po_headers ph
where ph.po_header_id = header_id
and pod.po_header_id = header_id
AND pod.po_line_id = Nvl(v_po_line_id, pod.po_line_id)
AND pod.line_location_id = Nvl(v_po_line_location_id, pod.line_location_id)
AND pod.po_distribution_id = Nvl(v_po_distribution_id, pod.po_distribution_id)
and ph.po_header_id = pl.po_header_id
and nvl(pll.po_release_id,0) = nvl(v_po_release_id,nvl(pll.po_release_id,0))
and pll.po_line_id = pl.po_line_id
--and pl.item_id = v_item_id
-- change for non item master receipts.
and ( pl.item_id = v_item_id
OR ( v_item_id IS NULL
AND pl.item_id IS NULL
AND pl.item_description = v_item_desc))
AND ((v_revision IS NOT NULL
AND Nvl(pl.item_revision, v_revision) = v_revision)
OR (v_revision IS NULL))
and NVL(pll.APPROVED_FLAG,'N') = 'Y'
and NVL(pll.CANCEL_FLAG, 'N') = 'N'
and NVL(pll.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING','CLOSED')
and pll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
and pll.line_location_id = pod.line_location_id
and pll.ship_to_organization_id = nvl(v_ship_to_org_id,pll.ship_to_organization_id)
and pll.ship_to_location_id = nvl(v_ship_to_location_id,pll.ship_to_location_id)
AND Nvl(pll.receiving_routing_id,1) = Decode(l_allow_routing_override,
'Y',
Nvl(pll.receiving_routing_id,1),
3)
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)
order by pl.item_revision, nvl(pll.promised_date,pll.need_by_date);
select count(*)
from po_distributions pod,
po_line_locations pll,
po_lines pl
where pll.po_header_id = header_id
AND pod.po_line_id = Nvl(v_po_line_id, pod.po_line_id)
AND pod.line_location_id = Nvl(v_po_line_location_id, pod.line_location_id)
AND pod.po_distribution_id = Nvl(v_po_distribution_id, pod.po_distribution_id)
and nvl(pll.po_release_id,0) = nvl(v_po_release_id,nvl(pll.po_release_id,0))
and pll.po_line_id = pl.po_line_id
--and pl.item_id = v_item_id
-- change for non item master receipts.
and ( pl.item_id = v_item_id
OR ( v_item_id IS NULL
AND pl.item_id IS NULL
AND pl.item_description = v_item_desc))
AND ((v_revision IS NOT NULL
AND Nvl(pl.item_revision, v_revision) = v_revision)
OR (v_revision IS NULL))
and NVL(pll.APPROVED_FLAG,'N') = 'Y'
and NVL(pll.CANCEL_FLAG, 'N') = 'N'
and NVL(pll.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING','CLOSED')
and pll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
and pll.line_location_id = pod.line_location_id
and pll.ship_to_organization_id = nvl(v_ship_to_org_id,pll.ship_to_organization_id)
and pll.ship_to_location_id = nvl(v_ship_to_location_id,pll.ship_to_location_id)
AND Nvl(pll.receiving_routing_id,1) = Decode(l_allow_routing_override,
'Y',
Nvl(pll.receiving_routing_id,1),
3)
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) ;
SELECT
pll.line_location_id
,pll.unit_meas_lookup_code
,nvl(pll.promised_date,pll.need_by_date) promised_date
,0 --pll.quantity_shipped
,pll.receipt_days_exception_code
,pll.qty_rcv_exception_code
,pll.days_early_receipt_allowed
,pll.days_late_receipt_allowed
,pod.po_distribution_id
,pll.ship_to_location_id
,nvl(pll.enforce_ship_to_location_code,'NONE') enforce_ship_to_location_code
,rs.rcv_transaction_id
,rs.item_revision
from po_distributions pod,
po_line_locations pll,
po_lines pl,
-- Bug 3444226 The Join with po_headers is unnecessary
-- po_headers ph,
rcv_supply rs,
rcv_shipment_headers rsh,
-- rcv_shipment_lines rsl,
rcv_transactions rt
where rsh.receipt_source_code = 'VENDOR'
-- Bug 3444226 The Join with po_headers is unnecessary
-- AND ph.po_header_id = header_id
AND pod.po_line_id = Nvl(v_po_line_id, pod.po_line_id)
AND pod.line_location_id = Nvl(v_po_line_location_id, pod.line_location_id)
AND pod.po_distribution_id = Nvl(v_po_distribution_id, pod.po_distribution_id)
and pl.po_line_id = rs.po_line_id
and pll.line_location_id = rs.po_line_location_id
and pod.line_location_id = rs.po_line_location_id
-- and pl.item_id = v_item_id
and NVL(pll.APPROVED_FLAG,'N') = 'Y'
and NVL(pll.CANCEL_FLAG, 'N') = 'N'
and NVL(pll.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED')
and pll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
and pll.ship_to_organization_id = nvl(v_ship_to_org_id,pll.ship_to_organization_id)
and pll.ship_to_location_id = nvl(v_ship_to_location_id,pll.ship_to_location_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 = rs.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 = rs.supply_source_id
connect by prior rt2.transaction_id = rt2.parent_transaction_id
)
--
and rs.supply_type_code = 'RECEIVING'
--and rsl.shipment_line_id = rs.shipment_line_id
and rsh.shipment_header_id = rs.shipment_header_id
AND (Nvl(rsh.receipt_num,'@@@')) = Nvl(v_receipt_num,Nvl(rsh.receipt_num,'@@@'))
and rt.transaction_id = rs.rcv_transaction_id
AND rt.transaction_date < Nvl(v_txn_date,(rt.transaction_date + 1))
--and rt.transaction_type <> 'UNORDERED'
--
and rs.po_header_id = header_id
and rs.item_id = v_item_id
AND ((v_revision IS NOT NULL
AND Nvl(rs.item_revision, v_revision) = v_revision)
OR (v_revision IS NULL))
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)))
AND (
v_lot_number IS NULL OR EXISTS
(
SELECT lot_num
FROM rcv_lots_supply rls
WHERE rls.transaction_id = rs.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 = rs.supply_source_id
)
AND (
v_lpn_id_to_match IS NULL
OR (rs.lpn_id = v_lpn_id_to_match)
)
--
order by rs.item_revision, nvl(pll.promised_date,pll.need_by_date);
SELECT count(*)
FROM po_distributions pod,
po_line_locations pll,
po_lines pl,
-- Bug 3444226 The Join with po_headers is unnecessary
-- po_headers ph,
rcv_supply rs,
rcv_shipment_headers rsh,
-- rcv_shipment_lines rsl,
rcv_transactions rt
where rsh.receipt_source_code = 'VENDOR'
-- Bug 3444226 The Join with po_headers is unnecessary
-- AND ph.po_header_id = header_id
AND pod.po_line_id = Nvl(v_po_line_id, pod.po_line_id)
AND pod.line_location_id = Nvl(v_po_line_location_id, pod.line_location_id)
AND pod.po_distribution_id = Nvl(v_po_distribution_id, pod.po_distribution_id)
and pl.po_line_id = rs.po_line_id
and pll.line_location_id = rs.po_line_location_id
and pod.line_location_id = rs.po_line_location_id
-- and NVL(pl.item_id,0) = nvl(v_item_id,nvl(pl.item_id,0))
and NVL(pll.APPROVED_FLAG,'N') = 'Y'
and NVL(pll.CANCEL_FLAG, 'N') = 'N'
and NVL(pll.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED')
and pll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
and pll.ship_to_organization_id = nvl(v_ship_to_org_id,pll.ship_to_organization_id)
and pll.ship_to_location_id = nvl(v_ship_to_location_id,pll.ship_to_location_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 = rs.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 = rs.supply_source_id
connect by prior rt2.transaction_id = rt2.parent_transaction_id
)
--
and rs.supply_type_code = 'RECEIVING'
--and rsl.shipment_line_id = rs.shipment_line_id
and rsh.shipment_header_id = rs.shipment_header_id
AND (Nvl(rsh.receipt_num,'@@@')) = Nvl(v_receipt_num,Nvl(rsh.receipt_num,'@@@'))
and rt.transaction_id = rs.rcv_transaction_id
AND rt.transaction_date < Nvl(v_txn_date,(rt.transaction_date + 1))
--and rt.transaction_type <> 'UNORDERED'
--
and rs.po_header_id = header_id
and rs.item_id = v_item_id
AND ((v_revision IS NOT NULL
AND Nvl(rs.item_revision, v_revision) = v_revision)
OR (v_revision IS NULL))
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)))
AND (
v_lot_number IS NULL OR EXISTS
(
SELECT lot_num
FROM rcv_lots_supply rls
WHERE rls.transaction_id = rs.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 = rs.supply_source_id
)
AND (
v_lpn_id_to_match IS NULL
OR (rs.lpn_id = v_lpn_id_to_match)
);
SELECT
pll.line_location_id
,pll.unit_meas_lookup_code
,nvl(pll.promised_date,pll.need_by_date) promised_date
,0 --pll.quantity_shipped
,pll.receipt_days_exception_code
,pll.qty_rcv_exception_code
,pll.days_early_receipt_allowed
,pll.days_late_receipt_allowed
,pod.po_distribution_id
,pll.ship_to_location_id
,nvl(pll.enforce_ship_to_location_code,'NONE') enforce_ship_to_location_code
,rs.rcv_transaction_id
,rs.item_revision
from po_distributions pod,
po_line_locations pll,
po_lines pl,
rcv_supply rs,
rcv_shipment_headers rsh,
rcv_transactions rt
where rsh.receipt_source_code = 'VENDOR'
AND pod.po_line_id = Nvl(v_po_line_id, pod.po_line_id)
AND pod.line_location_id = Nvl(v_po_line_location_id, pod.line_location_id)
AND pod.po_distribution_id = Nvl(v_po_distribution_id, pod.po_distribution_id)
and pl.po_line_id = rs.po_line_id
and pll.line_location_id = rs.po_line_location_id
and pod.line_location_id = rs.po_line_location_id
and NVL(pll.APPROVED_FLAG,'N') = 'Y'
and NVL(pll.CANCEL_FLAG, 'N') = 'N'
--and NVL(pll.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING','CLOSED')
and NVL(pll.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED')
and pll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
and pll.ship_to_organization_id = nvl(v_ship_to_org_id,pll.ship_to_organization_id)
and pll.ship_to_location_id = nvl(v_ship_to_location_id,pll.ship_to_location_id)
AND RT.TRANSACTION_TYPE <> 'UNORDERED'
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 = rs.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 = rs.supply_source_id
connect by prior rt2.transaction_id = rt2.parent_transaction_id
)
and rs.supply_type_code = 'RECEIVING'
--and rsl.shipment_line_id = rs.shipment_line_id
and rsh.shipment_header_id = rs.shipment_header_id
AND (Nvl(rsh.receipt_num,'@@@')) = Nvl(v_receipt_num,Nvl(rsh.receipt_num,'@@@'))
and rt.transaction_id = rs.rcv_transaction_id
AND rt.transaction_date < Nvl(v_txn_date,(rt.transaction_date + 1))
and rs.po_header_id = header_id
--and rs.item_id = v_item_id
AND ((v_revision IS NOT NULL
AND Nvl(rs.item_revision, v_revision) = v_revision)
OR (v_revision IS NULL))
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)))
AND (
v_lot_number IS NULL OR EXISTS
(
SELECT lot_num
FROM rcv_lots_supply rls
WHERE rls.transaction_id = rs.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 = rs.supply_source_id
)
AND (
v_lpn_id_to_match IS NULL
OR (rs.lpn_id = v_lpn_id_to_match)
)
order by rs.item_revision, nvl(pll.promised_date,pll.need_by_date);
SELECT count(*)
FROM po_distributions pod,
po_line_locations pll,
po_lines pl,
rcv_supply rs,
rcv_shipment_headers rsh,
rcv_transactions rt
where rsh.receipt_source_code = 'VENDOR'
AND pod.po_line_id = Nvl(v_po_line_id, pod.po_line_id)
AND pod.line_location_id = Nvl(v_po_line_location_id, pod.line_location_id)
AND pod.po_distribution_id = Nvl(v_po_distribution_id, pod.po_distribution_id)
and pl.po_line_id = rs.po_line_id
and pll.line_location_id = rs.po_line_location_id
and pod.line_location_id = rs.po_line_location_id
and NVL(pll.APPROVED_FLAG,'N') = 'Y'
and NVL(pll.CANCEL_FLAG, 'N') = 'N'
--and NVL(pll.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED','CLOSED FOR RECEIVING','CLOSED')
and NVL(pll.CLOSED_CODE,'OPEN') NOT IN ('FINALLY CLOSED')
and pll.SHIPMENT_TYPE IN ('STANDARD','BLANKET','SCHEDULED')
and pll.ship_to_organization_id = nvl(v_ship_to_org_id,pll.ship_to_organization_id)
and pll.ship_to_location_id = nvl(v_ship_to_location_id,pll.ship_to_location_id)
AND RT.TRANSACTION_TYPE <> 'UNORDERED'
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 = rs.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 = rs.supply_source_id
connect by prior rt2.transaction_id = rt2.parent_transaction_id
)
and rs.supply_type_code = 'RECEIVING'
--and rsl.shipment_line_id = rs.shipment_line_id
and rsh.shipment_header_id = rs.shipment_header_id
AND (Nvl(rsh.receipt_num,'@@@')) = Nvl(v_receipt_num,Nvl(rsh.receipt_num,'@@@'))
and rt.transaction_id = rs.rcv_transaction_id
AND rt.transaction_date < Nvl(v_txn_date,(rt.transaction_date + 1))
and rs.po_header_id = header_id
--and rs.item_id = v_item_id
AND ((v_revision IS NOT NULL
AND Nvl(rs.item_revision, v_revision) = v_revision)
OR (v_revision IS NULL))
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)))
AND (
v_lot_number IS NULL OR EXISTS
(
SELECT lot_num
FROM rcv_lots_supply rls
WHERE rls.transaction_id = rs.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 = rs.supply_source_id
)
AND (
v_lpn_id_to_match IS NULL
OR (rs.lpn_id = v_lpn_id_to_match)
);
insert_into_table boolean := FALSE;
SELECT ASN_TYPE
INTO l_asn_type
FROM RCV_SHIPMENT_HEADERS
WHERE SHIPMENT_HEADER_ID = p_shipment_header_id;
select quantity, nvl(qty_rcv_tolerance,0)
into l_poll_qty, l_poll_tolerance_pct
from po_line_locations_all
where line_location_id = temp_cascaded_table(current_n).po_line_location_id ;
SELECT blind_receiving_flag
INTO l_blind_receiving_flag
FROM rcv_parameters
WHERE organization_id = temp_cascaded_table(current_n).to_organization_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);
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);
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;
print_debug('Selected record information',4);