среда, 18. април 2018.

master detail oracle apex 5.1.x

Master detail oracle apex 5.1 have advanced options, older version apex (4.x/3.x) dont have, on one page could be put 2,3,4... interactive grid (known as tabular form on oldest apex version)
from master page (from master tables) user can view detail for every records( usefull in purchase order/ purchase order lines, invoices and invoice line, orders/order lines, Bill of Quantity / Bill Of quantity lines...all master - detailed situations)
I
theme 42 apex 5.1 have good performances with mozilla browsers (tablet, mobile ...) and apex users can get informations in any places.

Creating region with select type as interactive grid on design mode (not depretiotion mode) should be added details interactive grid with exactly row id selections.











недеља, 15. април 2018.

tracking contract for billing systems



Tracking contract values can be very usefull in billing systems, this example show how this work in oracle apex 5.1

https://apex.oracle.com/pls/apex/f?p=51562:17










CREATE OR REPLACE FORCE VIEW "DATA_RANGES" ("START_DATES", "DT", "FLEX002", "NUMBER_OF_PARTS", "R", "CODE_CO", "DEBT_VALUE_PER_MONTHS") AS
  with nums as
 (select level r from dual connect by level <= 9999)
select t.START_DATES,add_months(t.START_DATES, decode(FLEX002,'MONTH',1,'YEAR',12) * (r-1) ) dt,t.FLEX002,t.NUMBER_OF_PARTS, nums.r,t.CODE_CO,
((VALUE_CO-t.PARTICIPATION-t.ADD_VALUE)/t.NUMBER_OF_PARTS) as debt_value_per_months
 from contract t, nums
 where t.NUMBER_OF_PARTS >= nums.r
 order by t.START_DATES


this view -plan insert values into PLAN_TABLE with procedure calling in final steps in oracle apex form, as active transactions or not, shoud be changes in contract adding cnex of contract-thats reason


 Modal dialog is form for input new values for new contract, consist :
contract values, contract begin -date, contract end -date, number of months between start date and end date is equivalent of number of rates, participation values, if some pay initialy money

Model dialog looks
and with java script have calculations for populate fields








субота, 31. март 2018.

oracle apex 5.1.4 change clasical report to timeline

Gui give extend informations for some internal processing and have great visualisations for users on very simple way
 all version apex have this options, version 5.1.4 give mostly plug - in extends
and this is advanced options now with oracle 11g xe databases I m using for own manipulation of dates (import, sort, select updates inserting etc)

Switch from clasical select * from  view to timeline view :

субота, 23. септембар 2017.

demo apps apex 5.1.1 and 4.1.1 with jasper reports integrated

Apex 5.1.1 have powerfull design, I was compare demo apps from apex 4.1.1 and 5.1.1 part from orders with collection, trying to extend master detail reports with jasper with parameters from reports to form, on one click to pdf, connect to localy 127.0.0.1 hosts, not remotly ip address, this kind of reports development need backup and re-dmp database from remote server to localy, then transfer jrxml to remote folders structures, in this cases to tomcat folders.
Its tipicaly functionality of more erp systems, sales order, service requests, master detail BOM/BOQ, from now, from version 5.1.1 could be really erp system with all functionality, as have compiere or adempeire/idempiere..

 Reporting need consist with ptotos, so its important to change oracle blob data times from blob column to display images option

 next step is delete field (product_image) from detailed region in display, replacing it with images from palletes, which need be mapped to blob colum (product_image), its only need to do, iReport and oracle databases didn have equal data types, so its need be changeg, with this second step.
Next, need add additional parameters for filtering reasons and copu paste this jrxml to tomcat/reports folder(or glassfish /reports folder)  an of course in forms set MASTER_DETAIL_JASPER as report names and all 6 fields which need be populates with java script, if not, could be display all orders,a s pic above shown:

So nice, ist it, and secure, instead of I was built tis reporting with oracle 10g xe on 32 bytes os linux suse 11.3, one cpu 2ghz, 1 GB ram, my old laptop I m only use it for testing, connect to LAN, upgraded to 4.1.1 apex, and jasper reports 2 version with tomcat 6.29 jdk 1.6 0 24, only problem this scenario have is its slowly, and cant be pass more than 5 concurent client, procesor is too slowly and RAM ddr2 slowly too.

its same scenaio, with two version of apex, 4.1.1
and 5.1.1

jasper reports firewall centos suse

