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;

Create Employee Contact in Oracle Apps

/* Formatted on 2017/04/12 15:35 (Formatter Plus v4.8.8) */
DECLARE
ln_contact_rel_id per_contact_relationships.contact_relationship_id%TYPE;
ln_ctr_object_ver_num per_contact_relationships.object_version_number%TYPE;
ln_contact_person per_all_people_f.person_id%TYPE;
ln_object_version_number per_contact_relationships.object_version_number%TYPE;
ld_per_effective_start_date DATE;
ld_per_effective_end_date DATE;
lc_full_name per_all_people_f.full_name%TYPE;
ln_per_comment_id per_all_people_f.comment_id%TYPE;
lb_name_comb_warning BOOLEAN;
lb_orig_hire_warning BOOLEAN;
BEGIN
— Create Employee Contact
— ————————————-
hr_contact_rel_api.create_contact
( — Input data elements
— —————————–
p_start_date => sysdate,
p_business_group_id => fnd_profile.VALUE
(‘PER_BUSINESS_GROUP_ID’),
p_person_id => XX, — Number field
p_contact_type => ‘M’,
p_date_start => TO_DATE (’12-Apr-2017′),
p_last_name => ‘XYZ’,
p_first_name => ‘XX’,
p_personal_flag => ‘Y’,
— Output data elements
— ——————————–
p_contact_relationship_id => ln_contact_rel_id,
p_ctr_object_version_number => ln_ctr_object_ver_num,
p_per_person_id => ln_contact_person,
p_per_object_version_number => ln_object_version_number,
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_name_combination_warning => lb_name_comb_warning,
p_orig_hire_warning => lb_orig_hire_warning
);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
DBMS_OUTPUT.put_line (SQLERRM);
END;

API to Create Supplier

/* Formatted on 2017/04/12 15:39 (Formatter Plus v4.8.8) */
 -- API to Create Supplier

DECLARE
   l_vendor_rec      ap_vendor_pub_pkg.r_vendor_rec_type;
   l_return_status   VARCHAR2 (10);
   l_msg_count       NUMBER;
   l_msg_data        VARCHAR2 (1000);
   l_vendor_id       NUMBER;
   l_party_id        NUMBER;
BEGIN
-- --------------
-- Required
-- --------------
   l_vendor_rec.segment1 := '0000000001';
   l_vendor_rec.vendor_name := 'XYZ';
-- -------------
-- Optional
-- --------------
   l_vendor_rec.match_option := 'R';
   pos_vendor_pub_pkg.create_vendor (
                                     p_vendor_rec         => l_vendor_rec,
                                     x_return_status      => l_return_status,
                                     x_msg_count          => l_msg_count,
                                     x_msg_data           => l_msg_data,
                                     x_vendor_id          => l_vendor_id,
                                     x_party_id           => l_party_id
                                    );
   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      DBMS_OUTPUT.put_line (SQLERRM);
END;

Oracle Apps Inventory Tables

Following are important tables in Oracle Apps Inventory

MTL_SYSTEM_ITEMS_B
This table holds the definitions for inventory items, engineering items, and purchasing items. The primary key for an item is the INVENTORY_ITEM_ID and ORGANIZATION_ID.

MTL_ITEM_STATUS
This is the definition table for material status codes. Status code is a required item attribute. It indicates the status of an item, i.e., Active, Pending, Obsolete.

MTL_UNITS_OF_MEASURE_TL
This is the definition table for both the 25-character and the 3-character units of measure. The base_uom_flag indicates if the unit of measure is the primary unit of measure for the uom_class. Oracle Inventory uses this table to keep track of the units of measure used to transact an item.

MTL_ITEM_LOCATIONS
This is the definition table for stock locators. The associated attributes describe which subinventory this locator belongs to, what the locator physical capacity is, etc.

MTL_ITEM_CATEGORIES
This table stores inventory item assignments to categories within a category set.

MTL_CATEGORIES_B 
This is the code combinations table for item categories.

MTL_CATEGORIES_B and MTL_CATEGORIES_TL. MTL_CATEGORIES_TL table holds translated Description for Categories.

MTL_CATEGORY_SETS_B 
It contains the entity definition for category sets.

MTL_DEMAND
This table stores demand and reservation information used in Available To Promise, Planning and other Manufacturing functions. There are three major row types stored in the table: Summary Demand rows,Open Demand Rows, and Reservation Rows.

MTL_SECONDARY_INVENTORIES 
This is the definition table for the subinventory.

MTL_ONHAND_QUANTITIES
It stores quantity on hand information by control level and location.

MTL_TRANSACTION_TYPES
It contains seeded transaction types and the user defined ones.

MTL_MATERIAL_TRANSACTIONS 
This table stores a record of every material transaction or cost update performed in Inventory.

MTL_ITEM_ATTRIBUTES
This table stores information on item attributes.

MTL_ITEM_CATALOG_GROUPS_B 
This is the code combinations table for item catalog groups.

