[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NAME
FROM v$database;
SELECT rtrim(ltrim(value)) from v$parameter
WHERE lower(name) = 'utl_file_dir';
SELECT user_name
, email_address
FROM fnd_user
WHERE user_id = p_user_id;
SELECT NAME
FROM v$database;
|| ', MSN last update date:'
|| g_dc_table(k).msn_last_update_date
|| ', WLPN LPN context:'
|| g_dc_table(k).wlpn_lpn_context
|| ', From org:'
|| g_dc_table(k).from_organization_code
|| ')';
g_dc_table.DELETE;
SELECT 'RHI IN RUNNING WHEN THERE IS A ROW IN RSH'
, rhi.receipt_header_id
, rhi.ship_to_organization_id
, rhi.from_organization_id
, rhi.transaction_type
, rhi.processing_status_code
, rhi.receipt_source_code
, rhi.asn_type rhi_asn_type
, rsh.asn_type rsh_asn_type
, rhi.creation_date
FROM rcv_headers_interface rhi, rcv_shipment_headers rsh
WHERE rhi.header_interface_id = p_header_interface_id
AND rsh.shipment_header_id = rhi.receipt_header_id
AND rhi.processing_status_code IN('RUNNING', 'PENDING')
AND rhi.receipt_source_code in ('VENDOR','CUSTOMER')
AND nvl(rhi.asn_type, 'STD') = 'STD'
AND nvl(rsh.asn_type, '&&&&') NOT IN ('ASN','ASBN') ;
SELECT 'Shipment line exists without a shipment header'
, rsl.shipment_line_id
, rti.item_id
, rti.to_organization_id
, rti.from_organization_id
, rti.transaction_type
FROM rcv_shipment_lines rsl, rcv_transactions_interface rti
WHERE rti.interface_transaction_id = p_interface_transaction_id
AND rti.shipment_line_id = rsl.shipment_line_id
AND NOT EXISTS(SELECT 1
FROM rcv_shipment_headers rsh
WHERE rsh.shipment_header_id = rsl.shipment_header_id);
SELECT 'Shipment exists for Int Shp. or Internal Order without MMT'
, rsh.shipment_header_id
, rsl.shipment_line_id
, rsl.mmt_transaction_id
, rti.item_id
, rti.to_organization_id
, rti.from_organization_id
, rti.transaction_type
FROM rcv_shipment_headers rsh, rcv_shipment_lines rsl, rcv_transactions_interface rti
WHERE rti.interface_transaction_id = p_interface_transaction_id
AND rti.shipment_line_id = rsl.shipment_line_id
AND rsh.shipment_header_id = rsl.shipment_header_id
AND rsh.receipt_source_code IN('INTERNAL ORDER', 'INVENTORY')
AND rsl.mmt_transaction_id IS NOT NULL
AND NOT EXISTS(SELECT 1
FROM mtl_material_transactions mmt
WHERE rsl.mmt_transaction_id = mmt.transaction_id);*/
SELECT 'Flow status code is not awaiting return disposition for RMA receipt'
, rt.transaction_id
, oel.line_id
, rti.item_id
, rti.to_organization_id
, rti.from_organization_id
, rti.transaction_type
, oel.flow_status_code
FROM oe_order_lines_all oel, rcv_transactions rt, rcv_transactions_interface rti
WHERE rti.interface_transaction_id = p_interface_transaction_id
AND rti.interface_transaction_id = rt.interface_transaction_id
AND oel.line_id = rt.oe_order_line_id
AND rti.receipt_source_code = 'CUSTOMER'
AND rt.transaction_type = 'RECEIVE'
AND nvl(rti.auto_transact_code, 'RECEIVE') = 'RECEIVE'
AND oel.flow_status_code = 'AWAITING_RETURN';
SELECT 'Flow status code is not returned for RMA delivery'
, rt.transaction_id
, oel.line_id
, rti.item_id
, rti.to_organization_id
, rti.from_organization_id
, rti.transaction_type
, oel.flow_status_code
FROM oe_order_lines_all oel, rcv_transactions rt, rcv_transactions_interface rti
WHERE rti.interface_transaction_id = p_interface_transaction_id
AND rti.interface_transaction_id = rt.interface_transaction_id
AND oel.line_id = rt.oe_order_line_id
AND rti.receipt_source_code = 'CUSTOMER'
AND rt.transaction_type = 'DELIVER'
AND oel.flow_status_code IN ('AWAITING_RETURN', 'AWAITING_RETURN_DISPOSITION')
AND oel.shipped_quantity = oel.fulfilled_quantity;
SELECT 'MSN group mark id not null'
, msn.serial_number
, msn.inventory_item_id
, msn.current_organization_id
, rti.transaction_type
, msn.last_update_date
, msn.current_status
, wlpn.lpn_context
FROM mtl_serial_numbers msn,
rcv_transactions_interface rti,
mtl_serial_numbers_temp msnt,
wms_license_plate_numbers wlpn
WHERE rti.interface_transaction_id = p_interface_transaction_id
AND msnt.product_code = 'RCV'
AND msnt.product_transaction_id = rti.interface_transaction_id
AND msn.serial_number between msnt.fm_serial_number and msnt.to_serial_number
AND msn.inventory_item_id = rti.item_id
AND msn.current_organization_id = rti.to_organization_id
AND nvl(msn.lpn_id, -1) = wlpn.lpn_id (+)
AND (wlpn.lpn_context is null or wlpn.lpn_context in (1,3))
AND NVL(msn.group_mark_id ,-1) <> -1
UNION ALL
SELECT 'MSN group mark id not null'
, msn.serial_number
, msn.inventory_item_id
, msn.current_organization_id
, rt.transaction_type
, msn.last_update_date
, msn.current_status
, wlpn.lpn_context
FROM mtl_serial_numbers msn,
rcv_transactions rt,
mtl_unit_transactions mut,
wms_license_plate_numbers wlpn
WHERE rt.interface_transaction_id = p_interface_transaction_id
AND mut.product_code ='RCV'
AND mut.product_transaction_id = rt.transaction_id
AND msn.serial_number = mut.serial_number
AND msn.inventory_item_id = mut.inventory_item_id
AND msn.current_organization_id = mut.organization_id
AND nvl(msn.lpn_id, -1) = wlpn.lpn_id (+)
AND (wlpn.lpn_context is null or wlpn.lpn_context in (1,3))
AND NVL(msn.group_mark_id ,-1) <> -1
AND not exists
(select 1 from rcv_transactions rt1
where rt1.parent_transaction_id = rt.transaction_id);
SELECT 'MOL exists with invalid wms_process_flag'
, mol.line_id
, moh.header_id
, mol.inventory_item_id
, rt.organization_id
, rt.transaction_type
FROM mtl_txn_request_lines mol, mtl_txn_request_headers moh, rcv_transactions rt
WHERE rt.interface_transaction_id = p_interface_transaction_id
AND rt.transaction_type = 'RECEIVE'
AND (mol.lpn_id = rt.transfer_lpn_id
OR mol.lpn_id = rt.lpn_id)
AND mol.line_status <> 5
AND mol.header_id = moh.header_id
AND moh.move_order_type = 6
AND mol.wms_process_flag = 2;
SELECT organization_code
FROM org_organization_definitions
WHERE organization_id = p_org_id;
SELECT concatenated_segments
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id;
g_dc_table(i).msn_last_update_date := c6_rec.last_update_date;
SELECT *
FROM rcv_headers_interface
WHERE NVL(asn_type, 'STD') IN('ASN', 'ASBN', 'STD', 'WC')
AND processing_status_code IN('RUNNING', 'SUCCESS','ERROR','PENDING')
AND(NVL(validation_flag, 'N') = 'Y'
OR processing_status_code = 'SUCCESS') -- include success row for multi-line asn
AND(processing_request_id IS NULL
OR processing_request_id = x_request_id)
AND GROUP_ID = DECODE(x_group_id, 0, GROUP_ID, x_group_id);
SELECT distinct interface_transaction_id
FROM rcv_transactions
WHERE lpn_group_id = x_lpn_group_id;
SELECT distinct interface_transaction_id
FROM rcv_transactions
WHERE group_id = x_group_id
AND lpn_group_id is null;