How to Register a custom table in Oracle 11i/R12.

You can register your custom application tables using a PL/SQL routine in the AD_DD package.
Flexfields and Oracle Alert are the only features or products that depend on this information. Therefore you only need to register those tables (and all of their columns) that will be used with flexfields or Oracle Alert.
You can also use the AD_DD API to delete the registrations of tables and columns. You should delete the column registration first, then the table registration.

To alter a registration you should first delete the registration, then re-register the table or column.
The AD_DD API does NOT check for the existence of the registered table or column in the database schema, but only updates the required AOL tables. You must ensure that the tables and columns registered actually exist and have the same format as that defined using the AD_DD API.

You need NOT register views.

You should include calls to the table registration routines in a PL/SQL script.

You should run the AD_DD procedures against the APPS schema. You must commit your changes for them to take effect.

Procedures in the AD_DD Package:
procedure register_table (p_appl_short_name in varchar2,
p_tab_name in varchar2,
p_tab_type in varchar2,
p_next_extent in number default 512,
p_pct_free in number default 10,
p_pct_used in number default 70);

p_appl_short_ name: The application short name of the application that owns the table (usually your custom application).
p_tab_name: The name of the table (in uppercase letters).
p_tab_type: Use 'T' if it is a transaction table (almost all application tables), or 'S' for a "seed data" table.
p_next_extent: The next extent size, in kilobytes. Do not include the 'K'.
p_pct_free: The percentage of space in each of the table's blocks reserved for future updates to the table (1-99).
p_pct_used: Minimum percentage of used space in each data block of the table (1-99). The sum of p_pct_free and p_pct_used must be less than 100.

procedure register_column (p_appl_short_name in varchar2,
p_tab_name in varchar2,
p_col_name in varchar2,
p_col_seq in number,
p_col_type in varchar2,
p_col_width in number,
p_nullable in varchar2,
p_translate in varchar2,
p_precision in number default null,
p_scale in number default null);

p_col_name: The name of the column (in uppercase letters).
p_col_seq: The sequence number of the column in the table (the order in which the column appears in the table definition).
p_col_type: The column type ('NUMBER', 'VARCHAR2', 'DATE', etc.).
p_col_width: The column size (a number). Use 9 for DATE columns, 38 for NUMBER columns (unless it has a specific width).
p_nullable: Use 'N' if the column is mandatory or 'Y' if the column allows null values.
p_translate: Use 'Y' if the column values will be translated for an Oracle E-Business Suite product release (used only by Oracle E-Business Suite products) or 'N' if the values are not translated (most application columns).
p_precision: The total number of digits in a number.
p_scale: The number of digits to the right of the decimal point in a number.

procedure delete_table (p_appl_short_name in varchar2,
p_tab_name in varchar2);

procedure delete_column (p_appl_short_name in varchar2,
p_tab_name in varchar2,
p_col_name in varchar2);

Example:

EXECUTE ad_dd.register_table('FND', 'CUST_FLEX_TEST', 'T', 8, 10, 90);
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'APPLICATION_ID', 1, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'ID_FLEX_CODE', 2, 'VARCHAR2', 30, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'LAST_UPDATE_DATE', 3, 'DATE', 9, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'LAST_UPDATED_BY', 4, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'UNIQUE_ID_COLUMN', 5, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'UNIQUE_ID_COLUMN2', 6, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SET_DEFINING_COLUMN', 7, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SUMMARY_FLAG', 8, 'VARCHAR2', 1, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'ENABLED_FLAG', 9, 'VARCHAR2', 1, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'START_DATE_ACTIVE', 10, 'DATE', 9, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'END_DATE_ACTIVE', 11, 'DATE', 9, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT1', 12, 'VARCHAR2', 60, 'Y', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT2', 13, 'VARCHAR2', 60, 'Y', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT3', 14, 'VARCHAR2', 60, 'Y', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT4', 15, 'VARCHAR2', 60, 'Y', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT5', 16, 'VARCHAR2', 60, 'Y', 'N');


Challa.

Transfer Alert from one instance/database to other.

Transfer Alert from one instance/database to other.

Go to “Alert Manager” Responsibility
Alert  -> Define
Go to “Tools” Menu on top as shown in the below picture.

Click on “Transfer Alert”
And a new window will be opened as shown below.

Enter source
Application: (Source application name)
Alert: (alert name which you want to transfer)
Database: (source database which you want to transfer from)

Enter Destination
Application: (Destination application name)
Alert: (alert name which you want to name)
Database: (destination database which you want to transfer to)

and click on Transfer Button.
And you are done.!

Challa.

Oracle Alerts Table Names

Oracle Alerts Table Names

• Oracle Alert uses the following Tables:

• ALR_ALERTS
• ALR_ACTIONS
• ALR_ACTION_SETS
• ALR_ACTION_SET_INPUTS
• ALR_ACTION_SET_OUTPUTS
• ALR_ACTION_SET_MEMBERS
• ALR_ALERT_CHECKS
• ALR_ALERT_INPUTS
• ALR_ALERT_OUTPUTS
• ALR_ACTION_SET_CHECKS
• ALR_RESPONSE_SETS
• ALR_RESPONSE_ACTIONS
• ALR_VALID_RESONSES

• Oracle Alert uses the following internal views:

• ALR_ALERT_ACTIONS_VIEW
• ALR_ALERT_HISTORY_VIEW
• ALR_CHECK_ACTION_HISTORY_VIEW
• ALR_INSTALLATIONS_VIEW
• ALR_PERIODIC_ALERTS_VIEW
• ALR_RESPONSE_ACTIONS_VIEW
• ALR_SCHEDULED_PROGRAMS
• ALR_VARIABLES_AND_OUTPUTS

