Debashree's Blog

SQL for AR Invoice Balance

2015-01-09T17:00:00.000-08:00

-- SQL to Figure out the Invoice Balance.
SELECT rta.TRX_NUMBER ,xxfact.* ,ps.amount_due_remaining-- NVL (SUM (ps.amount_due_remaining), 0) invoice_balance
FROM ra_cust_trx_types_all rtt,
ra_customer_trx_all rta,
ra_cust_trx_line_gl_dist_all rgld,
gl_code_combinations cc,
hz_cust_accounts_all cust_acct,
ar_payment_schedules_all ps,
hz_cust_acct_sites_all acct_site,
hz_party_sites party_site,
hz_locations loc,
hz_cust_site_uses_all site_uses,
BOLINF.XX_MTSN_AR_FACTS_HDR_STG xxfact
WHERE xxfact.PROCESS_STATUS='P'
and rta.TRX_NUMBER =xxfact.invoice_number
and rta.TRX_NUMBER in(
'9607549','9607549','9553904','9539375',
'8585991','8253017','7609696',
'7080387','6653404','5313598',
'4785453','3965052','3429136',
'3399680','2871815','2675303',
'2455262','2186174','1660375')
--TRUNC (ps.gl_date) <= :p_as_of_date
--AND cust_acct.account_number = :p_account_number
AND ps.customer_id = cust_acct.cust_account_id
AND ps.cust_trx_type_id = rtt.cust_trx_type_id
--AND ps.trx_date <= :p_as_of_date
AND ps.CLASS NOT IN ('CM', 'PMT')
AND site_uses.site_use_code = 'BILL_TO'
AND acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id
AND NVL (site_uses.status, 'A') = 'A'
AND cust_acct.cust_account_id = acct_site.cust_account_id
AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
AND ps.customer_id = acct_site.cust_account_id
AND ps.customer_site_use_id = site_uses.site_use_id
AND rta.customer_trx_id = ps.customer_trx_id
AND rta.customer_trx_id = rgld.customer_trx_id
AND rgld.code_combination_id = cc.code_combination_id AND rgld.account_class = 'REV'

--------------------------------------------------
-- Query for Credit Memo Balance

SELECT NVL (SUM (ps.amount_due_remaining), 0) cr_memo_balance
FROM ra_cust_trx_types_all rtt,
ra_customer_trx_all rta,
ra_cust_trx_line_gl_dist_all rgld,
gl_code_combinations cc,
hz_cust_accounts_all cust_acct,
ar_payment_schedules_all ps,
hz_cust_acct_sites_all acct_site,
hz_party_sites party_site,
hz_locations loc,
hz_cust_site_uses_all site_uses
WHERE TRUNC (ps.gl_date) <= :p_as_of_date
AND cust_acct.account_number = :p_account_number
AND ps.customer_id = cust_acct.cust_account_id
AND ps.cust_trx_type_id = rtt.cust_trx_type_id
AND ps.trx_date <= :p_as_of_date
AND ps.CLASS = 'CM'
AND ps.status = 'OP'
AND site_uses.site_use_code = 'BILL_TO'
AND site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
AND acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id
AND NVL (site_uses.status, 'A') = 'A'
AND cust_acct.cust_account_id = acct_site.cust_account_id
AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
AND ps.customer_id = acct_site.cust_account_id
AND ps.customer_site_use_id = site_uses.site_use_id
AND rta.customer_trx_id = ps.customer_trx_id
AND rta.customer_trx_id = rgld.customer_trx_id
AND rgld.code_combination_id = cc.code_combination_id
AND rgld.account_class = 'REV'

O2C

2014-09-06T11:32:00.002-07:00

order to cash process steps can be listed as below

Enter the Sales Order
Book the Sales Order
Launch Pick Release
Ship Confirm
Create Invoice
Create the Receipts either manually or using Auto Lockbox ( In this article we will concentrate on Manual creation)
Transfer to General Ledger
Journal Import
Posting
O2C Query
SELECT hp.party_name customer
      ,hca.account_number

      ,hp.party_number

      ,ooh.order_number

      ,ooh.flow_status_code Order_status

      ,rcta.trx_date Invoice_date

      ,rcta.trx_number Invoice_number

      ,rcta.status_trx Invoice_Status

      ,rcta.invoice_currency_code Invoice_currency

      ,rctla.line_number

      ,msi.segment1 Item_number

      ,rctla.description

      ,rctla.extended_amount line_amt

      ,arp.amount_due_original

      ,arp.amount_due_remaining

      ,rcta.org_id

      ,rcta.customer_trx_id

  FROM RA_CUSTOMER_TRX_ALL RCTA,

       RA_CUSTOMER_TRX_LINES_ALL RCTLA,

       RA_CUST_TRX_TYPES_ALL rctt,

       AR_PAYMENT_SCHEDULES_ALL arp,

       HZ_CUST_ACCOUNTS_ALL HCA,

       HZ_PARTIES HP,

       MTL_SYSTEM_ITEMS_B msi,

       OE_ORDER_LINES_ALL ool,

       OE_ORDER_HEADERS_ALL ooh

WHERE rcta.customer_trx_id = rctla.customer_trx_id

  AND msi.inventory_item_id = rctla.inventory_item_id

  AND msi.organization_id = 116

  AND rcta.cust_trx_type_id = rctt.cust_trx_type_id

  AND arp.customer_trx_id = rcta.customer_trx_id

  AND rctla.line_type =  'LINE'

  AND rcta.org_id = rctla.org_id

  AND rcta.bill_to_customer_id = hca.cust_account_id

  AND hca.party_id = hp.party_id

  AND rctla.interface_line_attribute6 = ool.line_id

  AND ool.header_id = ooh.header_id

--  AND ooh.order_number = '13904756'

--  AND hp.party_name = 'BANQUE MAGNETIQUE'

ORDER BY hp.party_name,rcta.trx_number,rctla.line_number;
Enter the Sales Order:

Navigation: Order Management Super User Operations (USA)>Orders Returns >Sales Orders

Enter the Customer details (Ship to and Bill to address), Order type.

Click on Lines Tab. Enter the Item to be ordered and the quantity required.

Line is scheduled automatically when the Line Item is saved.

Scheduling/unscheduling can be done manually by selecting Schedule/Un schedule from the Actions Menu.

You can check if the item to be ordered is available in the Inventory by clicking on Availability Button.

Save the work.

Underlying Tables affected:

In Oracle, Order information is maintained at the header and line level.

The header information is stored in OE_ORDER_HEADERS_ALL and the line information in OE_ORDER_LINES_ALL when the order is entered. The column called FLOW_STATUS_CODE is available in both the headers and lines tables which tell us the status of the order at each stage.

At this stage, the FLOW_STATUS_CODE in OE_ORDER_HEADERS_ALL is ‘Entered’

Book the Sales Order:

Book the Order by clicking on the Book Order button.

Now that the Order is BOOKED, the status on the header is change accordingly.

Underlying tables affected:

At this stage:

The FLOW_STATUS_CODE in the table OE_ORDER_HEADERS_ALL would be

‘BOOKED’

The FLOW_STATUS_CODE in OE_ORDER_LINES_ALL will be

‘AWAITING_SHIPPING’.

Record(s) will be created in the table WSH_DELIVERY_DETAILS with

RELEASED_STATUS=’R’ (Ready to Release)

Also Record(s) will be inserted into WSH_DELIVERY_ASSIGNMENTS.

At the same time DEMAND INTERFACE PROGRAM runs in the background and inserts

into MTL_DEMAND

Launch Pick Release:

Navigation: Shipping > Release Sales Order > Release Sales Orders.

Key in Based on Rule and Order Number

In the Shipping Tab key in the below:

Auto Create Delivery: Yes

Auto Pick Confirm: Yes

Auto Pack Delivery: Yes

In the Inventory Tab:

Auto Allocate: Yes

Enter the Warehouse

Click on Execute Now Button.

On successful completion, the below message would pop up as shown below.

Pick Release process in turn will kick off several other requests like Pick Slip Report,

Shipping Exception Report and Auto Pack Report

Underlying Tables affected:

If Autocreate Delivery is set to ‘Yes’ then a new record is created in the table WSH_NEW_DELIVERIES.

DELIVERY_ID is populated in the table WSH_DELIVERY_ASSIGNMENTS.

The RELEASED_STATUS in WSH_DELIVERY_DETAILS would be now set to ‘Y’ (Pick Confirmed) if Auto Pick Confirm is set to Yes otherwise RELEASED_STATUS is ‘S’ (Release to Warehouse).

Pick Confirm the Order:

IF Auto Pick Confirm in the above step is set to NO, then the following should be done.

Navigation: Inventory Super User > Move Order> Transact Move Order

In the HEADER tab, enter the BATCH NUMBER (from the above step) of the order. Click FIND. Click on VIEW/UPDATE Allocation, then Click TRANSACT button. Then Transact button will be deactivated then just close it and go to next step.

Ship Confirm the Order:

Navigation: Order Management Super User>Shipping >Transactions.

Query with the Order Number.

Click On Delivery Tab

Click on Ship Confirm.

The Status in Shipping Transaction screen will now be closed.

This will kick off concurrent programs like.INTERFACE TRIP Stop, Commercial Invoice, Packing Slip Report, Bill of Lading

Underlying tables affected:

RELEASED_STATUS in WSH_DELIVERY_DETAILS would be ‘C’ (Ship Confirmed)

FLOW_STATUS_CODE in OE_ORDER_HEADERS_ALL would be “BOOKED”

FLOW_STATUS_CODE in OE_ORDER_LINES_ALL would be “SHIPPED”

Create Invoice:

Run workflow background Process.

Navigation: Order Management >view >Requests

Workflow Background Process inserts the records RA_INTERFACE_LINES_ALL with

INTERFACE_LINE_CONTEXT = ‘ORDER ENTRY’

INTERFACE_LINE_ATTRIBUTE1= Order_number

INTERFACE_LINE_ATTRIBUTE3= Delivery_id

and spawns Auto invoice Master Program and Auto invoice import program which creates Invoice for that particular Order.

The Invoice created can be seen using the Receivables responsibility

Navigation: Receivables Super User> Transactions> Transactions

Query with the Order Number as Reference.

Underlying tables:

RA_CUSTOMER_TRX_ALL will have the Invoice header information. The column INTERFACE_HEADER_ATTRIBUTE1 will have the Order Number.

RA_CUSTOMER_TRX_LINES_ALL will have the Invoice lines information. The column INTERFACE_LINE_ATTRIBUTE1 will have the Order Number.

Create receipt:

Navigation: Receivables> Receipts> Receipts

Enter the information.

Click on Apply Button to apply it to the Invoice.

Underlying tables:

AR_CASH_RECEIPTS_ALL

Transfer to General Ledger:

To transfer the Receivables accounting information to general ledger, run General Ledger Transfer Program.

Navigation: Receivables> View Requests

Parameters:

Give in the Start date and Post through date to specify the date range of the transactions to be transferred.
Specify the GL Posted Date, defaults to SYSDATE.
Post in summary: This controls how Receivables creates journal entries for your transactions in the interface table. If you select ‘No’, then the General Ledger Interface program creates at least one journal entry in the interface table for each transaction in your posting submission. If you select ‘Yes’, then the program creates one journal entry for each general ledger account.
If the Parameter Run Journal Import is set to ‘Yes’, the journal import program is kicked off automatically which transfers journal entries from the interface table to General Ledger, otherwise follow the topic Journal Import to import the journals to General Ledger manually.

