REST API Record Type Example

Goal :

Multi data insert into table of EBS 12.1 or 12.2 Enviornment

Follow the procedure :

Steps 1:

Create a package using with record type

 

CREATE OR REPLACE PACKAGE APPS.xxace_ap_restapi
AS
/* $Header: xxace_ap_restapi$ */
/*#
* This custom PL/SQL package can be used to load AP Invoices from restapi test.
* @rep:scope public
* @rep:product AP
* @rep:displayname AP Invoice Interface for restapi test
* @rep:category BUSINESS_ENTITY XXAPTEST_INT
*/
type xx_invoice_rec_test is record
(p_invoice_type VARCHAR2(30)
,p_invoice_number VARCHAR2(30)

);
type xx_invoice_tbl_test is table of xx_invoice_rec_test INDEX BY BINARY_INTEGER;
PROCEDURE xxace_ap
(
p_invoice_tbl1 in xx_invoice_tbl_test
,p_out_success_1 out VARCHAR2
,p_out_failure_2 out VARCHAR2
)
/*#
* Procedure to Load AP Invoice
* @param p_invoice_tbl1 in parameter 1
* @param p_out_success_1 out parameter 1
* @param p_out_failure_2 out parameter 2
* @rep:displayname AP Invoice Interface for restapi test
* @rep:category BUSINESS_ENTITY XXAPTEST_INT
* @rep:scope public
* @rep:lifecycle active
*/
;
end xxace_ap_restapi;
/

 

Package Body

CREATE OR REPLACE PACKAGE BODY APPS.xxace_ap_restapi
AS
PROCEDURE xxace_ap (p_invoice_tbl1 IN xx_invoice_tbl_test,
p_out_success_1 OUT VARCHAR2,
p_out_failure_2 OUT VARCHAR2)
IS
l_count NUMBER := 0;
–l_inv_seq NUMBER:= AP_INVOICES_INTERFACE_S.NEXTVAL;
excp EXCEPTION;

v_count NUMBER := 0;

V_PRE NUMBER;
V_AMT NUMBER;
v_cur VARCHAR2 (100);
BEGIN
FOR i IN p_invoice_tbl1.FIRST .. p_invoice_tbl1.LAST
LOOP
p_out_success_1 := NULL;
p_out_failure_2 := NULL;
v_count := v_count + 1;

p_out_success_1 :=
p_invoice_tbl1(i).p_invoice_number|| ‘no data’
|| ‘ ‘
|| v_count;

IF p_invoice_tbl1(i).p_invoice_number IS NULL
THEN
p_out_failure_2 := ‘Invoice Number is Missing.’;
— RAISE EXCP;
ELSIF p_invoice_tbl1(i).p_invoice_type IS NULL
THEN
p_out_failure_2 := ‘Invocie Type is Missing.’;
— RAISE EXCP;
END IF;

BEGIN
INSERT
INTO xxace.XXACE_CMS_AP_INV_STG (staging_id,
invoice_type,
invoice_number)
VALUES (
xxace_ap_stg_seq.NEXTVAL,
CASE
WHEN UPPER (p_invoice_tbl1(i).p_invoice_type) LIKE
‘CREDIT NOTE%’
THEN
‘Standard’
WHEN UPPER (p_invoice_tbl1(i).p_invoice_type) LIKE
‘DEBIT NOTE%’
THEN
‘Debit Memo’
ELSE
p_invoice_tbl1(i).p_invoice_type
END,
p_invoice_tbl1(i).p_invoice_number);
EXCEPTION
WHEN EXCP
THEN
ROLLBACK;
END;
END LOOP;

COMMIT;

END xxace_ap;
END xxace_ap_restapi;
/

 

Pls File : xxace_ap_restapi.pls