How to find the SQL of the Alert created.

How to find the SQL of the Alert created

When an event alert is created on specific database objects,a trigger is created in the database and it can be viewed by querying the below query.

SELECT DBA_TRIGGERS.TABLE_NAME, 

               DBA_TRIGGERS.TRIGGER_NAME, 

               DBA_TRIGGERS.STATUS, USER_OBJECTS.STATUS, 

               DBA_TRIGGERS.TRIGGER_BODY 

FROM    USER_OBJECTS, 

               DBA_TRIGGERS

WHERE  DBA_TRIGGERS.TRIGGER_NAME = USER_OBJECTS.OBJECT_NAME 

AND       USER_OBJECTS.OBJECT_TYPE = 'TRIGGER' 

AND       USER_OBJECTS.OBJECT_NAME LIKE 'ALR_%'

Example :

1) If an alert is created on the table ALR_DISTRIBUTION_LISTS then a trigger with the name ALR_ALR_DISTRIBUTION_LISTS_IAR will be created in the database.

2) If an alert is created on the table PO_ACTION_HISTORY then a trigger with the name ALR_PO_ACTION_HISTORY_UAR will be created in the database.

If you observe the above two examples clearly,you can see that the triggers are created with the table names on which alerts are created on,with ALR,IAR and UAR appended to the table names.

Where,

ALR specifices that it is an trigger created for ALERTS.

IAR and UAR specifices that the trigger is created for INSERT and UPDATE operations respectively on the database table.         

Challa.

Event Alert does not fire or ( ALECTC ) Concurrent Request does not start

Event Alert does not fire or Check Event Alert ( ALECTC ) Concurrent Request does not start

CHANGES

Symptoms : Event Alert does not fire or Check Event Alert ( ALECTC ) Concurrent Request does not start
Changes : Doing the setup of an Event Alert.
Cause : The cause can be setup or code related
Solution : 

I) Setup steps to verify

In the Responsibility : Alert Manager
a) Navigate to : System Options.

Tab : Mail Systems => Verify that only Oracle InterOffice is checked as In Use
Tab : Mail Server Options => Verify that the User Name for Send Mail Account and Response Mail Account is set for both to ORACLE

b) Navigate to : System Applications

Verify that the Application you want to setup an Alert for is listed in this form

c) Navigate to : System Installations

Verify that the Application you want to setup an Alert for is listed in this form

d) Navigate to : Alert Define

Main Form => Verify that the enabled flag is activated on the defined Alert
Button : Actions => Verify that there is at least 1 Action defined
Button : Action Details => verify that for Action Type : Message that there is an e-mail defined for the field : To

Button : Action Sets => Verify that there is at least 1 Action Set defined and enabled
Button : Action Sets Details => Tab Members should have at least 1 row defined

Button : Alert Details => Tab : Installations => Verify that the Alert is enabled for all Operating Units you want to use the Alert for.
This setup is very important as it will be included in the Alert Trigger definition.
The Alert database trigger will only fire if the Operating Unit set in the alert definition matches the org_id for your session.
The session org_id depends on the profile options : MO: Operating Unit, MO: Security profile and MO: Default Operating Unit

In the Responsibility : System Administrator

a) Navigate to : Profile System

Query the Profile Options ; MO%
For Release 11i make sure that the Profile Option : MO: Operating Unit has a value
For Release 12 with the new MOAC setup make sure 

1) Profile Option : MO: Operating Unit has a value OR
2) The Profile Options MO: Security profile AND MO: Default Operating Unit have a value

With Workflow used for email:

a) Navigate to : System Options.

Tab : Mail Systems => Verify that only Oracle InterOffice is checked as In Use --> As long as ANY one Name is marked in use, this will work.
Tab : Mail Server Options => Verify that the User Name for Send Mail Account and Response Mail Account is set for both to ORACLE --> This section is ignored now.

II) SQL to verify the setup

a) Verify if the Alert trigger is valid and enabled:

SELECT DBA_TRIGGERS.TABLE_NAME,
               DBA_TRIGGERS.TRIGGER_NAME,
               DBA_TRIGGERS.STATUS, USER_OBJECTS.STATUS,
               DBA_TRIGGERS.TRIGGER_BODY
FROM    USER_OBJECTS, DBA_TRIGGERS
WHERE  DBA_TRIGGERS.TRIGGER_NAME = USER_OBJECTS.OBJECT_NAME AND
                   USER_OBJECTS.OBJECT_TYPE = 'TRIGGER'
   AND        USER_OBJECTS.OBJECT_NAME LIKE 'ALR_%'

=> One can add the first characters of the table you defined the trigger on. 

e.g user_objects.object_name like 'ALR_FND_PROFILE%' , but don't use the full table_name as the trigger name doesn't contain the full table name.

b) Verify that the concurrent request ; 'Check Event Alert' has been started and completed normal.

SELECT REQUEST_ID
             , REQUEST_DATE
             , PHASE_CODE
             , STATUS_CODE
             , ORACLE_ID
             , PROGRAM_APPLICATION_ID
             , CONCURRENT_PROGRAM_ID
FROM APPLSYS.FND_CONCURRENT_REQUESTS
where Concurrent_program_id = (select Concurrent_program_id from fnd_concurrent_programs_tl where
USER_CONCURRENT_PROGRAM_NAME = 'Check Event Alert') order by request_date desc;

