Oracle Cloud ERP career switch from Oracle EBS?
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’) ;
RETURN (ToDate – FromDate – (TotalSundays+TotalSaturdays)/7 -1) ;
Executing this function in a pl/sql block…
SQL> SET SERVEROUTPUT ON
2 BusinessDays NUMBER;
4 BusinessDays := TotWeekDays(’01-MAY-2001′,’31-MAY-2001′) ;
5 DBMS_OUTPUT.PUT_LINE(‘Total Business Days : ‘||TO_CHAR(BusinessDays) ) ;
Total Business Days : 23
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?
Q16) FND USER EXITS:-
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
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
Thanks — Shivmohan Purohit
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. Quite useful as FAQ and Interview Questions for General Ledger.
12. What is an Invoice?
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.
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.
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.
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.
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.
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 Installation Tutorial on Windows XP