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.