Hi all,

Can anybody please tell me the steps to map a flatfile of Bank Statement(BAI2) mapped to the Cash Management Interface tables:

1. CE_STATEMENT_HEADERS_INT_ALL
2. CE_STATEMENT_LINES_INTERFACE

please let me know the steps to be done to import the data from the flatfile to Oracle Applications 11i Cash Management Interface tables.

Thank You,
Priyatham Porika
porikap@yahoo.com

Priyatham,

Hope you are using IS6.0 and above versions.

First step will be parsing the FlatFile(BA12)document based on the created FlatfileDictionary/Schema and extract the data using WmFlatFile Adapter package service pub.flatfile:convertToValues(documentReference the BA12 IS DocumentType)and perform mapping the fields of the above metioned tables based on your functional specs.

Then either using WmJDBC Adapter or OracleAPPS Adapter inserts the data in the target Oracle11i tables.This is the basic procedure followd for the FlatFile to DB integrations.

Also please check in the WmFlatFile Samples package it will help mapping a flatfile to IS DocumentType (example:XML type)

HTH,

Hi guys, Priyatham again,

I enclosed the BAI2 sample file

My question is in BAI2 file the Header starts with 01 and its corresponding lines start with 16.

How should I proceed in creating the Flatfile Directory/Schema first.
How will WM recognise whether it is a parent or child records in the file.(Please refer the Flat file Directory Document attached).

Can anybody tell me the sequence of steps to parse the Flatfile and map it to interface tables.

Thank You in Advance,
Priyatham Porika

Priyatham,

Not sure where the confusion is so here goes.
Define you record structure in the Dictionary.
Then you will need to find your flat file structure. It is there that you will need to name the record according to your record identifier.
Therefore your header record will name 01.
The next record 02 then 03.
The detail record 16 and will be repeating.
then the remainder of the records.

As for the remainder see RMG’s reply above.

HTH

Priyatham saab,

I have seen your sample BA12 FF(.csv file).

Using FlatFileEditor GUI ,Create a Dictionary or Schema directly
so under the FlatFileDefinition tab Choose RecordParser (Delimiter and select Record Character (newline) and Choose Field or Composite(Character set (,)seperated in the listbox.

And under the FlatFile Structure tab Create a RecordDefinition (01)Header record respective FieldDefinitions and similarly create another RecordDefinitions (02,03)records which should be beneath 01 and lineitems(16)RecordDefinition this should also be beneath 01 and if you click on the 16 recorddefinition down on the properties tab you will see Max Rep tab (set this to unlimited)so that 16 record will be repeated.

Finally once you done creating RecordDefinitions,FieldDefinitions there is option in the editor (Test).So browse the BA12 file and run the schema and check the results tab whether the file is extracting the data as expected.

please also check the WmFlatFileSamples package to get an idea how to create a FlatFileSchema and refer my above comments.

HTH,

You guys are really excellent and amazing. I think that’s what I was looking for. That solves my problem.

Thank You so much.
PriyathM Porika

Hi RMG/HTH bhai,

I did as you have mentioned but one small problem. As you mentioned above I have done but when I create the Field Definitions in the Flat File Structure tab:

In the Field Definition’s Names window it asks for Name : Start :End & Mandatory things. What will be Start and End needs to be. Since my BAI2 file is comma seperated right. What shall I give in start and end fields.

See attachment.
Thank you,
Priyatham porika

In the Field Definitions window you should select the NthPosition in the top listbox,so that it will show just FieldName,Position (Enter 1,2,3,4,5…nth)for each field.So this works for Comma seperated file.

And the other Start,End positions is for a FixedPosition flatfile.

HTH,

Thank You guys,

Have wonderful weekend.

Priyatham Porika

Hello Guys, Priyatham again:

Quick Questions:

1. Example:
   I have One header and 4 lines in my flat file. In my file Iam not
   provided with Line numbers. Since I have 4 lines I have to generate
   4 line number 1,2…4 for this header. How can I do that? What are
   the steps and How can I map in the Pipeline.

I am able to validate the BAI2 flatfile and now I am mapping the FF Schema document to Cash Management Interface tables.

2. Since I have Multi Organization at my client site. I have to
   populate Org_id in one of the interface table. To do this I will
   have to call the package/Function “FND_PROFILE.VALUE(‘ORG_ID’)” so
   that when I try to insert the value in Cash Management Interface
   tables by logging into corresponding responsibility I have to get
   the Org_id. How can I call this pacjage for Org_id field.

In Oracle Financials I just call the package “FND_PROFILE.VALUE(‘ORG_ID’)”. How can I do that in WM.

3. I need to map some of the fields from header to child records. When
   I try to map I get an error saying “More then one copy into the
   path different common parent(s)” is displayed.

How can I overcome this problem.

Thank You guys very much.
You were so helpful that I could get till this stage. If this is done then I think I need to import the data and run the Oracle Applications Standard Import program.

Can anybody help me please.

Thank You in Advance,
Priyatham Porika

Priyatham saab,

Here are the few answers regarding,see if it can helps

1Ans)Once you load and extract the flatfile data based on the Schema/documenttype,you will see the Header and Lineitems (16 record).So use the WmPublic service (pub.list:sizeOfList)to get the count of lineitems or else using loop step ($iteration) param you can get the count (1,2,3,4…)

2Ans)Using JDBCAdapter CustomSQL or DynamicSQL or Storedprocedure you can call that package/function.

