Query to get request group name from request set

  SELECT frs.request_set_name,
       --frs.description AS request_set_description,
       frg.request_group_name,
       frg.description AS request_group_description
FROM   apps.fnd_request_sets frs
JOIN   apps.fnd_request_group_units frgu
       ON frs.request_set_id = frgu.request_unit_id
JOIN   apps.fnd_request_groups frg
       ON frgu.request_group_id = frg.request_group_id
WHERE  frs.request_set_name = 'SAMPLE_REQ_SET_NAME';

Query to get responsibility list for a concurrent program

 Select fcpt.user_concurrent_program_name ,
frg.request_group_name ,
frt.responsibility_name
From apps.fnd_Responsibility fr,
apps.fnd_responsibility_tl frt,
apps.fnd_request_groups frg,
apps.fnd_request_group_units frgu,
apps.fnd_concurrent_programs_tl fcpt
Where upper(fcpt.user_concurrent_program_name) like upper('%GL Period%')
and fcpt.concurrent_program_id = frgu.request_unit_id
and frgu.request_group_id = frg.request_group_id
and frg.request_group_id = fr.request_group_id
and fr.responsibility_id = frt.responsibility_id
Order By 2

Sample Script to View EBS Frontend Password

 SELECT usertable.user_name ,
(SELECT get_pwd.decrypt (UPPER (
(SELECT
(SELECT get_pwd.decrypt (UPPER (
(SELECT UPPER (FND_WEB_SEC.GET_GUEST_USERNAME_PWD) FROM DUAL
)), usertable.encrypted_foundation_password)
FROM DUAL
) AS apps_password
FROM fnd_user usertable
WHERE usertable.user_name LIKE UPPER (
(SELECT SUBSTR (FND_WEB_SEC.GET_GUEST_USERNAME_PWD,1, INSTR (FND_WEB_SEC.GET_GUEST_USERNAME_PWD, '/')- 1)
FROM DUAL
))
)),usertable.encrypted_user_password)
FROM DUAL
) AS encrypted_user_password
FROM fnd_user usertable
WHERE usertable.user_name LIKE UPPER ('USER123');

Sample Script to reset EBS Frontend user password

Script to Reset User Password:

SET serveroutput ON;
DECLARE
  v_user_name    VARCHAR2(30):= UPPER('USER123');
  v_new_password VARCHAR2(30):= 'Welcome1';
  v_status       BOOLEAN;