I was installing sql from remote oracle database 11g xe (11.2.0.1) over sqlplus
to  10 g xe (10.2.0.1) 32 byte open suse 11.3 linux from directorium structures from centos, main oracle database machine with oracle apex 5.1.1, to oracle 10g xe with apex 4.1.1 all remote, and set jasper reports integration, with closing firewall and prepare form secure printing.
firewal dont alow tomcat/glassfich port where is JasperReports.war deployed
so sny way how to pdf/excell/rtf can be showen is over blob.
On centos linux I was enabled only port from oracle apex listener, port from
glassfish 8080 is hiden as 4848, all to.
open suse is remote pc with 32 byte systems with apex 4.1.1 and have tomcat with java 1.6, disable too tomcat port 6080, only open port with oracle apex listener 8082, and sometimes open port 22221 form ssh, other access is with team viewer.
its report with calling procedure REQ_GET_REPORT_TUNNEL from page 5
no pass 6080 port. ON config files from tomcat, I was set locahost, where is tomcat apache and oracle 10 g install, same is with centos, with server glassfish version 4 and properties file on report folders. For exporting specific reports, its only need copy/paste as option from team viewer for repote files trasfers (or scp) into folders structures, and seting oracle pages to show this reports
All pages are login pasword secured on web, so this kind of reporting is very popular and easy to seting, and work perfect and stable.
Oracle API (https://docs.oracle.com/cd/E59726_01/doc.50/e39149/toc.htm) give very big options for manipulate files, depends on your needs, its simple plsql procedures, programmer could simple modify it for solve current problems.
With export print pages from form, there is need field from apex forms




with two proces is blob created and open window in current apex pages, way how to hide public jasper reports port and show report over internal apex feature

1) PRC_SHOW_REPORT_TUNNEL

begin
  xlog('PRC_SHOW_REPORT_TUNNEL', 'url (orig):' || :p5_report_url);

  xlib_jasperreports.set_report_url(:G_REPORT_URL);
  xlib_jasperreports.show_report (p_rep_name                   => :p5_rep_name,
                                   p_rep_format                 => :p5_rep_format,
                                   p_data_source                => :p5_data_source,
                                   p_out_filename               => :p5_out_filename,
                                   p_rep_locale                 => :p5_rep_locale,
                                   p_rep_encoding               => :p5_rep_encoding,
                                   p_additional_params          => :p5_additional_params
                                  );


  apex_application.g_unrecoverable_error := true;

exception
  when others then
    xlog('PRC_SHOW_REPORT_TUNNEL', sqlerrm, 'ERROR');
    raise;
end;


2) PRC_GET_REPORT_TUNNEL

DECLARE
   l_blob        BLOB;
   l_mime_type   VARCHAR2 (100);
BEGIN
   xlog ('PRC_GET_REPORT_TUNNEL', 'url (orig):' || :g_report_url);
   ------------------------------------------------------------------------
   -- generate the report and return in BLOB
   ------------------------------------------------------------------------
   xlib_jasperreports.set_report_url (:G_report_url);
   xlib_jasperreports.get_report
                                (p_rep_name               => :p5_rep_name,
                                 p_rep_format             => :p5_rep_format,
                                 p_data_source            => :p5_data_source,
                                 p_rep_locale             => :p5_rep_locale,
                                 p_rep_encoding           => :p5_rep_encoding,
                                 p_additional_params      => :p5_additional_params,
                                 p_out_blob               => l_blob,
                                 p_out_mime_type          => l_mime_type
                                );
   ------------------------------------------------------------------------
   -- set mime header
   ------------------------------------------------------------------------
   OWA_UTIL.mime_header (ccontent_type      => l_mime_type,
                         bclose_header      => FALSE);
   ------------------------------------------------------------------------
   -- set content length
   ------------------------------------------------------------------------
   HTP.p ('Content-length: ' || DBMS_LOB.getlength (l_blob));
   OWA_UTIL.http_header_close;
   ------------------------------------------------------------------------
   -- download the file and display in browser
   ------------------------------------------------------------------------
   WPG_DOCLOAD.download_file (l_blob);
   ------------------------------------------------------------------------
   -- release resources
   ------------------------------------------------------------------------
   DBMS_LOB.freetemporary (l_blob);
   ------------------------------------------------------------------------
   -- stop rendering of APEX page
   ------------------------------------------------------------------------
   apex_application.g_unrecoverable_error := TRUE;
EXCEPTION
   WHEN OTHERS
   THEN
      xlog ('PRC_GET_REPORT_TUNNEL', SQLERRM, 'ERROR');
      RAISE;
END;


so with apex, can simple, export and import files in this or similar way, with strong apex API.


















петак, 22. септембар 2017.

oracle apex 5.1.1 jasper reports integration linux

Jasper reports integration

folder structures on linux centos
utl http grant to compiere
install sys and localy user compiere
immport test applications from opal consulting and close jasper reports ports from tomcat/ glassfish and use export from blob

run this query from sys as sysdba

begin
dbms_network_acl_admin.create_acl (
acl => 'utl_http.xml',
description => 'HTTP Access',
principal => 'COMPIERE',
is_grant => TRUE,
privilege => 'connect',
start_date => null,
end_date => null
);
commit;
dbms_network_acl_admin.add_privilege (
acl => 'utl_http.xml',
principal => 'COMPIERE',
is_grant => TRUE,
privilege => 'resolve',
start_date => null,
end_date => null);
commit;
dbms_network_acl_admin.assign_acl (
acl => 'utl_http.xml',
host => '192.168.8.102',
lower_port => null,
upper_port => null
);
commit;
end;

