Some Tips About FNDLOAD

Data Synchronization Data Synchronization is a process in which some setup data would be synchronized, and this would be more important when you are working in Oracle application development/implementation project. The equally important that ATG data Migration takes place necessary to synchronize the data across databases instance during
· Installations (New descriptive flex field creations etc)
· Upgrades (Apps upgrade etc.)
· Maintenance (Value set changes etc.)

Generic Loader (FNDLOAD) The Generic Loader (FNDLOAD) is a concurrent program that can move Oracle Applications data between database and text file representations. The loader reads a configuration file to determine what data to access. For information on specific configuration files consult the Open Interfaces Guide for your product group.
From an FNDLOAD perspective, there is absolute no limitation on parallel execution.  Parallel upload of concurrent programs and responsibilities should through fine unless upload logic in LCT file restrict the same.

There is no way to limit the download of the value hierarchy data with the existing Flexfield loader logic. 
If this data is removed manually from the .ldt file generated when downloading data, the upload might error out as a result and this is NOT a supported method of using FNDLOAD and the corresponding generated data.

Overview The Generic Loader can download data from an application entity into a portable, editable text file. This file can then be uploaded into any other database to copy the data. Conversion between database store and file format is specified by a configuration file that is read by the loader.

The Generic Loader downloads data from a database according to a configuration (.lct) file, and converts the data into a data file (.ldt file). The Generic Loader can then upload this data to another database using a configuration file.

The loader operates in one of two modes: download or upload. In the download mode, data is downloaded from the database to a text file; in the upload mode, data is uploaded from a text file to the database.

Data structures supported by the loader include master-detail relationships and foreign key reference relationships.

In both downloading and uploading, the structure of the data involved is described by a configuration file. The configuration file describes the structure of the data and also the access methods to use to copy the data into or out of the database. The same configuration file may be used for both uploading and downloading.

When downloading, the Generic Loader creates a second file, called the data file that contains the structured data selected for downloading. The data file has a standard syntax for representing the data that has been downloaded. When uploading, the Generic Loader reads a data file to get the data that it is to upload. In most cases, the data file was produced by a previous download, but may have come from another source. The data file cannot be interpreted without the corresponding configuration file available.

What can be done?
These are the extensive list which can be done through FNDLOAD
· Concurrent Programs, Executables
· Request Groups, Request Sets
· Profile Options
· Key and Descriptive Flexfields
· Menus and Responsibilities
· Forms and Form Functions/Personalizations
· Attachments
· Messages
· Value Sets and Values
· Lookup Types
· User Responsibilities
· Printer Definitions
· FND Dictionary
· Help Configuration
· Document Sequences
· Alerts
· Concurrent Manager Schedules
The FNDLOAD: Concurrent Program – FNDLOAD can be executed as a concurrent program.

Some advantages when using FNDLOAD 1. Because downloaded data is stored in a text file, version administration is possible
2. There is nothing to worry to go to purchase because Investment = 0$
3. No learning curve. This is relief for developer and DBA
4. Fully supported and recommended by Oracle
5. Capture the migrations in a file and use it during installations, clones etc. to migrate in batch
6. Pin-point when something happened and where (database) easily
7. Your AOL data migration process is now simplified and streamlined goal attained

Some disadvantages when using FNDLOAD 1. Applications patching mechanisms use FNDLOAD heavily possibility of negative impact is not zero
2. UPLOAD_MODE=REPLACE only for menus
3. No validation against migrating database/instance sensitive data

Syntax The Generic Loader is a concurrent program named FNDLOAD. The concurrent executable takes the following parameters:

FNDLOAD apps/pwd 0 Y mode configfile datafile entity [ param ...]
where

<apps/pwd> The APPS schema and password in the form username/password[@connect_string]. If connect_string is omitted, it is taken in a platform-specific manner from the environment
using 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.

Modes of Operation This is important because it would drive the whole flow, and it always be either Upload or Download.

Example of download FNDLOAD apps/pwd 0 Y DOWNLOAD ${FND_TOP}/patch/115/import/afcpprog.lct myfile.ldt \ PROGRAM CONCURRENT_PROGRAM_NAME= concurrent_program_short_name> APPLICATION_SHORT_NAME=<application_short_name>

Example of Upload FNDLOAD apps/pwd 0 Y UPLOAD ${FND_TOP}/patch/115/import/afcpprog.lct myfile.ldt - CUSTOM_MODE=FORCE undocumented parameter

What are FNDLOAD Options? · Application level OR single entity level download
o (Example) Download all the profile options of Inventory or just the INV: Default Primary UOM
· Entire OR Partial upload of a data file
o (Example) Upload the entire myfile.ldt or just a single entity indicated by - and mode UPLOAD or UPLOAD_PARTIAL
o Entity name required for UPLOAD_PARTIAL mode
· Custom mode force update
o To override Oracle’s upload algorithm and update the custom AOL data regardless, use CUSTOM_MODE= FORCE
o UPLOAD_MODE=REPLACE (only for menus)
· Support for NLS uploads
o NLS data uploads for translated columns are supported, use UPLOAD_MODE=NLS (No space before and after = sign)
· Control FNDLOAD download of valuesets and values
o FNDLOAD parameter P_VSET_DOWNLOAD_CHILDREN, value 'N' does not download flex data from VSET_SECURITY_RULE, VSET_ROLLUP_GROUP, and VSET_VALUE

Where is Configuration File Located By default Oracle delivers most of configuration files that can be used to download certain entities.
· Configuration files with extension .lct
o On Unix - all the configuration files are in $FND_TOP/patch/115/import directory
o On Unix Oracle also places the original configuration files in $FND_TOP/admin/import directory
· Data files with extension .ldt
FNDLOAD File Structure
· The configuration files (.lct) are delivered and maintained by Oracle.
· It has entity definitions, parent-child relationships and user input parameters identified by :NAME.
· Downloading a parent automatically downloads all children - (Example) Concurrent Program download.
· The data files (.ldt) have both entity definition and the data.
· It also shows the version and the location of the configuration file (.lct) that was used.
· Without the configuration file, a data file is useless.
· Without the data file, a configuration file is meaningless.

FNDLOAD Files · Key files: .lct and .ldt
· FNDLOAD must be run as the apps user not as applsys or any other user, otherwise an Ora-6550 error will be received.
· Both are easily readable, editable and portable.
· Do not modify Oracle .lct files.
· Use a favorite editor to manipulate only the .ldt files but be cautious about data type, length, delimiter placements etc.
· Use the log file outputs or .ldt file contents creatively for quick file comparisons and answer questions as: (Why can this be accessed? What is that profile option name, value and level? What is the value set used for that DFF segment attribute10 etc.)
· Partial string searches (which value set has Priority something in its where clause, etc)

Some sample examples

1 - Printer Styles

FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME=”printer style name”

2 - Lookups

FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME=”prod” LOOKUP_TYPE=”lookup name”

3 - Descriptive Flexfield with all of specific Contexts

FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX P_LEVEL=:COL_ALL:REF_ALL:CTX_ONE:SEG_ALL? APPLICATION_SHORT_NAME=”prod” DESCRIPTIVE_FLEXFIELD_NAME=”desc flex name” P_CONTEXT_CODE=”context name”

4 - Multiple Flexfields
Use a combination of APPLICATION_SHORT_NAME and DESCRIPTIVE_FLEXFIELD_NAME names ie. APPLICATION_SHORT_NAME=PER >> will download all PER flexfields DESCRIPTIVE_FLEXFIELD_NAME=PER_% >> will download all flexfields that start with 'PER_'.

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct
file_name.ldt DESC_FLEX DESCRIPTIVE_FLEXFIELD_NAME="PER_%"

5 - Key Flexfield Structures

FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEX P_LEVEL=:COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL? APPLICATION_SHORT_NAME=”prod” ID_FLEX_CODE=”key flex code” P_STRUCTURE_CODE=”structure name”

