7 | THAT’S A PRINT
Engaging a team of software developers requires expertise, patience, and communication between the development team and team that owns and understands needs of the business. There are times that business can operate well with commercial software applications. Some businesses buy multiple tools. At some moment, the leaders within a company acknowledge that their internal or external business workflows are inefficient, or inaccurate, or incompatible with their other technology.
Is there something about your business that sets you apart from the other businesses? What is your competitive advantage? What are the messages you are sending to your clients? Invoicing represents a classic example of this topic.
What is an invoice?
First definition: It is a document that communicates what a client owes a vendor.
Second definition: It is a document that communicates.
Third definition: It is a document.
During this episode we will explore how an Oracle database can produce a document and we will explore some of the ways that organizations use and design invoices. We have pharmacies in the United States printing receipts that span nearly two meters when a customer buys a single tube of toothpaste. Someone made a decision about that design. It is a document. It communicates. And it informs the consumer about their purchase. Three boxes checked. But what does it communicate? What does it say about the pharmacy?
Our client, Electro-test, desires to speak in their customer’s language. They wish to reinforce strong corporate branding. Electro-test is making decisions about invoicing. What does their decisions say about Electro-Test.
Let’s explore this topic now.
Databases, such as Oracle, rarely have the ability to produce stunning reports enriched with graphic images, font selections, and color – on their own. In modern, cloud-based software databases face challenges knowing anything about the printers and peripherals that a user may have. The database located in the middle of the internet while the printers, like the desktop computer, tablet, or mobile phone sit at the edge of the cloud. In short, classic database engines cannot create reports – yet developers like me have been doing it for decades. How?
The key reason that database management and the creation of reports and document remain isolated from each other is that they have required different engines, different machines. What does a database do? It stores and retrieves data amazingly well. When design properly good database engines hide securely behind layers of protection from the public internet. We need database not to share their secrets easily as anyone who faced a stolen credit card or identity knows.
What does a database do and do exceedingly well? It stores organized data. Within, these data land within tables, within tables into records and fields (rows and columns). Speed is achieved with five-plus decades of optimization using indices and constraints on the data to ensure referential integrity. The indices improve the ability for a database engine to find an element of data within. Databases, such as Oracle, offer specialized coding and querying languages.
As discussed in prior episodes, spanning the internet to present information on a web browser, the core database requires additional tools. For me and our team, we employ APEX as means to present data to users via applications. Oracle APEX includes tools that create HTML based webpages while integrating CSS to format webpages – bringing in customized colors, fonts, spacing and layouts.
We seek analogous tools for printing. For we must span not only the distance of the internet to deliver a report or document to a printer, but we must also endeavor to have a database comprehend the limits of a simple piece of paper. What does know about the simple piece of paper? What tools do we have at hand to prepare documents with fonts, colors, graphics while controlling the margins and layout? The answer appears on our desktops within Microsoft Office and within Open Office. Each suite of software includes word processors, spreadsheets, and presentations. In the Office suite, we refer to these tools as “Word”, “Excel”, and “PowerPoint”. In the OpenOffice suite, we refer to these tools as "Writer”, “Calc”, and “Impress”.
To each their strength. Office tools such as Word and Writer excel at their abilities to format a printed document. We can drag-and-drop graphical elements such as logos and pictures. We rely on these tools to adjust the paper size, the margins, the fonts, the colors. In these documents, we can set up headers and footers. You know this.
You can not ask Microsoft Word to properly query data from an Oracle database? I accept that is it technically possible using advanced tools. It is not a strength or an innate feature of Word to query databases.
To illustrate the gap, I’ll digress with a totally stupid joke. Here goes: “How do you get down off of an elephant?” The answer is: “You don’t, you get down off of a duck.” For those in my audience with less comfort in English (this podcast has listeners in over twenty five countries). The downy feathers of a duck or goose are used for pillows and duvets. Elephants don’t have down.
Why do I tell this old joke? It illustrates that we can make poor assumptions. We often ask the wrong question or ask the questions in a wrong manner.
How do you print a gorgeous multi-color invoice in Dutch and French from a database? The answer is: You don’t. Oracle databases don’t have colors and fonts nor any knowledge of printers. Just like elephants don’t have down, the Oracle database doesn’t know how to print.
But our friends at United Codes do. They have a product called APEX Office print which we call AOP. With AOP, we blend the power of a database with the formatting of Microsoft Word with the strength of the portable document format (PDF).
To each their own strengths. This is where middleware fits in. Where does middleware go? In the middle. Middleware is the software-based infrastructure of applications. Middleware bridges the gaps that we find in the diversity of internet machines. Web browsers speak with HTML, CSS, and JavaScript. My database speaks with SQL and PL/SQL (oh yes, one of my friends informed me that I am supposed to say “sequel” instead of SQL. That is what the hip cool kids say. And I do when speaking casually and rapidly. But I have spent nearly three decades talking with audiences. The word “sequel” has multiple meaning whereas SQL is precise. Furthermore, saying SQL ties the phrase back to its real meaning of “structured query language”.)
How do we create documents from databases?
First, we use data within the Oracle database to prepare the data. We do this through queries written in SQL.
Second, we format the document with representations of text/data, the page layout, supporting graphical elements, typically with a word processor. It is possible to use any of the tools listed above: Excel, Calc, PowerPoint, or Impress.
Third, we print to a digital file commonly a PDF – but we can print to any of them.
This is a brilliant solution born from years, decades of struggles. So often during the podcast whether I write it or not, I explore my personal history of these emerging technologies. I did watch an excellent YouTube documentary this recent week about the history of BASIC. The programming language BASIC had been invented at Dartmouth College approximately on the year of my birth. First, it was cool being reminded yet again that each of these tools, these machines, were invented by human beings. They put their life’s thoughts and personality and desires into these machines. They literally invested these tools with a soul that lives on. Second, the story merged and mixed two themes. Dartmouth College also invented the time-sharing technology that forms the bedrock of all cloud-based computing. Dartmouth College build a computing center on the campus then strove to get undergraduate students involved with computers. The documentary illustrated how students of nearly every discipline were encouraged to use computers. Dartmouth had one computer. It was small, for the day. Part of the backstory was that a professor would take the train weekly from Hanover New Hampshire to Boston Massachusetts with a stack of cards. Once in Boston, he would take the subway to MIT, run the code that had carried on the cards. The code would run, he would then train back to Hanover with the results. In my day (decades later), we referred to sneaker-net as a means of walking documents across a room to print or share. The gang at Dartmouth wrote an operating system that permitted jobs to run through a computer while sharing the processor. Everyone got small slices of time at the CPU. A little job might run right through. A big job might hit the CPU a few times before finishing.
Listening and familiar with the technology, I saw the connection todays Oracle’s Cloud-based offerings and Amazon’s AWS as well as others.
Oracle database jobs when running on cloud-based servers (in fact any welldesigned server) do the same thing today. Sure, the code changed and matured. The operating systems grew up.
Watching this video, I connected in so many ways to this story. About 10 to 15 years after BASIC was invented, I sat at a terminal at my high school writing code in BASIC.
The echoes of that ancient technology ring in our daily work today. The personalities, decisions, assumptions, and effort that the pioneers invested in BASIC and time-sharing impact our lives now.
Before we recognized that the process of laying out database documents in a word process was smarter, faster, better we all attempted to design database documents from other tools. Commonly, we measured little boxes or counted distance in pica or millimeters. We placed data field on a grid just like a kid doing math homework on graph paper. The work was tedious and when anything changed, the whole thing changed . If the paper size changed from European A4 to American Letter, one often started fresh.
To each their strengths. While we still occasionally use millimeter precision laying out a form, we do it when needed only. For example, with the client Electro-Test, we must accommodate a QR code that is added after-the-fact to invoices. We must make sure that the mailing address on an invoice is visible through the window in the envelope – millimeter precision.
When using AOP to design reports, we create a template in Word (or any other listed tools). We lay it out by defining page size, margins, fonts, graphics. In our templating tool, we place any of that static or stable items. These static items are the text and graphics that are not generated by the database. There are a few exceptions, of course! I’ll return to this in a bit.
Within the templating tool, we need to represent where the database data goes. The gang at United Codes (Dimitri and Sunil and others) opted to use squiggle brackets to offset the field names. It is not common for writers to offset a word or phrase within a pair of squiggle brackets. By selecting this notation, AOP uses a technique that will not accidently be used within Word. Furthermore, this technique is similar to the way that other people identify field when doing data merges in Word say for mailing labels and envelops. If you want the customer name to appear, you type left squiggle-bracket then customer_name then a right squiggle bracket.
Job One is setting up the template.
The next job is setting up the data.
Oracle, and other databases, store their data in complicated and proprietary data structures that are optimized for performance. Therefore, the data are stored in separate tables. Therefore, we need a means of querying a subset of the data then presenting that data in a format that is commonly used on the internet. Any guesses? If you guessed JSON as a data format, you were exactly right. Invoice data involves at least two tables. There is the invoice header and the invoice detail. The header data includes the bill-to, addresses, the due date, the total amount owed. The invoice detail documents each line within the invoice: service provided, description, quantity, amount per line. It is more granular. One invoice, or invoice header (same thing), has multiple invoice lines.
The power of JSON is that we can communicate this parent-child relationship over the internet easily.
We create a single query using either APEX or SQL that creates the JSON data structure for an invoice. Given we defined AOP as middleware and not controlled directly by the user, we previously setup AOP to communicate securely with our database server. We have password and the data are encrypted in this middleware link.
The third job is using the AOP services on the Cloud or on your own personal servers to merge the data set with the template. The document returned is formatted and in the styled you want: PDF, Word, PowerPoint, etc.
We all love it when we can identify a process with the phrase “As simple as 1-2-3”. In this case, it can be true.
One – create a template.
Two – create an SQL query that generates JSON data with the information needed from the database.
Three – send both to AOP and get back a stunning document.
While simple is wonderful, excellence appears when you can push simple up the spectrum all the way to complicated.
For Electro-Test, we dialed the complexity knob all the way to eleven – a reference to the movie Spinal Tap.
My colleague Stephanie Dickerson, a friend we all call Stevie, used conditional statements that change the template. We need one of four different graphics in the footer. Electro-test has two divisions. One division performs inspections that must meeting regulatory standards. One division performs health-and-safety inspections for commercial environments. So that is two different “companies” or divisions. Each has its own graphic and address. The footers must match the customer’s preferred language: Dutch or French. Two divisions, two languages – that results in four options: Division 1 (VZW) in French. Division 1 (VZW) in Dutch. Division 2 (BV) in French. Division 2 (BV) in Dutch.
She does the same with the four versions of the graphics that sit at the top of each page.
Then the column headings that are needed and the other text are each done with conditional statements based on the customers preferred language. She uses conditional statements to determine if the invoice is an invoice or a credit note. And if the invoice is prepaid, then the payment instructions change. You do not need to pay an invoice that has been paid.
I would have taken a very different approach, but Stevie’s approach is more efficient for our team of developers. While the Word document, the template, can be difficult to read and change, the beauty of the technique is that we have exactly one template to use anywhere we need to be.
Had I done this I might have used as many as four templates. I might have landed on eight templates. My technique would have resulted in more complexity during upgrades and changes to the code. Instead of making the changes once, I would have made them four, six, or eight times. I would have had simple and easy to manage templates while creating more complexity in selecting the template – likely more code would be needed. And upgrading with multiple templates takes more steps and involves more risk.
Complex things are complex.
Sometimes we get to pick where we face the complexity. In this case, Stevie opted to face the complexity in the template. By creating one template that serves so many purposes, she must do some funny stuff to make it happen. She accepts the ‘cost’ of that work within the template. She showed me recently that she takes difficult sections out of her template and works them in a new Word document, then copies them back.
Had I done the work, I may have simplified the template layout process thereby pushing the complexity into managing multiple templates.
We’re not avoiding the complexity and neither option is more correct. We made different decisions about how to face that complexity.
Regardless of the solutions, we must present both Invoice and Credit Notes for two divisions in one of two languages. That is three sets of two decisions – or two to the third power. At a minimum there are eight conditions to meet. Furthermore, invoices can be prepaid or not yet paid. That would be four sets of binary decisions or two to the fourth power which is sixteen. There are sixteen possible templates. Or one template that creates great results for sixteen different conditions.
Our team bangs on ideas as if ideas were made of tin. We shape process and ideas into structured workflows. We solve problems: two divisions, two languages, two document types, either paid nor not-yet-paid. Our goal is to create perfect invoices.
What is a perfect invoice? Do you know? I am still discovering it after thirty-plus years. My friend Ron, the original face of an audience member for this show, illustrated that an invoice is marketing. He said: “I must prove the value of our services to our client with every invoice.” So true and yet, ten years ago this thought was a new to me.
This client, Electro-Test, shows me that we must “speak to client in their language.” Yes, another overlooked truth.
An invoice must be accurate: always accurate, perfectly accurate, transparently accurate. An invoice reinforces the trust between a client and a business. I heard my husband this week on the phone with the local propane gas company. They filled both of our tanks this week. The vendor invoiced the gas at two different rates for the two tanks. The two tanks sit next to each other behind the house and surrounded by our 40 hectare (100 acre) property. My husband asked: Which rate is right?
Well, I knew the answer.
Both rates are right. Both rates came from the propane company’s software, therefore both rates are right. Both invoices are right.
To maintain trust, of course the propane company adjusted the rates so that they matched.
Have you ever gotten in a fuss with a client? The issues may be trust, missed deadlines, missed requirements, or any of normal human experiences that caused friction. The most common battle zone centers on the humble invoice – and trust. When that fuss starts, out comes the invoice. What have you done for me and how much am I paying for it. This is a universal experience.
Your firm provides a product or a service. You invoice for that service. That invoice ought to inform both parties about the nature of the relationship between client and vendor. How many of you are Amazon customers? I get random invoices – frankly not even invoices – notices that they took money. Sometime during the month, they bill me fifteen dollars for something. I don’t know why. It takes a lot of clicks to find answers. They don’t honestly want me to research their invoices. Amazon and others use invoices to obfuscate and confuse their clients. Other companies use invoices to further leverage their clients. Several of the big box stores in the United States create massively long receipts (which really are invoices). They include discount coupons. They have awards programs. They want your email address for some lottery or notification. The Home Depot always wants me to save paper, help the environment, and enroll in a paperless invoice. Which means providing them my email address. No. Many of these firms wish to further monetize our transaction. They are not transparent about that. They are willing to pay you for your personal information with a discount or a coupon.
I don’t trust that. The printed receipts from U.S. pharmacies have become a bit of a meme. People show that they bought a tube of toothpaste then show that their receipt is not only longer than the tube of toothpaste, but that the receipt weights more than the tube of toothpaste and that the receipt may span for nearly two meters, from the purchaser’s head to the concrete below their feet. I don’t want my work to be a meme. Additionally, I don’t want to be a data element, thank you.
In summary, some companies use the receipt or invoice process in ways that I disagree with.
The invoice ought to be a professional handshake. Each party entitled to the legal concept of consideration – a fair exchange that benefited both. Yes, I have an option. Nothing should be hidden. If you require my email address, then tell me why and what you will do with that information. Use plain language. Unless you do, I assume that you will sell my demographics, my age, my name, my email, my purchases to someone else. Europe has fewer of these shenanigans than the United States. The E.U.’s General Data Protection Regulation attempt to shine a torch on this stuff.
The vendor gets to say: “I did this for you.” When listening to my friend Ron, he employed his invoice to prove value. “You benefited from this service thus…” Each description focused on how his firm improved their client’s position. Given that Ron’s firm helped American business with compliance, then each description linked the billable activity with improved regulatory compliance. Well done.
Have I wandered too far from printing?
No, because printing in the business software results in invoices and other forms or reports. One client may instruct the developers to create a brilliantly clear invoice that is visually attractive in stunning colors and written in the language of the recipient, like our client Electro-Test. That’s complicated. Our development team learned that street names in Belgium are often done differently in French and Dutch. Not only do we need the service description to be in the recipient’s language of either French or Dutch. we must also match the spelling of the address. We must match Electro-tests branded red and grey. We must match their corporate fonts. And more, which I will get to. Additionally, our spacing had to be precise to the millimeter.
As software developers we do execute unpleasant tasks with professionalism. If I had a client that required one of those deliberately complicated and lengthy invoices, I would do it because I like paying my mortgage. C’est le vie. I did once develop software for a company that ask for one of these less-than-honorable tasks. This firm is or was the powerhouse in wholesale food distribution. This was fifteen years ago in the late 2000s. It was about the only job I could find after returning from a year in Iraq. I landed back in the United States just as the 2008 market crunch hit. I would have sat at the street corner shaking a tin can saying: Will code for mortgage money (frankly, still true).
For this firm, I wrote code in Oracle that deliberately screwed small business owners. We used software to scan paper invoices or interpret digital invoices for their critical information. The software read the due date and the amount due. When ready to pay, the software queued their check to be printed on the last minute of the last business hour on the due date. The code used the vendor’s postal code to calculate which corporate bank account was the greatest distance from the vendor. We then printed a paper check on that bank account which we mailed to the vendor. The vendor would have to deposit the paper check, then wait for the funds to clear. I wrote this nasty bit of software just at the waning days of banks older style practices. The added distance added days to the processing time for the money. This duration is called “float”. The owner of this privately held company made as much in interest on the float as he did from the meager mark-up on the products. The financial incentives fell apart when interest rates dropped and when banks made modest improvement in the electronic movement of funds. I don’t know if they are still running this code fifteen years later. They may be. Some young coders likely encountered that nasty logic involving postal codes and finding the most remote bank. That coder looked at that crap and said: “I am not touching that.”
Let me pause to say thank you. Writing articles such as this is difficult when there are no graphics. It prevents me from getting super technical. If you add pictures to a podcast it is called a video. You’ve granted me access to your earholes and I thank you. There are folks like you listening to this show from over 25 countries. You, my dear listener, took me for a walk, or a drive, or you are getting some exercise. Maybe you are making something? Thank you for taking me along, I appreciate it. Bedankt, danke, tank, merci, gracias, grazie, shukran, shukriyaa, maholo, assanti, quyana. If you enjoy this, let me know or pass on the links to others.
Returning to invoices…Classically, invoice data have two constituents. The invoice header, printing at the head, or top, of the document identifies who is who. It identifies who the customer is, how much they owe, the due date, and other instructions. The invoice detail contains the line items for each good or service purchased. It typically has a product, or service, code, the quantity purchased, the unit price, the extended price. You know this; you’ve seen enough invoices in your life. In the database, we tend to use two tables for these data. One table, often called the invoice header or just invoice contains the bill-to and remit data, plus the amount owed, due date, taxes, and such. The second table, often called invoice lines or invoice details is a child table to the header. It contains one row of data for each item or service invoiced.
Every once in a while, we use a third table. Some clients have wanted invoices to show lines grouped together. When we group lines together, we occasionally add a third table to store grouping data.
When writing database applications for clients, we hear the phrase “yeah-but” a lot. “Yeah-but” and “What if” help us discover the extremes of what is required. The core remains consistent. The core remains modelled after either a legacy or a manual process. Grouping and subtotals, complicated tax calculations, and the like. These are flourishes that promote the invoice to a hand-built craft. The complexity and specialization transforms the process from an off-the-shelf or store-bought process to a bespoke process.
Remember that high school physics lesson about light: particle versus wave? The trick to this game is remembering both matter. We must see both: the flourishes plus the core requirements. We can’t go so far into the extremes of customizing an invoice people do not instantly recognize the document as an invoice. It must be clean, accurate, and legible. On the other hand, a boring presentation of columns and rows also fails. We must honor the core principles whilst engaging creative energy to bring the document to a useful life.
In general, the biographical information about an invoice: who, how much, and when due sits in the invoice header table. The specifics about each task or item, gets stored in an invoice detail table.
Do we care how many lines sit within an invoice? No. We followed the data normalization guidelines to reduce data redundancy and improve efficiency. One line or one million lines, the database does not care. Wait, can an invoice have no lines? Ummm… let’s say no. Then of course, we may hear the phrase “yeah but” or the phrase “what if”. Then, sure, an invoice with zero detail lines is a flourish, an exception. Cool. We can handle exceptions. I don’t know why an invoice would have zero lines, but it can.
We need to hand these invoice data over to AOP so that it can assemble a document. AOP is over there (I am pointing in some random direction). AOP is also on the Cloud, or maybe you have your own AOP server. The AOP server or AOP services are external to the database server. Uh oh? I am wandering in the land of middleware again, aren’t I.
Middleware is software that is infrastructure for other software. AOP is a type of middleware. It is typically not touched directly by users. We hand data to it and get results back. It is a black box that does magic stuff to our data while creating a PDF (or Word document, or Excel or Power Point or others).
In our hand off of data to AOP, we must communicate the data in a standard format. Once again, the industry selected JSON as a common method for passing data through middleware. JSON is a text file or a block of text that contains the data. The term JSON, spelt J-S-O-N, is the JavaScript Object Notation format. We use squiggle brackets or curly braces to divide up the data in a neat manner. The JSON data format is more modern, resilient, and flexible tha n CSV and XML. XML, the extensible markup language, is also a file format for storing data. XML has been around for decades and serves the internet as a means of storing and transmitting data. While Oracle can query and manipulate XML data, some XML data present challenges to Oracle. The compatibility with JSON is a bit neater and cleaner.
While in Oracle, the invoice data had been stored in two tables; one for the header data and one for the lines describing the details of each transaction. Harkening back to a prior episode, we would see this as a parent-child relationship between header and lines. The data, when presented in JSON is a single file. We fold the data in upon itself thereby nesting layers of complexity into the data. We can then have customer data, invoice header data, invoice line data transmitted easily and without much redundancy. Data stored in native Oracle data tables gain efficiency by having hidden and proprietary means of storing to optimize retrieval and searching. Oracle’s efficiency derives also from being constructed within the fabric of a relational database. Early architectural and technical decisions with relational databases acknowledge their future need of storing the entire stock exchange and all airline reservations. Oracle data table lack portability. JSON offers portability to these data. JSON can be read by humans and easily read by software. We are able to include images within the JSON data format as well. We convert the images to a text-formatted jumble of numbers and letters using a Base64 encoding algorithm.
With Oracle, we query the invoice data preparing a JSON dataset that may also include photographs and other images.
We completed the first step.
In the second step we need to layout the invoice with fonts, colors, lines, and corporate logos so the document looks fantastic. APEX Office Print, by United Codes, opted to use common word processing, spreadsheet, and presentation tools familiar to us all. We can use either Microsoft or OpenOffice. OpenOffice is a free version created on open standards. Developers employ the power of Word or a word-like application to give structure to the invoice document – or any other database document. We create a template. On the template, we represent where data goes by putting in the field name surrounded by squiggle brackets. While difficult to represent verbally on a podcast, you may be able to picture a Word document that has a field called customer name on the left-hand side near the margin. We wrap customer name in a pair of squiggle brackets. This technique tells AOP to substitute the word “customer name” with data that is the actual customer’s name.
While United Codes did more than just substitution, I can visualize a tool that goes through my word document replacing one thing for another. We have all be doing that for a while.
We now have two of the three ingredients to prepare the AOP document. We need to transmit the invoice template and the invoice data to the AOP engine. This AOP engine, or middleware, sits on a server that you own or maybe you use the servers owned by United Codes in the cloud. We send these two ingredients over. The engine sends us an invoice in PDF. Clearly, everything has to be done well. The data must be complete and accurate. The template must be complete and accurate. The data fields must match the field names on the template. These errors, or bugs, will either prevent the invoice from returning or generate a PDF document with errors in it. Remember the old saying: Garbage In, Garbage Out. If we feed any engine garbage such as malformed data or a poorly constructed template, then the engine will fail.
Thankfully, there are cool debugging features with AOP. They guys behind AOP, Dimitri Gielis and Sunil Tandan, detail the source of the errors. It is our job as developers to not hand off garbage, but if we do by accident, we can use AOP to help us pinpoint the issue and resolve it.
The creation of complex and gorgeous reports from a cloud-based application requires more than what a database engine can provide alone. A database has been optimized by decades of investment by people to store and retrieve data quickly, efficiently, accurately, and reliably. We bring in other tools such as AOP to bridge the span between what the database engine does and the generation of a printable document.
The trick for business leaders is to decide what message do you want your customers to hear? Eletro-Test decided to “speak to their customer’s language”. My old friend and client Ron decided to “Prove their value” demonstrating their effectiveness. Whereas the yet-unnamed US pharmacies have filled their invoices (receipts) with advertising, coupons, pleas to get your personal data, and stuff intended to pull you back. Frankly, I ignore it all pitching it in the bin instantly. To me, I hear the pharmacy say: “Don’t read this document”. Maybe that is their point. They do not want me to read it. They want to confuse us. Who knows.
People make these decisions and we developers help them implement them.
Until next time, be well and do good and have fun.