Note : PHASE_CODE = C and STATUS_CODE = C means Completed Normal.

Challa.

Concurrent Programs related to Alerts in Oracle Applications

Alerts Concurrent Programs

Concurrent Program Short Code - >  Concurrent Program Name

• ALECDC                         ->       Check Periodic Alert
• ALECTC                          ->       Check Event Alert
• ALEPPE                           ->       Periodic Alert Scheduler
• ALPPIM                          ->        Response Processor
• ALDTCA                         ->       Test Concurrent Program Action
• ALUPCR                         ->        Delete Concurrent Request
• ALUMMM                       ->       Maintain Oracle Office Messages
• ALUTAD                         ->       Transfer Alert Definition
• ALPPNR                          ->       No Response Action Processor
• ALPPWF                          ->      Alert Action Processor

Event Alerts and Periodic Alerts.

Alerts in Oracle Application

Oracle Alert facilitates the flow of information within your organization by letting you create entities called alerts to monitor your business information and to notify you of the information you want.

Two types of alerts:
1. Event alert
2. Periodic alert.

Event Alert: These Alerts are fired/triggered based on some change in data in the database.whenever some events take place in the system.

Ex: If you want to notify your manager when you create an item in the inventory you can use Event based alerts. When you create an item in the inventory it will create a new record in mtl_system_items_b, here inserting a record in the table is an event so whenever a new record is inserted it will send the alert. In same alert you can also send the information related to that particular item.

Periodic Alert: These are fired periodically like once-a-day,once-a-month,once-per-hour.Depending on our requirement.

Ex: If you want to know list of items created on that day at the end of day you can use periodic alerts repeating periodically by single day. This alert is not based on any changes to database. This alert will notify you everyday regardless of data exists or not that means even if no items are created you will get a blank notification.

Navigation to define an alert:

Go to “Alert Manager” Responsibility -> Alert  ->  Define

How to define a periodic alert:

Go to Alert Manager > Alert > Define.

1. Select the ‘Periodic’ Tab.
2. Enter the name of the application that owns the alert in the Application field.
3. Name the alert (up to 50 characters), and give it a meaningful description (up to 240 characters).
4. Check Enabled to enable your periodic alert.
5. Set the frequency for the periodic alert to any of the following:

• On Demand
• On Day of the Month
• On Day of the Week
• Every N Calendar Days
• Every Day
• Every Other Day
• Every N Business Days
• Every Business Day
• Every Other Business Day

Enter a SQL Select statement that retrieves all the data your alert needs to perform the actions you plan to define. Your periodic alert Select statement must include an INTO clause that contains one output for each column selected by your Select statement.




Here is an example of a periodic alert Select statement that looks for users who have not changed their passwords within the number of days specified by the value in: THRESHOLD_DAYS

SELECT user_name,
password_date,
:THRESHOLD_DAYS
INTO &USER,
&LASTDATE,
&NUMDAYS
FROM fnd_user
WHERE sysdate = NVL(password_date,
sysdate) + :THRESHOLD_DAYS
ORDER BY user_name

Although Oracle Alert does not support PL/SQL statements as the alert SQL statement definition, you can create a PL/SQL packaged function that contains PL/SQL logic and enter a SQL Select statement that calls that packaged function.
You can verify the accuracy and effectiveness of your Select statement. Choose Verify to parse your Select statement and display the result in a Note window.
Choose Run to execute the Select statement in one of your application’s Oracle IDs, and display the number of rows returned in a Note window.
Once you are satisfied with the SQL statement, save your work.

Specifying Alert Details:

Once you define an event or periodic alert in the Alerts window, you need to display to the Alert Details window to complete the alert definition. The Alert Details window includes information such as which Application installations you want the alert to run against, what default values you want your inputs variables to use, and what additional characteristics you want your output variables to have.




Creating Alert Actions:

After you define your alert you need to create the actions you want your alert to perform. There are four types of actions you can create:

• Message actions
• Concurrent program actions
• operating script actions
• SQL statement script actions

Choose Actions
Enter a name (up to 80 characters) and description (up to 240 characters) for your alert action.
Select a level for your action: Detail, Summary, or No Exception.
Choose Action Details to display the Action Details window.

Select the type of action you want to create in the Action Type field

How to define a event alert:

Follow the same steps as of how to define a periodic alert and
Specify the name of the application and the database table that you want Oracle Alert to monitor.

Note: You cannot use a view as the event table for your alert.

Check After Insert and/or After Update if you want to run your event alert when an application user inserts and/or updates a row in the database table.

Specify a value in the Keep _ Days field to indicate the number of days of exceptions, actions, and response actions history you want to keep for this alert.

Specify a value in the End Date field if you want to disable your alert by a certain date.


Challa.

Error 500--Internal Server Error in discoverer.

Sometimes we get error while retrieving the list of values for a Discoverer report in the Application showing 

500 Internal Server Error as shown below.

This can be resolved by following the below steps.

1)Select the preferences on the top right corner of the page.

2)Click on the preferences and a New window will be displayed as shown below.

Here change the value for Cancel list-of-values retrieval after.
(In my case i have changed it to 240 as the column contains very huge data for LOV's.)

3)Change the value and click on apply.

And the issue is resolved.

If the same issue occurs in the Discoverer Desktop (or) Discoverer Plus as shown below.

1) Go to Tools -> Options.

A pop-up will be opened as shown in below screenshot.

2)Click on the Query Governor Tab and change the value for Cancel value retrieval after.
3)Click on OK.

The issue is resolved.

Challa.

Materialized views

Oracle Materialized views

