Oracle PL/SQL – Calculating Weekdays between two dates


Calculating total number of Week Days between two given dates is most common requirement. This article shows few methods to do this.

Calculating total number of Week Days between two given dates

————————————————————-

CREATE OR REPLACE FUNCTION TotWeekDays( FromDate DATE ,

ToDate DATE )

RETURN NUMBER IS

ToTalSunDays NUMBER := NEXT_DAY(ToDate – 7,’SUNDAY’) –

NEXT_DAY(FromDate – 1,’SUNDAY’) ;

ToTalSaturDays NUMBER := NEXT_DAY(ToDate – 7,’SATURDAY’) –

NEXT_DAY(FromDate – 1,’SATURDAY’) ;

BEGIN

RETURN (ToDate – FromDate – (TotalSundays+TotalSaturdays)/7 -1) ;

END ;

Executing this function in a pl/sql block…

SQL> SET SERVEROUTPUT ON

SQL> DECLARE

2 BusinessDays NUMBER;

3 BEGIN

4 BusinessDays := TotWeekDays(’01-MAY-2001′,’31-MAY-2001′) ;

5 DBMS_OUTPUT.PUT_LINE(‘Total Business Days : ‘||TO_CHAR(BusinessDays) ) ;

6* END;

SQL> /

Total Business Days : 23

How to restrict functions and menus available to a user in menus


Hello Friends, Using this article, i wanted to give brief on how we can exclude some forms/ function as security in oracle applications. plz see i am trying to use two different way to get the same functionality.
There are two possibilities:
A – Restrict an existing menu:
1) Log in as System Administrator responsibility
2) Find the name of the appropriate menu      Navigator: Application -> Menu
3) Create a new responsibility       Navigator: Security -> Responsibility -> Define
4) Assign the appropriate menu to the new responsibility
5) Enter functions in the Function and Menu Exclusions section of the form Responsibilities
6) Save
7) Assign the new responsibility to the appropriate user

Example:  Restrict a user to be able to do only Assignments in the Asset Workbench.
1) Log in as the System Administrator responsibility
2) Find the name of the appropriate menu:
Description: %Asset%
2) Create a new responsibility: ASSET – assignments
3) Assign the menu FA_TRANSACTION to the responsibility ASSET – assignements
4) Enter all functions from the LOV in the Function and Menu Exclusions
section except of the Responsibilities form except “Asset Workbench” and
“Assets:Assignments”
5) Save
6) Assign the responsibility ASSET – assignments to the appropriate user

B – Create a new menu:
1) log in as System Administrator responsibility
2) Create a new menu: Navigator: Application -> Menu      The new menu should contain only the functions and menus which should be accessible for the user.
3) Create a new responsibility       Navigator: Security -> Responsibility -> Define
4) Assign the appropriate menu to the new responsibility
5) Save
6) Assign the new responsibility to the appropriate user

Example:  Restrict a user to be able to do only Assignments in the Asset Workbench.
1) Log in with the System Administrator responsibility
2) Create the new menu menu:
Navigator: Application -> Menu
Menu: FA_ASSIGN
User Menu Name: FA Assignement
Description: FA Assignement
Seq: 1 Navigator Prompt: Assignment Function: Asset Workbench
Seq: 2 Navigator Prompt: Assignment Function: Assets:Assignements
3) Create a new responsibility: ASSET – assignments
4) Assign the menu FA_ASSIGN to the responsibility ASSET – assignements
5) Save
6) Assign the responsibility ASSET – assignments to the appropriate user

Please share your feedback and also if you think this article is helpful for you. or if this is difficult to understand and use, so i can improve on my future articles. thanks – Shivmohan Purohit

Oracle Database related Questions


Here there are very basic still sometime become critical to know concept if you working in Oracle Database related activities. 

