Disable/Enable Oracle Application Login User API

How to Disable(end date) large number of Users in Oracle Apps
How to end date large number of Users - fnd_users in Oracle Apps

To Disable/Enable bulk number of users in oracle Applications, we have a API

apps.fnd_user_pkg.EnableUser =>To Enable Users
apps.fnd_user_pkg.DisableUser =>To Disable Users,

Below is the syntax which can used to Huge number of Users in one GO !!
-------------------------------------------------------------------------------------

declare cursor cur1 is
select user_name from apps.fnd_user where LOWER(user_name) Not IN ('username','username', .......);
begin
for all_user in cur1 loop
apps.fnd_user_pkg.EnableUser(all_user.user_name);
commit;
end loop;
End;


-------------------------------------------------------------------------------------------


declare cursor cur1 is
select user_name from apps.fnd_user where LOWER(user_name) Not IN ('username','username', .......);
begin
for all_user in cur1 loop
apps.fnd_user_pkg.DisableUser(all_user.user_name);
commit;
end loop;
End;

Delete Responsibility From Backend in Oracle Apps

/* Formatted on 2017/04/20 12:15 (Formatter Plus v4.8.8) */
DECLARE
   v_user_name             VARCHAR2 (100) := 'SAJAL';
   v_responsibility_name   VARCHAR2 (100) := 'Application Developer';
   v_application_name      VARCHAR2 (100) := NULL;
   v_responsibility_key    VARCHAR2 (100) := NULL;
   v_security_group        VARCHAR2 (100) := NULL;
BEGIN
   SELECT fa.application_short_name, fr.responsibility_key,
          frg.security_group_key
     INTO v_application_name, v_responsibility_key,
          v_security_group
     FROM fnd_responsibility fr,
          fnd_application fa,
          fnd_security_groups frg,
          fnd_responsibility_tl frt
    WHERE fr.application_id = fa.application_id
      AND fr.data_group_id = frg.security_group_id
      AND fr.responsibility_id = frt.responsibility_id
      AND frt.LANGUAGE = USERENV ('LANG')
      AND frt.responsibility_name = v_responsibility_name;

   fnd_user_pkg.delresp (username            => v_user_name,
                         resp_app            => v_application_name,
                         resp_key            => v_responsibility_key,
                         security_group      => v_security_group
                        );
   COMMIT;
   DBMS_OUTPUT.put_line (   'Responsiblity '
                         || v_responsibility_name
                         || ' is removed from the user '
                         || v_user_name
                         || ' Successfully'
                        );
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line
         (   'Error encountered while deleting responsibilty from the user and the error is '
          || SQLERRM
         );
END;
/

Delete Request Set from Backend in Oracle Apps

BEGIN
FND_SET.DELETE_SET(request_set => 'XX_RG', application => 'XXAPPL');
COMMIT;
end;

In the code above, XX_RG is an example request set name and XXAPPL is an example application name.

Delete Concurrent Program and Executable from Backend in Oracle Apps

DECLARE
   l_prog_short_name   VARCHAR2 (240);
   l_exec_short_name   VARCHAR2 (240);
   l_appl_full_name    VARCHAR2 (240);
   l_appl_short_name   VARCHAR2 (240);
   l_del_prog_flag     VARCHAR2 (1) := 'Y'; --Set flag whether to delete Concurrent program or not
   l_del_exec_flag     VARCHAR2 (1) := 'Y'; --Set flag whether to delete executable or not
BEGIN
   --
   -- set concurrent program and executable short name
   --
   l_prog_short_name := 'XX_TEST_CP';             -- Concurrent program short name
   l_exec_short_name := 'XX_TEST_EXEC';           -- Executable short name
   l_appl_full_name := 'XX Custom Application';   -- Application full name
   l_appl_short_name := 'XXCUST';                 -- Application Short name

   --
   -- Check if the program exists. if found, delete the program
   --
   IF     fnd_program.program_exists (l_prog_short_name, l_appl_short_name)
      AND fnd_program.executable_exists (l_exec_short_name, l_appl_short_name)
   THEN

      IF l_del_prog_flag = 'Y'
 THEN
      --
      --API call to delete Concurrent Program
      --
      fnd_program.delete_program (l_prog_short_name, l_appl_full_name);
 --
 END IF;
 --
      IF l_del_exec_flag = 'Y'
 THEN
      --
      --API call to delete Executable
      --
      fnd_program.delete_executable (l_exec_short_name, l_appl_full_name);
 --
 END IF;
      COMMIT;
 --
      DBMS_OUTPUT.put_line ('Concurrent Program '||l_prog_short_name || ' deleted successfully');
      DBMS_OUTPUT.put_line ('Executable '||l_exec_short_name || ' deleted successfully');
   --
   -- if the program does not exist in the system
   --
   ELSE
      DBMS_OUTPUT.put_line (l_prog_short_name ||' or '||l_exec_short_name|| ' not found');
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Error while deleting: ' || SQLERRM);
END;