Materialized view : It is a physical table selective portion of the data from table’s periodically can be seen, it is used to improve the performance, useful in distributed environment.

Refresh : We can refresh the Materialized views frequently to get the new data into it.

Fast Refresh : This doesn't work for complex query. This is only applicable for a simple query like one-table access.

Complete Refresh : This requires the same amount of time like recreating the materialized view contents.

Refresh Force : Refreshes the materialized view as fast,if there are tables which can satisfy the conditions.

Privileges to Create Materialized Views

grant query rewrite to scott;
grant create materialized view to scott;
alter session set query_rewrite_enabled = true;

Just needed to grant the CREATE MATERIALIZED VIEW system privilege.
Challa> select * from user_sys_privs;

USERNAME PRIVILEGE ADM
________________________________________
Challa CREATE MATERIALIZED VIEW NO

Roles granted are listed below for completeness of the topic.
Challa> select * from user_role_privs;

USERNAME GRANTED_ROLE ADM DEF OS_
________________________________________
Challa CONNECT NO YES NO
Challa RESOURCE NO YES NO

We are running Oracle10g Release 2 Standard Edition in which QUERY REWRITE is not an enabled feature. Describing what I mean, I create a test table below upon which the materialized view will be based.

Challa> create table t1 as select owner,object_name,object_type from all_objects;
Table created.

Challa> select count(*) from t1;

COUNT(*)
———-
2997

Again when I attempt to create the materialized view with the “enable query rewrite” clause, I encounter the “feature not enabled” error.

Challa> create materialized view mv_test
2 refresh complete on commit
3 enable query rewrite
4 as
5 select object_type, count(*)
6 from t1
7 group by object_type;

from t1
*
ERROR at line 6:
ORA-00439: feature not enabled: Materialized view rewrite
I remove the “enable query rewrite” clause and the materialized view is created successfully.

Challa> create materialized view mv_test
2 refresh complete on commit
3 as
4 select object_type, count(*)
5 from t1
6 group by object_type;

Materialized view created.

Packages to Refresh The Materialized Views :

The DBMS_MVIEW package contains three APIs for performing refresh operations:

• DBMS_MVIEW.REFRESH
  Refresh one or more materialized views.
• DBMS_MVIEW.REFRESH_ALL_MVIEWS
  Refresh all materialized views.
• DBMS_MVIEW.REFRESH_DEPENDENT
  Refresh all materialized views that depend on a specified master table or materialized view or list of master tables or materialized views.

Monitoring a Refresh :

While a job is running, you can query the V$SESSION_LONGOPS view to tell you the progress of each materialized view being refreshed.

SELECT * FROM V$SESSION_LONGOPS;

To look at the progress of which jobs are on which queue, use:

SELECT * FROM DBA_JOBS_RUNNING;

Packages to Refresh The Materialized Views :


The DBMS_MVIEW package contains three APIs for performing refresh operations:
DBMS_MVIEW.REFRESH
Refresh one or more materialized views.
DBMS_MVIEW.REFRESH_ALL_MVIEWS
Refresh all materialized views.
DBMS_MVIEW.REFRESH_DEPENDENT
Refresh all materialized views that depend on a specified master table or materialized view or list of master tables or materialized views.


Monitoring a Refresh :

While a job is running, you can query the V$SESSION_LONGOPS view to tell you the progress of each materialized view being refreshed.


          SELECT * FROM V$SESSION_LONGOPS; 

To look at the progress of which jobs are on which queue, use:


          SELECT * FROM DBA_JOBS_RUNNING;

Checking the Status of a Materialized View :

Three views are provided for checking the status of a materialized view:

DBA_MVIEWS, ALL_MVIEWS, and USER_MVIEWS.

To check if a materialized view is fresh or stale, issue the following 

statement:

SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE 
FROM USER_MVIEWS ORDER BY MVIEW_NAME;

MVIEW_NAME                          STALENESS                 LAST_REF              COMPILE_STATE
----------                                             ---------                       --------                            -------------
CUST_MTH_SALES_MV     NEEDS_COMPILE           FAST                      NEEDS_COMPILE
PROD_YR_SALES_MV          FRESH                              FAST                              VALID

If the compile_state column shows NEEDS COMPILE, the other displayed column values cannot be trusted as reflecting the true status. To revalidate the materialized view, issue the following statement:

ALTER MATERIALIZED VIEW [materialized_view_name] COMPILE;

Then reissue the SELECT statement.

Scheduling Refresh
Very often you have multiple materialized views in the database. Some of these can be computed by rewriting against others. This is very common in data warehousing environment where you may have nested materialized views or materialized views at different levels of some hierarchy.

In such cases, you should create the materialized views as BUILD DEFERRED, and then issue one of the refresh procedures in DBMS_MVIEW package to refresh all the materialized views. Oracle Database computes the dependencies and refreshes the materialized views in the right order. Consider the example of a complete hierarchical cube described in "Examples of Hierarchical Cube Materialized Views". Suppose all the materialized views have been created as BUILD DEFERRED. Creating the materialized views as BUILD DEFERRED only creates the metadata for all the materialized views. And, then, you can just call one of the refresh procedures in DBMS_MVIEW package to refresh all the materialized views in the right order:

DECLARE numerrs PLS_INTEGER;
BEGIN DBMS_MVIEW.REFRESH_DEPENDENT (
   number_of_failures => numerrs, list=>'SALES', method => 'C');
DBMS_OUTPUT.PUT_LINE('There were ' || numerrs || ' errors during refresh');
END;
/

Challa.