CREATE OR REPLACE PACKAGE xxace_ap_restapi
AS
/* $Header: xxace_ap_restapi $ */
/*#
* This custom PL/SQL package can be used to load AP Invoices from restapitest.
* @rep:scope public
* @rep:product AP
* @rep:displayname AP Invoice Interface for restapi test
* @rep:category BUSINESS_ENTITY XXAPTEST_INT
*/
type xx_invoice_rec_test is record
(p_invoice_type VARCHAR2(30)
,p_invoice_number VARCHAR2(30)

);
type xx_invoice_tbl_test is table of xx_invoice_rec_test INDEX BY BINARY_INTEGER;
PROCEDURE xxace_ap
(
p_invoice_tbl1 in xx_invoice_tbl_test
,p_out_success_1 out VARCHAR2
,p_out_failure_2 out VARCHAR2
)
/*#
* Procedure to Load AP Invoice
* @param p_invoice_tbl1 in parameter 1
* @param p_out_success_1 out parameter 1
* @param p_out_failure_2 out parameter 2
* @rep:displayname AP Invoice Interface for restapi test
* @rep:category BUSINESS_ENTITY XXAPTEST_INT
* @rep:scope public
* @rep:lifecycle active
*/
;
end xxace_ap_restapi;

 

 

Step 2:

create Business Entity using Application Developer

Business Entity : XXAPTEST_INT

Step 3: PLS File Upload

copy xxace_ap_restapi.pls to /tmp & $AP_TOP/patch/115/sql

cd $AP_TOP/patch/115/sql

$IAS_ORACLE_HOME/perl/bin/perl $FND_TOP/bin/irep_parser.pl -g -v -username=intuser ap:patch/115/sql:xxace_ap_restapi.pls:12.0=/tmp/xxace_ap_restapi.pls -outdir=$AP_TOP/patch/115/irep/patch/115/sql/

cd $AP_TOP/patch/115/irep/patch/115/sql/

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

Step 4:

Deploy REST Service using Integrated SOA Gateway

Internal name : xxace_ap_restapi

and give grant

 

Step 5: Testing the REST API (REST API Chrome Client )

 

 

Body :

 

{
“xxace_ap”: {
“@xmlns”: “http://XXXXXXX.com:8065/webservices/rest/xxace_ap_restapi/xxace_ap/”,
“RESTHeader”: {
“@xmlns”: “http://xxxxxxxxxcom:8065/webservices/rest/xxace_ap_restapi/xxace_ap/”,
“Responsibility”: “20-***_**_USER”,
“RespApplication”: “SQLAP”,
“SecurityGroup”: “STANDARD”,
“NLSLanguage”: “AMERICAN”
},
“inputParameters”: {
“P_INVOICE_TBL1”:
{
“attributes1” : {“P_INVOICE_TYPE”: “CREDIT NOTE”,
“P_INVOICE_NUMBER”: “12345test” },
“attributes2” : {“P_INVOICE_TYPE”: “DEBIT NOTE”,
“P_INVOICE_NUMBER”: “12345testnew” },
“attributes3” : {“P_INVOICE_TYPE”: “DEBIT NOTE”,
“P_INVOICE_NUMBER”: “12345testnewAPI” }
}
}
}
}

 

POST Data Send

Output :

 

Check the tables inserted or not

 

 

NOTE :

1. Parsing parameter should be “RECORD TYPE SEQUENCE” which  you written the query RECORD TYPE  in Packages
2. Parsing the VALUE in REST API Client , you should send like ” attribute1,attribute2,attribute3… this is common name”

Manual Checking :

 

DECLARE

l_out_success_1   varchar2 (30);

l_out_failure_2   varchar2 (30);

l_invoice_data    xxace_ap_restapi.xx_invoice_tbl_test;

BEGIN

l_invoice_data (1).p_invoice_type := ‘Type1’;

l_invoice_data (1).p_invoice_number := ‘Inv001’;

xxace_ap_restapi.xxace_ap (p_invoice_tbl1    => l_invoice_data,

p_out_success_1   => l_out_success_1,

p_out_failure_2   => l_out_failure_2);

 

DBMS_OUTPUT.put_line (‘ l_out_success_1 ‘ || l_out_success_1);

DBMS_OUTPUT.put_line (‘ l_out_failure_2 ‘ || l_out_failure_2);

END;