{"id":5445,"date":"2023-09-06T17:19:13","date_gmt":"2023-09-06T15:19:13","guid":{"rendered":"https:\/\/gpmfactory.com\/?p=5445"},"modified":"2024-01-29T17:19:54","modified_gmt":"2024-01-29T16:19:54","slug":"fop-lab-experimental-apex-project","status":"publish","type":"post","link":"https:\/\/gpmfactory.com\/index.php\/2023\/09\/06\/fop-lab-experimental-apex-project\/","title":{"rendered":"FOP Lab &#8211; Experimental APEX Project"},"content":{"rendered":"\n<p>This application is an experimental project dedicated to developers who need to build sql query generating XML. <br>In addition, the application generates a default xslt FOP template which can be used by a FOP server.<br><a href=\"https:\/\/github.com\/patrickmonaco\/Fop-Lab\">Download on Github<\/a><br><br>For generating pdf, we must provide a valid Report server endpoint with FOP installed on tomcat.<br>I used the <a href=\"https:\/\/github.com\/mikhailidim\/apex5_fop\">apex5_fop project<\/a> from mikhailidim available on github.<br>That can be https:\/\/github.com\/darklordgrep\/APEX-FOP\/blob\/main\/apex_fop.jsp as well, which has been published recently in march 2023.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Usage<\/h3>\n\n\n\n<p>We choose a table or a view and some general rendering options.<br>then, FOP LAB  follows all the Foreign Keys links in oder to generate an sql query and a default xslt layout.<br>If no FK are retrieved, only the master table is printed. In others cases, the master is printed in form display and all the chlid tables are displayed as xslt tables.<br>Ie: if we choose EMP table, the \u00ab\u00a0managed people\u00a0\u00bb are displayed as a grid under each master emp.<\/p>\n\n\n\n<p>xslt templates are generated from xslt fragments which can be customized directly in the application.<br> <br><\/p>\n\n\n\n<figure class=\"wp-block-image size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2023\/09\/image.png\" alt=\"\" class=\"wp-image-5446\" style=\"width:302px;height:310px\" width=\"302\" height=\"310\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2023\/09\/image.png 858w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2023\/09\/image-292x300.png 292w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2023\/09\/image-768x789.png 768w\" sizes=\"auto, (max-width: 302px) 100vw, 302px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2023\/09\/image-3-1024x681.png\" alt=\"\" class=\"wp-image-5451\" style=\"width:361px;height:240px\" width=\"361\" height=\"240\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2023\/09\/image-3-1024x681.png 1024w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2023\/09\/image-3-300x199.png 300w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2023\/09\/image-3-768x510.png 768w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2023\/09\/image-3.png 1106w\" sizes=\"auto, (max-width: 361px) 100vw, 361px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2023\/09\/image-1.png\" alt=\"\" class=\"wp-image-5447\" style=\"width:362px;height:515px\" width=\"362\" height=\"515\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2023\/09\/image-1.png 618w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2023\/09\/image-1-211x300.png 211w\" sizes=\"auto, (max-width: 362px) 100vw, 362px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2023\/09\/image-2-772x1024.png\" alt=\"\" class=\"wp-image-5448\" style=\"width:351px;height:466px\" width=\"351\" height=\"466\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2023\/09\/image-2-772x1024.png 772w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2023\/09\/image-2-226x300.png 226w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2023\/09\/image-2-768x1019.png 768w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2023\/09\/image-2.png 848w\" sizes=\"auto, (max-width: 351px) 100vw, 351px\" \/><\/figure>\n\n\n\n<p>The application is mainly intended to generate a sql query which generates in turn some xml stuff.<br>Because wrinting this kind of query is cumbersome, FOP LAB can be a good friend.<br>If we need to customize content of a child, the supported metthod is to create a view with a name identical to the child table and a suffix &lsquo;_FOP_V&rsquo;. In this case the view supersedes the original table.<br>IN the following exemple, we use ORDERS_ITEMS_FOP_V instead ORDERS_ITEMS to display aggregates (Total) and retrieve the product name.<br>It&rsquo;s needed to add a Foreign key with &lsquo;DISABLED&rsquo; status for linking the view with the ORDERS table.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2023\/09\/image-4.png\" alt=\"\" class=\"wp-image-5453\" style=\"width:364px;height:296px\" width=\"364\" height=\"296\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2023\/09\/image-4.png 1006w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2023\/09\/image-4-300x244.png 300w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2023\/09\/image-4-768x625.png 768w\" sizes=\"auto, (max-width: 364px) 100vw, 364px\" \/><\/figure>\n\n\n\n<p>Exemple of generated Sql Query<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>select \"ORDERS\"\nfrom\n(SELECT XMLSERIALIZE(\n\u00a0\u00a0\u00a0\u00a0 CONTENT XMLELEMENT(\"ORDERS\",\nXMLELEMENT(\"HEADER\", XMLELEMENT(\"report_date\", TO_CHAR(SYSDATE,'Day DD Month YYYY')),\nXMLELEMENT(\"TITLE\", ''),\nXMLELEMENT(\"DD\", TO_CHAR(SYSDATE,'Day DD')),\nXMLELEMENT(\"D\", TO_CHAR(SYSDATE,'DD')),\nXMLELEMENT(\"DAY\", TO_CHAR(SYSDATE,'Day')),\nXMLELEMENT(\"MONTH\", TO_CHAR(SYSDATE,'Month')),\nXMLELEMENT(\"YEAR\", TO_CHAR(SYSDATE,'YYYY'))\n),\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 XMLAGG (\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 XMLELEMENT(\"ORDERS_REC\",\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 XMLFOREST (\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 e.ORDER_ID AS \"ORDER_ID\",\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 e.ORDER_DATETIME AS \"ORDER_DATETIME\",\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 e.CUSTOMER_ID AS \"CUSTOMER_ID\",\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 e.ORDER_STATUS AS \"ORDER_STATUS\",\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 e.STORE_ID AS \"STORE_ID\",\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CUSTOMERS.CUSTOMER_ID AS \"CUSTOMERS_CUSTOMER_ID\",\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CUSTOMERS.FULL_NAME AS \"CUSTOMERS_FULL_NAME\",\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CUSTOMERS.EMAIL_ADDRESS AS \"CUSTOMERS_EMAIL_ADDRESS\",\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 STORES.STORE_ID AS \"STORES_STORE_ID\",\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 STORES.STORE_NAME AS \"STORES_STORE_NAME\",\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 STORES.WEB_ADDRESS AS \"STORES_WEB_ADDRESS\",\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 STORES.PHYSICAL_ADDRESS AS \"STORES_PHYSICAL_ADDRESS\",\/* ------ ORDER_ITEMS_FOP_V \/ SYS_C0064516 ------ *\/\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (select XMLAGG(\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 XMLELEMENT(\"ORDER_ITEMS_FOP_V_ORDERS_REC\",\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 XMLFOREST(\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ORDER_ITEMS_FOP_V.QUANTITY AS \"QUANTITY\",\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ORDER_ITEMS_FOP_V.UNIT_PRICE AS \"UNIT_PRICE\",\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ORDER_ITEMS_FOP_V.MNT AS \"MNT\",\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ORDER_ITEMS_FOP_V.ORDER_ID AS \"ORDER_ID\",\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ORDER_ITEMS_FOP_V.PRODUCT_ID AS \"PRODUCT_ID\",\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ORDER_ITEMS_FOP_V.PRODUCT_NAME AS \"PRODUCT_NAME\"\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 from ORDER_ITEMS_FOP_V\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 where ORDER_ITEMS_FOP_V.ORDER_ID = e.ORDER_ID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ) ORDER_ITEMS_FOP_V\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\n\u00a0\u00a0\u00a0\u00a0 )\n) AS \"ORDERS\"\nfrom \"ORDERS\" e ,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CUSTOMERS,\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 STORES\nwhere rownum&lt;100and\nNVL(:PKID,e.ORDER_ID) = e.ORDER_ID\nAND e.CUSTOMER_ID =CUSTOMERS.CUSTOMER_ID\nAND e.STORE_ID =STORES.STORE_ID\n)<\/code><\/pre>\n\n\n\n<p>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/p>\n","protected":false},"excerpt":{"rendered":"<p>This application is an experimental project dedicated to developers who need to build sql query generating XML. In addition, the application generates a default&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"ppma_author":[150],"class_list":["post-5445","post","type-post","status-publish","format-standard","hentry","category-non-classe"],"authors":[{"term_id":150,"user_id":1,"is_guest":0,"slug":"admin8700","display_name":"Patrick","avatar_url":"https:\/\/secure.gravatar.com\/avatar\/209d5ed69b74d288390621ab4c1d3773?s=96&d=mm&r=g","0":null,"1":"","2":"","3":"","4":"","5":"","6":"","7":"","8":""}],"_links":{"self":[{"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/posts\/5445","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/comments?post=5445"}],"version-history":[{"count":11,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/posts\/5445\/revisions"}],"predecessor-version":[{"id":5504,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/posts\/5445\/revisions\/5504"}],"wp:attachment":[{"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/media?parent=5445"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/categories?post=5445"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/tags?post=5445"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/ppma_author?post=5445"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}