Wednesday, November 19, 2014

Credit Card Validation Oracle EBS R12

Introduction

Have you ever encountered the below validation failure for credit card in EBS R12.

  • Validation failed for the field - Credit Card
  • Unable to set up a Credit Card Bank Account for the Customer. Credit Card information on the order is not valid.
Below is the sample block you can use to validate your credit card in Oracle EBS.


We will use standard Oracle Package and Lookup to validate them

  • Package : iby_cc_validate
  • Oe Lookup : Credit Card

Sample Block

SET serveroutput ON;

DECLARE
  v_fill_chars VARCHAR (3)  := '*-#';
  v_cc_number  VARCHAR (50) := '1234567812345678';
  --'4111*1111 1111-1111#';
  v_api_version   NUMBER           := 1.0;
  v_init_msg_list VARCHAR2 (20000) := ' ';
  v_return_status VARCHAR2 (20000);
  v_msg_count     NUMBER;
  v_msg_data      VARCHAR2 (20000);
  v_clean_cc      VARCHAR (50);
  v_cc_type iby_cc_validate.cctype;
  v_cc_valid   BOOLEAN;
  v_expr_date  DATE        := SYSDATE ();
  v_error_flag VARCHAR2(1) := 'N';
  
BEGIN
  -- Procedure Call to strip unwanted and special characters
  -- checks that illegal characters were not found
  
  iby_cc_validate.stripcc (v_api_version ,
                           v_init_msg_list,
                           v_cc_number,
                           v_fill_chars,
                           v_return_status,
                           v_msg_count,
                           v_msg_data,
                           v_clean_cc );
  
  
  IF v_return_status = fnd_api.g_ret_sts_unexp_error THEN
    v_error_flag    := 'Y';
  END IF;
  
  
  -- Procedure Call to validate credit Card Type
  iby_cc_validate.getcctype (v_api_version ,
                             v_init_msg_list ,
                             v_clean_cc ,
                             v_return_status ,
                             v_msg_count ,
                             v_msg_data ,
                             v_cc_type );
  
  
  IF v_return_status = fnd_api.g_ret_sts_unexp_error AND v_cc_type = iby_cc_validate.c_invalidcc THEN
    dbms_output.put_line ('Credit card number type is invalid');
    v_error_flag := 'Y';
  ELSE
    dbms_output.put_line ('Credit card Type is Valid.');
  END IF;
  
  
    -- Procedure Call to validate credit card structure
  iby_cc_validate.validatecc (v_api_version ,
                              v_init_msg_list ,
                              v_clean_cc ,
                              v_expr_date ,
                              v_return_status ,
                              v_msg_count ,
                              v_msg_data ,
                              v_cc_valid );
                              
  IF v_cc_valid AND v_return_status != fnd_api.g_ret_sts_unexp_error THEN
    dbms_output.put_line ('Credit card structure is valid.');
  ELSE
    dbms_output.put_line ('Credit card structure is invalid or has expired.');
    v_error_flag := 'Y';
  END IF;
  
 -- Functional Call to returns Credit Card Validation Results using Luhn Algorithm
  IF iby_cc_validate.CheckCCDigits(v_clean_cc) = 0 THEN
    dbms_output.put_line ('Credit card  is valid.');
  ELSE
    dbms_output.put_line ('Credit card  is invalid or has expired.');
    v_error_flag := 'Y';
  END IF;
  
  
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line(SQLERRM);
END;



Tuesday, November 18, 2014

Query to find Credit Card Type in EBS R12

To Check the Valid Credit Card Type in R12. Execute the query below.


SELECT lookup_code,
      meaning,
      enabled_flag,
      start_date_active,
      end_date_active
FROM oe_lookups
WHERE lookup_type = 'CREDIT_CARD';


Sunday, November 9, 2014

Types of Invoice Class in AR Transaction

Invoice : A document that you create that lists amounts owed for the purchases of goods or services, any tax, freight charges and payment terms.

Credit Memo : A document that partially or fully reverses an original invoice.

Debit Memo : Debits that you assign to a customer to collect additional charges .

ChargeBack : A new debit item that you assign to your customer when closing an existing, outstanding debit item.

Deposit : A type of commitment whereby a customer agrees to deposit or prepay a sum of money for the future purchase of goods and services.

Guarantee : A contractual obligation to purchase a specified amount of goods or services over a predefined period of time.

Monday, October 27, 2014

Cash Flow Statements : Accounting Basics Day 9

Introduction

Cash flow statement provides considerable information about what is really happening in a business beyond that contained in either the income statement or the balance sheet. Analyzing this statement will quickly become obvious that the benefits of understanding the sources and uses of a company’s cash far outweigh the costs of undertaking some very straightforward analyses. In this chapter we are going to learn about the preparation of the cash flow statements.

What are Cash Flow Statements?

Cash flow statement, which shows inflows and outflows of the cash and cash equivalents. This statement is usually prepared by a company as a tool to know about the sources and uses of cash and cash equivalents of an enterprise over a period of time from various activities of an enterprise. It has gained substantial importance in the last decade because of its practical utility to the users of financial information.

Who Requires a Cash Flow Statement?


  • Executives require knowing if the cash generated by the company will be sufficient to fund their expansion strategy
  • Stockholders will be able to know if the firm is generating enough cash to pay dividends
  • Suppliers would be interested to know if their customers will be able to pay, on offering credit

