[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_msni (
p_api_version IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_transaction_interface_id IN OUT NOCOPY NUMBER
, p_fm_serial_number IN VARCHAR2
, p_to_serial_number IN VARCHAR2
, p_po_line_loc_id IN NUMBER
, p_product_transaction_id IN OUT NOCOPY NUMBER
, p_origination_date IN DATE DEFAULT NULL
, p_status_id IN NUMBER DEFAULT NULL
, p_territory_code IN VARCHAR2 DEFAULT NULL
, p_serial_attribute_category IN VARCHAR2 DEFAULT NULL
, p_c_attribute1 IN VARCHAR2 DEFAULT NULL
, p_c_attribute2 IN VARCHAR2 DEFAULT NULL
, p_c_attribute3 IN VARCHAR2 DEFAULT NULL
, p_c_attribute4 IN VARCHAR2 DEFAULT NULL
, p_c_attribute5 IN VARCHAR2 DEFAULT NULL
, p_c_attribute6 IN VARCHAR2 DEFAULT NULL
, p_c_attribute7 IN VARCHAR2 DEFAULT NULL
, p_c_attribute8 IN VARCHAR2 DEFAULT NULL
, p_c_attribute9 IN VARCHAR2 DEFAULT NULL
, p_c_attribute10 IN VARCHAR2 DEFAULT NULL
, p_c_attribute11 IN VARCHAR2 DEFAULT NULL
, p_c_attribute12 IN VARCHAR2 DEFAULT NULL
, p_c_attribute13 IN VARCHAR2 DEFAULT NULL
, p_c_attribute14 IN VARCHAR2 DEFAULT NULL
, p_c_attribute15 IN VARCHAR2 DEFAULT NULL
, p_c_attribute16 IN VARCHAR2 DEFAULT NULL
, p_c_attribute17 IN VARCHAR2 DEFAULT NULL
, p_c_attribute18 IN VARCHAR2 DEFAULT NULL
, p_c_attribute19 IN VARCHAR2 DEFAULT NULL
, p_c_attribute20 IN VARCHAR2 DEFAULT NULL
, p_d_attribute1 IN DATE DEFAULT NULL
, p_d_attribute2 IN DATE DEFAULT NULL
, p_d_attribute3 IN DATE DEFAULT NULL
, p_d_attribute4 IN DATE DEFAULT NULL
, p_d_attribute5 IN DATE DEFAULT NULL
, p_d_attribute6 IN DATE DEFAULT NULL
, p_d_attribute7 IN DATE DEFAULT NULL
, p_d_attribute8 IN DATE DEFAULT NULL
, p_d_attribute9 IN DATE DEFAULT NULL
, p_d_attribute10 IN DATE DEFAULT NULL
, p_n_attribute1 IN NUMBER DEFAULT NULL
, p_n_attribute2 IN NUMBER DEFAULT NULL
, p_n_attribute3 IN NUMBER DEFAULT NULL
, p_n_attribute4 IN NUMBER DEFAULT NULL
, p_n_attribute5 IN NUMBER DEFAULT NULL
, p_n_attribute6 IN NUMBER DEFAULT NULL
, p_n_attribute7 IN NUMBER DEFAULT NULL
, p_n_attribute8 IN NUMBER DEFAULT NULL
, p_n_attribute9 IN NUMBER DEFAULT NULL
, p_n_attribute10 IN NUMBER DEFAULT NULL
, p_attribute_category IN VARCHAR2 DEFAULT NULL
, p_attribute1 IN VARCHAR2 DEFAULT NULL
, p_attribute2 IN VARCHAR2 DEFAULT NULL
, p_attribute3 IN VARCHAR2 DEFAULT NULL
, p_attribute4 IN VARCHAR2 DEFAULT NULL
, p_attribute5 IN VARCHAR2 DEFAULT NULL
, p_attribute6 IN VARCHAR2 DEFAULT NULL
, p_attribute7 IN VARCHAR2 DEFAULT NULL
, p_attribute8 IN VARCHAR2 DEFAULT NULL
, p_attribute9 IN VARCHAR2 DEFAULT NULL
, p_attribute10 IN VARCHAR2 DEFAULT NULL
, p_attribute11 IN VARCHAR2 DEFAULT NULL
, p_attribute12 IN VARCHAR2 DEFAULT NULL
, p_attribute13 IN VARCHAR2 DEFAULT NULL
, p_attribute14 IN VARCHAR2 DEFAULT NULL
, p_attribute15 IN VARCHAR2 DEFAULT NULL
)
IS
l_api_name varchar2(50) := 'insert_msni';
select
plla.ship_to_organization_id,
pla.item_id
into
l_org_id,
l_inventory_item_id
from po_lines_all pla,
po_line_locations_all plla
where plla.line_location_id = p_po_line_loc_id
and pla.po_line_id = plla.po_line_id;
inv_rcv_integration_apis.insert_msni (
p_api_version => p_api_version
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_transaction_interface_id => p_transaction_interface_id
, p_fm_serial_number => p_fm_serial_number
, p_to_serial_number => p_to_serial_number
, p_organization_id => l_org_id
, p_inventory_item_id => l_inventory_item_id
, p_product_transaction_id => p_product_transaction_id
, p_product_code => 'RCV'
, p_origination_date => p_origination_date
, p_status_id => p_status_id
, p_territory_code => p_territory_code
, p_serial_attribute_category => p_serial_attribute_category
, p_c_attribute1 => p_c_attribute1
, p_c_attribute2 => p_c_attribute2
, p_c_attribute3 => p_c_attribute3
, p_c_attribute4 => p_c_attribute4
, p_c_attribute5 => p_c_attribute5
, p_c_attribute6 => p_c_attribute6
, p_c_attribute7 => p_c_attribute7
, p_c_attribute8 => p_c_attribute8
, p_c_attribute9 => p_c_attribute9
, p_c_attribute10 => p_c_attribute10
, p_c_attribute11 => p_c_attribute11
, p_c_attribute12 => p_c_attribute12
, p_c_attribute13 => p_c_attribute13
, p_c_attribute14 => p_c_attribute14
, p_c_attribute15 => p_c_attribute15
, p_c_attribute16 => p_c_attribute16
, p_c_attribute17 => p_c_attribute17
, p_c_attribute18 => p_c_attribute18
, p_c_attribute19 => p_c_attribute19
, p_c_attribute20 => p_c_attribute20
, p_d_attribute1 => p_d_attribute1
, p_d_attribute2 => p_d_attribute2
, p_d_attribute3 => p_d_attribute3
, p_d_attribute4 => p_d_attribute4
, p_d_attribute5 => p_d_attribute5
, p_d_attribute6 => p_d_attribute6
, p_d_attribute7 => p_d_attribute7
, p_d_attribute8 => p_d_attribute8
, p_d_attribute9 => p_d_attribute9
, p_d_attribute10 => p_d_attribute10
, p_n_attribute1 => p_n_attribute1
, p_n_attribute2 => p_n_attribute2
, p_n_attribute3 => p_n_attribute3
, p_n_attribute4 => p_n_attribute4
, p_n_attribute5 => p_n_attribute5
, p_n_attribute6 => p_n_attribute6
, p_n_attribute7 => p_n_attribute7
, p_n_attribute8 => p_n_attribute8
, p_n_attribute9 => p_n_attribute9
, p_n_attribute10 => p_n_attribute10
, p_attribute_category => p_attribute_category
, p_attribute1 => p_attribute1
, p_attribute2 => p_attribute2
, p_attribute3 => p_attribute3
, p_attribute4 => p_attribute4
, p_attribute5 => p_attribute5
, p_attribute6 => p_attribute6
, p_attribute7 => p_attribute7
, p_attribute8 => p_attribute8
, p_attribute9 => p_attribute9
, p_attribute10 => p_attribute10
, p_attribute11 => p_attribute11
, p_attribute12 => p_attribute12
, p_attribute13 => p_attribute13
, p_attribute14 => p_attribute14
, p_attribute15 => p_attribute15
, p_att_exist => 'N'
);
END insert_msni;
* Public Procedure: insert_mtli
* Requires: p_api_version, p_transaction_interface_id, p_lot_number
* p_transaction_quantity, p_transaction_uom, p_po_line_loc_id,
* p_product_transaction_id
* Effects: This procedure inserts the record into MTL_TRANSACTION_LOTS_INTERFACE
* table during the creation of ASN.
* Returns: x_return_status, x_msg_count, x_msg_data, p_transaction_interface_id,
* x_serial_transaction_temp_id, p_product_transaction_id
*
* Bugs Fixed : 7476612 - Modified the code to get the Primary UOM from
* po_uom_s.get_primary_uom by passing item_id, org_id, and
* transaction_uom.
*/
PROCEDURE insert_mtli (
p_api_version IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_transaction_interface_id IN OUT NOCOPY NUMBER
, p_lot_number IN VARCHAR2
, p_transaction_quantity IN NUMBER
, p_transaction_uom IN VARCHAR2
, p_po_line_loc_id IN NUMBER
, x_serial_transaction_temp_id OUT NOCOPY NUMBER
, p_product_transaction_id IN OUT NOCOPY NUMBER
, p_vendor_id IN NUMBER DEFAULT NULL
, p_grade_code IN VARCHAR2 DEFAULT NULL
, p_origination_date IN DATE DEFAULT NULL
, p_date_code IN VARCHAR2 DEFAULT NULL
, p_status_id IN NUMBER DEFAULT NULL
, p_change_date IN DATE DEFAULT NULL
, p_age IN NUMBER DEFAULT NULL
, p_retest_date IN DATE DEFAULT NULL
, p_maturity_date IN DATE DEFAULT NULL
, p_item_size IN NUMBER DEFAULT NULL
, p_color IN VARCHAR2 DEFAULT NULL
, p_volume IN NUMBER DEFAULT NULL
, p_volume_uom IN VARCHAR2 DEFAULT NULL
, p_place_of_origin IN VARCHAR2 DEFAULT NULL
, p_best_by_date IN DATE DEFAULT NULL
, p_length IN NUMBER DEFAULT NULL
, p_length_uom IN VARCHAR2 DEFAULT NULL
, p_recycled_content IN NUMBER DEFAULT NULL
, p_thickness IN NUMBER DEFAULT NULL
, p_thickness_uom IN VARCHAR2 DEFAULT NULL
, p_width IN NUMBER DEFAULT NULL
, p_width_uom IN VARCHAR2 DEFAULT NULL
, p_curl_wrinkle_fold IN VARCHAR2 DEFAULT NULL
, p_supplier_lot_number IN VARCHAR2 DEFAULT NULL
, p_territory_code IN VARCHAR2 DEFAULT NULL
, p_vendor_name IN VARCHAR2 DEFAULT NULL
, p_lot_attribute_category IN VARCHAR2 DEFAULT NULL
, p_c_attribute1 IN VARCHAR2 DEFAULT NULL
, p_c_attribute2 IN VARCHAR2 DEFAULT NULL
, p_c_attribute3 IN VARCHAR2 DEFAULT NULL
, p_c_attribute4 IN VARCHAR2 DEFAULT NULL
, p_c_attribute5 IN VARCHAR2 DEFAULT NULL
, p_c_attribute6 IN VARCHAR2 DEFAULT NULL
, p_c_attribute7 IN VARCHAR2 DEFAULT NULL
, p_c_attribute8 IN VARCHAR2 DEFAULT NULL
, p_c_attribute9 IN VARCHAR2 DEFAULT NULL
, p_c_attribute10 IN VARCHAR2 DEFAULT NULL
, p_c_attribute11 IN VARCHAR2 DEFAULT NULL
, p_c_attribute12 IN VARCHAR2 DEFAULT NULL
, p_c_attribute13 IN VARCHAR2 DEFAULT NULL
, p_c_attribute14 IN VARCHAR2 DEFAULT NULL
, p_c_attribute15 IN VARCHAR2 DEFAULT NULL
, p_c_attribute16 IN VARCHAR2 DEFAULT NULL
, p_c_attribute17 IN VARCHAR2 DEFAULT NULL
, p_c_attribute18 IN VARCHAR2 DEFAULT NULL
, p_c_attribute19 IN VARCHAR2 DEFAULT NULL
, p_c_attribute20 IN VARCHAR2 DEFAULT NULL
, p_d_attribute1 IN DATE DEFAULT NULL
, p_d_attribute2 IN DATE DEFAULT NULL
, p_d_attribute3 IN DATE DEFAULT NULL
, p_d_attribute4 IN DATE DEFAULT NULL
, p_d_attribute5 IN DATE DEFAULT NULL
, p_d_attribute6 IN DATE DEFAULT NULL
, p_d_attribute7 IN DATE DEFAULT NULL
, p_d_attribute8 IN DATE DEFAULT NULL
, p_d_attribute9 IN DATE DEFAULT NULL
, p_d_attribute10 IN DATE DEFAULT NULL
, p_n_attribute1 IN NUMBER DEFAULT NULL
, p_n_attribute2 IN NUMBER DEFAULT NULL
, p_n_attribute3 IN NUMBER DEFAULT NULL
, p_n_attribute4 IN NUMBER DEFAULT NULL
, p_n_attribute5 IN NUMBER DEFAULT NULL
, p_n_attribute6 IN NUMBER DEFAULT NULL
, p_n_attribute7 IN NUMBER DEFAULT NULL
, p_n_attribute8 IN NUMBER DEFAULT NULL
, p_n_attribute9 IN NUMBER DEFAULT NULL
, p_n_attribute10 IN NUMBER DEFAULT NULL
, p_attribute_category IN VARCHAR2 DEFAULT NULL
, p_attribute1 IN VARCHAR2 DEFAULT NULL
, p_attribute2 IN VARCHAR2 DEFAULT NULL
, p_attribute3 IN VARCHAR2 DEFAULT NULL
, p_attribute4 IN VARCHAR2 DEFAULT NULL
, p_attribute5 IN VARCHAR2 DEFAULT NULL
, p_attribute6 IN VARCHAR2 DEFAULT NULL
, p_attribute7 IN VARCHAR2 DEFAULT NULL
, p_attribute8 IN VARCHAR2 DEFAULT NULL
, p_attribute9 IN VARCHAR2 DEFAULT NULL
, p_attribute10 IN VARCHAR2 DEFAULT NULL
, p_attribute11 IN VARCHAR2 DEFAULT NULL
, p_attribute12 IN VARCHAR2 DEFAULT NULL
, p_attribute13 IN VARCHAR2 DEFAULT NULL
, p_attribute14 IN VARCHAR2 DEFAULT NULL
, p_attribute15 IN VARCHAR2 DEFAULT NULL
, p_lot_exp_dt IN DATE DEFAULT NULL -- Added for bug7137189
)
IS
l_api_name varchar2(50) := 'insert_mtli';
select
plla.ship_to_organization_id,
pla.item_id
--pla.UNIT_MEAS_LOOKUP_CODE
into
l_org_id,
l_inventory_item_id
--l_uom
from
po_lines_all pla,
po_line_locations_all plla
where plla.line_location_id = p_po_line_loc_id
and plla.po_line_id = pla.po_line_id;
select RCV_TRANSACTIONS_INTERFACE_S.nextval into l_serial_txn_temp_id from dual;
inv_rcv_integration_apis.insert_mtli(
p_api_version => p_api_version
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_transaction_interface_id => p_transaction_interface_id
, p_lot_number => p_lot_number
, p_transaction_quantity => p_transaction_quantity
, p_primary_quantity => l_primary_quantity
, p_organization_id => l_org_id
, p_inventory_item_id => l_inventory_item_id
, p_expiration_date => l_expiration_date -- bug8404937
, x_serial_transaction_temp_id => x_serial_transaction_temp_id
, p_product_transaction_id => p_product_transaction_id
, p_product_code => 'RCV'
, p_vendor_id => p_vendor_id
, p_grade_code => p_grade_code
, p_origination_date => p_origination_date
, p_date_code => p_date_code
, p_status_id => p_status_id
, p_change_date => p_change_date
, p_age => p_age
, p_retest_date => p_retest_date
, p_maturity_date => p_maturity_date
, p_item_size => p_item_size
, p_color => p_color
, p_volume => p_volume
, p_volume_uom => p_volume_uom
, p_place_of_origin => p_place_of_origin
, p_best_by_date => p_best_by_date
, p_length => p_length
, p_length_uom => p_length_uom
, p_recycled_content => p_recycled_content
, p_thickness => p_thickness
, p_thickness_uom => p_thickness_uom
, p_width => p_width
, p_width_uom => p_width_uom
, p_curl_wrinkle_fold => p_curl_wrinkle_fold
, p_supplier_lot_number => p_supplier_lot_number
, p_territory_code => p_territory_code
, p_vendor_name => p_vendor_name
, p_lot_attribute_category => p_lot_attribute_category
, p_c_attribute1 => p_c_attribute1
, p_c_attribute2 => p_c_attribute2
, p_c_attribute3 => p_c_attribute3
, p_c_attribute4 => p_c_attribute4
, p_c_attribute5 => p_c_attribute5
, p_c_attribute6 => p_c_attribute6
, p_c_attribute7 => p_c_attribute7
, p_c_attribute8 => p_c_attribute8
, p_c_attribute9 => p_c_attribute9
, p_c_attribute10 => p_c_attribute10
, p_c_attribute11 => p_c_attribute11
, p_c_attribute12 => p_c_attribute12
, p_c_attribute13 => p_c_attribute13
, p_c_attribute14 => p_c_attribute14
, p_c_attribute15 => p_c_attribute15
, p_c_attribute16 => p_c_attribute16
, p_c_attribute17 => p_c_attribute17
, p_c_attribute18 => p_c_attribute18
, p_c_attribute19 => p_c_attribute19
, p_c_attribute20 => p_c_attribute20
, p_d_attribute1 => p_d_attribute1
, p_d_attribute2 => p_d_attribute2
, p_d_attribute3 => p_d_attribute3
, p_d_attribute4 => p_d_attribute4
, p_d_attribute5 => p_d_attribute5
, p_d_attribute6 => p_d_attribute6
, p_d_attribute7 => p_d_attribute7
, p_d_attribute8 => p_d_attribute8
, p_d_attribute9 => p_d_attribute9
, p_d_attribute10 => p_d_attribute10
, p_n_attribute1 => p_n_attribute1
, p_n_attribute2 => p_n_attribute2
, p_n_attribute3 => p_n_attribute3
, p_n_attribute4 => p_n_attribute4
, p_n_attribute5 => p_n_attribute5
, p_n_attribute6 => p_n_attribute6
, p_n_attribute7 => p_n_attribute7
, p_n_attribute8 => p_n_attribute8
, p_n_attribute9 => p_n_attribute9
, p_n_attribute10 => p_n_attribute10
, p_attribute_category => p_attribute_category
, p_attribute1 => p_attribute1
, p_attribute2 => p_attribute2
, p_attribute3 => p_attribute3
, p_attribute4 => p_attribute4
, p_attribute5 => p_attribute5
, p_attribute6 => p_attribute6
, p_attribute7 => p_attribute7
, p_attribute8 => p_attribute8
, p_attribute9 => p_attribute9
, p_attribute10 => p_attribute10
, p_attribute11 => p_attribute11
, p_attribute12 => p_attribute12
, p_attribute13 => p_attribute13
, p_attribute14 => p_attribute14
, p_attribute15 => p_attribute15
, p_att_exist => 'N'
);
END insert_mtli;
procedure insert_wlpni
(p_api_version IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_po_line_loc_ID IN NUMBER
, p_license_plate_number IN VARCHAR2
, p_LPN_GROUP_ID IN NUMBER
, p_PARENT_LICENSE_PLATE_NUMBER IN VARCHAR2
)
IS
l_api_name varchar2(50) := 'insert_wlpni';
select 1, parent_license_plate_number
from wms_lpn_interface
where license_plate_number = p_lpn
and source_group_id = p_grp_id;
select plla.ship_to_organization_id
into l_org_id
from po_line_locations_all plla
where line_location_id = p_po_line_loc_id;
--Parent LPN is null ==> Insert LPN record, if not exist yet
--Check if LPN already exist
open l_lpn_exist_csr(p_license_plate_number, p_lpn_group_id);
--If LPN does not exist, insert
inv_rcv_integration_apis.insert_wlpni(
p_api_version => p_api_version,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_ORGANIZATION_ID => l_ORG_ID,
p_LPN_ID => null,
p_license_plate_number => p_license_plate_number,
p_LPN_GROUP_ID => p_LPN_GROUP_ID,
p_PARENT_LICENSE_PLATE_NUMBER => null);
--Parent LPN is not null ==> 1. Insert new record with LPN and Parent LPN or update existing LPN with Parent LPN or return error if existing LPN has other Parent LPN
-- 2. Insert new record for Parent LPN if does not exist
--Check if LPN already exist
l_exist := null;
--If LPN does not exist, insert
inv_rcv_integration_apis.insert_wlpni(
p_api_version => p_api_version,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_ORGANIZATION_ID => l_ORG_ID,
p_LPN_ID => null,
p_license_plate_number => p_license_plate_number,
p_LPN_GROUP_ID => p_LPN_GROUP_ID,
p_PARENT_LICENSE_PLATE_NUMBER => p_parent_license_plate_number);
update wms_lpn_interface
set parent_license_plate_number = p_parent_license_plate_number
where source_group_id = p_lpn_group_id
and license_plate_number = p_license_plate_number;
--To see if we need to insert new record for the Parent LPN
l_exist := null;
--Parent LPN as LPN does NOT exist ==> Insert
inv_rcv_integration_apis.insert_wlpni(
p_api_version => p_api_version,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_ORGANIZATION_ID => l_ORG_ID,
p_LPN_ID => null,
p_license_plate_number => p_parent_license_plate_number,
p_LPN_GROUP_ID => p_LPN_GROUP_ID,
p_PARENT_LICENSE_PLATE_NUMBER => null);
END insert_wlpni;
select
plla.ship_to_organization_id,
pla.item_id,
pla.item_revision,
decode(msi.serial_number_control_code,2,inv_rcv_integration_apis.G_EXISTS_ONLY,inv_rcv_integration_apis.G_EXISTS_OR_CREATE)
into
l_org_id,
l_item_id,
l_revision,
l_val_mode
from
po_lines_all pla,
mtl_system_items msi,
po_line_locations_all plla
where pla.item_id = msi.inventory_item_id
and plla.ship_to_organization_id = msi.organization_id
and plla.line_location_id = p_line_loc_id
and plla.po_line_id = pla.po_line_id;
select
plla.ship_to_organization_id
into
l_org_id
from
po_line_locations_all plla
where plla.line_location_id = p_line_loc_id;
select lpn_context into l_lpn_context
from wms_license_plate_numbers
where lpn_id = l_lpn_id;
select
plla.ship_to_organization_id,
pla.item_id,
pla.expiration_date
into
l_org_id,
l_item_id,
l_exp_date
from
po_lines_all pla,
po_line_locations_all plla
where plla.line_location_id = p_line_loc_id
and plla.po_line_id = pla.po_line_id;
select pla.item_id,
plla.ship_to_organization_id
into l_item_id,
l_org_id
from po_lines_all pla ,po_line_locations_all plla
where plla.line_location_id = p_line_location_id
and pla.po_line_id=plla.po_line_id
and pla.po_header_id=plla.po_header_id ;
select
msi.lot_control_code,
msi.serial_number_control_code
into
l_lot_code,
l_serial_code
from
mtl_system_items msi
where msi.inventory_item_id=l_item_id
and msi.organization_id=l_org_id;
select WIP_ENTITY_ID,
WIP_LINE_ID,
WIP_OPERATION_SEQ_NUM,
PO_DISTRIBUTION_ID
from po_distributions_all
where line_location_id = p_line_location_id;
SELECT nvl(pll.quantity, 0),
nvl(pll.quantity_received, 0),
nvl(pll.quantity_shipped, 0),
nvl(pll.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_shipped,
x_quantity_cancelled,
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.
*/
SELECT nvl(sum(primary_quantity),0),
min(primary_unit_of_measure)
INTO x_interface_quantity,
x_primary_uom
FROM rcv_transactions_interface
WHERE processing_status_code = 'PENDING'
AND transaction_type IN ('RECEIVE', 'MATCH','CORRECT','SHIP')
AND po_line_location_id = p_line_location_id;
SELECT pl.item_id,
pl.unit_meas_lookup_code
INTO 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 uom_code
INTO l_asn_uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = p_asn_unit_of_measure;
SELECT uom_code
INTO l_po_uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure =
( select nvl(poll.UNIT_MEAS_LOOKUP_CODE, pol.UNIT_MEAS_LOOKUP_CODE)
from po_line_locations_all poll,
po_lines_all pol
where poll.line_location_id = p_line_location_id
and poll.po_line_id = pol.po_line_id );
select count(*)
into l_rows
from rcv_transactions_interface rti,
mtl_transaction_lots_interface mtli
where rti.INTERFACE_TRANSACTION_ID = mtli.PRODUCT_TRANSACTION_ID
and mtli.PRODUCT_CODE = 'RCV'
and rti.INTERFACE_TRANSACTION_ID = p_asn_line_id
and mtli.LOT_NUMBER is not null;
select count(*)
into l_rows
from rcv_shipment_lines rsl, rcv_transactions rt,
mtl_transaction_lot_numbers mtln
where rsl.shipment_line_id = rt.shipment_line_id
and rt.transaction_type = 'RECEIVE'
and rt.transaction_id = mtln.PRODUCT_TRANSACTION_ID
and mtln.PRODUCT_CODE = 'RCV'
and rsl.shipment_line_id = p_asn_line_id
and mtln.LOT_NUMBER is not null;
select count(*)
into l_temp
from rcv_shipment_lines rsl, rcv_lots_supply rcvls, mtl_lot_numbers mln
where rsl.SHIPMENT_LINE_ID= rcvls.SHIPMENT_LINE_ID
and rsl.to_organization_id = mln.ORGANIZATION_ID
and rsl.ITEM_ID = mln.INVENTORY_ITEM_ID
and rcvls.LOT_NUM = mln.LOT_NUMBER
and rsl.shipment_line_id = p_asn_line_id
and rcvls.LOT_NUM is not null;
select count(*)
into l_rows
from mtl_serial_numbers_interface msni,
rcv_transactions_interface rti
where rti.INTERFACE_TRANSACTION_ID = msni.PRODUCT_TRANSACTION_ID
and msni.PRODUCT_CODE = 'RCV'
and rti.INTERFACE_TRANSACTION_ID = p_asn_line_id
and msni.FM_SERIAL_NUMBER is not null;
select count(*)
into l_rows
from rcv_shipment_lines rsl, rcv_transactions rt,
mtl_unit_transactions mut
where rsl.shipment_line_id = rt.shipment_line_id
and rt.transaction_type = 'RECEIVE'
and rt.transaction_id = mut.PRODUCT_TRANSACTION_ID
and mut.PRODUCT_CODE = 'RCV'
and rsl.shipment_line_id = p_asn_line_id
and mut.SERIAL_NUMBER is not null;
select count(*)
into l_temp
from rcv_serials_supply rss, rcv_shipment_lines rsl,
mtl_serial_numbers msn
where rsl.SHIPMENT_LINE_ID = rss.SHIPMENT_LINE_ID
and rsl.to_organization_id = msn.CURRENT_ORGANIZATION_ID
and rsl.ITEM_ID = msn.INVENTORY_ITEM_ID
and rss.SERIAL_NUM = msn.SERIAL_NUMBER
and rsl.SHIPMENT_LINE_ID = p_asn_line_id
and rss.SERIAL_NUM is not null;
select count(*)
into l_rows
from rcv_transactions_interface rti, po_headers_all poh, po_releases_all por,
po_line_locations_all pll
where rti.po_header_id = poh.po_header_id
and rti.po_release_id = por.po_release_id(+)
and rti.po_line_location_id = pll.line_location_id
and rti.INTERFACE_TRANSACTION_ID = p_asn_line_id
and rti.LICENSE_PLATE_NUMBER is not null;
select count(*)
into l_rows
from rcv_shipment_lines rsl, po_headers_all poh, po_releases_all por,
wms_license_plate_numbers wlpn, po_line_locations_all pll
where rsl.po_header_id = poh.po_header_id
and rsl.po_release_id = por.po_release_id(+)
and rsl.ASN_LPN_ID = wlpn.LPN_ID
and rsl.po_line_location_id = pll.line_location_id
and rsl.SHIPMENT_LINE_ID = p_asn_line_id
and wlpn.LICENSE_PLATE_NUMBER is not null;
SELECT nvl(pll.quantity, 0),
nvl(pll.quantity_received, 0),
nvl(pll.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,
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.
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 rti
WHERE (transaction_status_code = 'PENDING'
and processing_status_code <> 'ERROR')
AND transaction_type IN ('RECEIVE', 'MATCH','CORRECT','SHIP','CANCEL') -- bug 657347 should include 'SHIP'
-- when calculating total quantity
-- in the interface table
AND NOT EXISTS(SELECT 1 FROM rcv_transactions rt -- bug 9583207 should not include
WHERE rt.transaction_type='DELIVER' -- Correction to Deliver transaction
AND rt.transaction_id = rti.parent_transaction_id
AND rti.transaction_type = 'CORRECT')
AND po_line_location_id = p_line_location_id;
SELECT nvl(pll.quantity_shipped, 0),
pll.qty_rcv_exception_code,
pl.item_id,
pl.unit_meas_lookup_code
INTO x_quantity_shipped,
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.
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 rti
WHERE (transaction_status_code = 'PENDING'
and processing_status_code <> 'ERROR')
AND transaction_type IN ('SHIP')
AND NOT EXISTS(SELECT 1 FROM rcv_transactions rt
WHERE rt.transaction_type='DELIVER'
AND rt.transaction_id = rti.parent_transaction_id
AND rti.transaction_type = 'CORRECT')
AND po_line_location_id = p_line_location_id;