[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT oel.line_number oe_order_line_num,
msi.description item_description,
oel.sold_to_org_id customer_id,
oel.ship_to_org_id customer_site_id,
oel.ship_to_org_id from_organization_id,
oel.ship_from_org_id to_organization_id,
oel.unit_selling_price unit_price,
oeh.transactional_curr_code currency_code,
oeh.conversion_type_code currency_conversion_type,
oeh.conversion_rate_date currency_conversion_date,
oeh.conversion_rate currency_conversion_rate,
oel.subinventory subinventory,
oel.ship_from_org_id deliver_to_location_id
FROM oe_order_headers oeh,
oe_order_lines oel,
mtl_system_items msi
WHERE oel.line_id = v_line_id
AND oel.header_id = oeh.header_id
AND oel.booked_flag = 'Y'
AND oel.ordered_quantity > NVL(oel.shipped_quantity, 0)
AND oeh.open_flag = 'Y'
AND oel.line_category_code = 'RETURN'
AND oel.open_flag = 'Y'
AND oel.flow_status_code = 'AWAITING_RETURN'
AND msi.organization_id = oe_sys_parameters.VALUE('MASTER_ORGANIZATION_ID', oel.org_id)
AND msi.inventory_item_id = oel.inventory_item_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 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 primary_unit_of_measure
--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).primary_unit_of_measure
--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 DISTINCT oeh.header_id
INTO x_cascaded_table(n).oe_order_header_id
FROM oe_order_headers_all oeh,
oe_order_lines_all oel,
oe_transaction_types_all oett
WHERE oeh.order_number = x_cascaded_table(n).oe_order_num
AND oeh.header_id = oel.header_id
AND oel.line_category_code = 'RETURN'
AND oel.line_type_id = oett.transaction_type_id
AND oett.order_category_code IN('MIXED', 'RETURN')
AND oel.open_flag = 'Y'
AND oeh.booked_flag = 'Y'
AND ( ( oeh.ship_from_org_id IS NOT NULL
AND oeh.ship_from_org_id = x_cascaded_table(n).to_organization_id)
OR EXISTS(SELECT 1
FROM oe_order_lines_all oela
WHERE oela.header_id = oeh.header_id
AND oela.ship_from_org_id = x_cascaded_table(n).to_organization_id));
SELECT line_id,
inventory_item_id
INTO my_line_id,
my_item_id
FROM oe_order_lines_all
WHERE header_id = x_cascaded_table(n).oe_order_header_id
AND line_number = x_cascaded_table(n).document_line_num
-- pjiang: extra filter for oe line split
AND flow_status_code = 'AWAITING_RETURN';
SELECT line_number
INTO x_cascaded_table(n).document_line_num
FROM oe_order_lines_all
WHERE line_id = x_cascaded_table(n).oe_order_line_id;
SELECT acct.cust_account_id
INTO x_cascaded_table(n).customer_id
FROM hz_cust_accounts acct
WHERE acct.account_number = x_cascaded_table(n).customer_account_number;
SELECT acct.cust_account_id
INTO x_cascaded_table(n).customer_id
FROM hz_parties party,
hz_cust_accounts acct
WHERE acct.party_id = party.party_id
AND party.party_name = x_cascaded_table(n).customer_party_name;
SELECT MAX(org.organization_id)
INTO x_cascaded_table(n).to_organization_code
FROM hr_locations hl,
HR_ALL_ORGANIZATION_UNITS org --Bug 5217526. Earlier used org_organization_definitions
WHERE x_cascaded_table(n).ship_to_location_id = hl.location_id
AND hl.inventory_organization_id = org.organization_id;
SELECT MAX(org.organization_id)
INTO x_cascaded_table(n).to_organization_code
FROM hr_locations hl,
HR_ALL_ORGANIZATION_UNITS org --Bug 5217526. Earlier used org_organization_definitions
WHERE x_cascaded_table(n).ship_to_location_code = hl.location_code
AND hl.inventory_organization_id = org.organization_id;
SELECT NVL(oel.ship_to_org_id, oeh.ship_to_org_id) customer_site_id,
NVL(oel.ship_from_org_id, oeh.ship_from_org_id) to_organization_id,
NVL(oel.sold_to_org_id, oeh.sold_to_org_id) customer_id,
NVL(oel.promise_date, oel.request_date) expected_receipt_date,
oel.ordered_quantity ordered_qty,
'N' enforce_ship_to_location_code,
oel.deliver_to_contact_id deliver_to_person_id,
oel.deliver_to_org_id deliver_to_location_id,
oel.header_id oe_order_header_id,
oel.line_id oe_order_line_id,
oeh.order_number oe_order_num,
oel.line_number oe_order_line_num,
oel.inventory_item_id item_id,
mum.unit_of_measure,
msi.description description
FROM oe_order_headers_all oeh,
oe_order_lines_all oel,
oe_transaction_types_all olt,
oe_transaction_types_tl t,
mtl_units_of_measure_tl mum,
mtl_system_items msi
WHERE oeh.header_id = v_header_id
AND oeh.header_id = oel.header_id
AND oel.line_id = NVL(v_line_id, oel.line_id)-- bug 4740567
AND oel.line_number = NVL(v_rma_line_num, oel.line_number)
AND oeh.open_flag = 'Y'
AND oel.line_category_code = 'RETURN'
AND oel.open_flag = 'Y'
AND oel.inventory_item_id = NVL(v_item_id, oel.inventory_item_id)
AND oel.ship_from_org_id = NVL(v_ship_to_org_id, oel.ship_from_org_id)
AND oel.line_type_id = olt.transaction_type_id
AND olt.transaction_type_code = 'LINE'
AND olt.transaction_type_id = t.transaction_type_id
AND t.LANGUAGE = USERENV('LANG')
AND msi.organization_id = oe_sys_parameters.VALUE('MASTER_ORGANIZATION_ID', oel.org_id)
AND msi.inventory_item_id = oel.inventory_item_id
AND ( oel.ordered_item_id = NVL(v_customer_item_id, oel.ordered_item_id)
OR oel.ordered_item_id IS NULL)
AND oel.booked_flag = 'Y'
AND oel.ordered_quantity > NVL(oel.shipped_quantity, 0)
AND oel.flow_status_code = 'AWAITING_RETURN'
AND oel.order_quantity_uom = mum.uom_code
AND mum.LANGUAGE = USERENV('LANG')
ORDER BY expected_receipt_date;
insert_into_table BOOLEAN := FALSE;
temp_cascaded_table.DELETE;
insert_into_table := TRUE;
insert_into_table := FALSE;
IF insert_into_table THEN
already_allocated_qty := 0;
insert_into_table := insert_into_table
AND (rma_line_qty > 0);
IF insert_into_table THEN
txn_remaining_qty_rma_uom := rcv_transactions_interface_sv.convert_into_correct_qty(txn_remaining_qty,
temp_cascaded_table(1).unit_of_measure,
temp_cascaded_table(1).item_id,
x_rma_line_record.unit_of_measure
);
insert_into_table := FALSE;
IF insert_into_table THEN --{ allocate part of the txn qty to this line
-- record where we are allocating the qty from
temp_cascaded_table(temp_cascaded_table.LAST).oe_order_line_id := x_rma_line_record.oe_order_line_id;
ELSE -- }{ matches if insert_into_table
-- remove the row if the current line is not matched to the txn
temp_cascaded_table.DELETE(temp_cascaded_table.COUNT);
END IF; --} matches if insert_into_table
SELECT NVL(oeh.open_flag, 'N'),
NVL(oel.line_category_code, 'N'),
NVL(oel.open_flag, 'N'),
NVL(oel.inventory_item_id, 0),
NVL(mci.customer_item_number, 'N'),
NVL(oel.booked_flag, 'N'),
NVL(oel.flow_status_code, 'N'),
oel.ordered_quantity,
NVL(oel.shipped_quantity, 0)
INTO x_header_open_flag,
x_line_category_code,
x_line_open_flag,
x_item_id,
x_customer_item_num,
x_booked_flag,
x_flow_status_code,
x_ordered_quantity,
x_shipped_quantity
FROM oe_order_headers_all oeh,
oe_order_lines_all oel,
mtl_customer_items mci
WHERE oeh.header_id = x_cascaded_table(n).oe_order_header_id
AND oeh.header_id = oel.header_id
AND oel.line_number = NVL(x_cascaded_table(n).oe_order_line_num, oel.line_number)
AND oel.inventory_item_id = NVL(x_cascaded_table(n).item_id, oel.inventory_item_id)
AND oel.ordered_item_id = mci.customer_item_id(+);
temp_cascaded_table.DELETE;
asn_debug.put_line('delete the temp table ');
temp_cascaded_table.DELETE;
SELECT NVL(MIN(inspection_required_flag), 'N')
INTO x_inspection_required_flag
FROM oe_po_enter_receipts_v
WHERE oe_order_header_id = x_cascaded_table(n).oe_order_header_id
AND item_id = x_cascaded_table(n).item_id;
select rma_receipt_routing_id
into x_cascaded_table(n).routing_header_id
from mtl_client_parameters
WHERE client_code = l_client_code;
SELECT NVL(MIN(rma_receipt_routing_id), 1)
INTO x_cascaded_table(n).routing_header_id
FROM rcv_parameters
WHERE organization_id = x_cascaded_table(n).to_organization_id;
SELECT NVL(MIN(rma_receipt_routing_id), 1)
INTO x_cascaded_table(n).routing_header_id
FROM rcv_parameters
WHERE organization_id = x_cascaded_table(n).to_organization_id;
SELECT category_set_id
INTO l_category_set_id
FROM mtl_default_category_sets
WHERE functional_area_id = 2;
SELECT MAX(category_id)
INTO x_cascaded_table(n).category_id
FROM mtl_item_categories
WHERE inventory_item_id = x_cascaded_table(n).item_id
AND organization_id = x_cascaded_table(n).to_organization_id
AND category_set_id = l_category_set_id;
select set_of_books_id
into x_sob_id
FROM org_organization_definitions
WHERE organization_id = x_cascaded_table(n).to_organization_id ;
SELECT transaction_date into x_parent_txn_date
from rcv_transactions rt
where rt.transaction_id = x_parent_txn_id;
SELECT transaction_date into x_parent_txn_date
from rcv_transactions_interface rti
where rti.interface_transaction_id = x_parent_txn_id;
SELECT oola.reference_line_id
INTO x_oe_reference_order_line_id
FROM oe_order_lines_all oola
WHERE oola.line_id = x_oe_order_line_id
AND oola.return_context = 'ORDER';
SELECT max(mmt.transaction_date)
INTO x_so_issue_transaction_date
FROM mtl_material_transactions mmt
WHERE mmt.inventory_item_id = x_item_id
-- AND mmt.transaction_type_id = 33 -- Bug 16511481 removed
AND mmt.transaction_action_id in (1, 7) -- Bug 16511481 added
AND mmt.transaction_source_type_id = 2
AND mmt.trx_source_line_id = x_oe_reference_order_line_id;
SELECT ooha.order_number, oola.line_number||'.'||oola.shipment_number
INTO x_oe_reference_order_num, x_oe_reference_order_line
FROM oe_order_headers_all ooha, oe_order_lines_all oola
WHERE ooha.header_id = oola.header_id
AND oola.line_id = x_oe_reference_order_line_id;
SELECT NVL(MAX(inventory_item_id), -9999)
INTO x_inventory_item
FROM mtl_system_items
WHERE inventory_item_id = x_item_id_record.item_id;
SELECT NVL(MAX(inventory_item_id), -9999)
INTO x_inventory_item
FROM mtl_system_items
WHERE SYSDATE BETWEEN NVL(start_date_active, SYSDATE - 1) AND NVL(end_date_active, SYSDATE + 1)
AND inventory_item_id = x_item_id_record.item_id
AND organization_id = NVL(x_item_id_record.to_organization_id,organization_id); -- Bug 12985791
* check below. Similarly changed the select statement and the
* check for nvl(max(item_id),0).
*/
SELECT NVL(MAX(organization_id), -9999)
INTO x_organization_id
FROM mtl_system_items
WHERE inventory_item_id = x_item_id_record.item_id
AND organization_id = NVL(x_item_id_record.to_organization_id, organization_id);
SELECT NVL(MAX(inventory_item_id), -9999)
INTO x_item_id_po
FROM oe_order_lines_all
WHERE line_id = x_item_id_record.po_line_id
AND inventory_item_id = x_item_id_record.item_id;
SELECT NVL(MAX(inventory_item_id), -9999)
INTO x_item_id_po
FROM oe_order_lines_all
WHERE line_id = x_item_id_record.po_line_id
AND inventory_item_id = x_item_id_record.item_id;
SELECT DECODE(msi.revision_qty_control_code,
1, 'N',
2, 'Y',
'N'
)
INTO x_revision_control_flag
FROM mtl_system_items msi
WHERE inventory_item_id = x_item_revision_record.item_id
AND organization_id = x_item_revision_record.to_organization_id;
SELECT NVL(MAX(line_id), 0)
INTO x_inventory_item
FROM oe_order_lines_all
WHERE line_id = x_item_revision_record.po_line_id
AND NVL(item_revision, x_item_revision_record.item_revision) = x_item_revision_record.item_revision;
SELECT NVL(MAX(inventory_item_id), 0)
INTO x_inventory_item
FROM mtl_item_revisions
WHERE inventory_item_id = x_item_revision_record.item_id
AND organization_id = NVL(x_item_revision_record.to_organization_id, organization_id)
AND revision = x_item_revision_record.item_revision;
SELECT NVL(MAX(inventory_item_id), 0) -- does this accurately check for active revisions??
INTO x_inventory_item
FROM MTL_ITEM_REVISIONS_B mir --Bug 5217526. Earlier using mtl_item_revisions_org_val_v
WHERE mir.inventory_item_id = x_item_revision_record.item_id
AND mir.organization_id = NVL(x_item_revision_record.to_organization_id, mir.organization_id)
AND mir.revision = x_item_revision_record.item_revision;
SELECT NVL(MAX(line_id), 0)
INTO x_inventory_item
FROM oe_order_lines_all
WHERE line_id = x_item_revision_record.po_line_id
AND NVL(item_revision, x_item_revision_record.item_revision) = x_item_revision_record.item_revision;
SELECT NVL(MAX(oel.line_id), 0)
INTO x_order_line_id
FROM oe_order_lines_all oel,
mtl_customer_items mci
WHERE oel.line_id = x_cascaded_table(n).oe_order_line_id
AND oel.ordered_item_id = mci.customer_item_id
AND mci.customer_item_number = x_cascaded_table(n).customer_item_num;
SELECT (NVL(oeh.sold_to_org_id, 0))
INTO x_customer_id
FROM oe_order_headers_all oeh
WHERE oeh.header_id = x_cascaded_table(n).oe_order_header_id;
SELECT NVL(MAX(unit_of_measure), 'notfound')
INTO x_unit_of_measure
FROM mtl_units_of_measure
WHERE unit_of_measure = x_uom_record.unit_of_measure;
SELECT primary_unit_of_measure
INTO x_primary_unit_of_measure
FROM mtl_system_items_kfv
WHERE inventory_item_id = x_uom_record.item_id
AND organization_id = NVL(x_uom_record.to_organization_id, organization_id); -- Raj added as org_id is part of uk
SELECT NVL(MAX(order_quantity_uom), 'notfound')
INTO x_unit_meas_lookup_code_lines
FROM oe_order_lines_all
WHERE line_id = x_uom_record.po_line_id;
SELECT DECODE(msi.revision_qty_control_code,
1, 'N',
2, 'Y',
'N'
)
INTO x_revision_control_flag
FROM mtl_system_items msi
WHERE inventory_item_id = x_item_revision_record.item_id
AND organization_id = x_item_revision_record.to_organization_id;
SELECT item_revision
INTO x_item_revision_record.item_revision
FROM oe_order_lines_all
WHERE oe_order_lines_all.line_id = x_item_revision_record.po_line_id;
SELECT MAX(location_id)
INTO x_location_id_record.location_id
FROM hr_locations
WHERE location_code = x_location_id_record.location_code;
SELECT MAX(ml.inventory_location_id)
INTO x_locator_id_record.locator_id
FROM mtl_item_locations_kfv ml
WHERE ml.concatenated_segments = x_locator_id_record.LOCATOR
AND ( ml.disable_date > SYSDATE
OR ml.disable_date IS NULL)
AND ml.subinventory_code IS NULL;
SELECT MAX(ml.inventory_location_id)
INTO x_locator_id_record.locator_id
FROM mtl_item_locations_kfv ml
WHERE ml.concatenated_segments = x_locator_id_record.LOCATOR
AND ( ml.disable_date > SYSDATE
OR ml.disable_date IS NULL)
AND ml.subinventory_code = x_locator_id_record.subinventory;
SELECT MAX(routing_header_id)
INTO x_routing_header_id_record.routing_header_id
FROM rcv_routing_headers
WHERE routing_name = x_routing_header_id_record.routing_code;
SELECT MAX(routing_step_id)
INTO x_routing_step_id_record.routing_step_id
FROM rcv_routing_steps
WHERE step_name = x_routing_step_id_record.routing_step;
SELECT MAX(reason_id)
INTO x_reason_id_record.reason_id
FROM mtl_transaction_reasons
WHERE reason_name = x_reason_id_record.reason_name;
SELECT MIN(inventory_item_id),
MIN(primary_unit_of_measure),
MIN(lot_control_code), -- bug 608353
MIN(serial_number_control_code)
INTO x_item_id_record.item_id,
x_item_id_record.primary_unit_of_measure,
x_item_id_record.use_mtl_lot, -- bug 608353
x_item_id_record.use_mtl_serial
FROM mtl_item_flexfields
WHERE item_number = x_item_id_record.item_num
AND organization_id = x_item_id_record.to_organization_id;
SELECT MIN(inventory_item_id),
MIN(primary_unit_of_measure),
MIN(lot_control_code), -- bug 608353
MIN(serial_number_control_code)
INTO x_item_id_record.item_id,
x_item_id_record.primary_unit_of_measure,
x_item_id_record.use_mtl_lot,
x_item_id_record.use_mtl_serial
FROM mtl_item_flexfields
WHERE item_number = x_item_id_record.vendor_item_num
AND organization_id = x_item_id_record.to_organization_id;
SELECT inventory_organization_id
INTO x_organization_id
FROM hr_locations
WHERE location_id = p_hr_location_id;