Oracle Apps R12 Query to Find Payment against Invoice

SELECT b.segment1 vendor_number,

       b.vendor_name vendor_name,

       c.vendor_site_code,

       c.pay_group_lookup_code,

       a.invoice_num invoice_number,

       a.invoice_date,

       a.gl_date,

       d.due_date,

       a.invoice_currency_code,

       a.invoice_amount,

       a.amount_paid,

       a.pay_group_lookup_code,

       d.payment_priority,

       (SELECT MAX (check_date)

          FROM ap_checks_all aca, ap_invoice_payments_all aip

         WHERE aca.CHECK_ID = aip.CHECK_ID AND aip.invoice_id = a.invoice_id)

          "Last Payment Made on",

          a.cancelled_date

  FROM apps.ap_invoices_all a,

       apps.ap_suppliers b,

       apps.ap_supplier_sites_all c,

       apps.ap_payment_schedules_all d,

       apps.ap_invoice_payments_all ap,

       ap_checks_all ac

WHERE     a.vendor_id = b.vendor_id

       AND a.vendor_site_id = c.vendor_site_id

       AND b.vendor_id = c.vendor_id

       AND a.invoice_id = d.invoice_id

       AND ap.invoice_id = a.invoice_id

       AND ac.CHECK_ID = ap.CHECK_ID

       and ac.STATUS_LOOKUP_CODE <> 'VOIDED'

       AND a.org_id = 89

       and a.invoice_id= 1234

       AND a.pay_group_lookup_code IN ('DISTRIBUTOR')

       AND ac.check_date BETWEEN TO_DATE ('01-Apr-2014', 'DD-MON-YYYY') AND TO_DATE ('30-Jun-2014 23:59:59', 'DD-MON-YYYY HH24:MI:SS')

Read More

Query to find the parent request id recursively

SELECT request_id,
  description
FROM fnd_concurrent_requests
WHERE NVL (request_type, 'X')        = 'M'
  CONNECT BY PRIOR parent_request_id = request_id
  START WITH request_id              = &RequestID;

Read More

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;

Read More

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';

Read More

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.

Read More

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 category	of the cash flow statement
Current Assets	Operating Activities
Current Liabilities	Operating Activities
Long-term Assets	Investing Activities
Long-term Liabilities	Financing Activities
Stockholders' Equity	Financing Activities

*This refers to current assets other than Cash.


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

Read More

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;

Read More

The Treatment given to Adjusting entries

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

Click here to go back to Accounting Basics Day 8

Read More

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

Read More

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:

Click here to go back to Accounting Basics Day 7

Read More