************************************************************
Query to Get schedule Concurrent Program
*************************************************************
SELECT r.request_id, CASE WHEN pt.user_concurrent_program_name
= 'Report Set' THEN DECODE
(r.description,
NULL, pt.user_concurrent_program_name, r.description || ' (' ||
pt.user_concurrent_program_name || ')' ) ELSE
pt.user_concurrent_program_name END
job_name, u.user_name requestor,
u.description requestor_description,
u.email_address, frt.responsibility_name requested_by_resp, r.request_date,
r.requested_start_date, DECODE
(r.hold_flag, 'y', 'yes', 'n', 'no') on_hold, CASE WHEN r.hold_flag = 'y' THEN SUBSTR (u2.description, 0,
40) END last_update_by, CASE WHEN r.hold_flag = 'y' THEN r.last_update_date END last_update_date, r.argument_text
PARAMETERS, NVL2
(r.resubmit_interval,
'periodically', NVL2 (r.release_class_id,
'On specific days', 'Once') )
AS schedule_type,
r.resubmit_interval resubmit_every,
r.resubmit_interval_unit_code resubmit_time_period, DECODE
(r.resubmit_interval_type_code,
'START', 'From the start of the prior run', 'END', 'From the Completion of
the prior run' )
apply_the_update_option, r.increment_dates, TO_CHAR ((r.requested_start_date),
'HH24:MI:SS') start_time FROM
applsys.fnd_concurrent_programs_tl pt,
applsys.fnd_concurrent_programs pb,
applsys.fnd_user u,
applsys.fnd_user u2,
applsys.fnd_printer_styles_tl s,
applsys.fnd_concurrent_requests r,
applsys.fnd_responsibility_tl frt WHERE pb.application_id = r.program_application_id AND pb.concurrent_program_id =
r.concurrent_program_id AND
pb.application_id = pt.application_id
AND r.responsibility_id = frt.responsibility_id AND pb.concurrent_program_id =
pt.concurrent_program_id AND u.user_id
= r.requested_by AND u2.user_id =
r.last_updated_by AND
s.printer_style_name(+) = r.print_style
AND r.phase_code = 'P'-- AND
pb.concurrent_program_name = :p_concurrent_short_name
*************************************************************
Below is a customized function which takes two dates as
parameters and return the difference between them but after excluding/ignoring
the holidays.
Note: considering Friday and Saturday are holiday, you
can change it in the code according to the holidays in your project country.
*************************************************************
CREATE OR REPLACE FUNCTION apps.xx_remove_holidays (p_date1 DATE, p_date2
DATE) RETURN NUMBER-- Author : Mahmoud
Ezz El-Din -------------------------------------------------------------------------------------
-- Created: 21-jan-2010
--------------------------------------------------------------------------------------------
-- Purpose: To get the number of days between two dates without (Fridays
and Saturdays). ---------------------------
IS
v_date1 DATE;
v_date2 DATE;
v_no_of_days_without_holidays NUMBER;
v_no_of_days_with_holidays NUMBER;
BEGIN
IF (p_date1 IS NULL) OR (p_date2
IS NULL) OR (p_date2 > p_date1)
THEN
RETURN (0);
ELSE
v_date1 := TO_DATE (p_date1,
'dd/mm/rrrr');
v_date2 := TO_DATE (p_date2,
'dd/mm/rrrr');
v_no_of_days_without_holidays
:= v_date1 - v_date2;
v_no_of_days_with_holidays :=
v_date1 - v_date2;
FOR i IN 1 .. v_no_of_days_with_holidays
LOOP
IF UPPER (TRIM (TO_CHAR (v_date1, 'DAY'))) =
UPPER ('saturday')
OR UPPER (TRIM (TO_CHAR
(v_date1, 'DAY'))) = UPPER ('friday')
THEN
v_no_of_days_without_holidays :=
v_no_of_days_without_holidays - 1;
END IF;
v_date1 := v_date1 - 1;
END LOOP;
RETURN (NVL
(v_no_of_days_without_holidays, 0));
END IF;
END;
/
*************************************************************
SQL to get the request sets that can execute the required
concurrent program
*************************************************************
SELECT frs.user_request_set_name "Request Set Name",
frs.start_date_active "Request set Start Date", frs.end_date_active "Request set
End Date", frsf.stage_name, frsf.user_stage_name, frsp.SEQUENCE
"Stage Request Sequence",
fcp.user_concurrent_program_name "Concurrent Program" FROM fnd_request_sets_vl frs,
fnd_req_set_stages_form_v frsf, fnd_request_set_programs frsp,
fnd_concurrent_programs_tl fcp WHERE frs.request_set_id =
frsf.request_set_id AND
frsf.request_set_stage_id = frsp.request_set_stage_id AND frsp.concurrent_program_id =
fcp.concurrent_program_id AND
fcp.user_concurrent_program_name = 'NAME OF PROG'
*************************************************************
# Oracle EBS SQL Query to pull down all flexfield# values
based upon a specified flex value set name.
********************************************************
SELECT fv.*FROM applsys.fnd_flex_values fv,applsys.fnd_flex_value_sets
fvsWHERE fvs.flex_value_set_id = fv.flex_value_set_idAND
fvs.flex_value_set_name = '<FLEX_FIELD_VALUE_NAME>';
********************************************************
Background:Run the following script to know who are all
having access to a one particular responsibility
*************************************************************
Script:
SELECT fu.user_id, fu.user_name, fu.email_address FROM fnd_user_resp_groups_direct furg, fnd_user fu, fnd_responsibility_tl fr WHERE
UPPER (fr.responsibility_name) = UPPER ('&Enter_Resp_Name') AND fr.responsibility_id =
furg.responsibility_id AND
furg.user_id = fu.user_id AND
furg.end_date IS NULL AND fu.end_date IS NULL AND fr.language = USERENV
('LANG')ORDER BY fu.user_name;
**************************************
Script to find singing/approval limits assigned to
employeesBackground: Create employee signing limits for expense report approvals.
Managers(Who is an employee) can approve an expense report only if the total
amount of the expense report does not exceed their pre-defined signing limit.
When you assign signing limits to a manager, you also specify a cost center to
which this signing limit applies and you can give managers signing limits for
multiple cost centers.Navigation: Payables Manager: Employees > Signing
LimitsWhat the following script does? Use the following sql script/query to
find Signing Limits you assign to employees who are responsible for approving
expense reports entered in Oracle Self-Service Expenses
*************************************************************
SQL Query/Script:
SELECT pap.full_name, awsla.cost_center, awsla.org_id, hou.name organization_name, awsla.signing_limit FROM ap_web_signing_limits_all awsla, per_all_people_f pap, hr_organization_units hou WHERE
awsla.employee_id = pap.person_id
AND awsla.org_id = hou.organization_id AND pap.effective_start_date = (SELECT
max(pap1.effective_start_date) FROM
apps.per_all_people_f pap1
WHERE pap1.person_id = pap.person_id) AND awsla.document_type = 'APEXP'ORDER
BY hou.name, pap.full_name, awsla.cost_center, awsla.signing_limit;Additional Info:1.
The columns START_DATE, EFFECTIVE_START_DATE and EFFECTIVE_END_DATE of
per_all_people_f table are all maintained by DateTrack. The START_DATE is the
date when the first record for this person was created. The earliest
EFFECTIVE_START_DATE for a person is equal to the START_DATE.2. Table
ap_web_signing_limits_all corresponds to the Signing Limits windowPayables
Manager: Employees > Signing Limits
*************************************************************
Convert Number to WordsBackground:Imagine you are
developing a report for AP module [Infact in any module] and customer wanted to
display Invoice amount in Words, then you can use this solution.
*************************************************************
Solution:
SELECT ap_amount_utilities_pkg.ap_convert_number(12345) AS
amt_in_words FROM
dual;Output:AMT_IN_WORDS---------------------Twelve thousand three hundred
forty-fiveNote:1. Maximum number allowed is (10 Power 12)2. Non Oracle Apps
users can use the following query.
select TO_CHAR(TO_DATE(&enter_a_number, 'J'),'JSP') FROM dual Limitation: Entered number should not
exceed 5373484
********************************
SQL script to find the values of qualifiers of Key Flex
FieldBackground: Run the following SQL query to find which KFF Segment is
assigned with which qualifier
**********************************
SQL Query:
SELECT fifs.ID_FLEX_STRUCTURE_CODE,
fsav.APPLICATION_COLUMN_NAME,
ffsg.SEGMENT_NAME,
fsav.SEGMENT_ATTRIBUTE_TYPE, fsav.ATTRIBUTE_VALUE FROM FND_SEGMENT_ATTRIBUTE_VALUES fsav, FND_ID_FLEX_STRUCTURES fifs, FND_ID_FLEX_SEGMENTS ffsg WHERE fsav.ID_FLEX_NUM = fifs.ID_FLEX_NUM AND ffsg.ID_FLEX_NUM =
fifs.ID_FLEX_NUM AND ffsg.APPLICATION_COLUMN_NAME
= fsav.APPLICATION_COLUMN_NAME AND
fifs.ID_FLEX_STRUCTURE_CODE = '&Flexfield_Code';More Info: For each Segment
you get 6 different rows. ATTRIBUTE_VALUE describes which qualifier is set for
the SegmentFor GL you can get "Flexfield_Code" using below navigation
(Accounting Flexfield)
GL > Setup > Financials > Flexfields > Key >
SegmentsAgain different modules have different navigation & this query
works for any KFF given Flexfield_Code for the above query.Meaning of SEGMENT_ATTRIBUTE_TYPE values:
FA_COST_CTR =>
Cost Center SegmentGL_ACCOUNT => Natural Account
SegmentGL_BALANCING
=> Balancing SegmentGL_INTERCOMPANY => Intercompany SegmentGL_SECONDARY_TRACKING
=> Secondary Tracking Segment
*************************************************************
Query to find Reponsibilities/Users having access to Discoverer
WorkbookBackground:Use the following query to find the Which are
responsibilities/Users having access to Oracle Discoverer Report/Workbook.
Supply valid Workbook Name to the query.
*******************************
SQL Query:
SELECT docs.doc_name,
fnd.responsibility_name,
priv.ap_eu_id, DECODE
(usr.eu_role_flag, 0, 'user', 1, 'role') user_role, usr.eu_username FROM eul5_us.eul5_documents docs, eul5_us.eul5_access_privs priv, eul5_us.eul5_eul_users usr, fnd_responsibility_tl fnd WHERE
docs.doc_id = priv.gd_doc_id(+)
AND priv.ap_eu_id = usr.eu_id(+)
AND usr.eu_username = '#' || fnd.responsibility_id(+) || '#' ||
fnd.application_id(+) AND
priv.ap_type = 'GD' AND
docs.doc_name LIKE '&Workbook_Name';
******************************************
How to kill a Oracle database session Business Case:This
post to help Apps developers to kill the blocked session in the lower instances
without waiting for DBA.For production executions please contact your DBA's
******************************
Run the following query:
select sid, serial#, username, status, server from v$session;
SID SERIAL# USERNAME STATUS SERVER---------- ----------
------------------------------ -------- --------- 23 38 ACTIVE DEDICATED
25 1 ACTIVE DEDICATED
26 10516 SYSTEM ACTIVE DEDICATED 29 93 SRINI02 ACTIVE DEDICATED
Findout the session and kill by passing the SID, SERIAL#
combination.Example:alter system kill session '29, 93';
*****************************
Query to find Values of a lookupBusiness Case:It is
tedious to check values of a lookup from the application.Use this query to find
the values to find out from database.
**********************************
Queries:
Lookup Information:
select * from fnd_lookup_types_VL
Where lookup_type = '&Enter_lookup_type';=> Tip:You can use the above
table to find lookups available in the systemValues of a lookup:select * from fnd_lookup_values Where lookup_type =
'&Enter_lookup_type';
**********************************************
Script to find Concurrent Programs of a Request Set and
Vice VersaBackground:Why Request Set?To run bunch of concurrent programs
sequentially or parallel we use Request Set
*************************
Query 1:Provide Concurrent Program name to the following query.It
lists all the Request Sets which are created with the Concurrent Program given.
SELECT DISTINCT user_request_set_name
FROM FND_REQUEST_SETS_TL WHERE request_set_id IN (SELECT request_set_id FROM FND_REQUEST_SET_PROGRAMS WHERE concurrent_program_id = (SELECT
CONCURRENT_PROGRAM_ID
FROM fnd_concurrent_programs_tl WHERE
upper(USER_CONCURRENT_PROGRAM_NAME) = upper( '&Enter_Prog_name')));Query
2:Provide the Request Set name to the following query.It lists all concurrent
programs of this Request Set.SELECT USER_CONCURRENT_PROGRAM_NAME FROM fnd_concurrent_programs_tl WHERE
CONCURRENT_PROGRAM_ID IN (SELECT
concurrent_program_id FROM
FND_REQUEST_SET_PROGRAMS WHERE
request_set_id =
(SELECT request_set_id FROM FND_REQUEST_SETS_TL WHERE
upper(user_request_set_name) = upper('&Request_Set_Name')));
*************************************************************
List of all Responsibilites and their associated
MenusBackground:While defining Responsibility, we assign a Menu to it.When
running the following query, it lists all responsibilities and their associated
Menu's.
*************************************************************
Query:
SELECT DISTINCT frt.responsibility_name, fmt.user_menu_name FROM apps.fnd_responsibility_tl frt, apps.fnd_responsibility fr, apps.fnd_menus_tl fmt, apps.fnd_menus fm, apps.fnd_application_tl fat, apps.fnd_application fa WHERE frt.responsibility_id(+) =
fr.responsibility_id AND fr.menu_id
= fmt.menu_id AND fr.menu_id =
fm.menu_id AND fat.application_id =
fa.application_id AND fa.application_id
= fr.application_id AND
frt.LANGUAGE = 'US';
*************************************************************
R12/11i - Current Login Users ListRun the following query
to find out who are the Oracle Apps Users currently Logged into the application
*************************************************************
SELECT DISTINCT icx.session_id, icx.user_id, fu.user_name, fu.description FROM icx_sessions icx, fnd_user fu WHERE
disabled_flag != 'Y' AND
icx.pseudo_flag = 'N' AND
(last_connect + DECODE
(fnd_profile.VALUE ('ICX_SESSION_TIMEOUT'), NULL, limit_time, 0 , limit_time, fnd_profile.VALUE
('ICX_SESSION_TIMEOUT')/60) / 24) > SYSDATE AND icx.counter <
limit_connects AND icx.user_id =
fu.user_id
*************************************************************
Add a concurrent program to a request groupBackground:Add
a concurrent program to a request group through API/Script
*************************************************************
Script:
BEGINfnd_program.add_to_group(
PROGRAM_SHORT_NAME =>
'XX_SHORTNAME',
PROGRAM_APPLICATION => 'XX_PROG_APPL', REQUEST_GROUP => 'All Reports', GROUP_APPLICATION => 'SQLAP' );commit;END;/Explanation of the Parameters:program_short_name = Short name of the program. (e.g.
FNDSCRMT)program_application =
Application of the program. (e.g. 'FND')request_group = Name of request
groupgroup_application =
Application of the request group
*************************************************************
Create User API/Script in Oracle ApplicationsFollowing
script creates a User called DEVUSER with password WELCOME123*************************************************************
DECLARE l_user_id NUMBER;
l_resp_id NUMBER; l_app_id
NUMBER;BEGIN
FND_USER_PKG.CreateUser (x_user_name => 'DEVUSER', x_owner => NULL,
x_unencrypted_password =>
'WELCOME123',
x_email_address =>
'devuser@domain.com'); SELECT
user_id INTO l_user_id FROM fnd_user WHERE user_name = 'DEVUSER'; SELECT responsibility_id,
application_id INTO l_resp_id,
l_app_id FROM fnd_responsibility WHERE responsibility_key = 'SYSTEM_ADMINISTRATOR'; FND_USER_RESP_GROUPS_API. insert_assignment (user_id => l_user_id, responsibility_id => l_resp_id,
responsibility_application_id
=> l_app_id,
security_group_id
=> NULL,
start_date
=> SYSDATE, end_date => NULL, description => NULL); COMMIT;EXCEPTION WHEN OTHERS THEN raise_application_error ( -20001, 'An
error was encountered - ' || SQLCODE || ' -ERROR- ' || SQLERRM);END;
*************************************************************
Script to check the Release Version, System Name of
Oracle ApplicationsRun the following query to find Release Version, System
Name, Multi-Org enabled or not etc
*************************************************************
SELECT release_name,
multi_org_flag,
multi_lingual_flag,
multi_currency_flag,
applications_system_name FROM
fnd_product_groups;
*************************************************************
Generic Loader (FNDLOAD)Background: FNDLOAD is a Oracle
provided utility to transfer AOL Objects from one instance to other.Eg.
Responsibility, Application, Concurrent Program, Executable, Profile Option,
DFF definition, Value Sets, Menu, Lookup Types, Printer drivers etc
*************************************************************
Oracle FNDLOAD :
Sample Syntax
FNDLOAD apps/pwd 0 Y mode configfile datafile entity [ param
...]<apps/pwd>: The APPS schema and password in the form
username/password[@connect_string]. Ifconnect_string is omitted, it is taken in
a platform-specific manner from the environmentusing the name TWO_TASK.< 0 Y
>: Concurrent program flags.<mode>: UPLOAD or DOWNLOAD. UPLOAD causes
the datafile to be uploaded to the database. DOWNLOAD causes the loader to
fetch rows and write them to the datafile.<configfile>: The configuration
file to use (usually with a suffix of .lct, but not enforced or supplied by the
loader).<datafile>: The data file to write (usually with a suffix of
.ldt, but not enforced or supplied by the loader). If the data file already
exists, it will be overwritten.<entity>: The entity(ies) to upload or
download. When uploading, always upload all entities, so specify a
"-" to upload all entities.< [param] >: Zero or more additional
parameters are used to provide bind values in the access SQL (both UPLOAD and
DOWNLOAD). Each parameter is in the form NAME=VALUE. NAME should not conflict
with an attribute name for the entities being loaded. Note: Objects enclosed in
[ ] are optional and may be used for more precise download or upload.Some
Sample Examples: FNDLOAD Concurrent
ProgramDownload:FNDLOAD apps/password O Y DOWNLOAD
$FND_TOP/patch/115/import/afcpprog.lct your_output_dir/your_name.ldt PROGRAM
[APPLICATION_SHORT_NAME=$app]
CONCURRENT_PROGRAM_NAME="SHORT_PROGRAM_NAME"Upload:FNDLOAD
apps/password O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct
your_output_dir/your_name.ldtFNDLOAD Profile OptionsDownloadFNDLOAD
apps/password O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct
your_output_dir/your_name.ldt PROFILE
PROFILE_NAME="profile_option_short_name"[APPLICATION_SHORT_NAME=app]UploadFNDLOAD
apps/password O Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct
your_output_dir/your_name.ldtFNDLOAD Value SetDownloadFNDLOAD apps/password O Y
DOWNLOAD $FND_TOP/patch/115/import/afffload.lct your_output_dir/your_name.ldt
VALUE_SET FLEX_VALUE_SET_NAME="value_set_name"UploadFNDLOAD
apps/password O Y UPLOAD $FND_TOP/patch/115/import/afffload.lct
your_output_dir/your_name.ldtFNDLOAD Request Group DownloadFNDLOAD
apps/password 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct your_output_dir/your_name.ldt
REQUEST_GROUP REQUEST_GROUP_CODE="REQUEST_GROUP_CODE" UploadFNDLOAD
apps/password 0 Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct
your_output_dir/your_name.ldtFNDLOAD Request SetDownloadFNDLOAD apps/password 0
Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct your_output_dir/your_name.ldt
REQ_SET REQUEST_SET_NAME="REQUEST_SET_NAME"UploadFNDLOAD
apps/password 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct
your_output_dir/your_name.ldtFNDLOAD Responsibility DownloadFNDLOAD
apps/password 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct
your_output_dir/your_name.ldt FND_RESPONSIBILITY
RESP_KEY="RESPONSIBILITY_KEY_VALUE"UploadFNDLOAD apps/password 0 Y
UPLOAD $FND_TOP/patch/115/import/afscursp.lct
your_output_dir/your_name.ldtFNDLOAD User RecordDownloadFNDLOAD apps/password 0
Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct your_output_dir/your_name.ldt
FND_USER USER_NAME="USERNAME_TO_PROCESS"UploadFNDLOAD apps/password 0
Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct your_output_dir/your_name.ldt FNDLOAD
MessageDownload FNDLOAD apps/password 0 Y DOWNLOAD
$FND_TOP/patch/115/import/afmdmsg.lct your_output_dir/your_name.ldt
FND_NEW_MESSAGES APPLICATION_SHORT_NAME='app'
[MESSAGE_NAME="MESSAGE_NAME"] UploadFNDLOAD apps/password 0 Y
DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct
your_output_dir/your_name.ldt FNDLOAD
MenuDownload FNDLOAD apps/appspwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct
file_name.ldt MENU MENU_NAME="MENU_NAME"UploadFNDLOAD
apps/appspwd 0 Y DOWNLOAD
$FND_TOP/patch/115/import/afsload.lct file_name.ldtFNDLOAD Printer Style
Download: FNDLOAD apps/password O Y DOWNLOAD
$FND_TOP/patch/115/import/afcppstl.lct your_output_dir/your_name.ldt STYLE
PRINTER_STYLE_NAME="PRINTER_STYLE_NAME" Upload:FNDLOAD apps/password
O Y UPLOAD $FND_TOP/patch/115/import/afcppstl.lct
your_output_dir/your_name.ldtFAQs:1. How to overwrite custom entries using
FNDLOAD?The argument CUSTOM_MODE=FORCE is required for UPLOAD when custom
entries have to be overwritten. Example:FNDLOAD apps/password 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct
your_output_dir/your_name.ldt CUSTOM_MODE=FORCE2. How to transfer custom
objects from one EBS instance to another?- Download the custom object from the
source instanceExample:FNDLOAD apps/$pwd 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct
ADALE_USR.ldt FND_USER USER_NAME=ADALE- Move the custom LDT file
(ADALE_USR.ldt) over to the Destination instance - Upload the custom entry to
the Destination instance Example:FNDLOAD apps/$pwd 0 Y UPLOAD
$FND_TOP/patch/115/import/afscursp.lct ADALE_USR.ldt CUSTOM_MODE=FORCE Special
Note:- FNDLOAD GUI application - MigrationLogiK is a highly efficient and
intuituve tool allowing building migration scripts for both FND and database
objects.- Refer Note 735338.1 for
additional details
*************************************************************
Capturing custom LOG messages for your Program UnitsHere
is the best practice to capture you custom log messages from your custom
Program Units (Interfaces, Reports
etc.). This helps to debug your code when the issue comes
*************************************************************
1. Create a profile with default value 'N'2. Put debug statement in the
program units based on profile value
IF fnd_profile.value('Custom Profile') = 'Y' THEN FND_FILE.PUT_LINE(FND_FILE.LOG,'Debug
Level 1');==> Pl/Sql Program units (Interface etc.) SRW.MESSAGE(1,'Debug Level 1')==>
For Reports END IF;3. When you want to
debug, Run the program by setting the profile value 'Y' *************************************************************
Query to find the Request Group of a Concurrent Program-
Run the following query to find the Request Group of a Concurrent Program.-
Provide Concurrent Program executable name for the following query
*************************************************************
SELECT fa.application_short_name,
frg.request_group_name,
fe.execution_file_name,
fe.executable_name FROM
fnd_request_group_units frgu,
fnd_concurrent_programs fcp,
fnd_request_groups frg,
fnd_executables fe,
fnd_application fa WHERE
frgu.request_unit_id = fcp.concurrent_program_id AND frgu.request_group_id =
frg.request_group_id AND
fe.executable_id = fcp.executable_id
AND FRG.application_id = fa.application_id AND fe.executable_name =
'&CP_EXEC_NAME'; Note:A Concurrent Program can be attached to a multiple
Request Groups. So, if the query returns more than one row do NOT get confused
*************************************************************
Query to find all Responsibilities added to a User -
Oracle AppsProvide User Name to the following query. It will show all the
responsibilities added to that user
*************************************************************
SELECT fu.user_name, fr.responsibility_name, furg.start_date, furg.end_date FROM fnd_user_resp_groups_direct furg, fnd_user fu, fnd_responsibility_tl fr WHERE
upper(fu.user_name) = upper('&user_name') AND furg.user_id = fu.user_id AND furg.responsibility_id =
fr.responsibility_id AND
fr.language = USERENV ('LANG')ORDER BY start_date;Note:It shows End Dated
Responsibilities also
*************************************************************
Oracle Discoverer EUL Tables & DescriptionEUL Tables
Information
*************************************************************
EUL4_BASTable gives list of Business AreasEUL4_OBJSTable gives all folders
in the EULEUL4_KEY_CONSTable gives all Folder Joins details.- Key_Obj_ID points
to parent folder,- FK_Obj_ID_Remote points to child folderEUL4_EXPRESSIONSTable
gives all Items that are in the EUL- IT_Obj_ID is not null AND join to a row in
EUL4_Objs you have a Folder Item.They are listed as Exp_Type of 'CO' or
'CI'.COs are database object items.CIs are created items (like date hierarchy
items, complex folder items, etc).EUL4_OBJSTable gives details about the types
of objects- Obj_Type tells you Standard (SOBJ) from Complex (COBJ)
folders.EUL_USERSTable gives user details.EUL4_ACCESS_PRIVSTable to use to get
the list of Discoverer users that was granted access to the User
Edition.Basically, when a user has been granted privileges one row per
privilege is inserted into this table. - The column called AP_EU_ID contains
the ID of the user.- The column GP_APP_ID is the one that tells you what
privilege a user has
*************************************************************
Query to find all Oracle Discoverer Reports/WorkbooksRun
the following sequence of queries in APPS schema to find all Discoverer
Reports/Workbooks that exist in the system
*************************************************************
Query1:
Run the following query to know the name of tableSELECT
owner,table_name FROM all_tables WHERE
owner = 'EUL4_US' AND table_name LIKE
'%DOCUMENT%';Generally the above query returns table_name as 'EUL4_DOCUMENTS'
Meaning, all the discoverer report/Workbook names are stored in this tableQuery
2: Run the following query to see all reportsSELECT DOC_ID, DOC_NAME, DOC_DEVELOPER_KEY, DOC_CREATED_BY FROM EUL4_US.EUL4_DOCUMENTS;Note:If the First
Query is not returning any data then try the following query and manually find
the table name. SELECT
owner,table_name FROM all_tables WHERE table_name LIKE '%EUL%DOCUMENT%';
You have to modify the 2nd query in this case with the new OWNER.TABLE_NAME
*************************************************************
Table of TableType (Matrix) - PL/SQL
CollectionsBackground:PL/SQL Collections.Following example Explanation:1.
Declare a Record Type.2. Declare a Table Type of this Record Type.3. Declare a
Table Type of above Table Type.Now imagine this is a kind of Matrix. Each cell
can hold 3 values here, which is value1, value2, value3. So for all 3x3=9
cells, we will have 27 values
*************************************************************
DECLARE /* Declare Record
Type*/ TYPE RecType IS RECORD (
value1 NUMBER, value2
NUMBER, value3 NUMBER
); /* Declare Table Type of the
above Record Type*/ TYPE TblType IS TABLE OF RecType INDEX BY
PLS_INTEGER; /* Declare Table Type of
the above Table Type (2d table or Matrix)*/
TYPE TblOfTblType IS TABLE OF TblType INDEX BY
PLS_INTEGER; /*Declare a table variable of
above type(Table of table type)*/
matrix TblOfTblType; BEGIN
/*Writing the data*/ FOR i IN 4
.. 6 LOOP FOR j IN 1 .. 3 LOOP matrix (i) (j).value1 := i * j; matrix (i) (j).value2 := i + j; matrix (i) (j).value3 := i - j; END LOOP; END LOOP;
/*Reading and Printing the data*/
FOR i IN matrix.FIRST .. matrix.LAST LOOP FOR j IN matrix (i).FIRST .. matrix
(i).LAST LOOP
DBMS_OUTPUT.put_line ('matrix (' || i || ') ('||j||').value1= ' ||
matrix (i) (j).value1);
DBMS_OUTPUT.put_line ('matrix (' || i || ') ('||j||').value2= ' ||
matrix (i) (j).value2);
DBMS_OUTPUT.put_line ('matrix (' || i || ') ('||j||').value3= ' ||
matrix (i) (j).value3); END
LOOP; END LOOP;END
*************************************************************
Code to send E-mail from PL/SQL by reading the file
contentBusiness Case:This is a email program to sends email to the
receiver/user by reading the text file contentChange the following things when
you run this program:1. v_email_server parameter value. Put your mail server
details.2. v_port parameter value. Generally, it is 25 only.3. v_dirpath
parameter value. Directory path of the file to read4. v_filename parameter
value. This is your text file name. (I put 10 lines in the text file for
testing
*************************************************************
DECLARE v_file_handle UTL_FILE.FILE_TYPE; v_conn UTL_SMTP.CONNECTION; v_reply UTL_SMTP.REPLY; v_email_server VARCHAR2 (100):=
'lax02.lax.corp.int.gect.com';
v_port NUMBER := 25; v_dirpath VARCHAR2 (50) := '/usr/tmp'; v_filename VARCHAR2 (50) := 'testing123.txt'; v_sender VARCHAR2 (50) := 'name@mydomain.com'; v_recpnt VARCHAR2 (255):=
'name@senderdomain.com'; v_msg VARCHAR2 (32767); v_line VARCHAR2 (1000); v_message VARCHAR2 (1000); CRLF VARCHAR2 (2) := CHR (13) || CHR (10);BEGIN /* Check if the file exists */ BEGIN
v_file_handle := UTL_FILE.FOPEN (v_dirpath, v_filename, 'R'); EXCEPTION WHEN UTL_FILE.INVALID_PATH THEN RETURN; WHEN OTHERS THEN RETURN; END;
/* Try connecting smtp server and
do handshake*/ v_conn :=
UTL_SMTP.OPEN_CONNECTION (v_email_server, v_port); v_reply := UTL_SMTP.HELO (v_conn,
v_email_server); IF v_reply.code !=
250 THEN RETURN; END IF;
/*UTL_SMTP.MAIL initiates a mail transaction with the server. The
destination is a mailbox.*/ v_reply :=
UTL_SMTP.MAIL (v_conn, v_sender); IF
v_reply.code != 250 THEN
RETURN; END IF; /* UTL_SMTP.RCPT specifies the recipient of
an e-mail message. */ v_reply :=
UTL_SMTP.RCPT (v_conn, v_recpnt); IF
v_reply.code != 250 THEN
RETURN; END IF; /*UTL_SMTP.OPEN_DATA sends the DATA command
after which you can use WRITE_DATA and WRITE_RAW_DATA to write a portion of the
e-mail message.*/ UTL_SMTP.OPEN_DATA
(v_conn); v_message := 'This is an auto
generated mail. Please do not reply to this mail.'; v_msg
:= 'Date: ' || TO_CHAR (SYSDATE, 'Mon DD yyyy hh24:mi:ss') || CRLF
|| 'From: ' || v_sender ||
CRLF || 'Subject: ' ||
'Sample file' || CRLF ||
'To: ' || v_recpnt || CRLF
|| v_message || CRLF ||
CRLF; /*UTL_SMTP.WRITE_DATA
Writes a portion of the e-mail message*/
UTL_SMTP.WRITE_DATA (v_conn, v_msg);
/*Read each line of the mail and put it in the mail*/ LOOP
BEGIN UTL_FILE.GET_LINE
(v_file_handle, v_line);
EXCEPTION WHEN
NO_DATA_FOUND THEN EXIT; END;
v_msg := '*** truncated ***' || CRLF; v_msg := v_line || CRLF; UTL_SMTP.WRITE_DATA (v_conn, v_msg); END LOOP;
UTL_FILE.FCLOSE (v_file_handle);
/*UTL_SMTP.CLOSE_DATA call ends the e-mail message*/ UTL_SMTP.CLOSE_DATA (v_conn); /*UTL_SMTP.QUIT terminates an SMTP session
and disconnects from the server*/
UTL_SMTP.QUIT (v_conn);EXCEPTION when others then raise_application_error(-20000,
SQLERRM);END;
Note:In a typical Oracle Apps environment1. The file that has to be read
has to kept in DB Node. Meaning database server. NOT on Appl Tier. Meaning NOT
on Middle tier.2. To find out the email server, The navigation isSysadmin
LoginWorkflow Administrator Web Applications > Oracle Applications Manager
> Workflow Manager > Under Configuration 'Service Components' > select
'Workflow Notification Mailer' > Edit > Inbound EMail Account (IMAP) >
Note Server Name
*************************************************************
Script to update Email Style for all Employees - Oracle
ApplicationsBusiness Case:Login > Click on Preferences > Under
Notifications, 'Email Style'Update this email style as 'HTML mail' for all
employeesSolution: Run following SQL's to update 'Email Style' to 'HTML mail',
under The Notification Preference For All The Users.1.UPDATE
wf_local_roles SET
notification_preference='MAILHTM2' WHERE orig_system IN ('FND_USR',
'PER');2.UPDATE fnd_user_preferences
SET preference_value='MAILHTM2' WHERE preference_name ='MAILTYPE' AND module_name ='WF' AND user_name <> '-WF_DEFAULT-';3. Issue
Commit.Note: 1. To update Email Style as 'HTML mail with attachments', we need
to use 'MAILHTML' instead of 'MAILHTM2'2. Other email styles and
description:DISABLE = DisabledQUERY = Do not send me mailMAILHTM2 = HTML
mailMAILHTML = HTML mail with attachmentsSUMHTML = HTML summary mailMAILTEXT =
Plain text mailMAILATTH = Plain text mail with HTML attachments SUMMARY = Plain
text summary mail 3. If this preference need to be set for all new users
created in the system Navigate to
Workflow Administrator Web (New)>Administration Under Global Preferences, Set the
Notification Style as appropriate. In this case 'HTML mail
*************************************************************
Reading File, Writing File through UTL_FILE -
PL/SQLBackground:With the UTL_FILE package, your PL/SQL programs can read and
write operating system text files
*************************************************************
Prerequisite steps:1. Create UTF_FILE directory. CREATE OR REPLACE DIRECTORY MY_UTL_DIR AS
'/usr/tmp/utl'2. Now keep a simple text file 'temp.txt' in '/usr/tmp/utl'
directory. My temp.txt file has 10 lines.
Program 1:Now lets try a simple program which reads the file temp.txt and
print on the console.DECLARE l_fileID UTL_FILE.FILE_TYPE; l_dirpath VARCHAR2 (50) := 'MY_UTL_DIR'; l_filename VARCHAR2 (50) := 'temp.txt'; l_line_counter NUMBER := 1; l_buffer VARCHAR2(32000);BEGIN /*Open file*/ l_fileID := UTL_FILE.FOPEN (l_dirpath,
l_filename, 'r', 32000); LOOP /*Read and output the line until we
reach the last line*/
UTL_FILE.GET_LINE(l_fileID, l_buffer, 32000); dbms_output.put_line('Line' ||
l_line_counter: ||' '|| l_buffer); l_line_counter := l_line_counter +
1; END LOOP;EXCEPTION WHEN UTL_FILE.INVALID_PATH THEN DBMS_OUTPUT.PUT_LINE('File location is
invalid'); WHEN NO_DATA_FOUND THEN
/*EOF Reached*/
UTL_FILE.fclose(l_fileID); /* Close the File Type*/ NULL;END;Program 2:Now lets try a simple
write program to write 10 lines to the file TestWrite.txtDECLARE l_fileID UTL_FILE.FILE_TYPE; l_dirpath VARCHAR2 (50) := 'MY_UTL_DIR'; l_filename VARCHAR2 (50) := 'TestWrite.txt'; l_buffer VARCHAR2(32000); l_count NUMBER :=0;BEGIN /*Open file*/ l_fileID := UTL_FILE.FOPEN (l_dirpath,
l_filename, 'w', 32000); LOOP l_count := l_count+1; /*PUT_LINE procedure writes the text
string stored in the buffer to the open file*/ UTL_FILE.PUT_LINE(l_fileID, 'This is
Line Number: ' || l_count); EXIT
WHEN l_count = 11; END LOOP; UTL_FILE.fclose(l_fileID); /* Close the File
Type*/EXCEPTION WHEN
UTL_FILE.WRITE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Operating system error occurred during the write
operation.'); WHEN others THEN DBMS_OUTPUT.PUT_LINE('Other
Exception.');END;Note:UTL_FILE documentation:http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_file.htm
*************************************************************
Monitoring Failed Concurrent Programs - Oracle
ApplicationsThe following script finds all concurrent requests which are failed
from the Date specified to the script. This provides log file location also
*************************************************************
SELECT fcr.request_id,
fu.user_name,
fcp.user_concurrent_program_name,
TO_CHAR (fcr.actual_start_date, 'DD-MON-YYYY HH24:MI:SS')
actual_start_date, TO_CHAR
(fcr.actual_completion_date, 'DD-MON-YYYY HH24:MI:SS')
actual_completion_date,
fcr.logfile_name FROM
apps.fnd_concurrent_requests fcr,
apps.fnd_concurrent_programs_tl fcp, apps.fnd_user fu WHERE
fcr.program_application_id = fcp.application_id AND fcr.concurrent_program_id =
fcp.concurrent_program_id AND
fcr.requested_by = fu.user_id AND
fcr.phase_code = 'C' AND
fcr.status_code = 'E' AND
fcr.actual_completion_date > to_date('&Enter_Date','DD-MON-YYYY') AND fcp.user_concurrent_program_name
NOT IN ('Request Set
Stage', 'Report Set')ORDER BY 1=> You may configure an alert to send a mail
everyday to the support group detailing failed concurrent requests in last 24
hours
*************************************************************
SQL script to find Scheduled Concurrent ProgramsRun the
following script to find Scheduled Concurrent Programs and its frequency
*************************************************************
SELECT * FROM
apps.FND_CONC_REQ_SUMMARY_V WHERE phase_code = 'P' AND status_code IN ('I', 'Q') AND (NVL (request_type, 'X') !=
'S') AND requested_start_date
>= SYSDATEORDER BY program_short_name DESC;PHASE_CODE status P stands for
PendingSTATUS_CODE status I stands for Normal & status Q stands of
StandbyRequest_type != 'S' condition is to disallow Request Set Stage programs
*************************************************************
Reset/Update Oracle Apps Users Passwords- Use the
following script to reset passwords of all application users
*************************************************************
DECLARE RetVal BOOLEAN;
NewPwd VARCHAR2 (200) :=
'welcome123'; CURSOR c_user IS
SELECT user_name FROM
fnd_user WHERE employee_id IS NOT
NULL AND (end_date IS NULL OR
end_date > SYSDATE);BEGIN FOR r_user
IN c_user LOOP RetVal :=
APPS.FND_USER_PKG.CHANGEPASSWORD (r_user.user_name, NewPwd); COMMIT;
END LOOP;END
*************************************************************
Adding Responsibility to User using script - Oracle
ApplicationsPrerequisites: Apps password (database)Run below script. When
prompted provide the following details to the script- Enter the user name to which
responsibility is to be added- Enter the responsibility to be added to this
user
*************************************************************
DECLARE v_user_name VARCHAR2 (20) :=
'&Enter_User_Name';
v_req_resp_name VARCHAR2 (50)
:= '&Enter_Required_Responsibility';
v_description VARCHAR2 (100)
:= 'Adding Responsibility to user using script'; v_appl_shrt_name VARCHAR2 (20); v_appl_name VARCHAR2 (50); v_resp_key VARCHAR2 (50);BEGIN SELECT fav.application_short_name, fav.application_name, frv.responsibility_key INTO v_appl_shrt_name, v_appl_name,
v_resp_key FROM FND_APPLICATION_VL
fav, FND_RESPONSIBILITY_VL frv WHERE
frv.application_id = fav.application_id
AND frv.responsibility_name = v_req_resp_name; fnd_user_pkg.addresp (username => v_user_name, resp_app => v_appl_shrt_name, resp_key => v_resp_key, security_group => 'STANDARD', description => v_description, start_date => SYSDATE, end_date => NULL); COMMIT;
DBMS_OUTPUT.put_line ('The responsibility ' || v_req_resp_name || ' is
added to the user ' || v_user_name);EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('Responsibility IS NOT added due to ' || SQLCODE
|| '; ' || SUBSTR (SQLERRM, 1, 250));
ROLLBACK;END;**************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************************
