Thursday, 28 March 2013

Simple Accounting Entries in Asset

Asset Purchased entry
Asset Clearing a/c Dr
To AP accrual a/c Cr
Asset Adding Entry Asset Cost a/c Dr
To Asset Clearing a/c Cr
Asset Changes Entry Asset Cost a/c Dr
To asset clearing a/c
Asset transferred Entry Asset Cost a/c Dr
Accumulated Depreciation a/c Dr
Asset a/c Dr
To Accumulated Depreciation a/c Cr
Asset Revalue Entry Asset Cost a/c Dr
To accumulated Depreciation a/c Cr
To revaluation Reserve a/c Cr
Asset Retirement Entry Accumulated Depreciation a/c Dr
Proceeds of Sale a/c Dr
To asset gain a/c Cr
To gain A/c Cr
Asset Removed Entry Accumulated Depreciation a/c Dr
Proceeds of Sale a/c Dr
To asset gain a/c Cr
To gain a/c Cr
To Cost of Removal a/c Cr

Simple Accounting Entries in Receivables

Standard Transaction
Receivables a/c Dr
To Revenue a/c Cr
To Tax a/c Cr
To freight Cr
Revenue recognization Invoice with rules (in 1st period of rule)
Bill in Advance
Receivables a/c Dr
To Revenue a/c Cr
To Tax a/c Cr
To freight Cr
In each period when revenue is recognized
Unearned revenue a/c Dr
To revenue Cr
Invoice With rules
Bill in Arrears
Unbilled Receivables a/c Dr
To revenue a/c Cr
In each period when revenue is recognized
Receivables a/c Dr
To Unbilled Receivables a/c Cr
To Tax a/c Cr
To freight Cr
Debit memo Entry Receivables a/c Dr
To Revenue a/c Cr
To Tax a/c Cr
To freight Cr
Credit memo Entry
Against Invoice, chargeback
Revenue a/c Dr
Tax a/c Dr
Freight Dr
To Receivables a/c Cr
When credit memo adjusted with Invoice, Chargeback,
Receivables a/c Dr
To Receivables a/c Cr
Credit memo against commitmentRevenue a/c Dr
To Receivables a/c Cr
On a/c Credit Revenue a/c Dr
Tax a/c Dr
Freight Dr
To Receivables a/c Cr
Deposit Entry Receivables a/c Dr
To unearned Revenue a/c Cr
Receivables a/c Dr
To Revenue a/c Cr
To Tax a/c Cr
To Freight a/c Cr
Adjustment on Invoice against deposit Unearned revenue a/c Dr
To Receivables a/c Cr
Guarantee Entry Unbilled receivables
To unearned Revenue a/c Cr
Adjustment on Invoice against on GuaranteeReceivables a/c Dr
To Revenue a/c Cr
To Tax a/c Cr
To Freight a/c Cr
Unearned revenue a/c Dr
To unbilled Receivables a/c Cr
Charge back Entry CB Adjustment a/c Dr
To Receivables a/c Cr
CB a/c Dr
To CB Adjustment a/c Cr
Adjustments of Charge back
Positive adjustment
Receivables a/c Dr
To Adjustment a/c Cr
Negative adjustmentAdjustment a/c Dr
To Receivables a/c Cr

Simple Accounting Entries in Payables

At the time of invoice (For inventory item)
Inventory AP accrual a/c Dr
To AP liability a/c Cr
At the time of invoice (For Expense item) Charge a/c Dr
To AP liability a/c Cr
At the of Payment AP liability a/c Dr
To Cash Clearing a/c
Prepayment Entry Prepaid a/c Dr
To AP liability a/c Cr
AP liability a/c Dr
To Cash Clearing a/c Cr
Cash Clearing a/c Dr
To Cash or bank a/c Cr
Adjustment of prepayment against Invoice AP liability a/c Dr
To prepayment a/c Cr
Credit memo or debit memo entry AP liability a/c Dr
To charge a/c or Inv AP accrual a/c
With holding Tax Entry AP liability a/c Dr
To Withholding Tax a/c Cr
Releasing With holding Tax Withholding Tax a/c Dr
To AP Liability a/c
Purchase price variance Entry Material a/c Dr
To Receiving a/c Cr
To PO price Variance a/c Cr
If standard price is less than PO price
Material a/c Dr
To PO price variance a/c Cr
To Receiving a/c Cr
Currency Gain Entry AP liability a/c Dr
To realized gain a/c Cr
To Cash a/c Cr

Currency LossAP liability a/c Dr
Realized Loss a/c Dr
To Cash a/c Cr
For future dated Payment AP liability a/c Dr
To Future dated payment a/c Cr
At payment Maturity Future Payment a/c Dr
To cash a/c Cr
Discount takenAP Liability a/c Dr
To System discount a/c Cr
To cash a/c Cr
Cross currency Entry Charge a/c Dr
To Liability a/c Cr
Cross currency Payment time Entry Liability a/c Dr
Rounding a/c Dr
To cash clearing a/c

Simple Accounting Entries in Purchasing

