Oracle APPS Integrated SOA Gateway Publishing Web Service

March 16, 2015 · by altunkan · in E-Business Suite

Hello,

In this tutorial, I am going to explain how to publish web services with Integrated SOA Gateway.

Integrated SOA Gateway is an external module which can be installed on Oracle EBS. We can publish web services with pl/sql in a very simple way with Integrated SOA Gateway. I am going to create a custom service which gets teams and team players from a consumer client and insert them into custom tables which are logically associated with each other.

1. Since we are going to implement a custom web service, it will better to create a new custom entity. If the requirement is related to a specific EBS module, then it would be better to use a predefined business entity. We are going to create our web service in this custom entity. To define a custom entity, open lookups form and query BUSINESS_ENTITY. Insert a new line and give a proper entity name.

2. We need to create our tables which are going to hold team and player information. Run the following script and create team table and player table with their public synonyms and sequences. Our data model is 1-*. We need to create web service according to this data model.


create table xxntc.xxntc_anil_teams (
    team_id                     number primary key,
    bulk_request_name           varchar2(100),
    team_name                   varchar2(240),
    team_budget                 number,
    team_establishment_date     date,
    creation_date               date,
    created_by                  number,
    last_update_date            date,
    last_updated_by             number,
    last_update_login           number
);

create public synonym xxntc_anil_teams for xxntc.xxntc_anil_teams;

create sequence xxntc.xxntc_anil_teams_s start with 1;

create public synonym xxntc_anil_teams_s for xxntc.xxntc_anil_teams_s;

create table xxntc.xxntc_anil_team_players (
    player_id                   number primary key,
    team_id                     number,
    player_name                 varchar2(120),
    player_date_of_birth        date,
    player_salary               number,
    creation_date               date,
    created_by                  number,
    last_update_date            date,
    last_updated_by             number,
    last_update_login           number    
); 

create public synonym xxntc_anil_team_players for xxntc.xxntc_anil_team_players;

create sequence xxntc.xxntc_anil_team_players_s start with 1;

create public synonym xxntc_anil_team_players_s for xxntc.xxntc_anil_team_players_s;

create table xxntc.xxntc_anil_team_results (
    result_id                   number primary key,
    bulk_request_name           varchar2(100),
    team_id                     number,
    team_name                   varchar2(240),
    description                 varchar2(240),
    creation_date               date,
    created_by                  number,
    last_update_date            date,
    last_updated_by             number,
    last_update_login           number      
);

create public synonym xxntc_anil_team_results for xxntc.xxntc_anil_team_results;

create sequence xxntc.xxntc_anil_team_results_s start with 1;

create public synonym xxntc_anil_team_results_s for xxntc.xxntc_anil_team_results_s;

create table xxntc.xxntc_anil_teams (

team_id number primary key,

bulk_request_name varchar2(100),

team_name varchar2(240),

team_budget number,

team_establishment_date date,

creation_date date,

created_by number,

last_update_date date,

last_updated_by number,

last_update_login number

);

create public synonym xxntc_anil_teams for xxntc.xxntc_anil_teams;

create sequence xxntc.xxntc_anil_teams_s start with 1;

create public synonym xxntc_anil_teams_s for xxntc.xxntc_anil_teams_s;

create table xxntc.xxntc_anil_team_players (

player_id number primary key,

team_id number,

player_name varchar2(120),

player_date_of_birth date,

player_salary number,

creation_date date,

created_by number,

last_update_date date,

last_updated_by number,

last_update_login number

);

create public synonym xxntc_anil_team_players for xxntc.xxntc_anil_team_players;

create sequence xxntc.xxntc_anil_team_players_s start with 1;

create public synonym xxntc_anil_team_players_s for xxntc.xxntc_anil_team_players_s;

create table xxntc.xxntc_anil_team_results (

result_id number primary key,

bulk_request_name varchar2(100),

team_id number,

team_name varchar2(240),

description varchar2(240),

creation_date date,

created_by number,

last_update_date date,

last_updated_by number,

last_update_login number

);

create public synonym xxntc_anil_team_results for xxntc.xxntc_anil_team_results;

create sequence xxntc.xxntc_anil_team_results_s start with 1;

create public synonym xxntc_anil_team_results_s for xxntc.xxntc_anil_team_results_s;

XXNTC_ANIL_TEAMS: This table is going to keep team data.

XXNTC_ANIL_TEAM_PLAYERS: This table is going to keep players data. It is logically connected to team table via team_id.

XXNTC_ANIL_TEAM_RESULTS: This table is going to keep result of the services based on teams. We are going to insert every team record that we processed successfully or unsuccessfully.

3. In order to create a service which reflects our data model, we need to create Pl/Sql OBJECTS and TABLE TYPES. Run the following scripts one by one.

XX_PLAYER: Keeps a player data.


create or replace type xx_player as object (
    player_name                 varchar2(240),
    player_date_of_birth        date,
    player_salary               number
);

create or replace type xx_player as object (

player_name varchar2(240),

player_date_of_birth date,

player_salary number

);

XX_PLAYER_TABLE: Keeps many player data. This is a table type which is constructed from XX_PLAYER object type.


create or replace type xx_player_table is table of xx_player;

create or replace type xx_player_table is table of xx_player;

XX_TEAM: This object is going to keep team data. In order to make a team has many players, we need to add xx_player_table TABLE TYPE as an attribute in this object.


create or replace type xx_team as object (
    team_name                   varchar2(240),
    team_budget                 number,
    team_establishment_date     date,
    team_players                xx_player_table
);

create or replace type xx_team as object (

team_name varchar2(240),

team_budget number,

team_establishment_date date,

team_players xx_player_table

);

XX_TEAM_TABLE: Keeps many table data. This is a table type which is constructed from xx_team object.


create or replace type xx_team_table is table of xx_team;

create or replace type xx_team_table is table of xx_team;

