About Sample AP Invoice Source file structure

Here, We will discuss about an AP Standard Invoice source file structure and about the fields which are required to import to ERP

Source file Row types: The source file has 3 types of records: Header, Line and Trailer.

INVOICE HEADER FIELDS

INVOICE LINE FIELDS

INVOICE TRAILER FIELDS

INVOICE HEADER

INVOICE LINE 1

INVOICE LINE2

INVOICE HEADER

INVOICE LINE1

INVOICE 2

...

INVOICE TRAILER

Invoice Source file example: The highlighted fields are mainly required

Invoice,InvoiceNumber,Supplier Name, Supplier Number, Status, Invoice Date, Submit for Approval?,Handling Amount, Misc Amount, Shipping Amount, Line Level Taxation, Tax amount, Tax Rate,Tax Code, Supplier Note, Payment Terms, Shipping Terms, Chart of Accounts, Currency, Contract Number,Image Scan FileName, Image Scan URL, Taxes In Origin Country Currency  Delivery Number, Delivery Date, Ship To Name, Ship To Id, Ship To Attention, Ship To Street1,Ship To Street2, Ship To City,Ship To State,Ship To Postal Code,Ship to Country Code,Ship to Country Name,Bill To Address Id,Bill to address Legal Entity Name,Bill to address Street, Bill To Address City, Bill To Address Postal Code,Bill To Address Country Code, Remit To Address Street1,Remit to address Street2,Remit To address City, Remit to address state, Remit to address postal code,Remit to address Country code,Remit to code,Remit to Tax Prefix,Remit to tax number,Remit to tax country code,Original Invoice number,Original Invoice date, Is Credit Note, Attachment 1, Attachment 2,Attachment 3 , Attachment 4, Attachment 5,Attachment 6, Attachment 7, Attachment 8, Attachment 9, Attachment 10

Invoice Line,Invoice Number,Supplier Name,Supplier Number, Line Number,Description,Supplier Part Number, Price, Quantity, Line Tax Amount,Line Tax Rate,Line Tax Code,Line Tax Location,Line Tax Description,Line Tax Supply Date,Unit of Measures,PO Numner, PO Line Number, Account Name, Account Code, Account Segement 1, Account Segment 2, Account Segment 3, Account Segment 4, Account Segment 4, Account Segment 5, Account Segment 6, Account Segment 7, Account Segment 8, Account Segment 9, Account Segment 10, Account Segment 11, Account Segment 12, Account Segment 13, Account Segment 14, Account Segment 15, Account Segment 16, Account Segment 17, Account Segment 18, Account Segment 19, Account Segment 20,Account Allocation Amount, Account Allocation Percent, Budget period Name, Net Weight , Weight UOM, Price Per Weight

Invoice Trailer,Invoice Count,Total Amount

"Invoice","001","",000123","Approved","2023-10-17T19:00:00-05:00","N","1000.00","","","No","","","","Suppler Note","","","ABC","USD",.....

"Invoice Line","001","","000123","1","Description","","1000.00","001","","","","","","","EA","","","","501-12345-511234-"....

"Invoice Tralier","1","1000.00"

Why do we need COA lookup:

For some segment like Company name , source team represents it like "ABC" which Oracle understands as "101" and etc. , this type of domain value maps, we need to create a lookup in OIC to map the source value to Oracle specific target values.

For the above file invoice, a distribution combination created as below:

501-511234-12345-00-L-000-0000000-XX-0000-00000-00-000-0000-0000

Distribution Combination provides analysis by the account combination on the transaction distribution that represents a monetary transaction.

Important points: 

• A Source file contains 3 types of records: Header, Line and Trailer.
• One invoice has two parts: Header and Line. 
• Each Invoice can have multiple lines
• Invoice header has invoice number, supplier number, invoice approval status, invoice date, total handling invoice amount, supplier note, COA, currency details
• Invoice line have similar information like header plus line level invoice break ups.
• Tralier contains the total invoice counts and total invoice amount which are required for validation.
• Using the invoice line Account code, distribution combination needs to be created.

Database - Difference between oracle Date and Timestamp types

• DATE and TIMESTAMP have the same size (7 bytes). Those bytes are used to store century, decade, year, month, day, hour, minute and seconds. But TIMESTAMP allows to store additional info such as fractional seconds (11 bytes) and fractional seconds with timezone (13 bytes).
• TIMESTAMP was added as an ANSI compliant to Oracle. Before that, it had DATE only.
• In general cases you should use DATE. But if precision in time is a requirement, use TIMESTAMP.

Reference:

https://stackoverflow.com/questions/19141030/difference-between-oracle-date-and-timestamp

OIC - DB polling and logical delete from oracle database in Oracle Integration

Usecase: Here, we will show you how to poll records from a database based on status field logical delete concept and if the record has non empty email id then, it will write them to a ftp directory, else update the record as ERROR in the database table.

Logic steps:

1. Create a database connection as Oracle database or dbaas 
2. Create a db table and insert records with status as NEW.
3. Create an app driven orchestration integration and configure db polling where 
1. Logical delete field = Status
2. Read value= PROCESSED
3. Unread value = NEW
4. Rejected value =REJECTED
5. POLLING Frequency = 30 sec
4. Add a switch and check if the record has non empty email id and then write them to a ftp ditectory.
5. Else configure db adpater invoke and update the status as ERROR and update date using current-dateTime() function.