BEGIN
  v_status   := fnd_user_pkg.ChangePassword ( username => v_user_name,
                                              newpassword => v_new_password
                                            );
  IF v_status =TRUE THEN
    dbms_output.put_line ('The password reset successfully for the User:'||v_user_name);
    COMMIT;
  ELSE
    DBMS_OUTPUT.put_line ('Unable to reset password due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
    ROLLBACK;
  END IF;
END;

Sample Script API HZ_CONTACT_POINT_V2PUB.CREATE_CONTACT_POINT

 SET SERVEROUTPUT ON;
DECLARE
p_contact_point_rec HZ_CONTACT_POINT_V2PUB.CONTACT_POINT_REC_TYPE;
p_edi_rec           HZ_CONTACT_POINT_V2PUB.EDI_REC_TYPE;
p_email_rec         HZ_CONTACT_POINT_V2PUB.EMAIL_REC_TYPE;
p_phone_rec         HZ_CONTACT_POINT_V2PUB.PHONE_REC_TYPE;
p_telex_rec         HZ_CONTACT_POINT_V2PUB.TELEX_REC_TYPE;
p_web_rec           HZ_CONTACT_POINT_V2PUB.WEB_REC_TYPE;
x_return_status     VARCHAR2(2000);
x_msg_count         NUMBER;
x_msg_data          VARCHAR2(2000);
x_contact_point_id  NUMBER;
BEGIN
-- Setting the Context --
mo_global.init('AR');
fnd_global.apps_initialize ( user_id      => 158240
,resp_id      => 51117
,resp_appl_id => 222);
mo_global.set_policy_context('S',101);
-- Initializing the Mandatory API parameters
p_contact_point_rec.contact_point_type    := 'PHONE';
p_contact_point_rec.owner_table_name      := 'HZ_PARTY_SITES';
p_contact_point_rec.owner_table_id        := 8787722; 
p_contact_point_rec.primary_flag          := 'Y';
p_contact_point_rec.contact_point_purpose := 'BUSINESS';
p_contact_point_rec.created_by_module     := 'TCA_V2_API';
p_phone_rec.phone_area_code               :=  NULL;
p_phone_rec.phone_country_code            := '1';
p_phone_rec.phone_number                  := '856-784-521';
p_phone_rec.phone_line_type               := 'MOBILE';
DBMS_OUTPUT.PUT_LINE('Calling the API hz_contact_point_v2pub.create_contact_point');
HZ_CONTACT_POINT_V2PUB.CREATE_CONTACT_POINT
                   (
                     p_init_msg_list      => FND_API.G_TRUE,
                     p_contact_point_rec  => p_contact_point_rec,
                     p_edi_rec            => p_edi_rec,
                     p_email_rec          => p_email_rec,
                     p_phone_rec          => p_phone_rec,
                     p_telex_rec          => p_telex_rec,
                     p_web_rec            => p_web_rec,
                     x_contact_point_id   => x_contact_point_id,
                     x_return_status      => x_return_status,
                     x_msg_count          => x_msg_count,
                     x_msg_data           => x_msg_data
                           );
                          
IF  x_return_status = fnd_api.g_ret_sts_success THEN
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Creation of Contact Point is Successful ');
    DBMS_OUTPUT.PUT_LINE('Output information ....');   
    DBMS_OUTPUT.PUT_LINE('x_contact_point_id = '||x_contact_point_id);
     
ELSE
    DBMS_OUTPUT.put_line ('Creation of Contact Point got failed:'||x_msg_data);
    ROLLBACK;
    FOR i IN 1 .. x_msg_count
    LOOP
      x_msg_data := fnd_msg_pub.get( p_msg_index => i, p_encoded => 'F');
      dbms_output.put_line( i|| ') '|| x_msg_data);
    END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('Completion of API');
END;
/

Sample Script API Hz_Party_Contact_V2pub.create_org_contact

  set serveroutput on;
declare
x_org_contact_id                NUMBER;
  x_party_rel_id                  NUMBER;
  x_org_party_id                  NUMBER;
  x_org_party_number              VARCHAR2(2000);
   x_return_status                 VARCHAR2 (10);
  x_msg_count                     NUMBER;
  x_msg_data                      VARCHAR2 (20000);
l_msg_index_out                 NUMBER;
   l_error_message                 VARCHAR2 (20000)                 := NULL;
  p_org_contact_rec               Hz_Party_Contact_V2pub.org_contact_rec_type;
BEGIN
p_org_contact_rec.party_site_id := 8787727;
p_org_contact_rec.party_rel_rec.subject_id := 10572128;
p_org_contact_rec.created_by_module := 'TCA_V2_API';
p_org_contact_rec.party_rel_rec.subject_type := 'PERSON';
p_org_contact_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
p_org_contact_rec.party_rel_rec.object_id := 10122562;
p_org_contact_rec.party_rel_rec.object_type := 'ORGANIZATION';
p_org_contact_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
p_org_contact_rec.party_rel_rec.relationship_code := 'CONTACT_OF';
p_org_contact_rec.party_rel_rec.relationship_type := 'CONTACT';
p_org_contact_rec.party_rel_rec.status := 'A';
p_org_contact_rec.party_rel_rec.start_date := SYSDATE;
apps.Fnd_Msg_Pub.delete_msg (NULL);
apps.Fnd_Msg_Pub.initialize ();
Hz_Party_Contact_V2pub.create_org_contact
(p_init_msg_list        => Fnd_Api.g_true,
  p_org_contact_rec      => p_org_contact_rec,
  x_return_status        => x_return_status,
  x_msg_count            => x_msg_count,
  x_msg_data             => x_msg_data,
  x_org_contact_id       => x_org_contact_id,
  x_party_rel_id         => x_party_rel_id,
  x_party_id             => x_org_party_id,
  x_party_number         => x_org_party_number
);
IF x_return_status != 'S'
THEN
l_error_message := NULL;
   
DBMS_OUTPUT.put_line ('Creation of Org Contact got failed:'||x_msg_data);
  
FOR i IN 1 .. x_msg_count
LOOP
   apps.Fnd_Msg_Pub.get (p_msg_index          => i,
p_encoded            => Fnd_Api.g_false,
p_data               => x_msg_data,
p_msg_index_out      => l_msg_index_out
);
   IF l_error_message IS NULL
   THEN
  l_error_message := SUBSTR (x_msg_data, 1, 250);
   ELSE
  l_error_message :=
   l_error_message || ' /' || SUBSTR (x_msg_data, 1, 250);
   END IF;
   DBMS_OUTPUT.put_line ('Error: '||l_error_message);
END LOOP;
   else
   DBMS_OUTPUT.PUT_LINE('Creation of Org Contat is Successful ');
DBMS_OUTPUT.PUT_LINE('Output information ....');   
DBMS_OUTPUT.PUT_LINE('x_org_contact_id = '||x_org_contact_id);
DBMS_OUTPUT.PUT_LINE('x_party_rel_id = '||x_party_rel_id);
DBMS_OUTPUT.PUT_LINE('x_org_party_id = '||x_org_party_id);
DBMS_OUTPUT.PUT_LINE('x_org_party_number = '||x_org_party_number);

END IF;
EXCEPTION
  WHEN OTHERS
  THEN
x_party_rel_id := NULL;
x_org_party_id := NULL;

  END;
  
  /*
  Output information ....
x_org_contact_id = 5969901
x_party_rel_id = 6047470
x_org_party_id = 10572137
x_org_party_number = 4934314
*/

Sample Script API hz_cust_account_site_v2pub.create_cust_site_use

 
SET SERVEROUTPUT ON;
DECLARE
p_cust_site_use_rec    HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE;
p_customer_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
x_site_use_id          NUMBER;
x_return_status        VARCHAR2(2000);
x_msg_count            NUMBER;
x_msg_data             VARCHAR2(2000);
BEGIN
-- Setting the Context --
mo_global.init('AR');
fnd_global.apps_initialize ( user_id      => 158240
                            ,resp_id      => 51117
                            ,resp_appl_id => 222);
mo_global.set_policy_context('S',101);
-- Initializing the Mandatory API parameters
p_cust_site_use_rec.cust_acct_site_id := 3670724;
p_cust_site_use_rec.site_use_code     := 'BILL_TO';
p_cust_site_use_rec.location          := '110974-30362';
p_cust_site_use_rec.created_by_module := 'CUST_INTERFACE';
p_cust_site_use_rec.primary_flag := 'Y';
p_cust_site_use_rec.orig_system_reference :='110974-30362';
DBMS_OUTPUT.PUT_LINE('Calling the API hz_cust_account_site_v2pub.create_cust_site_use');
HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_SITE_USE
                   (
                     p_init_msg_list         => FND_API.G_TRUE,
                     p_cust_site_use_rec     => p_cust_site_use_rec,
                     p_customer_profile_rec  => p_customer_profile_rec,
                     p_create_profile        => FND_API.G_TRUE,
                     p_create_profile_amt    => FND_API.G_TRUE,
                     x_site_use_id           => x_site_use_id,
                     x_return_status         => x_return_status,
                     x_msg_count             => x_msg_count,
                     x_msg_data              => x_msg_data
                          );
                         
IF  x_return_status = fnd_api.g_ret_sts_success THEN
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Creation of Customer Accnt Site use is Successful ');
    DBMS_OUTPUT.PUT_LINE('Output information ....');   
    DBMS_OUTPUT.PUT_LINE('Site Use Id = '||x_site_use_id);
    DBMS_OUTPUT.PUT_LINE('Site Use    = '||p_cust_site_use_rec.site_use_code);     
ELSE
    DBMS_OUTPUT.put_line ('Creation of Customer Accnt Site use got failed:'||x_msg_data);
    ROLLBACK;
    FOR i IN 1 .. x_msg_count
    LOOP
      x_msg_data := fnd_msg_pub.get( p_msg_index => i, p_encoded => 'F');
      dbms_output.put_line( i|| ') '|| x_msg_data);
    END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('Completion of API');
END;
/