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.