Note: in 11i it is P_STRUCTURE_CODE, in R12 it was renamed to ID_FLEX_STRUCTURE_CODE


6 - Concurrent Programs

FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME=”prod” CONCURRENT_PROGRAM_NAME=”concurrent name”

7 - Value Sets

FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME=”value set name”

8 - Value Sets with values

FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME=”value set name”

9 - Profile Options

FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME=”profile option” APPLICATION_SHORT_NAME=”prod” 

10 - Request Group

FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME=”request group” APPLICATION_SHORT_NAME=”prod”

11 - Request Sets

FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET APPLICATION_SHORT_NAME=”prod” REQUEST_SET_NAME=”request set”

12 - Responsibilities 

FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY=”responsibility"

13 - Responsibilities with all Security Groups

FNDLOAD apps/<PASSWORD> 0 Y DOWNLOAD FND_TOP/patch/115/import/afscursp.lct <USER>.ldt
FND_USER USER_NAME="<USER>" SECURITY_GROUP=% DATA_GROUP_NAME=%

14 - Menu (Please refer to Appendix B within the Oracle E-Business Suite System Administrator's
Guide - Configuration the for loaders on more information.)

FNDLOAD apps/ pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME=”menu_name”

15 - Forms/Functions/Personalizations: Refer to the System Administrator's Guide on dependencies

FNDLOAD <userid>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct
<filename.ldt> FND_FORM_CUSTOM_RULES form_name=<form name>

OR

FNDLOAD <userid>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt FUNCTION FUNCTION_NAME=<function_name>

OR

FNDLOAD <userid>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt FORM FORM_NAME=<form_name>

OR

FNDLOAD <userid>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct <filename.ldt> FND_FORM_CUSTOM_RULES function_name=<function name>

16 - User/Responsibilities 

FNDLOAD apps/ pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_USER

17 - Alert

FNDLOAD apps/pwd 0 Y DOWNLOAD
$ALR_TOP/patch/115/import/alr.lct my_file.ldt ALR_ALERTS
APPLICATION_SHORT_NAME=FND ALERT_NAME=Alert name to downloa

18 - Blob

With Release 12.1.1, FNDLOAD supports BLOB data (upload / download ) to better serve content-rich applications.

FNDLOAD apps/pwd 0 Y mode configfile datafile entity [ param ... ]

 19 - Overwrite custom definitions

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/.lct $
XX_TOP/import/.ldt CUSTOM_MODE=FORCE

20 - Load an NLS Language

FNDLOAD <APPS USR>/<APPS PWD> 0 Y UPLOAD <controlfile.lct> <datafile.ldt> \
- UPLOAD_MODE=NLS CUSTOM_MODE=FORCE WARNINGS=TRUE

21 - Migrate the role registration process from one instance to another
a. Please navigate to the path: $FND_TOP /patch/115/import/US/umxrgsvc.ldt

b. The following command can be used to download:

FNDLOAD apps/<PASSWD>@(instance name) O Y DOWNLOAD $FND_TOP/patch/115/import/umxrgsvc.lct umxrgsvc.ldt UMX_REG_SERVICES REG_SERVICE_CODE UMX

c. The following command can be used to upload:

FNDLOAD apps/<PASSWD>@(instance name) O Y UPLOAD $FND_TOP/patch/115/import/umxrgsvc.lct umxrgsvc.ldt UMX_REG_SERVICES REG_SERVICE_CODE UMX

22 - Transfer Custom Messages to another Instance
a. Download the message from the source instance.

FNDLOAD apps/apps 0 Y DOWNLOAD @FND:patch/115/import/afmdmsg.lct password.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME=FND MESSAGE_NAME=PASSWORD-INVALID-NO-SPEC-CHAR

b. Move the custom LDT file (password.ldt) over to the destination instance.

c. Upload the custom message to the destination instance.

FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afmdmsg.lct password.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME=FND CUSTOM_MODE=FORCE

23 - Download UMX Roles and Role Assignment data from one instance and upload to another.

To download from one instance:
FNDLOAD <username/pwd@sid> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afrole.lct
umxroles.ldt WF_ROLE ORIG_SYSTEM=UMX%

To upload to another instance:
FNDLOAD <username/pwd@sid> 0 Y UPLOAD $FND_TOP/patch/115/import/afrole.lct
umxroles.ldt

Creating ACL for UTL_SMTP

SELECT *
FROM dba_network_acls;

-- Create ACL and privilege
begin
  dbms_network_acl_admin.create_acl (
    acl         => 'utl_mail.xml',
    description => 'Allow mail to be send',
    principal   => 'APPS',
    is_grant    => TRUE,
    privilege   => 'connect'
    );
    commit;
end;

--Add Privilege

begin
  dbms_network_acl_admin.add_privilege (
  acl       => 'utl_mail.xml',
  principal => 'APPS',
  is_grant  => TRUE,
  privilege => 'resolve'
  );
  commit;
end


--Test1

begin
  dbms_network_acl_admin.assign_acl(
  acl  => 'utl_mail.xml',
  host => 'mail.company.com.sa'
  );
  commit;
end;

--Test2
begin
  utl_mail.send(
  sender     => 'fromuser@company.com',
  recipients => 'myname@company.com,user2@company.com, user3@company.com',
  message    => 'Test E-mail from ERP Database'
  );
  commit;
end;

Core HR SQL Queries

--Organization through Organization Hierarchy

SELECT NAME, TYPE, organization_id
                 FROM (SELECT hou.NAME, TYPE, organization_id
                         FROM hr_all_organization_units hou
                        WHERE organization_id IN (
                                 SELECT     organization_id_child
                                                              organization_id
                                       FROM (SELECT organization_id_child,
                                                    organization_id_parent,
                                                    org_structure_version_id
                                               FROM per_org_structure_elements
                                              WHERE org_structure_version_id IN (
                                                       SELECT posv.org_structure_version_id
                                                         FROM per_organization_structures pos,
                                                              per_org_structure_versions posv
                                                        WHERE primary_structure_flag =
                                                                           'Y'
                                                          AND pos.organization_structure_id =
                                                                 posv.organization_structure_id
                                                          AND pos.NAME =
                                                                 :p_x_org_hierarchy))
                                 CONNECT BY organization_id_child =
                                                   PRIOR organization_id_parent
                                 START WITH organization_id_child =
                                                             :x_organization_id)
                          AND UPPER (hou.TYPE) = :x_organization_type)
                WHERE ROWNUM = 1;

--Find Organization Manager Query
SELECT NVL
                         ((SELECT NVL (papf.person_id, 0)
                             FROM hr_all_organization_units org,
                                  hr_all_organization_units_tl org_tl,
                                  hr_organization_information org_info2,
                                  per_all_people_f papf
                            WHERE org_info2.organization_id =
                                                           org.organization_id
                              AND org_info2.org_information_context =
                                                     'Organization Name Alias'
                              AND org_info2.org_information2 = TO_CHAR (papf.person_id(+))
                              AND org.organization_id = org_tl.organization_id
                              AND org_tl.LANGUAGE = USERENV ('LANG')
                              AND org_tl.NAME = :x_org_name
                              AND papf.current_employee_flag = 'Y'
                              AND TRUNC (:x_effective_date)
                                     BETWEEN papf.effective_start_date
                                         AND papf.effective_end_date),
                          0
                         ) val
                 FROM DUAL;
               
--Find the List Users that have a particular Responsibility
select rg.user_id,fu.user_name,ppf.employee_number,ppf.full_name,rt.responsibility_name
from fnd_responsibility_tl rt,FND_USER_RESP_GROUPS_DIRECT rg,fnd_user fu, per_all_people_f ppf
where upper(RESPONSIBILITY_NAME) like '%APPROVALS MANAGEMENT BUSINESS ANALYST'
and rt.RESPONSIBILITY_ID = rg.RESPONSIBILITY_ID
and rg.user_id = fu.user_id
and fu.employee_id = ppf.person_id
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date