Reading from File and Writing to File using UTL_FILE Package - PL/SQL

Background:

With the UTL_FILE package, your PL/SQL programs can read and write operating system text files.

Prerequisite steps:

1. Create UTF_FILE directory.
CREATE OR REPLACE DIRECTORY MY_UTL_DIR AS '/usr/tmp/utl'
2. Now keep a simple text file 'temp.txt' in '/usr/tmp/utl' directory. My temp.txt file has 10 lines.

Program 1: Now lets try a simple program which reads the file temp.txt and print on the console.

DECLARE
l_fileID UTL_FILE.FILE_TYPE;
l_dirpath VARCHAR2 (50) := 'MY_UTL_DIR';
l_filename VARCHAR2 (50) := 'temp.txt';
l_line_counter NUMBER := 1;
l_buffer VARCHAR2(32000);
BEGIN
/*Open file*/
l_fileID := UTL_FILE.FOPEN (l_dirpath, l_filename, 'r', 32000);
LOOP
/*Read and output the line until we reach the last line*/
UTL_FILE.GET_LINE(l_fileID, l_buffer, 32000);
dbms_output.put_line('Line' || l_line_counter: ||' '|| l_buffer);
l_line_counter := l_line_counter + 1;
END LOOP;
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE('File location is invalid');
WHEN NO_DATA_FOUND THEN /*EOF Reached*/
UTL_FILE.fclose(l_fileID); /* Close the File Type*/
NULL;
END;

Program 2 : Now lets try a simple write program to write 10 lines to the file TestWrite.txt

DECLARE
l_fileID UTL_FILE.FILE_TYPE;
l_dirpath VARCHAR2 (50) := 'MY_UTL_DIR';
l_filename VARCHAR2 (50) := 'TestWrite.txt';
l_buffer VARCHAR2(32000);
l_count NUMBER :=0;
BEGIN
/*Open file*/
l_fileID := UTL_FILE.FOPEN (l_dirpath, l_filename, 'w', 32000);
LOOP
l_count := l_count+1;
/*PUT_LINE procedure writes the text string stored in the buffer to the open file*/
UTL_FILE.PUT_LINE(l_fileID, 'This is Line Number: ' || l_count);
EXIT WHEN l_count = 11;
END LOOP;
UTL_FILE.fclose(l_fileID); /* Close the File Type*/
EXCEPTION
WHEN UTL_FILE.WRITE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Operating system error occurred during the write operation.');
WHEN others THEN
DBMS_OUTPUT.PUT_LINE('Other Exception.');
END;

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_file.htm

How to run Discoverer Reports in Multi-Org Environment?

When working in R12 MOAC environment, sometimes discoverer reports which are based on some context based view will not give data. Then do the following setup to make the discoverer reports work in MOAC environment.

1) Create a Security Profile in HR

1) Run concurrent program Security List Maintenance

1) Tag this profile to MO: Security Profile option at responsibility level.

1) Update profile option “Initialization SQL Statement – Custom” with following pl/sql block at responsibility level.

begin if (fnd_profile.value(‘XLA_MO_SECURITY_PROFILE_LEVEL’)= 0) 

then mo_global.init(‘S’); 

else 

GL_SECURITY_PKG.init(); mo_global.init(‘M’); 

end if; 

end;

When multiple organizations are included in Security Profile: TEST SECURITY PROFILE, This profile is attached to respDiscoverer EUL Management.

Challa.

Discoverer Report Registration in Oracle Applications

The following are the steps,to register a discoverer report in Oracle Apps. Make sure you followed each and every step correctly.

1. Login to Application Developer or System Adminstrator -->Function

2. Enter Function , User Function Name, Description(Optional) in Description Tab.

E.g
Function: UNPAID_INVOICES
User Function Name: Supplier Invoices Validated But not unpaid.
Description: Give meaningful meaning.

In Properties Tab:

Select Type “SSWA jsp function”
Maintenance Mode Support – None
Context Dependence - Responsibility


4. In Form Tab  Parameters --> Enter the Workbook name created previously

mode=DISCO&workbook= Supplier Invoices Validated But Unpaid Report(workbook name you created in discoverer desktop)

E.g
workbook=TEST_WORKBOOK
In case you want to give some parameters then you can use:
workbook=<workbook_name>&parameters=<Parameter_nam e1>~<value1>*<Parameter_name2>~<value2>*<Parameter _name3>~<value3>*Example
workbook=<workbook_name>&Parameters=age~26*salary~ 1000*

5. In Web HTML Tab

Enter HTML Call - OracleOasis.jsp

6. Now you can add this Function in any existing or new Menu

Also Note that Discoverer Report cannot be invoked when you’ve direct login into Oracle Applications through http://<url>:<port number>/dev60cgi/f60cgi
You need to access through logging in from the browser web page. Otherwise it will give an Error.

7. Now navigate to Responsibility which contain the Menu to which you added this function.

8. Click the Function Name to Display the report.

Challa.

Solution for Function not available to this responsibility.

If you get Function not available to this responsibility error while trying to use diagnostics -> Examine.Try the below steps as follows.

1. Login to Oracle Application and choose the System Administrator responsibility.
2. Navigate to Profile -> System.
3. Search on %diagnostic%
4. Select 'Utilities: Diagnostics'.
5. Set profile to 'Yes'.

challa.

Submiting XMLP Report From Backend

DECLARE

    V_REQUEST_ID NUMBER;
    l_boolean    BOOLEAN;
    l_boolean1  BOOLEAN;