Division of Cash Flow Statements

The cash flow statement is divided into three sections

  1. Operating activities - Converts the items reported on the income statement from the accrual basis of accounting to cash. Shows the results of cash inflows and outflows related to the fundamental operations of the basic line or lines of business in which the company engages. (Example: cash receipts from the sale of goods or services and cash outflows for purchasing inventory and paying rent and taxes.)
  2. Investing activities - Reports the purchase and sale of long-term investments and property, plant and equipment.
  3. Financing activities - Reports the issuance and repurchase of the company's own bonds and stock and the payment of dividends.

Detecting changes in cash with change in various accounts


  • When an asset (other than cash) increases, the Cash account decreases.
  • When an asset (other than cash) decreases, the Cash account increases.
  • When a liability increases, the Cash account increases.
  • When a liability decreases, the Cash account decreases.
  • When owner's equity increases, the Cash account increases.
  • When owner's equity decreases, the Cash account decreases.

The differences in a company's balance sheet accounts will provide much of the needed information. For example, if the statement of cash flows is for the year 2009, the balance sheet accounts at December 31, 2009 will be compared to the balance sheet accounts at December 31, 2008. The changes—or differences—in these account balances will likely be entered in one of the sections of the statement of cash flows.

Cash Provided From or Used By Operating Activities

Operating activities are the activities that constitute the primary or main activities of an enterprise, for example, for a company manufacturing garments, procurement of raw material, incurrence of manufacturing expenses, sale of garments, etc. These are the principal revenue producing activities (or the main activities) of the enterprise and other activities that are not investing or financing activities. By Operating Activities This section of the cash flow statement reports the company's net income and then converts it from the accrual basis to the cash basis by using the changes in the balances of current asset and current liability accounts, such as:

Accounts Receivable
Inventory
Supplies
Prepaid Insurance
Other Current Assets
Notes Payable (generally due within one year)
Accounts Payable
Wages Payable
Payroll Taxes Payable
Interest Payable
Income Taxes Payable
Unearned Revenues
Other Current Liabilities

In addition to using the changes in current assets and current liabilities, the operating activities section has adjustments for depreciation expense and for the gains and losses on the sale of long-term assets.

Cash Provided From or Used By Investing Activities

Investing activities relate to purchase and sale of long-term assets or fixed assets such as machinery, furniture, land and building, etc. Transactions related to long-term investment are also investing activities.
This section of the cash flow statement reports changes in the balances of long-term asset accounts, such as:

Long-term Investments
Land
Buildings
Equipment
Furniture & Fixtures
Vehicles
In short, investing activities involve the purchase and/or sale of long-term investments and property, plant, and equipment.

Cash provided from or used by financing activities

Financing activities relate to long-term funds or capital of an enterprise, e.g. cash proceeds from issue of equity shares, debentures, raising long-term bank loans, redemption of bank loan, etc
This section of the cash flow statement reports changes in balances of the long-term liability and stockholders' equity accounts, such as:

Notes Payable (generally due after one year) Bonds Payable
Deferred Income Taxes
Preferred Stock
Paid-in Capital in Excess of Par-Preferred Stock
Common Stock
Paid-in Capital in Excess of Par-Common Stock
Paid-in Capital from Treasury Stock
Retained Earnings
Treasury Stock

In short, financing activities involve the issuance and/or the repurchase of a company's own bonds or stock. Dividend payments are also reported in this section.
Where to enter the balance sheet changes
Below are the changes where balance sheet accounts should be entered on Statement of cash flows:


A change in this...is reported in this section
balance sheet categoryof the cash flow statement
Current Assets Operating Activities
Current Liabilities Operating Activities
Long-term Assets Investing Activities
Long-term Liabilities Financing Activities
Stockholders' EquityFinancing Activities

*This refers to current assets other than Cash.


--Coming Soon -- Preparation of Cash Flow Statement  --

Friday, October 17, 2014

Query to derive Qualifier Attribute and Context from Modifier Name

Query :

SELECT 
  c.list_line_no "Modifier Line No",
  b.name "Modifier Name",
  b.description  "Modifier Description",
  a.qualifier_context,
  a.qualifier_attribute,
  a.qualifier_attr_value
FROM qp_qualifiers_v a,
  qp_list_headers b,
  qp_list_lines c
WHERE a.list_header_id=b.list_header_id
and c.list_header_id=b.list_header_id
and c.list_line_id = a.list_line_id
and b.name = &Modifier_Name
ORDER BY a.creation_date DESC;



Wednesday, October 15, 2014

The Treatment given to Adjusting entries

Below is the chart to identify how adjusting entities are marked in accounting.

Tuesday, October 14, 2014

Adjusting Entries : Accounting Basics Day 8

Introduction 

Adjusting Entries are journal entries made at the end of the accounting period to allocate revenue and expenses to the period in which they actually are applicable. The Adjusting entries emphasize that while ascertaining the profitability, the revenues be considered as they are earned and not based on receipts, and the expenses be considered as they are incurred and not when they are paid Hence, many items need some adjustment while preparing the financial statements. In this chapter we’ll discuss all items which require adjustments and the way these are brought into the books of account and incorporated in the final accounts

What are adjusting entries?

