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 :
- Parsing parameter should be “RECORD TYPE SEQUENCE” which you written the query RECORD TYPE in Packages
- 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;
DBA 