BEGIN
    l_boolean := FND_REQUEST.ADD_DELIVERY_OPTION
 (TYPE         => 'E',
 -- this one to speciy the delivery option as Email
  p_argument1  => 'Testing the Email option from back end',
 -- subject for the mail
  p_argument2  => 'challa95@gmail.com',
-- from address
  p_argument3  => 'challa95@gmail.com',
-- to address
  p_argument4  => 'challa95@gmail.com',
-- cc address to be specified here.
  nls_language => '' -- language option
               );      
    IF l_boolean = TRUE
    THEN
        FND_GLOBAL.APPS_INITIALIZE(8511,51272,20003);
        l_boolean1 := FND_REQUEST.add_layout  
(template_appl_name   => 'XXAA'
 ,template_code       => 'XXAAPTOSUEMP'
 ,template_language   => 'en'
 ,template_territory  => 'US'
 ,output_format       => 'PDF'
            );                   

        V_REQUEST_ID := FND_REQUEST.SUBMIT_REQUEST
                           (APPLICATION   => 'XXAA'
                            ,PROGRAM      => 'XXAAPTOSUEMP'
                           );

        COMMIT ;
        DBMS_OUTPUT.PUT_LINE('Request submitted. V_REQUEST_ID = '
|| V_REQUEST_ID);
    END IF;

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Request set submission failed - unknown error:' ||SQLERRM);

END;

Challa.

Query To Find Concurrent Program's Parameters and Value Sets

SELECT
fcp.user_concurrent_program_name "Concurrent Program Name",
fcp.concurrent_program_name "Short Name",
fdfcuv.column_seq_num "Column Seq Number",
fdfcuv.end_user_column_name "Parameter Name",
fdfcuv.form_left_prompt "Prompt",
fdfcuv.srw_param,
ffvs.flex_value_set_name "Value Set Name",
flv.meaning "Default Type",
fdfcuv.DEFAULT_VALUE "Default Value"
FROM
fnd_concurrent_programs_vl fcp,
fnd_descr_flex_col_usage_vl fdfcuv,
fnd_flex_value_sets ffvs,
fnd_lookup_values flv
WHERE 1=1
AND fdfcuv.descriptive_flexfield_name = '$SRS$.'||fcp.concurrent_program_name
AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
AND flv.lookup_code(+) = fdfcuv.default_type
-- AND fcp.user_concurrent_program_name like 'XXAA%Report'
-- AND ffvs.flex_value_set_name LIKE '%JOB%'
ORDER BY
fcp.user_concurrent_program_name
,fdfcuv.column_seq_num;

Query to Extract AOL Menu Hierarchy Query

SELECT   lev "LEVEL", fm.user_menu_name "MASTER_MENU",
         entry_sequence "ENTRY_SEQ", a.prompt "PROMPT",
         fms.user_menu_name "CHILD_MENU",
         ffv.user_function_name "FUNCTION_NAME"
FROM (SELECT     LEVEL lev, fme.menu_id, fme.sub_menu_id, function_id,
                     entry_sequence, PRIOR entry_sequence prior_entry_seq,
                     fme.prompt
                FROM fnd_menu_entries_vl fme
               WHERE fme.grant_flag = 'Y'
          START WITH fme.menu_id = '69914'
          CONNECT BY PRIOR fme.sub_menu_id = fme.menu_id) a,
         fnd_menus_vl fm,
         fnd_menus_vl fms,
         fnd_form_functions_vl ffv
   WHERE a.menu_id = fm.menu_id AND fms.menu_id(+) = a.sub_menu_id
         AND ffv.function_id(+) = a.function_id
ORDER BY lev, prior_entry_seq, entry_sequence;

Oracle Procure To Pay (P2P) Life Cycle

Oracle Procure To Pay (P2P) Life Cycle

The Procure To Pay cycle is vital to an organization as in this process the organization buys and receives goods or services from its vendors and makes necessary payments.

The P2P cycle comprises of the following steps,

1. Create requisition
2. Approve requisition
3. Create purchase order
4. Approve purchase order
5. Receive goods
6. Create Payables invoice
7. Pay the invoice
8. Return to Vendor (optional)
9. Transfer to General Ledger
10. Import Journal into General Ledger

Step 1: Create requisition

Responsibility: Any Purchasing responsibility
Navigation: Requisition > Requisition Summary



Click on New button



Create a new Purchase Requisition.
Enter the requisition details and save the form.



Step 2: Approve the requisition

After the requisition is saved the Approve button is enabled.
Click on the Approve button to send the requisition for approval.



Click on OK button and the requisition will be sent for approval.



Now go into Workflow Administrator responsibility to approve the workflow.



Requisition is approved by default if not we need to approve manually



After the requisition is approved the status of the requisition will change to Approved.

Below the tables are effected

SELECT *
FROM PO_REQUISITION_HEADERS_ALL
WHERE 1=1
AND SEGMENT1='14308'
AND REQUISITION_HEADER_ID = 183202
ORDER BY CREATION_DATE DESC

SELECT * FROM PO_REQUISITION_LINES_ALL
WHERE 1=1
AND REQUISITION_HEADER_ID = 183202
AND REQUISITION_LINE_ID = 210409

SELECT * FROM PO_REQ_DISTRIBUTIONS_ALL
WHERE 1=1
AND REQUISITION_LINE_ID = 210409

Step 3: Create the Purchase Order from the requisition

When the Requisition is approved we need to create a Purchase Order for it.We shall use the AutoCreate feature of Purchasing to do the conversion from Requisition to Purchase Order.

Responsibility: Purchasing responsibility
Navigation: AutoCreate