Underlying tables:

This transfers data about your adjustments, chargeback, credit memos, commitments, debit memos, invoices, and receipts to the GL_INTERFACE table.

Journal Import:

To transfer the data from General Ledger Interface table to General Ledger, run the Journal Import program from Oracle General Ledger.

Navigation: General Ledger > Journal> Import> Run

Parameters:

Select the appropriate Source.
Enter one of the following Selection Criteria:

No Group ID: To import all data for that source that has no group ID. Use this option if you specified a NULL group ID for this source.

All Group IDs: To import all data for that source that has a group ID. Use this option to import multiple journal batches for the same source with varying group IDs.

Specific Group ID: To import data for a specific source/group ID combination. Choose a specific group ID from the List of Values for the Specific Value field.

If you do not specify a Group ID, General Ledger imports all data from the specified journal entry source, where the Group_ID is null.
Define the Journal Import Run Options (optional)

Choose Post Errors to Suspense if you have suspense posting enabled for your set of books to post the difference resulting from any unbalanced journals to your suspense account.

Choose Create Summary Journals to have journal import create the following:

• one journal line for all transactions that share the same account, period, and currency and that has a debit balance

• one journal line for all transactions that share the same account, period, and currency and that has a credit balance.
Enter a Date Range to have General Ledger import only journals with accounting dates in that range. If you do not specify a date range, General Ledger imports all journals data.
Choose whether to Import Descriptive Flexfields, and whether to import them with validation.

Click on Import button.

Underlying tables:

GL_JE_BATCHES, GL_JE_HEADERS, GL_JE_LINES

Posting:

We have to Post journal batches that we have imported previously to update the account balances in General Ledger.

Navigation: General Ledger> Journals > Enter

Query for the unposted journals for a specific period as shown below.

From the list of unposted journals displayed, select one journal at a time and click on Post button to post the journal.

If you know the batch name to be posted you can directly post using the Post window

Navigation: General Ledger> Journals> Post

GL Important Concepts and Questions

2014-09-06T11:16:00.000-07:00

1. Which process push the data into which tables?

Create Accounting : Subledger tables and xla_events -> xla_ae_headers, xla_ae_lines and xla_distribution_links
Transfer to GL: xla_ae_headers and xla_ae_lines -> gl_je_headers and gl_je_lines
Post to GL : gl_je_headers and gl_je_lines -> gl_balances
Subledger Accounting Balances Update : xla_ae_headers and xla_ae_lines -> xla_control_balances

2. Which tables store segment values and descriptions?

The tables are:
- FND_FLEX_VALUES_TL
- FND_FLEX_VALUES.

3. Why is the Field still protected against update, even after unfreezing the structure?

Most probably another flexfield that uses this value set is still frozen.
You can find the other flexfields with the following sql which shows the frozen structures containing the value set:

select vs.flex_value_set_name, st.id_flex_structure_code
from fnd_id_flex_structures st, fnd_flex_value_sets vs, fnd_id_flex_segments sg
where st.id_flex_num = sg.id_flex_num
and vs.flex_value_set_id = sg.flex_value_set_id
and st.freeze_flex_definition_flag = 'Y'
and st.application_id = 101
and st.id_flex_code = 'GL#'
order by 1,2

Inventory Pending material transaction -Period close issue

2014-07-28T11:50:00.004-07:00

Pending transactions for inventory period close can be viewed from Inventory Accounting Periods form

Select Open period and click on Pending button.

Transactions under Resolution Required and Unprocessed Shipping Transactions zones must be resolved before an accounting period can be closed.

Transactions under Resolution Recommended will not prevent the closing of a period but these transactions cannot be resolved after an accounting period is closed.

Below are the queries to check for the pending transactions.

Nav :Accounting Close Cycle > Inventory Accounting Periods

--Unprocessed Material Transactions

SELECT COUNT(*)

FROM MTL_MATERIAL_TRANSACTIONS_TEMP

WHERE ORGANIZATION_ID =

(SELECT ORGANIZATION_ID

FROM APPS.ORG_ORGANIZATION_DEFINITIONS

WHERE ORGANIZATION_CODE = 'ORG_CODE' AND

TRANSACTION_DATE <

(SELECT SCHEDULE_CLOSE_DATE + 1

FROM APPS.ORG_ACCT_PERIODS

WHERE ORGANIZATION_ID =

(SELECT ORGANIZATION_ID

FROM APPS.ORG_ORGANIZATION_DEFINITIONS

WHERE ORGANIZATION_CODE = '')

AND PERIOD_NAME = '')

AND NVL(TRANSACTION_STATUS, 0) <> 2

--Uncosted Material Transactions

SELECT /*+ INDEX (MMT MTL_MATERIAL_TRANSACTIONS_N10) */

COUNT(*)

FROM MTL_MATERIAL_TRANSACTIONS MMT

WHERE ORGANIZATION_ID =

(SELECT ORGANIZATION_ID

FROM APPS.ORG_ORGANIZATION_DEFINITIONS

WHERE ORGANIZATION_CODE = '')

AND TRANSACTION_DATE <

(SELECT SCHEDULE_CLOSE_DATE + 1

FROM APPS.ORG_ACCT_PERIODS

WHERE ORGANIZATION_ID =

(SELECT ORGANIZATION_ID

FROM APPS.ORG_ORGANIZATION_DEFINITIONS

WHERE ORGANIZATION_CODE = '')

AND PERIOD_NAME = '')

AND COSTED_FLAG IS NOT NULL

--Pending WIP Costing Transactions

SELECT COUNT(*)

FROM WIP_COST_TXN_INTERFACE

WHERE ORGANIZATION_ID =

(SELECT ORGANIZATION_ID

FROM APPS.ORG_ORGANIZATION_DEFINITIONS

WHERE ORGANIZATION_CODE = '')

AND TRANSACTION_DATE <

(SELECT SCHEDULE_CLOSE_DATE + 1

FROM APPS.ORG_ACCT_PERIODS

WHERE ORGANIZATION_ID =

(SELECT ORGANIZATION_ID

FROM APPS.ORG_ORGANIZATION_DEFINITIONS

WHERE ORGANIZATION_CODE = '')

AND PERIOD_NAME = '')

--Uncosted WSM Transactions

SELECT COUNT(*)

FROM WSM_SPLIT_MERGE_TRANSACTIONS

WHERE ORGANIZATION_ID =

(SELECT ORGANIZATION_ID

FROM APPS.ORG_ORGANIZATION_DEFINITIONS

WHERE ORGANIZATION_CODE = '')

AND TRANSACTION_DATE <

(SELECT SCHEDULE_CLOSE_DATE + 1

FROM APPS.ORG_ACCT_PERIODS

WHERE ORGANIZATION_ID =

(SELECT ORGANIZATION_ID

FROM APPS.ORG_ORGANIZATION_DEFINITIONS

WHERE ORGANIZATION_CODE = '')

AND PERIOD_NAME = '')

--Uncosted WSM Interface

SELECT COUNT(*)

FROM WSM_SPLIT_MERGE_TXN_INTERFACE

WHERE ORGANIZATION_ID =

(SELECT ORGANIZATION_ID

FROM APPS.ORG_ORGANIZATION_DEFINITIONS

WHERE ORGANIZATION_CODE = '')

AND TRANSACTION_DATE <

(SELECT SCHEDULE_CLOSE_DATE + 1

FROM APPS.ORG_ACCT_PERIODS

WHERE ORGANIZATION_ID =

(SELECT ORGANIZATION_ID

FROM APPS.ORG_ORGANIZATION_DEFINITIONS

WHERE ORGANIZATION_CODE = '')

AND PERIOD_NAME = '')

--Pending Receiving Transactions

SELECT COUNT(*)

FROM RCV_TRANSACTIONS_INTERFACE

WHERE ORGANIZATION_ID =

(SELECT ORGANIZATION_ID

FROM APPS.ORG_ORGANIZATION_DEFINITIONS

WHERE ORGANIZATION_CODE = '')

AND TRANSACTION_DATE <

(SELECT SCHEDULE_CLOSE_DATE + 1

FROM APPS.ORG_ACCT_PERIODS

WHERE ORGANIZATION_ID =

(SELECT ORGANIZATION_ID

FROM APPS.ORG_ORGANIZATION_DEFINITIONS

WHERE ORGANIZATION_CODE = '')

AND PERIOD_NAME = '')

AND DESTINATION_TYPE_CODE = 'INVENTORY'

--Pending Material Transactions

SELECT COUNT(*)

FROM MTL_TRANSACTIONS_INTERFACE

WHERE ORGANIZATION_ID =

(SELECT ORGANIZATION_ID

FROM APPS.ORG_ORGANIZATION_DEFINITIONS

WHERE ORGANIZATION_CODE = '')

AND TRANSACTION_DATE <

(SELECT SCHEDULE_CLOSE_DATE + 1

FROM APPS.ORG_ACCT_PERIODS

WHERE ORGANIZATION_ID =

(SELECT ORGANIZATION_ID

FROM APPS.ORG_ORGANIZATION_DEFINITIONS

WHERE ORGANIZATION_CODE = '')

AND PERIOD_NAME = '')

AND PROCESS_FLAG <> 9

--Pending Shop Floor Move Transactions

SELECT COUNT(*)

FROM WIP_MOVE_TXN_INTERFACE

WHERE ORGANIZATION_ID =

(SELECT ORGANIZATION_ID

FROM APPS.ORG_ORGANIZATION_DEFINITIONS

WHERE ORGANIZATION_CODE = '')

AND TRANSACTION_DATE <

(SELECT SCHEDULE_CLOSE_DATE + 1

FROM APPS.ORG_ACCT_PERIODS

WHERE ORGANIZATION_ID =

(SELECT ORGANIZATION_ID

FROM APPS.ORG_ORGANIZATION_DEFINITIONS

WHERE ORGANIZATION_CODE = '')

AND PERIOD_NAME = '')

--Unprocessed Shipping Transactions

SELECT COUNT(*)

FROM WSH_DELIVERY_DETAILS WDD,

WSH_DELIVERY_ASSIGNMENTS WDA,

WSH_NEW_DELIVERIES WND,

WSH_DELIVERY_LEGS WDL,

WSH_TRIP_STOPS WTS

WHERE WDD.SOURCE_CODE = 'OE'

AND WDD.RELEASED_STATUS = 'C'

AND WDD.INV_INTERFACED_FLAG IN ('N', 'P')

AND WDD.ORGANIZATION_ID = 86

AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID

AND WND.DELIVERY_ID = WDA.DELIVERY_ID

AND WND.STATUS_CODE IN ('CL', 'IT')

AND WDL.DELIVERY_ID = WND.DELIVERY_ID

AND WTS.PENDING_INTERFACE_FLAG IN ('Y', 'P')

AND TRUNC(WTS.ACTUAL_DEPARTURE_DATE) BETWEEN

TO_DATE('27-OCT-2012 00:00:00', 'DD-MON-YYYY HH24:MI:SS') AND

TO_DATE('23-NOV-2012 23:59:59', 'DD-MON-YYYY HH24:MI:SS')

AND WDL.PICK_UP_STOP_ID = WTS.STOP_ID

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

2013-04-16T20:37:00.004-07:00

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

Parent Child relationship for Accounting Flexfield

2013-04-10T00:10:00.002-07:00

SELECT gcc.code_combination_id
      ,ffvc.parent_flex_value
      ,gcc.segment1 Balancing_Segment
      ,gcc.segment,ffvc.description
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

2013-04-10T00:02:00.000-07:00

ACCOUNTING Debit /Credit

2013-04-09T23:06:00.004-07:00

Balance Sheet ,Income Stmt n Cash Flow Basic logic

2013-04-09T22:57:00.001-07:00

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:

ASSETS = LIABILITIES + SHAREHOLDERS' EQUITY

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-term. Current 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.

Important Reports on Receivables

2013-04-08T20:10:00.003-07:00

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

2013-04-08T09:55:00.002-07:00

Get Calendar Information

select calendar_type,
start_date,
end_date,
period_num,
period_name
from fa_calendar_periods
where calendar_type='&calendertype'
order by 2;

Get Depreciation Period :

select book_type_code,
period_name,
period_counter,
period_num,
fiscal_year,
period_open_date,
period_close_date,
calendar_period_open_date,
calendar_period_close_date
from fa_deprn_periods
where book_type_code='&book'
order by 3

How to check AP ,GL, AR periods are closed

SELECT (SELECT sob.NAME
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',
closing_status
) gl_status,
set_of_books_id
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',
closing_status
) 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',
closing_status
) 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',
closing_status
) 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',
'Closed'
) 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(+)
ORDER BY 1

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",
details."Prepayment_Currency",
details."Prepayment_Amount",
details."Vendor_Num",
details."Vendor_Name"

