[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT soh.currency_code ,
soh.order_category
FROM so_headers_all soh,
so_picking_headers_all soph
WHERE soph.picking_header_id = X_picking_header_id
AND soh.header_id = soph.order_header_id;
SELECT pick_slip_number
FROM so_picking_headers_all
WHERE picking_header_id = X_Picking_Header_id
AND status_code = 'OPEN';
SELECT
decode(msi.restrict_subinventories_code,1,'Y','N') ,
decode(msi.revision_qty_control_code,2,'Y','N') ,
decode(msi.lot_control_code,2,'Y',3,'Y','N'),
decode(msi.serial_number_control_code,
-- 2=Predefined serial# 5=Dynamic at inv. receipt 6=Dynamic at sales issue
2,'Y', 5,'Y', 6,decode(X_order_category,'P','N','D'),'N')
from mtl_system_items msi
where msi.inventory_item_id = X_item_id
and msi.organization_id = X_warehouse_id;
select description
from mtl_secondary_inventories
where organization_id = X_warehouse_id
and quantity_tracked = 1
and trunc(sysdate) <= nvl( disable_date, trunc(sysdate) )
and secondary_inventory_name = x_subinventory;
select mtlsub.description
from mtl_item_sub_inventories mtlisi
, mtl_secondary_inventories mtlsub
where mtlisi.organization_id = X_warehouse_id
and mtlisi.inventory_item_id = X_item_id
and mtlsub.organization_id = X_warehouse_id
and mtlsub.secondary_inventory_name = mtlisi.secondary_inventory
and mtlsub.quantity_tracked = 1
and trunc(sysdate) <= nvl( mtlsub.disable_date, trunc(sysdate) )
and mtlsub.secondary_inventory_name = x_subinventory;
select mtlsub.secondary_inventory_name
from mtl_item_sub_defaults mtlisd,
mtl_secondary_inventories mtlsub
where mtlisd.inventory_item_id = X_item_id
and mtlisd.organization_id = X_warehouse_id
and mtlisd.default_type = 1
and mtlsub.organization_id = mtlisd.organization_id
and mtlsub.secondary_inventory_name = mtlisd.subinventory_code
and mtlsub.quantity_tracked = 1
and trunc(sysdate) <= nvl( mtlsub.disable_date, trunc(sysdate));
select decode( nvl( mtlpar.stock_locator_control_code, 1 ),
1, 'N', 2, 'Y', 3, 'D',
4, DECODE( NVL( mtlsin.locator_type, 1 ),
1, 'N', 2, 'Y', 3, 'D', 4, 'N',
5, DECODE( NVL( mtlsis.location_control_code, 1 ),
1, 'N', 2, 'Y', 3, 'D', 'N' ),
'N' ),
5, DECODE( NVL( mtlsis.location_control_code, 1 ),
1, 'N', 2, 'Y', 3, 'D', 'N' ),
'N' )
, DECODE( mtlsis.restrict_locators_code, 1, 'Y', 'N' )
from mtl_parameters mtlpar
, mtl_secondary_inventories mtlsin
, mtl_system_items mtlsis
where mtlpar.organization_id = X_warehouse_id
and mtlsin.organization_id = mtlpar.organization_id
and mtlsin.secondary_inventory_name = X_subinventory
and mtlsis.organization_id = mtlpar.organization_id
and mtlsis.inventory_item_id = X_item_id;
select 'lot number valid'
from mtl_onhand_quantities
where inventory_item_id = X_item_id
and organization_id = X_warehouse_id
and nvl(subinventory_code,'X') = nvl(X_subinventory,'X')
and lot_number = X_lot_number;
select 'revision is valid'
from mtl_item_revisions
where organization_id = X_warehouse_id
and inventory_item_id = X_item_id
and revision = X_revision;
select 'valid locator id'
from mtl_item_locations mtlloc
where organization_id = X_warehouse_id
and mtlloc.inventory_location_id = X_locator_id
and ( nvl(X_location_restricted_flag, 'N') = 'N'
or
(nvl(X_location_restricted_flag, 'N') = 'Y'
and nvl(mtlloc.inventory_location_id, -1) in (
select mtlsls.secondary_locator
from mtl_secondary_locators mtlsls
where mtlsls.organization_id = X_warehouse_id
and mtlsls.inventory_item_id = X_item_id
and mtlsls.subinventory_code = X_subinventory)));
select mtldl.locator_id
from mtl_item_loc_defaults mtldl
where mtldl.inventory_item_id = X_item_id
and mtldl.organization_id = X_warehouse_id
and mtldl.default_type = 1
and mtldl.subinventory_code = X_subinventory
and ( nvl(X_location_restricted_flag, 'N') = 'N'
OR
(nvl(X_location_restricted_flag, 'N') = 'Y'
and nvl(mtldl.locator_id, -1) in
(select mtlsls.secondary_locator
from mtl_secondary_locators mtlsls
where mtlsls.organization_id = X_warehouse_id
and mtlsls.inventory_item_id = X_item_id
and mtlsls.subinventory_code = X_subinventory)));
SELECT
'valid serial number', S.CURRENT_STATUS
FROM MTL_SERIAL_NUMBERS S,
MTL_ITEM_LOCATIONS LOC
WHERE S.CURRENT_ORGANIZATION_ID = x_warehouse_id
AND S.INVENTORY_ITEM_ID = x_item_id
AND NVL( S.REVISION, '~' ) = NVL( x_revision, '~' )
AND NVL( S.LOT_NUMBER, '~' ) = NVL( x_lot_number, '~' )
AND S.SERIAL_NUMBER = x_serial_number
AND S.CURRENT_ORGANIZATION_ID = LOC.ORGANIZATION_ID(+)
-- if not under locator control then -1 = -1 else s.current_loc=x_loc
AND DECODE(x_location_control_flag,
'Y', NVL(S.CURRENT_LOCATOR_ID, -1),
'D', NVL(S.CURRENT_LOCATOR_ID, -1),
-1) =
DECODE(x_locator_id,
'', DECODE(x_location_control_flag,
'Y', LOC.INVENTORY_LOCATION_ID,
'D', LOC.INVENTORY_LOCATION_ID, -1),
x_locator_id)
AND NVL(S.CURRENT_LOCATOR_ID, -1) = LOC.INVENTORY_LOCATION_ID(+)
-- if restricted loc then check in mtl_sec_loc
AND (NVL(x_location_restricted_flag, 'N') = 'N' OR
(NVL(x_location_restricted_flag, 'N') = 'Y'
AND
NVL(LOC.INVENTORY_LOCATION_ID, -1) IN (
SELECT NVL(MAX(MTLSLS.SECONDARY_LOCATOR),-1)
FROM MTL_SECONDARY_LOCATORS MTLSLS
WHERE MTLSLS.ORGANIZATION_ID = x_warehouse_id
AND MTLSLS.INVENTORY_ITEM_ID = x_item_id
AND MTLSLS.SUBINVENTORY_CODE = x_subinventory
-- Bug 842175
AND MTLSLS.SECONDARY_LOCATOR = x_locator_id )))
AND S.CURRENT_SUBINVENTORY_CODE = x_subinventory
AND S.CURRENT_STATUS IN (
DECODE( x_serial_number_control_flag, 'Y', 3, -1 ),
DECODE( x_serial_number_control_flag, 'D', 3, -1 ),
DECODE( x_serial_number_control_flag, 'D', 1, -1 ),
DECODE( x_serial_number_control_flag, 'D', 4, -1 ),
DECODE( x_serial_number_control_flag, 'D', 5, -1 ));
select 'exists'
from so_picking_lines_all pl, so_picking_line_details pld
where pl.picking_line_id = pld.picking_line_id
and pld.delivery_id = del_id
and pl.ra_interface_status is not null
and exists( select 'interfaced lines exist'
from so_lines_all l
where l.s5 in (5,8,9)
and l.line_id = pl.order_line_id);
SELECT freight_charge_type_id
FROM so_freight_charge_types
WHERE freight_charge_type_id = nvl(X_in_id, freight_charge_type_id)
AND freight_charge_type_code = decode(X_in_id,null,X_in_code,freight_charge_type_code )
AND nvl(start_date_active,sysdate) <= sysdate
AND nvl(end_date_active,sysdate) >= sysdate;
SELECT 'valid'
FROM org_freight
WHERE organization_id = x_organization_id
AND freight_code = x_carrier_code
AND nvl(disable_date, sysdate) >= sysdate;
SELECT container_id
FROM wsh_packed_containers
WHERE container_id = nvl(x_container_id, container_id)
AND sequence_number = decode(x_container_id, null, x_sequence_number, sequence_number)
AND delivery_id = x_delivery_id;
SELECT currency_code,
name,
nvl(precision,0),
decode(instr(to_char(nvl(x_amount,0)),'.'),0,0,
length(to_char(nvl(x_amount,0)))-
instr(to_char(nvl(x_amount,0)),'.'))
FROM fnd_currencies_VL
WHERE enabled_flag = 'Y'
AND name = decode( X_in_code, null, X_in_name, name)
AND currency_code = nvl( X_in_code, currency_code)
AND trunc(sysdate) between nvl( start_date_active, trunc(sysdate) )
and nvl( end_date_active, trunc(sysdate) );
SELECT uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = decode(uomcode,'',uomdesc,unit_of_measure)
AND uom_code = nvl(uomcode,uom_code)
AND uom_class = NVL(class,uom_class)
AND nvl(disable_date,sysdate) >= sysdate;
SELECT user_id
FROM fnd_user
WHERE user_id = NVL(userid,user_id)
AND user_name = DECODE(userid,null,username,user_name)
AND nvl(start_date , sysdate) <= sysdate
AND nvl(end_date,sysdate) >= sysdate;
PROCEDURE UPDATE_SHIPPING_ONLINE
(x_picking_header_id in number,
x_batch_id in number) IS
BEGIN
declare x_dummy varchar2(250);
FND_MESSAGE.Set_Token('PROCESS','OE_SH_UPDATE_SHIPPING_INFO');
END UPDATE_SHIPPING_ONLINE;
SELECT 'SHIP_DIFF_ORG'
FROM so_headers_all h,
so_picking_headers_all ph
WHERE h.header_id = ph.order_header_id
AND ph.picking_header_id = X_picking_header_id
AND nvl(h.org_id,-99) <>
(SELECT nvl(h2.org_id,-99)
FROM so_headers h2
WHERE rownum = 1);
SELECT lookup_code
FROM so_lookups
WHERE lookup_type = X_LOOKUP_TYPE
AND lookup_code = nvl(X_code,lookup_code)
AND meaning = decode(X_code,null,X_meaning,meaning)
AND nvl(start_date_active,sysdate) <= sysdate
AND nvl(end_date_active,sysdate) >= sysdate
AND enabled_flag = 'Y';