At the time of receiving
Material value a/c Dr
To Inventory AP accrual account Cr
At the time of invoice Inventory AP accrual a/c Dr
To AP liability a/c Cr
At the time of Payment AP liability a/c Dr
To Cash Clearing a/c Cr
At the time of Clearing Cash clearing a/c Dr
To Cash a/c Cr
Stock sent to the sub inventory Material a/c Dr
To receiving a/c Cr
Goods in TransitGoods in transit a/c Dr
To Inventory AP accrual a/c Cr
Receiving of Stock Receiving a/c Dr
To Inventory AP accrual a/c Cr
Inspection of stockInspection a/c Dr
To Receiving a/c CR
Material a/c Dr
To inspection a/c CR
Encumbrance for requisition, PO, Invoice PO charge account a/c Dr
To reserve for Encumbrance a/c Cr

6 Phases of AIM & its Definations

AIM Methodology 6 Phases

1- Definition phase
2- Operational analysis
3- Solution design
4- Build
5- Transition
6- Production cutover.
Definition phase First, they should initiate the project.
Client and the consulting company should have the clear understanding of the project what they are dealing with,
And they should know what the technology, scope is and understands the project from all the corners, they should access and manage risks and complete the project Plan.
Operational analysis Produce accurate information for the business areas being addressed.Define the detail functional data and operational requirements that the new application system must support.
Train the project team members
Start high level mapping and identify the gaps
Solution designtranslate finalized TOBE processes to oracle.
Complete final mapping and close every gap.
Build Consultant will configure the conference room pilot testing for the unit testing, known as CRP.
Business system testing scripts are formulated at this phase in preparation for the UAT.
Testing of Interfaces, Data conversions and customizations.
Perform integration testing and system load testing
.Transition Conduct end user training
Execute migrate activities
Setup production environment.
These are the things done in Transition
Before u go in to the production there will be black hole period.
Production Perform production cutover,
After the go live the consultants will support the client through the successful completion of one full business cycle.

Purging Step in WIP & BOM

1- Close Discrete jobs ( before that there should not be any pending Move transactions,Resource transaction & uncosted transactions against this job)
2- Once job Closed we can purge the job ( Job can be purge for which the last effecticity date falls in closed accounting period.
3-Purge report will complete with normal but it will give the below message in it.

Exception :
cannot purge headers . Foreign Key to table discrete job exists in MTL_MATERIAL_TRANSACTIONS & MTL_TRANSACTIONS_ACCOUNTS table.

It means there were related transactions are lying in Inventory. If you want to purge those transaction also .

4- Then perform Transaction purge in inventory by giving the Purge date as closed inventory period date.

5- Once this get completed, Now again run the Purge program for the job. these time entire related informations against the job will be purged from system.

6- WIP Purge will delete the records from following tables
          i) MRP_RELIEF_INTERFACE
         ii)WIP_PERIOD_BALANCES
        iii)WIP_DISCRETE_JOBS
         iv)WIP_ENTITIES
         v)WIP_MOVE_TRANSACTIONS
        vi)WIP_OPERATION_RESOURCES
       vii)WIP_TRANSACTION_ACCOUNTS
       viii)WIP_REQUIREMENT_OPERATIONS
       ix) WIP_REQ_OPERATION_COST_DETAILS
       x) WIP_OPERATIONS
       xi) WIP_TRANSACTIONS 

7- Once Transaction Purge & WIP Purge completed , we can delete BOM & ROUTING ( But for that all the discrete jobs used this Bill and Routing has to be purged even if its in closed status also system will  not allow us to purge).

8- Create delete Group for both Bill & Routing

9- Execute the Check Group to validate whether the group satisfies all delete constrints

10- Once Check Group completed check the error status from Delete group window, and if there is no more error proceed with Delete Execution.

Wednesday, 27 March 2013

AR INVOICE FREQUENTLY ENCOUNTERED ERRORS:

1. Please Correct Revenue (or Receivable or Freight or Tax) Account Assignment A: AutoAccounting is used to determine the accounts when the distribution table is not populated. When AutoInvoice rejects a line with this error, it generally prints the account with the missing segment(s).

Menu: Setup>Transactions>AutoAccounting
Query the account mentioned in the above error and note the setup for that missing segment.

If it is based on:
i) Transaction Type - Menu: Setup>Transaction>Transaction Types,
and verify that all accounts are populated.
ii) Salesperson - Menu: Setup>Transactions>Salespersons, and verify
that all accounts are populated.
iii) Standard Lines - For an Inventory Item Navigate to
Menu: Setup>Transactions>Items>Inventory items, and verify that the 'Sales Account' is populated in the 'Invoicing' alternate region.
For a Memo Line, Navigate to Setup =>Transactions =>Memo Lines, and make sure that 'Revenue Account' is populated.
iv) Taxes – Go to Tax Managers Responsibility
Go to Taxes and query for your tax.
Here go to Tax Accounts and verify all accounts are populated.