--Find total Active employee in the company
select ppf.employee_number,ppf.full_name,ppf.email_address
from per_all_people_f ppf
where ppf.current_employee_flag = 'Y'
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and ppf.employee_number is not null
and ppf.person_type_id = ( select person_type_id
from per_person_types
where user_person_type = 'Employee'
and business_group_id = ppf.BUSINESS_GROUP_ID
)

--Get Month Wise Hired Employees Head Count
select to_char(ORIGINAL_DATE_OF_HIRE,'MON') MM, COUNT(PERSON_ID) TOTAL from per_people_x
WHERE to_char(ORIGINAL_DATE_OF_HIRE,'RRRR') = '2017'
group by to_char(ORIGINAL_DATE_OF_HIRE,'MON')

--Provide a List of Active Employees along with their Supervisor’s Name and email address
select ppf.employee_number,ppf.full_name,ppf.email_address ,paaf.supervisor_id,sup.full_name,sup.email_address
from per_all_people_f ppf,per_all_assignments_f paaf,per_people_x sup
where ppf.current_employee_flag = 'Y'
and paaf.supervisor_id (+) = sup.person_id
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
and ppf.person_id = paaf.person_id
and ppf.employee_number is not null
--d ppf.employee_number = '39988'
and ppf.person_type_id = ( select person_type_id
from per_person_types
where user_person_type = 'Employee'
and business_group_id = ppf.BUSINESS_GROUP_ID
)

--Get the Employee Salary Increase Summary
select ppf.employee_number,ppf.full_name,ppp.proposed_salary_n,ppp.change_date
from per_pay_proposals ppp,per_all_people_f ppf,per_all_assignments_f paaf
where ppf.person_id = paaf.person_id
and paaf.assignment_id = ppp.assignment_id
and ppf.employee_number = ‘603167’
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
and ppp.approved = ‘Y’
and change_date = (select min(change_date) from per_pay_proposals where assignment_id = ppp.assignment_id and proposed_salary_n = ppp.proposed_salary_n)
order by ppp.change_date desc

--Get the History for An Employee’s Transfers in the company
--Get the list of employee who were terminated and have been rehired
select ppf.employee_number,ppf.full_name,ppf.email_address
from per_all_people_f ppf
where trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and person_id in
( select person_id
from (
select count(1),person_id
from per_periods_of_service
group by person_id
having count(1) > 1
)
)

--Get the List of Terminated Employees
select ppf.employee_number,ppf.full_name,ppf.email_address
from per_all_people_f ppf
where trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and current_employee_flag is null
and ppf.person_type_id = ( select person_type_id
from per_person_types
where user_person_type = 'Ex-employee'
and business_group_id = ppf.BUSINESS_GROUP_ID
)

--Get an Employees Leave History
select a.date_start,a.date_end,a.absence_days,
(select name from per_ABSENCE_ATTENDANCE_types
where ABSENCE_ATTENDANCE_TYPE_ID = a.ABSENCE_ATTENDANCE_TYPE_ID
and business_group_id =a.business_group_id) leave_type
from per_absence_attendances a
where person_id = (select person_id from per_all_people_f where employee_number = '1000063' and employee_number is not null)
order by date_start desc

--Get EIT Based information for employees
select ppf.employee_number,ppf.full_name, ppei.* from per_all_people_f ppf, per_people_extra_info ppei
where ppf.person_id = ppei.person_id
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and ppf.employee_number = '1000063'
--and ppei.information_type = 'XX_OTHER_DETAILS'

--Get SIT Based information for employees
select * from per_person_analyses ppa,per_analysis_criteria pac
where ppa.ANALYSIS_CRITERIA_ID = pac.ANALYSIS_CRITERIA_ID
and person_id = 115043
and pac.ID_FLEX_NUM =
(
select fis.ID_FLEX_NUM
from FND_ID_FLEXS fif, FND_ID_FLEX_STRUCTURES fis
where fif.ID_FLEX_CODE = fis.ID_FLEX_CODE
and id_flex_structure_code = ‘XX_PROBATION_EVALUATION’
)

--Get the List of all Organization which are below an organization in Org Hierarchy
--Get the List of all Organization which are Above an organization in Org Hierarchy
--Parent and Child Organizations List

select hp.name parent_org, hc.name child_organization
from PER_ORG_STRUCTURE_ELEMENTS pose, PER_ORG_STRUCTURE_VERSIONS posv, hr_all_organization_units hc, hr_all_organization_units hp
where 1=1
and pose.BUSINESS_GROUP_ID = posv.BUSINESS_GROUP_ID
and pose.ORGANIZATION_ID_CHILD = hc.organization_id
and pose.ORGANIZATION_ID_PARENT = hp.organization_id

--Get the Oracle Balance Values
SELECT pdb.defined_balance_id,dim.dimension_name
FROM pay_defined_balances pdb,
pay_balance_types typ,
pay_balance_dimensions dim
WHERE pdb.balance_type_id = typ.balance_type_id
AND pdb.balance_dimension_id = dim.balance_dimension_id
AND dim.legislation_code = ( SELECT legislation_code FROM per_business_groups WHERE business_group_id = 81)
AND UPPER (typ.balance_name) = UPPER ('Indemnity Days')
AND UPPER (dim.dimension_name) = UPPER ('Assignment Run');

select pay_balance_pkg.get_value (P_Balance_ID, P_Assignment_Action_ID, trunc(sysdate))
from dual

--Trunc(Sysdate) will return you balance value as of Date, you can use any old date as per your requirement.

--Disable Responsibilities for Users
--Normally such requirement occurs when you want that no user should be able to logged in during any particular time like some upgrade project or any critical migration activity. You can disable the users by following query.  Al though direct updates are not allowed but since this table does not contain object version id column so we used queries mentioned below. Please consult Oracle Support if you want to double check the impact of using this update statement.

update fnd_responsibility a
set a.END_DATE = to_date(’05-APR-2016?)
where a.RESPONSIBILITY_KEY in
(      SELECT distinct b.RESPONSIBILITY_KEY
FROM FND_USER_RESP_GROUPS A,
FND_RESPONSIBILITY_VL B,
FND_USER C,
PER_ALL_PEOPLE_F D
WHERE A.responsibility_id = B.responsibility_id AND
C.user_id = A.user_id AND
(to_char(A.end_date) IS NULL   OR   A.end_date > sysdate)
AND C.user_name = D.EMPLOYEE_NUMBER
and (b.RESPONSIBILITY_KEY like ‘%LSG%’)
and C.EMPLOYEE_ID is not null
)

--In order to reset the responsibilities back to original state

update fnd_responsibility a
set a.END_DATE = NULL
where a.RESPONSIBILITY_KEY in
(      SELECT distinct b.RESPONSIBILITY_KEY
FROM FND_USER_RESP_GROUPS A,
FND_RESPONSIBILITY_VL B,
FND_USER C,
PER_ALL_PEOPLE_F D
WHERE A.responsibility_id = B.responsibility_id AND
C.user_id = A.user_id AND
(to_char(A.end_date) IS NULL   OR   A.end_date > sysdate)
AND C.user_name = D.EMPLOYEE_NUMBER
and (b.RESPONSIBILITY_KEY like ‘%LSG%’)
and C.EMPLOYEE_ID is not null
)
and end_date IS not NULL
and end_date = ’05-APR-2016?

--Provide the List of Employees current and Previous Assignment Details
--(shows  Employee Organization Change history. Like wise we can change the query to show the history of Grade, Payroll, Organization, Location, Position, Job change summary)

