DEBUGGING APPLICATIONS WITH SQL*TRACE

I. How To Run SQL*Trace and Format the Trace File
The following document explains how to run SQL*Trace, and how to format the trace file that is created. This document can also be found in GSX by querying on the key words ‘trace’, ‘applications’ and ‘tkprof’.

SQL*Trace is a database tool analysts or customers can use to debug certain problems in Oracle Applications. SQL*Trace creates a file containing all SQL statements, and statistics for each statement executed during a user’s session.
This file can later be reviewed to determine:
· What SQL statements were executed.
· How long and how much CPU time did each script take
· How the database was accessed by each statement (Explain option)
SQL*Trace is especially useful for performance issues. Oracle developers WILL ask for 'trace output' for performance problems in order to find out exactly what the process is doing at the database level.
SQL*Trace may also help identify the SQL script that is causing other problems such as: · A process suddenly terminates with an ORA error. · Quickpick is not returning an expected value. · Query in a form is not returning an expected value.
SQL*Trace for form level processes can be turned on from the application menu. For processes run by the concurrent manager, such as executables and Oracle Reports, SQL*Trace must be turned on at the database level.
NOTE: It is possible to run SQL*Trace on an Oracle Report without shutting down the database; however, it requires modification of the report. Another bulletin is available which explains how to do this, query the problem repository on ‘r20conv’ and ‘trace’.
Running SQL*Trace at the Database Level
Following are the steps to turn SQL*Trace on at the database level. NOTE: This process should be performed by your database administrator, if you have one.
1. Edit your INIT.ORA or CONFIG.ORA (Server Administration Guide, Appendix A )
- This file resides under $ORACLE_HOME/dbs
- The INIT.ORA is read when the database is started.
- The following INIT.ORA parameters must be set:
SQL_TRACE = TRUE
USER_DUMP_DEST = TIMED_STATISTICS = TRUE
MAX_DUMP_FILE_SIZE =
2. Shutdown Concurrent Manager and Database.
- Shutdown the concurrent manager first. Then shut down the database.
- Make sure that no users are on the system.
3. Restart the Database and the Concurrent Manager.
4. Run the process.
SUGGESTION: Do not have other users on the system while trace is on. Only run the process in question. This will make finding the problem much easier.
5. Check the USER_DUMP_DEST directory for the trace output.
NOTE: An easy way of determining the directory a trace file is written to is to run the following SQL:
Select value from v$parameter where name = 'user_dump_dest';
6. Find the trace file for your process.
- If there are multiple trace files use the creation date to help determine which file is yours. In UNIX, use 'ls -lrt'.
- If there are multiple files for the same session, they may all be relevant.
- The file names will be *.trc.
7. Shutdown and restart the database with the original INIT.ORA.
Using TKPROF to Format the Trace File
The trace file that is created contains statistics on how long each SQL script took, but this information needs to be formatted to be readable. Use tkprof on the trace file to format it into readable performance statistics. Tkprof also formats the SQL scripts making them easier to read.
Before running tkprof on the trace file:
- Make sure the table plan_table exists.
- If not, find the command to create the table from the Server Application Developer's Guide, page B-11.
TKPROF Format:
tkprof tracefile outputfile [explain= ] [table= ] [print= ] [insert= ] [sys= ] [sort= ]
Examples:
1. tkprof ora_11598.trc myfilename
2. tkprof ora_11598.trc /tmp/myfilename
3. tkprof ora_11598.trc /tmp/myfilename explain=apps/apps
4. tkprof ora_23532.trc myfilename explain=apps/apps sort=execpu
NOTES: · In order to create the tkprof file, you must have write access in the directory where you are creating the file. If you do not have write access in the trace directory, then specify a directory where you do have write access. The /tmp directory is a possible directory to use since users should have write access to this directory.
Example #2 shows how to do this. · The values listed in [ ] are optional; however, explain should always be used if the issue is a performance problem. Explain should be followed by the userid/passwd of the product that SQL*Trace was executed from.
See Example #3. · For more information, type and hit return at the operating system prompt. This will return an explanation on the usage of tkprof, as well as the optional parameters. ·
Example #4 shows how to sort the SQL scripts in order of the CPU time spent executing. This will list the SQL scripts that use the most CPU time at the top of the file. This is useful for identifying performance problems in large trace files.
Setting Up Form Level Trace
Following are the steps to set up SQL*Trace from your application menu. This is used to turn SQL*Trace on while in a form. (It is not necessary to shut down the database when running SQL*Trace from a form).
1. Go to the Application Developer responsibility.
2. Navigate to the Define Menu form. This will vary slightly with Application version.
3. Query the Menu for which you would like to add Trace. (This is typically added to the menu OTHER.)
4. Add the following entry in the Menu Entries zone.
Prompt Description Type Application Name Arguments
Trace SQL Trace Menu Appl. Object Library TRACE
5. Commit.