grant execute on utp_http to COMPIERE;


SQL> l
1* select * from user_network_acl_privileges;


DECLARE
  ACL_PATH  VARCHAR2(4000);
  ACL_ID    RAW(16);
BEGIN
  -- Look for the ACL currently assigned to '*' and give APEX_040000
  -- the "connect" privilege if APEX_040000 does not have the privilege yet.

  SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
   WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

  -- Before checking the privilege, make sure that the ACL is valid
  -- (for example, does not contain stale references to dropped users).
  -- If it does, the following exception will be raised:
  --
  -- ORA-44416: Invalid ACL: Unresolved principal 'APEX_040000'
  -- ORA-06512: at "XDB.DBMS_XDBZ", line ...
  --
  SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID
    FROM XDB.XDB$ACL A, PATH_VIEW P
   WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) AND
         EQUALS_PATH(P.RES, ACL_PATH) = 1;

  DBMS_XDBZ.ValidateACL(ACL_ID);
   IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_040000',
     'connect') IS NULL THEN
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
     'APEX_040000', TRUE, 'connect');
  END IF;

EXCEPTION
  -- When no ACL has been assigned to '*'.
  WHEN NO_DATA_FOUND THEN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
    'ACL that lets power users to connect to everywhere',
    'APEX_040000', TRUE, 'connect');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END;
/
COMMIT;




DECLARE
  ACL_PATH  VARCHAR2(4000);
  ACL_ID    RAW(16);
BEGIN
  -- Look for the ACL currently assigned to 'localhost' and give APEX_040000
  -- the "connect" privilege if APEX_040000 does not have the privilege yet.
  SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
   WHERE HOST = 'localhost' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

  -- Before checking the privilege, make sure that the ACL is valid
  -- (for example, does not contain stale references to dropped users).
  -- If it does, the following exception will be raised:
  --
  -- ORA-44416: Invalid ACL: Unresolved principal 'APEX_040000'
  -- ORA-06512: at "XDB.DBMS_XDBZ", line ...
  --

  SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID
    FROM XDB.XDB$ACL A, PATH_VIEW P
   WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) AND
         EQUALS_PATH(P.RES, ACL_PATH) = 1;

  DBMS_XDBZ.ValidateACL(ACL_ID);
   IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_040000',
     'connect') IS NULL THEN
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH,
     'APEX_040000', TRUE, 'connect');
  END IF;

EXCEPTION
  -- When no ACL has been assigned to 'localhost'.
  WHEN NO_DATA_FOUND THEN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('local-access-users.xml',
    'ACL that lets power users to connect to everywhere',
    'APEX_040000', TRUE, 'connect');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('local-access-users.xml','localhost');
END;
/
COMMIT;










REM Show the dangling references to dropped users in the ACL that is assigned
REM to '*'.

SELECT ACL, PRINCIPAL
  FROM DBA_NETWORK_ACLS NACL, XDS_ACE ACE
 WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL AND
       NACL.ACLID = ACE.ACLID AND
       NOT EXISTS (SELECT NULL FROM ALL_USERS WHERE USERNAME = PRINCIPAL);

Next, run the following code to fix the ACL:

DECLARE
  ACL_ID   RAW(16);
  CNT      NUMBER;
BEGIN
  -- Look for the object ID of the ACL currently assigned to '*'
  SELECT ACLID INTO ACL_ID FROM DBA_NETWORK_ACLS
   WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

  -- If just some users referenced in the ACL are invalid, remove just those
  -- users in the ACL. Otherwise, drop the ACL completely.
  SELECT COUNT(PRINCIPAL) INTO CNT FROM XDS_ACE
   WHERE ACLID = ACL_ID AND
         EXISTS (SELECT NULL FROM ALL_USERS WHERE USERNAME = PRINCIPAL);

  IF (CNT > 0) THEN

    FOR R IN (SELECT PRINCIPAL FROM XDS_ACE
               WHERE ACLID = ACL_ID AND
                     NOT EXISTS (SELECT NULL FROM ALL_USERS
                                  WHERE USERNAME = PRINCIPAL)) LOOP
      UPDATE XDB.XDB$ACL
         SET OBJECT_VALUE =
               DELETEXML(OBJECT_VALUE,
                         '/ACL/ACE[PRINCIPAL="'||R.PRINCIPAL||'"]')
       WHERE OBJECT_ID = ACL_ID;
    END LOOP;

  ELSE
    DELETE FROM XDB.XDB$ACL WHERE OBJECT_ID = ACL_ID;
  END IF;

END;
/

REM commit the changes.

COMMIT;



adempiere 390

Adempiere 390 is ready for production, and could be build with netbeans

Assets