Today, as part of the ERP implementation,Data Conversion/Data Migration/Interface plays an important role.
As you all know, data can be entered in different ways:
1. Data can be entered using Oracle Applications screen.
2. Data can also be entered using an Oracle Open System Interface.
3. Also, data can be loaded using 3rd party tools such as Data Loader, etc.
First let me tell me you about,
1. What is Migration/Conversion and Interface?
2. What point of time they are used?
Hope, everybody who has worked in the implementation Project, should have come
across Data Migration / Data Conversion / Migration.
I am citing here definitions and some situations for those who didn’t have the opportunity to work in the implementation project.
What is Data Migration?
Data Migration can be defined as a process of moving very large volumes of data from
our clients existing system to new systems. Existing systems could be anything like IT applications, spreadsheets and standalone databases.
Say suppose, a new customer who uses existing IT application likes to switch over to
Oracle E-Business Suite, considering the performance and volume of data, we would go
in for Data Migration to port the existing data to ERP.
What is Data Conversion?
Data conversion can be defined as a process of converting data from one structural form to another to suit the requirements of the system to which it is migrated.
Say suppose, an existing customer who uses older version of Oracle E-Business Suite(11i)likes to switch over to Oracle E-Business Suite(12i), considering the new features, we would go in for Data Conversion.
Few Points:
1. Frequency of Conversions are a one time Event.
2. Conversions are executed before Production.
3. Conversions are executed in Batch
What is Interface?
In common man's language, Interface is a communication channel by which you can
move data into Oracle Applications.
Say suppose, there are situations wherein we need to upload data frequently from an
external system like spreadsheets, etc., we would go in for Interface.
Few Points:
1. Frequency of Interface is On-going
2. Interface are executed during production
3. Interface are executed in Batch / real time
Let us get to know, how many types of Interfaces are there and what are they.
Normally in any system there are two types of Interfaces.
(i) Inbound Interface
(ii) Outbound Interface
Inbound Interface:
Inbound Interface is the one which allows data to get into oracle application from
outside is called inbound interface.
Types of Inbound Interfaces available:
1. Open interfaces
2. API's(Application Program Interface)
3. XML GATEWAY --Mainly used for automation transactions with third party systems
4. WEBADI--Used for uploading data from excel.
5. PLSQL Packages for Reading XML Data--Use this in case of importing non standard transactions
Custom Inbound Interface programs have following steps :
Datafile (Legacy data) => Temporary tables => Validate data => Load into Interface Tables => Run Concurrent Program to load the data into Apps Base tables
Datafile (Legacy data) => Temporary tables => Validate data => Run required API (application program interface) to load the data into Apps Base tables.
Datafile (Legacy data) => Prepare delimited data file => Create Control file with
instructions to load the data into Apps Base tables.
Run SQLLDR from unix or DOS:
sqlldr user/password@instance control=control_file.ctl data=data_file.dat
bad=bad_file.dat
Outbound Interface:
The one which allows data to get data from oracle applications to other systems is
called outbound interface.
Outbound Interface selects and transform an Application data by SQL statements
and creates output files using Oracle Reports, PL/SQL (FND_FILE utility) or
SQL*Plus scripts.
Types of Outbound Interfaces available:
1. Custom programs where we pull data to a csv file using UTL_FILE in the required format.
2. PLSQL Packages for generating XML
Okey, That’s it from me, now. Will continue to post some more on various topics. Please drop your comments if you feel something has to be added or modified. Appreciate your help on this.
Wednesday, November 18, 2009
Wednesday, May 27, 2009
Oracle Receivables Receipts Workbench-FAQ
1. What is the difference between on-account and unapplied receipts?
Answer :
a. Unapplied Receipt:-
This receipt is not applied to an existing invoice when it is entered. When the customer does not want to apply the entered receipt they can leave it as unapplied and apply the amount on a later date.
b. On-Account:-
In case of an On-Account receipt, there is no identified invoice against the receipt. Advance money is received from the customer and the invoice for the same can be raised at a later date.
2. What is the difference between a debit memo reversal and reversed receipt ??
Answer :
reversed receipt :-
Receivables lets you reverse a receipt ,when a receipt is returned from the bank for insufficient funds or if a customer stops payment on a receipt
There are Two types Of reversals
a. Standard Reversal :-
A payment Reversal Where Receivables Creates Reversing general Ledger and Re-open the Debit Items you closed By reversing The original payment.
A standard reversal can be created for cash and miscellaneous receipts, as well as a transaction related to a chargeback as long as there is no activity against the chargeback and the chargeback has not been posted to the general ledger.
You also cannot process a standard reversal for any receipts that have
adjustments or charge backs that have been posted to the general ledger. For these exceptions, a debit memo reversal must be used.
To reverse a receipt:
A. Navigate to: Receipts>Receipts or Receipts>Batches
(enter batch information, then press the Receipts button).
B. Query the receipt to reverse.
C. Select the Reverse button.
D. Confirm or change the reversal date and reversal GL date. Oracle Receivables
assigns the GL date and reversal GL date as the current date. If the
current date is not in an open period, then it assigns both dates as the
last date of the most recent open period. If the original GL date of the
receipt is later than the current date, then Receivables uses the original GL date. You can change the reversal GL date and GL date to any date in an open period that is on or after the original GL date for the receipt.
E. Select the Category for this reversal.
Valid categories are: Reverse Payment, Non-Sufficient Funds, and
Stop Payment.
F. Select a Reason for this reversal.
G. Type a comment for the reversal, if desired.
H. Select the Reverse button.
I. Save your work.
b. Debit Memo Reversal :-
A reversal of a payment That Generates a new debit Memo instead of reopening old invoices and Debit memos.
Debit memo reversals are required when you want to reverse
a receipt that was previously applied to a chargeback and the chargeback has activity against or when charge backs or adjustments have been posted to the General Ledger for the receipt you want to reverse.
Receivables does not update any of the activity associated with the original receipt when you create a debit memo reversal. Instead it creates new receivable debit items for the items closed by the original receipt. You cannot process a debit memo reversal for a miscellaneous receipt.
To create a debit memo reversal:
A. Navigate to: Receipts>Receipts or Receipts>Batches (enter batch information,
then press the Receipts button).
B. Query the receipt to reverse.
C. Select the Reverse button.
D. Confirm or change the reversal date and reversal GL date. Oracle Receivables assigns the GL date and reversal GL date as the current date. If the current date is not in an open period, then it assigns both dates as the last date of the most recent open period. If the original GL date of the receipt is later than the current date, then Receivables uses the original GL date. You can change the reversal GL date and GL date to any date in an open period that is on or after the original GL date for the receipt.
E. Select the Category for this reversal.
Valid categories are: Reverse Payment, Non-Sufficient Funds, and
Stop Payment.
F. Select a Reason for this reversal.
G. Type a comment for the reversal, if desired.
H. Check the Debit Memo Reversal box.
I. Select a debit memo Transaction Type for this reversal.
J. Select the Reverse button.
K. Save your work.
3. What is the maturity date for receipts in Oracle Receivables?
Answer :
The maturity date in Oracle Receivables is a date that determines when funds for an automatic receipt can be transferred from your customer's bank account to your bank account. The maturity date cannot be before the receipt date.
4. When a receipt is reversed, where does the reversal of the On Account line go?
Answer :
If a receipt that has a partial amount placed on account is reversed, the on account portion of the receipt is also reversed.
5. How do you delete receipts and applications from interim tables?
Answer :
If a receipt came in through quick cash and was not purged from the interim table or it was missing from the batch and was entered manually, then you can delete the receipts and their application as follows:
SELECT receipt_number , cash_receipt_id, gl_date
FROM ar_interim_cash_receipts_all
WHERE receipt_number = &receipt_number;
Using the cash_receipt_id you can delete the data:
DELETE
FROM ar_interim_cash_receipts_all
WHERE cash_receipt_id = &id;
DELETE
FROM ar_interim_cash_rcpt_lines_all
WHERE cash_receipt_id = &id;
commit;
6. How do you refund an overpaid (on account) receipt?
Answer :
If your customer has overpaid and you want to cut a refund check for the overage from on account, follow these procedures to clear the credit entry from Accounts Receivable and process a refund through Accounts Payable.
This is a manual process between AR and AP.
A. You need to get the on account balance applied to an invoice. When the receipt was applied, the following general ledger entries were made:
Cash On Account
-------- ----------------
100 | | 100
B. Create a debit memo for the amount of the on account credit you want to refund. Use a special Debit Memo transaction type that uses a clearing account rather than Revenue. This will cause the following general ledger entries to be made:
Receivable Clearing
--------------- -------------
100 | | 100
C. Apply the on account amount to the new debit memo. This will cause the following general ledger entries to be made (leaves you with a Cash debit for $100 and a Clearing account credit for $100):
Receivable On Account Cash
--------------- ---------------- -------------
| 100 100 | 100 | 100
D. In Oracle Accounts Payable, process a check crediting $100 to cash and debiting the clearing account for $100 as below. This returns the cash and all other accounts to zero.
Cash Clearing
------------ -------------
| 100 100 |
7. How do you refund a receipt for returned goods?
Answer :
If your customer is requesting a refund for goods returned, follow these procedures to clear the credit entry from Accounts Receivable and process a refund through Accounts Payable. This is a manual process between AR and AP.
A. Create a credit memo in AR for the returned goods. Use the same Revenue and Receivable accounts referenced on the original invoice. This will cause the following general ledger entries to be made:
Receivable Revenue
--------------- -------------
| 100 100 |
B. Create a debit memo to offset the credit memo. Use a special Debit Memo transaction type that uses a clearing account rather than Revenue. This will cause the following general ledger entries to be made:
Receivable Clearing
--------------- -------------
100 | | 100
C. In Oracle Accounts Payable, process a check crediting $100 to cash and debiting the clearing account for $100 as below. This returns the cash and all other accounts to zero.
Receivable Clearing
--------------- -------------
| 100 100 |
8. What is receipt API?
Answer :
The Receipt API in its first phase caters to the following basic functionality via different API calls:
a) Creating a cash receipt
b) Applying it to a debit item
c) Creating a cash receipt and applying it to a debit item in one pass
d) On-account application
e) Unapplying the On-Account application
f) Unapplying the receipt application to a particular transaction
g) Reversing the receipt
h) Create a Miscellaneous receipt
i) Create a cash receipt activity application
j) Unapplies a cash receipt activity application
The ten Receipt API's are as under:
1 AR_RECEIPT_API_PUB.CREATE_CASH : Creates a single cash receipt, as in case of manually created cash receipts.
2 AR_RECEIPT_API_PUB.APPLY : Applies a cash receipt to a particular installment of a debit item. The application can also be a cross currency application.
3 AR_RECEIPT_API_PUB.CREATE_AND_APPLY : Creates a cash receipt and applies it to a specified installment of a debit item in one pass. Application fails if the creation fails due to some reason.
4 AR_RECEIPT_API_PUB.APPLY_ON_ACCOUNT : Does an On Account application for a cash receipt.
5 AR_RECEIPT_API_PUB.UNAPPLY_ON_ACCOUNT : Unapplies the On-account application on the specified receipt.
6 AR_RECEIPT_API_PUB.UNAPPLY : Unapplies the application of a particular installment of a debit item against the specified cash receipt.
7 AR_RECEIPT_API_PUB.REVERSE : Reverses the specified receipt.
8 AR_RECEIPT_API_PUB.CREATE_MISC : Creates a Miscellaneous receipt
9 AR_RECEIPT_API_PUB.ACTIVITY_APPLICATION : Creates an activity application on a cash receipt
10 AR_RECEIPT_API_PUB.ACTIVITY_UNAPPLICATION : Unapplies a particular activity application on a cash receipt
9. How can you change the type of the receipt from cash to misc once it is entered and saved?
Answer :
This is not possible as the field Type is not updateable once it is entered and saved. Cash type receipts store data in different tables than miscellaneous type receipts. That is why the 'Type' field on the Receipts form is protected as soon as the receipt is saved for the first time.
10. How do you apply payments to unrelated customers' invoices?
Answer :
The correct way to apply a receipt to more than one customer is to either establish a relationship between the customers or make sure the
System Option - Allow Payment of Unrelated Transactions is checked.
Once one or both of the conditions above are met, follow these procedures:
A. Navigate to Receipts>Receipts or Receipts>Batches to enter a new receipt.
B. Enter the receipt number, amount, payment method, and customer number of the first customer you want to apply the invoice too. You must enter a customer number on this screen in order to make the Applications button
available.
C. Select the Applications button.
D. Apply the receipt to the first customer's invoice.
E. If you want to apply monies to another, different customer's invoice, then
scroll to the right to find the Customer number field and change the number
to the other customer's number.
NOTE: If you will be processing many payments to related customers' invoices,
you can drag the Customer number column and drop it close to the Apply to
column, then save these changes in a folder.
F. Then scroll back to the Apply To field and select the LOV. You should now
see the other customer's open invoices and can make a selection from here.
G. Save your changes.
11 . How are count and amount totals at the receipt header level updated after reversing a receipt?
Answer :
a. Actual Count and Amount will always show THE ACTUAL NUMBER AND AMOUNT of
receipts entered, regardless of any reversals.
b. Returned Count and Amount show the number and amount of receipts reversed
for the reasons "Non-sufficient funds", or "Stop Payment", i.e. the CUSTOMER
has backed out of the payment.
c. Reversed Count and Amount show the number and amount of receipts reversed
for the reason "Reverse Payment", i.e. the ENTRY CLERK has made a mistake
and wants to back out the payment.
d. Difference Count and Amount show the Control - (Actual - Reversed) totals.
12. If a receipt is applied to a wrong customer account. What is the best way to move the funds from one account to another?
Answer :
The receipt can be reversed and a new receipt entered for the correct customer
if the original receipt does not have any adjustments or chargebacks that have
been posted to the general ledger. Otherwise, a debit memo reversal will have
to be created.
Or, if you have established a relationship between the customers or activated
the System Option - Allow Payment of Unrelated Transactions, then you can
unapply the application for the original customer and then apply an amount to
the other customer on the existing receipt.
13 . How can you prorate a receipt amount between tax and line items?
Answer :
Application Rule Sets determine the logic Receivables uses to apply partial payments to your customer's open debit items, as well as how discounts affect the open balance for each type of associated charges.
You can set up different application rule sets for each transaction type. You can also assign a default
application rule set in the System Options.
Receivables provides the following seeded Application Rule Sets:
- Line first / tax after
- Line first / tax prorate
- Prorate all
- Over application rule
14.How do you change the GL date default for receipts?
Answer :
Oracle Receivables defaults the current date for the receipt GL date as long as the date is in an open period. Otherwise, it will default to the last day of the most recent open period. There are no profile options available to stop the receipt's GL date from defaulting.
However, the profile option 'AR: Application GL Date Default' will control the GL date default when you apply receipts
15. How do you apply a receipt to the same transaction twice?
Answer :
Oracle Receivables does not allow you to add a second line to a receipt for the same invoice or debit memo. However, you can change the original line applied to the invoice and increase the applied amount by the unapplied portion that
you want to apply.
References
Metalink web site and user guides.
Answer :
a. Unapplied Receipt:-
This receipt is not applied to an existing invoice when it is entered. When the customer does not want to apply the entered receipt they can leave it as unapplied and apply the amount on a later date.
b. On-Account:-
In case of an On-Account receipt, there is no identified invoice against the receipt. Advance money is received from the customer and the invoice for the same can be raised at a later date.
2. What is the difference between a debit memo reversal and reversed receipt ??
Answer :
reversed receipt :-
Receivables lets you reverse a receipt ,when a receipt is returned from the bank for insufficient funds or if a customer stops payment on a receipt
There are Two types Of reversals
a. Standard Reversal :-
A payment Reversal Where Receivables Creates Reversing general Ledger and Re-open the Debit Items you closed By reversing The original payment.
A standard reversal can be created for cash and miscellaneous receipts, as well as a transaction related to a chargeback as long as there is no activity against the chargeback and the chargeback has not been posted to the general ledger.
You also cannot process a standard reversal for any receipts that have
adjustments or charge backs that have been posted to the general ledger. For these exceptions, a debit memo reversal must be used.
To reverse a receipt:
A. Navigate to: Receipts>Receipts or Receipts>Batches
(enter batch information, then press the Receipts button).
B. Query the receipt to reverse.
C. Select the Reverse button.
D. Confirm or change the reversal date and reversal GL date. Oracle Receivables
assigns the GL date and reversal GL date as the current date. If the
current date is not in an open period, then it assigns both dates as the
last date of the most recent open period. If the original GL date of the
receipt is later than the current date, then Receivables uses the original GL date. You can change the reversal GL date and GL date to any date in an open period that is on or after the original GL date for the receipt.
E. Select the Category for this reversal.
Valid categories are: Reverse Payment, Non-Sufficient Funds, and
Stop Payment.
F. Select a Reason for this reversal.
G. Type a comment for the reversal, if desired.
H. Select the Reverse button.
I. Save your work.
b. Debit Memo Reversal :-
A reversal of a payment That Generates a new debit Memo instead of reopening old invoices and Debit memos.
Debit memo reversals are required when you want to reverse
a receipt that was previously applied to a chargeback and the chargeback has activity against or when charge backs or adjustments have been posted to the General Ledger for the receipt you want to reverse.
Receivables does not update any of the activity associated with the original receipt when you create a debit memo reversal. Instead it creates new receivable debit items for the items closed by the original receipt. You cannot process a debit memo reversal for a miscellaneous receipt.
To create a debit memo reversal:
A. Navigate to: Receipts>Receipts or Receipts>Batches (enter batch information,
then press the Receipts button).
B. Query the receipt to reverse.
C. Select the Reverse button.
D. Confirm or change the reversal date and reversal GL date. Oracle Receivables assigns the GL date and reversal GL date as the current date. If the current date is not in an open period, then it assigns both dates as the last date of the most recent open period. If the original GL date of the receipt is later than the current date, then Receivables uses the original GL date. You can change the reversal GL date and GL date to any date in an open period that is on or after the original GL date for the receipt.
E. Select the Category for this reversal.
Valid categories are: Reverse Payment, Non-Sufficient Funds, and
Stop Payment.
F. Select a Reason for this reversal.
G. Type a comment for the reversal, if desired.
H. Check the Debit Memo Reversal box.
I. Select a debit memo Transaction Type for this reversal.
J. Select the Reverse button.
K. Save your work.
3. What is the maturity date for receipts in Oracle Receivables?
Answer :
The maturity date in Oracle Receivables is a date that determines when funds for an automatic receipt can be transferred from your customer's bank account to your bank account. The maturity date cannot be before the receipt date.
4. When a receipt is reversed, where does the reversal of the On Account line go?
Answer :
If a receipt that has a partial amount placed on account is reversed, the on account portion of the receipt is also reversed.
5. How do you delete receipts and applications from interim tables?
Answer :
If a receipt came in through quick cash and was not purged from the interim table or it was missing from the batch and was entered manually, then you can delete the receipts and their application as follows:
SELECT receipt_number , cash_receipt_id, gl_date
FROM ar_interim_cash_receipts_all
WHERE receipt_number = &receipt_number;
Using the cash_receipt_id you can delete the data:
DELETE
FROM ar_interim_cash_receipts_all
WHERE cash_receipt_id = &id;
DELETE
FROM ar_interim_cash_rcpt_lines_all
WHERE cash_receipt_id = &id;
commit;
6. How do you refund an overpaid (on account) receipt?
Answer :
If your customer has overpaid and you want to cut a refund check for the overage from on account, follow these procedures to clear the credit entry from Accounts Receivable and process a refund through Accounts Payable.
This is a manual process between AR and AP.
A. You need to get the on account balance applied to an invoice. When the receipt was applied, the following general ledger entries were made:
Cash On Account
-------- ----------------
100 | | 100
B. Create a debit memo for the amount of the on account credit you want to refund. Use a special Debit Memo transaction type that uses a clearing account rather than Revenue. This will cause the following general ledger entries to be made:
Receivable Clearing
--------------- -------------
100 | | 100
C. Apply the on account amount to the new debit memo. This will cause the following general ledger entries to be made (leaves you with a Cash debit for $100 and a Clearing account credit for $100):
Receivable On Account Cash
--------------- ---------------- -------------
| 100 100 | 100 | 100
D. In Oracle Accounts Payable, process a check crediting $100 to cash and debiting the clearing account for $100 as below. This returns the cash and all other accounts to zero.
Cash Clearing
------------ -------------
| 100 100 |
7. How do you refund a receipt for returned goods?
Answer :
If your customer is requesting a refund for goods returned, follow these procedures to clear the credit entry from Accounts Receivable and process a refund through Accounts Payable. This is a manual process between AR and AP.
A. Create a credit memo in AR for the returned goods. Use the same Revenue and Receivable accounts referenced on the original invoice. This will cause the following general ledger entries to be made:
Receivable Revenue
--------------- -------------
| 100 100 |
B. Create a debit memo to offset the credit memo. Use a special Debit Memo transaction type that uses a clearing account rather than Revenue. This will cause the following general ledger entries to be made:
Receivable Clearing
--------------- -------------
100 | | 100
C. In Oracle Accounts Payable, process a check crediting $100 to cash and debiting the clearing account for $100 as below. This returns the cash and all other accounts to zero.
Receivable Clearing
--------------- -------------
| 100 100 |
8. What is receipt API?
Answer :
The Receipt API in its first phase caters to the following basic functionality via different API calls:
a) Creating a cash receipt
b) Applying it to a debit item
c) Creating a cash receipt and applying it to a debit item in one pass
d) On-account application
e) Unapplying the On-Account application
f) Unapplying the receipt application to a particular transaction
g) Reversing the receipt
h) Create a Miscellaneous receipt
i) Create a cash receipt activity application
j) Unapplies a cash receipt activity application
The ten Receipt API's are as under:
1 AR_RECEIPT_API_PUB.CREATE_CASH : Creates a single cash receipt, as in case of manually created cash receipts.
2 AR_RECEIPT_API_PUB.APPLY : Applies a cash receipt to a particular installment of a debit item. The application can also be a cross currency application.
3 AR_RECEIPT_API_PUB.CREATE_AND_APPLY : Creates a cash receipt and applies it to a specified installment of a debit item in one pass. Application fails if the creation fails due to some reason.
4 AR_RECEIPT_API_PUB.APPLY_ON_ACCOUNT : Does an On Account application for a cash receipt.
5 AR_RECEIPT_API_PUB.UNAPPLY_ON_ACCOUNT : Unapplies the On-account application on the specified receipt.
6 AR_RECEIPT_API_PUB.UNAPPLY : Unapplies the application of a particular installment of a debit item against the specified cash receipt.
7 AR_RECEIPT_API_PUB.REVERSE : Reverses the specified receipt.
8 AR_RECEIPT_API_PUB.CREATE_MISC : Creates a Miscellaneous receipt
9 AR_RECEIPT_API_PUB.ACTIVITY_APPLICATION : Creates an activity application on a cash receipt
10 AR_RECEIPT_API_PUB.ACTIVITY_UNAPPLICATION : Unapplies a particular activity application on a cash receipt
9. How can you change the type of the receipt from cash to misc once it is entered and saved?
Answer :
This is not possible as the field Type is not updateable once it is entered and saved. Cash type receipts store data in different tables than miscellaneous type receipts. That is why the 'Type' field on the Receipts form is protected as soon as the receipt is saved for the first time.
10. How do you apply payments to unrelated customers' invoices?
Answer :
The correct way to apply a receipt to more than one customer is to either establish a relationship between the customers or make sure the
System Option - Allow Payment of Unrelated Transactions is checked.
Once one or both of the conditions above are met, follow these procedures:
A. Navigate to Receipts>Receipts or Receipts>Batches to enter a new receipt.
B. Enter the receipt number, amount, payment method, and customer number of the first customer you want to apply the invoice too. You must enter a customer number on this screen in order to make the Applications button
available.
C. Select the Applications button.
D. Apply the receipt to the first customer's invoice.
E. If you want to apply monies to another, different customer's invoice, then
scroll to the right to find the Customer number field and change the number
to the other customer's number.
NOTE: If you will be processing many payments to related customers' invoices,
you can drag the Customer number column and drop it close to the Apply to
column, then save these changes in a folder.
F. Then scroll back to the Apply To field and select the LOV. You should now
see the other customer's open invoices and can make a selection from here.
G. Save your changes.
11 . How are count and amount totals at the receipt header level updated after reversing a receipt?
Answer :
a. Actual Count and Amount will always show THE ACTUAL NUMBER AND AMOUNT of
receipts entered, regardless of any reversals.
b. Returned Count and Amount show the number and amount of receipts reversed
for the reasons "Non-sufficient funds", or "Stop Payment", i.e. the CUSTOMER
has backed out of the payment.
c. Reversed Count and Amount show the number and amount of receipts reversed
for the reason "Reverse Payment", i.e. the ENTRY CLERK has made a mistake
and wants to back out the payment.
d. Difference Count and Amount show the Control - (Actual - Reversed) totals.
12. If a receipt is applied to a wrong customer account. What is the best way to move the funds from one account to another?
Answer :
The receipt can be reversed and a new receipt entered for the correct customer
if the original receipt does not have any adjustments or chargebacks that have
been posted to the general ledger. Otherwise, a debit memo reversal will have
to be created.
Or, if you have established a relationship between the customers or activated
the System Option - Allow Payment of Unrelated Transactions, then you can
unapply the application for the original customer and then apply an amount to
the other customer on the existing receipt.
13 . How can you prorate a receipt amount between tax and line items?
Answer :
Application Rule Sets determine the logic Receivables uses to apply partial payments to your customer's open debit items, as well as how discounts affect the open balance for each type of associated charges.
You can set up different application rule sets for each transaction type. You can also assign a default
application rule set in the System Options.
Receivables provides the following seeded Application Rule Sets:
- Line first / tax after
- Line first / tax prorate
- Prorate all
- Over application rule
14.How do you change the GL date default for receipts?
Answer :
Oracle Receivables defaults the current date for the receipt GL date as long as the date is in an open period. Otherwise, it will default to the last day of the most recent open period. There are no profile options available to stop the receipt's GL date from defaulting.
However, the profile option 'AR: Application GL Date Default' will control the GL date default when you apply receipts
15. How do you apply a receipt to the same transaction twice?
Answer :
Oracle Receivables does not allow you to add a second line to a receipt for the same invoice or debit memo. However, you can change the original line applied to the invoice and increase the applied amount by the unapplied portion that
you want to apply.
References
Metalink web site and user guides.
Thursday, May 7, 2009
Rebranding – R12 Applications
Purpose of the blog:
The purpose of this document is to rebrand the entire R12 application to have client’s look and feel.
Introduction:
This section identifies the places needs to be rebranded. The Login page should be adapted to conform with Login page as per „Brand Identity in Graphical User Interfaces Specification“.Favicon should be introduced for Web application. Oracle colors should be replaced by colors used in Client Look and Feel OrangeTouch for Web and for Java.
Oracle logo should be removed in the forms menu. eBS should be replaced with charge@once business Enterprise Financials. About dialogs need to be adapted for Web and Java. Splash screen required for Java application.
Rebranding of eBS
1.1 Login page should be adapted to conform with Login page as per „Brand Identity in Graphical User Interfaces Specification“
Existing login template layout will be replaced. Web page stylesheet (*.css) and image changes would require.
Solution:
Login as SYSADMIN
Navigate to Functional Administrator;
Personalization tab;
Search criteria: “Document Path = /oracle/apps/fnd/sso/login/webui”
Select /oracle/apps/fnd/sso/login/webui/MainLoginPG
Check ‘Include’ and press Apply button
Select ‘Personalization Structure’ as Complete View
Select globalTop image to personalize
Write ‘xxGlobalLeft.bmp’ to Image URI site field and apply.
Select global image to personalize
Write ‘xxGlobalLeftBack.bmp’ to Image URI site field and apply.
Press ‘Add Item’ to Region11.
Choose Item Style: ‘Image’
ID: ‘xxGlobalRight’
Image URI: ‘xxGlobalRight.bmp’
Hide region15login by selecting personalize button and setting Rendered option to ‘false’
Make toplines.gif and toplies_dummy.gif as rendered false.
Copy the custom people.jpg to $OA_MEDIA directory.
Make the language region, accessibility region as rendered false.
Go to customize standard footer and customize the copyright message.
Backup ‘people.jpg’, ‘lightBlue_back.jpg’, ‘topLines.gif’ files in $OA_MEDIA directory
1.2 Product banner should be introduced for Web and Desktop application
eBS Customized look and feel template (CLAF) will be configured which includes background image changes, tab layout, logo changes (e.g. Oracle logo in web form below could be changed separately) together with text link stylesheet (*.css) definition and Oracle forms changes (Oracle logo) would take 12 man days.
Assumption: Icons, logos and backgrounds (*.jpeg, *.gif images) are provided already in preprocessed shape (all necessary sizing and cropping etc..).
Solution:
LOGO installation and configuration:
1. Copy XX_Logo_(130x58).gif file to OA_MEDIA (/opt/oracle/product/12.0/apps/apps_st/comn/java/classes/oracle/apps/media)
2. System Administrator --> Profile System Values --> Change "Corporate Branding Image for Oracle Applications" value to "/OA_MEDIA/XX_Logo_(130x58).gif"
1.3 Oracle colors should be replaced by colors used in Client Look and Feel OrangeTouch for Web and for Java
Oracle eBS look and feel personalization functionality will be used. To accommodate color scheme changes careful walkthrough through all used modules needed. Every web and Java forms control (tab view, table/grid view, master-detail view etc..) needs detailed color scheme tuning in OAF (Oracle Applications framework).
Client look and feel (LAF) installation and configuration:
1. Copy myxx-desktop.xml and myxx-desktop-metadata.xml files to OA_HTML/cabo/lafs (/opt/oracle/product/12.0/apps/apps_st/comn/webapps/oacore/html/cabo/lafs)
2. Copy myxx-desktop.xss file to OA_HTML/cabo/styles (/opt/oracle/product/12.0/apps/apps_st/comn/webapps/oacore/html/cabo/styles)
3. Add "myxx-desktop" value to APPLICATIONS_LOOK_AND_FEEL lookup type
Workaround --> Create and save "myxx-desktop" Custom Look and Feel manually (user needs responsibility ""), and copy myxx-desktop.xss file to OA_HTML/cabo/styles
4. System Administrator --> Profile System Values --> Change "Oracle Applications Look and Feel" value to "myxx-desktop"
1.4 Oracle logo should be removed in the forms menu
1. Open the file $INST_TOP/ora/10.1.2/forms/server/appsweb.cfg2. Check the baseHTML and baseHTMLJInitiator variables inside the file.
2. They point to the appsbase.htm file used in the forms (ussually is the same file for both)
3. Open the file(s) pointed by those variables
4. Go to the "// Forms Applet Parameters" section related to the client browser you use.
5. Check if there's a line that references to "PARAM name=logo".
An example of how that line must be for Internet Explorer is:
IEhtml += '<' + 'PARAM name=logo value="' + xlogo + '">';
add or change that line according to what you have and what you want.
6. Go to the "// Oracle Applications default Parameters" section in the same file
7. Add or edit the definition of xlogo variable. It must be:
var xlogo = "%logo%"
8.- Save the file(s)
9.- Locate the variable "logo" in $INST_TOP/ora/10.1.2/forms/server/appsweb.cfg file at the "Forms Server Information: servlet, port, machine name and domain" section. If it's not there then you can create the line. The variable must point to the .gif file you want to add on the forms and point the "logo" variable to your file with:logo=noStop and start the apache and forms services.
1.5 eBS should be replaced with Product Name
Application name normally in eBS is distributed between various GUI elements and controls (Web and Java forms). Some labels could be a part of eBS product core which not necessarily is available for modification. Investigation of all locations and implementation. Some examples where application name could be located: context menu in Java forms(file, help), header/footer in web forms, text labels in web wizard type forms, web page titles etc...
Solution:
1. Go to "Application Developer" responsibility
2. Select "Messages" from the menu
3. Query the message name and then enter your message text in the "Current Message Text" field
4. Run Generate Messages program.
5. Save changes and exit6. Clear cache and bounce Apache to see the change
Message Texts:
ORACLE_APPLICATIONS – Product name
1.6 About dialogs need to be adapted for Web and Java
Software version information may reside not only in traditional help->about dialog. All possible location investigation and implementation
Solution:
1. Go to "Application Developer" responsibility
2. Select "Messages" from the menu
3. Query the message name and then enter your message text in the "Current Message Text" field
4. Run generate messages program.
5. Save changes and exit
6. Clear cache and bounce Apache to see the change
Message Texts:
VERSION_COPYRIGHT - Product name Copyright (c) 2009 XX Limited, All Rights Reserved. XX Ltd
VERSION_WINDOW_TITLE - About Product Name V2.0
Copy the FNDMENU.mmb from the /d01/oracle/PROD/apps/apps_st/appl/au/12.0.0/resource/US directory.
Open the mmb file in forms builder and change the label “About Oracle Applications” as About Product Name V2.0.
Compile the menu to generate mmx. Since it is R12 use frmcmp_batch command to compile mmb.
1.7 Splash screen required for Java application
Splash screen is shown during first Java forms load. Java forms application part is initiated through web components therefore before implementation detailed analysis of OAF (Oracle Applications framework) components will be needed.
Solution:
Rename the forms_logo.gif file in the $OA_MEDIA and place the custom Client gif file in the same folder with the name forms_logo.gif.
To change the splash screen copyright text message, edit the configuration file appsweb.cfg in the below folder:
/d01/oracle/PROD/apps/apps_st/comn/webapps/oacore/html/bin
1.8 Favicon should be introduced for Web application
For all web based application pages customized browser URL icon will be introduced.
Solution:
Favicon Icon should be generated using any online tool and keep it in the below folder as favicon.ico:
/d01/oracle/PROD/inst/apps/PROD_blrrdebs2/portal
Also add the below line in all the header part of html files in the /mnt/fs-fc/prof/applprof/inst/apps/PROF_epn-0/portal folder to activate the favicon in the respective pages.
<
link rel=”shortcut icon” href=” /d01/oracle/PROD/inst/apps/PROD_blrrdebs2/portal
\favicon.ico” mce_href=” /d01/oracle/PROD/inst/apps/PROD_blrrdebs2/portal\favicon.ico”
>
Mandatory: User should add the url as their favourites to activate the favicon which is the pre-requiste in IE.
Issues Faced
Ø We referred many sites to remove the Oracle logo from the forms menu. All suggested to modify the formsweb.cfg file and it was not worked. We raised a service request with Orcle and they advised us to do the changes in appsweb.cfg and appsbast.htm files since it is R12. It worked finally.
Conclusion
Each layout control in eBS might conform to certain CLAF (Customized look and feel) rules therefore proper unit level testing is needed which multiplies to each used form in selected solution. Complexity of architecture (two different technologies are used: web and java forms) can double certain work amount (e.g. banners and common menu items).
The purpose of this document is to rebrand the entire R12 application to have client’s look and feel.
Introduction:
This section identifies the places needs to be rebranded. The Login page should be adapted to conform with Login page as per „Brand Identity in Graphical User Interfaces Specification“.Favicon should be introduced for Web application. Oracle colors should be replaced by colors used in Client Look and Feel OrangeTouch for Web and for Java.
Oracle logo should be removed in the forms menu. eBS should be replaced with charge@once business Enterprise Financials. About dialogs need to be adapted for Web and Java. Splash screen required for Java application.
Rebranding of eBS
1.1 Login page should be adapted to conform with Login page as per „Brand Identity in Graphical User Interfaces Specification“
Existing login template layout will be replaced. Web page stylesheet (*.css) and image changes would require.
Solution:
Login as SYSADMIN
Navigate to Functional Administrator;
Personalization tab;
Search criteria: “Document Path = /oracle/apps/fnd/sso/login/webui”
Select /oracle/apps/fnd/sso/login/webui/MainLoginPG
Check ‘Include’ and press Apply button
Select ‘Personalization Structure’ as Complete View
Select globalTop image to personalize
Write ‘xxGlobalLeft.bmp’ to Image URI site field and apply.
Select global image to personalize
Write ‘xxGlobalLeftBack.bmp’ to Image URI site field and apply.
Press ‘Add Item’ to Region11.
Choose Item Style: ‘Image’
ID: ‘xxGlobalRight’
Image URI: ‘xxGlobalRight.bmp’
Hide region15login by selecting personalize button and setting Rendered option to ‘false’
Make toplines.gif and toplies_dummy.gif as rendered false.
Copy the custom people.jpg to $OA_MEDIA directory.
Make the language region, accessibility region as rendered false.
Go to customize standard footer and customize the copyright message.
Backup ‘people.jpg’, ‘lightBlue_back.jpg’, ‘topLines.gif’ files in $OA_MEDIA directory
1.2 Product banner should be introduced for Web and Desktop application
eBS Customized look and feel template (CLAF) will be configured which includes background image changes, tab layout, logo changes (e.g. Oracle logo in web form below could be changed separately) together with text link stylesheet (*.css) definition and Oracle forms changes (Oracle logo) would take 12 man days.
Assumption: Icons, logos and backgrounds (*.jpeg, *.gif images) are provided already in preprocessed shape (all necessary sizing and cropping etc..).
Solution:
LOGO installation and configuration:
1. Copy XX_Logo_(130x58).gif file to OA_MEDIA (/opt/oracle/product/12.0/apps/apps_st/comn/java/classes/oracle/apps/media)
2. System Administrator --> Profile System Values --> Change "Corporate Branding Image for Oracle Applications" value to "/OA_MEDIA/XX_Logo_(130x58).gif"
1.3 Oracle colors should be replaced by colors used in Client Look and Feel OrangeTouch for Web and for Java
Oracle eBS look and feel personalization functionality will be used. To accommodate color scheme changes careful walkthrough through all used modules needed. Every web and Java forms control (tab view, table/grid view, master-detail view etc..) needs detailed color scheme tuning in OAF (Oracle Applications framework).
Client look and feel (LAF) installation and configuration:
1. Copy myxx-desktop.xml and myxx-desktop-metadata.xml files to OA_HTML/cabo/lafs (/opt/oracle/product/12.0/apps/apps_st/comn/webapps/oacore/html/cabo/lafs)
2. Copy myxx-desktop.xss file to OA_HTML/cabo/styles (/opt/oracle/product/12.0/apps/apps_st/comn/webapps/oacore/html/cabo/styles)
3. Add "myxx-desktop" value to APPLICATIONS_LOOK_AND_FEEL lookup type
Workaround --> Create and save "myxx-desktop" Custom Look and Feel manually (user needs responsibility ""), and copy myxx-desktop.xss file to OA_HTML/cabo/styles
4. System Administrator --> Profile System Values --> Change "Oracle Applications Look and Feel" value to "myxx-desktop"
1.4 Oracle logo should be removed in the forms menu
1. Open the file $INST_TOP/ora/10.1.2/forms/server/appsweb.cfg2. Check the baseHTML and baseHTMLJInitiator variables inside the file.
2. They point to the appsbase.htm file used in the forms (ussually is the same file for both)
3. Open the file(s) pointed by those variables
4. Go to the "// Forms Applet Parameters" section related to the client browser you use.
5. Check if there's a line that references to "PARAM name=logo".
An example of how that line must be for Internet Explorer is:
IEhtml += '<' + 'PARAM name=logo value="' + xlogo + '">';
add or change that line according to what you have and what you want.
6. Go to the "// Oracle Applications default Parameters" section in the same file
7. Add or edit the definition of xlogo variable. It must be:
var xlogo = "%logo%"
8.- Save the file(s)
9.- Locate the variable "logo" in $INST_TOP/ora/10.1.2/forms/server/appsweb.cfg file at the "Forms Server Information: servlet, port, machine name and domain" section. If it's not there then you can create the line. The variable must point to the .gif file you want to add on the forms and point the "logo" variable to your file with:logo=noStop and start the apache and forms services.
1.5 eBS should be replaced with Product Name
Application name normally in eBS is distributed between various GUI elements and controls (Web and Java forms). Some labels could be a part of eBS product core which not necessarily is available for modification. Investigation of all locations and implementation. Some examples where application name could be located: context menu in Java forms(file, help), header/footer in web forms, text labels in web wizard type forms, web page titles etc...
Solution:
1. Go to "Application Developer" responsibility
2. Select "Messages" from the menu
3. Query the message name and then enter your message text in the "Current Message Text" field
4. Run Generate Messages program.
5. Save changes and exit6. Clear cache and bounce Apache to see the change
Message Texts:
ORACLE_APPLICATIONS – Product name
1.6 About dialogs need to be adapted for Web and Java
Software version information may reside not only in traditional help->about dialog. All possible location investigation and implementation
Solution:
1. Go to "Application Developer" responsibility
2. Select "Messages" from the menu
3. Query the message name and then enter your message text in the "Current Message Text" field
4. Run generate messages program.
5. Save changes and exit
6. Clear cache and bounce Apache to see the change
Message Texts:
VERSION_COPYRIGHT - Product name Copyright (c) 2009 XX Limited, All Rights Reserved. XX Ltd
VERSION_WINDOW_TITLE - About Product Name V2.0
Copy the FNDMENU.mmb from the /d01/oracle/PROD/apps/apps_st/appl/au/12.0.0/resource/US directory.
Open the mmb file in forms builder and change the label “About Oracle Applications” as About Product Name V2.0.
Compile the menu to generate mmx. Since it is R12 use frmcmp_batch command to compile mmb.
1.7 Splash screen required for Java application
Splash screen is shown during first Java forms load. Java forms application part is initiated through web components therefore before implementation detailed analysis of OAF (Oracle Applications framework) components will be needed.
Solution:
Rename the forms_logo.gif file in the $OA_MEDIA and place the custom Client gif file in the same folder with the name forms_logo.gif.
To change the splash screen copyright text message, edit the configuration file appsweb.cfg in the below folder:
/d01/oracle/PROD/apps/apps_st/comn/webapps/oacore/html/bin
1.8 Favicon should be introduced for Web application
For all web based application pages customized browser URL icon will be introduced.
Solution:
Favicon Icon should be generated using any online tool and keep it in the below folder as favicon.ico:
/d01/oracle/PROD/inst/apps/PROD_blrrdebs2/portal
Also add the below line in all the header part of html files in the /mnt/fs-fc/prof/applprof/inst/apps/PROF_epn-0/portal folder to activate the favicon in the respective pages.
<
link rel=”shortcut icon” href=” /d01/oracle/PROD/inst/apps/PROD_blrrdebs2/portal
\favicon.ico” mce_href=” /d01/oracle/PROD/inst/apps/PROD_blrrdebs2/portal\favicon.ico”
>
Mandatory: User should add the url as their favourites to activate the favicon which is the pre-requiste in IE.
Issues Faced
Ø We referred many sites to remove the Oracle logo from the forms menu. All suggested to modify the formsweb.cfg file and it was not worked. We raised a service request with Orcle and they advised us to do the changes in appsweb.cfg and appsbast.htm files since it is R12. It worked finally.
Conclusion
Each layout control in eBS might conform to certain CLAF (Customized look and feel) rules therefore proper unit level testing is needed which multiplies to each used form in selected solution. Complexity of architecture (two different technologies are used: web and java forms) can double certain work amount (e.g. banners and common menu items).
Wednesday, February 4, 2009
How to Use the SYS_REFCURSOR and REF CURSOR in Oracle Packages and Procedures
SYS_REFCURSOR is a built-in REF CURSOR type that allows any result set to be associated with it and can be used in 9i or higher. SYS_REFCURSOR can be used to:
- Delcare a cursor variable in an Oracle stored procedure/function;
- Pass cursors from and to an Oracle stored procedure/function.
Exampel 1: Getting a cursor out from an Oracle Package/Procedure
The following Oracle stored procedure demonstrates how to get a REFCURSOR cursor variable out from a package/procedure:
Package Specification Definition:
CREATE OR REPLACE PACKAGE XX_BI_PKG AS
PROCEDURE xx_ar_inv_proc(p_recordset OUT SYS_REFCURSOR);
END;
Package body definition:
CREATE OR REPLACE PACKAGE BODY XX_BI_PKG IS
PROCEDURE xxnsn_ar_inv_proc(p_recordset OUT sys_refcursor)
AS
BEGIN
OPEN p_recordset FOR SELECT RCT.trx_date
,COUNT(RCT.customer_trx_id)
,SUM(APS.amount_due_original)
FROM ra_customer_trx_all RCT
,ar_payment_schedules_all APS
WHERE RCT.customer_trx_id = APS.customer_trx_id
AND APS.class = 'INV'
AND APS.status = 'OP'
GROUP BY RCT.trx_date;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END xx_ar_inv_proc;
END;
The following PL/SQL statement is used to harness the above Oracle stored procedure:
DECLARE
v_cursor sys_refcursor;
v_date varchar2(100);
v_count number(12);
v_amount number(12);
BEGIN
XX_BI_PKG.xx_ar_inv_proc(p_recordset =>v_cursor);
LOOP
FETCH v_cursor
INTO v_date, v_count, v_amount;
EXIT WHEN v_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_date ' ' v_count ' ' v_amount);
END LOOP;
CLOSE v_cursor;
END;
Tuesday, February 3, 2009
How to Hide the Forgot My Password Link in R12?
To personalize the R12 login page, first set the following profile options to "Yes"
1) Personalize Self-Service Defn
2) FND: Personalization Region Link Enabled
Now log out from the application and login again.The personalization links will appear in the login page.
1) Click on the Personalize Table Layout:(region14) link which is right above the login field.
2) Look for the Row Layout: (ForgotPasswordUrlRowLayoutRN1 row
3) Click on Personalize link (pencil)6. Look for the render field and change it to false.
4) Log out and login again to see the changes..
Ref: Metalink note:603787.1
1) Personalize Self-Service Defn
2) FND: Personalization Region Link Enabled
Now log out from the application and login again.The personalization links will appear in the login page.
1) Click on the Personalize Table Layout:(region14) link which is right above the login field.
2) Look for the Row Layout: (ForgotPasswordUrlRowLayoutRN1 row
3) Click on Personalize link (pencil)6. Look for the render field and change it to false.
4) Log out and login again to see the changes..
Ref: Metalink note:603787.1
Subscribe to:
Posts (Atom)