Adjusting entries are accounting journal entries that convert a company's accounting records to the accrual basis of accounting(we have learnt about the accrual basis of accounting in the previous chapters) .An adjusting journal entry is typically made just prior to issuing a company's financial statements.
There are two scenarios where adjusting journal entries are needed before the financial statements are issued:


  • Nothing has been entered in the accounting records for certain expenses or revenues, but those expenses and/or revenues did occur and must be included in the current period's income statement and balance sheet.
  • Something has already been entered in the accounting records, but the amount needs to be divided up between two or more accounting periods.

Adjusting entries almost always involve a

  • Balance sheet account (Interest Payable, Prepaid Insurance, Accounts Receivable, etc.) and an Income statement account (Interest Expense, Insurance Expense, Service Revenues, etc.)

Types of Adjusting Entries

Some important and common items, which need to be adjusted at the time of preparing the final accounts, are discussed below.

  1. Closing stock
  2. Outstanding expenses
  3. Prepaid Expenses
  4. Accrued incomes
  5. Incomes received in advance
  6. Interest on capital
  7. Interest on drawings
  8. Interest on loan
  9. Interest on investment
  10. Depreciation
  11. Bad Debts
  12. Provision for bad and doubtful debts
  13. Provision for discount on debtors
  14. Provision for discount on creditors.

Note: All adjustments will be given outside the trial balance.

Closing Stock

The unsold goods in stock at the end of the accounting period is called as closing stock. This is to be valued at cost or market price whichever is lower.

Outstanding Expenses

Expenses which have been incurred but not yet paid during the accounting period for which the final accounts are being prepared are called as outstanding expenses.

Prepaid Expenses

Expenses which have been paid in advance are called as prepaid (Unexpired) expenses

Accrued Incomes or Outstanding Incomes

Income which has been earned but not received during the accounting period is called as accrued income.

Incomes Received in Advance

Income received during a particular accounting period for the work to be done in future period is called as income received in advance.

Interest on Capital

In order to see whether the business is really earning profit or not, it is desirable to charge interest on capital at a certain rate.

Interest on Drawings

Amount withdrawn by the owner for his personal use is called as drawings. When interest on capital is allowed, then interest on drawings is charged from the owner. Interest on drawings is an income for the business and will reduce the capital of the owner

Interest on Loan (Outstanding)

Borrowings from banks, financial institutions and outsiders for business are called loans. Amount payable towards interest on loan is an expense for the business

Interest on Investment:

Interest receivable on investments is an income for the business

Depreciation

Depreciation is the reduction in the value of fixed assets due to its use or obsolescence. Generally depreciation is charged at some percentage on the value of fixed asset.

Bad Debts

Debts which cannot be recovered are called bad debts. It is a loss for the business.

Provision for Bad and Doubtful Debts 

Every business suffers a percentage of bad debts over and above the debts definitely known as irrecoverable and written off as Bad (Bad debts written off). If Sundry debtors figure is to be shown correctly in the Balance sheet provision for bad and doubtful debts must be adjusted.
This Provision for bad and doubtful debts is generally provided at a certain percentage on Debtors, based on past experience. While preparing final accounts, the bad debts written off given in adjustment is first deducted from the Sundry debtors then on the balance amount (Sundry debtors – Bad debt written off) provision for bad and doubtful debts calculated
Provision for Discount on Debtors
To motivate the debtors to make prompt payments, cash discount may be allowed to them. After providing provision for bad and doubtful debts, the remaining debtors are called as good debtors. They may pay their dues in time and avail themselves of the cash discount Permissible. So a provision for discount on good debtors at a certain percentage may have to be created.
Provision for Discount on Creditors
Similar to cash discount allowed to debtors, the firm may have a chance to receive the cash discount from the creditors for prompt payment. Provision for discount on Creditors is calculated at a certain percentage on Sundry Creditors.

Treatment Given to Adjusting Entries



Click here to go back to Accounting Basics Day 7
Click here to go to Accounting Basics Day 9


Sunday, October 12, 2014

Balance sheet

A Balance sheet or statement of financial position is a summary of the financial balances of a sole proprietorship, a business partnership or a company. Assets, liabilities and ownership equity are listed as of a specific date, such as the end of its financial year. A balance sheet is often described as a snapshot of a company's financial condition. Of the four basic financial statements, the balance sheet is the only statement which applies to a single point in time of a business calendar year.

The purpose of preparing a Balance sheet is

  • To know the nature and value of assets of the business
  • To ascertain the total liabilities of the business.
  • To know the position of owner’s equity. 

The Balance Sheet consists of the assets and liabilities of a company, the format of the Balance Sheet is shown below

Format:

Balance Sheet As on xx-xx-20xx of Ms.XX


Assets

Assets represents everything which a business owns and has money value. In other words, asset includes possessions and properties of the business. Assets are classified as follows:

Tangible Assets:
Assets which have some physical existence are known as tangible assets. They can be seen, touched and felt, e.g. Plant and Machinery Tangible assets are classified into

  • Fixed assets: Assets which have long period of life and cannot be converted into cash in a short period are termed as fixed assets.
  • Current assets: Assets which can be converted into cash in the ordinary course of business and are held for a short period are known as current assets. For example, cash in hand, cash at bank, sundry debtors etc.
Intangible Assets
The assets which have no physical existence and cannot be seen or felt. They help to generate revenue in future, e.g. goodwill, patents, trademarks etc.

Liabilities

