[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nvl(pll.quantity, 0),
nvl(pll.quantity_received, 0),
nvl(pll.quantity_cancelled,0),
nvl(pll.secondary_quantity, 0),
nvl(pll.secondary_quantity_received, 0),
nvl(pll.secondary_quantity_cancelled,0),
1 + (nvl(pll.qty_rcv_tolerance,0)/100),
pll.qty_rcv_exception_code,
pl.item_id,
pl.unit_meas_lookup_code
INTO x_quantity_ordered,
x_quantity_received,
x_quantity_cancelled,
/*Bug# 1548597*/
x_secondary_qty_ordered,
x_secondary_qty_received,
x_secondary_qty_cancelled,
--end bug 1548597
x_qty_rcv_tolerance,
x_qty_rcv_exception_code,
x_item_id,
x_po_uom
FROM po_line_locations_all pll, --
po_lines_all pl --
WHERE pll.line_location_id = p_line_location_id
AND pll.po_line_id = pl.po_line_id;
** select may return multiple rows and we only want one value
** to be returned. Having a sum and min group function in the
** select ensures that this sql statement will not raise a
** no_data_found exception even if no rows are returned.
*/
/* Bug# 2347348 : Primary Unit of Measure cannot have value
for One time Items. So Added a decode statement to fetch
unit_of_measure in case of One Time Items and Primary
Unit of Measure for Inventory Items.
*/
SELECT nvl(sum(decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))),0),
decode(min(item_id),null,min(unit_of_measure),min(primary_unit_of_measure))
INTO x_interface_quantity,
x_primary_uom
FROM rcv_transactions_interface
WHERE (transaction_status_code = 'PENDING'
and processing_status_code <> 'ERROR')
AND transaction_type IN ('RECEIVE', 'MATCH','CORRECT','SHIP') -- bug 657347 should include 'SHIP'
-- when calculating total quantity
-- in the interface table
AND po_line_location_id = p_line_location_id;
SELECT nvl(sum(primary_quantity),0),
decode(min(item_id),null,min(unit_of_measure),min(primary_unit_of_measure))
INTO x_interface_quantity,
x_primary_uom
FROM rcv_transactions_interface
WHERE (transaction_status_code = 'PENDING'
and processing_status_code <> 'ERROR')
*/
/*
** Modified by Subhajit on 09/06/95
** Earlier transaction type were ('RECEIVE','MATCH')
** CORRECT transaction were not taken into consideration
AND transaction_type IN ('RECEIVE', 'MATCH')
*/
/*
AND transaction_type IN ('RECEIVE', 'MATCH','CORRECT','SHIP') -- bug 657347 should include 'SHIP'
-- when calculating total quantity
-- in the interface table
AND po_line_location_id = p_line_location_id;
SELECT nvl(sum(secondary_quantity),0),
min(secondary_unit_of_measure)
INTO x_secondary_interface_qty,
x_secondary_uom
FROM rcv_transactions_interface
WHERE (transaction_status_code = 'PENDING'
and processing_status_code <> 'ERROR')
AND transaction_type IN ('RECEIVE', 'MATCH','CORRECT','SHIP')
AND po_line_location_id = p_line_location_id;
SELECT nvl(pll.amount, 0),
nvl(pll.amount_received, 0),
nvl(pll.amount_cancelled,0),
1 + (nvl(pll.qty_rcv_tolerance,0)/100),
pll.qty_rcv_exception_code
INTO x_amount_ordered,
x_amount_received,
x_amount_cancelled,
x_qty_rcv_tolerance,
x_qty_rcv_exception_code
FROM po_line_locations_all pll, --
po_lines_all pl --
WHERE pll.line_location_id = p_line_location_id
AND pll.po_line_id = pl.po_line_id;
SELECT nvl(sum(decode(nvl(order_transaction_id,-999),-999,amount,nvl(interface_transaction_amt,0))),0)
INTO x_interface_amount
FROM rcv_transactions_interface
WHERE (transaction_status_code = 'PENDING'
and processing_status_code <> 'ERROR')
AND transaction_type IN ('RECEIVE', 'MATCH','CORRECT')
AND po_line_location_id = p_line_location_id;
SELECT NVL(quantity, interface_transaction_qty) quantity,
unit_of_measure,
secondary_quantity,
secondary_unit_of_measure
FROM rcv_transactions_interface
WHERE ( transaction_status_code = 'PENDING'
AND processing_status_code <> 'ERROR')
AND transaction_type IN('RECEIVE', 'MATCH', 'CORRECT', 'SHIP')
AND oe_order_line_id = p_oe_order_line_id;
SELECT NVL(oel.ordered_quantity2, 0),
NVL(oel.shipped_quantity2, 0),
NVL(oel.cancelled_quantity2, 0),
NVL(oel.ship_tolerance_above, 0),
oel.inventory_item_id,
uom.unit_of_measure
INTO x_secondary_qty_ordered,
x_secondary_qty_received,
x_secondary_qty_cancelled,
x_qty_rcv_tolerance,
x_item_id,
x_oe_uom
FROM oe_order_lines_all oel,
mtl_units_of_measure uom
WHERE oel.line_id = p_oe_order_line_id
AND uom.uom_code = oel.order_quantity_uom;
SELECT nvl(rsl.quantity_shipped, 0),
nvl(rsl.quantity_received, 0),
rsl.item_id,
rsl.unit_of_measure,
/*Bug# 1548597 */
nvl(rsl.secondary_quantity_shipped, 0),
nvl(rsl.secondary_quantity_received, 0)
--End Bug 1548597
INTO x_quantity_shipped,
x_quantity_received,
x_item_id,
x_shipment_uom,
x_secondary_qty_shipped,
x_secondary_qty_received
FROM rcv_shipment_lines rsl
WHERE rsl.shipment_line_id = p_shipment_line_id;
** select may return multiple rows and we only want one value
** to be returned. Having a sum and min group function in the
** select ensures that this sql statement will not raise a
** no_data_found exception even if no rows are returned.
*/
SELECT nvl(sum(decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))),0),
min(primary_unit_of_measure)
INTO x_interface_quantity,
x_primary_uom
FROM rcv_transactions_interface
WHERE (transaction_status_code = 'PENDING'
and processing_status_code <> 'ERROR')
AND transaction_type = 'RECEIVE'
AND shipment_line_id = p_shipment_line_id;
SELECT nvl(sum(secondary_quantity),0),
min(secondary_unit_of_measure)
INTO x_secondary_interface_qty,
x_secondary_uom
FROM rcv_transactions_interface
WHERE (transaction_status_code = 'PENDING'
and processing_status_code <> 'ERROR')
AND transaction_type = 'RECEIVE'
AND shipment_line_id = p_shipment_line_id;
SELECT quantity,
unit_of_measure,
item_id,
to_org_primary_uom
INTO x_transaction_quantity,
x_transaction_uom,
x_item_id,
x_primary_uom
FROM rcv_supply
WHERE supply_type_code = 'RECEIVING'
AND supply_source_id = p_transaction_id;
select SUM(decode(transaction_type,
'RECEIVE', Secondary_Quantity,
'CORRECT',DECODE(Secondary_Quantity/DECODE(ABS(Secondary_Quantity),0,1,ABS(Secondary_Quantity)),
1,Secondary_Quantity,
-1,-1*Secondary_Quantity,
Secondary_Quantity),
'RETURN TO VENDOR', Secondary_Quantity,
'RETURN TO RECEIVING', Secondary_Quantity,
'ACCEPT', Secondary_Quantity,
'REJECT', Secondary_Quantity,
'DELIVER',-1*Secondary_Quantity,
'UNORDERED', Secondary_Quantity,
'MATCH',Secondary_Quantity,
'TRANSFER',Secondary_quantity,0))
into x_secondary_transaction_qty
from rcv_transactions
start with transaction_id = p_transaction_id
connect by parent_transaction_id = prior transaction_id;
SELECT rt.unit_of_measure,
rsl.item_id,
rt.primary_unit_of_measure
INTO x_transaction_uom,
x_item_id,
x_primary_uom
FROM rcv_transactions rt,
rcv_shipment_lines rsl
WHERE rsl.shipment_line_id = rt.shipment_line_id
AND rt.transaction_id = p_transaction_id;
** select may return multiple rows and we only want one value
** to be returned. Having a sum and min group function in the
** select ensures that this sql statement will not raise a
** no_data_found exception even if no rows are returned.
*/
SELECT nvl(sum(decode(transaction_type,
'CORRECT', -1 * (decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))),
decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))
)),0)
INTO x_interface_quantity
FROM rcv_transactions_interface
WHERE (transaction_status_code = 'PENDING'
and processing_status_code <> 'ERROR')
AND parent_transaction_id = p_transaction_id;
SELECT nvl(sum(decode(transaction_type,
'CORRECT', -1 * (decode(nvl(order_transaction_id,-999),
-999,primary_quantity,nvl(interface_transaction_qty,0))),
secondary_quantity)),0),
min(secondary_unit_of_measure)
INTO x_secondary_interface_qty,
x_secondary_uom
FROM rcv_transactions_interface
WHERE (transaction_status_code = 'PENDING'
and processing_status_code <> 'ERROR')
AND parent_transaction_id = p_transaction_id;
SELECT nvl(sum(amount),0)
into l_receive_correct
from rcv_transactions
where parent_transaction_id = p_transaction_id
and transaction_type = 'CORRECT';
select transaction_id
into l_deliver_id
from rcv_transactions
where parent_transaction_id= p_transaction_id
and transaction_type='DELIVER';
SELECT nvl(sum(amount),0)
into l_deliver_correct
from rcv_transactions
where parent_transaction_id = l_deliver_id
and transaction_type = 'CORRECT';
** select may return multiple rows and we only want one value
** to be returned. Having a sum and min group function in the
** select ensures that this sql statement will not raise a
** no_data_found exception even if no rows are returned.
*/
SELECT nvl(sum(decode(transaction_type,
'CORRECT', -1 * (decode(nvl(order_transaction_id,-999),-999,amount,nvl(interface_transaction_amt,0))),
decode(nvl(order_transaction_id,-999),-999,amount,nvl(interface_transaction_amt,0))
)),0)
INTO x_interface_amount
FROM rcv_transactions_interface
WHERE (transaction_status_code = 'PENDING'
and processing_status_code <> 'ERROR')
AND parent_transaction_id = p_transaction_id;
SELECT nvl(sum(decode(transaction_type,
'CORRECT', -1 * (decode(nvl(order_transaction_id,-999),-999,amount,nvl(interface_transaction_amt,0))),
decode(nvl(order_transaction_id,-999),-999,amount,nvl(interface_transaction_amt,0))
)),0)
INTO x_interface_deliver_amount
FROM rcv_transactions_interface
WHERE (transaction_status_code = 'PENDING'
and processing_status_code <> 'ERROR')
AND parent_transaction_id = l_deliver_id;
select rt.unit_of_measure,
rsl.item_id
into x_parent_uom,
x_item_id
from rcv_transactions rt,
rcv_shipment_lines rsl
where rt.transaction_id = p_parent_id
and rt.shipment_line_id = rsl.shipment_line_id;
select rt.quantity,
rt.unit_of_measure,
rsl.item_id,
/* bug# 1548597 */
rt.secondary_quantity,
rt.secondary_unit_of_measure,
--end bug # 1548597
/* Bug 3735987 Start */
rt.consigned_flag,
rt.organization_id,
rt.vendor_site_id,
rt.subinventory,
rt.locator_id,
rt.po_header_id,
rt.po_line_id,
rsl.item_revision,
rt.primary_unit_of_measure
into x_deliver_quantity,
x_deliver_uom,
x_item_id,
x_secondary_deliver_quantity,
x_secondary_deliver_uom,
l_consigned_flag ,
l_org_id,
l_vendor_site_id,
l_subinventory ,
l_locator_id,
l_po_header_id,
l_po_line_id,
l_item_revision,
l_primary_rt_uom
from rcv_transactions rt,
rcv_shipment_lines rsl
where rt.transaction_id = p_transaction_id
and rt.shipment_line_id = rsl.shipment_line_id;
** select may return multiple rows and we only want one value
** to be returned. Having a sum and min group function in the
** select ensures that this sql statement will not raise a
** no_data_found exception even if no rows are returned.
*/
SELECT nvl(sum(decode(transaction_type,
'CORRECT', -1 * (decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))),
decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))
)),0),
min(primary_unit_of_measure)
INTO x_interface_quantity,
x_primary_uom
FROM rcv_transactions_interface
WHERE (transaction_status_code = 'PENDING'
and processing_status_code <> 'ERROR')
AND parent_transaction_id = p_transaction_id;
SELECT nvl(sum(decode(transaction_type,
'CORRECT', -1 * secondary_quantity,
secondary_quantity)),0)
INTO x_secondary_interface_qty
FROM rcv_transactions_interface
WHERE (transaction_status_code = 'PENDING'
and processing_status_code <> 'ERROR')
AND parent_transaction_id = p_transaction_id;
** select may return multiple rows and we only want one value
** to be returned. Having a sum and min group function in the
** select ensures that this sql statement will not raise a
** no_data_found exception even if no rows are returned.
*/
SELECT nvl(sum(decode(transaction_type,
'CORRECT', -1 * primary_quantity,
primary_quantity)),0),
min(primary_unit_of_measure)
INTO x_trx_quantity,
x_primary_uom
FROM rcv_transactions
WHERE parent_transaction_id = p_transaction_id
AND transaction_type in ('CORRECT','RETURN TO RECEIVING');
SELECT nvl(sum(decode(transaction_type,
'CORRECT', -1 * secondary_quantity,
secondary_quantity)),0)
INTO x_secondary_trx_quantity
FROM rcv_transactions
WHERE parent_transaction_id = p_transaction_id
AND transaction_type in ('CORRECT','RETURN TO RECEIVING');
SELECT decode(msi.revision_qty_control_code,1,'F',2,'T')
INTO l_revision_control
FROM mtl_system_items_b msi
WHERE msi.inventory_item_id = x_item_id
AND msi.organization_id = l_org_id;
select rt.amount
into x_deliver_amount
from rcv_transactions rt
where rt.transaction_id = p_transaction_id;
SELECT nvl(sum(decode(transaction_type,
'CORRECT', -1 * (decode(nvl(order_transaction_id,-999),-999,amount,nvl(interface_transaction_amt,0))),
decode(nvl(order_transaction_id,-999),-999,amount,nvl(interface_transaction_amt,0))
)),0)
INTO x_interface_amount
FROM rcv_transactions_interface
WHERE (transaction_status_code = 'PENDING'
and processing_status_code <> 'ERROR')
AND parent_transaction_id = p_transaction_id;
** select may return multiple rows and we only want one value
** to be returned. Having a sum and min group function in the
** select ensures that this sql statement will not raise a
** no_data_found exception even if no rows are returned.
*/
SELECT nvl(sum(amount),0)
INTO x_transaction_amount
FROM rcv_transactions
WHERE parent_transaction_id = p_transaction_id
AND transaction_type = 'CORRECT';
select nvl(sum(decode(supply_type_code,'RECEIVING',quantity,0)),0)
into l_quantity
from mtl_supply
where po_distribution_id = p_po_distribution_id;
** balance mtl_supply so the above select will raise no_data_found.
** We now select from po_distributions the quantity yet to be
** delivered so that after exploding the distributions we don't
** see zero quantities
** Also see 624832.
*/
select (pod.QUANTITY_ORDERED - nvl(pod.QUANTITY_DELIVERED,0) -
nvl(pod.QUANTITY_CANCELLED,0)) qty,
(poll.quantity - nvl(poll.quantity_received,0) -
nvl(poll.quantity_cancelled,0)) qty_rcvd,
pol.UNIT_MEAS_LOOKUP_CODE, -- should get it from po_lines actually
pol.item_id,
1 + (nvl(poll.qty_rcv_tolerance,0)/100), -- 1337787
nvl(poll.quantity,0),
nvl(poll.quantity_received,0),
nvl(poll.quantity_cancelled,0)
INTO p_available_quantity,
x_balance_receipt_quantity,
p_unit_of_measure,
x_item_id,
x_qty_rcv_tolerance,
x_qty_ordered,
x_qty_received,
x_qty_cancelled
from po_distributions_all pod, --
po_line_locations_all poll, --
po_lines_all pol --
where pod.line_location_id = poll.line_location_id
and pod.po_distribution_id = p_po_distribution_id
and pod.po_line_id = pol.po_line_id;
SELECT nvl(sum(decode(transaction_type,
'CORRECT', -1 * (decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))),
decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))
)),0),
decode(min(item_id),null,min(unit_of_measure),min(primary_unit_of_measure))
INTO x_interface_quantity,
x_primary_uom
FROM rcv_transactions_interface
WHERE (transaction_status_code = 'PENDING'
and processing_status_code <> 'ERROR')
AND po_distribution_id = p_po_distribution_id;
select (pod.AMOUNT_ORDERED - nvl(pod.AMOUNT_DELIVERED,0) -
nvl(pod.AMOUNT_CANCELLED,0)) amt,
(poll.amount - nvl(poll.amount_received,0) -
nvl(poll.amount_cancelled,0)) amt_rcvd,
1 + (nvl(poll.qty_rcv_tolerance,0)/100), -- 1337787
nvl(poll.amount,0),
nvl(poll.amount_received,0),
nvl(poll.amount_cancelled,0)
INTO p_available_amount,
x_balance_receipt_amount,
x_amt_rcv_tolerance,
x_amt_ordered,
x_amt_received,
x_amt_cancelled
from po_distributions_all pod, --
po_line_locations_all poll, --
po_lines_all pol --
where pod.line_location_id = poll.line_location_id
and pod.po_distribution_id = p_po_distribution_id
and pod.po_line_id = pol.po_line_id;
SELECT nvl(sum(decode(transaction_type,
'CORRECT', -1 * (decode(nvl(order_transaction_id,-999),-999,amount,nvl(interface_transaction_amt,0))),
decode(nvl(order_transaction_id,-999),-999,amount,nvl(interface_transaction_amt,0))
)),0)
INTO x_interface_amount
FROM rcv_transactions_interface
WHERE (transaction_status_code = 'PENDING'
and processing_status_code <> 'ERROR')
AND po_distribution_id = p_po_distribution_id;
SELECT quantity,
unit_of_measure
INTO p_available_quantity,
p_unit_of_measure
FROM mtl_supply
WHERE supply_type_code = 'RECEIVING'
AND supply_source_id = p_transaction_id
AND po_distribution_id = p_po_distribution_id;
select nvl(sum(decode(transaction_type,
'CORRECT', -1 * (decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))),
decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))
)),0),
min(primary_unit_of_measure)
INTO x_interface_quantity,
x_primary_uom
FROM rcv_transactions_interface
WHERE (transaction_status_code = 'PENDING'
and processing_status_code <> 'ERROR')
AND parent_transaction_id = p_transaction_id
AND po_distribution_id = p_po_distribution_id;
SELECT MUOM2.unit_of_measure
INTO X_primary_uom
FROM mtl_units_of_measure MUOM1,
mtl_units_of_measure MUOM2
WHERE MUOM1.unit_of_measure = X_transaction_uom
AND MUOM1.uom_class = MUOM2.uom_class
AND MUOM2.base_uom_flag = 'Y';
SELECT MSI.primary_unit_of_measure
INTO X_primary_uom
FROM mtl_system_items_kfv MSI
WHERE MSI.inventory_item_id = X_item_id
AND MSI.organization_id = X_organization_id;
SELECT PL.ITEM_ID,
PL.UNIT_MEAS_LOOKUP_CODE
INTO x_item_id,
x_po_uom
FROM PO_LINE_LOCATIONS PLL,
PO_LINES PL
WHERE PLL.LINE_LOCATION_ID = p_line_location_id AND
PLL.PO_LINE_ID = PL.PO_LINE_ID;
SELECT nvl(sum(decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))),0),
MIN(PRIMARY_UNIT_OF_MEASURE)
INTO x_interface_quantity,
x_primary_uom
FROM RCV_TRANSACTIONS_INTERFACE
WHERE (TRANSACTION_STATUS_CODE = 'PENDING'
and processing_status_code <> 'ERROR') AND
TRANSACTION_TYPE IN ('RECEIVE','MATCH','CORRECT') AND
PO_LINE_LOCATION_ID = p_line_location_id;
SELECT rsl.item_id,
rsl.unit_of_measure
INTO x_item_id,
x_shipment_uom
FROM rcv_shipment_lines rsl
WHERE rsl.shipment_line_id = p_shipment_line_id;
SELECT nvl(sum(decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))),0),
min(primary_unit_of_measure)
INTO x_interface_quantity,
x_primary_uom
FROM rcv_transactions_interface
WHERE (transaction_status_code = 'PENDING'
and processing_status_code <> 'ERROR')
AND transaction_type = 'RECEIVE'
AND shipment_line_id = p_shipment_line_id
AND po_line_location_id = p_line_location_id;
select nvl(quantity_shipped,0)
into l_quantity_shipped
from po_line_locations
where
line_location_id = p_line_location_id;
select sum(nvl(quantity,0))
into l_quantity_returned
from rcv_transactions
where shipment_line_id = p_shipment_line_id and
transaction_type = 'RETURN TO VENDOR';
SELECT quantity,
unit_of_measure
INTO x_available_quantity,
x_unit_of_measure
FROM mtl_supply
WHERE supply_type_code = 'SHIPMENT'
AND supply_source_id = p_shipment_line_id
AND po_distribution_id = p_distribution_id;
select nvl(sum( decode(nvl(order_transaction_id,-999),-999,primary_quantity,nvl(interface_transaction_qty,0))),0),
min(primary_unit_of_measure)
INTO l_interface_quantity,
l_primary_uom
FROM rcv_transactions_interface
WHERE (transaction_status_code = 'PENDING'
and processing_status_code <> 'ERROR')
AND transaction_type = 'RECEIVE'
AND shipment_line_id = p_shipment_line_id
AND po_line_location_id = p_line_location_id
AND po_distribution_id = p_distribution_id;
select
1 + (nvl(poll.qty_rcv_tolerance,0)/100), -- 1337787
nvl(poll.quantity,0),
nvl(poll.quantity_received,0),
nvl(poll.quantity_cancelled,0)
INTO
l_qty_rcv_tolerance,
l_qty_ordered,
l_qty_received,
l_qty_cancelled
from po_distributions_all pod, --
po_line_locations_all poll, --
po_lines_all pol --
where pod.line_location_id = poll.line_location_id
and pod.po_distribution_id = p_distribution_id
and pod.po_line_id = pol.po_line_id;