XX_TEAM_RETURN: We are going to use this object as a response type for our service.


create or replace type xx_team_return as object (
    team_id                     number,
    team_name                   varchar2(240),
    description                 varchar2(240)
);

create or replace type xx_team_return as object (

team_id number,

team_name varchar2(240),

description varchar2(240)

);

XX_TEAM_RETURN_TABLE: Since in one requests, we may get more than one team, we also need to give a response for all of the teams that are sent with request.


create or replace type xx_team_return_table is table of xx_team_return;

create or replace type xx_team_return_table is table of xx_team_return;

4. After all of the OBJECTS and TABLE TYPES are created, we need to create our Pl/Sql package. SPEC file should be saved with pls extension. We are going to use some defined annotations in spec file. These annotations will provide information about service. Later, these information will be inserted into EBS SOA tables. Please do the following steps:

Write a header for your pls file.
Put a new line and define your service information. Do not put more than one line!
- scope: public
- product: XXNTC: This is a custom application. You can also use standard applications.
- displayname: Service Display name
- category: Provide a business entity. You can use pre-defined entities or custom entity which we created in first step.
Write annotations for every procedure or function which are going to be in your service. In this example, send_teams_data will be published in the service. Other procedures or functions are used for internal business logic.


create or replace package xx_team_service as
    /* $Header: XX_TEAM_SERVICE.pls 120.0 2014/12/03 17:10 aaltunkan $ */
    /*#
    * This custom PL/SQL package can be used to manipulate team data
    * @rep:scope public
    * @rep:product XXNTC
    * @rep:displayname Send Team Data
    * @rep:category BUSINESS_ENTITY XX_TEAM_ENTITY
    */
    
    g_user_id            number          := fnd_profile.value('USER_ID');
    g_login_id           number          := fnd_profile.value('LOGIN_ID');
    
    /*# 
    * Use this method to send team data
    * @param bulk_request_name request name
    * @param teams_data team data object records
    * @return xx_team_return_table
    * @rep:displayname Send Team Collection Data
    * @rep:category BUSINESS_ENTITY XX_TEAM_ENTITY
    * @rep:scope public
    * @rep:lifecycle active
    */    
    function send_teams_data (
        bulk_request_name   in xxntc.xxntc_anil_teams.bulk_request_name%type,
        teams_data          in xx_team_table
    ) return xx_team_return_table;

    procedure insert_result (
        p_bulk_request_name   in xxntc.xxntc_anil_teams.bulk_request_name%type,
        p_team_id             in number,        
        p_team_name           in xxntc.xxntc_anil_teams.team_name%type, 
        p_description         in xxntc.xxntc_anil_team_results.description%type
    );
    
    function generate_team_output(p_bulk_request_name   in xxntc.xxntc_anil_teams.bulk_request_name%type) return xx_team_return_table;

end;

create or replace package xx_team_service as

/* $Header: XX_TEAM_SERVICE.pls 120.0 2014/12/03 17:10 aaltunkan $ */