Prepayment AP invoice Details

SELECT pv.VENDOR_NAME,
ai.invoice_num,
NVL (
DECODE (
SIGN (SUM (amount - NVL (prepay_amount_remaining, amount))),
1,
DECODE (SUM (prepay_amount_remaining), 0, 'Y', NULL),
NULL
),
'N'
)
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 pv.VENDOR_ID = ai.VENDOR_ID
AND aid.line_type_lookup_code = 'ITEM'
AND ai.invoice_type_lookup_code = 'PREPAYMENT'
AND ai.INVOICE_ID = :P_INVOICE_ID
AND NVL (reversal_flag, 'N') <> 'Y'
GROUP BY pv.vendor_name, ai.invoice_num
HAVING NVL (
DECODE (
SIGN (SUM (amount - NVL (prepay_amount_remaining, amount))),
1,
DECODE (SUM (prepay_amount_remaining), 0, 'Y', NULL),
NULL
),
'N'
) <> '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,
pvs.address_line1,
pvs.address_line2,
pvs.city,
pvs.zip,
abb.end_date branch_end_date,
aba.inactive_date acct_inactive_date,
aba.org_id
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(+)

AGIS R12 Setup

2013-03-21T22:12:00.000-07:00

Release 12- Advanced Global Intercompany System Setup – Part 1

Posted on April 30, 2009 by Dinesh Nair

OVERVIEW
Part one on setup offers a broad view on how AGIS is Setup in R12

The setups are divided into four parts
1. Environmental setups
2. General Ledger (GL) setups
3. Receivable (AR) setups
4. Payables(AP) setups
5. Advanced Global Intercompany System (AGIS)

Part two on transaction processing discusses the setups those are required for processing the transaction in AGIS

Transaction Processing in AGIS
The summaries of steps those are used for AGIS transaction processing are As follows.

1. Initiation of a transaction by an intercompany organization
2. Completion of accounting from the initiator side
3. Completion of accounting from recipient side
4. Approval of the transaction by the recipient

If the invoicing is opted for, in transaction type setups then,

1. Processing of Invoice from Receivables
(For the initiator subsidiary side)
2. Processing of invoice in Payables
(For the Recipient subsidiary side)

Part three discusses the reports in AGIS and reconciliation features in AGIS

1. Standard reports Available in AGIS
2. econciliation features in AGIS

The setup steps are explained in the subsequent sections, from start to finish

The details of the examples used in this document is as follows,

-Initiator Subsidiary-‘Blink AGIS INV ORG’. This organization is a part of BCR Singapore Legal Entity. This Legal Entity uses 001 balancing segment value. The user that is having access to initiator subsidiary is called BCR_S

-Recipient Subsidiary–‘Blink AGIS INVJ ORG’. This organization is a part of BCR Japan Legal Entity. This Legal Entity uses 002 balancing segment values. The user having access to initiator subsidiary is called BCR_J

PART ONE- AGIS SETUP

Welcome to the Advanced Global Intercompany System (AGIS) setup

Description of the setup that is used in this viewlet is as follows
One ledger called Blink ledger.
Two legal entities under that ledger called BCR Singapore legal entity and BCR Japan legal entity. Two operating units Blink AGIS Singapore OU, Blink AGIS Japan OU respectively.

*Note: Setting up of receivable setup is mandatory, for using invoicing functionality. And for setting up of receivables mandatory that inventory Organization are created as well.

The transactions in this example are assumed to be happening from BCR Singapore to BCR Japan. For theses two create
Two employees: BCR Singapore and BCR Japan
Two Users : BCR_S and BCR_J
Create receivables and GL Responsibilities.

As the first step we should define employees in HRMS. These employees are used later on in the AGIS security setups.

Navigation: HRMS, Progress Admin S&L>>People>>Enter and Maintain

Define an employee ‘BCR, Singapore’ .This person will be attached to the user initiating the transaction in AGIS. In our example transaction gets initiated by BCR Singapore

Similarly create another employee (Recipient) BCR Japan and Save your new creations.

Create a users like BCR_S & BCR_J. Note the Person attached to this User is (BCR, Singapore) he is now the contact Person

Next step is to define Custom responsibilities like GL Super User, AP Super User, AP Super User, HRMS Management, OM, & AGIS Super user Resposibilities and attach it to our users.

Our next setup in AGIS set id to define a business group

Login to HRMS Responsibility and create Business group called Blink AGIS, Which you will later require to attach with the operating Units.

Navigation:
HRMS Management>>HRMS Manager>>Work Structures>>Organization>>Description

Click on Other button at line Level and set the Business Group settings:

Next step is to set some of the system profile options.
Navigation: System Administrator>>Profile>>System

Profile options where set for
1. HR: Business Group,
2. HR: Security,
3. MO: operating Unit,
4. MO: security profile,
5. MO: Default Operating Unit Profile,
6. GL Ledger name
One might already know, If MO: Security profile is defined the MO: Operating unit profile becomes redundant.

In this part we will see about GL ,AR, AP, setups

Now our next step is to create Legal entities namely BCR Singapore legal entity and BCR Japan legal entity of which one will be Our Initiator and other will be Recipient for our Intercompany transactions.

In BCR General Ledger Super User Responsibility

*Note: When you create our business group HRMS security profile gets automatically created with the same name of our Business Group.

Navigation: Setup>>Financials>>Accounting Setup Manager>>Accounting Setup

Click on Legal Entity tab in Accounting Setup Manager page to create new legal entity. Fill in all the mandatory columns like Territory, Legal Entity Name, Organization name, RCB Number and Legal Address. The screen shot below shows the sample of legal entity creation. Values such as Place of Registration, Inspection date, Primary Activity, Secondary Activity, Type of Company, and Fiscal year end are optional fields.

Similarly Create BCR Japan Legal Entity. And query the names in Legal entity form for confirming the creation of legal entities.

Our next step in the process is to create a valid ledger. Here I have a valid ledger called Blink Ledger highlighted on the screen shot below. Now attach the legal entities we have created in the previous step to this valid ledger.

In the screen shot below you can see the legal entities attached to the Ledger. Then define the operating unit by clicking the Operating unit update icon as shown below.

Create Operating units Namely ‘Blink AGIS Singapore OU’ and ’Blink AGIS Japan OU’. While creating attach the business group we created in the initial steps. (Blink AGIS)

Query the operating Units as show below for confirming the creation. Here you can see your operating units, Short code, Business group to which they are attached and the corresponding legal entities they are assigned to

So far what is covered is assignment of Legal entities and Operating units to Ledger.

Next step would be to define Inter-company Balancing Rules. Options to define inter- company rules would be visible, If and only if the inter company option is enabled.

Clink on Update icon to define Intercompany balancing rule

Under Intercompany company accounts link, there is a link called ‘Define Relationships’ where one can create relationship between various legal entities. This is the place where GL is noticed of ‘from where’ and’ to where’ the transactions are going to flow.In case, define the relationships for the BCR Singapore Legal Entity to BCR Japan Legal entity. As of now reverse way transactions is not happening so only one way relationship (Singapore LE to Japan LE) would suffice.

Define the relationship here. There are two parties in a relationship. First one is the Transacting Entity and the second is Trading Partner Entity.

Note: The relationship can be defined individual / multiple Balancing segment values of the transacting and trading entity.

Now we have just finished the required setups in GL for AGIS. Before entering AR setup the prerequisite is to have a Inventory organization.

Below screen shots will tell you the creation of INV organization:

Complete the Accounting Information, Receiving Information, Inventory Information to complete the INV. Organization creation.

Now set the following system profile option at this responsibility level
Profile options where set for
1. HR: Business Group,
2. HR: Security,
3. MO: operating Unit,
4. MO: security profile,
5. MO: Default Operating Unit Profile,
6. GL Ledger name
*Note: When you create our business group HRMS security profile gets automatically created with the same name of our Business Group.

Now we will move on to Payables setups.

In AP Define Financial Options,Payable Option, and System profile Option.setting up of Financial & payables options are more are less similar to 11i.

Login to Intercompany Super user responsibility assigned to our user. The First step is defining organizations. These are the organizations which will be used by AGIS

Navigation: Intercompany Super user>>Setup>> Organization

Define Organization as shown below.

Similarly create organization for Japan legal entity also. And Query the same in organization form for confirming your creation. Note that in the above Screen while creating organization select ‘Yes’ for enabled column. This flag indicates that this organization is enabled for intercompany transactions. Without this processing of transactions in those organizations, through AGIS is not possible

Next Step is to security definition. Security is defined as, which user/person will have access to which and how many intercompany organizations
Navigation: Intercompany Super User>>Setup>>security

Query the Person name and the Respective organization on which the person should be given access. And enable “Yes” for the options as shown below

In the same way shown above give access for BCR_S to Singapore Org. and BCR_J to Japan Org.

Next set is to define Transaction types in AGIS.
Your transaction type will decide whether invoicing is required for the transaction and manual approval is required.

Our next step inthe process is to define AGIS system options

Next we will define invoicing options

Under invoicing option Define Receivable Assignment and Customer/Supplier association as shown in the below screen shots

Example setup of one of the operating unit is shown in figure.
Note that Receivables Transaction Type and Receivables Memo lines can be linked only id Receivable setups are complete.If those setups are not complete then trhe LOV will not appear.The values like “Intercompany” , “Global Intercompany” respectively are seeded values in Receivables. These have been specifically seeded in Receivables for AGIS

