Oracle Timecard (OTL) Automation


Recently i have seen many requests for OTL timecard automations in Oracle. One of the reasons is the financial downturn in the martket.

Many companies have announced furlough for employees- meaning the offices and manufacturing units will be shutdonw for a specified amount of time and it will be mandatory for employees to either take that time off from their vacation time, flex time, may be borrow it from next year or simply be unpaid for that time period. Whatever be the case, it saves company a lot of money that it was otherwise obligated to pay in vacation time or paid days. Now because its mandatory, it has to be in the system for the payroll to pick it up.

If the organization uses time entry system – like oracle – to enter vacation/flex time, and processes payroll then following needs to happen in order for a succesfull furlough automation.

– Employees time must be entered on the online timecard.

– The timecard must be approved by manager or Auto-approval process.

– This OTL timecard must be transferred to Batch Element Entries for Payroll.

To automate this process we will use HXC Timecard APIs provided by Oracle. These APIs help us in creating a timecard for a day, week, and also attach elements/ projects to the timecard. Also, the APIs submit the timecard with a workflow process type so it can either be picked for AUTO processing or Manual approval.

Before we go into the details, we have to see how a typical timecard is built. For the sake of simplicity, we will consider Monthly Paid (exempt) employees.

A timecard is a combination of DAYS. Each DAY will be one Row in a table. For Every day that you need to enter time (e.g. Saturday Sunday will not need a time entry being a holiday) you need a DETAIL type row also. And, for each DETAIL record that has to go into an element or project for costing or payroll purpose, we need an ATTRIBUTE also.

These are nothing but the TIME BUILDING BLOCKS. To make things simple, lets go and see these records:

select * from hxc_time_building_blocks

TYPE: MEASURE ( Number of hours to be put in that particular day) or RANGE (For period like week or day).
SCOPE: TIMECARD, DAY, DETAIL or APPLICATION_PERIOD

Now, if you see a typical timecard, this is how it looks (Use the hierarchical Query below):

TIMECARD (12/1/2008  – 12/7/2008) TYPE: RANGE
|_  DAY (12/1/2008 – 12/1/2008) TYPE: RANGE
|_  DETAIL (12/1/2008):  8 HRS   (may have attributes)  TYPE:MEASURE
|_  DAY (12/2/2008 – 12/2/2008)
|_  DETAIL (12/2/2008):  8 HRS
|_  DAY (12/3/2008 – 12/3/2008)
|_  DETAIL (12/3/2008):  8 HRS
|_  DAY (12/4/2008 – 12/4/2008)
|_  DETAIL (12/4/2008):  8 HRS
|_  DAY (12/5/2008 – 12/5/2008)
|_  DETAIL (12/5/2008):  8 HRS
|_  DAY (12/6/2008 – 12/6/2008)
|_  DAY (12/7/2008 – 12/7/2008)

SELECT htbb.time_building_block_id, htbb.TYPE, htbb.measure, htbb.unit_of_measure, htbb.start_time, htbb.stop_time,htbb.parent_building_block_id, ‘N’ parent_is_new, htbb.SCOPE,htbb.object_version_number, htbb.approval_status,htbb.resource_id, htbb.resource_type, htbb.approval_style_id,htbb.date_from, htbb.date_to, htbb.comment_text,htbb.parent_building_block_ovn, ‘N’ NEW, ‘N’ changed,htbb.application_set_id, htbb.translation_display_key
FROM apps.hxc_time_building_blocks htbb START WITH ( htbb.time_building_block_id in (6848088) AND htbb.object_version_number in (1 ) )CONNECT BY PRIOR htbb.time_building_block_id = htbb. parent_building_block_id AND PRIOR htbb.object_version_number = htbb.parent_building_block_ovn ORDER BY htbb.time_building_block_id ASC

NOW Over to APIs….

hxc_timestore_deposit.create_timecard_bb
— Create a Timecard building block (only timecard, no days, or details)
— Here are the parameters it takes
(
p_start_time => to_date(’01-DEC-2008 00:00:00′,’DD-MON-YYYY HH24:MI:SS’) ,
p_stop_time =>  to_date(’07-DEC-2008 23:59:59′,’DD-MON-YYYY HH24:MI:SS’),
p_resource_id => emp.person_id ,
p_comment_text => ‘Automated TimeCard: DEC08’,
p_approval_style_id => 41, –This is your workflow approval style, default to AUTO APPROVE
p_app_blocks => l_tbl_timecard_info,
p_time_building_block_id => l_tc_bb_id –returns the id of  TC Building block
);