The amount which a business owes to others is liabilities. Credit balances of personal and real accounts together with the capital account are liabilities.

  • Long Term Liabilities : Liabilities which are repayable after a long period of time are known as Long Term Liabilities. For example, capital, long term loans etc.
  • Current Liabilities : Current liabilities are those which are repayable within a year. For example, creditors for goods purchased, short term loans etc.
  • Contingent liabilities : It is an anticipated liability which may or may not arise in future. For example, liability arising for bills discounted. Contingent liabilities will not appear in the balance sheet.
From the following Trial Balance of M/s. Arjun & Sons, prepare trading and profit and loss account for the year ending on 31st March 2008 and the balance sheet as on the date:






Saturday, October 11, 2014

Profit and Loss Account


After calculating the gross profit or gross loss the next step is to prepare the profit and loss account. To earn net profit a trader has to incur many expenses apart from those spent for purchases and manufacturing of goods. If such expenses are less than gross profit, the result will be net profit. When total of all these expenses are more than gross profit the result will be net loss.

The aim of profit and loss account is to ascertain the net profit earned or net loss suffered during a particular period.

The Format of Profit and Loss Account is shown below


Items appearing in the debit side

Those expenses which are chargeable to the normal activities of the business are recorded in the debit side of profit and loss account.
They are termed as Indirect expenses.

  • Office and Administrative Expenses: Expenses incurred for the functioning of an office are office and administrative expenses – office salaries, office rent, office lighting, printing and stationery, postages, telephone charges etc.
  • Repairs and Maintenance Expenses: These expenses relates to the maintenance of assets - repairs and renewals, depreciation etc.
  • Financial Expenses: Expenses incurred on borrowings –Interest paid on loan
  • Selling and Distribution Expenses: All expenses relating to sales and distribution of goods - advertising, travelling expenses, salesmen salary, commission paid to salesmen, discount allowed, repacking charges etc.

Items appearing in the credit side

Besides the gross profit, other gains and incomes of the business are shown on the credit side. The following are some of the incomes and gains.

  • Interest received on investment
  • Interest received on fixed deposits.
  • Discount earned.
  • Commission earned.
  • Rent Received

Example


Let us now prepare Trading and Profit Loss Account for the year ending 31st March 2002
from the books of Mr. Sree Ram


Balancing of Income statement

The difference between the two sides of profit and loss account indicates either net profit or net loss. If the total on the credit side is more the difference is called net profit. On the other hand if the total of debit side is more the difference represents net loss. The net profit or net loss is transferred to capital account.

Closing Entries

Profit and loss account should be closed by transferring the net profit or net loss to capital account.

If net profit
Profit and Loss A/c.............DrXXX
To Capital A/c (Net profit transferred to capital A/c)XXX
If net loss
Capital A/c....................Dr.XXX
To Profit and loss A/c (Net loss transferred to Capital A/c)XXX

Friday, October 10, 2014

Trading Account


Trading means buying and selling. The trading account shows the result of buying and selling of goods.
At the end of each year, it is necessary to ascertain the net profit or net loss. For this purpose, it is first necessary to know the gross profit or gross loss. The trading account is prepared to ascertain this. The difference between the selling price and the cost price of the goods is the gross earning of the business concern. Such gross earning is called as gross profit. However, when the selling price is less than the cost of goods purchased, the result is gross loss.
Let us now look at the format of the trading account

We’ll now see the explanation for each entity in the trading account.

Items appearing in the debit side of trading account

  1. Opening stock: Stock on hand at the beginning of the year is termed as opening stock. The closing stock of the previous accounting year is treated as the opening stock of the current accounting year. In the case of new business, there will not be any opening stock.
  2. Purchases: Goods Purchased during the year are included in Purchases, this includes both cash and credit purchases of goods. All the Purchases which have been purchased and returned back to the dealers due to some defects or excess are called as purchase returns. Purchase returns must be deducted from the total purchases to get net purchases.
  3. Direct Expenses: Expenses which are incurred from the stage of purchase to the stage of making the goods in saleable condition are termed as direct expenses. Some of the direct expenses are:



  • Wages: It is the amount paid to workers for the amount of labor they do for the organization.
  • Carriage or carriage inwards: It means the transportation charges paid to bring the goods from the place of purchase to the place of business.
  • Octroi Duty: Amount paid to bring the goods within the municipal limits.
  • Customs duty, dock dues, clearing charges, import duty etc.: These expenses are paid to the Government on the goods imported.
  • Other expenses: Fuel, power, lighting charges, oil, grease, waste related to production and packing expenses.

Items appearing in the credit side

Sales: This includes both cash and credit sale made during the year. Net sales are derived by deducting sales return from the total sales.
Closing stock: Closing stock is the value of goods which remain in the hands of the trader at the end of the year. It does not appear in the trial balance. It appears outside the trial balance. (As it appears outside the trial balance, first it will be recorded in the credit side of the trading account and then shown in the assets side of the balance sheet)

Let us prepare a Trading Account from the following information of a trader.

Total purchases made during the year 2003 Rs.2,00,000.

Total sales made during the year 2003 Rs.2, 50,000


ParticularsRsParticularsRs
To Purchases2,00,000By sales2,50,000
To Gross Profit c/d50,000
2,50,0002,50,000