In the above step definition of Customer/ Supplier Association is done.There are two tabs in the setup.
1.Customer/Supplier association tab to define the Association
2.Trading partner tab to reviewthe earlier Created association.

The second step in the creation of customer supplier association is selection of customer and entering a “Bill To Site” for the customer
Note that both are mandatory.

With this we have completed AGIS setup required for processing AGIS transactions We will discuss on AGIS Transaction process in the next part.

AGIS – Transaction Processing

Now we are ready with the setups for processing AGIS transaction. Login using Initiator Account . In our example BCR_S (Singapore is the initiator) and select Outbound transaction

This is the first form for data entry. notice the Transaction type, Initiator fields entered at the top of the screen.

In the second half, Receipient Details are entered.

The next screen is to complete accounting from the initiator side. Click on Add line button and enter The second effect of transaction from initiator side.

Once the transaction entry is complete click On Submit button.
After submition you can see the status of the transaction appears as “Submitted” which is Shown below

Now Login as Recipient. in our example BCR_J (Japan is Our Recipient)
and click on Inbound transaction as shown below

Query for the same batch number. And you will find the Status of the trasaction appears as “Received” for the Recipient.

Click on update icon for entering Recipient side accounting information.

About these ads

Mass Addition on FIxed Asset

2013-03-21T17:23:00.001-07:00

AP to Fixed Asset flow

1.Asset Invoice Creation within AP

For assets to be transferred to Assets, it must be approved and posted in the GL and charged the distribution to a clearing account that is already assigned to an asset category or charge directly to the asset cost account.

The track as asset flag will be automatically checked if the accounts have been set up as Asset within the GL.

Invoice can be posted either to the asset clearing account or directly to the asset cost account.

Having a clearing account provides more control particularly if an asset does not get added in the correct period. That would therefore make the reconciliation between GL and FA easier.

Typical steps are herewith:

1. Navigate to the Submit a New Request Window and select REQUEST SET.

2. Select the Mass Additions Create Request Set. This set contains two programs:

Mass Additions Create
Mass Additions Create Report

2.Mass Addition Create

Following the successful creation of invoice in AP,the Mass Addition Create program is ran from the Account Payables.

Mass Addition Create lets you send Mass Addition lines from Oracle Payables as cost adjustments or as potential new assets. Create Mass Additions sends valid invoice line distributions and associated discounts from Payables to an interface table in Oracle Assets.

The Mass Addition Create process generates a Mass Addition Execution Report which shows the number of records inserted, Invoice ID and the asset account.

We can download the Mass Addition Create Report , it shows assets that have been successfully created from the process which will aid the reconciliation between Asset created and Asset Purchased (entered in AP). The report contains such information as legal entity name, asset account(as indicated on AP) , cost centre (as indicated on AP), Supplier name, Invoice Number & Date, the asset description and the cost of the asset.

Also to ensure that assets created matches assets purchased, if the prepare mass addition form is queried for all assets, a queue name other than POSTED indicates that the asset invoice has not yet been successfully created as an asset on Fixed Asset.

3.Mass Addition Post

The posting process creates assets from mass addition lines in the POST queue using the data you entered. It also adds mass additions in the COST ADJUSTMENT queue to existing assets. You can run this program as often as you want during a period.

At this state, they become an asset and available on the Asset Workbench. The queue name at this point changes to POSTED .Mass Additions Posting Report FAS824 is the output of this process.

Navigation > Mass Additions > Post Mass Additions

4.Depreciation & Transfer to GL

Depreciation computations depend on such factors as the type of depreciation (Life, units of production, or flat rate), the depreciation life and the depreciation method.

The asset category specifies a default depreciation method to be used with an asset. You will usually want to set up categories in such a way that you do not need to override the defaults they establish.

Data Flow:

Here is underline data flow diagram based out of the above discussed sub processes.

2013-03-21T16:37:00.004-07:00

PO Matching Setting in Purchase Order Shipment Line(2-way,3-way,4-way)

PO Matching Setting in Purchase Order Shipment Line

Invoice matching can be set in five different areas of Oracle Purchasing:
In the list below, a setting at any level will override the settings above it.

1. Oracle Purchasing Options
a. Navigate to: Setup > Organizations > Purchasing Options
b. Select Default Alternative Region

2. Supplier Information
a. Navigate to: Supply Base > Suppliers
b. Query on specific supplier
c. Click on Open
d. Select Receiving Alternative Region

3. Line Types
a. Navigate to: Setup > Purchasing > Line Types
b. In the Receipt Required field: Yes = 3-way, No = 2-way

4. Items
a. Navigate to: Items > Master Items
b. Query on specific item
c. Select Purchasing Alternative Region
d. In the Invoice Matching section: Yes = 3-way, No = 2-way

5. Purchase Order Shipments
a. Navigate to: Purchase Orders > Purchase Orders
b. Enter (header and) line information
c. Click on Shipments button
d. Select More Alternative Region

Ramification of Invoice Match to PO and Invoice Match to Receipt

Oracle Payables shares purchase order information from your purchasing system to enable online matching with invoices. Invoiced or billed items are matched to the original purchase orders to ensure that you pay only for the goods or services you ordered and/or received. If you are billed for an item over the amount and quantity tolerances you define in the Invoice Tolerance window, during Approval, Oracle Payables applies a hold to the invoice, which prevents payment.
Oracle Payables supports three levels of matching which verify that purchase order and
invoice information match within defined tolerances.

Match Approval Level: 2-Way , 3-Way, 4-Way.

In 2-way: what ever you have ordered for the PO you will make the payment for the suppliers in 2- way i.e we will compare two documents PO and Invoice.
2-way matching verifies that Purchase order and invoice information match within your tolerances:
Quantity billed <= Quantity Ordered
Invoice price <= Purchase order price
Eg:Suppose we Had given PO for 100 items ,for that we will receive invoice for 100 items. so that we will make payment for that 100 items.

In 3-Way you will compare 3 documents i.e PO+reciept+Invoice.
3-way matching verifies that the receipt and invoice information match with the quantity tolerances defined:
Quantity billed <= Quantity received.
Eg:Suppose we have ordered 100 items in PO. But we had received only 75 items ,But we had received invoice for 100 items. so, we will make payment for only 75 items.

In 4-Way you will compare 4 documents i.e PO+Receipt+Invoice+Inspection.
4-way matching verifies that acceptance documents and invoice information match within the quantity tolerances defined:
Quantity billed <= Quantity accepted.
Eg:Suppose we have 100 items in PO. Suppers send us 75 items We will do inspection on those items what ever we have received, If 15items got damaged. finally, we are going to make payment to the 60 items only.

When you match to a purchase order, Payables automatically checks that the total of PO_DISTRIBUTIONS.QUANTITY_ORDERED = AP_INVOICE_DISTRIBUTIONS.QUANTITY_INVOICED (2-way matching). Payables only checks QUANTITY_RECEIVED (3-way matching) if the RECEIPT_REQUIRED_FLAG is set to Y and only checks QUANTITY_ACCEPTED (4-way matching) if the INSPECTION_REQUIRED_FLAG is set to Y.
Invoice Match Option:
The Invoice Match Option determines whether or not you intend to match invoices for this supplier against purchase orders or receipts.
Invoice Match option to PO Match:
--------------------------------------------
•Payables must match the invoice to the purchase order.
•If the Invoice is matched to a PO rather than to the Receipt when the AP team do a match they have the full PO available to match rather than just the specific lines on the PO that were received.
•There is a possibility someone in payables matching to the wrong distribution if they use Match to PO.

•The accrual is valued at the PO exchange rate date.

Invoice Match option to PO Match:----------------------------------------------
•Payables must match the invoice to the receipt.
•Receipt Match Option is recommended if you want accounting to use exchange rate information based on the receipt date or if you want to update exchange rate information on the receipt.
•If you use the match to receipt option AP team can't match until the goods are received. If the receiving doesn't happen there is no way to associate the invoice with the PO.
•Invoice processing will be on hold till a receipt is entered into the system.
•Receipt Match option determines the cost with more accuracy i.e. Match to Material item receipt and link other invoice charges to receipt and You can now also associate freight, tax, and miscellaneous charges from invoices to the related receipt.
•Run the Matching Detail Report from Other -> Request -> Run. This report will show you detail of how an invoice, purchase order, or receipt was matched. This report is especially helpful when an invoice is on hold and you are trying determine why the hold was placed.

The Invoice Match Option defaults from the Supplier Sites window. You can change the Invoice Match Option on the shipment until you receive against the shipment.

Financial Options - Invoice Match Option:
For purchase order shipments, indicate whether you want to match invoices to purchase orders or to purchase order receipts. If the supplier was created automatically during Expense Report Import, the default value is Purchase Order, and you can change it to Receipts as appropriate.

The decision to set the Invoice match option to Receipt or Purchase Order depends on Business Needs.
•If you are using Multi Currency Functionality for Purchase Order and if like to have the rate based on Receipt date you need to go with Receipt Match at the invoice match option.
•The main advantage is the Exchange Rate factor i.e. Receipt/Current Date instead of Purchaser Order date.
•Receipt Match gives much closer control of the matching process,Particularly where you have multiple receipts of large purchase order.

If a Business is using Invoice Match option Purhcase Order and like to change from Purchase Order to Receipt Match, then what would be the impact?

•All Supplier sites need to be updated with Invoice Match option to Receipt.
•Purchase orders which are already approved with Invoice Match Option of Purchase order should continue with the same existing process.
•All existing Open PO, which need a change from Purchase Order to Receipt, should be corrected by changing the invoice match option in the purchase order shipment to Receipt. Payables team should match invoices to Receipt for all Purchase Orders created after the cut-off date.
•Training to AP/PO users.

In order for the receipt to use the current exchange rate and not the PO exchange rate, the Invoice Match Option at the system level and on the suppliers should be Receipt, not Purchase Order.

How to find Trace File Names in Oracle apps based on request id

2013-03-21T16:26:00.003-07:00

SELECT 'Request id: '||request_id

, 'Trace id: '||oracle_Process_id

, 'Trace Flag: '||req.enable_trace,

'Trace Name: '||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',

'Prog. Name: '||prog.user_concurrent_program_name,

'File Name: '||execname.execution_file_name|| execname.subroutine_name ,

'Status : '||decode(phase_code,'R','Running') ||'-'||decode(status_code,'R','Normal'),

'SID Serial: '||ses.sid||','|| ses.serial#,

'Module : '||ses.module

from fnd_concurrent_requests req, v$session ses,

v$process proc, v$parameter dest, v$parameter dbnm,

fnd_concurrent_programs_vl prog, fnd_executables execname

where req.request_id = req.request_id ---&request

and req.oracle_process_id=proc.spid(+)

and proc.addr = ses.paddr(+) and dest.name='user_dump_dest' anddbnm.name='db_name' and req.concurrent_program_id = prog.concurrent_program_id and req.program_application_id = prog.application_id and prog.application_id = execname.application_id and prog.executable_id=execname.executable_id;

Delete Cache memory in Oracle Apps R12

2013-03-21T16:25:00.002-07:00

Delete Cache memory in Oracle Apps R12

Here is the Navigation for the same