hxc_timestore_deposit.create_day_bb
— Creates a DAY building block (only DAY no details)
— Here are the parameters it takes
(
p_day => l_start_date,
p_parent_building_block_id => l_tc_bb_id,
p_comment_text => ‘Automated TimeCard: DEC08’,
p_app_blocks => l_tbl_timecard_info,
p_time_building_block_id => l_day_bb_id
);

hxc_timestore_deposit.create_detail_bb
— Creates a DETAIL building block, in next step we have to attach the attribute to this DETAIL
— Here are the parameters it takes
(
p_type => ‘MEASURE’,
p_measure => 8, –Number of Hours
p_parent_building_block_id => l_day_bb_id,
p_comment_text => ‘Automated TimeCard: DEC08’,
p_app_blocks => l_tbl_timecard_info,
p_app_attributes => l_tbl_attributes_info,
p_time_building_block_id => l_detail_bb_st_id
);
hxc_timestore_deposit.create_attribute (
p_building_block_id=> l_detail_bb_st_id,
p_attribute_name=> ‘Dummy Element Context’,
p_attribute_value=> ‘ELEMENT – 60110’, –This is the Accrual PTO Element we want to update through this API.
p_app_attributes=> l_tbl_attributes_info);

HXC_TIMESTORE_DEPOSIT.EXECUTE_DEPOSIT_PROCESS
–This is the Submission Call. This process will submit the timecard, days and details with attributes. Timecard will stay in SUBMITTED State until approved via Manual or AUTO Approve process.
(
p_validate => FALSE,
p_app_blocks => l_tbl_timecard_info,
p_app_attributes => l_tbl_attributes_info,
p_messages => l_tbl_messages,
p_mode => ‘SUBMIT’,
p_deposit_process => ‘OTL Deposit Process’,
p_retrieval_process => ‘BEE Retrieval Process’,
p_timecard_id => l_new_timecard_id,
p_timecard_ovn => l_new_timecard_ovn
);

Hope this article helped you in understanding the basics of the Time Entry APIs. Please note that this is  the initial and basic knowledge. You will need some more knowledge – like DELETE timecards API for rollback in case of any issues. UPDATE timecard APIs etc for a full fledge capability on OTL timecard automation.

Oracle 10g Cost Based Optimizer


Hello Friends, here i like to give some brief on Cost based optimizer in Oracle, i am not covering Rule based optimizer as it is not much in use as well not recommended. plz share ur feedback or if u like to contribute more on this topic,

Oracle 10g Cost Based Optimizer

Whenever you execute a SQL statement, a component of the database known as the optimizer must decide how best to access the data operated on by that statement. To figure out the optimal execution path for a statement, the optimizers consider the following:

  • The syntax you’ve specified for the statement
  • Any conditions that the data must satisfy (the WHERE clauses)
  • The database tables your statement will need to access
  • All possible indexes that can be used in retrieving data from the table
  • The Oracle RDBMS version
  • The current optimizer mode
  • SQL statement hints
  • All available object statistics (generated via the ANALYZE command)
  • The physical table location (distributed SQL)
  • INIT.ORA settings (parallel query, async I/O, etc.)

Understanding the Cost-Based Optimizer

The information required by the cost-based optimizer is available once a table has been analyzed via the ANALYZE command, or via the DBMS_STATS facility. Plz note The ANALYZE command and the DBMS_STATS functions collect statistics about tables, clusters, and indexes, and store those statistics in the data dictionary.

The RDBMS kernel defaults to using the cost-based optimizer under a number of situations, including the following:

  • OPTIMIZER_MODE = CHOOSE has been specified in the INIT.ORA file, and statistics exist for at least one table involved in the statement
  • An ALTER SESSION SET OPTIMIZER_MODE = CHOOSE command has been executed, and statistics exist for at least one table involved in the statement
  • An ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS (or ALL_ROWS) command has been executed, and statistics exist for at least one table involved in the statement
  • A statement uses the FIRST_ROWS or ALL_ROWS hint (e.g., SELECT /*+ FIRST_ROWS */. . .)

ANALYZE command

The way that you analyze your tables can have a dramatic effect on your SQL performance. If your DBA forgets to analyze tables or indexes after a table re-build, the impact on performance can be devastating. If your DBA analyzes each weekend, a new threshold may be reached and Oracle may change its execution plan. The new plan will more often than not be an improvement, but will occasionally be worse.

