How to Handle the Projects ?
It is commonly understood in the IT industry that "most projects fail." This prediction of doom and gloom is overheard in corporate corridors and has been consistently written about in the technology trade media. Large-scale information technology projects are traditionally prone to greater failure rates than other complex projects of similar size and scope -- in engineering or construction, for example. According to a University of Maryland study, projects are often designated as "failures" if they are over schedule by more than 30 percent, over budget by more than 30 percent, and the end product does not meet user requirements.
Industry research reports that most projects "seem" to fail in the last third of the project; yet in reality, they fail in the first third of the project. For those IT executives not experienced or well-trained to manage complex IT projects, a project may look like it is running along smoothly until it is too late to do something about it.
Fortunately, IT executives can learn from those who have gone before them. Given the complexity of IT projects and their high rates of failure, sponsoring an IT project can be a daunting and monumental task. After years of experience working with IT executives from multi-national to regional corporations in various industries, I have witnessed how those who practice a set of seven distinct habits are infinitely more successful than those who do not.
Though some are more visceral than measurable, the following practices should allow every IT executive to recognize failure before it occurs -- or better yet, avoid signs of failure all together.
1. Understand The Challenge There are three things inherent to IT projects that make them more apt to fail than other projects. First, there is no governing body in the IT industry that dictates certain standards for IT implementation. Second, technology is evolving at such a rapid pace, it is difficult to build a competency in any one particular area before it changes. Last, it has become standard practice to deliver IT projects with at least some flaws. Conversely, a margin of error is slim to none in the automobile, aviation or medical industries.
2. Exhibit Self-Awareness Successful IT executives need to fully embrace their role as the project sponsor. They need to obtain all necessary C-level buy-in and secure adequate funding. Project sponsors must clearly articulate the objectives and strategies to the project team, offer their time and lend their support.
3. Know Your Team Players The "human factor" is absolutely critical to the success or failure of an IT project. IT executives need to hand pick each member of the project team based on a detailed assessment of the skills and experience necessary to get the job done. The most critical player on the team is the project manager. In addition to having a specific set of skills, training and experience, the project manager must also have the ability to motivate and drive peers, exhibit attention to detail, and understand how to use project management tools properly.
4. Outsource Experienced IT executives often hire an outsourced IT consulting firm to assure success. When choosing an IT consulting firm, the savvy IT executive looks for a firm with literally hundreds of man years addressing challenges similar to what their company is facing; that has a proven, scalable and flexible methodology; that is vendor independent; that staffs every complex project with senior-level strategists that they can trust and respect; and that is able to provide in-person service on-the-fly.
5. Start with Discovery Believe it or not, many IT projects begin without a "discovery" phase. During the discovery phase, requirements and expectations are defined, the infrastructure is assessed and project recommendations are made. Ideally, neither a project team nor a project budget should be finalized until the discovery phase is complete. In fact, in some instances, a discovery phase may reveal a project that is destined for failure before any time or resources are consumed. A discovery phase can be a painful process, but one that yields valuable results. Following the discovery phase, there should also be a clearly defined planning, design, testing, implementation and monitoring phase.
6. Require Documentation IT executives should request a number of weekly documents from the project manager. First, a "run rate analysis" for both the budget and the schedule -- where the project is in relation to where it is supposed to be. Second, there must be a pre-determined "serial path" as well as a "critical path." A serial path assumes that one item is dependent upon the other and will not start until the item that precedes it is complete. A "critical path" maps out how to accomplish the goal in the least amount of time. Tasks that can be accomplished in parallel are clearly defined on a "critical path." IT executives who are trained to recognize potential project failure stay off the critical path at all costs, as there is no room for error once a project hits the critical path stage.
7. Communicate Frequently and Openly Project sponsors need to be seen and heard and stay front and center. They need to listen carefully to their project managers and ask the right questions at the right time. They need to be ready to address problems along the way. A "no news is good news" mentality will leave a project sponsor with a failed project in the end. At the onset of a project, successful IT executives ask their project managers to create an "issues and concerns" template. They direct their managers to complete this form as challenges or roadblocks arise during the project.
IT success does happen. IT executives who practice the aforementioned seven habits will find themselves going from the hot seat to Easy Street. Good luck, I hope it works for you the same way I've seen it work for so many of your peers.
It is commonly understood in the IT industry that "most projects fail." This prediction of doom and gloom is overheard in corporate corridors and has been consistently written about in the technology trade media. Large-scale information technology projects are traditionally prone to greater failure rates than other complex projects of similar size and scope -- in engineering or construction, for example. According to a University of Maryland study, projects are often designated as "failures" if they are over schedule by more than 30 percent, over budget by more than 30 percent, and the end product does not meet user requirements.
Industry research reports that most projects "seem" to fail in the last third of the project; yet in reality, they fail in the first third of the project. For those IT executives not experienced or well-trained to manage complex IT projects, a project may look like it is running along smoothly until it is too late to do something about it.
Fortunately, IT executives can learn from those who have gone before them. Given the complexity of IT projects and their high rates of failure, sponsoring an IT project can be a daunting and monumental task. After years of experience working with IT executives from multi-national to regional corporations in various industries, I have witnessed how those who practice a set of seven distinct habits are infinitely more successful than those who do not.
Though some are more visceral than measurable, the following practices should allow every IT executive to recognize failure before it occurs -- or better yet, avoid signs of failure all together.
1. Understand The Challenge There are three things inherent to IT projects that make them more apt to fail than other projects. First, there is no governing body in the IT industry that dictates certain standards for IT implementation. Second, technology is evolving at such a rapid pace, it is difficult to build a competency in any one particular area before it changes. Last, it has become standard practice to deliver IT projects with at least some flaws. Conversely, a margin of error is slim to none in the automobile, aviation or medical industries.
2. Exhibit Self-Awareness Successful IT executives need to fully embrace their role as the project sponsor. They need to obtain all necessary C-level buy-in and secure adequate funding. Project sponsors must clearly articulate the objectives and strategies to the project team, offer their time and lend their support.
3. Know Your Team Players The "human factor" is absolutely critical to the success or failure of an IT project. IT executives need to hand pick each member of the project team based on a detailed assessment of the skills and experience necessary to get the job done. The most critical player on the team is the project manager. In addition to having a specific set of skills, training and experience, the project manager must also have the ability to motivate and drive peers, exhibit attention to detail, and understand how to use project management tools properly.
4. Outsource Experienced IT executives often hire an outsourced IT consulting firm to assure success. When choosing an IT consulting firm, the savvy IT executive looks for a firm with literally hundreds of man years addressing challenges similar to what their company is facing; that has a proven, scalable and flexible methodology; that is vendor independent; that staffs every complex project with senior-level strategists that they can trust and respect; and that is able to provide in-person service on-the-fly.
5. Start with Discovery Believe it or not, many IT projects begin without a "discovery" phase. During the discovery phase, requirements and expectations are defined, the infrastructure is assessed and project recommendations are made. Ideally, neither a project team nor a project budget should be finalized until the discovery phase is complete. In fact, in some instances, a discovery phase may reveal a project that is destined for failure before any time or resources are consumed. A discovery phase can be a painful process, but one that yields valuable results. Following the discovery phase, there should also be a clearly defined planning, design, testing, implementation and monitoring phase.
6. Require Documentation IT executives should request a number of weekly documents from the project manager. First, a "run rate analysis" for both the budget and the schedule -- where the project is in relation to where it is supposed to be. Second, there must be a pre-determined "serial path" as well as a "critical path." A serial path assumes that one item is dependent upon the other and will not start until the item that precedes it is complete. A "critical path" maps out how to accomplish the goal in the least amount of time. Tasks that can be accomplished in parallel are clearly defined on a "critical path." IT executives who are trained to recognize potential project failure stay off the critical path at all costs, as there is no room for error once a project hits the critical path stage.
7. Communicate Frequently and Openly Project sponsors need to be seen and heard and stay front and center. They need to listen carefully to their project managers and ask the right questions at the right time. They need to be ready to address problems along the way. A "no news is good news" mentality will leave a project sponsor with a failed project in the end. At the onset of a project, successful IT executives ask their project managers to create an "issues and concerns" template. They direct their managers to complete this form as challenges or roadblocks arise during the project.
IT success does happen. IT executives who practice the aforementioned seven habits will find themselves going from the hot seat to Easy Street. Good luck, I hope it works for you the same way I've seen it work for so many of your peers.
Apr 19, 2010
What is an API?
The Application Programmatic Interface or API is a PL/SQL packaged procedure that can be
used as an alternative to Application online forms for Application data entry and
manipulation.
The advantage of using an API to update application data is that users can maintain HRMS
information without using manual entry in Oracle application forms.
APIs insure the integrity of the interrelationship of Oracle Applications tables. You can
modify application information without detailed knowledge of the database structure, because
the API updates all the interrelated tables.
APIs help protect customer-specific data from database structural changes. As Oracle changes
table structures, the APIs are modified correspondingly, so that data can continue to be
modified without error or code updates.
How do I use an API to upload data?
The API package usually contains multiple procedures to insert, update, or delete application
data; the API procedures are executed when they are called by other PL/SQL modules, by a
direct SQL*Plus call, or through a front end such as the Data Pump.
The APIs do not issue commits. When a user-defined procedure or script calls the API
procedure, the calling module must manage transaction commit statements. The calling
module should also address exception handling and logging to delivered exception tables.
The API package should never be modified. Oracle cannot support modified APIs nor
systems that have used modified APIs, because HRMS data integrity could be compromised.
APIs can be used as building blocks called within an extensive customer-defined package.
Unique functionality can be addressed with API User Hooks or other user code, providing
that Oracle HRMS data is modified only through the call to the delivered API.
Different API's Present in Oracle Applications :
FND_PROGRAM.EXECUTABLE ( )
FND_PROGRAM.DELETE_EXECUTABLE( )
FND_PROGRAM.REGISTER( )
FND_PROGRAM.DELETE_PROGRAM( )
FND_PROGRAM.PARAMETER( )
FND_PROGRAM.DELETE_PARAMETER( )
FND_PROGRAM.INCOMPATIBILITY( )
FND_PROGRAM.DELETE_INCOMPATIBILITY( )
FND_PROGRAM.REQUEST_GROUP( )
FND_PROGRAM.DELETE_GROUP( )
FND_PROGRAM.ADD_TO_GROUP( )
FND_PROGRAM.REMOVE_FROM_GROUP( )
FND_REQUEST.SUBMIT_REQUEST( )
FND_CONCURRENT.WAIT_FOR_REQUEST( )
FND_REQUEST.SET_PRINT_OPTIONS ( )
FND_GLOBAL.USER_IDFND_GLOBAL.APPS_INITIALIZE(user_id in number,resp_id in number,resp_appl_id in number);
FND_GLOBAL.LOGIN_ID
FND_GLOBAL.CONC_LOGIN_ID
FND_GLOBAL.PROG_APPL_ID
FND_GLOBAL.CONC_PROGRAM_ID
FND_GLOBAL.CONC_REQUEST_ID
FND_PROFILE.PUT(name,value)
FND_PROFILE.GET(name IN varchar2,value out varchar2)
API’S IN APPS
PO
PO_CUSTOM_PRICE_PUB
PO_DOCUMENT_CONTROL_PUB
PO_DOC_MANAGER_PUB
PO_WFDS_PUB
AP
AP_NOTES_PUB
AP_WEB_AUDIT_LIST_PUB
INV
INV_COST_GROUP_PUB
INV_ITEM_CATALOG_ELEM_PUB
INV_ITEM_CATEGORY_PUB
INV_ITEM_PUB
INV_ITEM_REVISION_PUB
INV_ITEM_STATUS_PUB
INV_LOT_API_PUB
INV_MATERIAL_STATUS_PUB
INV_MOVEMENT_STATISTICS_PUB
INV_MOVE_ORDER_PUB
INV_PICK_RELEASE_PUB
INV_PICK_WAVE_PICK_CONFIRM_PUB
INV_RESERVATION_PUB
INV_SERIAL_NUMBER_PUB
INV_SHIPPING_TRANSACTION_PUB
The Application Programmatic Interface or API is a PL/SQL packaged procedure that can be
used as an alternative to Application online forms for Application data entry and
manipulation.
The advantage of using an API to update application data is that users can maintain HRMS
information without using manual entry in Oracle application forms.
APIs insure the integrity of the interrelationship of Oracle Applications tables. You can
modify application information without detailed knowledge of the database structure, because
the API updates all the interrelated tables.
APIs help protect customer-specific data from database structural changes. As Oracle changes
table structures, the APIs are modified correspondingly, so that data can continue to be
modified without error or code updates.
How do I use an API to upload data?
The API package usually contains multiple procedures to insert, update, or delete application
data; the API procedures are executed when they are called by other PL/SQL modules, by a
direct SQL*Plus call, or through a front end such as the Data Pump.
The APIs do not issue commits. When a user-defined procedure or script calls the API
procedure, the calling module must manage transaction commit statements. The calling
module should also address exception handling and logging to delivered exception tables.
The API package should never be modified. Oracle cannot support modified APIs nor
systems that have used modified APIs, because HRMS data integrity could be compromised.
APIs can be used as building blocks called within an extensive customer-defined package.
Unique functionality can be addressed with API User Hooks or other user code, providing
that Oracle HRMS data is modified only through the call to the delivered API.
Different API's Present in Oracle Applications :
FND_PROGRAM.EXECUTABLE ( )
FND_PROGRAM.DELETE_EXECUTABLE( )
FND_PROGRAM.REGISTER( )
FND_PROGRAM.DELETE_PROGRAM( )
FND_PROGRAM.PARAMETER( )
FND_PROGRAM.DELETE_PARAMETER( )
FND_PROGRAM.INCOMPATIBILITY( )
FND_PROGRAM.DELETE_INCOMPATIBILITY( )
FND_PROGRAM.REQUEST_GROUP( )
FND_PROGRAM.DELETE_GROUP( )
FND_PROGRAM.ADD_TO_GROUP( )
FND_PROGRAM.REMOVE_FROM_GROUP( )
FND_REQUEST.SUBMIT_REQUEST( )
FND_CONCURRENT.WAIT_FOR_REQUEST( )
FND_REQUEST.SET_PRINT_OPTIONS ( )
FND_GLOBAL.USER_IDFND_GLOBAL.APPS_INITIALIZE(user_id in number,resp_id in number,resp_appl_id in number);
FND_GLOBAL.LOGIN_ID
FND_GLOBAL.CONC_LOGIN_ID
FND_GLOBAL.PROG_APPL_ID
FND_GLOBAL.CONC_PROGRAM_ID
FND_GLOBAL.CONC_REQUEST_ID
FND_PROFILE.PUT(name,value)
FND_PROFILE.GET(name IN varchar2,value out varchar2)
API’S IN APPS
PO
PO_CUSTOM_PRICE_PUB
PO_DOCUMENT_CONTROL_PUB
PO_DOC_MANAGER_PUB
PO_WFDS_PUB
AP
AP_NOTES_PUB
AP_WEB_AUDIT_LIST_PUB
INV
INV_COST_GROUP_PUB
INV_ITEM_CATALOG_ELEM_PUB
INV_ITEM_CATEGORY_PUB
INV_ITEM_PUB
INV_ITEM_REVISION_PUB
INV_ITEM_STATUS_PUB
INV_LOT_API_PUB
INV_MATERIAL_STATUS_PUB
INV_MOVEMENT_STATISTICS_PUB
INV_MOVE_ORDER_PUB
INV_PICK_RELEASE_PUB
INV_PICK_WAVE_PICK_CONFIRM_PUB
INV_RESERVATION_PUB
INV_SERIAL_NUMBER_PUB
INV_SHIPPING_TRANSACTION_PUB
function STATUSFormula return Char is
Cursor lpn is
SELECT meaning from mfg_lookups where lookup_type = 'WIP_JOB_STATUS' and lookup_code = :status_type;
l_num_lpn varchar2(50);
begin
open lpn;
fetch lpn into l_num_lpn;
close lpn;
return(l_num_lpn);
end;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
function CF_ORG_NAMEFormula return VARCHAR2 is
V_ORG_NAME VARCHAR2(100);
begin
SELECT organization_name
INTO V_ORG_NAME
FROM org_organization_definitions
WHERE organization_id = (fnd_profile.value('ORG_ID'));
return(V_ORG_NAME);
RETURN NULL;
exception
when others then
return(null);
end;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
function CF_UsernameFormula return VARCHAR2 is
uname VARCHAR2(100);
begin
select user_name into uname from fnd_user where user_id=(fnd_profile.value('USER_ID'));
return(uname);
RETURN NULL;
nd;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
function CF_trx_qtyFormula return Number is
v_Trx_quantity number;
v_lot_number number;
v_expiration_date date;
begin
Select moq.transaction_quantity
, mln.lot_number
, mln.expiration_date
into
v_Trx_quantity
, :cp_lot_number
, :cp_expiration_date
from mtl_onhand_quantities moq, mtl_lot_numbers mln
where MLN.INVENTORY_ITEM_ID = MOQ.INVENTORY_ITEM_ID
AND moq.organization_id = mln.organization_id
AND MOQ.ORGANIZATION_ID =:P_ORGANIZATION_ID
AND MOQ.INVENTORY_ITEM_ID =:INVENTORY_ITEM_ID;
--AND moq.locator_id =:locator_id1;
-- :cp_expiration_date :=v_expiration_date;
-- :cp_lot_number :=v_lot_number;
SRW.MESSAGE(021, v_Trx_quantity );
SRW.MESSAGE(022, :cp_lot_number );
SRW.MESSAGE(023, :cp_expiration_date );
return(v_Trx_quantity);
exception
when others then
return(null);
SRW.MESSAGE(024,'Error out!!!' );
end;
Cursor lpn is
SELECT meaning from mfg_lookups where lookup_type = 'WIP_JOB_STATUS' and lookup_code = :status_type;
l_num_lpn varchar2(50);
begin
open lpn;
fetch lpn into l_num_lpn;
close lpn;
return(l_num_lpn);
end;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
function CF_ORG_NAMEFormula return VARCHAR2 is
V_ORG_NAME VARCHAR2(100);
begin
SELECT organization_name
INTO V_ORG_NAME
FROM org_organization_definitions
WHERE organization_id = (fnd_profile.value('ORG_ID'));
return(V_ORG_NAME);
RETURN NULL;
exception
when others then
return(null);
end;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
function CF_UsernameFormula return VARCHAR2 is
uname VARCHAR2(100);
begin
select user_name into uname from fnd_user where user_id=(fnd_profile.value('USER_ID'));
return(uname);
RETURN NULL;
nd;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
function CF_trx_qtyFormula return Number is
v_Trx_quantity number;
v_lot_number number;
v_expiration_date date;
begin
Select moq.transaction_quantity
, mln.lot_number
, mln.expiration_date
into
v_Trx_quantity
, :cp_lot_number
, :cp_expiration_date
from mtl_onhand_quantities moq, mtl_lot_numbers mln
where MLN.INVENTORY_ITEM_ID = MOQ.INVENTORY_ITEM_ID
AND moq.organization_id = mln.organization_id
AND MOQ.ORGANIZATION_ID =:P_ORGANIZATION_ID
AND MOQ.INVENTORY_ITEM_ID =:INVENTORY_ITEM_ID;
--AND moq.locator_id =:locator_id1;
-- :cp_expiration_date :=v_expiration_date;
-- :cp_lot_number :=v_lot_number;
SRW.MESSAGE(021, v_Trx_quantity );
SRW.MESSAGE(022, :cp_lot_number );
SRW.MESSAGE(023, :cp_expiration_date );
return(v_Trx_quantity);
exception
when others then
return(null);
SRW.MESSAGE(024,'Error out!!!' );
end;
If we need to do some complex calculations, some agregrated functions like group calculations, we used Materiazed View.
A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views. Collectively these objects are called master tables (a replication term) or detail tables (a data warehousing term). This reference uses "master tables" for consistency. The databases containing the master tables are called the master databases.
When you create a materialized view, Oracle Database creates one internal table and at least one index, and may create one view, all in the schema of the materialized view. Oracle Database uses these objects to maintain the materialized view data. You must have the privileges necessary to create these objects.
Syantax :
CREATE MATERIALIZED VIEW schema.name
PCTFREE integer
PCTUSED integer
TABLESPACE tablespace_name
BUILD IMMEDIATE
REFRESH FAST | FORCE ON COMMIT / DEMAND
USING INDEX / USING NO INDEX
INITRANS integer
STORAGE CLAUSE
AS (SQL statement);
A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views. Collectively these objects are called master tables (a replication term) or detail tables (a data warehousing term). This reference uses "master tables" for consistency. The databases containing the master tables are called the master databases.
When you create a materialized view, Oracle Database creates one internal table and at least one index, and may create one view, all in the schema of the materialized view. Oracle Database uses these objects to maintain the materialized view data. You must have the privileges necessary to create these objects.
Syantax :
CREATE MATERIALIZED VIEW schema.name
PCTFREE integer
PCTUSED integer
TABLESPACE tablespace_name
BUILD IMMEDIATE
REFRESH FAST | FORCE ON COMMIT / DEMAND
USING INDEX / USING NO INDEX
INITRANS integer
STORAGE CLAUSE
AS (SQL statement);
Apr 1, 2010
1. Query for finding the nth maximum salary ?
Select distinct a.sal from emp a where (&n-1) = (select count (unique sal ) from emp b where b.sal > a.sal)
2. Query for finding the nth minimum salary ?
Select a.sal from emp1 a where (&n-1) = (select count (unique sal) from emp1 b where b.sal < a.sal)
3. Query for deleting the duplicate rows in table ?
Delete from emp where rowed not in (select max(rowid) from emp group by empno)
4. Query for finding the 2nd maximum ?
Select empno from emp where sal = (select max(sal) from emp where sal <> (select max(sal) from emp));
5. Query for finding the 2nd minimum ?
Select empno from emp where sal = (select min(sal) from emp where sal <> (select min(sal) from emp));
6. Query to find the cumulative total?
Select sum(x.sal) from emp1 x, emp1 y where y.rowid >= x.rowed group by y.row order by sum(x.sal)
7. Query to find the alternate rows ?
Select empno, ename from emp where (empno,rownum) in (select empno, mod(rownum,2) from emp);
8. Query to find the other alternate rows ?
Select * from emp where rowed not in (select empno, ename from emp where (empno,rownum) in (select empno,mod(rownum,2) from emp));
9. Query to delete alternate rows ?
Delete from emp where (empno,rownum) in (select empno, mod(rownum,2) from emp);
10. Query to print some text with the column values ?
Select empno,deptno, decode (mod(rownum,5),0,'*****') print from emp;
11. Query to get column without specifying the column name ?
Select &n, &q from emp where deptno = 10;
12. Query to delete duplicate rows by leaving one row deleted on specific condition ?
Delete from emp where deptno = 10 and rowid not in (select min(rowid) from emp where deptno = 10);
13. Query to delete duplicate rows but leaving one row undeleted ?
Delete from emp where deptno = 10 and rowid not in (select min(rowid) from emp where deptno = x.deptno);
14. Query to select all columns, rowid with out specifying the column name ?
Select rowid, &a from emp;
15. Query to print static text?
Select empno, sal, 'Maximum from emp where sal = (select max(sal) from emp)
Select distinct a.sal from emp a where (&n-1) = (select count (unique sal ) from emp b where b.sal > a.sal)
2. Query for finding the nth minimum salary ?
Select a.sal from emp1 a where (&n-1) = (select count (unique sal) from emp1 b where b.sal < a.sal)
3. Query for deleting the duplicate rows in table ?
Delete from emp where rowed not in (select max(rowid) from emp group by empno)
4. Query for finding the 2nd maximum ?
Select empno from emp where sal = (select max(sal) from emp where sal <> (select max(sal) from emp));
5. Query for finding the 2nd minimum ?
Select empno from emp where sal = (select min(sal) from emp where sal <> (select min(sal) from emp));
6. Query to find the cumulative total?
Select sum(x.sal) from emp1 x, emp1 y where y.rowid >= x.rowed group by y.row order by sum(x.sal)
7. Query to find the alternate rows ?
Select empno, ename from emp where (empno,rownum) in (select empno, mod(rownum,2) from emp);
8. Query to find the other alternate rows ?
Select * from emp where rowed not in (select empno, ename from emp where (empno,rownum) in (select empno,mod(rownum,2) from emp));
9. Query to delete alternate rows ?
Delete from emp where (empno,rownum) in (select empno, mod(rownum,2) from emp);
10. Query to print some text with the column values ?
Select empno,deptno, decode (mod(rownum,5),0,'*****') print from emp;
11. Query to get column without specifying the column name ?
Select &n, &q from emp where deptno = 10;
12. Query to delete duplicate rows by leaving one row deleted on specific condition ?
Delete from emp where deptno = 10 and rowid not in (select min(rowid) from emp where deptno = 10);
13. Query to delete duplicate rows but leaving one row undeleted ?
Delete from emp where deptno = 10 and rowid not in (select min(rowid) from emp where deptno = x.deptno);
14. Query to select all columns, rowid with out specifying the column name ?
Select rowid, &a from emp;
15. Query to print static text?
Select empno, sal, 'Maximum from emp where sal = (select max(sal) from emp)
What are Oracle Report Triggers :
As a general rule, any processing that will affect the data retrieved by the report should be performed in the Before Parameter Form or After Parameter Form triggers. (These are the two report triggers that fire before anything is parsed or fetched.) Any processing that will not affect the data retrieved by the report can be performed in the other triggers.
Report Builder has five global report triggers. You cannot create new global report triggers. The trigger names indicate at what point the trigger fires:
Before Report trigger :
Description The Before Report trigger fires before the report is executed but after queries are parsed and data is fetched.
Definition Level report
On Failure
Displays an error message and then returns to the place from which you ran the report .
After Report trigger :
Description The After Report trigger fires after you exit the Runtime Previewer, or after report output is sent to a specified destination, such as a file, a printer, or a mailid. This trigger can be used to clean up any initial processing that was done, such as deleting tables. Note, however, that this trigger always fires, whether or not your report
completed successfully.
Definition Level report
On Failure
Does not affect formatting because the report is done. You can raise a message, though, to indicate that the report did not run correctly. For example, you could put the system time in a variable in the Before Report trigger and then compare it against the system time in the After Report trigger . If the report took longer than a certain time to run, you could raise an error.
Usage Notes
* The After-Report trigger does not fire when you are in the Live Previewer.
Between Pages trigger :
Description The Between Pages trigger fires before each page of the report is formatted, except the very first page. This trigger can be used for customized page formatting. In the Runtime Previewer or Live Previewer, this trigger only fires the first time that you go to a page. If you subsequently return to the page, the trigger does not fire again.
Definition Level report
On Failure
Displays an error message when you try to go to the page for which the trigger returned FALSE. The pages subsequent to the page that returned FALSE are not formatted. If the trigger returns FALSE on the last page, nothing happens because the report is done formatting. The Between Pages trigger does not fire before the first page. If the trigger returns FALSE on the first page, the first page is displayed, but, if you try to go to the second page, an error message is displayed.
Before Parameter Form trigger :
Description The Before Parameter Form trigger fires before the Runtime Parameter Form is displayed. From this trigger, you can access and change the values of parameters, PL/SQL global variables, and report-level columns. If the Runtime Parameter Form is suppressed, this trigger still fires. Consequently, you can use this trigger for validation of command line parameters.
Definition Level report
On Failure
Displays an error message and then returns to the place from which you ran the report .
After Parameter Form trigger :
Description The After Parameter Form trigger fires after the Runtime Parameter Form is displayed. From this trigger, you can access parameters and check their values. This trigger can also be used to change parameter values or, if an error occurs, return to the Runtime Parameter Form. Columns from the data model are not accessible from this trigger. If the Runtime Parameter Form is suppressed, the After Parameter Form trigger still fires. Consequently, you can use this trigger for validation of command line parameters or other data.
Definition Level report
On Failure
Returns to the Runtime Parameter Form. If the Form is suppressed, then returns to place from which you ran the report .
Report trigger must explicitly return TRUE or FALSE. The following table describe what happens when the report trigger returns FALSE.
REPORT TRIGGER | WHAT HAPPENS IF TRIGGER RETURNS FALSE |
Before Report | Displays an error message and then returns to the place from where the report was executed. |
After Report | Does not effect formatting because the report is done.You can raise a message, though, to indicate that the report did not run correctly. |
Between Pages | Displays an error message when you try to go to the page for which the trigger returned FALSE. The pages subsequent to the page that returned FALSE are not formatted. |
Before Parameter Form | Displays an error message and then returns to the place from where the report was executed. |
After Parameter Form | Returns to the Runtime Parameter Form. If the form is suppressed, and then returns to the place from where the report was executed. |
Report trigger order of execution :
The order of events when a report is executed is as follows:
Before Parameter Form trigger is fired.
1 Runtime Parameter Form appears (if not suppressed).
2 After Parameter Form trigger is fired (unless the user cancels from the Runtime Parameter Form).
3 Report is “compiled.”
4 Queries are parsed.
5 Before Report trigger is fired.
6 SET TRANSACTION READONLY is executed (if specified via the READONLY argument or setting).
7 The report is executed and the Between Pages trigger fires for each page except the last one. (Note that data can be fetched at any time while the report is being formatted.) COMMITs can occur during this time due to any of the following-- user exit with DDL, SRW.DO_SQL with DDL, or if ONFAILURE=COMMIT, and the report fails.
8 COMMIT is executed (if READONLY is specified) to end the transaction.
9 After Report trigger is fired.
10 COMMIT/ROLLBACK/NOACTION is executed based on what was specified via the ONSUCCESS argument or setting.
Cautions
· In steps 4 through 9, avoid DDL statements that would modify the tables on which the report is based. Step 3 takes a snapshot of the tables and the snapshot must remain valid throughout the execution of the report . In steps 7 through 9, avoid DML statements that would modify the contents of the tables on which the report is based. Queries may be executed in any order, which makes DML statements unreliable (unless performed on tables not used by the report).
· If you specify READONLY, you should avoid DDL altogether. When you execute a DDL statement (e.g., via SRW.DO_SQL or a user exit), a COMMIT is automatically issued. If you are using READONLY, this will prematurely end the transaction begun by SET TRANSACTION READONLY.
Report trigger restrictions:
· If you are sending your report output to the Runtime Previewer or Live Previewer, you should note that some or all of the report triggers may be fired before you see the report output. For example, suppose that you use SRW.MESSAGE to issue a message in the Between Pages trigger when a condition is met. If there are forward references in the report (e.g., a total number of pages displayed before the last page), Report Builder may have to format ahead to compute the forward references. Hence, even though you have not yet seen a page, it may already have been formatted and the trigger fired.
· In report triggers, you can use the values of report-level columns and parameters.
For example, you might need to use the value of a parameter called COUNT1 in a condition (e.g., IF :COUNT1 = 10).
Note, though, that you cannot reference any page-dependent columns (i.e., a column with a Reset At of Page) or columns that rely on page-dependent columns.
Note, though, that you cannot reference any page-dependent columns (i.e., a column with a Reset At of Page) or columns that rely on page-dependent columns.
· In the Before and After Parameter Form, and Before and After Report triggers, you can set the values of parameters (e.g., give them a value in an assignment statement, :COUNT1 = 15). In the Before and After Report triggers, you can also set the values of report-level, placeholder columns.
· In the Between Pages trigger, you cannot set the values of any data model objects.
Note also that the use of PL/SQL global variables to indirectly set the values of columns or parameters is not recommended. If you do this, you may get unpredictable results.
· If you run a report from Report Builder Runtime (i.e., not the command line or SRW.RUN_REPORT), you should commit database changes you make in the Before Parameter Form, After Parameter Form, and Validation triggers before the report runs. When running in this way, these triggers will share the parent process’ database connection. When the report is actually executed, however, it will establish its own database connection.
· A lexical reference cannot be used to create additional bind variables after the After Parameter Form trigger fires. For example, suppose you have a query like the following (note that the WHERE clause is replaced by a lexical reference): SELECT ENAME, SAL FROM EMP &where_clause
If the value of the WHERE_CLAUSE parameter contains a reference to a bind variable, you must specify the value in the After Parameter Form trigger or earlier.
You would get an error if you supplied the following value for the parameter in the Before Report trigger. If you supplied this same value in the After Parameter Form trigger, the report would run.
· GROUP FILTER :
Description: A group filter is a PL/SQL function that determines which records to include in a group , if the Filter Type property is PL/SQL. The function must return a boolean value (TRUE or FALSE). Depending on whether the function returns TRUE or FALSE, the current record is included or excluded from the report. You can access group filters from the Object Navigator, the Property Palette (the PL/SQL Filter property), or the PL/SQL Editor.
Definition Level group
On Failure Excludes the current record from the group .
· FORMULA :
Description: Formulas are PL/SQL functions that populate formula or placeholder columns. You can access the PL/SQL for formulas from the Object Navigator, the PL/SQL Editor, or the Property Palette (i.e., the PL/SQL Formula property). A column of datatype Number can only have a formula that returns a value of datatype NUMBER. A column of Datatype Date can only have a formula that returns a value of datatype DATE. A column of Datatype Character can only have a formula that returns a value of datatype CHARACTER, VARCHAR, or VARCHAR2.
Definition Level column
On Failure No value is returned for the column.
VALIDATION TRIGGER :
Description: Validation triggers are PL/SQL functions that are executed when parameter values are specified on the command line and when you accept the Runtime Parameter Form. (Notice that this means each validation trigger may fire twice when you execute the report.) Validation triggers are also used to validate the Initial Value
property of the parameter. The function must return a boolean value (TRUE or FALSE). Depending on whether the function returns TRUE or FALSE, the user is returned to the Runtime Parameter Form. You can access validation triggers from the Object Navigator, the PL/SQL Editor, or the Property Palette (Validation Trigger property).
Definition Level parameter
On Failure The user is returned to the parameter value in the Runtime Parameter Form
where they can either change it or cancel the Runtime Parameter Form.
· FORMAT TRIGGER :
Description Format triggers are PL/SQL functions executed before the object is formatted. The trigger can be used to dynamically change the formatting attributes of the object. The function must return a Boolean value (TRUE or FALSE). Depending on whether the function returns TRUE or FALSE, the current instance of the object is included or excluded from the report output. You can access format triggers from the Object Navigator, the Property Palette, or the PL/SQL Editor.
Definition Level layout object
On Failure Excludes the current instance of the object from the output.
Usage Notes
* Format triggers do not affect the data retrieved by the report. For example, if a format trigger returns FALSE for a field, the data for the field is retrieved even though the field does not appear in the output.
* If a format trigger suppresses report output on the last page of the report, the last page will still be formatted and sent to the appropriate output and the page will be included in the total number of pages.
· ACTION TRIGGER :
Description Action triggers are PL/SQL procedures executed when a button is selected in the Runtime Previewer. The trigger can be used to dynamically call another report (drill down) or execute any other PL/SQL. You can access action triggers from the Object Navigator, the Property Palette (PL/SQL Trigger property), or the PL/SQL Editor.
Definition Level button
Usage Notes
* PL/SQL action triggers cannot be tested in the Live Previewer because the buttons are not active there. You must use the Runtime Previewer (choose View Runtime Preview from the Live Previewer).
* You cannot use the PL/SQL Interpreter to debug action triggers because it is not available from the Runtime Previewer and buttons cannot be activated in the Live Previewer. To get around this, you can move your action trigger code to a report trigger to test it from the Live Previewer.
Which are User Exits used in Apps Reports?
· FND FLEXSQL
Call this user exit to create a SQL fragment usable by your report to tailor your SELECT statement that retrieves flexfield values. This fragment allows you to SELECT flexfield values or to create a WHERE, ORDER BY, GROUP BY, or HAVING clause to limit or sort the flexfield values returned by your SELECT statement. You call this user exit once for each fragment you need for your select statement. You define all flexfield columns in your report as type CHARACTER even though your table may use NUMBER or DATE or some other datatype.
Syntax:
FND FLEXSQL
CODE=”flexfield code”
APPL_SHORT_NAME=”application short name”
OUTPUT=”:output lexical parameter name”
MODE=”{ SELECT | WHERE | HAVING | ORDER BY}”
[DISPLAY=”{ALL | flexfield qualifier | segment number}”]
[SHOWDEPSEG=”{Y | N}”]
[NUM=”:structure defining lexical” |
MULTINUM=”{Y | N}”]
[TABLEALIAS=”code combination table alias”]
[OPERATOR=”{ = | < | > | <= | >= | != | ”||” |
BETWEEN | QBE}”]
[OPERAND1=”:input parameter or value”]
[OPERAND2=”:input parameter or value”]
FND FLEXIDVAL
Call this user exit to populate fields for display. You pass the key flexfields data retrieved by the query into this exit from the formula column. With this exit you display values, descriptions and prompts by passing appropriate token (any one of VALUE, DESCRIPTION, APROMPT or LPROMPT).
Syntax:
FND FLEXIDVAL
CODE=”flexfield code”
APPL_SHORT_NAME=”application short name”
DATA=”:source column name”
[NUM=”:structure defining source column/lexical”]
[DISPLAY=”{ALL|flexfield qualifier|segment number}”]
[IDISPLAY=”{ALL|flexfield qualifier|segment
number}”]
[SHOWDEPSEG=”{Y | N}”]
[VALUE=”:output column name”]
[DESCRIPTION=”:output column name”]
[APROMPT=”:output column name”]
[LPROMPT=”:output column name”]
[PADDED_VALUE=”:output column name”]
[SECURITY=”:column name”]
· FND SRWINIT
You call the user exit FND SRWINIT from your Before Report Trigger.FND SRWINIT fetches concurrent request information and sets up profile options. You must include this step if you use any Oracle Application Object Library features in your report (such as concurrent processing).
· FND SRWEXIT
You call the user exit FND SRWEXIT from your After Report Trigger. FND SRWEXIT frees all the memory allocation done in other Oracle Applications user exits. You must include this step if you use any Oracle Application Object Library features in your report (such as concurrent processing).
Basic Implementation Steps
Step 1 : Call FND SRWINIT from your Before Report Trigger
You call the user exit FND SRWINIT from your Before Report Trigger. FND SRWINIT fetches concurrent request information and sets up profile options. You must include this step if you use any Oracle Application Object Library features in your report (such as concurrent processing).
Step 2 : Call FND SRWEXIT from your After Report Trigger
You call the user exit FND SRWEXIT from your After Report Trigger. FND SRWEXIT frees all the memory allocation done in other Oracle Applications user exits. You must include this step if you use any Oracle Application Object Library features in your report (such as concurrent processing).
Step 3 : Call FND FLEXSQL from the Before Report Trigger
You need to pass the concatenated segment values from the underlying code combinations table to the user exit so that it can display appropriate data and derive any descriptions and values from switched value sets as needed. You get this information by calling the AOL user exit FND FLEXSQL from the Before Report Trigger. This user exit
populates the lexical parameter that you specify with the appropriate column names/SQL fragment at run time. You include this lexical parameter in the SELECT clause of your report query. This enables the report itself to retrieve the concatenated flexfield segment values. You call this user exit once for each lexical to be set. You do not display this
column in your report. You use this ”hidden field” as input to the FND FLEXIDVAL user exit call. This user exit can also handle multi–structure flexfield reporting by generating a decode on the structure column. If your report query uses table joins, this user exit can prepend your code combination table name alias to the column names it returns.
SELECT &LEXICAL alias, column
becomes, for example,
SELECT SEGMENT1||’\n’||SEGMENT2 alias, column
Note: Oracle Reports needs the column alias to keep the name of column fixed for the lexicals in SELECT clauses. Without the alias, Oracle Reports assigns the name of the column as the initial value of the lexical and a discrepancy occurs when the value of the lexical changes at run time.
Step 4 : Restrict report data based upon flexfield values
You call the user exit FND FLEXSQL with MODE=”WHERE” from the Before Report Trigger. This user exit populates a lexical parameter that you specify with the appropriate SQL fragment at run time. You include this lexical parameter in the WHERE clause of your report query. You call this user exit once for each lexical to be changed. If your report query uses table joins, you can have this user exit prepend your code combination table name alias to the column names it returns.
WHERE tax_flag = ’Y’ and &LEXICAL < &reportinput
becomes, for example,
WHERE tax_flag = ’Y’ and T1.segment3 < 200 The same procedure can be applied for a HAVING clause.
Step 5 : Order by flexfield columns
You call the user exit FND FLEXSQL with MODE=”ORDER BY” from the Before Report Trigger. This user exit populates the lexical parameter that you specify with the appropriate SQL fragment at run time. You include this lexical parameter in the ORDER BY clause of your report query. You call this user exit once for each lexical to be changed. If your report query uses table joins, you can have this user exit prepend your code combination table name alias to the column names it returns.
ORDER BY column1, &LEXICAL
becomes, for example,
ORDER BY column1, segment1, segment3
Step 6 : Display flexfield segment values, descriptions, and prompts
Create a Formula Column (an Oracle Reports 6.0 data construct that enables you to call a user exit). Call the user exit FND FLEXIDVAL as the Formula for this column. This user exit automatically fetches more complicated information such as descriptions and prompts so that you do not have to use complicated table joins to the flexfield tables. Then
you create a new field (an Oracle Reports 6.0 construct used to format and display Columns), assign the Formula Column as its source, and add this field to your report using the screen painter. You need to include this field on the same Repeating Frame (an Oracle Reports 6.0 construct found in the screen painter that defines the frequency of data
retrieved) as the rest of your data, where data could be actual report data, boilerplate, column headings, etc. The user exit is called and flexfield information retrieved at the frequency of the Repeating Frame that contains your field. In the report data case, the user exit is called and flexfield information retrieved once for every row retrieved with your query. All flexfield segment values and descriptions are displayed left justified. Segment values are not truncated, that is, the Display Size defined in Define Key Segments screen is ignored. Segment value descriptions are truncated to the description size (if one is displayed) or the concatenated description size (for concatenated segments)
defined in the form.
System Parameters:
BACKGROUND Is whether the report should run in the foreground or the background.
COPIES Is the number of report copies that should be made when the report is printed.
CURRENCY Is the symbol for the currency indicator (e.g., "$").
DECIMAL Is the symbol for the decimal indicator (e.g., ".").
DESFORMAT Is the definition of the output device's format (e.g., landscape mode for a printer). This parameter is used when running a report in a character-mode environment, and when sending a bitmap report to a file (e.g. to create PDF or HTML output).
DESNAME Is the name of the output device (e.g., the file name, printer's name, mail userid).
DESTYPE Is the type of device to which to send the report output (screen, file, mail, printer, or screen using PostScript format).
MODE Is whether the report should run in character mode or bitmap.
ORIENTATION Is the print direction for the report (landscape, portrait, default).
PRINTJOB Is whether the Print Job dialog box should appear before the report is run.
THOUSANDS Is the symbol for the thousand's indicator (e.g., ",").
Other Columns of Reports:
Placeholder columns :
A placeholder is a column for which you set the datatype and value in PL/SQL that you define. You can set the value of a placeholder column in the following places:
· the Before Report Trigger, if the placeholder is a report-level column
· a report-level formula column, if the placeholder is a report-level column
· a formula in the placeholder's group or a group below it (the value is set once for each record of the group)
Summary columns:
A summary column performs a computation on another column's data. Using the Report Wizard or Data Wizard, you can create the following summaries: sum, average, count, minimum, maximum, % total. You can also create a summary column manually in the Data Model view, and use the Property Palette to create the following additional summaries: first, last, standard deviation, variance.
Note: For group reports, the Report Wizard and Data Wizard create n summary fields in the data model for each summary column you define: one at each group level above the column being summarized, and one at the report level. For example, if a report is grouped by division, and further grouped by department, then a summary column defined for a salary total would create fields for the sum of salaries for each division and each department group (group-level summaries), and the sum of all salaries (report-level summary).
Formula columns :
A formula column performs a user-defined computation on another column(s) data, including placeholder columns. Formula columns should not be used to set values for parameters.
Description Formulas are PL/SQL functions that populate formula or placeholder columns
. You can access the PL/SQL for formulas from the Object Navigator, the PL/SQL Editor, or the Property Palette (i.e., the PL/SQL Formula property).
A column of datatype Number can only have a formula that returns a value of datatype NUMBER. A column of Datatype Date can only have a formula that returns a value of datatype DATE. A column of Datatype Character can only have a formula that returns a value of datatype CHARACTER, VARCHAR, or VARCHAR2.
Definition : Level column
On Failure: No value is returned for the column.
SQL*loader is one of the Oracle tool which will be used to transfer the data from Flat-File to oracle Database table.
We can find the fallowing files in SQL*loader
1. Flat or Data File
2. Control File
3. Bad File
4. Discard File
5. Log File
1. Flat or Data File
2. Control File
3. Bad File
4. Discard File
5. Log File
Flat Or Data File: This file contains the records in a special format; these records will be fetching for other legacy. The extension of these files might be .dat, .txt, or .csv (comma separated view).
Control File: This is SQL loader execution file, which will be used to transfer the date from file to table. In side of these control file, we will mention the Data file path, table name, column mapping. The extension of control file is .ctl
Control File Creation:
Load data
INFILE ‘Data File Path’
INSERT INTO ‘Table Name’
FIELD TERMINATED BY ‘,’
WHERE deptno = 10
TRAILING NULL COLS(column1 , empno
column2, ename
column3, deptno)
INFILE ‘Data File Path’
INSERT INTO ‘Table Name’
FIELD TERMINATED BY ‘,’
WHERE deptno = 10
TRAILING NULL COLS(column1 , empno
column2, ename
column3, deptno)
Once we develop the control file we will execute this by using fallowing command
C:\> sqlldr user/passward @ Database Control = name of control file (with extension .ctl)This command will start the control file execution, and it will try to read the data and inserting into table. After completion of this execution, automatically three files will gets created
Bad file
Discard file
Log file
Bad File: Bad file contain the records, which are rejected by the SQL*loader. SQL*loader will reject the records, when ever the Flat file format is not correct or if any internal error occurs it will rejected. The extension of bad file is .bad
Discard File: Discard file contains the records which are rejected by the control file, control file reject the records, if record is not satisfying the conditions, which we have mentioned inside of control files the extension of discard file is .dis
Logfile: It contains the complete info of the process, like no of records successfully loaded in to the table
No of records successfully loaded in to the bad file & discard file.
And where the bad, discard file gets created and time taken to complete the process.
Taking the complete log.
SQL* Loader Modes:
INSERT
APPEND
REPLACE
We can replaced the data in to the table by using any one of the allowing method
INSERT: When we are using this statement, table should be empty. SQL * loader will insert the new data form the file.
APPEND: This mode will be use to attach the new record to the existing records.
REPLACE: This will replace the existing records with new records.
C:\> sqlldr userid/passward@Database control=text1.ctl path=direct
SQL* Loader Paths: We can execution SQL* loader in two paths or nodes
Direct
Conventional
By default SQL*loader will be running in conventional mode, if we want to run in direct mode will use the fallowing syntax
C:\> sqlldr userid/passward@Database control=text1.ctl path=direct
Direct mode will disable the table and column constrains and it will insert the data.
Conventional path will check every constrains, if it is satisfied it will insert the record
Conventional path is just like ‘insert statement’
SQL Commands Limitations:
to_date, to_char, upper, lower, Initcap, string, decode, nvl
when clause
sequence_name.next_value, Ref-Cursor
sysdate, ltrim, rtrim, constant
Direct
Conventional
By default SQL*loader will be running in conventional mode, if we want to run in direct mode will use the fallowing syntax
C:\> sqlldr userid/passward@Database control=text1.ctl path=direct
Direct mode will disable the table and column constrains and it will insert the data.
Conventional path will check every constrains, if it is satisfied it will insert the record
Conventional path is just like ‘insert statement’
SQL Commands Limitations:
to_date, to_char, upper, lower, Initcap, string, decode, nvl
when clause
sequence_name.next_value, Ref-Cursor
sysdate, ltrim, rtrim, constant
PL/SQL Stored Procedures :
If you want to make a PL/SQL procedure as a Concurrent Program, then we will define that procedure by using fallowing syntax
Syntax:
CREATE OR REPLACE PROCEDURE Procedure_Name
(errbuf OUT VARCHAR2,
recoded IN VARCHAR2,
x IN NUMBER,
y IN NUMBER) AS
BEGIN
PL/SQL statements;
Fnd_file.put_line (fnd_file.output, ’message’variables);
Fnd_file.put.line (fnd_file.log, ’message’variables);
END ;
ERRBUF: Used to get the error messages in to the log file if any errors occur in side of procedure.
RETCODE: Used to get the status of Concurrent Program
The Status can be either 0 – for success
1 – for warning
2 – for error
Inside of procedure body we can use all valid PL/SQL statements except DBMS_OTUPUT.PUT_LINE Instead of this we will use fallowing to API’S (Application Programming Interface).
API is nothing but a package.
• Fnd_file.put_line(fnd_file.output,’message’variables); - is write for the output file.
• Fnd_file.put.line(fnd_file.log,’message’variables); - is used for log file.
Steps for Developing the Procedure:
1. Develop the procedure as per client requirement.
2. Create an executable with execution method as PL/SQL stored procedure
3. Define the Concurrent Program at as
• EXECUTION
• PARAMETER
• INCOMPATIBILITIES PROGRAM
4. Attach the Concurrent Program to the request group.
5. Attach the request group to the responsibility.
6. Attach the responsibility to user.
7. User will submit program from SRW window
If you want to make a PL/SQL procedure as a Concurrent Program, then we will define that procedure by using fallowing syntax
Syntax:
CREATE OR REPLACE PROCEDURE Procedure_Name
(errbuf OUT VARCHAR2,
recoded IN VARCHAR2,
x IN NUMBER,
y IN NUMBER) AS
BEGIN
PL/SQL statements;
Fnd_file.put_line (fnd_file.output, ’message’variables);
Fnd_file.put.line (fnd_file.log, ’message’variables);
END ;
ERRBUF: Used to get the error messages in to the log file if any errors occur in side of procedure.
RETCODE: Used to get the status of Concurrent Program
The Status can be either 0 – for success
1 – for warning
2 – for error
Inside of procedure body we can use all valid PL/SQL statements except DBMS_OTUPUT.PUT_LINE Instead of this we will use fallowing to API’S (Application Programming Interface).
API is nothing but a package.
• Fnd_file.put_line(fnd_file.output,’message’variables); - is write for the output file.
• Fnd_file.put.line(fnd_file.log,’message’variables); - is used for log file.
Steps for Developing the Procedure:
1. Develop the procedure as per client requirement.
2. Create an executable with execution method as PL/SQL stored procedure
3. Define the Concurrent Program at as
• EXECUTION
• PARAMETER
• INCOMPATIBILITIES PROGRAM
4. Attach the Concurrent Program to the request group.
5. Attach the request group to the responsibility.
6. Attach the responsibility to user.
7. User will submit program from SRW window
If we want to run the SQL script from the Concurrent Program window then we should follow the below steps.
1. Develop the SQL*plus (.SQL)
2. Transfer the SQL script file (.sql) file from local machine to the server in to respectable path CUST_TOP/11.5.0/SQL/.SQL
2. Transfer the SQL script file (.sql) file from local machine to the server in to respectable path CUST_TOP/11.5.0/SQL/.SQL
Then we have to follow the steps to implement (to run from Concurrent Program window) in Oracle Applications.
1. Create executable by executable method SQL*plus
2. Define Concurrent Program, attach executable, parameters, incompatibilities
3. Attach Concurrent Program to request group.
4. Attach request to responsibility
5. Attach responsibility to user
6. User will submit from SRS window.
1. Create executable by executable method SQL*plus
2. Define Concurrent Program, attach executable, parameters, incompatibilities
3. Attach Concurrent Program to request group.
4. Attach request to responsibility
5. Attach responsibility to user
6. User will submit from SRS window.
Simple SQL Script:
Column user_id format 99999999
Column user_name format a50
Column ucreation_date format a11
Prompt *****************************
Prompt This is SQL* Plus Script
Prompt *****************************
SELECT user_id, user_name,creation_date
FROM end_user;
SQL Script with Parameter:
Inside of SQL script we can receive the parameter value by using &1, &2, &3 and so on. First Concurrent Program parameter value will come in the place, where we have mention &1. Second Concurrent Program parameter in to &2 and so on…
Note:
We can define max of 100 PARAMETERS for a Concurrent Program.
The Format Type of Concurrent Program output should be ‘TEXT’.
Oracle Applications Standards for New Report Developments...
For developing a Report in Oracle Applications we should follow three standards.
1. Creation of Bind Variable - P_CONC_REQUEST_ID:We must create a Bind Variable called “P_CONC_REQUEST_ID” (We can’t change this name. It is standard name.). If we run Conc. Prgm. from SRS window, it will give a Request ID. It will get store in ‘P_CONC_REQUEST_ID” automatically. This Bind Variable is useful, when we call another Conc. Prgm. with in a Conc. Prgm.
For developing a Report in Oracle Applications we should follow three standards.
1. Creation of Bind Variable - P_CONC_REQUEST_ID:We must create a Bind Variable called “P_CONC_REQUEST_ID” (We can’t change this name. It is standard name.). If we run Conc. Prgm. from SRS window, it will give a Request ID. It will get store in ‘P_CONC_REQUEST_ID” automatically. This Bind Variable is useful, when we call another Conc. Prgm. with in a Conc. Prgm.
2. FND SRWINIT in Before Report Trigger:
We call the USER_EXIT (‘FND SRWINIT’) form Before Report Trigger. Syntax is
SRW.USER_EXXIT(‘FND SRWINIT’):
This USER_EXIT is initializing the user profiles in the report trigger i.e., before getting the date from the Database.
Note:
While executing the Conc. Prgm. the system allocate memory for the program which contains all details of user. In above syntax,
SRW.USER_EXIT refers to D2K and purpose of this is, when we want to transfer the control from execution of report to other 3rd generation language and again transfer the control to report execution.
FND SRWINIT refers to Oracle Applications. Purpose of this is to get the “User Profile”.
3. FND SRWEXIT in After Report Trigger:
We call the USER_EXIT (‘FND SRWEXIT’) form After Report Trigger. Syntax is
SRW.USER_EXXIT (‘FND SRWEXIT’):
This USER_EXIT is frees the memory which is occupied by user profiles.
We call the USER_EXIT (‘FND SRWEXIT’) form After Report Trigger. Syntax is
SRW.USER_EXXIT (‘FND SRWEXIT’):
This USER_EXIT is frees the memory which is occupied by user profiles.
Navigation Path for Value Set Creation:
Application-> Validation -> Set.
Once we create Independent & Dependent valueset then we can attach values to the valueset by using the following Navigation.
Application ->
-> Validation
-> Values (To create values for value set)
NOTE: Once we attach any value to Independent & Dependent we can’t delete that value, but we can disable that value. Duplicate values are not allowed in list of values.
Develop a Report using Query and by creating valueset :Select USER_ID, USERNAME From FND_USER Where
USER_ID Between :X AND :Y
:$FLEX$ - It is One of the Oracle applications Key word which we use to get the prevents parameter value in current list of values “WHERE Clause”. We can be use Table Values in the “Where Clause Box”.
Query Using: $FLEX$
Select
VENDOR_SITE_ID
From
PO_VENDOR_SITE_ALL
Where
VENODR_ID = :$FLEX$.VEN_TABLE
Note: We can give Where Clause Condition in creation of Second Value Set.
Practical:Query:
Select *
From ORF_ORGANIZATION_DEFINATIONS
Where ORGANIZATION_ID = :P_ORG_ID
And BUSINESS_GROUP_ID = :P_BUSINESS_GROUP_ID
In Where Clause write the statement as
Where
BUSINESS_GROUP_ID = :$FLEX$.BUISINESS_GROUP
Range:
When ever we have to restrict the user with in the given values we use Range. For example when ever our parameter is having “From Date and To Date” we have to use Range option to restrict the user to enter the values between Low and High.
Note:
Pre defined value set for date is “FND_DATE” and its default format is “DD-MON-YY”.Alias name is mandatory when we are specifying ‘:$FLEX$’ and Column Name in ‘Additional Column’.
Application-> Validation -> Set.
Once we create Independent & Dependent valueset then we can attach values to the valueset by using the following Navigation.
Application ->
-> Validation
-> Values (To create values for value set)
NOTE: Once we attach any value to Independent & Dependent we can’t delete that value, but we can disable that value. Duplicate values are not allowed in list of values.
Develop a Report using Query and by creating valueset :Select USER_ID, USERNAME From FND_USER Where
USER_ID Between :X AND :Y
:$FLEX$ - It is One of the Oracle applications Key word which we use to get the prevents parameter value in current list of values “WHERE Clause”. We can be use Table Values in the “Where Clause Box”.
Query Using: $FLEX$
Select
VENDOR_SITE_ID
From
PO_VENDOR_SITE_ALL
Where
VENODR_ID = :$FLEX$.VEN_TABLE
Note: We can give Where Clause Condition in creation of Second Value Set.
Practical:Query:
Select *
From ORF_ORGANIZATION_DEFINATIONS
Where ORGANIZATION_ID = :P_ORG_ID
And BUSINESS_GROUP_ID = :P_BUSINESS_GROUP_ID
In Where Clause write the statement as
Where
BUSINESS_GROUP_ID = :$FLEX$.BUISINESS_GROUP
Range:
When ever we have to restrict the user with in the given values we use Range. For example when ever our parameter is having “From Date and To Date” we have to use Range option to restrict the user to enter the values between Low and High.
Note:
Pre defined value set for date is “FND_DATE” and its default format is “DD-MON-YY”.Alias name is mandatory when we are specifying ‘:$FLEX$’ and Column Name in ‘Additional Column’.
Jul 22, 2008
Oracle Application Object Library uses values; value sets and validation tables as important components of key FLEXFIELDs, descriptive FLEXFIELDs, and Standard Request Submission. This section helps you understand, use and change values, value sets, and validation tables. When you first define your FLEXFIELDs, you choose how many segments you want to use and what order you want them to appear. You also choose how you want to validate each of your segments. The decisions you make affect how you define your value sets and your values. You define your value sets first, either before or while you define your FLEXFIELD segment structures. You typically define your individual values only after your FLEXFIELD has been completely defined (and frozen and compiled). Depending on what type of value set you use, you may not need to predefine individual values at all before you can use your FLEXFIELD.
You can share value sets among segments in different FLEXFIELDs, segments in different structures of the same FLEXFIELD, and even segments within the same FLEXFIELD structure. You can share value sets across key and descriptive FLEXFIELDs. You can also use value sets for report parameters for your reports that use the Standard Request Submission feature.
Because the conditions you specify for your value sets determine what values you can use with them, you should plan both your values and your value sets at the same time. For example, if your values are 01, 02 instead of 1, 2, you would define the value set with Right–Justify Zero–fill set to Yes.
Value set is nothing but List of Values with validations. We can use the Value Sets when ever the Concurrent Program has parameters and while defining the Flex Fields. We have to attach the value sets to the Concurrent Program. Validations are depending on Client Requirement.
Value sets are of 8 types.There are several validation types that affect the way users enter and use segment or parameter values:
1. None (not validated at all)
2. Independent
3. Dependent
4. Table
5. Special (advanced)
6. Pair (advanced)
7. Translatable Independent
8. Translatable Dependent
1. None (not validated at all)
2. Independent
3. Dependent
4. Table
5. Special (advanced)
6. Pair (advanced)
7. Translatable Independent
8. Translatable Dependent
You cannot change the validation type of an existing value set, since your changes affect all FLEXFIELDs and report parameters that use the same value set.
None: You use a None type value set when you want to allow users to enter any value so long as that value meets the value set formatting rules. That is, the value must not exceed the maximum length you define for your value set, and it must meet any format requirements for that value set. For example, if the value set does not allow alphabetic characters, your user could not enter the value ABC, but could enter the value 456 (for a value set with maximum length of three). The values of the segment using this value set are not otherwise validated, and they do not have descriptions. Because a NONE value set is not validated, a segment that uses this value set does not provide a list of values for your users. A segment that uses this value set (that is, a non–validated segment) cannot use FLEXFIELD value security rules to restrict the values a user can enter.
Independent > An Independent value set provides a predefined list of values for a segment. These values can have an associated description. For example, the value 01 could have a description of ‘Company 01’. The meaning of a value in this value set does not depend on the value of any other segment. Independent values are stored in an Oracle Application Object Library table. You define independent values using an Oracle Applications window, Segment Values.
Table > A table–validated value set provides a predefined list of values like an independent set, but its values are stored in an application table. You define which table you want to use, along with a WHERE cause to limit the values you want to use for your set. Typically, you use a table–validated set when you have a table whose values are already maintained in an application table (for example, a table of vendor names maintained by a Define Vendors form). Table validation also provides some advanced features such as allowing a segment to depend upon multiple prior segments in the same structure.
Dependent > A dependent value set is similar to an independent value set, except that the available values in the list and the meaning of a given value depend on which independent value was selected in a prior segment of the FLEXFIELD structure. You can think of a dependent value set as a collection of little value sets, with one little set for each independent value in the corresponding independent value set. You must define your independent value set before you define the dependent value set that depends on it. You define dependent values in the Segment Values windows, and your values are stored in an Oracle Application Object Library table.
Special and Pair Value Sets:
Special and pair value sets provide a mechanism to allow a”FLEXFIELD–within–a–FLEXFIELD”. These value sets are primarily used for Standard Request Submission parameters. You do not generally use these value sets for normal FLEXFIELD segments. Special and Pair value sets use special validation routines you define. For example, you can define validation routines to provide another FLEXFIELD as a value set for a single segment or to provide a range FLEXFIELD as a value set for a pair of segments.
Translatable Independent and Translatable Dependent :A Translatable Independent value set is similar to Independent value set in that it provides a predefined list of values for a segment. However, a translated value can be used. A Translatable Dependent value set is similar to Dependent value set in that the available values in the list and the meaning of a given value depend on which independent value was selected in a prior segment of the FLEXFIELD structure. However, a translated value can be used. FLEXFIELD Value Security cannot be used with Translatable Independent or Translatable Dependent value sets. For format validation, translatable value sets must use the format type Char. The maximum size must be no greater than 150. The Number Only option and the Right–justify and Zero–Fill Numbers option cannot be used with translatable value sets. Range FLEXFIELDs cannot use Translatable Independent or Translatable Dependent value sets.
Developing Report with out Parameters:
To develop any report in Report-6i tool, select “Build a new Report Manually” option.
Select SQL query object and write the Select Statement.
It will ask the Database connection. Provide User id, Password and Connection string (DB host) – APPS/APPS@ORCL.
Create Layout manually according to the given query.
Compile the report with out errors.
Save the report in Local Machine.
Copy the Report and paste it in respective path (Respective TOP).
Path - APPS/ D / Oracle / PROD_APPL / PO / 11.5.0 / Reports / US / .rdf
Implementing Report in Oracle Applications:Connect to APPS Server.
Create Executable for created report.Navigation: System Administrator
->Responsibility
-> Concurrent Program
->Executable
Mandatory Fields in this window are: Executable, Short Name, Application, Execution File Name
Note: From here we can refer the Report (Executable) with Short Name;
Create the Concurrent Program for this Executable.
Navigation: System Administrator
->Concurrent
-> Program
-> Define
Give the required information like Program, Short Name, Application, Executable (Name, Method), Output (Format…) etc.
Create Request Group.Navigation: Security
-> Responsibility
-> Request
Here we have to give information like Group (RG Name), Application (E.g. Oracle Purchasing)
Add the Concurrent Program as Request to Request Group which is created.
To develop any report in Report-6i tool, select “Build a new Report Manually” option.
Select SQL query object and write the Select Statement.
It will ask the Database connection. Provide User id, Password and Connection string (DB host) – APPS/APPS@ORCL.
Create Layout manually according to the given query.
Compile the report with out errors.
Save the report in Local Machine.
Copy the Report and paste it in respective path (Respective TOP).
Path - APPS/ D / Oracle / PROD_APPL / PO / 11.5.0 / Reports / US / .rdf
Implementing Report in Oracle Applications:Connect to APPS Server.
Create Executable for created report.Navigation: System Administrator
->Responsibility
-> Concurrent Program
->Executable
Mandatory Fields in this window are: Executable, Short Name, Application, Execution File Name
Note: From here we can refer the Report (Executable) with Short Name;
Create the Concurrent Program for this Executable.
Navigation: System Administrator
->Concurrent
-> Program
-> Define
Give the required information like Program, Short Name, Application, Executable (Name, Method), Output (Format…) etc.
Create Request Group.Navigation: Security
-> Responsibility
-> Request
Here we have to give information like Group (RG Name), Application (E.g. Oracle Purchasing)
Add the Concurrent Program as Request to Request Group which is created.
Create Responsibility.
Navigation: Security
-> Responsibility
->Define
While creating Responsibility we need to mention three things.
· Data Group – Required. It is nothing but Oracle User ID
· Menu – Required. Collection of Forms, which are related to specific business
· Request Group – Optional. Collection of Concurrent Programs and Reports which are related to specific business
Note: If we create Responsibility with Request Group, no need to add RG again to Responsibility.
Navigation: Security
-> Responsibility
->Define
While creating Responsibility we need to mention three things.
· Data Group – Required. It is nothing but Oracle User ID
· Menu – Required. Collection of Forms, which are related to specific business
· Request Group – Optional. Collection of Concurrent Programs and Reports which are related to specific business
Note: If we create Responsibility with Request Group, no need to add RG again to Responsibility.
Create User.
Navigation: Security
-> User
-> Define
Attach the Responsibility to User.
Run the Concurrent Program from SRS window.
Note: All the Concurrent Programs should run from SRS (Standard Request Submission) window.
Navigation: Security
-> User
-> Define
Attach the Responsibility to User.
Run the Concurrent Program from SRS window.
Note: All the Concurrent Programs should run from SRS (Standard Request Submission) window.
How to go to SRS window:
Select relative Responsibility (FILE à Switch Responsibility)
Go to View à Requests à Submit New Request à Select the Report à Click Submit
After Submission it will give Request ID. Here 2 fields View Report, View LOG File will give information of the Resultant file created by Concurrent Program.
Note:
· We can find all O/P and LOG files by the following query from the Database.
Select LOGFILE_NAME, OUTFILE_NAME
From FND_CONCURRENT_REQUESTS
Where REQUEST_ID = ‘XXX’;
· We can’t delete Concurrent Program but we can disable it.
· Columns and Rows fields will be used to mention the O/P file Columns and Rows.
· Save, This Check Box can use to save the O/P file in the server.
· Use in SRS, if we uncheck this Check Box, then this we can’t run this program from SRS window.
· Copy To, We can create new for the existing Report.
Scheduling the Concurrent Program:
We can schedule the Concurrent Program in 4 ways.
· As Soon As Possible (Default)
· Once
· Periodically
· On Specific Days
We can save the Schedule and can apply this Schedule to any other Concurrent Program.
We can run with a message in the LOG File by using SRW.MESSAGE() function.
Develop Report with Parameters:
There are 2 types of Parameters.
1. Bind parameters
2. Lexical Parameters
Bind Parameter: Bind parameter is a variable which we will use to pass the value. We should use ‘:’ before any variable in a query.
Lexical parameter: Lexical parameter is a parameter which we will use inside of a query. By using this parameter we can replace any clause or any where inside of the statement like Select, From, Where, Order By clauses.
If our report has parameters then we should define parameters from the report while creating Concurrent Program. While defining the parameters we should mention following fields.
It is a list of values with validations. We have to use value set for a parameter. By using this we can restrict the invalid entries by the end-user.
Prompt: This field will use to display the string while submitting the Concurrent Program in the Parameter Form.
Token: It is a field which is use to give the link between Concurrent Program Parameter and report Bind Variable. When we create Bind Variables in report those may or may not be in the same sequence. So we can map these bind Variable with the Token fields.
Required Check Box: If we uncheck this, the parameter is optional, otherwise it is mandatory.
Default Types: There are 4 default types.
· Constant
· Profile
· SQL Statement
· Segment
Constant: We can select this default type if we have to pass the constant value to parameter of the Concurrent Program. We can mention the value in the “Default Value Field” at right side.
Profile:
SQL Statement: If we have to set the default value as SQL Query result set then we should select default value type is SQL Statement and we have to enter the SQL Query in the ”Default Value field”.
Segment: If we want to get previous parameter value as default to the next parameter, we have to select the default type as Segment and we have to give the previous parameter name in “Default Value Field”.
Note: The query which we have to enter in default value field should give only one value
Using the format trigger we can hide or display the “Layout Object”. This layout object can be a Field or Text.
We can display input parameter values in the first page (or any other pages) by using Bind Variables.