1)Go to Functional Administrator
2)Click on Core Services Tab
3)Click on Caching Framework Tab
4)Within Caching framework click on Global configuration
5)Under Cache policy click on "Clear ALL Cache" Button
5) Click on Apply

Oracle Fixed Asset key tables

2013-03-21T16:20:00.000-07:00

FIXED ASSETS

Tables of Fixed Assets
=================

1- FA_DEPRN_PERIODS
2- FA_DEPRN_SUMMARY
3- FA_ADDITIONS_B
4- FA_BOOKS
5- FA_CATEGORIES_B
6- FA_DEPRN_DETAIL

FA_DEPRN_PERIODS contains information about your depreciation periods. Oracle Assets uses this table to determine when each period in FA_CALENDARS was open for a depreciation book. PERIOD_OPEN_DATE and PERIOD_CLOSE_DATE are the dates when you opened and closed each book’s depreciation period. Each time you run the depreciation program, it closes the current period by setting PERIOD_CLOSE_DATE to the system date. It also opens the next period by inserting a new row into this table in which PERIOD_CLOSE_DATE is NULL and PERIOD_OPEN_DATE equals the PERIOD_CLOSE_DATE of the old row. CALENDAR_PERIOD_OPEN_DATE and CALENDAR_PERIOD_CLOSE_DATE correspond to your calendar as defined by the START_DATE and END_DATE columns in FA_CALENDAR_PERIODS.

FA_DEPRN_SUMMARY contains depreciation information for your assets. Each time you run the depreciation program, it inserts one row into thistable for each asset. PERIOD_COUNTER is the period for which you ran the depreciation program. DEPRN_AMOUNT is the depreciation expense for an asset in a depreciation period. It is the sum of DEPRN_AMOUNT in all the rows of FA_DEPRN_DETAIL for the asset and period. YTD_DEPRN is the accumulated depreciation of an asset for the current fiscal year as of the end of this period. DEPRN_RESERVE is the total accumulated depreciation for this asset. DEPRN_SOURCE_CODE tells you what program created the row BOOKS Created by the Depreciation Books form, Quick Additions form, or the post mass additions program when you enter a new asset. DEPRN Created by the depreciation program when you run depreciation. ADJUSTED_COST is the depreciable basis the depreciation program uses to calculate depreciation for an asset in a depreciation period. This value is the same as the asset’s recoverable cost, except for assets that use a diminishing value depreciation method, assets to which you have made an amortized adjustment, and assets you have revalued.
For assets that use a diminishing value method, the ADJUSTED_COST is the beginning of year net book value, which the depreciation program updates at the start of each fiscal year. When you perform an amortized adjustment on an asset or revalue it, the ADJUSTED_COST becomes the asset’s net book value at the time of the adjustment or revaluation. BONUS_RATE is the bonus rate that Oracle Assets adds to the adjusted rate to give you the flat rate for the fiscal year. The depreciation program uses this rate to calculate depreciation for an asset. This only applies to assets that use both a flat–rate depreciation method and bonus depreciation.

FA_ADDITIONS_B contains descriptive information to help you identify your assets. Oracle Assets does not use this table to calculate depreciation.When you add an asset, Oracle Assets inserts a row into this table and into FA_ASSET_HISTORY. When you change the asset information stored in this table, Oracle Assets updates it in this table. It also creates a new row in FA_ASSET_HISTORY. When you perform a unit retirement, Oracle Assets reduces the CURRENT_UNITS by the units retired. UNIT_ADJUSTMENT_FLAG is set to YES by the Additions form if you change the number of units for an asset. The Transfers form resets it to NO after you reassign the remaining units. FA_ADJUSTMENTS stores information that Oracle Assets needs to create journal entries for transactions. The posting program creates journal entries for regular depreciation expense from information in FA_DEPRN_DETAIL. Oracle Assets inserts a row in this table for the debit and credit sides of a financial transaction. All the rows for the same transaction have the same value in the TRANSACTION_HEADER_ID column. The SOURCE_TYPE_CODE column tells you which program created the adjustment:
- ADDITION Depreciation program
- ADJUSTMENT Expensed or Amortized Adjustment User Exit
- CIP ADDITION Depreciation program
- CIP ADJUSTMENT Expensed or Amortized Adjustment User Exit
- CIP RETIREMENT Gain/loss program
- DEPRECIATION Depreciation program (Retroactive transactions andexpensed depreciation adjustments)
- RETIREMENT Gain/loss program
- RECLASS Reclassification user exit
- TRANSFER Transfers form
- TAX Reserve Adjustments form
- REVALUATION Mass revaluation program
The ADJUSTMENT_TYPE column tells you which type of account Oracle Assets adjusts. DEBIT_CREDIT_FLAG is DR if the amount is a debit and CR if the amount is a credit. ADJUSTMENT_AMOUNT is the amount debited or credited to the account. ANNUALIZED_ADJUSTMENT is the adjustment amount for a period times the number of periods in a fiscal year. The depreciation program uses it to calculate the depreciation adjustment for an asset when you perform multiple retroactive transactions on the asset. Oracle Assets calculates ADJUSTMENT_PER_PERIOD by dividing the ADJUSTMENT_AMOUNT for a retroactive transaction by the numberof periods between the period you entered the transaction and the period that it was effective. For current period transactions, this columnis zero. PERIOD_COUNTER_CREATED IS the period that you entered the adjustment into Oracle Assets. PERIOD_COUNTER_ADJUSTED is the period to which the adjustment applies. It is the same as PERIOD_COUNTER_CREATED, unless you enter a reserve adjustment, in which case PERIOD_COUNTER_ADJUSTED is the last period of the fiscal year to which the adjustment applies. CODE_COMBINATION_ID indicates the Accounting Flexfield combination Oracle Assets debits or credits for all transactions except reclassifications and intercompany transfers. This CODE_COMBINATION_ID is generated using the Account Generator, and the posting program does not perform any further processing.

FA_BOOKS contains the information that Oracle Assets needs to calculate depreciation. When you initially add an asset, Oracle Assets inserts one row into the table. This becomes the ”active” row for the asset. Whenever you use the Depreciation Books form to change the asset’s depreciation information, or if you retire or reinstate it, Oracle Assets inserts another row into the table, which then becomes the new ”active” row, and marks the previous row as obsolete.
At any point in time, there is only one ”active” row in the table for an asset in any given depreciation book. Generally, Oracle Assets uses the active row, but if you run a report for a prior accounting period, Oracle Assets selects the row that was active during that period. You can identify the active row for anasset in a book because it is the only one whose DATE_INEFFECTIVE and TRANSACTION_HEADER_ID_OUT are NULL. When Oracle Assets terminates a row, the DATE_INEFFECTIVE and TRANSACTION_HEADER_OUT are set to the DATE_EFFECTIVE and TRANSACTION_HEADER_IN of the new row, respectively. This means that you can easily identify rows affected by the same transaction because they have the same DATE_EFFECTIVE / DATE_INEFFECTIVE and TRANSACTION_HEADER_ID_IN / TRANSACTION_HEADER_ID_OUT pairs.When Oracle Assets creates the new row, the value used for the TRANSACTION_HEADER_ID_IN column is the same as the TRANSACTION_HEADER_ID in the row inserted into FA_TRANSACTION_HEADERS, and the DATE_EFFECTIVE is the system date. When you retire an asset, Oracle Assets inserts a new row to reduce the COST by the amount retired. When you reinstate an asset, Oracle Assets inserts a new row to increase the COST by the COST_RETIRED in the corresponding row in FA_RETIREMENTS.RATE_ADJUSTMENT_FACTOR is originally 1. It is used to spread depreciation over the remaining life of an asset after an amortization or revaluation. If you perform a revaluation or an amortized adjustment, Oracle Assets resets the Rate Adjustment Factor to prorate the remaining recoverable net book value over the remaining life. This fraction is calculated as [Recoverable Cost – what Depreciation Reserve would be]/Recoverable Cost. The depreciation program uses this value to adjust the depreciation rate for an asset.

FA_CATEGORIES_B stores information about your asset categories. This table provides default information when you add an asset. The depreciation program does not use this information to calculate depreciation.The Asset Categories form inserts one row in this table for each asset category you define. The Application Object Library table

FND_ID_FLEX_SEGMENTS stores information about which column in this table is used for each segment.

FA_DEPRN_DETAIL contains the depreciation amounts that the depreciation program charges to the depreciation expense account in each distribution line.
Oracle Assets uses this information to create depreciation expense journal entries for your general ledger.The depreciation program inserts one row per distribution line for an asset each time you run depreciation.
For example, if you assign an asset to two different cost centers, the depreciation program inserts two rows in this table for the asset. DEPRN_AMOUNT is the amount of depreciation expense calculated forthis distribution line.YTD_DEPRN is the year–to–date depreciation allocated to thisdistribution line.When you add an asset, Oracle Assets inserts a row into this table for the period before the current period. This row has the asset cost in the ADDITION_COST_TO_CLEAR column and a DEPRN_SOURCE_CODE of ’B’. This column is used for reporting on new assets. When you run depreciation, Oracle Assets transfers the cost to the COST column in the current period row, this row has a DEPRN_SOURCE_CODE of ’D’.

ASSET CATEGORIES

SELECT DISTINCT a.segment1||'.'||a.segment2 CATEGORY,a.segment1 MAJOR_CATEGORY
, a.segment2 MINOR_CATEGORY
, gl1.segment1||'.'||gl1.SEGMENT2||'.'||gl1.SEGMENT3||'.'||gl1.SEGMENT4||'.'||gl1.SEGMENT5 ASSET_COST
, gl2.segment1||'.'||gl2.SEGMENT2||'.'||gl2.SEGMENT3||'.'||gl2.SEGMENT4||'.'||gl2.SEGMENT5 ASSET_COST_CLEARING
, c.DEPRN_EXPENSE_ACCT
, gl3.segment1||'.'||gl3.SEGMENT2||'.'||gl3.SEGMENT3||'.'||gl3.SEGMENT4||'.'||gl3.SEGMENT5 DEPRN_RESERVE_ACCOUNT
, deprn_method
, life_in_months
, (life_in_months/12) lIFE
, prorate_convention_code
FROM fa_categories a
, FA_CATEGORY_BOOK_DEFAULTS b
, fa_category_books c
, gl_code_combinations gl1
, gl_code_combinations gl2
, gl_code_combinations gl3
WHERE a.category_id = b.category_id
AND c.category_id = b.category_id
AND c.category_id = a.category_id
AND a.ENABLED_FLAG = 'Y'
AND c.ASSET_COST_ACCOUNT_CCID = gl1.CODE_COMBINATION_ID
AND c.ASSET_CLEARING_ACCOUNT_CCID = gl2.CODE_COMBINATION_ID
AND c.RESERVE_ACCOUNT_CCID = gl3.CODE_COMBINATION_ID
--AND c.WIP_COST_ACCOUNT_CCID = gl4.CODE_COMBINATION_ID
--AND c.WIP_CLEARING_ACCOUNT_CCID = gl5.CODE_COMBINATION_ID
AND b.book_type_code = c.book_type_code
AND c.book_type_code = 'CORP BOOK'

FA YTD Depreciation