select
ppf.employee_number
,paaf.assignment_number
,ppf.full_name
,paaf.organization_id current_org_id
,paaf.effective_start_date Curr_org_start_date
,(select name from hr_all_organization_units where organization_id = paaf.organization_id) current_org_name
,paaf_prev.effective_start_date prev_org_start_date
,paaf_prev.organization_id prev_org_id
,(select name from hr_all_organization_units where organization_id = paaf_prev.organization_id) prev_org
from per_all_assignments_f paaf,per_all_assignments_f paaf_prev,pay_people_groups ppg,pay_people_groups ppg_prev,per_all_people_f ppf
where paaf_prev.effective_end_date + 1= paaf.effective_start_date
and paaf_prev.assignment_id = paaf.assignment_id
and paaf_prev.assignment_type = 'E'
and ppf.employee_number = '1000063'
and paaf.assignment_type = 'E'
and paaf.organization_id <> paaf_prev.organization_id
and paaf.PEOPLE_GROUP_ID = ppg.people_group_id
and paaf_prev.PEOPLE_GROUP_ID = ppg_prev.people_group_id
and paaf.effective_start_date between ppf.effective_start_date and ppf.effective_end_date
and paaf.person_id = ppf.person_id
order by paaf_prev.effective_start_date desc

--Employee Short Leave Details and Hours Calculation
select paa.person_id,paa.date_start, paa.date_end,time_start,time_end
,round ( ( ((substr(time_end,1,2) -substr(time_start,1,2)) * 60
+ (substr(time_end,4,2) -substr(time_start,4,2))) / 60 ) ,2) Hours
from per_absence_attendances paa,per_absence_Attendance_types paat
where paa.person_id = 68567
and paa.absence_attendance_type_id = paat.absence_attendance_type_id
and paat.name = 'Short Leave'
and paa.date_start between :P_Period_Start_date and :P_Period_End_date
and paa.date_end between :P_Period_Start_date and :P_Period_End_date

Compile Forms command line

TEST

f60gen module=/u5/applupg2/uatappl/au/11.5.0/forms/US/APXINWKB.fmb userid=APPS/APPS
output_file=/u5/applupg2/uatappl/ap/11.5.0/forms/US/APXINWKB.fmx
module_type=form
batch=no
compile_all=special



PROD


f60gen module=/u3/applprd/prdappl/au/11.5.0/forms/US/APXINWKB.fmb userid=APPS/APPS
output_file=/u3/applprd/prdappl/ap/11.5.0/forms/US/APXINWKB.fmx
module_type=form
batch=no
compile_all=special

Check Database Locks

--To check database locks----[Kill Inactive Session] ----------------------------------------------------
SELECT * FROM dba_locks WHERE blocking_others='Blocking';

select LOCK_TYPE,SESSION_ID,BLOCKING_OTHERS from dba_locks where BLOCKING_OTHERS !='Not Blocking';

select process,sid, blocking_session from v$session where blocking_session is not null;

--Locked tables
SELECT l.session_id sid, o.object_name, o.object_type
FROM v$locked_object l, all_objects o
WHERE l.object_id = o.object_id

select SID,SERIAL#,MODULE,ACTION,CLIENT_IDENTIFIER,BLOCKING_SESSION_STATUS,STATE from v$session
where status='INACTIVE'
--where CLIENT_IDENTIFIER='%MONIR%'
AND MODULE like '%BOM%'
AND STATE='WAITING'
AND CLIENT_IDENTIFIER !='SYSADMIN'


--get SERIAL# where SID is from the above script
select * from v$session where SID=6563


--To kill the session with seesion id------------------------------------------------------------------
ALTER SYSTEM KILL SESSION '1351,993';

select * from v$session where process='144'



SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name 
FROM V$Locked_Object A, All_Objects B
WHERE A.Object_ID = B.Object_ID


select a.session_id,a.oracle_username, a.os_user_name, b.owner "OBJECT OWNER", b.object_name,b.object_type,a.locked_mode from
(select object_id, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from v$locked_object) a,
(select object_id, owner, object_name,object_type from dba_objects) b
where a.object_id=b.object_id;

SELECT a.object_id, a.session_id, substr(b.object_name, 1, 40)
FROM v$locked_object a, dba_objects b
WHERE a.object_id = b.object_id
AND b.object_name like 'BOM%'
ORDER BY b.object_name;




SELECT l.*, o.owner object_owner, o.object_name
FROM SYS.all_objects o, v$lock l
WHERE l.TYPE = 'TM'
AND o.object_id = l.id1
AND o.object_name in ('AP_INVOICES_ALL', 'AP_INVOICE_LINES_ALL', 'AP_INVOICE_DISTRIBUTIONS_ALL');




SELECT SID, SERIAL#
FROM v$session
WHERE SID = 960;



--R12: APXPAWKB Cannot Select this Payment Document Because it is in use By Another Single Payment (Doc ID 1322570.1)
--In the instance where user can reproduce the issue run the following queries:

1)

select * from dba_objects where object_name like 'CE_PAYMENT_DOCUMENTS'
and owner = 'CE';

2)

select * from v$locked_object where object_id in (select object_id from
dba_objects where object_name like 'CE_PAYMENT_DOCUMENTS'and owner = 'CE');

3)

select * from v$session where sid in (select session_id from
v$locked_object where object_id in (select object_id from dba_objects where
object_name like 'CE_PAYMENT_DOCUMENTS'and owner = 'CE'));

4)

select * from dba_locks where session_id in (select session_id from
v$locked_object where object_id in (select object_id from dba_objects where
object_name like ‘CE_PAYMENT_DOCUMENTS’and owner = ‘CE’));

Query result 3 provides the session which is having the lock of payment document.

Kill the session which is locking the 'CE_PAYMENT_DOCUMENTS' table

alter system kill session 'session,serial';

Application Check List

Application Information Gathering
=================================

Existing Operating Units
========================

select ORGANIZATION_ID, NAME
from hr_operating_units
order by ORGANIZATION_ID;


Registered Applications
=======================

select application_id, application_short_name, basepath
from fnd_application
order by application_id;

Registered ORACLE Schemas
=========================

select fou.oracle_id, fou.oracle_username
        , fou.install_group_num, fou.read_only_flag
        , decode(nvl(du.username,' X '),' X ','No','Yes') in_dba_users
        , nvl(du.default_tablespace,'Unknown') default_tablespace
        , nvl(du.temporary_tablespace,'Unknown') temporary_tablespace
from dba_users du, fnd_oracle_userid fou
where du.username(+) = fou.oracle_username
order by 1

Product Installation Status, Version Info and Patch Level
=========================================================

select decode(nvl(a.APPLICATION_short_name,'Not Found'),
        'SQLAP','AP','SQLGL','GL','OFA','FA',
        'Not Found','id '||to_char(fpi.application_id),
        a.APPLICATION_short_name) apps,
        decode(fpi.status,'I','Installed','S','Shared',
               'N','Inactive',fpi.status) status,
        fpi.product_version,
        nvl(fpi.patch_level,'-- Not Available --') Patchset,
        to_char(fpi.last_update_date,'dd-Mon-RRRR') "Update Date"
from fnd_oracle_userid o, fnd_application a, fnd_product_installations fpi
where fpi.application_id = a.application_id(+)
  and fpi.oracle_id = o.oracle_id(+)
order by 1,2;


 Product Database Configuration
 ==============================

select decode(nvl(a.APPLICATION_short_name,'Not Found'),
        'SQLAP','AP','SQLGL','GL','OFA','FA',
        'Not Found','id '||to_char(fpi.application_id),
        a.APPLICATION_short_name) apps,
        decode(nvl(o.ORACLE_username,'Not Found'),
               'Not Found','id '||to_char(fpi.oracle_id),
               o.ORACLE_username) ORACLE_username,
        decode(fpi.db_status,'I','Installed','L','Custom','N','Not Installed',
        'S','Shared',fpi.db_status) "DB Status",
        fpi.install_group_num,
        fpi.sizing_factor,
        fpi.tablespace, fpi.index_tablespace