If you do want to analyze frequently, use DBMS_STATS.EXPORT_SCHEMA_STATS to back up the existing statistics prior to re-analyzing. This gives you the ability to revert back to the previous statistics if things screw up. When you analyze, you can have Oracle look at all rows in a table (ANALYZE COMPUTE) or at a sampling of rows (ANALYZE ESTIMATE). Typically, When a table is analyzed using ANALYZE, all associated indexes are analyzed as well. If an index is subsequently dropped and recreated, it must be re-analyzed. Be aware that the procedures DBMS_STATS.GATHER_SCHEMA_STATS and GATHER_TABLE_STATS analyze only tables by default, not their indexes. When using those procedures, you must specify the CASCADE=>TRUE option for indexes to be analyzed as well.

Following are some sample ANALYZE statements:

ANALYZE TABLE EMP ESTIMATE STATISTICS SAMPLE 5 PERCENT FOR ALL INDEXED COLUMNS;

ANALYZE INDEX EMP_NDX1 ESTIMATE STATISTICS SAMPLE 5 PERCENT FOR ALL INDEXED COLUMNS; ANALYZE TABLE EMP COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;

If you analyze a table by mistake, you can delete the statistics. For example:

ANALYZE TABLE EMP DELETE STATISTICS;

Analyzing can take an excessive amount of time if you use the COMPUTE option on large objects. We find that on almost every occasion, ANALYZE ESTIMATE 5 PERCENT on a large table forces the optimizer make the same decision as ANALYZE COMPUTE.

Inner workings of the cost-based optimizer

The cost-based optimizer is flexible and can adapt to its environment. This adaptation is possible only once the necessary underlying object statistics have been refreshed (re-analyzed). What is constant is the method by which the cost-based optimizer calculates each possible execution plan and evaluates its cost (efficiency).

The cost-based optimizer’s functionality can be (loosely) broken into the following steps:

1.       Parse the SQL (check syntax, object privileges, etc.).

2.       Generate a list of all potential execution plans.

3.       Calculate (estimate) the cost of each execution plan using all available object statistics.

4.       Select the execution plan with the lowest cost.

The cost-based optimizer will be used only if at least one table within a SQL statement has statistics (table statistics for unanalyzed tables are estimated). If no statistics are available for any table involved in the SQL, the RDBMS will resort to the rule-based optimizer, unless the cost-based optimizer is forced via statement-level HINTS or by an optimizer goal of ALL_ROWS or FIRST_ROWS.

To understand how the cost-based optimizer works and, ultimately, how to exploit it, we need to understand how it thinks.

Primary key and/or UNIQUE index equality —A UNIQUE index’s selectivity is recognized as 100%. No other indexed access method is more precise. For this reason, a unique index is always used when available.

Non-UNIQUE index equality —For non-UNIQUE indexes, index selectivity is calculated. The cost-based optimizer makes the assumption that the table (and subsequent indexes) have uniform data spread unless you use the FOR ALL INDEXED COLUMNS option of the ANALYZE. That option will make the cost-based optimizer aware of how the data in the indexed columns is skewed.

Range evaluation —For index range execution plans, selectivity is evaluated. This evaluation is based on a column’s most recent high-value and low-value statistics. Again, the cost-based optimizer makes the assumption that the table (and subsequent indexes) have uniform data spread unless you use the FOR ALL INDEXED COLUMNS option when analyzing the table.

Range evaluation over bind variables—For index range execution plans, selectivity is guessed. Prior to Oracle9i, because bind variable values are not available at parse time (values are passed to the cursor after the execution plan has been decided), the optimizer cannot make decisions based on bind variable values. The optimizer assumes a rule of thumb of 25% selectivity for unbounded bind variable ranges (e.g., WHERE dept_no = :b1) and 50% selectivity for bounded ranges (WHERE dept_no > :b1 AND dept_no < :b2). Beginning with Oracle9i, the cost-based optimizer obtains bind variable values prior to determining an execution plan.

System resource usage —By default, the cost-based optimizer assumes that you are the only person accessing the database. Oracle9i gives you the ability to store information about system resource usage, and can make much better informed decisions based on workload (read up on the DBMS_STATS.GATHER_SYSTEM_STATS package).

Current statistics are important —The cost-based optimizer can make poor execution plan choices when a table has been analyzed but its indexes have not been, or when indexes have been analyzed but not the tables.