/*#

* This custom PL/SQL package can be used to manipulate team data

* @rep:scope public

* @rep:product XXNTC

* @rep:displayname Send Team Data

* @rep:category BUSINESS_ENTITY XX_TEAM_ENTITY

g_user_id number := fnd_profile.value('USER_ID');

g_login_id number := fnd_profile.value('LOGIN_ID');

/*#

* Use this method to send team data

* @param bulk_request_name request name

* @param teams_data team data object records

* @return xx_team_return_table

* @rep:displayname Send Team Collection Data

* @rep:category BUSINESS_ENTITY XX_TEAM_ENTITY

* @rep:scope public

* @rep:lifecycle active

function send_teams_data (

bulk_request_name in xxntc.xxntc_anil_teams.bulk_request_name%type,

teams_data in xx_team_table

) return xx_team_return_table;

procedure insert_result (

p_bulk_request_name in xxntc.xxntc_anil_teams.bulk_request_name%type,

p_team_id in number,

p_team_name in xxntc.xxntc_anil_teams.team_name%type,

p_description in xxntc.xxntc_anil_team_results.description%type

);

function generate_team_output(p_bulk_request_name in xxntc.xxntc_anil_teams.bulk_request_name%type) return xx_team_return_table;

end;

Create your body file. We will iterate TABLE TYPE objects and insert data into our custom tables. The “replace” function is used for replacing null character which may be send by some client as NULL. In Pl/Sql chr(0) equals “\0” in C. If you do not replace, then it won’t be null and your if checks will probably fail.


create or replace package body xx_team_service as
    /* $Header: XX_TEAM_SERVICE.pkb 120.0 2014/12/03 17:10 aaltunkan $ */

    function send_teams_data (
        bulk_request_name   in xxntc.xxntc_anil_teams.bulk_request_name%type,
        teams_data          in xx_team_table
    ) return xx_team_return_table is
    
        l_team_id                       number;
        l_player_id                     number;
        
        l_budget_exception              exception;
        
    begin
    
        for i in 1..teams_data.count
        loop
            savepoint before_insert;
            begin
                --insert teams
                l_team_id := xxntc_anil_teams_s.nextval;
                insert into xxntc_anil_teams (
                    team_id, 
                    bulk_request_name, 
                    team_name, 
                    team_budget, 
                    team_establishment_date, 
                    creation_date, 
                    created_by, 
                    last_update_date, 
                    last_updated_by, 
                    last_update_login
                )
                values (
                    l_team_id,
                    replace(bulk_request_name, chr(0), null),
                    replace(teams_data(i).team_name, chr(0), null),
                    replace(teams_data(i).team_budget, chr(0), null),
                    replace(teams_data(i).team_establishment_date, chr(0), null),
                    sysdate,
                    g_user_id,
                    sysdate,
                    g_user_id,
                    g_login_id 
                );
                
                if replace(teams_data(i).team_budget, chr(0), null) < 1000 then
                    raise l_budget_exception;
                end if;
                
                --insert players
                for j in 1..teams_data(i).team_players.count
                loop
                    l_player_id := xxntc_anil_team_players_s.nextval;
                    insert into xxntc_anil_team_players (
                        player_id,
                        team_id,
                        player_name,
                        player_date_of_birth,
                        player_salary,
                        creation_date,
                        created_by,
                        last_update_date,
                        last_updated_by,
                        last_update_login
                    )
                    values (
                        l_player_id,
                        l_team_id,
                        replace(teams_data(i).team_players(j).player_name, chr(0), null),
                        replace(teams_data(i).team_players(j).player_date_of_birth, chr(0), null),
                        replace(teams_data(i).team_players(j).player_salary, chr(0), null),
                        sysdate,
                        g_user_id,
                        sysdate,
                        g_user_id,
                        g_login_id
                    );
                
                end loop;
                
                insert_result(bulk_request_name, l_team_id, replace(teams_data(i).team_name, chr(0), null), 'Team is created in Oracle EBS!');
            
            exception when l_budget_exception then
                insert_result(bulk_request_name, l_team_id, replace(teams_data(i).team_name, chr(0), null), 'Budget must be greater than 1000!');
                rollback to before_insert;
            when others then
                insert_result(bulk_request_name, l_team_id, replace(teams_data(i).team_name, chr(0), null), 'Unexcepted Error:'||sqlcode || '-' || sqlerrm);
                rollback to before_insert;
            end;
        
        end loop;
        
        return generate_team_output(bulk_request_name);
    exception when others then
        return null;
    end; 

    procedure insert_result (
        p_bulk_request_name   in xxntc.xxntc_anil_teams.bulk_request_name%type,
        p_team_id             in number,
        p_team_name           in xxntc.xxntc_anil_teams.team_name%type, 
        p_description         in xxntc.xxntc_anil_team_results.description%type
    ) is
        pragma autonomous_transaction;
        l_result_id         number;
    begin
        l_result_id := xxntc_anil_team_results_s.nextval;
        insert into xxntc_anil_team_results (
            result_id, 
            bulk_request_name, 
            team_id, 
            team_name, 
            description, 
            creation_date, 
            created_by, 
            last_update_date, 
            last_updated_by, 
            last_update_login
        )   
        values (
            l_result_id,
            p_bulk_request_name,
            p_team_id,
            p_team_name,
            p_description,
            sysdate,
            g_user_id,
            sysdate,
            g_user_id,
            g_login_id
        );
        commit;
    exception when others then
        rollback;             
    end;
    
    function generate_team_output(p_bulk_request_name   in xxntc.xxntc_anil_teams.bulk_request_name%type) return xx_team_return_table is
        l_return_record                 xx_team_return;
        l_return_table                  xx_team_return_table    := xx_team_return_table();
    begin
        for s in ( 
            
            select rownum rn, x.*
              from (select *
                      from xxntc_anil_team_results
                     where bulk_request_name = p_bulk_request_name) x
        
        )
        loop
            l_return_record                     := xx_team_return(null, null, null);
            l_return_record.team_id             := s.team_id;
            l_return_record.team_name           := s.team_name;
            l_return_record.description         := s.description;
            
            l_return_table.extend;
            l_return_table(s.rn)                := l_return_record;
        end loop;
        
        return l_return_table;
    exception when others then
        return null;
    end;    

end;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

create or replace package body xx_team_service as

/* $Header: XX_TEAM_SERVICE.pkb 120.0 2014/12/03 17:10 aaltunkan $ */

function send_teams_data (

bulk_request_name in xxntc.xxntc_anil_teams.bulk_request_name%type,

teams_data in xx_team_table

) return xx_team_return_table is

l_team_id number;

l_player_id number;

l_budget_exception exception;

begin

for i in 1..teams_data.count

loop

savepoint before_insert;

begin

--insert teams

l_team_id := xxntc_anil_teams_s.nextval;

insert into xxntc_anil_teams (

team_id,

bulk_request_name,

team_name,

team_budget,

team_establishment_date,

creation_date,

created_by,

last_update_date,

last_updated_by,

last_update_login

)

values (

l_team_id,

replace(bulk_request_name, chr(0), null),

replace(teams_data(i).team_name, chr(0), null),

replace(teams_data(i).team_budget, chr(0), null),

replace(teams_data(i).team_establishment_date, chr(0), null),

sysdate,

g_user_id,

sysdate,

g_user_id,

g_login_id

);

if replace(teams_data(i).team_budget, chr(0), null) < 1000 then

raise l_budget_exception;

end if;

--insert players

for j in 1..teams_data(i).team_players.count

loop

l_player_id := xxntc_anil_team_players_s.nextval;

insert into xxntc_anil_team_players (

player_id,

team_id,

player_name,

player_date_of_birth,

player_salary,

creation_date,

created_by,

last_update_date,

last_updated_by,

last_update_login

)

values (

l_player_id,

l_team_id,

replace(teams_data(i).team_players(j).player_name, chr(0), null),

replace(teams_data(i).team_players(j).player_date_of_birth, chr(0), null),

replace(teams_data(i).team_players(j).player_salary, chr(0), null),

sysdate,

g_user_id,

sysdate,

g_user_id,

g_login_id

);

end loop;

insert_result(bulk_request_name, l_team_id, replace(teams_data(i).team_name, chr(0), null), 'Team is created in Oracle EBS!');

exception when l_budget_exception then

insert_result(bulk_request_name, l_team_id, replace(teams_data(i).team_name, chr(0), null), 'Budget must be greater than 1000!');

rollback to before_insert;

when others then

insert_result(bulk_request_name, l_team_id, replace(teams_data(i).team_name, chr(0), null), 'Unexcepted Error:'||sqlcode || '-' || sqlerrm);

rollback to before_insert;

end;

end loop;

return generate_team_output(bulk_request_name);