Notes:

• Support for processing message payloads up to 10 MB in size. In the case of polling, we must set the Rejected value property to REJECTED on the polling strategy and options page. If the incoming message is greater than 10 MB threshold size, that particular record is updated to REJECTED instead of read or new.
• For each db record each instance will be created.

Create table query:

Create table SD_EMPLOYEE_Test(

ID NUMBER PRIMARY KEY,

FirstName VARCHAR2(200),

LastName VARCHAR2(200),

Department VARCHAR2(100),

Age NUMBER,

EmailAddress VARCHAR2(100),

Status VARCHAR2(10),

CreateDate Date,

UpdateDate Date

);

Detailed steps with screenshots:

Integration flow:

Configure db polling trigger

Edit > Review and verify table and relationship attributes

Edit > Review the polling strategy and specify polling options.

Add a switch and have below condition

Configure write file FTP part

Map the polled record to ftp directory

In else case, configure update db status part.

Testing: 

6 records added with status as NEW

After activating the integration, we can see, 5 records processed and 1 in ERROR as no email id.

Below 5 files created for each eno empty email record :

OIC - Gen3 - Read file in segment - Set your own chunk size

In OIC Generation 2, we can also read file in segments or chunks using stage action but the chunk size is default limited to 200 records which we were not allowed to change.

In Generation 3 (23.04 onwards), it brings new feature that we can choose our own chunk or segment size from minimum 200 to maximum 2000 records.

Jdeveloper 12c - unable to launch the java virtual machine located at path: C:\\msvcr100.dll

Issue details: 

Sudden old java jdk version has been removed and new version jdk1.8.0_361 INSTALLED to my system. When I was trying to open the oracle jdeveloper 12c, its showing below error: 

Unable to launch the java virtual machine located at path: C:\Program Files \Java\jdk1.8.0_361\jre\bin\msvcr100.dll

Solution:

Step1: setup the java jdk path

Search with system enviroment variables in windows 10 

>> environmental variable 

>> edit user variable JAVA_HOME with jdk bin path like C:\Program Files \Java\jdk1.8.0_361\bin 

>> edit system variable path and add New as C:\Program Files \Java\jdk1.8.0_361\bin

>> ok >> ok >> ok

Step2:  Copy the msvcr100.dll file

From:

C:\Windows\System32

To:

C:\Program Files \Java\jdk1.8.0_361\jre\bin

These above 2 steps will resolve the issue.

OIC - Create a Reusable integration to pass Request Payload into Database table as clob type

Usecase: Here, we will create a reusable component or integration which will take any data XML or Json or etc as binary data and simply save as Text to database table as clob type.

Follow my below blog how to save XML or Json file into stage and then save the request payload into Database table as clob type.

https://soalicious.blogspot.com/2023/10/oic-save-integration-request-payload.html

Logic flow:

1. Create a database table with payload column as clob type.
2. Create a rest trigger and database connection.
3. Create an app driven integration and configure the rest trigget and request payload format as binary.
4. Configure the database table to insert the payload.
5. Map the binary request payload to database payload using  decodebase64(encodeReferenceToBase64(Stream Reference) functions (Basically we are converting the stream reference to base64 data and then decode the base64 data).

Table create:

CREATE TABLE OIC_TRACKING(

Id INTEGER PRIMARY KEY,

Payload CLOB,

Integration_Name VARCHAR2(100),

Integration_Identifier VARCHAR2(100));

Detailed steps with screenshots:

Integration flow:

Configure rest to pass binary data

Configure database to insert 

Map the stream ref to database table payload column.

Testing:

OIC - Save Integration request payload XML/JSON into database

Usecase: 

Here, We have a project requirement that we have to save the integration source data XML or Json into a database table so that the support person can track the payload if required. In addition, we need to save the metadata details like instance id, name, identifier etc.

Though Oracle integration allows us to track the complete complete using activity stream if debug or audit tracing is enabled. Saving the payload in the activity may have a security rick as the payload may contain sensitive data. 

But having a payload handy is essential for the operations or support team to debug the issues. Instead of storing the payload in the activity stream, we can save it into a persistent store like a database.

Note: here, we will save a Request Json data to database table, the same you can do using xml data.

Logic steps:

1. Create a database table where payload column is as clob type.
2. Create a database connection and a rest trigger connection.
3. Create an appdriven integration and configure Rest to pass the json data as request paylaod.
4. Write the request content into a stage location using the XML or JSON structure which ever required.
5. Configure the database to insert
6. Map the staged payload reference to database paylaod column using decodebase64(encodeReferenceToBase64(FileReference) functions.

Table create:

CREATE TABLE OIC_TRACKING(

Id INTEGER PRIMARY KEY,

Payload CLOB,

Integration_Name VARCHAR2(100),

Integration_Identifier VARCHAR2(100));

Detailed steps (with screenshots):

Integration flow:

Configure rest trigger to send request

Assign some integration metadata specific details like instance id, name, identifier.

Write the JSON file into stage location

Configure the database to insert data

Map the stage payload to database payload column.

Test: