[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION update_req_line_qty(
p_lineid IN NUMBER
, p_qty IN NUMBER
) RETURN BOOLEAN;
FUNCTION update_req_line_date(
p_lineid IN NUMBER
, p_receipt_date IN DATE
) RETURN BOOLEAN;
FUNCTION update_planned_po(
p_docid IN NUMBER
, p_shipid IN NUMBER DEFAULT 0
, p_entity_type IN VARCHAR2
, p_supply_flag IN OUT NOCOPY BOOLEAN
) RETURN BOOLEAN;
ELSIF (p_action = 'Update_Req_Line_Qty') THEN
d_progress := 90;
l_return_value := update_req_line_qty(
p_lineid => p_lineid
, p_qty => p_qty
);
ELSIF (p_action = 'Update_Req_Line_Date') THEN
d_progress := 100;
l_return_value := update_req_line_date(
p_lineid => p_lineid
, p_receipt_date => p_receipt_date
);
l_return_value := delete_supply(
p_entity_id => p_docid
, p_entity_type => 'PO'
);
l_return_value := delete_supply(
p_entity_id => p_docid
, p_entity_type => 'RELEASE'
);
l_return_value := update_supply(
p_entity_id => p_lineid
, p_entity_type => 'PO LINE'
);
l_return_value := update_supply(
p_entity_id => p_shipid
, p_entity_type => 'PO SHIPMENT'
);
l_return_value := update_supply(
p_entity_id => p_docid
, p_entity_type => 'RELEASE SHIPMENT'
, p_shipid => p_shipid
);
IF (UPPER(p_reservation_action) = 'UPDATE_SO_QUANTITY') THEN
d_progress := 710;
select requisition_header_id
into l_doc_id
from po_requisition_lines_all
where requisition_line_id = p_lineid;
END IF; -- IF (UPPER(p_reservation_action) = 'UPDATE_SO_QUANTITY')...
/* Update mtl_supply for an Approve PO Action */
/* */
/* ----------------------------------------------------------------------- */
FUNCTION approve_po_supply(p_docid IN NUMBER) RETURN BOOLEAN IS
l_auth_status po_headers.authorization_status%TYPE;
SELECT poh.authorization_status
FROM po_headers_all poh
WHERE poh.po_header_id = header_id;
UPDATE mtl_supply ms
SET ms.quantity = 0
, ms.change_flag = 'Y'
WHERE ms.supply_type_code = 'REQ'
AND ms.supply_source_id IN
(
SELECT prl.requisition_line_id
FROM po_requisition_lines_all prl
, po_distributions_all pd
WHERE prl.line_location_id = pd.line_location_id
AND pd.po_header_id = p_docid
);
PO_LOG.stmt(d_module, d_progress, 'Updated ' || SQL%ROWCOUNT || ' rows.');
/* Update mtl_supply for an Approve Blanket Release Action */
/* */
/* ----------------------------------------------------------------------- */
FUNCTION approve_blanket_supply(p_docid IN NUMBER)
RETURN BOOLEAN
IS
l_auth_status po_headers.authorization_status%TYPE;
SELECT por.authorization_status
FROM po_releases_all por
WHERE por.po_release_id = release_id;
UPDATE mtl_supply ms
SET ms.quantity = 0
, ms.change_flag = 'Y'
WHERE ms.supply_type_code = 'REQ'
AND ms.supply_source_id IN
(
SELECT prl.requisition_line_id
FROM po_requisition_lines_all prl
, po_line_locations_all pll
WHERE prl.line_location_id = pll.line_location_id
AND pll.po_release_id = p_docid
);
PO_LOG.stmt(d_module, d_progress, 'Updated ' || SQL%ROWCOUNT || ' rows.');
/* Update mtl_supply for an Approve Planned Release Action */
/* */
/* ----------------------------------------------------------------------- */
FUNCTION approve_planned_supply(p_docid IN NUMBER)
RETURN BOOLEAN
IS
l_auth_status po_releases.authorization_status%TYPE;
SELECT por.authorization_status, por.po_header_id
FROM po_releases_all por
WHERE por.po_release_id = release_id;
/* Insert new PO Supply into mtl_supply for Standard or Planned PO */
/* Approval, Blanket or Planned Release Approval, Standard or Planned */
/* PO Line Approval, Standard or Planned PO Shipment Approval, Blanket */
/* or Planned Release Shipment */
/* */
/* New PO Supply is inserted based on Entity Type */
/* */
/* Entity Type Action */
/* ----------- --------------------------------------------- */
/* PO New PO Supply for Standard or Planned PO */
/* Approval */
/* */
/* RELEASE New PO Supply for Blanket or Planned Release */
/* Approval */
/* */
/* PO LINE New PO Supply for Standard or Planned PO Line */
/* Approval */
/* */
/* PO SHIPMENT New PO Supply for Standard or Planned PO */
/* Shipment Approval */
/* */
/* RELEASE New PO Supply for Blanket or Planned Release */
/* SHIPMENT Shipment */
/* */
/* ----------------------------------------------------------------------- */
-- : create_po_supply had conflicting logic in PO_SUPPLY
-- vs. the Pro*C code. The latter is more accurate, so the method has been changed
-- to reflect the logic in Pro*C wherever there is a conflict.
FUNCTION create_po_supply(
p_entity_id IN NUMBER
, p_entity_type IN VARCHAR2
) RETURN BOOLEAN
IS
l_distid po_distributions.po_distribution_id%TYPE;
SELECT SUM(to_org_primary_quantity),
to_org_primary_uom,
NVL(item_id, -1)
FROM mtl_supply
WHERE supply_type_code IN ('RECEIVING', 'SHIPMENT')
AND po_line_location_id = p_line_loc_id
GROUP BY to_org_primary_uom, nvl(item_id, -1);
l_return_value := delete_supply(
p_entity_id => p_entity_id
, p_entity_type => p_entity_type
);
PO_LOG.stmt(d_module, d_progress, 'delete_supply not successful');
sql_dist := 'SELECT pd.po_distribution_id, '
|| 'pd.quantity_ordered - nvl(pd.quantity_delivered, 0) - nvl(pd.quantity_cancelled, 0), '
|| 'pl.unit_meas_lookup_code, '
|| 'pd.line_location_id '
|| 'FROM po_distributions_all pd, po_lines_all pl ';
SELECT NVL(SUM(pod.quantity_ordered - NVL(pod.quantity_delivered, 0)
- NVL(pod.quantity_cancelled, 0)), 0)
INTO l_released_qty
FROM po_distributions_all pod
, po_releases_all por
WHERE pod.source_distribution_id = l_distid
AND pod.po_release_id = por.po_release_id
AND NVL(por.authorization_status, 'IN PROCESS') = 'APPROVED';
INSERT INTO mtl_supply(supply_type_code,
supply_source_id,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date,
po_header_id,
po_line_id,
po_line_location_id,
po_distribution_id,
po_release_id, --
item_id,
item_revision,
quantity,
unit_of_measure,
receipt_date,
need_by_date,
destination_type_code,
location_id,
to_organization_id,
to_subinventory,
change_flag)
SELECT 'PO',
pd.po_distribution_id,
pd.last_updated_by,
pd.last_update_date,
pd.last_update_login,
pd.created_by,
pd.creation_date,
pd.po_header_id,
pd.po_line_id,
pd.line_location_id,
pd.po_distribution_id,
pd.po_release_id,
pl.item_id,
pl.item_revision,
l_out_poqty,
pl.unit_meas_lookup_code,
nvl(pll.promised_date, pll.need_by_date),
nvl(pll.promised_date, pll.need_by_date), -- bug 4300150
pd.destination_type_code,
pd.deliver_to_location_id,
pd.destination_organization_id,
pd.destination_subinventory,
'Y'
FROM po_distributions_all pd,
po_line_locations_all pll,
po_lines_all pl
WHERE pd.po_distribution_id = l_distid
AND pll.line_location_id = pd.line_location_id
AND pl.item_id IS NOT NULL --
AND pl.po_line_id = pd.po_line_id
AND nvl(pll.closed_code, 'OPEN') not in ('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING')
AND nvl(pll.cancel_flag, 'N') = 'N'
AND nvl(pll.approved_flag, 'Y') = 'Y'
AND pll.quantity IS NOT NULL --
AND not exists
(
SELECT 'Supply Exists'
FROM mtl_supply ms1
WHERE ms1.supply_type_code = 'PO'
AND ms1.supply_source_id = pd.po_distribution_id
);
/* Delete Supply for PO Header or PO Release */
/* */
/* New PO Supply is inserted based on Entity Type */
/* */
/* Entity Type Action */
/* ----------- ----------------------------------------- */
/* PO Remove PO Supply for PO Header */
/* */
/* RELEASE Remove PO Supply for PO Release */
/* */
/* PO LINE Remove PO Supply for PO Line */
/* */
/* PO SHIPMENT Remove PO Supply for PO Shipment */
/* */
/* RELEASE SHIPMENT Remove PO Supply for Release Shipment */
/* */
/* ----------------------------------------------------------------------- */
FUNCTION delete_supply(
p_entity_id IN NUMBER
, p_entity_type IN VARCHAR2
) RETURN BOOLEAN
IS
d_module VARCHAR2(70) := 'po.plsql.PO_SUPPLY.delete_supply';
DELETE FROM mtl_supply
WHERE supply_type_code = 'PO'
AND po_header_id = p_entity_id
AND po_release_id IS NULL; -- : From Pro*C
DELETE FROM mtl_supply
WHERE supply_type_code = 'PO'
AND po_release_id = p_entity_id;
DELETE FROM mtl_supply
WHERE supply_type_code = 'PO'
AND po_line_id = p_entity_id
AND po_release_id IS NULL; -- : From Pro*C
DELETE FROM mtl_supply
WHERE supply_type_code = 'PO'
AND po_line_location_id = p_entity_id;
PO_LOG.stmt(d_module, d_progress, 'Deleted ' || SQL%ROWCOUNT || ' records');
END delete_supply;
/* Update Supply Quantity to 0 for PO Line, PO Shipment or Release */
/* Shipment */
/* */
/* PO Supply is Updated based on the Entity Type */
/* */
/* Entity Type Action */
/* ----------- --------------------------------------------- */
/* PO LINE Update Supply Quantity for PO Line */
/* */
/* PO SHIPMENT Update Supply Quantity for PO Shipment */
/* */
/* RELEASE Update Supply Quantity for Release Shipment */
/* SHIPMENT */
/* */
/* ----------------------------------------------------------------------- */
FUNCTION update_supply(
p_entity_id IN NUMBER
, p_entity_type IN VARCHAR2
, p_shipid IN NUMBER DEFAULT 0
) RETURN BOOLEAN
IS
d_module VARCHAR2(70) := 'po.plsql.PO_SUPPLY.update_supply';
UPDATE mtl_supply
SET quantity = 0
, change_flag = 'Y'
WHERE supply_type_code = 'PO'
AND po_line_id = p_entity_id
AND po_release_id IS NULL; --
UPDATE mtl_supply
SET quantity = 0
, change_flag = 'Y'
WHERE supply_type_code = 'PO'
AND po_line_location_id = p_entity_id;
UPDATE mtl_supply
SET quantity = 0
, change_flag = 'Y'
WHERE supply_type_code = 'PO'
AND po_release_id = p_entity_id
AND po_line_location_id = p_shipid;
PO_LOG.stmt(d_module, d_progress, 'Updated ' || SQL%ROWCOUNT || ' records');
END update_supply;
l_return_value := delete_supply(
p_entity_id => p_entity_id
, p_entity_type => 'PO'
);
l_return_value := update_supply(
p_entity_id => p_entity_id
, p_entity_type => 'PO LINE'
);
l_return_value := update_supply(
p_entity_id => p_entity_id
, p_entity_type => 'PO SHIPMENT'
);
l_return_value := delete_supply(
p_entity_id => p_entity_id
, p_entity_type => 'RELEASE'
);
l_return_value := update_supply(
p_entity_id => p_entity_id
, p_entity_type => 'RELEASE SHIPMENT'
, p_shipid => p_shipid
);
/* Update mtl_supply for Cancel Planned Release or Cancel Planned */
/* Release Shipment Action */
/* */
/* Cancellation of Planned Release and Planned Shipment is based on */
/* Entity Type */
/* */
/* Entity Type Action */
/* ----------- ------------------------------------------ */
/* RELEASE Cancel Planned Release */
/* */
/* RELEASE SHIPMENT Cancel Planned Release Shipment */
/* */
/* ----------------------------------------------------------------------- */
FUNCTION cancel_planned(
p_entity_id IN NUMBER
, p_entity_type IN VARCHAR2
, p_shipid IN NUMBER DEFAULT 0
, p_recreate_flag IN BOOLEAN
) RETURN BOOLEAN
IS
l_supply_flag BOOLEAN := TRUE;
SELECT por.po_header_id
FROM po_releases_all por
WHERE por.po_release_id = release_id;
l_return_value := delete_supply(
p_entity_id => p_entity_id
, p_entity_type => 'RELEASE'
);
PO_LOG.stmt(d_module, d_progress, 'delete_supply not successful');
l_return_value := update_planned_po(
p_docid => p_entity_id
, p_entity_type => 'ADD PLANNED'
, p_supply_flag => l_supply_flag
);
PO_LOG.stmt(d_module, d_progress, 'update_planned_po not successful');
l_return_value := update_supply(
p_entity_id => p_entity_id
, p_entity_type => 'RELEASE SHIPMENT'
, p_shipid => p_shipid
);
PO_LOG.stmt(d_module, d_progress, 'update_supply not successful');
l_return_value := update_planned_po(
p_docid => p_entity_id
, p_shipid => p_shipid
, p_entity_type => 'UPDATE PLANNED'
, p_supply_flag => l_supply_flag
);
PO_LOG.stmt(d_module, d_progress, 'update_planned_po not successful');
* Added an extra FOR UPDATE clause in the below cursor to avoid the
* deadlock scenario.
*/
CURSOR sup2
IS
SELECT /*+ index(mtl_supply MTL_SUPPLY_N10) */
quantity
, unit_of_measure
, nvl(item_id, -1) item_id
, from_organization_id
, to_organization_id
, receipt_date
, rowid
FROM mtl_supply
WHERE change_flag = 'Y'
ORDER BY DECODE (supply_type_code,
'REQ', 1,
'PO', 2,
'SHIPMENT', 3,
'RECEIVING', 4,
5), QUANTITY
FOR UPDATE;
SELECT muom.unit_of_measure
, NULL
FROM mtl_units_of_measure muom
, mtl_units_of_measure tuom
WHERE tuom.unit_of_measure = from_uom
AND tuom.uom_class = muom.uom_class
AND muom.base_uom_flag = 'Y';
SELECT primary_unit_of_measure
, postprocessing_lead_time
FROM mtl_system_items
WHERE inventory_item_id = item_id
AND organization_id = to_org;
DELETE FROM mtl_supply
WHERE rowid = c_sup2.rowid;
PO_LOG.stmt(d_module, d_progress, 'Deleted ' || SQL%ROWCOUNT || ' rows');
UPDATE mtl_supply
SET to_org_primary_quantity = l_pri_qty
, to_org_primary_uom = l_uom
, change_flag = null
, change_type = null
, expected_delivery_date =
DECODE(c_sup2.item_id, -1, to_date(NULL),
c_sup2.receipt_date + NVL(l_lead_time, 0))
WHERE rowid = c_sup2.rowid;
PO_LOG.stmt(d_module, d_progress, 'Updated ' || SQL%ROWCOUNT || ' rows');
SELECT authorization_status
FROM po_requisition_headers_all
WHERE requisition_header_id = header_id;
UPDATE mtl_supply
SET quantity = 0
, change_flag = 'Y'
WHERE supply_type_code = 'REQ'
AND req_header_id = p_entity_id;
UPDATE mtl_supply
SET quantity = 0
, change_flag = 'Y'
WHERE supply_type_code = 'REQ'
AND req_line_id = p_entity_id;
PO_LOG.stmt(d_module, d_progress, 'Updated ' || SQL%ROWCOUNT || ' rows');
UPDATE mtl_supply ms
SET ms.quantity = 0
, ms.change_flag = 'Y'
WHERE ms.supply_type_code = 'REQ'
AND ms.req_header_id = p_docid
AND EXISTS
(
SELECT 1
FROM po_requisition_lines_all porl
WHERE porl.source_type_code = 'VENDOR'
AND porl.requisition_line_id = ms.req_line_id
);
PO_LOG.stmt(d_module, d_progress, 'Updated ' || SQL%ROWCOUNT || ' rows');
DELETE FROM mtl_supply ms1
WHERE ms1.supply_source_id IN
(
SELECT pl.requisition_line_id
FROM po_requisition_lines_all pl
WHERE pl.requisition_header_id = p_entity_id
AND NVL(pl.modified_by_agent_flag, 'N') <> 'Y'
AND NVL(pl.closed_code, 'OPEN') = 'OPEN'
AND NVL(pl.cancel_flag, 'N') = 'N'
AND pl.line_location_id IS NULL
)
AND ms1.supply_type_code = 'REQ';
PO_LOG.stmt(d_module, d_progress, 'Deleted ' || SQL%ROWCOUNT || ' rows');
INSERT INTO mtl_supply
(supply_type_code,
supply_source_id,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date,
req_header_id,
req_line_id,
item_id,
item_revision,
quantity,
unit_of_measure,
receipt_date,
need_by_date,
destination_type_code,
location_id,
from_organization_id,
from_subinventory,
to_organization_id,
to_subinventory,
change_flag)
SELECT 'REQ',
prl.requisition_line_id,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date,
prl.requisition_header_id,
prl.requisition_line_id,
prl.item_id,
decode(prl.source_type_code,'INVENTORY', null,
prl.item_revision),
prl.quantity - ( nvl(prl.QUANTITY_CANCELLED, 0) +
nvl(prl.QUANTITY_DELIVERED, 0) ),
prl.unit_meas_lookup_code,
prl.need_by_date,
prl.need_by_date,
prl.destination_type_code,
prl.deliver_to_location_id,
prl.source_organization_id,
prl.source_subinventory,
prl.destination_organization_id,
prl.destination_subinventory,
'Y'
FROM po_requisition_lines_all prl
WHERE prl.requisition_header_id = p_entity_id
AND nvl(prl.modified_by_agent_flag,'N') <> 'Y'
AND nvl(prl.CLOSED_CODE,'OPEN') = 'OPEN'
AND nvl(prl.CANCEL_FLAG, 'N') = 'N'
-- : Filter out amount basis
AND prl.matching_basis <> 'AMOUNT'
AND prl.line_location_id is null
AND not exists
(SELECT 'supply exists'
FROM mtl_supply ms
WHERE ms.supply_type_code = 'REQ'
AND ms.supply_source_id = prl.requisition_line_id);
INSERT INTO mtl_supply
(supply_type_code,
supply_source_id,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date,
req_header_id,
req_line_id,
item_id,
item_revision,
quantity,
unit_of_measure,
receipt_date,
need_by_date,
destination_type_code,
location_id,
from_organization_id,
from_subinventory,
to_organization_id,
to_subinventory,
change_flag)
SELECT 'REQ',
prl.requisition_line_id,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date,
prl.requisition_header_id,
prl.requisition_line_id,
prl.item_id,
decode(prl.source_type_code,'INVENTORY', null,
prl.item_revision),
prl.quantity - ( nvl(prl.QUANTITY_CANCELLED, 0) +
nvl(prl.QUANTITY_DELIVERED, 0) ),
prl.unit_meas_lookup_code,
prl.need_by_date,
prl.need_by_date,
prl.destination_type_code,
prl.deliver_to_location_id,
prl.source_organization_id,
prl.source_subinventory,
prl.destination_organization_id,
prl.destination_subinventory,
'Y'
FROM po_requisition_lines_all prl
WHERE prl.requisition_line_id = p_entity_id
AND nvl(prl.modified_by_agent_flag,'N') <> 'Y'
AND nvl(prl.CLOSED_CODE, 'OPEN') = 'OPEN'
AND nvl(prl.CANCEL_FLAG, 'N') = 'N'
AND prl.line_location_id IS NULL
-- : Add filters from Pro*C
AND prl.matching_basis <> 'AMOUNT'
AND NOT EXISTS
(
SELECT 'supply exists'
FROM mtl_supply
WHERE supply_type_code = 'REQ'
AND supply_source_id = prl.requisition_line_id
);
PO_LOG.stmt(d_module, d_progress, 'Inserted ' || SQL%ROWCOUNT || ' rows');
insert into mtl_supply(supply_type_code,
supply_source_id,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date,
req_header_id,
req_line_id,
item_id,
item_revision,
quantity,
unit_of_measure,
receipt_date,
need_by_date,
destination_type_code,
location_id,
from_organization_id,
from_subinventory,
to_organization_id,
to_subinventory,
change_flag)
select 'REQ',
prl.requisition_line_id,
prl.last_updated_by,
prl.last_update_date,
prl.last_update_login,
prl.created_by,
prl.creation_date,
prl.requisition_header_id,
prl.requisition_line_id,
prl.item_id,
prl.item_revision,
prl.quantity - (nvl(prl.quantity_cancelled, 0) +
nvl(prl.quantity_delivered, 0)),
prl.unit_meas_lookup_code,
prl.need_by_date,
prl.need_by_date,
prl.destination_type_code,
prl.deliver_to_location_id,
prl.source_organization_id,
prl.source_subinventory,
prl.destination_organization_id,
prl.destination_subinventory,
'Y'
from po_requisition_lines_all prl
where prl.requisition_line_id in
(select prl1.requisition_line_id
from po_requisition_lines_all prl1
where prl1.requisition_header_id =
(select prl2.requisition_header_id
from po_requisition_lines_all prl2
where prl2.requisition_line_id = p_lineid
and prl2.modified_by_agent_flag = 'Y'))
and nvl(prl.modified_by_agent_flag, 'N') <> 'Y'
and nvl(prl.closed_code, 'OPEN') = 'OPEN'
and nvl(prl.cancel_flag, 'N') = 'N'
and prl.line_location_id is null
and not exists
(select 'Supply Exists'
from mtl_supply
where supply_type_code = 'REQ'
and supply_source_id = prl.requisition_line_id);
FUNCTION update_req_line_qty(
p_lineid IN NUMBER
, p_qty IN NUMBER
) RETURN BOOLEAN
IS
d_module VARCHAR2(70) := 'po.plsql.PO_SUPPLY.update_req_line_qty';
UPDATE mtl_supply
SET quantity = p_qty
, change_flag = 'Y'
WHERE supply_type_code = 'REQ'
AND req_line_id = p_lineid;
PO_LOG.stmt(d_module, d_progress, 'Updated ' || SQL%ROWCOUNT || ' rows');
END update_req_line_qty;
FUNCTION update_req_line_date(
p_lineid IN NUMBER
, p_receipt_date IN DATE
) RETURN BOOLEAN
IS
d_module VARCHAR2(70) := 'po.plsql.PO_SUPPLY.update_req_line_date';
UPDATE mtl_supply
SET receipt_date = p_receipt_date
, need_by_date = p_receipt_date -- Bug 3443313
, change_flag = 'Y'
WHERE supply_type_code = 'REQ'
AND req_line_id = p_lineid;
PO_LOG.stmt(d_module, d_progress, 'Updated ' || SQL%ROWCOUNT || ' rows');
END update_req_line_date;
FUNCTION update_planned_po(
p_docid IN NUMBER
, p_shipid IN NUMBER DEFAULT 0
, p_entity_type IN VARCHAR2
, p_supply_flag IN OUT NOCOPY BOOLEAN
) RETURN BOOLEAN
IS
d_module VARCHAR2(70) := 'po.plsql.PO_SUPPLY.update_planned_po';
IF (p_entity_type = 'UPDATE PLANNED') THEN
d_progress := 20;
UPDATE mtl_supply ms
SET ms.quantity =
(
SELECT ms.quantity +
NVL( sum(nvl(pd.quantity_cancelled,0)),0)
FROM po_distributions_all pd
WHERE pd.po_release_id = p_docid
AND pd.line_location_id = p_shipid
AND pd.source_distribution_id = ms.supply_source_id
)
, ms.change_flag = 'Y'
WHERE ms.supply_type_code = 'PO'
AND ms.po_line_location_id =
(
SELECT poll. source_shipment_id
FROM po_line_locations_all poll
WHERE poll.line_location_id = p_shipid
);
SELECT pod.source_distribution_id, pod.quantity_ordered
BULK COLLECT INTO l_ppo_dist_id_tbl, l_ppo_dist_qty_tbl
FROM po_distributions_all pod
WHERE pod.po_release_id = p_docid
AND (pod.po_line_id IS NOT NULL AND pod.line_location_id IS NOT NULL);
UPDATE mtl_supply mts
SET mts.quantity = l_ppo_dist_qty_tbl(i) -
(
SELECT NVL(sum(pod.quantity_ordered -
NVL(pod.quantity_cancelled, 0)), 0)
FROM po_distributions_all pod
WHERE pod.source_distribution_id = l_ppo_dist_id_tbl(i)
AND pod.po_line_id IS NOT NULL
AND pod.line_location_id IS NOT NULL
)
, mts.change_flag = 'Y'
WHERE mts.po_distribution_id = l_ppo_dist_id_tbl(i);
PO_LOG.stmt(d_module, d_progress, 'Updated ' || SQL%ROWCOUNT || ' rows');
END update_planned_po;