from fnd_oracle_userid o, fnd_application a, fnd_product_installations fpi
where fpi.application_id = a.application_id(+)
  and fpi.oracle_id = o.oracle_id(+)
order by 1,2

Adding Report to Request Group using API

/**********************************************************************
*PURPOSE: To Add a Concurrent Program to a Request Group from backend *                                 
**********************************************************************/
--

DECLARE
    l_program_short_name    VARCHAR2 (200);
    l_program_application   VARCHAR2 (200);
    l_request_group         VARCHAR2 (200);
    l_group_application     VARCHAR2 (200);
    l_check                 VARCHAR2 (2);
--
BEGIN
    --
    l_program_short_name := 'XXINVNLCDNT';
    l_program_application := 'Inventory';
    l_request_group := 'OM Concurrent Programs';
    l_group_application := 'Order Management';
    --
    --Calling API to assign concurrent program to a reqest group
    --
    apps.fnd_program.add_to_group (
        program_short_name    => l_program_short_name,
        program_application   => l_program_application,
        request_group         => l_request_group,
        group_application     => l_group_application);
    --
    COMMIT;

    --
    BEGIN
        --
        --To check whether a paramter is assigned to a Concurrent Program or not
        --
        SELECT 'Y'
          INTO l_check
          FROM fnd_request_groups       frg,
               fnd_request_group_units  frgu,
               fnd_concurrent_programs  fcp
         WHERE     frg.request_group_id = frgu.request_group_id
               AND frg.application_id = frgu.application_id
               AND frgu.request_unit_id = fcp.concurrent_program_id
               AND frgu.unit_application_id = fcp.application_id
               AND fcp.concurrent_program_name = 'XXINVNLCDNT';

        --
        DBMS_OUTPUT.put_line (
            'Adding Concurrent Program to Request Group Succeeded');
    --
    EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
            DBMS_OUTPUT.put_line (
                'Adding Concurrent Program to Request Group Failed');
    END;
END;

Validating Parameters in Oracle Reports using PL/SQL

Parameters can be populated and validated using various srw pl/sql triggers.

The following gives examples of:

Validation trigger in parameter spread sheet 

Before parameter form trigger

After parameter form trigger

Before report trigger   

Examples of validation triggers on the property sheet for parameter PARAM_SAL. 

Query: select * from emp where sal > :PARAM_SAL

These functions validate just this one trigger. The validation occurs when 

the user hits next field after inputting a value for the parameter. When the 

trigger is failed it returns to the parameter form.

Example 1:

This trigger aborts the report execution if no rows match the query criteria 

once the user has entered a value for param_sal.

function PARAM_SALValidTrigger return boolean is

hold_count number(4);

hold_sal  number(10);

begin

  hold_sal := :param_sal;

  select count(*) into hold_count from emp where sal > hold_sal; 

  if hold_count = 0 then

     srw.message(001,'this report returns no employees');

     raise srw.program_abort;

  end if;

  return(true);

end;

Example 2

In this trigger the users value for param_sal is compared to the maximum 

salary in the EMP table. If it is greater the report execution is aborted.

example query for your report: select * from emp where sal >= :parm_sal

function PARAM_SALValidTrigger return boolean is

hold_max number(10);

begin

  select max(sal) into hold_max from emp;

  if :param_sal > hold_max then

     srw.message(002,'SAL must be equal to or less than MAX(SAL)= '||

     to_char(hold_max));

     raise srw.program_abort;

  end if;

  return(true);

end;

Example 3

'Before parameter form' triggers can be used set up the environment for the

report e.g. create a table. It can also be used to supply default parameter 

values.

This function populates the initial value of the parameter param_sal with the 

lowest salary value from the emp table.

function BeforePForm return boolean is

min_sal number(10);

begin

  select min(sal) into min_sal from emp;

  :param_sal := min_sal;

  return(true);

end;

Example 4

'After parameter form' triggers can be used to validate a combination of 

parameters. Failing results in a return to the PARAMETER FORM.

Query: select * from emp where job=:jb and deptno=:dt

function  AfterPForm return boolean is

begin

  if (:dt = 20) and (:jb = 'MANAGER') then 

     srw.message(003,'cannot report on Managers in Dept 20');

     raise srw.program_abort;

  end if;

  return(true);

end;

Example 5

'Before report triggers' can be used to validate a combination of parameters.

The example below is the same as the after parameter form trigger above 

other than on failure return is passed to the MAIN MENU.

A 'Before Report Trigger' is executed right before formatting the report,

that is after initializing all internal structures, opening all SQL cursors

etc. In other words, after 'compiling' the report definition.

A second use of this trigger may be to launch a number of other reports

using the SRW.RUN_REPORT procedure.

function BeforeReport return boolean is

begin

  if (:dt = 20) and (:jb = 'MANAGER') then 

     srw.message(004,'cannot report on Managers in Dept 20');

     raise srw.program_abort;

  end if;

  return(true);

end;

ORACLE REPORTS PERFORMANCE TIPS

Doc ID 61535.1

Performing operations in SQL may be faster than performing them in Oracle
Reports or PL/SQL.  The list below explains the most common cases where using
SQL would improve performance:

 -  perform calculations directly in your query rather than in a formula or
    summary,

 -  use a WHERE clause instead of a group filter or format trigger to exclude
    records,

 -  use the SUBSTR function to truncate character strings instead of
    truncating in Oracle Reports.

SQL can perform calculations more quickly than a summary or formula.  WHERE
and SUBSTR can reduce unnecessary fetching because they operate on the data
during, rather than after, data retrieval.


SRW.DO_SQL Statements
---------------------

SRW.DO_SQL enables you to add any DDL or DML operation to your report.  This
functionality is very valuable, but it can also be very expensive if used
unwisely.

Only use SRW.DO_SQL when necessary.  SRW.DO_SQL statements are parsed, a
cursor is opened to the database, and then the statement is executed.  Unlike
queries, an SRW.DO_SQL statement will do those things each time its owner
(a group) fetches data.  For example, if your SRW.DO_SQL statement is owned by
a group that fetches 10 records, the statement will be parsed 10 times, 10
cursors will be opened, and the statement will be executed 10 times.
Perform computations within the query or PL/SQL instead of SRW.DO_SQL
owned by a group.


CDE_MM.GET_REF
--------------

Only use the CDE_MM.GET_REF packaged procedure when necessary.  It is
intended to reduce the amount of temporary space used by Oracle Reports.
Oracle Reports will not cache a column retrieved via CDE_MM.GET_REF in a
temporary file.  While this reduces the need for temporary space, it slows
performance because the column's values must always be retrieved from the
database.


When You Should Use Multi-Query Data Models
-------------------------------------------

Reduce the number of queries in your report as much as possible.  The fewer
queries it contains, the faster your report will run.  Multi-query data models 
are easier to understand, but single-query data models tend to execute more 
quickly.

Use multi-query data models only when:

 - you are fetching many large columns from the parent and
   only a few small columns from the child,

 - you are trying to do things that SELECT does not support directly
   (multi-way outer join),

 - you have complex views (distributed queries or GROUP BY queries).

 - you need, but do not have or want, to use a view.

For a one-query report, only one cursor is opened to fetch all the master
and detail records.  For a two-query report, Oracle Reports opens two cursors
(one for each query) after appending the detail query's link to the WHERE
clause of the detail query.  For each master record fetched, Oracle must
rebind, execute, and fetch data from the detail query.


Indexes
-------

Be sure to have indexes on columns used in the SELECT statements' WHERE clauses,
on database key columns, and on the table(s) in the detail queries.  Indexes 
have little impact on master queries, because those queries access the database 
only once.  Indexes significantly improve performance of master/detail reports.
The lower the ratio of master to detail records, the more important indexes on
the detail query become for two-query reports.

Indexes are recommended for tables in the detail queries because Oracle
Reports implicitly creates a WHERE clause from the parent/child relationships
and adds it to the detail query.