UTL_FILE - Random Access of Files

UTL_FILE - Random Access of Files
I was recently asked if I could read the first and last line of a file using PL/SQL. Until recently this was not possible without reading the whole file or using a Java stored procedure, but Oracle9i Release 2 supports random access of files through the UTL_FILE package. This article shows a simple mechanism to solve this problem using these UTL_FILE enhancements.
First we create a directory object pointing to the location of the file of interest:
CREATE OR REPLACE DIRECTORY my_docs AS '/usr/users/oracle/';
Prior to Oracle9i Release 2 I would solve this problem by reading the whole file as follows:
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
l_file UTL_FILE.file_type;
l_location VARCHAR2(100) := 'MY_DOCS';
l_filename VARCHAR2(100) := 'temp';
l_text VARCHAR2(32767);
BEGIN
-- Open file.
l_file := UTL_FILE.fopen(l_location, l_filename, 'r', 32767);
-- Read and output first line.
UTL_FILE.get_line(l_file, l_text, 32767);
DBMS_OUTPUT.put_line('First Line: ' l_text '');
-- Read through the file until we reach the last line.
BEGIN
LOOP
UTL_FILE.get_line(l_file, l_text, 32767);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
-- Output the last line.
DBMS_OUTPUT.put_line('Last Line : ' l_text '');
-- Close the file. UTL_FILE.fclose(l_file);
END;
/
Using the UTL_FILE enhancements we can now do the following:
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
l_file UTL_FILE.file_type;
l_location VARCHAR2(100) := 'MY_DOCS';
l_filename VARCHAR2(100) := 'temp';
l_exists BOOLEAN;
l_file_length NUMBER;
l_blocksize NUMBER;
l_text VARCHAR2(32767);
BEGIN
UTL_FILE.fgetattr(l_location, l_filename, l_exists, l_file_length, l_blocksize);
IF l_exists THEN
-- Open file.
l_file := UTL_FILE.fopen(l_location, l_filename, 'r', 32767);
-- Read and output first line.
UTL_FILE.get_line(l_file, l_text, 32767);
DBMS_OUTPUT.put_line('First Line: ' l_text '');
UTL_FILE.FSEEK (l_file, l_file_length-1);
-- Step backwards through the file until we reach the start of the last line.
FOR i IN REVERSE 0 .. l_file_length-2
LOOP
UTL_FILE.FSEEK (l_file, NULL, -2);
UTL_FILE.get_line(l_file, l_text, 1);
EXIT WHEN l_text IS NULL;
END LOOP;
-- Read and output the last line.
UTL_FILE.get_line(l_file, l_text, 32767);
DBMS_OUTPUT.put_line('Last Line : ' l_text '');
-- Close the file. UTL_FILE.fclose(l_file);
END IF;
END;
/
The fgetattr procedure allows us to check that the file exists and return the file length. We then read the first line using the get_line procedure as normal. To get the last line we need to skip to the end of the file using the fseek procedure and work backwards until we hit a line terminator. The get_line procedure does not return line terminators so we detect it's presence by checking for the return of an empty line. We can then display the last line.
I'm not too sure about the performance of the fseek procedure. For large files it's often quicker to read the whole file which is a bit disappointing. Even so, if you need to move both backwards and forwards in the file, these enhancements may still be useful.

KFF and DFF