exception when others then

return null;

end;

procedure insert_result (

p_bulk_request_name in xxntc.xxntc_anil_teams.bulk_request_name%type,

p_team_id in number,

p_team_name in xxntc.xxntc_anil_teams.team_name%type,

p_description in xxntc.xxntc_anil_team_results.description%type

) is

pragma autonomous_transaction;

l_result_id number;

begin

l_result_id := xxntc_anil_team_results_s.nextval;

insert into xxntc_anil_team_results (

result_id,

bulk_request_name,

team_id,

team_name,

description,

creation_date,

created_by,

last_update_date,

last_updated_by,

last_update_login

)

values (

l_result_id,

p_bulk_request_name,

p_team_id,

p_team_name,

p_description,

sysdate,

g_user_id,

sysdate,

g_user_id,

g_login_id

);

commit;

exception when others then

rollback;

end;

function generate_team_output(p_bulk_request_name in xxntc.xxntc_anil_teams.bulk_request_name%type) return xx_team_return_table is

l_return_record xx_team_return;

l_return_table xx_team_return_table := xx_team_return_table();

begin

for s in (

select rownum rn, x.*

from (select *

from xxntc_anil_team_results

where bulk_request_name = p_bulk_request_name) x

)

loop

l_return_record := xx_team_return(null, null, null);

l_return_record.team_id := s.team_id;

l_return_record.team_name := s.team_name;

l_return_record.description := s.description;

l_return_table.extend;

l_return_table(s.rn) := l_return_record;

end loop;

return l_return_table;

exception when others then

return null;

end;

5. Compile packages on APPS.

6. Connect to application server with FTP and upload pls file into /tmp folder or some custom folder.

7. Connect to application server with SSH and run the following unix command. This command runs a perl script which is going to parse your pls file according to the annotations you defined and generates an ildt file.


$IAS_ORACLE_HOME/perl/bin/perl $FND_TOP/bin/irep_parser.pl -g -v -username=sysadmin xxntc:patch/115/sql:XX_TEAM_SERVICE:12.0=/tmp/XX_TEAM_SERVICE.pls

$IAS_ORACLE_HOME/perl/bin/perl $FND_TOP/bin/irep_parser.pl -g -v -username=sysadmin xxntc:patch/115/sql:XX_TEAM_SERVICE:12.0=/tmp/XX_TEAM_SERVICE.pls

8. After ILDT file is created successfully, give proper permissions.


chmod 777 XX_TEAM_SERVICE.ildt

chmod 777 XX_TEAM_SERVICE.ildt

9. Use FNDLOAD program in order to upload ILDT file into Oracle EBS. This will insert service information into EBS tables.


$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/wfirep.lct XX_TEAM_SERVICE.ildt

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/wfirep.lct XX_TEAM_SERVICE.ildt

10. Login with SYSADMIN into EBS and navigate to Integrated SOA Gateway -> Integration Repository.

11. Click to the “Search” button.

12. Write your service short name into Internal Name text input and click to “Go” button. Then click to the service link.

13. Click to the “Generate WSDL” button and wait for SOA to create WSDL for your service.

14. In order to give permissions to the users or groups, do the followings.

soa_gw_8

15. Check “Username Token” and Click to the “Deploy” button. This will add wsse:security to your service.

16. Click to the “View WSDL” link in order get WSDL Url.

Testing

1. Install SoapUI free version.

2. Create soap project in SoapUI with your service WSDL url.

3. Copy and paste the following into request.