Q: What is an Oracle instance?Every running Oracle database is associated with an Oracle instance. When adatabase is started on a database server (regardless of the type of computer),Oracle allocates a memory area called the System Global Area (SGA) and starts one or more Oracle processes. This combination of the SGA and the Oracle processes is called an Oracle instance. The memory and processes of an instance manage the associated database’s data efficiently and serve the one or multiple users of the database.
The Instance and the Database

 
After starting an instance, Oracle associates the instance with the specified database. This is called mounting the database. The database is then ready to be opened, which makes it accessible to authorized users. Multiple instances can execute concurrently on the same computer, each accessing its own physical database. In clustered and massively parallel systems (MPP),the Oracle Parallel Server allows multiple instances to mount a single database. Only the database administrator can start up an instance and open the database.If a database is open, the database administrator can shut down the database so that it is closed. When a database is closed, users cannot access the information that it contains. Security for database startup and shutdown is controlled via connections to Oracle with administrator privileges. Normal users do not have control over the current status of an Oracle database.

 

 

 

Q: What is a view?

A view is a tailored presentation of the data contained in one or more tables(or other views). Unlike a table, a view is not allocated any storage space, nor does a view actually contain data; rather, a view is defined by a query that extracts or derives data from the tables the view references. These tables are called base tables. Views present a different representation of the data that resides within thebase tables. Views are very powerful because they allow you to tailor the presentation of data to different types of users. Views are often used to:

• provide an additional level of table security by restricting access to a predetermined set of rows and/or columns of a table

• hide data complexity

• simplify commands for the user

• present the data in a different perspective from that of the base table

• isolate applications from changes in definitions of base tables

• express a query that cannot be expressed without using a view

Q: What is referential integrity?
 

 

Rules governing the relationships between primary keys and foreign keys of tables within a relational database that determine data consistency. Referential integrity requires that the value of every foreign key in every table be matched by the value of a primary key in another table.

Q: What is a cursor?A cursor is a private sql work area used to perform manipulations on data using pl\sql, mainly used for multiple row manipulations and locking columns. Data which is populated into the cursor is known as active dataset.

 

Cursors are of two types 1.implicit   2.explicit

Implicit———attributes or properties for implicit cursor

1.sql%is open:attribute returns a boolean value stating wether the cursor is open or closed.

2.sql % found: returns boolean value stating whether the record is found in the cursor.

3.sql%notfound : returns a boolean value stating whether the record is not found in the cursor

4.sql %rowcount :returns a pneumeric value stating no.of rows executed in the cursor.

Explicit cursors—retrives multiple rows, users can perform locks on th data in the cursor attributes-

1.% is open

2.% found

3.% not found

4.% rowcount

WE CAN WRITE TWO CURSORS IN ONE PROGRAM

WE CAN WRITE A CURSOR SPECIFYING PARAMETERS

CURSOR WITH UPDATE CLAUSE IS USED TO PERFORM LOCKS ON DATA.

Q: Why Use Sql* Loader in Oracle Database? 
The Sql Loader utility loads data into an existing ORACLE table from an external files.

 

 

How To Open A Worksheet Directly From Oracle Applications Menu ?


Hello Friends, many of my past work involve integrating oracle discoverer with oracle applications, here there is one of the ways to leverage both to integrate within applications.

 How To Open A Worksheet Directly From Oracle Applications Menu ?

 

 If a workbook has 4 worksheets how to default to a particular worksheet. This article will help you to pass the worksheet identifier and parameter directly while opening a workbook. The worksheet identifier and the worksheet parameters for the workbook has to be specified in the form function with below mentioned guidlines.
 

Form Function  Definition

 

Function Name

Internal Function Name

Function User Name

Function name (as shown on the menu)

Type

WWW

Description

Function description

Parameters

workbook=workbookname
&PARAMETERS=sheetid~worksheet id*param_parameter name One~Parameter One Value*param_parameter name Two~Parameter Two Value*

HTML Call

OracleOasis.RunDiscoverer

 

 Parameters in the parameter list are delimited by the characters

