Oracle Apps API's

Contents Included in this Page:

1- API to Create Supplier

2- API to Update Salary - PER_PAY_PROPOSALS

3- API to create apps user.

4- API to create bank account.

5- API to create Employee.

6- API to reset application password

7- API to get description of GL Account

8- API to initiate Apps session from Toad or sql.

9- API to register Executable

10- API to register Concurrent Program

11- API to attach Concurrent Porgram to Request Group
12- API to create inventory item.
13-API to update inventory item's Template

** We can get complete details of every module's API in its Implementation guide.
** Tables related information and its constraints can get from etrm.oracle.com

Details:

1- 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          := '0000235916';    

  --* If the segment1 is auto generated than this no will not be

  -- updated and the auto generated will come.

   l_vendor_rec.vendor_name   := 'TEST_SUPP';

   -- Optional

   l_vendor_rec.match_option  :='R';

   pos_vendor_pub_pkg.create_vendor

   (

        -- Input Parameters

        p_vendor_rec      => l_vendor_rec,

        -- Output Parameters

        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;

2- API to Update Salary - PER_PAY_PROPOSALS

DECLARE
   l_inv_next_sal_date_warning   BOOLEAN;

   DBMS_OUTPUT.put_line (i.pay_proposal_id || ' has been Updated !!!!');

CURSOR c1

      IS

      SELECT * FROM per_pay_proposals;

   l_proposed_salary_warning     BOOLEAN;

   l_approved_warning            BOOLEAN;

   l_payroll_warning             BOOLEAN;

BEGIN

   
   FOR i IN c1 LOOP

      l_inv_next_sal_date_warning := NULL;

      l_proposed_salary_warning := NULL;

      l_approved_warning := NULL;

      l_payroll_warning := NULL;

      hr_maintain_proposal_api.update_salary_proposal 

      (

         p_pay_proposal_id             => i.pay_proposal_id,

         p_change_date                 => i.change_date,

         p_proposed_salary_n           => 10000,

         p_object_version_number       => i.object_version_number,

         p_multiple_components         => 'N',

         p_approved                    => 'Y',

         p_validate                    => FALSE,

         p_inv_next_sal_date_warning   => l_inv_next_sal_date_warning,

         p_proposed_salary_warning     => l_proposed_salary_warning,

         p_approved_warning            => l_approved_warning,

         p_payroll_warning             => l_payroll_warning

      );

   End Loop;

       

         Exception   When Others then Rollback;

Dbms_output.put_line(Sqlerrm);

End;

3 - API to CREATE FND User

Declare

lc_user_name                      VARCHAR2(100)   := 'Mrahman';

lc_user_password               VARCHAR2(100)   := 'Oracle123';         

ld_user_start_date               DATE                     := TO_DATE('23-JUN-2012');  

ld_user_end_date                VARCHAR2(100)  := NULL;  

ld_password_date               VARCHAR2(100)  := TO_DATE('23-JUN-2012');  

ld_password_lifespan_days  NUMBER              := 90;  

ln_person_id                         NUMBER              := 32979;
lc_email_address                  VARCHAR2(100) := 'mrahman@test.com';

BEGIN

fnd_user_pkg.createuser   

( 

x_user_name    => lc_user_name,     

x_owner  => NULL,    

x_unencrypted_password     => lc_user_password,    

x_start_date                              => ld_user_start_date,    

x_end_date                               => ld_user_end_date,    

x_password_date                    => ld_password_date,    

x_password_lifespan_days   => ld_password_lifespan_days,    

x_employee_id                        => ln_person_id,    

x_email_address                     => lc_email_address 

); 

Commit;

Exception   When Others then Rollback;

Dbms_output.put_line(Sqlerrm);

End;

4 -- API to Create Bank

DECLARE    lc_output                VARCHAR2(3000);
   lc_msg_dummy     VARCHAR2(3000);
   lc_return_status     VARCHAR2(3000);
   lc_msg_data           VARCHAR2(3000);

   ln_bank_id             NUMBER;
   ln_msg_count        NUMBER;
   lr_extbank_rec       apps.iby_ext_bankacct_pub.extbank_rec_type;
   lr_response_rec     apps.iby_fndcpt_common_pub.result_rec_type;

BEGIN    lc_return_status    := '';
   ln_msg_count       := '';
   lc_msg_data          := '';

   lr_extbank_rec.bank_name       := 'Test Supp Bank';
   lr_extbank_rec.bank_number   := 'TSB0000001';
   lr_extbank_rec.country_code    := 'US';

   apps.fnd_msg_pub.delete_msg(NULL);
   apps.fnd_msg_pub.initialize();

   IBY_EXT_BANKACCT_PUB.create_ext_bank
   (       -- ------------------------------            -- Input data elements
           -- ------------------------------            p_api_version       => 1.0,
           p_init_msg_list     => FND_API.G_TRUE,
           p_ext_bank_rec     => lr_extbank_rec,
           -- --------------------------------
           -- Output data elements
           -- --------------------------------            x_bank_id            => ln_bank_id,
           x_return_status   => lc_return_status,
           x_msg_count       => ln_msg_count,
           x_msg_data         => lc_msg_data,
           x_response          => lr_response_rec      );

     lc_output := ' ';

     IF (lc_return_status <> 'S')
     THEN
               FOR i IN 1 .. ln_msg_count
               LOOP
                            apps.fnd_msg_pub.get
                            (       i,
                                    apps.fnd_api.g_false,
                                    lc_msg_data,
                                    lc_msg_dummy
                             );

                            lc_output := lc_output ||
                                                  (TO_CHAR (i) ||
                                                                       ': '  ||
                                                   SUBSTR (lc_msg_data, 1, 250));
             END LOOP;

             apps.fnd_file.put_line
            (apps.fnd_file.output, 'Error Occured while Creating Bank: ');

       END IF;

     COMMIT; 

EXCEPTION           WHEN OTHERS THEN
                        ROLLBACK; 
                        dbms_output.put_line(SQLERRM); 

END; 

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

5 - Oracle HRMS API – Create Employee

API - hr_employee_api.create_employee

Example --

 -- Create Employee
 -- ------------------------- 

DECLARE

 lc_employee_number                       PER_ALL_PEOPLE_F.EMPLOYEE_NUMBER%TYPE    := 'TestEmp';
 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                                         => TO_DATE('08-JUN-2011'),
               p_business_group_id                      => fnd_profile.value_specific('PER_BUSINESS_GROUP_ID'),
               p_last_name                                       => 'TEST',
               p_first_name                                       => 'Rahman',
               p_middle_names                              => NULL,
               p_sex                                                     => 'M',
               p_national_identifier                       => '183-09-6723',
               p_date_of_birth                                 => TO_DATE('03-DEC-1988'),
               p_known_as                                       => 'PRAJ',
               -- 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;
/

SHOW ERR;

6 - To reset application password.

v_flag BOOLEAN;
BEGIN
v_flag := fnd_user_pkg.ChangePassword(‘Test’,’abcd′);
END;

7 - To get the description of GL Account.

Parameters:
1. Chart of Account id
2. Number of the Segment
3. Segment value for which you need the description

SELECT gcc.code_combination_id,apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,1,gcc.segment1) Segment1_desc,  apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,2,gcc.segment2) Segment2_desc,

  apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,3,gcc.segment3) Segment3_desc,

  apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,4,gcc.segment4) Segment4_desc,

  apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,5,gcc.segment5) Segment5_desc