Additionally the following can be checked:
Setup of Cross Validation Rules. Run Cross validation Listing Report in GL to identify the cross validation rule stopping the import of this line.
2. Invalid Salesrep Number A: This error is seen in 12.0.5 and log File Shows AR_RAXTRX_SALESREP_INACTIVE.
This is a known issue in 12.0.5.
Please refer Note 727200.1 : R12 RAXTRX Fails With Invalid Salesrep Number And AR_RAXTRX_SALESREP_INACTIVE 3. You must supply payment terms for your non-credit transaction A: Payment term cannot be present for a credit memo. TERM_ID and TERM_NAME must be null if you are trying to import a credit transaction.

4. a) Invalid Warehouse ID (WAREHOUSE_ID)
b) Unable to derive a gl date for your transaction. Please ensure that your transaction is in a gl period which you have defined
A:
When you get both these errors at the same time, please check whether your gl period is open for the date seen in the columns GL_DATE, SHIP_DATE_ACTUAL and SALES_ORDER_DATE in the table RA_INTERFACE_LINES_ALL 5. Invalid Warehouse ID (WAREHOUSE_ID) A: Please do the following:
Using appropriate Order Management responsibility...
Setup>System Parameters>Values
Select the appropriate Operating Unit
For Category select Generic Parameters
Scroll down and verify your Item Validation Organization 6. Duplicate Transaction Flexfield A: This error shows up in 2 scenarios:
i) The combination of fields INTERFACE_LINE_CONTEXT, INTERFACE_LINE_ATTRIBUTE1, INTERFACE_LINE_ATTRIBUTE2, …, INTERFACE_LINE_ATTRIBUTE15 have duplicate values in the table RA_INTERFACE_LINES_ALL
ii) There is already one transaction in AR with the same Line Transaction Flexfield. The combination of fields INTERFACE_LINE_CONTEXT, INTERFACE_LINE_ATTRIBUTE1, INTERFACE_LINE_ATTRIBUTE2, …, INTERFACE_LINE_ATTRIBUTE15 already exists in table RA_CUSTOMER_TRX_LINES_ALL. 7. Cannot get remit to address A: A "Default Value" should be assigned for the Country and State with a blank zip code range. By adding this "default value" will eliminate the user from having to enter every possible Country and State combination that may be used. Also, by using this "default value" items will pass validation without running into an error because the remit-to address is not defined.

You can only have one default remit-to address defined.

Navigation: Setup-->Print-->Remit-to Addresses
Set up the default value for the remit to address.
i) Go to Setup->Print->Remit-To Addresses.
ii) Query up the address that will be the default address
iii) Go to the "Receipts From" section of the form.
iv) Put the cursor in the Country field and click on the LOV.
v) Select the value "Default value"
vi) Put cursor in the State field and click on LOV.
vii) Select the value "Default value"
viii) Save 8. a) Invalid bill to customer id, name ORIG_SYSTEM_BILL_CUSTOMER_ID
b) bill to address id must exist in Oracle Receivables and must be assigned to the bill to customer ORIG_SYSTEM_BILL_ADDRESS_ID
A:

This can be done by querying up the customer on the customer standard form
i) Navigation: Customers > Standard
ii) Query up the customer
iii) Click on the address in the alternate region
iv) Place cursor on the correct address
v) Click on the open button, will take you to the business purpose screen
vi) Make sure that there is a usage of bill to already set
vii) Make sure that at least one of the bill to's is active

If this is setup, then please check that the value in the interface table RA_INTERFACE_LINES_ALL for columns ORIG_SYSTEM_BILL_CUSTOMER_ID and ORIG_SYSTEM_BILL_ADDRESS_ID has a value that is seen in columns CUST_ACCT_SITE_ID and CUST_ACCOUNT_ID in the table HZ_CUST_ACCT_SITES_ALL. 9. Invoice lines with the same invoice number have been separated by the grouping process, causing duplicate invoice numbers. A: Here, AutoInvoice is trying to separate your lines into different invoices due to grouping rule settings. For further details, Please refer to the 3rd question of this FAQ. "Why is AutoInvoice splitting one order into two invoices? "

AR Auto Invoice FAQ (Source from Metalink)

1-Why is AutoInvoice splitting one order into two invoices?
A:
AutoInvoice uses grouping rules to group invoices, credit memos and debit memos. Grouping rules contain various transaction attributes (columns in RA_INTERFACE_LINES_ALL table) that must be identical for the same transaction.
There are 2 kinds of attributes: mandatory and optional. You can only add or drop optional attributes. Please see the User's Guide for a listing of all columns included in the mandatory attributes and all the columns that can be added as optional attributes.
Check the grouping rule that you are using. You may have defined an optional attribute that is different for two lines of the same invoice, and is, therefore, causing a second invoice to be generated. Check both optional and mandatory attributes of the two lines.
If AutoInvoice is generating one invoice for multiple orders, then you may not have defined sales order as an optional attribute.
The Grouping Rule that is used by AutoInvoice is attached in your Batch source :
Navigation: Setup -> Transactions -> Sources -> AutoInvoice tab

Grouping Rule can be checked at:
Navigation: Setup -> Transactions -> AutoInvoice -> Grouping Rule