Oracle Payables Useful Tables


Hello Friends , here is some of quite commonly used AP (Payables) tables and their usage. There are many other tables also in AP but here i am putting only few commonly used tables. for other table if needed we can dig furthur. Let go through below article and let me know if it useful.

1- AP_INVOICES_ALL

2- AP_INVOICE_DISTRIBUTIONS_ALL

3- AP_PAYMENT_SCHEDULES_ALL

4- AP_HOLDS_ALL

5- AP_AE_LINES_ALL

6- AP_AE_HEADERS_ALL

AP_INVOICES_ALL

AP_INVOICES_ALL contains records for invoices you enter. There is one row for each invoice you enter. An invoice can have one or more invoice distribution lines. An invoice can also have one or more scheduled payments. An invoice of type EXPENSE REPORT must relate to a row in AP_EXPENSE_REPORT_HEADERS_ALL unless the record has been purged from AP_EXPENSE_REPORT_HEADERS_ALL. Your Oracle Payables application uses the INTEREST type invoice for interest that itcalculates on invoices that are overdue. Your Oracle Payables application links the interest invoice to the original invoice by inserting the INVOICE_ID in the AP_INVOICE_RELATIONSHIPS table.

AP_INVOICE_DISTRIBUTIONS_ALL

AP_INVOICE_DISTRIBUTIONS_ALL holds the distribution line information that you enter for invoices. There is a row for each invoice distribution. A distribution line must be associated with an invoice. An invoice can have multiple distribution lines. Your Oracle Payables application automatically creates rows in this table when:

1) you choose a distribution set at the invoice level

2) you import expense reports

3) you match an invoice to a purchase order or receipt; ituses information from the matched purchase order or receipt

4) you import invoices via the Open Interface Import process

5) you select to automatically calculate tax

6) you select to automatically do withholding.

Each invoice distribution line has its own accounting date. When you account for an invoice, your OraclePayables application creates accounting events, accounting entry headers and accounting entry lines for those distribution lines that have accounting dates included in the selected accounting date range for the Payables Accounting Process.The accounting entries can then be transferred over to General Ledger by running the Transfer to General Ledger process which creates journal entries. Values for POSTED_FLAG may be Y for accounted distributions or N for distributions that have not been accounted. Values for ACCRUAL_POSTED_FLAG may be Y if distribution has been accounted and system is set up for accrual basis accounting or N if either distribution has not been accounted or accrual basis accounting is not used. Values for CASH_POSTED_FLAG may be Y if distribution has been accounted and system is set up for cash basis accounting, N if either distribution has not been accounted or system is not set up for cash basis accounting or P if distribution has been partially accounted in the cash set of books. The MATCH_STATUS_FLAG indicates the approval status for the distribution. Values for the MATCH_STATUS_FLAG can be null or N for invoice distributions that Approval has not tested or T for distributions that have been tested or A for distributions that have been tested and approved. Invoice distributions may be interfaced over/from Oracle Assets or Oracle Projects. Your Oracle Payables application sets the ASSETS_ADDITION_FLAG to U for distributions not tested by Oracle Assets; Oracle Assets then adjusts this flag after it tests a distribution for assignment as an asset.
To avoid the same invoice distribution being interfaced to both Oracle Projects and Oracle Assets, you must interface any project–related invoice distribution to Oracle Projects before you can interface it to Oracle Assets. If the project–related invoice distribution is charged to a capital project in Oracle Projects, Oracle Projects sets the ASSET_ADDITION_FLAG to P when the PA_ADDITION_FLAG is set to Y, Z or T. Oracle Assets only picks up invoice distributions with the ASSET_ADDITION_FLAG set to U and if project–related, with the PA_ADDITION_FLAG set to Y, Z, or T. PA_ADDITION_FLAG tracks the status of project–related supplier invoice distribution lines and expense report distribution lines.
For supplier invoice distributions entered via Oracle Payables, the PA_ADDITION_FLAG is set to N if the distribution is project–related, otherwise it is set to E and it is updated by Oracle Projects when the distribution is processed by the Oracle Projects Interface Supplier Invoice process. Oracle Projects sets the PA_ADDITION_FLAG to Y or Z after the item is successfully processed, or may be set to a rejection code if the line is rejected during transfer to Oracle Projects; see QuickCodes listing for all the errors. You must correct the rejection reason an try to retransfer the line. For supplier invoice adjustment lines interfaced from Oracle Projects to Oracle Payables (which must net to zero with another line), the value for the PA_ADDITION_FLAG is set to T. For expense report distributions interfaced from Oracle Projects to Oracle Payables via Invoice Import, this value is set to N. This row is never picked up by the Interface Supplier Invoices process based on the AP_INVOICES.INVOICE_TYPE_LOOKUP_CODE = EXPENSEREPORT. For expense report adjustment lines interfaced from Oracle Projects to Oracle Payables which net to zero with another line, thisvalue is set to T. Both lines are associated with the original invoice by the Oracle Projects Interface Expense Reports to AP process. Values for the ENCUMBERED_FLAG are as follows:

– Y indicates aregular distribution that has been successfully encumbered by Payables;

– W indicates a regular distribution that has been encumbered in advisory mode even though insufficient funds existed;

– H indicates a regular distribution that has not been encumbered because it was put on hold;

– Nor null indicates a regular line that has not been encumbered because it has not been looked at yet;

– D is the same as Y for a reversal distribution line;

– X is the same as W for a reversal distribution line;

– P is the same as H for a reversal distribution line;

– R indicates a line to be ignored by encumbrance and approval code because neither the original nor the reversal distributions were looked at and they offset each other so, they can be ignored.

AP_PAYMENT_SCHEDULES_ALL

AP_PAYMENT_SCHEDULES_ALL contains information about scheduled payments for an invoice. You need one row for each time you intend to make a payment on an invoice. Your Oracle Payables application uses this information to determine when to make payments on an invoice and how much to pay in an automatic payment batch. Values for HOLD_FLAG may be ’Y’ to place a hold on the scheduled payment, or ’N’ not to do so. Values for PAYMENT_STATUS_FLAG may be ’Y’ for fully paid payment schedules, ’N’ for unpaid scheduled payments, or ’P’ for partially paid scheduled payments. For converted records, enter a value for AMOUNT_REMAINING.

AP_HOLDS_ALL

AP_HOLDS_ALL contains information about holds that you or your Oracle Payables application place on an invoice. For non–matching holds, there is one row for each hold placed on an invoice. For matching holds, there is one row for each hold placed on an invoice–shipment match. An invoice may have one or more corresponding rows in this table. Your Oracle Payables application does not pay invoices that have one or more unreleased holds recorded in this table. This table holds information referenced by the Invoice Holds window. In the strictest sense, AP_HOLDS_ALL has no primary key. It is possible for your Oracle Payables application to place a certain type of hold on an invoice, then release it, then place another hold of the same type (if data changes before each submission of Approval), which would result in a duplicate primary key. But for practical purposes, the primary key is a concatenation of INVOICE_ID, LINE_LOCATION_ID,and HOLD_LOOKUP_CODE.

AP_AE_LINES_ALL

An accounting entry line is an entity containing a proper accounting entry with debits or credits both in transaction currency as well as functional currency along with an account and other reference information pointing to the transaction data that originated the accounting entry line. An accounting entry line is grouped with other accounting entry lines for a specific accounting entry header. Any such group of accounting entry lines should result in balanced entries in the functional currency.

AP_AE_HEADERS_ALL

An accounting entry header is an entity grouping all accounting entry lines created for a given accounting event and a particular set of books. An accounting entry header can either be transferred over to GL or not at all. That is, either all its accounting entry lines are transferred or none at all. The transferred to GL status is marked in the GL_TRANSFER_FLAG. Possible values for GL_TRANSFER_FLAG are Y, N, or E. Y indicates that the accounting entry header has been transferred to GL. N indicates that the accounting entry header has not been transferred to GL due to 2 possible reasons: either the transfer process has not run or it has run but the accounting entry had an accounting error on it. E indicates that an error was encountered during the transfer to GL process.

Thanks – Shivmohan Purohit

Distinction between configuration, personalization, and extensibility.


Distinction between configuration, personalization, and extensibility.

Configuration provides setup and administrative choices using the native features of the product. Some configuration examples include:

  • Profile Options
  • User-defined fields (Flexfield)
  • Function Security Setup
  • Data Security Setup
  • and a lot more….

Personalization enables you to declaratively tailor the UI look-and-feel, layout or visibility of page content to suit a business need or a user preference. Some personalization examples include:

  • Tailor the order in which table columns are displayed.
  • Tailor a query result.
  • Tailor the color scheme of the UI.
  • Folder Forms
  • Forms Personalization
  • Oracle Application Framework (OAF)

