Tuesday, April 16, 2013

Put Concurrent Requests on HOLD when we need to bounce the CONC Managers due to some maintenance activity

To put the jobs on hold –
update applsys.fnd_concurrent_requests set hold_flag = 'Y',last_update_date = sysdate,last_updated_by = -1 where phase_code = 'P';
 To release job after downtime –
update applsys.fnd_concurrent_requests a set a.hold_flag = 'N' , last_update_date = sysdate, last_updated_by = -1 where phase_code = 'P'
and a.hold_flag = 'Y'
and last_updated_by = -1
and last_update_date > sysdate-1;

Terminate one concurrent Request:

update applsys.fnd_concurrent_requests
set phase_code = 'C',
status_code = 'X',
actual_completion_date = sysdate
where request_id = '&R';

Wednesday, April 10, 2013

Parent Child relationship for Accounting Flexfield

 SELECT gcc.code_combination_id
      ,gcc.segment1 Balancing_Segment
  FROM gl_code_combinations gcc,
       fnd_flex_value_children_v ffvc,
       fnd_id_flex_segments fifs
 WHERE 1=1
   AND fifs.flex_value_set_id = ffvc.flex_value_set_id
   AND fifs.id_flex_code='GL#'
   AND ffvc.parent_flex_value = NVL(:P_Parent_Flex_value, ffvc.parent_flex_value)
   AND ffvc.flex_value = gcc.segment
   AND fifs.application_column_name = 'SEGMENT'||:P_Segment_Num;

some more imp tables are gl_balances, gl_code_combinations and fnd_flex_norm hierarchy  fnd_flex_values, fnd_flex_value_sets to report on flex values

Accounting Flow

Tuesday, April 9, 2013

ACCOUNTING Debit /Credit

Balance Sheet ,Income Stmt n Cash Flow Basic logic

There are four main financial statements. They are: (1) balance sheets; (2) income statements; (3) cash flow statements; and (4) statements of shareholders’ equity. Balance sheets show what a company owns and what it owes at a fixed point in time. Income statements show how much money a company made and spent over a period of time. Cash flow statements show the exchange of money between a company and the outside world also over a period of time. The fourth financial statement, called a “statement of shareholders’ equity,” shows changes in the interests of the company’s shareholders over time.
Let’s look at each of the first three financial statements in more detail.

Balance Sheets

A balance sheet provides detailed information about a company’s assets,liabilities and shareholders’ equity.
Assets are things that a company owns that have value. This typically means they can either be sold or used by the company to make products or provide services that can be sold. Assets include physical property, such as plants, trucks, equipment and inventory. It also includes things that can’t be touched but nevertheless exist and have value, such as trademarks and patents. And cash itself is an asset. So are investments a company makes.
Liabilities are amounts of money that a company owes to others. This can include all kinds of obligations, like money borrowed from a bank to launch a new product, rent for use of a building, money owed to suppliers for materials, payroll a company owes to its employees, environmental cleanup costs, or taxes owed to the government. Liabilities also include obligations to provide goods or services to customers in the future.
Shareholders’ equity is sometimes called capital or net worth. It’s the money that would be left if a company sold all of its assets and paid off all of its liabilities. This leftover money belongs to the shareholders, or the owners, of the company.

The following formula summarizes what a balance sheet shows:
A company's assets have to equal, or "balance," the sum of its liabilities and shareholders' equity.
A company’s balance sheet is set up like the basic accounting equation shown above. On the left side of the balance sheet, companies list their assets. On the right side, they list their liabilities and shareholders’ equity. Sometimes balance sheets show assets at the top, followed by liabilities, with shareholders’ equity at the bottom.
Assets are generally listed based on how quickly they will be converted into cash. Current assets are things a company expects to convert to cash within one year. A good example is inventory. Most companies expect to sell their inventory for cash within one year. Noncurrent assets are things a company does not expect to convert to cash within one year or that would take longer than one year to sell. Noncurrent assets include fixed assets.Fixed assets are those assets used to operate the business but that are not available for sale, such as trucks, office furniture and other property.
Liabilities are generally listed based on their due dates. Liabilities are said to be either current or long-termCurrent liabilities are obligations a company expects to pay off within the year. Long-term liabilities are obligations due more than one year away.
Shareholders’ equity is the amount owners invested in the company’s stock plus or minus the company’s earnings or losses since inception. Sometimes companies distribute earnings, instead of retaining them. These distributions are called dividends.
A balance sheet shows a snapshot of a company’s assets, liabilities and shareholders’ equity at the end of the reporting period. It does not show the flows into and out of the accounts during the period.