MTL_ITEM_REVISIONS_B 
It stores revision levels for an inventory item.

MTL_CUSTOMER_ITEMS 
It stores customer item information for a specific customer. Each record can be defined at one of the following levels: Customer, Address Category, and Address. The customer item definition is organization independent.

MTL_SYSTEM_ITEMS_INTERFACE 
It temporarily stores the definitions for inventory items, engineering items and purchasing items before loading this information into Oracle Inventory.

MTL_TRANSACTIONS_INTERFACE 
It allows calling applications to post material transactions (movements, issues, receipts etc. to Oracle Inventory  transaction module.

MTL_ITEM_REVISIONS_INTERFACE
It temporarily stores revision levels for an inventory item before loading this information into Oracle Inventory.

MTL_ITEM_CATEGORIES_INTERFACE
This table temporarily stores data about inventory item assignments to category sets and categories before loading this information into Oracle Inventory.

MTL_DEMAND_INTERFACE 
It is the interface point between non-Inventory applications and the Inventory demand module. Records inserted into this table are processed by the Demand Manager concurrent program.

MTL_INTERFACE_ERRORS
It stores errors that occur during the item interface process reporting where the errors occurred along with the error messages.

MTL_PARAMETERS
It maintains a set of default options like general ledger accounts; locator, lot, and serial controls, inter-organization options, costing method, etc. for each organization defined in Oracle Inventory.

Register Table in Oracle Apps EBS

Create custom table on Custom Scheme

After that create synonym in APPS scheme.

CREATE TABLE XX_XTR_BOND_MASTER

(

BOND_CODE_ID          NUMBER                  NOT NULL,

BOND_CODE             VARCHAR2(20 BYTE)       NOT NULL,

BOND_NAME             VARCHAR2(200 BYTE),

TENURE                NUMBER,

HOLIDAY_CONSESSION    VARCHAR2(2 BYTE),

INTEREST_FREQUENCY    VARCHAR2(2 BYTE),

INTEREST_PAY_DATE     DATE,

EFFECTIVE_START_DATE  DATE,

EFFECTIVE_END_DATE    DATE,

LAST_UPDATE_DATE      DATE                    NOT NULL,

LAST_UPDATED_BY       NUMBER                  NOT NULL,

LAST_UPDATE_LOGIN     NUMBER,

CREATION_DATE         DATE                    NOT NULL,

CREATED_BY            NUMBER(15)              NOT NULL,

ATTRIBUTE1            VARCHAR2(150 BYTE),

ATTRIBUTE2            VARCHAR2(150 BYTE),

ATTRIBUTE3            VARCHAR2(150 BYTE),

ATTRIBUTE4            VARCHAR2(150 BYTE),

ATTRIBUTE5            VARCHAR2(150 BYTE),

ATTRIBUTE6            VARCHAR2(150 BYTE),

ATTRIBUTE7            VARCHAR2(150 BYTE),

ATTRIBUTE8            VARCHAR2(150 BYTE),

ATTRIBUTE9            VARCHAR2(150 BYTE),

ATTRIBUTE10           VARCHAR2(150 BYTE),

ATTRIBUTE11           VARCHAR2(150 BYTE),

ATTRIBUTE12           VARCHAR2(150 BYTE),

ATTRIBUTE13           VARCHAR2(150 BYTE),

ATTRIBUTE14           VARCHAR2(150 BYTE),

ATTRIBUTE15           VARCHAR2(150 BYTE),

ATTRIBUTE_CATEGORY1   VARCHAR2(250 BYTE)

);

CREATE SYNONYM APPS.XX_XTR_BOND_MASTER FOR XX_XTR_BOND_MASTER;

BEGIN

AD_DD.REGISTER_TABLE(‘XX’,’XX_XTR_BOND_MASTER’,’T’);

END;

BEGIN

AD_DD.DELETE_TABLE(‘XX’,’XX_XTR_BOND_MASTER’);

END;

BEGIN

AD_DD.REGISTER_COLUMN(‘XXREC’,’XX_XTR_BOND_MASTER’,’BOND_CODE_ID’,1,’NUMBER’,100,’N’,’N’);

AD_DD.REGISTER_COLUMN(‘XXREC’,’XX_XTR_BOND_MASTER’,’BOND_CODE’,2,’VARCHAR2′,20,’N’,’N’);

AD_DD.REGISTER_COLUMN(‘XXREC’,’XX_XTR_BOND_MASTER’,’BOND_NAME’,3,’VARCHAR2′,200,’Y’,’N’);

AD_DD.REGISTER_COLUMN(‘XXREC’,’XX_XTR_BOND_MASTER’,’TENURE’,4,’NUMBER’,100,’Y’,’N’);

AD_DD.REGISTER_COLUMN(‘XXREC’,’XX_XTR_BOND_MASTER’,’HOLIDAY_CONSESSION’,5,’VARCHAR2′,2,’Y’,’N’);

AD_DD.REGISTER_COLUMN(‘XXREC’,’XX_XTR_BOND_MASTER’,’INTEREST_FREQUENCY’,6,’VARCHAR2′,2,’Y’,’N’);

AD_DD.REGISTER_COLUMN(‘XXREC’,’XX_XTR_BOND_MASTER’,’INTEREST_PAY_DATE’,7,’DATE’,20,’Y’,’N’);

AD_DD.REGISTER_COLUMN(‘XXREC’,’XX_XTR_BOND_MASTER’,’EFFECTIVE_START_DATE’,8,’DATE’,20,’Y’,’N’);

AD_DD.REGISTER_COLUMN(‘XXREC’,’XX_XTR_BOND_MASTER’,’EFFECTIVE_END_DATE’,9,’DATE’,20,’Y’,’N’);

AD_DD.REGISTER_COLUMN(‘XXREC’,’XX_XTR_BOND_MASTER’,’LAST_UPDATE_DATE’,10,’DATE’,20,’N’,’N’);

AD_DD.REGISTER_COLUMN(‘XXREC’,’XX_XTR_BOND_MASTER’,’LAST_UPDATED_BY’,11,’NUMBER’,20,’N’,’N’);

AD_DD.REGISTER_COLUMN(‘XXREC’,’XX_XTR_BOND_MASTER’,’LAST_UPDATE_LOGIN’,12,’NUMBER’,20,’Y’,’N’);

AD_DD.REGISTER_COLUMN(‘XXREC’,’XX_XTR_BOND_MASTER’,’CREATION_DATE’,13,’DATE’,20,’N’,’N’);

AD_DD.REGISTER_COLUMN(‘XXREC’,’XX_REC_XTR_BOND_MASTER’,’CREATED_BY’,14,’NUMBER’,20,’N’,’N’);

AD_DD.REGISTER_COLUMN(‘XXREC’,’XX_XTR_BOND_MASTER’,’ATTRIBUTE1′,15,’VARCHAR2′,250,’N’,’N’);

AD_DD.REGISTER_COLUMN(‘XXREC’,’XX_XTR_BOND_MASTER’,’ATTRIBUTE2′,16,’VARCHAR2′,250,’N’,’N’);

AD_DD.REGISTER_COLUMN(‘XXREC’,’XX_XTR_BOND_MASTER’,’ATTRIBUTE3′,17,’VARCHAR2′,250,’N’,’N’);

AD_DD.REGISTER_COLUMN(‘XXREC’,’XX_XTR_BOND_MASTER’,’ATTRIBUTE4′,18,’VARCHAR2′,250,’N’,’N’);

AD_DD.REGISTER_COLUMN(‘XXREC’,’XX_XTR_BOND_MASTER’,’ATTRIBUTE5′,19,’VARCHAR2′,250,’N’,’N’);

AD_DD.REGISTER_COLUMN(‘XXREC’,’XX_XTR_BOND_MASTER’,’ATTRIBUTE6′,20,’VARCHAR2′,250,’N’,’N’);

AD_DD.REGISTER_COLUMN(‘XXREC’,’XX_XTR_BOND_MASTER’,’ATTRIBUTE7′,21,’VARCHAR2′,250,’N’,’N’);

AD_DD.REGISTER_COLUMN(‘XXREC’,’XX_XTR_BOND_MASTER’,’ATTRIBUTE8′,22,’VARCHAR2′,250,’N’,’N’);

AD_DD.REGISTER_COLUMN(‘XXREC’,’XX_XTR_BOND_MASTER’,’ATTRIBUTE9′,23,’VARCHAR2′,250,’N’,’N’);

AD_DD.REGISTER_COLUMN(‘XXREC’,’XX_XTR_BOND_MASTER’,’ATTRIBUTE10′,24,’VARCHAR2′,250,’N’,’N’);

AD_DD.REGISTER_COLUMN(‘XXREC’,’XX_XTR_BOND_MASTER’,’ATTRIBUTE11′,25,’VARCHAR2′,250,’N’,’N’);

AD_DD.REGISTER_COLUMN(‘XXREC’,’XX_XTR_BOND_MASTER’,’ATTRIBUTE12′,26,’VARCHAR2′,250,’N’,’N’);

AD_DD.REGISTER_COLUMN(‘XXREC’,’XX_XTR_BOND_MASTER’,’ATTRIBUTE13′,27,’VARCHAR2′,25,’N’,’N’);

AD_DD.REGISTER_COLUMN(‘XXREC’,’XX_XTR_BOND_MASTER’,’ATTRIBUTE14′,28,’VARCHAR2′,250,’N’,’N’);

AD_DD.REGISTER_COLUMN(‘XXREC’,’XX_XTR_BOND_MASTER’,’ATTRIBUTE15′,29,’VARCHAR2′,250,’N’,’N’);

AD_DD.REGISTER_COLUMN(‘XXREC’,’XX_XTR_BOND_MASTER’,’ATTRIBUTE_CATEGORY1′,30,’VARCHAR2′,250,’N’,’N’);

END;