<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xx="http://xmlns.oracle.com/apps/xxntc/soaprovider/plsql/xx_team_service/" xmlns:send="http://xmlns.oracle.com/apps/xxntc/soaprovider/plsql/xx_team_service/send_teams_data/" xmlns:soap="soap">
   <soapenv:Header>
      <xx:SOAHeader>
         <!--Optional:-->
         <xx:Responsibility>PAYABLES_MANAGER</xx:Responsibility>
         <!--Optional:-->
         <xx:RespApplication>SQLAP</xx:RespApplication>
         <!--Optional:-->
         <xx:SecurityGroup>STANDARD</xx:SecurityGroup>
         <!--Optional:-->
         <xx:NLSLanguage>TURKISH</xx:NLSLanguage>
         <!--Optional:-->
         <xx:Org_Id></xx:Org_Id>
      </xx:SOAHeader>
      <wsse:Security soap:mustUnderstand="1" xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">
         <wsse:UsernameToken>
            <wsse:Username>NTC_ANIL</wsse:Username>
            <wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">111111</wsse:Password>
         </wsse:UsernameToken>
      </wsse:Security>
   </soapenv:Header>
   <soapenv:Body>
      <send:InputParameters>
         <!--Optional:-->
         <send:BULK_REQUEST_NAME>BULK-TEST-1</send:BULK_REQUEST_NAME>
         <!--Optional:-->
         <send:TEAMS_DATA>
            <!--Zero or more repetitions:-->
            <send:TEAMS_DATA_ITEM>
               <!--Optional:-->
               <send:TEAM_NAME>TEST</send:TEAM_NAME>
               <!--Optional:-->
               <send:TEAM_BUDGET>50000</send:TEAM_BUDGET>
               <!--Optional:-->
               <send:TEAM_ESTABLISHMENT_DATE>2015-03-10T02:00:00</send:TEAM_ESTABLISHMENT_DATE>
               <!--Optional:-->
               <send:TEAM_PLAYERS>
                  <!--Zero or more repetitions:-->
                  <send:TEAM_PLAYERS_ITEM>
                     <!--Optional:-->
                     <send:PLAYER_NAME>ANIL ALTUNKAN</send:PLAYER_NAME>
                     <!--Optional:-->
                     <send:PLAYER_DATE_OF_BIRTH>1987-12-30</send:PLAYER_DATE_OF_BIRTH>
                     <!--Optional:-->
                     <send:PLAYER_SALARY>2000</send:PLAYER_SALARY>
                  </send:TEAM_PLAYERS_ITEM>
                  <send:TEAM_PLAYERS_ITEM>
                     <!--Optional:-->
                     <send:PLAYER_NAME>ANIL ALTUNKAN</send:PLAYER_NAME>
                     <!--Optional:-->
                     <send:PLAYER_DATE_OF_BIRTH>1987-12-30</send:PLAYER_DATE_OF_BIRTH>
                     <!--Optional:-->
                     <send:PLAYER_SALARY>2000</send:PLAYER_SALARY>
                  </send:TEAM_PLAYERS_ITEM>                  
               </send:TEAM_PLAYERS>
            </send:TEAMS_DATA_ITEM>
            <send:TEAMS_DATA_ITEM>
               <!--Optional:-->
               <send:TEAM_NAME>TEST2</send:TEAM_NAME>
               <!--Optional:-->
               <send:TEAM_BUDGET>50000</send:TEAM_BUDGET>
               <!--Optional:-->
               <send:TEAM_ESTABLISHMENT_DATE>2015-03-10T02:00:00</send:TEAM_ESTABLISHMENT_DATE>
               <!--Optional:-->
               <send:TEAM_PLAYERS>
                  <!--Zero or more repetitions:-->
                  <send:TEAM_PLAYERS_ITEM>
                     <!--Optional:-->
                     <send:PLAYER_NAME>ANIL ALTUNKAN</send:PLAYER_NAME>
                     <!--Optional:-->
                     <send:PLAYER_DATE_OF_BIRTH>1987-12-30</send:PLAYER_DATE_OF_BIRTH>
                     <!--Optional:-->
                     <send:PLAYER_SALARY>2000</send:PLAYER_SALARY>
                  </send:TEAM_PLAYERS_ITEM>
                  <send:TEAM_PLAYERS_ITEM>
                     <!--Optional:-->
                     <send:PLAYER_NAME>ANIL ALTUNKAN</send:PLAYER_NAME>
                     <!--Optional:-->
                     <send:PLAYER_DATE_OF_BIRTH>1987-12-30</send:PLAYER_DATE_OF_BIRTH>
                     <!--Optional:-->
                     <send:PLAYER_SALARY>2000</send:PLAYER_SALARY>
                  </send:TEAM_PLAYERS_ITEM>                  
               </send:TEAM_PLAYERS>
            </send:TEAMS_DATA_ITEM>            
            <send:TEAMS_DATA_ITEM>
               <!--Optional:-->
               <send:TEAM_NAME>TEST3</send:TEAM_NAME>
               <!--Optional:-->
               <send:TEAM_BUDGET>400</send:TEAM_BUDGET>
               <!--Optional:-->
               <send:TEAM_ESTABLISHMENT_DATE>2015-03-10T02:00:00</send:TEAM_ESTABLISHMENT_DATE>
               <!--Optional:-->
               <send:TEAM_PLAYERS>
                  <!--Zero or more repetitions:-->
                  <send:TEAM_PLAYERS_ITEM>
                     <!--Optional:-->
                     <send:PLAYER_NAME>ANIL ALTUNKAN</send:PLAYER_NAME>
                     <!--Optional:-->
                     <send:PLAYER_DATE_OF_BIRTH>1987-12-30</send:PLAYER_DATE_OF_BIRTH>
                     <!--Optional:-->
                     <send:PLAYER_SALARY>2000</send:PLAYER_SALARY>
                  </send:TEAM_PLAYERS_ITEM>
                  <send:TEAM_PLAYERS_ITEM>
                     <!--Optional:-->
                     <send:PLAYER_NAME>ANIL ALTUNKAN</send:PLAYER_NAME>
                     <!--Optional:-->
                     <send:PLAYER_DATE_OF_BIRTH>1987-12-30</send:PLAYER_DATE_OF_BIRTH>
                     <!--Optional:-->
                     <send:PLAYER_SALARY>2000</send:PLAYER_SALARY>
                  </send:TEAM_PLAYERS_ITEM>                  
               </send:TEAM_PLAYERS>
            </send:TEAMS_DATA_ITEM>            
         </send:TEAMS_DATA>
      </send:InputParameters>
   </soapenv:Body>
</soapenv:Envelope>

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xx="http://xmlns.oracle.com/apps/xxntc/soaprovider/plsql/xx_team_service/" xmlns:send="http://xmlns.oracle.com/apps/xxntc/soaprovider/plsql/xx_team_service/send_teams_data/" xmlns:soap="soap">

<soapenv:Header>

<xx:SOAHeader>

<xx:Responsibility>PAYABLES_MANAGER</xx:Responsibility>

<xx:RespApplication>SQLAP</xx:RespApplication>

<xx:SecurityGroup>STANDARD</xx:SecurityGroup>

<xx:NLSLanguage>TURKISH</xx:NLSLanguage>

<xx:Org_Id></xx:Org_Id>

</xx:SOAHeader>

<wsse:Security soap:mustUnderstand="1" xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">

<wsse:UsernameToken>

<wsse:Username>NTC_ANIL</wsse:Username>

<wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">111111</wsse:Password>

</wsse:UsernameToken>

</wsse:Security>

</soapenv:Header>

<soapenv:Body>

<send:InputParameters>

<send:BULK_REQUEST_NAME>BULK-TEST-1</send:BULK_REQUEST_NAME>

<send:TEAMS_DATA>

<send:TEAMS_DATA_ITEM>

<send:TEAM_NAME>TEST</send:TEAM_NAME>

<send:TEAM_BUDGET>50000</send:TEAM_BUDGET>

<send:TEAM_ESTABLISHMENT_DATE>2015-03-10T02:00:00</send:TEAM_ESTABLISHMENT_DATE>

<send:TEAM_PLAYERS>

<send:TEAM_PLAYERS_ITEM>

