UBL Invoice

[D2C]

Hello,

I am trying to create an UBL invoice from my invoices in VFP.
I do not want to create an XML directly from a cursor but be able to define my own XML file.
What is the best approach here?

Example of how the result should look like:

<Invoice>
<cbc:UBLVersionID>2.0</cbc:UBLVersionID>
<cbc:CustomizationID>urn:oasis:names:specification:ubl:xpath:Invoice-2.0:sbs-1.0-draft</cbc:CustomizationID><cbc:ProfileID>bpid:urn:oasis:names:draft:bpss:ubl-2-sbs-invoice-notification-draft</cbc:ProfileID>
<cbc:ID>A00095678</cbc:ID>
<cbc:CopyIndicator>false</cbc:CopyIndicator>
<cbc:UUID>E081-40B4-906C-94C5FF9D1AC3</cbc:UUID>
<cbc:IssueDate>2005-06-21</cbc:IssueDate>
<cbc:InvoiceTypeCode>SalesInvoice</cbc:InvoiceTypeCode>
<cbc:Note>sample</cbc:Note>

some values within the tags need to be filled in from invoice data.

thanks,
d2c

 

[Olaf Doschke]

In short: Use Textmerge within TEXT/ENDTEXT blocks or - if working with template files instead of TEXT..ENDTEXT codeblocks, use the Textmerge() function.

example:

save this to ublinvoicetemplate.xml:
[pre]<Invoice>
<cbc:UBLVersionID>2.0</cbc:UBLVersionID>
<cbc:CustomizationID>urnasis:names:specification:ubl:xpath:Invoice-2.0:sbs-1.0-draft</cbc:CustomizationID><cbcrofileID>bpid:urnasis:names:draft:bpss:ubl-2-sbs-invoice-notification-draft</cbcrofileID>
<cbc:ID>[highlight #FCE94F]<<ID>>[/highlight]</cbc:ID>
</Invoice>[/pre]

Create Cursor crsInvoices([highlight #FCE94F]ID[/highlight] C(9))
Insert into crsInvoices Values ("A00095678")
Insert into crsInvoices Values ("B00095888")
Insert into crsInvoices Values ("A00095679")
Insert into crsInvoices Values ("C00001678")

Local lcTemplate, lcInvoice
lcTemplate = FileToStr("ublinvoicetemplate.xml")

Select crsInvoices
Scan
   lcInvoice = Textmerge(lcTemplate)
   StrToFile(lcvoice,Textmerge("ublinvoice[highlight #FCE94F]<<id>>[/highlight].xml"))
Endscan

So here Textmerge is used to put the ID field value into the template and also into the file name.

Your outset should be having all necessary data in tables, all you need is a query into cursor to put all necessary data together, then put all your field names into the templat within the textmerge delimiters << and >>, to convert dates to certain format, you can use any VFP expression with functions like DTOC(), using field names puts their value into the string as the ? command prints them to the _screen, so many things will only need the field name.

Bye, Olaf.

 

[Olaf Doschke]

Another idea: Instead of template XML files you could also put textmerge templates, text with textmerge placeholders, into memo fields. Since this is merely text you don't need special fields.

Take note - if the xml result should be UTF-8 you use STRCONV() to put in UTF-8, then it's also good to use Memo with no codepage to put in UTF-8 strings, which don't get autoconverted between what is stored and ANSI 1252 or files with UTF8. Better use Notepad++ instead of Microsofts Windows notepad, which adds a few binary bytes as BOM to mark a file as UTF-8, Notepad++ does that without such a BOM and identifies the codepage by inferring it from the content or by you specifying it. The whole codepage/encoding has to be handled with a little care, even though the normal data within XML is merely making use of the latin alphabet, digits and some more characters, which all are part of ASCII, many ANSI codepages and UTF-8 anyway, but the first time you have some french accent or german umlaut that'll cause a parse error at the side of the receiver of the invoice xml files, if not put in correctly.

Bye, Olaf.

 

[D2C]

Thank you very much,

what about sections that have repeating items, like articles?
should I make a template for that and repeat it? How is this done?

thanks in advance,
best regards,

d2c

 

[Olaf Doschke]

Yes, you would have a nested loop on detail data and add to the xml. Once you understood the textmerge concept, how hard is it to apply to any situation?

select maindata
scan && invoices
  lcItems=""
  select detaildata
  scan && items within invoices
     lcItems = lcItems + textmerge(itemtemplate)
  endscan
  lcXML = textmerge(maintemplate) && maintemplate referring lcItems at one place
endscan

You put in one placeholder for the items list in the main template:

[pre]<Invoice>
<cbc:UBLVersionID>2.0</cbc:UBLVersionID>
<cbc:CustomizationID>urnasis:names:specification:ubl:xpath:Invoice-2.0:sbs-1.0-draft</cbc:CustomizationID><cbcrofileID>bpid:urnasis:names:draft:bpss:ubl-2-sbs-invoice-notification-draft</cbcrofileID>
<cbc:ID><<ID>></cbc:ID>
[highlight #FCE94F]<<lcItems>>[/highlight]
</Invoice>[/pre]

So you would finally do lcXML = Textmerge(lcTemplate) and that would merge in the items you prepare in advance in lcItems.

You can go about this as you like and as you can. The detail problem of merging data into the xml text is solved, is it really that hard to apply it to whatever needs you have? Chop your XML into its major parts to have subtemplates like an itemtemplate and process these templates from inside to outside. You don't have to build up the xml byte by byte from top to bottom, you don't do the typical XMLserialisation acting as a stream of output. You rather create your building blocks to finally put together as the whole thing.

Bye, Olaf.