3Ans)The error indicates that you are trying to map a string field which is already mapped.you can do this if the variable is a list.
By the way are you mapping the B12 flatfile to some canonical document that will be used to insert into DB tables?

HTH,

Hi RMG/HTH bhai,

Case1:

"So use the WmPublic service (pub.list:sizeOfList)to get the count of lineitems or else using loop step ($iteration) param you can get the count (1,2,3,4…) "

Can you give me the steps how I can use either one of them? I will try to use one of them.

Case2:
Its an function in an package. I want to call/assign to the field. Can I do that. If so what are the steps.

Case3:
Yes I am trying to map the canonical document to the interface tables:

1. CE_STATEMENT_HEADERS_INT_ALL
2. CE_STATEMENT_LINES_INTERFACE

since the child is linked to parent I have to use the parent level records to child mapped to child. How can I do that?

New Case4:

Suppose say I am getting transaction code value as “010” from flat file and before I insert into the interface table I have to query it from the Oracle Applications Database cash management table ce_transaction_codes table and get appropriate description for that and insert it into interface table. How can I do that.
No problem I will write the query.

My problem is how do I grab the value from the file and associate this value to the query I have written and get the appropriate description for that value from the query and insert the into the interface table?
Any idea.?

Thanks RMG/HTH in advance.
Priyatham Porika

Priyatham,

I will clarify my best to help you,

Case1:Just map the lineitem recordlist to sizeOfList service input it gives the size of the list.or else put a loop step inarry on the lineitem record and put a map step inside the loop you will see the ($iteration)variable in the pipeline.

Case2:In the JDBC Adapter customSQL template just call that function as a SQL query and pass the input as FND_PROFILE.VALUE(?) (where ? is the Input Field Type will be string name “Org_Id”

Case3:Since child is linked to parent better way is to map that particular field values to another temp string variable and map it to interface tables.

Case4:Use the JDBC Adapter CustomSQL template as i mentioned above and create a SQL query according and provide the transaction code value (010) as input argument and map this input from source flatfile or canonical document and retrieve the corresponding value.The next step will be inserting this code in the Interfacetable as per your requirement.

Sorry if my comments are too confusing.

HTH,
RMG

Hai Priyatham,

I have done this integration and i have the code as per my practilal knowlege the dictionary and schma dosent solve the problem becasue we have to check the FileHeader(01), GRoupHeader(02), AccountHeader(03), DetailLines(16), AccountTrailer(49), GroupTrailer(98), File Trailer(99), and we also have the Continuation lines(88) comming for Account Header(03) and DetailLines(16) so inthis case all the continous lies should be added so what i have done is i have take entier data as single and process it and it is very comples to explain here so can u give your personal id so iwill send you the my sample code but one thing i like to tell you is while inserting the Header and Detail lines try to use XA-Connection adapter because we have to start the explic trans like Start,commit,Rollback trans and this connection supports the parent and child realtion ship while trans are going please format the data by the 01,02,03,16,49,98,99 dont go by schma becasuse we the every field should be checked even the “,” commma positions also so try write another service for formating the data and dont forget to attach the 88 lines for the preceding lines which it is comming i think you will get it for 03 and 16 soo add the entier 88 line.