Home

User Manual

Contents

1. 24 4 5 Retrieve a report Run 26 5 ZA Practical example N 30 5L 30 5 2 Development zo u in u S Sans Gp anu Qu kanqa 32 5 2 1 Create atolder and document 32 5 22 Select dita s a sS 32 SPP RENE 32 5 2 2 2 CUSTOIMICNS 35 5 2 2 3 Items EISE sodes u eds espe Reve au 37 p 224 bue LUE 38 5 2 2 5 PO CHU Geel SIN uu dotem aS masma 40 Table of Contents Doxxy User Manual Version 01 02 iAdvise Doxxy 523 3 HOW o u uuu bag bre ned anu 41 u uw 42 52 22 44 524 EX re MUI HOM GIT u u 46 5241 PHYSOEStIEMEN 46 5242 ValiGationiOl the templates u u uu ul 47 5 2 4 3 Meta Data sul VeV uuu iu yy M pa uc E sa 48 5244 Visualisation ot thie template u u uuu Gillean eae 50 6 HBSGUIIICKS ioo etos mc Asa iesu E DIS
2. Bonam Shirt 63 50 6n Tha arzaur pl Taki Ki Your ceder har nume 3 Eger rier andi ch ou wm orin 1 Fanm bod meii Gece suas of pour Wn yeu miy your Tha Cee xum Ics r hh pmi mes hk arun rm in rer br iriri A practical example Doxxy User Manual Version 01 02 iAdvise Doxxy 5 2 DEVELOPMENT This is what the Order Confirmation sample report will look like in Doxxy DOXXY USER Home m DEMO Order Confirmation Folders a LE gt B lt h Order Confirmation order confirmation letter including an overview of the ordered Actions Preview V Sw DOXXY i et SQL PL SQL Templates Parameters gt wx o SQL Statements PL SQL Statements C Reset lt Templates i Parent Name Name Description gt 9 Parameters gt Ord B SYSTEM raers gt MY FOLDER Orders Customers Orders Items Orders Product available This paragraph will explain how we created the document 5 2 1 CREATE A FOLDER AND DOCUMENT START Create a new folder or navigate to the desired folder Create a new document name it Order Confirmation and save it in this folder 5 2 2 SELECT DATA WHAT For this report we will need data from different tables These must be selected using the appropriate SQL statements
3. m DEMO Folders f LE DEMO Doxy Folder with sample demo reports documents based on Actions Sr DOXXY gt lM APEX g Documents li Order Confirmation Q Search amp Reset gt SYSTEM x MY FOLDER Name Description Last Changed iil My Documents Order Order confirmation letter including an overview of the ordered products Example 28 02 2014 16 11 36 pre Confirmation document from the Doxxy manual WI New Folder Doxxy 01 02 00 By using the Actions button you can view and edit modify create subfolder import export delete the properties of your folder Actions Edit Folder Info Create Subfolder Import Document s Export Documents Delete Exploring Doxxy Doxxy User Manual Version 01 02 10 69 3 2 DOCUMENTS Documents can be created at all folder levels When you click on a folder that contains documents in the tree the details pertaining to these documents will be displayed on the right hand side You can exit your folders at any time by using the bread crumb navigation Home zz DOXXY DEMO Folders Lu A un ku DEMO Doxxy Folder with sample demo reports documents based Actions v DOXXY APEX Documents li Order Confirmation Q Search amp Reset gt SYSTEM v MY FOLDER Name Description Last Changed z My Documents Order Order confirmation letter including an o
4. Select a clear unique code for your SQL statement You will need to use this code in your template as reference to your statement Tip Use capital letters This will highlight the text among the default text in your document Document Area Use the selection box to position the data in your report Here you have three options Body of a document Header of a document Footer of a document Parse SOL It is still possible to check the syntax after the SQL statement has been entered by simply clicking on the Parse SQL button If this was successful the appropriate communication will be displayed SQL Statement 7 SELECT o order id c cust first name c cust first name e ecust last name cust name TO CHAR o order timestamp DD MM YYYY order date TRIM TO CHAR o order total 9996G999D00 order total FROM demo orders o JOIN demo customers c c customer id o customer id Statement succesfully parsed Copy from SQL My first report Doxxy User Manual Version 01 02 iAdvise Doxxy Copy from SQL SQL statements can be copied from any documents and folders that you have access to COPY from SQL Select Folder 2 No folder gt Select Document No document gt Select SQL 7 No SQL v SOL Example 9 Finally click on the Create button to confirm the creation of your SQL statement If you navigate back to the info page of the document
5. 5 2 2 1 ORDERS We will obviously need the data from the orders itself These are located in the DEMO ORDERS table A practical example Doxxy User Manual Version 01 02 iAdvise Doxxy We want to incorporate the ability to choose to either print one letter for an order or to print all letters for all orders in succession For this we will need a parameter namely P ORDER ID In order to use a parameter in the 501 statement we must first create it Parameters can be created by using the Parameters tab b Order Confirmation order confirmation letter including an overview of the ordered product items e Preview SOL PL SQL Templates Parameters Parameters Mo Parameters found CREATE Parameter Document Order Confirmation Display 1 Order Id x Bind Variable 7 P ORDER ID Data Number gt Default Value 1 Enter a specific order id or leave empty to retrieve all orders just for demo reasons Description 2 We can retrieve the parameters in the SQL statement by using the colon notation of the Bind Variable Name e g P ORDER ID A practical example Doxxy User Manual Version 01 02 iAdvise Doxxy Click on the SQL tab or on SQL Statements in the tree to create a new SQL statement Add the appropriate WHERE clause to control the parameter The Orders SQL statement will look like this SELECT o order id TO CHAR O Order times
6. The Customers SQL statement will look like this SELECT Couse Dome c cust first name Street 8206551 c OUust last name cust name DECODE CSCUSL SUCPOeSLt address2 NULL NUDE Street address SUSE STTOSLT C GS City ts eoust state eeCust postal Code cust city FROM demo Orders JOIN demo customers c ON o customer id c customer id WHERE o order id ORDER ID In this case the orders are the masters while the customer details are the details of a specific order As Parent SQL Statement we will therefore enter the Orders SQL statement By completing the parent statement you will see an overview of the Link Variables with all the columns from the parent statement on the right hand side These columns can be used to link or bind the detail statement with the parent statement in other words the columns that can be linked A practical example Doxxy User Manual Version 01 02 iAdvise Doxxy If you have created the Order parent statement correctly the ORDER ID column will be displayed between the Link Variables The reference to the link variable will again take place through the colon notation If done correctly the link variable will change to light blue in the overview If the link was saved a green checkmark will appear We use Usage in Document to select the desired view in this case Paragraph As Recordset Code we will
7. Usage In Document Recordset Code Document Area Body of a document RI Name Choose a clear and unique name for your SQL statement Sequence A reference number for the SQL statement it determines the order of the SQL statements in the visualization in the the Doxxy UI and processing by the Doxxy engine Description Optionally a description may be added My first report Doxxy User Manual Version 01 02 16 69 iAdvise Doxxy Parent SQL statement In the event that you want to display a master detail structure you can indicate your master SQL statement here The list box will automatically be completed with all previously created SQL statements SQL Statement Enter your statement here to retrieve all the data that you require from the database This is the data that you would want to display in your report later on Usage in Document Use the list boxes to clarify how the data should be displayed in your report You have five types of layouts to choose from Table in a table List a numbered or unnumbered bulleted list Paragraph in een paragraph as standard standalone text in a previous version of Doxxy this used to be called Global document Page Break in a paragraph where a page break is added after each record of the SELECT statement Page Reset in a paragraph where a page break is added after each record of the SELECT statement and any page numbers are reset to 1 Recordset Code
8. 7 Parameters x PL SQL Statement BEGIN INSERT INTO demo report logs document name log time VALUES Order Confirmation SYSDATE COMMIT END Parse PL SQL Copy from PLSOQL 5 2 4 2 VALIDATION OF THE TEMPLATE When you upload a template Doxxy performs a basic validation of the template structure In the following example two errors were detected 1 The END CUSTOMERS tag is missing 2 The template detects a potential error with regards to the name of the recordset linked to the SQL statement retrieving the Items that are to be used in a MS Word List ITEM LIST or ITEMS LIST A practical example Doxxy User Manual Version 01 02 wy c GE iAdvise Doxxy CREATE Template The tag is invalid for the following reason End tag not found The tag is invalid for the following reason End tag not found The tag ITEMS LIST is invalid for the following reason Begin tag not found Document 3 Order Confirmation Template 7 Template with Error Upload New Template file selected Download Template confirmation template error docx Template Format DOCX gt Description 12 5 2 4 3 META DATA SURVEY The Doxxy tool also allows you to create a report containing all the important report metadata 501 statements PL SQL statements parameters and templates If required you can add this output to the technical documentation of your pr
9. an APEX environment and press the Import button to add the Doxxy application Application Express Home SOL Workshop Team Development Administration f X Application Builder Applications Database Applications Websheet Applications Packaged Applications Actions applications found This installation procedure assumes that you have already installed an APEX environment or have one available with access to a workspace where you can install the Doxxy application Specify File File Import Confirmation Install Select the file you wish to import to the export repository Once imported you can install your file If the imported file is a packaged application export the installation wizard will allow you to run the packaged installation scripts after installing the application definition Import file file selected File Database Application Page or Component Expor 2 Websheet Application Export 2 Plug in CSS Export Image Export File Export Theme Export User Interface Defaults Team Development Feedback File Character Set Unicode UTF 8 Tasks Manage Export Repositon Export Component Export The file is imported Click on Next to install the Doxxy application APPENDIX Doxxy User Manual Version 01 02 iAdvise Doxxy gt Specify File File Import Confirmation Install If you wish to Inst
10. letters for these codes e g BEGIN ORDERS 3 The codes used to refer to the column names in SQL statements should also be done in capital letters e g CUST NAME ORDER DATE and ORDER TOTAL This is an example of a possible template in Word Orders This report gives you an overview of all orders Order Total BEGIN ORDERS CUST NAME ORDER DATE DRDER TOTAL END ORDERS Give your template a descriptive name e g orders template docx and save it Return to your document in the Doxxy web application Navigate to the appropriate folder and select the document that you would like to add a template to the same one that you have just written the SOL statement for My first report Doxxy User Manual Version 01 02 ncs iAdvise Doxxy Use the Templates tab to load your templates Right now the list of loaded templates is empty g Orders overview Overview of all orders Actions Preview SQL PL SQL Templates Parameters k Templates No Templates found To add a template click on the Create Template button on the Templates tab or click on the icon that appears when you hover over Templates in the expanded tree Complete the fields CREATE Template Document 77 Orders overview Template Name 7 Orders Template Upload New Template Browse orders template docx Template Format 7 DOCX gt Description 7 Template for the Order Overview docum
11. lt height gt lt indicator gt The function has the following parameters the BLOB column containing the image e the width of the image in the Word document in cm theheight of the image in the Word document in cm e anindicator 0 1 to determine if the original proportions of the image need to be maintained In case both the width and height are NULL then the original measurements of the image are used Example SELECT bl dg4o interface img product image 2 2 AS tag in document FROM demo product info Please also note that two additional columns have been added in order to display the conditional data in the templates 1 The PRODUCT AVAIL column returns the values 1 or 0 respectively to indicate whether the product is available or not If the product is available it will be printed in black If the product is no longer available the product will be displayed in red 2 The CHECK PHOTO is required to show a blank photo if no BLOB could be found for a specific product in the database This column will also display a 1 picture in database or 0 no picture database Edit the statement of the Items statement Next we have to indicate that we need the data to be displayed in an additional view namely table layout To do this simply click on the Add button under Usage in Document below In this way we can add as many views as we want For this example we used Table For Recordset Code we will en
12. n EUN 52 5 54 742 54 7 1 1 EXDORE One 54 7 1 2 Export multiple documielits u eoi u u Lumu u usu LS 55 7 2 56 8 SSYSAdmin 7 Admin ODUIONS ooo u ua 57 S L dUseradminist atiol aux nu D sa 58 8 2 LOPPIHB ess cider bolas a aD Fic eet usu 60 8 2 1 Actes Guat 60 8 2 2 AC Ou pr EC 61 83 Manage CANS CS usa 62 8 4 Manage SSROnVIWS u a CENE 62 DNA RUE 64 64 Installation steps in APEX 65 Table of Contents Doxxy User Manual Version 01 02 iAdvise Doxxy 1 INTRODUCTION 1 1 PREFACE Currently there are virtually no affordable products available to generate Microsoft Word documents based on data from the Oracle database Tools that might meet these requirements are often expensiv
13. not mandatory if blank ENDIF MY CHECK Here the MY CHECK is a column from an SQL statement that returns a 1 or O For example if you want the number of products ordered to be displayed in black if the product is still available but you want the items to be displayed in red if the items are no longer in stock then it becomes pay attention to the color IF PRODUCT AVAIL IQUANTITY ELSE PRODUCT AVAIL QUANTITY ENDIF PRODUCT AVAIL If you want a warning to be displayed in red if one of the products is no longer in stock a separate Product avail SQL statement is written for this check then it becomes BEGIN PRODUCT AVAIL IF CHECK PRODUCT AVAIL This product is currently not in stock your order may be delayed ELSE CHECK PRODUCT AVAIL ENDIF CHECK PRODUCT AVAIL END PRODUCT AVAIL Images from the database To display images from the database you will have to follow a number of steps 1 Inthe query you can use a specific API call to retrieve your image bl dg4o interface img image blob column width lt height gt lt indicator gt Example oo FROM demo product info 2 Youonly need to add the following tag to the template IMG tag in document A practical example Doxxy User Manual Version 01 02 iAdvise Doxxy 5 2 4 EXTRA FUNCTIONALITY 5 2 4 1 PL SQL STATEMENTS Doxxy allows to execute specific business logic written in PL SQL at the beginning or end of
14. package The log levels vary from 1 to 6 with log level 1 being the most strict and will therefore allow the least amount of messages to go through only these error messages will be displayed Log level 6 not only allows all messages to go through such as errors and warnings but also general information This is really useful to debug the code Log Configurations Default Level 1 Actions Packaqe Name Log Level Creation User BLSDG4O OOXML PARTS2 6 SYSADMIN SYSAdmin Admin Doxxy User Manual 60 69 Version 01 02 iAdvise Doxxy 8 2 2 LOG MESSAGES The results of the tracing are displayed on the Log Messages page Here you will get an overview of the messages that you have generated These messages can be exported by using the Actions menu and sent to the Doxxy team who will analyse it These messages can provide the Doxxy team with useful information which will assist them in detecting and resolving the errors Log Messages Default Level 1 Actions Select Columns Log Dt Log Time Log User Info Li Error Stack 17 03 2014 14 23 ADMIN ERROR 180 i ORA 31011 XML parsing failed ORA 19202 Error occurred in XML processing LPX 00234 namespace prefix w rPr is not declared Error at line 2 17 03 2014 12 54 DOXXY USER ERROR ORA 20001 unknown tag BEGIN SQL LINK VARIABLES 17 03 2014 12 49 DOXXY USER ERROR ORA 20001 unknown tag type END SQL TABLE 17 03 201
15. product info p ON i product id p product id WHERE i order id ORDER ID ORDER BY p product name ORDER TOTAL ABC Parameters Create Parse SQL Copy trom SQL P_ORDER_ID Usage In Document Recordset Code Document Area List ITEMS LIST Body of a document Q Add 5 2 2 4 ITEMS TABLE We actually need two almost equal sets of data for this report You only have to select this data from a single SQL statement once This SQL statement is analogous to the SQL statement for Items list We only need some additional columns such as a BLOB column for the image of the product We can expand this statement with these additional columns The Items SQL statement will look like this SELECT p product name P product description CHAR Unit Price 99960999D00 unit price T Qu ta t TRIMCDO CHAPT TL Unit price 1 quantity DECODBADsSproduct avail Y 1 0 product avail bl dg4o interface img p product image 2 2 photo SIGN NVL dbms lob getlength p product image 0 check photo FROM demo order items i JOIN demo product info p ON i product id p product id WHERE i order id ORDER ID ORDER p product name A practical example Doxxy User Manual Version 01 02 iAdvise Doxxy The column Photo selects the BLOB that contains the image of the product Use the specific API call to retrieve this image bl dg4o interface img image blob column width
16. right Folders Order Confirmation SQL Statements Fern Q Soarch Parent Name By using the Actions button you can view or edit modify delete move export print meta data report visualize template your document Request a preview of your work DOCX report by using the Preview button or use the handy eye icon that appears when you hover over the tree Actions Preview Edit Document Info Move Copy Export Delete e Print Meta Data af Visualize Template Exploring Doxxy Doxxy User Manual Version 01 02 ne We iAdvise Doxxy 4 FIRST REPORT Follow these simple steps to create a report If required these can be repeated a couple of times until you have achieved the desired result Step Description Where Create the desired folder and document In the Doxxy tool 2 Data Select the information that you would like to In the Doxxy tool What display in your report from the database How to be displayed in your report template your requirements application The following paragraphs contain a brief description on how you can perform these different steps with the Doxxy tool In Doxxy besides What and How there are two more refinements possible when creating a report namely defining one or more parameters and adding PL SQL logic before or after the execution of a report These additional possibilities are addres
17. select CUSTOMERS The data from this statement will be displayed in the Body of a document Save your SQL statement by clicking the Create button Once you ve successfully gone through all the above steps you should get the following result Statement Parent SQL Statement 2 Link Variables Orders w ORDER ID x SQL Statement SELECT c cust first name ORDER TIMESTAMP c cust first name c cust last name cust name c cust street addressl DECODE c cust street address2 NULL NULL ORDER TOTAL c cust street addressl cust street c cust city c cust state c cust postal code cust city FROM demo orders o JOIN demo customers c ON o customer id c customer id Creat Fe WHERE o order id ORDER ID Parameters P ORDER ID Parse SQL Copy from SQL Usage Document Recordset Code Document Area Paragraph gt CUSTOMERS Body of a document Add Doxxy User Manual Version 01 02 iAdvise Doxxy Note the visualized master details structure between the different queries in the tree to the left Y 28 MY_FOLDER Y My Documents Y Order Confirmation EE 5 2 2 3 ITEMS LIST The items are printed twice in this report each time in a different view The ordered items are listed in the letter The items are displayed in a table window on the second page of the attachment For this l
18. that you created you will find this SQL statement in the SQL tab Actions e Preview g Orders overview overview of all orders SOL PL SQL Templates Parameters o SQL Statements Parent Name Name Description Orders All orders from the DEMO ORDERS table If you require more data you can select it by using the extra SQL statements You may repeat this Create SQL step as many times as you need until you have collected all the data To edit an SQL statement that has already been created click on the appropriate statement s name In the next step we will create a template and upload it here My first report Doxxy User Manual Version 01 02 Sc GE iAdvise Doxxy 4 3 CREATE TEMPLATES How In this step you will create the layout of your report You determine what your report should look like and this can be achieved by simply designing your report in a Word document template This Word template allows you to use all the functionalities that Microsoft Word has to offer Use a code word in the places where you want the data from the database to be inserted You may use these code words or simply codes in various places 1 As plain text in a paragraph 2 Ina bulleted list 3 Ina table These codes may be used for various purposes This should always follow a specific syntax 1 To start a data set from an SQL statement BEGIN MY RECORDSET CODE 2 To close data set from an SQL statement E
19. the details of the tree item that has been selected In the following illustration you will see a list of your folders 3 DOXXY USER Folders nie di Folders v li DOXXY Q Search amp Reset APEX lt ii DEMO Name Description E ii SYSTEM DOXXY Internal Doxxy folder vV FOLDER APEX Folder containing reports on APEX metadata o My Documents DEMO Doxxy Folder with sample demo reports documents based on DEMO xxx tables SYSTEM System info for the Doxxy Environment MY FOLDER My Project Folder My Documents My Documents Folder Doxxy 01 02 00 You can create folders by using the Create Folder button Give the folder a descriptive name and if necessary select the parent directory that you want to create your folder in Next click on Create CREATE Folder Folder Name New Folder New Folder within FOLDER Description 7 Parent Folder MY FOLDER Exploring Doxxy Doxxy User Manual Version 01 02 iAdvise Doxxy To open a folder click on the desired folder s name in the tree On the illustration below you will notice that the DOXXY folder has been opened This folder in turn contains a number of subfolders namely APEX DEMO and SYSTEM The DEMO folder contains the Order Confirmation demo report which is an example document that we created We will discuss it later on in this manual A DOXXY USER Home
20. you will use the more generic way of the retrieve function see below In our example report no parameters were defined Imagine however if we had created a parameter P ORDER ID which was expecting a numeric value as input then the parameter can be used as follows p bind variable 3 gt ORDER ID p bind variable number 3 gt 1 for order id 1 Usethe p application key parameter to specify the application key if it is required for the document For more information about application keys please see the Admin options section No application key is required for our sample report e Use parameter p user id to pass the username This parameter is provided for future development in a next release and does not apply yet e Use parameter p format to specify the required output format in the TRIAL version by default only DOCX is possible If you have a license for the PDF option you can also specify PDF here Generic form FUNCTION generate document p document IN 2 x document template name IN VARCHAR2 bind variables IN bind variables t p application key IN VARCHAR2 DEFAULT NULL user id IN dg4o users username TYPE DEFAULT NULL tormat IN VARCHAR2 DEFAULT DOCX RETURN BLOB Enter the parameters as follows Usethe p document parameter to select the document that you require The name of the document and the full path should be specified please
21. 1 User Manual J Version 01 02 w http www doxxy eu Last modification 14 04 2014 m info doxxy eu Developed by iAdvise t doxxyNews iAdvise Doxxy TABLE OF CONTENTS L IntroducHol oii s p cO HEURES n E SE Ec a UII LN EVE 4 117 TL 4 1 2 What you expect from Doxxy 5 1 3 What is already possible in Doxxy 5 2 JnstalldBoliustdieses bed ndo odo Sut bora 6 21 lt 5 55 aO sis 6 22 yu un UTR 6 3 EXDIOFIDg 00 L tu u kanaka 8 31 Folders uuu uu uu ates 9 22 DOCUMENTS kasa aya maa ayasa 11 4 13 4 1 Create a folder and document Start 13 42 Select data lll l Su ness 15 4 3 Create templates 19 4 4 Request a preview Preview
22. 4 12 47 ADMIN ERROR ORA 3101 1 XML parsing failed ORA 19202 Error occurred in XML processing LPX 00225 end element tag w body does not match start element tag w tc Error at line 2 17 03 2014 ORA 3101 1 XML parsing failed ORA 19202 Error occurred in XML processing LPX 00244 invalid use of less than lt character use amp it Error at line 2 17 03 2014 ORA 20203 Document corrupt SQL STATEMENTS DOCX xml is invalid ORA 06512 at DG4O DEV BLSDG4O0 MESSAGES line 181 ORA 06512 0040 DEVBLSDG4O0 OOXML PARTS2 line 1804 ORA 31011 XML parsing failed ORA 19202 Error occurred in XML processing LPX 00244 invalid use of less than character use amp it Error at line 2 17 03 2014 1803 BL DG4O OOXML PARTS2 ORA 3101 1 XML parsing failed ORA 19202 Error occurred in XML processing LPX 00244 invalid use of less than character use amp lt Error at line 2 17 03 2014 1803 BL DG4O OOXML PARTS2 ORA 3101 1 XML parsing failed ORA 19202 Error occurred in XML processing LPX 00244 invalid use of less than character use amp lt Error at line 2 17 03 2014 1916 BL DG4O0 OOXML PARTS2 ORA 20203 Document corrupt SQL STATEMENTS is invalid ORA 06512 at DG4O DEV BLSDG4O MESSAGES line 181 ORA 06512 at 0040 DEVBLSDG4O0 OOXML PARTS2 line 1804 ORA 31011 XML parsing failed ORA 19202 Error occurred in XML processing LPX 00244 invalid use of less than lt 7 character use amp it Err
23. 4 44 REQUEST A PREVIEW PREVIEW Once you have selected the data that you require and your templates have been uploaded in the Doxxy tool you can review your report by requesting a preview In Doxxy go to the list of your created documents Select the appropriate document and click on the Preview button in the upper right corner to generate your report g Orders overview overview of all orders Actions Preview SOL PL SQL Templates Parameters Another useful way in which you can request a preview is to use the eye icon that appears when you hover over the document that you want to review in the tree Folders DOXXY Y FOLDER Y My Documents IC CREER 3 Mew Folder In both cases the Generate Preview screen will appear The report can be downloaded once you have selected the appropriate template Choose which template Doxxy must use to generate your report The selection boxes will automatically include all your previously loaded templates If you have used parameters you can also enter the values here Parameters will be covered in the example in the next section My first report Doxxy User Manual Version 01 02 iAdvise Doxxy Press the Download Preview button If everything goes as expected a report will be generated that can be saved and opened Generate Preview Template 1 Orders Template Output format DOCX 7 Cancel Show Call Download P
24. ATE CREATE CREATE dg4o 0102 ANY DIRECTORY dg4o 0102 TRIGGER TO dg4o 0102 VIEW TO dg4o 0102 TABLE TO dg4o 0102 Installation Doxxy User Manual Version 01 02 gum iAdvise Doxxy 3 EXPLORING DOXXY Before you begin to explore Doxxy you will need to launch Doxxy in your internet browser Go to the appropriate APEX url and sign in using your username and password Login Username doxxy user Once you have logged in successfully you will be directed to the Home page Depending on the user rights and the directories you have been given access to you will see the following screen DOXXY USER Folders Folders Description DOXXY Internal Doxxy folder APEX Folder containing reports on APEX metadata DEMO Doxxy Folder with sample demo reports documents based on DEMO xxx tables SYSTEM System info for the Doxxy Environment MY FOLDER My Project Folder Doxxy 01 02 00 You can still return to the homepage at any time by using the bread crumb navigation at the top just below the black bar or by clicking on the Doxxy logo in the upper left corner of the screen Exploring Doxxy Doxxy User Manual Version 01 02 8 69 iAdvise Doxxy 3 1 FOLDERS To the left you will see a useful interactive tree this shows you all the folders sub folders documents and document details that you have access to To the right you will see
25. ND MY RECORDSET CODE 3 Toretrieve data from an SQL statement MY COLUMN NAME 4 To display data conditionally IF MY CHECK content if MY CHECK 1 ELSE MY CHECK content if MY CHECK not mandatory if empty ENDIF MY CHECK MY CHECK is a column from an SQL statement that returns a 1 or O 5 To display images from the database IMG MY BLOB COLUMN MY BLOB COLUMN is a column from an SQL statement that returns a BLOB containing an image The symbols for start and end tag can be entered via Edit Document Info By default it is set to the left and right square brackets g Orders overview overview of all orders Actions Preview Edit Document Info SQL PL SQL Templates Parameters Move Export SQL Statements ii Delete eate SQL Print Meta Data Visualize Template My first report Doxxy User Manual Version 01 02 Ec GE iAdvise Doxxy EDIT Document Info x Dacument Mame 7 Order overview Overview of all orders Description 7j Folder MY DOCUMENTS Begin Tag 21 End Tag 21 When you generate your report in the next step your report will look exactly the same as the one that you have chosen in your Word template The codes will be replaced by the data that you require from your SELECT statements If you have placed the codes in a table then Doxxy will automatically display the correct number of rows according to the number of records that is returned b
26. NULL bind variable name 2 IN VARCHAR2 DEFAULT NULL rP pind Variable tese VARCHAR2 DEFAULT NULL p bind variable name 3 VARCHAR2 DEFAULT NULL p bind variable number 3 IN NUMBER DEFAULT NULL bind variable name 4 IN VARCHAR2 DEFAULT NULL bind variable number 4 IN NUMBER DEFAULT NULL bind variable name 5 VARCHAR2 DEFAULT NULL bind variable date 5 IN DATE DEFAULT NULL bind variable name 6 VARCHAR2 DEFAULT NULL bind variable date 6 IN DATE DEFAULT NULL application key IN VARCHAR2 DEFAULT NULL user id IN 9940 users username TYPE DEFAULT NULL p format IN VARCHAR2 DEFAULT DOCX Enter the parameters as follows Usethe p document parameter to select the document that you require The name of the document and the full path should be specified please note that these are case sensitive For this report example we used document gt FOLDER My Documents Orders overview e Usethe p document template name parameter to select the template that you require For our sample report we used p document template name gt Orders Template My first report Doxxy User Manual Version 01 02 iAdvise Doxxy e You have two parameter options in which you can enter the VARCHAR2 data type two parameters for the NUMBER data type and two parameters for the DATE data type This can cause a restriction if your report requires multiple parameters In that case
27. QL Statements loading templates and if required adding a PL SQL procedure or defining input Parameters Please note that there are several ways to navigate between the different data this can be done via the tree to the left or by using the tabs in the detailed screen to the right 4 2 SELECT DATA WHAT In this step you will determine which data to display in your report You can do this by selecting the data through one or more SELECT statements b Orders overview overview of orders Actions Preview SOL PL SQL Templates Parameters o SQL Statements No SQL statements found First we need data for our report In this example we will show you a simple overview of all the orders that have been placed from the DEMO ORDERS table Click on the Create SQL button in the SQL tab and complete the fields My first report Doxxy User Manual Version 01 02 uo iAdvise Doxxy Create SQL Statement Info 1 Sequence 7 Orders 10 Description 7 All orders from the DEMO ORDERS table Statement Parent SOL Statement 7 Parameters No Parent Parameters SQL Statement 77 SELECT o order id c cust first name c cust first name c cust last name cust name TO CHAR o order timestamp DD MM YYYY TRIM TO CHAR o order total 999 999000 order total FROM demo orders o JOIN demo customers c ON c customer id o customer id Parse SQL Copy from SQL
28. QUANTITY NUMBER 8 The sample report is an Order confirmation It shows an order confirmation letter with an overview of all the ordered items A copy of all the ordered items is attached to the letter You can request a report for one specific order or for all the orders together The latter results in one letter per order grouped together and in succession in a Word document A practical example Doxxy User Manual Version 01 02 iAdvise him rim mand mid him durwTuey E ar when 1 ORDER CONFIRMATION Drga kay Tetoep cemu Loon CT ras mr ee 1 T saan 2112 Dex Coe r hh pmi mrs uru Track Far a Leve anm SOC zit xn Tier DI Tha armour pd EZ mdr cmm seme 1 Kp amer hug nissim unt Fanm Brad med maman ci xar crm Wn mji your purchanar Tha Don xam Ics him rimi mid him Append ue y of qeur wien romber T ORDER COMFISMAT ICHN Due 45012 ewm Tura 1 iiih 21 2112 kis Track you Ear whopping dt presuczr mnd Far MEDC
29. S TAS IF PRODUCT PHO IF PRODUCT IF PROQUCT IF PROOUCT iE t RODUCT A UCT DESCRIPTION ELSE F TO AIL WNIT_PRICE AVAIL TOTAL VAIL PROOUCT H agpucr AVAIL F2ODUCT IMG phatal ELSE PROOUCT TITY ELSE FR 15 AME ELSE PRODU DESCRIPTION ENDIF PRO ELSE CHECK_ WAILJ UNIT_PRICE ODUCT_AVAIL PRODUCT AV CT AwAL PRGOUC DACT j ENDIF FRODUCT AlL TOTAL T AMAIL ENDIF PRODU R CEJ ENDOE P I EuDIF PRODOUC AVAIL 7 AVAIL ILIEND ITEMS TABLE EMDIF CHECK ORDER IBEGIN PROOGUCT PRODUCT This praduct is currently nat im stock your order may be drayed EL5E CHECK PRODUCT AVAIL EENGIF CHECK PROQUCT END ORDERS Advise nv Veldkant 336 2550 Kontich Page 2 A practical example Doxxy User Manual Version 01 02 iAdvise Doxxy Tables To display data table use the following notation Product Description Exwtended Price in BEGIN ITEMS_TABLE DESCRIPTION TOTAL PRICE END ITEMS PRODUCT NAME TABLE Conditional display The conditional display of data can be done by using the following notation IF MY CHECK content if MY CHECK 1 ELSE MY CHECK content if MY CHECK 0
30. all now click Next gt You can also install this file at a later time by navigating to the Export Repository Current Application Mo application selected Tasks Manage Export Heposito Preview File Select the schema on which all the Doxxy packages must be installed Choose the appropriate Parsing Schema and the Build Status in this case the parsing database schema DOXXY and the Run and Build Application as build status respectively Next select an option to generate the appropriate Application ID for the Doxxy application Click on Install Application Specify File File Import Confirmation lt Install Application When you install an application having the same ID as an existing application in the current workspace the existing application is deleted and then replaced by the new application If you attempt to install an application having the same ID as an existing application in a different workspace a benign error message displays If you are importing a packaged Application Express application the installation wizard will allow you to install supporting objects Current Workspace Export File Workspace ID 47136106353637242 Export File Application ID 455 Export File Version 2010 05 13 Export File Parsing Schema 0840 Application Origin This application was exported from another workspace x Parsing Schema Build Status Install Application Auto Assig
31. consists of the following steps 1 Create a specific Doxxy database schema 2 Create a workspace in APEX and link it to the Doxxy database schema 3 Login to this workspace and import the APEX export file including the Supporting Objects Please see the Appendix for more information regarding the third step Remarks e By default the IMAGE PREFIX attribute is defined as i If required you may change this according to your environment e Doxxy installation comes with a standard user sysadmin sysadmin Please use the application to change your password according to your own specific requirements e The Supporting Objects as mentioned in step of the installation contain all the required Doxxy objects as well as the PL SQL engine Installation Doxxy User Manual Version 01 02 iAdvise Doxxy Sample script for creating a specific Doxxy schema REM change password tablespace name to your requirements CREATE USER dg4o 0102 IDENTIFIED BY my password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT GRANT UNLIMITED ON users CREATE CREATE CREATE CREATE CREATE CREATE SESSION TO dg4o 0102 SEQUENCE TO dg4o 0102 SYNONYM TO dg4o 0102 PUBLIC SYNONYM dg4o 0102 ANY CONTEXT TO dg4o 0102 PROCEDURE 1626 010277 DROP PUBLIC SYNONYM dg4o 0102 CREATE CREATE CRE
32. cross x showing on the left hand side of the erroneous element From the next example you may conclude that e the end tag of the customers recordset is missing e the cust name tag is referring to a column that is not retrieved by any of the SQL statements Probably a spelling mistake e There is some confusion about the recordset name of the order items in the template both the tags item list and items list were found asthetag item list is incorrect the Doxxy engine cannot check the columns product name and total price against any SQL statement Because of this these tags also get an invalid marking Visualize Template Template Template with Error x Visualize Collapse All Expand All 3 begin orders begin customers INVALID end tag not found 5 cust name INVALID column cust name was found in the queries for this document cust street cust city order id order timestamp cust first name begin item list INVALID column item list was not found in the queries for this document 5 product name INVALID column product name was found in the queries for this document total price INVALID column total price was not found in the queries for this document end items list INVALID begin tag found order total order id i order id A practical example Doxx
33. e SQL statement We only want this check to happen once for the items in the order that we ve just printed We will achieve this by adding a WHERE clause again namely WHERE order id ORDER ID The Product Available SQL statement will look like this SELECT SIGN COUNT 1 check product avail x FROM demo order items i JOIN demo product into p ON z product id p product id WHERE i order id ORDER ID AND p product avail lt gt y For Parent SOL we will enter the Orders SQL statement A practical example Doxxy User Manual Version 01 02 iAdvise Doxxy We use Usage Document to select the desired view in this case Paragraph For Recordset Code we will select PRODUCT AVAIL The data from this statement will be displayed in the Body of a document Finally we will check that the link variable ORDER 10 is highlighted light blue Save your SQL statement by clicking the Create button Once you ve successfully gone through all the above steps you should get the following result Statement Parent SQL Statement 2 Orders M Link Variables af ORDER ID 123 x SQL Statement 1 SELECT SIGN COUNT 1 check product avail ORDER TIMESTAMP ABC FROM demo order items i demo product info p WHERE i product id p product id ORDER TOTAL ABC AND i order id ORDER ID AND p product avail lt gt Y P SQL C from SQL a ce ME RES Parameters Create Usage In Document Recordse
34. e require a complex installation procedure or are difficult to use The innovative Doxxy tool a solution that was developed in pure PL SQL now allows you to generate documents from the Oracle database in a simple and cost effective manner Source documents templates can be created and uploaded by the user and from there Word documents that contain data from the Oracle database can be generated in a quick and simple manner The front end that manages your metadata such as selecting your data or loading templates was created in Oracle Application Express APEX This simple tool will get you started in no time and the installation process is very straightforward In addition to this Doxxy 01 02 includes a lot of exciting new features The default output format is DOCX From version 01 02 it s also possible to generate PDF files We hope that you will experience endless satisfaction with Doxxy Introduction Doxxy User Manual Version 01 02 iAdvise Doxxy 1 2 WHAT CAN YOU EXPECT FROM easy tool that will get you started in no time Complete use of all Microsoft Word options such as the different fonts bold underline lists tables images Templates and data can be separated The users can supply templates No Oracle experience is required The data can be compiled separately by an Oracle developer layout of the documents will depend on the format of the templates in Microsoft Word The
35. e added by simply importing the logo into the Word document Microsoft Word features Headers and footnotes Microsoft Word has a feature which allows you to simply add headers and footnotes Adding data from the database If you want to record data from the database anywhere in your letter you can do so by simply using the following notation BEGIN MY_RECORDSET COLUMN NAME END MY RECORDSET CODE Example BEGIN CUSTOMERSJ CUST END CUSTOMERS These codes must be an exact replication of the column aliases in the SQL statements Please note that the tag for closing the Recordset Code ORDERS is not displayed on this page This will be displayed in the attachment at the bottom of the next page In this way we can also use columns from the ORDER statement on the next page The attentive reader should also remember that we selected Page Break as Usage Document under Orders In this way a new page will be used for each new order Bulleted lists Bulleted lists can be displayed by using the following notation e BEGIN ORDERS CUST NAME ORDER DATE CORDER TOTAL END ORDERS The codes for the recordset and the columns must be an exact match A practical example Doxxy User Manual Version 01 02 5 2 3 2 THE APPENDIX PONNY Printed on TIME d MM vyyy Appendix Summary of your order with reference number ORDER ID eee Price in IBEGIN TTEM
36. ent Template Name Choose a clear and unique name for your document template Upload New Template Select the previously saved Word document that will serve as a template The document name will also be the file name My first report Doxxy User Manual Version 01 02 iAdvise Doxxy Template Format Bestandstype van de template Op dit moment kan je enkel DOCX aanduiden Description Here you can enter information about your template To confirm the creation of your document template click on the Create button You will see that your template has been uploaded g Orders overview Overview of all orders Actions Preview SQL PL SQL Templates Parameters k Templates Filename Description Orders Template DOC orders template docx Template for the Order Overview document You can upload multiple templates for a document When generating your report you will choose according to which template you want the report generator to display your data This can be useful if you want to create e g an English and a German version of the same report In this case you will create two templates one in English and one in German To add multiple templates please repeat the previous step Click on the name of the template if you want to view or customize an existing template It is possible to upload the customised version again My first report Doxxy User Manual Version 01 02 iAdvise Doxxy
37. er driven To import a document just select the folder that contains the document that you want to import from the tree If you open the Actions menu you will see an Import Document s option z My Documents Documents Folder Actions Edit Folder Info Doc t Create Subfolder LOCUMents Document s Export Documents Delete Q Search amp Reset When choosing the Import Document s option a pop up screen will appear where you can retrieve the exported XML file If you then click on Import the file will be uploaded and the definitions created in the Doxxy metadata tables Please note If the folder that you selected already contains a document with the same name the latter will be replaced Import Document BEWARE If there are documents with the same name they will be overwritten x Select document s to import Browse de Order Confirmation xml Deployment Doxxy User Manual Version 01 02 iAdvise Doxxy 8 SYSADMIN ADMIN OPTIONS In Doxxy there are two Administrator profiles an Admin and a SYSAdmin role You can find the Administrator privileges by unfolding the user s menu in the upper right corner Only if your user has sufficient privileges you will be able to access these Admin pages 4 DOXXY USER m Folders Change Password Logout If your user got the SYSAdmin role the following menu options will appea
38. h will add an implicit page break to each new order This way each new order will automatically appear on a new page For Recordset Code we will select ORDERS The data from this statement will be displayed in the Body of a document Save your SQL statement by clicking the Create button Once you ve successfully gone through all the above steps you should get the following result A practical example Doxxy User Manual Version 01 02 iAdvise Doxxy Statement Parent SQL Statement 2 Parameters Create No Parent w P ORDER ID SQL Statement 7 SELECT o order id TO CHAR o order timestamp fmDD month YYYY nls date language american order timestamp TRIM TO CHAR o order total 999G999Dp00 order total FROM demo orders o WHERE o order id P ORDER ID OR P ORDER ID IS NULL Parse SQL Copy from SQL Usage In Document Recordset Code Document Area Page Break 5 Body of a document Q Add 5 2 2 2 CUSTOMERS The order confirmation is addressed to the customer in the right hand corner In order to print the customer details we will have to select them from the DEMO CUSTOMERS table We only want to view the customer details which are associated with a particular order ORDER ID from the previous SQL statement for the letter that we want to print For this we will add a WHERE clause to the statement of the customers namely WHERE order id ORDER ID
39. iables t Ll BLOB BEGIN 1 band variables TP ORDER PDT anam ber value Ly 1 doc BLSDG4O INTERFACE generate document p document gt MY FOLDER My Documents Orders overview document template name gt Orders Template bind variables gt 1 bind variables application key gt 7 POL User od 57 p format gt DOCX This procedure will create a BLOB which contains your DOCX document You can also perform other actions in your application download preview print Tip Use the Show Call button in the Generate Preview screen to generate this PL SQL code You can copy and paste this code in your own application My first report Doxxy User Manual Version 01 02 Aic GE iAdvise Doxxy Generate Preview x Template gt Orders Template Output format 27 Y Cancel Show API Call Download Preview My first report Doxxy User Manual Version 01 02 iAdvise Doxxy 5 A PRACTICAL EXAMPLE 5 1 SITUATION This chapter contains a complete example that makes use of DEMO tables these are also used in the Sample Application in APEX P ORDER ID NUMBER F CUSTOMER ID NUMBER ORDER TOTAL NUMBER 8 2 ORDER TIMESTAMP TIMESTAMP WITH LOCAL TIME ZONE USER NAME VARCHARZ 100 BYTE TAGS VARCHARZ 4000 BYTE P ORDER ITEM ID NUMBER 3 UF ORDER ID NUMBER PRODUCT ID NUMBER UNIT PRICE NUMBER 8 2
40. ice overview of the tags that have been used in the template allowing you to easily check if all the tags in the document were used correctly Any error will be marked clearly To visualize your template you first need to select the required document from the tree structure on the left hand side Click on the Actions button A list menu will appear in which you ll need to choose Visualize Template Actions Edit Document Info Move Copy Export Delete A Print Meta Data w Visualize Template From the Template list you can select the template that you want to visualize After pressing the Visualize button the structure of the recordset codes that were used with the BEGIN and END tags the IF tags and the column tags are displayed in a tree structure This structure reflects how your Word template was built Visualize Template Template 1 Order Confirmation x Visualize Collapse All Expand All 1 begin orders 1 begin customers j cust name cust street cust city order order timestamp cust first name F 1 begin items list z product_name total price 12 end items list order total order id 12 end customers E D practical example Doxxy User Manual Version 01 02 iAdvise Doxxy In case the template still contains errors this is indicated with a red
41. ist we only need two columns namely the product name and the price Moreover we only need the ordered items for the order that we want to print We will achieve this by adding a WHERE clause again namely WHERE order id ORDER_ID with ORDER ID as the column from the parent SQL statement Orders The Items SQL statement will be displayed as follows SELECT name TRIM TO CHAR i unit price i quantity 999G999D00 total price FROM demo order Items 2 JOIN demo product info p ON l product id id WHERE i order id ORDER ID ORDER BY p product name For Parent SQL we will enter the Orders SQL statement By using Usage in Document we select the desired view in this case List For Recordset Code we will select ITEMS LIST The data from this statement will be displayed in the Body of a document Finally we will check that the link variable ORDER ID is highlighted light blue Save your SQL statement by clicking the Create button A practical example Doxxy User Manual Version 01 02 ou c GE iAdvise Doxxy Once you ve successfully gone through all the above steps you should get the following result Statement Parent SQL Statement 7 Orders Link Variables v ORDER ID x SQL Statement 2 SELECT p product name ORDER TIMESTAMP ABC TRIM TO CHAR i unit price i quantity 999G999D00 total price FROM demo order items i JOIN demo
42. istration Application Builder Application 328 Application 328 010200 m 4 i Th Run Application Supporting Objects Shared Components Utilities Export Import H Actions v Create Page gt Page Updated Updated By Page Type User Interface Group Lock Run 0 Page ZERO 2 weeks ago fraeybj Global Page Desktop Unassigned 101 Login 9 months ago gt fraeybj Dynamic Form Desktop Unassigned n Click on the fourth tab User Interface You will now see an Image Prefix label under General Properties Change this according to the prefix that is applicable to your environment for example p Starting from APEX 4 2 you can leave this field blank APEX will then use the default image folder ee Ez T adl SOL Workshop Team Development Administration Application Builder Application 328 User Interfaces Definition Security Globalization Application 328 Show All General Properties Logo Javascript User Interface Detection User Interfaces General Properties Content Delivery Network None use Web Server gt Media Type Click on Apply Changes and run the Doxxy application APPENDIX Doxxy User Manual 68 69 Version 01 02 iAdvise Doxxy You can log in to the application by using the default user sysadmin sysadmin We suggest that you change the password according to your own specific requirements Login Username sysadmin Pas
43. kaged Application for APEX The file contains 1 the APEX UI and 2 all database objects as supporting objects The installation process consists of the following steps 1 Create a specific Doxxy database schema 2 Create a workspace in APEX and link it to the Doxxy database schema 3 Login this workspace and import the APEX export file including the Supporting Objects Exampe script for creating a specific Doxxy schema REM change password 4 tablespace name to your requirements CREATE USER 9940 0102 IDENTIFIED BY my password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users GRANT CREATE SESSION TO dg4o 0102 GRANT CREATE SEQUENCE TO dg4o 0102 GRANT CREATE SYNONYM TO dg4o 0102 GRANT CREATE PUBLIC SYNONYM TO dg4o 0102 GRANT CREATE ANY CONTEXT TO dg4o 0102 GRANT CREATE PROCEDURE TO dg4o 0102 GRANT DROP PUBLIC SYNONYM TO dg4o 0102 GRANT CREATE TYPE TO dg4o 0102 GRANT CREATE ANY DIRECTORY TO dg4o 0102 GRANT CREATE TRIGGER TO dg4o 0102 GRANT CREATE VIEW TO dg4o 0102 GRANT CREATE TABLE TO 9940 0102 This Appendix gives you the installation steps to follow during this third point APPENDIX Doxxy User Manual 64 69 Version 01 02 iAdvise Doxxy INSTALLATION STEPS IN APEX This installation procedure assumes that you have already installed an APEX environment or have one available with access to a workspace where you can install the Doxxy application Start in
44. le reports from the same folder altogether This type of export is folder driven To achieve this select the folder from the tree structure By unfolding the Actions menu you ll see the option Export Documents APEX Folder containing reports on APEX metadata Actions FJ Edit Folder Info Create Subfolder Documents Import Document s Delete You will get an overview of all the documents in this folder By checking the checkboxes of the required documents you can specify which documents you want to include in the export file Export Documents g Documents Description Last Changed Overview pages a report which lists all pages of a given APEX application 14 03 2014 17 08 59 Javascript Toont alle Javascripts gebruikt worden in apex in de packages Toont alle plaatsen 28 01 2013 Source vanwaar javascript wordt aangeroepen branches items body attributes buttons page header 20 44 00 Summary Toont alle ajax calls met de daarbij horende application processes Press the Export button Again a XML file is created now containing all the meta data and the definitions of all selected documents In our example the XML file Doxxy multi export xml will be created and the system will prompt to save the file in the location of your choice Deployment Doxxy User Manual Version 01 02 iAdvise Doxxy 7 2 IMPORT DOCUMENT Importing one or more documents is always fold
45. n New Application ID Reuse Application ID 155 From Export File Change Application ID gt Tasks APPENDIX Doxxy User Manual 66 69 Version 01 02 iAdvise Doxxy The application is now being installed Once completed you will be asked if you would like to install the Supporting Objects These Supporting Objects contain all the necessary Doxxy objects and the PL SQL engine Select Yes to confirm and click on Next Supporting Objects Confirmation Cancel This application installer will guide you through the process of creating your database objects and seed data Application 328 doxxy 010200 Parsing Schema Free Space Required in KB Finally confirm that the installation must be performed The installation will now be completed The installation can take a couple of minutes So be patient After the installation is completed you can edit the image folder by choosing Edit application Install Summary Edit Application Application Your application s supporting objects been installed Tasks Your imported file is located in the export repository Unless you plan to install again you should remove it e Manage Export Repository e Export e Component Export APPENDIX Doxxy User Manual 67 69 Version 01 02 iAdvise Doxxy Select Edit Application Properties as illustrated below Application Express Home 444 Adi SOL Workshop Team Development v Admin
46. note that these are case sensitive For our sample report we used document gt FOLDER My Documents Orders overview Use the p document template name parameter to select the template that you require For our sample report we used p document template name gt Orders Template My first report Doxxy User Manual Version 01 02 20 E iAdvise Doxxy e You will then only have one record type parameter instead of a set of parameters to submit the input data In our example no parameters were defined Imagine however if we had created a parameter P ORDER ID which was expecting a numeric value as input parameter then you can be used as follows p bind variables gt bind variables You ll need to specify bind variables in advance bind variables P ORDER ID number value 1 for order id 1 e parameters p application key p user id p format have the same meaning the generic form as in the limited form Example In order to use these functions in your own application you will need a GRANT on the package which is associated with the retrieve functions namely BL5DG4O INTERFACE package A useful tip is to also create a synonym public or not e g BL5DG4O INTERFACE You can generate your document by performing the following procedure in your application this uses the generic form of the retrieve function DECLARE 1 bind variables BL DG40 INTERFACE bind var
47. oject To generate the metadata report you first need to select the appropriate document in the tree structure on the left hand side Next you need to click the Actions button A menu will open Select Print Meta Data and the survey report will be created Actions Edit Document Info Move I Export Delete im Print Meta Data Visualize Template A practical example Doxxy User Manual Version 01 02 ASE iAdvise Doxxy Below you can see a part of the generated documentation DOCUMENTFICHE ORDER CONFIRMATION Description Order confirmation letter including an overview of the ordered product items Tags Author DOXXY USER SQL Statements Orders Customers Items Product Available Write Log Entry After Templates Order Confirmation Template with Error Details SQL Statements Orders SELECT o order id TO CHAR o order timestamp fmDD month YYYY date language american order timestamp CHAR o order total 9996999D00 order total FROM demo orders a WHERE o order id P ORDER ID OR P ORDER ID IS NULL Usage in document Recordset Code Page Break ORDERS Body of a document Used Link Variables ee A practical example Doxxy User Manual Version 01 02 iAdvise Doxxy 5 2 4 4 VISUALISATION OF THE TEMPLATE Another clever feature is the possibility to visualize the templates of a certain document You will get a n
48. or at line 2 Use the Purge button in the right upper corner to clear the list of messages SYSAdmin Admin Doxxy User Manual Version 01 02 iAdvise Doxxy 8 3 MANAGE LICENSES Use the License option to manage your license keys By default it will read TRIAL This means that you are using a free trial version of Doxxy This trial version offers the same features as the paid version without any functional limitation Every single document will contain a watermark Doxxy features like PDF generation are not included in the TRIAL version Admin License Users Version Mr 2 0102 Applications Key 7 TRIAL Log Messages Expiration Date 2 31 12 9999 Log Configs Install Date 23 01 2013 Synonyms You can purchase a Doxxy license through our website http www doxxy eu or by sending an e mail to info doxxy eu A license is valid for one Oracle database instance We will ask you to furnish us with the database instance name in order for us to create a unique key for you We will then send you the unique key which must be entered on this page Please visit our website for more information regarding the prices license duration and support 8 4 MANAGE SYNONYMS Doxxy is typically installed in its own Oracle schema All SQL statements are parsed within the Doxxy schema This also means that this schema should get read privileges on the tables views and other objects from the application that you wish to
49. ost to assist you with this problem as quickly as possible Who knows the description and associated solution to your problem might be published in the list of tips and tricks which in turn might assist other Doxxy users in the future Tips amp tricks Doxxy User Manual Version 01 02 iAdvise Doxxy 7 DEPLOYMENT If you want to transfer a document from a development environment to a test or production environment you can do so by using the export import feature provided for within the application 7 1 ExPORT 7 1 1 EXPORT ONE DOCUMENT A document export is document driven To export a document just select the document you want to export from the tree If you open the Actions menu you will see an Export option G Order Confirmation order confirmation letter including an overview of the ordered Actions Preview Edit Document Info SQL PL SQL Templates Parameters Move Copy Export o SQL Statements Delete im Print Meta Data Visualize Template Q Search Reset When choosing the Export option an XML file is automatically created which contains all the metadata and definitions of the selected document In our example the XML file de Order Confirmation xml will be created and the system will prompt to save the file in the location of your choice Deployment Doxxy User Manual Version 01 02 iAdvise Doxxy 7 1 22 EXPORT MULTIPLE DOCUMENTS You can export multip
50. r Users Applications Log Messages Log Configs License Synonyms If you only got the Admin role you will only be able to access the Users screen in read mode and the Log Messages SYSAdmin Admin Doxxy User Manual 57 69 Version 01 02 iAdvise Doxxy 8 1 USER ADMINISTRATION The Users are one of the items which you fill find in the Admin pages Users Actions Applications Log Messages Log Configs Username Full Name Email Folders License DOXXY USER Doxxy User Synonyms SYSADMIN Sysadmin Here you can create users by using the Create button manage them by clicking on the user s name or delete them by clicking on the trash icon A user always has a Username to log in and a full name The password can also be managed here Please note that Roles should be linked to each user There are four types of roles 1 System Administrator a user with this role has access to all options in the Admin folder This role is required to add users and link them to folders You can also access logging information and see as well as modify information with regards to licenses 2 Administrator a user with this role has limited access to the Admin folder check users and log messages 3 Document Editor a user with this role can create and manage folders and documents However this user does not have access to the Admin pages 4 Viewer Reader this user can onl
51. review The default output format is DOCX If your license includes the PDF option you can also choose PDF as output format You can review the result by opening the downloaded preview This report gives you an overview of all orders Customer Name Order Total John Dulles 2 380 00 William Hartsfield 1 640 00 William Hartsfield 730 00 Edward Logan 1 515 00 Edward Logan 905 00 Edward Butch OHare 04 08 2013 950 00 For this particular document you can repeat steps 2 to 4 as many times as you want until you achieve the desired result My first report Doxxy User Manual Version 01 02 iAdvise Doxxy 4 5 RETRIEVE A REPORT RUN When you are done with your report in Doxxy you will want to load it from within your own application The retrieve function was designed for this purpose By using a number of parameters you will provide the information required and the input parameters You will receive a generated document in the form of a BLOB The retrieve function includes a basic simpler but more limited and a generic slightly more complicated but more complete form Both of these are located in the BLSDG4O_INTERFACE package under the database schema where 15 installed Limited form FUNCTION generate document p document VARCHAR2 x document template name 2 p bind variable name 1 IN VARCHAR2 DEFAULT NULL pp Dind variable rest ot VARCHAR2 DEFAULT
52. sed in the next chapter including a fully elaborated example 4 1 CREATE A FOLDER AND DOCUMENT START Before you begin we suggest that you create an appropriate folder for your test reports or navigate to your desired folder We used the My Documents folder for this user manual DOXXY Y FOLDER My Documents Bi New Folder My first report Doxxy User Manual Version 01 02 iAdvise Doxxy This folder is initially empty My Documents my Documents Folder Actions g Documents Create Document to start working Create the document in which you want to manage all your metadata such as SELECT statements PL SQL statements templates and parameters do this click on the Create Document button on the top right hand side or click on the icon that appears as soon as you hover over the folder which you would like to create a document CREATE Document x Document Name 1 Orders overview Overview of all orders Description 3 Folder My Documents Begin Tag End Tag 2 Choose an appropriate name for your document and enter a brief description Click on the Create button in the bottom left corner first report Doxxy User Manual Version 01 02 th SS iAdvise Doxxy After the document has been created it will appear under the folder where it was created By clicking on the document name we can shape our document by adding S
53. sword If this was successful the following page as illustrated below will be displayed Folders zs etin Folders v T vV SYSTEM Document Meta Data Fiche Name Description gt System Info DOXXY Internal Doxxy folder SYSTEM System info for the Doxxy Environment Doxxy 01 02 00 You can always check if Doxxy is functioning correctly by executing the pre packaged System Info report under the DOXXY SYSTEM folder That document gives an overview of the general system information for the installed version of Doxxy You can now start using Doxxy APPENDIX Doxxy User Manual 69 69 Version 01 02
54. t Code Document Area Paragraph gt PRODUCT_AVAIL Body of a document P ORDER ID Q Add 5 2 3 CREATE A TEMPLATE How The template consists of two pages 1 The first page contains the letter to the customer with a summary of the ordered items in bulleted list 2 The second page contains the appendix that displays a summary table of the ordered items together with a picture of the relevant product A practical example Doxxy User Manual Version 01 02 iAdvise Doxxy 5 2 3 1 THE LETTER usay Printed on TIME V amp d MM vyyy iAdvise ORDER CONFIRMATION BEGIN CUSTOMERS CUST NAME CUST STREET CUST CITY Your reference number ORDER ID Kontich ORDER TIMESTAMP Dear CUST FIRST NAME Thank you for shopping at iAdvise We have following products reserved for you BEGINM ITEMS LIST PRODUCT E TOTAL PRICE END ITEMS LIST The total amount of your order is ORDER TOTAL Your order has reference number ORDER ID Keep this number and this confirmation with you when contacting us Please find enclosed a detailed summary of your order We hope vou enjoy your purchase The Doxxy team Advise nv END CUSTOMERS iAdvise nv Veldkant 33b 2550 Kontich Page 1 A practical example Doxxy User Manual Version 01 02 iAdvise Doxxy Fixed image in the template The company logo is displayed in the top left corner of the letter The logo can b
55. tamp month XYYY nls date language amerrican order timestamp TRIM TO CHAR O 0rder total 799963998009 order total EROM demo Orders 0 WHERE o order id P ORDER ID OR P ORDER ID IS NULL The ORDER TIMESTAMP column is used to date the letter The ORDER TOTAL column is duplicated in the letter Please note that the ORDER 10 is also selected this is not the same as P ORDER ID The ORDER ID column will be used as a link between the different statements This statement is the master statement of the report It has no parent of its own Please note that there is an overview of the Parameters to the right of the SQL statement It is also possible to create or modify the parameters by using the Parameters box If you want to add a parameter to a SQL statement you can use the colon notation An easier way to achieve this is placing the cursor in the appropriate position in the statement and clicking the name of the parameter The parameter will automatically appear in the right notation in the SQL statement If the parameter was correctly added to the SQL statement it will be marked in light blue in the overview Additionally it will get a green checkmark if everything was saved successfully Through Usage in Document we select the desired view in this case the Page Break This is basically the same as Paragraph aka Global Document in the previous version of Doxxy but only with the added functionality whic
56. ter the unique code ITEMS TABLE The table will be displayed in the Body of a document Click on the Save Changes button to save the changes to your SQL statement Once you ve successfully gone through all the above steps you should get the following result A practical example Doxxy User Manual Version 01 02 iAdvise Doxxy Statement Parent SQL Statement Orders Link Variables af ORDER ID 123 SQL Statement 12 SELECT p product name p product description TRIM TO CHAR i unit price 9996999D00 unit price ET ORDER TOTAL ABC TRIM TO CHAR i unit price i quantity total price DECODE p product avail Y 1 0 product avail bl dg4o interface img p product image 2 2 photo SIGN NVL dbms lob getlength p product image 0 check photo FROM demo order items i Parameters G Cronte JOIN demo_product_info p i product_id p product_id WHERE i order ORDER ID ORDER BY p product name P ORDER ID ORDER TIMESTAMP ABC Parse SQL Copy from SQL Usage In Document Recordset Code Document Area List ITEMS LIST Body of a document ITEMS TABLE Body of a document Q Add 5 2 2 5 PRODUCT AVAILABLE If at least one product from the ordered items is no longer available we want additional warning to be displayed at the bottom of the report To achieve this we will also need a type of check column We select this by using the last Product availabl
57. the document generation process This is an optional step but it can be used for example to set a language code or store a trace of the report in a log table via the Oracle Context mechanism Initially the PL SQL tab contains no lines G Order Confirmation Order contirmation letter including an overview of the ordered Actions Preview SQL PL SQL Templates Parameters o PL SQL Statements Search Reset PL SQL statements found In this example we will store a record in a document logging table containing the name of the document along with the date and time on which the document was created Click on the Create PL SQL button in the PL SQL tab and fill in the fields Give a meaningful name a sequence number and a short description Apart from the PL SQL code itself it s important to indicate whether the logic should be executed before or after the creation of the document You can specify this in the Before or After field In the example below the code will be executed After the creation of the documents Please note that you can also use parameters here This example however does not contain any parameters A practical example Doxxy User Manual Version 01 02 iAdvise Doxxy Create PL SQL Statement Info Name Sequence 1 Write Log Entry 10 Description 12 Write a trace of the processing this docurnent Statement Before or After
58. use in your SQL statements You can then simply use these objects by prefixing them with the name of the schema from your application An alternative solution is that you create PRIVATE SYNONYMS for every single one of these objects in the Doxxy schema The Synonyms screen allows you to do this using the Doxxy web application It shows a list of all the objects that can be accessed by the Doxxy schema tables views packages Existing synonyms will be included in the list In the next example you ll notice that for the application schema DEMO for some tables synonyms were created while for others not SYSAdmin Admin Doxxy User Manual Version 01 02 iAdvise Doxxy Overview Synonyms Actions Applications Log Messages Log Configs Owner Table Synonym License DEMO DEMO IMAGES DEMO IMAGES DEMO DEMO USERS DEMO USER DEMO DEMO CUSTOMERS DEMO CUSTOMERS DEMO DEMO ORDERS DEMO ORDERS DEMO DEMO ORDER ITEMS DEMO ORDER ITEMS DEMO DEMO PRODUCT INFO DEMO PRODUCT INFO DEMO DEMO STATES DEMO DEMO PAGE HIERARCHY If you want to create a new synonym press the LJ button Provide a name for the new synonym and click to save the synonym DEMO DEMO STATES DEMO STATES Existing synonyms deleted pressing the button SYSAdmin Admin Doxxy User Manual Version 01 02 iAdvise Doxxy APPENDIX INSTALLATION INTRODUCTION Doxxy 01 02 comes as an export file of a Pac
59. user only has to have basic knowledge of this word processor The software supports Microsoft Word tables Data from the Oracle database can be entered into these tables Bulleted lists both numbered and unnumbered are supported Data from the Oracle database can be added to these lists Dynamic headers footers are supported Images and charts in the templates are supported 1 3 WHAT IS ALREADY POSSIBLE IN Input data from the Oracle database using SQL statements Capture input parameters Reuse columns and parameters in other SQL statements Nested master detail structures Display data according to the different layout options text paragraphs tables and lists Conditional display of text and data Conditional formatting of text and data Display images both those imprinted in the templates as well as images from the database Load different templates this is really handy if you want to generate your report in various languages Generate to DOCX optional license allows you to generate PDF files Introduction Doxxy User Manual Version 01 02 iAdvise Doxxy 2 INSTALLATION 2 1 SOFTWARE REQUIREMENTS Oracle 10 2 or higher APEX APEX 4 x or higher 2 2 INSTALLATION Doxxy 01 02 comes as an export file of a Packaged Application for APEX The file contains 1 the APEX UI and 2 all database objects as supporting objects The installation process
60. verview of the ordered products Example 28 02 2014 16 11 36 V Confirmation document from the Doxxy manual H New Folder You can create documents by using the Create Document button or by using the icon that appears when you hover over a folder in the folder tree Rename the document provide some additional information and select the parent folder that you want to save the document in Next click on Create CREATE Document Document Name My first document My first document Description Folder My Documents Begin Tag End Please note the start and end tag can be set per document In this example we have used the square brackets and but these can be changed if required We will need these tags when we create the templates Exploring Doxxy Doxxy User Manual Version 01 02 11 69 iAdvise Doxxy You can open a document by clicking on the document that you require in the tree to the left In the illustration below the Order Confirmation document has been opened A document consists of one or more SQL statements one or more PL SQL statements one or more templates and any input parameters In the illustration below the Order Confirmation demo report has been opened You can easily navigate between the SQL statements PL SQL statements the templates and the parameters by using the folder tree to the left or the detailed view to the
61. y User Manual Version 01 02 iAdvise Doxxy 6 TIPS amp TRICKS Right now you have acquired enough information to get you started with Doxxy However you will occasionally want to know how to tackle a particular problem Unfortunately we cannot cover everything in this user manual We will of course keep you informed of all the interesting facts and provide you with solutions to frequently asked questions Our website http www doxxy eu is ideal for this purpose We recommend that you visit our website regularly to stay up to date with the latest tips and tricks Tips amp tricks Doxxy User Manual Version 01 02 iAdvise Doxxy Product Architecture Demo Support Contact Us Try Doxxy A Buy Doxxyw ORACLE DATABASE What is Doxxy Simple Architecture Download now Document generation for Oracle made easy Easy to install No massive infrastructure cost Get started with Doxxy and try it for free Merge your business data with a User friendly RAD tool Pure PL SQL engine Easy to embed into your application Intuitive UI Oracle APEX to maintain Low cost buy in the meta data Do you want more info Contact us Word template docx to create your operational document like letters orders InVOIC8S More More More More If you have any questions or you need assistance with solving a problem please do not hesitate to contact us at info doxxy eu We will do our utm
62. y read the data in the folders and documents This user cannot create new folders and documents or make any changes to it At the bottom of the page you can decide which folder s a user has access to Note that in the following example the user DOXXY USER got the roles Administrator and Document Editor and access to the folder DOXXY SYSAdmin Admin Doxxy User Manual 58 69 Version 01 02 Admin Create Edit DOXXY_USER Username r DOXXY_USER Log Messages 21 Log Configs License Email 7 Synonyms System Administrator Administrator Viewer Reader Document Editor Roles v Change Password Folders Q Actions Folder Edit Document SYSAdmin Admin Doxxy User Manual Version 01 02 iAdvise Doxxy 8 2 LOGGING Although the Doxxy team has done its utmost to create a high quality error free application there is always a possibility that errors might have slipped through encounter any errors please contact the Doxxy support team info doxxy eu Depending on the problem we might ask you to trace certain packages There are two ways of doing this through the Log Configs and Log Messages 8 2 1 LOG CONFIGURATIONS You can adjust the level of the logging settings on the Log Configs page This will determine the level of severity of the messages to be transmitted and to be logged in the execution of the
63. y the corresponding SELECT statement If there is a code in a bulleted list then the list will also contain the correct number of items according to the corresponding SELECT statement This section explains how to create your template Please refer to the example in the next section for more information about displaying conditional data and displaying images in your report To create your template please open Microsoft Word Design your document in the way you want the data to be displayed titles tables lists headers and footers and images If you want to insert data from an SQL statement you have three display options to choose from namely paragraph bulleted list or table Some concrete examples Paragraph BEGIN ORDERS Customer CUST NAME made an order on ORDER DATE He had to ORDER TOTAL for the ordered products END ORDERS Bulleted list e BEGIN ORDERS CUST NAME ORDER DATE lt TOTAL END ORDERS Table Order Total BEGIN ORDERS CUST NAME ORDER DATE ORDER TOTAL END ORDERS My first report Doxxy User Manual Version 01 02 iAdvise Doxxy Important notes 1 15 mandatory to use the BEGIN END tags to indicate the beginning and end of a statement regardless of the selected view 2 The codes used in the BEGIN and END tags refer to the Recordset Codes from the SQL statements To achieve better readability it is recommended to always use capital

Download Pdf Manuals

Related Search

Related Contents

  Manual de Instruções  ZW150-5 - Scai Spa  00表1_表4.ai - 日本ネットワークサポート  Hitachi DVD Recorder with 250GB Hard Disc Drive  Samsung ES75 دليل المستخدم  Pyle Car Stereos User Manual  Call Me cod. CM100 – Instalación & Manual de uso 12/02/2013  Garmin Forerunner 610 Owner's Manual  Manual de usuario del monitor LCD  

Copyright © All rights reserved. Failed to retrieve file