2-What is the suggested setting for the 'Log file message level'? A:
For day-to-day business needs and to improve performance, set this to 0. If AutoInvoice errors out, set the message level to 3 to get detail messages in the log to help debug the problem.

Message Level 0 gives the following entries in the log file:
Product Version
Program Name
AutoInvoice Start Time
AutoInvoice Concurrent Request Arguments
Error and Warning Messages
AutoInvoice End Time
AutoInvoice Logical Steps

Message Level 1 gives you all of the above entries plus:
Time- Stamped function labels

Message Level 2 gives you all of the above entries plus:
Sizes of Allocated Arrays
Dynamic SQL Statements
Number of Rows Updated, Inserted and Deleted

Message Level 3 gives you all of the above entries plus:
Method IV SQL Array Values

If the errors are regarding AutoAccounting, message level can be set to 10 or above to get more detailed AutoAccounting messages in the log. 3-How can a credit memo (from legacy) be applied against an invoice using AutoInvoice? A:
To link a credit memo to an invoice there are 2 options:

i) Populate REFERENCE_LINE_ID on RA_INTERFACE_LINES_ALL with the CUSTOMER_TRX_LINE_ID of the invoice.
OR
ii) Populate REFERENCE_LINE_ATTRIBUTE1 to 15 with the INTERFACE_LINE_ATTRIBUTE1 to 15 of the invoice.
You also need to populate REFERENCE_LINE_CONTEXT with INTERFACE_LINE_CONTEXT of the invoice. INTERFACE_LINE_CONTEXT and INTERFACE_LINE_ATTRIBUTE1 to 15 are stored in RA_CUSTOMER_TRX_LINES_ALL.
To create an on-account credit (i.e. not linked to an invoice) do not populate REFERENCE_LINE_ID, REFERENCE_LINE_ATTRIBUTES or REFERENCE_LINE_CONTEXT.
4-Can Adjustments be imported through AutoInvoice?
A:
No. Only Invoices, Credit Memos, Debit Memos, and On-account Credits can be imported through AutoInvoice. The adjustments can be imported by using Adjustment API's. This API allows users to create, approve, update, and reverse adjustments for invoices using simple calls to PL/SQL functions 5-How is the Tax code derived in AutoInvoice? A:
Release 12 uses E-business Tax for calculating tax for AutoInvoice.

i) For newly setup Release 12 instances (Where Tax Regime Determination is set to Determine Tax Regime) Tax code is derived based on your Regime to Rate flow settings for that Operating Unit / Legal Entity

ii) For upgraded Release 12 instances (Where Tax Regime Determination is set to STCC)
Tax is calculated like 11i. 6-Can changes be made to existing invoices via AutoInvoice? A:
No. You can only create credit memos and apply them to existing invoices if they are still open (or if Allow Over application is checked for that Transaction Type).
You cannot update existing invoices. Once a transaction in AR has any activity against it you cannot update it manually. Activity is defined as cash applied, credit memo applied, adjustment, posting to GL and, printing. 7-Once the transactions have been imported successfully into AR using AutoInvoice, how can they be purged from the interface tables? A:
If the system option 'Purge Interface Tables' is set to Yes, data that has been validated and successfully imported in AR will be purged automatically. If the purge option is set to No, you can submit the AutoInvoice Purge Program from the Run AutoInvoice form to purge the interface tables. This program will only purge rows that have been processed successfully.

Navigation to check the 'Purge Interface Tables' option:
- Menu: Setup>System>System Options
- Alternate region 'Trans and customers'
- 'Purge Interface Tables' checkbox

Navigation to submit the AutoInvoice purge program:
- Menu: Interface>AutoInvoice
- Request name: AutoInvoice Purge Program 8-How often can you run AutoInvoice? A:
AutoInvoice can be run as many times as you like. It can run at anytime of the day, month, year. You need to set the rules for the running of this process based on your company's business needs. You can also schedule to run AutoInvoice automatically at regular intervals. 9-How can the errors in the interface table lines be corrected? A:
The errors in the interface tables can be corrected with the help of the
AutoInvoice Validation Report and the AutoInvoice Errors window, which
displays records that failed AutoInvoice validation. Depending on the error
you may need to make changes either in Receivables, or to your feeder program or to the imported records in the interface tables.

Menu: Interfaces>Control>AutoInvoice>Interface Lines

Interface Lines Window - This window lets you see all of the interface lines
that have been processed but rejected by AutoInvoice. You can view and edit data in this window.

Menu: Interfaces>Control>AutoInvoice>Interface Exceptions

Interface Errors Window - This window lets you see all of the errors generated by AutoInvoice. This window is read-only, but you can drill down to view errors in more detail, and modify data in these windows. 10-How does the GL date get derived? A:
For invoices without Rules:

AutoInvoice first uses the GL date in the interface table (RA_INTERFACE_LINES_ALL), if one exists.

If one does not exist then it is derived as follows:

If the Derive Date box is checked for your batch source
(Menu: Setup>Transactions>Sources, query your batch source, alternate region Accounting),
AutoInvoice first uses the ship date in the interface table. If the ship date does not exist, AutoInvoice uses the sales order date. If the sales order date does not exist, AutoInvoice uses the date entered in the Submit Request window when AutoInvoice was run.

If the Derive Date box is not checked for your batch source, AutoInvoice uses the date entered in the Submit Request window when AutoInvoice was run.

For Invoices with Rules:

AutoInvoice first uses the GL date in the interface table
(RA_INTERFACE_LINES_ALL), if one exists.

If the Invoicing Rule is 'Bill In Advance', AutoInvoice uses the Rule Start Date for the GL date.

If the Invoicing Rule is 'Bill in Arrears' and the invoice line has an accounting rule of type 'Accounting, Fixed Duration' and a period of 'Specific Date', AutoInvoice computes an end date using the earliest accounting rule date.

For all other Accounting rules, AutoInvoice computes an ending date for each invoice line, and then takes the earliest date of these lines and uses it as the GL date of the invoice.

If your invoice does not use a fixed rule accounting duration and the rule start date is not provided in the interface table, GL date is derived as follows:

If the Derive Date box is checked in batch source options, AutoInvoice first uses the ship date. If the ship date does not exist, AutoInvoice uses the sales order date. If the sales order date does not exist, AutoInvoice uses the date entered in the Submit Request window.

If the Derive Date box is not checked, AutoInvoice uses the date entered in the Submit Request window.

11-AutoInvoice is taking too long to run. How can I improve its performance? A:
Although there are many reasons which can contribute towards poor AutoInvoice performance, the most common ones are as follows:

a) Indexes have not been defined for the Line Transaction Flexfield on
RA_INTERFACE_LINES_ALL

b) Custom indexes or triggers: If any custom indexes or triggers have been defined, drop them to see if performance improves.

c) Run Gather Schema Statistics from the System Administrator responsibility

d) Check the log file message level setting in the Accounts Receivable
System Parameters. (Navigation: Setup -> System -> System Options -> Trans and customers tab)
If it is set to 3, change it to a lower value.

Message level 3 is used when AutoInvoice fails in order to generate a log file with the highest possible level of information. 12-The Import program (RAXTRX) does not get spawned when I run AutoInvoice. What could be the reason? A:
In this case the Master Program (RAXMTR) may complete without error but will not spawn the Import Program (RAXTRX). The log file for RAXMTR will show the 'No data Found' message.

AutoInvoice executes a statement similar to the following to select records for importing:

SELECT COUNT(*)
FROM RA_INTERFACE_LINES_ALL
WHERE NVL(INTERFACE_STATUS, '~') != 'P'
AND (REQUEST_ID IS NULL)
AND BATCH_SOURCE_NAME = ‘&batch_source_name’
AND LINK_TO_LINE_ATTRIBUTE1 IS NULL
AND LINK_TO_LINE_ATTRIBUTE2 IS NULL
.
.
AND LINK_TO_LINE_ATTRIBUTE15 IS NULL

Please ensure:
i) There are records present in the interface table RA_INTERFACE_LINES_ALL to be imported.
ii) The column INTERFACE_STATUS and REQUEST_ID must be null 13-How do you default the batch source on the AutoInvoice submission screen? A:
Go to Concurrent -> Program -> Define
Query for Short Name RAXMTR
Click on Parameters
Go to Batch Source ID
Change the default type to Constant
Change the Default value to the NAME (not id) of the value you want "Order Entry" (it is case sensitive).
Save. 14-What is the difference between the ATTRIBUTE_CATEGORY and the HEADER ATTRIBUTE_CATEGORY columns in the RA_INTERFACE_LINES_ALL table.? A:
They are for 2 different descriptive flexfields.

RA_INTERFACE_LINES_ALL.ATTRIBUTE_CATEGORY and ATTRIBUTE1-15 are used to
store values for the descriptive flexfield, 'Invoice Line Information', which appears at the invoice line level. The value in these columns will be transferred to RA_CUSTOMER_TRX_LINES_ALL.ATTRIBUTE_CATEGORY and ATTRIBUTE1-15 if the transaction passes validation.

RA_INTERFACE_LINES_ALL.HEADER_ATTRIBUTE_CATEGORY and HEADER_ATTRIBUTE1-15 are used to store values for the descriptive flexfield, 'Invoice Information',
which appears at the invoice level. The value in these columns will be transferred to RA_CUSTOMER_TRX_ALL.ATTRIBUTE_CATEGORY and ATTRIBUTE1-15 if
the transaction passes validation.

Since they are descriptive flexfields, you can set them up to store any value. 15-How to import gapless transaction numbers with AutoInvoice? A:
Oracle does not guarantee 100% gapless transaction numbering even with the cache on the sequence turned off. You can make the invoice numbering follow your document sequencing and this will bring you closer to 100% gapless numbering.

Setup Steps:
i) Define a Transaction Type:
Navigation: Setup -> Transactions -> Transaction Type ii) Define a Batch Source that will copy the document number to the transaction number:Navigation: Setup -> Transactions -> Sources
You may choose to manually number or automatically number your invoice since the transaction number will be replaced by the document number later.
Check the box Copy Document Number to Transaction Number.
Save your work.