Income Statements

An income statement is a report that shows how much revenue a company earned over a specific time period (usually for a year or some portion of a year). An income statement also shows the costs and expenses associated with earning that revenue. The literal “bottom line” of the statement usually shows the company’s net earnings or losses. This tells you how much the company earned or lost over the period.
Income statements also report earnings per share (or “EPS”). This calculation tells you how much money shareholders would receive if the company decided to distribute all of the net earnings for the period. (Companies almost never distribute all of their earnings. Usually they reinvest them in the business.)
To understand how income statements are set up, think of them as a set of stairs. You start at the top with the total amount of sales made during the accounting period. Then you go down, one step at a time. At each step, you make a deduction for certain costs or other operating expenses associated with earning the revenue. At the bottom of the stairs, after deducting all of the expenses, you learn how much the company actually earned or lost during the accounting period. People often call this “the bottom line.”
At the top of the income statement is the total amount of money brought in from sales of products or services. This top line is often referred to as gross revenues or sales. It’s called “gross” because expenses have not been deducted from it yet. So the number is “gross” or unrefined.
The next line is money the company doesn’t expect to collect on certain sales. This could be due, for example, to sales discounts or merchandise returns.
When you subtract the returns and allowances from the gross revenues, you arrive at the company’s net revenues. It’s called “net” because, if you can imagine a net, these revenues are left in the net after the deductions for returns and allowances have come out.
Moving down the stairs from the net revenue line, there are several lines that represent various kinds of operating expenses. Although these lines can be reported in various orders, the next line after net revenues typically shows the costs of the sales. This number tells you the amount of money the company spent to produce the goods or services it sold during the accounting period.
The next line subtracts the costs of sales from the net revenues to arrive at a subtotal called “gross profit” or sometimes “gross margin.” It’s considered “gross” because there are certain expenses that haven’t been deducted from it yet.
The next section deals with operating expenses. These are expenses that go toward supporting a company’s operations for a given period – for example, salaries of administrative personnel and costs of researching new products. Marketing expenses are another example. Operating expenses are different from “costs of sales,” which were deducted above, because operating expenses cannot be linked directly to the production of the products or services being sold.
Depreciation is also deducted from gross profit. Depreciation takes into account the wear and tear on some assets, such as machinery, tools and furniture, which are used over the long term. Companies spread the cost of these assets over the periods they are used. This process of spreading these costs is called depreciation or amortization. The “charge” for using these assets during the period is a fraction of the original cost of the assets.
After all operating expenses are deducted from gross profit, you arrive at operating profit before interest and income tax expenses. This is often called “income from operations.”
Next companies must account for interest income and interest expense. Interest income is the money companies make from keeping their cash in interest-bearing savings accounts, money market funds and the like. On the other hand, interest expense is the money companies paid in interest for money they borrow. Some income statements show interest income and interest expense separately. Some income statements combine the two numbers. The interest income and expense are then added or subtracted from the operating profits to arrive at operating profit before income tax.
Finally, income tax is deducted and you arrive at the bottom line: net profit or net losses. (Net profit is also called net income or net earnings.) This tells you how much the company actually earned or lost during the accounting period. Did the company make a profit or did it lose money?

Earnings Per Share or EPS

Most income statements include a calculation of earnings per share or EPS. This calculation tells you how much money shareholders would receive for each share of stock they own if the company distributed all of its net income for the period.
To calculate EPS, you take the total net income and divide it by the number of outstanding shares of the company.

Cash Flow Statements

Cash flow statements report a company’s inflows and outflows of cash. This is important because a company needs to have enough cash on hand to pay its expenses and purchase assets. While an income statement can tell you whether a company made a profit, a cash flow statement can tell you whether the company generated cash.
A cash flow statement shows changes over time rather than absolute dollar amounts at a point in time. It uses and reorders the information from a company’s balance sheet and income statement.
The bottom line of the cash flow statement shows the net increase or decrease in cash for the period. Generally, cash flow statements are divided into three main parts. Each part reviews the cash flow from one of three types of activities: (1) operating activities; (2) investing activities; and (3) financing activities.

Operating Activities

The first part of a cash flow statement analyzes a company’s cash flow from net income or losses. For most companies, this section of the cash flow statement reconciles the net income (as shown on the income statement) to the actual cash the company received from or used in its operating activities. To do this, it adjusts net income for any non-cash items (such as adding back depreciation expenses) and adjusts for any cash that was used or provided by other operating assets and liabilities.

Investing Activities