FA For getting YTD Depreciation..etc...
--CREATE OR REPLACE VIEW MVL_FA_LISTING_V
(BOOK_TYPE_CODE, ASSET_NUMBER, ASSET_ID, TAG_NUMBER, DATE_PLACED_IN_SERVICE,
DESCRIPTION, ASSET_CATEGORY, ASSET_CATEGORY1, ASSET_COST, ORIGINAL_ASSET_COST,
ASSET_LIFE, ACCUM_DEPRECIATION, YTD_DEPRECIATION, DEP_THIS_RUN, DEPARTMENT_NO,
CUSTODIAN, EMPLOYEE_NUMBER, PO_NUMBER, INVOICE_NUMBER, VENDOR_NUMBER,
VENDOR_NAME, SERIAL_NUMBER, LOCATION_FLEXFIELD, TAX_MAJOR_CATEGORY, TAX_MINOR_CATEGORY,
RETIREMENT_TYPE, ASSET_KEY, PERIOD_NAME, ACQUISITION_DATE, MANUFACTURER_NAME,
RETIREMENT_PENDING_FLAG)
AS
SELECT fbv.book_type_code
,fab.asset_number,fab.asset_id
,fab.tag_number
,fbv.date_placed_in_service
,fab.description
,fcb.segment1||'.'||fcb.segment2 asset_category
,fcb.segment1||'.'||fcb.segment2 asset_category1
,fbv.cost asset_cost
,fbv.original_cost original_asset_cost
,fbv.life_in_months asset_life
,mvl_discoverer_fin_support.get_depreciation_dtls(fab.asset_id
,fbv.book_type_code
,fdh.distribution_id
,fdp.period_counter
,'ACCUM') accum_depreciation
,mvl_discoverer_fin_support.get_depreciation_dtls(fab.asset_id
,fbv.book_type_code
,fdh.distribution_id
,fdp.period_counter
,'YTD') ytd_depreciation
,mvl_discoverer_fin_support.get_depreciation_dtls(fab.asset_id
,fbv.book_type_code
,fdh.distribution_id
,fdp.period_counter
,'RUN') dep_this_run
,gcc.segment2 department_no
,(SELECT ppx.full_name FROM per_people_x ppx WHERE ppx.person_id = fdh.assigned_to AND ROWNUM = 1) custodian
,(SELECT ppx.employee_number FROM per_people_x ppx WHERE ppx.person_id = fdh.assigned_to AND ROWNUM = 1) employee_number
,(SELECT po_number FROM fa_invoice_details_v WHERE asset_id = fbv.asset_id AND ROWNUM = 1) po_number
,(SELECT invoice_number FROM fa_invoice_details_v WHERE asset_id = fbv.asset_id AND ROWNUM = 1) invoice_number
,(SELECT vendor_number FROM fa_invoice_details_v WHERE asset_id = fbv.asset_id AND ROWNUM = 1) vendor_number
,(SELECT vendor_name FROM fa_invoice_details_v WHERE asset_id = fbv.asset_id AND ROWNUM = 1) vendor_name
,fab.serial_number
,fl.segment3||'.'||fl.segment4 location_flexfield
,fab.attribute1 tax_major_category
,fab.attribute2 tax_minor_category
,(SELECT retirement_type_code FROM fa_retirements WHERE retirement_id = fdh.retirement_id AND ROWNUM = 1) retirement_type
,(SELECT segment1||'.'||segment2 FROM fa_asset_keywords WHERE code_combination_id = fab.asset_key_ccid AND ROWNUM = 1) asset_key
,fdp.period_name
,fab.attribute3 acquisition_date
,fab.manufacturer_name
,fbv.retirement_pending_flag
FROM apps.fa_additions_v fab
,apps.fa_books_v fbv
,apps.fa_categories_b fcb
,apps.fa_deprn_periods fdp
,apps.fa_distribution_history fdh
,apps.gl_code_combinations gcc
,apps.fa_locations fl
WHERE 1=1
AND fab.asset_id = fbv.asset_id
AND fcb.category_id = fab.asset_category_id
AND fbv.transaction_header_id_out IS NULL
AND fdp.book_type_code = fbv.book_type_code
AND fdh.asset_id = fbv.asset_id
AND fdh.code_combination_id = gcc.code_combination_id
AND fdh.location_id=fl.location_id
AND fbv.transaction_header_id_out IS NULL
AND fdh.transaction_header_id_out IS NULL
/
SELECT fnd_id_flex_segments.application_column_name,
fnd_id_flex_segments.segment_name, fnd_id_flex_segments.segment_num,
fnd_flex_values.flex_value_set_id,
fnd_flex_values.flex_value AS SEGMENT,
fnd_flex_values_tl.description, fnd_id_flex_segments.application_id, fnd_flex_values.PARENT_FLEX_VALUE_LOW as parent
FROM applsys.fnd_flex_values fnd_flex_values,
applsys.fnd_flex_values_tl fnd_flex_values_tl,
applsys.fnd_id_flex_segments fnd_id_flex_segments
WHERE fnd_flex_values.flex_value_id = fnd_flex_values_tl.flex_value_id
AND fnd_flex_values.flex_value_set_id =
fnd_id_flex_segments.flex_value_set_id
AND fnd_id_flex_segments.flex_value_set_id = 1008035
--      AND fnd_flex_values.flex_value <> 'T'
AND fnd_id_flex_segments.application_id = '140'
AND fnd_id_flex_segments.segment_num = 3
/
SELECT fa_additions.asset_id, disc_gl_set_of_books.set_of_books_id,
disc_gl_set_of_books.description AS set_of_books,
disc_gl_set_of_books.set_of_book_currency,
fa_additions.asset_number, fa_additions.tag_number,
fa_additions.description, fa_additions.manufacturer_name,
fa_additions.serial_number, fa_additions.model_number,
fa_categories_b.segment1, fa_categories_b.segment2,
fa_categories_b.segment3, fa_books.book_type_code,
ROUND ( fa_books.COST
* fa_distribution_history.units_assigned
/ fa_additions.current_units,
0
) COST,
fa_books.date_effective, fa_books.date_ineffective,
fa_books.date_placed_in_service, fa_books.deprn_start_date,
fa_books.original_cost, fa_additions.current_units,
fa_distribution_history.units_assigned, fa_books.life_in_months,
fa_employees.employee_number, fa_employees.NAME employee_name,
fa_locations.segment1 "STATE", fa_locations.segment2 "CITY",
fa_locations.segment3 "SITE", fa_locations.segment4 "LOCATOR",
fa_category_books.asset_cost_acct "ASSET_ACCOUNT_ID",
segment2.description AS "ASSET_ACCOUNT",
fa_book_controls.book_class, disc_ccid_dsc_mv.account_type,
disc_ccid_dsc_mv.code_combination_id, disc_ccid_dsc_mv.gl_seg1,
disc_ccid_dsc_mv.gl_seg2, disc_ccid_dsc_mv.gl_seg3,
disc_ccid_dsc_mv.gl_seg4, disc_ccid_dsc_mv.gl_seg5,
disc_ccid_dsc_mv.gl_seg6, disc_ccid_dsc_mv.gl_seg7,
disc_ccid_dsc_mv.gl_seg8, disc_ccid_dsc_mv.gl_seg9,
disc_ccid_dsc_mv.gl_seg10, disc_ccid_dsc_mv.gl_seg11,
disc_ccid_dsc_mv.gl_name_seg1, disc_ccid_dsc_mv.gl_name_seg2,
disc_ccid_dsc_mv.gl_name_seg3, disc_ccid_dsc_mv.gl_name_seg4,
disc_ccid_dsc_mv.gl_name_seg5, disc_ccid_dsc_mv.gl_name_seg6,
disc_ccid_dsc_mv.gl_name_seg7, disc_ccid_dsc_mv.gl_name_seg8,
disc_ccid_dsc_mv.gl_name_seg9, disc_ccid_dsc_mv.gl_name_seg10,
disc_ccid_dsc_mv.gl_name_seg11,
fa_books.date_placed_in_service "DATE_IN",
--TO_DATE(FA_DISTRIBUTION_HISTORY.DATE_INEFFECTIVE,'DD-MON-RRRR') "DATE_INEFFECTIVE",
--FA_BOOKS.DATE_INEFFECTIVE
NULL "DATE_IN_MONTH", fa_deprn_summary.deprn_amount,
fa_deprn_summary.ytd_deprn, fa_deprn_summary.deprn_reserve,
fa_deprn_summary.deprn_source_code,
fa_deprn_summary.addition_cost_to_clear adjusted_cost,
fa_deprn_periods.period_name
--      disc_fa_invoice_details.vendor_name,
--          disc_fa_invoice_details.invoice_number,
--          disc_fa_invoice_details.invoice_date,
--          disc_fa_invoice_details.fixed_assets_cost AS fa_cost_by_invoice
FROM   apps.fa_additions fa_additions,
fa.fa_book_controls fa_book_controls,
fa.fa_books fa_books,
fa.fa_categories_b fa_categories_b,
fa.fa_category_books fa_category_books,
fa.fa_distribution_history fa_distribution_history,
gl.gl_code_combinations gl_code_combinations,
apps.fa_employees fa_employees,
fa.fa_locations fa_locations,
apps.disc_ccid_dsc_mv disc_ccid_dsc_mv,
apps.disc_segment2 segment2,
apps.disc_gl_set_of_books disc_gl_set_of_books,
--          disc_gl_month in_month,
fa.fa_deprn_detail fa_deprn_summary,
fa.fa_deprn_periods fa_deprn_periods
--          disc_fa_invoice_details disc_fa_invoice_details
WHERE fa_books.book_type_code = fa_book_controls.book_type_code
AND fa_books.asset_id = fa_additions.asset_id
AND fa_books.date_ineffective IS NULL
AND fa_distribution_history.book_type_code = fa_books.book_type_code
AND fa_distribution_history.asset_id = fa_additions.asset_id
AND fa_distribution_history.date_ineffective IS NULL
AND fa_categories_b.category_id = fa_additions.asset_category_id
AND fa_category_books.book_type_code =
fa_distribution_history.book_type_code
AND fa_category_books.category_id = fa_additions.asset_category_id
AND gl_code_combinations.code_combination_id =
fa_distribution_history.code_combination_id
AND fa_employees.employee_id(+) = fa_distribution_history.assigned_to
AND fa_locations.location_id = fa_distribution_history.location_id
AND gl_code_combinations.code_combination_id =
disc_ccid_dsc_mv.code_combination_id
AND segment2.SEGMENT = fa_category_books.asset_cost_acct
AND disc_gl_set_of_books.set_of_books_id =
fa_book_controls.set_of_books_id
--      AND fa_books.date_placed_in_service BETWEEN in_month.start_date AND in_month.end_date
AND fa_deprn_summary.asset_id = fa_distribution_history.asset_id
AND (    fa_deprn_periods.period_counter =
fa_deprn_summary.period_counter
AND fa_deprn_periods.book_type_code =
fa_deprn_summary.book_type_code
)
AND fa_deprn_summary.distribution_id =
fa_distribution_history.distribution_id
--      AND fa_books.asset_id = disc_fa_invoice_details.asset_id(+)
--      AND fa_deprn_summary.deprn_source_code = 'D'
--and fa_additions.asset_id = '10000767';
/

