ERP BI - Case Study 2 - Create Fixed position based BI report

Case Study: 

we will create a fixed position based eText template(RTF) to generate BI report. The columns will be as below:

1. Start with hardcoded value "NEW"   which is of 3 chars.
2. Next a whitespace.
3. Next vendor number , size 15 chars,  if less size, will be padded with whitespace.
4. Next invoice Number, size 30 chars, all in caps, if less size , will be padded with whitespace
5. Next invoice date , in YYYYMMDD, size 8 chars.
6. Next a whitespace
7. Next invoice amount , size 15 chars, if less size, will be padded "0" before the original digits.
8. Next a whitespace
9. Next Invoice Currency code, size 3 chars.

SQL Query:

SELECT PS.segment1 VENDOR_NUM

,AVA.invoice_num INVOICE_NUM

,AVA.invoice_date INVOICE_DATE

,AVA.invoice_amount INVOICE_AMOUNT

,AVA.invoice_currency_code INVOICE_CURRENCY_CODE

FROM ap_invoices_all AVA

,poz_suppliers PS

WHERE AVA.vendor_id = PS.vendor_id

EFT template or layout:

https://docs.google.com/document/d/1HzqeSCsFBc8BO9m3bmGsaBXJqPul-a9n/edit?usp=drivesdk&ouid=105651791254983245041&rtpof=true&sd=true

Detailed screenshots:

Or

Notes:

1. All SQL functions can be applied in data like TO_CHAR, TO_NUMBER, MAX, MIN, NVL, DECODE, UPPER, LOWER, COUNT, CELL, ROUND, REPLACE ETC.
2. Etext template can also be used for character delimited files as well.
3. Payment files, bank statements are created using eText templates.