from gl_code_combinations gcc

where gcc.code_combination_id = 12854 -- code combination id

;

8 - API to initiate Apps session from Toad or sql.

begin

FND_GLOBAL.apps_initialize(user_id =>1654,  resp_id =>20434,  resp_appl_id =>101);

end;

9- API to register Executable

BEGIN

        FND_PROGRAM.executable ('XXREQNOTIFTOBUYER' -- executable name

       , 'Payables' -- application

       , 'XX_REQNOTBUY_API' -- short_name

       , 'Executable for Approved requisition notif to Buyer' -- description

       , 'PL/SQL Stored Procedure' -- execution_method

       , 'XXREQNOTIFTOBUYER' -- execution_file_name

       , ''-- subroutine_name

       , '' -- Execution File Path

       , 'US' -- language_code

       ,'');

       COMMIT;

   END;

10- API to register Concurrent Program

BEGIN
 FND_PROGRAM.register('XXREQNOTIFTOBUYER' -- program
, 'Payables' -- application
, 'Y' -- enabled
, 'XX_REQNOTBUY_API' -- short_name
, 'Approved requisition notif to Buyer' -- description
, 'XX_REQNOTBUY_API' -- executable_short_name
, 'Payables' -- executable_application
, ''  -- execution_options
, ''  -- priority
, 'Y' -- save_output
, 'Y' -- print
,  '' -- cols
, ''  -- rows
, ''  -- style
, 'N' -- style_required
, ''  -- printer
, ''  -- request_type
, ''  -- request_type_application
, 'Y' -- use_in_srs
, 'N' -- allow_disabled_values
, 'N' -- run_alone
, 'TEXT' -- output_type
, 'N' -- enable_trace
, 'Y' -- restart
, 'Y' -- nls_compliant
, '' -- icon_name
, 'US'); -- language_code
 COMMIT;
            END;