Let us also try to go through another example and try to learn about the Trading account in detail
The following example the information given for the year ending 31st March 2002
Opening stock Rs. 1, 70,000
Purchases return Rs. 10,000
Sales Rs.2, 50,000
Wages Rs. 50,000
Sales return Rs. 20,000
Purchases Rs. 1, 00,000
Carriage inward Rs. 20,000
Closing stock Rs. 1, 60,000


Click here to go back to Accounting Basics Day 7


Thursday, October 9, 2014

Final Accounts : Accounting Basics Day 7

Introduction

The Stakeholders of a company will be interested in knowing whether the business has resulted in profit or loss and what the financial position of the company is at a given period. The company can ascertain these by preparing the final accounts. The final accounts are prepared at the end of the year from the trial balance. In this chapter we are going to learn in detail about the preparation of Final accounts

What are Final Accounts?

The businessman is interested in knowing whether the business has resulted in profit or loss and what the financial position of the business is at a given period. In short, he wants to know the profitability and the financial soundness of the business. The trader can ascertain these by preparing the final accounts. The final accounts are prepared at the end of the year from the trial balance. Hence the trial balance is said to be the connecting link between the ledger accounts and the final accounts.
The final Accounts generally include two statements known as balance sheet and income statement which are required for external reporting and also for internal needs of the management like planning, decision-making and control.



Trading and Profit and Loss Accounts (Income statement):

The profit and loss account is the accounting report which summarizes the revenues and expenses and ascertains the profit/loss for a specified accounting period. It also represents the changes in the owner’s equity between two successive periods

Balance Sheet:

The purpose of balance sheet is to show its resources and obligations for acquiring its resources i.e., assets and liabilities. According to American Institute of Public Accountants, balance sheet is “a tabular statement of summary of balances (debits and credits) carried forward after an actual and constructive closing of books of accounts and kept according to principles of Financial Statements of a Company accounting”. Balance sheet is the statement prepared on a particular date and shows classified properties and assets on the right hand side and obligations or liabilities on the left hand side

Assets

Real accounts and Personal accounts (which we have learnt about in the previous chapters) are capable of being called assets. Any element (account) that is capable of being liquidated (that is capable of being converted to cash by giving it away) indicates an asset. Machinery, Furniture, Cash, etc are real accounts that can be called assets. Debtors represent the persons and organizations who owe to the organization. They would clear their dues by paying out either in cash or in some other form. Thus Debtors get liquidated and as such can be called assets.

Liabilities

All elements representing liabilities are Personal accounts. An element that is capable of being cleared by paying out indicates a liability.
Creditors represent the persons and organization to whom the organization owes. The organization would clear its due by paying them either in cash or in some other form. Thus creditors are cleared by paying out and as such can be called liabilities.

How to enter the entries into Balance sheet and Income statements?

Before learning to enter the accounts into the Income Statement let us first distinguish the entries and learn to enter them into the financial statements.
As we know that the ledger accounts maintained within an organizational accounting system are classified into three as Personal, Real and Nominal.

Nominal Accounts

Nominal accounts are related to expenses, losses, incomes and gains. Since ascertaining profits or losses involves dealing with incomes, gains, expenses and losses we can conclude that all the nominal accounts together would give us the information relating to the profits or losses made by the organization.

Real Accounts

Real accounts are related to tangible aspects. In general we can identify that all asset accounts are real accounts.

Personal Accounts

Personal accounts are related to persons and organizations. These are persons/organization which owe the organization or to whom the organization owes. In effect they either form creditors (liabilities) or debtors (assets).

Since all the nominal accounts have been dealt with in deriving the information relating to profits and we are left with only the real and personal accounts which represent either assets or liabilities we can conclude that all the real and personal accounts together give us the information relating to the position of the organization.






Wednesday, October 8, 2014

ORA-06530: Reference to uninitialized composite

CREATE OR REPLACE TYPE XX_QP_PRICE_MOD_OBJ_TYPE
IS
  OBJECT
  (
    modifier_name        VARCHAR2 (240),
    modifier_description VARCHAR2 (240),
    adjustment_amount    NUMBER );

CREATE OR REPLACE TYPE xx_qp_price_mod_tbl_type IS VARRAY (1000) OF XX_QP_PRICE_MOD_OBJ_TYPE ;


SET SERVEROUTPUT ON
DECLARE
  v_mod_tbl xx_qp_price_mod_tbl_type := xx_qp_price_mod_tbl_type();
  i         NUMBER                           :=1;
  V_MOD_IDX NUMBER                           := 1;
BEGIN
  LOOP
    v_mod_tbl.extend();
    v_mod_tbl(v_mod_idx).adjustment_amount := 100;
    v_mod_tbl(v_mod_idx).modifier_name     := 200;
    v_mod_idx                              := v_mod_idx + 1;
    EXIT
  WHEN i=5;
    i  := i + 1;
  END LOOP;
EXCEPTION
WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;


Output :
anonymous block completed
ORA-06530: Reference to uninitialized composite




Solution:

SET SERVEROUTPUT ON
DECLARE
  v_mod_tbl xx_qp_price_mod_tbl_type := xx_qp_price_mod_tbl_type();
  i         NUMBER                           :=1;
  V_MOD_IDX NUMBER                           := 1;