The second part of a cash flow statement shows the cash flow from all investing activities, which generally include purchases or sales of long-term assets, such as property, plant and equipment, as well as investment securities. If a company buys a piece of machinery, the cash flow statement would reflect this activity as a cash outflow from investing activities because it used cash. If the company decided to sell off some investments from an investment portfolio, the proceeds from the sales would show up as a cash inflow from investing activities because it provided cash.

Financing Activities

The third part of a cash flow statement shows the cash flow from all financing activities. Typical sources of cash flow include cash raised by selling stocks and bonds or borrowing from banks. Likewise, paying back a bank loan would show up as a use of cash flow.

Monday, April 8, 2013

Important Reports on Receivables

Transaction Reports

Transaction Register
Incomplete Invoices Report
Past Due Invoice Report
Invoice Exception Report
Aging - 7 Buckets Report
Receipt and Adjustment Report

Receipt Register

Unapplied and Unresolved Receipts Register
Applied Receipts Register
Miscellaneous Receipts Register
Adjustment Register
Adjustment Approval Report
Customer and Billing Reports

Account Status Report

Customer Listing Detail and Summary Reports
Credit Hold Report
Billing History
Billing and Receipt History
Customer Open Balance Letter
Accounting and Reconciliation Reports

Unposted Items Report

AR Reconciliation Report
AR to GL Reconciliation Report

Few Important Query

Get Calendar Information

select calendar_type,
from fa_calendar_periods
where calendar_type='&calendertype'
order by 2;

Get Depreciation Period :

select book_type_code,
from fa_deprn_periods
where book_type_code='&book'
order by 3

How to check AP ,GL, AR periods are closed 

FROM gl.gl_sets_of_books sob
WHERE sob.set_of_books_id = a.set_of_books_id) "SOB_Name",
a.period_name "Period_Name", a.period_num "Period_Num",
a.gl_status "GL_Status", b.po_status "PO_Status",
c.ap_status "AP_Status", d.ar_status "AR_Status",
e.fa_status "FA_Status"
FROM (SELECT period_name, period_num,
DECODE (closing_status,
'O', 'Open',
'C', 'Closed',
'F', 'Future',
'N', 'Never',
) gl_status,
FROM gl.gl_period_statuses
WHERE application_id = 101
AND UPPER (period_name) = UPPER ('&period_name')
AND set_of_books_id ='&sob' ) a,
(SELECT period_name,
DECODE (closing_status,
'O', 'Open',
'C', 'Closed',
'F', 'Future',
'N', 'Never',
) po_status,set_of_books_id
FROM gl.gl_period_statuses
WHERE application_id = 201
AND UPPER (period_name) = UPPER ('&period_name')
AND set_of_books_id ='&sob' ) b,
(SELECT period_name,
DECODE (closing_status,
'O', 'Open',
'C', 'Closed',
'F', 'Future',
'N', 'Never',
) ap_status,set_of_books_id
FROM gl.gl_period_statuses
WHERE application_id = 200
AND UPPER (period_name) = UPPER ('&period_name')
AND set_of_books_id ='&sob' ) c,
(SELECT period_name,
DECODE (closing_status,
'O', 'Open',
'C', 'Closed',
'F', 'Future',
'N', 'Never',
) ar_status,set_of_books_id
FROM gl.gl_period_statuses
WHERE application_id = 222
AND UPPER (period_name) = UPPER ('&period_name')
AND set_of_books_id ='&sob') d,
(SELECT fdp.period_name,
DECODE (fdp.period_close_date,
NULL, 'Open',
) fa_status,fbc.set_of_books_id
FROM fa.fa_book_controls fbc, fa.fa_deprn_periods fdp
WHERE fbc.set_of_books_id ='&sob'
AND fbc.book_type_code = fdp.book_type_code
AND UPPER (fdp.period_name) = UPPER ('&period_name')) e
WHERE a.period_name = b.period_name(+)
AND a.period_name = c.period_name(+)
AND a.period_name = d.period_name(+)
AND a.period_name = e.period_name(+)
AND a.set_of_books_id=b.set_of_books_id(+)
and a.set_of_books_id=c.set_of_books_id(+)
and a.set_of_books_id=d.set_of_books_id(+)
and a.set_of_books_id=e.set_of_books_id(+)

AP to GL Transfer