IMPORTANT: Query Modifications
------------------------------

Oracle Reports modifies your queries in the following cases:

1.  For each link you create, Oracle Reports will append a clause to the
    child query as specified in the link.

    For example:
         SELECT deptno, ename, sal
         FROM emp
         WHERE sal > 1000

    If you create a link to this query using DEPTNO as the child column, a SQL
    clause of WHERE, and a condition of "equal to", then your query will be
    modified as follows:

         SELECT deptno, ename, sal
         FROM emp
         WHERE (sal > 1000) AND (deptno = :deptno)

    NOTE:  This is not true for multi-query matrix report data models.

2.  For each database column with Break Order set, Oracle Reports will
    PREPEND an ORDER BY clause to the query.

    For example:
         SELECT deptno, ename, sal
         FROM emp
         ORDER BY sal

    If you create a break group with DEPTNO as the break column, then your 
    query will be modified as follows:

         SELECT deptno, ename, sal
         FROM emp
         ORDER BY 1, sal

    These SQL statements will be sent to the database, then the SQL optimizer 
    will determine the optimal way to get the data from the database and return 
    it to Oracle Reports.  The optimizer will determine whether to use indexes, 
    which table to use as the "driving" table, and so forth.


Break Columns
-------------

When you create a break group, place as few columns as possible in the group.
Try to keep a 1:1 ratio of break columns to break groups.  Try to ensure
that the break column is as small as possible.  A break column that is shorter
in length will typically give better performance than a break column that is
longer.  For larger break columns, it may help performance to use the SUBSTR
function to reduce the length of the column.

For each break group, Oracle Reports prepends its break columns to the ORDER
BY clause of the query.  (The only exception to the rule is when the break
column is a formula column.)  By minimizing the number of break columns in
your break groups, you minimize the number of columns that are added to the
ORDER BY clause.  The fewer the columns added to the ORDER BY clause, the less
the processing that needs to be done when the query runs.  The size of the key
that Oracle Reports uses internally to build indexes will also be smaller,
resulting in better performance.


Maximum Rows And Group Filters
------------------------------

Use the Maximum Rows property in the Query property sheet to reduce the number
of records retrieved by the report's queries.  When designing a report that
accesses large amounts of data, you may want to restrict the amount of data
retrieved, so the report will run more quickly during testing.

Maximum Rows in the Query property sheet restricts the number of records
fetched by the query.  A group filter determines which records to include and
which records to exclude.  Since Maximum Rows actually restricts the amount of
data retrieved, it is faster than a group filter in most cases.

If you use a group filter of Last or Conditional, Oracle Reports must retrieve
all of the records in the group before applying the filter criteria.   Maximum
Rows or a Filter of First is faster.  Typically Maximum Rows is faster than a
Filter of First because it only retrieves as many records as needed.  The
performance difference may vary depending upon the ARRAYSIZE you have
specified.


Unused Data Model Objects
-------------------------

Make sure that you remove or suppress any data model objects that are not
actually used in your report.  If your data model includes a query that is
only used in certain cases (when a parameter is set to a certain value), you
can conditionally suppress the query with the SRW.SET_MAXROW packaged
procedure.  SRW.SET_MAXROW (queryname, 0) will cause the query to fetch no
records.


Unused Frames
-------------

Remove any unnecessary frames from the layout.  When Oracle Reports creates a
default layout, it puts frames around virtually everything.  This is done to 
protect the objects in the frames from being overwritten by other objects in 
the output.  If you know that the objects in the frames are not in danger of 
being overwritten, you can eliminate the frame without adversely affecting 
your report output.

The fewer objects in the layout, the fewer objects Oracle Reports must format
at runtime.  As a result, performance is better when you reduce the number of
objects in the layout.


Total Number Of Pages
---------------------

Limit your use of total number of pages as the source of fields (Total Logical
Pages).  When you use a total number of pages field source, Oracle Reports
must save all of the pages in temporary storage in order to determine the
total number of pages.  This can significantly increase the amount of
temporary disk space used by Reports, and the additional writing to files can
slow performance.


Format Triggers
---------------

Place PL/SQL in the Format Trigger of the object with the lowest frequency
possible.  PL/SQL in the Format Trigger of a frame instead of a field
typically makes the report run faster.

PL/SQL in Format Triggers is executed for each instance of its object.  The
lower the frequency of the object, the fewer times the PL/SQL will be executed
and the faster the report will run.


Oracle Graphics Integration
---------------------------

If an Oracle Graphics display referenced by a report uses some or all of the
same data as the report, pass the data from the report to the display.  If the
report and the display use the same data, passing the data reduces the amount
of fetching that needs to be done.  If you do not pass the data from the
report to the display, the data is actually fetched twice: once for the report
and once for the display.

Using SRW Packaged Procedures in Reports

Oracle Reports is shipped with a collection of PL/SQL constructs that contain

many functions, procedures, and exceptions that you can reference in your

libraries or reports.  The name of Oracle Reports' package is SRW.  Therefore,

anytime you reference a construct in the SRW package, you must prefix it with

SRW (such as, SRW.DO_SQL).

 SRW Packages

Oracle Reports contains the following packages:

-  SRW.BREAK                        -  SRW.RUN_REPORT_BATCHNO

-  SRW.CONTEXT_FAILURE              -  SRW.SET_FIELD_CHAR

-  SRW.DO_SQL                       -  SRW.SET_FIELD_DATE

-  SRW.DO_SQL_FAILURE               -  SRW.SET_FIELD_NUM

-  SRW.GETERR_RUN                   -  SRW.SET_MAXROW

-  SRW.GET_PAGE_NUM                 -  SRW.TRACE_ADD_OPTION

-  SRW.INTEGER_ERROR                -  SRW.TRACE_END

-  SRW.MAXROW_INERR                 -  SRW.TRACE_REM_OPTION

-  SRW.MAXROW_UNSET                 -  SRW.TRACE_START

-  SRW.MESSAGE                      -  SRW.TRUNCATED_VALUE

-  SRW.NULL_ARGUMENTS               -  SRW.UNKNOWN_QUERY

-  SRW.PROGRAM_ABORT                -  SRW.UNKNOWN_USER_EXIT

-  SRW.REFERENCE                    -  SRW.USER_EXIT

-  SRW.RUN_REPORT                   -  SRW.USER_EXIT20

-  SRW.SET_ATTR                     -  SRW.USER_EXIT_FAILURE

-  SRW.RUN_REPORT_FAILURE

Commonly Used SRW Packages And Examples

SRW.DO_SQL executes a specified SQL statement.  This procedure executes

any DDL or DML statements.  However, DML statements are usually faster

when they are in PL/SQL than when executed via SRW.DO_SQL.  Since you

cannot perform a DDL statement in PL/SQL, this packaged procedure is useful

for performing them within Reports rather than using a user exit.

Avoid DDL statements that modify the tables on which the report is based.  A

snapshot of the tables is taken prior to report execution and must remain

valid throughout the execution of the report.

Example 1 - SRW.DO_SQL

Syntax/Explanation:

  SRW.DO_SQL(sql_statement CHAR); -- Executes specified SQL statement.

  SRW.DO_SQL_FAILURE; -- Stops report execution upon SRW.DO_SQL failure.

  SRW.MESSAGE(msg_number NUMBER, msg_text CHAR);

                      -- Displays a specified message and message number.

  SRW.PROGRAM_ABORT;  -- Stops execution of report when raised.

Example:

Create a temporary table named TEMP prior to the Runtime Parameter Form:

FUNCTION createtab RETURN BOOLEAN IS

