on 08-24-2012 10:10 AM
Hello every one,
I have a new interface. The requirement is i have to convert IDoc(Invoice) to CSV file.
The target feilds are as mentioned below
Invoice Number,Invoice Date,PO Number,Invoice Amount,Invoice Description,Line Type,Line Amount,Line Description,PO Line Number,PO Shipment Number,Quantity Invoiced,Unit Price,UOM
114187750,08-Oct-2012,3846806,15820.23,Invoice 114187750 for PO 3846806,ITEM,90,,7,1,5,18,Each
I know the above can be achived easiely usin FCC. But there is slight complexity in it, i.e.. If an invoice has more than one line item, then each line item is represented by a line or record in the CSV file.
For example i will show the format below
Invoice Number,Invoice Date,PO Number,Invoice Amount,Invoice Description,Line Type,Line Amount,Line Description,PO Line Number,PO Shipment Number,Quantity Invoiced,Unit Price,UOM
114187750,08-Oct-2012,3846806,15820.23,Invoice 114187750 for PO 3846806,ITEM,90,,7,1,5,18,Each
114187750,08-Oct-2012,3846806,15820.23,Invoice 114187750 for PO 3846806,ITEM,45,,10,1,3,15,Each
114187750,08-Oct-2012,3846806,15820.23,Invoice 114187750 for PO 3846806,ITEM,21.02,,11,1,1,21.02,Each
114187750,08-Oct-2012,3846806,15820.23,Invoice 114187750 for PO 3846806,ITEM,340,,16,1,1,340,Each
114187750,08-Oct-2012,3846806,15820.23,Invoice 114187750 for PO 3846806,ITEM,232.23,,24,1,1,232.23,Each
114187750,08-Oct-2012,3846806,15820.23,Invoice 114187750 for PO 3846806,ITEM,145.6,,25,1,1,145.6,Each
114187750,08-Oct-2012,3846806,15820.23,Invoice 114187750 for PO 3846806,ITEM,90,,26,1,2,45,Each
114187750,08-Oct-2012,3846806,15820.23,Invoice 114187750 for PO 3846806,ITEM,4935,,27,1,1,4935,Each
114187750,08-Oct-2012,3846806,15820.23,Invoice 114187750 for PO 3846806,ITEM,900,,28,1,1,900,Each
114187750,08-Oct-2012,3846806,15820.23,Invoice 114187750 for PO 3846806,ITEM,1785,,29,1,1,1785,Each
114187750,08-Oct-2012,3846806,15820.23,Invoice 114187750 for PO 3846806,ITEM,3900,,30,1,1,3900,Each
114187750,08-Oct-2012,3846806,15820.23,Invoice 114187750 for PO 3846806,ITEM,136.5,,31,1,1,136.5,Each
114187750,08-Oct-2012,3846806,15820.23,Invoice 114187750 for PO 3846806,FREIGHT,148.79,Delivery 500230778,,1,1,148.79,Each
114187750,08-Oct-2012,3846806,15820.23,Invoice 114187750 for PO 3846806,ITEM,195.04,,21,1,8,24.38,Each
114187750,08-Oct-2012,3846806,15820.23,Invoice 114187750 for PO 3846806,FREIGHT,4.48,Delivery 500235191,,1,1,4.48,Each
I just wanted to know if i can accomlish the above.
Regards
Swetha
Hi Swetha,
This should be possible, if you bear in mind two important things:
1. FCC will not be capable of duplicating the header line after each item, so you will need to take care of it in the mapping step. Your mapping result should be something like that:
<Entry>
<Header/> //Document 1
<Item/> //Item 1 of Document 1
</Entry>
<Entry>
<Header/> //Document 1
<Item/> //Item 2 of Document 1
</Entry>
...
2. Use the Nodeception concept suggested by in his blog , to achieve the final structure that can be processed by FCC:
<Header/> //Document 1
<Item/> //Item 1 of Document 1
<Header/> //Document 1
<Item/> //Item 2 of Document 1
...
Then, you can use FCC as normally, to convert the data to flat file.
Regards,
Greg
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Dear Swetha,
I think I might have understood your requirement wrong initially. You want just one file line for each invoice line, don't you? If so, you can handle the key part of your requirement in (graphical) mapping. Your receiver structure should depict the required file structure:
Invoice Number,Invoice Date,PO Number,Invoice Amount,Invoice Description,Line Type,Line Amount,Line Description,PO Line Number,PO Shipment Number,Quantity Invoiced,Unit Price,UOM
Then, in mapping, create one outbound node for each invoice item, and use the useOneAsMany mapping function to repeat the invoice header data on each item. Finally, use the following set of FCC attributes to convert the output to flat data:
NameA.fieldSeparator = ,
NameA.endSeparator = 'nl'
Best regards,
Greg
Thanks for the help Greg,
You are right, i will try the above approach and let you know the progress. Sorry about posting another thread. I am reallty new to SAP PI and this is my second interface only. And worse part is i am on my own ............a bit challenging though.
Thanks again very much for your help. I mean it.
Hi Greg
I needed a bit more clear assistance. I have created a target DT as shown below
Record
InvoiceNumber
InvoiceDate
PONumber
InvoiceAmount,
InvoiceDescription
LineType
LineAmount
LineDescription
POLineNumber
POShipmentNumber
QuantityInvoiced
UnitPrice
UOM
My Map spec is as shown below
MF | E1EDK02-BELNR when QUAL="009" | COLUMN "A" | |
MF | E1EDK02 - DATUM when QUAL = "009" | Use MM/DD/YYY format | COLUMN "B" |
MF | E1EDP02-BELNR when QUALF="001" | COLUMN "C" | |
MF | E1EDS01-SUMME when QUALF = "011" | COLUMN "D" | |
MF | Set [INV_NO]=E1EDK02-BELNR when QUAL="009", Set [PO_NO]=E1EDK02-BELNR when QUAL="001" | Map "Invoice "+[INV_NO]+" for PO "+[PO_NO] | COLUMN "E" |
MF | E1EDP19-IDTNR when QUAL="002" | IF E1EDP19-IDTNR = "FREIGHT CHARGES", Map "FREIGHT", ELSE Map "ITEM" | COLUMN "F" |
MF | E1EDP26-BETRG when Qual ="003" | COLUMN "G" | |
MF | Set [DELY_NO]=E1EDP02-BELNR when QUAL="016", Set [ITM_FLG]=TRUE IF E1EDP19-IDTNR [QUAL="002"] = "FREIGHT CHARGES" ELSE FALSE | IF [ITM_FLG]=TRUE, Map "Delivery "+[DELY_NO] | COLUMN "H" |
MF | E1EDP02-ZEILE when QUALF= "001" | Map IF [ITM_FLG] = FALSE | COLUMN "I" |
C | Map "1" | COLUMN "J" | |
MF | E1EDP01-MENGE | COLUMN "K" | |
MF | E1EDP26-BETRG when Qual ="001" | COLUMN "L" | |
MF | E1EDP01-MENEE | IF E1EDP01-MENEE="FT" Map "Foot" ELSE Map "Each" | COLUMN "M" |
Now where do i use the Use one as many function................I am really confused, please can any one help me.
Regards
In general, you use useOneAsMany whenever you need to replicate a segment, for instance from header to items. So all E1EDP* source fields can be mapped more or less 1:1, but for E1EDK* and E1EDS* segments, you need useOneAsMany.
If you need a review of basic graphical mapping concepts of queues and contexts, have a look at my blog , I hope it will make life easier for you .
Regards,
Greg
User | Count |
---|---|
71 | |
26 | |
10 | |
9 | |
7 | |
6 | |
4 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.