Extensibility is about extending the functionality of an application beyond what can be done through personalization. Some extensibility examples include:

  • Add new functional flows
  • Extend or override existing business logic
  • Using Oracle Forms Developer, Oracle JDeveloper and Oracle Workflow

Oracle General Ledger and Budgeting


What is planning budget ?

The plan for the future expenses is planning budget. It is a paper work. There is no funds requirement. It does not require journals. There are no restrictions for estimating of funds. It is a budget through which you cannot exercise budgetary control. But u can compare your actual with budgets through inquiry window.

What is Consolidation in GL?

It Consolidates the Subsidiaries & Parent financial information. At a point of time we can check the Performance of the Companies overall Performance. When we have multiple setup and combine primary and secondary books getting the financial reports is called consolidation We will use consolidation reports at the time of period end closing Consolidation is the Process of combining the Financial results of different companies typically combining subsidiary accounting information into a parent company.The main purpose of consolidation is for reporting purpose. Consolidation is the period-end process of combing the financials of separate subsidiary with the pay rent company to from single ,combine statement of final result consolidation is one of the reporting tool.consolidation may map the subsidiary set of books to parent set of books, even if the set of books values are different using segment rule,accounting rule It is the process of combining the financial results of multiple companies into one financial statement the company may have different sobs in different countries,so we will collect all the financialtransactions(sob) in to one set of book ,that is parent sob then the company easely findout all the finacial year endingresullts in their functional curency,that purpose multiple companies are using consolidation I think consolidation can be done with two ways that is FSG, GCSÂ for FSG it can be done when both parent SOB and Subsidary SOB have Currency, Calender, COA, same then we go for FSG.In Global consolidation System currency, coa, calender are different in parent sob and subsidary sob we have to go for GCS.I am Saikrishna actaully I am pursuing my oracle financials in hyd i just completed one of my module GL institute.

How to import data to General Ledger by Feeder System ?
There are three ways:

1- You can upload Journal Entries using ADI (Application Desktop Integrator) functionality. ADI is used to interface spreadsheet uploads with Oracle applications data. ADI allows users take advantage of many of the data-entry shortcuts of a spreadsheet, such as copying and pasting cells, dragging and dropping ranges of cells and using formulas to calculate journal line amounts. ADI validates the data entered against the accounts, security rules and reference information that are defined in the General Ledger (GL).

2- Using Journal Import, we can bring the data from the feeder system; to import the Transactions from External Feeder Systems following are the steps-1. Populate the data from External system to GL_Interface table (GL_INTERFACE table acts like a bridge between the external system and the Oracle General Ledger Base tables where journals are stored). 2. Run the Journal Import process to import the date to base tables (Errors in Journal Import are listed in Execution Report)

3. From any sub module run individual module transfer to GL program Ex: Payables transfer to General Ledger

After creating Journal Source how do we approve to the specific Set of Books ?

To approve journals from specific source, while creating the source ‘Require Journal Approval’ check box should be enabled. To approve all the journals that come from different sources In the Set Of Books window under ‘Journaling’ tab ‘journal approval’ should be enabled.

While creating combination of accounts what is the use of PRESERVED check box ?

If we check preserve for a account code combination then even if you disable this account segment value, the inherited (parent. child) segment values will not be disabled. If it is unchecked all the related segment values will be disabled. Preserved check box will help you to retain the original character of the General Ledger code combination, even after u change the nature of the segments during maintenance of the GLCC.

What is the MD form 50 ?

MD50 is a Functional design document, designed by the functional consultant. After interacting with the client, functional consultant will prepare this document. In this we will compare the present business process and client requirement. It is mainly used for Application Extensions and Interface functional design.

What are the journal entries in Procure to Pay Cycle ?

When the Material Received at the Gate-

Receiving Accrual A/C — DrMaterial AP Accrual A/C — Cr

When the Material delivered to The Inv Org Material

A/c Dr Purchase Price Variance A/c Dr Receiving A/c Cr

When the Invoice Is enterd into the Payable with Matching Of PO Material

Ap Accrual A/c Dr Invoice Price Variance A/c Dr AP Liability A/c Cr

Material Receipt

Receiving Accural Dr AP Accural Cr

On completion on inspection and accepting of goods

Inventory Dr Receiving Accural Cr The Above given 2 entries are in PO module

On entering Invoice in AP

