[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
-- select transaction_type_id into X_order_type_id
-- from so_order_types
-- where name = X_order_type;
select unit_of_measure into X_day_uom
from mtl_units_of_measure
where uom_code = X_day_uom_code;
select unit_of_measure into X_month_uom
from mtl_units_of_measure
where uom_code = X_month_uom_code;
/* Get the default order by selection and code. */
procedure Get_Default_Order_by(x_order_by_code IN VARCHAR2,
x_order_by_lookup_type IN VARCHAR2,
x_order_by OUT NOCOPY VARCHAR2 ) IS
BEGIN
SELECT Meaning
INTO X_order_by
FROM CS_LOOKUPS
WHERE lookup_type = x_order_by_lookup_type
AND lookup_code = x_order_by_code;
select name
into mfg_org_name
from hr_organization_units
where organization_id = mfg_org_id;
select customer_id,address_id
into l_customer_id,l_address_id
from ra_contacts
where contact_id = p_contact_id;
select decode(area_code,null,null,'('||area_code||')')||
phone_number||
decode(extension,null,null,' x'||extension)
into l_phone_no
from ra_phones
where contact_id = p_contact_id
and customer_id = l_customer_id
and address_id is null
and primary_flag = 'Y'
and status = 'A';
select decode(area_code,null,null,'('||area_code||')')||
phone_number||
decode(extension,null,null,' x'||extension)
into l_phone_no
from ra_phones
where contact_id = p_contact_id
and customer_id = l_customer_id
and address_id = l_address_id
and primary_flag = 'Y'
and status = 'A';
select description
into l_rev_desc
from mtl_item_revisions
where organization_id = p_org_id
and inventory_item_id = p_inv_item_id
and revision = p_revision;
select location
into l_location
from ra_site_uses
where site_use_id = p_site_use_id;
select party_name
into l_cust_name
from hz_parties
where party_id = p_customer_id;
select 'Y' INTO return_val
from cs_cp_services CSS
where css.customer_product_id = cp_id
and css.warranty_flag='Y' ;
select bic.component_item_id war_item_id
from bom_inventory_components bic
where bic.bill_sequence_id = c_bill_seq_id
--
--Fix to bug#479703. vharihar 4/16/97.
-- and trunc(l_war_date) between trunc(bic.effectivity_date) and
-- trunc(nvl(bic.disable_date,l_war_date))
and l_war_date >= bic.effectivity_date
and l_war_date < nvl(bic.disable_date,l_war_date+1)
--
and exists
(
select 'Component is a Warranty'
from mtl_system_items mtl
where mtl.organization_id = p_organization_id
and mtl.inventory_item_id = bic.component_item_id
and mtl.vendor_warranty_flag = 'Y'
)
order by bic.component_item_id;
select common_bill_sequence_id
into l_com_bill_seq_id
from bom_bill_of_materials
where organization_id = p_organization_id
and assembly_item_id = p_inventory_item_id
and alternate_bom_designator is null;
select name
into l_system_name
from cs_systems
where system_id = p_system_id;
select su1.location,
su1.site_use_id,
addr1.address1 || DECODE(ADDR1.ADDRESS1,'','',
DECODE(ADDR1.ADDRESS2,'','',', ')) ||
ADDR1.address2 ,
addr1.address3 || DECODE(addr1.address3,'','',
DECODE(addr1.address4,'','',', ')) ||
ADDR1.address4 ,
SUBSTR(ADDR1.CITY || DECODE(ADDR1.CITY,'','',
DECODE(ADDR1.STATE || ADDR1.COUNTRY || ADDR1.POSTAL_CODE,'','',
', ')) || ADDR1.STATE || ' ' ||
ADDR1.POSTAL_CODE || ' ' || ADDR1.COUNTRY,1,220)
INTO x_location,
x_site_use_id,
x_address1,
x_address2,
x_address3
FROM RA_SITE_USES su1,
RA_ADDRESSES ADDR1
WHERE addr1.customer_id = x_id
AND addr1.address_id = su1.address_id
AND su1.primary_flag = 'Y'
AND su1.status = 'A'
AND su1.site_use_code = x_site_use_code ;
SELECT trunc(nvl(serv.start_date_active, sysdate)) start_date_active,
trunc(nvl(serv.end_date_active, sysdate)) end_date_active
FROM cs_cp_services serv
WHERE serv.customer_product_id = X_CP_ID;
fnd_msg_pub.delete_msg ;
select su1.location,
addr1.address1 || DECODE(ADDR1.ADDRESS1,'','',
DECODE(ADDR1.ADDRESS2,'','',', ')) ||
ADDR1.address2 ,
addr1.address3 || DECODE(addr1.address3,'','',
DECODE(addr1.address4,'','',', ')) ||
ADDR1.address4 ,
SUBSTR(ADDR1.CITY || DECODE(ADDR1.CITY,'','',
DECODE(ADDR1.STATE || ADDR1.COUNTRY || ADDR1.POSTAL_CODE,'','',
', ')) || ADDR1.STATE || ' ' ||
ADDR1.POSTAL_CODE || ' ' || ADDR1.COUNTRY,1,220)
INTO x_location,
x_address1,
x_address2,
x_address3
FROM RA_SITE_USES su1,
RA_ADDRESSES ADDR1
WHERE su1.site_use_id = x_id
AND addr1.address_id = su1.address_id ;
select mc.description
into l_cat
from
mtl_categories mc,
mtl_default_category_sets mdc,
mtl_item_categories mic
where mic.inventory_item_id = p_inv_item_id
and mic.organization_id = p_inv_orgn_id
and mdc.functional_area_id+0 = 7
and mic.category_set_id = mdc.category_set_id
and mc.category_id = mic.category_id;
SELECT address1, address2, address3, address4, city, state, postal_code, country
INTO temp_address1, temp_address2, temp_address3, temp_address4, temp_city,
temp_state, temp_code, temp_country
FROM CS_RA_ADDR_LOC_RG_V
WHERE site_use_id = site_id;
select decode(phone_country_code,'','',phone_country_code || '-' ) ||
decode(phone_area_code,'','',phone_area_code || '-' ) || phone_number
into l_phone
from (select phone_number, phone_area_code, phone_country_code
from hz_contact_points
where owner_table_id = party_id
and owner_table_name ='HZ_PARTY_SITES'
and contact_point_type = 'PHONE'
order by primary_flag desc, creation_date asc)
where rownum = 1;
select TRANSPOSED_PHONE_NUMBER into l_phone
from (select reverse(TRANSPOSED_PHONE_NUMBER)
TRANSPOSED_PHONE_NUMBER
from hz_contact_points
where owner_table_id = party_id
and owner_table_name ='HZ_PARTY_SITES'
and contact_point_type = 'PHONE'
order by primary_flag desc, creation_date asc)
where rownum = 1;
select
decode(created_by_module,'SR_ONETIME' ,'UnValidAddrXRN','ValidXAddrRN') into addrswitch
from hz_party_sites
where party_site_id = incident_location_id;
select phone_number
into ret_val
from per_phones where phone_type='M'
and parent_id= p_resource_id and rownum =1;
select phone_number
into ret_val
from per_phones where phone_type='W1'
and parent_id= p_resource_id
and date_to is null and rownum =1;
select email_address
into ret_val
from per_all_people_f where person_id = p_resource_id
and sysdate between effective_start_date and effective_end_date;
FUNCTION GetProfileLastUpdate(p_party_id NUMBER,
p_level VARCHAR2,
p_account_id NUMBER DEFAULT NULL,
p_site_id NUMBER DEFAULT NULL
) RETURN DATE IS
v_date DATE := NULL;
SELECT MAX(last_update_date)
FROM csc_prof_check_results
WHERE party_id = p_party_id
AND cust_account_id IS NULL;
SELECT MAX(last_update_date)
FROM csc_prof_check_results
WHERE party_id = p_party_id
AND cust_account_id = p_account_id;
SELECT MAX(last_update_date)
FROM csc_prof_check_results
WHERE party_site_id = p_party_site_id
AND party_id = p_party_id
AND cust_account_id IS NULL;
END GetProfileLastUpdate;
SELECT block_name ,
sql_stmnt_for_drilldown,
object_code
FROM csc_prof_blocks_vl
WHERE block_id = vblockID;
SELECT COLUMN_NAME,
COLUMN_SEQUENCE,
REPLACE(LABEL,' ','') LABEL
FROM csc_prof_table_columns_vl
WHERE block_id = vblockID
ORDER BY column_sequence;