BEGIN
  LOOP
    v_mod_tbl.extend();
    v_mod_tbl(v_mod_idx).adjustment_amount := 100;
    v_mod_tbl(v_mod_idx).modifier_name     := 200;
    v_mod_tbl(v_mod_tbl.last) := XX_QP_PRICE_MOD_OBJ_TYPE(NULL,NULL,NULL);
    v_mod_idx                              := v_mod_idx + 1;
    EXIT
  WHEN i=5;
    i  := i + 1;
  END LOOP;
EXCEPTION
WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

Profit or Loss on sale of asset


Some times, a business may dispose an asset when it is worn out. In that case, it is advisable to find the profit or loss on sale of asset. This is done by comparing the selling price with the book value of the asset.

Book value = Cost Price - Total Depreciation provided till the date of sale,

If the selling price is more than the book value, then it is Profit on Sale. If the selling price is less than the book value, it is Loss on Sale.

Let Us now solve a problem to find out the profit or Loss on sale of machinery. Ram manufacturing company purchased on 1st April 2007, Machinery for Rs.2, 00,000. After having used it for three years it was sold for Rs. 160,000. Depreciation is to be provided every year at the rate of 10% per annum on the fixed instalment method. Books are closed on 31st March every year.

Book value on the date of sale is Rs. 140,000 As book value is less than selling price the difference is Profit.
= 160,000-140,000
Profit on sale of machinery = Rs. 20,000.

Illustration:

Srinivas & Co. purchased machinery on 1st April 2007 for Rs.75, 000. After having used it for three years it was sold for Rs.35, 000. Depreciation is to be provided every year at the rate of 10% per annum on Diminishing balance method. Accounts are closed on 31st March every year. Find out the profit or loss on sale of machinery.


Book value on the date of sale = Rs.54,675.
As book value is greater than selling price the difference is loss.
= 54,675 – 35,000
Loss on sale of Machinery = Rs.19, 675.





Tuesday, October 7, 2014

Recording Depreciation

Depreciation is directly charged against the asset by debiting Depreciation account and crediting the Asset account. Depreciation account is closed by transferring to Profit and Loss account at the end of the year. The entries will be as under:

1) For the amount of depreciation to be provided at the end of the Year:
                        Depreciation A/c….. Dr.
                        To Asset A/c.

2) For transferring the amount of depreciation at the end of the year.
                        Profit and Loss A/c….. Dr.
                        To Depreciation A/c.

Asset Account will be shown at cost less depreciation i.e., written down value at the end of the year in the Balance sheet

Now Let Us look at an example and learn about passing journal entries and preparing fixed asset account and depreciation account for three years.

Ram & sons purchased a fixed asset on 1.4.2006 for Rs.3, 00, 000. Depreciation is to be provided at 10% annually according to the Straight line method. The books are closed on 31st March every year.

Amount of Depreciation = 300,000 * (10/100)
                                          = 30,000




In the Books of Rajesh & Co.
Journal Entries
Date Particulars L.F.  Debit  Credit
1-Apr-06 Fixed asset A/c Dr 3,00,000
To Bank A/c 3,00,000
31-Mar-07 Depreciation A/c Dr 3,00,000
To Fixed asset A/c 3,00,000
(Depreciation provided)
31-Mar-07 Profit & Loss A/c Dr 3,00,000
To Depreciation A/c 3,00,000
(Depreciation transferred
to Profit & Loss A/c)
31-Mar-08 Depreciation A/c Dr 3,00,000
To Fixed asset A/c 3,00,000
(Depreciation provided)
31-Mar-08 Profit & Loss A/c Dr 3,00,000
To Depreciation A/c 3,00,000
(Depreciation transferred
to Profit & Loss account)
31-Mar-09 Depreciation A/c Dr 3,00,000
To Fixed asset A/c 3,00,000
(Depreciation provided)
31-Mar-09 Profits & Loss A/c Dr
To Depreciation A/c
(Depreciation transferred
to Profit & Loss A/c)



Ledger Account
Fixed Asset Account
Date.  Particulars  Rs.  Date  Particulars  Rs
1-Apr-06 To Bank A/c 3,00,000 31-Mar-07 By Depreciation A/c 30,000
By Balance c/d 2,70,000
3,00,000 3,00,000
1-Apr-07 To Balance b/d 2,70,000 31-Mar-08 By Depreciation A/c 30,000
By Balance c/d 2,40,000
2,70,000 270,000
1-Apr-08 To Balance b/d 2,40,000 31-Mar-09 By Depreciation A/c 30,000
By Balance c/d 2,10,000
2,40,000 2,40,000
By Depreciation A/c 30,000
1-Apr-09 To Balance b/d 2,10,000 By Balance c/d 2,10,000

Depreciation A/c
Date.  Particulars  Rs.  Date  Particulars  Rs
31-Mar-07 To Fixed Asset A/c 30000 31-Mar-07 By Profit & Loss A/c 30,000
30000 30000
31-Mar-08 To Fixed Asset A/c 30000 31-Mar-08 By Profit & Loss A/c 30000
30000 30000
31-Mar-09 To Fixed Asset A/c 30000 31-Mar-08 By Profit & Loss A/c 30000
30000 30000




ORACLE NOSQL DATABASE, 12CR1 VERSION 3.0, ENTERPRISE EDITION

Architecture 

Oracle NoSQL Database is built upon the proven Oracle Berkeley DB Java Edition high-availability storage engine, which is in widespread use in enterprises across industries. In addition to that it adds a layer of services for use in distributed environments. The resulting solution provides distributed, highly available key/value storage that is well suited to large-volume, latency-sensitive applications.