AP Accural Dr Accounts Payable Cr

On Making payment

Accounts Payable Dr   Cash/Bank Cr 1. On Entry of PO ________ No accounting impact On Material

On Payment

Accounts Payable Dr’ Cash/Bank Cr

When we raise the Purchase Order entry

Material in transit a/c Dr To AP Accrual A/c

Thanks – Shivmohan

Oracle developer Interview tips


It looks like the job market for Oracle is opening up. I’m seeing many of my friends find new jobs and I’m doing a lot of interviewing at work. I hope this is a trend that continues. In this entry, I share my method for interviewing Oracle resources and provide some sample interview questions and answers for developers.

Interviewing anyone can be difficult. Interviewing technical resources is very difficult. To me, probably the hardest thing is pinpointing exactly what you want this person to do. What, exactly, will this person be doing in their day-to-day job? It’s easy to say having a good job description makes it easier but in many cases, employers have a generic template when looking for someone.

First off, is there a difference between a developer and a programmer? I think there is. A programmer is a coder. I don’t mean that as a bad thing. Every project needs coders. A developer should be part analyst and part coder. A developer should be able to handle requirements gathering through implementation. Every project should have at least one good developer. In your interview, you should distinguish between the two.

Before you start to interview people, make sure you really know what you’re looking for. It’s not fair to the candidate to say you’re looking for forms experience and then spend all of your time on advanced back end programming. And don’t ask a backend coder the fine details of forms.

When I interview someone, I look for more than yes/no answers to my questions. In an interview, I expect a candidate to communicate with me. I’m looking for a comfort level. If the person says they don’t know a particular topic, that’s acceptable. If they fumble and make something up, that’s a problem to me. I don’t look for textbook definitions. I want to know they understand what I’m asking and what they’re answering. I also don’t believe in tricky interviews. What’s the point?

Regardless of the exact position, any Oracle resource, including a DBA, should know some basic things about SQL and PL/SQL. Some sample questions I ask are:

For Basic SQL:

  • How do you convert a date to a string? To_char. A bonus would be that they always include a format mask.
  • What is an aggregate function? I’m looking for “grouping”, sums or accounts, etc.
  • What is an interval? Specifies a period of time.
  • What is a nested subquery? A subquery in a where clause.
  • What is the dual table? A single row table provided by oracle for selecting values and expressions.

For Basic PL/SQL:

  • Describe the block structure of PLSQL. Declaration, Begin, exception, end.
  • What is an anonymous block? Unnamed PL/SQL block.
  • What is a PL/SQL collection? PL/SQL Table, Varray, PL/SQL Array, etc.
  • What is the difference between an explicit cursor and a select into. You might get something about performance but that’s a myth. An explicit cursor is just more typing. A cursor for loop would be used to return more than a single row.
  • Why would you choose to use a package versus straight procedures and functions? I look for maintenance, grouping logical functionality, dependency management, etc. I want to believe that they believe using packages is a “good thing”.

These are pretty basic questions. If I don’t get a warm fuzzy from these, and they are 100% answerable by anyone with some real experience, then the person goes no further.

So, where do you go after the basics? That really depends on what you’re looking for. If you are hiring a Java coder to work with your Oracle group or you’re looking for a DBA, you might end the coding part here. You would expect a DBA to know more but I would move on to administrative questions. You might also stop here if you’re looking for a junior developer to train.

If you’re looking for a senior PL/SQL coder type, you will want to go deeper. You need to remember to ask specific questions about a person’s background and forms developers will have different experience than a back-end developer. But either should have a good grasp of advanced topics.

The hard part is that there are so many advanced topics; it’s hard to know what to ask. You need to tailor it for your environment. If you use a lot of AQ, ask AQ questions. If you’re very OO, ask OO questions.

Here are some more advanced, but still generic questions:

For Advanced SQL:

  • What is the difference between an aggregate and an analytic function? I’m looking for them knowing that a sum aggregate (or any other aggregate function) will return one row for a group and a sum analytic will return one result for each row in the group. If they mention the “Window”, they get a bonus point. 😉
  • How do you create a hierarchical query? Connect by.
  • How would you generate XML from a query? The answer here is “A lot of different ways”. They should know that there are SQL functions: XMLELEMENT, XMLFOREST, etc and PL/SQL functions: DBMS_XMLGEN, DBMS_XMLQUERY, etc.
  • What do you need before implementing a member function? You need to create a type.
  • How do you tune a query? I’m looking for a discussion of autotrace and/or explain plan. Ask them what they’re looking for in a plan. This should not be a single sentence. Look for a comfort level.