AP Invoice Payment Status Code in oarcle

SELECT PAYMENT_STATUS_FLAG FROM AP_INVOICE_ALL
WHERE INVOICE_NUM = '<Invoice_number>' ;



Payment_status_flag is available at header level, Statuses will be
'Y'- Fully paid
'P'- Partially Paid
'N'- Not Paid

API to Create Supplier Site

/* Formatted on 2017/04/12 15:54 (Formatter Plus v4.8.8) */
DECLARE
   l_vendor_site_rec   ap_vendor_pub_pkg.r_vendor_site_rec_type;
   lc_return_status    VARCHAR2 (10);
   ln_msg_count        NUMBER;
   lc_msg_data         VARCHAR2 (4000);
   ln_vendor_site_id   NUMBER;
   ln_party_site_id    NUMBER;
   ln_location_id      NUMBER;
BEGIN
-- ---------------
-- Required
-- ---------------
   l_vendor_site_rec.vendor_id := 40000;
   l_vendor_site_rec.vendor_site_code := 'Supplier_Site';
   l_vendor_site_rec.address_line1 := '05 IFFCO Chowk';
   l_vendor_site_rec.city := 'Gurgaon';
   l_vendor_site_rec.country := 'IND';
   l_vendor_site_rec.org_id := 182;
-- --------------
-- Optional
-- --------------
   l_vendor_site_rec.purchasing_site_flag := 'N';
   l_vendor_site_rec.pay_site_flag := 'N';
   l_vendor_site_rec.rfq_only_site_flag := 'N';
   pos_vendor_pub_pkg.create_vendor_site
                                     (
-- ------------------------------
-- Input data elements
-- ------------------------------
                                      p_vendor_site_rec      => l_vendor_site_rec,
-- ---------------------------------
-- Output data elements
-- ---------------------------------
                                      x_return_status        => lc_return_status,
                                      x_msg_count            => ln_msg_count,
                                      x_msg_data             => lc_msg_data,
                                      x_vendor_site_id       => ln_vendor_site_id,
                                      x_party_site_id        => ln_party_site_id,
                                      x_location_id          => ln_location_id
                                     );
   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      DBMS_OUTPUT.put_line (SQLERRM);
END;

Create Employee from Backend in Oracle Apps API

/* Formatted on 2017/04/12 15:31 (Formatter Plus v4.8.8) */
DECLARE
lc_employee_number per_all_people_f.employee_number%TYPE
:= ‘XX_01’;
ln_person_id per_all_people_f.person_id%TYPE;
ln_assignment_id per_all_assignments_f.assignment_id%TYPE;
ln_object_ver_number per_all_assignments_f.object_version_number%TYPE;
ln_asg_ovn NUMBER;
ld_per_effective_start_date per_all_people_f.effective_start_date%TYPE;
ld_per_effective_end_date per_all_people_f.effective_end_date%TYPE;
lc_full_name per_all_people_f.full_name%TYPE;
ln_per_comment_id per_all_people_f.comment_id%TYPE;
ln_assignment_sequence per_all_assignments_f.assignment_sequence%TYPE;
lc_assignment_number per_all_assignments_f.assignment_number%TYPE;
lb_name_combination_warning BOOLEAN;
lb_assign_payroll_warning BOOLEAN;
lb_orig_hire_warning BOOLEAN;
BEGIN
hr_employee_api.create_employee
( — Input data elements
— ——————————
p_hire_date => sysdate,
p_business_group_id => fnd_profile.value_specific
(‘PER_BUSINESS_GROUP_ID’),
p_last_name => ‘TEST’,
p_first_name => ‘XX’,
p_middle_names => NULL,
p_sex => ‘M’,
p_national_identifier => ‘183-09-6723′,
p_date_of_birth => TO_DATE (’28-NOV-1989’),
p_known_as => ‘SAJAL’,
— Output data elements
— ——————————–
p_employee_number => lc_employee_number,
p_person_id => ln_person_id,
p_assignment_id => ln_assignment_id,
p_per_object_version_number => ln_object_ver_number,
p_asg_object_version_number => ln_asg_ovn,
p_per_effective_start_date => ld_per_effective_start_date,
p_per_effective_end_date => ld_per_effective_end_date,
p_full_name => lc_full_name,
p_per_comment_id => ln_per_comment_id,
p_assignment_sequence => ln_assignment_sequence,
p_assignment_number => lc_assignment_number,
p_name_combination_warning => lb_name_combination_warning,
p_assign_payroll_warning => lb_assign_payroll_warning,
p_orig_hire_warning => lb_orig_hire_warning
);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
DBMS_OUTPUT.put_line (SQLERRM);
END;