[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM rcv_headers_interface
WHERE processing_status_code = 'PENDING';
SELECT *
FROM rcv_transactions_interface
WHERE processing_status_code = 'PENDING';
rcv_table_functions.update_rhi_row(rhi_row);
rcv_table_functions.update_rti_row(rti_row);
SELECT uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = p_unit_of_measure;
SELECT rl.quantity,
rl.item_id,
rl.unit_meas_lookup_code unit_of_measure,
si.primary_unit_of_measure
FROM po_requisition_lines_all rl,
mtl_system_items si
WHERE rl.requisition_line_id = p_supply_demand_line_id
AND si.inventory_item_id(+) = rl.item_id;
SELECT oel.ordered_quantity quantity,
oel.inventory_item_id item_id,
uom.unit_of_measure,
si.primary_unit_of_measure
FROM oe_order_lines_all oel,
mtl_units_of_measure uom,
mtl_system_items si
WHERE line_id = p_supply_demand_line_id
AND order_quantity_uom = uom_code
AND si.inventory_item_id(+) = oel.inventory_item_id;
SELECT quantity_shipped quantity,
item_id,
unit_of_measure,
primary_unit_of_measure
FROM rcv_shipment_lines
WHERE shipment_line_id = p_shipment_line_id;
SELECT sum(pod.quantity_ordered) quantity,
pol.item_id,
pol.unit_meas_lookup_code unit_of_measure,
si.primary_unit_of_measure
FROM po_line_locations_all pll,
po_lines_all pol,
po_distributions_all pod,
mtl_system_items si
WHERE pll.line_location_id = p_supply_demand_line_id
AND pll.po_line_id = pol.po_line_id
AND si.inventory_item_id(+) = pol.item_id
AND nvl(si.organization_id,pll.ship_to_organization_id) =
pll.ship_to_organization_id
and pod.line_location_id = pll.line_location_id
and (p_project_id is null or
pod.project_id= p_project_id)
and(p_task_id is null or
pod.task_id = p_task_id)
GROUP BY pol.item_id,
pol.unit_meas_lookup_code,
si.primary_unit_of_measure;
SELECT DECODE(rti.transaction_type,
'SHIP', rti.quantity,
'RECEIVE', rti.quantity,
'CORRECT', DECODE(rt.transaction_type,
'SHIP', rti.quantity,
'RECEIVE', rti.quantity,
0
),
0
) quantity_shipped,
DECODE(rti.transaction_type,
'DELIVER', rti.quantity,
'CORRECT', DECODE(rt.transaction_type,
'DELIVER', rti.quantity,
0
),
DECODE(rti.auto_transact_code,
'DELIVER', rti.quantity,
0
)
) quantity_delivered,
rti.item_id,
rti.unit_of_measure,
rti.primary_unit_of_measure,
rti.to_organization_id
FROM rcv_transactions_interface rti,
rcv_transactions rt
WHERE rti.parent_transaction_id = rt.transaction_id(+)
AND rti.quantity > 0
AND ( rti.transaction_type IN('RECEIVE', 'SHIP', 'DELIVER')
OR ( rti.transaction_type = 'CORRECT'
AND rt.transaction_type IN('RECEIVE', 'DELIVER')))
AND rti.processing_status_code IN('PENDING', 'RUNNING')
AND ( p_organization_id IS NULL
OR p_organization_id = rti.to_organization_id)
AND ( p_item_id IS NULL
OR p_item_id = rti.item_id)
AND ( p_revision IS NULL
OR p_revision = rti.item_revision)
AND ( p_subinventory_code IS NULL
OR p_subinventory_code = rti.subinventory)
AND ( p_locator_id IS NULL
OR p_locator_id = rti.locator_id)
AND ( l_lpn_id = fnd_api.g_miss_num --Bug 5329067
OR l_lpn_id = rti.lpn_id --Bug 5329067
OR ( l_lpn_id IS NULL--Bug 5329067
AND rti.lpn_id IS NULL))
AND ( p_project_id IS NULL
OR p_project_id = rti.project_id)
AND ( p_task_id IS NULL
OR p_task_id = rti.task_id)
AND rti.requisition_line_id = p_supply_demand_line_id
UNION ALL
SELECT rsl.quantity_received,
rsl.quantity_received - NVL(rs.quantity, 0) quantity_delivered,
rsl.item_id,
rsl.unit_of_measure,
rsl.primary_unit_of_measure,
rsl.to_organization_id
FROM rcv_shipment_lines rsl,
rcv_supply rs
WHERE rsl.shipment_line_id = rs.shipment_line_id(+)
AND ( p_organization_id IS NULL
OR p_organization_id = rsl.to_organization_id)
AND ( p_item_id IS NULL
OR p_item_id = rsl.item_id)
AND ( p_revision IS NULL
OR p_revision = rsl.item_revision)
AND ( p_subinventory_code IS NULL
OR p_subinventory_code = rsl.to_subinventory)
AND ( p_locator_id IS NULL
OR p_locator_id = rsl.locator_id)
AND ( l_lpn_id = fnd_api.g_miss_num --Bug 5329067
OR NVL(l_lpn_id, fnd_api.g_miss_num) IN(SELECT NVL(rt.lpn_id, fnd_api.g_miss_num) --Bug 5329067
FROM rcv_transactions rt
WHERE rt.shipment_line_id = rsl.shipment_line_id))
AND ( p_project_id IS NULL
OR p_project_id IN(SELECT rt.project_id
FROM rcv_transactions rt
WHERE rt.shipment_line_id = rsl.shipment_line_id))
AND ( p_task_id IS NULL
OR p_task_id IN(SELECT rt.task_id
FROM rcv_transactions rt
WHERE rt.shipment_line_id = rsl.shipment_line_id))
AND rsl.requisition_line_id = p_supply_demand_line_id;
SELECT DECODE(rti.transaction_type,
--'SHIP', rti.quantity,--dont count
'RECEIVE', rti.quantity,
'CORRECT', DECODE(rt.transaction_type,
--'SHIP', rti.quantity,--dont count
'RECEIVE', rti.quantity,
0
),
0
) quantity_shipped,
DECODE(rti.transaction_type,
'DELIVER', rti.quantity,
'CORRECT', DECODE(rt.transaction_type,
'DELIVER', rti.quantity,
0
),
DECODE(rti.auto_transact_code,
'DELIVER', rti.quantity,
0
)
) quantity_delivered,
rti.item_id,
rti.unit_of_measure,
rti.primary_unit_of_measure,
rti.to_organization_id
FROM rcv_transactions_interface rti,
rcv_transactions rt
WHERE rti.parent_transaction_id = rt.transaction_id(+)
AND rti.quantity > 0
AND ( rti.transaction_type IN('RECEIVE', 'SHIP', 'DELIVER')
OR ( rti.transaction_type = 'CORRECT'
AND rt.transaction_type IN('RECEIVE', 'DELIVER')))
AND rti.processing_status_code IN('PENDING', 'RUNNING')
AND ( p_organization_id IS NULL
OR p_organization_id = rti.to_organization_id)
AND ( p_item_id IS NULL
OR p_item_id = rti.item_id)
AND ( p_revision IS NULL
OR p_revision = rti.item_revision)
AND ( p_subinventory_code IS NULL
OR p_subinventory_code = rti.subinventory)
AND ( p_locator_id IS NULL
OR p_locator_id = rti.locator_id)
AND ( l_lpn_id = fnd_api.g_miss_num --Bug 5329067
OR l_lpn_id = rti.lpn_id --Bug 5329067
OR ( l_lpn_id IS NULL --Bug 5329067
AND rti.lpn_id IS NULL))
AND ( p_project_id IS NULL
OR p_project_id = rti.project_id)
AND ( p_task_id IS NULL
OR p_task_id = rti.task_id)
AND rti.oe_order_line_id = p_supply_demand_line_id
UNION ALL
SELECT rsl.quantity_received,
rsl.quantity_received - NVL(rs.quantity, 0) quantity_delivered,
rsl.item_id,
rsl.unit_of_measure,
rsl.primary_unit_of_measure,
rsl.to_organization_id
FROM rcv_shipment_lines rsl,
rcv_supply rs
WHERE rsl.shipment_line_id = rs.shipment_line_id(+)
AND ( p_organization_id IS NULL
OR p_organization_id = rsl.to_organization_id)
AND ( p_item_id IS NULL
OR p_item_id = rsl.item_id)
AND ( p_revision IS NULL
OR p_revision = rsl.item_revision)
AND ( p_subinventory_code IS NULL
OR p_subinventory_code = rsl.to_subinventory)
AND ( p_locator_id IS NULL
OR p_locator_id = rsl.locator_id)
AND ( l_lpn_id = fnd_api.g_miss_num --Bug 5329067
OR NVL(l_lpn_id, fnd_api.g_miss_num) IN(SELECT NVL(rt.lpn_id, fnd_api.g_miss_num)--Bug 5329067
FROM rcv_transactions rt
WHERE rt.shipment_line_id = rsl.shipment_line_id))
AND ( p_project_id IS NULL
OR p_project_id IN(SELECT rt.project_id
FROM rcv_transactions rt
WHERE rt.shipment_line_id = rsl.shipment_line_id))
AND ( p_task_id IS NULL
OR p_task_id IN(SELECT rt.task_id
FROM rcv_transactions rt
WHERE rt.shipment_line_id = rsl.shipment_line_id))
AND rsl.oe_order_line_id = p_supply_demand_line_id;
SELECT DECODE(rti.transaction_type,
--'SHIP', rti.quantity,
'RECEIVE', rti.quantity,
'CORRECT', DECODE(rt.transaction_type,
'SHIP', rti.quantity,
'RECEIVE', rti.quantity,
0
),
0
) quantity_shipped,
DECODE(rti.transaction_type,
'DELIVER', rti.quantity,
'CORRECT', DECODE(rt.transaction_type,
'DELIVER', rti.quantity,
0
),
DECODE(rti.auto_transact_code,
'DELIVER', rti.quantity,
0
)
) quantity_delivered,
rti.item_id,
rti.unit_of_measure,
rti.primary_unit_of_measure,
rti.to_organization_id
FROM rcv_transactions_interface rti,
rcv_transactions rt
WHERE rti.parent_transaction_id = rt.transaction_id(+)
AND rti.quantity > 0
AND ( rti.transaction_type IN('RECEIVE', 'SHIP', 'DELIVER')
OR ( rti.transaction_type = 'CORRECT'
AND rt.transaction_type IN('RECEIVE', 'DELIVER')))
AND rti.processing_status_code IN('PENDING', 'RUNNING')
AND ( p_organization_id IS NULL
OR p_organization_id = rti.to_organization_id)
AND ( p_item_id IS NULL
OR p_item_id = rti.item_id)
AND ( p_revision IS NULL
OR p_revision = rti.item_revision)
AND ( p_subinventory_code IS NULL
OR p_subinventory_code = rti.subinventory)
AND ( p_locator_id IS NULL
OR p_locator_id = rti.locator_id)
AND ( l_lpn_id = fnd_api.g_miss_num --Bug 5329067
OR l_lpn_id = rti.lpn_id--Bug 5329067
OR ( l_lpn_id IS NULL --Bug 5329067
AND rti.lpn_id IS NULL))
AND ( p_project_id IS NULL
OR p_project_id = rti.project_id)
AND ( p_task_id IS NULL
OR p_task_id = rti.task_id)
AND rti.shipment_line_id = p_shipment_line_id
UNION ALL
SELECT rsl.quantity_received,
rsl.quantity_received - NVL(rs.quantity, 0) quantity_delivered,
rsl.item_id,
rsl.unit_of_measure,
rsl.primary_unit_of_measure,
rsl.to_organization_id
FROM rcv_shipment_lines rsl,
rcv_supply rs
WHERE rsl.shipment_line_id = rs.shipment_line_id(+)
AND ( p_organization_id IS NULL
OR p_organization_id = rsl.to_organization_id)
AND ( p_item_id IS NULL
OR p_item_id = rsl.item_id)
AND ( p_revision IS NULL
OR p_revision = rsl.item_revision)
AND ( p_subinventory_code IS NULL
OR p_subinventory_code = rsl.to_subinventory)
AND ( p_locator_id IS NULL
OR p_locator_id = rsl.locator_id)
AND ( l_lpn_id = fnd_api.g_miss_num --Bug 5329067
OR NVL(l_lpn_id, fnd_api.g_miss_num) IN(SELECT NVL(rt.lpn_id, fnd_api.g_miss_num) --Bug 5329067
FROM rcv_transactions rt
WHERE rt.shipment_line_id = rsl.shipment_line_id))
AND ( p_project_id IS NULL
OR p_project_id IN(SELECT rt.project_id
FROM rcv_transactions rt
WHERE rt.shipment_line_id = rsl.shipment_line_id))
AND ( p_task_id IS NULL
OR p_task_id IN(SELECT rt.task_id
FROM rcv_transactions rt
WHERE rt.shipment_line_id = rsl.shipment_line_id))
AND rsl.shipment_line_id = p_shipment_line_id;
SELECT DECODE(rti.transaction_type,
'SHIP', rti.quantity,
'RECEIVE', rti.quantity,
'CORRECT', DECODE(rt.transaction_type,
'SHIP', rti.quantity,
'RECEIVE', rti.quantity,
0
),
'RETURN TO CUSTOMER', DECODE(rt.transaction_type,
'RECEIVE', -1 * rti.quantity,
0
), --Return txn is similar as -ve Correction. So make the qty
--as -ve in order to calculate the available qty correctly
'RETURN TO VENDOR', DECODE(rt.transaction_type,
'RECEIVE', -1 * rti.quantity,
0
),
0
)quantity_received, --Bug 5329067
DECODE(rti.transaction_type,
'DELIVER', rti.quantity,
'CORRECT', DECODE(rt.transaction_type,
'DELIVER', rti.quantity,
0
),
'RETURN TO CUSTOMER', DECODE(rt.transaction_type,
'DELIVER', -1 * rti.quantity,
0
), --Returns txn is similar as -ve Correction. So make the qty
--as -ve in order to calculate the available qty correctly
'RETURN TO VENDOR', DECODE(rt.transaction_type,
'DELIVER', -1 * rti.quantity,
0
),
'RETURN TO RECEIVING', DECODE(rt.transaction_type,
'DELIVER', -1 * rti.quantity,
0
),
DECODE(rti.auto_transact_code,
'DELIVER', rti.quantity,
0
)
) quantity_delivered, --Bug 5329067
rti.item_id,
rti.unit_of_measure,
rti.primary_unit_of_measure,
rti.to_organization_id
FROM rcv_transactions_interface rti,
rcv_transactions rt
WHERE rti.parent_transaction_id = rt.transaction_id(+)
AND ( rti.quantity > 0
OR (rti.quantity < 0 AND rti.transaction_type = 'CORRECT')
)--Bug 5329067
AND ( rti.transaction_type IN('RECEIVE', 'SHIP', 'DELIVER')
OR ( rti.transaction_type = 'CORRECT'
AND rt.transaction_type IN('RECEIVE', 'DELIVER')
)
OR ( rti.transaction_type IN ('RETURN TO CUSTOMER','RETURN TO VENDOR', 'RETURN TO RECEIVING')
AND rt.transaction_type IN('RECEIVE', 'DELIVER')
)
)--Bug 5329067
AND rti.processing_status_code IN('PENDING', 'RUNNING')
AND ( p_organization_id IS NULL
OR p_organization_id = rti.to_organization_id)
AND ( p_item_id IS NULL
OR p_item_id = rti.item_id)
AND ( p_revision IS NULL
OR p_revision = rti.item_revision)
AND ( p_subinventory_code IS NULL
OR p_subinventory_code = rti.subinventory)
AND ( p_locator_id IS NULL
OR p_locator_id = rti.locator_id)
AND ( l_lpn_id = fnd_api.g_miss_num --Bug 5329067
OR l_lpn_id = rti.lpn_id --Bug 5329067
OR ( l_lpn_id IS NULL--Bug 5329067
AND rti.lpn_id IS NULL))
AND ( p_project_id IS NULL
OR p_project_id = rti.project_id)
AND ( p_task_id IS NULL
OR p_task_id = rti.task_id)
AND rti.po_line_location_id = p_supply_demand_line_id
UNION ALL
SELECT rsl.quantity_received quantity_received, --Bug 5329067
rsl.quantity_received - NVL(rs.quantity, 0) quantity_delivered,
rsl.item_id,
rsl.unit_of_measure,
rsl.primary_unit_of_measure,
rsl.to_organization_id
FROM rcv_shipment_lines rsl,
rcv_supply rs
WHERE rsl.shipment_line_id = rs.shipment_line_id(+)
AND ( p_organization_id IS NULL
OR p_organization_id = rsl.to_organization_id)
AND ( p_item_id IS NULL
OR p_item_id = rsl.item_id)
AND ( p_revision IS NULL
OR p_revision = rsl.item_revision)
AND ( p_subinventory_code IS NULL
OR p_subinventory_code = rsl.to_subinventory)
AND ( p_locator_id IS NULL
OR p_locator_id = rsl.locator_id)
AND ( l_lpn_id = fnd_api.g_miss_num --Bug 5329067
OR NVL(l_lpn_id, fnd_api.g_miss_num) IN(SELECT NVL(rt.lpn_id, fnd_api.g_miss_num) --Bug 5329067
FROM rcv_transactions rt
WHERE rt.shipment_line_id = rsl.shipment_line_id))
AND ( p_project_id IS NULL
OR p_project_id IN(SELECT rt.project_id
FROM rcv_transactions rt
WHERE rt.shipment_line_id = rsl.shipment_line_id))
AND ( p_task_id IS NULL
OR p_task_id IN(SELECT rt.task_id
FROM rcv_transactions rt
WHERE rt.shipment_line_id = rsl.shipment_line_id))
AND rsl.po_line_location_id = p_supply_demand_line_id;
update_rcv_quantity is a local helper function that takes care
of the house keeping to keep the code easy to read. This
procedure increments x_local_quantity and
x_local_quantity appropriately - ensuring to keep
everything in the primary_unit_of_measure.
*/
PROCEDURE update_rcv_quantity(
p_row get_rcv_po_row%ROWTYPE
) IS
x_local_quantity NUMBER;
END update_rcv_quantity;
update_rcv_quantity(c_rcv_row);
update_rcv_quantity(c_rcv_row);
update_rcv_quantity(c_rcv_row);
update_rcv_quantity(c_rcv_row);
update_rcv_quantity(c_rcv_row);
SELECT 'Y'
INTO x_valid_status
FROM po_requisition_lines_all rl,
po_requisition_headers_all rh
WHERE rh.requisition_header_id = rl.requisition_header_id
AND type_lookup_code = l_lookup_code
AND authorization_status = 'APPROVED'
AND NVL(rl.cancel_flag, 'N') = 'N'
AND NVL(rl.closed_code, 'OPEN') = 'OPEN'
AND NVL(rh.closed_code, 'OPEN') = 'OPEN'
AND document_type_code IS NULL
AND destination_type_code <> 'EXPENSE'
AND ( p_organization_id IS NULL
OR p_organization_id = rl.destination_organization_id)
AND ( p_item_id IS NULL
OR p_item_id = rl.item_id)
AND rl.requisition_line_id = p_supply_demand_line_id
AND rh.requisition_header_id = p_supply_demand_header_id;
SELECT 'Y'
INTO x_valid_status
FROM oe_order_lines_all
WHERE open_flag = 'Y'
AND line_category_code = l_lookup_code
AND ( p_organization_id IS NULL
OR p_organization_id = deliver_to_org_id)
AND ( p_item_id IS NULL
OR p_item_id = inventory_item_id)
AND line_id = p_supply_demand_line_id
AND header_id = p_supply_demand_header_id;
SELECT 'Y'
INTO x_valid_status
FROM rcv_shipment_lines rsl,
rcv_shipment_headers rsh
WHERE shipment_line_status_code IN('EXPECTED', 'PARTIALLY RECEIVED')
AND rsl.shipment_header_id = rsh.shipment_header_id
AND ( p_organization_id IS NULL
OR p_organization_id = rsl.to_organization_id)
AND ( p_item_id IS NULL
OR p_item_id = rsl.item_id)
AND rsl.shipment_line_id = p_supply_demand_line_id
AND rsl.shipment_header_id = p_supply_demand_header_id;
SELECT 'Y'
INTO x_valid_status
FROM po_line_locations_all pll,
po_lines_all pol,
po_headers_all poh
WHERE pol.po_header_id = poh.po_header_id
AND pll.po_line_id = pol.po_line_id
AND NVL(pll.approved_flag, 'N') = 'Y'
AND NVL(pol.closed_code, 'OPEN') <> 'FINALLY CLOSED'
AND NVL(pll.cancel_flag, 'N') = 'N'
AND pll.shipment_type IN('STANDARD', 'BLANKET', 'SCHEDULED')
AND ( p_organization_id IS NULL
OR p_organization_id = pll.ship_to_organization_id)
AND ( p_item_id IS NULL
OR p_item_id = pol.item_id)
AND pll.line_location_id = p_supply_demand_line_id
AND pol.po_header_id = p_supply_demand_header_id;
SELECT 'Y'
INTO x_valid_status
FROM rcv_shipment_lines rsl,
rcv_shipment_headers rsh
WHERE shipment_line_status_code IN('EXPECTED', 'PARTIALLY RECEIVED')
AND rsl.shipment_header_id = rsh.shipment_header_id
AND rsl.shipment_line_id = p_supply_demand_line_detail
AND rsl.po_line_location_id = p_supply_demand_line_id
AND rsl.po_header_id = p_supply_demand_header_id;