For Somewhat Advanced PL/SQL:

  • What is the default value of a boolean? NULL. This is somewhat tricky but apparently there are languages that default boolean to false. A PL/SQL developer needs to know all variables default to NULL.
  • Why is using implicit conversions a poor programming practice? For dates, you must ASSUME that the default date format will always be the same (and it won’t be). In some cases, implicit conversion is slower. I want to feel like they don’t believe writing to_char or to_number is more work than it’s worth. BTW, this also applies to SQL.
  • How can you tell if an UPDATE updated no rows? SQL%NOTFOUND.
  • How can you tell if a SELECT returned no rows. NO_DATA_FOUND exception.
  • How do you run Native Dynamic SQL? Execute immediate.
  • What is an autonomous transaction? Identified by pragma autonomous. A child transaction separate from the parent that MUST be committed or rolled back.

At this point I usually ask the candidate to explain specific statements on the resume. If they say they tuned queries or improved performance, I say how? What did you do? What tools did you use?

That’s my interviewing method. I hope that helps you get the best people for your organization.

How IT Companies Got their Names


Apple Computers
It was the favorite fruit of founder Steve Jobs. He was three months late in filling a name for the business, and he threatened to call his company Apple Computers if the other colleagues didn’t suggest a better name by 5 o’clock
CISCO
It is not an acronym as popularly believed. It is short for San Francisco.
Compaq
This name was formed by using COMp, for computer, and PAQ to denote a small integral object.
Corel
The name was derived from the founder’s name Dr. Michael Cowpland. It stands for COwpland Research Laboratory.
Google
The name started as a joke boasting about the amount of information the search-engine would be able to search. It was originally named ‘Googol’, a word for the number represented by 1 followed by 100 zeros. After founders- Stanford graduate students Sergey Bin and Larry Page presented their project to an angel investor, they received a cheque made out to ‘Google’
Hotmail
Founder Jack Smith got the idea of accessing email via the web from a computer anywhere in the world. When Sabeer Bhatia came up with the business plan for the mail service, he tried all kinds of names ending in ‘mail’ and finally settled for hotmail as it included the letters ‘html’ – the programming language used to write web pages. It was initially referred to as HoTMaiL with selective uppercasing.
Hewlett Packard
Bill Hewlett and Dave Packard tossed a coin to decide whether the company they founded would be called Hewlett-Packard or Packard-Hewlett.
Intel
Bob Noyce and Gordon Moore wanted to name their new company ‘Moore Noyce’ but that was already trademarked by a hotel chain so they had to settle for an acronym of INTegrated ELectronics.
Lotus (Notes)
Mitch Kapor got the name for his company from ‘The Lotus Position’ of ‘Padmasana’. Kap0or used to be a teacher of Transcendental Meditation of Maharishi Mahesh Yogi.
Microsoft
Coined by Bill Gates to represent the company that was devoted to MICROcomputer SOFTware. Originally christened Micro-Soft, the ‘-’ was removed later on.
Motorola
Founder Paul Galvin came up with this name when his company started manufacturing radios for cars. The popular radio company at the time was called Victrola.
ORACLE
Larry Ellison and Bob Oats were working on a consulting project for the CIA(Central Intelligence Agency). The code name for the project was called Oracle(the CIA saw this as the system to give answers to all questions or something such). The project was designed to help use the newly written SQL code by IBM. The project eventually was terminated but Larry and Bob decided to finish what they started and bring it to the world. They kept the name Oracle and created the RDBMS engine. Later they kept the same name for the company.
Sony
It originated from the Latin word ‘sonus’ meaning sound, and ‘sonny’ a slang used by Americans to refer to a bright youngster.
SUN
Founded by 4 Stanford University buddies, SUN is the acronym for Stanford University Network. Andreas Bechtolsheim built a microcomputer; Vinod Khosla recruited him and Scott McNealy to manufacture computers based on it, and Bill Joy to develop a UNIX- based OS for the computer.
Yahoo!
The word was invented by Jonathan Swift and used in his book ‘Gulliver’s Travels’. It represents a person who is repulsive in appearance and action and is barely human. Yahoo! Founders Jerry Yang and David Filo selected the name because they considered themselves yahoos. Yahoo stands for Yet Another Hirarchy for Officius Oracle.