<send:PLAYER_NAME>ANIL ALTUNKAN</send:PLAYER_NAME>

<send:PLAYER_DATE_OF_BIRTH>1987-12-30</send:PLAYER_DATE_OF_BIRTH>

<send:PLAYER_SALARY>2000</send:PLAYER_SALARY>

</send:TEAM_PLAYERS_ITEM>

<send:TEAM_PLAYERS_ITEM>

<send:PLAYER_NAME>ANIL ALTUNKAN</send:PLAYER_NAME>

<send:PLAYER_DATE_OF_BIRTH>1987-12-30</send:PLAYER_DATE_OF_BIRTH>

<send:PLAYER_SALARY>2000</send:PLAYER_SALARY>

</send:TEAM_PLAYERS_ITEM>

</send:TEAM_PLAYERS>

</send:TEAMS_DATA_ITEM>

<send:TEAMS_DATA_ITEM>

<send:TEAM_NAME>TEST2</send:TEAM_NAME>

<send:TEAM_BUDGET>50000</send:TEAM_BUDGET>

<send:TEAM_ESTABLISHMENT_DATE>2015-03-10T02:00:00</send:TEAM_ESTABLISHMENT_DATE>

<send:TEAM_PLAYERS>

<send:TEAM_PLAYERS_ITEM>

<send:PLAYER_NAME>ANIL ALTUNKAN</send:PLAYER_NAME>

<send:PLAYER_DATE_OF_BIRTH>1987-12-30</send:PLAYER_DATE_OF_BIRTH>

<send:PLAYER_SALARY>2000</send:PLAYER_SALARY>

</send:TEAM_PLAYERS_ITEM>

<send:TEAM_PLAYERS_ITEM>

<send:PLAYER_NAME>ANIL ALTUNKAN</send:PLAYER_NAME>

<send:PLAYER_DATE_OF_BIRTH>1987-12-30</send:PLAYER_DATE_OF_BIRTH>

<send:PLAYER_SALARY>2000</send:PLAYER_SALARY>

</send:TEAM_PLAYERS_ITEM>

</send:TEAM_PLAYERS>

</send:TEAMS_DATA_ITEM>

<send:TEAMS_DATA_ITEM>

<send:TEAM_NAME>TEST3</send:TEAM_NAME>

<send:TEAM_BUDGET>400</send:TEAM_BUDGET>

<send:TEAM_ESTABLISHMENT_DATE>2015-03-10T02:00:00</send:TEAM_ESTABLISHMENT_DATE>

<send:TEAM_PLAYERS>

<send:TEAM_PLAYERS_ITEM>

<send:PLAYER_NAME>ANIL ALTUNKAN</send:PLAYER_NAME>

<send:PLAYER_DATE_OF_BIRTH>1987-12-30</send:PLAYER_DATE_OF_BIRTH>

<send:PLAYER_SALARY>2000</send:PLAYER_SALARY>

</send:TEAM_PLAYERS_ITEM>

<send:TEAM_PLAYERS_ITEM>

<send:PLAYER_NAME>ANIL ALTUNKAN</send:PLAYER_NAME>

<send:PLAYER_DATE_OF_BIRTH>1987-12-30</send:PLAYER_DATE_OF_BIRTH>

<send:PLAYER_SALARY>2000</send:PLAYER_SALARY>

</send:TEAM_PLAYERS_ITEM>

</send:TEAM_PLAYERS>

</send:TEAMS_DATA_ITEM>

</send:TEAMS_DATA>

</send:InputParameters>

</soapenv:Body>

</soapenv:Envelope>

4. Run the request.

5. Get the response.


<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">
   <env:Header/>
   <env:Body>
      <OutputParameters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.oracle.com/apps/xxntc/soaprovider/plsql/xx_team_service/send_teams_data/">
         <SEND_TEAMS_DATA>
            <SEND_TEAMS_DATA_ITEM>
               <TEAM_ID>2</TEAM_ID>
               <TEAM_NAME>TEST</TEAM_NAME>
               <DESCRIPTION>Team is created in Oracle EBS!</DESCRIPTION>
            </SEND_TEAMS_DATA_ITEM>
            <SEND_TEAMS_DATA_ITEM>
               <TEAM_ID>3</TEAM_ID>
               <TEAM_NAME>TEST2</TEAM_NAME>
               <DESCRIPTION>Team is created in Oracle EBS!</DESCRIPTION>
            </SEND_TEAMS_DATA_ITEM>
            <SEND_TEAMS_DATA_ITEM>
               <TEAM_ID>4</TEAM_ID>
               <TEAM_NAME>TEST3</TEAM_NAME>
               <DESCRIPTION>Budget must be greater than 1000!</DESCRIPTION>
            </SEND_TEAMS_DATA_ITEM>
         </SEND_TEAMS_DATA>
      </OutputParameters>
   </env:Body>
</env:Envelope>

<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">

<env:Header/>

<env:Body>

<SEND_TEAMS_DATA>

<SEND_TEAMS_DATA_ITEM>

<TEAM_ID>2</TEAM_ID>

<TEAM_NAME>TEST</TEAM_NAME>

<DESCRIPTION>Team is created in Oracle EBS!</DESCRIPTION>

</SEND_TEAMS_DATA_ITEM>

<SEND_TEAMS_DATA_ITEM>

<TEAM_ID>3</TEAM_ID>

<TEAM_NAME>TEST2</TEAM_NAME>

<DESCRIPTION>Team is created in Oracle EBS!</DESCRIPTION>

</SEND_TEAMS_DATA_ITEM>

<SEND_TEAMS_DATA_ITEM>

<TEAM_ID>4</TEAM_ID>

<TEAM_NAME>TEST3</TEAM_NAME>

<DESCRIPTION>Budget must be greater than 1000!</DESCRIPTION>

</SEND_TEAMS_DATA_ITEM>

</SEND_TEAMS_DATA>

