Oracle Custom WEB ADI for AP Invoice Interface

Oracle Custom WEB ADI for AP Invoice Interface Requirement: Web ADI interface needed to be developed to import Invoices, Layout to be designed

Solution: Following steps to be followed  Create integrator  Create Layout  Create Mappings  Create Document  Defining the Form Function for Integrator

Ra u Ch

Oracle Custom WEB ADI for AP Invoice Interface Compile the below Custom table and Package in APPS schema --> ==================================================================== DROP TABLE TABLE XXAA_APINV_IFACE_TBL XXAA_APINV_IFACE_TBL; ; CREATE TABLE XXAA_APINV_IFACE_TBL TABLE XXAA_APINV_IFACE_TBL ( SOURCE VARCHAR2( VARCHAR2 (50 50), ), ORGANIZATION VARCHAR2( VARCHAR2 (40 40), ), INVOICE_TYPE VARCHAR2( VARCHAR2 (40 40), ), SUPPLIER_NAME VARCHAR2( VARCHAR2 (60 60), ), SUPPLIER_NUM VARCHAR2( VARCHAR2 (60 60), ), SUPPLIER_SITE VARCHAR2( VARCHAR2 (40 40), ), INVOICE_DATE DATE, DATE , INVOICE_NUMBER VARCHAR2( VARCHAR2 (60 60), ), INVOICE_AMOUNT NUMBER( NUMBER (10 10, ,2), TERMS VARCHAR2( VARCHAR2 (60 60), ), INVOICE_CURRENCY VARCHAR2( VARCHAR2 (25 25), ), GL_DATE DATE, DATE , PAYMENT_CURRENCY VARCHAR2( VARCHAR2 (25 25), ), PAYMENT_METHOD VARCHAR2( VARCHAR2 (40 40), ), LINE_NUM NUMBER, NUMBER , LINE_TYPE VARCHAR2( VARCHAR2 (25 25), ), LINE_AMOUNT NUMBER( NUMBER (10 10, ,2), SEGMENT1 VARCHAR2( VARCHAR2 (25 25), ), SEGMENT2 VARCHAR2( VARCHAR2 (25 25), ), SEGMENT3 VARCHAR2( VARCHAR2 (25 25), ), SEGMENT4 VARCHAR2( VARCHAR2 (25 25), ), SEGMENT5 VARCHAR2( VARCHAR2 (25 25), ), LINE_DESCRIPTION VARCHAR2( VARCHAR2 (100 100), ), ORG_ID NUMBER, NUMBER , VENDOR_ID NUMBER, NUMBER , VENDOR_SITE_ID NUMBER, NUMBER , TERM_ID NUMBER, NUMBER , CODE_COMBINATION_ID NUMBER, NUMBER , last_update_date DATE, DATE , last_updated_by NUMBER, NUMBER , creation_date DATE, DATE , created_by NUMBER, NUMBER , last_update_login NUMBER , status VARCHAR2( VARCHAR2 (50 50), ), error_code VARCHAR2( VARCHAR2 (15 15), ), error_message VARCHAR2( VARCHAR2 (3000 3000) ) ); --> ==================================================================== CREATE OR REPLACE PACKAGE PACKAGE XXAA_APINV_IFACE_PKG XXAA_APINV_IFACE_PKG IS PROCEDURE XXAA_APINV_IFACE_LOAD_PRC PROCEDURE XXAA_APINV_IFACE_LOAD_PRC ( P_SOURCE VARCHAR2 ,P_ORGANIZATION VARCHAR2 ,P_INVOICE_TYPE VARCHAR2 ,P_SUPPLIER_NAME VARCHAR2 ,P_SUPPLIER_NUM VARCHAR2 ,P_SUPPLIER_SITE VARCHAR2 ,P_INVOICE_DATE DATE ,P_INVOICE_NUMBER VARCHAR2 ,P_INVOICE_AMOUNT NUMBER ,P_TERMS VARCHAR2

Ra u Ch

Oracle Custom WEB ADI for AP Invoice Interface Compile the below Custom table and Package in APPS schema --> ==================================================================== DROP TABLE TABLE XXAA_APINV_IFACE_TBL XXAA_APINV_IFACE_TBL; ; CREATE TABLE XXAA_APINV_IFACE_TBL TABLE XXAA_APINV_IFACE_TBL ( SOURCE VARCHAR2( VARCHAR2 (50 50), ), ORGANIZATION VARCHAR2( VARCHAR2 (40 40), ), INVOICE_TYPE VARCHAR2( VARCHAR2 (40 40), ), SUPPLIER_NAME VARCHAR2( VARCHAR2 (60 60), ), SUPPLIER_NUM VARCHAR2( VARCHAR2 (60 60), ), SUPPLIER_SITE VARCHAR2( VARCHAR2 (40 40), ), INVOICE_DATE DATE, DATE , INVOICE_NUMBER VARCHAR2( VARCHAR2 (60 60), ), INVOICE_AMOUNT NUMBER( NUMBER (10 10, ,2), TERMS VARCHAR2( VARCHAR2 (60 60), ), INVOICE_CURRENCY VARCHAR2( VARCHAR2 (25 25), ), GL_DATE DATE, DATE , PAYMENT_CURRENCY VARCHAR2( VARCHAR2 (25 25), ), PAYMENT_METHOD VARCHAR2( VARCHAR2 (40 40), ), LINE_NUM NUMBER, NUMBER , LINE_TYPE VARCHAR2( VARCHAR2 (25 25), ), LINE_AMOUNT NUMBER( NUMBER (10 10, ,2), SEGMENT1 VARCHAR2( VARCHAR2 (25 25), ), SEGMENT2 VARCHAR2( VARCHAR2 (25 25), ), SEGMENT3 VARCHAR2( VARCHAR2 (25 25), ), SEGMENT4 VARCHAR2( VARCHAR2 (25 25), ), SEGMENT5 VARCHAR2( VARCHAR2 (25 25), ), LINE_DESCRIPTION VARCHAR2( VARCHAR2 (100 100), ), ORG_ID NUMBER, NUMBER , VENDOR_ID NUMBER, NUMBER , VENDOR_SITE_ID NUMBER, NUMBER , TERM_ID NUMBER, NUMBER , CODE_COMBINATION_ID NUMBER, NUMBER , last_update_date DATE, DATE , last_updated_by NUMBER, NUMBER , creation_date DATE, DATE , created_by NUMBER, NUMBER , last_update_login NUMBER , status VARCHAR2( VARCHAR2 (50 50), ), error_code VARCHAR2( VARCHAR2 (15 15), ), error_message VARCHAR2( VARCHAR2 (3000 3000) ) ); --> ==================================================================== CREATE OR REPLACE PACKAGE PACKAGE XXAA_APINV_IFACE_PKG XXAA_APINV_IFACE_PKG IS PROCEDURE XXAA_APINV_IFACE_LOAD_PRC PROCEDURE XXAA_APINV_IFACE_LOAD_PRC ( P_SOURCE VARCHAR2 ,P_ORGANIZATION VARCHAR2 ,P_INVOICE_TYPE VARCHAR2 ,P_SUPPLIER_NAME VARCHAR2 ,P_SUPPLIER_NUM VARCHAR2 ,P_SUPPLIER_SITE VARCHAR2 ,P_INVOICE_DATE DATE ,P_INVOICE_NUMBER VARCHAR2 ,P_INVOICE_AMOUNT NUMBER ,P_TERMS VARCHAR2

Ra u Ch

Oracle Custom WEB ADI for AP Invoice Interface ,P_INVOICE_CURRENCY ,P_GL_DATE ,P_PAYMENT_CURRENCY ,P_PAYMENT_METHOD ,P_LINE_NUM ,P_LINE_TYPE ,P_LINE_AMOUNT ,P_LINE_DESCRIPTION ,P_SEGMENT1 ,P_SEGMENT2 ,P_SEGMENT3 ,P_SEGMENT4 ,P_SEGMENT5

VARCHAR2 DATE VARCHAR2 VARCHAR2 NUMBER VARCHAR2 NUMBER VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2

); PROCEDURE XXAA_APINV_IFACE_IMPORT_PRC; PROCEDURE XXAA_APINV_IFACE_IMPORT_PRC ; END XXAA_APINV_IFACE_PKG END XXAA_APINV_IFACE_PKG; ; / --> ==================================================================== CREATE OR REPLACE PACKAGE PACKAGE XXAA_APINV_IFACE_PKG XXAA_APINV_IFACE_PKG IS PROCEDURE XXAA_APINV_IFACE_LOAD_PRC PROCEDURE XXAA_APINV_IFACE_LOAD_PRC ( P_SOURCE VARCHAR2 ,P_ORGANIZATION VARCHAR2 ,P_INVOICE_TYPE VARCHAR2 ,P_SUPPLIER_NAME VARCHAR2 ,P_SUPPLIER_NUM VARCHAR2 ,P_SUPPLIER_SITE VARCHAR2 ,P_INVOICE_DATE DATE ,P_INVOICE_NUMBER VARCHAR2 ,P_INVOICE_AMOUNT NUMBER ,P_TERMS VARCHAR2 ,P_INVOICE_CURRENCY VARCHAR2 ,P_GL_DATE DATE ,P_PAYMENT_CURRENCY VARCHAR2 ,P_PAYMENT_METHOD VARCHAR2 ,P_LINE_NUM NUMBER ,P_LINE_TYPE VARCHAR2 ,P_LINE_AMOUNT NUMBER ,P_LINE_DESCRIPTION VARCHAR2 ,P_SEGMENT1 VARCHAR2 ,P_SEGMENT2 VARCHAR2 ,P_SEGMENT3 VARCHAR2 ,P_SEGMENT4 VARCHAR2 ,P_SEGMENT5 VARCHAR2 ); PROCEDURE XXAA_APINV_IFACE_IMPORT_PRC; PROCEDURE XXAA_APINV_IFACE_IMPORT_PRC ; END XXAA_APINV_IFACE_PKG END XXAA_APINV_IFACE_PKG; ; / --> ==================================================================== CREATE OR REPLACE PACKAGE body body XXAA_APINV_IFACE_PKG XXAA_APINV_IFACE_PKG IS PROCEDURE XXAA_APINV_IFACE_LOAD_PRC PROCEDURE XXAA_APINV_IFACE_LOAD_PRC ( P_SOURCE VARCHAR2

Ra u Ch

Oracle Custom WEB ADI for AP Invoice Interface ,P_ORGANIZATION ,P_INVOICE_TYPE ,P_SUPPLIER_NAME ,P_SUPPLIER_NUM ,P_SUPPLIER_SITE ,P_INVOICE_DATE ,P_INVOICE_NUMBER ,P_INVOICE_AMOUNT ,P_TERMS ,P_INVOICE_CURRENCY ,P_GL_DATE ,P_PAYMENT_CURRENCY ,P_PAYMENT_METHOD ,P_LINE_NUM ,P_LINE_TYPE ,P_LINE_AMOUNT ,P_LINE_DESCRIPTION ,P_SEGMENT1 ,P_SEGMENT2 ,P_SEGMENT3 ,P_SEGMENT4 ,P_SEGMENT5 ) IS l_error_message l_error_code l_org_id l_invoice_num l_vendor_id l_vendor_site_id l_term_id l_code_combination_id

Ra u Ch

VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 DATE VARCHAR2 NUMBER VARCHAR2 VARCHAR2 DATE VARCHAR2 VARCHAR2 NUMBER VARCHAR2 NUMBER VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2 VARCHAR2

VARCHAR2(3000 VARCHAR2( 3000); ); VARCHAR2( VARCHAR2 (2); NUMBER; NUMBER ; VARCHAR2( VARCHAR2 (60 60); ); NUMBER; NUMBER ; NUMBER; NUMBER ; NUMBER; NUMBER ; NUMBER; NUMBER ;

BEGIN l_error_message := '' ''; ; l_error_code :='V' := 'V'; ; -->Organization Validation BEGIN l_org_id := NULL NULL; ; SELECT organization_id SELECT organization_id INTO l_org_id INTO l_org_id FROM hr_organization_units FROM hr_organization_units hou WHERE hou WHERE hou. .name= name=P_ORGANIZATION; P_ORGANIZATION ; EXCEPTION WHEN no_data_found WHEN no_data_found THEN l_error_message := :=l_error_message l_error_message|| ||',' ','|| ||'Organization 'Organization not defined '; '; l_error_code :='E' := 'E'; ; WHEN too_many_rows WHEN too_many_rows THEN l_error_message := :=l_error_message l_error_message|| ||',' ','|| ||'Too 'Too Many Records for Organization '; '; l_error_code :='E' := 'E'; ; WHEN OTHERS THEN l_error_message := :=l_error_message l_error_message|| ||',' ','|| ||'Other 'Other Errors with Organization '|| ' ||SQLCODE SQLCODE ||sqlerrm || sqlerrm; ; l_error_code :='E' := 'E'; ; END; END ; -->Invoice Number Validation BEGIN

Oracle Custom WEB ADI for AP Invoice Interface

Ra u Ch

l_invoice_num := NULL NULL; ; SELECT COUNT (api. api.invoice_num) invoice_num ) INTO l_invoice_num INTO l_invoice_num FROM ap_invoices_all FROM ap_invoices_all api WHERE UPPER UPPER( (api. api.invoice_num) invoice_num ) = UPPER UPPER( (P_INVOICE_NUMBER P_INVOICE_NUMBER); ); IF l_invoice_num > 0 IF l_invoice_num THEN l_error_message := :=l_error_message l_error_message|| ||',' ','|| ||'Invoice 'Invoice number already exist in oracle '; l_error_code END IF IF; ;

:='E' := 'E'; ;

EXCEPTION WHEN no_data_found WHEN no_data_found THEN l_error_message := :=l_error_message l_error_message|| ||',' ','|| ||'Error 'Error in validating the invoice number '; '; l_error_code :='E' := 'E'; ; WHEN OTHERS THEN l_error_message := :=l_error_message l_error_message|| ||',' ','|| ||'Other 'Other Errors with invoice number '|| '||SQLCODE SQLCODE ||sqlerrm || sqlerrm; ; l_error_code :='E' := 'E'; ; END; END ; -->Supplier Validation BEGIN l_vendor_id := NULL NULL; ; SELECT vendor_id SELECT vendor_id INTO l_vendor_id INTO l_vendor_id FROM ap_suppliers FROM ap_suppliers aps WHERE (UPPER UPPER( (aps. aps.vendor_name) vendor_name ) = UPPER UPPER( (P_SUPPLIER_NAME) P_SUPPLIER_NAME ) OR UPPER UPPER( (aps. aps.segment1) segment1) = UPPER( UPPER (P_SUPPLIER_NUM)); P_SUPPLIER_NUM )); EXCEPTION WHEN no_data_found WHEN no_data_found THEN l_error_message := :=l_error_message l_error_message|| ||',' ','|| ||'Supplier 'Supplier not defined '; '; l_error_code :='E' := 'E'; ; WHEN too_many_rows WHEN too_many_rows THEN l_error_message := :=l_error_message l_error_message|| ||',' ','|| ||'Too 'Too Many Records for Supplier '; '; l_error_code :='E' := 'E'; ; WHEN OTHERS THEN l_error_message := :=l_error_message l_error_message|| ||',' ','|| ||'Other 'Other Errors with Supplier '|| '||SQLCODE SQLCODE || ||sqlerrm sqlerrm; ; l_error_code :='E' := 'E'; ; END; END ; -->Supplier Site Validation BEGIN l_vendor_site_id := NULL NULL; ; SELECT vendor_site_id SELECT vendor_site_id INTO l_vendor_site_id INTO l_vendor_site_id FROM ap_supplier_sites_all FROM ap_supplier_sites_all ass WHERE ass WHERE ass. .vendor_id = l_vendor_id AND ass AND ass. .org_id = l_org_id AND UPPER UPPER( (ass. ass.vendor_site_code vendor_site_code) ) = UPPER UPPER( (P_SUPPLIER_SITE); P_SUPPLIER_SITE ); EXCEPTION WHEN no_data_found WHEN no_data_found THEN l_error_message := :=l_error_message l_error_message|| ||',' ','|| ||'Supplier 'Supplier Site not defined '; '; l_error_code :='E' := 'E'; ; WHEN too_many_rows WHEN too_many_rows THEN l_error_message := :=l_error_message l_error_message|| ||',' ','|| ||'Too 'Too Many Records for Supplier Site '; '; l_error_code :='E' := 'E'; ;


Ra u Ch

WHEN OTHERS THEN l_error_message :=l_error_message||','||'Other Errors with Supplier Site '||SQLCODE ||sqlerrm; l_error_code :='E'; END; -->Terms Validation BEGIN l_term_id := NULL; SELECT term_id INTO l_term_id FROM ap_terms apt WHERE UPPER(apt.name) = UPPER(P_TERMS); EXCEPTION WHEN no_data_found THEN l_error_message :=l_error_message||','||'Terms not defined '; l_error_code :='E'; WHEN too_many_rows THEN l_error_message :=l_error_message||','||'Too Many Records for Terms '; l_error_code :='E'; WHEN OTHERS THEN l_error_message :=l_error_message||','||'Other Errors with Terms '||SQLCODE ||sqlerrm; l_error_code :='E'; END; -->Account segment Validation BEGIN SELECT code_combination_id INTO l_code_combination_id FROM gl_code_combinations WHERE segment1= P_SEGMENT1 AND segment2 = P_SEGMENT2 AND segment3 = P_SEGMENT3 AND segment4 = P_SEGMENT4 AND segment5 = P_SEGMENT5; EXCEPTION WHEN no_data_found THEN l_error_message :=l_error_message||','||'Invalid Flex field combination '; l_error_code :='E'; WHEN too_many_rows THEN l_error_message :=l_error_message||','||'Too Many Records for Flex field combination '; l_error_code :='E'; WHEN OTHERS THEN l_error_message :=l_error_message||','||'Other Errors with Flex field combination '||SQLCODE ||sqlerrm; l_error_code :='E'; END; -->Insert the data into the staging table INSERT INTO XXAA_APINV_IFACE_TBL ( SOURCE ,ORGANIZATION ,INVOICE_TYPE ,SUPPLIER_NAME ,SUPPLIER_NUM ,SUPPLIER_SITE ,INVOICE_DATE ,INVOICE_NUMBER

Oracle Custom WEB ADI for AP Invoice Interface ,INVOICE_AMOUNT ,TERMS ,INVOICE_CURRENCY ,GL_DATE ,PAYMENT_CURRENCY ,PAYMENT_METHOD ,LINE_NUM ,LINE_TYPE ,LINE_AMOUNT ,LINE_DESCRIPTION ,SEGMENT1 ,SEGMENT2 ,SEGMENT3 ,SEGMENT4 ,SEGMENT5 ,ORG_ID ,VENDOR_ID ,VENDOR_SITE_ID ,TERM_ID ,CODE_COMBINATION_ID ,last_update_date ,last_updated_by ,creation_date ,created_by ,last_update_login ,status ,error_code ,error_message ) VALUES (UPPER(P_SOURCE) ,P_ORGANIZATION ,UPPER(P_INVOICE_TYPE) ,P_SUPPLIER_NAME ,P_SUPPLIER_NUM ,P_SUPPLIER_SITE ,P_INVOICE_DATE ,P_INVOICE_NUMBER ,P_INVOICE_AMOUNT ,P_TERMS ,P_INVOICE_CURRENCY ,P_GL_DATE ,P_PAYMENT_CURRENCY ,P_PAYMENT_METHOD ,P_LINE_NUM ,P_LINE_TYPE ,P_LINE_AMOUNT ,P_LINE_DESCRIPTION ,P_SEGMENT1 ,P_SEGMENT2 ,P_SEGMENT3 ,P_SEGMENT4 ,P_SEGMENT5 ,L_ORG_ID ,L_VENDOR_ID ,L_VENDOR_SITE_ID ,L_TERM_ID ,L_CODE_COMBINATION_ID ,sysdate

Ra u Ch

Oracle Custom WEB ADI for AP Invoice Interface ,fnd_global.user_id ,sysdate ,fnd_global.user_id ,fnd_global.login_id ,'NEW' ,l_error_code ,SUBSTR(l_error_message,2) ); IF (l_error_code='E') THEN raise_application_error(-20101,SUBSTR(l_error_message,2)); END IF; EXCEPTION WHEN OTHERS THEN raise_application_error(-20102,'Error -'||SQLCODE||'-'||sqlerrm); END XXAA_APINV_IFACE_LOAD_PRC; --> -------------------------------------------------------------------PROCEDURE XXAA_APINV_IFACE_IMPORT_PRC IS CURSOR c_inv IS SELECT DISTINCT api.source, api.org_id, api.invoice_type, api.vendor_id, api.vendor_site_id, api.invoice_date, api.invoice_number, api.invoice_amount, api.term_id, api.invoice_currency, api.gl_date, api.payment_currency, api.payment_method FROM XXAA_APINV_IFACE_TBL api WHERE api.error_code='V' ORDER BY api.org_id, api.invoice_type, api.vendor_id, api.vendor_site_id, api.invoice_number; CURSOR c_lin(X_INVOICE_NUMBER VARCHAR2) IS SELECT apl.line_num, apl.line_type, apl.line_amount, apl.code_combination_id, apl.line_description FROM XXAA_APINV_IFACE_TBL apl WHERE apl.error_code='V' AND apl.invoice_number = X_INVOICE_NUMBER

Ra u Ch

Oracle Custom WEB ADI for AP Invoice Interface ORDER BY apl.line_num; l_batch_name l_conc_request_id l_phase l_status l_dev_phase l_dev_status l_message l_request_status l_count l_inv_seq

VARCHAR2(100) := TO_CHAR(SYSDATE,'DD-MON-RR:HH24MISS'); NUMBER; VARCHAR2(25); VARCHAR2(25); VARCHAR2(25); VARCHAR2(25); VARCHAR2(500); BOOLEAN; NUMBER; NUMBER;

BEGIN BEGIN FOR r_inv IN c_inv LOOP select ap_invoices_interface_s.NEXTVAL into l_inv_seq from dual; INSERT INTO AP_INVOICES_INTERFACE ( invoice_id, source, org_id , invoice_type_lookup_code, vendor_id, vendor_site_id, invoice_date, invoice_num, invoice_amount, terms_id, invoice_currency_code, gl_date, payment_currency_code, payment_method_lookup_code ) VALUES ( l_inv_seq, r_inv.source, r_inv.org_id, r_inv.invoice_type, r_inv.vendor_id, r_inv.vendor_site_id, r_inv.invoice_date, r_inv.invoice_number, r_inv.invoice_amount, r_inv.term_id, r_inv.invoice_currency, r_inv.gl_date, r_inv.payment_currency, r_inv.payment_method ); FOR r_lin IN c_lin(r_inv.invoice_number) LOOP

Ra u Ch


Ra u Ch

INSERT INTO AP_INVOICE_LINES_INTERFACE ( invoice_id, invoice_line_id, line_number, line_type_lookup_code, amount, dist_code_combination_id, description ) VALUES ( l_inv_seq, ap_invoice_lines_interface_s.NEXTVAL, r_lin.line_num, r_lin.line_type, r_lin.line_amount, r_lin.code_combination_id, r_lin.line_description ); END LOOP; END LOOP; COMMIT; END; l_conc_request_id := FND_REQUEST.SUBMIT_REQUEST ( APPLICATION ,PROGRAM ,ARGUMENT1 ,ARGUMENT2 ,ARGUMENT4 );

=> 'SQLAP' => 'APXIIMPT' => '204' => 'MANUAL INVOICE ENTRY' => l_batch_name

COMMIT; l_request_status := FND_CONCURRENT.WAIT_FOR_REQUEST ( l_conc_request_id, 60, 0, l_phase, l_status, l_dev_phase, l_dev_status, l_message ); COMMIT; EXCEPTION WHEN OTHERS THEN raise_application_error(-20103,'Error -'||SQLCODE||'-'||sqlerrm); END XXAA_APINV_IFACE_IMPORT_PRC; END XXAA_APINV_IFACE_PKG; / --> ====================================================================

Oracle Custom WEB ADI for AP Invoice Interface Integrator 1. Create Integrator Navigation : Desktop Integration Manager Responsibility-> Create Integrator

Integrator Name Internal Name Application Next

: XXAA APINV Interface Integrator : XXAA_APINV_IFACE_INTEGRATOR : Payables

Ra u Ch


2. Create Interface Interface Name Interface Type Package Name Procedure/Funtion API Returns Click on Apply button

: XXAA APINV Interface : API – Procedure : XXAA_APINV_IFACE_PKG : XXAA_APINV_IFACE_LOAD_PRC : FND Message Code

Ra u Ch


Select the Interface and Click on Next button

Ra u Ch


3. Create Content Content Name

: XXAA APINV Interface Content

Ra u Ch

Oracle Custom WEB ADI for AP Invoice Interface Content Type Number of columns Apply

: Text File :9

Rename the display names as per csv header columns for better under standing And provide the content parameter (download parameter) as Web ADI: Download "Text File" Parameters

Ra u Ch


4. Create Uploader

Ra u Ch


Ra u Ch


5. Create Importer Select importer type as PL/SQL API Importer Name : XXAA_APINV_IFACE

Ra u Ch


Click on PL/SQL API Call plus button to add our custom procedure

Ra u Ch


Enter following information API Type : PL/SQL Procedure Package Name : XXAA_APINV_IFACE_PKG Procedure Name : XXAA_APINV_IFACE_IMPORT_PRC API Returns : FND Message Code Click on apply and then submit button

Ra u Ch


Ra u Ch


Ra u Ch

Oracle Custom WEB ADI for AP Invoice Interface Layout 1. Create Layout Navigation: Desktop Integration Responsibility Select the integrator Go Create button

Define Layout

2.

Enter the name : XXAA APINV Interface Layout

3.

Include the fields in layout, Select the line in Placement

Ra u Ch


Click on Apply button

Ra u Ch


Ra u Ch


2. Define Mapping 1. Define mapping Navigation: Desktop Integration Responsibility -> Defining mapping-> select integrator-> go

2.

Click on define mapping button

3. Enter the mapping details Mapping Name : XXAA APINV Interface Mapping Mapping Key : XXAA_APINV_IFACE_MAPPING Number of column :9

Ra u Ch


4.

Map the source and target columns

Ra u Ch


3. Create Document 1. Create Document :- Test the document with sample data Navigation: Desktop Integration Responsibility -> Create Document -> select integrator

2.

Select the desktop application like excel or word

3. 4.

Select the layout if there are multiple layouts Select the content

Ra u Ch


5.

Select the data file

6.

Select the NONE to open the Excel file

Ra u Ch

Oracle Custom WEB ADI for AP Invoice Interface 7.

Click on create document button

8.

Click on create document button to download the sheet

Ra u Ch


Enter the data and upload and verify the invoice number from application

Note: we have developed the sheet. Now it needs some cosmetic changes

Ra u Ch

Oracle Custom WEB ADI for AP Invoice Interface Changes 1. Change the Labels of fields Navigation: Go to integrator-> Interface-> Enter the Integrator name and click on Go

Click on Update button

Click on Next

Ra u Ch


Select the Integrator

Ra u Ch


Change the Prompt Left Label

Ra u Ch


Ra u Ch

Oracle Custom WEB ADI for AP Invoice Interface Click on the Update button and change Above Prompt then Save and Submit

Ra u Ch

Oracle Custom WEB ADI for AP Invoice Interface 

Ra u Ch

Create the document to check new changes

2. Assign LOV and hints to the columns Navigation: Go to integrator-> Interface->click on update button of respective attribute (say P_INVOICE_CURRENCY) to add LOV check the not null check box to indicate required columns in spread sheet Select the validation type as table and enter below information Validation Type : Table Id Column : CURRENCY_CODE Meaning Column : CURRENCY_CODE Desc Column : CURRENCY_CODE Validation Entity : FND_CURRENCIES

Provide the information under user hint text item as ‘*List–Text’ to give an idea user about what value should be entered or select




Do the same for other columns too then save and get the new document to see the changes

Ra u Ch


Ra u Ch

3. Add default value as ‘USD’ to currency code item Navigation: Go to integrator-> Interface-> select the default type and default value for currency code attribute

Do the changes for all required fields


Ra u Ch


Result

Ra u Ch

Oracle Custom WEB ADI for AP Invoice Interface Defining the Form Function for Integrator Define the form function for integrator to run the custom ADI document from any responsibility

1. Define the custom Form Function Navigation -> Application Developer -> Application -> Function

Function User Function Name

: XXAA_APINV_IFACE_FF : XXAA APINV Interface Integrator

Ra u Ch


Ra u Ch

Properties Type - SSWA servlet function

Form Parameters

bne:page=BneCreateDoc&bne:viewer=BNE:EXCEL2007&bne:reporting=N&bne:integrator=USER_NAME:XXA A APINV Interface Integrator&bne:noreview=Yes


Web HTML HTML Call : BneApplicationService

Save it. Assign Function to Custom Menu Go and add the function into the custom responsibility Navigation-> Application Developer -> Application -> Menu

Ra u Ch


Ra u Ch

\ Search for your menu where you want to add this ADI Menu : AP_NAVIGATE_GUI12 Prompt : XXAA APINV Interface Integrator Function : XXAA APINV Interface Integrator


Now you can navigate to your custom responsibility to see the ADI which you have assigned.

Ra u Ch


Ra u Ch

Test the sheet 1. Once you create document, it will populate the file data on the sheet

2.

Fill the required information

Source

OU Name

MANUAL INVOICE ENTRY

Vision Opera tions


Vision Opera tions


Vision Opera tions

Inv Typ e STA ND AR D STA ND AR D STA ND AR D

Supp Nam e Dell Com puter s Dell Com puter s Dell Com puter s

Sup p Nu m

Su pp Sit e

509 2

DE LL

509 2

DE LL

509 2

DE LL

Inv Date 15NO V2015 15NO V2015 15NO V2015

Inv Num 15NOV2015_0 01 15NOV2015_0 01 15NOV2015_0 01

In v A mt

9.9 9

9.9 9

9.9 9

Ter ms Im me diat e Im me diat e Im me diat e

In v Cu rr

US D

US D

US D

GL Date 15MA R2015 15MA R2015 15MA R2015

Pa y Cu rr

Pay Met hod

US D

CH ECK

US D

CH ECK

US D

CH ECK

Lin e Nu m

Lin e Ty pe

Lin e A mt

Line Descripti on

1

ITE M

4.4 4

15-NOV2015_001 Line_01

2

ITE M

3.3 3

15-NOV2015_001 Line_02

3

ITE M

2.2 2

15-NOV2015_001 Line_03

C o m p

A c c t

01

1 1 0

01

1 1 0

01

1 1 0

D e p t 6 1 0 0 6 1 0 0 6 1 0 0

3. Upload the data into oracle Navigation: Add-Ins Tab-> Oracle-> Upload

4. Once we click on upload button, it will pop up the upload page where we have to select ‘Automatically Submit Import’ check box as we have added import procedure in importer rule . If it is checked then only it will call that import program, otherwise it wont

O u lt 0 0 0 0 0 0 0 0 0 0 0 0

P r o d

0 0 0 0 0 0 0 0 0


5.

Ra u Ch

If there is any error, then program will return error message and will rollback the transaction

6. After correcting the data, run again. And if there is no error, then program will insert all validated data into oracle tables and call the import journal program

Click on Upload button


Wait until Import program completed

7. We can monitor the import program from spread sheet itself by clicking on monitor button Click on the Monitor to check the request id Nav : Add-Ins Oracle Monitor

Ra u Ch


Verify the concurrent request id from application

Ra u Ch


Click on View Output button

Ra u Ch


Search with Invoice number from payables responsibility

Ra u Ch

Oracle Custom WEB ADI for AP Invoice Interface

Recommend Documents