[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT Nvl(SUM(rs.to_org_primary_quantity),0)
INTO l_supply_prim_qty
FROM rcv_supply rs
, rcv_transactions rt
WHERE rs.supply_source_id = rt.transaction_id
AND rs.supply_type_code = 'RECEIVING'
AND rs.to_organization_id = p_organization_id
AND rs.item_id = p_item_id
--10/04/05: Reservations are not created on the revision level, so
--when availability API is called, revision maybe NULL. In
--that case, don't use revision as a query criteria
AND Nvl(rs.item_revision,'*&@') = Nvl(p_revision,Nvl(rs.item_revision,'*&@'));
SELECT Nvl(SUM((quantity-Nvl(quantity_delivered,0))*primary_quantity/quantity),0)
INTO l_mol_primary_qty
FROM mtl_txn_request_lines mtrl
, mtl_txn_request_headers mtrh
WHERE mtrh.header_id = mtrl.header_id
AND mtrh.move_order_type = 6
AND mtrl.organization_id = p_organization_id
AND mtrl.inventory_item_id = p_item_id
--10/04/05: Reservations are not created on the revision level, so
--when availability API is called, revision maybe NULL. In
--that case, don't use revision as a query criteria
AND Nvl(mtrl.revision,'@#@') = Nvl(p_revision,Nvl(mtrl.revision,'@#@'))
AND Nvl(mtrl.crossdock_type, -1) = 2
AND mtrl.line_status = 7
AND mtrl.quantity - Nvl(mtrl.quantity_delivered,0) > 0;
SELECT Nvl(ABS(SUM(primary_quantity)),0)
INTO l_rti_primary_quantity
FROM rcv_transactions_interface rti
WHERE to_organization_id = p_organization_id
AND item_id = p_item_id
--10/04/05: Reservations are not created on the revision level, so
--when availability API is called, revision maybe NULL. In
--that case, don't use revision as a query criteria
AND NVL(item_revision, '@@@') = NVL(p_revision,NVL(item_revision, '@@@'))
AND rti.processing_status_code <> 'ERROR'
AND rti.transaction_status_code <> 'ERROR'
AND NOT exists (SELECT '1' FROM rcv_transactions rt
WHERE rt.interface_transaction_id = rti.interface_transaction_id)
AND (TRANSACTION_TYPE = 'DELIVER'
OR (TRANSACTION_TYPE IN ('RETURN TO VENDOR','RETURN TO CUSTOMER')
AND EXISTS (SELECT '1' FROM rcv_transactions rt
WHERE rt.transaction_id = rti.parent_transaction_id
AND rt.transaction_type IN ('RECEIVE','ACCEPT','REJECT','TRANSFER')))
OR (TRANSACTION_TYPE IN ('CORRECT')
AND quantity < 0
AND EXISTS (SELECT '1' FROM rcv_transactions rt
WHERE rt.transaction_id = rti.parent_transaction_id
AND rt.transaction_type IN ('RECEIVE')))
OR (TRANSACTION_TYPE IN ('CORRECT')
AND quantity > 0
AND EXISTS (SELECT '1' FROM rcv_transactions rt
WHERE rt.transaction_id = rti.parent_transaction_id
AND rt.transaction_type IN ('DELIVER'))));
SELECT primary_uom_code
INTO x_source_primary_uom_code
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = p_item_id;