</OutputParameters>

</env:Body>

</env:Envelope>

6. Check your tables.


select *
  from xxntc_anil_teams
 where bulk_request_name = 'BULK-TEST-1';

select *

from xxntc_anil_teams

where bulk_request_name = 'BULK-TEST-1';

Result:


   TEAM_ID BULK_REQUEST_NAME    TEAM_NAME            TEAM_BUDGET TEAM_ESTABLISHMENT_DATE CREATION_DATE CREATED_BY LAST_UPDATE_DATE LAST_UPDATED_BY LAST_UPDATE_LOGIN
---------- -------------------- -------------------- ----------- ----------------------- ------------- ---------- ---------------- --------------- -----------------
         2 BULK-TEST-1          TEST                       50000 10/03/2015              10/03/2015       1013537 10/03/2015               1013537                -1
         3 BULK-TEST-1          TEST2                      50000 10/03/2015              10/03/2015       1013537 10/03/2015               1013537                -1

2 rows selected.

TEAM_ID BULK_REQUEST_NAME TEAM_NAME TEAM_BUDGET TEAM_ESTABLISHMENT_DATE CREATION_DATE CREATED_BY LAST_UPDATE_DATE LAST_UPDATED_BY LAST_UPDATE_LOGIN

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

2 BULK-TEST-1 TEST 50000 10/03/2015 10/03/2015 1013537 10/03/2015 1013537 -1

3 BULK-TEST-1 TEST2 50000 10/03/2015 10/03/2015 1013537 10/03/2015 1013537 -1

2 rows selected.


select xp.*
  from xxntc_anil_team_players xp, xxntc_anil_teams xt
 where xp.team_id = xt.team_id and xt.bulk_request_name = 'BULK-TEST-1';

select xp.*

from xxntc_anil_team_players xp, xxntc_anil_teams xt

where xp.team_id = xt.team_id and xt.bulk_request_name = 'BULK-TEST-1';

Result:


 PLAYER_ID    TEAM_ID PLAYER_NAME          PLAYER_DATE_OF_BIRTH PLAYER_SALARY CREATION_DATE CREATED_BY LAST_UPDATE_DATE LAST_UPDATED_BY LAST_UPDATE_LOGIN
---------- ---------- -------------------- -------------------- ------------- ------------- ---------- ---------------- --------------- -----------------
         2          2 ANIL ALTUNKAN        30/12/1987                    2000 10/03/2015       1013537 10/03/2015               1013537                -1
         3          2 ANIL ALTUNKAN        30/12/1987                    2000 10/03/2015       1013537 10/03/2015               1013537                -1
         4          3 ANIL ALTUNKAN        30/12/1987                    2000 10/03/2015       1013537 10/03/2015               1013537                -1
         5          3 ANIL ALTUNKAN        30/12/1987                    2000 10/03/2015       1013537 10/03/2015               1013537                -1

4 rows selected.

PLAYER_ID TEAM_ID PLAYER_NAME PLAYER_DATE_OF_BIRTH PLAYER_SALARY CREATION_DATE CREATED_BY LAST_UPDATE_DATE LAST_UPDATED_BY LAST_UPDATE_LOGIN

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

2 2 ANIL ALTUNKAN 30/12/1987 2000 10/03/2015 1013537 10/03/2015 1013537 -1

3 2 ANIL ALTUNKAN 30/12/1987 2000 10/03/2015 1013537 10/03/2015 1013537 -1

4 3 ANIL ALTUNKAN 30/12/1987 2000 10/03/2015 1013537 10/03/2015 1013537 -1

5 3 ANIL ALTUNKAN 30/12/1987 2000 10/03/2015 1013537 10/03/2015 1013537 -1

4 rows selected.


select *
  from xxntc_anil_teams
 where bulk_request_name = 'BULK-TEST-1';

select *

from xxntc_anil_teams

where bulk_request_name = 'BULK-TEST-1';

Result:


   TEAM_ID BULK_REQUEST_NAME    TEAM_NAME            TEAM_BUDGET TEAM_ESTABLISHMENT_DATE CREATION_DATE CREATED_BY LAST_UPDATE_DATE LAST_UPDATED_BY LAST_UPDATE_LOGIN
---------- -------------------- -------------------- ----------- ----------------------- ------------- ---------- ---------------- --------------- -----------------
         2 BULK-TEST-1          TEST                       50000 10/03/2015              10/03/2015       1013537 10/03/2015               1013537                -1
         3 BULK-TEST-1          TEST2                      50000 10/03/2015              10/03/2015       1013537 10/03/2015               1013537                -1

2 rows selected.

TEAM_ID BULK_REQUEST_NAME TEAM_NAME TEAM_BUDGET TEAM_ESTABLISHMENT_DATE CREATION_DATE CREATED_BY LAST_UPDATE_DATE LAST_UPDATED_BY LAST_UPDATE_LOGIN

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

2 BULK-TEST-1 TEST 50000 10/03/2015 10/03/2015 1013537 10/03/2015 1013537 -1

3 BULK-TEST-1 TEST2 50000 10/03/2015 10/03/2015 1013537 10/03/2015 1013537 -1

2 rows selected.

Delete Service:


declare

   

  cursor c_irep is
  
  select class_id, class_name
    from fnd_irep_classes
   where irep_name = 'XX_TEAM_SERVICE'  -- package name 
   order by class_id desc;