High Availability and No-Single Point of Failure 

Oracle NoSQL Database provides single-master, multi-replica database replication. Transactional data is delivered to all replica nodes with flexible durability policies per transaction.
In the event the master replica node fails, a PAXOS-based automated fail-over election process minimizes downtime. This allows for scalability, fail-over, and hot-standby.

Transparent Load Balancing 


Oracle NoSQL Database Driver partitions the data in real time and evenly distributes it across the storage nodes. It is network topology and latency-aware, routing read and write operations to the most appropriate storage node in order to optimize load distribution and performance.


JSON Data Format

Oracle NoSQL Database has support for the Avro data serialization, which provides an extremely compact, schema-based binary data format. Avro allows you to define a schema (using JSON) for the data contained in a record's value and it also supports schema evolution.

Configurable Smart Topology

System administrators indicate how much capacity is available on a given storage node, allowing more capable storage nodes to host multiple replication nodes. Once the system knows about the capacity for the storage nodes in a configuration, it automatically allocates replication nodes intelligently. This results better load balancing for the system, better use of system resources and minimizing system impact in the event of storage node failure. Smart Topology also supports Data Centres, ensuring that a full set of replicas is initially allocated to each data centre.

Elastic Configuration

Oracle NoSQL Database includes a topology planning feature, with which an administrator can now modify the configuration of a NoSQL database, while the database is still online. This allows the administrator to:
Increase Data Distribution: by increasing number of shards in the cluster, which increases write throughput.
Increase Replication Factor: by assigning additional replication nodes to each shard, which increases read throughput and system availability.
Rebalance Data Store: by modifying the capacity of a storage node(s), the system can be rebalanced, re-allocating replication nodes to the available storage nodes, as appropriate. The topology rebalance command allows the administrator to move replication nodes and/or partitions from over utilized nodes onto underutilized storage nodes or vice versa.

Easy Administration and Enhanced System Monitoring

Oracle NoSQL Database provides an administration service, which can be accessed either from a web console or a command-line interface (CLI). This service supports core functionality such as the ability to configure, start, stop and monitor a storage node, without requiring manual effort with configuration files, shell scripts, or explicit database operations.

In addition it also allows Java Management Extensions (JMX) or Simple Network Management Protocol (SNMP) agents to be available for monitoring. This allows management clients to poll information about the status, performance metrics and operational parameters of the storage node and its managed services.

Table Data Model

Release 3.0 introduces tabular data structure, which simplifies application data modelling by leveraging existing schema design core concepts. Table model is layered on top of the distributed key-value structure, inheriting all its advantages and simplifying application design even further by enabling seamless integration with familiar SQL-based applications.

Secondary Index

Primary key only based indexing limits number of low latency access paths. Sometime application needs a few non-primary-key based paths to support the whole solution for the real-time system. Being able to define secondary index on any value field dramatically improves performance for queries.

APIs

Oracle NoSQL Database includes Java and C APIs. These simple APIs allow the application developer to perform CRUD operations on Oracle NoSQL Database. These libraries also include Avro support, so that developers can serialize key-value records and de-serialize key-value records interchangeably between C and Java applications.

Large Object Support

Stream based APIs are provided in the product to read and write Large Objects (LOBs) such as audio and video files, without having to materialize the value in its entirety in memory. This permits low latency operations across mixed workloads of objects of varying sizes.

Apache Hadoop Integration

KVAvroInputFormat and KVInputFormat classes are available to read data from Oracle NoSQL Database natively into Hadoop Map/Reduce jobs. One use for this class is to read NoSQL Database records into Oracle Loader for Hadoop.

Oracle Database Integration via External Tables (EE Only)

Support for external table allows fetching Oracle NoSQL data from Oracle database using SQL statements such as Select, Select Count(*) etc. Once NoSQL data is exposed through external tables, one can access the data via standard JDBC drivers and/or visualize it through enterprise Business Intelligence tools.

Integration with Other Oracle Products (EE Only)

Oracle Event Processing (OEP) provides read access to Oracle NoSQL Database via the NoSQL Database cartridge. Once the cartridge is configured, CQL queries can be used to query the data.

Oracle Semantic Graph has developed a Jena Adapter for Oracle NoSQL Database to store large volumes of RDF data (as triplets/quadruplets). This adapter enables fast access to graph data stored in Oracle NoSQL Database via SPARQL queries.

An integration with Oracle Coherence has been provided that allows Oracle NoSQL Database to be used as a cache for Oracle Coherence applications, also allowing applications to directly access cached data from Oracle NoSQL Database.

High Performance 

Network topology and latency aware Oracle NoSQL Database Driver working in conjunction with highly scalable, fault tolerant, high throughput storage engine enables a more granular distribution of resources and processing, which reduces the incidence of hot spots and provides greater performance on commodity based hardware.

Online Rolling Upgrade 

Upgrade and patching is an important part of any software support cycle. Oracle NoSQL
Database provides facilities to perform a rolling upgrade, allowing a system administrator to upgrade all of the nodes in the NoSQL Database cluster while the database continues to remain online and available to clients.

Multi Zone Deployment

Oracle NoSQL Database supports the definition of multiple zones from within the topology deployment planner. It leverages the definition of these zones internally to intelligently allocate replication of processes and data, ensuring optimal reliability during hardware, network & power related failure scenarios.