11- API to attach Concurrent Porgram to Request Group

BEGIN

        FND_PROGRAM.add_to_group('XX_REQNOTBUY_API' -- program_short_name

                , 'Payables' -- application

        , 'All Reports' -- Report Group Name

        , 'SQLAP'); -- Report Group Application

        COMMIT;

 END;

12 - API to create inventory item

DECLARE
l_inventory_item_id number;
l_organization_id number;
l_return_status varchar2(4000);
l_msg_data varchar2(4000);
l_msg_count number;
l_msg_index number;
API_ERROR EXCEPTION;
BEGIN
FND_GLOBAL.APPS_INITIALIZE (USER_ID =>1873,
RESP_ID =>20634,
RESP_APPL_ID =>401);
EGO_ITEM_PUB.PROCESS_ITEM
(
p_api_version => 1.0
,p_init_msg_list => 'T'
,p_commit => 'T' 
,p_transaction_type =>'CREATE'
,p_segment1 =>'xx_testing'
,p_description =>'xx_testing_detail'
,p_long_description =>'same as before'
,p_organization_id =>121
,p_template_id =>19
,p_inventory_item_status_code =>'Active'
,p_approval_status =>'A'
,x_inventory_item_id =>l_inventory_item_id
,x_organization_id =>l_organization_id
,x_return_status =>l_return_status
,x_msg_count =>l_msg_count
,x_msg_data =>l_msg_data
);
if l_return_status = FND_API.G_RET_STS_SUCCESS
THEN
DBMS_OUTPUT.PUT_LINE('Item is created successfully, Inventory Item Id : '||l_inventory_item_id);
commit;
else
DBMS_OUTPUT.PUT_LINE('Item  creatION is failed');
RAISE API_ERROR;
ROLLBACK;
END IF;
-- HANDLE EXCEPTION
EXCEPTION
WHEN API_ERROR
THEN
FOR I IN 1 .. l_msg_count
loop
DBMS_OUTPUT.PUT_LINE(SUBSTR (FND_MSG_PUB.Get(p_encoded=>FND_API.G_FALSE),1,255));
DBMS_OUTPUT.PUT_LINE('MESSAGE IS :'||l_msg_data);
end loop;
when others
THEN
FOR I IN 1 .. l_msg_count
loop
DBMS_OUTPUT.PUT_LINE(SUBSTR (FND_MSG_PUB.Get(p_encoded=>FND_API.G_FALSE),1,255));
DBMS_OUTPUT.PUT_LINE('MESSAGE IS :'||l_msg_data);
end loop;
end;

13 - API to update inventory item's template

declare
 l_item_table      ego_item_pub.item_tbl_type;
   x_item_table      ego_item_pub.item_tbl_type;
   x_return_status   VARCHAR2 (1);
   x_msg_count       NUMBER (10);
   x_msg_data        VARCHAR2 (1000);
   x_message_list    error_handler.error_tbl_type;
BEGIN
           l_item_table (1).transaction_type := 'UPDATE';
       l_item_table (1).inventory_item_id :=242008;  --INVENTORY_ITEM_ID;
       l_item_table (1).organization_id :=127; -- I.ORGANIZATION_ID;
       l_item_table (1).template_id :=19;-- I.NEW_TEMPLATE_ID;
       ego_item_pub.process_items (p_api_version        => 1.0,
                       p_init_msg_list      => fnd_api.g_true,
                       p_commit             => fnd_api.g_true,
                       p_item_tbl           => l_item_table,
                       x_item_tbl           => x_item_table,
                       x_return_status      => x_return_status,
                       x_msg_count          => x_msg_count
                      );
       DBMS_OUTPUT.PUT_LINE ('Return Status ==>' || x_return_status);
       DBMS_OUTPUT.PUT_LINE ('Error Messages :');
           error_handler.get_message_list (x_message_list => x_message_list);
           FOR i IN 1 .. x_message_list.COUNT
           LOOP
              DBMS_OUTPUT.PUT_LINE (x_message_list (i).MESSAGE_TEXT);
           END LOOP;
END;