Steps Involved :
1. Registering A Tables & Columns :
Register the Table & Columns if the KFF/DFF is on User Defined Table. The following is the
example
AD_DD.register_table('AR','RAJ_KFF_TEST','T',8,10,90);
ad_dd.register_column('AR','RAJ_KFF_TEST','Attribute_category',1,'Varchar2',20,'N','N');
ad_dd.register_column('AR','RAJ_KFF_TEST','CCID',2,'NUMBER',10,'N’,'N');
ad_dd.register_column('AR','RAJ_KFF_TEST','attribute1',3,'VARCHAR2',20,'N','N');
For a KFF ....
It is necessary to have a column to store CCID and a column to Store Structure Information In the above example the Attribute_category is used to store Structure Info.
and CCID to store ccid number. These are specified at Registration of the Flexflied.
For A DFF...
It is enough to store only the Structure Info.
Example the Attribute_category may act as an Structure Field.
2. Registering The FlexFiled :
After creating the table and registering it, register the FlexField You You want to Use.
Remember : U have to Use this name when referencing the flexfield.
Also u have to specify the CCID column & Structure Column for KFF here.
Remember : U have to enable the columns of the table here, otherwise u can't define segments for the same.
3. Defining the Segments :
Every Flexfiled must have Segment Qualifiers And FlexField columns these are defined in the AOL
at Segment Definition.
After defining these segments freeze and compile the flexfield segments definition.
These 3 steps complete the process of Registration of table and flexfield & definition of Segments.
Some Finer Points :
1. You have to check the Dynamic Insertion Allowed ( for KFF only) to allow the users to dynamically create
an intelligent combination key.
2. You can check the Protect check box to ensure that users do not change the definition of flexfield by mistake.
Incorporating DFF's / KFF's in the forms :
The whole process essentially requires 4 steps ....
1. Modify 7 triggers
2. Create Hidden Fields corresponding to the segments
3. Define the FlexField in the New-Form-Instance-Trigger
4. Set the Profile Options ( Flex:Open_Descr_Window , Flex:Open-Key_Window ) to YES

Generic Activities to Open A Form In Apps ....
You have to go through some steps to enable a form in Apps.
1. Open your Template.fmb ( this is provided by Oracle-Apps) .
2. Delete the BLOCKNAME ( datablock , canvas , window ) from the Template.fmb
3. Save this form Module with ur custom name.
4. Create A Canvas , subclass it with Profile class Canvas
5. Similary create a Window ( subclass it with Window profile class) , Datablock.
6. Modify the APPS_CUSTOM package body with following code....
if (wnd = '') then
app_window.close_first_window;
Give your name of Window at the Bolded place.
7. Modify The pre-form trigger as follows..
app_window.set_window_position('BLOCKNAME', 'FIRST_WINDOW');
Give Your block name here at the bolded place.
Now modify the required triggers for implementing the DFF's and KFF's .......
1. The Following triggers should be change...
a. When-new-form-instance
b. pre-query
c. post-query
d. WHEN-NEW-ITEM-INSTANCE
e. PRE-UPDATE
f. WHEN-VALIDATE-RECORD
g. WHEN-VALIDATE-ITEM
add FND_FLEX.EVENT('EVENT NAME') where EVENT NAME is the trigger name itself.
eg. FND_FLEX.EVENT('WHEN-VALIDATE-ITEM');
2. Create Hidden Fields ( Set Canvas to null) . These should be as many as the number of segments u defined .
3. Define Your flex-filed at the New-Form-Instance trigger as follows ...
FND_KEY_FLEX.DEFINE(BLOCK=>'FLEX_BLOCK',FIELD=>'KFF_SEGS',APPL_SHORT_NAME=>'AR', DESC_FLEX_NAME=>'DFF_FLEX')
for DFF....
Block - is the block name in the form
Field - Field which is acting as DFF/KFF
Appl_Short_name :- AR/ AP / CS etc.,
Desc_FLEX_NAME :- Name of the Flex Filed U have given at the time of Registration.
For KFF.....
CODE - the number u give at the time of registering ur KFF
NUM - Use the following SQL to get the number . ( default is 101 )
SELECT ID_FLEX_NUM FROM FND_ID_FLEX_STRUCTURES WHERE ID_FLEX_CODE='CODE';
4. Create a TEXT-FIELD to act as DFF or KFF
For DFF subclass it with ....
TEXT_ITEM_DESC_FLEX
For Kff subclass it with ...
TEXT_ITEM itself
5. Save ur work and
attach this form to a function
Function to a menu.
That's all....
Good Luck !! Happy Experimenting...

Important Tables and colums in GL,AP,AR,PO and HRMS

Gl_CODE_CONMBINATIONS,
-CODE_COMBINATION_ID.
-CART_OF _ACCOUNTS_ID.
-DETAIL_POSTING_ALLOWED_FLAG.
-ACCOUNT_TYPE.
-TEMPLATE_ID.
SEGMENT1,SEGMENT2,
GL_BALANCES
-SET_OF_BOOKS_ID,
-CURRENCY_CODE,
-CODE_COMBINATION_ID,
-ACTUAL_FLAG,
-PERIOD_NET_DR
-PERIOD_NET_CR
GL_LOOKUPS
-LOOKUP_TYPE,
-LOOKUP_CODE,
-MEANING,
-DESCRIPTION,
-ENABLED FLAG,
-START_DATE_ACTIVE,
-END_DATE_ACTIVE,
-description ACCT_TYPE,
GL_SETS_OF_BOOKS
-SET_OF_BOOKS_ID,
-CURRENCY_CODE,
-CHART_OF_ACCOUNTS_ID,
-NAME,
-PERIOD_SET_NAME,
-DESCRIPTION,
GL_PERIOD_SETS
-PERIOD_SET_NAME,
-DESCRIPTION,
-ATTRIBUTES1
-PERIOD_SET_ID.
AP_PAYMENT_SCHEDULES
-INVOICE_ID,
-PAYMENT_CROSS_RATE,
-PAYMENT_NUM,
-AMOUNT_REMAINING,
-DUE_DATE,
-FUTURE_PAY_DUE_DATE,
-GROSS_AMOUNT,
-PAYMENT_METHOD,
-PAYMENT_PRIORITY,
-PAYMENT_STATUS
AP_INVOICE_ALL
-INVOICE_ID,
-VENDOR_ID,
-INVOICE_NUM,
-SET_OF_BOOKS_ID,
-INVOICE_CURRENCY_CODE,
-PAYMENT_CURRENCY_CODE,
-INVOICE_AMOUNT
-VEODOR_SITE_ID,
-AMOUNT_PAID.
PO_VENDORS
-VENDOR_ID,
-VENDOR_NAME,
-SEGMENT1,
-SUMMARY_FLAG,
-ENABLED_FLAG,
-EMPLOYEE_ID,
-VENDOR_TYPE_LOOKUP_CODE,
-SHIP_LOCATION_ID.
PO_VENDOR_SITES
-ADDRESS_STYLE,
-LANGUAGE,
-PROVINCE,
-COUNTRY,
-AREA_CODE,
-PHONE,
-SHIP_TO_LOCATION,
-BILL_TO_LOCATION,
-PAYMENT_METHOD,
-BANK_ACCT_METHOD,
-BANK_ACCT_NAME,
-BANK_ACCT_NUMBER
AP_LOOKUP_CODES
-LOOKUP_TYPE,
-LOOKUP_CODE,
-DISABLED_FIELD,
-DESCRIPTION
-ENABLED_FLAG,
AP_DOC_SEQUENCE_AUDIT
-DOC_SEQUENCE_ID,
-DOC_SEQUENCE_ASSIGNMENT_ID,
-DOC_SEQUENCE_VALUE,
-CAREATION_DATE,
-CREATED_BY
FND_DOC_SEQUENCE_ASSIGNMENTS
-DOC_SEQUENCE_ASSIGNMENT_ID,
-CAREATED_BY,
-APPLICATION_ID,
-DOC_SEQUENCE_ID,
-CATEGORY_CODE,
-SET_OF_BOOKS_ID
-METHOD_CODE,
FND_COLUMNS
-APPLICATION_ID,
-TABLE_ID,
-COLUMN_ID,
-COLUMN_NAME,
-USER_COLUMN_NAME,
-COLUMN_SEQUENCE,
-WIDTH,
-NULL_ALLOWED_FLAG,
-TRANSFLATE_FLAG,
FLEXFILED_USAGE_CODE,
-DESCRIPTION.
RA_CUSTOMERS
-ROW_ID
-CUSTOMER_ID,
-PARTY_ID,
-PARTY_NUMBER,
PARTY_TYPE,
CUSTOMER_NAME,
-CUSTOMER_NUMBER
-ORIG_SYSTEM_NUMBER.
-STATUS,
-CUSTOMER_TYPE,
RA_ADRESS_ALL
-ROW_ID,
-PARTY_SITE_ID,
-PARTY_ID
-PARTY_LOCATION_ID,
KEY_ACCOUNT_FLAG,
-PROGRAM_UPDATE_DATE,
-TERRITORY _ID,
-ADDRESS_KEY,
RA_SITE_USES_ALL
-SITE_USE_ID,
-SITE_USE_CODE,
-ADDRESS_ID,
PRIMARY_KEY,
-STATUS,
-LOCATION,
-CONTACT_ID,
-BILL_TO_SITE_USE_ID,
-ORIG_SYSTEM_REFERENCE,
-WAREHOUSE_ID,
-ORDER_TYPE_ID,
AR_PAYMENT_SCHEDULES
-PAYMENT_SCHEDULE_ID,
-DUE_DATE,
-AMOUNT_DUE_ORIGINAL,
AMOUNT_REMAINING,
-NUMBER_OF_DUE_DATES,
-STATUS,
-INVOICE_CURRENCY_CODE,
CUST_TRX_TYPE_ID,
-CUSTOMER_ID,
-CUSTOMER_TRX_ID
AR_CASH _RECEIPTS_V
-ROW_ID,
-CASH_RECEIPT_ID,
-AMOUNT,
-FUNCTIONAL_AMOUNT,
-NET_AMOUNT,
-CURRENCY_CODE,
-RECEIPT_NUMBER,
-RECEIPT_DATE,
-TYPE.
RA_CUSTOMER_TRX_PARTIAL_V
-ROW_ID,
-CUSTOMER_TRX_ID,
-TRX_NUMBER,
-OLD_TRX_NUMBER,
-TRX_DATE,
-TERM_DUE_DATE,
-INITIAL_CUSTOMER_TRX_ID,
-BATCH_ID,
-TERM_ID
RA_CUSTOMER_TRX_LINES_V
-ROW_ID,
-CUSTOMER_TRX_LINE_ID,
-CUSTOMER_TRX_ID,
-DESCRIPTION.
RA_PAYMENTS_SCHEDULES_ALL
-PAYMENT_SCHEDULE_ID,
-DUE_DATE,
-AMOUNT_DUE_ORIGINAL,
-AMOUNT_DUE_REMAINING,
-NUMBEr_OF_DUE_DATES,
-STATUS,
-INVOICE_CURRENCY_CODE,
-CLASS,
-TERMD_ID.
RA_CUSTOMER_TRX_ALL
-CUSTOMER_TRX_ID,
-TRX_NUMBER,
-CUSTOMER_TRX_TYPE_ID,
-TRX_DATE,
-SET_OF_BOOKS_ID,
-BILL_TO_CONTACT_ID,
-BATCH_ID,
-SHIP_TO_CUSTOMER_ID,
-SHIP_TO_SITE_USE_ID.
AR_CUSTOMER_PROFILES
-CUSTOMER_PROFILE_ID,
-CUSTOMER_ID,
-STATUS,
COLLECTOR_ID,
-CREDIT_CHECKING
-TOLERANCE,
-CUSTOMER_PROFILE_CLASS_ID,
-SITE_USE_ID,
-CREDIT_RATING
HRMS TABLES BY SCREEN WISE.
================================
PEOPLE(SCREEN)
-ENTER AND MAINTAIN
PER_PEOPLE_V
PER_ADDRESS_V --------ADDRESSES OF EMPLOYEES
PER_IMAGES -------STORES EMPLOYEE PHOTOS
PER_ASSIGNMENTS_V ------STORES_ASSIGNMENTS
PER_PAY_PROPOSALS_V2 –STOES SALARIES
PAY_PAYWISE_ELEMENT_ENTRIES---ENTRIES
PER_SPEICAL_INFO_TYPES_VS ---SPECIAL INFORM
WORKSTRUCTURES
LOCATIONS
-HR_LOCATIONS_V
ORGANIZATION
-HR_ORGANIZATION_UNITS_V
HIERARCHY
-PER_ORGANIZATION_STRUCTURES_V
JOBS
-PER_JOBS_VL
CAREER PATHS
-PER_CAREER_PATHS
PAYROLLS
DESCRIPTION
-PAY_PAYROLLS_V2
PAYMENT METHOD
-PAY_ORG_PAYMENT_METHODS_V
GL MAPPING SEGMENT
-PAY_PAYWSPGL_PAYROLLS
PER_PEOPLE_V
Person_id,employee_number,full_name,nationality,
Business_group_id,marital_status,original_date_of_hire,sex,
Current_employee_flag,effective_start_date,effective_end_date,title.
PER_ALL_ASSIGNMENTS_F
Assignment_id,Person_id,Job_id,Grade_id,Organoization_id
Business_group_id,Location_id,Supervisor_id,Position_id,
Recruiter_id,Primary_Flag,Effective_start_date,Effective_end_date,,
Payroll_id.
PER_JOBS HR_ALL_POSITIONS_F
Job_id,Business_group_id Psotion_id,Business_grouip_id,
Job_definition_id,Name, Position_defintion_id,Name,
status,Date_from Status,Date_from
PER_GRADES
Postion_id,
Business_grouip_id,
Position_defintion_id,
Name,satus,Date_from
PER_JOB_DEFINITIONS
Job_definition_id
PER_GRADE_DEFINITIONS
Summary_flag, Grade_definition_id,
Segment1, Summary_flag,
Segment2, Segment1
Enabled_flag Segment2
Enablled_Flag
PER_ADDRESSES_V
(It stores all the address and contact details of every Employee.The primary column is Row_id)
Columns: row_id,address_id,business_group_id,person_id,Priamry_flag,style,address_line1,
address_line2,address_type,postal_code
PER_IMAGESLIt stores the photo images of Employee by maintaining link to the per_people_f table .Images are stored in the in the format of BLOB)
Columns: Row_id,special_information_types,business_group_id,id_flex_num,Name,Enabled_Flag etc.
PAY_PAYWSMEE_ELEMENT_ENTRIES
(It Stores the elements)
Columns: Element_entry_id,Assignment_id,Effective_start_date,Effective_end_date,
Element_link_id,Original_entry_id ETC,
PAY_PAYROLLS_F:(Stores payroll type)
Columns: Payroll_id, _id,Effective_start_date,Effective_end_date,default_payment_method_id,
Business_group_id,period_type,payroll_name,gl_set_books_id.
PER_TIME_PERIODS_V(Stores the period of payroll)
Columns: row_id,time_period,payroll_id.
HR_LOCATIONS
Location_id,Location_code,business_group_id,description_style,Address_line1,Address_line2
Town_or_City,Country,ship_to_location_id.
HR_ALL_ORGANIZATION_UNITS
Organization_id,Business_group_id,Location_id,Date_from,Name,type,Internal_External_flag
HR_ALL_POSITIONS_F
Position_id,Effective_start_date,Effective_end_date,business_group_id,Job_id,Location_id,
Organization_id,Date_Effecticve,Name,Permanent_Temporary_Flag,Position_type.
PER_PERSON_ANALYSES
(It stores special information types SIT)
Analysis_criteria_id
HR_LOCATIONS
LOCATION_ID,Location_code,Business_group_id,Description_style,Address_line1,
Address_line2,Town_or_city,Country,Ship_to_Location_id.
HR_ALL_ORGANIZATIONS_UNITS
Organization_id,Business_group_id,Location_id,Date_from,Name,Type,
Internal_Eternal_Flag
HR_ALL_PEOPLE_F
Position_f,Effective_start_date,Effective_end_dateBusiness_group_id,Job_id,Location_id,Organization_id,Date_Effective,Name,Permanent_temporary_flag,Position_type. PER_ALL_PEOPLE_F
Person_id

