Oracle Apps technical

How to use data loader?

2011-05-10T23:15:00.000-07:00

Step 1: Download and install Data loader tool free version from internet

Step 2: After download install it in your desktop and then click on the data loader icon and start it

Step 3: After the data loader is started, either import the data into the data loader sheet or copy paste it manually as below:

Data loader command and action as below:
TAB – Press the tab key
*DN – Press the down key
*SAVE – Save the record

Please refer the user guide for other commands. Link is given in the data loader window itself.

Step 4: Select the Oracle application window where we are going to upload the data:

Step 5: Select the command group as applicable:

Step 6: Move the cursor to the first field in the oracle applications window

Step 7: Now Start the load as below:

As soon as we click OK, data loader will start loading data into the oracle window which will be visible to us. We can also set the time delay to insert the records between fields.

Step 8: Data load completed successfully

Getting actual sales order line number from oe_order_lines_all table

2011-04-07T21:51:00.001-07:00

SELECT CASE WHEN (t1.service_number IS NOT NULL AND t1.option_number IS NOT NULL
AND t1.component_number IS NOT NULL)
THEN t1.line_number
|| '.'
|| t1.shipment_number
|| '.'
|| t1.option_number
|| '.'
|| t1.component_number
|| '.'
|| t1.service_number
WHEN (t1.service_number IS NOT NULL AND t1.option_number IS NOT NULL AND
t1.component_number IS NULL)
THEN t1.line_number || '.' || t1.shipment_number || '.' || t1.option_number || '..' || t1.service_number
WHEN (t1.service_number IS NOT NULL AND t1.option_number IS NULL AND t1.component_number IS NOT NULL)
THEN t1.line_number || '.' || t1.shipment_number || '..' || t1.component_number || '.' || t1.service_number
WHEN (t1.service_number IS NOT NULL AND t1.option_number IS NULL AND t1.component_number IS NULL)
THEN t1.line_number || '.' || t1.shipment_number || '...' || t1.service_number
WHEN (t1.service_number IS NULL AND t1.option_number IS NOT NULL AND t1.component_number IS NOT NULL)
THEN t1.line_number || '.' || t1.shipment_number || '.' || t1.option_number || '.' || t1.component_number
WHEN (t1.service_number IS NULL AND t1.option_number IS NOT NULL AND t1.component_number IS NULL)
THEN t1.line_number || '.' || t1.shipment_number || '.' || t1.option_number
WHEN (t1.service_number IS NULL AND t1.option_number IS NULL AND t1.component_number IS NOT NULL)
THEN t1.line_number || '.' || t1.shipment_number || '..' || t1.component_number
ELSE t1.line_number || '.' || t1.shipment_number END so_line#
FROM OE_ORDER_LINES_ALL t1

Changing the profile value dynamically using script.

2011-03-15T04:33:00.000-07:00

DECLARE
v_stat boolean;
v_date varchar2(20);
BEGIN
dbms_output.disable;
dbms_output.enable(100000);
select to_char(sysdate,'DD') into v_date
from dual;
v_stat := FND_PROFILE.SAVE('ASO_QUOTE_DURATION', v_date, 'SITE');
IF v_stat THEN
dbms_output.put_line( 'Profile Value Updated' );
ELSE
dbms_output.put_line( 'Profile value Not updated' );
END IF;
commit;
END;

API to change the FND USER password in Oracle from backend

2011-03-15T04:28:00.000-07:00

declare
c boolean;
begin
c:=fnd_user_pkg.ChangePassword('MSHUNMUGAM','welcome1');
end;

TCA..Useful Scripts !!!!

2011-03-14T23:46:00.000-07:00

To get the list of customers created in a financial year:

SELECT DISTINCT hl.address1 "Customer Name",
hl.address2 "Address2",
hl.address3 "Address3",
hl.city ,
hl.state,
hl.postal_code,
hl.country,
DECODE(hcsa.status,'A','Active','Inactive') status,HCSA.creation_date
FROM apps.hz_parties hp,
apps.hz_party_sites hps,
apps.hz_locations hl,
apps.hz_cust_accounts_all hca,
apps.hz_cust_acct_sites_all hcsa,
apps.hz_cust_site_uses_all hcsu
WHERE hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND hp.party_id = hca.party_id
AND hcsa.party_site_id = hps.party_site_id
AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
AND hca.cust_account_id = hcsa.cust_account_id
AND TO_DATE(TO_CHAR(HCSA.creation_date,'DD-MON-YYYY'),'DD-MON-YYYY') >= '28-JUN-2010'
ORDER BY hl.address1,HCSA.creation_date

To get the list of customers whose credit limit changed in the financial year:

SELECT DISTINCT hl.address1 "Customer Name",
hl.address2 "Address2",
hl.address3 "Address3",
hl.city ,
hl.state,
hl.postal_code,
hl.country,
DECODE(hcsa.status,'A','Active','Inactive') status,
hcpa.last_update_date
FROM apps.hz_parties hp,
apps.hz_party_sites hps,
apps.hz_locations hl,
apps.hz_cust_accounts_all hca,
apps.hz_cust_acct_sites_all hcsa,
apps.hz_cust_site_uses_all hcsu,
apps.hz_customer_profiles cp,
apps.hz_cust_profile_amts hcpa
WHERE hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND hp.party_id = hca.party_id
AND hcsa.party_site_id = hps.party_site_id
AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
AND hca.cust_account_id = hcsa.cust_account_id
and cp.cust_account_id = hca.cust_account_id
AND hcpa.cust_account_profile_id = cp.cust_account_profile_id
AND cp.site_use_id IS NULL
AND hcpa.site_use_id IS NULL
AND TO_DATE(TO_CHAR(hcpa.last_update_date,'DD-MON-YYYY'),'DD-MON-YYYY') >= '28-JUN-2010'
ORDER BY hcpa.last_update_date

Customers with Credit Limit:

SELECT DISTINCT hp.party_name "Customer Name",
DECODE(hp.status,'A','Active','Inactive') status,
hcpa.trx_credit_limit "Trx Credit Limit" ,
hcpa.overall_credit_limit "Overall Credit Limit"
FROM apps.hz_parties hp,
apps.hz_cust_accounts_all hca,
apps.hz_cust_acct_sites_all hcsa,
apps.hz_cust_site_uses_all hcsu,
apps.hz_customer_profiles cp,
apps.hz_cust_profile_amts hcpa
WHERE hp.party_id = hca.party_id
AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
AND hca.cust_account_id = hcsa.cust_account_id
and cp.cust_account_id = hca.cust_account_id
AND hcpa.cust_account_profile_id = cp.cust_account_profile_id
AND cp.site_use_id IS NULL
AND hcpa.site_use_id IS NULL
ORDER BY hp.party_name

Customers with last 12 month gross sales :

select cust.address1 "Customer Name",
cust.address2 "Address1",
cust.address3 "Address3",
cust.city,
cust.state,
cust.postal_code,
cust.country,
nvl(sum(aps.amount_due_original),0) "Gross Sales"
from ar_payment_schedules_all aps,
(select distinct cust_acct.cust_account_id Customer_id,
acct_site.cust_acct_site_id,
cust_acct.account_number Customer_Number,
loc.address1 ,
loc.address2 ,
loc.address3,
loc.city,
loc.state,
loc.country,
loc.postal_code,
site_uses.site_use_id Site_Use_id
from
hz_cust_accounts cust_acct,
hz_parties party,
hz_cust_acct_sites_all acct_site,
hz_party_sites party_site,
hz_locations loc,
hz_cust_site_uses_all site_uses,
fnd_territories_vl terr,
ar_collectors coll,
hz_customer_profiles cp
where cp.cust_account_id = cust_acct.cust_account_id
and cust_acct.party_id = party.party_id
and cp.collector_id = coll.collector_id
and site_uses.status = 'A'
and cust_acct.cust_account_id = acct_site.cust_account_id
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 site_uses.site_use_code = 'BILL_TO'
and site_uses.status = 'A'
and loc.country = terr.territory_code
and cp.cust_account_profile_id = (
select max(cp2.cust_account_profile_id)
from hz_customer_profiles cp2
where cust_acct.cust_account_id = cp2.cust_account_id
and (site_uses.site_use_id = cp2.site_use_id
or
cp2.site_use_id is null
))
order by loc.address1) cust
where aps.customer_id = cust.customer_id
and aps.customer_site_use_id = cust.site_use_id
and aps.invoice_currency_code = 'USD'
and aps.trx_date between
add_months(sysdate, -12) and sysdate
and aps.class not in ('CM', 'PMT')
GROUP BY cust.cust_acct_site_id,cust.address1, cust.address2,
cust.address3 ,
cust.city,
cust.state,
cust.postal_code,
cust.country
ORDER BY cust.address1

DATE parameter dependent on previous parameter value

2011-03-07T03:10:00.000-08:00

Requirement: Requirement is to have a date parameter in the report which should be enabled if the previous parameter value is YES and should be disabled if the previous parameter value is NO:

Solution: Follow below steps to meet the above requirement

Step:1 Create a value set with validation type as NONE:

Step:2 Create a value set with validation type as SPECIAL:

In Edit Information, event type as Validate, give the below code

Step 3:

Now go to the concurrent program definition,

First parameter list down only 'Yes' and 'No' values. Now create a dummy parameter as below and attach the valueset created in the first step here. Default type as SQL Statement and value as below:

SELECT DECODE(:$FLEX$.EXTR_YES_NO,'Yes','1','') FROM DUAL

Step 4:

In the date parameter attach the valueset created in the step 2

Now while submitting the report, if we give Yes as first parameter value, date parameter will be enabled. If we give No, date parameter will be disabled.

Data Conversion / Migration / Interface

2009-11-18T09:16:00.000-08:00

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.

Oracle Receivables Receipts Workbench-FAQ

2009-05-27T21:42:00.000-07:00

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.

Rebranding – R12 Applications

2009-05-07T23:18:00.001-07:00

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).

How to Use the SYS_REFCURSOR and REF CURSOR in Oracle Packages and Procedures

2009-02-04T21:05:00.000-08:00

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;

How to Hide the Forgot My Password Link in R12?

2009-02-03T01:06:00.000-08:00

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