“param_” at the beginning of each parameter and
“~” as delimiter between parameter name and value and
“*” at the end of each parameter

 ICX interprets the PARAMETER list and passes the parameters to Discoverer in the required URL format.

 Example 1

 If the workbook id is CAHRMS_ABSENCE_CALENDAR_REPORT and worksheet id is 1 then parameter will be as follows

 workbook=CAHRMS_ABSENCE_CALENDAR_REPORT&PARAMETERS=sheetid~1*

 Example 2

If the workbook id is CAHRMS_ABSENCE_CALENDAR_REPORT , worksheet id is 1 and the value for worksheet parameter “deptname”  to be passed is 10 then parameter will be as follows

workbook=CAHRMS_ABSENCE_CALENDAR_REPORT&PARAMETERS=sheetid~1*param_deptname~10*

 Example 3

If a workbook has two parameters “Company” and “City” you would specify in the Parameter list of the Form funtion:

param_Company~Oracle Corp.*param_City~Redwood Shores* 

Thanks – Shivmohan . do share your feedback

Oracle Applications – FNDLOAD Utility


Hello Friend, here is the long awaited and most demanding article on FNDLOAD. The Generic Loader (FNDLOAD) is a concurrent program that can transfer Oracle Application entity data between database and text file.The loader reads a configuration file to determine which entity to access.

1 – Printer Styles

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

2 – Lookups

 

3 – Descriptive Flexfield with all of specific Contexts
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME=”FND”
LOOKUP_TYPE=”lookup name”
 

 

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX P_LEVEL=’COL_ALL:REF_ALL:CTX_ONE:SEG_ALL’ APPLICATION_SHORT_NAME=”FND” DESCRIPTIVE_FLEXFIELD_NAME=”desc flex name” P_CONTEXT_CODE=”context name” 

4 – Key Flexfield Structures

 

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

5 – Concurrent Programs

 

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME=”FND” CONCURRENT_PROGRAM_NAME=”concurrent name” 

6 – Value Sets

 

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

7 – Value Sets with values

 

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

8 – Profile Options

 

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME=”profile option” APPLICATION_SHORT_NAME=”FND” 

8 – Request Groups

 

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME=”request group” APPLICATION_SHORT_NAME=”FND” 

10 – Request Sets

 

11 – Responsibilities
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET
APPLICATION_SHORT_NAME=”FND” REQUEST_SET_NAME=”request set”
 

 

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY=”responsibility” 

12 – Menus

 

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

 

 

 

 

Reference: Oracle Application System Administrator’s Guide Appendix C has detail description on the usage of loaders. Thanks – Shivmohan Purohit

 

 

 

 

 

 

Create a menu function to launch a concurrent request , request group and a request set


Hello Friends, here again i trying to share very smart and easy way to do very secure feature to run either particular request/ program or a request set or a certain request group using a menu option and attaching “standard request submission” form.

How to create a menu function to launch a concurrent request , request group and a request set ?

Create a Function and assign Form name “Run Reports”
   
Request:
Pass parameters PROGRAM_APPL_SHORT_NAME=”<Name>” CONCURRENT_PROGRAM_NAME=”<Name>”.
   
Request Group:

Pass parameters REQUEST_GROUP_CODE=”< Name >” REQUEST_GROUP_APPL_SHORT_NAME=”< Name>”

 

Request Set : Pass parameters REQUEST_SET_NAME = “< Name >”   SET_APPL_SHORT_NAME =”< Name>”      SUBMIT_ONCE  (default ’N’) Note :      SUBMIT_ONCE can be set to either Y or N ( N is the default).      SUBMIT_ONCE is used in conjunction with CONCURRENT_PROGRAM_NAME or REQUEST_SET_NAME.      If SUBMIT_ONCE is set to Y, then the form will exit after the Submit button is clicked.

 

Thanks, do share your feedback n comments.
shivmohan