Few Questions to have a look

What is the interface?
Interface Table is a table which is used as medium for transfer of data between two systems.
What is INBOUND and OUT BOUND? (Different types of interfaces)
Inbound Interface:For inbound interfaces, where these products are the destination, interface tables as well as supporting validation, processing, and maintenance programs are provided.
Outbound Interface:For outbound interfaces, where these products are the source, database views are provided and the destination application should provide the validation, processing, and maintenance programs
What is multi org?
Legal entity has more than one operating unit is called as multi org”a) Business group --- Human resources information is secured byBusiness groupb) Legal entity. --- inter-company and fiscal/tax reporting.Security responsibility operating unit.c) Operating unit --- secures AR, OE, AP, PA and PO Information.d) Organizations --- is a specialize unit of work at particular locations
What are the User PARAMETERS in the Reports?P_CONC_REQUEST_IDP_FLEX_VALUEFND USER EXITS:-FND SRWINIT sets your profile option values, multiple organizations and allows Oracle Application ObjectLibrary user exits to detect that they have been called by an Oracle Reports program.FND SRWEXIT ensures that all the memory allocated for AOL user exits have been freed up properly.FND FLEXIDVAL are used to display flex field information like prompt, value etcFND FLEXSQL these user exits allow you to use flex fields in your reportsFND FORMAT_CURRENCY is used to print currency in various formats by using formula column
What are the requests groups?
a) Single request: - this allows you to submit an individual request.
b) Request set : - this allows you to submit a pre-defined set of requests.
Difference between Bind and Lexical parameters?
BIND VARIABLE :are used to replace a single value in sql, pl/sql bind variable may be used to replace expressions in select, where, group, order by, having, connect by, start with cause of queries.bind reference may not be referenced in FROM clause (or) in place of reserved words or clauses.
LEXICAL REFERENCE:you can use lexical reference to replace the clauses appearing AFTER select,from, group by, having, connect by, start with. you can’t make lexical reference in a pl/sql ements.
update clause:1) use explicit locking to deny access for the duration of a transaction2) lock the rows before update or deleteEx : select …….From…….For update[ of column ref] [no_wait]where current of clause?
1) use cursor to update or delete the current rowWhere current of <>
What is the package?Group logically related pl/sql types, items and subprograms.1. package specification2. package body
Advantages of a package:
A. Modularity
B. Easier Application Design
C.Information Hiding
D,OverloadingYou cannot overload:•Two subprograms if their formal parameters differ only in name or parameter mode. (datatype and theirtotal number is same).•Two subprograms if their formal parameters differ only in datatype and the different datatypes are in thesame family (number and decimal belong to the same family)•Two subprograms if their formal parameters differ only in subtype and the different subtypes are basedon types in the same family (VARCHAR and STRING are subtypes of VARCHAR2)•Two functions that differ only in return type, even if the types are in different families.
What are triggers?
triggers are similar to procedures, in that they are the named pl/sql blocks with declarative,executable and exception-handling sections, how ever a procedure is executed explicitly from another block via a procedure call, which can also pass arguments.A trigger is executed implicitly when ever a particular event task places. And is nothing but a event.The triggering event is a DML (insert, update, delete) operations on a data base tablefires whenever a data event(such as DML) or system event(such as login or shutdown) occurs on a schema or database
Trigger timing :
1) before
2) after
3) instead of ( this is used for views)
events :
1) insert
2)update
3) delete
Trigger type :
1) statement level
2) row level.
Firing sequence of database triggers
1. before statement trigger
2. before row trigger
3. after row trigger
4. after statement trigger
LOCKS?Is to reduce concurrency
1) share lock
it allows the other users for only reading not to insert
2) exclusive lock
only one user can have the privileges of insert orothers can only read.
3) update lock
multiple user can read, update delete
Lock levels :
1) table level
2) table space
3) data base level.
What are ad-hoc reports?
Ad-hoc Report is made to meet one-time reporting needs. Concerned with or formed for a particular purpose. For example, ad hoc tax codes or an ad hoc database query