Enable Credit Card related Field in Oracle Order Management.

2013-03-12T20:37:00.003-07:00

Navigate to oracle payment setup and then choose system security option and the mask the fields and bank account .

Now navigate to order management and choose payment type as credit card .
the all field of credit card details are disabled.

but if the credit card info is available to customer site then it will be available to credit card number place.

but it will not allow to modify any credit card details on OM sales order.
but if we change the profile OM credit card privileged to Yes then it will allow to modify credit card details , cvs number etc.

Integration of Service Contracts

2013-03-07T19:38:00.002-08:00

Integration of various modules of e-Business Suite from an Oracle Service Contracts perspective.

With Inventory

Manages items associated with Oracle Service Contracts agreements and contracts: services, usages, warranties, subscriptions, and serviceable items

With Advanced Pricing

Supplies the information to price the services and usages sold on the contract.

With Order Management

Raises sales orders from which warranties and extended warranties are created.
Inventory is used to define serviceable products and service items (warranties and extended warranties)

With Quoting

Provides quotes for sales orders

With Receivables

Service Contract is integrated with the Oracle Receivables module for generating Invoices. In the Receivables we have to define Transaction sources and associated Transaction types which will be used for generating Invoices from Oracle SC. In the Service Contract we have to use the Accounting Rule, Payment Terms, and Invoicing Rule that will be used for the respective Contracts.

With Bills of Material

Associates warranties with serviceable items

With Install Base

Provides details of trackable items and the tracking of counters.

With CMRO (Complex Maintenance, Repair, and Overhaul)

Provides details of preventive maintenance programs, that may be associated with service contracts.

With TeleService

Manages service requests
Retrieves contract details for a customer such as account, end date, status,contract type& number on all of their contracts.
Looks for the preferred engineers for a particular service.
Has visibility to coverage levels & times, reaction & resolution times for coverage, billing rates and types for a specified coverage.

With iSupport

Provides customer access to service contracts to view entitlements, preferred technician, response and reaction times via self service mechanisms

With Depot Repair

Provides details for repairs, exchanges, replacements, and loaners, which may update the contract coverages.
Has visibility to coverage details.

With Field Service

Provides details about service and repairs to be carried out in the field.
Has visibility to coverage details

GL base tables descriptions

2010-02-23T15:00:00.000-08:00

GL_JE_HEADERS

GL_JE_HEADERS stores journal entries. There is a one–to–many relationship between journal entry batches and journal entries. Each row in this table includes the associated batch ID, the journal entry name and description, and other information about the journal entry. This table corresponds to the Journals window of the Enter Journals form. STATUS is ’U’ for unposted, ’P’ for posted. Other statuses indicate that an error condition was found. CONVERSION_FLAG equal to ’N’ indicates that you manually changed a converted amount in the Journal Entry Lines zone of a foreign currency journal entry. In this case, the posting program does not re–convert your foreign amounts. This can happen only if your user profile option MULTIPLE_RATES_PER_JE is ’Yes’. BALANCING_SEGMENT_VALUE is null if there is only one balancing segment value in your journal entry. If there is more than one, BALANCING_SEGMENT_VALUE is the greatest balancing segment value in your journal entry.

GL_JE_LINES

GL_JE_LINES stores the journal entry lines that you enter in the Enter Journals form. There is a one–to–many relationship between journal entries and journal entry lines. Each row in this table stores the associated journal entry header ID, the line number, the associated code combination ID, and the debits or credits associated with the journal line. STATUS is ’U’ for unposted or ’P’ for posted.

GL_JE_BATCHES

GL_JE_BATCHES stores journal entry batches. Each row includes the batch name, description, status, running total debits and credits, and other information. This table corresponds to the Batch window of the Enter Journals form. STATUS is ’U’ for unposted, ’P’ for posted, ’S’ for selected, ’I’ for in the process of being posted. Other values of status indicate an error condition. STATUS_VERIFIED is ’N’ when you create or modify an unposted journal entry batch.
The posting program changes STATUS_VERIFIED to ’I’ when posting is in process and ’Y’ after posting is complete.

GL_JE_HEADERS

GL_JE_HEADERS stores journal entries. There is a one–to–many relationship between journal entry batches and journal entries. Each row in this table includes the associated batch ID, the journal entry name and description, and other information about the journal entry. This table corresponds to the Journals window of the Enter Journals form. STATUS is ’U’ for unposted, ’P’ for posted. Other statuses indicate that an error condition was found. CONVERSION_FLAG equal to ’N’ indicates that you manually changed a converted amount in the Journal Entry Lines zone of a foreign currency journal entry. In this case, the posting program does not re–convert your foreign amounts. This can happen only if your user profile option MULTIPLE_RATES_PER_JE is ’Yes’. BALANCING_SEGMENT_VALUE is null if there is only one balancing segment value in your journal entry. If there is more than one, BALANCING_SEGMENT_VALUE is the greatest balancing segment value in your journal entry.

GL_JE_LINES

GL_JE_LINES stores the journal entry lines that you enter in the Enter Journals form. There is a one–to–many relationship between journal entries and journal entry lines. Each row in this table stores the associated journal entry header ID, the line number, the associated code combination ID, and the debits or credits associated with the journal line. STATUS is ’U’ for unposted or ’P’ for posted.

GL_JE_BATCHES

GL_JE_BATCHES stores journal entry batches. Each row includes the batch name, description, status, running total debits and credits, and other information. This table corresponds to the Batch window of the Enter Journals form. STATUS is ’U’ for unposted, ’P’ for posted, ’S’ for selected, ’I’ for in the process of being posted. Other values of status indicate an error condition. STATUS_VERIFIED is ’N’ when you create or modify an unposted journal entry batch.
The posting program changes STATUS_VERIFIED to ’I’ when posting is in process and ’Y’ after posting is complete.

Supplier Interface Staging table

2010-01-20T16:58:00.000-08:00

Staging table structure of supplier Interface-----

create table xx_ap_suppliers_stg --ap_suppliers_int
(
vendor_full_name varchar2(240) not null --VENDOR_NAME
,vendor_code varchar2(30) not null --SEGMENT1
,alt_vendor_name varchar2(320) --VENDOR_NAME_ALT
,active_vendor_since date --START_DATE_ACTIVE
,inactive_date date --END_DATE_ACTIVE
,vendor_type varchar2(30) --VENDOR_TYPE_LOOKUP_CODE
,domestic_or_international varchar2(25) --STANDARD_INDUSTRY_CLASS
,customer_num varchar2(25) --CUSTOMER_NUM
,parent_vendor_code varchar2(30) --
,small_scale_industry varchar2(1) --SMALL_BUSINESS_FLAG
,industry varchar2(25) --ORGANIZATION_TYPE_LOOKUP_CODE
,min_order_amount number --MIN_ORDER_AMOUNT
,inspection_reqd_flag varchar2(1) --INSPECTION_REQUIRED_FLAG
,receipt_reqd_flag varchar2(1) --RECEIPT_REQUIRED_FLAG
,qty_receipt_tolerance number --QTY_RCV_TOLERANCE
,days_early_receipt_allwd number --DAYS_EARLY_RECEIPT_ALLOWED
,days_late_receipt_allwd number --DAYS_LATE_RECEIPT_ALLOWED
,substitute_receipt_allwd varchar2(1) --ALLOW_SUBSTITUTE_RECEIPTS_FLAG
,unordered_receipts_allowed varchar2(1) --ALLOW_UNORDERED_RECEIPTS_FLAG
,request_id number(15)
,status varchar2(15)
,reject_code varchar2(1000)
);

create table xx_ap_supplier_sites_stg --ap_supplier_sites_int
(
vendor_code varchar2(30) not null --for reference to supplier
,vendor_site_code varchar2(15) not null --VENDOR_SITE_CODE
,vendor_site_code_alt varchar2(320) --VENDOR_SITE_CODE_ALT
,operating_unit varchar2(30) not null --ORG_ID
,inactive_date date --INACTIVE_DATE
,purchasing_site_flag varchar2(1) --PURCHASING_SITE_FLAG
,rfq_only_site_flag varchar2(1) --RFQ_ONLY_SITE_FLAG
,pay_site_flag varchar2(1) --PAY_SITE_FLAG
,address_line1 varchar2(240) --ADDRESS_LINE1
,address_line2 varchar2(240) --ADDRESS_LINE2
,address_line3 varchar2(240) --ADDRESS_LINE3
,address_line4 varchar2(240) --ADDRESS_LINE4
,city varchar2(25) --CITY
,district varchar2(150) --PROVINCE
,state varchar2(150) --STATE
,pin varchar2(20) --ZIP
,country varchar2(25) --COUNTRY
,landmark varchar2(560) --ADDRESS_LINES_ALT
,area_code varchar2(10) --AREA_CODE
,phone varchar2(15) --PHONE
,fax varchar2(15) --FAX
,vendor_internet_site varchar2(2000) --EMAIL_ADDRESS
,freight_terms varchar2(25) --FREIGHT_TERMS_LOOKUP_CODE
,payment_terms varchar2(50) --TERMS_NAME
,terms_date_basis varchar2(25) --TERMS_DATE_BASIS
,pay_date_basis varchar2(25) --PAY_DATE_BASIS_LOOKUP_CODE
,pay_group varchar2(25) --PAY_GROUP_LOOKUP_CODE
,payment_priority number --PAYMENT_PRIORITY
,invioce_currency varchar2(15) --INVOICE_CURRENCY_CODE
,payment_currency varchar2(15) --PAYMENT_CURRENCY_CODE
,invoice_amount_limit number --INVOICE_AMOUNT_LIMIT
,vat_code varchar2(20) --VAT_CODE
,preferred_payment_method varchar2(25) --PAYMENT_METHOD_LOOKUP_CODE
,vendor_bank_name varchar2(50) --
,bank_acct_number varchar2(50) --
,hold_unmatched_invoices varchar2(1) --HOLD_UNMATCHED_INVOICES_FLAG
,vat_regd_num varchar2(20) --VAT_REGISTRATION_NUM
,bank_charge_bearer varchar2(1) --BANK_CHARGE_BEARER
,l_segment1 varchar2(10)
,l_segment2 varchar2(10)
,l_segment3 varchar2(10)
,l_segment4 varchar2(10)
,l_segment5 varchar2(10)
,l_segment6 varchar2(10)
,l_segment7 varchar2(10)
,l_segment8 varchar2(10)
,p_segment1 varchar2(10)
,p_segment2 varchar2(10)
,p_segment3 varchar2(10)
,p_segment4 varchar2(10)
,p_segment5 varchar2(10)
,p_segment6 varchar2(10)
,p_segment7 varchar2(10)
,p_segment8 varchar2(10)
,request_id number(15)
,status varchar2(15)
,reject_code varchar2(1000)
);