begin

  for r_irep in c_irep loop

    delete from fnd_irep_class_datasources where class_id = r_irep.class_id;

    delete from fnd_lookup_assignments where obj_name = 'FND_IREP_CLASSES' and instance_pk1_value = to_char(r_irep.class_id);

    delete from fnd_child_annotations where parent_id = r_irep.class_id and parent_flag = 'C';

    delete from fnd_irep_uses_tables where class_id = r_irep.class_id;

    delete from fnd_irep_uses_maps where class_id = r_irep.class_id;

    delete from fnd_lookup_assignments where obj_name = 'FND_IREP_FUNCTION_FLAVORS' and instance_pk1_value in (select function_id from fnd_form_functions where irep_class_id = r_irep.class_id);

    delete from fnd_child_annotations where parent_flag = 'F' and parent_id in (select function_id from fnd_form_functions where irep_class_id = r_irep.class_id);

    delete from fnd_parameters where function_id in (select function_id from fnd_form_functions where irep_class_id = r_irep.class_id);

    delete from fnd_irep_function_flavors where function_id in (select function_id from fnd_form_functions where irep_class_id = r_irep.class_id);

    delete from fnd_irep_classes where assoc_class_id = r_irep.class_id;

    delete from fnd_irep_classes where class_id = r_irep.class_id;

    delete from fnd_form_functions where irep_class_id = (select class_id from fnd_irep_classes where assoc_class_id = r_irep.class_id);

    delete from fnd_object_key_sets where object_id = r_irep.class_id;

    delete from fnd_lookup_assignments where obj_name = 'FND_OBJECTS' and instance_pk1_value = to_char(r_irep.class_id);

    delete from fnd_child_annotations where parent_id = r_irep.class_id and parent_flag = 'O';

    delete from fnd_object_type_members where object_id = r_irep.class_id;

    delete from fnd_irep_class_parent_assigns where class_name = r_irep.class_name;
  
  end loop;

  commit;

end;
/

declare

cursor c_irep is

select class_id, class_name

from fnd_irep_classes

where irep_name = 'XX_TEAM_SERVICE' -- package name

order by class_id desc;

begin

for r_irep in c_irep loop

delete from fnd_irep_class_datasources where class_id = r_irep.class_id;

delete from fnd_lookup_assignments where obj_name = 'FND_IREP_CLASSES' and instance_pk1_value = to_char(r_irep.class_id);

delete from fnd_child_annotations where parent_id = r_irep.class_id and parent_flag = 'C';

delete from fnd_irep_uses_tables where class_id = r_irep.class_id;

delete from fnd_irep_uses_maps where class_id = r_irep.class_id;

delete from fnd_lookup_assignments where obj_name = 'FND_IREP_FUNCTION_FLAVORS' and instance_pk1_value in (select function_id from fnd_form_functions where irep_class_id = r_irep.class_id);

delete from fnd_child_annotations where parent_flag = 'F' and parent_id in (select function_id from fnd_form_functions where irep_class_id = r_irep.class_id);

delete from fnd_parameters where function_id in (select function_id from fnd_form_functions where irep_class_id = r_irep.class_id);

delete from fnd_irep_function_flavors where function_id in (select function_id from fnd_form_functions where irep_class_id = r_irep.class_id);

delete from fnd_irep_classes where assoc_class_id = r_irep.class_id;

delete from fnd_irep_classes where class_id = r_irep.class_id;

delete from fnd_form_functions where irep_class_id = (select class_id from fnd_irep_classes where assoc_class_id = r_irep.class_id);

delete from fnd_object_key_sets where object_id = r_irep.class_id;

delete from fnd_lookup_assignments where obj_name = 'FND_OBJECTS' and instance_pk1_value = to_char(r_irep.class_id);

delete from fnd_child_annotations where parent_id = r_irep.class_id and parent_flag = 'O';

delete from fnd_object_type_members where object_id = r_irep.class_id;

delete from fnd_irep_class_parent_assigns where class_name = r_irep.class_name;

end loop;

commit;

end;

Tags: integrated soa gateway, oracle, oracle apps, plsql web service, soa gateway, web service

9 Responses

Farook · October 20, 2016 at 20:40:18 · →

Hi Anil,
First i have to say its a very good Article, and i am trying to explore the same, i am running in to a fault string “User not authorized to execute service” when invoking.

Could you help me on it

Thanks
Farook
1. altunkan · October 24, 2016 at 08:45:40 · →
  
  Hello,
  
  Thank you for your nice comment.
  
  If you did everything right, you need to bounce oacore, oafm and apache. This issue may occur sometimes. Bouncing is the soluition.
  
  Thanks,
  Anil
Farook · October 24, 2016 at 15:20:47 · →

Yep, i found that and it worked

Thank you
Farook · October 24, 2016 at 15:26:36 · →

Also i have a question,
I am using rest Web service, if i do a basic REST “application/xml” i get a response. but when using ‘application/json” i get a below error. i think the issue is when getting the Auth token when hitting below URL any idea ? is there something i am missing, when i looked for the “AuthnService” is see it FND_IREP_CLASSES

http://:/webservices/rest/login

“ISGServiceFault” : {
“Code” : “INVALID_ALIAS”,
“Message” : “Invalid Alias is used for service invocation.”,
“Resolution” : “Please pass a valid alias for the api.”,
“ServiceDetails” : {
“ServiceName” : “login”,
“OperationName” : “login”,
“InstanceId” : “0”
1. altunkan · October 25, 2016 at 06:08:56 · →
  
  Sorry Farook, I have never implemented rest service in EBS.
  
  I guess you are using a new EBS version which is above 12.1.3 since rest service is only exist in newer versions.
Manish · August 21, 2018 at 08:19:11 · →

Hi Can you please provide you contact detail
1. altunkan · October 15, 2018 at 11:48:41 · →
  
  Hey, You can send mail to altunkan@gmail.com
Fredrick Inyasia · January 17, 2019 at 14:44:48 · →

Thank you

Anil this is a great document. i have been cracking my head on this for sometime.

Thank you
Sohail Anwar · October 4, 2019 at 13:25:30 · →

This is a great post. It really helped me to develop custom web services.

Oracle APPS Integrated SOA Gateway Publishing Web Service

Testing

Delete Service:

9 Responses

Leave a Reply Click here to cancel reply.

Visitors

Subscribe to Blog via Email