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



ToDate DATE )


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

NEXT_DAY(FromDate – 1,’SUNDAY’) ;

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

NEXT_DAY(FromDate – 1,’SATURDAY’) ;


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


Executing this function in a pl/sql block…



2 BusinessDays NUMBER;


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

Oracle 11i Application Developer – Technical FAQ – Part 2

Hello Friends, here is the second set of Questions on Application Developers , more of general questions, i am not digging in particular areas , only trying to put question which i normally ask with candidates. If you want help in any specific areas , plz advise, i will try to do that.

Q11 ) What is SET-OF-BOOKS?
Collection of Chat of Accounts and Currency and Calendars is called SOB

Q12 ) What is the interface?
Interface Table is a table which is used as medium for transfer of data between two systems.

Q13 Tell me what is the procedure to develop an interface?
a. First we will get the Requirement document.
b. We will create control file based on that plot file.
c. Then the control files which loads the data into staging tables.
d. Through pl/sql programs we will mapping and validate the data and then dump into the interface tables.
e. Through the standard programs we will push the data from interface tables to Base tables.

Q14) What is multi org?
“Legal entity has more than one operating unit is called as multi org”
a) Business group — Human resources information is secured by Business group
b) Legal entity. — inter-company and fiscal/tax reporting.
c) Operating unit — secures AR, OE, AP, PA and PO Information.
d) Organizations — is a specialize unit of work at particular locations

Q15) What are the User PARAMETERS in the Reports?

FND SRWINIT sets your profile option values, multiple organizations and allows Oracle Application Object Library user exits to detect that they have been called by an Oracle Reports program.
FND SRWEXIT ensures that all the memory allocated for AOL user exits have been freed up properly.
FND FLEXIDVAL are used to display flex field information like prompt, value etc
FND FLEXSQL these user exits allow you to use flex fields in your reports
FND FORMAT_CURRENCY is used to print currency in various formats by using formula column

Q17) what are the two parameters that are mandatory for pl/sql type concurrent program?
Procedure/function (ERRBUF OUT
ERRBUF :- Used to write the error message to log or request file.
RETCODE :- Populate log request file with program submission details info.

Q18.) What is Value Set?
–The value set is a collection (or) container of values.
–When ever the value set associated with any report parameters. It provides list of values to the end user to accept one of the values as report parameter value.
n If the list of values needed to be dynamic and ever changing and define a table based values set.

Q19) What r the validation types in Value Set?
1) None ——– validation is minimal.
2) Independent ——input must exist on previously defined list of values
3) Dependent ——input is checked against a subset of values based on a
prior value.
3) Table —– input is checked against values in an application table
4) Special ——values set uses a flex field itself.
5) Pair —— two flex fields together specify a range of valid values.
6) Translatable independent —– input must exist on previously defined list
of values; translated values can be used.
7) Translatable dependent ——- input is checked against a subset of values
based on a prior values; translated value can be used.

Q20) Who information’s?
1) Created by
2) Creation date
3) Last _updated by
4) last_update_date

Thanks  — Shivmohan Purohit

Learning New Tools – A Perspective

Learning new tools and technologies has become part of daily chores of any IT professional. There is no way out. Or there is no good reason of why one should not learn new things. I personally am a tech savvy guy and always in lookout of learning new things. The interest is not just to learn things pertaining to data warehouse and BI but everything, which comes on way. The only thing that the new tools/technology I learn should have some fundamentals or concepts to take home.

During this last 6-7 years of being into IT, I have learned numerous theories, technologies, programming languages, tools etc. Most of them were through self-learning. But this self-learning was dependent on all my previous learnings, which I inculcated in the past and without which all this self learning would not have been possible. Today I just picked one more tool/technology to build some understanding on it. I don’t have the access to the software but just the documentation. This is one among few tools/technology I am trying to learn for which I don’t have the access of the software. Though I have hands on extensive hands-on experience on a similar kind of technology by another vendor.

This whole thing lead me to think of how can one approach taking up new tool/technology. Possibly three ways which came into my mind:

1. First hit the document.. get some background. and then come to the tool/hands-on and then again go back to the manuals/references. .. an then back to hands on.. May be over the period doing both things simultaneously.
2. First hit the tool ..let your intuition take over the wheel first.. play around stretch your understanding/intuition… and then come back to references/manual/docs/some text and then back to the tool. Over the period both doing both things simultaneously.
3. First attend some seminar ,some talk, some discussion ( as good as 1 but instead of text you are get into more live things) and then hit the tool may be then back to the manuals tools.. come back to tool/hands-on then go back to discussion and so forth. May be I call it Spaghetti approach. In this approach it could be that you start with Books first and then tools an then talks or any combination.

Which to choose?? Time and availability of resources can give the right call for this.. I keep trying all this approaches. Most of the times approach 2 is a good deal for me. Approach 1 is something we have been trying since the college days. First read about the “c” language, listen some lectures… and then get to the labs for some hands-on. And that was good since one didn’t had so many fundamentals/concepts built up, not so much of exposure to the tools/languages of similar kind. Again like all my postings, there is no need to reach to conclusion of which is better and which not. Depends like everything else. My idea here is just to bring out some points.

Oracle Applications / Financials – Functional Question on General Ledger – Budgeting

Oracle Applications / Financials – Functional Question on General Ledger – Budgeting. Quite useful as FAQ and Interview Questions for General Ledger.

11. What is Set of Books? What are the four conditions when you change your SOBs?SOB is of 2 types – Primary and Reporting.Primary SOB – All transactions are with functional Currency

12. What is an Invoice?
AR invoice is a document sent to the customer with details like, Bill-to customer code, product code, qty sent, price, currency, credit terms, tax details, etc. Based on this invoice, customer will make payment to the company and the same is applied against the invoice. AP invoice is the document received from the supplier and contains information such supplier details, product code, qty, price and tax details. This invoice is entered in the AP module and payment is made to the supplier against this invoice.
There are 2 types of invoices-

1. Periodic 2. Milestone

Also, Invoice is an information sheet which a company sends to the buyer along with the good. It explains the details of the goods in the shipment and also the prices. Invoices can contain all sorts of data regarding the shipment and goods depending on the company and product.

13. Can you disable budgetary control for a set of books?

You can, however existing encumbrances are not cleared from the feeder systems. Therefore it is not recommended. If you do change the budgetary control options for an existing set of books, you must do two things for the change to be reflected.

–Run the Period Map Maintenance concurrent request, it must complete successfully.

–Exit Oracle Applications and restart. You must completely exit the application…it is not sufficient to select Sign on again from the Oracle Applications Special menu.

14. Is there a limit to the number of periods in a budget year or how many years a budget can span?

There is no limit for the budget. One can define budgetary control for n number of years however, one year can have maximum of 60 fiscal periods.

15. Why don’t my Detail budgets roll up to my Master budget?
Detail budgets do not automatically roll up to the master budget. The GL uses summary accounts to maintain master/detail budget relationships between them.

16. I was able to post a budget journal to a closed period, why?
Yes, a budget journal can be posted to any period that is in an open budget year for that budget. This is regardless of the status of that period. The budget journal is not linked with your accounting period. Once you have open the budget period then you can book budget journal for that whole period.

17. How many ‘Current’ budgets can you have?
For each set of books, you can have only one current budget at any point in time. The only distinction between a ‘current’ and an ‘open’ budget is that the current budget defaults into the budget field on several budget-related forms. It can be replaced however by any ‘open’ budget in the field.
18. What is a funding budget?

Funding budget is a budget against which accounting transactions are checked for available funds when budgetary control is enabled for your set of books. Funding Budgets are approved budgets.Two types of budgets are there in Oracle Apps: 1- Fund 2- Plan.

Fund budget create the Budget Journal but plan budget used only for planning. Fund budget requires journal entries, and is assigned to a summary template or account range in the budget org, where the funds check level is set at Absolute or Advisory. It is the assignment that makes it a ‘funding budget’; it is not done at the budget definition level.

19. Why is my budget requiring budget journals?
At the set of books level that option is not enabled? This would happen when the budget itself is defined to require budget journals. This is done at the budget definition level.