create table xx_ap_sup_site_contacts_stg --ap_sup_site_contact_int
(
vendor_code varchar2(30) not null --reference to supplier
,vendor_site_code varchar2(15) not null --reference to site code
,prefix varchar2(5) --PREFIX
,first_name varchar2(15) --FIRST_NAME
,middle_name varchar2(15) --MIDDLE_NAME
,last_name varchar2(20) --LAST_NAME
,designation varchar2(30) --TITLE
,department varchar2(230) --DEPARTMENT
,email varchar2(2000) --EMAIL_ADDRESS
,area_code varchar2(10) --AREA_CODE
,phone varchar2(15) --PHONE
,alt_phone varchar2(15) --ALT_PHONE
,fax varchar2(15) --FAX
,inactive_date date --INACTIVE_DATE
,request_id number(15)
,status varchar2(15)
,reject_code varchar2(1000)
);

DATE FUNCTION to Return a start time

2010-01-20T16:41:00.000-08:00

FUNCTION get_oper_start_time(v_schedule_DATE DATE) RETURN DATE IS
BEGIN
RETURN to_DATE(to_char(v_schedule_DATE,'dd-mm-yyyy') || '08:00 AM', 'dd-mm-yyyy hh:mi AM');

EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000,'Exception (OTHERS) in get_oper_start_time '|| SQLERRM);
RETURN NULL;
END get_oper_start_time ;

Oracle Workflow Engine API

2010-01-20T15:10:00.000-08:00

The Oracle Workflow Engine:

Manages the state of activities for each process instance.

Is implemented in server-side PL/SQL. Combination of Tables/Views/Packages and procedures

Determines the next activity once a prerequisite activity completes.

Executes function activities automatically.

the Notification System to send notification messages

Supports results-based branches, parallel branches, loops, and sub-processes.

Can execute activities from non-savepoint environments such as database triggers and distributed transactions.

Can defer activities too costly to execute in real time to background engines for processing.

Maintains a history of completed activities.

Detects error conditions and executes error processes.

Initiating a Workflow Process
We Call the Workflow Engine APIs to initiate a workflow process.
Use the CreateProcess and StartProcess APIs if you want to perform additional tasks, such as setting item attributes, after creating and before starting the process.
If you do not need to perform any additional tasks, you can use the LaunchProcess API, which is a wrapper combining the CreateProcess and StartProcess APIs.

The procedure that executes the Workflow Engine APIs to initiate a process must identify the item type and item key of the process for these APIs. The item type and item key passed to these APIs uniquely identify an item and must be passed to subsequent API calls for each specific process.

Workflow Engine Processing
Upon starting a process, the Workflow Engine:

Identifies and executes the Start activity node

Executes the Start node if it is a function, notification, or process activity

Determines the next activity to transition to after completing the prerequisite activity or activities

Drives through the process

Automatically executes function activities and Send or Raise event activities
Pauses when it encounters a notification activity or blocking activity

Calls the Notification System to notify a performer

Transitions to the next activity after the performer completes the notification,
the blocking activity is completed, or the event message is received

when it encounters an End activity

Exception Handling
Use WF_CORE APIs to raise and catch errors in your PL/SQL procedures.

The Workflow Engine sets the status of the function activity to “ERROR” if:
The PL/SQL procedure raises an unhandled exception
The PL/SQL procedure returns a result beginning with “ERROR:”

If an activity encounters an error, information about the error is stored in the following columns in the WF_ITEM_ACTIVITY_STATUSES table, which are viewable from the Workflow Monitor.

ERROR_NAME

ERROR_MESSAGE

WORKFLOW ENGINE API’s
To start or run a workflow process

WF_ENGINE.CreateProcess creates a new runtime process for a work item.

WF_ENGINE.StartProcess begins execution of the specified process.

WF_ENGINE.LaunchProcess launches a specified process by creating the new runtime process and beginning its execution.

WF_ENGINE.SetItemOwner sets the owner of an existing item.

WF_ENGINE.SetItemUserKey sets a user-friendly identifier for an item.

WF_ENGINE.GetItemUserKey returns the user-friendly identifier assigned to an item.

WF_ENGINE.SetItemParent defines the parent/child relationship for master/detail processes.

WF_ENGINE.Event receives an event from the Business Event System into a workflow process.

WF_ENGINE.Background runs a background engine to process deferred and timed out activities and stuck processes.

WF_ENGINE.CreateForkProcess forks a runtime process by creating a new process that is a copy of the original.

WF_ENGINE.StartForkProcess begins execution of the specified new forked process.

To communicate attribute information to the Workflow Engine

WF_ENGINE.SetItemAttrText, WF_ENGINE.SetItemAttrNumber, WF_ENGINE.SetItemAttrDate, and WF_ENGINE.SetItemAttrEvent set the value of an item type attribute in a process.

WF_ENGINE.SetItemAttrTextArray, WF_ENGINE.SetItemAttrNumberArray, and WF_ENGINE.SetItemAttrDateArray set the values of an array of item type attributes in a process.

WF_ENGINE.GetItemAttrText, WF_ENGINE.GetItemAttrNumber, WF_ENGINE.GetItemAttrDate, and WF_ENGINE.GetItemAttrEvent return the value of an item type attribute in a process.

WF_ENGINE.GetItemAttrInfo returns information about an item attribute, such as its type and format.

WF_ENGINE.AddItemAttr adds a new item attribute to the runtime process.

WF_ENGINE.AddItemAttrTextArray, WF_ENGINE.AddItemAttrNumberArray, and WF_ENGINE.AddItemAttrDateArray add an array of new item type attributes to the runtime process.

WF_ENGINE.GetActivityAttrText, WF_ENGINE.GetActivityAttrNumber, WF_ENGINE.GetActivityAttrDate, and WF_ENGINE.GetActivityAttrEvent return the value of an activity attribute in a process.

WF_ENGINE.GetActivityAttrInfo returns information about an activity attribute, such as its type and format.

To communicate state changes to the Workflow Engine

WF_ENGINE.CompleteActivity notifies the engine that the specified activity has been completed for the item, identifying the activity by the activity node label name.

WF_ENGINE.CompleteActivityInternalName notifies the engine that the specified activity has been completed for the item, identifying the activity by its internal name.

WF_ENGINE.BeginActivity determines if the specified activity can currently be performed and raises an exception if it cannot.

WF_ENGINE.AssignActivity assigns an activity to another performer.

WF_ENGINE.GetActivityLabel returns the instance label of an activity, given the internal activity instance identification.

WF_ENGINE.AbortProcess aborts process execution and cancels outstanding notifications.

WF_ENGINE.SuspendProcess suspends process execution so that users cannot transition items to new activities.

WF_ENGINE.ResumeProcess returns a suspended process to normal execution status.

WF_ENGINE.HandleError handles any activity that has encountered an error. This API can also be called for any arbitrary activity in a process to roll back part of the process to that activity.

WF_ENGINE.ItemStatus returns the status and results for the root process of the specified item instance.

FND_USER LOCK TABLE

2010-01-20T15:00:00.000-08:00

The SQl will tell you the FND_USER.USER_NAME of the person that has locked a given table in Oracle APPS.

The column named "module" will tell you the name of the Form Function or the Concurrent Program Short name which has aquired a lock onto that table.

SELECT c.owner
,c.object_name
,c.object_type
,fu.user_name locking_fnd_user_name
,fl.start_time locking_fnd_user_login_time
,vs.module
,vs.machine
,vs.osuser
,vlocked.oracle_username
,vs.sid
,vp.pid
,vp.spid AS os_process
,vs.serial#
,vs.status
,vs.saddr
,vs.audsid
,vs.process
FROM fnd_logins fl
,fnd_user fu
,v$locked_object vlocked
,v$process vp
,v$session vs
,dba_objects c
WHERE vs.sid = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
AND c.object_name LIKE '%' || upper('&tab_name_leaveblank4all') || '%'
AND nvl(vs.status
,'XX') != 'KILLED';

Concept of Key Flexfield and Descriptive Flexfield

2010-01-20T13:42:00.000-08:00

Key Flexfield is used to describe unique identifiers that will have a better meaning than using number IDs. e.g a part number a cost centre etc
Desc Flex is used to just capture extra information.
Key Flexfields have qualifiers whereas Desc Flexfields do not.
Desc Flexfields can have context sensitive segments while Key flexfields cannot.

To desing key flex field one must follow the below step....

Identifying Key flexfields that are required for you Oracle Applications installation
Designing a Key flexfield structure
Selecting the appropriate Key flexfield to define
Defining structure information for the Key flexfield
Defining segment attributes
Defining flexfield qualifiers
Defining segment qualifiers
Implementing optional features as needed

Lets say we are defining the structure for an Accounting Flexfield. Query for the Flexfield Title “Accounting Flexfield” using the Flexfield Title window will appper. If there are already existing structures for Accounting Flexfields, they would be listed in the Structures window .
Now
Lets say we want to define a new structure for the Accounting Flexfield with the structure code as “NEW_ACCOUNTING_STRUCTURE”. Enter the code name in the Code window and the title as “New Accounting Flexfield Struc” in the Title window. Enter an optional description for the new structure which may include the purpose of the new structure. Save the changes.

There are few check buttons on the form which can be left unchecked/checked.

The Enabled checkbox is checked by default. You can optionally uncheck it. You cannot delete a Key flexfield structure, but you can disable it by unchecking the Enabled checkbox. Next, you may select a Segment Separator other than the default period. Your choices are period, dash, pipe, or any custom character. You must have a segment separator.

Next, you can select whether you want to Cross-Validate Segments. Unchecking this checkbox will disable cross-validation for this Key flexfield structure. You can then identify whether you want to Freeze Rollup Groups. If you mark this checkbox, you will not be able to change the assigned rollup groups in the Define Segment Values form. As a last step, you can determine whether you want to Allow Dynamic Inserts for Key flexfield structures. If you do not allow dynamic inserts, you must create all flexfield combinations in the combination form.

Debashree's Blog

SQL for AR Invoice Balance

O2C

GL Important Concepts and Questions

2. Which tables store segment values and descriptions?

Inventory Pending material transaction -Period close issue

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

Terminate one concurrent Request:

Parent Child relationship for Accounting Flexfield

Accounting Flow

ACCOUNTING Debit /Credit

Balance Sheet ,Income Stmt n Cash Flow Basic logic

Balance Sheets

Income Statements

Earnings Per Share or EPS

Cash Flow Statements

Operating Activities

Investing Activities

Financing Activities

Important Reports on Receivables

Account Status Report

Few Important Query

Get Calendar Information

Get Depreciation Period :

How to check AP ,GL, AR periods are closed

AP to GL Transfer

Find Supplier Site Information :

Prepayment AP invoice Details

Supplier Bank Account Information

AGIS R12 Setup

Release 12- Advanced Global Intercompany System Setup – Part 1

Mass Addition on FIxed Asset

PO Matching Setting in Purchase Order Shipment Line(2-way,3-way,4-way)

PO Matching Setting in Purchase Order Shipment Line

Ramification of Invoice Match to PO and Invoice Match to Receipt

How to find Trace File Names in Oracle apps based on request id

Delete Cache memory in Oracle Apps R12

Delete Cache memory in Oracle Apps R12

Oracle Fixed Asset key tables

FIXED ASSETS

Enable Credit Card related Field in Oracle Order Management.

Integration of Service Contracts

GL base tables descriptions

Supplier Interface Staging table

DATE FUNCTION to Return a start time

Oracle Workflow Engine API

FND_USER LOCK TABLE

Concept of Key Flexfield and Descriptive Flexfield

`Terminate one concurrent Request:`