[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT rsh.shipment_header_id shipment_header_id,
rsh.shipment_num shipment_num,
rsl.shipment_line_id shipment_line_id,
rsl.item_id item_id,
rsl.item_description item_description,
rsl.to_organization_id to_organization_id,
rsl.from_organization_id from_organization_id,
rsl.routing_header_id routing_header_id,
rsl.category_id category_id,
rsh.currency_code currency_code,
rsh.conversion_rate currency_conversion_rate,
rsh.conversion_rate_type currency_conversion_type,
rsh.conversion_date currency_conversion_date,
rsl.to_subinventory to_subinventory,
rsl.ship_to_location_id ship_to_location_id,
rsl.deliver_to_location_id deliver_to_location_id,
rsl.deliver_to_person_id deliver_to_person_id,
rsl.ussgl_transaction_code ussgl_transaction_code,
rsl.destination_type_code destination_type_code,
rsl.destination_context destination_context,
rsl.unit_of_measure unit_of_measure,
rsl.primary_unit_of_measure primary_unit_of_measure
FROM rcv_shipment_headers rsh,
rcv_shipment_lines rsl
-- Following 2 lines are commented out for Bugfix 5201151
-- WHERE rsh.shipment_header_id = NVL(v_shipment_header_id, rsh.shipment_header_id)
-- AND NVL(rsh.shipment_num, '0') = NVL(v_shipment_num, NVL(rsh.shipment_num, '0'))
WHERE rsh.shipment_header_id = v_shipment_header_id -- Bugfix 5201151
AND rsl.shipment_header_id = rsh.shipment_header_id
AND NVL(rsl.item_id, 0) = NVL(v_item_id, NVL(rsl.item_id, 0))
AND rsl.line_num = NVL(v_document_line_num, rsl.line_num)
AND rsl.to_organization_id = NVL(v_ship_to_org_id, rsl.to_organization_id)
AND rsl.from_organization_id = NVL(v_ship_from_org_id, rsl.from_organization_id)
AND (NVL(rsl.shipment_line_status_code, 'EXPECTED') <> 'FULLY RECEIVED')
AND rsh.receipt_source_code = 'INVENTORY';
SELECT COUNT(*) AS line_count
FROM rcv_shipment_headers rsh,
rcv_shipment_lines rsl
-- Following 2 lines are commented out for Bugfix 5201151
-- WHERE rsh.shipment_header_id = NVL(v_shipment_header_id, rsh.shipment_header_id)
-- AND NVL(rsh.shipment_num, '0') = NVL(v_shipment_num, NVL(rsh.shipment_num, '0'))
WHERE rsh.shipment_header_id = v_shipment_header_id -- Bugfix 5201151
AND rsl.shipment_header_id = rsh.shipment_header_id
AND NVL(rsl.item_id, 0) = NVL(v_item_id, NVL(rsl.item_id, 0))
AND rsl.line_num = NVL(v_document_line_num, rsl.line_num)
AND rsl.to_organization_id = NVL(v_ship_to_org_id, rsl.to_organization_id)
AND rsl.from_organization_id = NVL(v_ship_from_org_id, rsl.from_organization_id)
AND (NVL(rsl.shipment_line_status_code, 'EXPECTED') <> 'FULLY RECEIVED')
AND rsh.receipt_source_code = 'INVENTORY';
insert_into_table BOOLEAN := FALSE;
SELECT distinct rsh.shipment_header_id
INTO l_shipment_header_id
FROM rcv_shipment_headers rsh,
rcv_shipment_lines rsl
WHERE shipment_num = temp_cascaded_table(current_n).shipment_num
AND rsh.shipment_header_id = rsl.shipment_header_id
AND rsl.to_organization_id = NVL(temp_cascaded_table(current_n).to_organization_id, to_organization_id)
AND rsl.from_organization_id = NVL(temp_cascaded_table(current_n).from_organization_id, from_organization_id)
AND rsh.receipt_source_code = 'INVENTORY';--Bug: 6313315
asn_debug.put_line('Error while selecting shipment_header_id for shipment_num = ' || temp_cascaded_table(current_n).shipment_num );
asn_debug.put_line('delete the temp table ');
temp_cascaded_table.DELETE(i);
asn_debug.put_line('Need to insert a row into po_interface_errors for transfer');
temp_cascaded_table.DELETE(i);
insert_into_table := FALSE;
insert_into_table := TRUE;
insert_into_table := TRUE;
IF rows_fetched = x_record_count THEN --{ last row needs to be inserted anyway
-- so that the row can be used based on qty tolerance checks
IF (g_asn_debug = 'Y') THEN
asn_debug.put_line('quantity is less then 0 but last record');
insert_into_table := TRUE;
insert_into_table := FALSE;
IF insert_into_table THEN --{
IF (x_first_trans) THEN --{
IF (g_asn_debug = 'Y') THEN
asn_debug.put_line('first time ' || TO_CHAR(current_n));
SELECT NVL(MAX(hre.full_name), 'notfound')
INTO x_full_name
FROM hr_employees_current_v hre
WHERE ( hre.inactive_date IS NULL
OR hre.inactive_date > SYSDATE)
AND hre.employee_id = temp_cascaded_table(current_n).deliver_to_person_id;
END IF; --} matches if insert into table
SELECT rsh.shipment_header_id,
rsh.shipment_num,
rsl.shipment_line_id,
rsl.item_description,
rsl.to_organization_id,
rsl.from_organization_id,
rsl.routing_header_id,
rsl.category_id,
rsh.currency_code,
rsh.conversion_rate currency_conversion_rate,
rsh.conversion_rate_type currency_conversion_type,
rsh.conversion_date currency_conversion_date,
rsl.to_subinventory,
rsl.ship_to_location_id
FROM rcv_shipment_headers rsh,
rcv_shipment_lines rsl
WHERE rsh.shipment_header_id = v_shipment_header_id
AND rsh.shipment_header_id = rsl.shipment_header_id
AND rsl.shipment_line_id = v_shipment_line_id;
SELECT NVL(x_cascaded_table(n).use_mtl_lot, lot_control_code),
NVL(x_cascaded_table(n).use_mtl_serial, serial_number_control_code)
INTO x_cascaded_table(n).use_mtl_lot,
x_cascaded_table(n).use_mtl_serial
FROM mtl_system_items
WHERE mtl_system_items.inventory_item_id = x_cascaded_table(n).item_id
AND mtl_system_items.organization_id = x_cascaded_table(n).to_organization_id;
SELECT muom.uom_code
INTO x_cascaded_table(n).uom_code
FROM mtl_units_of_measure muom
WHERE muom.unit_of_measure = x_cascaded_table(n).unit_of_measure;
SELECT rt.po_revision_num,
rsl.item_description,
rsup.po_release_id,
rt.location_id loc_id,
rt.organization_id,
rt.inspection_status_code,
rt.routing_header_id,
rt.currency_code,
rt.currency_conversion_rate,
rt.currency_conversion_type,
rt.currency_conversion_date,
rt.location_id,
rsup.shipment_header_id,
rsup.shipment_line_id,
rsl.category_id,
--rt.vendor_id,
--rt.vendor_site_id,
--rt.po_unit_price,
--rt.movement_id,
rt.deliver_to_person_id,
rt.deliver_to_location_id,
rt.subinventory,
rt.transfer_lpn_id
FROM rcv_transactions rt,
rcv_shipment_lines rsl,
rcv_supply rsup
WHERE rt.transaction_id = v_parent_trx_id
AND rt.transaction_id = rsup.rcv_transaction_id
AND rsup.supply_type_code = 'RECEIVING'
AND rsl.shipment_line_id = rsup.shipment_line_id
AND rt.transaction_id = rsup.rcv_transaction_id
AND rt.transaction_type <> 'UNORDERED';
SELECT rti.po_revision_num,
rti.item_description,
rti.po_release_id,
rti.location_id loc_id,
rti.to_organization_id organization_id,
rti.inspection_status_code,
rti.routing_header_id,
rti.currency_code,
rti.currency_conversion_rate,
rti.currency_conversion_type,
rti.currency_conversion_date,
rti.location_id,
rti.shipment_header_id,
rti.shipment_line_id,
rti.category_id,
--rti.vendor_id,
--rti.vendor_site_id,
--rti.po_unit_price,
--rti.movement_id,
rti.deliver_to_person_id,
rti.deliver_to_location_id,
rti.subinventory,
rti.transfer_lpn_id
FROM rcv_transactions_interface rti
WHERE interface_transaction_id = v_parent_inter_trx_id;
SELECT NVL(x_cascaded_table(n).use_mtl_lot, lot_control_code),
NVL(x_cascaded_table(n).use_mtl_serial, serial_number_control_code)
INTO x_cascaded_table(n).use_mtl_lot,
x_cascaded_table(n).use_mtl_serial
FROM mtl_system_items
WHERE mtl_system_items.inventory_item_id = x_cascaded_table(n).item_id
AND mtl_system_items.organization_id = x_cascaded_table(n).to_organization_id;
insert_into_table BOOLEAN := FALSE;
SELECT rsup.rcv_transaction_id rcv_transaction_id,
rt.transaction_date transaction_date,
rt.transaction_type,
rt.unit_of_measure unit_of_meas,
rt.primary_unit_of_measure,
rt.primary_quantity,
rsup.to_organization_id,
--RT.PO_UNIT_PRICE unit_price,
rsl.category_id,
rsl.item_description,
--RSUP.PO_LINE_ID,
rt.location_id,
rsup.item_id,
rsl.deliver_to_person_id, --pod.DELIVER_TO_PERSON_ID ,
rsl.deliver_to_location_id, --pod.DELIVER_TO_LOCATION_ID ,
rsup.to_subinventory destination_subinventory, --pod.destination_subinventory ,
rt.destination_type_code,
rt.organization_id destination_organization_id, --pod.destination_organization_id,
rt.quantity qty,
0 interface_available_qty
FROM rcv_supply rsup,
rcv_transactions rt,
rcv_shipment_lines rsl
WHERE rt.transaction_id = v_parent_trx_id
AND rsup.to_organization_id = NVL(v_to_organization_id, rsup.to_organization_id)
AND rsup.supply_type_code = 'RECEIVING'
AND rsl.shipment_line_id = rsup.shipment_line_id
AND rt.transaction_id = rsup.rcv_transaction_id
AND rt.transaction_type <> 'UNORDERED'
ORDER BY transaction_date ASC;
SELECT rti.interface_transaction_id rcv_transaction_id,
rti.transaction_date transaction_date,
rti.transaction_type,
rti.unit_of_measure unit_of_meas,
rti.primary_unit_of_measure,
rti.primary_quantity,
rti.to_organization_id,
rti.category_id,
rti.item_description,
rti.location_id,
rti.item_id,
rti.deliver_to_person_id,
rti.deliver_to_location_id,
rti.subinventory destination_subinventory,
rti.destination_type_code,
rti.to_organization_id destination_organization_id,
rti.quantity qty,
rti.interface_available_qty
FROM rcv_transactions_interface rti
WHERE interface_transaction_id = v_parent_inter_trx_id;
asn_debug.put_line('delete the temp table ');
temp_cascaded_table.DELETE(i);
SELECT MAX(rsup.supply_type_code),
MAX(rt.transaction_type)
INTO l_supply_code,
l_transaction_type
FROM rcv_transactions rt,
rcv_supply rsup
WHERE rt.transaction_id = temp_cascaded_table(current_n).parent_transaction_id
AND rsup.rcv_transaction_id = rt.transaction_id;
temp_cascaded_table.DELETE(i);
insert_into_table := FALSE;
insert_into_table := TRUE;
insert_into_table := TRUE;
IF rows_fetched = x_record_count THEN -- { last row needs to be inserted anyway
-- so that the row can be used based on qty tolerance checks
IF (g_asn_debug = 'Y') THEN
asn_debug.put_line('Quantity is less then 0 but last record');
insert_into_table := TRUE;
insert_into_table := FALSE;
IF insert_into_table THEN --{ --start
IF (x_first_trans) THEN --{
IF (g_asn_debug = 'Y') THEN
asn_debug.put_line('First Time ' || TO_CHAR(current_n));
SELECT NVL(MAX(hre.full_name), 'notfound')
INTO x_full_name
FROM hr_employees_current_v hre
WHERE ( hre.inactive_date IS NULL
OR hre.inactive_date > SYSDATE)
AND hre.employee_id = temp_cascaded_table(current_n).deliver_to_person_id;
SELECT muom.uom_code
INTO x_cascaded_table(n).uom_code
FROM mtl_units_of_measure muom
WHERE muom.unit_of_measure = x_cascaded_table(n).unit_of_measure;
insert_into_table BOOLEAN := FALSE;
SELECT rt.transaction_id rcv_transaction_id,
rt.parent_transaction_id grand_parent_txn_id,
rt.transaction_date transaction_date,
rt.transaction_type parent_transaction_type,
rt.quantity qty,
rt.unit_of_measure unit_of_meas,
rt.primary_unit_of_measure,
rt.primary_quantity,
rt.organization_id,
rsl.category_id,
rsl.item_description,
rsl.shipment_line_id,
rsl.shipment_header_id,
rt.location_id,
rsl.item_id,
rt.deliver_to_person_id,
rt.deliver_to_location_id,
rt.subinventory destination_subinventory,
rt.destination_type_code,
rsl.ussgl_transaction_code,
rt.oe_order_line_id
FROM rcv_transactions rt,
rcv_shipment_lines rsl
WHERE rt.transaction_id = v_parent_trx_id
AND rt.shipment_line_id = rsl.shipment_line_id
AND rt.organization_id = NVL(v_to_organization_id, rt.organization_id)
AND rt.transaction_type IN('RECEIVE', 'TRANSFER', 'ACCEPT', 'REJECT', 'RETURN TO CUSTOMER')
ORDER BY rt.transaction_id;
SELECT rti.interface_transaction_id rcv_transaction_id,
rti.parent_transaction_id grand_parent_txn_id,
rti.transaction_date transaction_date,
rti.transaction_type parent_transaction_type,
rti.quantity qty,
rti.unit_of_measure unit_of_meas,
rti.primary_unit_of_measure,
rti.primary_quantity,
rti.to_organization_id organization_id,
rti.category_id,
rti.item_description,
rti.shipment_line_id,
rti.shipment_header_id,
rti.location_id,
rti.item_id,
rti.deliver_to_person_id,
rti.deliver_to_location_id,
rti.subinventory destination_subinventory,
rti.destination_type_code,
rti.ussgl_transaction_code,
rti.oe_order_line_id
FROM rcv_transactions_interface rti
WHERE interface_transaction_id = v_parent_interface_txn_id
AND rti.transaction_type IN('RECEIVE', 'TRANSFER', 'ACCEPT', 'REJECT', 'RETURN TO CUSTOMER');
SELECT transaction_id
INTO x_cascaded_table(n).parent_transaction_id
FROM rcv_transactions
WHERE source_transaction_num = x_cascaded_table(n).parent_source_transaction_num;
asn_debug.put_line('delete the temp table ');
temp_cascaded_table.DELETE(i);
SELECT MAX(rsup.supply_type_code),
MAX(rt.transaction_type)
INTO l_supply_code,
l_transaction_type
FROM rcv_transactions rt,
rcv_supply rsup
WHERE rt.transaction_id = temp_cascaded_table(current_n).parent_transaction_id
AND rsup.rcv_transaction_id = rt.transaction_id;
temp_cascaded_table.DELETE(i);
insert_into_table := FALSE;
SELECT rt.unit_of_measure,
rsl.item_id,
rt.primary_unit_of_measure
INTO l_transaction_uom,
l_item_id,
l_primary_uom
FROM rcv_transactions rt,
rcv_shipment_lines rsl
WHERE rsl.shipment_line_id = rt.shipment_line_id
AND rt.transaction_id = int_org_cor_rec.rcv_transaction_id;
SELECT NVL(SUM(interface_transaction_qty),0)
INTO l_interface_quantity
FROM rcv_transactions_interface
WHERE (transaction_status_code = 'PENDING'
AND processing_status_code <> 'ERROR')
AND group_id = temp_cascaded_table(current_n).group_id
AND transaction_type = 'CORRECT'
AND parent_transaction_id IN ( SELECT transaction_id
FROM rcv_transactions
WHERE parent_transaction_id = int_org_cor_rec.rcv_transaction_id);
insert_into_table := TRUE;
insert_into_table := TRUE;
insert_into_table := TRUE;
IF insert_into_table THEN --{ --start
IF (x_first_trans) THEN --{
IF (g_asn_debug = 'Y') THEN
asn_debug.put_line('First Time ' || TO_CHAR(current_n));
SELECT rsl.item_description,
rt.location_id loc_id,
rt.organization_id,
rt.routing_header_id,
rt.destination_type_code,
rt.destination_context,
rt.inspection_status_code,
rt.currency_code,
rt.currency_conversion_rate,
rt.currency_conversion_type,
rt.currency_conversion_date,
rt.location_id,
rt.shipment_header_id,
rt.shipment_line_id,
rsl.category_id,
rt.deliver_to_person_id,
rt.deliver_to_location_id,
rt.subinventory,
rt.lpn_id,
rt.transfer_lpn_id,
rt.transaction_type
FROM rcv_transactions rt,
rcv_shipment_lines rsl
WHERE rt.transaction_id = v_parent_trx_id
AND rt.transaction_type IN('RECEIVE', 'TRANSFER', 'ACCEPT', 'REJECT')
AND rt.shipment_line_id = rsl.shipment_line_id
ORDER BY rt.transaction_id;
SELECT rti.item_description,
rti.location_id loc_id,
rti.to_organization_id organization_id,
rti.routing_header_id,
rti.destination_type_code,
rti.destination_context,
rti.inspection_status_code,
rti.currency_code,
rti.currency_conversion_rate,
rti.currency_conversion_type,
rti.currency_conversion_date,
rti.location_id,
rti.shipment_header_id,
rti.shipment_line_id,
rti.category_id,
rti.deliver_to_person_id,
rti.deliver_to_location_id,
rti.subinventory,
rti.lpn_id,
rti.transfer_lpn_id,
rti.transaction_type
FROM rcv_transactions_interface rti
WHERE interface_transaction_id = v_parent_inter_trx_id;
SELECT to_organization_id,
from_organization_id
FROM rcv_shipment_lines
WHERE shipment_line_id = p_shipment_line_id;
SELECT NVL(x_cascaded_table(n).use_mtl_lot, lot_control_code),
NVL(x_cascaded_table(n).use_mtl_serial, serial_number_control_code)
INTO x_cascaded_table(n).use_mtl_lot,
x_cascaded_table(n).use_mtl_serial
FROM mtl_system_items
WHERE mtl_system_items.inventory_item_id = x_cascaded_table(n).item_id
AND mtl_system_items.organization_id = x_cascaded_table(n).to_organization_id;
select nvl(max(shipment_header_id),0)
into x_header_id
from rcv_shipment_lines
where shipment_header_id =
x_header_record.header_record.receipt_header_id
and shipment_line_id = x_cascaded_table(n).shipment_line_id;