How to use data loader?

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

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.

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

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

TCA..Useful Scripts !!!!

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

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

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.