20. Why can’t I inquire on my budget amounts from INQUIRE/BUDGETS navigation path?
The Budget Inquiry form (GLXIQBUD) is used to perform inquiries about master and detail budgets. GL compares summary balances between your master and detail budgets, and checks for budget variances and violations. This form only looks at summary accounts. To inquire on detail accounts you must use the navigation INQUIRE/ACCOUNTS, and choose the ‘budget’ amount type.

21. If I delete my budget org, will the budget amounts be deleted?
No, the amounts will be same. Deleting the budget organization does not remove the budget amounts from the GL_BALANCES table.

22. Can I update/adjust an existing account range in my budget organization?
Yes you can update an existing account range in Budget Organization.

23 How many times can a budget be purged?
Budget can be purged only one time.

24. Why is there no value in the REQUEST_ID column of GL_BUDGET_INTERFACE for rows with data that failed to be uploaded by the Budget Spreadsheet Upload program?
You are trying to open the next budget year. After navigating to the form and querying the budget, you notice the [Open Next Year] button is grayed out. You find that Account code combinations are not being added to the Budget Organization.
25. Why don’t my Detail budgets roll up to my Master budget?

Detail budgets do not automatically roll up to the master budget. The GL uses summary accounts to maintain master/detail budget relationships between hierarchy levels. Summary templates are defined so that accounts in your lower level detail budgets roll up into the same summary accounts as the detail accounts in your controlling master budget. A common misconception is that the detail budgets somehow roll up to the master budget by definition, this is not true. You must actually budget to a detail account in the master budget; this then serves as the controlling amount for the detail budgets. Master/Detail budgets are used in the budgeting process to control Authority and identify budgets that exceed control limits. They are not intended for reporting purposes.

26. I was able to post a budget journal to a closed period, why?
A budget journal can be posted to any period that is in an open budget year for that budget. This is regardless of the status of that period (closed, opened, or future enterable).
27. Why don’t my budget amounts appear on my FSG?

To include budgets (encumbrances or currencies) in a FSG report, your report definition must specify a row set of column set that has control values specified in the Balance Control options. In the report definition itself, you associate budget names with the control values that are assigned to the row or column set.

Shivmohan Purohit

Reporting SOB – All transactions are with foreign Currency

You have to assign your SOB to your responsibility. Set of book means linking between the 3cs i.e. chart of accounts, calendar, currency and maintain 5 mandatory accounts. Set of books are created keeping in mind the ‘Accounting Structure”. This is a combination of 3 ‘Cs’ – Chart of Accounts, Currency and Calendar. Chart of Accounts is made up of segments- Max of 30 Segments are allowed. Two Segments are mandatory – Balancing and Natural segment, Calendar –This can have a max of 365 periods (daily calendar) and a min of 1 period (Yearly calendar). Periods can be monthly, fortnightly, weekly, daily.

Oracle – Technical FAQ – Part 4

 Q18.) What are the OOPS concepts in Oracle?

A18.) Oracle does implement the OOPS concepts. The best example is the Property Classes. We can categorise the properties by setting the visual attributes and then attach the property classes for the objects. OOPS supports the concepts of objects and classes and we can consider the property classes as classes and the items as objects.


Q19) What is the difference between candidate key, unique key and primary key?

A19) Candidate keys are the columns in the table that could be primary keys and the primary key is the key that has been selected to identify the rows. Unique key is also useful for identifying the distinct rows in the table.


Q20) What is concurrency?

A20) Concurrency is allowing simultaneous access of same data by different users. Locks useful for accessing the database are

a) Exclusive — The exclusive lock is useful for locking the row when an insert , update or delete is being done. This lock should not be applied when we do only select from the row.

b) Share lock — We can do the table as Share_Lock as many share_locks can be put on the same resource.


Q21) What are the Previleges and Grants?

A21) Privileges are the right to execute a particular type of SQL statements. The grant has to be given by the owner to object.


Q22) What are Table Space, Data files, parameter file, control files?

A22) Table space: The table space is useful for storing the data in the database. When a database is created two table spaces are created.

a) System Table space : This data file stores all the tables related to the system and dba tables.

b) User Table Space: This data file stores all the user related tables.

We should have separate table spaces for storing the tables and indexes so that the access is fast.

Data files : Every oracle Data base has one or more physical data files. They store the data for the database. Every datafile is associated with only one database. Once the data file is created the size can not change. To increase the size of the database to store more data we have to add data file.