SELECT details."Vendor_Num", details."Vendor_Name",
details."Prepayment_Num", details."Prepayment_Currency",
details."Prepayment_Amount" "Prepayment_Amount",
SUM (details."Apply_Amount") "Apply_Amount_Sum"
FROM (SELECT pv.segment1 "Vendor_Num", pv.vendor_name "Vendor_Name",
ai1.invoice_num "Prepayment_Num",
ai1.invoice_currency_code "Prepayment_Currency",
ai2.invoice_num "Invoice_Num",
ai2.invoice_currency_code "Invoice_Currency",
aid1.amount "Prepayment_Amount",
NVL (aid2.amount, 0) "Apply_Amount"
FROM ap.ap_invoices_all ai1,
ap.ap_invoices_all ai2,
ap.ap_invoice_distributions_all aid1,
ap.ap_invoice_distributions_all aid2,
po.po_vendors pv
WHERE ai1.set_of_books_id = &sob
AND ai1.invoice_id = aid1.invoice_id
AND ai2.invoice_id(+) = aid2.invoice_id
AND aid1.invoice_distribution_id = aid2.prepay_distribution_id(+)
AND TRUNC (ai1.invoice_date) > TRUNC (SYSDATE - 3650)
AND ai1.invoice_type_lookup_code = 'PREPAYMENT'
AND ai1.cancelled_date IS NULL
-- AND ai1.invoice_num = '071230'
AND aid1.amount + NVL (aid2.amount, 0) <> 0
AND aid1.amount <> NVL (aid2.amount, 0)
AND ai1.vendor_id = pv.vendor_id) details
HAVING (details."Prepayment_Amount" + SUM (details."Apply_Amount") <> 0)
AND (details."Prepayment_Amount" <> SUM (details."Apply_Amount"))
GROUP BY details."Prepayment_Num",

Find Supplier Site Information :

pov.vendor_name Supplier,
povs.vendor_site_code Site,
povs.address_line1 A1ddress,
povs.address_line2 A2ddress,
povs.address_line3 A3ddress,
povs.city||', '||
povs.state||' '||
povs.zip A4ddress,
from po_vendors pov,
po_vendor_sites povs
where pov.vendor_id=601
and pov.vendor_id=povs.vendor_id
order by 1

Prepayment AP invoice Details 

SIGN (SUM (amount - NVL (prepay_amount_remaining, amount))),
DECODE (SUM (prepay_amount_remaining), 0, 'Y', NULL),
AS PP_F -- Y is Fully Applied, N is Partially or Not Applied
FROM ap_invoice_distributions_all aid, ap_invoices_all ai, po_vendors pv
WHERE aid.invoice_id = ai.INVOICE_ID
AND aid.line_type_lookup_code = 'ITEM'
AND ai.invoice_type_lookup_code = 'PREPAYMENT'
AND NVL (reversal_flag, 'N') <> 'Y'
GROUP BY pv.vendor_name, ai.invoice_num
SIGN (SUM (amount - NVL (prepay_amount_remaining, amount))),
DECODE (SUM (prepay_amount_remaining), 0, 'Y', NULL),
) <> 'Y';

Supplier Bank Account Information

SELECT DISTINCT abau.bank_account_uses_id bank_account_uses_id,
abau.end_date end_date,
abau.external_bank_account_id external_bank_account_id,
abau.primary_flag primary_flag,
abau.start_date start_date,
abau.vendor_id vendor_id, 
abau.vendor_site_id vendor_site_id,
aba.bank_account_name bank_account_name,
aba.bank_account_num bank_account_num,
aba.bank_account_type bank_account_type,
aba.account_type account_type,
aba.currency_code currency_code,
aba.description bank_account_description,
aba.check_digits check_digits,
aba.multi_currency_flag multi_currency_flag,
abb.bank_name bank_name, 
abb.bank_name_alt bank_name_alt,
abb.bank_number bank_number,
abb.bank_branch_name bank_branch_name,
abb.bank_branch_name_alt bank_branch_name_alt,
abb.bank_num bank_num, abb.institution_type institution_type,
abb.bank_branch_type bank_branch_type,
pv.vendor_name vendor_name,
pv.segment1 vendor_number,
pv.vendor_type_lookup_code vendor_type_lookup_code,
pvs.vendor_site_code vendor_site_code,
abb.end_date branch_end_date,
aba.inactive_date acct_inactive_date, 
FROM ap_bank_account_uses_all abau,
ap_bank_accounts_all aba,
ap_bank_branches abb,
po_vendors pv,
po_vendor_sites_all pvs
WHERE abau.external_bank_account_id = aba.bank_account_id
AND aba.bank_branch_id = abb.bank_branch_id
AND abau.vendor_id = pv.vendor_id
AND abau.vendor_id = pvs.vendor_id(+)
AND abau.vendor_site_id = pvs.vendor_site_id(+)