Tag Archives: oracle

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

Aside

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 … Continue reading

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.

Aside

 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 … Continue reading

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
Aside

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 … Continue reading