Parameter file:Parameter file is needed to start an instance. A parameter file contains the list of instance configuration parameters e.g. db_block_buffers = 500

db_name = ORA7

db_domain = u.s.acme lang

Control files : Control files record the physical structure of the data files and redo log files . They contain the DB name, name and locations of dbs , data files, redo log files and time stamp.


Q23) What is physical storage of the Data?

A23) The finest level of granularity of the data base are the blocks.

Extent : Extent is the number of specific number of contigious data blocks.

Segments : Set of Extents allocated for Extents. There are three types of segments

a) Data Segment : Non clustered tables has data segment data of every table is stored in cluster data segment.

b) Index Segment : Each index has index segment that stores data.

c) Rollback Segment : Temporarily store ‘undo’ information.


Q24) What is Normalisation ? Explain 1NF, 2NF, 3NF.

Normalization is a process of eliminating data redudancy.

1NF- No repeating groups.

2NF- Eliminates the functional dependency on the partial key field.

3NF- Eliminates the functional dependency on the non-key field.

Video Tutorial : Oracle SOA Suite

Video Tutorial : Oracle SOA Suite Installation Tutorial on Windows XP

“Hello World” program using Oracle SOA Suite
Creating a DB Connection in Jdeveloper
Creating Application Server and Integration Server Connections
Database Adapter Demo
Creating a Business Event in Oracle Applications
Capture Business Event in a ESB Process

Oracle – Technical FAQ – Part 2

Q6) What are the database triggers and stored procedures?
A6) Database triggers are procedures that are automatically executed as a result of insert in, update to or delete from table.
Database triggers have the values old and new to denote the old value in the table before it is deleted and the new indicated the new value that will be used. DT are useful for implementing complex business rules which can not be enforced using the integrity rules. We can have the trigger as before trigger or after trigger and at statement or row level.
e.g. operations insert, update, delete — 3
before, after 3 * 2 — a total of 6 combinations

At statement level (once for the trigger) or row level (for every execution) 6 * 2 — a total of 12.

Thus a total of 12 combinations are there and the restriction of usage of 12 triggers has been lifted from Oracle 7.3 onwards

Stored Procedures : Stored procedures are procedures that are stored in complied form in the database. The advantage of using the stored procedures is that many users can use the same procedure in compiled and ready to use format.


Q7) How many integrity rules are there and what are they?

A7) There are three integrity rules and they are as follows

a)Entity integrity Rule : The entity integrity rule enforces that the primary key can not be Null

b) Foreign key integrity rule : The FKIR denotes that the relationship between the foreign key and the primary key has to be enforced. When there is data in child tables the master tables can not be deleted.

c) Business integrity rules : The third integrity rule is about the complex business processes which can not be implemented by the above 2 rules.


Q.8.) What are the various Master and Detail relationships?

A.8.) The various Master and Detail relationship are

a) Non Isolated : The Master can not be deleted when a child is existing

b) Isolated : The master can be deleted when the child is existing

c) Cascading : The child gets deleted when the master is deleted.


Q9) What are the various Block Coordination properties ?.

A9) The various block coordination properties are

a) Immediate – Default setting. The detail records are shown when the Master Record are shown.

b) Differed with AutoQuery — Oracle forms defer fetching the detail records until the operator navigates to the detail block.

C) Deffered with no auto query — The operator must navigate to the detail block and explicitly executes a query.


Q10) What are the different Optimisation techniques ?

A10) The various optimisation techniques are

a) Execute plan : We can see the plan of the query and change it accordingly based on the indexes

b) Optimizer_hint : Set_item_property (‘DeptBlock’,OPTIMIZER_HINT,’FIRST_ROWS’);

Select /* + First Rows */ Deptno, Dname, Loc, Rowid from dept

where (Deptno > 25)

c) Optimize_sql : By setting the optimize_sql = No, Oracle Forms assign a single cursor for all SQL statements. This slow downs the processing because for everytime the SQL must be parsed whenever they are executed. f45run module = my_firstform useid = scott/tiger optimize_Tp = No


  more questions are here :

Part 3 is here :

Part 1 is here :