Hi Friends,
During an exercise session at gym with a colleague, she
shared with me the idea of blogging. I sort of liked it, but then I thought
since the people around the globe are already very much knowledgeable, what
difference would I make to them. I assumed (still assume so ;) ) that I don’t have
enough of knowledge to shed light on technical concepts of Oracle E-Business.
But the same day, I was given a task of creating a customer
interface for a new business unit added in the existing business group. This
was the first interface I ever had to make in my career. Interfaces were a
dream challenge for me; I hope the same for most of technical resources here. But
this challenge brought some fear of not being able to deliver with quality or
may be not being able to meet the timelines. I just knew I had to make this
interface, why, what (as in the scope) and how was something I did not even know.
Anyhow, I managed to meet with the customer. Got to know
what requirements are, on a very vague level since I had no idea what
information you should get in order to make a Receivables Customer interface.
Thanks to my new line manager, he helped me a lot in getting to the right
direction. And now when I am able to deliver it, I believe I have got something
to share with the people like me about what major steps we need to follow to
develop an interface. Let me tell you, there would be some additional steps in
your business case. But one thing I am sure about is, cycle remains the same.
So here is the basic steps that I got to follow when I wsa
going to create an inbound (an interface that is feeding information into
E-Business Suite). Our business case is Receivables Customer. I am not limiting
to the technical details here but the whole process steps :)
1.
Firstly, I suggest you to check with the person
(most probably your boss or a change manager) who is assigning the activity
about the basic purpose of the interface. Why does customer want it in his/her opinion?
2.
Get the full contact details of the customer
3.
Arrange meeting(s) with them and focus on the
following points specially.
a.
What is the target they are going to achieve?
b.
Is it currently being achieved by some other way/process?
c.
If yes, how they are doing it currently? Get all
the inputs and outputs of current process
d.
What is their understanding by the interface
e.
What is their expected data loading frequency?
f.
Their sample data. Data should have all the
required fields (mandatory and optional) populated, at least a few of the
sample records should not have any null value. The best sample data should have
atleast 30 – 35 records with maximum variance captured.
g.
Check if they want acknowledgment against the
loaded data and if yes, in what form?
Make sure you get clear to all of
the points raised during these requirement sessions, this will make your life
heaven during development stage, as I did not and I really paid for it. But not
all of us have knowledgeable as well as helping manager as mine. So better be
careful at requirement analysis stage :D
4.
Now that you have got the sample data, identify the
set of activities that you have to perform to achieve the target. And identify the
interface tables for them. Check the mandatory fields’ requirement for each
table and then make a query that works to load the data into interface table. Your
target is, you got to load that sample data into the EBS Standard interface
table(s). In my case, I had to create customer and its profile. So, I had to populate
two tables as per the data shared by user
a. RA_CUSTOMERS_INTERFACE_ALL
b. RA_CUSTOMER_PROFILES_INT_ALL
One thing, I must suggest you here
is, to read the list of required/mandatory columns for these interface tables.
Ok, I can help you by providing some links that can explain it for you J
Let me share my sample queries with
you, to load data into both of the tables. You would have to use them as per
your requirement (as per the sample data you have).
For RA_CUSTOMERS_INTERFACE_ALL, it is going to be something like
INSERT INTO RA_CUSTOMERS_INTERFACE_ALL
(orig_system_customer_ref,
customer_name,
orig_system_address_ref,
address1,
city,
county,
state,
country,
postal_code,
site_use_code,
primary_site_use_flag,
customer_status,
insert_update_flag,
last_updated_by,
last_update_date,
created_by,
creation_date,
org_id,
LOCATION)
VALUES
('ORIG_CUSTOMER_REF',
'CUST_NAME',
'ABCD',
'ADDRESS1',
'CITY',
'COUNTY',
'STATE',
'COUNTRY',
'POSTAL CODE',
'BILL_TO',
'Y',
'A',
'I',
-1,
sysdate,
-1,
sysdate,
1234,
'location');
For RA_CUSTOMER_PROFILES_INT_ALL,
it is going to be something like
INSERT INTO RA_CUSTOMER_PROFILES_INT_ALL
(INSERT_UPDATE_FLAG,
ORIG_SYSTEM_CUSTOMER_REF,
CUSTOMER_PROFILE_CLASS_NAME,
CREDIT_HOLD,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
ORG_ID)
VALUES
('I',
' ORIG_CUSTOMER_REF ',
'Customer profile Class',
'N',
0,
sysdate,
0,
sysdate,
0,
1234);
5.
Once I was done with the interface tables, I
luckily got the standard concurrent program with the name of Customer
Interface. It fetches the data from interface tables and populates the data
into actual tables. No wait, it’s not all done L.
Once I ran the Customer Interface, I got a lot of errors. These errors are
populated in INTERFACE_STATUS column
of the interface tables; I had to decode them and resolve them one by one, this
is how we get the data cleansed. You might also have to involve the customer
here.
6.
Once I was able to remove all the errors, my
customer was finally created in Receivables module. It was indeed a happy
moment for me. But it’s not done as yet. I just got to know how to achieve the
target was not able to set it up in the system. This was just done through
toad. I still got some things to do.
7.
After being able to create the customer, I had 2
things
a.
Cleansed Data
b.
Refined Query
8.
Now with cleansed data, I got to create a data
file. I made a *.csv file as my data file
9.
And with refined query, I wrote the control file
(*.ctl) that will read data from my *.csv file
10.
Then I placed the control file to the Bin
directory of the targeted module. And also placed data file anywhere at the
server (I just noted the directory path of data file location as well))
11.
Then, logged in to the EBS application through
system administrator. Created an executable with SQL*LOADER type and set its
application to the targeted module, execution file name as the control file
name of yours (the one I created in step 9). Named it appropriately (as per my
company standards ;) )
12.
Next step, of course you know, was to create corresponding
concurrent program having output as text type and a parameter of filename,
having the full directory path and file name of data file (the path I noted in
step 10 was for some purpose of course :) )
13.
Next Step was to check the request group against
the targeted responsibility and addition of our concurrent program to the
identified request group.
14.
Switched to the targeted responsibility and run the
concurrent program(s) (the one(s) I created). and then finally Customer Import
standard concurrent program
15.
Then created a request set and added all the
concurrent programs in the required sequence to the request set.
16.
Scheduled it as per the required frequency.
And finally I was all done. I hope
I am able to provide some assistance by sharing my knowledge.
Here are some useful links
If you need any assistance, feel
free to mail me at sana.sagheer@gmail.com
Happy development :)