iii) Define Document sequencing in System Administrator:a. Define Sequence
Navigation: Application -> Document -> Define
Choose sequence type of Gapless
Note: It is not necessary to define a separate sequence for each transaction you enter. You may decide to define only 3 sequences one for transactions, one for receipts and one for adjustments.
.
b. Define Document Categories
Navigation: Application -> Document -> Categories
Ensure that the name of the specific transaction type, payment method, and receivables activity is in the CODE field.
For transactions the table is RA_CUSTOMER_TRX
For receipts the table is AR_CASH_RECEIPTS
For Adjustments the table is AR_ADJUSTMENTS
Note: You must define a category for every transaction type, payment method, and receivables activity that you have defined. The categories are based on table names.

From Release 11i onwards for any new defined Transaction Type or Payment Method, the Document Category is automatically generated with the correct code matching the name.
For Types defined on prior releases those do not exist and you need to do this manually.

c. Assign the Sequences to the categories
Navigation: Application -> Document -> Assign
The category you created will be in the list of values as well as the sequence that you defined. iv) Set the profile options in System Administrator:
Navigation: Profile -> System
Check site and enter Application Oracle Receivables
Query the profile Sequential Numbering
This should be set to Partially Used or Always Used depending on your setup.
Note: Depending on your business requirements you can also choose to set this profile option at responsibility level.)v) Set the "Document Number Generation Level" in Receivables ResponsibilityNavigation: Setup -> System -> System Options -> Trans and Customers tab
Set the option "Document Number Generation Level" to either 'when completed' or 'when saved' that will generate the document sequence for the transaction either when you complete the transaction or save the same respectively.16-How can a manual tax line be imported using AutoInvoice? A:
For importing a manual tax line, Please ensure the following tax related columns are populated along with the other required columns for the Line Transaction Flexfield and Link to Line attributes:

TAX_CODE
TAX
TAX_RATE_CODE
TAX_REGIME_CODE
TAX_STATUS_CODE
TAX_JURISDICTION_CODE (optional) 17-Why AutoInvoice does not calculate tax automatically? A:
Please ensure the following:
1) The Tax code that you have defined has the "Make Tax available for Transactions" checkbox checked
2) The Tax falls in the jurisdiction defined for the tax code
3) The tax has a default jurisdiction, default rate and default status defined.
4) There are no violation of the rules defined
5) The tax gets calculated when you create a manual invoice.

Oracle Apps 11i - R12 Migration – Queries for Identifying & Migrating Components

 
1. Responsibilities Listing
SELECT (SELECT application_short_name
FROM fnd_application fa
WHERE fa.application_id = frt.application_id) application,
frt.responsibility_id, frt.responsibility_name
FROM apps.fnd_responsibility_tl frt;

 
2. Menus Listing
SELECT DISTINCT a.responsibility_name, c.user_menu_name
FROM apps.fnd_responsibility_tl a,
apps.fnd_responsibility b,
apps.fnd_menus_tl c,
apps.fnd_menus d,
apps.fnd_application_tl e,
apps.fnd_application f
WHERE a.responsibility_id(+) = b.responsibility_id
AND a.responsibility_id = ‘20538’
AND b.menu_id = c.menu_id
AND b.menu_id = d.menu_id
AND e.application_id = f.application_id
AND f.application_id = b.application_id
AND a.LANGUAGE = 'US';

3. Submenu And Function Listing
SELECT c.prompt, c.description
FROM apps.fnd_menus_tl a, fnd_menu_entries_tl c
WHERE a.menu_id = c.menu_id AND a.user_menu_name = 'F4 UK
PAY Navigator';

4. User And Assigned Responsibility Listing
SELECT UNIQUE u.user_id, SUBSTR (u.user_name, 1, 30) user_name,
SUBSTR (r.responsibility_name, 1, 60) responsiblity,
SUBSTR (a.application_name, 1, 50) application
FROM fnd_user u,
fnd_user_resp_groups g,
fnd_application_tl a,
fnd_responsibility_tl r
WHERE g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
ORDER BY SUBSTR (user_name, 1, 30),
SUBSTR (a.application_name, 1, 50),
SUBSTR (r.responsibility_name, 1, 60);

5. Responsibility And Assigned Request Group Listing
SELECT responsibility_name responsibility, request_group_name,
frg.description
FROM fnd_request_groups frg, fnd_responsibility_vl frv
WHERE frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name

6. Profile Option With Modification Date and User
SELECT t.user_profile_option_name, profile_option_value,
v.creation_date,
v.last_update_date,
v.creation_date - v.last_update_date "Change Date",
(SELECT UNIQUE user_name
FROM fnd_user
WHERE user_id = v.created_by) "Created By",
(SELECT user_name
FROM fnd_user
WHERE user_id = v.last_updated_by) "Last Update By"
FROM fnd_profile_options o,
fnd_profile_option_values v,
fnd_profile_options_tl t
WHERE o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND start_date_active <= SYSDATE
AND NVL (end_date_active, SYSDATE) >= SYSDATE
AND o.profile_option_name = t.profile_option_name
AND level_id = 10001
AND t.LANGUAGE IN (SELECT language_code
FROM fnd_languages
WHERE installed_flag = 'B'
UNION
SELECT nls_language
FROM fnd_languages
WHERE installed_flag = 'B')
ORDER BY user_profile_option_name;

77. Forms Personalization Listing
SELECT ffft.user_function_name "User Form Name", ffcr.SEQUENCE,
ffcr.description, ffcr.rule_type, ffcr.enabled, ffcr.trigger_event,
ffcr.trigger_object, ffcr.condition, ffcr.fire_in_enter_query,
(SELECT user_name
FROM fnd_user fu
WHERE fu.user_id = ffcr.created_by) "Created By "
FROM fnd_form_custom_rules ffcr, fnd_form_functions_vl ffft
WHERE ffcr.ID = ffft.function_id
ORDER BY 1;

8. Patch Level Listing
SELECT a.application_name,
DECODE (b.status, 'I', 'Installed', 'S', 'Shared', 'N/A') status,
patch_level
FROM apps.fnd_application_vl a, apps.fnd_product_installations b
WHERE a.application_id = b.application_id;

9. Function Listing
SELECT function_id, user_function_name, creation_date, description
FROM applsys.fnd_form_functions_tl
y order by user_function_name;

10. Request Attached To Responsibility Listing
SELECT responsibility_name , frg.request_group_name,
fcpv.user_concurrent_program_name, fcpv.description
FROM fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_concurrent_programs_vl fcpv,
fnd_responsibility_vl frv
WHERE frgu.request_unit_type = 'P'
AND frgu.request_group_id = frg.request_group_id
AND frgu.request_unit_id = fcpv.concurrent_program_id
AND frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name;

11. Request Listing Application Wise
SELECT fa.application_short_name,
fcpv.user_concurrent_program_name,
description,
DECODE (fcpv.execution_method_code,
'B', 'Request Set Stage Function',
'Q', 'SQL*Plus',
'H', 'Host',
'L', 'SQL*Loader',
'A', 'Spawned',
'I', 'PL/SQL Stored Procedure',
'P', 'Oracle Reports',
'S', 'Immediate',
fcpv.execution_method_code
) exe_method,
output_file_type, program_type, printer_name,
minimum_width,
minimum_length, concurrent_program_name,
concurrent_program_id
FROM fnd_concurrent_programs_vl fcpv, fnd_application fa
WHERE fcpv.application_id = fa.application_id
ORDER BY description

12. Count Module Wise Reports
SELECT fa.application_short_name,
DECODE (fcpv.execution_method_code,
'B', 'Request Set Stage Function',
'Q', 'SQL*Plus',
'H', 'Host',
'L', 'SQL*Loader',
'A', 'Spawned',
'I', 'PL/SQL Stored Procedure',
'P', 'Oracle Reports',
'S', 'Immediate',
fcpv.execution_method_code
) exe_method,
COUNT (concurrent_program_id) COUNT
FROM fnd_concurrent_programs_vl fcpv, fnd_application fa
WHERE fcpv.application_id = fa.application_id
GROUP BY fa.application_short_name, fcpv.execution_method_code
ORDER BY 1;

13. Request Status Listing
SELECT f.request_id , pt.user_concurrent_program_name user_concurrent_program_name
, f.actual_start_date actual_start_date
, f.actual_completion_date actual_completion_date,
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)
|| ' HOURS ' ||
floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
round((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600 -
(floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60) ))
|| ' SECS ' time_difference
,
DECODE(p.concurrent_program_name,'ALECDC',p.concurrent_program_name||'['||f.descriptio
n||']',p.concurrent_program_name) concurrent_program_name
, decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase
, f.status_code
FROM apps.fnd_concurrent_programs p
, apps.fnd_concurrent_programs_tl pt
, apps.fnd_concurrent_requests f
WHERE f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV('Lang')
and f.actual_start_date is not null
ORDER by f.actual_completion_date-f.actual_start_date desc;

14. User And Responsibility Listing
SELECT UNIQUE u.user_id, SUBSTR (u.user_name, 1, 30) user_name,
SUBSTR (r.responsibility_name, 1, 60) responsiblity,
SUBSTR (a.application_name, 1, 50) application
FROM fnd_user u,
fnd_user_resp_groups g,
fnd_application_tl a,
fnd_responsibility_tl r
WHERE g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
and a.application_name = 'Purchasing'
ORDER BY SUBSTR (user_name, 1, 30),
SUBSTR (a.application_name, 1, 50),
SUBSTR (r.responsibility_name, 1, 60)

15. Applied Patch Listing
SELECT patch_name, patch_type, maint_pack_level, creation_date
FROM applsys.ad_applied_patches
ORDER BY creation_date DESC

16. Query To Find Responsibility of a Concurrent Program
SELECT fcpl.user_concurrent_program_name "REPORT NAME",
fnrtl.responsibility_name, frg.request_group_name,
fcp.concurrent_program_name "CONCURRENT PROGRAM SHORT NAME"
FROM apps.fnd_request_groups frg,
apps.fnd_request_group_units frgu,
apps.fnd_concurrent_programs fcp,
apps.fnd_concurrent_programs_tl fcpl,
apps.fnd_executables fe,
apps.fnd_responsibility fnr,
apps.fnd_responsibility_tl fnrtl
WHERE frg.application_id = frgu.application_id
AND frg.request_group_id = frgu.request_group_id
AND frg.request_group_id = fnr.request_group_id
AND frg.application_id = fnr.application_id
AND fnr.responsibility_id = fnrtl.responsibility_id
AND frgu.request_unit_id = fcp.concurrent_program_id
AND frgu.unit_application_id = fcp.application_id
AND fcp.concurrent_program_id = fcpl.concurrent_program_id
AND fe.execution_method_code LIKE 'P' --'Oracle Reports'
AND fcp.executable_id = fe.executable_id
AND fe.application_id = fcp.executable_application_id
AND fcpl.user_concurrent_program_name LIKE :conc_prog_name
--AND fnrtl.responsibility_name LIKE '6010 DPW FUJ A%'

-- Example Resp. Name : Inventory, Vision Operations (USA)
-- AND fnrtl.LANGUAGE = 'US'
--AND fcpl.LANGUAGE = 'US';

17. Query To Find Parameters and ValueSets of a Concurrent Program
SELECT
fcpl.user_concurrent_program_name "Concurrent Program Name",
fcp.concurrent_program_name "Short Name",
fdfcuv.column_seq_num "Column Seq Number",
fdfcuv.end_user_column_name "Parameter Name",
fdfcuv.form_left_prompt "Prompt",
fdfcuv.enabled_flag " Enabled Flag",
fdfcuv.required_flag "Required Flag",
fdfcuv.display_flag "Display Flag",
fdfcuv.flex_value_set_id "Value Set Id",
ffvs.flex_value_set_name "Value Set Name",
flv.meaning "Default Type",
fdfcuv.DEFAULT_VALUE "Default Value"

FROM
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpl,
fnd_descr_flex_col_usage_vl fdfcuv,
fnd_flex_value_sets ffvs,
fnd_lookup_values flv

WHERE
fcp.concurrent_program_id = fcpl.concurrent_program_id
AND fcpl.user_concurrent_program_name = :conc_prg_name
AND fdfcuv.descriptive_flexfield_name = '$SRS$.'
|| fcp.concurrent_program_name
AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
AND flv.lookup_code(+) = fdfcuv.default_type
AND fcpl.LANGUAGE = USERENV ('LANG')
AND flv.LANGUAGE(+) = USERENV ('LANG')

ORDER BY fdfcuv.column_seq_num;

18. Query To Find Discoverer Reports – Last Run Details
SELECT DISTINCT docs.doc_created_by doc_owner, docs.doc_name doc_name,
(SELECT TRUNC (MAX (dm.qs_created_date))
FROM eul5_qpp_stats dm
WHERE dm.qs_doc_name = stats.qs_doc_name) last_run
FROM eul5_qpp_stats stats, eul5_documents docs
WHERE docs.doc_name NOT LIKE 'Workbook%'
AND stats.qs_created_date(+) > :cutoff
AND docs.doc_created_date < :cutoff
AND docs.doc_name = stats.qs_doc_name(+)
HAVING :run_date >=
NVL ((SELECT TRUNC (MAX (dm.qs_created_date))
FROM eul5_qpp_stats dm
WHERE dm.qs_doc_name = stats.qs_doc_name),
'01-JAN-2000'
)
GROUP BY docs.doc_created_by, docs.doc_name, stats.qs_doc_name
ORDER BY last_run DESC, docs.doc_created_by, docs.doc_name;

19. Query to Find Output Type of a Concurrent Program
SELECT --fcpv.application_id,fcpv.executable_id,
fe.executable_name, fcpv.user_concurrent_program_name,
fcpv.output_file_type
FROM fnd_concurrent_programs_vl fcpv, fnd_executables fe,
fnd_application fa
WHERE fcpv.executable_id = fe.executable_id
--AND fcpv.output_file_type = 'XML'
AND fcpv.application_id = fa.application_id
AND fa.basepath LIKE ('%')
AND fa.application_short_name LIKE ('%')
AND fcpv.user_concurrent_program_name = :concurrent_program_name

20. Query to get details of XML Publisher Templates
SELECT * FROM XDO_TEMPLATES_VL WHERE END_DATE IS NOT NULL WHERE TEMPLATE_CODE =:temp_code;

select * from xdo_lobs where lob_code =:template_code;


21. Query to get the details of Valueset
SELECT flex_value_set_name,application_table_name, value_column_name, id_column_name,
meaning_column_name, additional_where_clause
FROM fnd_flex_validation_tables ffvt,fnd_flex_value_sets ffvs
WHERE ffvs.flex_value_set_id = ffvt.flex_value_set_id
and flex_value_set_name=:ValueSetName