BEGIN

   SRW.DO_SQL(CREATE TABLE temp (empno NUMBER NOT NULL PRIMARY KEY,

                                 sal   NUMBER (10,2))

                             PCTFREE 5 PCTUSED 75');

   RETURN TRUE;

EXCEPTION

   WHEN SRW.DO_SQL_FAILURE THEN

      SRW.MESSAGE(100,'ERROR WHILE CREATING CHECK TABLE.');

      SRW.MESSAGE(50, 'REPORT WAS STOPPED BEFORE THE PARM FORM.');

      RAISE SRW.PROGRAM_ABORT;

END;

Example 2 - SRW.RUN_REPORT

Syntax/Explanation:

  SRW.RUN_REPORT(command_line CHAR);  -- Executes specified R25RUN command

  SRW.RUN_REPORT_FAILURE;    -- Stops report execution when failure of

                             -- SRW.RUN_REPORT occurs.

Example:

Drill Down Report called from a report button:

FUNCTION foo RETURN BOOLEAN IS

BEGIN

  SRW.RUN_REPORT('report=REP_A p_parm1=20');      -- calls report REP_A and

  EXCEPTION                                       -- display to screen;

     WHEN SRW.RUN_REPORT_FAILURE THEN             -- passes value 20 to the

       SRW.MESSAGE(100, 'ERROR CALLING REPORT.')  -- p_parm1 parameter

       RAISE SRW.PROGRAM_ABORT;

  RETURN TRUE;

END;

Note that you can only call another report to the screen using SRW.RUN_REPORT

from a button.  If you issue SRW.RUN_REPORT from a report trigger, you

must pass BATCH=YES.  Therefore, DESTYPE can only be FILE, PRINTER, or MAIL.

Example 3 - SRW.SET_ATTR

Syntax/Explanation

SRW.SET_ATTR applies attribute settings, such as font size or color, to layout

objects.  This procedure applies formatting attributes to the current frame,

repeating frame, field, or boilerplate object.  You specify which formatting

attributes should be applied to an object by defining a mask.  A mask is a

collection of attributes; each attribute has one or more values.  You can

change a number of attributes, such as the borderwidth, background border

color, border pattern, foreground border color, fill pattern, global text

color, weight and several other attributes.  For a complete list, refer to

the "The Oracle Reports PL/SQL Package: SRW" section in the

"PL/SQL Interface" chapter of the Oracle Reports Reference Manual.

SRW.SET_ATTR(object_id NUMBER, attr SRW.GENERIC_ATTR);

object_id   Is always zero.  (The object must always set its own attributes.)

attr        Is srw.attr (that is, the attributes to change and their values).

Example:

If salary equals 2000, this code segment sets the following attributes:

   Border width = 1

   Foreground border color = red

   Background border color = blue

   Border pattern = checker

   Foreground fill color = yellow

   Background fill color = green

   Fill pattern = crisscross

IF :sal = 2000 THEN

    SRW.ATTR.MASK  :=           SRW.BORDERWIDTH_ATTR       +

                                SRW.FBCOLOR_ATTR           +

                                SRW.BBCOLOR_ATTR           +

                                SRW.BORDPATT_ATTR          +

                                SRW.FFCOLOR_ATTR           +

                                SRW.BFCOLOR_ATTR           +

                                SRW.FILLPATT_ATTR;

    SRW.ATTR.BORDERWIDTH   := 1;

    SRW.ATTR.FBCOLOR       := 'red';

    SRW.ATTR.BBCOLOR       := 'blue';

    SRW.ATTR.BORDPATT      := 'checker';

    SRW.ATTR.FFCOLOR       := 'yellow';

    SRW.ATTR.BFCOLOR       := 'green';

    SRW.ATTR.FILLPATT      := 'crisscross';

SRW.SET_ATTR(0, SRW.ATTR);

END IF;

RETURN TRUE;

Example 4 - SRW. SET_FIELD

Syntax/Explanation:

SRW.SET_FIELD_CHAR(object_id, text CHAR);

SRW.SET_FIELD_DATE(object_id, date DATE);

SRW.SET_FIELD_NUM(object_id, number NUMBER);

These SRW SET_FIELD procedures are very useful in format triggers.  They are

often used to change the data that will be displayed in a particular item

based on a specific condition.  These are invaluable, since you cannot use

assignment statements to set a field's value in Reports.  SRW.SET_FIELD_XXX

must be used to accomplish this.

Example:

To display the text "LOW" in the salary_range field for every employee whose

salary is less than 20,000, create the following format trigger on the

salary_range field.

FUNCTION chgfield RETURN BOOLEAN IS

BEGIN

  IF :sal < 20000 THEN

     SRW.SET_FIELD_CHAR(0, 'LOW');

  ELSE

     END IF;

RETURN TRUE;

END;

For additional examples using the SRW.SET_FIELD_XXXX procedures in format

triggers, see:

Note31364.1  Using Format Triggers in Reports.

Running SQL*Trace on an Applications Report

There are two methods to running a Report with SQL*Trace which are:

Method #1:  Normally, in order to run a report with SQL*Trace, you would have to do the following:

    1. Turn SQL*Trace on by modifying your init.ora file.
    2. Shut down the concurrent managers and the database.
    3. Bring the database and the concurrent managers back up.
    4. Arrange to run JUST that specific report without running
       anything else.
    5. After running the report, shut down the database and
       concurrent managers.
    6. Turn SQL*Trace off by modifying your init.ora file.
    7. Bring the database and concurrent managers back up.

Method #2:  A simpler method is as follows (detailed instructions follow):

    1. Backup your current .rdf of the report.
    2. Modify the .rex for the report, adding two simple lines.
    3. Convert the new .rex into the new .rdf file.
    4. Run the report.
    5. Restore the original .rdf file.


Detailed instructions to run a Report with SQL*Trace using Method #2:

1. Backup your current .rdf and .rex files.
   
2. Create the .rex file if necessary:

   Make sure a .rex text file of the report exists.  (Customers are
   not shipped the .rex text file to save space on their system).
   If the .rex does not exist, create one by issuing the following
   command as the applmgr user:
 
     r25convm userid=<userid>/<password> source=<reportname>.rdf
     stype=rdffile dtype=rexfile dest=<reportname>.rex  
 
   For Example:

     r25convm userid=ar/ar source=RAXIIR.rdf stype=rdffile
     dtype=rexfile dest=RAXIIR.rex
 
3. Edit the .rex file:

   A. Pull the .rex file into your editor of choice and search
      for 'SRWINIT'.  You should find an entry similar to the
      following, with a NAME of 'beforerep____':

      Example I:

        DEFINE  TOOL_PLSQL
        BEGIN
          ITEMID           = 103
          NAME             = <<"beforerep0040">>
          PLSLFID_EP       = (BLONG) NULLP
          OBJECT_ID        = 0
          TYPE
          PLSLFID_ST       = (TLONG)
        <<"SRW.USER_EXIT('FND SRWINIT');
        ">>
        END

      Example II:

        BEGIN
          SRW.USER_EXIT('FND SRWINIT');
          IF (:p_debug_switch = 'Y') THEN
            SRW.MESSAGE('1','After SRWINIT');
          END IF;
        END

   B. Immediately after the ; which follows 'FND SRWINIT', (and
      remaining within the "" that surround the line) add the
      following line:

        srw.do_sql('alter session set sql_trace=TRUE');

      The modified versions of the above examples should look
      like the following:    

      Example I:

        DEFINE  TOOL_PLSQL                                
        BEGIN  
          ITEMID           = 103                                  
          NAME             = <<"beforerep0040">>        
          PLSLFID_EP       = (BLONG) NULLP            
          OBJECT_ID        = 0      
          TYPE                                                              
          PLSLFID_ST       = (TLONG)      
        <<"SRW.USER_EXIT('FND SRWINIT');
        srw.do_sql('alter session set sql_trace=TRUE');                      
        ">>                                                                  
        END
                                                                 
      Example II:
                                                           
        BEGIN                                                                
          SRW.USER_EXIT('FND SRWINIT');
          srw.do_sql('alter session set sql_trace=TRUE');                    
          IF (:p_debug_switch = 'Y') THEN                                    
            SRW.MESSAGE('1','After SRWINIT');                            
          END IF;                                                              
        END

   C. Find the line similar to the following, by searching
      for 'SRWEXIT':

        <<"SRW.USER_EXIT('FND SRWEXIT');
        ">>
        END

   D. Immediately after the ; which follows 'FND SRWEXIT',
      but remaining within the "", add the following line:

        srw.do_sql('alter session set sql_trace=FALSE');

      For Example:

        <<"SRW.USER_EXIT('FND SRWEXIT');
        srw.do_sql('alter session set sql_trace=FALSE');
        ">>
        END

      NOTE: This is really not necessary as the trace session
      will terminate when the report completes, but it's a
      good idea to include it as a precautionary measure.

4. Convert the modified .rex file into a .rdf file:
 
   To do this, issue the following command as the applmgr user -- rwcon60 (Reports 6.0.x) or r25convm (Reports 2.5.x) or r25mrepm (Reports 2.5.x):
   
     r25convm userid=<userid>/<password> source=<reportname>.rex
     stype=rexfile dtype=rdffile dest=<reportname>.rdf

   For Example:

     r25convm userid=ap/ap source=RAXIIR.rex stype=rexfile
     dtype=rdffile dest=RAXIIR.rdf
 
   NOTE: If you did not properly modify the .rex file, the system
   will not be able to generate the .rdf file.  An example of the
   message you may get is: "Conversion of 'RAXNCAR.rex' cancelled".

5. Run the report:

   Logon to the Applications and run the report through the concurrent
   managers.  When it has completed, exit the Applications.

6. Access the trace file:

   The trace file will be located in the directory specified by the  
   user_dump_dest variable in your init.ora file.    

7. Restore the original report files:

   Restore the original .rdf file (and .rex if one existed before  
   modification).  You may wish to backup the .rdf file containing
   the SQL*Trace code, just in case you need to run it at a later
   time.  Once the issue you're troubleshooting is resolved, you
   can delete it.


Click to add to Favorites Running SQL*Trace on an Applications Report (Doc ID 1019231.6) To BottomTo Bottom
Applies to:
Oracle Application Object Library - Version 11.5.10.2 to 12.1.3 [Release 11.5 to 12.1]
Information in this document applies to any platform.
***Checked for relevance on 10-Jan-2013***
Goal

 The goal of this document is to provide instructions to run a report with SQL*Trace
Solution

There are two methods to running a Report with SQL*Trace which are:

Method #1:  Normally, in order to run a report with SQL*Trace, you would have to do the following:

    1. Turn SQL*Trace on by modifying your init.ora file.
    2. Shut down the concurrent managers and the database.
    3. Bring the database and the concurrent managers back up.
    4. Arrange to run JUST that specific report without running
       anything else.
    5. After running the report, shut down the database and
       concurrent managers.
    6. Turn SQL*Trace off by modifying your init.ora file.
    7. Bring the database and concurrent managers back up.

Method #2:  A simpler method is as follows (detailed instructions follow):

    1. Backup your current .rdf of the report.
    2. Modify the .rex for the report, adding two simple lines.
    3. Convert the new .rex into the new .rdf file.
    4. Run the report.
    5. Restore the original .rdf file.


Detailed instructions to run a Report with SQL*Trace using Method #2:

1. Backup your current .rdf and .rex files.
   
2. Create the .rex file if necessary:

   Make sure a .rex text file of the report exists.  (Customers are
   not shipped the .rex text file to save space on their system).
   If the .rex does not exist, create one by issuing the following
   command as the applmgr user:
 
     r25convm userid=<userid>/<password> source=<reportname>.rdf
     stype=rdffile dtype=rexfile dest=<reportname>.rex  
 
   For Example:

     r25convm userid=ar/ar source=RAXIIR.rdf stype=rdffile
     dtype=rexfile dest=RAXIIR.rex
 
3. Edit the .rex file:

   A. Pull the .rex file into your editor of choice and search
      for 'SRWINIT'.  You should find an entry similar to the
      following, with a NAME of 'beforerep____':

      Example I:

        DEFINE  TOOL_PLSQL
        BEGIN
          ITEMID           = 103
          NAME             = <<"beforerep0040">>
          PLSLFID_EP       = (BLONG) NULLP
          OBJECT_ID        = 0
          TYPE
          PLSLFID_ST       = (TLONG)
        <<"SRW.USER_EXIT('FND SRWINIT');
        ">>
        END

      Example II:

        BEGIN
          SRW.USER_EXIT('FND SRWINIT');
          IF (:p_debug_switch = 'Y') THEN
            SRW.MESSAGE('1','After SRWINIT');
          END IF;
        END

   B. Immediately after the ; which follows 'FND SRWINIT', (and
      remaining within the "" that surround the line) add the
      following line:

        srw.do_sql('alter session set sql_trace=TRUE');

      The modified versions of the above examples should look
      like the following:    

      Example I:

        DEFINE  TOOL_PLSQL                                
        BEGIN  
          ITEMID           = 103                                  
          NAME             = <<"beforerep0040">>        
          PLSLFID_EP       = (BLONG) NULLP            
          OBJECT_ID        = 0      
          TYPE                                                              
          PLSLFID_ST       = (TLONG)      
        <<"SRW.USER_EXIT('FND SRWINIT');
        srw.do_sql('alter session set sql_trace=TRUE');                      
        ">>                                                                  
        END
                                                                 
      Example II:
                                                           
        BEGIN                                                                
          SRW.USER_EXIT('FND SRWINIT');
          srw.do_sql('alter session set sql_trace=TRUE');                    
          IF (:p_debug_switch = 'Y') THEN                                    
            SRW.MESSAGE('1','After SRWINIT');                            
          END IF;                                                              
        END

   C. Find the line similar to the following, by searching
      for 'SRWEXIT':

        <<"SRW.USER_EXIT('FND SRWEXIT');
        ">>
        END

   D. Immediately after the ; which follows 'FND SRWEXIT',
      but remaining within the "", add the following line:

        srw.do_sql('alter session set sql_trace=FALSE');

      For Example:

        <<"SRW.USER_EXIT('FND SRWEXIT');
        srw.do_sql('alter session set sql_trace=FALSE');
        ">>
        END

      NOTE: This is really not necessary as the trace session
      will terminate when the report completes, but it's a
      good idea to include it as a precautionary measure.

4. Convert the modified .rex file into a .rdf file:
 
   To do this, issue the following command as the applmgr user -- rwcon60 (Reports 6.0.x) or r25convm (Reports 2.5.x) or r25mrepm (Reports 2.5.x):
   
     r25convm userid=<userid>/<password> source=<reportname>.rex
     stype=rexfile dtype=rdffile dest=<reportname>.rdf

   For Example:

     r25convm userid=ap/ap source=RAXIIR.rex stype=rexfile
     dtype=rdffile dest=RAXIIR.rdf
 
   NOTE: If you did not properly modify the .rex file, the system
   will not be able to generate the .rdf file.  An example of the
   message you may get is: "Conversion of 'RAXNCAR.rex' cancelled".

5. Run the report:

   Logon to the Applications and run the report through the concurrent
   managers.  When it has completed, exit the Applications.

6. Access the trace file:

   The trace file will be located in the directory specified by the  
   user_dump_dest variable in your init.ora file.    

7. Restore the original report files:

   Restore the original .rdf file (and .rex if one existed before  
   modification).  You may wish to backup the .rdf file containing
   the SQL*Trace code, just in case you need to run it at a later
   time.  Once the issue you're troubleshooting is resolved, you
   can delete it.
References
NOTE:1070541.6 - How To Generate A Report ( .RDF File) From The Unix Command Line
NOTE:1020489.102 - How to Generate a Report from a Windows NT Command Line
NOTE:1019231.6 - Running SQL*Trace on an Applications Report