Enter the requisition number, 14308, and click on Find.



You might get the below message. For this one we can remove the Buyer name and create the po or assign the buyer to requisition



Navigation: Management > Manage Buyer Workload



Enter the requisition number, 14308, and click on Find button.



Now assign a Buyer to the requisition so that this requisition can be picked up by him to create the Purchase order.

We shall assign a new buyer, Stock, Ms. Pat, and select the requisition.



Save the form.
You will get a confirmation message.



Now you need to log into oracle as the Stock, Ms. Pat (i.e. the buyer who was set on the requisition). After logging in to Oracle, go to a Purchasing responsibility.

Open the AutoCreate form.



Enter the requisition number, 14308, and click on Find button. The AutoCreate
Documents form opens with the requisition details.


Select the requisition. We shall take the default values,

Action: Create
Document Type: Standard PO
Grouping: Default

Click on Automatic button.



Click on Create button to create the PO. The PO form opens with the new PO details that has been created from the requisition.



The PO number is 6036.



Step 4: Approve the Purchase Order

Enter the Supplier and the supplier site on this PO.
Save the PO. Click on the Shipments button.



The Shipments form opens. We find the shipping org is set to V1. This means that the goods will be received in the V1 org when it is shipped by the supplier (Step 5).



Close the Shipment form to go back to the PO form. Click on Approve button to send the PO for approval.


The Approve Document form opens. Click on OK button to send the PO for approval.



Once the PO is approved the status changes to Approved, Reserved.



Below the tables are effected

--PO_HEADERS_ALL

select po_header_id from po_headers_all where segment1 =<po_number>;
select * from po_headers_all where po_header_id =<po_header_id>;

--po_lines_all

select * from po_lines_all where po_header_id =<po_header_id>;

--po_line_locations_all

select * from po_line_locations_all where po_header_id =<po_header_id>;

--po_distributions_all

select * from po_distributions_all where po_header_id =<po_header_id>;

--po_releases_all

SELECT * FROM po_releases_all WHERE po_header_id =<po_header_id>;
SELECT * FROM PO_HEADERS_ALL
WHERE 1=1
AND SEGMENT1 = '6036'
AND PO_HEADER_ID = 119352

SELECT * FROM PO_LINES_ALL
WHERE 1=1
AND PO_HEADER_ID = 119352
AND PO_LINE_ID = 182280

SELECT * FROM PO_DISTRIBUTIONS_ALL
WHERE 1=1
AND PO_HEADER_ID = 119352

SELECT * FROM PO_LINE_LOCATIONS_ALL
WHERE 1=1
AND PO_HEADER_ID = 119352

Step 5: Receive the goods/items

Once the PO is sent to the supplier to be fulfilled the supplier sends the goods to the organization. The goods now have to be received in Oracle.

To receive goods go to any Purchasing responsibility that has the receiving functionality.

Navigation: Receiving > Receipts


The Organization screen opens. Select V1 (As checked in PO shipment in Step

4) and click on OK.



Enter the PO number and the supplier name is populated automatically.

Click on Find button. The receipt header form opens.



Close this form and the Receipts form opens.



Select the Line. You can change the quantity received at this section..
Save the form.



To get the receipt number, click on the Header button.



You now have the receipt number, 8458.



Close all the forms

Step 6: Inspect the goods

Now that we have received the goods based on the PO (even though the shipment was partial) we need to have the goods inspected and QA check done (This is required if the PO is based on 4 way matching, else the payment cannot be made).

Responsibility: Purchasing responsibility that has the Receiving Transactions or QA functionality enabled
Navigation: Receiving > Receiving Transactions



Enter the Purchase Order Number, i.e. 6036.

The supplier number is populated automatically. Now click on Find button.



Note that the Inspection status shows as Not Inspected – Standard Receipt and the Destination Type is Inventory.

Click on Inspect button. The inspection window opens.



We are going to pass all 5 goods as part of the QA check process. We shall select Quality Code from the list of values as Excellent.

Enter a comment on the right most field.
Press OK button. The window will close and the receipt status will change to Inspect.
Requery the receiving transaction on the PO.

Note that the Inspection field has the value, Accepted – Standard Receipt. QA check has been completed. Close the form.

Below are the tables effected Once PO is received data is moved to respective receiving tables and inventory tables

--RCV_SHIPMENT_HEADERS

select * from rcv_shipment_headers where shipment_header_id in
(select shipment_header_id from rcv_shipment_lines
where po_header_id =<po_header_id>);

--RCV_SHIPMENT_LINES

select * from rcv_shipment_lines where po_header_id =<po_header_id>;

--RCV_TRANSACTIONS

select * from rcv_transactions where po_header_id =<po_header_id>;

--RCV_ACCOUNTING_EVENTS

SELECT * FROM rcv_Accounting_Events WHERE rcv_transaction_id IN
(select transaction_id from rcv_transactions
where po_header_id =<po_header_id>);

--RCV_RECEIVING_SUB_LEDGER

select * from rcv_receiving_sub_ledger where rcv_transaction_id in
(select transaction_id from rcv_transactions where po_header_id =<po_header_id>);

--RCV_SUB_LEDGER_DETAILS

select * from rcv_sub_ledger_details
where rcv_transaction_id in (select transaction_id from rcv_transactions
where po_header_id =<po_header_id>);

--MTL_MATERIAL_TRANSACTIONS

select * from mtl_material_transactions where transaction_source_id =<po_header_id>;

--MTL_TRANSACTION_ACCOUNTS

select * from mtl_transaction_accounts where transaction_id in
( select transaction_id from mtl_material_transactions
where transaction_source_id = <po_header_id>);