There are two types of Zones: Primary zones contain nodes that can be served as masters or replicas and are typically connected by fast interconnects. Secondary zones contain nodes which can only be served as replicas. Secondary zones can be used to provide low latency read access to data at a distant location, or to offload read-only workloads, like analytics, report generation, and data exchange for improved workload management.

Enterprise Security (EE)

OS-independent, cluster-wide password-based user authentication and Oracle Wallet integration, enables greater protection from unauthorized access to sensitive data. Additionally, session-level Secure Sockets Layer (SSL) encryption and network port restrictions deliver greater protection from network intrusion.

Commercial Grade Software and Support

Oracle NoSQL Database overcomes a significant limitation faced by many enterprises considering the implementation of NoSQL databases—the need for full supportability. Because it is a commercial product fully supported by Oracle, this solution gives organizations the confidence and limited risk they need to deploy Oracle NoSQL Database in the production environments they depend on to manage their business-critical data.

For more information visit oracle.com

Monday, October 6, 2014

Buckets in Oracle Pricing Modifiers

Buckets


Pricing buckets control how discounts and other benefits are calculated across phases. Grouping discounts and benefits into buckets helps determine the net selling price across all pricing phases.
Null Bucket.

Modifiers that are assigned to a Null bucket are applied last and always adjust from the list price. Order level modifiers must be in the Null bucket. The pricing engine uses the following steps when calculating the selling price for Null buckets:

  1. Calculates percent discounts using the list price.
  2. Sums all bucket modifier values to create a bucket subtotal.
  3. Applies the subtotal after the last numbered bucket.

Suppose that the following buckets are set up, each with a different adjustment:

  • Null bucket: a 50% discount is assigned to the Null bucket.
  • Bucket 1: a 10% discount is assigned.
  • Bucket 2: a 10% surcharge is assigned.

The buckets are used to calculate the price for an SP ATO Model with a list price of $55. The following table shows how the final price is calculated:


The below are the steps to assign modifiers to different buckets in Oracle Apps R12.

Update Profile Option

Goto:  System Administrator -> Profile Options

Name: QP: Allow Buckets For Manual Modifiers
Value: Yes



Update Pricing Modifier Setup to add buckets

Modifier Name: One-Time Discount

Bucket: 4



Once the buckets are assigned, the modifiers are applied on the priority basis.





Friday, October 3, 2014

Written Down Value Method of Depreciation

This method is also called as Diminishing Balance Method. In this method depreciation is charged at a fixed percentage each year on the reducing balance (i.e., cost less depreciation) of asset. The amount of depreciation goes on decreasing every year.
For example, If the asset is purchased for Rs.2, 00,000 and depreciation is to be charged at 10% p.a. on reducing balance method, then

Depreciation for the 1st year      = 10% on Rs.2, 00,000, i.e., Rs.20,000
Depreciation for the 2nd year    = 10% on Rs.1, 80,000 (Rs.2, 00,000 –– Rs.20, 000)
                                                       = Rs. 18,000
Depreciation for the 3rd year    = 10% on Rs.162, 000 (Rs.180, 000 - Rs.18, 000)
                                                      = Rs.16, 200 and so on.

Merits

In earlier years the amount of depreciation is more and the amount of repairs and renewals is less whereas in later years the amount of depreciation is less and the amount of repairs and renewals is more therefore the total charge (i.e., depreciation plus repairs and renewals) remains almost uniform year after year, since,
This method is recognized by the Income Tax authorities
It is a logical method as the depreciation is calculated on the diminished balance every year.

Demerits

It is very difficult to determine the rate by which the value of asset could be written down to zero.

Click here to go back to Depreciation: Day 6


Thursday, October 2, 2014

Straight Line Method for Calculating Depreciation

This method is also called as Fixed Instalment Method .In this method the same amount of depreciation is charged every year throughout the life of the asset. The amount and rate of depreciation is calculated as under.

Amount of depreciation = ( Total cost – Scrap value ) / Estimated Life

Rate of depreciation = ( Amount of Depreciation / Original Cost ) * 100

Let us now look at a simple example to calculate amount of depreciation and Rate of depreciation, given a company purchased Machinery for Rs.2, 00,000. Its installation costs amounted to Rs.10, 000. Its estimated life is 5 years and the scrap value is Rs.10, 000. Calculate the amount and rate of depreciation
Total cost = Purchase Price + Installation Charges
= Rs.2, 00,000 + Rs.10, 000
= Rs. 2, 10,000

Amount of depreciation = ( Total cost – Scrap value ) / Estimated Life

                                          = ( Rs.2, 10,000 – Rs.10, 000 ) / 5
                                          = Rs. 40,000

Rate of depreciation = ( Amount of depreciation/Original cost ) * 100

                                   = ( Rs 40,000/Rs. 210,000 ) * 100
                                   = 19.04%

Under straight line method, for each of the five years, the amount of depreciation to be charged will be Rs.40, 000

Merits:


  • It is very simple and easy to understand.
  • The amount and rate of depreciation is easy to calculate
  • Under this method, the book value of the asset becomes zero or equal to its scrap value at the expiry of its useful life.

Demerits

The amount of depreciation is same in all the years, although the usefulness of the machine to the business is more in the initial years than in the later years

Click here to go back to Depreciation: Day 6