--RCV_SHIPMENT_HEADERS

select * from rcv_shipment_headers
where 1=1
AND SHIPMENT_HEADER_ID = 4815921

--RCV_SHIPMENT_LINES

select * from rcv_shipment_lines
where 1=1
AND po_header_id = 119352
AND SHIPMENT_HEADER_ID = 4815921
and SHIPMENT_LINE_ID = 4805482

--RCV_TRANSACTIONS

select * from rcv_transactions
where 1=1
AND po_header_id = 119352
and transaction_id in (4895409 ,4895410)

--RCV_ACCOUNTING_EVENTS

SELECT * FROM rcv_Accounting_Events
where 1=1
and rcv_transaction_id in (4895409 ,4895410)

--RCV_RECEIVING_SUB_LEDGER

select * from rcv_receiving_sub_ledger
where 1=1
and rcv_transaction_id in (4895409 ,4895410)

--RCV_SUB_LEDGER_DETAILS

select * from rcv_sub_ledger_details
where 1=1
and rcv_transaction_id in (4895409 ,4895410)

--MTL_MATERIAL_TRANSACTIONS

select * from mtl_material_transactions
where 1=1
and transaction_source_id = 119352

--MTL_TRANSACTION_ACCOUNTS

SELECT *
FROM mtl_transaction_accounts
WHERE transaction_id IN (SELECT transaction_id
FROM mtl_material_transactions
WHERE transaction_source_id = 119352);

Step 7: Create Payables invoice

Responsibility: Payables responsibility to create an AP invoice
Navigation: Invoice > Entry > Invoices



Create an Invoice for supplier. This is the supplier which had received the
PO (Refer to Step 3 or 4).

Note: We are creating the invoice manually in this case. The invoice can be created automatically by executing the concurrent program named, Pay on Receipt AutoInvoice, from the Purchasing responsibility and entering the goods receipt number (GRN) as the parameter.

Click on Match button at the bottom right of the form.





Important:

We shall not enter the invoice distribution as this will come automatically from matching the invoice with PO receipt.

There are 3 options for matching.

• Invoice
• Receipt
• Purchase Order

We are matching Receipt to this invoice so that the supplier is paid for only the amount that has been received in the store.

Matching is done in 3 ways

• 2-way PO and Invoice quantity and total amounts are matched
• 3-way PO, Invoice, Receipt quantity and total amounts are matched
• 4-way PO, Invoice, Receipt, QA (Acceptance) quantity and total amounts are matched

Find Receipts form opens.

Enter the Receipt number (End of Step 5)
Click on Find button. Match to Receipts form opens.



Check the box under Match.



Notice that the Distribution Total is in Red. It means that the Invoice header amount and distribution amounts are not matching. Click on Match button at the bottom

You will be taken back to the Invoice form. Modify the Invoice header amount to match the amount of the distribution.



Now you can validate the Invoice by clicking on Actions button on the Invoice form and selecting Validate.

Click on OK button.



Now the invoice status changes to Validated.



Close the invoice form.

Step 8: Pay the invoice

Responsibility: Payables responsibility with payment functionality
Navigation: Payments > Entry > Payments



Enter the bank and supplier details



Click on Enter/Adjust Invoices button to enter the invoice



Save and close this window.
Click on Actions button. Select Format and Print Remittance Advice.



Click on OK. The Payment Process Manager will handle the payment and will execute the remittance program as well to make the payment.



After the processes are over the supplier is paid fully for the goods that he has supplied.

Below are the tables which get effected

--AP_INVOICE_DISTRIBUTIONS_ALL

select * from ap_invoice_distributions_all where po_distribution_id in
( select po_distribution_id from po_distributions_all where po_header_id=<po_header_id>);

--AP_INVOICES_ALL

select * from ap_invoices_all where invoice_id in
(select invoice_id from ap_invoice_distributions_all where po_distribution_id in
( select po_distribution_id from po_distributions_all where po_header_id =<po_header_id>));

select * from ap_invoice_distributions_all
where 1=1
AND po_distribution_id = 279894

select * from ap_invoices_all
where 1=1
AND invoice_id in (218359)

Step 9: Receive the remaining goods or Close the Invoice line

Receive the remaining goods For example if we select the some of goods like 10 and in that we did the transaction for only 5 goods

The supplier has been paid for the goods that have been received in the stores but we have received 5 goods out of the total number of 10. Therefore we have the option of receiving 5 more to complete the PO. If we receive the other 5 goods then we have to create another invoice to pay off those received goods.

If we do not want to receive the remaining goods but instead we want to close the PO at this stage then we need to Close the PO line so that the remaining quantity is cancelled and the PO is closed.

Note:

If we do not finish this step the PO will remain in open state and the amount will go into encumbrance, i.e. the open amount will be carried over to the next periods as the amount which will need to be paid to the supplier.

Responsibility: Purchasing responsibility
Navigation: Purchase Orders > Purchase Order Summary


Query for the PO, i.e. 6036.
Click on Find.



Click on Lines button.



On the top menu click on Tools > Control. You will get a popup message.



Click on OK.



The Control Documents window opens. Select Finally Close and enter a reason.
Click on OK button.



Click on OK button. Now requery the PO.

The PO is now Closed. Note the PO status, Approved, Closed, Reserved.



Step 10: Transfer Payables data to General Ledger

Responsibility: Payables
Navigation: Invoices àquery with invoice and click on Actions and check the create accounting and select the final posting to GL and Check the GL batch name from vew requests








Challa.